← back to Commercialrealestate
Add crawl-broker-comps.js: $0 no-auth Crexi broker book-expansion (fills broker_closed_listing, backfills global_id); +discover-broker-endpoint
31c55f548622202eecbafb1eaa3c4c5a6c693ccf · 2026-08-19 09:03:59 -0700 · Steve Abrams
Files touched
M public/contractors.htmlM public/firms.htmlM public/lending-grid.cssM public/lending-grid.jsA scripts/crawl-broker-comps.jsM scripts/discover-broker-endpoint.js
Diff
commit 31c55f548622202eecbafb1eaa3c4c5a6c693ccf
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Wed Aug 19 09:03:59 2026 -0700
Add crawl-broker-comps.js: $0 no-auth Crexi broker book-expansion (fills broker_closed_listing, backfills global_id); +discover-broker-endpoint
---
public/contractors.html | 3 +-
public/firms.html | 64 ++++++++++++++-----
public/lending-grid.css | 5 ++
public/lending-grid.js | 1 +
scripts/crawl-broker-comps.js | 121 ++++++++++++++++++++++++++++++++++++
scripts/discover-broker-endpoint.js | 2 +-
6 files changed, 179 insertions(+), 17 deletions(-)
diff --git a/public/contractors.html b/public/contractors.html
index faa8636..9772c40 100644
--- a/public/contractors.html
+++ b/public/contractors.html
@@ -255,7 +255,8 @@ function passLocal(c){
return true;
}
function filteredSorted(){
- let rows=DATA.filter(passLocal);
+ // #2 data hygiene — drop junk/placeholder CSLB rows (a business name with no alphanumeric char, e.g. ".").
+ let rows=DATA.filter(passLocal).filter(c=>c.business_name && /[A-Za-z0-9]/.test(c.business_name));
rows.sort((a,b)=>{let x=a[sortKey],y=b[sortKey];const xm=(x==null||x===''),ym=(y==null||y==='');
if(xm&&ym)return 0;if(xm)return 1;if(ym)return -1;
if(DATE_KEYS.has(sortKey)){const dx=new Date(x).getTime(),dy=new Date(y).getTime();
diff --git a/public/firms.html b/public/firms.html
index 7587a21..94362b9 100644
--- a/public/firms.html
+++ b/public/firms.html
@@ -62,23 +62,57 @@ window.GRID_CONFIG = {
{ k: 'activity', l: 'Listing activity', derive: r => r.listings >= 10 ? '10+ listings' : r.listings >= 1 ? '1–9 listings' : 'no active listings', limit: 4 },
{ k: 'mix', l: 'Roster mix', derive: r => r.commercial > 0 && r.residential > 0 ? 'commercial + residential' : r.commercial > 0 ? 'commercial-only' : 'residential-only', limit: 4 },
],
- // Click a firm → drill into its full broker roster + active listings (via /api/firm),
- // instead of the generic aggregate-field dump. Turns the directory into a navigable one.
- detail: async (firm, h) => {
- const esc = h.esc, money = h.money;
- const d = await fetch('/api/firm?name=' + encodeURIComponent(firm.firm)).then(x => x.json());
- if (d.error) throw new Error(d.error);
+ // #2 data hygiene — drop junk/placeholder firm names (a name with no alphanumeric char) before they clutter a sort.
+ rowOk: r => !!(r.firm && /[A-Za-z0-9]/.test(r.firm)),
+ // #1 firm → broker → property drill (handled by the __firmsUI module below so the "← back" nav works).
+ detail: firm => window.__firmsUI.openFirm(firm.firm),
+};
+</script>
+<script>
+// firm → broker → property drill for the detail modal. buildFirm renders the roster (each broker
+// clickable) + firm listings; clicking a broker renders THEIR listings with a "← back to firm" link.
+window.__firmsUI = (function () {
+ const esc = s => String(s == null ? '' : s).replace(/&/g, '&').replace(/</g, '<').replace(/"/g, '"');
+ const money = n => (n == null || n === '') ? '' : '$' + Number(n).toLocaleString();
+ const norm = u => /^https?:/.test(u) ? u : 'https://' + u;
+ let CURFIRM = null;
+ const listRow = l => `<div class="litem"><span class="m">${esc(l.address || '—')}${l.city ? ', ' + esc(l.city) : ''}</span><span>${l.price ? money(l.price) : (l.sold_price ? money(l.sold_price) + ' sold' : '')}${l.units ? ' · ' + l.units + 'u' : ''}${l.type ? ' · ' + esc(l.type) : ''}${l.sold_date ? ' · ' + esc(String(l.sold_date).slice(0, 10)) : ''}</span></div>`;
+ async function buildFirm(name) {
+ const d = await fetch('/api/firm?name=' + encodeURIComponent(name)).then(x => x.json());
+ if (d.error) return `<h3>${esc(name)}</h3><div class="sec">Detail unavailable — ${esc(d.error)}</div>`;
const agg = d.agg || {};
const roster = (d.roster || []).map(b =>
- `<div class="litem"><span class="m">${esc(b.name)}${b.agent_type ? ' · ' + esc(b.agent_type) : ''}</span><span>${b.phone ? '☎ <a class="dl" href="tel:' + esc(b.phone) + '">' + esc(b.phone) + '</a> ' : ''}${b.email ? '✉ <a class="dl" href="mailto:' + esc(b.email) + '">' + esc(b.email) + '</a> ' : ''}${b.listings && +b.listings ? '· ' + esc(b.listings) + ' listings' : ''}</span></div>`).join('');
- const listings = (d.current || []).slice(0, 25).map(l =>
- `<div class="litem"><span class="m">${esc(l.address || '—')}${l.city ? ', ' + esc(l.city) : ''}</span><span>${l.price ? money(l.price) : ''}${l.units ? ' · ' + l.units + 'u' : ''}${l.type ? ' · ' + esc(l.type) : ''}</span></div>`).join('');
- return `<h3>${esc(firm.firm)}</h3>` +
- `<div class="mfirm">Brokerage firm · ${agg.brokers || firm.brokers || 0} brokers (${agg.residential || 0} residential) · ${agg.phone || 0}☎ ${agg.email || 0}✉</div>` +
- `<div class="sec"><h4>Roster (${(d.roster || []).length})</h4>${roster || '<div class="miss">—</div>'}</div>` +
- (listings ? `<div class="sec"><h4>Active listings (top ${Math.min(25, (d.current || []).length)} of ${(d.current || []).length})</h4>${listings}</div>` : '');
- },
-};
+ `<div class="litem"><span class="m"><a class="dl brokerlink" data-bid="${esc(b.id)}">${esc(b.name)}</a>${b.agent_type ? ' · ' + esc(b.agent_type) : ''}</span><span>${b.phone ? '☎ <a class="dl" href="tel:' + esc(b.phone) + '">' + esc(b.phone) + '</a> ' : ''}${b.email ? '✉ <a class="dl" href="mailto:' + esc(b.email) + '">' + esc(b.email) + '</a> ' : ''}${b.listings && +b.listings ? '· ' + esc(b.listings) + ' listings' : ''}</span></div>`).join('');
+ const listings = (d.current || []).slice(0, 25).map(listRow).join('');
+ return `<h3>${esc(name)}</h3>` +
+ `<div class="mfirm">Brokerage firm · ${agg.brokers || 0} brokers (${agg.residential || 0} residential) · ${agg.phone || 0}☎ ${agg.email || 0}✉</div>` +
+ `<div class="sec"><h4>Roster (${(d.roster || []).length}) — click a broker for their listings</h4><div class="rosterscroll">${roster || '<div class="miss">—</div>'}</div></div>` +
+ (listings ? `<div class="sec"><h4>Firm active listings (top ${Math.min(25, (d.current || []).length)} of ${(d.current || []).length})</h4>${listings}</div>` : '');
+ }
+ async function buildBroker(id) {
+ const d = await fetch('/api/broker?id=' + encodeURIComponent(id)).then(x => x.json());
+ const back = CURFIRM ? `<div style="margin-bottom:8px"><a class="dl backfirm">← ${esc(CURFIRM)}</a></div>` : '';
+ if (d.error) return `${back}<div class="sec">Broker detail unavailable — ${esc(d.error)}</div>`;
+ const b = d.broker || {};
+ const contact = `${b.phone ? '☎ <a class="dl" href="tel:' + esc(b.phone) + '">' + esc(b.phone) + '</a> ' : ''}${b.email ? '✉ <a class="dl" href="mailto:' + esc(b.email) + '">' + esc(b.email) + '</a>' : ''}` || '—';
+ const cur = (d.current || []).map(listRow).join('');
+ const closed = (d.closed || []).map(listRow).join('');
+ return back + `<h3>${esc(b.name || '—')}</h3>` +
+ `<div class="mfirm">${esc(b.title || b.agent_type || 'Broker')}${b.firm ? ' · ' + esc(b.firm) : ''}</div>` +
+ `<div class="sec"><div class="litem"><span class="m">Contact</span><span>${contact || '—'}</span></div>` +
+ (b.website ? `<div class="litem"><span class="m">Website</span><span><a class="dl" href="${esc(norm(b.website))}" target="_blank" rel="noopener noreferrer">${esc(b.website)}↗</a></span></div>` : '') +
+ (b.dre_license ? `<div class="litem"><span class="m">DRE #</span><span>${esc(b.dre_license)}</span></div>` : '') + `</div>` +
+ `<div class="sec"><h4>Active listings (${(d.current || []).length})</h4><div class="rosterscroll">${cur || '<div class="miss">—</div>'}</div></div>` +
+ (closed ? `<div class="sec"><h4>Closed (${(d.closed || []).length})</h4><div class="rosterscroll">${closed}</div></div>` : '');
+ }
+ document.addEventListener('click', async e => {
+ const bl = e.target.closest('#mbody .brokerlink');
+ if (bl) { e.preventDefault(); const mb = document.getElementById('mbody'); mb.innerHTML = '<div class="sec" style="opacity:.6">Loading broker…</div>'; mb.innerHTML = await buildBroker(bl.dataset.bid); return; }
+ const bf = e.target.closest('#mbody .backfirm');
+ if (bf && CURFIRM) { e.preventDefault(); const mb = document.getElementById('mbody'); mb.innerHTML = '<div class="sec" style="opacity:.6">Loading…</div>'; mb.innerHTML = await buildFirm(CURFIRM); return; }
+ });
+ return { openFirm: name => { CURFIRM = name; return buildFirm(name); } };
+})();
</script>
<script src="/column-manager.js" defer></script>
<script src="/lending-grid.js" defer></script>
diff --git a/public/lending-grid.css b/public/lending-grid.css
index e8fa89e..2083b34 100644
--- a/public/lending-grid.css
+++ b/public/lending-grid.css
@@ -83,4 +83,9 @@ table.g a{color:var(--blue);text-decoration:none;} table.g a:hover{text-decorati
#modal .x{position:absolute;top:12px;right:14px;background:none;border:0;color:var(--mut);font-size:22px;cursor:pointer;}
#modal .sec{margin-top:14px;} #modal .sec h4{margin:0 0 6px;font-size:12px;text-transform:uppercase;letter-spacing:.5px;color:var(--mut);}
#modal .litem{display:flex;justify-content:space-between;gap:14px;padding:5px 0;border-bottom:1px solid var(--line2,#20262f);font-size:13px;} #modal .litem .m{color:var(--mut);}
+#modal .litem a.dl{color:var(--blue);text-decoration:none;} #modal .litem a.dl:hover{text-decoration:underline;}
+/* firm→broker drill: cap a long roster so the modal stays compact (Steve refine #3) */
+.rosterscroll{max-height:340px;overflow:auto;margin:2px -4px 0;padding:0 4px;}
+.rosterscroll::-webkit-scrollbar{width:8px;} .rosterscroll::-webkit-scrollbar-thumb{background:var(--line);border-radius:4px;}
+a.brokerlink{cursor:pointer;} a.backfirm{cursor:pointer;color:var(--gold);text-decoration:none;font-size:12.5px;} a.backfirm:hover{text-decoration:underline;}
@media(max-width:820px){ .rail{display:none;} .tblwrap,.grid{margin-left:10px;margin-right:10px;} }
diff --git a/public/lending-grid.js b/public/lending-grid.js
index b95a177..453061f 100644
--- a/public/lending-grid.js
+++ b/public/lending-grid.js
@@ -298,6 +298,7 @@
initControls();
fetch(CFG.api).then(r => r.json()).then(d => {
DATA = (CFG.dataKey ? d[CFG.dataKey] : (d.rows || d.data)) || [];
+ if (typeof CFG.rowOk === 'function') DATA = DATA.filter(CFG.rowOk); // drop junk/placeholder rows before they clutter a sort
if (CFG.demo != null || d.meta) { const dm = $('#demoNote'); if (dm && d.meta) dm.textContent = `${d.meta.source || ''} · ${DATA.length.toLocaleString()} records · retrieved ${dt(d.meta.retrieved_at)}`; }
autoCols(); buildSortSel(); buildRail(); render();
}).catch(() => { $('#count').textContent = 'failed to load ' + CFG.api; });
diff --git a/scripts/crawl-broker-comps.js b/scripts/crawl-broker-comps.js
new file mode 100644
index 0000000..c8bafbe
--- /dev/null
+++ b/scripts/crawl-broker-comps.js
@@ -0,0 +1,121 @@
+// crawl-broker-comps.js — $0 NO-AUTH firm/broker book-expansion via Crexi's PUBLIC API.
+//
+// Steve's ask (2026-08-19): "for every listing find the broker firm and pull listings from there…
+// use crexi to find firms and brokers." Discovery (2026-08-19) proved two Crexi endpoints answer a
+// plain GET with NO auth, NO browser, NO Browserbase — i.e. $0 and ungated:
+// • GET /assets/<assetId>/brokers -> broker {id, globalId, publicProfileId, brokerage, numberOfAssets}
+// • GET /brokers/<globalId>/comps?count=<n> -> that broker's FULL closed-transaction book {totalCount, data[]}
+//
+// So we can, for EVERY Crexi listing we hold, resolve its broker(s), learn each broker's globalId,
+// and pull their entire closed-deal book — folding it into broker_closed_listing (purpose-built,
+// currently 0 rows) + backfilling broker.global_id / public_profile_id. This is the CLOSED-DEAL half
+// of the expansion; ACTIVE for-sale inventory needs POST /assets/search (bearer) — see crawl-broker-listings.js.
+//
+// COST $0 (public GET). GATE: read-only external GETs + local `cre` DB writes only (reversible/internal,
+// git-tracked). LISTING_LIMIT caps the pilot; run unbounded once verified.
+//
+// Usage: LISTING_LIMIT=50 node scripts/crawl-broker-comps.js (0 = all Crexi listings)
+'use strict';
+const fs = require('fs');
+const path = require('path');
+const db = require('./db/brokers-db');
+
+const ROOT = path.join(__dirname, '..');
+const LIMIT = parseInt(process.env.LISTING_LIMIT || '50', 10);
+const CONC = parseInt(process.env.CONC || '6', 10);
+const API = 'https://api.crexi.com';
+const H = { accept: 'application/json' };
+const sleep = (ms) => new Promise(r => setTimeout(r, ms));
+
+async function getJSON(url, tries = 3) {
+ for (let i = 0; i < tries; i++) {
+ try { const r = await fetch(url, { headers: H });
+ if (r.status === 429) { await sleep(1500 * (i + 1)); continue; }
+ if (!r.ok) return null; return await r.json();
+ } catch { await sleep(500 * (i + 1)); }
+ }
+ return null;
+}
+// tiny concurrency pool
+async function pool(items, n, fn) {
+ const out = []; let idx = 0;
+ await Promise.all(Array.from({ length: n }, async () => {
+ while (idx < items.length) { const i = idx++; out[i] = await fn(items[i], i); if (i % 50 === 0) process.stderr.write(` ${i}/${items.length}\r`); }
+ }));
+ return out;
+}
+
+(async () => {
+ // idempotent schema adds (reversible)
+ await db.pool.query(`ALTER TABLE broker ADD COLUMN IF NOT EXISTS global_id text`);
+ await db.pool.query(`ALTER TABLE broker ADD COLUMN IF NOT EXISTS public_profile_id text`);
+ await db.pool.query(`ALTER TABLE broker_closed_listing ADD COLUMN IF NOT EXISTS crexi_asset_id text`);
+ await db.pool.query(`CREATE UNIQUE INDEX IF NOT EXISTS ux_closed_broker_asset ON broker_closed_listing(broker_id, crexi_asset_id)`);
+
+ const doc = JSON.parse(fs.readFileSync(path.join(ROOT, 'data', 'listings.json'), 'utf8'));
+ let crx = doc.listings.filter(l => /^crx\d+/.test(l.id)).map(l => l.id.replace(/^crx/, ''));
+ if (LIMIT) crx = crx.slice(0, LIMIT);
+ console.log(`Phase 1 — resolving brokers for ${crx.length} Crexi listings via /assets/<id>/brokers ($0)`);
+
+ // Phase 1: listing -> brokers, collect distinct brokers by globalId
+ const brokers = new Map(); // globalId -> {globalId,numericId,ppid,name,firm,book}
+ await pool(crx, CONC, async (assetId) => {
+ const arr = await getJSON(`${API}/assets/${assetId}/brokers`);
+ if (!Array.isArray(arr)) return;
+ for (const b of arr) {
+ if (!b.globalId) continue;
+ const name = [b.firstName, b.lastName].filter(Boolean).join(' ').trim();
+ if (!brokers.has(b.globalId)) brokers.set(b.globalId, {
+ globalId: b.globalId, numericId: String(b.id || ''), ppid: b.publicProfileId || null,
+ name, firm: (b.brokerage && b.brokerage.name) || null, book: b.numberOfAssets || 0
+ });
+ }
+ });
+ console.log(`\n distinct brokers resolved: ${brokers.size} (book sum ${[...brokers.values()].reduce((s, b) => s + b.book, 0)})`);
+
+ // Upsert broker identity (name+firm graph) + backfill global_id / public_profile_id
+ for (const b of brokers.values()) {
+ if (!b.name) continue;
+ const bid = await db.upsertBroker({ name: b.name, firm: b.firm, crexi_id: b.numericId || null, total_assets: b.book || null, source: 'crexi' }).catch(() => null);
+ if (bid) await db.pool.query(`UPDATE broker SET global_id=COALESCE(global_id,$2), public_profile_id=COALESCE(public_profile_id,$3) WHERE id=$1`, [bid, b.globalId, b.ppid]).catch(() => {});
+ b.dbId = bid;
+ }
+
+ // Phase 2: per broker -> full closed-comps book (public GET, $0)
+ console.log(`Phase 2 — pulling closed-comps books for ${brokers.size} brokers ($0)`);
+ let compsInserted = 0, brokersWithComps = 0;
+ const list = [...brokers.values()].filter(b => b.dbId);
+ await pool(list, CONC, async (b) => {
+ const want = Math.min(500, Math.max(3, b.book || 100));
+ const j = await getJSON(`${API}/brokers/${b.globalId}/comps?brokerGlobalId=${b.globalId}&count=${want}`);
+ const data = j && j.data; if (!Array.isArray(data) || !data.length) return;
+ brokersWithComps++;
+ for (const c of data) {
+ const loc = c.location || {};
+ const addr = c.name || loc.fullAddress || loc.address || '';
+ const city = loc.city || null;
+ const price = c.salePrice || null;
+ const sold = c.closedDate ? String(c.closedDate).slice(0, 10) : null;
+ const type = (c.assetTypes && c.assetTypes[0]) || null;
+ const src = `https://api.crexi.com/brokers/${b.globalId}/comps`;
+ const r = await db.pool.query(
+ `INSERT INTO broker_closed_listing(broker_id, crexi_asset_id, address, city, sold_price, sold_date, type, source, created_at)
+ VALUES($1,$2,$3,$4,$5,$6,$7,$8, now())
+ ON CONFLICT(broker_id, crexi_asset_id) DO NOTHING`,
+ [b.dbId, String(c.id || c.assetId || ''), addr, city, price, sold, type, src]).catch(() => ({ rowCount: 0 }));
+ compsInserted += r.rowCount;
+ }
+ });
+
+ const totalClosed = (await db.pool.query('select count(*) c from broker_closed_listing')).rows[0].c;
+ const report = {
+ ran_at: new Date().toISOString(), cost_usd: 0, mode: LIMIT ? `PILOT (${LIMIT} listings)` : 'FULL',
+ listings_scanned: crx.length, brokers_resolved: brokers.size, brokers_with_globalid: [...brokers.values()].filter(b => b.globalId).length,
+ brokers_with_comps: brokersWithComps, comps_inserted_this_run: compsInserted, broker_closed_listing_total: +totalClosed
+ };
+ fs.writeFileSync(path.join(ROOT, 'data', 'raw', `broker-comps-${Date.now()}.json`), JSON.stringify(report, null, 2));
+ console.log(`\n=== DONE ($0) ===`);
+ console.log(report);
+ await db.pool.end().catch(() => {});
+ process.exit(0);
+})().catch(e => { console.error('FATAL', e); process.exit(1); });
diff --git a/scripts/discover-broker-endpoint.js b/scripts/discover-broker-endpoint.js
index d09dc63..dea6257 100644
--- a/scripts/discover-broker-endpoint.js
+++ b/scripts/discover-broker-endpoint.js
@@ -46,7 +46,7 @@ const PROFILES = (process.env.PROFILES || 'george-ouzounian-georgeouzo,ash-ghava
finally { if (browser) try { await browser.close(); } catch {} }
hits.sort((a, b) => b.listLen - a.listLen);
- fs.writeFileSync(path.join(ROOT, 'data', 'raw', 'discover-broker-endpoint.json'), JSON.stringify({ slugs: SLUGS, bearer: !!auth, hits }, null, 2));
+ fs.writeFileSync(path.join(ROOT, 'data', 'raw', 'discover-broker-endpoint.json'), JSON.stringify({ slugs: PROFILES, bearer: !!auth, hits }, null, 2));
console.log('\nendpoints carrying listings (sale/lease/assets), richest first:');
for (const h of hits.filter(x => x.sale || x.lease || x.assets || x.listLen > 1).slice(0, 15))
console.log(` [${h.method}] ${h.path} len=${h.listLen} sale=${h.sale} lease=${h.lease} keys=${Array.isArray(h.keys) ? h.keys.join(',') : h.keys}`);
← 36bec4d agent profiles: /api/agent-profile?name= (our broker graph -
·
back to Commercialrealestate
·
backfill: broker-website listing scraper (plain-fetch pilot 7e3bea3 →