PERF-3: cache /api/marketplace/status (30s in-process TTL)

28303ddd97d7a9c4d097f81a307f9f41fae0ba8a · 2026-05-23 11:55:37 -0700 · Steve Abrams

src/marketplace/index.js:196 — the status endpoint runs 13 correlated
COUNT(*) subqueries, each scanning a different mp_* table. Logs showed
637ms–7642ms per call. With no cache, every dashboard refresh (and
every external monitor) hit the full block.

Fix: single-shot in-process cache. {ts, payload} singleton, 30s TTL,
no LRU needed because the SQL is a fixed string with no parameters.
Cached responses include `cached: true` and `age_ms` so clients can see
the freshness without a separate uncached endpoint.

ROI: at 1 hit/sec the cached path saves 13 COUNT(*) full-table scans
59 times per minute. At even modest traffic the table-scan budget
collapses by 99%+. For monitors/uptime checks that poll every 10s, the
DB hit rate drops to 2/min instead of 6/min.

Cache invalidation: TTL only. Acceptable because the dashboard tolerates
30s lag on count widgets, and pattern uploads / approvals don't need
real-time count reflection on this endpoint (the per-designer-dashboard
endpoint pulls live counts via a separate route).

Files touched

M src/marketplace/index.js

Diff

commit 28303ddd97d7a9c4d097f81a307f9f41fae0ba8a
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Sat May 23 11:55:37 2026 -0700

    PERF-3: cache /api/marketplace/status (30s in-process TTL)
    
    src/marketplace/index.js:196 — the status endpoint runs 13 correlated
    COUNT(*) subqueries, each scanning a different mp_* table. Logs showed
    637ms–7642ms per call. With no cache, every dashboard refresh (and
    every external monitor) hit the full block.
    
    Fix: single-shot in-process cache. {ts, payload} singleton, 30s TTL,
    no LRU needed because the SQL is a fixed string with no parameters.
    Cached responses include `cached: true` and `age_ms` so clients can see
    the freshness without a separate uncached endpoint.
    
    ROI: at 1 hit/sec the cached path saves 13 COUNT(*) full-table scans
    59 times per minute. At even modest traffic the table-scan budget
    collapses by 99%+. For monitors/uptime checks that poll every 10s, the
    DB hit rate drops to 2/min instead of 6/min.
    
    Cache invalidation: TTL only. Acceptable because the dashboard tolerates
    30s lag on count widgets, and pattern uploads / approvals don't need
    real-time count reflection on this endpoint (the per-designer-dashboard
    endpoint pulls live counts via a separate route).
---
 src/marketplace/index.js | 14 +++++++++++++-
 1 file changed, 13 insertions(+), 1 deletion(-)

diff --git a/src/marketplace/index.js b/src/marketplace/index.js
index b05c624..52ee79e 100644
--- a/src/marketplace/index.js
+++ b/src/marketplace/index.js
@@ -193,7 +193,17 @@ function mount(app) {
   });
 
   // ── public status & health
+  // PERF-3 (2026-05-23): in-process status cache. The 13-subquery COUNT() block
+  // scanned every mp_* table on every hit; logs showed 637-7642ms per call.
+  // Now memoized for 30s. Cache key is the SQL itself (fixed string), so a
+  // single shared {ts, db, counts} singleton is sufficient.
+  let __statusCache = { ts: 0, payload: null };
+  const __STATUS_TTL_MS = 30_000;
   app.get('/api/marketplace/status', async (_req, res) => {
+    const now = Date.now();
+    if (__statusCache.payload && (now - __statusCache.ts) < __STATUS_TTL_MS) {
+      return res.json({ ...__statusCache.payload, cached: true, age_ms: now - __statusCache.ts });
+    }
     try {
       const live = await ping();
       const counts = await one(`
@@ -212,7 +222,9 @@ function mount(app) {
           (SELECT COUNT(*) FROM mp_licensing_inquiries WHERE status='new') AS open_inquiries,
           (SELECT COUNT(*) FROM mp_takedown_requests WHERE status='open') AS open_takedowns
       `);
-      res.json({ ok: true, db: live, ts: new Date().toISOString(), counts });
+      const payload = { ok: true, db: live, ts: new Date().toISOString(), counts };
+      __statusCache = { ts: now, payload };
+      res.json(payload);
     } catch (err) {
       res.status(500).json({ ok: false, error: err.message });
     }

← 8f28f8f PERF-2: parallelize designer detail follow-up queries (4-awa · back to Wallco Ai · REFACTOR-1a: extract lib/db.js (psqlQuery, pgEsc, psqlExecLo 3b6954e →