broker snapshot: active-listing recency gate + size-ceiling health verdict (DTD A-with-rider)

77b92999a351a14a1f7d3dab6099440e76cc2512 · 2026-08-19 11:38:38 -0700 · Steve

- curCom query: WHERE l.created_at > now()-interval '120 days' — the commercial `listing`
  table has NO status column (mark-off-market runs on the residential table), so a
  created_at recency window is the correctness gate that stops a future stale row from
  surfacing as an ACTIVE listing on a public broker profile. No-op today (all listings
  <=90d); real fix needs a last_seen column (follow-up).
- post-write: emit snapshot-health PASS/WARN/FAIL (fleet-health vocab) + WARN>30MB /
  FAIL>60MB, since readBrokerSnap loads the whole file into the prod Node heap.
Not run here (parallel session just regenerated the snapshot); nightly regen applies it.
Item 1 (snapContact reads brokerDetail) already shipped by the parallel session (fd9337c).

Files touched

M scripts/export-brokers-snapshot.js

Diff

commit 77b92999a351a14a1f7d3dab6099440e76cc2512
Author: Steve <steve@designerwallcoverings.com>
Date:   Wed Aug 19 11:38:38 2026 -0700

    broker snapshot: active-listing recency gate + size-ceiling health verdict (DTD A-with-rider)
    
    - curCom query: WHERE l.created_at > now()-interval '120 days' — the commercial `listing`
      table has NO status column (mark-off-market runs on the residential table), so a
      created_at recency window is the correctness gate that stops a future stale row from
      surfacing as an ACTIVE listing on a public broker profile. No-op today (all listings
      <=90d); real fix needs a last_seen column (follow-up).
    - post-write: emit snapshot-health PASS/WARN/FAIL (fleet-health vocab) + WARN>30MB /
      FAIL>60MB, since readBrokerSnap loads the whole file into the prod Node heap.
    Not run here (parallel session just regenerated the snapshot); nightly regen applies it.
    Item 1 (snapContact reads brokerDetail) already shipped by the parallel session (fd9337c).
---
 scripts/export-brokers-snapshot.js | 20 ++++++++++++++++++--
 1 file changed, 18 insertions(+), 2 deletions(-)

diff --git a/scripts/export-brokers-snapshot.js b/scripts/export-brokers-snapshot.js
index a078069..e017a36 100644
--- a/scripts/export-brokers-snapshot.js
+++ b/scripts/export-brokers-snapshot.js
@@ -40,8 +40,15 @@ async function main() {
   const CAP_B = 100, CAP_BX = 12, CAP_F = 300, CAP_FX = 40;
   const rows = (s) => pool.query(s).then(r => r.rows);
   const [curCom, curCon, closedAll, expiredAll] = await Promise.all([
+    // Correctness gate (DTD 2026-08-19, A-with-rider): the commercial `listing` table has NO
+    // status column, so we can't filter sold/off-market by status. It IS actively refreshed
+    // (nothing older than ~90d today), so a created_at recency window keeps a future stale row
+    // from ever surfacing as an ACTIVE listing on a public broker profile. 120d = headroom over
+    // the current ~90d max; widen only if genuinely-active long-listed deals start dropping off.
+    // (A real fix needs a last_seen column on the commercial sweep — tracked as a follow-up.)
     rows(`SELECT bl.broker_id, l.address, l.city, l.zip, l.type, l.price, l.units, l.cap_rate, l.created_at AS listed_at
-            FROM broker_listing bl JOIN listing l ON l.id=bl.listing_id`),
+            FROM broker_listing bl JOIN listing l ON l.id=bl.listing_id
+           WHERE l.created_at > now() - interval '120 days'`),
     rows(`SELECT bc.broker_id, c.address, c.city, NULL::text zip, 'Condo' type, c.price,
                  NULL::int units, NULL::numeric cap_rate, NULL::timestamptz listed_at
             FROM broker_condo bc JOIN condo c ON c.id=bc.condo_id`),
@@ -91,7 +98,16 @@ async function main() {
     source: 'snapshot', exported_at: new Date().toISOString() };
   const dest = path.join(__dirname, '..', 'data', 'brokers-snapshot.json');
   fs.writeFileSync(dest, JSON.stringify(out));
-  console.log(`✔ wrote ${dest} — ${brokers.length} brokers (${(fs.statSync(dest).size/1e6).toFixed(1)}MB)`);
+  const mb = fs.statSync(dest).size / 1e6;
+  console.log(`✔ wrote ${dest} — ${brokers.length} brokers (${mb.toFixed(1)}MB)`);
+  // Size-ceiling guard (DTD 2026-08-19, A-with-rider item 4): readBrokerSnap() loads this ENTIRE file
+  // into the prod Node heap, so runaway growth (more brokers × per-broker books) degrades every CRCP
+  // endpoint. Emit a PASS/WARN/FAIL verdict (fleet-health vocabulary): WARN past 30MB, FAIL past 60MB.
+  // The forward fix past the ceiling is to slice brokerDetail into a lazy-loaded sidecar, not the
+  // always-loaded snapshot.
+  const verdict = mb > 60 ? 'FAIL' : mb > 30 ? 'WARN' : 'PASS';
+  if (verdict !== 'PASS') console.warn(`⚠️  ${verdict}: broker snapshot is ${mb.toFixed(1)}MB (>${mb > 60 ? 60 : 30}MB) — it loads into the prod Node heap; move brokerDetail to a lazy sidecar before it grows further.`);
+  console.log(`snapshot-health: ${verdict} size=${mb.toFixed(1)}MB brokers=${brokers.length}`);
   await pool.end();
 }
 if (require.main === module) main().catch(e => { console.error(e); process.exit(1); });

← 86a32ab Add SFV single-family pool-homes viewer (new crcp landing) · back to Commercialrealestate · CRCP: infinite scroll across all grid pages (replace hard ro 38e4820 →