← back to Commercialrealestate
mls: add residential inventory (9.6k SFR + condos) alongside CRE + Census rent averages
0dae177b880abdea759279083e830052932340fb · 2026-07-06 12:30:08 -0700 · Steve Abrams
- /api/residential endpoint (DB-first, sfr-redfin.json snapshot fallback for prod)
- export-sfr-snapshot.js: dump public.sfr -> data/sfr-redfin.json (prod has no PG)
- mls.html: mapSfr() + fetch residential; Single-Family/Condo asset-class chips auto-build
- Census median gross rent join by city -> Area Rent/mo + Area GRY% cols + stat pills (area proxy, honestly labeled)
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Files touched
M public/mls.htmlA scripts/export-sfr-snapshot.jsM scripts/serve.js
Diff
commit 0dae177b880abdea759279083e830052932340fb
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Mon Jul 6 12:30:08 2026 -0700
mls: add residential inventory (9.6k SFR + condos) alongside CRE + Census rent averages
- /api/residential endpoint (DB-first, sfr-redfin.json snapshot fallback for prod)
- export-sfr-snapshot.js: dump public.sfr -> data/sfr-redfin.json (prod has no PG)
- mls.html: mapSfr() + fetch residential; Single-Family/Condo asset-class chips auto-build
- Census median gross rent join by city -> Area Rent/mo + Area GRY% cols + stat pills (area proxy, honestly labeled)
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
public/mls.html | 23 +++++++++++++++++++++--
scripts/export-sfr-snapshot.js | 42 ++++++++++++++++++++++++++++++++++++++++++
scripts/serve.js | 26 ++++++++++++++++++++++++++
3 files changed, 89 insertions(+), 2 deletions(-)
diff --git a/public/mls.html b/public/mls.html
index 3223c2a..b34fe52 100644
--- a/public/mls.html
+++ b/public/mls.html
@@ -87,7 +87,7 @@
</head>
<body>
<header>
- <h1><b>MLS</b> · LA County CRE</h1>
+ <h1><b>MLS</b> · LA County — Residential + CRE</h1>
<div class="nav"><a href="/">← Explorer</a><a href="/map.html">🗺️ Map</a><a href="/crcp.html">🛰️ CRCP</a></div>
<span class="spacer"></span>
<input class="search" id="q" type="text" placeholder="search anything — address, city, firm, type, year…">
@@ -136,6 +136,8 @@ const COLS=[
{k:'ppu',l:'$/Unit',t:'money',calc:r=>r.units?Math.round(r.price/r.units):null},
{k:'pps',l:'$/SqFt',t:'money',calc:r=>r.sqft?Math.round(r.price/r.sqft):null},
{k:'cap_rate',l:'Cap %',t:'pct'},
+ {k:'area_rent',l:'Area Rent/mo',t:'money'},
+ {k:'area_yield',l:'Area GRY %',t:'pct'},
{k:'year_built',l:'Year',t:'n'},
{k:'rent_control',l:'Rent Ctrl',t:'s'},
{k:'status',l:'Status',t:'status'},
@@ -199,15 +201,20 @@ function renderStats(rows){ const sb=$('#statsbar'); if(!sb) return; if(!rows.le
const pps=rows.map(r=>r.sqft?+r.price/r.sqft:null).filter(v=>v>0);
const caps=rows.map(r=>r.cap_rate).filter(v=>v!=null&&v!=='').map(Number).filter(v=>!isNaN(v));
const yrs=rows.map(r=>+r.year_built).filter(v=>v>1800);
+ const rents=rows.map(r=>+r.area_rent).filter(v=>v>0);
+ const ylds=rows.map(r=>+r.area_yield).filter(v=>v>0);
const pill=(l,v)=>v==null?'':`<span class="statpill"><span class="sl">${l}</span> <b>${v}</b></span>`;
sb.innerHTML=pill('Count',rows.length)
+pill('Median price',prices.length?'$'+Math.round(median(prices)).toLocaleString():null)
+pill('Median $/unit',ppu.length?'$'+Math.round(median(ppu)).toLocaleString():null)
+pill('Median $/SF',pps.length?'$'+Math.round(median(pps)).toLocaleString():null)
+pill('Avg cap',caps.length?(caps.reduce((a,b)=>a+b,0)/caps.length).toFixed(2)+'% ('+caps.length+')':null)
+ +pill('Avg area rent',rents.length?'$'+Math.round(rents.reduce((a,b)=>a+b,0)/rents.length).toLocaleString()+'/mo':null)
+ +pill('Median area GRY',ylds.length?median(ylds).toFixed(2)+'%':null)
+pill('Median year',yrs.length?Math.round(median(yrs)):null);
}
function mapCondo(c){ return { id:c.id, address:c.address, city:c.city, zip:c.zip, type:'Condo', price:+c.price||0, units:1, cap_rate:null, year_built:c.year_built||null, status:'Active', source:c.source, firm:c.firm_name||null, beds:c.beds, baths:c.baths, sqft:c.sqft, hoa:c.hoa, warrantable_status:c.warrantable_status, broker_name:c.broker_name, project_name:c.project_name }; }
+function mapSfr(s){ return { id:s.id, address:s.address, city:s.city, zip:s.zip, type:'Single-Family', price:+s.price||0, units:1, cap_rate:null, year_built:s.year_built||null, status:'Active', source:s.source, firm:s.firm_name||null, beds:s.beds, baths:s.baths, sqft:s.sqft, broker_name:s.broker_name||null }; }
let VISCOL=(function(){let s={};try{s=JSON.parse(localStorage.getItem('mlsCols')||'{}');}catch(e){}return s;})();
function colVis(k){ if(k in VISCOL) return !!VISCOL[k]; const c=COLS.find(x=>x.k===k); return c?c.def!==0:true; }
function visCols(){ return COLS.filter(c=>colVis(c.k)); }
@@ -277,7 +284,7 @@ function render(){
$('#count').textContent=`${DATA.length} loaded · pick a filter or search to view`;
$('#statsbar').innerHTML=''; const _mw=$('#moreWrap'); if(_mw) _mw.innerHTML='';
$('#tableView').classList.add('hide'); $('#gridView').classList.remove('hide');
- $('#gridView').className='grid'; $('#gridView').innerHTML=`<div class="mlsempty">👈 Expand a filter tab or search to begin.<br><span style="font-size:12px">${DATA.length} listings + condos loaded. Pick <b>Asset class</b> (◆ Non-QM · ⚑ Unwarrantable · Condo), a city, status, a price range, or ⭐ Shortlist.</span></div>`;
+ $('#gridView').className='grid'; $('#gridView').innerHTML=`<div class="mlsempty">👈 Expand a filter tab or search to begin.<br><span style="font-size:12px">${DATA.length.toLocaleString()} listings loaded — Single-Family + Condo (residential) + Commercial. Pick an <b>Asset class</b> (Single-Family · Condo · CRE types · ◆ Non-QM · ⚑ Unwarrantable), a city, status, a price range, or ⭐ Shortlist.</span></div>`;
return;
}
const rows=filtered(); const cols=visCols();
@@ -338,7 +345,19 @@ const railEl=document.querySelector('.rail');
if(railEl){ railEl.addEventListener('click',e=>{ const h=e.target.closest('h4'); if(h && h.parentElement.classList.contains('rsec')) h.parentElement.classList.toggle('collapsed'); });
document.querySelectorAll('.rail .rsec').forEach(s=>s.classList.add('collapsed')); }
fetch('/data/ranked.json').then(r=>r.json()).then(async d=>{ DATA=(d.ranked||d)||[];
+ // Residential inventory bolted onto the CRE table (asset-class chips auto-build from r.type):
try{ const cd=await (await fetch('/api/condos')).json(); DATA=DATA.concat((cd.condos||[]).map(mapCondo)); }catch(e){}
+ try{ const sr=await (await fetch('/api/residential')).json(); DATA=DATA.concat((sr.sfr||[]).map(mapSfr)); }catch(e){}
+ // Rent averages: join Census ACS median gross rent by city (area proxy — NOT the specific home's
+ // achievable rent). Powers Area Rent/mo, Area GRY % (gross rental yield), and area rent stat.
+ try{ const dm=await (await fetch('/data/demographics.json')).json(); const bc=dm.byCity||{};
+ const RENT={}; Object.keys(bc).forEach(k=>{ RENT[k.toLowerCase().trim()]=bc[k]; });
+ DATA.forEach(r=>{ const e=RENT[String(r.city||'').toLowerCase().trim()]; if(!e) return;
+ r.area_rent=e.median_gross_rent||null;
+ r.renter_pct=e.renter_pct||null; r.rent_burden_pct=e.rent_burden_pct||null;
+ if(r.area_rent&&+r.price>0) r.area_yield=+((r.area_rent*12/+r.price)*100).toFixed(2);
+ });
+ }catch(e){}
autoCols(); buildRail(); render(); })
.catch(()=>{ $('#count').textContent='failed to load /data/ranked.json'; });
</script>
diff --git a/scripts/export-sfr-snapshot.js b/scripts/export-sfr-snapshot.js
new file mode 100644
index 0000000..5f0ce42
--- /dev/null
+++ b/scripts/export-sfr-snapshot.js
@@ -0,0 +1,42 @@
+// export-sfr-snapshot.js — dump the scraped single-family for-sale listings (public.sfr in the
+// local `cre` DB) to a static JSON snapshot: data/sfr-redfin.json.
+//
+// WHY: prod (Kamatera) has no Postgres, so the live crcp.agentabrams.com serves data-backed pages
+// from static snapshots with a DB->snapshot fallback (same pattern as data/condos-redfin.json /
+// data/closed-sales.json). fetch-sfr-redfin.js only writes to the DB, so this is the companion
+// exporter that produces the file /api/residential falls back to on prod.
+//
+// Run AFTER fetch-sfr-redfin.js: node scripts/export-sfr-snapshot.js
+'use strict';
+const fs = require('fs');
+const path = require('path');
+const { Pool } = require('pg');
+
+const ROOT = path.join(__dirname, '..');
+const OUT = path.join(ROOT, 'data', 'sfr-redfin.json');
+const pool = new Pool({ host: '/tmp', port: 5432, database: 'cre', user: process.env.USER || 'stevestudio2' });
+
+(async () => {
+ // Tight, card-safe column projection. listing_text is the constant label "Single Family
+ // Residential" for every gis-csv row, so it's dropped (the client tags type itself).
+ const { rows } = await pool.query(`
+ SELECT id, address, city, zip, price, beds, baths, sqft, year_built,
+ firm_name, broker_name, source, lat, lng, created_at
+ FROM sfr
+ WHERE price > 0
+ ORDER BY created_at DESC, price DESC`);
+
+ const payload = {
+ meta: {
+ source: 'Redfin LA County single-family for-sale — feed-first (gis-csv), via Browserbase',
+ note: 'Listing agent/firm only present where Redfin exposed it (fetch-sfr-agents.js); NULL otherwise (honest labeling).',
+ fetched_at: new Date().toISOString(),
+ count: rows.length,
+ },
+ sfr: rows,
+ };
+ fs.writeFileSync(OUT, JSON.stringify(payload, null, 2));
+ const kb = (fs.statSync(OUT).size / 1024).toFixed(0);
+ console.log(JSON.stringify({ count: rows.length, out: OUT, size_kb: +kb }));
+ await pool.end();
+})().catch(e => { console.error(e.message); process.exit(1); });
diff --git a/scripts/serve.js b/scripts/serve.js
index d8fe7b4..8a084bb 100644
--- a/scripts/serve.js
+++ b/scripts/serve.js
@@ -296,6 +296,32 @@ app.get('/api/condos', async (req, res) => {
}
});
+// Residential single-family for-sale inventory (cre.sfr, scraped from Redfin gis-csv). Same
+// DB-first -> static-snapshot fallback shape as /api/condos so it works locally (Postgres) AND on
+// prod (Kamatera has no DB — reads data/sfr-redfin.json produced by export-sfr-snapshot.js).
+const SFR_LABEL = 'Single-family for-sale — Redfin LA County (feed-first gis-csv). Listing agent shown where Redfin exposed it.';
+app.get('/api/residential', async (req, res) => {
+ const limit = Math.min(+req.query.limit || 12000, 12000);
+ // 1) live DB first
+ if (brokerdb) {
+ try {
+ const r = await brokerdb.pool.query(
+ `SELECT id, address, city, zip, price, beds, baths, sqft, year_built,
+ firm_name, broker_name, source, lat, lng
+ FROM sfr WHERE price > 0 ORDER BY created_at DESC, price DESC LIMIT $1`, [limit]);
+ if (r.rows.length) return res.json({ sfr: r.rows, label: SFR_LABEL, source: 'db' });
+ } catch (_) { /* fall through to snapshot */ }
+ }
+ // 2) snapshot fallback (prod)
+ try {
+ const file = path.join(ROOT, 'data', 'sfr-redfin.json');
+ const { sfr = [] } = JSON.parse(fs.readFileSync(file, 'utf8'));
+ return res.json({ sfr: sfr.slice(0, limit), label: SFR_LABEL, source: 'snapshot' });
+ } catch (e) {
+ return res.json({ sfr: [], note: 'no sfr data', error: String(e.message).split('\n')[0] });
+ }
+});
+
// Aggregated stats over the REAL scraped condo listings (cre.condo) — warrantable-vs-unwarranted
// breakdown + by-city, driving the graphics. Distinct from /api/warrantability (which charts the HUD
// FHA reference LIST, not the actual for-sale inventory we scraped).
← 6e2d2bf closed-sales: snapshot fallback so sales.html has data on pr
·
back to Commercialrealestate
·
residential: fresh Redfin scrape snapshots (10.6k SFR + 1.29 9a70d22 →