`scripts/db/transaction-parties-schema.sql`

30 lines
-- transaction-parties-schema.sql — the full "who's on the public record of a sale" entity layer.
-- Companies (title / escrow / insurance / lender) + per-property/sale edges. Populated from TITLE
-- RECORDS via scripts/sources/title-records.js once Steve provides the industry login; seeded now with
-- the known major LA County players so the graph/entity list is ready.
CREATE TABLE IF NOT EXISTS transaction_company (
  id          serial PRIMARY KEY,
  name        text NOT NULL,
  norm_name   text,                          -- normalized for matching recorded-doc text
  kind        text NOT NULL,                 -- title | escrow | insurance | lender | other
  underwriter text,                          -- for title agents: the underwriter they write on
  notes       text,
  source      text DEFAULT 'seed',
  created_at  timestamptz DEFAULT now(),
  UNIQUE (name, kind)
);
-- Edge: a company appears on a property's transaction in a role (from a recorded document).
CREATE TABLE IF NOT EXISTS transaction_party (
  id            serial PRIMARY KEY,
  company_id    integer REFERENCES transaction_company(id) ON DELETE CASCADE,
  property_ref  text,                        -- listing/condo/sfr id OR address
  city          text,
  role          text,                        -- title | escrow | title_insurer | lender | seller_agent | buyer_agent
  recorded_date date,
  doc_type      text,                        -- grant_deed | deed_of_trust | policy | ...
  source        text,                        -- 'title-records' adapter, etc. (never fabricated)
  created_at    timestamptz DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_txparty_company ON transaction_party(company_id);
CREATE INDEX IF NOT EXISTS idx_txparty_prop    ON transaction_party(property_ref);