A scheduled notification system delivers messages at a future time specified by the user or application logic — appointment reminders, subscription renewal alerts, drip campaign emails. Core challenges: efficient polling or time-based indexing for due notifications, timezone-correct scheduling, handling missed fire times during downtime, and supporting recurring schedules without drift.
Core Data Model
CREATE TYPE notification_status AS ENUM ('pending','processing','sent','failed','cancelled');
CREATE TYPE notification_channel AS ENUM ('push','email','sms','in_app');
CREATE TABLE ScheduledNotification (
notification_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID NOT NULL,
channel notification_channel NOT NULL,
template_id TEXT NOT NULL,
template_vars JSONB NOT NULL DEFAULT '{}',
send_at_utc TIMESTAMPTZ NOT NULL, -- always stored in UTC
status notification_status NOT NULL DEFAULT 'pending',
-- Recurrence
recurrence_rule TEXT, -- iCal RRULE, e.g. "FREQ=WEEKLY;BYDAY=MO"
recurrence_end TIMESTAMPTZ,
-- Delivery tracking
locked_by TEXT, -- worker ID, for lease-based locking
locked_until TIMESTAMPTZ,
sent_at TIMESTAMPTZ,
failure_reason TEXT,
retry_count INT NOT NULL DEFAULT 0,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
-- Index that the polling worker uses — partial index on pending only
CREATE INDEX idx_notification_due
ON ScheduledNotification (send_at_utc ASC)
WHERE status = 'pending';
-- Track next fire time for recurring notifications separately
CREATE TABLE RecurringSchedule (
schedule_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID NOT NULL,
template_id TEXT NOT NULL,
template_vars JSONB NOT NULL DEFAULT '{}',
channel notification_channel NOT NULL,
rrule TEXT NOT NULL, -- FREQ=DAILY;BYHOUR=9;BYMINUTE=0
user_timezone TEXT NOT NULL, -- IANA tz, e.g. "America/New_York"
next_fire_at TIMESTAMPTZ NOT NULL,
is_active BOOLEAN NOT NULL DEFAULT TRUE,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_recurring_next ON RecurringSchedule (next_fire_at) WHERE is_active = TRUE;
Scheduling a Notification
from datetime import datetime
from zoneinfo import ZoneInfo
from uuid import uuid4
import psycopg2
def schedule_notification(
conn,
user_id: str,
channel: str,
template_id: str,
template_vars: dict,
send_at_local: datetime, # naive or timezone-aware datetime
user_timezone: str = "UTC" # IANA timezone string
) -> str:
"""
Accept local time + timezone, convert to UTC before storing.
This correctly handles DST transitions.
"""
if send_at_local.tzinfo is None:
# Localize naive datetime to user's timezone
tz = ZoneInfo(user_timezone)
send_at_aware = send_at_local.replace(tzinfo=tz)
else:
send_at_aware = send_at_local
# Convert to UTC for storage
send_at_utc = send_at_aware.astimezone(ZoneInfo("UTC"))
notification_id = str(uuid4())
with conn.cursor() as cur:
cur.execute(
"""INSERT INTO ScheduledNotification
(notification_id, user_id, channel, template_id, template_vars, send_at_utc)
VALUES (%s, %s, %s, %s, %s, %s)""",
(notification_id, user_id, channel, template_id,
psycopg2.extras.Json(template_vars), send_at_utc)
)
conn.commit()
return notification_id
Worker: Polling and Lease-Based Locking
import time, socket, logging
from datetime import datetime, timezone, timedelta
WORKER_ID = socket.gethostname()
LOCK_DURATION_SEC = 60 # worker has 60s to process before lock expires
BATCH_SIZE = 100
POLL_INTERVAL_SEC = 5
def run_notification_worker(conn):
"""
Polling worker with lease-based locking:
- Atomically claims a batch of due notifications using SELECT ... FOR UPDATE SKIP LOCKED
- Sets locked_by and locked_until to prevent concurrent workers from picking the same job
- Processes each notification; updates status to 'sent' or 'failed'
"""
while True:
claimed = claim_due_notifications(conn)
for notification in claimed:
process_notification(conn, notification)
time.sleep(POLL_INTERVAL_SEC)
def claim_due_notifications(conn) -> list[dict]:
now = datetime.now(timezone.utc)
lock_until = now + timedelta(seconds=LOCK_DURATION_SEC)
with conn.cursor() as cur:
cur.execute("""
UPDATE ScheduledNotification
SET status = 'processing',
locked_by = %s,
locked_until = %s
WHERE notification_id IN (
SELECT notification_id FROM ScheduledNotification
WHERE status = 'pending'
AND send_at_utc = 3 THEN 'failed' ELSE 'pending' END,
retry_count = retry_count + 1,
failure_reason = %s,
locked_by = NULL,
locked_until = NULL,
send_at_utc = CASE WHEN retry_count < 3 THEN NOW() + interval '5 minutes' ELSE send_at_utc END
WHERE notification_id = %s
""", (str(e), nid))
conn.commit()
Recurring Notifications
from dateutil.rrule import rrulestr
from zoneinfo import ZoneInfo
def advance_recurring_schedule(conn, schedule_id: str):
"""
After firing a recurring notification, compute the next fire time
using the RRULE and update next_fire_at.
"""
with conn.cursor() as cur:
cur.execute(
"SELECT rrule, user_timezone, next_fire_at, recurrence_end FROM RecurringSchedule WHERE schedule_id = %s",
(schedule_id,)
)
row = cur.fetchone()
if not row:
return
rrule_str, user_tz_name, last_fire, end_at = row
user_tz = ZoneInfo(user_tz_name)
# Parse RRULE and find next occurrence after last_fire
rule = rrulestr(rrule_str, dtstart=last_fire.astimezone(user_tz))
next_fire_local = rule.after(last_fire.astimezone(user_tz))
if next_fire_local is None or (end_at and next_fire_local > end_at):
# No more occurrences — deactivate
with conn.cursor() as cur:
cur.execute("UPDATE RecurringSchedule SET is_active = FALSE WHERE schedule_id = %s", (schedule_id,))
else:
next_fire_utc = next_fire_local.astimezone(ZoneInfo("UTC"))
with conn.cursor() as cur:
cur.execute(
"UPDATE RecurringSchedule SET next_fire_at = %s WHERE schedule_id = %s",
(next_fire_utc, schedule_id)
)
conn.commit()
Key Interview Points
- Polling vs push: Polling with SELECT … WHERE send_at_utc <= NOW() is simple and reliable. With the partial index on pending rows, this query is fast even with millions of notifications. Alternative: use pg_notify or Redis sorted sets scored by Unix timestamp — ZRANGEBYSCORE key 0 now_ts to pop due notifications without a polling loop. Redis approach reduces DB load but adds infrastructure complexity.
- FOR UPDATE SKIP LOCKED: This is the standard pattern for job queues in Postgres. It atomically locks rows being processed and skips rows already locked by other workers, preventing duplicate processing without a separate lock table. Multiple workers can run in parallel safely.
- Timezone handling: “Send at 9am every Monday” must fire at 9am in the user’s local time — which shifts with DST. Always store in UTC but compute local fire time using the IANA timezone. A naive UTC-offset (+5:30) breaks during DST transitions because offsets change. dateutil.rrule respects DST when given a timezone-aware dtstart.
- Missed fire recovery: If workers were down for 2 hours, there may be 1,000 overdue notifications. The worker picks them up in send_at_utc order (FIFO), which is typically correct. For time-sensitive notifications (appointment reminders), add a staleness check: if send_at_utc < NOW() – interval ‘1 hour’, skip and mark as ‘failed’ with reason ‘expired’. Configure per-template.
- Fan-out at scale: For a campaign sending 10M notifications at 8am, insert all 10M rows and let the worker fleet process them. Scale workers horizontally — each worker claims a batch of 100 with SKIP LOCKED. At 100 workers × 100 batch = 10,000 notifications/sec, 10M notifications take 1,000 seconds (~17 minutes). Pre-insert the batch days in advance and scale the worker fleet for campaign windows.
Scheduled notification and booking reminder system design is discussed in Airbnb system design interview questions.
Scheduled notification and job alert delivery design is covered in LinkedIn system design interview preparation.
Scheduled notification and financial alert system design is discussed in Coinbase system design interview guide.