Add cre.sfr table + broker_sfr edge (mirror condo/broker_condo for SFR for-sale)

44fe3fb7c2abfa6959a469b474eebf115aea88ef · 2026-06-28 16:05:36 -0700 · Steve

Files touched

A scripts/db/sfr-schema.sql

Diff

commit 44fe3fb7c2abfa6959a469b474eebf115aea88ef
Author: Steve <steve@designerwallcoverings.com>
Date:   Sun Jun 28 16:05:36 2026 -0700

    Add cre.sfr table + broker_sfr edge (mirror condo/broker_condo for SFR for-sale)
---
 scripts/db/sfr-schema.sql | 39 +++++++++++++++++++++++++++++++++++++++
 1 file changed, 39 insertions(+)

diff --git a/scripts/db/sfr-schema.sql b/scripts/db/sfr-schema.sql
new file mode 100644
index 0000000..06af6fd
--- /dev/null
+++ b/scripts/db/sfr-schema.sql
@@ -0,0 +1,39 @@
+-- sfr-schema.sql — single-family-residence FOR-SALE layer for the CRE explorer (local Postgres "cre").
+-- Mirrors cre.condo but for detached SFR stock (Redfin uipt=1). Feeds the 'sfr-bankstmt' call segment
+-- (self-employed buyers needing bank-statement / P&L income qualification). url is UNIQUE so the
+-- gis-csv sweep upserts idempotently across re-runs / price-banded passes.
+--
+-- HONEST LABELING (same rule as condo): broker_name / firm_name are populated only when the Redfin
+-- detail feed exposes a public listing agent. SFRs with a suppressed/absent agent stay NULL — never
+-- fabricated. broker_sfr is the residential-agent edge, mirroring broker_condo.
+
+CREATE TABLE IF NOT EXISTS sfr (
+  id           text PRIMARY KEY,           -- 'rdf' + MLS# or synthetic addr+zip hash
+  address      text,
+  city         text,
+  zip          text,
+  price        bigint,
+  beds         numeric,
+  baths        numeric,
+  sqft         integer,
+  year_built   integer,
+  listing_text text,                       -- minimal (property type from the feed)
+  firm_id      integer REFERENCES firm(id),
+  firm_name    text,                        -- listing brokerage when public
+  broker_name  text,                        -- lead listing agent when public
+  source       text UNIQUE,                 -- Redfin listing URL (idempotent upsert key)
+  created_at   timestamptz DEFAULT now()
+);
+CREATE INDEX IF NOT EXISTS idx_sfr_zip   ON sfr(zip);
+CREATE INDEX IF NOT EXISTS idx_sfr_city  ON sfr(lower(city));
+CREATE INDEX IF NOT EXISTS idx_sfr_firm  ON sfr(firm_id);
+
+-- The residential edge: an agent listed an SFR. Mirrors broker_condo for cre.sfr.
+CREATE TABLE IF NOT EXISTS broker_sfr (
+  broker_id  integer NOT NULL REFERENCES broker(id) ON DELETE CASCADE,
+  sfr_id     text    NOT NULL REFERENCES sfr(id)    ON DELETE CASCADE,
+  role       text DEFAULT 'listing',       -- listing | co | team
+  PRIMARY KEY (broker_id, sfr_id)
+);
+CREATE INDEX IF NOT EXISTS idx_bs_sfr    ON broker_sfr(sfr_id);
+CREATE INDEX IF NOT EXISTS idx_bs_broker ON broker_sfr(broker_id);

← f51be7b CRCP: Call Segments panel (Arcstone loan products) — mixed-u · back to Commercialrealestate · CRCP: compliant lead-source playbook on SFR/non-QM segments dfbb2c5 →