CRE: transaction-party entity model — title/escrow/insurance/lender companies + per-property edges (transaction_company + transaction_party), seeded major LA players; populated from title-records adapter once Steve's login lands

50e36fee3ee1c205fdb562340b4ca17991b38416 · 2026-06-28 16:32:39 -0700 · Steve

Files touched

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

Diff

commit 50e36fee3ee1c205fdb562340b4ca17991b38416
Author: Steve <steve@designerwallcoverings.com>
Date:   Sun Jun 28 16:32:39 2026 -0700

    CRE: transaction-party entity model — title/escrow/insurance/lender companies + per-property edges (transaction_company + transaction_party), seeded major LA players; populated from title-records adapter once Steve's login lands
---
 scripts/db/transaction-parties-schema.sql | 29 +++++++++++++++++++++++++++++
 1 file changed, 29 insertions(+)

diff --git a/scripts/db/transaction-parties-schema.sql b/scripts/db/transaction-parties-schema.sql
new file mode 100644
index 0000000..36ce962
--- /dev/null
+++ b/scripts/db/transaction-parties-schema.sql
@@ -0,0 +1,29 @@
+-- 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);

← 5aaf087 CRE viewer: neighborhood rent estimate (std + proforma from · back to Commercialrealestate · assumable-loan heuristic + estimate model + credentialed sou 5f05fc1 →