asseeninmovies: GET /api/spotted/stats — JSON counts (pending/live/rejected/submitted_7d/films/brands/categories) consumed by status-loop and external dashboards; public, no auth

ae66c636416de57a4ba07d410c2b03acb31c764d · 2026-05-12 17:48:33 -0700 · SteveStudio2

Files touched

M server.js

Diff

commit ae66c636416de57a4ba07d410c2b03acb31c764d
Author: SteveStudio2 <stevestudio2@SteveStacStudio.lan>
Date:   Tue May 12 17:48:33 2026 -0700

    asseeninmovies: GET /api/spotted/stats — JSON counts (pending/live/rejected/submitted_7d/films/brands/categories) consumed by status-loop and external dashboards; public, no auth
---
 server.js | 30 ++++++++++++++++++++++++++++++
 1 file changed, 30 insertions(+)

diff --git a/server.js b/server.js
index 49b351f..8901be5 100644
--- a/server.js
+++ b/server.js
@@ -598,6 +598,36 @@ app.get('/api/spotted', async (req, res) => {
   }
 });
 
+// JSON stats — same numbers the /spotted gallery + /spotted/queue strips
+// render, exposed as a single small API. Useful for the status-loop tail
+// monitor and any external dashboard. No auth needed — public counts only.
+app.get('/api/spotted/stats', async (_req, res) => {
+  try {
+    const r = await pool.query(`
+      SELECT
+        (SELECT count(*) FROM asim_spotted WHERE verified_at IS NULL) AS pending_total,
+        (SELECT count(*) FROM asim_spotted WHERE verified_at IS NOT NULL AND (verified_by IS NULL OR verified_by NOT LIKE 'rejected%')) AS live_total,
+        (SELECT count(*) FROM asim_spotted WHERE verified_by LIKE 'rejected%') AS rejected_total,
+        (SELECT count(*) FROM asim_spotted WHERE created_at >= NOW() - INTERVAL '7 days') AS submitted_7d,
+        (SELECT count(DISTINCT movie_id) FROM asim_spotted WHERE verified_at IS NOT NULL AND (verified_by IS NULL OR verified_by NOT LIKE 'rejected%')) AS films,
+        (SELECT count(DISTINCT NULLIF(TRIM(brand),'')) FROM asim_spotted WHERE verified_at IS NOT NULL AND (verified_by IS NULL OR verified_by NOT LIKE 'rejected%')) AS brands,
+        (SELECT count(DISTINCT product_category) FROM asim_spotted WHERE verified_at IS NOT NULL AND (verified_by IS NULL OR verified_by NOT LIKE 'rejected%') AND product_category IS NOT NULL) AS categories`);
+    const row = r.rows[0] || {};
+    res.json({
+      pending_total: Number(row.pending_total || 0),
+      live_total:    Number(row.live_total || 0),
+      rejected_total:Number(row.rejected_total || 0),
+      submitted_7d:  Number(row.submitted_7d || 0),
+      films:         Number(row.films || 0),
+      brands:        Number(row.brands || 0),
+      categories:    Number(row.categories || 0),
+      as_of:         new Date().toISOString(),
+    });
+  } catch (e) {
+    res.status(500).json({ error: e.message });
+  }
+});
+
 // ────────────────────────────────────────────────────────────────────
 // /spotted/queue — admin moderation for pending user submissions.
 // Token-gated (ASIM_ADMIN_TOKEN env or ephemeral boot-token printed once).

← b7e1d47 asseeninmovies: /movies perf — 3 partial indexes (has_poster · back to AsSeenInMovies · asseeninmovies: /spotted — 'Most-Spotted Films' rail (top 6 992f471 →