Design an On-Call Escalation System (like PagerDuty / OpsGenie)

-- Authoritative alert and incident state
CREATE TABLE alerts (
    alert_id              UUID PRIMARY KEY,
    source                VARCHAR(64) NOT NULL,
    latest_source_event_id VARCHAR(256),
    source_state          VARCHAR(16) NOT NULL DEFAULT 'firing', -- firing or resolved
    last_source_event_at  TIMESTAMP WITH TIME ZONE,
    dedup_key             VARCHAR(256) NOT NULL,
    incident_correlation_key VARCHAR(256),
    incident_owner        BOOLEAN NOT NULL DEFAULT TRUE,
    alert_name            VARCHAR(256) NOT NULL,
    severity              VARCHAR(20) NOT NULL,
    priority              VARCHAR(4) NOT NULL,
    service               VARCHAR(128) NOT NULL,
    labels                JSONB,
    description           TEXT,
    status                VARCHAR(32) NOT NULL DEFAULT 'triggered', -- triggered, notified_primary, escalated_secondary, escalated_manager, escalated_vp, acknowledged, snoozed, resolved
    escalation_level      INT NOT NULL DEFAULT 0,
    team_id               UUID NOT NULL,
    policy_id             UUID,
    policy_version        BIGINT,
    occurrence_count      BIGINT NOT NULL DEFAULT 1,
    last_seen_at          TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    acknowledged_by      VARCHAR(256),
    acknowledged_at      TIMESTAMP WITH TIME ZONE,
    resolved_by           VARCHAR(256),
    resolved_at           TIMESTAMP WITH TIME ZONE,
    snoozed_until         TIMESTAMP WITH TIME ZONE,
    next_action_at        TIMESTAMP WITH TIME ZONE,
    version               BIGINT NOT NULL DEFAULT 0,
    created_at            TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at            TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- The database is authoritative for active incident deduplication by team, fingerprint, and priority.
-- Redis is an advisory accelerator and may be rebuilt after cache loss.
CREATE UNIQUE INDEX idx_alerts_active_dedup
    ON alerts(team_id, dedup_key, priority)
    WHERE status <> 'resolved';

-- Related alerts can be correlated into one incident for paging while each source alert remains auditable.
-- The canonical owner alert carries the escalation state and next action for that correlated incident.
CREATE UNIQUE INDEX idx_alerts_active_incident_owner
    ON alerts(team_id, incident_correlation_key)
    WHERE status <> 'resolved' AND incident_owner = TRUE AND incident_correlation_key IS NOT NULL;

CREATE INDEX idx_alerts_active_team
    ON alerts(team_id, status, updated_at)
    WHERE status <> 'resolved';

CREATE INDEX idx_alerts_next_action
    ON alerts(next_action_at)
    WHERE next_action_at IS NOT NULL AND status NOT IN ('acknowledged', 'resolved');

CREATE INDEX idx_alerts_status
    ON alerts(status);

-- Immutable state transition history for incident timelines and MTTA / MTTR reconstruction.
CREATE TABLE alert_events (
    event_id              UUID PRIMARY KEY,
    alert_id              UUID NOT NULL REFERENCES alerts(alert_id),
    source                 VARCHAR(64),
    source_event_id       VARCHAR(256),
    event_type            VARCHAR(32) NOT NULL,
    actor_id              VARCHAR(256),
    channel               VARCHAR(32),
    occurred_at           TIMESTAMP WITH TIME ZONE,
    state_version         BIGINT,
    payload               JSONB,
    created_at             TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE UNIQUE INDEX idx_alert_events_source_event
    ON alert_events(source, source_event_id)
    WHERE source IS NOT NULL AND source_event_id IS NOT NULL;

CREATE INDEX idx_alert_events_alert_time
    ON alert_events(alert_id, created_at);

-- Provider callbacks are normalized into notification-events so analytics can measure dispatch, delivery, and failure independently of alert ingestion.

-- Durable notification state and provider reconciliation data.
CREATE TABLE notification_attempts (
    attempt_id            UUID PRIMARY KEY,
    alert_id              UUID NOT NULL REFERENCES alerts(alert_id),
    escalation_level      INT NOT NULL,
    recipient_user_id     VARCHAR(128) NOT NULL,
    channel               VARCHAR(32) NOT NULL,
    idempotency_key       VARCHAR(256) NOT NULL UNIQUE,
    provider              VARCHAR(64),
    provider_message_id   VARCHAR(256),
    status                VARCHAR(32) NOT NULL,
    attempt_count         INT NOT NULL DEFAULT 1,
    next_retry_at         TIMESTAMP WITH TIME ZONE,
    sent_at               TIMESTAMP WITH TIME ZONE,
    delivered_at          TIMESTAMP WITH TIME ZONE,
    acknowledged_at       TIMESTAMP WITH TIME ZONE,
    created_at            TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_notification_attempts_alert
    ON notification_attempts(alert_id, escalation_level, channel);

CREATE INDEX idx_notification_attempts_provider_message
    ON notification_attempts(provider, provider_message_id)
    WHERE provider_message_id IS NOT NULL;

-- Provider callback handlers update notification_attempts and write the corresponding notification event to the outbox in the same transaction.
-- CDC publishes notification-events asynchronously after the state transition commits.

-- Versioned escalation policies. Active incidents retain the policy version selected when routed.
CREATE TABLE escalation_policies (
    policy_id             UUID PRIMARY KEY,
    team_id               UUID NOT NULL,
    version               BIGINT NOT NULL,
    levels                JSONB NOT NULL, -- [{level: 0, wait_min: 5, channels: ["push", "sms"]}, ...]
    status                VARCHAR(16) NOT NULL DEFAULT 'active',
    created_at            TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE (team_id, version)
);

-- Base recurring schedule definition.
CREATE TABLE on_call_schedules (
    schedule_id           UUID PRIMARY KEY,
    team_id               UUID NOT NULL,
    rotation_type         VARCHAR(20) NOT NULL,
    recurrence_rule       JSONB NOT NULL,
    participants          JSONB NOT NULL,
    timezone              VARCHAR(64) DEFAULT 'UTC',
    schedule_version      BIGINT NOT NULL DEFAULT 1,
    updated_at            TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Materialized time bounded assignments support indexed point in time schedule lookup.
CREATE TABLE schedule_assignments (
    assignment_id         UUID PRIMARY KEY,
    schedule_id           UUID NOT NULL REFERENCES on_call_schedules(schedule_id),
    team_id               UUID NOT NULL,
    user_id               VARCHAR(128) NOT NULL,
    layer                 INT NOT NULL,
    priority              INT NOT NULL,
    start_time            TIMESTAMP WITH TIME ZONE NOT NULL,
    end_time              TIMESTAMP WITH TIME ZONE NOT NULL,
    schedule_version      BIGINT NOT NULL,
    CHECK (end_time > start_time)
);

CREATE INDEX idx_schedule_assignments_lookup
    ON schedule_assignments(team_id, layer, priority, start_time DESC);

-- Schedule overrides define temporary coverage changes. Overlap validation is serialized per schedule so concurrent writes cannot create ambiguous active overrides.
CREATE TABLE schedule_overrides (
    override_id           UUID PRIMARY KEY,
    schedule_id           UUID NOT NULL REFERENCES on_call_schedules(schedule_id),
    target_user_id        VARCHAR(128) NOT NULL,
    covering_user_id      VARCHAR(128) NOT NULL,
    priority              INT NOT NULL DEFAULT 100,
    reason                TEXT NOT NULL,
    start_time            TIMESTAMP WITH TIME ZONE NOT NULL,
    end_time              TIMESTAMP WITH TIME ZONE NOT NULL,
    created_by            VARCHAR(128) NOT NULL,
    created_at            TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    CHECK (end_time > start_time),
    CHECK (target_user_id <> covering_user_id)
);

CREATE INDEX idx_schedule_overrides_lookup
    ON schedule_overrides(schedule_id, start_time, end_time);

-- The scheduling service validates overlapping windows inside a serializable transaction or per-schedule lock.

-- Maintenance silence rules are persisted so ingestion and routing workers share the same suppression policy.
CREATE TABLE silences (
    silence_id            UUID PRIMARY KEY,
    team_id               UUID NOT NULL,
    matchers              JSONB NOT NULL,
    version               BIGINT NOT NULL DEFAULT 1,
    starts_at             TIMESTAMP WITH TIME ZONE NOT NULL,
    ends_at               TIMESTAMP WITH TIME ZONE NOT NULL,
    created_by            VARCHAR(128) NOT NULL,
    comment               TEXT,
    created_at            TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    CHECK (ends_at > starts_at)
);

CREATE INDEX idx_silences_active_window
    ON silences(team_id, starts_at, ends_at);

-- Transactional outbox. Alert state and this row commit atomically. CDC publishes the row asynchronously.
CREATE TABLE outbox_events (
    event_id              UUID PRIMARY KEY,
    aggregate_type        VARCHAR(64) NOT NULL,
    aggregate_id          UUID NOT NULL,
    topic                 VARCHAR(128) NOT NULL,
    partition_key         VARCHAR(256) NOT NULL,
    payload               JSONB NOT NULL,
    created_at            TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_outbox_created
    ON outbox_events(created_at);