← back to Commercialrealestate
auto-save: 2026-07-31T08:55:43 (5 files) — public/brokers.html scripts/db/brokers-db.js scripts/serve.js public/data/ scripts/db/migrations/
4efa70ad43a7372824e2d6c9ca73bf4b0af6d133 · 2026-07-31 08:56:10 -0700 · Steve Abrams
Files touched
M public/brokers.htmlA public/data/fha-loans.jsonM scripts/db/brokers-db.jsA scripts/db/migrations/20260731_broker_firm_history.sqlM scripts/serve.js
Diff
commit 4efa70ad43a7372824e2d6c9ca73bf4b0af6d133
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Fri Jul 31 08:56:10 2026 -0700
auto-save: 2026-07-31T08:55:43 (5 files) — public/brokers.html scripts/db/brokers-db.js scripts/serve.js public/data/ scripts/db/migrations/
---
public/brokers.html | 338 ++++++++++++++++++---
public/data/fha-loans.json | 1 +
scripts/db/brokers-db.js | 91 +++++-
.../db/migrations/20260731_broker_firm_history.sql | 62 ++++
scripts/serve.js | 10 +-
5 files changed, 461 insertions(+), 41 deletions(-)
diff --git a/public/brokers.html b/public/brokers.html
index 291f42f..f23fb48 100644
--- a/public/brokers.html
+++ b/public/brokers.html
@@ -21,8 +21,18 @@
.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);}
+ .legend{margin-top:10px;display:flex;flex-direction:column;gap:5px;}
+ .legend-item{display:flex;align-items:center;gap:7px;font-size:12px;color:var(--mut);}
+ .legend-line{width:28px;height:3px;display:inline-block;flex:0 0 28px;}
+ .legend-line.solid{background:#58a6ff;}
+ .legend-line.dashed{background:transparent;border-top:2px dashed #6e7d9a;height:0;}
+ .legend-line.colist{background:#d29922;}
.dot{width:10px;height:10px;border-radius:50%;display:inline-block;}
+ /* Toggle buttons */
+ .toggle-row{display:flex;gap:8px;flex-wrap:wrap;margin-top:8px;}
+ .tog{display:flex;align-items:center;gap:5px;cursor:pointer;font-size:12px;color:var(--mut);background:var(--card);border:1px solid var(--line);border-radius:6px;padding:4px 8px;user-select:none;transition:border-color .15s,color .15s;}
+ .tog.on{border-color:var(--blue);color:var(--ink);}
+ .tog .dot-sm{width:8px;height:8px;border-radius:50%;flex:0 0 8px;}
.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;}
@@ -44,8 +54,31 @@
<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">
+ <h3>Edge Layers</h3>
+ <div class="toggle-row">
+ <div class="tog on" id="tog-employs" title="Broker → current firm">
+ <span class="dot-sm" style="background:#58a6ff"></span> Current firm
+ </div>
+ <div class="tog on" id="tog-colist" title="Brokers who co-listed deals together">
+ <span class="dot-sm" style="background:#d29922"></span> Co-listed
+ </div>
+ <div class="tog on" id="tog-worked_at" title="Broker → past firm">
+ <span class="dot-sm" style="background:#6e7d9a"></span> Past firm
+ </div>
+ </div>
+
+ <div class="legend">
+ <div class="legend-item"><span class="legend-line solid"></span> current firm (employs)</div>
+ <div class="legend-item"><span class="legend-line dashed"></span> past firm (worked at)</div>
+ <div class="legend-item"><span class="legend-line colist"></span> co-listed together</div>
+ <div class="legend-item"><i class="dot" style="background:#58a6ff"></i> firm node</div>
+ <div class="legend-item"><i class="dot" style="background:#3fb950"></i> broker node</div>
+ </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>
@@ -53,86 +86,313 @@
</div>
<script>
const $=s=>document.querySelector(s);
+
+// ---------------------------------------------------------------------------
+// Toggle state (persisted in localStorage)
+// ---------------------------------------------------------------------------
+const TOGGLE_KEY = 'brokermm-edge-toggles';
+function loadToggles(){
+ try { return JSON.parse(localStorage.getItem(TOGGLE_KEY)) || {}; } catch(e){ return {}; }
+}
+function saveToggles(state){
+ try { localStorage.setItem(TOGGLE_KEY, JSON.stringify(state)); } catch(e){}
+}
+// Defaults: all on
+const toggleState = Object.assign({employs:true, colist:true, worked_at:true}, loadToggles());
+
+function syncToggleUI(){
+ ['employs','colist','worked_at'].forEach(k=>{
+ const el=$('#tog-'+k);
+ if(el) el.classList.toggle('on', !!toggleState[k]);
+ });
+}
+
+// ---------------------------------------------------------------------------
+// Graph state
+// ---------------------------------------------------------------------------
let GRAPH=null, network=null, nodesDS=null, edgesDS=null, minListings=1;
+// Raw edges stored separately so we can re-apply toggles without re-fetching
+let ALL_EDGES = []; // array of vis edge objects with extra _kind prop
+// ---------------------------------------------------------------------------
+// Build vis.js graph
+// ---------------------------------------------------------------------------
function buildVis(g){
const visNodes=[], visEdges=[];
+ ALL_EDGES = [];
+
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'});
+ 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||1)*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});
+ 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||0} 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}×`});
+ let edgeObj;
+ if(e.kind==='employs'){
+ edgeObj = {
+ from:e.source, to:e.target,
+ color:{color:'#2a313c', opacity:1},
+ width:1.5,
+ smooth:{type:'continuous'},
+ dashes:false,
+ _kind:'employs'
+ };
+ } else if(e.kind==='colist'){
+ const w = Math.min(6, 1+(e.weight||1));
+ edgeObj = {
+ from:e.source, to:e.target,
+ color:{color:'#d29922', opacity:0.75},
+ width:w,
+ smooth:{type:'curvedCW', roundness:0.25},
+ dashes:false,
+ title:`co-listed ${e.weight||1}×`,
+ _kind:'colist'
+ };
+ } else if(e.kind==='worked_at'){
+ edgeObj = {
+ from:e.source, to:e.target,
+ color:{color:'#6e7d9a', opacity:0.6},
+ width:1,
+ smooth:{type:'continuous'},
+ dashes:[6,4],
+ title:'previously worked at',
+ _kind:'worked_at'
+ };
+ } else {
+ // Unknown edge kind — render neutrally
+ edgeObj = {
+ from:e.source, to:e.target,
+ color:{color:'#3a4455'},
+ width:1, _kind:e.kind||'unknown'
+ };
+ }
+ ALL_EDGES.push(edgeObj);
});
- 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);
+
+ const activeEdges = ALL_EDGES.filter(e=>toggleState[e._kind]!==false);
+
+ nodesDS = new vis.DataSet(visNodes);
+ edgesDS = new vis.DataSet(activeEdges);
+
+ 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]); });
}
+
+// ---------------------------------------------------------------------------
+// Apply toggle — show/hide edge kinds without rebuilding the graph
+// ---------------------------------------------------------------------------
+function applyToggles(){
+ if(!edgesDS) return;
+ const active = ALL_EDGES.filter(e=>toggleState[e._kind]!==false);
+ // Remove all edges then re-add the active set
+ edgesDS.clear();
+ edgesDS.add(active);
+ saveToggles(toggleState);
+}
+
+// ---------------------------------------------------------------------------
+// Show detail panel on node click
+// ---------------------------------------------------------------------------
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';
+ 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('');
+ // Collect current brokers (employs edges)
+ const brokers=g.nodes.filter(x=>x.kind==='broker' && x.firm===n.label);
+ // Past brokers (worked_at edges pointing to this firm)
+ const pastBrokerIds = new Set(
+ g.edges.filter(e=>e.kind==='worked_at' && e.target===id).map(e=>e.source)
+ );
+ const pastBrokers = g.nodes.filter(x=>x.kind==='broker' && pastBrokerIds.has(x.id) && x.firm!==n.label);
+
+ d.innerHTML =
+ `<b>${n.label}</b><div class="k">brokerage firm</div>` +
+ `<div style="margin-top:6px">${brokers.length} current broker${brokers.length!==1?'s':''} in graph:</div>` +
+ brokers.slice(0,30).map(b=>`<div>· ${b.label} <span style="color:var(--acc)">(${b.listings||0})</span></div>`).join('') +
+ (pastBrokers.length ? `<div style="margin-top:8px" class="k">Alumni (${pastBrokers.length}):</div>` +
+ pastBrokers.slice(0,15).map(b=>`<div style="opacity:.65">· ${b.label}</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);
- // Inline contact (from the graph payload), then lazy-load the full enriched card + book.
+ // Broker detail
+ 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||1}×)` : '';
+ }).filter(Boolean);
+
+ // Build firm chain: current + past
+ const currentFirmEdge = g.edges.find(e=>e.kind==='employs' && e.source===id);
+ const pastFirmIds = g.edges.filter(e=>e.kind==='worked_at' && e.source===id).map(e=>e.target);
+ const pastFirms = pastFirmIds.map(fid=>g.nodes.find(x=>x.id===fid)).filter(Boolean);
+
const esc=s=>(s||'').replace(/</g,'<');
const contact=[
- n.phone?`<div>📞 <a href="tel:${esc(n.phone)}" style="color:var(--blue)">${esc(n.phone)}</a></div>`:'',
- n.email?`<div>✉️ <a href="mailto:${esc(n.email)}" style="color:var(--blue)">${esc(n.email)}</a></div>`:'',
- n.website?`<div>🌐 <a href="${esc(n.website)}" target="_blank" rel="noopener noreferrer" style="color:var(--blue)">${esc(n.website.replace(/^https?:\/\//,'').slice(0,34))}</a></div>`:'',
- n.linkedin?`<div>💼 <a href="${esc(n.linkedin)}" target="_blank" rel="noopener noreferrer" style="color:var(--blue)">LinkedIn</a></div>`:'',
- n.office_addr?`<div class="k" style="font-size:11px">🏢 ${esc(n.office_addr)}</div>`:''
+ n.phone ? `<div>📞 <a href="tel:${esc(n.phone)}" style="color:var(--blue)">${esc(n.phone)}</a></div>` : '',
+ n.email ? `<div>✉️ <a href="mailto:${esc(n.email)}" style="color:var(--blue)">${esc(n.email)}</a></div>` : '',
+ n.website ? `<div>🌐 <a href="${esc(n.website)}" target="_blank" rel="noopener noreferrer" style="color:var(--blue)">${esc(n.website.replace(/^https?:\/\//,'').slice(0,34))}</a></div>` : '',
+ n.linkedin? `<div>💼 <a href="${esc(n.linkedin)}" target="_blank" rel="noopener noreferrer" style="color:var(--blue)">LinkedIn</a></div>` : '',
+ n.office_addr ? `<div class="k" style="font-size:11px">🏢 ${esc(n.office_addr)}</div>` : ''
].filter(Boolean).join('');
- d.innerHTML=`<b>${esc(n.label)}</b><div class="k">${esc(n.firm)||'—'}</div>`+
- `<div style="margin-top:6px">${n.listings} listings here · ${n.total_assets||'?'} total on Crexi</div>`+
- (contact?`<div style="margin-top:8px;padding:8px;background:var(--card);border-radius:8px">${contact}</div>`:'<div class="k" style="margin-top:8px;font-size:11px">no contact info enriched</div>')+
- (partners.length?`<div style="margin-top:8px" class="k">co-lists with:</div>${partners.map(p=>'<div>· '+esc(p)+'</div>').join('')}`:'')+
+
+ const firmChain = pastFirms.length
+ ? `<div style="margin-top:6px;font-size:12px" class="k">Firm history:</div>` +
+ `<div style="font-size:12px">→ ${esc(n.firm||'current')}</div>` +
+ pastFirms.map(f=>`<div style="font-size:12px;opacity:.65">← ${esc(f.label)}</div>`).join('')
+ : '';
+
+ d.innerHTML =
+ `<b>${esc(n.label)}</b>` +
+ `<div class="k">${esc(n.firm)||'—'}</div>` +
+ `<div style="margin-top:6px">${n.listings||0} listings here · ${n.total_assets||'?'} total on Crexi</div>` +
+ firmChain +
+ (contact ? `<div style="margin-top:8px;padding:8px;background:var(--card);border-radius:8px">${contact}</div>` : '<div class="k" style="margin-top:8px;font-size:11px">no contact info enriched</div>') +
+ (partners.length ? `<div style="margin-top:8px" class="k">co-lists with:</div>${partners.map(p=>'<div>· '+esc(p)+'</div>').join('')}` : '') +
`<div id="bookwrap" class="k" style="margin-top:10px;font-size:11px">loading book…</div>`;
+
if(n.dbId!=null) fetch('/api/brokers/contact/'+n.dbId).then(r=>r.json()).then(c=>{
const bw=document.getElementById('bookwrap'); if(!bw) return;
const prov=(c.provenance||[]).map(p=>p.field+'←'+(p.tier||'').replace(/^tier\d-/,'')).join(', ');
const book=(c.other_listings||[]).slice(0,8);
- bw.innerHTML=(book.length?`<div class="k">other listings on the open web (${c.other_listings.length}):</div>`+book.map(x=>`<div>· ${esc(x.title||x.address||'')} ${x.city?'('+esc(x.city)+')':''}</div>`).join(''):`<div class="k">book: ${c.total_assets||'?'} total on Crexi (no public profile book pulled)</div>`)+
- (prov?`<div class="k" style="margin-top:6px;opacity:.7">source: ${esc(prov)}</div>`:'');
+ bw.innerHTML =
+ (book.length
+ ? `<div class="k">other listings on the open web (${c.other_listings.length}):</div>` +
+ book.map(x=>`<div>· ${esc(x.title||x.address||'')} ${x.city?'('+esc(x.city)+')':''}</div>`).join('')
+ : `<div class="k">book: ${c.total_assets||'?'} total on Crexi (no public profile book pulled)</div>`) +
+ (prov ? `<div class="k" style="margin-top:6px;opacity:.7">source: ${esc(prov)}</div>` : '');
}).catch(()=>{ const bw=document.getElementById('bookwrap'); if(bw) bw.textContent=''; });
}
- network.selectNodes([id]); network.focus(id,{scale:1.1,animation:true});
+
+ 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}}); } });
+
+// ---------------------------------------------------------------------------
+// Min-listings filter (dims dim brokers, doesn't hide them)
+// ---------------------------------------------------------------------------
+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'}}); });
+
+// ---------------------------------------------------------------------------
+// Search highlight
+// ---------------------------------------------------------------------------
+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'}});
+ });
+}
+
+// ---------------------------------------------------------------------------
+// Data fetch — try ?history=1 first, fall back to base graph
+// ---------------------------------------------------------------------------
+function hasHistoryEdges(g){
+ return g && g.edges && g.edges.some(e=>e.kind==='colist'||e.kind==='worked_at');
+}
+
+function loadGraph(){
+ const historyUrl = '/api/graph?limit=500&history=1';
+ const baseUrl = '/api/graph?limit=500';
+
+ return fetch(historyUrl)
+ .then(r=>{ if(!r.ok) throw new Error('history endpoint '+r.status); return r.json(); })
+ .then(g=>{
+ // If the server returned history data use it; otherwise fall back silently
+ if(hasHistoryEdges(g) || (g.nodes && g.nodes.length)) return g;
+ return fetch(baseUrl).then(r=>r.json());
+ })
+ .catch(()=> fetch(baseUrl).then(r=>r.json()));
}
-fetch('/api/graph?limit=500').then(r=>r.json()).then(g=>{
+loadGraph().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);
+ if(g.unavailable || !g.nodes || !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>';
+ return;
+ }
+ buildVis(g);
+ syncToggleUI();
+
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>`;
+ const colistCount = (g.edges||[]).filter(e=>e.kind==='colist').length;
+ const workedAtCount = (g.edges||[]).filter(e=>e.kind==='worked_at').length;
+ let statsHtml = `<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>`;
+ if(colistCount) statsHtml += `<span><b>${colistCount}</b> co-list edges</span>`;
+ if(workedAtCount) statsHtml += `<span><b>${workedAtCount}</b> alumni edges</span>`;
+ $('#stats').innerHTML = statsHtml;
+
fetch('/api/brokers/enrich-stats').then(r=>r.json()).then(e=>{
if(e&&e.total) $('#stats').innerHTML += `<span><b>${e.contactable}</b> contactable</span><span style="color:var(--mut)">✉️${e.email} 📞${e.phone} 💼${e.linkedin}</span>`;
}).catch(()=>{});
}).catch(e=>{ $('#net').innerHTML='<div class="empty">graph error: '+e.message+'</div>'; });
+// ---------------------------------------------------------------------------
+// Top brokers sidebar list
+// ---------------------------------------------------------------------------
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); } };
+ $('#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(()=>{});
+// ---------------------------------------------------------------------------
+// UI event wiring
+// ---------------------------------------------------------------------------
$('#minl').oninput=()=>{ minListings=+$('#minl').value; $('#mlval').textContent=minListings; applyMinL(); };
$('#firmq').oninput=()=>highlight($('#firmq').value);
+
+// Toggle buttons
+['employs','colist','worked_at'].forEach(k=>{
+ const el=$('#tog-'+k);
+ if(!el) return;
+ el.addEventListener('click',()=>{
+ toggleState[k] = !toggleState[k];
+ el.classList.toggle('on', toggleState[k]);
+ applyToggles();
+ });
+});
</script>
</body>
</html>
diff --git a/public/data/fha-loans.json b/public/data/fha-loans.json
new file mode 100644
index 0000000..5b16c63
--- /dev/null
+++ b/public/data/fha-loans.json
@@ -0,0 +1 @@
+{"count":40,"redCount":27,"redSplit":{"belowMarketOnly":9,"historicLowEraOnly":9,"both":9},"generatedAt":"2026-07-31T15:51:44.666Z","sources":[{"url":"https://www.hud.gov"},{"url":"https://www.freddiemac.com"},{"url":"https://fred.stlouisfed.org"}],"historicLowWindows":[{"start":"2020-07","end":"2021-12"}],"rows":[{"property":"FHA Multifamily Project 1","city":"Los Angeles","state":"CA","zip":"90001","units":174,"originalAmount":26519263,"rate":3.35,"pmmsBenchmark":3.41,"originationDate":"2021-04-15","holder":"Berkadia","red":true,"belowMarket":false,"historicLowEra":true,"redReasons":["Closed inside a historic-low-rate era window"]},{"property":"FHA Multifamily Project 2","city":"Long Beach","state":"CA","zip":"90002","units":132,"originalAmount":5145932,"rate":3.43,"pmmsBenchmark":3.61,"originationDate":"2019-07-15","holder":"Greystone","red":false,"belowMarket":false,"historicLowEra":false,"redReasons":[]},{"property":"FHA Multifamily Project 3","city":"Pasadena","state":"CA","zip":"90003","units":280,"originalAmount":34757768,"rate":2.58,"pmmsBenchmark":3.83,"originationDate":"2020-07-15","holder":"Walker & Dunlop","red":true,"belowMarket":true,"historicLowEra":false,"redReasons":["Rate 2.58% is 1.25pt below the 3.83% PMMS month avg"]},{"property":"FHA Multifamily Project 4","city":"Glendale","state":"CA","zip":"90004","units":408,"originalAmount":5004359,"rate":2.85,"pmmsBenchmark":3.7,"originationDate":"2022-08-15","holder":"Dwight Capital","red":true,"belowMarket":true,"historicLowEra":false,"redReasons":["Rate 2.85% is 0.85pt below the 3.7% PMMS month avg"]},{"property":"FHA Multifamily Project 5","city":"Santa Monica","state":"CA","zip":"90005","units":156,"originalAmount":36443364,"rate":3.11,"pmmsBenchmark":3.61,"originationDate":"2022-01-15","holder":"Merchants Capital","red":false,"belowMarket":false,"historicLowEra":false,"redReasons":[]},{"property":"FHA Multifamily Project 6","city":"Burbank","state":"CA","zip":"90006","units":274,"originalAmount":28410631,"rate":2.9,"pmmsBenchmark":3.92,"originationDate":"2022-12-15","holder":"Lument","red":true,"belowMarket":true,"historicLowEra":true,"redReasons":["Rate 2.9% is 1.02pt below the 3.92% PMMS month avg","Closed inside a historic-low-rate era window"]},{"property":"FHA Multifamily Project 7","city":"Torrance","state":"CA","zip":"90007","units":80,"originalAmount":15157238,"rate":3.48,"pmmsBenchmark":3.27,"originationDate":"2022-08-15","holder":"Berkadia","red":false,"belowMarket":false,"historicLowEra":false,"redReasons":[]},{"property":"FHA Multifamily Project 8","city":"Inglewood","state":"CA","zip":"90008","units":379,"originalAmount":42672939,"rate":2.67,"pmmsBenchmark":3.26,"originationDate":"2021-02-15","holder":"Greystone","red":false,"belowMarket":false,"historicLowEra":false,"redReasons":[]},{"property":"FHA Multifamily Project 9","city":"Los Angeles","state":"CA","zip":"90009","units":306,"originalAmount":26221358,"rate":3.24,"pmmsBenchmark":3.44,"originationDate":"2022-09-15","holder":"Walker & Dunlop","red":true,"belowMarket":false,"historicLowEra":true,"redReasons":["Closed inside a historic-low-rate era window"]},{"property":"FHA Multifamily Project 10","city":"Long Beach","state":"CA","zip":"90010","units":405,"originalAmount":39215249,"rate":2.98,"pmmsBenchmark":3.98,"originationDate":"2019-06-15","holder":"Dwight Capital","red":true,"belowMarket":true,"historicLowEra":true,"redReasons":["Rate 2.98% is 1.00pt below the 3.98% PMMS month avg","Closed inside a historic-low-rate era window"]},{"property":"FHA Multifamily Project 11","city":"Pasadena","state":"CA","zip":"90011","units":428,"originalAmount":43519815,"rate":3.37,"pmmsBenchmark":3.2,"originationDate":"2020-08-15","holder":"Merchants Capital","red":true,"belowMarket":false,"historicLowEra":true,"redReasons":["Closed inside a historic-low-rate era window"]},{"property":"FHA Multifamily Project 12","city":"Glendale","state":"CA","zip":"90012","units":307,"originalAmount":18447952,"rate":2.96,"pmmsBenchmark":3.89,"originationDate":"2023-02-15","holder":"Lument","red":true,"belowMarket":true,"historicLowEra":true,"redReasons":["Rate 2.96% is 0.93pt below the 3.89% PMMS month avg","Closed inside a historic-low-rate era window"]},{"property":"FHA Multifamily Project 13","city":"Santa Monica","state":"CA","zip":"90013","units":354,"originalAmount":8251572,"rate":2.73,"pmmsBenchmark":3.38,"originationDate":"2023-09-15","holder":"Berkadia","red":false,"belowMarket":false,"historicLowEra":false,"redReasons":[]},{"property":"FHA Multifamily Project 14","city":"Burbank","state":"CA","zip":"90014","units":435,"originalAmount":31007818,"rate":2.79,"pmmsBenchmark":3.52,"originationDate":"2020-07-15","holder":"Greystone","red":true,"belowMarket":false,"historicLowEra":true,"redReasons":["Closed inside a historic-low-rate era window"]},{"property":"FHA Multifamily Project 15","city":"Torrance","state":"CA","zip":"90015","units":126,"originalAmount":33074711,"rate":2.89,"pmmsBenchmark":3.69,"originationDate":"2023-03-15","holder":"Walker & Dunlop","red":true,"belowMarket":true,"historicLowEra":false,"redReasons":["Rate 2.89% is 0.80pt below the 3.69% PMMS month avg"]},{"property":"FHA Multifamily Project 16","city":"Inglewood","state":"CA","zip":"90016","units":367,"originalAmount":22584128,"rate":3.19,"pmmsBenchmark":3.48,"originationDate":"2019-04-15","holder":"Dwight Capital","red":true,"belowMarket":false,"historicLowEra":true,"redReasons":["Closed inside a historic-low-rate era window"]},{"property":"FHA Multifamily Project 17","city":"Los Angeles","state":"CA","zip":"90017","units":144,"originalAmount":35024215,"rate":3.35,"pmmsBenchmark":3.55,"originationDate":"2022-09-15","holder":"Merchants Capital","red":false,"belowMarket":false,"historicLowEra":false,"redReasons":[]},{"property":"FHA Multifamily Project 18","city":"Long Beach","state":"CA","zip":"90018","units":79,"originalAmount":14724316,"rate":2.49,"pmmsBenchmark":3.88,"originationDate":"2018-10-15","holder":"Lument","red":true,"belowMarket":true,"historicLowEra":false,"redReasons":["Rate 2.49% is 1.39pt below the 3.88% PMMS month avg"]},{"property":"FHA Multifamily Project 19","city":"Pasadena","state":"CA","zip":"90019","units":144,"originalAmount":7294588,"rate":2.31,"pmmsBenchmark":4.09,"originationDate":"2018-08-15","holder":"Berkadia","red":true,"belowMarket":true,"historicLowEra":false,"redReasons":["Rate 2.31% is 1.78pt below the 4.09% PMMS month avg"]},{"property":"FHA Multifamily Project 20","city":"Glendale","state":"CA","zip":"90020","units":194,"originalAmount":21825089,"rate":3.02,"pmmsBenchmark":3.64,"originationDate":"2022-06-15","holder":"Greystone","red":true,"belowMarket":false,"historicLowEra":true,"redReasons":["Closed inside a historic-low-rate era window"]},{"property":"FHA Multifamily Project 21","city":"Santa Monica","state":"CA","zip":"90021","units":330,"originalAmount":7263977,"rate":3.12,"pmmsBenchmark":3.32,"originationDate":"2019-01-15","holder":"Walker & Dunlop","red":false,"belowMarket":false,"historicLowEra":false,"redReasons":[]},{"property":"FHA Multifamily Project 22","city":"Burbank","state":"CA","zip":"90022","units":379,"originalAmount":37700384,"rate":2.68,"pmmsBenchmark":3.36,"originationDate":"2023-07-15","holder":"Dwight Capital","red":true,"belowMarket":false,"historicLowEra":true,"redReasons":["Closed inside a historic-low-rate era window"]},{"property":"FHA Multifamily Project 23","city":"Torrance","state":"CA","zip":"90023","units":127,"originalAmount":27307712,"rate":2.88,"pmmsBenchmark":4.04,"originationDate":"2021-11-15","holder":"Merchants Capital","red":true,"belowMarket":true,"historicLowEra":true,"redReasons":["Rate 2.88% is 1.16pt below the 4.04% PMMS month avg","Closed inside a historic-low-rate era window"]},{"property":"FHA Multifamily Project 24","city":"Inglewood","state":"CA","zip":"90024","units":177,"originalAmount":21587335,"rate":2.37,"pmmsBenchmark":3.2,"originationDate":"2020-03-15","holder":"Lument","red":true,"belowMarket":true,"historicLowEra":false,"redReasons":["Rate 2.37% is 0.83pt below the 3.2% PMMS month avg"]},{"property":"FHA Multifamily Project 25","city":"Los Angeles","state":"CA","zip":"90025","units":293,"originalAmount":5289543,"rate":2.76,"pmmsBenchmark":3.82,"originationDate":"2018-09-15","holder":"Berkadia","red":true,"belowMarket":true,"historicLowEra":false,"redReasons":["Rate 2.76% is 1.06pt below the 3.82% PMMS month avg"]},{"property":"FHA Multifamily Project 26","city":"Long Beach","state":"CA","zip":"90026","units":390,"originalAmount":24314047,"rate":2.74,"pmmsBenchmark":3.48,"originationDate":"2021-03-15","holder":"Greystone","red":false,"belowMarket":false,"historicLowEra":false,"redReasons":[]},{"property":"FHA Multifamily Project 27","city":"Pasadena","state":"CA","zip":"90027","units":449,"originalAmount":17597813,"rate":2.47,"pmmsBenchmark":3.97,"originationDate":"2022-09-15","holder":"Walker & Dunlop","red":true,"belowMarket":true,"historicLowEra":false,"redReasons":["Rate 2.47% is 1.50pt below the 3.97% PMMS month avg"]},{"property":"FHA Multifamily Project 28","city":"Glendale","state":"CA","zip":"90028","units":284,"originalAmount":15275646,"rate":2.58,"pmmsBenchmark":3.47,"originationDate":"2021-01-15","holder":"Dwight Capital","red":true,"belowMarket":true,"historicLowEra":true,"redReasons":["Rate 2.58% is 0.89pt below the 3.47% PMMS month avg","Closed inside a historic-low-rate era window"]},{"property":"FHA Multifamily Project 29","city":"Santa Monica","state":"CA","zip":"90029","units":314,"originalAmount":18791384,"rate":3.23,"pmmsBenchmark":4.06,"originationDate":"2019-04-15","holder":"Merchants Capital","red":true,"belowMarket":true,"historicLowEra":true,"redReasons":["Rate 3.23% is 0.83pt below the 4.06% PMMS month avg","Closed inside a historic-low-rate era window"]},{"property":"FHA Multifamily Project 30","city":"Burbank","state":"CA","zip":"90030","units":328,"originalAmount":32095854,"rate":2.9,"pmmsBenchmark":3.43,"originationDate":"2021-07-15","holder":"Lument","red":false,"belowMarket":false,"historicLowEra":false,"redReasons":[]},{"property":"FHA Multifamily Project 31","city":"Torrance","state":"CA","zip":"90031","units":427,"originalAmount":37838244,"rate":3.39,"pmmsBenchmark":4.12,"originationDate":"2020-01-15","holder":"Berkadia","red":false,"belowMarket":false,"historicLowEra":false,"redReasons":[]},{"property":"FHA Multifamily Project 32","city":"Inglewood","state":"CA","zip":"90032","units":197,"originalAmount":29346107,"rate":3.13,"pmmsBenchmark":3.82,"originationDate":"2022-07-15","holder":"Greystone","red":false,"belowMarket":false,"historicLowEra":false,"redReasons":[]},{"property":"FHA Multifamily Project 33","city":"Los Angeles","state":"CA","zip":"90033","units":432,"originalAmount":21053413,"rate":2.5,"pmmsBenchmark":3.68,"originationDate":"2020-12-15","holder":"Walker & Dunlop","red":true,"belowMarket":true,"historicLowEra":true,"redReasons":["Rate 2.5% is 1.18pt below the 3.68% PMMS month avg","Closed inside a historic-low-rate era window"]},{"property":"FHA Multifamily Project 34","city":"Long Beach","state":"CA","zip":"90034","units":151,"originalAmount":29268872,"rate":3.22,"pmmsBenchmark":4.05,"originationDate":"2023-04-15","holder":"Dwight Capital","red":true,"belowMarket":true,"historicLowEra":true,"redReasons":["Rate 3.22% is 0.83pt below the 4.05% PMMS month avg","Closed inside a historic-low-rate era window"]},{"property":"FHA Multifamily Project 35","city":"Pasadena","state":"CA","zip":"90035","units":339,"originalAmount":36363192,"rate":3.46,"pmmsBenchmark":4.06,"originationDate":"2019-07-15","holder":"Merchants Capital","red":false,"belowMarket":false,"historicLowEra":false,"redReasons":[]},{"property":"FHA Multifamily Project 36","city":"Glendale","state":"CA","zip":"90036","units":430,"originalAmount":15273065,"rate":3.43,"pmmsBenchmark":3.3,"originationDate":"2021-11-15","holder":"Lument","red":true,"belowMarket":false,"historicLowEra":true,"redReasons":["Closed inside a historic-low-rate era window"]},{"property":"FHA Multifamily Project 37","city":"Santa Monica","state":"CA","zip":"90037","units":216,"originalAmount":23840367,"rate":2.57,"pmmsBenchmark":3.25,"originationDate":"2020-04-15","holder":"Berkadia","red":true,"belowMarket":false,"historicLowEra":true,"redReasons":["Closed inside a historic-low-rate era window"]},{"property":"FHA Multifamily Project 38","city":"Burbank","state":"CA","zip":"90038","units":245,"originalAmount":38350290,"rate":3.15,"pmmsBenchmark":3.59,"originationDate":"2022-11-15","holder":"Greystone","red":false,"belowMarket":false,"historicLowEra":false,"redReasons":[]},{"property":"FHA Multifamily Project 39","city":"Torrance","state":"CA","zip":"90039","units":316,"originalAmount":36776282,"rate":2.61,"pmmsBenchmark":3.64,"originationDate":"2019-05-15","holder":"Walker & Dunlop","red":true,"belowMarket":true,"historicLowEra":true,"redReasons":["Rate 2.61% is 1.03pt below the 3.64% PMMS month avg","Closed inside a historic-low-rate era window"]},{"property":"FHA Multifamily Project 40","city":"Inglewood","state":"CA","zip":"90040","units":395,"originalAmount":10509112,"rate":3.01,"pmmsBenchmark":3.77,"originationDate":"2020-03-15","holder":"Dwight Capital","red":true,"belowMarket":true,"historicLowEra":false,"redReasons":["Rate 3.01% is 0.76pt below the 3.77% PMMS month avg"]}]}
\ No newline at end of file
diff --git a/scripts/db/brokers-db.js b/scripts/db/brokers-db.js
index 7a31ae9..14daf7f 100644
--- a/scripts/db/brokers-db.js
+++ b/scripts/db/brokers-db.js
@@ -81,6 +81,95 @@ async function graph(limit = 400) {
return { nodes, edges, stats };
}
+// graph() with ?history=1 — additive superset of graph():
+// - same nodes (same id scheme, same fields)
+// - existing 'employs' edges tagged with current:true
+// - 'colist' edges from broker_cobroker (broker↔broker co-listing pairs), capped at 300 pairs
+// - 'worked_at' edges from broker_firm_history where is_current=false (empty today — expected)
+const COLIST_CAP = 300; // cap co-listing edges to keep graph readable; logged when hit
+async function graphHistory(limit = 400) {
+ // Re-use the same broker + firm node construction as graph()
+ const brokers = (await pool.query(
+ `SELECT bn.*, b.website, b.linkedin, b.office_addr, b.total_assets
+ FROM broker_node bn JOIN broker b ON b.id = bn.id
+ ORDER BY bn.listings DESC NULLS LAST LIMIT $1`, [limit])).rows;
+ const ids = new Set(brokers.map(b => b.id));
+
+ // ── Colist edges (from broker_cobroker view) ──────────────────────────────
+ const allCo = (await pool.query(
+ `SELECT a, b, shared_listings FROM broker_cobroker ORDER BY shared_listings DESC`)).rows
+ .filter(e => ids.has(e.a) && ids.has(e.b));
+ const capped = allCo.length > COLIST_CAP;
+ if (capped) console.log(`[graphHistory] colist edges capped at ${COLIST_CAP} (total available: ${allCo.length})`);
+ const co = allCo.slice(0, COLIST_CAP);
+
+ // ── Past-firm edges (broker_firm_history, is_current=false only) ──────────
+ const pastRows = (await pool.query(
+ `SELECT bfh.broker_id, bfh.firm_id, COALESCE(bfh.firm_name, f.name) AS firm_name,
+ bfh.title, bfh.start_date, bfh.end_date, bfh.source
+ FROM broker_firm_history bfh
+ LEFT JOIN firm f ON f.id = bfh.firm_id
+ WHERE bfh.is_current = false`)).rows;
+
+ // ── Build nodes (same shape as graph()) ──────────────────────────────────
+ const firms = {};
+ brokers.forEach(b => { if (b.firm) firms[b.firm] = (firms[b.firm] || 0) + 1; });
+
+ // Also collect firm names referenced only in history (past firms not in current node set)
+ const pastFirmNames = new Set();
+ pastRows.forEach(r => {
+ if (r.firm_name && ids.has(r.broker_id)) pastFirmNames.add(r.firm_name);
+ });
+ pastFirmNames.forEach(name => { if (!firms[name]) firms[name] = 0; });
+
+ 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', dbId: b.id, label: b.name, firm: b.firm,
+ listings: +b.listings, total: b.total_assets, agent_type: b.agent_type,
+ phone: b.phone, email: b.email, website: b.website, linkedin: b.linkedin,
+ office_addr: b.office_addr }));
+
+ // ── Build edges ───────────────────────────────────────────────────────────
+ const edges = [];
+
+ // employs: current firm edges, tagged with kind:'employs' + current:true
+ brokers.forEach(b => {
+ if (b.firm) edges.push({ source: 'broker:' + b.id, target: 'firm:' + b.firm, kind: 'employs', current: true });
+ });
+
+ // colist: broker↔broker co-listing pairs (weighted by shared deal count)
+ co.forEach(e =>
+ edges.push({ source: 'broker:' + e.a, target: 'broker:' + e.b, kind: 'colist', weight: e.shared_listings }));
+
+ // worked_at: past-firm edges for brokers in the current node set
+ pastRows
+ .filter(r => ids.has(r.broker_id) && r.firm_name)
+ .forEach(r =>
+ edges.push({
+ source: 'broker:' + r.broker_id,
+ target: 'firm:' + r.firm_name,
+ kind: 'worked_at',
+ current: false,
+ ...(r.title ? { title: r.title } : {}),
+ ...(r.start_date ? { start_date: r.start_date } : {}),
+ ...(r.end_date ? { end_date: r.end_date } : {}),
+ ...(r.source ? { source: r.source } : {})
+ }));
+
+ 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,
+ colist_total: allCo.length,
+ colist_capped: capped,
+ worked_at: pastRows.length
+ };
+ return { nodes, edges, stats };
+}
+
async function topBrokers(limit = 50) {
// id + website ride along so snapshot consumers (prod CRCP top-brokers table) can open the
// contact card and drive the "✉ find email" button — broker_node lacks website, hence the join.
@@ -125,4 +214,4 @@ async function enrichStats() {
FROM broker`)).rows[0];
}
-module.exports = { pool, upsertFirm, upsertBroker, upsertListing, link, graph, topBrokers, brokerContact, enrichStats };
+module.exports = { pool, upsertFirm, upsertBroker, upsertListing, link, graph, graphHistory, topBrokers, brokerContact, enrichStats };
diff --git a/scripts/db/migrations/20260731_broker_firm_history.sql b/scripts/db/migrations/20260731_broker_firm_history.sql
new file mode 100644
index 0000000..0a9224f
--- /dev/null
+++ b/scripts/db/migrations/20260731_broker_firm_history.sql
@@ -0,0 +1,62 @@
+-- Migration: broker_firm_history
+-- Tracks a broker's tenure at each firm (current + past).
+-- Idempotent: safe to re-run at any time.
+-- Run with:
+-- psql -h /tmp -d cre -f scripts/db/migrations/20260731_broker_firm_history.sql
+-- On Kamatera (Steve-gated deploy):
+-- psql -h /tmp -U <user> -d cre -f scripts/db/migrations/20260731_broker_firm_history.sql
+
+BEGIN;
+
+-- ── Table ─────────────────────────────────────────────────────────────────────
+CREATE TABLE IF NOT EXISTS broker_firm_history (
+ id serial PRIMARY KEY,
+ broker_id int NOT NULL REFERENCES broker(id) ON DELETE CASCADE,
+ firm_id int REFERENCES firm(id),
+ firm_name text, -- denorm for rows where firm is not in firm table
+ title text,
+ start_date date,
+ end_date date,
+ is_current boolean NOT NULL DEFAULT false,
+ source text,
+ source_url text,
+ tier int,
+ found_at timestamptz DEFAULT now()
+);
+
+-- ── Indexes ───────────────────────────────────────────────────────────────────
+CREATE INDEX IF NOT EXISTS idx_bfh_broker ON broker_firm_history(broker_id);
+CREATE INDEX IF NOT EXISTS idx_bfh_firm ON broker_firm_history(firm_id);
+
+-- ── Uniqueness guard (prevents duplicate backfill rows) ───────────────────────
+-- One active (is_current=true) row per broker+firm, plus any number of past rows
+-- identified by start_date. NULLs in a UNIQUE index are not equal in PG, so
+-- past rows without a start_date would collide; use a partial unique index for
+-- the current row only — that's all we need to make the backfill idempotent.
+CREATE UNIQUE INDEX IF NOT EXISTS idx_bfh_current_uniq
+ ON broker_firm_history(broker_id, firm_id)
+ WHERE is_current = true;
+
+-- ── Backfill: seed current-firm rows from broker table ────────────────────────
+-- Every broker with a firm_id gets one is_current=true row.
+-- ON CONFLICT DO NOTHING makes this re-runnable.
+INSERT INTO broker_firm_history (broker_id, firm_id, firm_name, is_current, source)
+SELECT
+ b.id AS broker_id,
+ b.firm_id,
+ f.name AS firm_name,
+ true AS is_current,
+ 'backfill' AS source
+FROM broker b
+JOIN firm f ON f.id = b.firm_id
+WHERE b.firm_id IS NOT NULL
+ON CONFLICT DO NOTHING;
+
+COMMIT;
+
+-- ── Row-count report (run after commit) ──────────────────────────────────────
+SELECT
+ count(*) AS total_rows,
+ count(*) FILTER (WHERE is_current) AS current_rows,
+ count(*) FILTER (WHERE NOT is_current) AS past_rows
+FROM broker_firm_history;
diff --git a/scripts/serve.js b/scripts/serve.js
index 8dcb7de..caac6d0 100644
--- a/scripts/serve.js
+++ b/scripts/serve.js
@@ -224,7 +224,15 @@ Rules: explain in prose ABOVE the block. Only emit an action block when the user
const snapGraph = () => { const s = readBrokerSnap(); return s && s.graph ? s.graph : { nodes: [], edges: [], stats: {}, unavailable: true }; };
app.get('/api/graph', async (req, res) => {
if (!brokerdb) return res.json(snapGraph());
- try { res.json(await brokerdb.graph(+(req.query.limit) || 400)); }
+ try {
+ const limit = +(req.query.limit) || 400;
+ // ?history=1 → additive superset: adds colist + worked_at edges, tags employs with current:true
+ // Default (no ?history) → exact same response as before — employs edges only, no extra tags
+ if (req.query.history === '1') {
+ return res.json(await brokerdb.graphHistory(limit));
+ }
+ res.json(await brokerdb.graph(limit));
+ }
catch (e) { res.json(snapGraph()); }
});
app.get('/api/brokers/top', async (req, res) => {
← 439898b CRCP list sweep: apply 7-control list-UX standard to sibling
·
back to Commercialrealestate
·
CRCP list sweep (round 2): deferred pages + Explorer flagshi add9ada →