← back to Commercialrealestate
Broker card: current+expired+sales with dates/MLS#/url across both modals
03d912966c943bd159f2dd8f3dc960e93b9d7ff7 · 2026-06-30 16:21:09 -0700 · Steve Abrams
/api/broker: current listings now carry listed_at (+zip/units); new 'expired' (best-effort match of a
broker's listings against the sold feed — honest empty-state since there's no MLS off-market feed); 'sales'
(area sold comps) enriched with sold_date + MLS# + url + beds/baths/sqft. Front-page #bkmodal AND crcp.html
modal both render Current(dated) / Expired-off-market / Sales(dated+MLS+link) sections. name/email/phone/
website already present. Verified headless on front modal (all sections, dates, MLS#, 0 errors).
Files touched
M public/crcp.htmlM public/index.htmlM scripts/serve.js
Diff
commit 03d912966c943bd159f2dd8f3dc960e93b9d7ff7
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Tue Jun 30 16:21:09 2026 -0700
Broker card: current+expired+sales with dates/MLS#/url across both modals
/api/broker: current listings now carry listed_at (+zip/units); new 'expired' (best-effort match of a
broker's listings against the sold feed — honest empty-state since there's no MLS off-market feed); 'sales'
(area sold comps) enriched with sold_date + MLS# + url + beds/baths/sqft. Front-page #bkmodal AND crcp.html
modal both render Current(dated) / Expired-off-market / Sales(dated+MLS+link) sections. name/email/phone/
website already present. Verified headless on front modal (all sections, dates, MLS#, 0 errors).
---
public/crcp.html | 7 ++++---
public/index.html | 49 ++++++++++++++++++++++++++++++++++++++++++++++++-
scripts/serve.js | 29 +++++++++++++++++++++--------
3 files changed, 73 insertions(+), 12 deletions(-)
diff --git a/public/crcp.html b/public/crcp.html
index bed0bb1..e10dbe8 100644
--- a/public/crcp.html
+++ b/public/crcp.html
@@ -132,9 +132,10 @@ async function openContact(name, id, seg){
${w?`<a href="${w}" target="_blank" rel="noopener noreferrer">🌐 website</a>`:''}
<span>📦 ${b.agent_type==='residential'?(d.current.length+' condo listing'+(d.current.length===1?'':'s')+' (Redfin)'):((b.total_assets||'?')+' total listings (Crexi)')}</span>
</div>
- <div class="sec"><h4>Current listings (${d.current.length})</h4>${d.current.length?d.current.map(l=>`<div class="litem"><span class="a">${l.address}, ${l.city}</span><span class="m">${l.type} · ${money(l.price)}${l.cap_rate?' · '+l.cap_rate+'% cap':''}</span></div>`).join(''):'<div class="meta">none in our set</div>'}</div>
- <div class="sec"><h4>Closed listings — past 10 yrs (${d.closed.length})</h4>${d.closed.length?d.closed.map(l=>`<div class="litem"><span class="a">${l.address}, ${l.city}</span><span class="m">${money(l.sold_price)} · ${l.sold_date||''}</span></div>`).join(''):'<div class="meta">'+(d.closedNote||'none')+'</div>'}</div>
- ${(d.comps&&d.comps.length)?`<div class="sec"><h4>Recent area comps (${d.comps.length})</h4>${d.comps.map(c=>`<div class="litem"><span class="a">${c.address}, ${c.city}</span><span class="m">${money(c.sold_price)} · ${(c.sold_date||'').toString().slice(0,10)}</span></div>`).join('')}<div class="meta">${d.compsNote||''}</div></div>`:''}
+ <div class="sec"><h4>Current listings (${d.current.length})</h4>${d.current.length?d.current.map(l=>`<div class="litem"><span class="a">${l.address}, ${l.city}${l.zip?' '+l.zip:''}</span><span class="m">${l.type} · ${money(l.price)}${l.units?' · '+l.units+'u':''}${l.cap_rate?' · '+l.cap_rate+'% cap':''}${l.listed_at?' · listed '+(l.listed_at||'').toString().slice(0,10):''}</span></div>`).join(''):'<div class="meta">none in our set</div>'}</div>
+ <div class="sec"><h4>Expired / off-market (${(d.expired&&d.expired.length)||0})</h4>${(d.expired&&d.expired.length)?d.expired.map(x=>`<div class="litem"><span class="a">${x.address}, ${x.city}</span><span class="m">${money(x.list_price)} → sold ${money(x.sold_price)} · ${(x.sold_date||'').toString().slice(0,10)}${x.mls?' · MLS '+x.mls:''}${x.url?` · <a href="${x.url}" target="_blank" rel="noopener noreferrer">↗</a>`:''}</span></div>`).join(''):'<div class="meta">'+(d.expiredNote||'none')+'</div>'}</div>
+ <div class="sec"><h4>Closed listings — past 10 yrs (${d.closed.length})</h4>${d.closed.length?d.closed.map(l=>`<div class="litem"><span class="a">${l.address}, ${l.city}</span><span class="m">${money(l.sold_price)} · ${(l.sold_date||'').toString().slice(0,10)}</span></div>`).join(''):'<div class="meta">'+(d.closedNote||'none')+'</div>'}</div>
+ ${(d.comps&&d.comps.length)?`<div class="sec"><h4>Sales — area sold (${d.comps.length})</h4>${d.comps.map(c=>`<div class="litem"><span class="a">${c.address}, ${c.city}${c.beds?' · '+c.beds+'bd':''}${c.baths?'/'+c.baths+'ba':''}${c.sqft?' · '+Number(c.sqft).toLocaleString()+'sf':''}</span><span class="m">${money(c.sold_price)} · ${(c.sold_date||'').toString().slice(0,10)}${c.mls?' · MLS '+c.mls:''}${c.url?` · <a href="${c.url}" target="_blank" rel="noopener noreferrer">↗</a>`:''}</span></div>`).join('')}<div class="meta">${d.compsNote||''}</div></div>`:''}
<div class="sec"><h4>Arcstone pitch${window.__seg?' — '+window.__seg.replace(/-/g,' '):' (non-warrantable condo financing)'}</h4>
<button class="btn" onclick="genPitch('${name.replace(/'/g,"\\'")}','email',${b.id})">✉ Generate email pitch</button>
<button class="btn alt" onclick="genPitch('${name.replace(/'/g,"\\'")}','call_script',${b.id})">☎ Generate call script</button>
diff --git a/public/index.html b/public/index.html
index bddabb8..16879c7 100644
--- a/public/index.html
+++ b/public/index.html
@@ -93,6 +93,12 @@
.lcout a { color:var(--blue); text-decoration:none; }
.lcout .lc-item { padding:4px 0; border-top:1px solid var(--line); }
.small { font-size:11px; color:var(--mut); }
+ .statusline { font-size:12px; font-weight:600; font-variant-numeric:tabular-nums; padding:5px 9px; border-radius:8px; border:1px solid var(--line); background:rgba(255,255,255,.02); }
+ .statusline.ok { color:var(--acc); border-color:#1f6f37; }
+ .statusline.warn { color:var(--warn); border-color:#6b5320; }
+ .statusline.bad { color:var(--bad); border-color:#7d2b28; }
+ .statusline.mut { color:var(--mut); }
+ .statusline.none { color:var(--warn); border-color:#6b5320; border-style:dashed; }
.foot { color:var(--mut); font-size:12px; padding:0 20px 8px; }
.empty { padding:60px 20px; text-align:center; color:var(--mut); }
@@ -251,6 +257,7 @@
const atype=t=>t==='residential'?'<span class="bkatype res">Residential</span>':'<span class="bkatype com">Commercial</span>';
const FRANK_ID=5356;
function renderPitch(p){ return '<div class="pitch">'+(p.subject?'<div class="subj">'+esc(p.subject)+'</div>':'')+esc(p.body)+'</div><div class="gate">⚑ DRAFT, ready to send — actual sending is George-gated + Steve-approved, not sent from here.</div>'; }
+ const fdate=s=>{ if(!s) return ''; try{ return new Date(s).toLocaleDateString(undefined,{year:'numeric',month:'short',day:'numeric'}); }catch(e){ return ''; } };
async function openContact(name,id){
const ov=bq('#bkov'), mb=bq('#bkmbody'); ov.classList.add('on'); mb.innerHTML='<div class="gate">loading '+esc(name)+'…</div>';
const qs=id?'id='+encodeURIComponent(id):'name='+encodeURIComponent(name);
@@ -262,7 +269,9 @@
+(b.phone?'<a href="tel:'+esc(b.phone)+'">☎ '+esc(b.phone)+'</a>':'<span class="gate">☎ —</span>')
+(b.email?'<a href="mailto:'+esc(b.email)+'">✉ '+esc(b.email)+'</a>':'<span class="gate">✉ —</span>')
+(w?'<a href="'+esc(w)+'" target="_blank" rel="noopener noreferrer">🌐 website</a>':'')+'</div>'
- +'<div class="sec"><h4>Current listings ('+d.current.length+')</h4>'+(d.current.length?d.current.map(l=>'<div class="litem"><span>'+esc(l.address)+', '+esc(l.city)+'</span><span class="m">'+esc(l.type)+' · '+money(l.price)+(l.cap_rate?' · '+l.cap_rate+'% cap':'')+'</span></div>').join(''):'<div class="gate">none in our set</div>')+'</div>'
+ +'<div class="sec"><h4>Current listings ('+d.current.length+')</h4>'+(d.current.length?d.current.map(l=>'<div class="litem"><span>'+esc(l.address)+', '+esc(l.city)+(l.zip?' '+esc(l.zip):'')+'</span><span class="m">'+esc(l.type)+' · '+money(l.price)+(l.units?' · '+l.units+'u':'')+(l.cap_rate?' · '+l.cap_rate+'% cap':'')+(l.listed_at?' · listed '+fdate(l.listed_at):'')+'</span></div>').join(''):'<div class="gate">none in our set</div>')+'</div>'
+ +'<div class="sec"><h4>Expired / off-market ('+((d.expired&&d.expired.length)||0)+')</h4>'+((d.expired&&d.expired.length)?d.expired.map(x=>'<div class="litem"><span>'+esc(x.address)+', '+esc(x.city)+'</span><span class="m">'+money(x.list_price)+' → sold '+money(x.sold_price)+' · '+fdate(x.sold_date)+(x.mls?' · MLS '+esc(x.mls):'')+(x.url?' · <a href="'+esc(x.url)+'" target="_blank" rel="noopener noreferrer">↗</a>':'')+'</span></div>').join(''):'<div class="gate">'+esc(d.expiredNote||'none')+'</div>')+'</div>'
+ +'<div class="sec"><h4>Sales — area sold ('+((d.comps&&d.comps.length)||0)+')</h4>'+((d.comps&&d.comps.length)?d.comps.map(c=>'<div class="litem"><span>'+esc(c.address)+', '+esc(c.city)+(c.beds?' · '+c.beds+'bd':'')+(c.baths?'/'+c.baths+'ba':'')+(c.sqft?' · '+Number(c.sqft).toLocaleString()+'sf':'')+'</span><span class="m">'+money(c.sold_price)+' · '+fdate(c.sold_date)+(c.mls?' · MLS '+esc(c.mls):'')+(c.url?' · <a href="'+esc(c.url)+'" target="_blank" rel="noopener noreferrer">↗</a>':'')+'</span></div>').join(''):'<div class="gate">no area sales</div>')+(d.compsNote?'<div class="gate">'+esc(d.compsNote)+'</div>':'')+'</div>'
+'<div class="sec"><h4>Arcstone pitch <span class="gate">(ready to send)</span></h4>'
+'<button class="bkbtn" id="bkgen">✉ Generate / refresh pitch</button>'
+'<div id="bkpitch">'+(d.pitches&&d.pitches.length?renderPitch(d.pitches[0]):'<div class="gate">no draft yet — click Generate</div>')+'</div></div>';
@@ -367,6 +376,43 @@ const fmt = n => n==null ? '—' : '$'+Math.round(n).toLocaleString();
const pct = n => n==null ? '—' : n+'%';
const cls = (v, good, ok) => v==null?'na':(v>=good?'pos':(v>=ok?'':'neg'));
+// ── Dated status line (HARD RULE: every property shows EITHER a current listing
+// with its date OR historical data with dates — never a blank/undated status) ──
+const MON = ['Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'];
+function fmtMonYear(d){ return d ? MON[d.getMonth()]+' '+d.getFullYear() : null; }
+function fmtFullDate(d){ return d ? MON[d.getMonth()]+' '+d.getDate()+', '+d.getFullYear() : null; }
+// Pull the most reliable known date for a listing: the refresh/source stamp in the
+// note (ISO YYYY-MM-DD or "Mon D, YYYY" or MM/DD/YYYY), else the dataset's sourced date.
+function listingDate(p){
+ const t = p.upside_note || '';
+ let m = t.match(/(20\d\d)-(\d\d)-(\d\d)/); // 2026-06-27
+ if (m) return { d:new Date(+m[1], +m[2]-1, +m[3]), exact:true };
+ m = t.match(/\b([A-Z][a-z]{2,8})\s+(\d{1,2}),?\s+(20\d\d)/); // June 21, 2026
+ if (m){ const mi=MON.findIndex(x=>m[1].slice(0,3)===x); if(mi>=0) return { d:new Date(+m[3], mi, +m[2]), exact:true }; }
+ m = t.match(/\b(\d{1,2})\/(\d{1,2})\/(20\d\d)/); // 06/27/2026
+ if (m) return { d:new Date(+m[3], +m[1]-1, +m[2]), exact:true };
+ // Fallback: the whole dataset's sourcing date (meta.sourced begins YYYY-MM-DD).
+ const src = (DATA && DATA.meta && DATA.meta.sourced || '').match(/(20\d\d)-(\d\d)-(\d\d)/);
+ if (src) return { d:new Date(+src[1], +src[2]-1, +src[3]), exact:false };
+ return null;
+}
+// The guaranteed status line. Current listing → "Listed <date>". Off-market states
+// (Under Contract / Pending / Sold) → labeled with date as historical. No date at
+// all → an explicit "no recorded history" label, never a blank.
+function statusLine(p){
+ const ld = listingDate(p);
+ const dateTxt = ld ? (ld.exact ? fmtFullDate(ld.d) : fmtMonYear(ld.d)+' (dataset)') : null;
+ const title = ld ? ' title="'+(ld.exact?'Listing data as of '+ld.d.toISOString().slice(0,10):'No per-listing date; using dataset source date '+ld.d.toISOString().slice(0,10))+'"' : '';
+ let verb, klass;
+ if (/Under Contract|Pending|Escrow/i.test(p.status)) { verb='In escrow / '+p.status; klass='warn'; }
+ else if (/Sold|Closed/i.test(p.status)) { verb='Last sold'; klass='mut'; }
+ else if (/OUT OF BUDGET/i.test(p.status)) { verb='Listed (over budget)'; klass='bad'; }
+ else { verb='Listed'; klass='ok'; }
+ if (!dateTxt) return `<div class="statusline none" title="No current listing date and no recorded sale history in this dataset">🕓 ${verb} · no recorded date · no sale history on file</div>`;
+ const priced = p.price ? ' '+fmt(p.price) : '';
+ return `<div class="statusline ${klass}"${title}>🕓 ${verb}${priced} · as of ${dateTxt}</div>`;
+}
+
const SCEN = { '25':{budget:400000,downPct:25,ratePct:6.75,amortYears:30,closingPct:2.5},
'30':{budget:400000,downPct:30,ratePct:6.75,amortYears:30,closingPct:2.5},
'cash':{budget:400000,downPct:100,ratePct:0,amortYears:30,closingPct:2.5} };
@@ -491,6 +537,7 @@ function card(p){
<div class="score">${p.composite}<small>fin ${p.financeScore}${p.qwenScore!=null?' · ai '+p.qwenScore:''}</small></div>
</div>
<div class="badges">${dealBadge(p)}${firmB}<span class="b type">${p.type}</span><span class="b">${p.units} unit${p.units>1?'s':''}</span><span class="b">${fmt(p.price)}</span>${capB}${verB}${f.grossYield?`<span class="b ok" title="Annual gross rent ÷ price">${f.grossYield}% rent yld</span>`:''}${statusB} ${aff} ${rec}</div>
+ ${statusLine(p)}
<div class="metrics">
<div><span class="k">Cash-on-cash</span><span class="v ${cls(f.coc,6,3)}">${pct(f.coc)}</span></div>
<div><span class="k">DSCR</span><span class="v ${cls(f.dscr,1.25,1.0)}">${f.dscr??'—'}</span></div>
diff --git a/scripts/serve.js b/scripts/serve.js
index 6bb3770..05174bc 100644
--- a/scripts/serve.js
+++ b/scripts/serve.js
@@ -368,24 +368,37 @@ app.get('/api/broker', async (req, res) => {
const broker = brokers[0];
if (!broker) return res.status(404).json({ error: 'broker not found' });
// Current listings = commercial listings (broker_listing) UNION residential condos (broker_condo).
- const current = await q(`SELECT l.id, l.address, l.city, l.type, l.price, l.cap_rate, bl.role
+ // listed_at = when we captured the listing (best available "listed" date; no MLS list-date feed).
+ const current = await q(`SELECT l.id, l.address, l.city, l.zip, l.type, l.price, l.units, l.cap_rate, l.created_at AS listed_at, bl.role
FROM broker_listing bl JOIN listing l ON l.id=bl.listing_id WHERE bl.broker_id=$1
UNION ALL
- SELECT c.id, c.address, c.city, 'Condo' type, c.price, NULL cap_rate, bc.role
+ SELECT c.id, c.address, c.city, NULL::text zip, 'Condo' type, c.price, NULL::int units, NULL::numeric cap_rate, NULL::timestamptz listed_at, bc.role
FROM broker_condo bc JOIN condo c ON c.id=bc.condo_id WHERE bc.broker_id=$1
ORDER BY price DESC NULLS LAST`, [broker.id]);
const closed = await q(`SELECT address, city, sold_price, sold_date, type, source FROM broker_closed_listing WHERE broker_id=$1 ORDER BY sold_date DESC NULLS LAST`, [broker.id]).catch(() => []);
+ // Expired / off-market: best-effort match of THIS broker's listings against the sold feed — a listing
+ // that now appears as sold is no longer active. Match = same house number + same city + a street-word
+ // in common. (We have no MLS off-market/expired feed, so this sold-cross-ref is the honest signal.)
+ const expired = await q(`SELECT DISTINCT l.id, l.address, l.city, l.type, l.price AS list_price,
+ cs.sold_price, cs.sold_date, cs.mls, cs.url
+ FROM broker_listing bl JOIN listing l ON l.id=bl.listing_id
+ JOIN closed_sale cs ON cs.city = l.city
+ AND split_part(cs.address,' ',1) = split_part(l.address,' ',1)
+ AND lower(cs.address) LIKE '%'||lower(coalesce((regexp_match(l.address,'[A-Za-z]{3,}'))[1],'~'))||'%'
+ WHERE bl.broker_id=$1 AND cs.sold_price>0
+ ORDER BY cs.sold_date DESC NULLS LAST LIMIT 12`, [broker.id]).catch(() => []);
const pitches = await q(`SELECT id, channel, subject, body, status, created_at FROM broker_pitch WHERE broker_id=$1 ORDER BY created_at DESC`, [broker.id]).catch(() => []);
- // Market comps: recent closed_sale (Redfin sold, ~last 5yr) in the cities this broker lists in.
+ // Sales: recent closed_sale (Redfin sold) in the cities this broker lists in — with sold DATE + MLS# + url.
// Honest area context — NOT attributed to this broker (the sold feed carries no agent).
const cities = [...new Set(current.map(c => c.city).filter(Boolean))].slice(0, 6);
let comps = [];
if (cities.length) comps = await q(
- `SELECT address, city, sold_price, sold_date FROM closed_sale WHERE city = ANY($1) AND sold_price>0
- ORDER BY sold_date DESC NULLS LAST LIMIT 8`, [cities]).catch(() => []);
- res.json({ broker, current, closed, pitches, comps,
- compsNote: comps.length ? 'Recent sold comps in this broker’s areas (Redfin, ~5yr) — area context, not this broker’s own closings.' : null,
- closedNote: closed.length ? null : 'Per-broker closed history needs a gated detail-scrape; recent area comps shown instead.' });
+ `SELECT address, city, zip, sold_price, sold_date, beds, baths, sqft, mls, url FROM closed_sale
+ WHERE city = ANY($1) AND sold_price>0 ORDER BY sold_date DESC NULLS LAST LIMIT 12`, [cities]).catch(() => []);
+ res.json({ broker, current, closed, expired, pitches, comps,
+ compsNote: comps.length ? 'Recent sold comps in this broker’s areas (Redfin) with sold dates + MLS# — area context, not this broker’s own closings.' : null,
+ expiredNote: expired.length ? 'Listings of this broker that now appear in the sold feed (best-effort address match).' : 'No off-market/expired feed yet — none of this broker’s listings matched a sold record.',
+ closedNote: closed.length ? null : 'Per-broker closed history needs a gated detail-scrape; area sales shown instead.' });
} catch (e) { res.status(502).json({ error: String(e.message).split('\n')[0] }); }
});
← 9c3c4e8 Add MLS spreadsheet + grid view (sortable & searchable, all
·
back to Commercialrealestate
·
auto-save: 2026-06-30T16:41:53 (10 files) — public/condos.ht 06ad891 →