backfill option-3: openclaw real-Chrome (beats 403/JS) + qwen3:14b extraction of firm-site listings, attributed at FIRM level (role=firm-listing, reversible source=broker-site-oc) — Steve: build option 3, fan to top 200

86b8219d936394dadc7a027092dcbe0255b23dbe · 2026-08-19 09:29:32 -0700 · Steve Abrams
Files touched

A scripts/backfill-openclaw-llm.js
Diff

commit 86b8219d936394dadc7a027092dcbe0255b23dbe
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Wed Aug 19 09:29:32 2026 -0700

    backfill option-3: openclaw real-Chrome (beats 403/JS) + qwen3:14b extraction of firm-site listings, attributed at FIRM level (role=firm-listing, reversible source=broker-site-oc) — Steve: build option 3, fan to top 200
---
 scripts/backfill-openclaw-llm.js | 97 ++++++++++++++++++++++++++++++++++++++++
 1 file changed, 97 insertions(+)

diff --git a/scripts/backfill-openclaw-llm.js b/scripts/backfill-openclaw-llm.js
new file mode 100644
index 0000000..210f0ae
--- /dev/null
+++ b/scripts/backfill-openclaw-llm.js
@@ -0,0 +1,97 @@
+#!/usr/bin/env node
+// backfill-openclaw-llm.js — OPTION 3 (Steve 2026-08-19): real listings direct from broker FIRM
+// sites via openclaw real-Chrome (defeats 403/JS) + local qwen3:14b extraction ($0). Firm-site
+// listing GRIDS carry address+price but NOT the per-listing agent, so these are attributed at the
+// FIRM level (role='firm-listing', linked to every broker at that firm) and shown on agent profiles
+// as a clearly-labeled "Firm listings" section — never silently claimed as that one agent's book.
+// Reversible: everything tagged source='broker-site-oc'. Dry-run default; --apply to write.
+//
+// Usage: node scripts/backfill-openclaw-llm.js [--limit N] [--apply]
+const { execFileSync } = require('child_process');
+const db = require('./db/brokers-db');
+const OC = process.env.HOME + '/.npm-global/bin/openclaw';
+const OLLAMA = 'http://127.0.0.1:11434/api/generate';
+const LIMIT = +(process.argv.find(a => a.startsWith('--limit='))?.split('=')[1]) || 5;
+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 domainOf = u => { try { return new URL(/^https?:/i.test(u) ? u : 'https://' + u).hostname.replace(/^www\./, ''); } catch { return u; } };
+
+function oc(args, timeout = 45000) { try { return execFileSync(OC, args, { encoding: 'utf8', timeout, stdio: ['ignore', 'pipe', 'ignore'] }); } catch (e) { return null; } }
+function ocEval(fn) { const out = oc(['browser', 'evaluate', '--fn', fn]); if (!out) return null; try { return JSON.parse(out); } catch { return out; } }
+
+async function llmExtract(text) {
+  // feed the price-dense window (skip nav/cookie header) in <=7k chunks
+  const firstPrice = text.search(/\$[0-9]{3,}(,[0-9]{3})+/);
+  const body = firstPrice > 0 ? text.slice(Math.max(0, firstPrice - 200)) : text;
+  const out = [];
+  for (let i = 0; i < body.length && i < 21000; i += 7000) {
+    const chunk = body.slice(i, i + 7000);
+    if (!/\$[0-9]{3,}/.test(chunk)) continue;
+    const prompt = `From this real-estate broker listings text, extract each property listing. Return ONLY a JSON array (no prose/markdown), items {"address":string,"price":number,"type":string_or_null}. Skip anything without a real street address AND a price.\n\nTEXT:\n${chunk}`;
+    try {
+      const r = await fetch(OLLAMA, { method: 'POST', body: JSON.stringify({ model: 'qwen3:14b', prompt, stream: false, options: { temperature: 0 } }) }).then(x => x.json());
+      const raw = (r.response || '').replace(/<think>[\s\S]*?<\/think>/g, '').replace(/```json|```/g, '').trim();
+      const m = raw.match(/\[[\s\S]*\]/); if (!m) continue;
+      const arr = JSON.parse(m[0]);
+      for (const it of arr) {
+        const addr = String(it.address || '').trim();
+        const price = +String(it.price).replace(/[^0-9.]/g, '');
+        const houseNo = (addr.match(/^\d+/) || ['0'])[0];
+        if (addr.length >= 8 && price >= 100000 && price <= 500000000 && !/^0+$/.test(houseNo) && !/\b000\b/.test(addr))
+          out.push({ address: addr, price, type: it.type || 'Commercial' });
+      }
+    } catch (e) { /* skip chunk */ }
+  }
+  // dedupe by address
+  const seen = new Set(); return out.filter(l => { const k = l.address.toLowerCase(); return !seen.has(k) && seen.add(k); });
+}
+
+async function scrapeDomain(url) {
+  if (!oc(['browser', 'navigate', /^https?:/i.test(url) ? url : 'https://' + url])) return { err: 'nav-fail' };
+  // find the best listings page link
+  const links = ocEval(`() => [...document.querySelectorAll('a')].map(a=>({t:(a.textContent||'').trim().slice(0,40),h:a.href})).filter(l=>/listing|propert|for.?sale|inventory|available|our.?deals/i.test(l.t+' '+l.h)).slice(0,10)`);
+  const cand = (Array.isArray(links) ? links : []).map(l => l.h).filter(Boolean);
+  // prefer an /listings or /properties page; else stay on the homepage
+  const listUrl = cand.find(h => /listing|propert|for-?sale|inventory/i.test(h)) || null;
+  if (listUrl) oc(['browser', 'navigate', listUrl]);
+  const txt = ocEval(`() => document.body.innerText`);
+  const text = typeof txt === 'string' ? txt : (txt && (txt.result || txt.value)) || '';
+  if (!text || text.length < 400) return { err: 'no-text' };
+  const listings = await llmExtract(text);
+  return { listUrl: listUrl || url, listings };
+}
+
+(async () => {
+  // top SHARED firm domains (most agents per domain = highest yield/effort)
+  const rows = (await db.pool.query(
+    `SELECT b.firm_id, f.name AS firm, count(*) AS agents,
+            (array_agg(b.website ORDER BY length(b.website)))[1] AS website,
+            array_agg(b.id) AS broker_ids
+       FROM broker b JOIN firm f ON f.id=b.firm_id
+      WHERE b.website IS NOT NULL AND b.website !~* 'crexi' AND b.website !~* $1
+      GROUP BY b.firm_id, f.name HAVING count(*) >= 2
+      ORDER BY count(*) DESC LIMIT $2`, [BIG.source, LIMIT])).rows;
+  console.log(`\n== Option-3 openclaw+LLM backfill · ${rows.length} firm domains · ${APPLY ? 'APPLY' : 'DRY-RUN'} ==\n`);
+  let totFound = 0, wrote = 0;
+  for (const r of rows) {
+    process.stdout.write(`  ${r.firm} (${r.agents} agents) — ${domainOf(r.website)} … `);
+    const res = await scrapeDomain(r.website);
+    if (res.err) { console.log(`[${res.err}]`); continue; }
+    totFound += res.listings.length;
+    console.log(`${res.listings.length} firm listing(s)`);
+    res.listings.slice(0, 3).forEach(l => console.log(`       $${l.price.toLocaleString()}  ${l.address}`));
+    if (APPLY && res.listings.length) {
+      for (const l of res.listings) {
+        const ins = await db.pool.query(
+          `INSERT INTO listing (id,address,price,type,source,created_at) VALUES (gen_random_uuid()::text,$1,$2,$3,'broker-site-oc',now()) RETURNING id`,
+          [l.address, l.price, l.type]).catch(() => ({ rows: [] }));
+        const lid = ins.rows[0]?.id; if (!lid) continue;
+        for (const bid of r.broker_ids) await db.pool.query(`INSERT INTO broker_listing (broker_id,listing_id,role) VALUES ($1,$2,'firm-listing') ON CONFLICT DO NOTHING`, [bid, lid]).catch(() => {});
+        wrote++;
+      }
+    }
+  }
+  console.log(`\n== ${rows.length} firms · ${totFound} listings extracted · ${APPLY ? wrote + ' written (source=broker-site-oc, role=firm-listing, reversible)' : 'DRY-RUN'} ==`);
+  console.log(APPLY ? "Undo: DELETE FROM broker_listing WHERE listing_id IN (SELECT id FROM listing WHERE source='broker-site-oc'); DELETE FROM listing WHERE source='broker-site-oc';" : 'Re-run with --apply to write.');
+  process.exit(0);
+})();
← 200087f CRCP: extend 'agent opens its own page' — condos listing age · back to Commercialrealestate · CRCP: Showcase daily refresh now openclaw-first ($0), browse e77b85d →