← back to Commercialrealestate
scripts/fetch-closed-sales.js
165 lines
// fetch-closed-sales.js — pull REAL recently-SOLD LA condos (last ~10yr) from Redfin's gis-csv feed,
// the sold analog of the active-listing scrape. Gives true sale price + date comps for market context
// and for grounding the warrantability/pitch narrative. Stored in cre.closed_sale. Per-AGENT
// attribution is a later join (agent sold-history) once residential agents are resolved — this is the
// market dataset, not agent-attributed (no fabricated attribution).
//
// Feed-first, reuses data/redfin-regions.json (region_ids already resolved). gis-csv sold param is
// probed at runtime (CC_PROBE=1 logs which sold filter returns rows). One Browserbase session.
// NODE_PATH -> browserbase skill. CAP via CC_MAXREGIONS (default 12 for the probe; 0 = all).
'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 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 MAXR = process.env.CC_MAXREGIONS != null ? +process.env.CC_MAXREGIONS : 12;
const PROBE = process.env.CC_PROBE === '1';
const NUM_HOMES = 350; // Redfin gis-csv per-query cap
const CAP_THRESHOLD = NUM_HOMES - 5; // >= this => region is truncated, re-query in price bands
// Price bands (USD) to union when a region is capped. Open-ended top band catches luxury tail.
const PRICE_BANDS = [
[0, 500000], [500000, 750000], [750000, 1000000],
[1000000, 1500000], [1500000, 3000000], [3000000, 0]
];
function parseCsv(text) {
const lines = text.trim().split('\n'); if (lines.length < 2) return [];
const cut = lines[0].split(',').map(h => h.replace(/"/g, '').trim());
return lines.slice(1).map(line => {
const cells = line.match(/("([^"]|"")*"|[^,]*)(,|$)/g).map(c => c.replace(/,$/, '').replace(/^"|"$/g, '').replace(/""/g, '"'));
const o = {}; cut.forEach((h, i) => o[h] = cells[i]); return o;
});
}
(async () => {
await pool.query(`CREATE TABLE IF NOT EXISTS closed_sale (
id serial PRIMARY KEY, address text, city text, zip text, sold_price bigint, sold_date date,
beds numeric, baths numeric, sqft int, year_built int, mls text, url text UNIQUE,
source text DEFAULT 'redfin gis-csv (sold)', created_at timestamptz DEFAULT now())`);
// Resume ledger: which region+type pairs have been fully swept, so a re-run after a session timeout
// skips finished regions and picks up the tail. Idempotent regardless (url is the unique key).
await pool.query(`CREATE TABLE IF NOT EXISTS swept_region (
region_id text, region_type int, name text, swept_at timestamptz DEFAULT now(),
PRIMARY KEY (region_id, region_type))`);
const FRESH = process.env.CC_FRESH === '1';
if (FRESH) await pool.query('TRUNCATE swept_region');
const done = new Set((await pool.query('SELECT region_id, region_type FROM swept_region')).rows
.map(r => r.region_id + ':' + r.region_type));
// Region list = resolved city regions (type 6) + neighborhood regions (type 1), from both resolver files.
const load = (f, type) => { try { return (JSON.parse(fs.readFileSync(path.join(ROOT, 'data', f), 'utf8')).regions || []).filter(r => r.region_id).map(r => ({ ...r, region_type: r.region_type || type })); } catch (_) { return []; } };
const seen = new Set();
const regions = [...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; })
.filter(r => PROBE || !done.has(r.region_id + ':' + r.region_type)); // resume: skip already-swept
const work = (MAXR ? regions.slice(0, MAXR) : regions);
console.log(`closed-sales: ${work.length} regions to sweep${PROBE ? ' (PROBE)' : ''} (${done.size} already done)`);
// Per-region incremental DB flush so a mid-session Browserbase timeout still persists everything
// fetched so far (idempotent on url). Returns # newly inserted.
const num = v => { const n = +String(v == null ? '' : v).replace(/[^0-9.]/g, ''); return Number.isFinite(n) && n > 0 ? Math.round(n) : null; };
let added = 0, captured = 0;
const flush = async (recs, region) => {
captured += recs.length;
for (const r of recs) {
const addr = r['ADDRESS'] || r['Address'], price = num(r['PRICE'] || r['Price']);
const url = r['URL'] || r['URL (SEE https://www.redfin.com/buy-a-home/comparative-market-analysis FOR INFO ON PRICING)'];
const sold = r['SOLD DATE'] || r['Sold Date'] || r['SOLD_DATE'];
if (!addr || !url) continue;
try {
const ins = await pool.query(`INSERT INTO closed_sale(address,city,zip,sold_price,sold_date,beds,baths,sqft,year_built,mls,url)
VALUES($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11) ON CONFLICT(url) DO NOTHING`,
[addr, r['CITY'] || r['City'], r['ZIP OR POSTAL CODE'] || r['ZIP'], price,
sold && /\d/.test(sold) ? new Date(sold) : null, num(r['BEDS']), num(r['BATHS']),
num(r['SQUARE FEET']), num(r['YEAR BUILT']), r['MLS#'] || r['MLS'], url.startsWith('http') ? url : 'https://www.redfin.com' + url]);
added += ins.rowCount || 0;
} catch (_) {}
}
if (region) await pool.query('INSERT INTO swept_region(region_id,region_type,name) VALUES($1,$2,$3) ON CONFLICT DO NOTHING',
[region.region_id, region.region_type, region.name || region.query]);
};
// Candidate sold-filter params to try (Redfin's gis-csv sold mode). First that returns rows wins.
// Redfin's free sold feed empirically returns ~5yr depth even with sold_within_days=3650 (10yr) —
// the depth is verified honestly AFTER the sweep (see min(sold_date) report); we do NOT label 10yr.
const SOLD_VARIANTS = [
`s=9&num_homes=${NUM_HOMES}&sold_within_days=3650`,
`status=9&sold_within_days=3650&num_homes=${NUM_HOMES}`,
`sold=1&sold_within_days=3650&num_homes=${NUM_HOMES}`,
`include=sold-3yr&num_homes=${NUM_HOMES}`
];
let browser, rows = [], soldParam = null;
try {
const bb = new Browserbase({ apiKey: get(bbEnv, 'BROWSERBASE_API_KEY') });
const session = await bb.sessions.create({ projectId: get(bbEnv, 'BROWSERBASE_PROJECT_ID'), browserSettings: { solveCaptchas: true, viewport: { width: 1440, height: 900 } } });
console.log('bb session', session.id);
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/', { waitUntil: 'domcontentloaded' }); await page.waitForTimeout(3000);
// in-page fetch of one gis-csv url -> parsed rows (or [] on err). 'price' = optional [min,max] band.
const fetchRegion = async (r, soldP, 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=${r.region_id}®ion_type=${r.region_type || 6}&uipt=3&${soldP}${extra}`;
const txt = await page.evaluate(async u => { try { const x = await fetch(u); return x.ok ? await x.text() : ('ERR' + x.status); } catch (e) { return 'EXC'; } }, url);
if (typeof txt === 'string' && (txt.startsWith('ERR') || txt === 'EXC')) return [];
return parseCsv(txt);
};
// PROBE: find the sold param that returns rows, on the first region.
const r0 = work[0];
if (!r0) { console.log('nothing to sweep — all resolved regions already in swept_region (CC_FRESH=1 to re-sweep).'); }
else for (const v of SOLD_VARIANTS) {
const url = `https://www.redfin.com/stingray/api/gis-csv?al=1®ion_id=${r0.region_id}®ion_type=${r0.region_type}&uipt=3&${v}`;
const txt = await page.evaluate(async u => { try { const r = await fetch(u); return r.ok ? await r.text() : ('ERR' + r.status); } catch (e) { return 'EXC'; } }, url);
const n = (typeof txt === 'string' && txt.startsWith('ERR')) ? 0 : parseCsv(txt).length;
console.log(` probe [${v}] -> ${typeof txt === 'string' && txt.startsWith('ERR') ? txt : n + ' rows'}`);
if (n > 0) { soldParam = v; if (PROBE) { rows = parseCsv(txt).map(x => ({ ...x, _region: r0.name })); } break; }
}
if (!soldParam) { console.log('NO sold variant returned rows — Redfin sold feed param needs revisiting.'); }
else if (!PROBE) {
let bandedRegions = 0, sweptCount = 0;
for (const r of work) {
let base;
try { base = await fetchRegion(r, soldParam); }
catch (e) { console.error(' session closed mid-sweep — flushed regions persist, re-run to resume:', e.message); break; }
if (base.length < CAP_THRESHOLD) {
await flush(base, r); sweptCount++;
await page.waitForTimeout(400);
continue;
}
// Region is capped/truncated — re-query in price bands and union (dedupe on url before flush).
bandedRegions++;
const seenUrl = new Set(); const unioned = [];
const keep = arr => { for (const x of arr) { const u = x['URL'] || x['URL (SEE https://www.redfin.com/buy-a-home/comparative-market-analysis FOR INFO ON PRICING)']; if (u && !seenUrl.has(u)) { seenUrl.add(u); unioned.push(x); } } };
keep(base);
let broke = false;
for (const band of PRICE_BANDS) {
try { keep(await fetchRegion(r, soldParam, band)); }
catch (e) { broke = true; break; }
await page.waitForTimeout(350);
}
await flush(unioned, broke ? null : r); sweptCount++; // only mark done if all bands completed
process.stderr.write(` [banded] ${r.name}: base ${base.length} -> ${seenUrl.size} unioned across ${PRICE_BANDS.length} bands${broke ? ' (PARTIAL — not marked done)' : ''}\n`);
if (broke) { console.error(' session closed mid-band — re-run to resume.'); break; }
}
console.log(`swept ${sweptCount}/${work.length} regions this session (${bandedRegions} banded)`);
}
} catch (e) { console.error('FATAL', e.message); } finally { if (browser) await browser.close().catch(() => {}); }
const depth = (await pool.query(
'SELECT count(*)::int n, min(sold_date) oldest, max(sold_date) newest, count(distinct city)::int cities FROM closed_sale')).rows[0];
console.log(`\nsold param = ${soldParam || 'NONE'} | captured ${captured} rows, +${added} new | closed_sale total ${depth.n}`);
console.log(`HONEST DEPTH: ${depth.oldest ? depth.oldest.toISOString().slice(0, 10) : '?'} -> ${depth.newest ? depth.newest.toISOString().slice(0, 10) : '?'} across ${depth.cities} cities (Redfin free sold feed ~5yr; NOT 10yr).`);
console.log('cost: ~$0.04-0.10 (1 Browserbase session).');
await pool.end();
})();