← back to Commercialrealestate
CRE: broker/agent relationship graph — cre Postgres DB (firm/broker/listing + co-list edges), Crexi /assets/<id>/brokers capture, /api/graph, vis-network mind-map at /brokers.html (verified: 68 brokers/52 firms/16 co-list edges from 60-listing sample)
08e290a50285ecc92ba933394eb2f06f50c92a8e · 2026-06-27 16:31:11 -0700 · Steve
Files touched
A public/brokers.htmlM public/index.htmlA scripts/db/broker-schema.sqlA scripts/db/brokers-db.jsA scripts/fetch-brokers.jsA scripts/probe-brokers.jsM scripts/serve.js
Diff
commit 08e290a50285ecc92ba933394eb2f06f50c92a8e
Author: Steve <steve@designerwallcoverings.com>
Date: Sat Jun 27 16:31:11 2026 -0700
CRE: broker/agent relationship graph — cre Postgres DB (firm/broker/listing + co-list edges), Crexi /assets/<id>/brokers capture, /api/graph, vis-network mind-map at /brokers.html (verified: 68 brokers/52 firms/16 co-list edges from 60-listing sample)
---
public/brokers.html | 114 +++++++++++++++++++++++++++++++++++++++++++
public/index.html | 1 +
scripts/db/broker-schema.sql | 65 ++++++++++++++++++++++++
scripts/db/brokers-db.js | 78 +++++++++++++++++++++++++++++
scripts/fetch-brokers.js | 86 ++++++++++++++++++++++++++++++++
scripts/probe-brokers.js | 67 +++++++++++++++++++++++++
scripts/serve.js | 13 +++++
7 files changed, 424 insertions(+)
diff --git a/public/brokers.html b/public/brokers.html
new file mode 100644
index 0000000..3b15bff
--- /dev/null
+++ b/public/brokers.html
@@ -0,0 +1,114 @@
+<!doctype html>
+<html lang="en">
+<head>
+<meta charset="utf-8">
+<meta name="viewport" content="width=device-width, initial-scale=1">
+<title>Broker Mind-Map — LA County CRE</title>
+<script src="https://unpkg.com/vis-network@9.1.9/standalone/umd/vis-network.min.js"></script>
+<style>
+ :root{ --bg:#0e1116; --card:#161b22; --line:#2a313c; --ink:#e6edf3; --mut:#8b949e; --acc:#3fb950; --blue:#58a6ff; --gold:#d29922; }
+ *{box-sizing:border-box;} html,body{height:100%;}
+ body{margin:0;background:var(--bg);color:var(--ink);font:14px/1.5 -apple-system,BlinkMacSystemFont,"Segoe UI",Roboto,sans-serif;display:flex;flex-direction:column;height:100vh;}
+ header{display:flex;align-items:center;gap:16px;padding:12px 20px;border-bottom:1px solid var(--line);flex:0 0 auto;}
+ header h1{font-size:17px;margin:0;} header a{color:var(--blue);text-decoration:none;font-size:13px;}
+ .stats{display:flex;gap:16px;margin-left:auto;font-size:12px;color:var(--mut);}
+ .stats b{color:var(--ink);font-size:15px;}
+ .wrap{flex:1 1 auto;display:flex;min-height:0;}
+ #net{flex:1;min-width:0;background:radial-gradient(circle at 50% 40%,#11161d,#0b0e13);}
+ aside{width:300px;flex:0 0 auto;border-left:1px solid var(--line);background:#0b0e13;overflow-y:auto;padding:14px;}
+ aside h3{font-size:12px;text-transform:uppercase;letter-spacing:.5px;color:var(--mut);margin:0 0 8px;}
+ .ctrl{margin-bottom:14px;padding-bottom:14px;border-bottom:1px solid var(--line);}
+ input[type=text],input[type=range]{width:100%;background:var(--card);color:var(--ink);border:1px solid var(--line);border-radius:8px;padding:7px 9px;font-size:13px;}
+ input[type=range]{padding:0;accent-color:var(--blue);}
+ .legend span{display:inline-flex;align-items:center;gap:5px;margin-right:12px;font-size:12px;color:var(--mut);}
+ .dot{width:10px;height:10px;border-radius:50%;display:inline-block;}
+ .blist .row{display:flex;justify-content:space-between;gap:8px;padding:6px 0;border-top:1px solid var(--line);cursor:pointer;font-size:13px;}
+ .blist .row:hover{color:var(--blue);} .blist .firm{color:var(--mut);font-size:11px;}
+ .blist .n{color:var(--acc);font-variant-numeric:tabular-nums;}
+ #detail{font-size:13px;} #detail .k{color:var(--mut);}
+ .empty{padding:40px;color:var(--mut);text-align:center;}
+</style>
+</head>
+<body>
+<header>
+ <h1>🕸️ Broker Mind-Map</h1>
+ <a href="/">← back to properties</a>
+ <div class="stats" id="stats"></div>
+</header>
+<div class="wrap">
+ <div id="net"></div>
+ <aside>
+ <div class="ctrl">
+ <h3>Filter</h3>
+ <input type="text" id="firmq" placeholder="highlight firm / broker…">
+ <div style="margin-top:10px;font-size:12px;color:var(--mut)">Min listings in our set: <b id="mlval">1</b></div>
+ <input type="range" id="minl" min="1" max="10" step="1" value="1">
+ <div class="legend" style="margin-top:10px"><span><i class="dot" style="background:#58a6ff"></i>firm</span><span><i class="dot" style="background:#3fb950"></i>broker</span><span><i class="dot" style="background:#d29922"></i>co-list</span></div>
+ </div>
+ <div class="ctrl" id="detailwrap" style="display:none"><h3>Selected</h3><div id="detail"></div></div>
+ <h3>Top brokers (by listings here)</h3>
+ <div class="blist" id="blist"></div>
+ </aside>
+</div>
+<script>
+const $=s=>document.querySelector(s);
+let GRAPH=null, network=null, nodesDS=null, edgesDS=null, minListings=1;
+
+function buildVis(g){
+ const visNodes=[], visEdges=[];
+ g.nodes.forEach(n=>{
+ if(n.kind==='firm'){
+ visNodes.push({id:n.id,label:n.label,shape:'box',color:{background:'#16324f',border:'#58a6ff'},font:{color:'#cfe3ff',size:13},value:Math.max(8,n.weight*4),group:'firm'});
+ } else {
+ const ok = (n.listings||0) >= minListings;
+ visNodes.push({id:n.id,label:n.label,shape:'dot',color:{background: ok?'#2ea043':'#23502f',border:'#3fb950'},font:{color: ok?'#e6edf3':'#5a6b5e',size:12},value:Math.max(6,(n.listings||1)*6),title:`${n.label}\n${n.firm||'—'}\n${n.listings} listings here / ${n.total_assets||'?'} total`,group:'broker',_listings:n.listings||0});
+ }
+ });
+ g.edges.forEach(e=>{
+ if(e.kind==='employs') visEdges.push({from:e.source,to:e.target,color:{color:'#2a313c'},width:1});
+ else visEdges.push({from:e.source,to:e.target,color:{color:'#d29922'},width:Math.min(6,1+(e.weight||1)),title:`co-listed ${e.weight||1}×`});
+ });
+ nodesDS=new vis.DataSet(visNodes); edgesDS=new vis.DataSet(visEdges);
+ const opts={ physics:{stabilization:{iterations:180},barnesHut:{gravitationalConstant:-9000,springLength:130,springConstant:.03}},
+ interaction:{hover:true,tooltipDelay:120}, nodes:{scaling:{min:6,max:48}}, edges:{smooth:{type:'continuous'}} };
+ network=new vis.Network($('#net'), {nodes:nodesDS,edges:edgesDS}, opts);
+ network.on('click', p=>{ if(p.nodes.length) showDetail(p.nodes[0]); });
+}
+function showDetail(id){
+ const g=GRAPH; const n=g.nodes.find(x=>x.id===id); if(!n) return;
+ const wrap=$('#detailwrap'), d=$('#detail'); wrap.style.display='block';
+ if(n.kind==='firm'){
+ const brokers=g.nodes.filter(x=>x.kind==='broker'&&x.firm===n.label);
+ d.innerHTML=`<b>${n.label}</b><div class="k">brokerage firm</div><div style="margin-top:6px">${brokers.length} brokers in graph:</div>`+brokers.slice(0,30).map(b=>`<div>· ${b.label} <span style="color:var(--acc)">(${b.listings})</span></div>`).join('');
+ } else {
+ const co=g.edges.filter(e=>e.kind==='colist'&&(e.source===id||e.target===id));
+ const partners=co.map(e=>{const oid=e.source===id?e.target:e.source;const o=g.nodes.find(x=>x.id===oid);return o?`${o.label} (${e.weight}×)`:''}).filter(Boolean);
+ d.innerHTML=`<b>${n.label}</b><div class="k">${n.firm||'—'}</div><div style="margin-top:6px">${n.listings} listings in our set · ${n.total_assets||'?'} total on Crexi</div>`+(partners.length?`<div style="margin-top:8px" class="k">co-lists with:</div>${partners.map(p=>'<div>· '+p+'</div>').join('')}`:'<div style="margin-top:8px" class="k">no co-listings in set</div>');
+ }
+ network.selectNodes([id]); network.focus(id,{scale:1.1,animation:true});
+}
+function applyMinL(){ if(!nodesDS) return;
+ nodesDS.forEach(nd=>{ if(nd.group==='broker'){ const ok=(nd._listings||0)>=minListings; nodesDS.update({id:nd.id,color:{background:ok?'#2ea043':'#23502f',border:'#3fb950'},font:{color:ok?'#e6edf3':'#5a6b5e',size:12}}); } });
+}
+function highlight(q){ if(!nodesDS) return; q=q.trim().toLowerCase();
+ nodesDS.forEach(nd=>{ const hit=q&&nd.label.toLowerCase().includes(q); nodesDS.update({id:nd.id,borderWidth:hit?4:1,font:{...(nd.font||{}),color:hit?'#fff':(nd.font&&nd.font.color)||'#e6edf3'}}); });
+}
+
+fetch('/api/graph?limit=500').then(r=>r.json()).then(g=>{
+ GRAPH=g;
+ if(g.unavailable || !g.nodes.length){ $('#net').innerHTML='<div class="empty">No broker data yet.<br>Run: <code>CC_LIMIT=0 node scripts/fetch-brokers.js</code></div>'; }
+ else buildVis(g);
+ const s=g.stats||{};
+ $('#stats').innerHTML=`<span><b>${s.brokers||0}</b> brokers</span><span><b>${s.firms||0}</b> firms</span><span><b>${s.listings||0}</b> listings</span><span><b>${s.edges||0}</b> links</span>`;
+}).catch(e=>{ $('#net').innerHTML='<div class="empty">graph error: '+e.message+'</div>'; });
+
+fetch('/api/brokers/top?limit=40').then(r=>r.json()).then(rows=>{
+ $('#blist').innerHTML = (rows||[]).map(t=>`<div class="row" data-name="${(t.name||'').replace(/"/g,'"')}"><span>${t.name}<div class="firm">${t.firm||'—'}</div></span><span class="n">${t.listings}</span></div>`).join('') || '<div class="k">none yet</div>';
+ $('#blist').onclick=e=>{ const r=e.target.closest('.row'); if(r){ $('#firmq').value=r.dataset.name; highlight(r.dataset.name); } };
+}).catch(()=>{});
+
+$('#minl').oninput=()=>{ minListings=+$('#minl').value; $('#mlval').textContent=minListings; applyMinL(); };
+$('#firmq').oninput=()=>highlight($('#firmq').value);
+</script>
+</body>
+</html>
diff --git a/public/index.html b/public/index.html
index ecd5885..cb6ff8b 100644
--- a/public/index.html
+++ b/public/index.html
@@ -116,6 +116,7 @@
<div class="sub" id="sub">loading…</div>
</div>
<div class="spacer"></div>
+ <a href="/brokers.html" style="color:var(--blue);text-decoration:none;font-size:13px;margin-right:12px" title="Broker / agent relationship mind-map">🕸️ Broker map</a>
<span class="seg" id="scenario">
<button data-s="25" class="active">25% down</button>
<button data-s="30">30% down</button>
diff --git a/scripts/db/broker-schema.sql b/scripts/db/broker-schema.sql
new file mode 100644
index 0000000..0474dbf
--- /dev/null
+++ b/scripts/db/broker-schema.sql
@@ -0,0 +1,65 @@
+-- broker-schema.sql — the broker/agent relationship graph for the CRE explorer (local Postgres "cre").
+-- Nodes: firm, broker, listing. Edges: broker_listing (who lists what). Built for mind-mapping
+-- connections — "which brokers co-list", "which firm dominates a submarket", "this agent's whole book".
+
+CREATE TABLE IF NOT EXISTS firm (
+ id serial PRIMARY KEY,
+ name text UNIQUE NOT NULL,
+ created_at timestamptz DEFAULT now()
+);
+
+CREATE TABLE IF NOT EXISTS broker (
+ id serial PRIMARY KEY,
+ name text NOT NULL,
+ firm_id integer REFERENCES firm(id),
+ title text,
+ phone text,
+ email text,
+ crexi_id text, -- vendor broker id when available
+ source text, -- where we learned this broker (crexi, sheet, etc.)
+ created_at timestamptz DEFAULT now(),
+ UNIQUE (name, firm_id) -- a broker is unique within a firm
+);
+
+CREATE TABLE IF NOT EXISTS listing (
+ id text PRIMARY KEY, -- the CRE listing id (e.g. crx2560155)
+ address text,
+ city text,
+ zip text,
+ type text,
+ price bigint,
+ cap_rate numeric,
+ units integer,
+ firm_name text, -- listing brokerage (denormalized for convenience)
+ source text,
+ created_at timestamptz DEFAULT now()
+);
+
+-- The edge table: a broker is on a listing (lead/co/team). This is the graph that powers the mind-map.
+CREATE TABLE IF NOT EXISTS broker_listing (
+ broker_id integer NOT NULL REFERENCES broker(id) ON DELETE CASCADE,
+ listing_id text NOT NULL REFERENCES listing(id) ON DELETE CASCADE,
+ role text DEFAULT 'broker', -- lead | co | team
+ PRIMARY KEY (broker_id, listing_id)
+);
+
+CREATE INDEX IF NOT EXISTS idx_broker_firm ON broker(firm_id);
+CREATE INDEX IF NOT EXISTS idx_listing_city ON listing(city);
+CREATE INDEX IF NOT EXISTS idx_listing_firm ON listing(firm_name);
+CREATE INDEX IF NOT EXISTS idx_bl_listing ON broker_listing(listing_id);
+
+-- A convenience view: broker with firm name + listing count (node weight for the mind-map).
+CREATE OR REPLACE VIEW broker_node AS
+ SELECT b.id, b.name, f.name AS firm, b.phone, b.email, b.title,
+ count(bl.listing_id) AS listings
+ FROM broker b
+ LEFT JOIN firm f ON f.id = b.firm_id
+ LEFT JOIN broker_listing bl ON bl.broker_id = b.id
+ GROUP BY b.id, f.name;
+
+-- Co-listing edges: pairs of brokers who appear on the same listing (the connection graph).
+CREATE OR REPLACE VIEW broker_cobroker AS
+ SELECT a.broker_id AS a, b.broker_id AS b, count(*) AS shared_listings
+ FROM broker_listing a
+ JOIN broker_listing b ON a.listing_id = b.listing_id AND a.broker_id < b.broker_id
+ GROUP BY a.broker_id, b.broker_id;
diff --git a/scripts/db/brokers-db.js b/scripts/db/brokers-db.js
new file mode 100644
index 0000000..faf26ed
--- /dev/null
+++ b/scripts/db/brokers-db.js
@@ -0,0 +1,78 @@
+// brokers-db.js — connection + ingest + graph queries for the broker relationship DB (local "cre").
+'use strict';
+const { Pool } = require('pg');
+const pool = new Pool({
+ host: process.env.CRE_PGHOST || '/tmp', port: +(process.env.CRE_PGPORT || 5432),
+ database: process.env.CRE_PGDATABASE || 'cre', user: process.env.CRE_PGUSER || process.env.USER || 'stevestudio2',
+ max: 6, idleTimeoutMillis: 10000, connectionTimeoutMillis: 4000
+});
+pool.on('error', () => {});
+
+async function upsertFirm(name) {
+ if (!name) return null;
+ const r = await pool.query(
+ `INSERT INTO firm(name) VALUES($1) ON CONFLICT(name) DO UPDATE SET name=EXCLUDED.name RETURNING id`, [name.trim()]);
+ return r.rows[0].id;
+}
+
+async function upsertBroker(b) {
+ const firmId = await upsertFirm(b.firm);
+ const r = await pool.query(
+ `INSERT INTO broker(name, firm_id, title, phone, email, crexi_id, source, total_assets)
+ VALUES($1,$2,$3,$4,$5,$6,$7,$8)
+ ON CONFLICT(name, firm_id) DO UPDATE SET
+ phone=COALESCE(EXCLUDED.phone, broker.phone),
+ email=COALESCE(EXCLUDED.email, broker.email),
+ crexi_id=COALESCE(EXCLUDED.crexi_id, broker.crexi_id),
+ title=COALESCE(EXCLUDED.title, broker.title),
+ total_assets=GREATEST(COALESCE(EXCLUDED.total_assets,0), COALESCE(broker.total_assets,0))
+ RETURNING id`,
+ [b.name.trim(), firmId, b.title || null, b.phone || null, b.email || null, b.crexi_id || null, b.source || 'crexi', b.total_assets || null]);
+ return r.rows[0].id;
+}
+
+async function upsertListing(l) {
+ await pool.query(
+ `INSERT INTO listing(id, address, city, zip, type, price, cap_rate, units, firm_name, source)
+ VALUES($1,$2,$3,$4,$5,$6,$7,$8,$9,$10)
+ ON CONFLICT(id) DO UPDATE SET firm_name=COALESCE(EXCLUDED.firm_name, listing.firm_name)`,
+ [l.id, l.address || null, l.city || null, l.zip || null, l.type || null,
+ l.price || null, l.cap_rate || null, l.units || null, l.firm_name || null, l.source || null]);
+ return l.id;
+}
+
+async function link(brokerId, listingId, role) {
+ await pool.query(
+ `INSERT INTO broker_listing(broker_id, listing_id, role) VALUES($1,$2,$3)
+ ON CONFLICT DO NOTHING`, [brokerId, listingId, role || 'broker']);
+}
+
+// The mind-map graph: broker + firm nodes, edges = broker→listing collapsed into broker↔firm and
+// broker↔broker (co-listing). Returns {nodes, edges, stats} sized for a force-directed view.
+async function graph(limit = 400) {
+ const brokers = (await pool.query(`SELECT * FROM broker_node ORDER BY listings DESC NULLS LAST LIMIT $1`, [limit])).rows;
+ const ids = new Set(brokers.map(b => b.id));
+ const co = (await pool.query(`SELECT a, b, shared_listings FROM broker_cobroker`)).rows
+ .filter(e => ids.has(e.a) && ids.has(e.b));
+ const firms = {};
+ brokers.forEach(b => { if (b.firm) firms[b.firm] = (firms[b.firm] || 0) + 1; });
+ const nodes = [];
+ Object.entries(firms).forEach(([name, n]) => nodes.push({ id: 'firm:' + name, kind: 'firm', label: name, weight: n }));
+ brokers.forEach(b => nodes.push({ id: 'broker:' + b.id, kind: 'broker', label: b.name, firm: b.firm, listings: +b.listings, total: b.total_assets }));
+ const edges = [];
+ brokers.forEach(b => { if (b.firm) edges.push({ source: 'broker:' + b.id, target: 'firm:' + b.firm, kind: 'employs' }); });
+ co.forEach(e => edges.push({ source: 'broker:' + e.a, target: 'broker:' + e.b, kind: 'colist', weight: e.shared_listings }));
+ const stats = {
+ brokers: (await pool.query(`SELECT count(*) c FROM broker`)).rows[0].c,
+ firms: (await pool.query(`SELECT count(*) c FROM firm`)).rows[0].c,
+ listings: (await pool.query(`SELECT count(*) c FROM listing`)).rows[0].c,
+ edges: (await pool.query(`SELECT count(*) c FROM broker_listing`)).rows[0].c
+ };
+ return { nodes, edges, stats };
+}
+
+async function topBrokers(limit = 50) {
+ return (await pool.query(`SELECT name, firm, listings, phone, email, title FROM broker_node ORDER BY listings DESC NULLS LAST LIMIT $1`, [limit])).rows;
+}
+
+module.exports = { pool, upsertFirm, upsertBroker, upsertListing, link, graph, topBrokers };
diff --git a/scripts/fetch-brokers.js b/scripts/fetch-brokers.js
new file mode 100644
index 0000000..f20e32b
--- /dev/null
+++ b/scripts/fetch-brokers.js
@@ -0,0 +1,86 @@
+// fetch-brokers.js — capture each Crexi listing's broker team and ingest into the broker graph DB.
+// Strategy: ONE Browserbase session captures Crexi's anonymous Bearer token, then fetches
+// api.crexi.com/assets/<id>/brokers for our listing ids FROM INSIDE the page (same browser, авторized),
+// in batches. Each broker → firm + agent name + book size → cre DB (firm/broker/listing + edges).
+//
+// CC_LIMIT=80 node scripts/fetch-brokers.js # sample run (default 80)
+// CC_LIMIT=0 node scripts/fetch-brokers.js # all listings
+// COST: 1 Browserbase session (~$0.05-0.10 depending on batch count). NODE_PATH → browserbase skill.
+'use strict';
+const fs = require('fs');
+const path = require('path');
+const { chromium } = require('playwright-core');
+const Browserbase = require('@browserbasehq/sdk').default;
+const db = require('./db/brokers-db');
+const bbEnv = fs.readFileSync(process.env.HOME + '/.claude/skills/browserbase/.env', 'utf8');
+const get = (t, k) => (t.match(new RegExp('^' + k + '=(.*)$', 'm')) || [])[1]?.replace(/['"]/g, '').trim();
+const ROOT = path.join(__dirname, '..');
+
+const LIMIT = process.env.CC_LIMIT != null ? +process.env.CC_LIMIT : 80;
+
+(async () => {
+ const all = JSON.parse(fs.readFileSync(path.join(ROOT, 'data', 'listings.json'), 'utf8')).listings
+ .filter(l => /^crx\d+/.test(l.id))
+ .map(l => ({ ...l, assetId: l.id.replace(/^crx/, '') }));
+ const work = LIMIT ? all.slice(0, LIMIT) : all;
+ console.log(`brokers for ${work.length}/${all.length} Crexi listings…`);
+
+ let browser, results = [];
+ try {
+ const bb = new Browserbase({ apiKey: get(bbEnv, 'BROWSERBASE_API_KEY') });
+ const session = await bb.sessions.create({ projectId: get(bbEnv, 'BROWSERBASE_PROJECT_ID'), browserSettings: { solveCaptchas: true, viewport: { width: 1440, height: 1000 } } });
+ console.log('bb session', session.id);
+ browser = await chromium.connectOverCDP(session.connectUrl);
+ const ctx = browser.contexts()[0];
+ const page = ctx.pages()[0] || await ctx.newPage();
+ page.setDefaultTimeout(45000);
+
+ // capture the Authorization bearer token Crexi's UI uses
+ let auth = null;
+ page.on('request', req => { const h = req.headers(); if (!auth && h.authorization && /bearer/i.test(h.authorization) && req.url().includes('api.crexi.com')) auth = h.authorization; });
+ await page.goto('https://www.crexi.com/properties/CA/Los-Angeles/multifamily', { waitUntil: 'domcontentloaded' });
+ await page.waitForTimeout(6000); await page.mouse.wheel(0, 2500).catch(() => {}); await page.waitForTimeout(2500);
+ if (!auth) { console.log('no bearer captured; will try cookie-auth in-page fetch'); }
+
+ // fetch brokers in-page (same browser context → authorized), batched
+ const ids = work.map(w => w.assetId);
+ const BATCH = 25;
+ for (let i = 0; i < ids.length; i += BATCH) {
+ const batch = ids.slice(i, i + BATCH);
+ const out = await page.evaluate(async ({ batch, auth }) => {
+ const r = [];
+ for (const id of batch) {
+ try {
+ const resp = await fetch(`https://api.crexi.com/assets/${id}/brokers`, { headers: auth ? { authorization: auth } : {} });
+ if (resp.ok) r.push({ id, brokers: await resp.json() }); else r.push({ id, err: resp.status });
+ } catch (e) { r.push({ id, err: String(e).slice(0, 60) }); }
+ }
+ return r;
+ }, { batch, auth });
+ results.push(...out);
+ process.stdout.write(` ${Math.min(i + BATCH, ids.length)}/${ids.length}\r`);
+ }
+ } catch (e) { console.error('FATAL', e.message); } finally { if (browser) await browser.close().catch(() => {}); }
+
+ // ingest
+ const byId = Object.fromEntries(work.map(w => [w.assetId, w]));
+ let linked = 0, brokersSeen = 0, withBrokers = 0;
+ for (const row of results) {
+ const L = byId[row.id]; if (!L) continue;
+ await db.upsertListing({ id: L.id, address: L.address, city: L.city, zip: L.zip, type: L.type, price: L.price, cap_rate: L.cap_rate, units: L.units, source: L.source });
+ if (!Array.isArray(row.brokers) || !row.brokers.length) continue;
+ withBrokers++;
+ for (const b of row.brokers) {
+ const name = [b.firstName, b.lastName].filter(Boolean).join(' ').trim(); if (!name) continue;
+ const firm = b.brokerage && b.brokerage.name;
+ const brokerId = await db.upsertBroker({ name, firm, total_assets: b.numberOfAssets || null, crexi_id: String(b.id || b.globalId || ''), source: 'crexi' });
+ await db.link(brokerId, L.id, row.brokers.length > 1 ? 'co' : 'lead');
+ linked++; brokersSeen++;
+ }
+ }
+ console.log(`\ningested: ${withBrokers}/${results.length} listings had brokers · ${brokersSeen} broker links · ${linked} edges`);
+ const top = await db.topBrokers(12);
+ console.log('top brokers by listings in our set:');
+ top.forEach(t => console.log(` ${t.name} (${t.firm || 'n/a'}) — ${t.listings} here / ${t.total_assets || '?'} total`));
+ await db.pool.end();
+})();
diff --git a/scripts/probe-brokers.js b/scripts/probe-brokers.js
new file mode 100644
index 0000000..1bdf72c
--- /dev/null
+++ b/scripts/probe-brokers.js
@@ -0,0 +1,67 @@
+// probe-brokers.js — navigate real Crexi DETAIL pages and deep-scan every api.crexi.com JSON response
+// for broker/agent data. Writes data/raw/broker-probe.json with the endpoint(s) that carry brokers and
+// a sample. One Browserbase session (~$0.04). NODE_PATH must point at the browserbase skill.
+'use strict';
+const fs = require('fs');
+const path = require('path');
+const { chromium } = require('playwright-core');
+const Browserbase = require('@browserbasehq/sdk').default;
+const bbEnv = fs.readFileSync(process.env.HOME + '/.claude/skills/browserbase/.env', 'utf8');
+const get = (t, k) => (t.match(new RegExp('^' + k + '=(.*)$', 'm')) || [])[1]?.replace(/['"]/g, '').trim();
+const ROOT = path.join(__dirname, '..');
+
+const DETAIL_URLS = [
+ 'https://www.crexi.com/properties/2560155/california-6852-claire-ave-reseda-ca-91335-3840',
+ 'https://www.crexi.com/properties/2448930/california-17852-saticoy-street'
+];
+
+// Deep-scan an object for broker/agent-ish keys at any depth; return matched {path:value} pairs.
+function scanBroker(o, prefix = '', hits = {}, depth = 0) {
+ if (!o || typeof o !== 'object' || depth > 6) return hits;
+ for (const k of Object.keys(o)) {
+ const p = prefix ? prefix + '.' + k : k;
+ if (/^(brokers?|agents?|contacts?|brokerage|salesPerson|advisor|team|listedBy|firm|company)$/i.test(k)) {
+ hits[p] = JSON.stringify(o[k]).slice(0, 600);
+ }
+ if (o[k] && typeof o[k] === 'object') scanBroker(o[k], p, hits, depth + 1);
+ }
+ return hits;
+}
+
+(async () => {
+ const endpoints = {}; // pathname -> { keys, brokerHits, sampleSaved }
+ let savedSample = null;
+ let browser;
+ try {
+ const bb = new Browserbase({ apiKey: get(bbEnv, 'BROWSERBASE_API_KEY') });
+ const session = await bb.sessions.create({ projectId: get(bbEnv, 'BROWSERBASE_PROJECT_ID'), browserSettings: { solveCaptchas: true, viewport: { width: 1440, height: 1000 } } });
+ console.log('bb session', session.id);
+ browser = await chromium.connectOverCDP(session.connectUrl);
+ const ctx = browser.contexts()[0];
+ const page = ctx.pages()[0] || await ctx.newPage();
+ page.setDefaultTimeout(45000);
+ page.on('response', async (resp) => {
+ const u = resp.url(); if (!u.includes('api.crexi.com')) return;
+ let j; try { j = await resp.json(); } catch { return; }
+ const pathname = new URL(u).pathname.replace(/\/\d+/g, '/<id>');
+ const hits = scanBroker(j);
+ const e = endpoints[pathname] || (endpoints[pathname] = { keys: Array.isArray(j) ? '[array]' : Object.keys(j).slice(0, 20), brokerPaths: {} });
+ Object.assign(e.brokerPaths, hits);
+ if (Object.keys(hits).length && !savedSample) savedSample = { url: pathname, body: j };
+ });
+ for (const url of DETAIL_URLS) {
+ console.log('→', url);
+ await page.goto(url, { waitUntil: 'domcontentloaded' }).catch(e => console.log(' nav', e.message.split('\n')[0]));
+ await page.waitForTimeout(7000);
+ await page.mouse.wheel(0, 4000).catch(() => {}); await page.waitForTimeout(3000);
+ }
+ } catch (e) { console.error('FATAL', e.message); } finally { if (browser) await browser.close().catch(() => {}); }
+
+ fs.writeFileSync(path.join(ROOT, 'data', 'raw', 'broker-probe.json'), JSON.stringify({ endpoints, savedSample }, null, 2));
+ console.log('\n=== endpoints seen ===');
+ for (const [p, e] of Object.entries(endpoints)) {
+ const bk = Object.keys(e.brokerPaths);
+ console.log(`${p} ${bk.length ? '★ BROKER: ' + bk.join(', ') : '(no broker fields)'}`);
+ }
+ console.log('\nfull dump → data/raw/broker-probe.json');
+})();
diff --git a/scripts/serve.js b/scripts/serve.js
index c24cf94..fafe3f5 100644
--- a/scripts/serve.js
+++ b/scripts/serve.js
@@ -6,6 +6,7 @@ const path = require('path');
const fs = require('fs');
const mountClaudeChat = require('../claude-chat-bridge.cjs');
const { lookupAddress } = require('./sources/wholivedthere'); // READ-ONLY WhoLivedThere/pastdoor archive
+let brokerdb = null; try { brokerdb = require('./db/brokers-db'); } catch (_) {} // broker graph (cre DB)
const ROOT = path.join(__dirname, '..');
const PORT = process.env.PORT || 9911;
@@ -63,6 +64,18 @@ Action schema (omit keys that don't change):
- "highlightId": "<property id from the context>" (scrolls to + flashes that card)
Rules: explain in prose ABOVE the block. Only emit an action block when the user actually wants the view changed — for pure questions, NO action block. Use the exact ids/enum values above.`;
+// Broker relationship graph (mind-map) from the cre DB. Read-only.
+app.get('/api/graph', async (req, res) => {
+ if (!brokerdb) return res.json({ nodes: [], edges: [], stats: {}, unavailable: true });
+ try { res.json(await brokerdb.graph(+(req.query.limit) || 400)); }
+ catch (e) { res.status(502).json({ error: String(e.message).split('\n')[0], nodes: [], edges: [] }); }
+});
+app.get('/api/brokers/top', async (req, res) => {
+ if (!brokerdb) return res.json([]);
+ try { res.json(await brokerdb.topBrokers(+(req.query.limit) || 50)); }
+ catch (e) { res.status(502).json({ error: String(e.message).split('\n')[0] }); }
+});
+
app.use('/data', express.static(path.join(ROOT, 'data')));
app.use('/', express.static(path.join(ROOT, 'public')));
← 216da73 afternoon CRE update 2026-06-27
·
back to Commercialrealestate
·
CRE: extend Crexi sweep to 30 more LA County cities (1028→14 45d44d0 →