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

-- 006_timescale.up.sql
-- M7: Convert deliveries to a TimescaleDB hypertable with 7d
-- retention. Hot-path writes still go through the same
-- deliveries.*<channel>.<company> NATS subjects; the
-- deliverers INSERT rows just as before. The data-layer
-- change is invisible to the services.
--
-- SPEC §23 calls for both alerts and deliveries as hypertables,
-- but alerts in our codebase never live in Postgres — they
-- flow through NATS JetStream with a 24h stream retention
-- (see cmd/ingestd + cmd/routerd). The "alerts archive" path
-- would be a separate M7.5 if we ever want to long-term-store
-- alert bodies in ClickHouse. For M7, only deliveries is in
-- scope.
--
-- What this migration does:
--   1. Convert `deliveries` to a hypertable on `created_at`
--      (1d chunks). Timescale will copy data behind the
--      scenes if the table has rows; the existing
--      `idx_deliveries_company_alert` and
--      `idx_deliveries_status` indexes are kept.
--   2. Add a 7d retention policy. Timescale will drop
--      chunks older than 7 days automatically.
--   3. Note: PRIMARY KEY (id) is preserved; Timescale allows
--      a non-time column as PK on a hypertable. The
--      `id BIGSERIAL` is sequence-based, not time-based, so
--      it stays valid across chunk drops.
--
-- Why `created_at` not `sent_at`:
--   - `sent_at` is NULL while a delivery is in-flight. We
--     can't hypertable on a NULLable time column.
--   - `created_at` is the enqueue time, which is when the
--     delivery came into existence. For retention purposes
--     (7d after enqueue), this is the right anchor.
--   - The M7 archiver uses `created_at < now() - 7d` as the
--     "old enough to move" predicate.

-- 1. Create the hypertable. The `migrate_data => true` flag
-- tells Timescale to copy existing rows (the dev DB has
-- ~270 rows from the M5–M6.5 smoke runs).
--
-- Timescale requires the partitioning column to be part
-- of any UNIQUE/PRIMARY KEY constraint. The original PK
-- is `id BIGSERIAL` which is a sequence and never reused
-- across chunks, so we extend it to a composite PK
-- (id, created_at). No application code needs to change
-- because the constraint is still keyed on id for
-- uniqueness purposes (BIGSERIAL is monotonically
-- increasing, so (id, created_at) is in practice the
-- same as id alone).
ALTER TABLE deliveries DROP CONSTRAINT IF EXISTS deliveries_pkey CASCADE;
ALTER TABLE deliveries ADD PRIMARY KEY (id, created_at);

SELECT create_hypertable(
    'deliveries',
    'created_at',
    chunk_time_interval => INTERVAL '1 day',
    migrate_data => true,
    if_not_exists => true
);

-- 2. Add the 7-day retention policy. Timescale drops the
-- whole chunk (not row-by-row) when the chunk's max time
-- is older than the threshold. This is dramatically cheaper
-- than a DELETE on a large table.
SELECT add_retention_policy(
    'deliveries',
    INTERVAL '7 days',
    if_not_exists => true
);

-- 3. Optional: enable compression on older chunks. This
-- is a Timescale 2.x feature. We keep it off in v1 to
-- avoid surprising the operator; can be enabled per-chunk
-- age in M7.5.