← back to Commercialrealestate
additive: Assumable FHA/VA call segment + read-only /api/assumable endpoint
80c283879911b1eb7d4dd3d16acb5a64d7b82de9 · 2026-06-28 16:34:57 -0700 · Steve Abrams
Files touched
Diff
commit 80c283879911b1eb7d4dd3d16acb5a64d7b82de9
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Sun Jun 28 16:34:57 2026 -0700
additive: Assumable FHA/VA call segment + read-only /api/assumable endpoint
---
scripts/serve.js | 60 ++++++++++++++++++++++++++++++++++++++++++++++++++++++--
1 file changed, 58 insertions(+), 2 deletions(-)
diff --git a/scripts/serve.js b/scripts/serve.js
index 51a62df..2f0d12c 100644
--- a/scripts/serve.js
+++ b/scripts/serve.js
@@ -395,6 +395,31 @@ app.get('/api/closed-sales', async (req, res) => {
} catch (e) { res.status(502).json({ error: String(e.message).split('\n')[0], rows: [] }); }
});
+// Assumable-loan estimates (read-only). HEURISTIC screening signal, NOT title-verified — see
+// scripts/assumable-heuristic.js. ?likelihood=high|medium|low filters; ?limit caps (default 200).
+app.get('/api/assumable', async (req, res) => {
+ if (!brokerdb) return res.status(503).json({ error: 'DB unavailable' });
+ try {
+ const { likelihood } = req.query || {};
+ const limit = Math.min(+(req.query.limit) || 200, 1000);
+ const args = []; let where = '';
+ if (likelihood && ['high', 'medium', 'low'].includes(likelihood)) { args.push(likelihood); where = 'WHERE assumable_likelihood = $1'; }
+ const rows = (await brokerdb.pool.query(
+ `SELECT property_id, src, address, city, zip, price, assumable_likelihood, est_assumable_rate,
+ est_sale_year, basis, confidence, signals
+ FROM assumable_card ${where}
+ ORDER BY CASE assumable_likelihood WHEN 'high' THEN 0 WHEN 'medium' THEN 1 ELSE 2 END, price DESC NULLS LAST
+ LIMIT ${limit}`, args)).rows;
+ const counts = (await brokerdb.pool.query(
+ `SELECT assumable_likelihood s, count(*)::int n FROM assumable_estimate GROUP BY 1`)).rows
+ .reduce((o, r) => (o[r.s] = r.n, o), {});
+ res.json({
+ label: 'HEURISTIC estimate of an assumable FHA/VA loan — screening signal, NOT title-verified. Verify in title records before pitching.',
+ counts, estimates: rows
+ });
+ } catch (e) { res.status(502).json({ error: String(e.message).split('\n')[0] }); }
+});
+
// ── Call Segments — Steve's prospect buckets mapped to Arcstone loan products ──────────────────────
// Each segment = a set of properties + the AGENTS to call, with the matching Arcstone product + pitch
// hook. src 'listing' (commercial, has units/type) | 'condo' (residential, warrantability) | 'sfr'
@@ -406,6 +431,10 @@ const SEGMENTS = {
'dscr-1-4': { label: 'DSCR — 1-4 units', product: 'DSCR investor loan (1-4 units)', hook: 'DSCR — qualify on property cash flow, no income docs, 1-4 units', src: 'listing', where: 'units BETWEEN 1 AND 4' },
'dscr-5-9': { label: 'DSCR — 5-9 units', product: 'DSCR small multifamily (5-9 units)', hook: 'DSCR small multifamily, 5-9 units on cash flow', src: 'listing', where: 'units BETWEEN 5 AND 9' },
'sfr-bankstmt': { label: 'SFR — bank-statement / P&L', product: 'Bank Statement / P&L loan', hook: 'self-employed SFR buyers who need bank-statement or P&L income qualification', src: 'sfr', where: 'TRUE' },
+ // Assumable FHA/VA — agents whose listings LIKELY carry an assumable low-rate FHA/VA loan (heuristic
+ // estimate, NOT title-verified). src 'assumable' joins cre.assumable_estimate (medium|high) back to its
+ // condo/sfr property and onto the listing-agent edge. See scripts/assumable-heuristic.js.
+ 'assumable-fha-va': { label: 'Assumable FHA/VA (estimate)', product: 'Assumption advisory + 2nd-lien / gap financing', hook: 'their listing likely carries an assumable low-rate FHA/VA loan — pitch the assumption (verify in title records)', src: 'assumable', where: "assumable_likelihood IN ('medium','high')" },
'nonqm': { label: 'Non-QM (all)', product: 'Non-QM umbrella', hook: 'any non-QM scenario — non-warrantable condo, DSCR, bank-statement, asset-based', src: 'nonqm', where: null }
};
function segCallList(seg) {
@@ -431,6 +460,20 @@ function segCallList(seg) {
sql: `SELECT b.id, b.name, b.agent_type, b.phone, b.email, count(*)::int n, min(s.address) sample
FROM sfr s JOIN broker_sfr bs ON bs.sfr_id=s.id JOIN broker b ON b.id=bs.broker_id
WHERE ${seg.where} GROUP BY b.id ORDER BY n DESC, b.name LIMIT 300`, args: [] };
+ if (seg.src === 'assumable') return {
+ // assumable estimate -> property -> listing-agent edge (condo OR sfr). Union both edges so a
+ // likely-assumable condo OR sfr surfaces its listing agent. Estimate is heuristic (see hook).
+ sql: `SELECT id, name, agent_type, phone, email, sum(n)::int n, min(sample) sample FROM (
+ SELECT b.id, b.name, b.agent_type, b.phone, b.email, count(*) n, min(c.address) sample
+ FROM assumable_estimate a JOIN condo c ON c.id=a.property_id AND a.src='condo'
+ JOIN broker_condo bc ON bc.condo_id=c.id JOIN broker b ON b.id=bc.broker_id
+ WHERE ${seg.where} GROUP BY b.id
+ UNION ALL
+ SELECT b.id, b.name, b.agent_type, b.phone, b.email, count(*) n, min(s.address) sample
+ FROM assumable_estimate a JOIN sfr s ON s.id=a.property_id AND a.src='sfr'
+ JOIN broker_sfr bs ON bs.sfr_id=s.id JOIN broker b ON b.id=bs.broker_id
+ WHERE ${seg.where} GROUP BY b.id
+ ) u GROUP BY id, name, agent_type, phone, email ORDER BY n DESC LIMIT 300`, args: [] };
return null;
}
app.get('/api/segments', async (req, res) => {
@@ -448,10 +491,20 @@ app.get('/api/segments', async (req, res) => {
} else if (seg.src === 'sfr') {
props = (await brokerdb.pool.query(`SELECT count(*)::int n FROM sfr WHERE ${seg.where}`)).rows[0].n;
agents = (await brokerdb.pool.query(`SELECT count(DISTINCT bs.broker_id)::int n FROM sfr s JOIN broker_sfr bs ON bs.sfr_id=s.id WHERE ${seg.where}`)).rows[0].n;
+ } else if (seg.src === 'assumable') {
+ props = (await brokerdb.pool.query(`SELECT count(*)::int n FROM assumable_estimate WHERE ${seg.where}`)).rows[0].n;
+ agents = (await brokerdb.pool.query(
+ `SELECT count(DISTINCT broker_id)::int n FROM (
+ SELECT bc.broker_id FROM assumable_estimate a JOIN broker_condo bc ON bc.condo_id=a.property_id AND a.src='condo' WHERE ${seg.where}
+ UNION SELECT bs.broker_id FROM assumable_estimate a JOIN broker_sfr bs ON bs.sfr_id=a.property_id AND a.src='sfr' WHERE ${seg.where}
+ ) u`)).rows[0].n;
} else if (seg.src === 'nonqm') {
props = (await brokerdb.pool.query(`SELECT (SELECT count(*) FROM condo WHERE warrantable_status<>'fha_approved') + (SELECT count(*) FROM listing WHERE units BETWEEN 1 AND 9 OR type ILIKE '%mixed%') n`)).rows[0].n;
}
- out.push({ key, label: seg.label, product: seg.product, hook: seg.hook, props, agents, note: seg.src === 'sfr' && props === 0 ? 'SFR listings scraped, but no public listing agents captured yet.' : null });
+ const noAgents = (seg.src === 'sfr' || seg.src === 'assumable') && agents === 0;
+ out.push({ key, label: seg.label, product: seg.product, hook: seg.hook, props, agents,
+ note: noAgents ? 'Listings matched, but no public listing agents captured yet (HONEST: agent edge sparse).'
+ : seg.src === 'assumable' ? 'HEURISTIC estimate — likely-assumable FHA/VA, NOT title-verified. Verify in title records before pitching.' : null });
}
res.json({ segments: out });
} catch (e) { res.status(502).json({ error: String(e.message).split('\n')[0], segments: [] }); }
@@ -463,7 +516,10 @@ app.get('/api/segment', async (req, res) => {
try {
const cl = segCallList(seg);
const callList = cl ? (await brokerdb.pool.query(cl.sql, cl.args)).rows : [];
- res.json({ key: req.query.key, label: seg.label, product: seg.product, hook: seg.hook, note: seg.src === 'sfr' && callList.length === 0 ? 'SFR listings scraped, but no public listing agents captured yet.' : null, callList });
+ const note = (seg.src === 'sfr' || seg.src === 'assumable') && callList.length === 0
+ ? 'Listings matched, but no public listing agents captured yet.'
+ : seg.src === 'assumable' ? 'HEURISTIC estimate — likely-assumable FHA/VA, NOT title-verified. Verify in title records before pitching.' : null;
+ res.json({ key: req.query.key, label: seg.label, product: seg.product, hook: seg.hook, note, callList });
} catch (e) { res.status(502).json({ error: String(e.message).split('\n')[0] }); }
});
← 5f05fc1 assumable-loan heuristic + estimate model + credentialed sou
·
back to Commercialrealestate
·
CRCP: building prices on Call Segments (med + p10–p90 band) b0cabb2 →