`db/migrations/010_parcel_event.sql`

39 lines
-- M-PH1: property historical-event log — the "all historical data" layer.
-- No BEGIN/COMMIT here — migrate.ts wraps each file in a transaction.
--
-- The `parcel` table holds only CURRENT state (one last_sale, current values).
-- parcel_event is the time-series: every recorded deed/sale, assessment year,
-- permit, and ownership change for a parcel, each carrying a source_url that
-- links straight back to the public record it came from. Keyed to a parcel by
-- (county_fips, source_id=APN) so it joins to `parcel` and the property page.

CREATE TABLE parcel_event (
  id           BIGSERIAL PRIMARY KEY,
  county_fips  TEXT NOT NULL,
  source_id    TEXT NOT NULL,          -- APN / BBL / PIN (matches parcel.source_id)
  event_type   TEXT NOT NULL,          -- deed | sale | assessment | permit | ownership
  event_date   DATE,
  amount       NUMERIC,                -- price / assessed value / permit valuation
  doc_type     TEXT,                   -- recorder doc type code
  doc_number   TEXT,                   -- recorder document number (links to ARCC)
  detail       JSONB,                  -- source-specific extra (buyer/seller, use, etc.)
  source       TEXT NOT NULL,          -- ingest source id, e.g. 'sandiego_ca'
  source_url   TEXT,                   -- PUBLIC-RECORD link back to the property/document
  created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (county_fips, source_id, event_type, event_date, doc_number)
);
CREATE INDEX idx_parcel_event_parcel ON parcel_event (county_fips, source_id, event_date DESC);
CREATE INDEX idx_parcel_event_type   ON parcel_event (event_type);

-- Per-parcel public-record deep links (assessor / recorder / GIS), so the
-- property page can always point the user at the authoritative source even
-- when the deep history itself is behind a paid/in-person wall (AB 1785).
CREATE TABLE parcel_links (
  county_fips TEXT NOT NULL,
  source_id   TEXT NOT NULL,
  kind        TEXT NOT NULL,           -- gis | assessor | recorder | tax | permits
  url         TEXT NOT NULL,
  label       TEXT,
  PRIMARY KEY (county_fips, source_id, kind)
);