`scripts/db/broker-of-record-history.sql`

29 lines
-- broker-of-record-history.sql — append-only change-log of the broker/firm of record per address.
--
-- DOCTRINE (docs/SOURCING.md): the listing broker-of-record is a deal's source of truth. Steve wants
-- EVERY update to that fact recorded, keyed by ADDRESS, so we can surface the "Last Broker of Record"
-- and "Last Firm of Record" for any property and see how it changed over time.
--
-- Lives in its OWN file: data/broker-of-record.sqlite (NOT the read-only assessor roll, which serve.js
-- opens with {readonly:true} — this table is written to). Applied by scripts/broker-of-record-recorder.js.
--
-- Append-only: rows are only ever INSERTed. A row is inserted the first time an address is observed, and
-- again ONLY when broker_firm or broker_agent differs from the latest row for that address (an "update").

CREATE TABLE IF NOT EXISTS broker_of_record_history (
  id              INTEGER PRIMARY KEY AUTOINCREMENT,
  address_key     TEXT,   -- normalized: lower(trim(collapse-ws( address + ", " + city )))
  address         TEXT,
  city            TEXT,
  ain             TEXT,   -- assessor parcel number when known (join key to the roll)
  broker_firm     TEXT,   -- firm of record (the brokerage)
  broker_agent    TEXT,   -- broker of record (the listing agent)
  source_of_truth TEXT,   -- firm-direct | broker-of-record | aggregator
  source_host     TEXT,   -- host the observation came from (crexi.com, a firm domain, etc.)
  observed_at     TEXT    -- ISO-8601 timestamp of when this observation was recorded
);

-- Newest-first lookups per address are the hot path (lastBrokerOfRecord + the /api/broker-of-record route).
CREATE INDEX IF NOT EXISTS idx_bor_addr_observed
  ON broker_of_record_history (address_key, observed_at DESC);