Notification Platform: Preferences, Templates, Delivery and Retries

In one line

A notification platform stores user preferences and quiet hours, templates with versions and locales, notifications and per-channel delivery attempts with provider responses. Sending is asynchronous with retries and backoff, deduplication, suppression lists and rate limits, and scheduled sends use indexed due-time queues.

0/8 · 0%

Think of it like this

A post room. Each resident has delivery preferences ("no post on Sundays", "email instead of letter"), letters use standard templates, every delivery attempt is logged, and failed deliveries are retried a few times before being returned.

Key ideas

01
Preferences: notification_preference(user_id, category, channel, enabled) plus quiet hours and time zone; transactional notifications (OTP, receipts) may bypass marketing preferences but never legal suppression (unsubscribe, bounce lists).
02
Templates: template(id, version, channel, locale, subject, body); notifications reference the version used for audit.
03
Notification and delivery: notification(id, user_id, category, template_version, payload, dedup_key, scheduled_at, status) and delivery_attempt(notification_id, channel, provider, attempt_no, status, provider_message_id, error, at).
04
Deduplication: UNIQUE (dedup_key) such as order-9001-shipped-email, so retried events don't send twice. Rate limiting per user and provider; provider failover (SES → SendGrid).
05
Scheduling: a partial index on (scheduled_at) WHERE status = 'pending', polled with FOR UPDATE SKIP LOCKED; retries with exponential backoff and jitter; after N failures, dead-letter and alert. Provider webhooks (delivered, bounced) update status idempotently.

Code & diagrams

notifications.sqlsql

CREATE TABLE notification (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id bigint NOT NULL, category text NOT NULL, channel text NOT NULL,
  template_id bigint NOT NULL, template_version int NOT NULL,
  payload jsonb NOT NULL,
  dedup_key text NOT NULL UNIQUE,
  scheduled_at timestamptz NOT NULL DEFAULT now(),
  status text NOT NULL DEFAULT 'pending' CHECK (status IN ('pending','sending','sent','failed','suppressed')),
  attempts int NOT NULL DEFAULT 0
);
CREATE INDEX notification_due ON notification (scheduled_at) WHERE status = 'pending';
CREATE INDEX ON notification (user_id, scheduled_at DESC);

-- claim a batch of due notifications
WITH due AS (
  SELECT id FROM notification
  WHERE status = 'pending' AND scheduled_at <= now()
  ORDER BY scheduled_at FOR UPDATE SKIP LOCKED LIMIT 200
)
UPDATE notification n SET status = 'sending', attempts = attempts + 1
FROM due WHERE n.id = due.id RETURNING n.*;

-- on transient failure: back off
UPDATE notification SET status = 'pending',
       scheduled_at = now() + (interval '30 seconds' * power(2, attempts))
WHERE id = 5501 AND attempts < 6;

Interview problem

The problem

Notification service for 100M users

Services emit events (order shipped, OTP, marketing campaigns). Deliver via push, SMS and email respecting preferences, quiet hours, deduplication, provider rate limits and retries; campaigns send 50M messages in 2 hours. Design the data and flow.

When it breaks

Retry storm after a provider outage

What you see

Millions of notifications retry at the same moment when the provider recovers, hitting its rate limit and failing again.

Fix & prevent

Exponential backoff with jitter, per-provider token buckets, and a circuit breaker that pauses sending during outages.

Explain it without notes

Why keep a dedup_key unique constraint in the notification table?

Practice

Design quiet-hours handling for a user in Asia/Kolkata with quiet hours 22:00–08:00.

Trade-offs

↔
Asynchronous delivery with retries and preferences adds latency and state, and gives reliability, compliance and control.

Done when you can

I can design notification preferences, templates, dedup, scheduling, retries and provider limits.

Command Palette