← back to Commercialrealestate
CRE: closed-sales scraper (Redfin gis-csv sold feed, 10yr) → closed_sale market-comps table; per-agent attribution follows agent resolution
a686d470ddb33dbaf522fc8015e6d1a4ae6819fa · 2026-06-28 08:02:01 -0700 · Steve
Files touched
A scripts/fetch-closed-sales.js
Diff
commit a686d470ddb33dbaf522fc8015e6d1a4ae6819fa
Author: Steve <steve@designerwallcoverings.com>
Date: Sun Jun 28 08:02:01 2026 -0700
CRE: closed-sales scraper (Redfin gis-csv sold feed, 10yr) → closed_sale market-comps table; per-agent attribution follows agent resolution
---
scripts/fetch-closed-sales.js | 101 ++++++++++++++++++++++++++++++++++++++++++
1 file changed, 101 insertions(+)
diff --git a/scripts/fetch-closed-sales.js b/scripts/fetch-closed-sales.js
new file mode 100644
index 0000000..402fdae
--- /dev/null
+++ b/scripts/fetch-closed-sales.js
@@ -0,0 +1,101 @@
+// 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';
+
+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())`);
+
+ const regions = JSON.parse(fs.readFileSync(path.join(ROOT, 'data', 'redfin-regions.json'), 'utf8')).resolved || [];
+ const work = (MAXR ? regions.slice(0, MAXR) : regions).filter(r => r.region_id);
+ console.log(`closed-sales: ${work.length} regions${PROBE ? ' (PROBE)' : ''}`);
+
+ // Candidate sold-filter params to try (Redfin's gis-csv sold mode). First that returns rows wins.
+ const SOLD_VARIANTS = [
+ 's=9&num_homes=350&sold_within_days=3650', // sold last 10yr (3650d)
+ 'status=9&sold_within_days=3650&num_homes=350',
+ 'sold=1&sold_within_days=3650&num_homes=350',
+ 'include=sold-3yr&num_homes=350'
+ ];
+
+ 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);
+
+ // PROBE: find the sold param that returns rows, on the first region.
+ const r0 = work[0];
+ 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=6&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) {
+ for (const r of work) {
+ 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&${soldParam}`;
+ const txt = await page.evaluate(async u => { try { const x = await fetch(u); return x.ok ? await x.text() : 'ERR'; } catch (e) { return 'EXC'; } }, url);
+ if (typeof txt === 'string' && (txt === 'ERR' || txt === 'EXC')) continue;
+ rows.push(...parseCsv(txt).map(x => ({ ...x, _region: r.name })));
+ await page.waitForTimeout(800);
+ }
+ }
+ } catch (e) { console.error('FATAL', e.message); } finally { if (browser) await browser.close().catch(() => {}); }
+
+ // ingest
+ 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;
+ for (const r of rows) {
+ const addr = r['ADDRESS'] || r['Address'], price = num(r['PRICE'] || r['Price']), 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 {
+ 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++;
+ } catch (_) {}
+ }
+ const total = (await pool.query('SELECT count(*)::int n FROM closed_sale')).rows[0].n;
+ console.log(`\nsold param = ${soldParam || 'NONE'} | captured ${rows.length} rows, +${added} new | closed_sale total ${total}`);
+ console.log('cost: ~$0.04-0.10 (1 Browserbase session).');
+ await pool.end();
+})();
← 2d837a2 CRE residential agents: feasibility probe (mainHouseInfo end
·
back to Commercialrealestate
·
CRE residential agents: integrate into CRCP — fetch-redfin-a 3fb9caa →