`db/migrations/015_source_ingest_run.sql`

31 lines
-- TK-10155 (contrarian fix 1): per-source ingest-MODE ledger.
--
-- The false-withdrawal poison: some listing sources ingest via an INCREMENTAL / new-day
-- sitemap (e.g. ColdwellBanker's sitemapindex-listings-new-day.xml) that structurally NEVER
-- re-surfaces old-but-still-active listings. On those sources a listing's last_seen freezes
-- and it falsely ages into stale/withdrawn — fabricating withdrawals + snapshots for LIVE
-- properties. The detector must therefore only derive gone-from-feed → stale/withdrawn for a
-- source that has actually been FULLY RE-SCANNED after the listing's last_seen.
--
-- This table records, once per ingest run, whether that run was a 'full' source rescan or an
-- 'incremental' new-day pull, plus when it ran and how many listings it touched. classifyState
-- reads MAX(started_at) over mode='full' runs for the source as the "last full rescan"
-- reference; a listing whose last_seen predates that reference (and wasn't seen) is genuinely
-- gone. Incremental-only sources never satisfy that test → never falsely withdraw.
--
-- Additive/idempotent only (CREATE ... IF NOT EXISTS); mutates no existing data. migrate.ts
-- wraps each file in a transaction, so no BEGIN/COMMIT here.

CREATE TABLE IF NOT EXISTS source_ingest_run (
  id            BIGSERIAL PRIMARY KEY,
  source        TEXT NOT NULL,
  mode          TEXT NOT NULL CHECK (mode IN ('full','incremental')),
  started_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  finished_at   TIMESTAMPTZ,
  listing_count INT
);

-- The detector's hot path: newest FULL rescan per source.
CREATE INDEX IF NOT EXISTS idx_sir_source_mode_started
  ON source_ingest_run (source, mode, started_at DESC);