← back to Commercialrealestate
listings: real DAYS ON MARKET (gap-immune Just Listed) + In Escrow / Fell Out tabs
a31904e16910ef746cfd4e410ba85c69167aeb5d · 2026-07-07 07:29:27 -0700 · Steve Abrams
- Capture Redfin DAYS ON MARKET + STATUS (was discarding both); derive listed_date. Just Listed now
keys on days_on_market (real list age) not our first-seen date — fixes the scrape-gap artifact.
- Scrapers now also sweep status=130 (pending/contingent) so in-escrow homes are captured; track
prev_market_status for fell-out-of-escrow detection.
- New chips: 🤝 In Escrow (pending/contingent), ↩️ Fell Out of Escrow (was pending, now active).
Just Sold = closed escrow. Status shows In Escrow; new Market + Days on Mkt columns.
- FIX: cast $N::int in the upsert — a bound param used only inside CURRENT_DATE-($N)::int failed
Postgres type inference (masked by .catch(()=>null)). schema: days_on_market/listed_date/
market_status/prev_market_status on sfr+condo; condo_card recreated.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Files touched
M public/mls.htmlA scripts/db/listing-signals-migration.sqlM scripts/export-residential-snapshots.jsM scripts/fetch-condos-redfin.jsM scripts/fetch-sfr-redfin.jsM scripts/serve.js
Diff
commit a31904e16910ef746cfd4e410ba85c69167aeb5d
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Tue Jul 7 07:29:27 2026 -0700
listings: real DAYS ON MARKET (gap-immune Just Listed) + In Escrow / Fell Out tabs
- Capture Redfin DAYS ON MARKET + STATUS (was discarding both); derive listed_date. Just Listed now
keys on days_on_market (real list age) not our first-seen date — fixes the scrape-gap artifact.
- Scrapers now also sweep status=130 (pending/contingent) so in-escrow homes are captured; track
prev_market_status for fell-out-of-escrow detection.
- New chips: 🤝 In Escrow (pending/contingent), ↩️ Fell Out of Escrow (was pending, now active).
Just Sold = closed escrow. Status shows In Escrow; new Market + Days on Mkt columns.
- FIX: cast $N::int in the upsert — a bound param used only inside CURRENT_DATE-($N)::int failed
Postgres type inference (masked by .catch(()=>null)). schema: days_on_market/listed_date/
market_status/prev_market_status on sfr+condo; condo_card recreated.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
public/mls.html | 36 ++++++++++++++++++---------
scripts/db/listing-signals-migration.sql | 19 +++++++++++++++
scripts/export-residential-snapshots.js | 6 +++--
scripts/fetch-condos-redfin.js | 42 +++++++++++++++++---------------
scripts/fetch-sfr-redfin.js | 29 ++++++++++++++--------
scripts/serve.js | 6 +++--
6 files changed, 94 insertions(+), 44 deletions(-)
diff --git a/public/mls.html b/public/mls.html
index a191607..ce4cc91 100644
--- a/public/mls.html
+++ b/public/mls.html
@@ -101,7 +101,7 @@
<div class="shell">
<aside class="rail">
<div class="rsec"><h4>Asset class</h4><div class="chips" id="fType"></div>
- <div style="margin-top:9px;display:flex;flex-wrap:wrap;gap:6px;align-items:center"><span class="chip" id="listedChip" style="border-color:rgba(63,185,80,.5);color:var(--acc)" title="Homes newly on the market within the day window">🆕 Just Listed</span><span class="chip" id="soldChip" style="border-color:rgba(88,166,255,.5);color:var(--blue)" title="Pulled listings matched to a recorded sale within the day window">✅ Just Sold</span><span style="display:inline-flex;align-items:center;gap:4px;font-size:11px;color:var(--mut)">within <input type="number" id="recentDays" min="1" max="365" step="1" style="width:52px;background:var(--card);color:var(--ink);border:1px solid var(--line);border-radius:7px;padding:4px 6px;font-size:12px" title="Day window for Just Listed / Just Sold"> days</span></div>
+ <div style="margin-top:9px;display:flex;flex-wrap:wrap;gap:6px;align-items:center"><span class="chip" id="listedChip" style="border-color:rgba(63,185,80,.5);color:var(--acc)" title="Homes newly on the market within the day window">🆕 Just Listed</span><span class="chip" id="soldChip" style="border-color:rgba(88,166,255,.5);color:var(--blue)" title="Closed escrow — pulled listings matched to a recorded sale within the day window">✅ Just Sold</span><span style="display:inline-flex;align-items:center;gap:4px;font-size:11px;color:var(--mut)">within <input type="number" id="recentDays" min="1" max="365" step="1" style="width:52px;background:var(--card);color:var(--ink);border:1px solid var(--line);border-radius:7px;padding:4px 6px;font-size:12px" title="Day window for Just Listed / Just Sold"> days</span><span class="chip" id="escrowChip" style="border-color:rgba(210,153,34,.5);color:var(--warn)" title="Currently in escrow — pending or contingent">🤝 In Escrow</span><span class="chip" id="fellChip" style="border-color:rgba(248,81,73,.45);color:var(--bad)" title="Back on the market — was pending/contingent, now active again">↩️ Fell Out of Escrow</span></div>
<div style="margin-top:9px;display:flex;flex-wrap:wrap;gap:6px"><span class="chip" id="nqChip" data-warr="nonqm" style="border-color:rgba(188,140,255,.5);color:#bc8cff" title="Arcstone non-QM target: unwarrantable condos + small multifamily (1–9u) + mixed-use">◆ Non-QM</span><span class="chip" id="unwChip" data-warr="unwarrantable" style="border-color:rgba(248,81,73,.4);color:var(--bad)" title="Filter the table to non-FHA-approved condos (Arcstone non-QM target)">⚑ Unwarrantable condos</span><a class="navchip" href="/condos" title="Full condo drill-down page">condos page →</a></div></div>
<div class="rsec"><h4>Status</h4><div class="chips" id="fStatus"></div></div>
<div class="rsec"><h4>Submarket / city</h4><div class="chips" id="fCity"></div></div>
@@ -142,6 +142,8 @@ const COLS=[
{k:'year_built',l:'Year',t:'n'},
{k:'rent_control',l:'Rent Ctrl',t:'s'},
{k:'status',l:'Status',t:'status'},
+ {k:'mkt',l:'Market',t:'s'},
+ {k:'dom',l:'Days on Mkt',t:'n'},
{k:'off_date',l:'Pulled',t:'s'},
{k:'sold_price',l:'Sold $',t:'money'},
{k:'sold_date',l:'Sold',t:'s'},
@@ -152,8 +154,10 @@ const COLS=[
];
let DATA=[], view='table', sortKey='rank', sortDir=1, q='';
const _rd=(function(){ const v=parseInt(localStorage.getItem('mlsRecentDays'),10); return (v>0&&v<=365)?v:7; })();
-const F={types:new Set(),cities:new Set(),status:new Set(),pmin:null,pmax:null,unwarrantable:false,nonqm:false,shortlist:false,justsold:false,justlisted:false,recentDays:_rd};
+const F={types:new Set(),cities:new Set(),status:new Set(),pmin:null,pmax:null,unwarrantable:false,nonqm:false,shortlist:false,justsold:false,justlisted:false,inescrow:false,fellout:false,recentDays:_rd};
const daysSince=d=>{ if(!d) return Infinity; const t=Date.parse(d); return isNaN(t)?Infinity:(Date.now()-t)/86400000; };
+// Real listing age: Redfin DAYS ON MARKET when present (gap-immune), else our first-seen date.
+const listAge=r=>{ if(r.dom!=null&&isFinite(+r.dom)) return +r.dom; return daysSince(r.first_seen); };
// shared shortlist with the Explorer (same localStorage key + same listing ids)
const SHORT=new Set((function(){ try{ return JSON.parse(localStorage.getItem('cre_shortlist')||'[]'); }catch(e){ return []; } })());
function saveShort(){ try{ localStorage.setItem('cre_shortlist', JSON.stringify([...SHORT])); }catch(e){} }
@@ -221,9 +225,11 @@ function renderStats(rows){ const sb=$('#statsbar'); if(!sb) return; if(!rows.le
}
// Listing lifecycle: status 'off_market' means it left Redfin's active feed (pulled). disposition
// 'sold' (cross-referenced vs closed_sale) vs 'withdrawn'. Surfaced as Status = Active/Sold/Withdrawn.
-function lifeStatus(x){ return x.status==='off_market' ? (x.disposition==='sold'?'Sold':'Withdrawn') : 'Active'; }
-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:lifeStatus(c), first_seen:c.created_at||null, off_date:c.off_market_at?String(c.off_market_at).slice(0,10):null, disposition:c.disposition||null, sold_price:c.sold_price?+c.sold_price:null, sold_date:c.sold_date?String(c.sold_date).slice(0,10):null, 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:lifeStatus(s), first_seen:s.created_at||null, off_date:s.off_market_at?String(s.off_market_at).slice(0,10):null, disposition:s.disposition||null, sold_price:s.sold_price?+s.sold_price:null, sold_date:s.sold_date?String(s.sold_date).slice(0,10):null, source:s.source, firm:s.firm_name||null, beds:s.beds, baths:s.baths, sqft:s.sqft, broker_name:s.broker_name||null }; }
+const isEscrow=v=>/pending|contingent/i.test(v||'');
+// Status column shows lifecycle first, then In Escrow for active-but-under-contract listings.
+function lifeStatus(x){ if(x.status==='off_market') return x.disposition==='sold'?'Sold':'Withdrawn'; if(isEscrow(x.market_status)) return 'In Escrow'; return 'Active'; }
+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:lifeStatus(c), first_seen:c.created_at||null, dom:c.days_on_market!=null?+c.days_on_market:null, listed_date:c.listed_date?String(c.listed_date).slice(0,10):null, mkt:c.market_status||null, prev:c.prev_market_status||null, off_date:c.off_market_at?String(c.off_market_at).slice(0,10):null, disposition:c.disposition||null, sold_price:c.sold_price?+c.sold_price:null, sold_date:c.sold_date?String(c.sold_date).slice(0,10):null, 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:lifeStatus(s), first_seen:s.created_at||null, dom:s.days_on_market!=null?+s.days_on_market:null, listed_date:s.listed_date?String(s.listed_date).slice(0,10):null, mkt:s.market_status||null, prev:s.prev_market_status||null, off_date:s.off_market_at?String(s.off_market_at).slice(0,10):null, disposition:s.disposition||null, sold_price:s.sold_price?+s.sold_price:null, sold_date:s.sold_date?String(s.sold_date).slice(0,10):null, 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)); }
@@ -253,7 +259,7 @@ function isNonQM(r){ const t=(r.type||'').toLowerCase();
if(r.type==='Condo' && r.warrantable_status && r.warrantable_status!=='fha_approved') return true;
if(/multi/.test(t) && r.units>=1 && r.units<=9) return true;
if(/mixed/.test(t)) return true; return false; }
-function anySelection(){ return F.types.size||F.cities.size||F.status.size||F.pmin!=null||F.pmax!=null||F.unwarrantable||F.nonqm||F.shortlist||F.justsold||F.justlisted||(q&&q.length); }
+function anySelection(){ return F.types.size||F.cities.size||F.status.size||F.pmin!=null||F.pmax!=null||F.unwarrantable||F.nonqm||F.shortlist||F.justsold||F.justlisted||F.inescrow||F.fellout||(q&&q.length); }
function passFacets(r){
if(F.types.size && !F.types.has(r.type)) return false;
if(F.cities.size && !F.cities.has(r.city)) return false;
@@ -262,10 +268,13 @@ function passFacets(r){
if(F.pmax!=null && !(+r.price<=F.pmax)) return false;
if(F.unwarrantable && !(r.type==='Condo' && r.warrantable_status && r.warrantable_status!=='fha_approved')) return false;
if(F.nonqm && !isNonQM(r)) return false;
- if(F.justlisted && !(String(r.status||'').startsWith('Active') && daysSince(r.first_seen)<=F.recentDays)) return false;
- // Just Sold = recently LEFT the market (off_market_at) with a matched sale — off-market date is the
- // "just" event; sold_date is the closing date and can lag by weeks/months (escrow).
+ // Just Listed = still on the market (not sold/withdrawn) & listed within N days (real DAYS ON MARKET).
+ if(F.justlisted && !(r.status!=='Sold' && r.status!=='Withdrawn' && listAge(r)<=F.recentDays)) return false;
+ // Just Sold / Closed escrow = recently LEFT the market (off_market_at) with a matched sale.
if(F.justsold && !(r.disposition==='sold' && daysSince(r.off_date)<=F.recentDays)) return false;
+ // In Escrow = currently pending/contingent. Fell Out = was pending/contingent, now back on market.
+ if(F.inescrow && !isEscrow(r.mkt)) return false;
+ if(F.fellout && !(isEscrow(r.prev) && !isEscrow(r.mkt) && r.status!=='Sold' && r.status!=='Withdrawn')) return false;
if(F.shortlist && !SHORT.has(r.id)) return false;
return true;
}
@@ -356,8 +365,13 @@ if(nqEl) nqEl.addEventListener('click',()=>{ F.nonqm=!F.nonqm; nqEl.classList.to
// Just Listed / Just Sold quick-filters + adjustable day window (persisted)
const listedEl=document.getElementById('listedChip'), soldEl=document.getElementById('soldChip'), rdEl=document.getElementById('recentDays');
if(rdEl){ rdEl.value=F.recentDays; rdEl.addEventListener('change',()=>{ const v=parseInt(rdEl.value,10); if(v>0&&v<=365){ F.recentDays=v; localStorage.setItem('mlsRecentDays',v); if(F.justsold||F.justlisted) render(); } }); }
-if(listedEl) listedEl.addEventListener('click',()=>{ F.justlisted=!F.justlisted; listedEl.classList.toggle('active',F.justlisted); if(F.justlisted){ F.justsold=false; soldEl&&soldEl.classList.remove('active'); } render(); });
-if(soldEl) soldEl.addEventListener('click',()=>{ F.justsold=!F.justsold; soldEl.classList.toggle('active',F.justsold); if(F.justsold){ F.justlisted=false; listedEl&&listedEl.classList.remove('active'); } render(); });
+const escrowEl=document.getElementById('escrowChip'), fellEl=document.getElementById('fellChip');
+const quickChips=[['justlisted',listedEl],['justsold',soldEl],['inescrow',escrowEl],['fellout',fellEl]];
+function setQuick(key,el){ const on=!F[key]; quickChips.forEach(([k,e])=>{ F[k]=false; e&&e.classList.remove('active'); }); F[key]=on; if(el) el.classList.toggle('active',on); render(); }
+if(listedEl) listedEl.addEventListener('click',()=>setQuick('justlisted',listedEl));
+if(soldEl) soldEl.addEventListener('click',()=>setQuick('justsold',soldEl));
+if(escrowEl) escrowEl.addEventListener('click',()=>setQuick('inescrow',escrowEl));
+if(fellEl) fellEl.addEventListener('click',()=>setQuick('fellout',fellEl));
// collapsible rail tabs — all collapsed on load
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'); });
diff --git a/scripts/db/listing-signals-migration.sql b/scripts/db/listing-signals-migration.sql
new file mode 100644
index 0000000..c76327d
--- /dev/null
+++ b/scripts/db/listing-signals-migration.sql
@@ -0,0 +1,19 @@
+-- Real Redfin listing signals (was discarding these): days_on_market + market_status from the feed,
+-- derived listed_date (gap-immune "just listed"), and prev_market_status for escrow transitions.
+DO $$
+DECLARE t text;
+BEGIN
+ FOREACH t IN ARRAY ARRAY['sfr','condo'] LOOP
+ EXECUTE format('ALTER TABLE %I ADD COLUMN IF NOT EXISTS days_on_market int', t);
+ EXECUTE format('ALTER TABLE %I ADD COLUMN IF NOT EXISTS listed_date date', t);
+ EXECUTE format('ALTER TABLE %I ADD COLUMN IF NOT EXISTS market_status text', t);
+ EXECUTE format('ALTER TABLE %I ADD COLUMN IF NOT EXISTS prev_market_status text', t);
+ END LOOP;
+END $$;
+CREATE OR REPLACE VIEW condo_card AS
+ SELECT c.id, c.address, c.city, c.zip, c.price, c.hoa, c.beds, c.baths, c.sqft, c.year_built,
+ c.project_name, c.warrantable_status, c.warrant_source, c.warrant_signals, c.broker_name,
+ COALESCE(c.firm_name, f.name) AS firm_name, c.source, c.created_at,
+ c.status, c.last_seen, c.off_market_at, c.disposition, c.sold_price, c.sold_date,
+ c.days_on_market, c.listed_date, c.market_status, c.prev_market_status
+ FROM condo c LEFT JOIN firm f ON f.id = c.firm_id;
diff --git a/scripts/export-residential-snapshots.js b/scripts/export-residential-snapshots.js
index 44c3cbf..74de674 100644
--- a/scripts/export-residential-snapshots.js
+++ b/scripts/export-residential-snapshots.js
@@ -19,7 +19,8 @@ const WHERE = `WHERE price > 0 AND (status='active' OR off_market_at > now() - i
// SFR
const sfr = (await pool.query(`
SELECT id, address, city, zip, price, beds, baths, sqft, year_built, firm_name, broker_name,
- source, lat, lng, status, off_market_at, disposition, sold_price, sold_date, last_seen, created_at
+ source, lat, lng, status, off_market_at, disposition, sold_price, sold_date, last_seen, created_at,
+ days_on_market, listed_date, market_status, prev_market_status
FROM sfr ${WHERE} ORDER BY (status='active') DESC, created_at DESC, price DESC`)).rows;
fs.writeFileSync(path.join(ROOT, 'data', 'sfr-redfin.json'), JSON.stringify({
meta: { source: 'Redfin LA County single-family — feed-first (gis-csv), local Chrome',
@@ -31,7 +32,8 @@ const WHERE = `WHERE price > 0 AND (status='active' OR off_market_at > now() - i
const condos = (await pool.query(`
SELECT id, address, city, zip, price, hoa, beds, baths, sqft, year_built, project_name,
warrantable_status, warrant_source, warrant_signals, broker_name, firm_name, source, created_at,
- status, off_market_at, disposition, sold_price, sold_date, last_seen
+ status, off_market_at, disposition, sold_price, sold_date, last_seen,
+ days_on_market, listed_date, market_status, prev_market_status
FROM condo_card ${WHERE} ORDER BY (status='active') DESC, created_at DESC, price DESC`)).rows;
fs.writeFileSync(path.join(ROOT, 'data', 'condos-redfin.json'), JSON.stringify({
meta: { source: 'Redfin LA County condos — feed-first (gis-csv), local Chrome',
diff --git a/scripts/fetch-condos-redfin.js b/scripts/fetch-condos-redfin.js
index b5c124d..d4d506d 100644
--- a/scripts/fetch-condos-redfin.js
+++ b/scripts/fetch-condos-redfin.js
@@ -95,7 +95,7 @@ function condosFromCSV(text, regionName) {
const ci = {
type: col('PROPERTY TYPE'), addr: col('ADDRESS'), city: col('CITY'), zip: col('ZIP OR POSTAL CODE'),
price: col('PRICE'), beds: col('BEDS'), baths: col('BATHS'), sqft: col('SQUARE FEET'),
- year: col('YEAR BUILT'), hoa: col('HOA/MONTH'), status: col('STATUS'),
+ year: col('YEAR BUILT'), hoa: col('HOA/MONTH'), status: col('STATUS'), dom: col('DAYS ON MARKET'),
url: H.findIndex(h => h.toUpperCase().startsWith('URL')), source: col('SOURCE'), mls: col('MLS#')
};
const out = [];
@@ -124,6 +124,8 @@ function condosFromCSV(text, regionName) {
baths: ci.baths >= 0 && row[ci.baths] ? Number(row[ci.baths]) || null : null,
sqft: ci.sqft >= 0 && row[ci.sqft] ? Math.round(Number(String(row[ci.sqft]).replace(/[^\d.]/g, ''))) || null : null,
year_built: ci.year >= 0 && row[ci.year] ? Number(row[ci.year]) || null : null,
+ days_on_market: ci.dom >= 0 && row[ci.dom] !== '' && row[ci.dom] != null ? (Number(String(row[ci.dom]).replace(/[^\d]/g, ''))) : null,
+ market_status: ci.status >= 0 ? ((row[ci.status] || '').trim() || null) : null,
property_type: ptype,
project_name: null, // CSV has no HOA/project name; classifier uses address+zip vs FHA list
listing_text: ptype, // minimal text; richer description not in the feed
@@ -169,25 +171,25 @@ function condosFromCSV(text, regionName) {
await page.goto('https://www.redfin.com/city/11203/CA/Los-Angeles/filter/property-type=condo', { waitUntil: 'domcontentloaded' }).catch(() => {});
await page.waitForTimeout(2500);
+ // status=9 active for-sale; status=130 pending/contingent (in escrow). Sweep both.
+ const fetchStatus = async (status) => {
+ const u = `https://www.redfin.com/stingray/api/gis-csv?al=1®ion_id=${reg.region_id}®ion_type=${reg.region_type}&uipt=3&num_homes=350&status=${status}&sf=1,2,3,5,6,7&v=8`;
+ try {
+ const res = await page.evaluate(async (uu) => { const r = await fetch(uu, { headers: { accept: 'text/csv' } }); return { status: r.status, text: await r.text() }; }, u);
+ return (res.status === 200 && res.text && res.text.includes('PROPERTY TYPE')) ? condosFromCSV(res.text, reg.name) : [];
+ } catch (e) { return []; }
+ };
for (const reg of group) {
const before = captured.size;
- const feedUrl = `https://www.redfin.com/stingray/api/gis-csv?al=1®ion_id=${reg.region_id}®ion_type=${reg.region_type}&uipt=3&num_homes=350&status=9&sf=1,2,3,5,6,7&v=8`;
- let csv = null;
- try {
- const res = await page.evaluate(async (u) => {
- const r = await fetch(u, { headers: { accept: 'text/csv' } });
- return { status: r.status, text: await r.text() };
- }, feedUrl);
- if (res.status === 200 && res.text && res.text.includes('PROPERTY TYPE')) csv = res.text;
- else process.stderr.write(` ${reg.name}: feed status ${res.status}, ${res.text ? res.text.slice(0, 60) : 'empty'}\n`);
- } catch (e) { process.stderr.write(` ${reg.name}: feed err ${e.message.slice(0, 60)}\n`); }
-
- if (csv) {
- const condos = condosFromCSV(csv, reg.name);
+ const active = await fetchStatus(9);
+ await page.waitForTimeout(250);
+ let pend = []; try { pend = await fetchStatus(130); } catch (_) {}
+ const condos = active.concat(pend);
+ if (condos.length) {
for (const c of condos) if (!captured.has(c.id)) captured.set(c.id, c);
const gained = captured.size - before;
perRegion.push({ region: reg.name, region_id: reg.region_id, region_type: reg.region_type, parsed: condos.length, gained, cumulative: captured.size });
- process.stderr.write(` ${reg.name}: parsed ${condos.length} condos (+${gained} new, cumulative ${captured.size})\n`);
+ process.stderr.write(` ${reg.name}: ${active.length} active + ${pend.length} escrow (+${gained} new, cumulative ${captured.size})\n`);
} else {
perRegion.push({ region: reg.name, region_id: reg.region_id, gained: 0, error: 'no feed' });
}
@@ -232,15 +234,17 @@ function condosFromCSV(text, regionName) {
const firmId = c.firm_name ? await brokerdb.upsertFirm(c.firm_name) : null;
await brokerdb.pool.query(
`INSERT INTO condo(id,address,city,zip,price,hoa,beds,baths,sqft,year_built,project_name,
- listing_text,warrantable_status,warrant_source,warrant_signals,firm_id,firm_name,broker_name,source,last_seen,status)
- VALUES($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,now(),'active')
+ listing_text,warrantable_status,warrant_source,warrant_signals,firm_id,firm_name,broker_name,source,last_seen,status,days_on_market,market_status,listed_date)
+ VALUES($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,now(),'active',$20::int,$21, CASE WHEN $20::int IS NOT NULL THEN CURRENT_DATE - $20::int ELSE NULL END)
ON CONFLICT(id) DO UPDATE SET price=EXCLUDED.price, hoa=EXCLUDED.hoa,
warrantable_status=EXCLUDED.warrantable_status, warrant_source=EXCLUDED.warrant_source,
warrant_signals=EXCLUDED.warrant_signals,
- last_seen=now(), status='active', off_market_at=NULL, disposition=NULL, sold_price=NULL, sold_date=NULL`,
+ last_seen=now(), status='active', off_market_at=NULL, disposition=NULL, sold_price=NULL, sold_date=NULL,
+ days_on_market=EXCLUDED.days_on_market, listed_date=EXCLUDED.listed_date,
+ prev_market_status=condo.market_status, market_status=EXCLUDED.market_status`,
[c.id, c.address, c.city, c.zip, c.price, c.hoa, c.beds, c.baths, c.sqft, c.year_built,
c.project_name, c.listing_text, c.warrantable_status, c.warrant_source,
- JSON.stringify(c.warrant_signals), firmId, c.firm_name, c.broker_name, c.source]);
+ JSON.stringify(c.warrant_signals), firmId, c.firm_name, c.broker_name, c.source, c.days_on_market ?? null, c.market_status ?? null]);
}
process.stderr.write(`Upserted ${condos.length} into cre.condo\n`);
} catch (e) { process.stderr.write('DB upsert err: ' + e.message + '\n'); }
diff --git a/scripts/fetch-sfr-redfin.js b/scripts/fetch-sfr-redfin.js
index b7fc62e..755a66b 100644
--- a/scripts/fetch-sfr-redfin.js
+++ b/scripts/fetch-sfr-redfin.js
@@ -81,7 +81,7 @@ function sfrsFromCSV(text) {
const ci = {
type: col('PROPERTY TYPE'), addr: col('ADDRESS'), city: col('CITY'), zip: col('ZIP OR POSTAL CODE'),
price: col('PRICE'), beds: col('BEDS'), baths: col('BATHS'), sqft: col('SQUARE FEET'),
- year: col('YEAR BUILT'), status: col('STATUS'),
+ year: col('YEAR BUILT'), status: col('STATUS'), dom: col('DAYS ON MARKET'),
url: H.findIndex(h => h.toUpperCase().startsWith('URL')), mls: col('MLS#')
};
const out = [];
@@ -109,6 +109,8 @@ function sfrsFromCSV(text) {
baths: ci.baths >= 0 && row[ci.baths] ? Number(row[ci.baths]) || null : null,
sqft: ci.sqft >= 0 && row[ci.sqft] ? Math.round(Number(String(row[ci.sqft]).replace(/[^\d.]/g, ''))) || null : null,
year_built: ci.year >= 0 && row[ci.year] ? Number(row[ci.year]) || null : null,
+ days_on_market: ci.dom >= 0 && row[ci.dom] !== '' && row[ci.dom] != null ? (Number(String(row[ci.dom]).replace(/[^\d]/g, '')) ) : null,
+ market_status: ci.status >= 0 ? ((row[ci.status] || '').trim() || null) : null,
listing_text: ptype,
source: url ? (url.startsWith('http') ? url : 'https://www.redfin.com' + url) : null
});
@@ -146,13 +148,15 @@ function loadRegions() {
const upsert = async (s) => {
if (!s.source) return;
const ins = await pool.query(
- `INSERT INTO sfr(id,address,city,zip,price,beds,baths,sqft,year_built,listing_text,source,last_seen,status)
- VALUES($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,now(),'active')
+ `INSERT INTO sfr(id,address,city,zip,price,beds,baths,sqft,year_built,listing_text,source,last_seen,status,days_on_market,market_status,listed_date)
+ VALUES($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,now(),'active',$12::int,$13, CASE WHEN $12::int IS NOT NULL THEN CURRENT_DATE - $12::int ELSE NULL END)
ON CONFLICT(id) DO UPDATE SET price=EXCLUDED.price, beds=EXCLUDED.beds, baths=EXCLUDED.baths,
sqft=EXCLUDED.sqft, year_built=EXCLUDED.year_built,
- last_seen=now(), status='active', off_market_at=NULL, disposition=NULL, sold_price=NULL, sold_date=NULL
+ last_seen=now(), status='active', off_market_at=NULL, disposition=NULL, sold_price=NULL, sold_date=NULL,
+ days_on_market=EXCLUDED.days_on_market, listed_date=EXCLUDED.listed_date,
+ prev_market_status=sfr.market_status, market_status=EXCLUDED.market_status
RETURNING (xmax = 0) AS inserted`,
- [s.id, s.address, s.city, s.zip, s.price, s.beds, s.baths, s.sqft, s.year_built, s.listing_text, s.source])
+ [s.id, s.address, s.city, s.zip, s.price, s.beds, s.baths, s.sqft, s.year_built, s.listing_text, s.source, s.days_on_market ?? null, s.market_status ?? null])
.catch(() => null);
if (ins && ins.rows[0] && ins.rows[0].inserted) added++;
};
@@ -187,10 +191,12 @@ function loadRegions() {
await page.goto('https://www.redfin.com/city/11203/CA/Los-Angeles/filter/property-type=house', { waitUntil: 'domcontentloaded' }).catch(() => {});
await page.waitForTimeout(2500);
- const fetchRegion = async (reg, band) => {
+ // status=9 = active for-sale; status=130 = pending/contingent (in escrow). We sweep both so the
+ // catalog carries in-escrow homes (market_status Pending/Contingent) for the escrow filters.
+ const fetchRegion = async (reg, band, status = 9) => {
let extra = '';
if (band) { if (band[0] > 0) extra += `&min_price=${band[0]}`; if (band[1] > 0) extra += `&max_price=${band[1]}`; }
- const url = `https://www.redfin.com/stingray/api/gis-csv?al=1®ion_id=${reg.region_id}®ion_type=${reg.region_type}&uipt=1&num_homes=${NUM_HOMES}&status=9&sf=1,2,3,5,6,7&v=8${extra}`;
+ const url = `https://www.redfin.com/stingray/api/gis-csv?al=1®ion_id=${reg.region_id}®ion_type=${reg.region_type}&uipt=1&num_homes=${NUM_HOMES}&status=${status}&sf=1,2,3,5,6,7&v=8${extra}`;
const res = await page.evaluate(async (u) => {
try { const r = await fetch(u, { headers: { accept: 'text/csv' } }); return { status: r.status, text: r.ok ? await r.text() : '' }; }
catch (e) { return { status: 0, text: '' }; }
@@ -202,16 +208,19 @@ function loadRegions() {
let base;
try { base = await fetchRegion(reg); }
catch (e) { process.stderr.write(' session closed mid-sweep — flushed regions persist, re-run to resume.\n'); throw e; }
+ // Pending/contingent (in-escrow) — small set, fetch once, no price-banding.
+ let pending = [];
+ try { pending = await fetchRegion(reg, null, 130); await page.waitForTimeout(250); } catch (_) {}
if (base.length < CAP_THRESHOLD) {
- await flush(base, reg);
- process.stderr.write(` ${reg.name}: ${base.length} SFR (cumulative +${added} new)\n`);
+ await flush(base.concat(pending), reg);
+ process.stderr.write(` ${reg.name}: ${base.length} active + ${pending.length} escrow (cumulative +${added} new)\n`);
await page.waitForTimeout(400);
continue;
}
// Capped — re-query in price bands and union (dedupe on id).
const seenId = new Set(); const unioned = [];
const keep = arr => { for (const x of arr) if (x.id && !seenId.has(x.id)) { seenId.add(x.id); unioned.push(x); } };
- keep(base);
+ keep(base); keep(pending);
let broke = false;
for (const band of PRICE_BANDS) {
try { keep(await fetchRegion(reg, band)); }
diff --git a/scripts/serve.js b/scripts/serve.js
index 5ee330d..bbb1ab5 100644
--- a/scripts/serve.js
+++ b/scripts/serve.js
@@ -280,7 +280,8 @@ app.get('/api/condos', async (req, res) => {
const r = await brokerdb.pool.query(
`SELECT id, address, city, zip, price, hoa, beds, baths, sqft, year_built, project_name,
warrantable_status, warrant_source, warrant_signals, broker_name, firm_name, source, created_at,
- status, off_market_at, disposition, sold_price, sold_date
+ status, off_market_at, disposition, sold_price, sold_date,
+ days_on_market, listed_date, market_status, prev_market_status
FROM condo_card ${where} ORDER BY (status='active') DESC, created_at DESC LIMIT 5000`, args);
if (r.rows.length) return res.json({ condos: r.rows, label: CONDO_LABEL, source: 'db' });
} catch (_) { /* fall through to snapshot */ }
@@ -309,7 +310,8 @@ app.get('/api/residential', async (req, res) => {
const r = await brokerdb.pool.query(
`SELECT id, address, city, zip, price, beds, baths, sqft, year_built,
firm_name, broker_name, source, lat, lng,
- status, off_market_at, disposition, sold_price, sold_date, created_at
+ status, off_market_at, disposition, sold_price, sold_date, created_at,
+ days_on_market, listed_date, market_status, prev_market_status
FROM sfr WHERE price > 0 AND (status='active' OR off_market_at > now() - interval '180 days')
ORDER BY (status='active') DESC, created_at DESC, price DESC LIMIT $1`, [limit]);
if (r.rows.length) return res.json({ sfr: r.rows, label: SFR_LABEL, source: 'db' });
← fae3225 mls: Just Listed + Just Sold quick-filter chips with adjusta
·
back to Commercialrealestate
·
fix: condo fetchStatus referenced loop-scoped reg from outer eb3ad19 →