backfill: broker-website listing scraper (plain-fetch pilot + heuristic extractor, dry-run default, reversible source=broker-site tag) — Steve: find listings direct from broker sites, not crexi

7e3bea33aea086561c82c8dd667609492f72e12e · 2026-08-19 09:08:44 -0700 · Steve Abrams
Files touched

A scripts/backfill-broker-listings.js
Diff

commit 7e3bea33aea086561c82c8dd667609492f72e12e
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Wed Aug 19 09:08:44 2026 -0700

    backfill: broker-website listing scraper (plain-fetch pilot + heuristic extractor, dry-run default, reversible source=broker-site tag) — Steve: find listings direct from broker sites, not crexi
---
 scripts/backfill-broker-listings.js | 78 +++++++++++++++++++++++++++++++++++++
 1 file changed, 78 insertions(+)

diff --git a/scripts/backfill-broker-listings.js b/scripts/backfill-broker-listings.js
new file mode 100644
index 0000000..3e31b94
--- /dev/null
+++ b/scripts/backfill-broker-listings.js
@@ -0,0 +1,78 @@
+#!/usr/bin/env node
+// backfill-broker-listings.js — find listings DIRECTLY from brokers' own websites (Steve 2026-08-19),
+// NEVER from crexi/aggregators. Enriches our broker graph (broker_listing -> listing) with real,
+// direct-from-source listings so the agent-profile pages show more of an agent's actual book.
+//
+// STRATEGY (honest, per the pilot): broker "websites" are heterogeneous —
+//   - plain-fetchable boutique sites (cremgroupre) -> curl works
+//   - 403 bot-blocked (lyonstahl) -> need openclaw real-Chrome (Phase 2, not this pilot)
+//   - JS-rendered (strandsrealty) -> need a browser (Phase 2)
+// This pilot does the PLAIN-FETCH subset + a heuristic price/address extractor and runs DRY by
+// default (prints what it WOULD insert). --apply writes to the DB, tagging rows source='broker-site'
+// so the whole backfill is reversible (DELETE FROM listing WHERE source='broker-site').
+//
+// Usage: node scripts/backfill-broker-listings.js [--limit N] [--apply]
+const db = require('./db/brokers-db');
+const LIMIT = +(process.argv.find(a => a.startsWith('--limit='))?.split('=')[1]) || 8;
+const APPLY = process.argv.includes('--apply');
+const BIG = /cbre|kw\.com|yourkwoffice|kellerwilliams|marcusmillichap|kidder|coldwell|compass|remax|century21|colliers|jll|cushman|berkshire/i;
+
+const UA = 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/120 Safari/537.36';
+async function fetchSite(url) {
+  try {
+    if (!/^https?:\/\//i.test(url)) url = 'https://' + url.replace(/^\/+/, ''); // scheme-less sites (www.x.com)
+    const ctrl = new AbortController(); const t = setTimeout(() => ctrl.abort(), 15000);
+    const r = await fetch(url, { headers: { 'User-Agent': UA, Accept: 'text/html' }, redirect: 'follow', signal: ctrl.signal });
+    clearTimeout(t);
+    if (!r.ok) return { ok: false, status: r.status };
+    return { ok: true, status: r.status, html: await r.text() };
+  } catch (e) { return { ok: false, status: 'ERR', err: String(e.message).slice(0, 40) }; }
+}
+
+// Heuristic listing extractor: find price tokens, grab a nearby street-address-looking phrase.
+const strip = h => h.replace(/<script[\s\S]*?<\/script>/gi, ' ').replace(/<style[\s\S]*?<\/style>/gi, ' ').replace(/<[^>]+>/g, ' ').replace(/&nbsp;/g, ' ').replace(/\s+/g, ' ');
+const ADDR = /\b\d{2,6}\s+(?:[NSEW]\.?\s+)?[A-Z][A-Za-z0-9.'-]+(?:\s+[A-Z][A-Za-z0-9.'-]+){0,3}\s+(?:St|Street|Ave|Avenue|Blvd|Boulevard|Rd|Road|Dr|Drive|Ln|Lane|Way|Pl|Place|Ct|Court|Hwy|Highway)\b/g;
+function extractListings(html) {
+  const text = strip(html);
+  const out = [];
+  const addrs = text.match(ADDR) || [];
+  for (const a of [...new Set(addrs)].slice(0, 40)) {
+    const i = text.indexOf(a);
+    const window = text.slice(Math.max(0, i - 120), i + 180);
+    const pm = window.match(/\$\s?([0-9]{3,}(?:,[0-9]{3})+)/);
+    const price = pm ? +pm[1].replace(/,/g, '') : null;
+    if (price && price >= 100000 && price <= 500000000) out.push({ address: a.trim(), price });
+  }
+  // dedupe by address
+  const seen = new Set(); return out.filter(l => !seen.has(l.address.toLowerCase()) && seen.add(l.address.toLowerCase()));
+}
+
+(async () => {
+  const brokers = (await db.pool.query(
+    `SELECT id, name, website FROM broker WHERE website IS NOT NULL AND website !~* 'crexi' AND website !~* $1 LIMIT $2`,
+    [BIG.source, LIMIT])).rows;
+  console.log(`\n== Backfill pilot: ${brokers.length} boutique broker sites · ${APPLY ? 'APPLY (writing)' : 'DRY-RUN'} ==\n`);
+  let totalFound = 0, wrote = 0, blocked = 0;
+  for (const b of brokers) {
+    const r = await fetchSite(b.website);
+    if (!r.ok) { console.log(`  ✗ ${b.name} — ${b.website} [${r.status}${r.err ? ' ' + r.err : ''}] (Phase-2 openclaw)`); blocked++; continue; }
+    const listings = extractListings(r.html);
+    totalFound += listings.length;
+    console.log(`  ${listings.length ? '✓' : '·'} ${b.name} — ${b.website} → ${listings.length} listing(s)`);
+    listings.slice(0, 4).forEach(l => console.log(`       $${l.price.toLocaleString()}  ${l.address}`));
+    if (APPLY && listings.length) {
+      for (const l of listings) {
+        // insert listing (tagged reversible) + link to broker; dedup by address+broker
+        const ins = await db.pool.query(
+          `INSERT INTO listing (id, address, price, type, source, created_at)
+           VALUES (gen_random_uuid()::text, $1, $2, 'Commercial', 'broker-site', now())
+           ON CONFLICT DO NOTHING RETURNING id`, [l.address, l.price]).catch(() => ({ rows: [] }));
+        const lid = ins.rows[0]?.id;
+        if (lid) { await db.pool.query(`INSERT INTO broker_listing (broker_id, listing_id, role) VALUES ($1,$2,'agent') ON CONFLICT DO NOTHING`, [b.id, lid]).catch(() => {}); wrote++; }
+      }
+    }
+  }
+  console.log(`\n== ${brokers.length} sites · ${totalFound} listings extracted · ${blocked} blocked (need openclaw) · ${APPLY ? wrote + ' written (source=broker-site, reversible)' : 'DRY-RUN (no writes)'} ==`);
+  console.log(APPLY ? 'Undo: DELETE FROM broker_listing WHERE listing_id IN (SELECT id FROM listing WHERE source=\'broker-site\'); DELETE FROM listing WHERE source=\'broker-site\';' : 'Re-run with --apply to write.');
+  process.exit(0);
+})();
← 31c55f5 Add crawl-broker-comps.js: $0 no-auth Crexi broker book-expa · back to Commercialrealestate · CRCP firms: roster brokers now deep-link to their canonical 84a87bc →