← back to Ventura Claw Leads
yolo tick 10: home-page social proof + /admin/leads date-range filter
f3a4936b9b6c028dc1f55d8e2f132789ea5565bf · 2026-05-06 19:13:04 -0700 · Steve Abrams
HOME-PAGE SOCIAL PROOF
- routes/public.js GET /: stats query now also pulls messages_30d (count of
business_interest rows in the last 30 days, scoped to active businesses
only via the join-free subquery) and claimed_count (how many active
businesses have been claimed by their owners).
- views/public/home.ejs: hero-meta strip adds two more stat lines, but only
when their value is > 0 — a fresh deployment with zero messages doesn't
show 'X messages routed' (which would look bad). Pure social proof for
visitors weighing whether to message a business.
LEADS DATE-RANGE FILTER
- routes/admin.js GET /admin/leads: added ?range=7|30|90|all (whitelist
with explicit value coercion, avoids any SQL injection vector). Combines
freely with ?filter=unread. Counts query expanded to also produce
last_7 / last_30 / last_90 totals so the filter chips can show counts.
- views/admin/leads.ejs: 4 new range chips (7d / 30d / 90d / All time)
separated from the All/Unread chips by a vertical divider. Both filter
axes preserved when switching the other (rangeHref/filterHref helpers
build the URL from current state).
46/46 tests still pass. Both surfaces verified live on prod.
Files touched
M routes/admin.jsM routes/public.jsM views/admin/leads.ejsM views/public/home.ejs
Diff
commit f3a4936b9b6c028dc1f55d8e2f132789ea5565bf
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Wed May 6 19:13:04 2026 -0700
yolo tick 10: home-page social proof + /admin/leads date-range filter
HOME-PAGE SOCIAL PROOF
- routes/public.js GET /: stats query now also pulls messages_30d (count of
business_interest rows in the last 30 days, scoped to active businesses
only via the join-free subquery) and claimed_count (how many active
businesses have been claimed by their owners).
- views/public/home.ejs: hero-meta strip adds two more stat lines, but only
when their value is > 0 — a fresh deployment with zero messages doesn't
show 'X messages routed' (which would look bad). Pure social proof for
visitors weighing whether to message a business.
LEADS DATE-RANGE FILTER
- routes/admin.js GET /admin/leads: added ?range=7|30|90|all (whitelist
with explicit value coercion, avoids any SQL injection vector). Combines
freely with ?filter=unread. Counts query expanded to also produce
last_7 / last_30 / last_90 totals so the filter chips can show counts.
- views/admin/leads.ejs: 4 new range chips (7d / 30d / 90d / All time)
separated from the All/Unread chips by a vertical divider. Both filter
axes preserved when switching the other (rangeHref/filterHref helpers
build the URL from current state).
46/46 tests still pass. Both surfaces verified live on prod.
---
routes/admin.js | 14 ++++++++++++--
routes/public.js | 6 +++++-
views/admin/leads.ejs | 25 +++++++++++++++++++++++--
views/public/home.ejs | 6 ++++++
4 files changed, 46 insertions(+), 5 deletions(-)
diff --git a/routes/admin.js b/routes/admin.js
index e6980c2..7081ae2 100644
--- a/routes/admin.js
+++ b/routes/admin.js
@@ -82,8 +82,14 @@ router.get('/leads', async (req, res, next) => {
try {
const bizId = res.locals.currentBusiness.id;
const filter = (req.query.filter === 'unread') ? 'unread' : 'all';
+ const rangeWhitelist = { '7': 7, '30': 30, '90': 90, 'all': null };
+ const rangeKey = String(req.query.range || 'all');
+ const rangeDays = rangeWhitelist[rangeKey] !== undefined ? rangeWhitelist[rangeKey] : null;
+
const where = ['business_id = $1'];
if (filter === 'unread') where.push('read_at IS NULL');
+ if (rangeDays) where.push(`created_at > now() - interval '${rangeDays} days'`);
+
const leads = await db.many(`
SELECT id, consumer_name, consumer_email, consumer_phone, message, zip, source,
created_at, delivered_at, delivery_email, delivery_msg_id, bill_amount_cents,
@@ -94,10 +100,14 @@ router.get('/leads', async (req, res, next) => {
LIMIT 200
`, [bizId]);
const counts = await db.one(`
- SELECT COUNT(*)::int AS total, COUNT(*) FILTER (WHERE read_at IS NULL)::int AS unread
+ SELECT COUNT(*)::int AS total,
+ COUNT(*) FILTER (WHERE read_at IS NULL)::int AS unread,
+ COUNT(*) FILTER (WHERE created_at > now() - interval '7 days')::int AS last_7,
+ COUNT(*) FILTER (WHERE created_at > now() - interval '30 days')::int AS last_30,
+ COUNT(*) FILTER (WHERE created_at > now() - interval '90 days')::int AS last_90
FROM business_interest WHERE business_id = $1
`, [bizId]);
- res.render('admin/leads', { title: 'Leads · Ventura Claw', leads, filter, counts });
+ res.render('admin/leads', { title: 'Leads · Ventura Claw', leads, filter, counts, range: rangeKey });
} catch (err) { next(err); }
});
diff --git a/routes/public.js b/routes/public.js
index afbe1ea..a96fcdb 100644
--- a/routes/public.js
+++ b/routes/public.js
@@ -9,7 +9,11 @@ router.get('/', async (req, res, next) => {
const stats = await db.one(`
SELECT COUNT(*)::int AS total,
COUNT(DISTINCT vertical)::int AS vertical_count,
- COUNT(DISTINCT city)::int AS city_count
+ COUNT(DISTINCT city)::int AS city_count,
+ (SELECT COUNT(*)::int FROM business_interest
+ WHERE created_at > now() - interval '30 days') AS messages_30d,
+ (SELECT COUNT(*)::int FROM businesses
+ WHERE status = 'active' AND claim_status IN ('self','claimed')) AS claimed_count
FROM businesses WHERE status = 'active'
`);
const featured = await db.many(`
diff --git a/views/admin/leads.ejs b/views/admin/leads.ejs
index 24313fc..8208cc0 100644
--- a/views/admin/leads.ejs
+++ b/views/admin/leads.ejs
@@ -6,9 +6,30 @@
<h1 class="display-sm">Inbox</h1>
<p class="lede">Every customer message routed to <%= currentBusiness.business_name %>. Replies via your email client go straight to the customer.</p>
+ <%
+ function rangeHref(r) {
+ var p = new URLSearchParams();
+ if (filter === 'unread') p.set('filter', 'unread');
+ if (r !== 'all') p.set('range', r);
+ var s = p.toString();
+ return '/admin/leads' + (s ? '?' + s : '');
+ }
+ function filterHref(f) {
+ var p = new URLSearchParams();
+ if (f === 'unread') p.set('filter', 'unread');
+ if (range !== 'all') p.set('range', range);
+ var s = p.toString();
+ return '/admin/leads' + (s ? '?' + s : '');
+ }
+ %>
<div style="display:flex;gap:8px;flex-wrap:wrap;align-items:center;margin-top:12px">
- <a href="/admin/leads" class="btn btn-ghost btn-sm" style="<%= filter === 'all' ? 'border-color:var(--fg)' : '' %>">All <span class="muted" style="font-size:11px">(<%= counts.total %>)</span></a>
- <a href="/admin/leads?filter=unread" class="btn btn-ghost btn-sm" style="<%= filter === 'unread' ? 'border-color:var(--brass);color:var(--brass)' : '' %>">Unread <span class="muted" style="font-size:11px">(<%= counts.unread %>)</span></a>
+ <a href="<%= filterHref('all') %>" class="btn btn-ghost btn-sm" style="<%= filter === 'all' ? 'border-color:var(--fg)' : '' %>">All <span class="muted" style="font-size:11px">(<%= counts.total %>)</span></a>
+ <a href="<%= filterHref('unread') %>" class="btn btn-ghost btn-sm" style="<%= filter === 'unread' ? 'border-color:var(--brass);color:var(--brass)' : '' %>">Unread <span class="muted" style="font-size:11px">(<%= counts.unread %>)</span></a>
+ <span style="border-left:1px solid var(--border);height:24px;margin:0 4px"></span>
+ <a href="<%= rangeHref('7') %>" class="btn btn-ghost btn-sm" style="<%= range === '7' ? 'border-color:var(--fg)' : '' %>">7d <span class="muted" style="font-size:11px">(<%= counts.last_7 %>)</span></a>
+ <a href="<%= rangeHref('30') %>" class="btn btn-ghost btn-sm" style="<%= range === '30' ? 'border-color:var(--fg)' : '' %>">30d <span class="muted" style="font-size:11px">(<%= counts.last_30 %>)</span></a>
+ <a href="<%= rangeHref('90') %>" class="btn btn-ghost btn-sm" style="<%= range === '90' ? 'border-color:var(--fg)' : '' %>">90d <span class="muted" style="font-size:11px">(<%= counts.last_90 %>)</span></a>
+ <a href="<%= rangeHref('all') %>" class="btn btn-ghost btn-sm" style="<%= range === 'all' ? 'border-color:var(--fg)' : '' %>">All time</a>
<a href="/admin/leads.csv" class="btn btn-ghost btn-sm" style="margin-left:auto" download>Export CSV ↓</a>
<% if (counts.unread > 0) { %>
<form method="post" action="/admin/leads/mark-all-read" style="margin:0">
diff --git a/views/public/home.ejs b/views/public/home.ejs
index d72d232..f1f31dd 100644
--- a/views/public/home.ejs
+++ b/views/public/home.ejs
@@ -13,6 +13,12 @@
<span><strong><%= stats.total %></strong> businesses</span>
<span><strong><%= stats.vertical_count %></strong> categories</span>
<span><strong><%= stats.city_count %></strong> neighborhoods</span>
+ <% if (stats.messages_30d > 0) { %>
+ <span><strong><%= stats.messages_30d %></strong> messages routed in the last 30 days</span>
+ <% } %>
+ <% if (stats.claimed_count > 0) { %>
+ <span><strong><%= stats.claimed_count %></strong> claimed by their owners</span>
+ <% } %>
</div>
</section>
← 0d288c7 yolo tick 9: lead read/unread tracking + dashboard unread co
·
back to Ventura Claw Leads
·
yolo tick 11: admin invite-teammate flow — multi-user per bu 2c5302b →