`db/migrations/013_broker_of_record_history.sql`

50 lines
-- TK-10139: append-only "broker of record history" — every observed change to the
-- broker/firm of record for an address (or parcel) is recorded as a new row. Nothing
-- here mutates existing catalog/listing data; this is a NEW table + supporting indexes.
-- No BEGIN/COMMIT here — migrate.ts wraps each file in a transaction.
--
-- Keyed primarily by a NORMALIZED address_key (lowercased, trimmed, whitespace-collapsed
-- "address, city"), with an OPTIONAL parcel_key ("county_fips:ain") when a stable parcel
-- identity is known. The recorder inserts a new row only when the broker/firm of record
-- DIFFERS from the most-recent observation for that key, so the table is the change log.

CREATE TABLE broker_of_record_history (
  id           BIGSERIAL PRIMARY KEY,
  address_key  TEXT NOT NULL,                      -- normalized "address, city"
  parcel_key   TEXT,                               -- "county_fips:ain" when known
  county_fips  TEXT,
  ain          TEXT,
  address      TEXT,
  city         TEXT,
  state_code   TEXT,
  broker_id    BIGINT REFERENCES broker(id),
  broker_name  TEXT,
  firm_id      BIGINT REFERENCES firm(id),
  firm_name    TEXT,
  source       TEXT,
  source_url   TEXT,
  observed_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- "newest observation per key" reads (drives the last_broker_of_record view + API joins).
CREATE INDEX idx_borh_addrkey_observed ON broker_of_record_history (address_key, observed_at DESC);
CREATE INDEX idx_borh_parcelkey_observed ON broker_of_record_history (parcel_key, observed_at DESC);

-- Newest broker/firm of record per address_key. Kept in sync with
-- db/views/last_broker_of_record.sql (standalone copy for reference).
CREATE OR REPLACE VIEW last_broker_of_record AS
SELECT DISTINCT ON (address_key)
  address_key,
  parcel_key,
  county_fips,
  ain,
  address,
  city,
  broker_id,
  broker_name,
  firm_id,
  firm_name,
  observed_at
FROM broker_of_record_history
ORDER BY address_key, observed_at DESC;