`scripts/db/condo-schema.sql`

90 lines
-- condo-schema.sql — the condo + warrantability layer for the CRE explorer (local Postgres "cre").
-- Pairs the existing broker/firm/listing graph with LA condo listings and an FHA/VA-approval-based
-- warrantability PROXY (HUD FHA-approved condo list = primary signal; heuristic text flags = secondary).
--
-- HONEST LABELING (hard rule): warrantable_status here is a PROXY derived from public FHA/VA approval
-- + listing-text heuristics. It is NOT a lender-verified Fannie/Freddie condo-questionnaire result.
-- "fha_approved" == financing-eligible on the FHA list, used as a warrantability proxy only.

-- The authoritative FHA-approved-condo reference table (loaded from data/fha-approved-condos.json).
-- This is the lookup the classifier matches a condo's project name / address / zip against.
CREATE TABLE IF NOT EXISTS fha_condo (
  condo_id          text,                 -- HUD condo id (e.g. S009020)
  submission        text,                 -- submission/phase (e.g. 001)
  project_name      text NOT NULL,
  address           text,
  city              text,
  zip               text,
  county            text,
  composition       text,                 -- "ALL 21 UNITS IN PROJECT" etc.
  fha_concentration text,                 -- % FHA-insured loans in the project
  approval_method   text,                 -- HRAP | DELRAP
  hud_status        text,                 -- Approved | Expired | Rejected | Withdrawn
  status_date       text,
  expiration_date   text,
  warrant_signal    text,                 -- fha_approved | fha_expired | fha_other
  fetched_at        timestamptz DEFAULT now(),
  PRIMARY KEY (condo_id, submission, project_name)
);
CREATE INDEX IF NOT EXISTS idx_fha_zip       ON fha_condo(zip);
CREATE INDEX IF NOT EXISTS idx_fha_city      ON fha_condo(lower(city));
CREATE INDEX IF NOT EXISTS idx_fha_project   ON fha_condo(lower(project_name));
CREATE INDEX IF NOT EXISTS idx_fha_signal    ON fha_condo(warrant_signal);

-- Warrantability status enum (PROXY values — see header note).
DO $$ BEGIN
  CREATE TYPE warrant_status AS ENUM ('fha_approved', 'fha_expired', 'not_listed', 'heuristic_flag');
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

-- The condo listing table — actual LA condos-for-sale, linked to the broker graph + classified.
CREATE TABLE IF NOT EXISTS condo (
  id                 text PRIMARY KEY,     -- listing id (crexi/zillow/redfin id, or a synthetic hash)
  address            text,
  city               text,
  zip                text,
  price              bigint,
  hoa                integer,             -- monthly HOA dues, USD
  beds               numeric,
  baths              numeric,
  sqft               integer,
  year_built         integer,
  project_name       text,                -- the HOA / condo-project name when known
  listing_text       text,                -- description blob the heuristic flags are mined from
  warrantable_status warrant_status,      -- result of classify-warrantability.js (PROXY)
  warrant_source     text,                -- "HUD FHA list (S009020 001, exp 08/21/2026)" | "heuristic: cash-only" | ...
  warrant_signals    jsonb,               -- full {status, signals[]} for transparency on the card
  firm_id            integer REFERENCES firm(id),   -- listing brokerage (reuse the broker graph)
  firm_name          text,                -- denormalized convenience
  broker_name        text,                -- lead listing broker when known
  source             text,
  created_at         timestamptz DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_condo_zip     ON condo(zip);
CREATE INDEX IF NOT EXISTS idx_condo_status  ON condo(warrantable_status);
CREATE INDEX IF NOT EXISTS idx_condo_firm    ON condo(firm_id);

-- Convenience view: condo with its listing broker + firm + status, ready for /api/condos.
CREATE OR REPLACE VIEW condo_card AS
  SELECT c.id, c.address, c.city, c.zip, c.price, c.hoa, c.beds, c.baths, c.sqft,
         c.year_built, c.project_name, c.warrantable_status, c.warrant_source, c.warrant_signals,
         c.broker_name, COALESCE(c.firm_name, f.name) AS firm_name, c.source, c.created_at
    FROM condo c
    LEFT JOIN firm f ON f.id = c.firm_id;

-- The match function: given a condo's project name + city + zip, find the best FHA-list match and
-- return its warrant_signal. SQL-side mirror of classify-warrantability.js's FHA-match step so the
-- DB can self-classify on insert if desired. Returns NULL when no list match (caller -> not_listed).
CREATE OR REPLACE FUNCTION fha_match(p_project text, p_address text, p_city text, p_zip text)
RETURNS TABLE (warrant_signal text, source text) AS $$
  -- 1) exact-ish project-name match within the same zip (strongest)
  SELECT f.warrant_signal,
         'HUD FHA list (' || COALESCE(f.condo_id,'?') || ' ' || COALESCE(f.submission,'') ||
           ', ' || f.hud_status || COALESCE(', exp ' || f.expiration_date, '') || ')'
    FROM fha_condo f
   WHERE p_project IS NOT NULL AND length(p_project) > 3
     AND lower(f.project_name) = lower(p_project)
     AND (p_zip IS NULL OR f.zip = p_zip)
   ORDER BY CASE f.warrant_signal WHEN 'fha_approved' THEN 0 ELSE 1 END
   LIMIT 1;
$$ LANGUAGE sql STABLE;