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

-- 008_dlq.down.sql
-- Reverse M8 DLQ additions. Drop the retention policy
-- first, then convert the hypertable back to a plain
-- table via the shadow-table rename pattern (see
-- 006_timescale.down.sql for the rationale).

-- 1. Remove the retention policy.
SELECT remove_retention_policy('deliveries_dlq', if_exists => true);

-- 2. Create a plain shadow table with the original
-- (pre-hypertable) layout: BIGSERIAL PK, no chunks.
CREATE TABLE IF NOT EXISTS deliveries_dlq_plain (
    id                BIGSERIAL PRIMARY KEY,
    alert_id          TEXT NOT NULL,
    company_id        TEXT NOT NULL,
    individual_id     TEXT NOT NULL,
    channel           TEXT NOT NULL,
    target            TEXT NOT NULL,
    original_subject  TEXT NOT NULL,
    attempts          INT NOT NULL,
    last_error        TEXT NOT NULL,
    payload           JSONB,
    discarded         BOOLEAN NOT NULL DEFAULT false,
    discarded_at      TIMESTAMPTZ,
    discarded_by      TEXT,
    created_at        TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- 3. Copy the live data over. Timescale will reject
-- SELECT FROM a hypertable that doesn't have
-- move_data/copy_data logic; here we use a plain
-- SELECT, which works on hypertables (you just lose
-- the chunk-aware planner).
INSERT INTO deliveries_dlq_plain
    (id, alert_id, company_id, individual_id, channel, target,
     original_subject, attempts, last_error, payload,
     discarded, discarded_at, discarded_by, created_at)
SELECT
    id, alert_id, company_id, individual_id, channel, target,
    original_subject, attempts, last_error, payload,
    discarded, discarded_at, discarded_by, created_at
FROM deliveries_dlq
ON CONFLICT (id) DO NOTHING;

-- 4. Drop the hypertable, rename the plain shadow.
DROP TABLE IF EXISTS deliveries_dlq CASCADE;
ALTER TABLE deliveries_dlq_plain RENAME TO deliveries_dlq;

-- 5. Recreate the operator-UI indexes.
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);