backfill: garbage-address filter + unique-domain-only attribution (skip shared firm sites); applied 24 clean direct listings to 5 agents (reversible source=broker-site)

48a8e858605b84a1925f6ce6ca1b10e276de5923 · 2026-08-19 09:22:14 -0700 · Steve Abrams
Files touched

M scripts/backfill-broker-listings.js
Diff

commit 48a8e858605b84a1925f6ce6ca1b10e276de5923
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Wed Aug 19 09:22:14 2026 -0700

    backfill: garbage-address filter + unique-domain-only attribution (skip shared firm sites); applied 24 clean direct listings to 5 agents (reversible source=broker-site)
---
 scripts/backfill-broker-listings.js | 24 +++++++++++++++++++++---
 1 file changed, 21 insertions(+), 3 deletions(-)

diff --git a/scripts/backfill-broker-listings.js b/scripts/backfill-broker-listings.js
index 3e31b94..2d32600 100644
--- a/scripts/backfill-broker-listings.js
+++ b/scripts/backfill-broker-listings.js
@@ -18,7 +18,14 @@ 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';
+const _domCache = new Map(); // fetch each domain ONCE (many agents share a firm site — don't hammer)
+const domainOf = u => { try { return new URL(/^https?:/i.test(u) ? u : 'https://' + u).hostname.replace(/^www\./, ''); } catch { return u; } };
 async function fetchSite(url) {
+  const dom = domainOf(url);
+  if (_domCache.has(dom)) return _domCache.get(dom);
+  const r = await _fetchSite(url); _domCache.set(dom, r); return r;
+}
+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);
@@ -41,7 +48,11 @@ function extractListings(html) {
     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 });
+    const addr = a.trim();
+    // reject garbage: leading-zero / placeholder house numbers ("000 Street ..."), too-short
+    const houseNo = (addr.match(/^\d+/) || ['0'])[0];
+    const bad = /^0+$/.test(houseNo) || /^0+\s/.test(addr) || /\b000\b/.test(addr) || addr.length < 8;
+    if (price && price >= 100000 && price <= 500000000 && !bad) out.push({ address: addr, price });
   }
   // dedupe by address
   const seen = new Set(); return out.filter(l => !seen.has(l.address.toLowerCase()) && seen.add(l.address.toLowerCase()));
@@ -51,6 +62,12 @@ function extractListings(html) {
   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;
+  // Which domains are shared by >1 broker? Their listings are FIRM-level, not one agent's —
+  // don't attribute those to a single agent (the misattribution trap). Only unique domains get linked.
+  const shareRows = (await db.pool.query(
+    `SELECT website FROM broker WHERE website IS NOT NULL AND website !~* 'crexi' AND website !~* $1`, [BIG.source])).rows;
+  const domCount = {}; for (const r of shareRows) { const d = domainOf(r.website); domCount[d] = (domCount[d] || 0) + 1; }
+  const isShared = url => (domCount[domainOf(url)] || 0) > 1;
   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) {
@@ -58,9 +75,10 @@ function extractListings(html) {
     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)`);
+    const shared = isShared(b.website);
+    console.log(`  ${listings.length ? '✓' : '·'} ${b.name} — ${b.website} → ${listings.length} listing(s)${shared && listings.length ? ' [SHARED firm site — not attributed]' : ''}`);
     listings.slice(0, 4).forEach(l => console.log(`       $${l.price.toLocaleString()}  ${l.address}`));
-    if (APPLY && listings.length) {
+    if (APPLY && listings.length && !shared) {  // only attribute UNIQUE-domain (personal-site) listings to the agent
       for (const l of listings) {
         // insert listing (tagged reversible) + link to broker; dedup by address+broker
         const ins = await db.pool.query(
← b422f11 CRCP: every agent opens its own page — link agent names in t · back to Commercialrealestate · CRCP: backfill real broker phones for Showcase listings — FR 7361844 →