starsofdesign: GET /api/stats — JSON counts (designers/firms/claimed/links/likes/comments/candidates) with shared module-level pool; mirrors asim /api/spotted/stats pattern; consumed by status-loop + external dashboards

728ecab21839893e045290aeab5f9e757a2776fb · 2026-05-12 18:09:29 -0700 · Steve Abrams

Files touched

M routes/public.js

Diff

commit 728ecab21839893e045290aeab5f9e757a2776fb
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Tue May 12 18:09:29 2026 -0700

    starsofdesign: GET /api/stats — JSON counts (designers/firms/claimed/links/likes/comments/candidates) with shared module-level pool; mirrors asim /api/spotted/stats pattern; consumed by status-loop + external dashboards
---
 routes/public.js | 40 ++++++++++++++++++++++++++++++++++++++++
 1 file changed, 40 insertions(+)

diff --git a/routes/public.js b/routes/public.js
index 86ea5a7..f7daf2c 100644
--- a/routes/public.js
+++ b/routes/public.js
@@ -1,4 +1,5 @@
 const express = require('express');
+const { Pool } = require('pg');
 const router = express.Router();
 
 const data = require('../lib/data');
@@ -7,6 +8,13 @@ const firmsLib = require('../lib/firms');
 const feedLib = require('../lib/feed');
 const { sortDesigners, SORTS } = require('../lib/sort');
 
+// Shared pool just for the small JSON stats endpoint — the lib/* modules
+// each carry their own pool and we don't want to leak handles per request.
+const statsPool = new Pool({
+  connectionString: process.env.DATABASE_URL || 'postgresql:///dw_unified?host=/tmp&user=stevestudio2',
+  max: 2,
+});
+
 const SITE_TITLE = 'Stars of Design';
 const META_DESC = 'Stars of Design — a curated profile directory of the world\'s most influential interior designers, decade by decade, with the wallcoverings that match each designer\'s signature style.';
 
@@ -180,6 +188,38 @@ router.get('/api/clients/:id/comments', async (req, res, next) => {
   } catch (e) { next(e); }
 });
 
+// JSON stats — counts surfaced by the homepage + cross-consumed by status-loop
+// and external dashboards. Public, no auth, returns DW-client tier only
+// (excluding Manufacturer Reps which are gated out everywhere else too).
+router.get('/api/stats', async (_req, res, next) => {
+  try {
+    const r = await statsPool.query(`
+      SELECT
+        (SELECT count(*) FROM sod_designers WHERE role IS NULL OR role <> 'Manufacturer Rep') AS designers,
+        (SELECT count(*) FROM sod_designers WHERE claimed_at IS NOT NULL AND (role IS NULL OR role <> 'Manufacturer Rep')) AS designers_claimed,
+        (SELECT count(*) FROM sod_firms) AS firms,
+        (SELECT count(*) FROM sod_firms WHERE claimed_at IS NOT NULL) AS firms_claimed,
+        (SELECT count(*) FROM sod_links) AS links,
+        (SELECT count(*) FROM sod_likes) AS likes,
+        (SELECT count(*) FROM sod_comments WHERE is_hidden = false) AS comments,
+        (SELECT count(*) FROM sod_email_candidates) AS candidates_total,
+        (SELECT count(*) FROM sod_email_candidates WHERE status = 'new') AS candidates_unreviewed`);
+    const row = r.rows[0] || {};
+    res.json({
+      designers:           Number(row.designers || 0),
+      designers_claimed:   Number(row.designers_claimed || 0),
+      firms:               Number(row.firms || 0),
+      firms_claimed:       Number(row.firms_claimed || 0),
+      links:               Number(row.links || 0),
+      likes:               Number(row.likes || 0),
+      comments:            Number(row.comments || 0),
+      candidates_total:    Number(row.candidates_total || 0),
+      candidates_unreviewed:Number(row.candidates_unreviewed || 0),
+      as_of:               new Date().toISOString(),
+    });
+  } catch (e) { next(e); }
+});
+
 router.post('/api/clients/:id/like', async (req, res, next) => {
   try {
     const id = parseInt(req.params.id, 10);

← 346478c starsofdesign: seed data/vendor-domains.txt (~80 lines) — ve · back to Stars of Design · starsofdesign: GET /favicon.ico fallback — returns public/fa f17b258 →