iter 71: stale-pitch alerts on /today.html — /api/stale-pitches?days=&limit= returns pitches sent >N days ago with no reply, no close, status NOT IN (skip/won/lost), sorted oldest-first; days_silent computed inline; /today.html grows a 'Cold leads · re-touch or close' card (red-tinted background) below the priority strip, auto-hides when empty, shows N most-stale rows with days-silent count + biz name + pitch_type + channel + sent date + log→ shortcut to /responses.html?id=; auto-refresh every 5 min; threshold 14d default; closes the loop after iter 70's bulk-followup — Steve sees both 'who hasn't replied yet' and one-click follow-up scheduling on the same page
Files touched
M public/today.htmlM src/server/index.ts
Diff
commit 1482b8fa89de73c8d079de2d99e9d30b04eddc96
Author: SteveStudio2 <steve@designerwallcoverings.com>
Date: Wed May 6 15:52:15 2026 -0700
iter 71: stale-pitch alerts on /today.html — /api/stale-pitches?days=&limit= returns pitches sent >N days ago with no reply, no close, status NOT IN (skip/won/lost), sorted oldest-first; days_silent computed inline; /today.html grows a 'Cold leads · re-touch or close' card (red-tinted background) below the priority strip, auto-hides when empty, shows N most-stale rows with days-silent count + biz name + pitch_type + channel + sent date + log→ shortcut to /responses.html?id=; auto-refresh every 5 min; threshold 14d default; closes the loop after iter 70's bulk-followup — Steve sees both 'who hasn't replied yet' and one-click follow-up scheduling on the same page
---
public/today.html | 32 ++++++++++++++++++++++++++++++++
src/server/index.ts | 29 +++++++++++++++++++++++++++++
2 files changed, 61 insertions(+)
diff --git a/public/today.html b/public/today.html
index 5229572..2722e59 100644
--- a/public/today.html
+++ b/public/today.html
@@ -143,6 +143,14 @@
<div id="prio-strip" style="display:grid;grid-template-columns:repeat(auto-fit,minmax(200px,1fr));gap:8px"></div>
</section>
+<section id="stale-section" style="display:none;padding:20px 32px;border-bottom:1px solid var(--rule);background:rgba(182,101,101,0.05)">
+ <div style="display:flex;justify-content:space-between;align-items:baseline;margin-bottom:12px;flex-wrap:wrap;gap:10px">
+ <h3 style="font-family:var(--serif);font-style:italic;font-weight:400;font-size:22px;color:#c47b7b;margin:0">Cold leads · re-touch or close</h3>
+ <span id="stale-summary" style="font-size:10px;letter-spacing:.18em;text-transform:uppercase;color:var(--ink-mute)">—</span>
+ </div>
+ <div id="stale-list" style="display:flex;flex-direction:column;gap:6px"></div>
+</section>
+
<section class="trend" style="padding:24px 32px;border-bottom:1px solid var(--rule);background:var(--noir-rise)">
<div style="display:flex;justify-content:space-between;align-items:baseline;margin-bottom:16px;flex-wrap:wrap;gap:12px">
<h3 style="font-family:var(--serif);font-style:italic;font-weight:400;font-size:22px;color:var(--metal);margin:0">14-day corridor velocity</h3>
@@ -536,6 +544,30 @@ async function loadFreshness() {
}
loadFreshness();
setInterval(loadFreshness, 5 * 60_000);
+
+// ─── Stale pitches (iter 71) ────────────────────────────────────────
+async function loadStale() {
+ try {
+ const data = await fetch('/api/stale-pitches?days=14&limit=10').then(r => r.json());
+ const rows = data.rows || [];
+ const sect = document.getElementById('stale-section');
+ if (!rows.length) { sect.style.display = 'none'; return; }
+ sect.style.display = 'block';
+ document.getElementById('stale-summary').innerHTML =
+ `<b style="color:#c47b7b;font-family:var(--mono)">${rows.length}</b> sent ≥${data.days_threshold}d ago, no reply yet`;
+ document.getElementById('stale-list').innerHTML = rows.map(r => `
+ <a href="/responses.html?id=${r.id}" target="_blank" style="display:grid;grid-template-columns:auto 1fr auto auto auto;gap:14px;align-items:center;padding:8px 12px;background:var(--noir);border:1px solid var(--rule);text-decoration:none;color:var(--ink);font-size:12px">
+ <span style="font-family:var(--mono);font-size:11px;color:#c47b7b;font-weight:500;min-width:50px">${r.days_silent}d</span>
+ <span><b style="font-family:var(--serif);font-size:14px;color:var(--metal-glow)">${escHtml(r.name)}</b> <span style="color:var(--ink-mute);margin-left:6px">${escHtml(r.pitch_type || '')}</span></span>
+ <span style="color:var(--ink-mute);font-size:10px;letter-spacing:.12em;text-transform:uppercase">${escHtml(r.outreach_channel || '—')}</span>
+ <span style="color:var(--ink-mute);font-family:var(--mono);font-size:10px">sent ${new Date(r.sent_at).toLocaleDateString()}</span>
+ <span style="color:var(--metal);font-size:10px;letter-spacing:.18em;text-transform:uppercase">log →</span>
+ </a>
+ `).join('');
+ } catch {}
+}
+loadStale();
+setInterval(loadStale, 5 * 60_000);
</script>
</body>
</html>
diff --git a/src/server/index.ts b/src/server/index.ts
index 72e38a0..6259a89 100644
--- a/src/server/index.ts
+++ b/src/server/index.ts
@@ -69,6 +69,7 @@ const ADMIN_PATHS = [
/^\/api\/snapshots\/?$/i,
/^\/api\/activity\/?$/i,
/^\/api\/data-freshness\/?$/i,
+ /^\/api\/stale-pitches\/?$/i,
/^\/crawl-derby(\.html)?\/?$/i,
/^\/api\/crawl(\/.*)?$/i,
/^\/postcards(\.html)?\/?$/i,
@@ -1739,6 +1740,34 @@ app.get('/api/data-freshness', async (_req, res) => {
}
});
+// ─── Stale pitches: sent >N days ago, no reply, not closed ─────────────
+// Powers the "Cold leads" card on /today.html.
+app.get('/api/stale-pitches', async (req, res) => {
+ try {
+ const days = Math.max(parseInt(String(req.query.days ?? '14'), 10) || 14, 1);
+ const limit = Math.min(parseInt(String(req.query.limit ?? '15'), 10) || 15, 100);
+ const r = await query(
+ `SELECT p.id, p.pitch_type, p.priority, p.status, p.outreach_channel,
+ p.sent_at, p.next_followup_at, p.contact_name, p.email, p.phone, p.linkedin,
+ b.name, b.address, b.city, b.zip,
+ FLOOR(EXTRACT(EPOCH FROM (NOW() - p.sent_at))/86400)::int AS days_silent
+ FROM pitches p
+ JOIN businesses b ON b.id = p.business_id
+ WHERE p.sent_at IS NOT NULL
+ AND p.sent_at < NOW() - ($1 || ' days')::interval
+ AND p.replied_at IS NULL
+ AND p.closed_at IS NULL
+ AND p.status NOT IN ('skip','won','lost')
+ ORDER BY p.sent_at ASC
+ LIMIT $2`,
+ [days, limit]
+ );
+ res.json({ count: r.rowCount, days_threshold: days, rows: r.rows });
+ } 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 {