0pt/sql/002_source_registry.sql

-- 0pt Source Registry persistence (Master-DoD §10; BEFORE_STATE.md §1).
--
-- Backs src/services/source-service.ts (0pt.source.schema.v1, the base
-- registry) and src/services/source-registry-service.ts (rights/license/
-- purpose, provenance, health, freshness — the P0 Source Registry surface
-- layered on top). Both previously held their state in bare `new Map()`s,
-- scoped per service *instance* (not even shared within one process) and
-- hard-blocked from production boot by resolveStoreMode
-- (src/platform/dependency-mode.ts) for lack of a DB. This migration is the
-- DB half of closing that gap; src/repositories/* is the code half.
--
-- Deliberately numbered 002, not 006: sql/004_iceberg_write_boundary.sql's
-- opt_source_rate_policies table already carries
-- `source_id TEXT NOT NULL REFERENCES opt_sources (source_id)` — a
-- forward reference to a table that has never existed in any sql/ file
-- (opt_sources was, until this file, only ever a doc-comment aspiration —
-- see the "What Postgres migration would require" section of
-- BEFORE_STATE.md §1 and the retracted `sql/002_p0_dataset_quality.sql`
-- claim in CLAUDE.md/app/api/0pt/sources/[sourceId]/query/route.ts, an
-- UNRELATED fabricated claim about a different, never-written file that
-- happened to reuse this same number). Applying 004 before opt_sources
-- exists would fail outright on a fresh database; this file must run
-- between 001 and 004 to make 004's existing FK resolvable. This is a
-- pre-existing ordering bug in 004, not introduced here — fixed here only
-- because this pass needed opt_sources to exist anyway.

-- ── 0pt.source.schema.v1 (src/types/source.ts) ─────────────────────────
CREATE TABLE IF NOT EXISTS opt_sources (
  source_id TEXT PRIMARY KEY,
  tenant_id TEXT NOT NULL,
  project_id TEXT NOT NULL,
  source_type TEXT NOT NULL,      -- database | file | web | api | stream | manual
  name TEXT NOT NULL,
  adapter_id TEXT,                -- e.g. "fabric_onelake_sql" (src/adapters/fabric-onelake-adapter.ts)
  access TEXT,                    -- api | bulk | authorized_web | licensed (never a bypass mode)
  description TEXT,
  status TEXT NOT NULL DEFAULT 'active',   -- active | paused | archived | deprecated
  correlation_id TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ,
  created_by TEXT
);
CREATE INDEX IF NOT EXISTS opt_sources_tenant_project_idx ON opt_sources (tenant_id, project_id);
CREATE INDEX IF NOT EXISTS opt_sources_type_idx ON opt_sources (source_type);
CREATE INDEX IF NOT EXISTS opt_sources_status_idx ON opt_sources (status);

-- ── Rights / license / purpose metadata (src/types/source-registry.ts) ─
-- One row per source (SourceRegistryService.setRights is an upsert-by-
-- source_id, same as the Map it replaces). No rights row = fail-closed
-- deny in assertPurposeAllowed — that logic lives in the service, not here.
CREATE TABLE IF NOT EXISTS opt_source_rights (
  source_id TEXT PRIMARY KEY REFERENCES opt_sources (source_id),
  tenant_id TEXT NOT NULL,
  license_id TEXT NOT NULL,
  license_url TEXT,
  allowed_purposes TEXT[] NOT NULL DEFAULT '{}',
  pii_allowed BOOLEAN NOT NULL DEFAULT false,
  redistribution TEXT NOT NULL,   -- internal | customer_deliverable | public | prohibited
  attribution TEXT,
  verified_at TEXT NOT NULL,      -- ISO date the assessment was made (date-only in practice; kept as TEXT, not TIMESTAMPTZ, so it round-trips byte-for-byte)
  verified_by TEXT NOT NULL,
  notes TEXT,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS opt_source_rights_tenant_idx ON opt_source_rights (tenant_id);

-- ── Provenance / method metadata (per-SOURCE, not per-field — see
-- BEFORE_STATE.md §3 for the distinction from bridge_provenance) ────────
CREATE TABLE IF NOT EXISTS opt_source_provenance (
  source_id TEXT PRIMARY KEY REFERENCES opt_sources (source_id),
  tenant_id TEXT NOT NULL,
  method TEXT NOT NULL,           -- official_api | bulk_download | authorized_web | licensed_feed | manual_upload
  authority TEXT,
  compliance_evidence_ref TEXT,
  cadence TEXT,
  freshness_sla_hours NUMERIC,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS opt_source_provenance_tenant_idx ON opt_source_provenance (tenant_id);

-- ── Health observations (source.health) — an operational signal, NOT an
-- audit log (AUD0 owns history). Bounded to the latest 100 rows per
-- source_id by the repository layer (src/repositories/postgres-source-
-- registry-repository.ts), mirroring the in-memory implementation's
-- `list.splice(0, list.length - 100)` bound.
CREATE TABLE IF NOT EXISTS opt_source_health_checks (
  health_check_id BIGSERIAL PRIMARY KEY,
  source_id TEXT NOT NULL REFERENCES opt_sources (source_id),
  tenant_id TEXT NOT NULL,
  status TEXT NOT NULL,           -- up | degraded | down | unknown
  checked_at TIMESTAMPTZ NOT NULL,
  latency_ms INT,
  detail TEXT                     -- short operator-facing text; never a raw upstream body
);
CREATE INDEX IF NOT EXISTS opt_source_health_checks_source_idx ON opt_source_health_checks (source_id, checked_at DESC, health_check_id DESC);

-- ── Freshness: last successful observation per source (source.health's
-- "up" checks call recordSuccess; getFreshness reads this + provenance's
-- freshness_sla_hours). One row per source — upserted, not appended.
CREATE TABLE IF NOT EXISTS opt_source_freshness (
  source_id TEXT PRIMARY KEY REFERENCES opt_sources (source_id),
  tenant_id TEXT NOT NULL,
  last_success_at TIMESTAMPTZ NOT NULL
);
CREATE INDEX IF NOT EXISTS opt_source_freshness_tenant_idx ON opt_source_freshness (tenant_id);