`scripts/assumable-heuristic.js`

209 lines
// assumable-heuristic.js — estimate P(a for-sale property carries an assumable FHA/VA loan) for every
// condo + SFR in cre, and UPSERT the result into cre.assumable_estimate.
//
// WHY: FHA & VA loans are assumable — a qualified buyer can take over the seller's existing low-rate
// note. A listing likely carrying a sub-4% assumable FHA/VA loan is a notable signal for buyers.
// The AUTHORITATIVE source is the recorded deed of trust in TITLE RECORDS. We do not have
// title access yet, so this is a transparent HEURISTIC screen that plugs title data in the moment the
// adapter (scripts/sources/title-records.js) is credentialed.
//
// HONEST LABELING (HARD): every estimate is a screening signal, confidence 'heuristic — verify in title
// records'. The heuristic CAN misfire (a 2021 sale may have been cash/conventional, not FHA/VA) — it is
// call-prioritization, never an authoritative assumption finding. When the title adapter returns a real
// recorded loan, basis flips to 'title' and confidence to 'title-verified' for that property.
//
// HEURISTIC INPUTS (all from what we already have, $0 local):
//   1. PROPERTY TYPE  — condos + SFR are FHA/VA-eligible; commercial is not. (We only score condo+sfr.)
//   2. PRICE BAND     — FHA/VA borrow within program limits. LA County 2024 FHA ceiling ~$1,149,825; VA
//                       has no cap but jumbo-VA is rare. Above the FHA ceiling, FHA is impossible and a
//                       large VA loan is unlikely -> likelihood drops.
//   3. PURCHASE ERA   — when the property's address matches a closed_sale, the SALE YEAR dates the loan.
//                       2020-2022 purchases sit on sub-4% rates (assumption is most valuable then).
//                       The rate is read from assumable_rate_proxy (APPROXIMATE historical averages).
//
// Usage: node scripts/assumable-heuristic.js            (recompute all; applies schema first)
//        node scripts/assumable-heuristic.js --limit 50 (smoke test)
'use strict';
const fs = require('fs');
const path = require('path');
const { Pool } = require('pg');
const titleRecords = require('./sources/title-records');

const ROOT = path.join(__dirname, '..');
const pool = new Pool({
  host: process.env.CRE_PGHOST || '/tmp', port: +(process.env.CRE_PGPORT || 5432),
  database: process.env.CRE_PGDATABASE || 'cre', user: process.env.CRE_PGUSER || process.env.USER || 'stevestudio2'
});

const FHA_CEILING_LA_2024 = 1149825;   // LA County 2024 FHA one-unit loan limit (the price-band cutoff)

// APPROXIMATE annual-average FHA/VA 30yr fixed rates by recording year. Ballpark historical averages
// (Freddie Mac PMMS-grade), used only to infer the rate a loan recorded that year likely carries.
// Labelled approximate everywhere it surfaces. Pre-2016 collapses into a single ~4.0% bucket.
const RATE_PROXY = [
  [2016, 3.65, 'approx avg 30yr fixed 2016'],
  [2017, 3.99, 'approx avg 30yr fixed 2017'],
  [2018, 4.54, 'approx avg 30yr fixed 2018'],
  [2019, 3.94, 'approx avg 30yr fixed 2019'],
  [2020, 3.11, 'approx avg 30yr fixed 2020 — sub-4 era'],
  [2021, 2.96, 'approx avg 30yr fixed 2021 — record-low era'],
  [2022, 5.34, 'approx avg 30yr fixed 2022 — rates rising'],
  [2023, 6.81, 'approx avg 30yr fixed 2023'],
  [2024, 6.72, 'approx avg 30yr fixed 2024'],
  [2025, 6.80, 'approx avg 30yr fixed 2025 (provisional)'],
  [2026, 6.70, 'approx avg 30yr fixed 2026 (provisional)']
];
// Normalize a street address to a join key: lowercase, strip unit/suite/#, collapse whitespace.
const normAddr = (a) => String(a || '').toLowerCase()
  .replace(/\b(unit|apt|apartment|ste|suite|#)\s*[\w-]+/g, '')
  .replace(/[.,]/g, ' ').replace(/\s+/g, ' ').trim();

async function applySchema() {
  await pool.query(fs.readFileSync(path.join(__dirname, 'db', 'assumable-schema.sql'), 'utf8'));
  // (Re)seed the rate proxy idempotently.
  for (const [yr, rate, note] of RATE_PROXY) {
    await pool.query(`INSERT INTO assumable_rate_proxy(rate_year, est_rate, note) VALUES($1,$2,$3)
      ON CONFLICT(rate_year) DO UPDATE SET est_rate=EXCLUDED.est_rate, note=EXCLUDED.note`, [yr, rate, note]);
  }
}

// Build an address -> most-recent sale-year map from closed_sale (the era-sharpening signal).
async function saleYearByAddress() {
  const r = await pool.query(
    `SELECT address, zip, date_part('year', sold_date)::int yr FROM closed_sale
     WHERE sold_date IS NOT NULL ORDER BY sold_date ASC`);
  const m = new Map();   // key = normAddr|zip  (last write wins -> most recent sale, since ordered ASC)
  for (const row of r.rows) m.set(normAddr(row.address) + '|' + (row.zip || ''), row.yr);
  return m;
}

const rateForYear = (yr) => {
  if (yr == null) return null;
  const hit = RATE_PROXY.find(([y]) => y === yr);
  if (hit) return hit[1];
  return yr < 2016 ? 4.00 : RATE_PROXY[RATE_PROXY.length - 1][1];
};

// The core heuristic. Given a property + an optional matched sale year, produce the estimate object.
function scoreProperty(p, saleYear) {
  const signals = [];
  let score = 0;   // 0..3 informal; mapped to low/medium/high at the end

  // (1) type — condo + SFR are FHA/VA-eligible stock (we only feed those in, so this always contributes).
  signals.push({ factor: 'property_type', value: p.src, note: `${p.src} is FHA/VA-eligible stock` });
  score += 1;

  // (2) price band vs FHA ceiling.
  const price = Number(p.price) || 0;
  if (price > 0 && price <= FHA_CEILING_LA_2024) {
    signals.push({ factor: 'price_band', value: price, note: `<= LA 2024 FHA limit $${FHA_CEILING_LA_2024.toLocaleString()} — FHA/VA-financeable` });
    score += 1;
  } else if (price > FHA_CEILING_LA_2024) {
    signals.push({ factor: 'price_band', value: price, note: `> LA 2024 FHA limit — FHA impossible, jumbo-VA rare` });
    score -= 1;
  } else {
    signals.push({ factor: 'price_band', value: null, note: 'price unknown' });
  }

  // (3) purchase era — only when an address matched a closed_sale. 2020-2022 -> sub-4% loan likely.
  let estRate = null, estSaleYear = null;
  if (saleYear != null) {
    estSaleYear = saleYear;
    estRate = rateForYear(saleYear);
    if (saleYear >= 2020 && saleYear <= 2022) {
      signals.push({ factor: 'purchase_era', value: saleYear, note: `bought ${saleYear} (sub-4% era) — assumable rate ~${estRate}% (approx)` });
      score += 1;
    } else {
      signals.push({ factor: 'purchase_era', value: saleYear, note: `bought ${saleYear} — est rate ~${estRate}% (approx); assumption value lower` });
    }
  } else {
    signals.push({ factor: 'purchase_era', value: null, note: 'no matched recent sale — era unknown' });
  }

  // Map informal score -> likelihood. High requires BOTH a financeable price AND a sub-4% era match.
  const subFourEra = saleYear != null && saleYear >= 2020 && saleYear <= 2022;
  const financeable = price > 0 && price <= FHA_CEILING_LA_2024;
  let likelihood;
  if (financeable && subFourEra) likelihood = 'high';
  else if (financeable && (saleYear == null || score >= 2)) likelihood = 'medium';
  else likelihood = 'low';

  const basisBits = [`${p.src} ${price ? '$' + price.toLocaleString() : 'price?'}`,
    financeable ? '≤FHA limit' : '>FHA limit',
    saleYear != null ? `last sale ${saleYear} → ~${estRate}% (approx)` : 'sale era unknown'];

  return {
    assumable_likelihood: likelihood,
    est_assumable_rate: estRate,
    est_sale_year: estSaleYear,
    basis: basisBits.join('; '),
    confidence: 'heuristic — verify in title records',
    signals: { factors: signals, source: 'heuristic' }
  };
}

(async () => {
  const LIMIT = (() => { const i = process.argv.indexOf('--limit'); return i > -1 ? +process.argv[i + 1] : 0; })();
  await applySchema();

  // The title adapter plugs in here. When credentialed it returns real recorded loans; until then it is
  // {available:false} and we fall through to the heuristic for every property.
  const titleStatus = await titleRecords.status();
  if (titleStatus.available) console.log('title-records adapter ONLINE — verified loans will override the heuristic.');
  else console.log(`title-records adapter offline (${titleStatus.needs}) — heuristic only. Estimates are screening signals.`);

  const saleYears = await saleYearByAddress();
  const props = (await pool.query(
    `SELECT id, address, zip, price, 'condo' src FROM condo
     UNION ALL SELECT id, address, zip, price, 'sfr' src FROM sfr
     ${LIMIT ? 'LIMIT ' + LIMIT : ''}`)).rows;

  let high = 0, med = 0, low = 0, titleVerified = 0;
  for (const p of props) {
    let est;
    // 1) Title records FIRST — authoritative when available.
    const recorded = await titleRecords.getRecordedLoans({ address: p.address, city: p.city, zip: p.zip });
    if (recorded && recorded.available && Array.isArray(recorded.loans)) {
      const fhaVa = recorded.loans.find(l => /fha|va/i.test(l.type || ''));
      if (fhaVa) {
        titleVerified++;
        est = {
          assumable_likelihood: 'high',
          est_assumable_rate: fhaVa.rate ?? rateForYear(new Date(fhaVa.recordedDate).getFullYear()),
          est_sale_year: fhaVa.recordedDate ? new Date(fhaVa.recordedDate).getFullYear() : null,
          basis: `recorded ${fhaVa.type} deed of trust (${fhaVa.lender || 'lender?'}, ${fhaVa.recordedDate || 'date?'})`,
          confidence: 'title-verified',
          signals: { factors: [{ factor: 'title_record', value: fhaVa }], source: 'title' }
        };
      }
    }
    // 2) Fall back to the heuristic.
    if (!est) {
      const yr = saleYears.get(normAddr(p.address) + '|' + (p.zip || ''));
      est = scoreProperty(p, yr);
    }

    if (est.assumable_likelihood === 'high') high++;
    else if (est.assumable_likelihood === 'medium') med++;
    else low++;

    await pool.query(
      `INSERT INTO assumable_estimate(property_id, src, assumable_likelihood, est_assumable_rate,
         est_sale_year, basis, confidence, signals, computed_at)
       VALUES($1,$2,$3,$4,$5,$6,$7,$8,now())
       ON CONFLICT(property_id) DO UPDATE SET
         src=EXCLUDED.src, assumable_likelihood=EXCLUDED.assumable_likelihood,
         est_assumable_rate=EXCLUDED.est_assumable_rate, est_sale_year=EXCLUDED.est_sale_year,
         basis=EXCLUDED.basis, confidence=EXCLUDED.confidence, signals=EXCLUDED.signals,
         computed_at=now()`,
      [p.id, p.src, est.assumable_likelihood, est.est_assumable_rate, est.est_sale_year,
       est.basis, est.confidence, JSON.stringify(est.signals)]);
  }

  console.log(`\nassumable estimate computed for ${props.length} properties`);
  console.log(`  high ${high} | medium ${med} | low ${low} | title-verified ${titleVerified}`);
  console.log('cost: $0 (local Postgres + local heuristic; no paid API).');
  console.log('LABEL: every estimate is "heuristic — verify in title records" until the title adapter is credentialed.');
  await pool.end();
})().catch(e => { console.error('FATAL', e.message); process.exit(1); });