Step 6: Data Storage and Schema

A wrong row in the preferences table sends an SMS to someone who refused it. A missing row in the delivery history costs one line in a support report. The two kinds of data in this design fail in such different ways that they belong in different databases.

The configuration a send uses (tenants, users, preferences, templates) is small, changes slowly, and must be correct. Relational tables give it constraints, foreign keys, and transactions. The history a send produces (every notification and every change to its status) is written constantly, grows to billions of records, is read by one key, usually the user ID. It can also be a little behind without harm. A NoSQL store handles that load by adding nodes.

The diagram below shows both groups, and a table for each store follows.

SQL Tables

The tables are normalized and connected by foreign keys. A foreign key is a column that must match a row in another table, so it prevents broken references.

tenants

One row per tenant, the application or organization using the service.

Every other table points to a tenant, which keeps each application's data separate, and the status flag switches a tenant's sending on or off in one update.

users

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

Because the database enforces the tenant_id foreign key, a notification can only point to a user who exists and belongs to the calling tenant.

user_preferences

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

Keeping preferences apart from the profile lets new preference fields arrive without touching user data, and the check before a send becomes a single lookup.

notification_templates

Reusable message templates, written once and used by many notifications.

Each template belongs to one tenant and one channel. An edit is one transaction, so every later send uses the approved text.

scheduled_notifications

Notifications that should go out later, like reminders or announcements at a set time.

The scheduler's query is simple: every row with status scheduled due in the next minute. As each job is handled, status moves to sent or canceled, and the send itself becomes an ordinary record in the notifications collection below.

audit_logs

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

Compliance records must not be lost or silently changed. ACID transactions guarantee that a write either completes fully or not at all, and an audit report filters this table by tenant or user.

NoSQL Collections

The tables above decide what gets sent, and the two collections below record what was sent, which is far more data. A store like MongoDB or Cassandra handles millions of records and a high write rate by adding nodes.

notifications

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

Keeping both message and template_id is not redundant. The text is what was actually sent, so it survives a later template edit, and the ID records which template produced it, so every message from a faulty template can be found later.

tenant_id and user_id are copied into every record because the common queries are "all notifications for this user" and "for this tenant", and each document must carry what those reads need. Some duplication is the price of fast reads and simple scaling.

The in-app inbox reads this collection by user, newest first, so the store keeps it partitioned by user_id and ordered by sent_at. In Cassandra, that is a partition key of user_id with sent_at as a descending clustering column; in MongoDB, it is a compound index on the same two fields. Either way, "the latest 20 for this user" reads one partition in order, without sorting. Records older than the retention window, such as 90 days, can expire through a time-to-live setting rather than a delete job.

notification_status

Tracks the delivery status of a notification, apart from its content: sent, delivered, read, or failed, and when each change happened.

Content is written once, while status changes several times: sent at one moment, delivered at another, read later. Appending each change as a small document is cheap, never rewrites the larger notification record, and avoids several writers updating one document at the same time. The status history can also be queried separately for analytics and debugging.

Dead letter storage. A notification that fails every retry goes to a dead letter queue, a separate store for undeliverable messages, where a person can inspect it. This can be the queue's own mechanism, such as a dedicated Kafka topic or a RabbitMQ dead letter exchange, or a table FailedNotifications(notification_id, user_id, channel, error_code, last_attempt_time) that the operations team reviews. Once the cause is fixed, the messages can be reprocessed, and they remain evidence when a provider misbehaves.