← back to Commercialrealestate
CRE: add condo + warrantability schema (fha_condo ref table, condo table w/ broker linkage, warrant_status enum, fha_match fn); load 2,661 FHA rows
848cc19775ed0044144b7565adbde311133ea658 · 2026-06-28 06:17:04 -0700 · Steve Abrams
Files touched
A scripts/db/condo-schema.sqlA scripts/db/load-fha-condos.js
Diff
commit 848cc19775ed0044144b7565adbde311133ea658
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Sun Jun 28 06:17:04 2026 -0700
CRE: add condo + warrantability schema (fha_condo ref table, condo table w/ broker linkage, warrant_status enum, fha_match fn); load 2,661 FHA rows
---
scripts/db/condo-schema.sql | 89 +++++++++++++++++++++++++++++++++++++++++++
scripts/db/load-fha-condos.js | 31 +++++++++++++++
2 files changed, 120 insertions(+)
diff --git a/scripts/db/condo-schema.sql b/scripts/db/condo-schema.sql
new file mode 100644
index 0000000..31c3165
--- /dev/null
+++ b/scripts/db/condo-schema.sql
@@ -0,0 +1,89 @@
+-- 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;
diff --git a/scripts/db/load-fha-condos.js b/scripts/db/load-fha-condos.js
new file mode 100644
index 0000000..e7d95f3
--- /dev/null
+++ b/scripts/db/load-fha-condos.js
@@ -0,0 +1,31 @@
+// load-fha-condos.js — load data/fha-approved-condos.json into the cre DB's fha_condo reference table.
+// Idempotent upsert keyed on (condo_id, submission, project_name). Run after fetch-fha-condos.js.
+'use strict';
+const fs = require('fs');
+const path = require('path');
+const { pool } = require('./brokers-db');
+
+async function main() {
+ const file = path.join(__dirname, '..', '..', 'data', 'fha-approved-condos.json');
+ const { condos } = JSON.parse(fs.readFileSync(file, 'utf8'));
+ let n = 0;
+ for (const c of condos) {
+ await pool.query(
+ `INSERT INTO fha_condo(condo_id, submission, project_name, address, city, zip, county,
+ composition, fha_concentration, approval_method, hud_status, status_date, expiration_date, warrant_signal)
+ VALUES($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14)
+ ON CONFLICT (condo_id, submission, project_name) DO UPDATE SET
+ address=EXCLUDED.address, city=EXCLUDED.city, zip=EXCLUDED.zip,
+ hud_status=EXCLUDED.hud_status, expiration_date=EXCLUDED.expiration_date,
+ warrant_signal=EXCLUDED.warrant_signal, fetched_at=now()`,
+ [c.condo_id, c.submission || '', c.project_name, c.address, c.city, c.zip, c.county,
+ c.composition, c.fha_concentration, c.approval_method, c.status, c.status_date,
+ c.expiration_date, c.warrant_signal]);
+ n++;
+ }
+ const r = await pool.query(`SELECT warrant_signal, count(*) FROM fha_condo GROUP BY warrant_signal ORDER BY 2 DESC`);
+ console.log(`Loaded ${n} fha_condo rows.`);
+ r.rows.forEach(x => console.log(' ', x.warrant_signal, x.count));
+ await pool.end();
+}
+main().catch(e => { console.error('FATAL', e.message); process.exit(1); });
← f67266d CRE: ingest HUD FHA-approved condo list for LA County (2,661
·
back to Commercialrealestate
·
CRE: warrantability classifier — FHA-list match (name + stre b27a82b →