← back to Commercialrealestate

auto-save: 2026-06-28T07:43:27 (2 files) — public/brokers.html scripts/db/brokers-db.js

44b6973e4a91c4f635d6d7e5f4133b11053628b3 · 2026-06-28 07:43:34 -0700 · Steve Abrams

Files touched

• M public/brokers.html
• M scripts/db/brokers-db.js

Diff

commit 44b6973e4a91c4f635d6d7e5f4133b11053628b3
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Sun Jun 28 07:43:34 2026 -0700

    auto-save: 2026-06-28T07:43:27 (2 files) — public/brokers.html scripts/db/brokers-db.js
---
 public/brokers.html      | 25 ++++++++++++++++++++++++-
 scripts/db/brokers-db.js | 40 +++++++++++++++++++++++++++++++++++++---
 2 files changed, 61 insertions(+), 4 deletions(-)

diff --git a/public/brokers.html b/public/brokers.html
index 3b15bff..21b29ec 100644
--- a/public/brokers.html
+++ b/public/brokers.html
@@ -83,7 +83,27 @@ function showDetail(id){
   } 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>');
+    // Inline contact (from the graph payload), then lazy-load the full enriched card + book.
+    const esc=s=>(s||'').replace(/</g,'&lt;');
+    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>`:''
+    ].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('')}`:'')+
+      `<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>`:'');
+    }).catch(()=>{ const bw=document.getElementById('bookwrap'); if(bw) bw.textContent=''; });
   }
   network.selectNodes([id]); network.focus(id,{scale:1.1,animation:true});
 }
@@ -100,6 +120,9 @@ fetch('/api/graph?limit=500').then(r=>r.json()).then(g=>{
   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>`;
+  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>'; });
 
 fetch('/api/brokers/top?limit=40').then(r=>r.json()).then(rows=>{
diff --git a/scripts/db/brokers-db.js b/scripts/db/brokers-db.js
index faf26ed..48028e5 100644
--- a/scripts/db/brokers-db.js
+++ b/scripts/db/brokers-db.js
@@ -50,7 +50,10 @@ async function link(brokerId, listingId, role) {
 // 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 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));
   const co = (await pool.query(`SELECT a, b, shared_listings FROM broker_cobroker`)).rows
     .filter(e => ids.has(e.a) && ids.has(e.b));
@@ -58,7 +61,8 @@ async function graph(limit = 400) {
   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 }));
+  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,
+    phone: b.phone, email: b.email, website: b.website, linkedin: b.linkedin, office_addr: b.office_addr }));
   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 }));
@@ -75,4 +79,34 @@ 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 };
+// Full enriched contact card for one broker (id), with per-field provenance + listing book.
+async function brokerContact(id) {
+  const b = (await pool.query(
+    `SELECT b.id, b.name, b.title, b.phone, b.email, b.website, b.linkedin, b.office_addr,
+            b.total_assets, b.enriched_at, f.name AS firm
+       FROM broker b LEFT JOIN firm f ON f.id=b.firm_id WHERE b.id=$1`, [id])).rows[0];
+  if (!b) return null;
+  b.provenance = (await pool.query(
+    `SELECT field, source_url, tier FROM broker_field_source WHERE broker_id=$1`, [id])).rows;
+  b.our_listings = (await pool.query(
+    `SELECT l.id, l.address, l.city, l.type, l.price FROM broker_listing bl
+       JOIN listing l ON l.id=bl.listing_id WHERE bl.broker_id=$1 ORDER BY l.price DESC NULLS LAST LIMIT 50`, [id])).rows;
+  b.other_listings = (await pool.query(
+    `SELECT title, address, city, state, price, asset_type, url FROM broker_other_listing
+      WHERE broker_id=$1 ORDER BY price DESC NULLS LAST LIMIT 50`, [id])).rows;
+  return b;
+}
+
+// Enrichment coverage rollup for the stats bar.
+async function enrichStats() {
+  return (await pool.query(
+    `SELECT count(*)::int total,
+            count(*) FILTER (WHERE phone IS NOT NULL)::int phone,
+            count(*) FILTER (WHERE email IS NOT NULL)::int email,
+            count(*) FILTER (WHERE website IS NOT NULL)::int website,
+            count(*) FILTER (WHERE linkedin IS NOT NULL)::int linkedin,
+            count(*) FILTER (WHERE phone IS NOT NULL OR email IS NOT NULL)::int contactable
+       FROM broker`)).rows[0];
+}
+
+module.exports = { pool, upsertFirm, upsertBroker, upsertListing, link, graph, topBrokers, brokerContact, enrichStats };

← d7ca649 CRCP: broker contact columns (phone/email/web) + click-throu  ·  back to Commercialrealestate  ·  CRE residential agents: feasibility probe (mainHouseInfo end 2d837a2 →