`scripts/db/assumable-schema.sql`

58 lines
-- assumable-schema.sql — the assumable-loan ESTIMATE layer for the CRE explorer (local Postgres "cre").
--
-- WHAT THIS IS: FHA & VA mortgages are assumable — a qualified buyer can take over the seller's existing
-- low-rate loan instead of originating a new one at today's rate. That is a live, notable signal:
-- a listing that likely carries a sub-4% assumable FHA/VA note is worth flagging for buyers.
-- The AUTHORITATIVE source for "does this property carry an assumable FHA/VA loan" is the
-- recorded deed of trust in TITLE RECORDS (lender + loan type + recording date). We do NOT have title
-- access yet (see scripts/sources/title-records.js — gated adapter).
--
-- HONEST LABELING (HARD RULE — matches the condo warrantability rule): every row here is a HEURISTIC
-- ESTIMATE, never a title-verified fact. assumable_likelihood is a screening signal only. confidence is
-- ALWAYS 'heuristic — verify in title records' until scripts/sources/title-records.js returns real data,
-- at which point the basis flips to 'title' and confidence becomes 'title-verified'. The heuristic can
-- misfire (a 2021 purchase may have been all-cash or conventional, not FHA/VA) — RECORDED DISSENT, by
-- design: this is a call-prioritization screen, NOT an authoritative assumption finding.

-- Recording-date -> FHA/VA 30yr rate PROXY. Approximate annual-average 30yr fixed rates by program-year,
-- used to infer the rate a loan recorded in year Y likely carries. Rates are APPROXIMATE historical
-- averages (Freddie Mac PMMS-grade ballpark for FHA/VA 30yr) and are LABELLED approximate everywhere.
CREATE TABLE IF NOT EXISTS assumable_rate_proxy (
  rate_year  int PRIMARY KEY,
  est_rate   numeric NOT NULL,       -- approximate avg FHA/VA 30yr fixed rate for loans recorded that year
  note       text                    -- provenance / caveat
);

-- Per-property assumable estimate. One row per condo/sfr id. Recomputed (idempotent UPSERT) by
-- scripts/assumable-heuristic.js. src distinguishes which for-sale table the id lives in.
DO $$ BEGIN
  CREATE TYPE assumable_likelihood AS ENUM ('low', 'medium', 'high');
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

CREATE TABLE IF NOT EXISTS assumable_estimate (
  property_id        text PRIMARY KEY,        -- condo.id or sfr.id
  src                text NOT NULL,           -- 'condo' | 'sfr'
  assumable_likelihood assumable_likelihood NOT NULL,
  est_assumable_rate numeric,                 -- proxy rate inferred from last-sale era (may be NULL)
  est_sale_year      int,                     -- the purchase era used for the rate proxy, when matched
  basis              text NOT NULL,           -- human-readable why ("condo $850k <FHA limit; last sale 2021 -> ~3.1%")
  confidence         text NOT NULL DEFAULT 'heuristic — verify in title records',
  signals            jsonb,                   -- full factor breakdown for transparency on the card
  computed_at        timestamptz DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_assum_src        ON assumable_estimate(src);
CREATE INDEX IF NOT EXISTS idx_assum_likelihood ON assumable_estimate(assumable_likelihood);

-- Card view: assumable estimate joined back to its for-sale property (condo + sfr union), ready for
-- /api/assumable. Carries the address/price/city so the front end can render a badge with no extra join.
CREATE OR REPLACE VIEW assumable_card AS
  SELECT a.property_id, a.src, a.assumable_likelihood, a.est_assumable_rate, a.est_sale_year,
         a.basis, a.confidence, a.signals, a.computed_at,
         p.address, p.city, p.zip, p.price, p.year_built
    FROM assumable_estimate a
    JOIN (
      SELECT id, address, city, zip, price, year_built, 'condo'::text src FROM condo
      UNION ALL
      SELECT id, address, city, zip, price, year_built, 'sfr'::text   src FROM sfr
    ) p ON p.id = a.property_id AND p.src = a.src;