`db/migrations/009_source_health.sql`

32 lines
-- M-SC1: self-correcting engine — per-job health state.
-- No BEGIN/COMMIT here — migrate.ts wraps each file in a transaction.
--
-- The hourly loop recomputes each job's health from ingest_runs every tick and
-- persists it here so it can (a) back off / quarantine broken sources instead of
-- hammering them, (b) detect a SILENT break (run 'ok' but row count collapsed vs
-- baseline), and (c) de-dupe alerts (only fire on a state TRANSITION, not every
-- tick). This table is the engine's memory between ticks.

CREATE TABLE source_health (
  job                 TEXT PRIMARY KEY,       -- loop job name (listings, brokers, ...)
  state               TEXT NOT NULL DEFAULT 'unknown', -- healthy|degraded|retrying|quarantined|unknown
  consecutive_failures INT NOT NULL DEFAULT 0,
  last_ok_at          TIMESTAMPTZ,
  last_failure_at     TIMESTAMPTZ,
  quarantined_at      TIMESTAMPTZ,
  baseline_upserted   NUMERIC,                -- median rows_upserted of recent OK runs
  last_upserted       NUMERIC,                -- most recent OK run's row count
  alerted_state       TEXT,                   -- last state we alerted on (transition de-dupe)
  updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Append-only self-correction event log (quarantine / recovery / degraded / probe).
CREATE TABLE source_events (
  id        SERIAL PRIMARY KEY,
  job       TEXT NOT NULL,
  event     TEXT NOT NULL,                    -- degraded|quarantined|recovered|probe|retry
  detail    TEXT,
  at        TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_source_events_job ON source_events (job, at DESC);