What to use for notification persistence?

One entry per user per notification, right in your database. You can have a background job to delete delivered entries to keep the database lean. In any event, you are way too early to consider scalability.

Really! That is what I had in mind, but I thought it would lead to an insane amount of duplication. I’m not trying to be hyper performant or anything, but it sounded to me that having one entry per user for a given event would be duplicating that event each time.

Would you have one table for the events and link the users with a many-to-many or you’d actually have a duplication of the event for each notification entry?

Thank you!

It is a matter of normalizing your database schema. You should familiar yourself with the normal form and eliminate the data duplication.

So the way I conceptualize in my mind now is to have a notifications table that holds all the data (perhaps just a json b field) for each notification and then a users_notifications table that references users and notifications and holds the status for the notification (if delivered or something else).

Does that seem right?

I have no idea of what are your requirements. Honestly, I think using a jsonb field is a bit strange.

You got this right. But you’ll want to use a has-many-through users -> user_notifications -> notifications because you need to store the status in the join table so you can index and filter on it. That’ll make the queries much more performant.

There are pros and cons of using jsonb to store your data and, as @derek-zhou implies, you really have to understand whether that’s something you need and you’re willing to live with the tradeoffs. Know, your, anti-patterns

I mostly thought of using jsonb because the notification system is supposed to receive events from all sorts of different parts of the application that results in different structs, so I thought of storing them schemalessly (is that word?) as to just have them ready when the user access it… but reading the references you pointed me to, I started thinking of ways of having a Notification struct and have the different results to comfort to it somehow instead of the other way around.

Anyways, thank you so much for your time and for the references!