How to store events? | Aryan Agarwal

How to store events?

While building a product storing events is very necessary. We should always store events for each and every action a user performs.

Storing events can help us in generating Notifications, Analytics, identifying issues/bugs, etc.

In this post, I am going to explain how we should store events.

Separate service for events

We should create a new service that will consume events from multiple servers to store events in an events table using some type of Queue service based on your use case such as Redis(Bull), Apache Kafka, RebitMq, etc.

In FIFO we are using Kafka because of multiple advantages but in this post, I won't be going into that comparison.

For A Quick Intro to Kafka check out - https://fifo.im/p/gq57fjqqsz30.

Database table schema

To store all events in a table we would need a generic table. So, for that after a lot of discussions and research, we came up with a schema that will contain the following columns:

1. id*
2. event_type*
3. performer_id*
4. performer_type*
5. entity_id
6. enitity_type
7. refrence_id
8. refrence_type
9. timestamp*
10. metadata
11. created_at*
12. updated_at*

*non-nullable fields

An explanation for every column

To understand properly I will be taking an example of a post-creation event, adding a comment to that post, and then replying to that comment. Also, I will be taking an example of a user signup event example.

id - An auto-increment integer primary key.

event_type - An Enum that can contain all the events as per your use case.

performer_id - An string type, the id of the user because of whom the event is generated, this can be null as well for system actions.

performer_type - This will be the type of performer_id and will be an Enum. Eg- USER, ADMIN, SUPER_ADMIN, SYSTEM, etc.

entity_id - An string type, the id of the entity that is generated for example post id.

enitity_type - This will be the type of entity_id and will be an Enum. Eg- POST, USER, ROOM, COMMENT, etc.

refrence_id - An string type nullable field, the id of the reference for which entity is generated for example post id when a comment is generated.

refrence_type - This will be the type of refrence_id and will be an Enum. Eg- POST, USER, ROOM, etc.

timestamp - The date & time at which the entity/event is generated, it will be sent from the main server with the event to the events server.

metadata - This will be a jsonb nullable field, it will contain the JSON of the event that is sent from the main server to the events server.

created_at - The date & time at which the event is inserted into the database.

updated_at - By default, it will contain the same date & time as created_at but if a row is updated the this field is also updated with the time at which the row is updated.

Using the above-explained table schema we can store any type of event and then we can use this data to generate Analytics, Notification, etc using SQL queries.

I will also be posting SQL queries for some complex use cases so please don't forget to check out my other interesting spaces:

• https://fifo.im/+glue_labs_engineering
• https://fifo.im/+full_stack_development

Thanks for reading out! Hope you have a nice day.