← back to Commercialrealestate
Add fetch-sfr-redfin.js — feed-first uipt=1 SFR sweep w/ price-banding + swept_region_sfr ledger
9a5c2603a159c647b0e3369694b0997e59442cad · 2026-06-28 16:07:01 -0700 · Steve
Files touched
A scripts/fetch-sfr-redfin.js
Diff
commit 9a5c2603a159c647b0e3369694b0997e59442cad
Author: Steve <steve@designerwallcoverings.com>
Date: Sun Jun 28 16:07:01 2026 -0700
Add fetch-sfr-redfin.js — feed-first uipt=1 SFR sweep w/ price-banding + swept_region_sfr ledger
---
scripts/fetch-sfr-redfin.js | 226 ++++++++++++++++++++++++++++++++++++++++++++
1 file changed, 226 insertions(+)
diff --git a/scripts/fetch-sfr-redfin.js b/scripts/fetch-sfr-redfin.js
new file mode 100644
index 0000000..b961752
--- /dev/null
+++ b/scripts/fetch-sfr-redfin.js
@@ -0,0 +1,226 @@
+// fetch-sfr-redfin.js — FEED-FIRST Redfin LA County SFR (single-family) FOR-SALE scraper.
+//
+// Same gis-csv feed that cracked the condo sweep (fetch-condos-redfin.js) but uipt=1 (single-family
+// detached) instead of uipt=3 (condo). status=9 = active for-sale. For each resolved LA region
+// (data/redfin-regions.json cities + redfin-neighborhoods.json hoods, 229 region_ids) we warm ONE
+// Browserbase session and fetch the gis-csv feed in-page, then upsert each SFR into cre.sfr.
+//
+// /stingray/api/gis-csv?al=1®ion_id=<id>®ion_type=<6 city|1 hood>&uipt=1&num_homes=350&status=9
+//
+// Dense regions truncate at Redfin's ~350 cap, so a capped region is re-queried in PRICE BANDS and
+// unioned (same as fetch-closed-sales.js) to beat the cap. A swept_region_sfr resume ledger marks
+// finished regions so a re-run after a session timeout picks up the tail (idempotent regardless —
+// sfr.id / sfr.source(url) are unique keys).
+//
+// HONEST LABELING: the gis-csv feed has no listing agent — broker_name/firm_name stay NULL here.
+// Listing agents are captured separately by fetch-sfr-agents.js (mainHouseInfoPanelInfo), which
+// honestly labels SFRs whose agent Redfin suppresses.
+//
+// HARD CAP (Steve-approved): stop at MAX_SESSIONS (30) sessions OR ~$1.50 spend, whichever first.
+// Per-session + RUNNING BATCH TOTAL surfaced live. Redfin-ONLY (no Zillow/2captcha — separate gate).
+//
+// Usage: NODE_PATH=$HOME/.claude/skills/browserbase/node_modules node scripts/fetch-sfr-redfin.js
+// CC_MAX_SESSIONS=30 CC_MAX_COST=1.50 CC_PER_SESSION_REGIONS=10 CC_MAXREGIONS=0(all) CC_FRESH=1
+'use strict';
+const fs = require('fs');
+const path = require('path');
+const { chromium } = require('playwright-core');
+const Browserbase = require('@browserbasehq/sdk').default;
+const { Pool } = require('pg');
+const { inLACounty } = require('./sources/la-county');
+
+const bbEnv = 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 ROOT = path.join(__dirname, '..');
+const pool = new Pool({ host: '/tmp', port: 5432, database: 'cre', user: process.env.USER || 'stevestudio2' });
+
+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_REGIONS = +(process.env.CC_PER_SESSION_REGIONS || 10);
+const MAXR = process.env.CC_MAXREGIONS != null ? +process.env.CC_MAXREGIONS : 0; // 0 = all
+const NUM_HOMES = 350;
+const CAP_THRESHOLD = NUM_HOMES - 5;
+const PRICE_BANDS = [
+ [0, 500000], [500000, 750000], [750000, 1000000],
+ [1000000, 1500000], [1500000, 3000000], [3000000, 0]
+];
+
+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;
+}
+
+// Extract SFR rows from a gis-csv response. Returns [] when no header (empty/blocked feed).
+function sfrsFromCSV(text) {
+ const rows = parseCSV(text);
+ 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'), status: col('STATUS'),
+ url: H.findIndex(h => h.toUpperCase().startsWith('URL')), 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();
+ // uipt=1 returns detached single-family; guard against any stray attached types.
+ if (!/single.?family|^house$/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;
+ 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,
+ 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,
+ listing_text: ptype,
+ source: url ? (url.startsWith('http') ? url : 'https://www.redfin.com' + url) : null
+ });
+ }
+ return out;
+}
+
+function loadRegions() {
+ const load = (f, type) => {
+ try { return (JSON.parse(fs.readFileSync(path.join(ROOT, 'data', f), 'utf8')).regions || []).filter(r => r.region_id).map(r => ({ name: r.query || r.name, region_id: r.region_id, region_type: r.region_type || type })); }
+ catch (_) { return []; }
+ };
+ const seen = new Set();
+ return [...load('redfin-regions.json', 6), ...load('redfin-neighborhoods.json', 1)]
+ .filter(r => { const k = r.region_id + ':' + r.region_type; if (seen.has(k)) return false; seen.add(k); return true; });
+}
+
+(async () => {
+ await pool.query(`CREATE TABLE IF NOT EXISTS swept_region_sfr (
+ region_id text, region_type int, name text, swept_at timestamptz DEFAULT now(),
+ PRIMARY KEY (region_id, region_type))`);
+ if (process.env.CC_FRESH === '1') await pool.query('TRUNCATE swept_region_sfr');
+ const done = new Set((await pool.query('SELECT region_id, region_type FROM swept_region_sfr')).rows
+ .map(r => r.region_id + ':' + r.region_type));
+
+ const allRegions = loadRegions().filter(r => !done.has(r.region_id + ':' + r.region_type));
+ const regions = MAXR ? allRegions.slice(0, MAXR) : allRegions;
+ if (!regions.length) { console.log(JSON.stringify({ note: 'all resolved regions already swept (CC_FRESH=1 to re-sweep)', captured: 0 })); await pool.end(); return; }
+ process.stderr.write(`SFR sweep: ${regions.length} regions to go (${done.size} already swept) · cap ${MAX_SESSIONS} sessions / $${MAX_COST.toFixed(2)}\n`);
+
+ let added = 0, captured = 0;
+ const num = v => { const n = +String(v == null ? '' : v).replace(/[^0-9.]/g, ''); return Number.isFinite(n) && n > 0 ? Math.round(n) : null; };
+
+ // Upsert one SFR (idempotent on id; source/url is also UNIQUE).
+ const upsert = async (s) => {
+ if (!s.source) return;
+ const ins = await pool.query(
+ `INSERT INTO sfr(id,address,city,zip,price,beds,baths,sqft,year_built,listing_text,source)
+ VALUES($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11)
+ ON CONFLICT(id) DO UPDATE SET price=EXCLUDED.price, beds=EXCLUDED.beds, baths=EXCLUDED.baths,
+ sqft=EXCLUDED.sqft, year_built=EXCLUDED.year_built
+ RETURNING (xmax = 0) AS inserted`,
+ [s.id, s.address, s.city, s.zip, s.price, s.beds, s.baths, s.sqft, s.year_built, s.listing_text, s.source])
+ .catch(() => null);
+ if (ins && ins.rows[0] && ins.rows[0].inserted) added++;
+ };
+ const flush = async (recs, region) => {
+ captured += recs.length;
+ for (const s of recs) await upsert(s);
+ if (region) await pool.query('INSERT INTO swept_region_sfr(region_id,region_type,name) VALUES($1,$2,$3) ON CONFLICT DO NOTHING',
+ [region.region_id, region.region_type, region.name]);
+ };
+
+ let sessions = 0;
+ 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}] ${group.length} regions (per-session $${SESSION_COST.toFixed(2)} · running batch total $${running} / cap $${MAX_COST.toFixed(2)})\n`);
+
+ try {
+ const bb = new Browserbase({ apiKey: get(bbEnv, 'BROWSERBASE_API_KEY') });
+ session = await bb.sessions.create({ projectId: get(bbEnv, 'BROWSERBASE_PROJECT_ID'), 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(2500);
+
+ const fetchRegion = async (reg, band) => {
+ let extra = '';
+ if (band) { if (band[0] > 0) extra += `&min_price=${band[0]}`; if (band[1] > 0) extra += `&max_price=${band[1]}`; }
+ const url = `https://www.redfin.com/stingray/api/gis-csv?al=1®ion_id=${reg.region_id}®ion_type=${reg.region_type}&uipt=1&num_homes=${NUM_HOMES}&status=9&sf=1,2,3,5,6,7&v=8${extra}`;
+ const res = await page.evaluate(async (u) => {
+ try { const r = await fetch(u, { headers: { accept: 'text/csv' } }); return { status: r.status, text: r.ok ? await r.text() : '' }; }
+ catch (e) { return { status: 0, text: '' }; }
+ }, url);
+ return (res.status === 200 && res.text && res.text.includes('PROPERTY TYPE')) ? sfrsFromCSV(res.text) : [];
+ };
+
+ for (const reg of group) {
+ let base;
+ try { base = await fetchRegion(reg); }
+ catch (e) { process.stderr.write(' session closed mid-sweep — flushed regions persist, re-run to resume.\n'); throw e; }
+ if (base.length < CAP_THRESHOLD) {
+ await flush(base, reg);
+ process.stderr.write(` ${reg.name}: ${base.length} SFR (cumulative +${added} new)\n`);
+ await page.waitForTimeout(400);
+ continue;
+ }
+ // Capped — re-query in price bands and union (dedupe on id).
+ const seenId = new Set(); const unioned = [];
+ const keep = arr => { for (const x of arr) if (x.id && !seenId.has(x.id)) { seenId.add(x.id); unioned.push(x); } };
+ keep(base);
+ let broke = false;
+ for (const band of PRICE_BANDS) {
+ try { keep(await fetchRegion(reg, band)); }
+ catch (e) { broke = true; break; }
+ await page.waitForTimeout(350);
+ }
+ await flush(unioned, broke ? null : reg);
+ process.stderr.write(` [banded] ${reg.name}: base ${base.length} -> ${seenId.size} unioned across ${PRICE_BANDS.length} bands${broke ? ' (PARTIAL)' : ''}\n`);
+ if (broke) throw new Error('session closed mid-band');
+ }
+ } catch (e) {
+ process.stderr.write(' session err: ' + e.message.split('\n')[0] + ' (re-run to resume from ledger)\n');
+ } finally { if (browser) try { await browser.close(); } catch (_) {} }
+ }
+
+ const actualCost = (sessions * SESSION_COST).toFixed(2);
+ const tot = (await pool.query('SELECT count(*)::int n, count(DISTINCT city)::int cities FROM sfr')).rows[0];
+ process.stderr.write(`\nSessions ${sessions} · ACTUAL COST ~$${actualCost} · captured ${captured} rows (+${added} new) · sfr total ${tot.n} across ${tot.cities} cities\n`);
+ console.log(JSON.stringify({ sessions, cost: '$' + actualCost, captured, added, sfr_total: tot.n, cities: tot.cities }));
+ await pool.end();
+})();
← e39860e CRCP: SFR/non-QM segments open the lead-source playbook even
·
back to Commercialrealestate
·
Wire sfr-bankstmt segment to cre.sfr + add fetch-sfr-agents. 9f0bd7e →