← back to Commercialrealestate
scripts/fetch-sfr-agents.js
186 lines
// 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();
})();