condos/stats + crcp/stats: re-aggregate warrantability with listingEffectiveWarrant (Cody FIX-FIRST)

3275554cd97f2a3b774f5fee23dde87da61958c4 · 2026-07-31 14:42:36 -0700 · Steve

Cody gate on 8607626 found the listing-level frozen count feeds TWO aggregate
surfaces I left frozen: /api/condos/stats (graphics 'real listings by
warrantability' doughnut) and /api/crcp/stats condosByStatus (dashboard tile
439 + the unwarrantable/non-QM count derived at crcp.html 477+533). Cards showed
27, aggregates 28 — the exact two-sources-two-counts divergence. Both now
re-aggregate per-row via listingEffectiveWarrant (byStatus + byCity approved).
Verified live (clean throwaway port): condos/stats + crcp/stats + cards all
agree fha_approved 27 / fha_expired 158.

Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>

Files touched

M scripts/serve.js

Diff

commit 3275554cd97f2a3b774f5fee23dde87da61958c4
Author: Steve <steve@designerwallcoverings.com>
Date:   Fri Jul 31 14:42:36 2026 -0700

    condos/stats + crcp/stats: re-aggregate warrantability with listingEffectiveWarrant (Cody FIX-FIRST)
    
    Cody gate on 8607626 found the listing-level frozen count feeds TWO aggregate
    surfaces I left frozen: /api/condos/stats (graphics 'real listings by
    warrantability' doughnut) and /api/crcp/stats condosByStatus (dashboard tile
    439 + the unwarrantable/non-QM count derived at crcp.html 477+533). Cards showed
    27, aggregates 28 — the exact two-sources-two-counts divergence. Both now
    re-aggregate per-row via listingEffectiveWarrant (byStatus + byCity approved).
    Verified live (clean throwaway port): condos/stats + crcp/stats + cards all
    agree fha_approved 27 / fha_expired 158.
    
    Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
 scripts/serve.js | 20 +++++++++++++-------
 1 file changed, 13 insertions(+), 7 deletions(-)

diff --git a/scripts/serve.js b/scripts/serve.js
index db50561..cb83a47 100644
--- a/scripts/serve.js
+++ b/scripts/serve.js
@@ -724,13 +724,17 @@ app.get('/api/residential', async (req, res) => {
 app.get('/api/condos/stats', async (req, res) => {
   if (!brokerdb) return res.json({ byStatus: {}, byCity: [], total: 0, unavailable: true });
   try {
+    // Re-derive warrantable_status per row (lapsed FHA certs → expired) so these aggregates match
+    // the /api/condos card badges — both use listingEffectiveWarrant. A frozen GROUP BY would keep
+    // reporting a lapsed cert as approved, diverging from the cards (Cody: two sources, two counts).
+    const today = new Date(); today.setHours(0, 0, 0, 0);
     const byStatus = {};
-    (await brokerdb.pool.query(`SELECT warrantable_status s, count(*)::int n FROM condo GROUP BY 1`)).rows
-      .forEach(r => byStatus[r.s] = r.n);
-    const byCity = (await brokerdb.pool.query(
-      `SELECT city, count(*)::int n,
-              count(*) FILTER (WHERE warrantable_status='fha_approved')::int approved
-         FROM condo WHERE city <> '' GROUP BY city ORDER BY n DESC LIMIT 14`)).rows;
+    (await brokerdb.pool.query(`SELECT warrantable_status, warrant_source FROM condo`)).rows
+      .forEach(r => { const s = listingEffectiveWarrant(r, today); byStatus[s] = (byStatus[s] || 0) + 1; });
+    const cityMap = {};
+    (await brokerdb.pool.query(`SELECT city, warrantable_status, warrant_source FROM condo WHERE city <> ''`)).rows
+      .forEach(r => { const c = cityMap[r.city] || (cityMap[r.city] = { n: 0, approved: 0 }); c.n++; if (listingEffectiveWarrant(r, today) === 'fha_approved') c.approved++; });
+    const byCity = Object.entries(cityMap).map(([city, v]) => ({ city, n: v.n, approved: v.approved })).sort((a, b) => b.n - a.n).slice(0, 14);
     const price = (await brokerdb.pool.query(
       `SELECT warrantable_status s, round(avg(price))::bigint avg_price, count(*)::int n
          FROM condo WHERE price>0 GROUP BY 1`)).rows;
@@ -866,7 +870,9 @@ app.get('/api/crcp/stats', async (req, res) => {
       Object.assign(out, c1);
       try { out.brokers_web = (await q(`SELECT count(*)::int n FROM broker WHERE website IS NOT NULL`))[0].n; } catch (_) { out.brokers_web = null; }
       try { out.condos = (await q(`SELECT count(*)::int n FROM condo`))[0].n;
-            out.condosByStatus = {}; (await q(`SELECT warrantable_status s,count(*)::int n FROM condo GROUP BY 1`)).forEach(r => out.condosByStatus[r.s] = r.n); } catch (_) { out.condos = 0; }
+            const cbsToday = new Date(); cbsToday.setHours(0, 0, 0, 0); out.condosByStatus = {};
+            // re-derive lapsed certs so the dashboard tile + unwarrantable-count match the cards
+            (await q(`SELECT warrantable_status, warrant_source FROM condo`)).forEach(r => { const s = listingEffectiveWarrant(r, cbsToday); out.condosByStatus[s] = (out.condosByStatus[s] || 0) + 1; }); } catch (_) { out.condos = 0; }
       try { out.sfr = (await q(`SELECT count(*)::int n FROM sfr`))[0].n; } catch (_) { out.sfr = 0; }
       const brokerCols = 'b.id, b.name, f.name firm, b.agent_type, ' +
         '(count(DISTINCT bl.listing_id) + count(DISTINCT bc.condo_id))::int listings, b.total_assets, b.phone, b.email' +

← 8607626 condos: re-derive listing-level warrantability from embedded · back to Commercialrealestate · crcp: doctrine link-scrub — never link CREXi/aggregators; li ff6c52b →