← back to Ventura Corridor
feat(news): news-recency badge on /pitches.html name column
4035b3c842c3e4759a63efaf46a84b9cbe270c8c · 2026-05-07 21:42:40 -0700 · SteveStudio2
Editors doing outreach can now spot active publishers in their pitch
queue without leaving the table. Three-tier badge inline with the
business name:
fresh_7d >= 3 solid gold-glow "🔥3" link to /business/<id>/news
fresh_7d >= 1 gold outline "📰N" link
total >= 1 gray outline "📰N" link (had news but stale)
(no badge) no scraped news
API change:
/api/pitches +news_count, +news_fresh_7d, +last_news_at per pitch row
via single LATERAL aggregate from news_items
(one extra COUNT/GROUP per row, not per item)
UI change:
public/pitches.html row template badge appended to .nm cell
using the same tier scheme as the
map detail panel for consistency
Carries the editorial signal "what's this business saying right now"
into the place where Steve actually decides who to pitch tonight.
Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
Files touched
M public/pitches.htmlM src/server/index.ts
Diff
commit 4035b3c842c3e4759a63efaf46a84b9cbe270c8c
Author: SteveStudio2 <stevestudio2@SteveStacStudio.lan>
Date: Thu May 7 21:42:40 2026 -0700
feat(news): news-recency badge on /pitches.html name column
Editors doing outreach can now spot active publishers in their pitch
queue without leaving the table. Three-tier badge inline with the
business name:
fresh_7d >= 3 solid gold-glow "🔥3" link to /business/<id>/news
fresh_7d >= 1 gold outline "📰N" link
total >= 1 gray outline "📰N" link (had news but stale)
(no badge) no scraped news
API change:
/api/pitches +news_count, +news_fresh_7d, +last_news_at per pitch row
via single LATERAL aggregate from news_items
(one extra COUNT/GROUP per row, not per item)
UI change:
public/pitches.html row template badge appended to .nm cell
using the same tier scheme as the
map detail panel for consistency
Carries the editorial signal "what's this business saying right now"
into the place where Steve actually decides who to pitch tonight.
Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
---
public/pitches.html | 9 ++++++++-
src/server/index.ts | 11 ++++++++++-
2 files changed, 18 insertions(+), 2 deletions(-)
diff --git a/public/pitches.html b/public/pitches.html
index 3eb8d56..a6aec5c 100644
--- a/public/pitches.html
+++ b/public/pitches.html
@@ -313,7 +313,14 @@ function render() {
<td style="color:${proxColor};font-size:14px;text-align:center" title="${r.dw_proximity || ''}">${proxIcon}</td>
<td><span class="stag s-${r.status}">${r.status}</span></td>
<td><span class="ptag t-${r.pitch_type}">${r.pitch_type}</span></td>
- <td class="nm">${escapeHtml(r.name)}</td>
+ <td class="nm">${escapeHtml(r.name)}${(() => {
+ const fresh = Number(r.news_fresh_7d || 0);
+ const total = Number(r.news_count || 0);
+ if (fresh >= 3) return ` <a href="/business/${r.business_id}/news" style="display:inline-block;padding:1px 5px;font-size:8px;letter-spacing:.16em;text-transform:uppercase;background:var(--metal-glow);color:#0a0a0c;text-decoration:none;border-radius:2px;vertical-align:middle" title="${fresh} articles in last 7 days — Hot publisher">🔥${fresh}</a>`;
+ if (fresh >= 1) return ` <a href="/business/${r.business_id}/news" style="display:inline-block;padding:1px 5px;font-size:8px;letter-spacing:.16em;text-transform:uppercase;color:var(--metal);border:1px solid var(--metal);text-decoration:none;vertical-align:middle" title="${fresh} articles in last 7 days">📰${fresh}</a>`;
+ if (total >= 1) return ` <a href="/business/${r.business_id}/news" style="display:inline-block;padding:1px 5px;font-size:8px;letter-spacing:.16em;text-transform:uppercase;color:var(--ink-mute);border:1px solid var(--rule);text-decoration:none;vertical-align:middle" title="${total} articles total">📰${total}</a>`;
+ return '';
+ })()}</td>
<td class="addr">${escapeHtml(r.address || '')}</td>
<td class="addr">${escapeHtml(r.city || '')}</td>
<td style="color:${dotColor};font-family:var(--mono);font-size:13px;letter-spacing:0.1em" title="contact·email·phone·observation·body — ${filled}/5">${dots}</td>
diff --git a/src/server/index.ts b/src/server/index.ts
index 82e22f0..71e21e5 100644
--- a/src/server/index.ts
+++ b/src/server/index.ts
@@ -575,9 +575,18 @@ app.get('/api/pitches', async (req, res) => {
p.reply_text, p.reply_channel, p.won_value_usd, p.lost_reason,
p.next_followup_at, p.followup_notes,
b.name, b.address, b.city, b.zip,
- b.raw->>'primary_naics_description' AS naics
+ b.raw->>'primary_naics_description' AS naics,
+ COALESCE(n.news_count, 0)::int AS news_count,
+ COALESCE(n.fresh_7d, 0)::int AS news_fresh_7d,
+ n.last_news_at
FROM pitches p
JOIN businesses b ON b.id = p.business_id
+ LEFT JOIN LATERAL (
+ SELECT COUNT(*) AS news_count,
+ COUNT(*) FILTER (WHERE fetched_at > now() - interval '7 days') AS fresh_7d,
+ MAX(fetched_at) AS last_news_at
+ FROM news_items WHERE business_id = b.id
+ ) n ON TRUE
${where.length ? 'WHERE ' + where.join(' AND ') : ''}
ORDER BY p.priority ASC, b.name ASC
LIMIT $${params.length}
← ebcb5c8 feat(news): /api/news/archive + /news/archive.html weekly hi
·
back to Ventura Corridor
·
feat(news): /api/news/by-day + 90-day calendar heatmap on /c f9ee539 →