auto-save: 2026-06-28T07:13:19 (1 files) — scripts/db/broker-enrich-schema.sql

fc5b4d48a651ab668ec7e67d0c30276935fb4fcc · 2026-06-28 07:13:26 -0700 · Steve Abrams

Files touched

A scripts/db/broker-enrich-schema.sql

Diff

commit fc5b4d48a651ab668ec7e67d0c30276935fb4fcc
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Sun Jun 28 07:13:26 2026 -0700

    auto-save: 2026-06-28T07:13:19 (1 files) — scripts/db/broker-enrich-schema.sql
---
 scripts/db/broker-enrich-schema.sql | 37 +++++++++++++++++++++++++++++++++++++
 1 file changed, 37 insertions(+)

diff --git a/scripts/db/broker-enrich-schema.sql b/scripts/db/broker-enrich-schema.sql
new file mode 100644
index 0000000..5369347
--- /dev/null
+++ b/scripts/db/broker-enrich-schema.sql
@@ -0,0 +1,37 @@
+-- broker-enrich-schema.sql — additive columns + provenance + book tables for Job B (broker enrichment).
+-- CCPA posture: business-contact fields only (name/firm/business phone/business email/public website/
+-- LinkedIn). Source URL recorded per filled field for provenance. NO consumer data. NO send.
+
+ALTER TABLE broker ADD COLUMN IF NOT EXISTS website     text;
+ALTER TABLE broker ADD COLUMN IF NOT EXISTS linkedin    text;
+ALTER TABLE broker ADD COLUMN IF NOT EXISTS specialties text;
+ALTER TABLE broker ADD COLUMN IF NOT EXISTS office_addr text;   -- brokerage office address (business)
+ALTER TABLE broker ADD COLUMN IF NOT EXISTS enriched_at timestamptz;
+
+-- Per-field provenance: which source URL gave us each enriched field (audit trail for the CCPA posture).
+CREATE TABLE IF NOT EXISTS broker_field_source (
+  broker_id  integer NOT NULL REFERENCES broker(id) ON DELETE CASCADE,
+  field      text    NOT NULL,            -- phone | email | website | linkedin | specialties | office_addr
+  value      text,
+  source_url text,                        -- where we read it
+  tier       text,                        -- tier1-listing | tier2-firmsite | tier3-search | tier4-browserbase
+  found_at   timestamptz DEFAULT now(),
+  PRIMARY KEY (broker_id, field)
+);
+
+-- Each broker's FULL listing book "anywhere" (beyond our LA set) when a public profile lists it.
+CREATE TABLE IF NOT EXISTS broker_other_listing (
+  broker_id   integer NOT NULL REFERENCES broker(id) ON DELETE CASCADE,
+  ext_id      text    NOT NULL,           -- external listing id / slug
+  title       text,
+  address     text,
+  city        text,
+  state       text,
+  price       bigint,
+  asset_type  text,
+  url         text,
+  source      text,                       -- crexi | firm-site | ...
+  found_at    timestamptz DEFAULT now(),
+  PRIMARY KEY (broker_id, ext_id)
+);
+CREATE INDEX IF NOT EXISTS idx_other_broker ON broker_other_listing(broker_id);

← 8bfec9f CRE Job A: graphics reflect REAL scraped condos — /api/condo · back to Commercialrealestate · CRE Job B: enrichment schema (website/linkedin/specialties/o 8378bf7 →