`TK-50-field-provenance.md`

56 lines
# TK-50 — Field-Level Data Provenance ("source every bit of info")

Every parcel ingester that writes to the `parcel` table now stamps **record-level
provenance** on each row and **declares its field→attribute mapping** once per
source. This memo is the coverage record + the (gated) backfill note. It supersedes
the earlier arcgis_sales-only draft: **provenance now covers the entire pipeline.**

## The model (see `db/migrations/012_parcel_provenance.sql`)

One source record (an ArcGIS feature, a joined CSV/Socrata row) → one `parcel`
row, so all of a parcel's fields share the same source. Provenance is therefore:

- On the parcel row: `source_url` (addressable record link), `source_key`
  (ingest source id), `fetched_at` (run start time), `raw_source` (JSONB of the
  raw source attributes we mapped from).
- Once per source, in `source_field_map(source_key, our_field, source_attr, notes)`:
  which raw attribute each of our fields came from — registered at ingest time by
  `registerSourceFieldMap()`.

"Where did field X come from?" resolves to:
`parcel.source_url` + `source_field_map[source_key][X].source_attr`
+ `parcel.raw_source ->> source_attr` (the raw value). Verified by
`src/ingest/parcels/provenance_selftest.ts`.

## Coverage — every ingester that calls the parcel upsert

| source_key | file | sourceUrl strategy | fields mapped | sale? |
|---|---|---|---|---|
| (14 arcgis_sales configs) | `arcgis_sales.ts` | per-record layer `?where=<idField>='<apn>'&f=html` | per-config `fieldMap` | priced deeds |
| `nyc_pluto` | `nyc_pluto.ts` (own upsert path — provenance cols wired into its bespoke `upsertChunk`) | Socrata `64uk-42ks.json?bbl=<bbl>` | 15 (owner, zoning, assessed land/total, derived impr, structure) | none |
| `king_wa` | `king_wa.ts` (4 EXTR extracts joined by PIN) | KingCo `eRealProperty/Detail.aspx?ParcelNbr=<pin>` | 15 (attr + which EXTR file) | REAL priced sales |
| `cook_il` | `cook_il.ts` (4 Socrata datasets joined by PIN) | Socrata `pabr-t5kh.json?pin=<pin>` | 14 (attr + which dataset) | none (assessed values) |
| `franklin_oh` | `franklin_oh.ts` (GDAL `.gdb` extract) | Auditor `Parcel/<parcelid>` | 8 | latest recorded sale |
| `miami_dade` | `miami_dade.ts` | layer `?where=FOLIO='<folio>'&f=html` | 8 (sale = newest of 3 DOS_n/PRICE_n slots) | up to 3 recorded sales |
| `wake_nc` | `wake_nc.ts` | layer `?where=REID='<reid>'&f=html` | 14 (owner, assessed L/B/T, structure, sale) | priced sales |
| `tarrant_tx` | `tarrant_tx.ts` | layer `?where=ACCOUNT='<acct>'&f=html` | 12 (owner, TRUE market value, deed date; no price) | deed date only |
| `bexar_tx` | `bexar_tx.ts` | layer `?where=PropID=<pid>&f=html` | 7 (owner, situs-parsed addr, sqft, use) | none |
| `fulton_ga` | `fulton_ga.ts` | layer `?where=ParcelID='<pid>'&f=html` (space-encoded) | 5 (owner, structured addr, LUCode, units) | none |
| `saltlake_ut` | `saltlake_ut.ts` | layer `?where=PARCEL_ID='<pid>'&f=html` (or SERIAL_NUM) | 9 (MARKET value, structure; derived impr) | none |
| `sandiego_ca` | `sandiego_ca.ts` | layer `?where=apn='<apn>'&f=html` | 14 (assessed value, structure, zoning, deed date) | deed date only |

`nyc_acris.ts` writes to `parcel_event`, not `parcel` — outside this row-provenance scope.

## HARD RAIL: no backfill of existing rows (gated)

Migration 012 is additive; existing rows keep `source_url = NULL` until they are
**re-ingested**. A one-time bulk backfill of the ~5M already-loaded prod rows is a
**separate, Steve-gated prod step** — it is NOT performed here and must not be run
autonomously (it is a prod write to Kamatera-canonical `dw_unified`/`usre`). Going
forward, every fresh ingest / re-ingest carries full provenance automatically.

Verification: `tsc --noEmit` clean; `provenance_selftest.ts` green; bounded real
runs of San Diego (Poway), Wake NC, Bexar TX confirmed all four columns populate
and the field-map resolves a real field to its source attr + raw value (test rows
purged, dev DB left consistent).