Database Design

The database is the memory of the notification system. The queue tells workers what to do next, but the database tells the company what happened.

A production schema must answer:

1. Which notification was created?
2. Which channel was attempted?
3. Which template version was used?
4. Did the provider accept it?
5. Did the user opt out?
6. How many times did we retry?
7. Why did it fail?

Core Tables

notifications
notification_templates
notification_preferences
notification_channels
delivery_attempts
provider_accounts
dead_letter_messages
audit_logs

Each table exists for a reason. If you skip attempts, you cannot debug retries. If you skip preferences, you may violate user consent. If you skip audit logs, support cannot explain what happened.

Notifications Table

CREATE TABLE notifications (
  id UUID PRIMARY KEY,
  tenant_id UUID NOT NULL,
  event_id TEXT NOT NULL,
  event_type TEXT NOT NULL,
  user_id UUID NOT NULL,
  template_key TEXT NOT NULL,
  status TEXT NOT NULL,
  priority TEXT NOT NULL DEFAULT 'normal',
  variables JSONB NOT NULL DEFAULT '{}',
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX notifications_dedupe_idx
ON notifications (tenant_id, event_id, user_id, template_key);

The notification row represents intent. It should not be overwritten with every provider retry detail. Keep lifecycle state here, but store delivery attempts elsewhere.

Delivery Attempts

CREATE TABLE delivery_attempts (
  id UUID PRIMARY KEY,
  notification_id UUID NOT NULL REFERENCES notifications(id),
  channel TEXT NOT NULL,
  provider TEXT NOT NULL,
  status TEXT NOT NULL,
  attempt_number INT NOT NULL,
  provider_message_id TEXT,
  error_code TEXT,
  error_message TEXT,
  started_at TIMESTAMPTZ NOT NULL,
  completed_at TIMESTAMPTZ
);

CREATE INDEX delivery_attempts_notification_idx
ON delivery_attempts (notification_id, channel, attempt_number DESC);

This table gives you a timeline. A notification may have one email attempt and three SMS attempts. That is normal during provider instability.

Templates

CREATE TABLE notification_templates (
  id UUID PRIMARY KEY,
  template_key TEXT NOT NULL,
  channel TEXT NOT NULL,
  version INT NOT NULL,
  subject TEXT,
  body TEXT NOT NULL,
  is_active BOOLEAN NOT NULL DEFAULT false,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (template_key, channel, version)
);

Templates must be versioned because notification content is part of production behavior. If a legal invoice template changes, you still need to know what was sent last month.

Preferences

CREATE TABLE notification_preferences (
  user_id UUID NOT NULL,
  category TEXT NOT NULL,
  channel TEXT NOT NULL,
  enabled BOOLEAN NOT NULL DEFAULT true,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (user_id, category, channel)
);

Security messages may ignore marketing opt-outs, but promotional messages must respect them. Store category and channel separately.

Indexes and Retention

Use indexes for real queries:

CREATE INDEX notifications_user_created_idx
ON notifications (user_id, created_at DESC);

CREATE INDEX notifications_status_created_idx
ON notifications (status, created_at);

For large systems, partition by month or tenant. Keep recent data hot and archive old attempts. Delivery attempts grow faster than notification rows because retries multiply data.

Common Mistakes

1. Storing only final status and losing retry history.
2. Not versioning templates.
3. Combining preferences into one JSON blob that cannot be indexed.
4. Missing a dedupe constraint.
5. Keeping audit data forever without compliance review.

Interview Questions

1. Why split notifications and delivery attempts?
2. What unique index prevents duplicate notifications?
3. How would you store user preferences?
4. When would you partition notification tables?

Exercise

Design schema for invoice emails. Include template version, provider response, and a query to fetch all failed invoice notifications from the last 24 hours.