← back to Commercialrealestate
residential: off-market tracking — see pulled listings, labeled SOLD vs WITHDRAWN
2d64c25140eb6b6fbb272a140de06957609b3407 · 2026-07-07 06:50:18 -0700 · Steve Abrams
- schema: sfr/condo gain last_seen/status/off_market_at/disposition/sold_price/sold_date;
condo_card view recreated to expose them.
- scrapers stamp last_seen=now() on every seen listing + reset a re-listed home to active.
- mark-off-market.js: after a FULL sweep, flag listings that stopped appearing (last_seen<sweep
start) as off_market, then cross-ref closed_sale (normalized addr key) -> disposition sold
(+price/date) vs withdrawn. >40%-would-flag safety abort guards partial/throttled sweeps.
- export-residential-snapshots.js: dumps BOTH snapshots post-mark w/ lifecycle fields (active +
off-market within 180d). Supersedes export-sfr-snapshot.js in the refresh flow.
- /api/residential + /api/condos return lifecycle fields, active-first.
- mls.html: Status now Active/Sold/Withdrawn + Pulled/Sold$/Sold-date columns; Status facet
buckets Active vs Off-market.
- refresh.sh: capture MARK_SINCE pre-sweep -> mark -> combined export.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Files touched
M public/mls.htmlA scripts/db/off-market-migration.sqlA scripts/export-residential-snapshots.jsM scripts/fetch-condos-redfin.jsM scripts/fetch-sfr-redfin.jsA scripts/mark-off-market.jsM scripts/run-residential-refresh.shM scripts/serve.js
Diff
commit 2d64c25140eb6b6fbb272a140de06957609b3407
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Tue Jul 7 06:50:18 2026 -0700
residential: off-market tracking — see pulled listings, labeled SOLD vs WITHDRAWN
- schema: sfr/condo gain last_seen/status/off_market_at/disposition/sold_price/sold_date;
condo_card view recreated to expose them.
- scrapers stamp last_seen=now() on every seen listing + reset a re-listed home to active.
- mark-off-market.js: after a FULL sweep, flag listings that stopped appearing (last_seen<sweep
start) as off_market, then cross-ref closed_sale (normalized addr key) -> disposition sold
(+price/date) vs withdrawn. >40%-would-flag safety abort guards partial/throttled sweeps.
- export-residential-snapshots.js: dumps BOTH snapshots post-mark w/ lifecycle fields (active +
off-market within 180d). Supersedes export-sfr-snapshot.js in the refresh flow.
- /api/residential + /api/condos return lifecycle fields, active-first.
- mls.html: Status now Active/Sold/Withdrawn + Pulled/Sold$/Sold-date columns; Status facet
buckets Active vs Off-market.
- refresh.sh: capture MARK_SINCE pre-sweep -> mark -> combined export.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
public/mls.html | 10 ++++-
scripts/db/off-market-migration.sql | 34 ++++++++++++++++
scripts/export-residential-snapshots.js | 45 +++++++++++++++++++++
scripts/fetch-condos-redfin.js | 7 ++--
scripts/fetch-sfr-redfin.js | 7 ++--
scripts/mark-off-market.js | 69 +++++++++++++++++++++++++++++++++
scripts/run-residential-refresh.sh | 20 +++++++---
scripts/serve.js | 11 ++++--
8 files changed, 186 insertions(+), 17 deletions(-)
diff --git a/public/mls.html b/public/mls.html
index 9211ffc..fe26252 100644
--- a/public/mls.html
+++ b/public/mls.html
@@ -141,6 +141,9 @@ const COLS=[
{k:'year_built',l:'Year',t:'n'},
{k:'rent_control',l:'Rent Ctrl',t:'s'},
{k:'status',l:'Status',t:'status'},
+ {k:'off_date',l:'Pulled',t:'s'},
+ {k:'sold_price',l:'Sold $',t:'money'},
+ {k:'sold_date',l:'Sold',t:'s'},
{k:'firm',l:'Firm',t:'s'},
{k:'financeScore',l:'Finance',t:'n'},
{k:'composite',l:'Score',t:'n'},
@@ -213,8 +216,11 @@ function renderStats(rows){ const sb=$('#statsbar'); if(!sb) return; if(!rows.le
+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 }; }
+// 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), 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), 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)); }
diff --git a/scripts/db/off-market-migration.sql b/scripts/db/off-market-migration.sql
new file mode 100644
index 0000000..4333891
--- /dev/null
+++ b/scripts/db/off-market-migration.sql
@@ -0,0 +1,34 @@
+-- off-market-migration.sql — add listing lifecycle tracking to sfr + condo so we can surface homes
+-- that were LISTED but PULLED OFF the market (no longer in Redfin's active status=9 feed), and label
+-- each pulled home SOLD (cross-referenced against closed_sale) vs WITHDRAWN.
+--
+-- last_seen : stamped now() every time the scraper sees the listing in the active feed
+-- status : 'active' (in latest full sweep) | 'off_market' (stopped appearing)
+-- off_market_at: when the mark-and-sweep first flagged it gone
+-- disposition : 'sold' | 'withdrawn' (only meaningful when off_market)
+-- sold_price/sold_date : filled from closed_sale when a sold match is found
+
+DO $$
+DECLARE t text;
+BEGIN
+ FOREACH t IN ARRAY ARRAY['sfr','condo'] LOOP
+ EXECUTE format('ALTER TABLE %I ADD COLUMN IF NOT EXISTS last_seen timestamptz', t);
+ EXECUTE format('ALTER TABLE %I ADD COLUMN IF NOT EXISTS status text DEFAULT ''active''', t);
+ EXECUTE format('ALTER TABLE %I ADD COLUMN IF NOT EXISTS off_market_at timestamptz', t);
+ EXECUTE format('ALTER TABLE %I ADD COLUMN IF NOT EXISTS disposition text', t);
+ EXECUTE format('ALTER TABLE %I ADD COLUMN IF NOT EXISTS sold_price numeric', t);
+ EXECUTE format('ALTER TABLE %I ADD COLUMN IF NOT EXISTS sold_date date', t);
+ EXECUTE format('UPDATE %I SET last_seen=created_at WHERE last_seen IS NULL', t);
+ EXECUTE format('UPDATE %I SET status=''active'' WHERE status IS NULL', t);
+ EXECUTE format('CREATE INDEX IF NOT EXISTS %I ON %I(status)', t||'_status_idx', t);
+ END LOOP;
+END $$;
+
+-- Recreate condo_card so /api/condos sees the new lifecycle columns (view had an explicit column list).
+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
+ 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
new file mode 100644
index 0000000..44c3cbf
--- /dev/null
+++ b/scripts/export-residential-snapshots.js
@@ -0,0 +1,45 @@
+// export-residential-snapshots.js — dump BOTH residential snapshots (data/sfr-redfin.json +
+// data/condos-redfin.json) from the cre DB AFTER mark-off-market.js has run, so the prod (no-DB)
+// snapshots carry the lifecycle fields (status / off_market_at / disposition / sold_price / sold_date).
+//
+// Supersedes export-sfr-snapshot.js in the refresh flow (that one is SFR-only and pre-lifecycle).
+// Includes every active listing + off-market listings pulled within the last OFFMKT_DAYS (so the
+// "pulled / sold" view has recent history without the file growing unbounded).
+'use strict';
+const fs = require('fs');
+const path = require('path');
+const { Pool } = require('pg');
+
+const ROOT = path.join(__dirname, '..');
+const pool = new Pool({ host: '/tmp', port: 5432, database: 'cre', user: process.env.USER || 'stevestudio2' });
+const OFFMKT_DAYS = +(process.env.OFFMKT_DAYS || 180);
+const WHERE = `WHERE price > 0 AND (status='active' OR off_market_at > now() - interval '${OFFMKT_DAYS} days')`;
+
+(async () => {
+ // 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
+ 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',
+ note: 'status=off_market means the listing left Redfin\'s active feed; disposition=sold cross-referenced against closed_sale, else withdrawn.',
+ offmarket_window_days: OFFMKT_DAYS, fetched_at: new Date().toISOString(), count: sfr.length },
+ sfr }, null, 2));
+
+ // Condos (via condo_card view — carries warrantability + lifecycle columns)
+ 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
+ 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',
+ offmarket_window_days: OFFMKT_DAYS, fetched_at: new Date().toISOString(), count: condos.length },
+ condos }, null, 2));
+
+ const byStatus = (rows) => rows.reduce((a, r) => (a[r.status] = (a[r.status] || 0) + 1, a), {});
+ console.log(JSON.stringify({ sfr: { count: sfr.length, byStatus: byStatus(sfr) },
+ condos: { count: condos.length, byStatus: byStatus(condos) } }));
+ await pool.end();
+})().catch(e => { console.error(e.message); process.exit(1); });
diff --git a/scripts/fetch-condos-redfin.js b/scripts/fetch-condos-redfin.js
index 9ee7d88..b5c124d 100644
--- a/scripts/fetch-condos-redfin.js
+++ b/scripts/fetch-condos-redfin.js
@@ -232,11 +232,12 @@ 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)
- VALUES($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19)
+ 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')
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`,
+ warrant_signals=EXCLUDED.warrant_signals,
+ last_seen=now(), status='active', off_market_at=NULL, disposition=NULL, sold_price=NULL, sold_date=NULL`,
[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]);
diff --git a/scripts/fetch-sfr-redfin.js b/scripts/fetch-sfr-redfin.js
index 2fb17b9..b7fc62e 100644
--- a/scripts/fetch-sfr-redfin.js
+++ b/scripts/fetch-sfr-redfin.js
@@ -146,10 +146,11 @@ 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)
- VALUES($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11)
+ `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')
ON CONFLICT(id) DO UPDATE SET price=EXCLUDED.price, beds=EXCLUDED.beds, baths=EXCLUDED.baths,
- sqft=EXCLUDED.sqft, year_built=EXCLUDED.year_built
+ 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
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])
.catch(() => null);
diff --git a/scripts/mark-off-market.js b/scripts/mark-off-market.js
new file mode 100644
index 0000000..a75dfff
--- /dev/null
+++ b/scripts/mark-off-market.js
@@ -0,0 +1,69 @@
+// mark-off-market.js — after a FULL Redfin sweep, flag listings that stopped appearing (pulled off
+// the market) and label each SOLD vs WITHDRAWN.
+//
+// Logic:
+// 1. Any status='active' row whose last_seen is older than MARK_SINCE (the timestamp captured just
+// BEFORE this run's sweep started) did NOT appear in the latest sweep -> mark off_market.
+// 2. For each freshly off_market row, look for a sale in closed_sale using a normalized address key
+// (house-number + first street word + zip). Match -> disposition='sold' (+ sold_price/date from
+// the most recent sale); no match -> disposition='withdrawn'.
+// 3. SAFETY: refuse to flag if it would take out >40% of active rows — that means the sweep missed
+// most regions (Redfin throttle / crash), not that half the market vanished.
+//
+// Usage: MARK_SINCE=<ISO8601> node scripts/mark-off-market.js
+// MARK_SINCE MUST be the moment before the sweep began. Without it the script refuses to run.
+'use strict';
+const { Pool } = require('pg');
+const pool = new Pool({ host: '/tmp', port: 5432, database: 'cre', user: process.env.USER || 'stevestudio2' });
+
+const SINCE = process.env.MARK_SINCE;
+const SAFETY_FRACTION = +(process.env.MARK_SAFETY_FRACTION || 0.40);
+
+// normalized address key shared by listings + closed_sale: leading number + first street word + zip
+const NUM = `substring(lower(trim({a})) from '^([0-9]+)')`;
+const ST1 = `(regexp_split_to_array(lower(regexp_replace(trim({a}),'^[0-9]+\\s+','')),'\\s+'))[1]`;
+
+async function sweepTable(t) {
+ const total = (await pool.query(`SELECT count(*)::int n FROM ${t} WHERE status='active'`)).rows[0].n;
+ const cand = (await pool.query(
+ `SELECT count(*)::int n FROM ${t} WHERE status='active' AND (last_seen IS NULL OR last_seen < $1)`, [SINCE])).rows[0].n;
+ if (total > 0 && cand / total > SAFETY_FRACTION) {
+ return { table: t, aborted: true, total, would_flag: cand, reason: `>${Math.round(SAFETY_FRACTION*100)}% of active would flag — sweep looks incomplete` };
+ }
+ // 1) flag the disappeared listings
+ const flagged = (await pool.query(
+ `UPDATE ${t} SET status='off_market', off_market_at=now()
+ WHERE status='active' AND (last_seen IS NULL OR last_seen < $1) RETURNING id`, [SINCE])).rowCount;
+
+ // 2) sold cross-ref for anything off_market not yet dispositioned
+ const sold = (await pool.query(
+ `WITH m AS (
+ SELECT DISTINCT ON (l.id) l.id, c.sold_price, c.sold_date
+ FROM ${t} l
+ JOIN closed_sale c
+ ON c.zip = l.zip
+ AND ${NUM.replace('{a}','c.address')} = ${NUM.replace('{a}','l.address')}
+ AND ${ST1.replace('{a}','c.address')} = ${ST1.replace('{a}','l.address')}
+ WHERE l.status='off_market' AND l.disposition IS NULL
+ ORDER BY l.id, c.sold_date DESC NULLS LAST)
+ UPDATE ${t} SET disposition='sold', sold_price=m.sold_price, sold_date=m.sold_date
+ FROM m WHERE ${t}.id = m.id RETURNING ${t}.id`)).rowCount;
+
+ // 3) everything else off_market with no sale match = withdrawn
+ const withdrawn = (await pool.query(
+ `UPDATE ${t} SET disposition='withdrawn'
+ WHERE status='off_market' AND disposition IS NULL RETURNING id`)).rowCount;
+
+ return { table: t, total_active_before: total, flagged_off_market: flagged, labeled_sold: sold, labeled_withdrawn: withdrawn };
+}
+
+(async () => {
+ if (!SINCE || isNaN(Date.parse(SINCE))) {
+ console.error('MARK_SINCE (ISO timestamp before the sweep) is required — refusing to run.');
+ process.exit(1);
+ }
+ const out = [];
+ for (const t of ['sfr', 'condo']) out.push(await sweepTable(t));
+ console.log(JSON.stringify({ mark_since: SINCE, results: out }, null, 2));
+ await pool.end();
+})().catch(e => { console.error(e.message); process.exit(1); });
diff --git a/scripts/run-residential-refresh.sh b/scripts/run-residential-refresh.sh
index a3283ec..32ed28d 100755
--- a/scripts/run-residential-refresh.sh
+++ b/scripts/run-residential-refresh.sh
@@ -37,19 +37,29 @@ send_alert() { # $1=subject $2=html-body
|| echo "[$(date '+%H:%M:%S')] alert email FAILED: $resp" >> "$LOG"
}
-# 1) Scrape condos (full sweep, local Chrome). Upserts cre.condo + rewrites data/condos-redfin.json.
+# Capture the sweep-start BEFORE scraping. mark-off-market flags any active listing whose last_seen
+# is older than this (i.e. it didn't reappear in this sweep = pulled off the market).
+MARK_SINCE_TS=$(node -e 'console.log(new Date().toISOString())')
+echo "[$(date '+%H:%M:%S')] sweep start (MARK_SINCE) = $MARK_SINCE_TS" >> "$LOG"
+
+# 1) Scrape condos (full sweep, local Chrome). Upserts cre.condo (last_seen=now on each seen listing).
echo "[$(date '+%H:%M:%S')] condos..." >> "$LOG"
node scripts/fetch-condos-redfin.js >> "$LOG" 2>&1
CONDO_RC=$?
-# 2) Scrape SFR (full sweep, local Chrome). Upserts cre.sfr only.
+# 2) Scrape SFR (full sweep, local Chrome). Upserts cre.sfr (last_seen=now on each seen listing).
echo "[$(date '+%H:%M:%S')] sfr..." >> "$LOG"
node scripts/fetch-sfr-redfin.js >> "$LOG" 2>&1
SFR_RC=$?
-# 3) Export the SFR snapshot from the DB (fetch-sfr writes DB only).
-echo "[$(date '+%H:%M:%S')] export sfr snapshot..." >> "$LOG"
-node scripts/export-sfr-snapshot.js >> "$LOG" 2>&1
+# 3) Mark listings that stopped appearing as off_market + label SOLD (vs closed_sale) / WITHDRAWN.
+# Has its own >40%-would-flag safety abort, so a partial/throttled sweep can't mass-flag the market.
+echo "[$(date '+%H:%M:%S')] mark off-market..." >> "$LOG"
+MARK_SINCE="$MARK_SINCE_TS" node scripts/mark-off-market.js >> "$LOG" 2>&1
+
+# 4) Export BOTH snapshots from the DB AFTER marking (carries status/sold/withdrawn + prices).
+echo "[$(date '+%H:%M:%S')] export snapshots..." >> "$LOG"
+node scripts/export-residential-snapshots.js >> "$LOG" 2>&1
EXP_RC=$?
# 4) Guard: never push a truncated snapshot. Require both files to be sane before rsync.
diff --git a/scripts/serve.js b/scripts/serve.js
index 1c3af06..d49306a 100644
--- a/scripts/serve.js
+++ b/scripts/serve.js
@@ -279,8 +279,9 @@ app.get('/api/condos', async (req, res) => {
// Explicit card-safe column projection (condo_card is a curated view; keep it tight, no wildcard).
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
- FROM condo_card ${where} ORDER BY created_at DESC LIMIT 5000`, args);
+ warrantable_status, warrant_source, warrant_signals, broker_name, firm_name, source, created_at,
+ status, off_market_at, disposition, sold_price, sold_date
+ 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 */ }
}
@@ -307,8 +308,10 @@ app.get('/api/residential', async (req, res) => {
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]);
+ firm_name, broker_name, source, lat, lng,
+ status, off_market_at, disposition, sold_price, sold_date
+ 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' });
} catch (_) { /* fall through to snapshot */ }
}
← ce1d6f2 refresh: email steve-office (George) on sanity-gate abort or
·
back to Commercialrealestate
·
CRE: migrate condos-report from mac2 (node path + Max-plan c 6061349 →