`scripts/db/sfr-schema.sql`

40 lines
-- 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);