iter 69: BTRC data freshness pill on /today.html — /api/data-freshness reads MAX(first_seen_at) for source LIKE '%btrc%' rows, computes days_since_ingest, classifies status as fresh (≤7d) / stale (≤30d) / very_stale / never; pill in /today.html header shows colored ✓/⚠/✕ icon + days-since label + BTRC row count, hover reveals last_btrc_ingest timestamp + source URL (data.lacity.org/6rrh-rzua) + recommended npm command (ingest:la_btrc); auto-refresh every 5 min; currently shows '✓ today · 17,238 rows' in green (last ingest 2026-05-06 15:14 UTC, fresh)

140c1a6906ae116acc8a05009782c2522656ebf4 · 2026-05-06 15:30:18 -0700 · SteveStudio2

Files touched

M public/today.html
M src/server/index.ts

Diff

commit 140c1a6906ae116acc8a05009782c2522656ebf4
Author: SteveStudio2 <steve@designerwallcoverings.com>
Date:   Wed May 6 15:30:18 2026 -0700

    iter 69: BTRC data freshness pill on /today.html — /api/data-freshness reads MAX(first_seen_at) for source LIKE '%btrc%' rows, computes days_since_ingest, classifies status as fresh (≤7d) / stale (≤30d) / very_stale / never; pill in /today.html header shows colored ✓/⚠/✕ icon + days-since label + BTRC row count, hover reveals last_btrc_ingest timestamp + source URL (data.lacity.org/6rrh-rzua) + recommended npm command (ingest:la_btrc); auto-refresh every 5 min; currently shows '✓ today · 17,238 rows' in green (last ingest 2026-05-06 15:14 UTC, fresh)
---
 public/today.html   | 21 ++++++++++++++++++++-
 src/server/index.ts | 35 +++++++++++++++++++++++++++++++++++
 2 files changed, 55 insertions(+), 1 deletion(-)

diff --git a/public/today.html b/public/today.html
index 689bdda..5229572 100644
--- a/public/today.html
+++ b/public/today.html
@@ -105,7 +105,7 @@
 <header>
   <div>
     <h1>Today · <em>DW pipeline</em></h1>
-    <div class="sub">your one-page action plan</div>
+    <div class="sub">your one-page action plan <span id="freshness-pill" style="margin-left:14px;font-size:9px;letter-spacing:.18em;padding:2px 8px;border:1px solid var(--rule);color:var(--ink-mute)" title="Click for ingest details">data: —</span></div>
   </div>
   <nav class="tabs">
     <a href="/">map</a>
@@ -517,6 +517,25 @@ async function loadPrioStrip() {
   }
 }
 loadPrioStrip();
+
+// ─── Data freshness pill (iter 69) ──────────────────────────────────
+async function loadFreshness() {
+  try {
+    const d = await fetch('/api/data-freshness').then(r => r.json());
+    const pill = document.getElementById('freshness-pill');
+    if (!pill) return;
+    const cls = ({ fresh: 'green', stale: 'metal', very_stale: 'red', never: 'red' })[d.status] || 'mute';
+    const color = ({ fresh: 'var(--green,#6a9b73)', stale: 'var(--metal)', very_stale: 'var(--red,#b66565)', never: 'var(--red,#b66565)' })[d.status];
+    const icon  = ({ fresh: '✓', stale: '⚠', very_stale: '⚠', never: '✕' })[d.status];
+    const days  = d.days_since_ingest;
+    pill.innerHTML = `data: <b style="color:${color};font-family:var(--mono);font-size:11px">${icon} ${days === null ? '—' : (days === 0 ? 'today' : days === 1 ? '1 day' : days + ' days')}</b> · ${(d.btrc_total || 0).toLocaleString()} BTRC rows`;
+    pill.style.borderColor = color || 'var(--rule)';
+    pill.style.color = color || 'var(--ink-mute)';
+    pill.title = `Last BTRC ingest: ${d.last_btrc_ingest || 'never'}\nSource: ${d.source_url}\nRefresh command: ${d.ingest_command}`;
+  } catch {}
+}
+loadFreshness();
+setInterval(loadFreshness, 5 * 60_000);
 </script>
 </body>
 </html>
diff --git a/src/server/index.ts b/src/server/index.ts
index b6b4b9b..ab47feb 100644
--- a/src/server/index.ts
+++ b/src/server/index.ts
@@ -68,6 +68,7 @@ const ADMIN_PATHS = [
   /^\/api\/followups\/?$/i,
   /^\/api\/snapshots\/?$/i,
   /^\/api\/activity\/?$/i,
+  /^\/api\/data-freshness\/?$/i,
   /^\/crawl-derby(\.html)?\/?$/i,
   /^\/api\/crawl(\/.*)?$/i,
   /^\/postcards(\.html)?\/?$/i,
@@ -1704,6 +1705,40 @@ app.get('/api/responses.csv', async (_req, res) => {
   }
 });
 
+// ─── Data-freshness signal: when was the last BTRC ingest, are we stale? ──
+app.get('/api/data-freshness', async (_req, res) => {
+  try {
+    const r = await query(`
+      SELECT
+        count(*) FILTER (WHERE source LIKE '%btrc%' OR source = 'la_btrc')                AS btrc_total,
+        MAX(first_seen_at) FILTER (WHERE source LIKE '%btrc%' OR source = 'la_btrc')      AS last_btrc_ingest,
+        count(*)                                                                          AS all_total,
+        MAX(last_seen_at)                                                                 AS last_any_seen
+      FROM businesses
+    `);
+    const row = r.rows[0];
+    const lastIngest = row.last_btrc_ingest ? new Date(row.last_btrc_ingest) : null;
+    const daysSince = lastIngest ? Math.floor((Date.now() - lastIngest.getTime()) / 86_400_000) : null;
+    let status: 'fresh' | 'stale' | 'very_stale' | 'never' = 'never';
+    if (daysSince !== null) {
+      if (daysSince <= 7)       status = 'fresh';
+      else if (daysSince <= 30) status = 'stale';
+      else                      status = 'very_stale';
+    }
+    res.json({
+      btrc_total: Number(row.btrc_total),
+      last_btrc_ingest: row.last_btrc_ingest,
+      days_since_ingest: daysSince,
+      status,
+      all_total: Number(row.all_total),
+      source_url: 'https://data.lacity.org/resource/6rrh-rzua.json',
+      ingest_command: 'npm run ingest:la_btrc'
+    });
+  } catch (e: any) {
+    res.status(500).json({ error: e.message });
+  }
+});
+
 // ─── Per-vertical conversion: which pitch_type replies/wins most ──────
 app.get('/api/responses/by-vertical', async (_req, res) => {
   try {

← 43adaa8 iter 68: per-vertical conversion stats — /api/responses/by-v · back to Ventura Corridor · iter 70: /pitches.html bulk-set-followup — extends iter 66 m d0c4c91 →