# Scheduled Notification Low-Level Design: Future Delivery, Timezone Handling, and Recurring Schedules

Source: https://www.techinterview.org/post/3233468291/lld-scheduled-notification/
Updated: 2026-04-17 · techinterview.org

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](https://www.techinterview.org/post/3233460279/airbnb-interview-guide-2026-search-systems-trust-and-safety-and-full-stack-engineering/).

Scheduled notification and job alert delivery design is covered in [LinkedIn system design interview preparation](https://www.techinterview.org/post/3233460278/linkedin-interview-guide-2026-social-graph-engineering-feed-ranking-and-professional-network-scale/).

Scheduled notification and financial alert system design is discussed in [Coinbase system design interview guide](https://www.techinterview.org/post/3233459920/coinbase-interview/).