← back to Commercialrealestate
Wire sfr-bankstmt segment to cre.sfr + add fetch-sfr-agents.js (broker_sfr edge)
9f0bd7e3d7fe41c6a9dc0a7bb2a6ad430dc9504b · 2026-06-28 16:11:26 -0700 · Steve
Files touched
A scripts/fetch-sfr-agents.jsM scripts/serve.js
Diff
commit 9f0bd7e3d7fe41c6a9dc0a7bb2a6ad430dc9504b
Author: Steve <steve@designerwallcoverings.com>
Date: Sun Jun 28 16:11:26 2026 -0700
Wire sfr-bankstmt segment to cre.sfr + add fetch-sfr-agents.js (broker_sfr edge)
---
scripts/fetch-sfr-agents.js | 185 ++++++++++++++++++++++++++++++++++++++++++++
scripts/serve.js | 19 +++--
2 files changed, 199 insertions(+), 5 deletions(-)
diff --git a/scripts/fetch-sfr-agents.js b/scripts/fetch-sfr-agents.js
new file mode 100644
index 0000000..f971069
--- /dev/null
+++ b/scripts/fetch-sfr-agents.js
@@ -0,0 +1,185 @@
+// fetch-sfr-agents.js — FEED-FIRST capture of the RESIDENTIAL listing agent + brokerage for every
+// cre.sfr (the Redfin SFR for-sale listings). Exact mirror of fetch-redfin-agents.js (condos), but
+// reads from cre.sfr and links agents via broker_sfr instead of broker_condo.
+//
+// GET /stingray/api/home/details/mainHouseInfoPanelInfo?propertyId=<id>&accessLevel=1
+// -> payload.mainHouseInfo.listingAgents[0]: agentInfo.agentName, brokerName, license,
+// agentPhoneNumber.phoneNumber, brokerPhoneNumber.phoneNumber, agentEmailAddress, brokerEmailAddress
+// (Redfin prefixes JSON with `{}&&`; email/phone present only when MLS exposes them publicly.)
+// listingAgents:[] => Redfin-listed, agent suppressed -> honest "no public agent", NOT fabricated.
+//
+// Agents stored as broker.agent_type='residential' (same population as the condo agents) so a single
+// agent who lists both a condo and an SFR is ONE broker row (ON CONFLICT(name, firm_id)).
+//
+// HARD CAP (Steve-approved): stop at MAX_SESSIONS (30) sessions OR ~$1.50 spend, whichever first.
+// Per-session cost + RUNNING BATCH TOTAL surfaced live. Business contact ONLY. NO send.
+//
+// Usage: NODE_PATH=$HOME/.claude/skills/browserbase/node_modules node scripts/fetch-sfr-agents.js
+// CC_MAX_SESSIONS=30 CC_MAX_COST=1.50 CC_PER_SESSION=40
+'use strict';
+const fs = require('fs');
+const path = require('path');
+const { chromium } = require('playwright-core');
+const Browserbase = require('@browserbasehq/sdk').default;
+const brokerdb = require('./db/brokers-db');
+
+const env = fs.readFileSync(process.env.HOME + '/.claude/skills/browserbase/.env', 'utf8');
+const get = (t, k) => (t.match(new RegExp('^' + k + '=(.*)$', 'm')) || [])[1]?.replace(/['"]/g, '').trim();
+const KEY = get(env, 'BROWSERBASE_API_KEY'), PROJECT = get(env, 'BROWSERBASE_PROJECT_ID');
+
+const SESSION_COST = 0.04;
+const MAX_SESSIONS = +(process.env.CC_MAX_SESSIONS || 30);
+const MAX_COST = +(process.env.CC_MAX_COST || 1.50);
+const PER_SESSION = +(process.env.CC_PER_SESSION || 40);
+const ROOT = path.join(__dirname, '..');
+const OUT = path.join(ROOT, 'data', 'sfr-agents.json');
+
+const strip = s => s.replace(/^[)\]}'&\s]*\{\}&&/, '').replace(/^[)\]}'\s]+/, '');
+const pn = v => (v && typeof v === 'object' ? v.phoneNumber : v) || null;
+
+function extractAgent(payloadText) {
+ let j; try { j = JSON.parse(strip(payloadText)); } catch { return { parseErr: true }; }
+ const mh = (j.payload && j.payload.mainHouseInfo) || (j.mainHouseInfo) || null;
+ if (!mh) return { noPayload: true };
+ const la = Array.isArray(mh.listingAgents) ? mh.listingAgents[0] : null;
+ if (!la) return { suppressed: true };
+ const ai = la.agentInfo || {};
+ const name = (ai.agentName || '').trim();
+ if (!name || ai.isAgentNameBlank) return { suppressed: true };
+ return {
+ name,
+ brokerage: (la.brokerName || '').trim() || null,
+ license: (la.license || '').trim() || null,
+ phone: pn(la.agentPhoneNumber) || pn(la.brokerPhoneNumber) || null,
+ email: (la.agentEmailAddress || la.brokerEmailAddress || '').trim() || null,
+ isRedfinAgent: !!ai.isRedfinAgent
+ };
+}
+
+async function loadTargets() {
+ // Every SFR with a parseable Redfin propertyId, not already linked to an agent (resume-safe reruns).
+ const r = await brokerdb.pool.query(`
+ SELECT s.id, s.source,
+ regexp_replace(s.source, '.*/home/([0-9]+).*', '\\1') AS pid,
+ s.address, s.city
+ FROM sfr s
+ WHERE s.source ~ '/home/[0-9]+'
+ AND NOT EXISTS (SELECT 1 FROM broker_sfr bs WHERE bs.sfr_id = s.id)
+ ORDER BY s.city, s.id`);
+ return r.rows.filter(x => x.pid && x.pid !== x.source);
+}
+
+async function persistAgent(sfr, a, sourceUrl) {
+ const firmId = a.brokerage ? await brokerdb.upsertFirm(a.brokerage) : null;
+ const r = await brokerdb.pool.query(
+ `INSERT INTO broker(name, firm_id, phone, email, source, agent_type, license)
+ VALUES($1,$2,$3,$4,'redfin','residential',$5)
+ ON CONFLICT(name, firm_id) DO UPDATE SET
+ phone=COALESCE(broker.phone, EXCLUDED.phone),
+ email=COALESCE(broker.email, EXCLUDED.email),
+ license=COALESCE(broker.license, EXCLUDED.license),
+ agent_type='residential'
+ RETURNING id`,
+ [a.name, firmId, a.phone, a.email, a.license]);
+ const brokerId = r.rows[0].id;
+
+ await brokerdb.pool.query(
+ `INSERT INTO broker_sfr(broker_id, sfr_id, role) VALUES($1,$2,'listing')
+ ON CONFLICT DO NOTHING`, [brokerId, sfr.id]);
+
+ await brokerdb.pool.query(
+ `UPDATE sfr SET broker_name=$2, firm_name=$3, firm_id=$4 WHERE id=$1`,
+ [sfr.id, a.name, a.brokerage, firmId]);
+
+ const prov = [];
+ if (a.phone) prov.push(['phone', a.phone]);
+ if (a.email) prov.push(['email', a.email]);
+ if (a.license) prov.push(['license', a.license]);
+ for (const [field, value] of prov) {
+ await brokerdb.pool.query(
+ `INSERT INTO broker_field_source(broker_id, field, value, source_url, tier)
+ VALUES($1,$2,$3,$4,'redfin-detail')
+ ON CONFLICT(broker_id, field) DO UPDATE SET value=EXCLUDED.value, source_url=EXCLUDED.source_url, tier=EXCLUDED.tier`,
+ [brokerId, field, value, sourceUrl]).catch(() => {});
+ }
+ return brokerId;
+}
+
+(async () => {
+ const targets = await loadTargets();
+ process.stderr.write(`SFR needing an agent: ${targets.length} (PER_SESSION=${PER_SESSION}, cap ${MAX_SESSIONS} sessions / $${MAX_COST.toFixed(2)})\n`);
+ if (!targets.length) { console.log(JSON.stringify({ note: 'all SFR already have an agent linked', captured: 0 })); await brokerdb.pool.end(); return; }
+
+ const results = [];
+ const summary = { captured: 0, withPhone: 0, withEmail: 0, withLicense: 0, suppressed: 0, errors: 0 };
+ let sessions = 0;
+
+ for (let g = 0; g < targets.length; g += PER_SESSION) {
+ if (sessions >= MAX_SESSIONS) { process.stderr.write(`\n[CAP] ${MAX_SESSIONS}-session cap reached; stopping.\n`); break; }
+ if (sessions * SESSION_COST >= MAX_COST) { process.stderr.write(`\n[CAP] $${MAX_COST.toFixed(2)} cost cap reached; stopping.\n`); break; }
+ const group = targets.slice(g, g + PER_SESSION);
+ let browser, session;
+ sessions++;
+ const running = (sessions * SESSION_COST).toFixed(2);
+ process.stderr.write(`\n[session ${sessions}/${MAX_SESSIONS}] ${group.length} SFR (per-session $${SESSION_COST.toFixed(2)} · running batch total $${running} / cap $${MAX_COST.toFixed(2)})\n`);
+
+ try {
+ const bb = new Browserbase({ apiKey: KEY });
+ session = await bb.sessions.create({ projectId: PROJECT, browserSettings: { solveCaptchas: true, viewport: { width: 1440, height: 1000 } } });
+ browser = await chromium.connectOverCDP(session.connectUrl);
+ const ctx = browser.contexts()[0];
+ const page = ctx.pages()[0] || await ctx.newPage();
+ page.setDefaultTimeout(45000);
+ await page.goto('https://www.redfin.com/city/11203/CA/Los-Angeles/filter/property-type=house', { waitUntil: 'domcontentloaded' }).catch(() => {});
+ await page.waitForTimeout(2000);
+
+ for (const t of group) {
+ try {
+ await page.goto(t.source, { waitUntil: 'domcontentloaded' }).catch(() => {});
+ await page.waitForTimeout(900);
+ const u = `https://www.redfin.com/stingray/api/home/details/mainHouseInfoPanelInfo?propertyId=${t.pid}&accessLevel=1`;
+ const res = await page.evaluate(async (u) => {
+ const r = await fetch(u, { headers: { accept: 'application/json' } });
+ return { status: r.status, text: await r.text() };
+ }, u);
+ if (res.status !== 200 || !res.text) { summary.errors++; results.push({ sfr: t.id, pid: t.pid, status: res.status, error: 'non-200' }); continue; }
+ const a = extractAgent(res.text);
+ if (a.suppressed || a.noPayload || a.parseErr) {
+ summary.suppressed++;
+ results.push({ sfr: t.id, pid: t.pid, agent: null, label: 'no public agent (Redfin-listed / suppressed)' });
+ continue;
+ }
+ await persistAgent(t, a, t.source);
+ summary.captured++;
+ if (a.phone) summary.withPhone++;
+ if (a.email) summary.withEmail++;
+ if (a.license) summary.withLicense++;
+ results.push({ sfr: t.id, pid: t.pid, agent: a.name, brokerage: a.brokerage, phone: !!a.phone, email: !!a.email });
+ process.stderr.write(` ${t.address}, ${t.city}: ${a.name} / ${a.brokerage || '?'}${a.phone ? ' ☎' : ''}${a.email ? ' ✉' : ''}\n`);
+ await page.waitForTimeout(500);
+ } catch (e) { summary.errors++; results.push({ sfr: t.id, pid: t.pid, error: String(e.message).slice(0, 80) }); }
+ }
+ } catch (e) {
+ process.stderr.write(' session err: ' + e.message.split('\n')[0] + '\n');
+ } finally { if (browser) try { await browser.close(); } catch (_) {} }
+ }
+
+ const actualCost = (sessions * SESSION_COST).toFixed(2);
+ fs.writeFileSync(OUT, JSON.stringify({
+ meta: {
+ source: 'Redfin per-property detail feed (mainHouseInfoPanelInfo), via warmed Browserbase session',
+ endpoint: '/stingray/api/home/details/mainHouseInfoPanelInfo?propertyId=<id>&accessLevel=1',
+ sessions_used: sessions,
+ cost: `~$${actualCost} (${sessions} Browserbase sessions @ $${SESSION_COST.toFixed(2)})`,
+ cap: `HARD CAP ${MAX_SESSIONS} sessions / $${MAX_COST.toFixed(2)}`,
+ label: 'Business-contact only (agent name / brokerage / business phone / business email / license). Suppressed agents honestly labeled, not fabricated.',
+ fetched_at: new Date().toISOString(),
+ summary
+ },
+ results
+ }, null, 2));
+
+ process.stderr.write(`\nSessions ${sessions} · ACTUAL COST ~$${actualCost}\n`);
+ console.log(JSON.stringify({ ...summary, sessions, cost: '$' + actualCost, endpoint: 'mainHouseInfoPanelInfo' }));
+ await brokerdb.pool.end();
+})();
diff --git a/scripts/serve.js b/scripts/serve.js
index 281532d..51a62df 100644
--- a/scripts/serve.js
+++ b/scripts/serve.js
@@ -228,11 +228,13 @@ app.get('/api/crcp/stats', async (req, res) => {
(SELECT count(*) FROM broker WHERE agent_type='commercial')::int agents_commercial,
(SELECT count(*) FROM broker WHERE agent_type='residential' AND phone IS NOT NULL)::int agents_res_phone,
(SELECT count(*) FROM broker WHERE agent_type='residential' AND email IS NOT NULL)::int agents_res_email,
- (SELECT count(DISTINCT condo_id) FROM broker_condo)::int condos_with_agent`).catch(() => [{}]);
+ (SELECT count(DISTINCT condo_id) FROM broker_condo)::int condos_with_agent,
+ (SELECT count(DISTINCT sfr_id) FROM broker_sfr)::int sfr_with_agent`).catch(() => [{}]);
Object.assign(out, c1);
try { out.brokers_web = (await q(`SELECT count(*)::int n FROM broker WHERE website IS NOT NULL`))[0].n; } catch (_) { out.brokers_web = null; }
try { out.condos = (await q(`SELECT count(*)::int n FROM condo`))[0].n;
out.condosByStatus = {}; (await q(`SELECT warrantable_status s,count(*)::int n FROM condo GROUP BY 1`)).forEach(r => out.condosByStatus[r.s] = r.n); } catch (_) { out.condos = 0; }
+ try { out.sfr = (await q(`SELECT count(*)::int n FROM sfr`))[0].n; } catch (_) { out.sfr = 0; }
const brokerCols = 'b.id, b.name, f.name firm, b.agent_type, ' +
'(count(DISTINCT bl.listing_id) + count(DISTINCT bc.condo_id))::int listings, b.total_assets, b.phone, b.email' +
(out.brokers_web != null ? ', b.website' : '');
@@ -403,7 +405,7 @@ const SEGMENTS = {
'standard-condo': { label: 'Standard condos (FHA-approved)', product: 'Conventional + Fannie Mae CPM check', hook: "offer to run the complex through Fannie Mae Condo Project Manager (CPM) for prior approvals/rejections", src: 'condo', where: "warrantable_status = 'fha_approved'" },
'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: '1=0' },
+ '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' },
'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) {
@@ -425,7 +427,11 @@ function segCallList(seg) {
FROM listing l JOIN broker_listing bl ON bl.listing_id=l.id JOIN broker b ON b.id=bl.broker_id
WHERE l.units BETWEEN 1 AND 9 OR l.type ILIKE '%mixed%' GROUP BY b.id
) u GROUP BY id, name, agent_type, phone, email ORDER BY n DESC LIMIT 300`, args: [] };
- return null; // sfr → no data
+ if (seg.src === 'sfr') return {
+ 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: [] };
+ return null;
}
app.get('/api/segments', async (req, res) => {
if (!brokerdb) return res.json({ segments: [] });
@@ -439,10 +445,13 @@ app.get('/api/segments', async (req, res) => {
} else if (seg.src === 'condo') {
props = (await brokerdb.pool.query(`SELECT count(*)::int n FROM condo WHERE ${seg.where}`)).rows[0].n;
agents = (await brokerdb.pool.query(`SELECT count(DISTINCT bc.broker_id)::int n FROM condo c JOIN broker_condo bc ON bc.condo_id=c.id WHERE ${seg.where}`)).rows[0].n;
+ } 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 === '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' ? 'No SFR listings scraped yet — would need an SFR data pull.' : null });
+ 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 });
}
res.json({ segments: out });
} catch (e) { res.status(502).json({ error: String(e.message).split('\n')[0], segments: [] }); }
@@ -454,7 +463,7 @@ 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' ? 'No SFR listings scraped yet.' : null, callList });
+ 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 });
} catch (e) { res.status(502).json({ error: String(e.message).split('\n')[0] }); }
});
← 9a5c260 Add fetch-sfr-redfin.js — feed-first uipt=1 SFR sweep w/ pri
·
back to Commercialrealestate
·
CRE viewer: neighborhood rent estimate (std + proforma from 5aaf087 →