Notification System: Data Storage and Schema

Step 6: Data Storage and Schema

In this hybrid notification system, SQL tables store structured, relational data (like user info, preferences, and templates) for consistency and ease of management, while NoSQL tables handle high-volume, unstructured event data (notification history and statuses) for scalability. This approach uses the strengths of both: relational databases ensure ACID compliance for critical metadata, and NoSQL databases provide flexible schemas and horizontal scaling to handle millions of notification records efficiently. Below is the schema design with each table's fields, data types, and descriptions in tabular format, along with explanations of design considerations before and after the tables for clarity.

SQL Tables

The SQL tables manage static or slowly changing data such as tenant configurations, user profiles, notification preferences, templates, scheduled jobs, and audit logs. These tables are normalized and use clear relationships (foreign keys) to maintain data integrity. Each table is outlined with its fields:

tenants

Stores tenant (application) details. Each tenant represents a client application or organization using the notification system.

Explanation: The tenants table ensures multi-tenancy support by segregating data per application. It includes basic identification and contact info for each tenant, along with a status flag to control activity. This structured information is well-suited for a relational table since it's relatively static and requires consistency.

users

Stores users who receive notifications. Users are associated with tenants.

Explanation: The users table holds profile and contact information for notification recipients. It relates to the tenants table via tenant_id to ensure each user is tied to the correct tenant (application). Storing users in SQL guarantees referential integrity (e.g., notifications link to valid users) and makes it easy to join with preferences or templates if needed.

user_preferences

Stores per-user notification preferences, such as which types of notifications or channels a user has enabled or disabled.

Explanation: The user_preferences table captures notification settings for each user. By separating preferences from the main user profile, the system can easily extend or modify preference fields without altering core user data. Each user has one row containing various preference flags (for different channels or categories of notifications). In a relational model, this structured approach simplifies queries for preference-controlled sends (for example, checking if a user has opted into email before sending).

notification_templates

Stores reusable message templates for notifications. Templates allow consistent formatting of messages and reuse across multiple notifications.

Explanation: The notification_templates table enables defining message formats once and reusing them. Each template can be specific to a channel and possibly personalized with placeholders. Storing templates in SQL ensures they can be transactionally updated (e.g., updating the content or disabling a template) with versioning if needed. Since templates are relatively static and shared data, an SQL table is appropriate for strong consistency (so that notifications always use the latest approved content for compliance).

scheduled_notifications

Stores notifications that are scheduled for future delivery (e.g., reminders or announcements that should be sent at a later time or on a specific schedule).

Explanation: The scheduled_notifications table holds messages that need to be sent in the future. Each entry ties a user with a template to send at a specific time. Using SQL here allows for reliable scheduling (with transactions to avoid duplicate scheduling or race conditions) and easy querying (e.g., find all notifications to send in the next hour). The status field tracks whether the job is still pending, already sent, or cancelled, and can be updated as the job is processed. When the scheduled time arrives, the system will retrieve these entries and then create actual notification events (which get recorded in the NoSQL notifications history).

audit_logs

Stores logs for compliance and tracking important system events. This includes records of notifications sent, preference changes, template edits, or any administrative actions, to provide an audit trail.

Explanation: The audit_logs table provides a historical record of significant events for compliance auditing and debugging. By storing these in SQL, we ensure they are immutable and safely stored (with ACID guarantees), which is important for compliance requirements. For example, whenever a notification is sent, an entry could be logged here (in addition to the NoSQL history) to satisfy regulatory needs that require easily queryable records of communication. It can also log user preference changes or template changes to track who did what and when. This structured log data can be filtered by tenant or user for audit reports.

NoSQL Tables

The NoSQL collections handle the high-scale data: the actual notifications sent to users and the tracking of their delivery/read status. Using a NoSQL database (like MongoDB or Cassandra) for this part of the system allows it to scale horizontally to millions of records and high write throughput with flexible schema. Each notification event is stored as a document, and any updates to its delivery status are recorded separately.

notifications

(NoSQL collection) Stores the notification history for users - each record represents a notification event (a message sent to a user).

Explanation: The notifications collection holds each notification that has been generated and sent to users. Storing these in a NoSQL database enables handling a massive volume of events. For example, every single notification for every user can be logged without impacting the performance of the transactional system. Each document includes the content and context of the notification, which is useful for building a notification history or inbox feature for users. We include references like template_id to know which template was used (if needed for reconstructing or analyzing messages), and store the actual message content so that the history is preserved even if templates change over time. The channel helps identify how it was delivered (for example, to filter user history by channel). Storing tenant_id and user_id with each record (even though that duplicates relational data) is intentional in NoSQL design to optimize queries by those fields (common access patterns are fetching all notifications for a given user or tenant). This denormalization is acceptable given NoSQL's focus on read performance and scalability. According to a scalable design approach, each notification event should capture all data needed without requiring joins, which is achieved by including message content and context directly in the document.

notification_status

(NoSQL collection) Tracks the delivery status of notifications, separate from the notification content. This is used to monitor whether notifications have been delivered, read, failed, etc., and to update their status over time.

Explanation: The notification_status collection is designed to keep track of the evolving status of each notification. By separating status tracking into its own collection, the system can record multiple status changes over time without rewriting the main notification record. For example, a notification might be sent at time A, delivered at time B, and read by the user at time C - each of these can be a separate entry in notification_status. This separation improves write performance (as status updates are high-frequency and can be appended as new documents) and avoids contention on the main notification record. It also allows querying the status history independently (for analytics or troubleshooting). Each status entry references the notification_id and the user_id for ease of lookup. This design is similar to having a NotificationReceivers/Statuses collection in other systems, which links a notification to its recipient and status. By logging statuses (like read/unread or delivered/failed) separately, we can efficiently track who has seen or received each notification and scale to large numbers of events and users without heavy joins.

• Dead Letter Queue (DLQ) storage: This is for notifications that failed to send after all retry attempts. We can have a separate persistent store or queue for these. A simple approach: a table FailedNotifications(notification_id, user_id, channel, error_code, last_attempt_time) that the ops team can review. Or integrate with a message queue's DLQ feature (e.g., a Kafka topic or RabbitMQ DLQ) where messages go after max retries. These records might be periodically reprocessed (maybe after the underlying issue is resolved) or at least analyzed. They are also useful for debugging issues with providers.

Next: Step 7, which examines each component in turn.