← back to Commercialrealestate
scripts/enrich-condo-brokers.js
316 lines
#!/usr/bin/env node
/*
* enrich-condo-brokers.js — populate per-listing broker + firm + all contact info on the
* Unwarrantable Condos set, then HOOK each broker's DRE# into the full CA DRE license record.
*
* Steve 2026-08-03: "every listing must be accompanied by the broker, firm and all info" +
* "using dre#, always hook into the full list". The DRE# is the join key — Redfin exposes the
* exact license number per listing, so we land a single exact DRE record (no name-match tier).
*
* Pipeline (per fha_expired condo):
* 1) POLITE local fetch of the Redfin listing page (source URL). Realistic Safari UA, jittered
* delay. Extract listingAgents[0] (agent name + brokerName firm + license DRE#) plus
* listingAgentNumber (agent phone) + listingBrokerNumber (office phone) from embedded JSON.
* 2) HOOK the DRE# into the full list: GET pplinfo.asp?License_id=<dre> and parseDetail()
* (reused from fetch-dre-licenses.js) -> status, expiration, responsible broker, dre_url.
* Cached by license (data/dre-bylicense-cache.json), seeded from licensed-agents.json.
* 3) Merge broker fields into data/condos-redfin.json (backup first; ADD-only, keyed by id) and
* write data/condo-brokers.json (resumable cache keyed by condo id).
*
* Blocked Redfin pages (anti-bot / non-200 / no agent block) are recorded in the run summary as
* `blocked` ids. TK-10655: the metered Browserbase fallback is RETIRED — local real Chrome on this
* residential IP clears Redfin's WAF, so --browser / --browserbase both run at $0. Cost: $0 local.
*
* Usage: node scripts/enrich-condo-brokers.js [--limit N] [--delay MS] [--only-missing] [--ids a,b]
*/
'use strict';
const fs = require('fs');
const path = require('path');
const { parseDetail } = require('./fetch-dre-licenses');
const ROOT = path.join(__dirname, '..');
const CONDOS = path.join(ROOT, 'data', 'condos-redfin.json');
const BROKERS = path.join(ROOT, 'data', 'condo-brokers.json');
const DRECACHE = path.join(ROOT, 'data', 'dre-bylicense-cache.json');
const LICENSED = path.join(ROOT, 'data', 'licensed-agents.json');
const DRE_BASE = 'https://www2.dre.ca.gov/PublicASP/pplinfo.asp';
const UA = 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/17.4 Safari/605.1.15';
const arg = (k, d) => { const i = process.argv.indexOf(k); return i > -1 ? (process.argv[i + 1] ?? true) : d; };
const LIMIT = +arg('--limit', 0) || Infinity;
const DELAY = +arg('--delay', 1800);
const ONLY_MISSING = process.argv.includes('--only-missing');
// Buyer-SIDE mode (TK-10243): target SOLD listings (where a buyer's agent can exist) instead of the
// fha_expired set, to capture "both sides" of the deal. Listing side is still refreshed on the pass.
const BUYER_SIDE = process.argv.includes('--buyer-side') || process.argv.includes('--sold');
const USE_BROWSER = process.argv.includes('--browser'); // real Chrome via Playwright ($0)
// --browserbase is RETIRED (TK-10655): the residential IP clears Redfin's WAF locally, so this flag
// now ALSO routes to local Chrome ($0) instead of the old metered cloud session.
const USE_BB = process.argv.includes('--browserbase');
const ONLY_IDS = (arg('--ids', '') || '').split(',').filter(Boolean);
const GLOBAL_MODS = require('child_process').execSync('npm root -g').toString().trim();
const sleep = ms => new Promise(r => setTimeout(r, ms));
const jitter = () => DELAY + Math.floor(Math.random() * 900);
const jload = (p, d) => { try { return JSON.parse(fs.readFileSync(p, 'utf8')); } catch { return d; } };
// ---- Redfin listing-agent extraction (embedded JSON in the page HTML) ----
// Pull every {name, firm, dre} from the (unescaped) listingAgents array. Prefers a real JSON.parse
// of the balanced-bracket array (nesting-safe), falls back to a per-object regex, dedups by dre||name.
function extractAgents(html) { return extractAgentArray(html, 'listingAgents'); }
// Buyer-SIDE agents on a SOLD listing (Redfin key mainHouseInfo.buyingAgents — parallel to
// listingAgents). Often empty ("[]") because Redfin doesn't always publish the buyer's agent; when
// present it carries the same {agentName, brokerName, license} shape. TK-10243.
function extractBuyingAgents(html) { return extractAgentArray(html, 'buyingAgents'); }
function extractAgentArray(html, keyName) {
const key = '"' + keyName + '":';
const ki = html.indexOf(key);
if (ki < 0) return [];
const start = html.indexOf('[', ki);
if (start < 0) return [];
let depth = 0, end = -1;
for (let j = start; j < html.length; j++) {
const ch = html[j];
if (ch === '[') depth++;
else if (ch === ']') { if (--depth === 0) { end = j; break; } }
}
if (end < 0) return [];
const arrText = html.slice(start, end + 1);
const raw = [];
let parsed = null;
try { parsed = JSON.parse(arrText); } catch (e) { parsed = null; }
if (Array.isArray(parsed)) {
for (const a of parsed) {
if (!a || typeof a !== 'object') continue;
const name = ((a.agentInfo && a.agentInfo.agentName) || a.agentName || '').toString().trim();
const firm = (a.brokerName || '').toString().trim();
const licRaw = (a.license || a.breNumber || '').toString().trim();
const dre = /^\d{6,8}$/.test(licRaw) ? licRaw : '';
if (name || dre) raw.push({ name, firm, dre });
}
} else {
const objs = arrText.match(/\{[^{}]*(?:\{[^{}]*\}[^{}]*)*\}/g) || [];
for (const blk of objs) {
const an = blk.match(/"agentName":"([^"]{1,80})"/);
const bn = blk.match(/"brokerName":"([^"]{1,120})"/);
const lic = blk.match(/"(?:license|breNumber)":"(\d{6,8})"/);
const a = {};
if (an && an[1].trim()) a.name = an[1].trim();
if (bn && bn[1].trim()) a.firm = bn[1].trim();
if (lic) a.dre = lic[1];
if (a.name || a.dre) raw.push(a);
}
}
const seen = new Set(), out = [];
for (const a of raw) { const k = a.dre || a.name; if (k && !seen.has(k)) { seen.add(k); out.push(a); } }
return out;
}
function extractBroker(raw) {
const out = {};
// Redfin embeds the listing state as an ESCAPED JSON string (\"listingAgents\":[...]) inside the
// page, so normalize \" -> " before matching. Harmless for our field extraction.
const html = String(raw).replace(/\\"/g, '"');
// Record EVERY license number on the listing (Steve 2026-08-03): the listingAgents array carries
// the primary listing agent AND any co-listing agent, each with its own name/brokerName/license.
// Extract the WHOLE array by balanced brackets, then JSON.parse it (handles arbitrary nesting like
// a contactInfo sub-object — Cody gate); fall back to a per-object regex only if it won't parse.
const agents = extractAgents(html);
if (agents.length) {
out.agents = agents; // ALL license-bearing agents
if (agents[0].name) out.broker_name = agents[0].name; // primary (back-compat)
if (agents[0].firm) out.firm_name = agents[0].firm;
if (agents[0].dre) out.broker_dre = agents[0].dre;
}
// Fallback flat fields near the top of the page state.
if (!out.broker_name) { const m = html.match(/"listingAgentName":"([^"]{1,80})"/); if (m && m[1].trim()) out.broker_name = m[1].trim(); }
const ap = html.match(/"listingAgentNumber":"([0-9()\-\s.]{7,20})"/); if (ap) out.agent_phone = ap[1].trim();
const op = html.match(/"listingBrokerNumber":"([0-9()\-\s.]{7,20})"/); if (op) out.office_phone = op[1].trim();
// BUYER SIDE (sold listings) — the "both sides" data. Captured when Redfin publishes it; blank
// otherwise (active listings have no buyer side yet). TK-10243.
const buyers = extractBuyingAgents(html);
if (buyers.length) {
out.buyer_agents = buyers;
if (buyers[0].name) out.buyer_agent_name = buyers[0].name;
if (buyers[0].firm) out.buyer_firm_name = buyers[0].firm;
if (buyers[0].dre) out.buyer_agent_dre = buyers[0].dre;
}
const bp = html.match(/"buyingAgentNumber":"([0-9()\-\s.]{7,20})"/); if (bp) out.buyer_agent_phone = bp[1].trim();
return out;
}
async function fetchRedfin(url, page) {
if (page) {
try {
const resp = await page.goto(url, { waitUntil: 'domcontentloaded', timeout: 60000 });
let html = await page.content();
// WAF/captcha challenge returns a tiny placeholder; poll up to ~25s while the solver runs.
for (let i = 0; i < 12 && !/listingAgents|brokerName/.test(html); i++) {
await sleep(2200);
html = await page.content();
}
return { status: resp ? resp.status() : 200, html };
} catch (e) { return { status: 0, html: '', err: String(e.message).split('\n')[0] }; }
}
try {
const r = await fetch(url, { headers: { 'User-Agent': UA, 'Accept-Language': 'en-US,en;q=0.9', 'Accept': 'text/html' } });
const html = r.ok ? await r.text() : '';
return { status: r.status, html };
} catch (e) { return { status: 0, html: '', err: String(e.message).split('\n')[0] }; }
}
// ---- DRE# -> full record (hook into the full list) ----
function seedDreCacheFromLicensed(cache) {
const lic = jload(LICENSED, { agents: [] });
for (const a of (lic.agents || [])) {
if (a.license && !cache[a.license]) {
cache[a.license] = {
broker_dre: a.license, dre_url: a.dre_url || `${DRE_BASE}?License_id=${a.license}`,
dre_status: a.status || a.status_label, dre_expiration: a.expiration,
responsible_broker: a.responsible_broker, responsible_broker_id: a.responsible_broker_id,
license_type: a.license_type, license_city: a.license_city, _src: 'licensed-agents.json',
};
}
}
}
async function hookDre(dre, cache) {
if (!dre) return null;
if (cache[dre]) return cache[dre];
await sleep(jitter());
let rec = { broker_dre: dre, dre_url: `${DRE_BASE}?License_id=${dre}` };
try {
const r = await fetch(`${DRE_BASE}?License_id=${encodeURIComponent(dre)}`, { headers: { 'User-Agent': UA } });
if (r.ok) {
const d = parseDetail(await r.text());
rec = { ...rec, dre_status: d.status, dre_expiration: d.expiration, dre_license_type: d.detail_type,
responsible_broker: d.responsible_broker, responsible_broker_id: d.responsible_broker_id,
discipline: d.discipline, dre_name: d.dre_name, _src: 'dre-live' };
}
} catch (e) { rec._err = String(e.message).split('\n')[0]; }
cache[dre] = rec;
fs.writeFileSync(DRECACHE, JSON.stringify(cache, null, 1));
return rec;
}
async function main() {
const doc = jload(CONDOS, { condos: [] });
const exp = doc.condos.filter(c => c.warrantable_status === 'fha_expired');
// SOLD set for buyer-side "both sides" capture — status/market_status says sold/closed, or a sold
// price/date is present. Only these can carry a buyer's agent.
const sold = doc.condos.filter(c => /sold|closed/i.test(c.status || '') || /sold|closed/i.test(c.market_status || '') || c.sold_date || c.sold_price);
const brokers = jload(BROKERS, {});
const dreCache = jload(DRECACHE, {});
seedDreCacheFromLicensed(dreCache);
let targets = BUYER_SIDE ? sold : exp;
const baseSet = targets;
if (ONLY_IDS.length) targets = targets.filter(c => ONLY_IDS.includes(c.id));
// --only-missing means: in buyer-side mode, retry rows with no buyer_agents yet; else no broker yet.
if (ONLY_MISSING) targets = targets.filter(c => BUYER_SIDE
? !(brokers[c.id] && Array.isArray(brokers[c.id].buyer_agents) && brokers[c.id].buyer_agents.length)
: !(brokers[c.id] && brokers[c.id].broker_name));
// Browser context. TK-10655 (prefer-local rule): the paid Browserbase fallback is RETIRED — this
// machine's residential IP clears Redfin's AWS-WAF on both the gis-csv feed AND the listing pages
// (verified 2026-08-18: listingAgents/brokerName extracted, 200, not blocked), so both --browser
// and the legacy --browserbase flag now drive the LOCAL real Chrome at $0. WAF-blocked listings
// still degrade gracefully (recorded as `blocked`, prior data preserved) — never a paid retry.
let browser = null, ctx = null, page = null, startedAt = Date.now();
if (USE_BB || USE_BROWSER) {
const { chromium } = require(GLOBAL_MODS + '/playwright');
browser = await chromium.launch({ channel: 'chrome', headless: true });
ctx = await browser.newContext({ userAgent: UA, locale: 'en-US', viewport: { width: 1280, height: 900 } });
page = await ctx.newPage();
console.log(' mode: local real Chrome (Playwright) · $0' + (USE_BB ? ' [--browserbase retired -> local]' : ''));
}
console.log(`condo-broker enrich${BUYER_SIDE ? ' [BUYER-SIDE/sold]' : ''}: ${targets.length} target(s) of ${baseSet.length} ${BUYER_SIDE ? 'sold' : 'fha_expired'} · delay ~${DELAY}ms · $0 local`);
const blocked = [];
let done = 0, gotBroker = 0, gotDre = 0;
// Abort guardrails (Codex dissent): a hot Redfin WAF polls ~25s per blocked listing, so an
// unbounded run wastes wall-clock. Bail early when the WAF is clearly hot. (Now $0 local — these
// are runtime guards, not cost guards.)
const MAX_CONSEC_BLOCKS = +process.env.MAX_CONSEC_BLOCKS || 8;
const MAX_SESSION_MIN = +process.env.MAX_SESSION_MIN || 12; // wall-clock runtime ceiling
let consecBlocks = 0, aborted = null;
for (const c of targets) {
if (done >= LIMIT) break;
const elapsedMin = (Date.now() - startedAt) / 60000;
if (elapsedMin > MAX_SESSION_MIN) { aborted = `runtime cap ${MAX_SESSION_MIN}min`; break; }
if (consecBlocks >= MAX_CONSEC_BLOCKS) { aborted = `${consecBlocks} consecutive WAF blocks — WAF is hot, stopping`; break; }
done++;
const { status, html } = await fetchRedfin(c.source, page);
let b = html ? extractBroker(html) : {};
if ((status !== 200 && status !== 202) || !b.broker_name) {
// one polite retry
await sleep(jitter());
const r2 = await fetchRedfin(c.source, page);
if (r2.html) { const b2 = extractBroker(r2.html); if (b2.broker_name) b = b2; }
}
if (!b.broker_name && !b.firm_name) { blocked.push(c.id); consecBlocks++; }
else { gotBroker++; consecBlocks = 0; }
let dre = null;
if (b.broker_dre) { dre = await hookDre(b.broker_dre, dreCache); if (dre && dre.dre_status) gotDre++; }
// Hook EVERY license number on the listing into its full CA DRE record (Steve: record all
// license numbers, always hook the full list). DRE lookups are $0/local and cached, so
// resolving co-agents adds no cost; the primary reuses the record already fetched above.
if (Array.isArray(b.agents)) {
for (const a of b.agents) {
if (!a.dre) continue;
const d = (a.dre === b.broker_dre) ? dre : await hookDre(a.dre, dreCache);
if (d) a.dre_record = d;
}
}
// Hook the BUYER-side license numbers into their full DRE records too (TK-10243, $0/cached).
if (Array.isArray(b.buyer_agents)) {
for (const a of b.buyer_agents) { if (!a.dre) continue; const d = await hookDre(a.dre, dreCache); if (d) a.dre_record = d; }
}
// NEVER clobber good data with an empty scrape (WAF blocks return no broker). Only overwrite
// when this fetch actually yielded broker/agent data; on a block, preserve any prior record and
// just flag the miss if we had nothing before (so --only-missing can retry it later).
const gotData = b.broker_name || b.firm_name || (Array.isArray(b.agents) && b.agents.length) || (Array.isArray(b.buyer_agents) && b.buyer_agents.length);
if (gotData) {
const prior = brokers[c.id] || {};
// Merge over the prior record — a PARTIAL hit (e.g. fallback listingAgentName with no agents
// array) must NOT drop the co-agent license list a previous full scrape recorded (Cody gate).
const mergedAgents = (Array.isArray(b.agents) && b.agents.length) ? b.agents : (prior.agents || undefined);
// Likewise never drop a previously-captured buyer side on a pass that didn't re-find it.
const mergedBuyers = (Array.isArray(b.buyer_agents) && b.buyer_agents.length) ? b.buyer_agents : (prior.buyer_agents || undefined);
brokers[c.id] = { ...prior, id: c.id, address: c.address, city: c.city, ...b, agents: mergedAgents, buyer_agents: mergedBuyers, dre: dre || prior.dre || undefined, fetched_at: new Date().toISOString(), http: status };
} else if (!brokers[c.id] || !(brokers[c.id].broker_name || brokers[c.id].broker_dre)) {
brokers[c.id] = { id: c.id, address: c.address, city: c.city, fetched_at: new Date().toISOString(), http: status, blocked: true };
}
fs.writeFileSync(BROKERS, JSON.stringify(brokers, null, 1));
process.stdout.write(`\r ${done}/${targets.length} broker:${gotBroker} dre:${gotDre} blocked:${blocked.length} `);
await sleep(jitter());
}
process.stdout.write('\n');
if (aborted) console.log(` ⚠ ABORTED EARLY: ${aborted}. Processed ${done}/${targets.length}; rerun later (WAF cooler) with --only-missing to finish. Existing data preserved.`);
if (browser) await browser.close();
// (Browserbase cost-ledger logging removed — TK-10655: enrichment is now $0 local, nothing to bill.)
// Merge into canonical condos-redfin.json (backup first; ADD-only by id).
const stamp = new Date().toISOString().slice(0, 10);
fs.copyFileSync(CONDOS, path.join(ROOT, 'data', `condos-redfin.json.bak-${stamp}`));
let merged = 0;
for (const c of doc.condos) {
const b = brokers[c.id];
if (!b || !b.broker_name) continue;
c.broker_name = b.broker_name;
if (b.firm_name) c.firm_name = b.firm_name;
if (b.broker_dre) { c.broker_dre = b.broker_dre; c.dre_url = (b.dre && b.dre.dre_url) || `${DRE_BASE}?License_id=${b.broker_dre}`; }
if (b.agent_phone) c.agent_phone = b.agent_phone;
if (b.office_phone) c.office_phone = b.office_phone;
if (b.dre) {
c.responsible_broker = b.dre.responsible_broker || c.responsible_broker;
c.broker_dre_status = b.dre.dre_status; c.broker_dre_expiration = b.dre.dre_expiration;
}
merged++;
}
fs.writeFileSync(CONDOS, JSON.stringify(doc, null, 1));
console.log(`✔ merged broker info into ${merged} condo records (backup: condos-redfin.json.bak-${stamp})`);
console.log(` broker found: ${gotBroker}/${targets.length} · DRE hooked: ${gotDre} · blocked(WAF, retry later): ${blocked.length}`);
if (blocked.length) console.log(` blocked ids: ${blocked.join(',')}`);
console.log(' cost: $0 (local plain-HTTP)');
}
if (require.main === module) main().catch(e => { console.error('FATAL', e); process.exit(1); });