← back to Commercialrealestate
TK-10709: Crexi two-level openclaw crawl (grid->detail) -> broker graph master DB, per-agent attribution (name+firm confirmed, creates new brokers), source=crexi reversible, dedup by address; recon-verified selectors
af11701671e844a8df864a88c2bdb67946de01d9 · 2026-08-19 10:09:43 -0700 · Steve Abrams
Files touched
A scripts/scrape-crexi-loopnet.js
Diff
commit af11701671e844a8df864a88c2bdb67946de01d9
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Wed Aug 19 10:09:43 2026 -0700
TK-10709: Crexi two-level openclaw crawl (grid->detail) -> broker graph master DB, per-agent attribution (name+firm confirmed, creates new brokers), source=crexi reversible, dedup by address; recon-verified selectors
---
scripts/scrape-crexi-loopnet.js | 114 ++++++++++++++++++++++++++++++++++++++++
1 file changed, 114 insertions(+)
diff --git a/scripts/scrape-crexi-loopnet.js b/scripts/scrape-crexi-loopnet.js
new file mode 100644
index 0000000..8dd1ac8
--- /dev/null
+++ b/scripts/scrape-crexi-loopnet.js
@@ -0,0 +1,114 @@
+#!/usr/bin/env node
+// scrape-crexi-loopnet.js — TK-10709. Our-own openclaw two-level crawl of Crexi (LoopNet pluggable)
+// -> broker graph MASTER DB, with CORRECT per-agent attribution from the detail page.
+// Recon (2026-08-19): search grid gives detail URLs + price + street address per card; the DETAIL
+// page carries "Name <agent> / Brokerage <firm> / Phone <p> / License <lic>". We match the agent to
+// a broker (create if new — the graph is the master), insert the listing tied to that agent
+// (role='agent'), source='crexi', reversible, deduped by normalized address. Never links out to Crexi.
+// $0: openclaw local Chrome. Dry-run default. Usage:
+// node scripts/scrape-crexi-loopnet.js [--type Multifamily] [--limit 30] [--apply]
+const { execFileSync } = require('child_process');
+const db = require('./db/brokers-db');
+const OC = process.env.HOME + '/.npm-global/bin/openclaw';
+const TYPE = (process.argv.find(a => a.startsWith('--type='))?.split('=')[1]) || 'Multifamily';
+const LIMIT = +(process.argv.find(a => a.startsWith('--limit='))?.split('=')[1]) || 25; // detail pages / run (polite)
+const APPLY = process.argv.includes('--apply');
+const SEARCH = `https://www.crexi.com/properties?types%5B%5D=${encodeURIComponent(TYPE)}&address_value=Los%20Angeles,%20CA,%20USA`;
+
+const sleep = ms => new Promise(r => setTimeout(r, ms));
+function oc(args, timeout = 45000) { try { return execFileSync(OC, args, { encoding: 'utf8', timeout, stdio: ['ignore', 'pipe', 'ignore'] }); } catch { return null; } }
+function ocEval(fn) { const out = oc(['browser', 'evaluate', '--fn', fn]); if (!out) return null; try { return JSON.parse(out); } catch { return out; } }
+const nAddr = s => String(s || '').toLowerCase().replace(/\b(street|st|avenue|ave|boulevard|blvd|road|rd|drive|dr|lane|ln|place|pl|court|ct|suite|ste|unit|#|north|south|east|west|n|s|e|w)\b/g, '').replace(/[^a-z0-9]+/g, '');
+const nName = s => String(s || '').toLowerCase().replace(/[^a-z0-9]+/g, '');
+const nFirm = s => String(s || '').toLowerCase().replace(/[^a-z0-9]+/g, '').replace(/(inc|llc|corp|corporation|company|co|realty|realestate|group|properties|partners|associates)$/, '');
+
+// ── STEP 1: search grid -> [{url, price, address}] (scroll the SPA to load all cards) ──
+async function gridSweep() {
+ if (!oc(['browser', 'navigate', SEARCH])) return [];
+ for (let i = 0; i < 5; i++) { oc(['browser', 'evaluate', '--fn', '() => window.scrollTo(0, document.body.scrollHeight)']); await sleep(1800); }
+ const cards = ocEval(`() => {
+ const out=[]; const seen=new Set();
+ for (const a of document.querySelectorAll('a[href*="/properties/"]')) {
+ const m=a.href.match(/\\/properties\\/(\\d+)\\//); if(!m) continue; const id=m[1]; if(seen.has(id)) continue;
+ let el=a, card=null; for(let i=0;i<7&⪙i++){ el=el.parentElement; if(el && /\\$[0-9]{3,}/.test(el.textContent||'')){card=el;break;} }
+ if(!card) continue; seen.add(id);
+ const txt=(card.textContent||'').replace(/\\s+/g,' ').trim();
+ const price=(txt.match(/\\$([0-9]{3,}(?:,[0-9]{3})+)/)||[])[1];
+ const addr=(txt.match(/\\d{2,6}\\s+[A-Z0-9][A-Za-z0-9.'-]*(?:\\s+[A-Za-z0-9.'-]+){0,4}\\s+(?:St|Street|Ave|Avenue|Blvd|Boulevard|Rd|Road|Dr|Drive|Ln|Lane|Way|Pl|Place|Ct|Court)\\b[^,]*,?\\s*[A-Za-z ]*,?\\s*CA\\s*\\d{5}/)||[])[0];
+ const cap=(txt.match(/([0-9.]+)%\\s*CAP/i)||[])[1];
+ out.push({ url:'https://www.crexi.com/properties/'+id+'/', id, price: price?+price.replace(/,/g,''):null, address: addr||null, cap: cap?+cap:null });
+ }
+ return out;
+ }`);
+ return Array.isArray(cards) ? cards : [];
+}
+
+// ── STEP 2: detail page -> {agent, firm, phone, license, address(fallback), price(fallback)} ──
+function detailAgent(url) {
+ if (!oc(['browser', 'navigate', url])) return null;
+ const d = ocEval(`() => { const t=document.body.innerText||''; return { t: t.slice(0, 8000) }; }`);
+ const t = (d && d.t) || ''; if (!t) return null;
+ // structured broker block: "Brokerage <firm> ... Phone <p> ... Name <Agent Name> License <lic>"
+ const agent = (t.match(/Name\s+([A-Z][A-Za-z.'-]+(?:\s+[A-Z][A-Za-z.'-]+){1,2})\s+License/) || [])[1]
+ || (t.match(/Listed by\s+([A-Z][A-Za-z.'&,\- ]{3,40})/) || [])[1];
+ const firm = (t.match(/Brokerage\s+([A-Za-z0-9.'&,\- ]{2,50}?)\s+(?:Brokerage )?Phone/) || [])[1]
+ || (t.match(/Listed by\s+([A-Za-z0-9.'&,\- ]{2,50}?)\s+(?:Valuation|Login|Sign)/) || [])[1];
+ const phone = (t.match(/Phone\s*([0-9][0-9\-().\s]{8,16}[0-9])/) || [])[1];
+ const license = (t.match(/License\s*([0-9]{6,9})/) || [])[1];
+ return { agent: agent && agent.trim(), firm: firm && firm.trim(), phone: phone && phone.trim(), license: license && license.trim() };
+}
+
+// ── resolve agent -> broker id (create if new; the graph is the master) ──
+async function resolveBroker(agent, firm) {
+ if (!agent) return null;
+ const rows = (await db.pool.query(
+ `SELECT b.id, f.name AS firm FROM broker b LEFT JOIN firm f ON f.id=b.firm_id WHERE lower(b.name)=lower($1)`, [agent])).rows;
+ // prefer a name match whose firm agrees (never the wrong same-name person)
+ let hit = rows.find(r => firm && r.firm && nFirm(r.firm) === nFirm(firm)) || (rows.length === 1 ? rows[0] : null);
+ if (hit) return hit.id;
+ if (!APPLY) return 'NEW';
+ // create the broker (+firm) — master DB grows with real Crexi agents
+ let firmId = null;
+ if (firm) {
+ const fr = (await db.pool.query(`SELECT id FROM firm WHERE lower(name)=lower($1) LIMIT 1`, [firm])).rows[0]
+ || (await db.pool.query(`INSERT INTO firm (name, created_at) VALUES ($1, now()) RETURNING id`, [firm])).rows[0];
+ firmId = fr && fr.id;
+ }
+ const ins = (await db.pool.query(
+ `INSERT INTO broker (id, name, firm_id, phone, source, agent_type, created_at)
+ VALUES (gen_random_uuid()::text, $1, $2, $3, 'crexi', 'commercial', now()) RETURNING id`,
+ [agent, firmId, null]).catch(() => ({ rows: [] }))).rows[0];
+ return ins ? ins.id : null;
+}
+
+(async () => {
+ console.log(`\n== Crexi crawl · ${TYPE} · LA · ${APPLY ? 'APPLY' : 'DRY-RUN'} · limit ${LIMIT} detail pages ==\n`);
+ const cards = await gridSweep();
+ console.log(`grid: ${cards.length} listing cards found\n`);
+ const seen = new Set(); let wrote = 0, matched = 0, created = 0, i = 0;
+ for (const c of cards) {
+ if (i >= LIMIT) break; i++;
+ const det = detailAgent(c.url); await sleep(1500); // polite throttle
+ const agent = det && det.agent, firm = det && det.firm;
+ const addr = c.address; const price = c.price;
+ if (!addr || !price || !agent) { console.log(` · [skip] ${addr || c.id} — ${!agent ? 'no agent' : 'no addr/price'}`); continue; }
+ if (seen.has(nAddr(addr))) continue; seen.add(nAddr(addr));
+ const bid = await resolveBroker(agent, firm);
+ const tag = bid === 'NEW' ? '[would create broker]' : bid ? '' : '[no broker]';
+ console.log(` ✓ $${price.toLocaleString()} ${addr} — ${agent} @ ${firm || '?'} ${tag}`);
+ if (bid === 'NEW') created++; else if (bid) matched++;
+ if (APPLY && bid && bid !== 'NEW') {
+ // dedup by normalized address across ALL sources
+ const dup = (await db.pool.query(`SELECT 1 FROM listing WHERE lower(regexp_replace(address,'[^a-zA-Z0-9]','','g')) LIKE $1 LIMIT 1`, ['%' + nAddr(addr) + '%'])).rows[0];
+ if (dup) { continue; }
+ const lid = (await db.pool.query(
+ `INSERT INTO listing (id,address,price,type,cap_rate,firm_name,source,created_at)
+ VALUES (gen_random_uuid()::text,$1,$2,$3,$4,$5,'crexi',now()) RETURNING id`,
+ [addr, price, TYPE, c.cap, firm]).catch(() => ({ rows: [] }))).rows[0];
+ if (lid) { await db.pool.query(`INSERT INTO broker_listing (broker_id,listing_id,role) VALUES ($1,$2,'agent') ON CONFLICT DO NOTHING`, [bid, lid.id]).catch(() => {}); wrote++; }
+ }
+ }
+ console.log(`\n== ${i} detail pages · ${matched} matched existing brokers · ${created} new brokers${APPLY ? ' created' : ' (would create)'} · ${APPLY ? wrote + ' listings written (source=crexi, role=agent, reversible)' : 'DRY-RUN'} ==`);
+ console.log(APPLY ? "Undo: DELETE FROM broker_listing WHERE listing_id IN (SELECT id FROM listing WHERE source='crexi' AND created_at::date=CURRENT_DATE); DELETE FROM listing WHERE source='crexi' AND created_at::date=CURRENT_DATE;" : 'Re-run with --apply to write.');
+ process.exit(0);
+})();
← dddefaf spec: robust Crexi/LoopNet agent-attributed scraper (opencla
·
back to Commercialrealestate
·
auto-data-snapshot: 2026-08-19T10:08:05 (1 data files) — dat 58c2ad4 →