← back to Commercialrealestate
direct-listings viewer: /api/direct-listings + public/direct-listings.html — browse the dedicated broker_direct_listing store (sort/filter/search, day/night); fix int/text broker_id join casts
8f9ce55db9c7cbacde14b532db82d5b7ac52e401 · 2026-08-19 11:00:00 -0700 · Steve Abrams
Files touched
A public/direct-listings.htmlM scripts/serve.js
Diff
commit 8f9ce55db9c7cbacde14b532db82d5b7ac52e401
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Wed Aug 19 11:00:00 2026 -0700
direct-listings viewer: /api/direct-listings + public/direct-listings.html — browse the dedicated broker_direct_listing store (sort/filter/search, day/night); fix int/text broker_id join casts
---
public/direct-listings.html | 118 ++++++++++++++++++++++++++++++++++++++++++++
scripts/serve.js | 17 ++++++-
2 files changed, 134 insertions(+), 1 deletion(-)
diff --git a/public/direct-listings.html b/public/direct-listings.html
new file mode 100644
index 0000000..4186d8a
--- /dev/null
+++ b/public/direct-listings.html
@@ -0,0 +1,118 @@
+<!doctype html><html lang="en"><head>
+<script>/* credential-safe fetch */(function(){var of=window.fetch,O=location.origin;window.fetch=function(u,o){try{if(typeof u==='string'){if(/^[a-z][a-z0-9+.-]*:/i.test(u)){var x=new URL(u);if(x.username||x.password){x.username='';x.password='';u=x.href;}}else{u=new URL(u,O+location.pathname+location.search).href;}}else if(u&&typeof u.url==='string'){var y=new URL(u.url,O+location.pathname+location.search);if(y.username||y.password){y.username='';y.password='';u=new Request(y.href,u);}}}catch(e){}return of.call(this,u,o);};try{if(location.href.indexOf('@')>=0){history.replaceState(null,'',location.pathname+location.search+location.hash);}}catch(e){}})();</script>
+<script>(function(){try{var t=localStorage.getItem('crcp-theme');document.documentElement.setAttribute('data-theme',t==='light'?'light':'dark');}catch(e){document.documentElement.setAttribute('data-theme','dark');}})();</script>
+<meta charset="utf-8"><meta name="viewport" content="width=device-width,initial-scale=1">
+<title>Direct Listings · our-own dbase · CRCP</title>
+<style>
+:root{--bg:#0e1116;--card:#161b22;--line:#2a313c;--ink:#e6edf3;--mut:#8b949e;--acc:#3fb950;--blue:#58a6ff;--gold:#ffa600;--onacc:#0e1116;--rowpad:7px}
+*{box-sizing:border-box}body{margin:0;background:var(--bg);color:var(--ink);font-family:-apple-system,Helvetica,Arial,sans-serif;font-size:13px}
+a{color:var(--blue);text-decoration:none}a:hover{text-decoration:underline}
+header{padding:14px 18px;border-bottom:1px solid var(--line);display:flex;flex-wrap:wrap;gap:10px;align-items:center}
+h1{font-size:17px;margin:0}h1 b{color:var(--ink)}
+.badge{font-size:10px;font-weight:700;text-transform:uppercase;letter-spacing:.5px;color:var(--onacc);background:var(--acc);padding:3px 8px;border-radius:6px}
+.nav{display:flex;align-items:center;gap:2px;flex-wrap:wrap}.nav a{font-size:12px;margin-left:12px}
+.wrap{padding:14px 18px}
+.stats{margin-bottom:12px}
+.stat{display:inline-block;background:var(--card);border:1px solid var(--line);border-radius:9px;padding:7px 12px;margin:0 8px 8px 0}
+.stat b{font-size:18px;color:var(--ink)}.stat span{display:block;font-size:10px;text-transform:uppercase;color:var(--mut);letter-spacing:.4px}
+.prov{font-size:11.5px;color:var(--mut);margin:0 0 12px;line-height:1.5}
+.toolbar{display:flex;flex-wrap:wrap;gap:10px;align-items:center;margin-bottom:10px}
+.search{flex:1;min-width:200px;background:var(--card);border:1px solid var(--line);color:var(--ink);border-radius:8px;padding:8px 11px;font-size:13px}
+select{background:var(--card);border:1px solid var(--line);color:var(--ink);border-radius:8px;padding:7px 9px;font-size:12px}
+.chips{display:flex;flex-wrap:wrap;gap:6px}
+.chip{border:1px solid var(--line);border-radius:20px;padding:3px 10px;font-size:11.5px;cursor:pointer;background:var(--card);color:var(--ink);user-select:none}
+.chip.active{background:var(--blue);color:var(--onacc);border-color:var(--blue);font-weight:600}
+.chip .ct{color:var(--mut);margin-left:4px;font-size:10px}.chip.active .ct{color:var(--onacc)}
+.count{color:var(--mut);font-size:12px}
+.tablewrap{overflow:auto;border:1px solid var(--line);border-radius:12px;background:var(--card);max-height:calc(100vh - 250px)}
+table{border-collapse:collapse;width:100%;font-size:12.5px}
+thead th{position:sticky;top:0;background:var(--headbg,#11161d);z-index:2;text-align:left;padding:9px 11px;border-bottom:1px solid var(--line);white-space:nowrap;cursor:pointer;user-select:none;color:var(--mut);font-weight:700;font-size:10.5px;text-transform:uppercase;letter-spacing:.4px}
+thead th:hover{color:var(--ink)}thead th.sorted{color:var(--blue)}
+tbody td{padding:var(--rowpad) 11px;border-bottom:1px solid var(--line);vertical-align:top}
+tbody td.num{text-align:right;font-variant-numeric:tabular-nums;white-space:nowrap}
+tbody tr:hover{background:var(--hover,rgba(88,166,255,.06))}
+td.price{color:var(--gold);font-weight:600}
+.src{font-size:9.5px;font-weight:700;text-transform:uppercase;letter-spacing:.4px;padding:2px 6px;border-radius:6px}
+.src.crexi{background:rgba(88,166,255,.16);color:var(--blue)}
+.src.brokersiteoc{background:rgba(63,185,80,.16);color:var(--acc)}
+.src.brokersite{background:rgba(255,166,0,.16);color:var(--gold)}
+.role{font-size:10px;color:var(--mut)}
+.empty{color:var(--mut);padding:40px;text-align:center}
+</style>
+<link rel="stylesheet" href="/crcp-theme.css">
+</head><body>
+<header>
+ <h1><b>Direct Listings</b> · our-own dbase</h1>
+ <span class="badge" id="cnt">…</span>
+ <div class="nav"><a href="/brokers.html">Brokers</a><a href="/just-listed.html">Just Listed</a><a href="/">Explorer</a></div>
+</header>
+<div class="wrap">
+ <div class="stats" id="stats"></div>
+ <div class="prov">Listings scraped <b>directly</b> from broker & firm websites (openclaw real-Chrome) and Crexi detail pages (agent-attributed) — kept in a dedicated store (<code>broker_direct_listing</code>), separate from the aggregator-originated graph. No listing here links out to Crexi/LoopNet.</div>
+ <div class="toolbar">
+ <input class="search" id="q" placeholder="search agent, firm, address, city…">
+ <div class="chips" id="srcChips"></div>
+ <select id="sort"></select>
+ <span class="count" id="count"></span>
+ </div>
+ <div class="tablewrap"><table><thead id="thead"></thead><tbody id="tbody"></tbody></table></div>
+ <div class="empty" id="empty" hidden>No direct listings yet.</div>
+</div>
+<script>
+const $=s=>document.querySelector(s);
+const esc=s=>(s==null?'':String(s)).replace(/&/g,'&').replace(/</g,'<').replace(/>/g,'>').replace(/"/g,'"');
+const money=n=>n==null||isNaN(+n)?'—':'$'+Math.round(+n).toLocaleString();
+let DATA=[],q='',srcF=new Set(),sortKey='scraped_at',sortDir=-1;
+const COLS=[
+ {k:'agent',l:'Agent',r:x=>esc(x.agent||'—')},
+ {k:'firm',l:'Firm',r:x=>esc(x.firm||'—')},
+ {k:'address',l:'Address',r:x=>esc(x.address||'—')},
+ {k:'city',l:'City',r:x=>esc(x.city||'—')},
+ {k:'price',l:'Price',num:1,cls:'price',r:x=>money(x.price)},
+ {k:'type',l:'Type',r:x=>esc(x.type||'—')},
+ {k:'cap_rate',l:'Cap %',num:1,r:x=>x.cap_rate!=null?(+x.cap_rate).toFixed(2)+'%':'—'},
+ {k:'role',l:'Role',r:x=>`<span class="role">${esc(x.role||'—')}</span>`},
+ {k:'source',l:'Source',r:x=>`<span class="src ${(x.source||'').replace(/[^a-z]/g,'')}">${esc(x.source||'—')}</span>`},
+ {k:'scraped_at',l:'Scraped',r:x=>x.scraped_at?new Date(x.scraped_at).toLocaleDateString(undefined,{month:'short',day:'numeric'}):'—'},
+];
+function pass(x){
+ if(srcF.size&&!srcF.has(x.source))return false;
+ if(!q)return true;
+ const hay=[x.agent,x.firm,x.address,x.city,x.type,x.source].map(v=>v==null?'':String(v)).join(' ').toLowerCase();
+ return q.toLowerCase().split(/\s+/).filter(Boolean).every(t=>hay.includes(t));
+}
+function filtered(){
+ return DATA.filter(pass).slice().sort((a,b)=>{let x=a[sortKey],y=b[sortKey];const xm=x==null||x==='',ym=y==null||y==='';
+ if(xm&&ym)return 0;if(xm)return 1;if(ym)return -1;
+ if(!isNaN(+x)&&!isNaN(+y)){x=+x;y=+y;}else{x=String(x).toLowerCase();y=String(y).toLowerCase();}
+ return x<y?-1*sortDir:x>y?sortDir:0;});
+}
+function render(){
+ document.documentElement.style.setProperty('--rowpad','7px');
+ const rows=filtered();
+ $('#thead').innerHTML='<tr>'+COLS.map(c=>`<th data-k="${c.k}" class="${c.num?'num':''}${c.k===sortKey?' sorted':''}">${c.l}${c.k===sortKey?(sortDir===1?' ▲':' ▼'):''}</th>`).join('')+'</tr>';
+ $('#tbody').innerHTML=rows.slice(0,2000).map(x=>'<tr>'+COLS.map(c=>`<td class="${c.num?'num ':''}${c.cls||''}">${c.r(x)}</td>`).join('')+'</tr>').join('');
+ $('#count').textContent=`${rows.length.toLocaleString()} of ${DATA.length.toLocaleString()}`;
+ $('#empty').hidden=rows.length>0;
+}
+$('#thead').addEventListener('click',e=>{const th=e.target.closest('th');if(!th)return;const k=th.dataset.k;if(k===sortKey)sortDir=-sortDir;else{sortKey=k;sortDir=COLS.find(c=>c.k===k)?.num?-1:1;}render();});
+$('#q').addEventListener('input',()=>{q=$('#q').value.trim();render();});
+$('#sort').innerHTML=[['scraped_at:desc','Newest scraped'],['price:desc','Price ↓'],['price:asc','Price ↑'],['agent:asc','Agent A→Z'],['firm:asc','Firm A→Z'],['city:asc','City A→Z']].map(([k,l])=>`<option value="${k}">Sort: ${l}</option>`).join('');
+$('#sort').addEventListener('change',()=>{const v=$('#sort').value.split(':');sortKey=v[0];sortDir=v[1]==='asc'?1:-1;render();});
+fetch('/api/direct-listings').then(r=>r.json()).then(d=>{
+ DATA=d.listings||[];
+ $('#cnt').textContent=`${DATA.length.toLocaleString()} direct`;
+ const bs=d.bySource||{};
+ $('#srcChips').innerHTML=Object.entries(bs).sort((a,b)=>b[1]-a[1]).map(([s,n])=>`<span class="chip" data-s="${esc(s)}">${esc(s)}<span class="ct">${n}</span></span>`).join('');
+ $('#stats').innerHTML=
+ `<div class="stat"><b>${DATA.length.toLocaleString()}</b><span>direct listings</span></div>`+
+ `<div class="stat"><b>${new Set(DATA.map(x=>x.firm).filter(Boolean)).size}</b><span>firms</span></div>`+
+ `<div class="stat"><b>${new Set(DATA.filter(x=>x.agent).map(x=>x.agent)).size}</b><span>agents</span></div>`+
+ `<div class="stat"><b style="color:var(--gold)">$${(DATA.reduce((s,x)=>s+(+x.price||0),0)/1e9).toFixed(2)}B</b><span>total</span></div>`;
+ render();
+}).catch(()=>{$('#count').textContent='failed to load /api/direct-listings';});
+$('#srcChips').addEventListener('click',e=>{const c=e.target.closest('.chip');if(!c)return;const s=c.dataset.s;srcF.has(s)?srcF.delete(s):srcF.add(s);c.classList.toggle('active');render();});
+</script>
+<script src="/corner-nav.js" defer></script>
+<script src="/crcp-theme.js" defer></script>
+</body></html>
diff --git a/scripts/serve.js b/scripts/serve.js
index dfdd03e..a32637d 100644
--- a/scripts/serve.js
+++ b/scripts/serve.js
@@ -431,7 +431,7 @@ app.get('/api/agent-profile', async (req, res) => {
// this agent's OWN directly-scraped listings from the dedicated store (broker_direct_listing)
const directOwn = (await brokerdb.pool.query(
`SELECT address, city, zip, type, price, units, cap_rate
- FROM broker_direct_listing WHERE broker_id = $1 AND (role='agent' OR source='crexi')
+ FROM broker_direct_listing WHERE broker_id = $1::text AND (role='agent' OR source='crexi')
ORDER BY price DESC NULLS LAST`, [b.id]).catch(() => ({ rows: [] }))).rows;
const listings = mergeListings(commercial, condoEdge, overlayCondos, directOwn);
@@ -455,6 +455,21 @@ app.get('/api/agent-profile', async (req, res) => {
} catch (e) { res.json({ name, firm: null, listings: [], found: false, error: String(e.message).split('\n')[0] }); }
});
+// Browse the dedicated DIRECT-listing store (broker_direct_listing) — our-own scrapes only, kept
+// separate from the crexi-originated graph. Powers /direct-listings.html.
+app.get('/api/direct-listings', async (req, res) => {
+ if (!brokerdb) return res.json({ listings: [], count: 0 });
+ try {
+ const rows = (await brokerdb.pool.query(
+ `SELECT d.address, d.city, d.zip, d.price, d.type, d.cap_rate, d.units, d.role, d.source,
+ coalesce(d.agent_name, b.name) AS agent, d.firm_name AS firm, d.scraped_at
+ FROM broker_direct_listing d LEFT JOIN broker b ON b.id::text = d.broker_id
+ ORDER BY d.scraped_at DESC NULLS LAST, d.price DESC NULLS LAST LIMIT 5000`)).rows;
+ const bySource = {}; for (const r of rows) bySource[r.source] = (bySource[r.source] || 0) + 1;
+ res.json({ listings: rows, count: rows.length, bySource });
+ } catch (e) { res.json({ listings: [], count: 0, error: String(e.message).split('\n')[0] }); }
+});
+
// Enrichment coverage rollup (how many brokers now have phone/email/website/linkedin).
const snapEnrich = () => { const s = readBrokerSnap(); return s && s.enrichStats ? s.enrichStats : { unavailable: true }; };
app.get('/api/brokers/enrich-stats', async (req, res) => {
← eab941c firm-site scraper: scroll for lazy-loaded grids + try common
·
back to Commercialrealestate
·
corner-nav: add 'Direct Listings' to Properties menu (the de 88f093b →