`db/migrations/012_parcel_provenance.sql`

39 lines
-- TK-50: FIELD-LEVEL DATA PROVENANCE — "source every bit of info."
-- No BEGIN/COMMIT here — migrate.ts wraps each file in a transaction.
--
-- One source record (e.g. one ArcGIS feature) → one parcel row, so ALL of a
-- parcel's fields share the same source. Provenance is therefore
--   (record source_url + fetched_at + source_key) + (which source attribute
--    each of our fields mapped from) + the raw record.
--
-- We store the record-level part on the parcel row (source_url/source_key/
-- fetched_at/raw_source) and the per-field attribute mapping ONCE per source
-- in source_field_map (our_field → source_attr). "Where did field X come
-- from?" resolves to:
--   parcel.source_url                       (the addressable record link)
-- + source_field_map[source_key][X].source_attr   (which raw attribute)
-- + parcel.raw_source[source_attr]          (the raw value we mapped from)
--
-- Additive only (ADD COLUMN / CREATE TABLE) — safe + reversible. Existing
-- rows get source_url=NULL until they are re-ingested (backfill is a separate,
-- gated prod step).

ALTER TABLE parcel ADD COLUMN IF NOT EXISTS source_url  TEXT;         -- per-record addressable link back to the exact source record
ALTER TABLE parcel ADD COLUMN IF NOT EXISTS source_key  TEXT;         -- ingest source id, e.g. 'oregon-rlis' — FK-ish into source_field_map
ALTER TABLE parcel ADD COLUMN IF NOT EXISTS fetched_at  TIMESTAMPTZ;  -- when the record was pulled (run start time)
ALTER TABLE parcel ADD COLUMN IF NOT EXISTS raw_source  JSONB;        -- the raw source-record attributes we mapped from

CREATE INDEX IF NOT EXISTS idx_parcel_source_key ON parcel (source_key);

-- Per-source declaration of which raw source attribute each of our fields maps
-- from. One row per (source_key, our_field). Registered at ingest time by
-- registerSourceFieldMap() so provenance is queryable without re-reading code.
CREATE TABLE IF NOT EXISTS source_field_map (
  source_key  TEXT NOT NULL,      -- matches parcel.source_key
  our_field   TEXT NOT NULL,      -- our parcel column, e.g. 'owner_name'
  source_attr TEXT NOT NULL,      -- the raw source attribute, e.g. 'OWNER_NAME'
  notes       TEXT,               -- free-text (e.g. 'grantee/current owner')
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  PRIMARY KEY (source_key, our_field)
);