`data/schema-ingest.sql`

33 lines
-- Ingest tracking — lets us drive the TMDB pull as resumable work units.
-- Phase A populates this table from TMDB's daily ID exports (no auth).
-- Phase B worker selects WHERE status='queued', fetches /3/{type}/{id},
-- writes details into asim_movies / asim_people / asim_credits, marks done.

CREATE TABLE IF NOT EXISTS asim_ingest_stubs (
  id            BIGSERIAL PRIMARY KEY,
  source        TEXT NOT NULL,      -- 'tmdb-movie' | 'tmdb-tv' | 'tmdb-person' | 'tmdb-collection' | 'tmdb-company' | 'tmdb-network' | 'aicp' | 'cannes' | etc.
  source_id     TEXT NOT NULL,      -- TMDB id as text (or AICP slug, etc.)
  popularity    REAL,               -- pulled from the ID-export file when present
  status        TEXT NOT NULL DEFAULT 'queued', -- 'queued' | 'in_flight' | 'done' | 'failed' | 'skipped_adult' | 'gone'
  attempts      INT NOT NULL DEFAULT 0,
  last_error    TEXT,
  fetched_at    TIMESTAMPTZ,
  enqueued_at   TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE (source, source_id)
);
CREATE INDEX IF NOT EXISTS idx_asim_stubs_status_pop ON asim_ingest_stubs(source, status, popularity DESC NULLS LAST);
CREATE INDEX IF NOT EXISTS idx_asim_stubs_fetched ON asim_ingest_stubs(fetched_at);

CREATE TABLE IF NOT EXISTS asim_ingest_runs (
  id            BIGSERIAL PRIMARY KEY,
  run_kind      TEXT NOT NULL,      -- 'tmdb-id-export-download' | 'tmdb-fetch-batch' | etc.
  source        TEXT,
  started_at    TIMESTAMPTZ DEFAULT NOW(),
  finished_at   TIMESTAMPTZ,
  status        TEXT NOT NULL DEFAULT 'running',
  records_in    BIGINT,
  records_out   BIGINT,
  notes         TEXT
);
CREATE INDEX IF NOT EXISTS idx_asim_runs_kind ON asim_ingest_runs(run_kind, started_at DESC);