Notification System: Data Storage and Schema

Step 6: Data Storage and Schema

So far we have moved notifications through services, queues, and workers. Now we decide where the data behind them is kept.

This design stores two kinds of data. One kind is small, changes slowly, and has to be right. The other is huge, is written constantly, and can be a little behind. No single database is good at both, so we use two.

SQL tables hold the small, structured data: tenants, users, preferences, and templates. A wrong preference here sends a message to somebody who refused it. Relational tables give constraints, foreign keys, and transactions to keep that from happening.

NoSQL collections hold the history: every notification sent, and every change to its status. This data is written constantly and grows to billions of records. It is read by one key, usually the user id. It can be a little behind without harm. A NoSQL store scales that load by adding nodes.

That one split explains every choice in this step. Configuration must be right and is small. History must scale and is not small.

The diagram below shows both groups and how they connect. After it comes a table for each store, listing every field, its type, and what it is for. A note after each table says why it is shaped that way.

SQL Tables

The SQL side holds data that is small and changes slowly: tenants, users, preferences, templates, scheduled jobs, and audit logs. The tables are normalized, and foreign keys tie them together. A foreign key is a column that must match a row in another table, so a broken reference cannot be stored.

tenants

One row per tenant. A tenant is an application or organization that uses the notification system.

Every other table points at a tenant, which is how each application's data stays apart from the rest. The row itself holds little: a name, a contact, and a status flag that switches the tenant on or off. It rarely changes, so a relational table fits it well.

users

One row per person who receives notifications. Every user belongs to a tenant.

The tenant_id foreign key ties each user to the right application. Because the database enforces it, a notification can only point at a user who exists. Joins to preferences or templates stay simple when we need them.

user_preferences

One row per user, holding which channels the user has turned on or off.

Preferences live apart from the profile, so we can add a new preference field without touching user data. Each row is one flag per channel. The check before a send is then a single lookup: has this user opted in to email?

notification_templates

Reusable message templates. A template is written once and used by many notifications.

Each template belongs to one tenant and one channel, and holds placeholders for the user's data. Templates change rarely and are shared by many sends, so they need strong consistency. An update is one transaction, and every later send uses the approved text. Versioning can be added if needed.

scheduled_notifications

Notifications that should go out later, like a reminder or an announcement at a set time.

Each row ties one user, one template, and one send time. Transactions stop the same job from being scheduled twice. The query the scheduler runs is then simple: find every row due in the next hour.

The status field moves from scheduled to sent or canceled as the job is handled. When the time arrives, the system reads the row and creates a real notification event. That event is recorded in the NoSQL notifications collection, described below.

audit_logs

A record of important events for compliance and debugging: notifications sent, preference changes, template edits, and admin actions.

Compliance needs records that cannot be changed and cannot be lost. A SQL table gives that through ACID guarantees, so a write either completes fully or not at all, and what is written stays.

A sent notification can be logged here as well as in the NoSQL history, so regulators get records that are easy to query. Preference and template changes are logged too, with who did what and when. Filter by tenant or user to build an audit report.

NoSQL Collections

The tables above decide what gets sent. The two collections below record what was sent, and there are far more of those records than there are rows above.

The NoSQL collections hold that high-volume data: the notifications themselves and their delivery status. A store like MongoDB or Cassandra scales to millions of records and a high write rate by adding nodes. It also allows a flexible schema, so one document can carry fields the next one does not. Each notification is one document. Each change to its status is a separate document.

notifications

The notification history. Each record is one notification event: one message sent to one user.

Every notification for every user is logged here, and the transactional SQL store never takes that load. Each document holds the content and the context, which is what an in-app inbox or history feature reads.

The record keeps both the message text and the template_id, and that is not redundant. The text is what was sent, so it survives a later template edit. The id says which template produced it, so every message from a bad template can be found later. The channel says how it was delivered, so a user's history can be filtered by channel.

tenant_id and user_id are copied into every record. That duplicates relational data on purpose. NoSQL reads do not join, and the common query is "all notifications for this user" or "for this tenant". So each document carries everything a read needs. That is the trade NoSQL makes: some duplication for fast reads and easy scaling.

notification_status

Tracks the delivery status of a notification, apart from its content. It records whether a notification was sent, delivered, read, or failed, and how that changed over time.

The two collections are split because the two halves are written so differently. The content is written once. The status changes several times: sent at time A, delivered at time B, read at time C.

Each of those is a new document in notification_status, so the large notification record is never rewritten. Status updates are frequent, and appending small documents is cheap. It also avoids many writers updating one record at the same time.

Each entry carries notification_id and user_id for easy lookup. The status history can then be queried on its own, for analytics or debugging. The pattern is common in other systems: a receivers-and-statuses collection that links a notification to a recipient and a state. Logging status this way shows who received or read each notification, and it scales without heavy joins.

Dead letter queue storage. Some notifications fail every retry. Those go to a dead letter queue, a separate store for messages that could not be delivered. A person can look at them later.

One option is a table FailedNotifications(notification_id, user_id, channel, error_code, last_attempt_time) that the operations team reviews. Another is the dead letter queue built into the message queue, like a Kafka topic or a RabbitMQ DLQ.

Once the cause is fixed, these records can be reprocessed. They are also evidence when a provider misbehaves.