broad-announce/migrations/008_dlq.up.sql

-- 008_dlq.up.sql
-- M8: Dead-Letter Queue for failed deliveries.
--
-- Concept:
--   - When a deliverd-* worker exhausts its retry budget
--     (BA_DELIVERD_MAX_ATTEMPTS attempts), it inserts one
--     row here AND marks the live `deliveries` row as
--     status='dlq' for the audit trail.
--   - The row is then visible to operators via admind's
--     DLQ endpoints and the /dlq HTML page; the operator
--     can replay (re-INSERT the original NATS envelope
--     into the same deliveries.<channel>.<company> subject)
--     or discard (set discarded=true, hidden from the
--     default list).
--   - The `original_subject` column captures the NATS
--     subject the row was originally delivered on, so
--     replay doesn't need to re-derive it from the payload.
--
-- Schema choices:
--   - Same shape as `deliveries` plus:
--       * original_subject  TEXT — `deliveries.fcm.<co>` etc.
--       * last_error        TEXT — copy of the last failure
--       * discarded         BOOLEAN — set by operator via
--         POST /v1/dlq/{id}/discard. Default false.
--       * discarded_at      TIMESTAMPTZ NULL.
--   - PK is (id, created_at) so this table is also a
--     Timescale hypertable (see step 2 below). 1d chunks,
--     7d retention matches the live deliveries table.
--   - The DLQ is forensic data: archiverd ships rows
--     older than 7d to ClickHouse `ba_archive.deliveries_dlq_archive`
--     with a 2-year TTL (vs 1y for live deliveries). See
--     PROMPT.md M8 "Loose ends".
--
-- Why a separate table, not just status='dlq' on deliveries?
--   - Operator UI: the DLQ list is a focused view, not a
--     full-text search across all deliveries.
--   - Replay needs the original_subject and a clean payload
--     snapshot. Keeping that in a dedicated table avoids
--     re-deriving it from deliveries.payload (which is
--     the full NATS envelope).
--   - ClickHouse-side, the DLQ gets a longer TTL (2y vs 1y)
--     because DLQ entries are forensic — you want them
--     around longer when triaging a regression.
--
-- Why 7d retention (same as deliveries)?
--   - DLQ rows are infrequent (only terminal failures).
--     7d is enough for the operator to notice + replay in
--     the normal ops loop. After 7d the data lives on in
--     ClickHouse for 2y, which is the long-term home.

CREATE TABLE IF NOT EXISTS deliveries_dlq (
    id                BIGSERIAL,
    alert_id          TEXT NOT NULL,
    company_id        TEXT NOT NULL,
    individual_id     TEXT NOT NULL,
    channel           TEXT NOT NULL,                -- fcm | telegram | sms | email | slack | teams | webhook
    target            TEXT NOT NULL,                -- the fcm_token, telegram_chat_id, phone_e164, …
    original_subject  TEXT NOT NULL,                -- deliveries.fcm.<co>  (for replay)
    attempts          INT NOT NULL,                 -- total attempts before giving up
    last_error        TEXT NOT NULL,                -- last failure reason
    payload           JSONB,                        -- snapshot at enqueue time (forensic)
    discarded         BOOLEAN NOT NULL DEFAULT false,
    discarded_at      TIMESTAMPTZ,
    discarded_by      TEXT,                         -- operator id / token (M8: 'admind-cli' until auth lands)
    created_at        TIMESTAMPTZ NOT NULL DEFAULT now(),  -- time of DLQ insert
    PRIMARY KEY (id, created_at)
);

-- The operator UI is filter-driven: most queries are
-- "show me non-discarded DLQ rows for company X in the
-- last 24h". These two indexes cover that.
CREATE INDEX IF NOT EXISTS idx_dlq_company_created
    ON deliveries_dlq(company_id, created_at DESC)
    WHERE discarded = false;
CREATE INDEX IF NOT EXISTS idx_dlq_alert
    ON deliveries_dlq(alert_id);

-- Convert to a Timescale hypertable on created_at, with
-- 1-day chunks and the same 7-day retention policy as
-- the live `deliveries` table. The composite PK
-- (id, created_at) is already in place above, satisfying
-- Timescale's "partition column must be in UNIQUE/PK".
SELECT create_hypertable(
    'deliveries_dlq',
    'created_at',
    chunk_time_interval => INTERVAL '1 day',
    migrate_data => true,
    if_not_exists => true
);

-- 7-day hot retention in Postgres. The archiver ships
-- rows older than that to ClickHouse before Timescale
-- drops the chunk. M8 ships BA_ARCHIVERD_DLQ_OLDER_THAN_HOURS=168.
SELECT add_retention_policy(
    'deliveries_dlq',
    INTERVAL '7 days',
    if_not_exists => true
);