broad-announce/migrations/006_timescale.down.sql

-- 006_timescale.down.sql
-- Reverse M7. Removes the retention policy, then converts
-- the hypertable back to a plain table. The data is
-- preserved; only the chunk metadata is dropped.
--
-- NOTE: Timescale's `detach_database` or `hypertable_to_table`
-- is a no-op in modern Timescale — once a table is a
-- hypertable, you can't truly "downgrade" it without a
-- table swap. We use the documented pattern of creating a
-- plain shadow table, copying data over, and renaming.
-- This is destructive on the timeline dimension but
-- preserves all rows.

-- 1. Remove the retention policy. After this, Timescale
-- will no longer auto-drop chunks.
SELECT remove_retention_policy('deliveries', if_exists => true);

-- 2. Timescale has no clean "downgrade a hypertable"
-- primitive. The recommended approach for a rollback is:
--   a) create a plain shadow table with the same schema
--   b) INSERT ... SELECT all rows
--   c) drop the hypertable, rename the shadow
-- We do that here. Note this is non-atomic and can take
-- a while on large tables.

CREATE TABLE IF NOT EXISTS deliveries_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,
    status          TEXT NOT NULL DEFAULT 'pending',
    attempts        INT NOT NULL DEFAULT 0,
    last_error      TEXT,
    payload         JSONB,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    sent_at         TIMESTAMPTZ,
    next_attempt_at TIMESTAMPTZ
);

-- The hypertable has the same columns, so a plain
-- INSERT ... SELECT is enough. We add the original
-- indexes back too.
CREATE INDEX IF NOT EXISTS idx_deliveries_plain_company_alert
    ON deliveries_plain(company_id, alert_id);
CREATE INDEX IF NOT EXISTS idx_deliveries_plain_status
    ON deliveries_plain(status) WHERE status IN ('pending','failed');

-- Drop the hypertable and rename. This breaks the FK
-- references and recreates the original table layout.
DROP TABLE IF EXISTS deliveries CASCADE;
ALTER TABLE deliveries_plain RENAME TO deliveries;

-- The original 002_deliveries.up.sql had these indexes
-- too; recreate them.
CREATE INDEX IF NOT EXISTS idx_deliveries_company_alert
    ON deliveries(company_id, alert_id);
CREATE INDEX IF NOT EXISTS idx_deliveries_status
    ON deliveries(status) WHERE status IN ('pending','failed');