← back to Commercialrealestate
scripts/fetch-condos-redfin.js
263 lines
// fetch-condos-redfin.js — FEED-FIRST Redfin LA County CONDO scraper (APPROVED, capped run).
//
// Strategy (feed-first, no DOM scraping): for each LA submarket region (resolved to a Redfin region_id
// by resolve-redfin-regions.js + resolve-redfin-neighborhoods.js), open ONE Browserbase session, land
// on the region's condo city/neighborhood page (sets Redfin's expected referer/cookies), then fetch
// Redfin's own gis-csv search feed IN-PAGE:
// /stingray/api/gis-csv?al=1®ion_id=<id>®ion_type=<6 city|1 hood>&uipt=3&num_homes=350&status=9
// uipt=3 = condo/co-op, status=9 = active for-sale. The CSV is clean columns (address/city/zip/price/
// beds/baths/sqft/year/HOA/url/MLS) — far more robust than intercepting the rendered map.
//
// Each condo is classified via classify-warrantability.js (FHA-list match primary + heuristic flags)
// and upserted into cre.condo with an honest PROXY label.
//
// HARD CAP (Steve-approved): stop at MAX_SESSIONS (80) sessions OR ~$3.20 spend, whichever first.
// Per-session + running batch total surfaced live. Redfin-ONLY — Zillow+2captcha is a SEPARATE gate.
//
// Usage: NODE_PATH=$HOME/.claude/skills/browserbase/node_modules node scripts/fetch-condos-redfin.js
// CC_MAX_SESSIONS=80 CC_MAX_COST=3.20 CC_PER_SESSION_REGIONS=1
'use strict';
const fs = require('fs');
const path = require('path');
const { chromium } = require('playwright-core');
const Browserbase = require('@browserbasehq/sdk').default;
const { classify, loadFhaList, PROXY_LABEL } = require('./classify-warrantability');
const { inLACounty } = require('./sources/la-county');
let brokerdb = null; try { brokerdb = require('./db/brokers-db'); } catch (_) {}
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');
// CC_LOCAL=1 launches the local Chrome (residential IP clears Redfin's bot wall) instead of a paid
// Browserbase session — $0/run. Same in-page gis-csv fetch; only the browser transport differs.
const LOCAL = process.env.CC_LOCAL === '1';
const CHROME_PATH = process.env.CHROME_PATH || '/Applications/Google Chrome.app/Contents/MacOS/Google Chrome';
const SESSION_COST = LOCAL ? 0 : 0.04;
const MAX_SESSIONS = +(process.env.CC_MAX_SESSIONS || (LOCAL ? 999 : 80));
const MAX_COST = +(process.env.CC_MAX_COST || 3.20); // in LOCAL mode SESSION_COST=0 so this cap never trips
const PER_SESSION_REGIONS = +(process.env.CC_PER_SESSION_REGIONS || (LOCAL ? 20 : 4)); // more regions per local Chrome launch (launches are cheap); small batches per paid session to stay under cap
const ROOT = path.join(__dirname, '..');
const OUT = path.join(ROOT, 'data', 'condos-redfin.json');
// Build the region work-list from the two resolver outputs (dedup by region_id+type).
function loadRegions() {
const out = [];
const seen = new Set();
const add = (r, region_type) => {
if (!r.region_id) return;
const key = r.region_id + ':' + region_type;
if (seen.has(key)) return; seen.add(key);
out.push({ name: r.query || r.name, region_id: r.region_id, region_type, url: r.url });
};
try {
const cities = JSON.parse(fs.readFileSync(path.join(ROOT, 'data', 'redfin-regions.json'), 'utf8')).regions || [];
cities.forEach(r => add(r, 6));
} catch (_) {}
try {
const hoods = JSON.parse(fs.readFileSync(path.join(ROOT, 'data', 'redfin-neighborhoods.json'), 'utf8')).regions || [];
hoods.forEach(r => add(r, r.region_type || 1));
} catch (_) {}
return out;
}
// Minimal CSV parser (handles quoted fields with commas).
function parseCSV(text) {
const rows = [];
let i = 0, field = '', row = [], inQ = false;
while (i < text.length) {
const c = text[i];
if (inQ) {
if (c === '"') { if (text[i + 1] === '"') { field += '"'; i++; } else inQ = false; }
else field += c;
} else {
if (c === '"') inQ = true;
else if (c === ',') { row.push(field); field = ''; }
else if (c === '\n') { row.push(field); rows.push(row); row = []; field = ''; }
else if (c === '\r') { /* skip */ }
else field += c;
}
i++;
}
if (field.length || row.length) { row.push(field); rows.push(row); }
return rows;
}
function condosFromCSV(text, regionName) {
const rows = parseCSV(text);
if (!rows.length) return [];
// Find the header row (Redfin prepends a disclaimer line for some feeds).
let hi = rows.findIndex(r => r.join(',').includes('PROPERTY TYPE') && r.join(',').includes('ADDRESS'));
if (hi < 0) return [];
const H = rows[hi].map(h => h.trim());
const col = name => H.findIndex(h => h.toUpperCase() === name || h.toUpperCase().startsWith(name));
const ci = {
type: col('PROPERTY TYPE'), addr: col('ADDRESS'), city: col('CITY'), zip: col('ZIP OR POSTAL CODE'),
price: col('PRICE'), beds: col('BEDS'), baths: col('BATHS'), sqft: col('SQUARE FEET'),
year: col('YEAR BUILT'), hoa: col('HOA/MONTH'), status: col('STATUS'), dom: col('DAYS ON MARKET'),
lat: col('LATITUDE'), lng: col('LONGITUDE'),
url: H.findIndex(h => h.toUpperCase().startsWith('URL')), source: col('SOURCE'), mls: col('MLS#')
};
const out = [];
for (let r = hi + 1; r < rows.length; r++) {
const row = rows[r];
if (!row || row.length < 5) continue;
const ptype = (row[ci.type] || '').trim();
// Keep HOA-governed attached for-sale stock the FHA list covers: condos + townhouses.
if (!/condo|co-?op|townhouse/i.test(ptype)) continue;
const price = Number((row[ci.price] || '').replace(/[^\d.]/g, ''));
const addr = (row[ci.addr] || '').trim();
const city = (row[ci.city] || '').trim();
if (!addr || !price) continue;
if (city && !inLACounty(city)) continue; // geo gate
const url = (row[ci.url] || '').trim();
const mls = (row[ci.mls] || '').trim();
const id = 'rdf' + (mls || (addr + (row[ci.zip] || '')).replace(/\s+/g, '').toLowerCase());
out.push({
id,
address: addr.slice(0, 120),
city,
zip: (row[ci.zip] || '').trim(),
price,
hoa: ci.hoa >= 0 && row[ci.hoa] ? Number(String(row[ci.hoa]).replace(/[^\d.]/g, '')) || null : null,
beds: ci.beds >= 0 && row[ci.beds] ? Number(row[ci.beds]) || null : null,
baths: ci.baths >= 0 && row[ci.baths] ? Number(row[ci.baths]) || null : null,
sqft: ci.sqft >= 0 && row[ci.sqft] ? Math.round(Number(String(row[ci.sqft]).replace(/[^\d.]/g, ''))) || null : null,
year_built: ci.year >= 0 && row[ci.year] ? Number(row[ci.year]) || null : null,
days_on_market: ci.dom >= 0 && row[ci.dom] !== '' && row[ci.dom] != null ? (Number(String(row[ci.dom]).replace(/[^\d]/g, ''))) : null,
market_status: ci.status >= 0 ? ((row[ci.status] || '').trim() || null) : null,
lat: ci.lat >= 0 && row[ci.lat] && isFinite(+row[ci.lat]) ? +row[ci.lat] : null,
lng: ci.lng >= 0 && row[ci.lng] && isFinite(+row[ci.lng]) ? +row[ci.lng] : null,
property_type: ptype,
project_name: null, // CSV has no HOA/project name; classifier uses address+zip vs FHA list
listing_text: ptype, // minimal text; richer description not in the feed
firm_name: null, // listing brokerage not in gis-csv; Job B enriches separately
broker_name: null,
source: url ? (url.startsWith('http') ? url : 'https://www.redfin.com' + url) : null,
_region: regionName
});
}
return out;
}
(async () => {
const regions = loadRegions();
if (!regions.length) { console.error('No regions resolved — run resolve-redfin-regions.js first.'); process.exit(1); }
process.stderr.write(`Regions to sweep: ${regions.length}\n`);
const captured = new Map();
let sessions = 0;
const perRegion = [];
// Chunk regions into sessions (PER_SESSION_REGIONS each) so we use far fewer sessions than the cap.
for (let g = 0; g < regions.length; g += PER_SESSION_REGIONS) {
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 = regions.slice(g, g + PER_SESSION_REGIONS);
let browser, session;
sessions++;
const running = (sessions * SESSION_COST).toFixed(2);
process.stderr.write(`\n[session ${sessions}/${MAX_SESSIONS}] regions: ${group.map(r => r.name).join(', ')} (per-session $${SESSION_COST.toFixed(2)} · running batch total $${running})\n`);
try {
if (LOCAL) {
browser = await chromium.launch({ executablePath: CHROME_PATH, args: ['--disable-blink-features=AutomationControlled'] });
} else {
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] || await browser.newContext({ userAgent: 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/120.0.0.0 Safari/537.36', viewport: { width: 1440, height: 1000 } });
const page = ctx.pages()[0] || await ctx.newPage();
page.setDefaultTimeout(45000);
// Warm up Redfin once per session.
await page.goto('https://www.redfin.com/city/11203/CA/Los-Angeles/filter/property-type=condo', { waitUntil: 'domcontentloaded' }).catch(() => {});
await page.waitForTimeout(2500);
// status=9 active for-sale; status=130 pending/contingent (in escrow). Sweep both.
const fetchStatus = async (reg, status) => {
const u = `https://www.redfin.com/stingray/api/gis-csv?al=1®ion_id=${reg.region_id}®ion_type=${reg.region_type}&uipt=3&num_homes=350&status=${status}&sf=1,2,3,5,6,7&v=8`;
try {
const res = await page.evaluate(async (uu) => { const r = await fetch(uu, { headers: { accept: 'text/csv' } }); return { status: r.status, text: await r.text() }; }, u);
return (res.status === 200 && res.text && res.text.includes('PROPERTY TYPE')) ? condosFromCSV(res.text, reg.name) : [];
} catch (e) { return []; }
};
for (const reg of group) {
const before = captured.size;
const active = await fetchStatus(reg, 9);
await page.waitForTimeout(250);
let pend = []; try { pend = await fetchStatus(reg, 130); } catch (_) {}
const condos = active.concat(pend);
if (condos.length) {
for (const c of condos) if (!captured.has(c.id)) captured.set(c.id, c);
const gained = captured.size - before;
perRegion.push({ region: reg.name, region_id: reg.region_id, region_type: reg.region_type, parsed: condos.length, gained, cumulative: captured.size });
process.stderr.write(` ${reg.name}: ${active.length} active + ${pend.length} escrow (+${gained} new, cumulative ${captured.size})\n`);
} else {
perRegion.push({ region: reg.name, region_id: reg.region_id, gained: 0, error: 'no feed' });
}
await page.waitForTimeout(800);
}
} catch (e) {
process.stderr.write(' session err: ' + e.message.split('\n')[0] + '\n');
} finally {
if (browser) try { await browser.close(); } catch (_) {}
}
}
const condos = [...captured.values()].map(c => { delete c._region; return c; });
const fha = loadFhaList();
for (const c of condos) {
const r = classify(c, fha);
c.warrantable_status = r.status;
c.warrant_source = r.source;
c.warrant_signals = r;
}
const actualCost = (sessions * SESSION_COST).toFixed(2);
const payload = {
meta: {
source: 'Redfin LA County condo search — feed-first (gis-csv), via Browserbase',
sessions_used: sessions,
cost: `~$${actualCost} (${sessions} Browserbase sessions @ $${SESSION_COST.toFixed(2)})`,
cap: `HARD CAP ${MAX_SESSIONS} sessions / $${MAX_COST.toFixed(2)} — Redfin-only; Zillow+2captcha is a separate gate`,
warrantability_label: PROXY_LABEL,
fetched_at: new Date().toISOString(),
count: condos.length,
perRegion
},
condos
};
fs.writeFileSync(OUT, JSON.stringify(payload, null, 2));
process.stderr.write(`\nCaptured ${condos.length} condos across ${sessions} sessions -> ${OUT}\n`);
if (brokerdb && condos.length) {
try {
for (const c of condos) {
const firmId = c.firm_name ? await brokerdb.upsertFirm(c.firm_name) : null;
await brokerdb.pool.query(
`INSERT INTO condo(id,address,city,zip,price,hoa,beds,baths,sqft,year_built,project_name,
listing_text,warrantable_status,warrant_source,warrant_signals,firm_id,firm_name,broker_name,source,last_seen,status,days_on_market,market_status,listed_date,lat,lng)
VALUES($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,now(),'active',$20::int,$21, CASE WHEN $20::int IS NOT NULL THEN CURRENT_DATE - $20::int ELSE NULL END,$22::float8,$23::float8)
ON CONFLICT(id) DO UPDATE SET price=EXCLUDED.price, hoa=EXCLUDED.hoa,
warrantable_status=EXCLUDED.warrantable_status, warrant_source=EXCLUDED.warrant_source,
warrant_signals=EXCLUDED.warrant_signals,
last_seen=now(), status='active', off_market_at=NULL, disposition=NULL, sold_price=NULL, sold_date=NULL,
days_on_market=EXCLUDED.days_on_market, listed_date=EXCLUDED.listed_date,
prev_market_status=condo.market_status, market_status=EXCLUDED.market_status,
prev_price=CASE WHEN EXCLUDED.price IS DISTINCT FROM condo.price THEN condo.price ELSE condo.prev_price END,
price_changed_at=CASE WHEN EXCLUDED.price IS DISTINCT FROM condo.price THEN now() ELSE condo.price_changed_at END,
lat=COALESCE(EXCLUDED.lat, condo.lat), lng=COALESCE(EXCLUDED.lng, condo.lng)`,
[c.id, c.address, c.city, c.zip, c.price, c.hoa, c.beds, c.baths, c.sqft, c.year_built,
c.project_name, c.listing_text, c.warrantable_status, c.warrant_source,
JSON.stringify(c.warrant_signals), firmId, c.firm_name, c.broker_name, c.source, c.days_on_market ?? null, c.market_status ?? null, c.lat ?? null, c.lng ?? null]);
}
process.stderr.write(`Upserted ${condos.length} into cre.condo\n`);
} catch (e) { process.stderr.write('DB upsert err: ' + e.message + '\n'); }
finally { try { await brokerdb.pool.end(); } catch (_) {} }
}
const by = {}; condos.forEach(c => by[c.warrantable_status] = (by[c.warrantable_status] || 0) + 1);
console.log(JSON.stringify({ count: condos.length, sessions, cost: '$' + actualCost, byStatus: by }));
})();