← back to Commercialrealestate
CRE :9911 — 'Best leads for Frank' ranked list: expandable MLS-style current listings + Sold button + 100-yr address-only property history
69ac4d3ef2e6bf1d1c6e9507765ac6f09b41ec4b · 2026-06-30 13:55:01 -0700 · Steve Abrams
- serve.js: GET /api/leads/frank (brokers ranked by Arcstone-financeability of current listings — mixed-use/condo x3, multifamily/commercial x2; excludes Frank; returns listings_json) + GET /api/property-history (address-only, NO owner names — assessor parcel roll + closed_sale sold history + WhoLivedThere permits/events).
- index.html: front-page section is now 🎯 Best leads for Frank — ranked, click-to-expand leads; each shows MLS-style current listings (address/type/price/units/cap), a Sold/area-history button (area sold comps), and per-listing 📜 100-yr property history popup (no names). Featured Frank card + contact/pitch modal retained.
Verified headless: 40 leads, expand 16 MLS rows, history popup + Sold all work, 0 console errors. Assessor facts (year built/beds/baths) populate once the 2.43M-roll ingest lands.
Files touched
M public/index.htmlM scripts/serve.js
Diff
commit 69ac4d3ef2e6bf1d1c6e9507765ac6f09b41ec4b
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Tue Jun 30 13:55:01 2026 -0700
CRE :9911 — 'Best leads for Frank' ranked list: expandable MLS-style current listings + Sold button + 100-yr address-only property history
- serve.js: GET /api/leads/frank (brokers ranked by Arcstone-financeability of current listings — mixed-use/condo x3, multifamily/commercial x2; excludes Frank; returns listings_json) + GET /api/property-history (address-only, NO owner names — assessor parcel roll + closed_sale sold history + WhoLivedThere permits/events).
- index.html: front-page section is now 🎯 Best leads for Frank — ranked, click-to-expand leads; each shows MLS-style current listings (address/type/price/units/cap), a Sold/area-history button (area sold comps), and per-listing 📜 100-yr property history popup (no names). Featured Frank card + contact/pitch modal retained.
Verified headless: 40 leads, expand 16 MLS rows, history popup + Sold all work, 0 console errors. Assessor facts (year built/beds/baths) populate once the 2.43M-roll ingest lands.
---
public/index.html | 84 ++++++++++++++++++++++++++++++++++++++++++++++---------
scripts/serve.js | 66 +++++++++++++++++++++++++++++++++++++++++++
2 files changed, 137 insertions(+), 13 deletions(-)
diff --git a/public/index.html b/public/index.html
index 8d33dfb..5f2f809 100644
--- a/public/index.html
+++ b/public/index.html
@@ -218,12 +218,28 @@
.pitch{background:#0a0d13;border:1px solid var(--line);border-radius:10px;padding:12px;font-size:13px;white-space:pre-wrap;margin-top:10px;}
.pitch .subj{color:var(--warn);font-weight:700;margin-bottom:6px;}
.gate{font-size:12px;color:var(--mut);margin-top:8px;}
+ .lead{border:1px solid var(--line);border-radius:11px;margin-bottom:8px;overflow:hidden;background:rgba(255,255,255,.012);}
+ .lead .lhead{display:flex;align-items:center;gap:12px;padding:11px 14px;cursor:pointer;}
+ .lead .lhead:hover{background:rgba(88,166,255,.06);}
+ .lead .rk{font-weight:700;color:var(--warn);width:24px;text-align:center;font-variant-numeric:tabular-nums;}
+ .lead .who{flex:1;min-width:0;} .lead .who .nm{font-weight:600;} .lead .who .fm{color:var(--mut);font-size:12px;}
+ .lead .fit{font-size:11px;font-weight:700;color:var(--acc);background:rgba(63,185,80,.13);border:1px solid rgba(63,185,80,.35);border-radius:8px;padding:2px 8px;white-space:nowrap;}
+ .lead .meta{color:var(--mut);font-size:12px;white-space:nowrap;}
+ .lead .car{color:var(--mut);transition:transform .15s;} .lead.open .car{transform:rotate(90deg);}
+ .lead .body{display:none;padding:4px 14px 12px;border-top:1px solid var(--line);}
+ .lead.open .body{display:block;}
+ .lead .acts{display:flex;gap:8px;margin:10px 0 6px;flex-wrap:wrap;}
+ .mls{width:100%;border-collapse:collapse;font-size:12.5px;}
+ .mls th,.mls td{text-align:left;padding:5px 7px;border-top:1px solid var(--line);} .mls th{color:var(--mut);font-size:10px;text-transform:uppercase;letter-spacing:.4px;border-top:0;}
+ .mls td.n{text-align:right;font-variant-numeric:tabular-nums;} .mls tr.mrow{cursor:pointer;} .mls tr.mrow:hover td{background:rgba(210,153,34,.07);}
+ .mls .hist{color:var(--blue);font-size:11px;}
+ .phitem{display:flex;justify-content:space-between;gap:10px;padding:4px 0;border-bottom:1px solid var(--line);font-size:13px;} .phitem .m{color:var(--mut);}
</style>
<section id="brokers">
- <h2>👤 Brokers & Agents</h2>
- <div class="bksub">Everyone connected to these properties — <b>name, company, phone, email</b>. Click anyone for full contact + a ready-to-send Arcstone pitch.</div>
+ <h2>🎯 Best leads for Frank</h2>
+ <div class="bksub">Brokers ranked by how financeable their <b>current listings</b> are for Arcstone. Click a lead to expand their listings (MLS-style) + a <b>Sold</b> button. Each property → <b>100-yr history by address</b> (no owner names). Contact + pitch on the ✉ button.</div>
<div id="bkfeat"></div>
- <table id="bktbl"><thead><tr><th>Name</th><th>Company</th><th>Phone</th><th>Email</th><th style="text-align:right">Listings</th></tr></thead><tbody id="bkbody"></tbody></table>
+ <div id="bkleads"><div class="gate">loading ranked leads…</div></div>
</section>
<div id="bkov"><div id="bkmodal"><button class="x" aria-label="Close" onclick="document.getElementById('bkov').classList.remove('on')">✕</button><div id="bkmbody"></div></div></div>
<script>
@@ -265,16 +281,58 @@
+'<div class="ci">'+(b.phone?'<a href="tel:'+esc(b.phone)+'">☎ '+esc(b.phone)+'</a>':'')+(b.email?'<a href="mailto:'+esc(b.email)+'">✉ '+esc(b.email)+'</a>':'')+'<span class="pill">View pitch →</span></div></div>';
bq('#bkfeatcard').onclick=()=>openContact(b.name,b.id);
}).catch(()=>{});
- // Contact roster: name / company / phone / email for the most-connected brokers.
- fetch('/api/crcp/stats').then(r=>r.json()).then(s=>{
- const rows=(s.topBrokers||[]).filter(b=>b.id!==FRANK_ID).slice(0,25);
- bq('#bkbody').innerHTML=rows.map(b=>'<tr class="bkrow" data-id="'+b.id+'" data-name="'+esc(b.name)+'">'
- +'<td>'+esc(b.name)+atype(b.agent_type)+'</td><td style="color:var(--mut)">'+esc(b.firm||'—')+'</td>'
- +'<td>'+(b.phone?'<a href="tel:'+esc(b.phone)+'">'+esc(b.phone)+'</a>':'<span class="miss">—</span>')+'</td>'
- +'<td>'+(b.email?'<a href="mailto:'+esc(b.email)+'">'+esc(b.email)+'</a>':'<span class="miss">—</span>')+'</td>'
- +'<td style="text-align:right;color:var(--acc);font-weight:600">'+(b.listings||0)+'</td></tr>').join('');
- bq('#bkbody').onclick=e=>{ const tr=e.target.closest('.bkrow'); if(!tr||e.target.tagName==='A') return; openContact(tr.dataset.name,tr.dataset.id); };
- }).catch(()=>{ bq('#bkbody').innerHTML='<tr><td colspan="5" class="gate">broker DB unavailable</td></tr>'; });
+ const moneyK=n=>{ if(n==null) return '—'; n=+n; return n>=1e6?'$'+(n/1e6).toFixed(2)+'M':'$'+Math.round(n/1e3)+'k'; };
+ // 100-year property history popup — address-only, NO owner/resident names (by design).
+ async function propHistory(address,city){
+ const ov=bq('#bkov'), mb=bq('#bkmbody'); ov.classList.add('on'); mb.innerHTML='<div class="gate">loading history for '+esc(address)+'…</div>';
+ let d; try{ d=await (await fetch('/api/property-history?address='+encodeURIComponent(address)+'&city='+encodeURIComponent(city||''))).json(); }catch(e){ mb.innerHTML='<div class="gate">error</div>'; return; }
+ const a=d.assessor||d.assessorArchive;
+ let h='<h3>'+esc(address)+'</h3><div class="mfirm">'+esc(city||'')+' · property history (address only — no names)</div>';
+ if(a){ h+='<div class="sec"><h4>Assessor parcel'+(a.roll_year?' ('+esc(a.roll_year)+' roll)':'')+'</h4><div class="ci">'
+ +(a.year_built?'<span>🏗 Built '+a.year_built+'</span>':'')
+ +(a.bedrooms!=null?'<span>🛏 '+a.bedrooms+' bd</span>':'')+(a.bathrooms!=null?'<span>🛁 '+a.bathrooms+' ba</span>':'')
+ +(a.sqft_main?'<span>📐 '+Number(a.sqft_main).toLocaleString()+' sqft</span>':'')+(a.units?'<span>🏠 '+a.units+' units</span>':'')
+ +((a.use_desc2||a.use_desc1||a.use_description)?'<span>🏷 '+esc(a.use_desc2||a.use_desc1||a.use_description)+'</span>':'')
+ +((a.roll_total_value||a.total_value)?'<span>💰 assessed '+money(a.roll_total_value||a.total_value)+'</span>':'')+'</div></div>'; }
+ h+='<div class="sec"><h4>Sold history ('+d.sales.length+')</h4>'+(d.sales.length?d.sales.map(x=>'<div class="phitem"><span>'+esc(x.address)+(x.city?', '+esc(x.city):'')+'</span><span class="m">'+money(x.sold_price)+' · '+String(x.sold_date||'').slice(0,10)+'</span></div>').join(''):'<div class="gate">no sold records on file for this address</div>')+'</div>';
+ if(d.permits&&d.permits.length) h+='<div class="sec"><h4>Permits ('+d.permits.length+')</h4>'+d.permits.map(p=>'<div class="phitem"><span>'+esc(p.permit_type||p.use_description||'permit')+'</span><span class="m">'+esc(String(p.issue_date||'').slice(0,10))+(p.valuation?' · '+money(p.valuation):'')+'</span></div>').join('')+'</div>';
+ if(d.events&&d.events.length) h+='<div class="sec"><h4>Place history</h4>'+d.events.map(ev=>'<div class="phitem"><span>'+esc(ev.summary||ev.kind)+'</span><span class="m">'+esc(String(ev.valid_from||'').slice(0,10))+'</span></div>').join('')+'</div>';
+ if(!a && !d.sales.length && !(d.permits||[]).length) h+='<div class="gate">No history on file yet for this address. The 2.43M-parcel assessor roll is still ingesting — year built, beds/baths, and assessed value will appear here once it lands.</div>';
+ mb.innerHTML=h;
+ }
+ // Sold / area history for a lead (per-broker closings aren't in our data; show area sold comps).
+ async function brokerSold(id,btn){
+ const box=btn.closest('.body').querySelector('.soldbox'); box.innerHTML='<div class="gate">loading sold history…</div>';
+ try{ const d=await (await fetch('/api/broker?id='+id)).json(); const sold=d.comps||[];
+ box.innerHTML='<div class="gate">Per-broker closings need a gated detail-scrape; showing recent <b>area sold comps</b> in this broker’s cities ('+sold.length+'):</div>'
+ +(sold.length?sold.map(c=>'<div class="phitem"><span>'+esc(c.address)+', '+esc(c.city)+'</span><span class="m">'+money(c.sold_price)+' · '+String(c.sold_date||'').slice(0,10)+'</span></div>').join(''):'<div class="gate">none</div>');
+ }catch(e){ box.innerHTML='<div class="gate">'+esc(e.message)+'</div>'; }
+ }
+ // Ranked best leads for Frank — expandable to MLS-style current listings + Sold + per-address history.
+ fetch('/api/leads/frank?limit=40').then(r=>r.json()).then(d=>{
+ const leads=d.leads||[];
+ if(!leads.length){ bq('#bkleads').innerHTML='<div class="gate">no leads (broker DB unavailable)</div>'; return; }
+ bq('#bkleads').innerHTML=leads.map((l,i)=>{
+ const rows=(l.listings_json||[]).map(p=>'<tr class="mrow" data-addr="'+esc(p.address)+'" data-city="'+esc(p.city||'')+'">'
+ +'<td>'+esc(p.address)+'<div style="color:var(--mut);font-size:11px">'+esc(p.city||'')+(p.zip?' '+esc(p.zip):'')+'</div></td>'
+ +'<td>'+esc(p.type||'—')+'</td><td class="n">'+money(p.price)+'</td><td class="n">'+(p.units||'—')+'</td>'
+ +'<td class="n">'+(p.cap_rate!=null?p.cap_rate+'%':'—')+'</td><td class="hist">📜 history</td></tr>').join('');
+ return '<div class="lead" data-id="'+l.broker_id+'" data-name="'+esc(l.name)+'">'
+ +'<div class="lhead"><span class="rk">'+(i+1)+'</span>'
+ +'<div class="who"><span class="nm">'+esc(l.name)+'</span>'+atype(l.agent_type)+'<div class="fm">'+esc(l.firm||'—')+'</div></div>'
+ +'<span class="fit">fit '+l.fit_score+'</span><span class="meta">'+l.listings+' listings · '+moneyK(l.total_value)+'</span><span class="car">▸</span></div>'
+ +'<div class="body"><div class="acts"><button class="bkbtn" data-act="contact">✉ Contact + pitch</button>'
+ +'<button class="bkbtn" data-act="sold">🔴 Sold / area history</button></div>'
+ +'<table class="mls"><thead><tr><th>Address</th><th>Type</th><th>Price</th><th>Units</th><th>Cap</th><th></th></tr></thead><tbody>'+rows+'</tbody></table>'
+ +'<div class="soldbox"></div></div></div>';
+ }).join('');
+ bq('#bkleads').onclick=e=>{
+ const lead=e.target.closest('.lead'); if(!lead) return;
+ const mrow=e.target.closest('.mrow'); if(mrow){ propHistory(mrow.dataset.addr, mrow.dataset.city); return; }
+ const act=e.target.closest('[data-act]'); if(act){ if(act.dataset.act==='contact') openContact(lead.dataset.name, lead.dataset.id); else brokerSold(lead.dataset.id, act); return; }
+ if(e.target.closest('.lhead')) lead.classList.toggle('open');
+ };
+ }).catch(()=>{ bq('#bkleads').innerHTML='<div class="gate">broker DB unavailable</div>'; });
})();
</script>
<div class="bar">
diff --git a/scripts/serve.js b/scripts/serve.js
index c542a35..6bb3770 100644
--- a/scripts/serve.js
+++ b/scripts/serve.js
@@ -125,6 +125,72 @@ app.get('/api/brokers/linkedin', async (req, res) => {
} catch (e) { res.status(502).json({ error: String(e.message).split('\n')[0], people: [] }); }
});
+// Best leads for Frank — rank brokers by how financeable their CURRENT listings are for Arcstone's
+// non-QM / portfolio products (mixed-use, non-warrantable condo, DSCR multifamily — the deals
+// conventional lenders pass on). Returns ranked brokers WITH their current listings (MLS-like fields).
+// Business listing data only; excludes Frank himself (the loan officer, not a lead). No owner names.
+const FRANK_BROKER_ID = 5356;
+app.get('/api/leads/frank', async (req, res) => {
+ if (!brokerdb) return res.json({ leads: [], unavailable: true });
+ try {
+ const limit = Math.min(+(req.query.limit) || 40, 200);
+ const leads = (await brokerdb.pool.query(`
+ WITH ll AS (
+ SELECT b.id broker_id, b.name, f.name firm, b.agent_type,
+ l.id lid, l.address, l.city, l.zip, l.type, l.price, l.units, l.cap_rate,
+ CASE WHEN l.type ILIKE '%mixed%' THEN 3
+ WHEN l.type ILIKE '%condo%' THEN 3
+ WHEN l.type ILIKE '%multi%' THEN 2
+ WHEN l.type ILIKE ANY(ARRAY['%retail%','%office%','%industrial%','%commercial%']) THEN 2
+ ELSE 1 END AS fit
+ FROM broker b JOIN firm f ON f.id=b.firm_id
+ JOIN broker_listing bl ON bl.broker_id=b.id
+ JOIN listing l ON l.id=bl.listing_id
+ WHERE b.id <> ${FRANK_BROKER_ID})
+ SELECT broker_id, name, firm, agent_type,
+ count(*)::int listings, sum(fit)::int fit_score, coalesce(sum(price),0)::bigint total_value,
+ json_agg(json_build_object('id',lid,'address',address,'city',city,'zip',zip,'type',type,
+ 'price',price,'units',units,'cap_rate',cap_rate)
+ ORDER BY price DESC NULLS LAST) listings_json
+ FROM ll GROUP BY broker_id, name, firm, agent_type
+ ORDER BY fit_score DESC, total_value DESC LIMIT $1`, [limit])).rows;
+ res.json({ leads, scoredBy: 'Arcstone-financeability of current listings (mixed-use/condo×3, multifamily/commercial×2). Business data; no owner names.' });
+ } catch (e) { res.status(502).json({ error: String(e.message).split('\n')[0], leads: [] }); }
+});
+
+// Property history for ONE address — last ~100 yrs, ADDRESS-ONLY (no owner/resident names, by design).
+// Combines: the public assessor parcel roll (year built, beds/baths/sqft, use, valuation — current roll),
+// sold transactions from closed_sale (Redfin sold history), and the WhoLivedThere/pastdoor archive
+// (permits + curated place events) where available. Degrades gracefully if a source is empty/absent.
+app.get('/api/property-history', async (req, res) => {
+ const address = String(req.query.address || '').trim();
+ const city = String(req.query.city || '').trim();
+ if (!address) return res.status(400).json({ error: 'address required' });
+ const num = (address.match(/^\s*(\d+)/) || [])[1] || '';
+ const word = (address.replace(/^\s*\d+\s*/, '').match(/[a-z]{3,}/i) || [])[0] || '';
+ const out = { address, city, assessor: null, sales: [], permits: [], events: [], names: 'omitted by design' };
+ if (brokerdb && num && word) {
+ try {
+ out.assessor = (await brokerdb.pool.query(
+ `SELECT property_location, year_built, effective_year_built, sqft_main, bedrooms, bathrooms, units,
+ use_desc1, use_desc2, roll_total_value, roll_land_value, roll_imp_value, roll_year, recording_date
+ FROM assessor_parcel WHERE situs_house_no=$1 AND situs_street ILIKE $2
+ ORDER BY roll_year DESC NULLS LAST LIMIT 1`, [+num, '%' + word + '%'])).rows[0] || null;
+ } catch (_) { /* table not ingested yet — degrade */ }
+ try {
+ out.sales = (await brokerdb.pool.query(
+ `SELECT address, city, sold_price, sold_date, type FROM closed_sale
+ WHERE address ILIKE $1 AND address ILIKE $2 AND sold_price>0
+ ORDER BY sold_date DESC NULLS LAST LIMIT 25`, [num + '%', '%' + word + '%'])).rows;
+ } catch (_) {}
+ }
+ try {
+ const wlt = await lookupAddress(address, city);
+ if (wlt && wlt.matched) { out.permits = wlt.permits || []; out.events = wlt.events || []; if (!out.assessor && wlt.parcel) out.assessorArchive = wlt.parcel; }
+ } catch (_) {}
+ res.json(out);
+});
+
// Firm market-share: listings per firm (the broker graph's listing.firm_name). Powers the share chart.
app.get('/api/firms/share', async (req, res) => {
if (!brokerdb) return res.json([]);
← 6d18e70 auto-save: 2026-06-30T13:40:46 (1 files) — scripts/seed-fran
·
back to Commercialrealestate
·
Add DW-Wins-Dashboard.png — CRE build wins dashboard (emaile 531f7e7 →