← back to Ventura Claw Leads
yolo tick 21: per-business profile-view counter (with bot+session+owner dedup)
eb723c7b1871cc3ea3ef5c9f9b9c396e0a90fbb5 · 2026-05-06 23:22:32 -0700 · Steve Abrams
DB:
- migrations/009_business_views.sql: business_views (business_id, day, n)
with PK on (business_id, day) so daily upsert is atomic. Index on
(business_id, day DESC) for the dashboard 30-day SUM.
- POST-DEPLOY GRANT (vcl role on prod, table owned by postgres):
GRANT ALL ON business_views TO vcl;
Counter logic in routes/public.js GET /business/:slug:
- Skip if user-agent matches /bot|crawler|spider|slurp|preview|fetch|
monitor|pingdom|uptime|headlesschrome/
- Skip if logged-in business owner viewing own profile
- Otherwise: 30-min per-session dedup via req.session.viewed_<bizId>,
then INSERT … ON CONFLICT (business_id, day) DO UPDATE SET n = n+1
- Fire-and-forget; render doesn't block on the counter write
Surfaces:
- Admin dashboard: new 'Profile views' stat card (last 30 days · today)
in the 5-card row alongside Total/30d/Delivered/Unread.
- Public profile sidebar: when activity threshold met (≥3 msgs OR ≥25 views
in 30 days), brass-bordered side card shows BOTH stats stacked, divided
by a dotted line. Below threshold: nothing rendered (no '0 views' embarrassment).
E2E verified all four scenarios:
fresh session → n=1 ✓
re-hit same session → n=1 (dedup) ✓
Googlebot UA → n=1 (bot skipped) ✓
another fresh session → n=2 ✓
46/46 tests still pass.
Files touched
A db/migrations/009_business_views.sqlM routes/admin.jsM routes/public.jsM views/admin/dashboard.ejsM views/public/business.ejs
Diff
commit eb723c7b1871cc3ea3ef5c9f9b9c396e0a90fbb5
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Wed May 6 23:22:32 2026 -0700
yolo tick 21: per-business profile-view counter (with bot+session+owner dedup)
DB:
- migrations/009_business_views.sql: business_views (business_id, day, n)
with PK on (business_id, day) so daily upsert is atomic. Index on
(business_id, day DESC) for the dashboard 30-day SUM.
- POST-DEPLOY GRANT (vcl role on prod, table owned by postgres):
GRANT ALL ON business_views TO vcl;
Counter logic in routes/public.js GET /business/:slug:
- Skip if user-agent matches /bot|crawler|spider|slurp|preview|fetch|
monitor|pingdom|uptime|headlesschrome/
- Skip if logged-in business owner viewing own profile
- Otherwise: 30-min per-session dedup via req.session.viewed_<bizId>,
then INSERT … ON CONFLICT (business_id, day) DO UPDATE SET n = n+1
- Fire-and-forget; render doesn't block on the counter write
Surfaces:
- Admin dashboard: new 'Profile views' stat card (last 30 days · today)
in the 5-card row alongside Total/30d/Delivered/Unread.
- Public profile sidebar: when activity threshold met (≥3 msgs OR ≥25 views
in 30 days), brass-bordered side card shows BOTH stats stacked, divided
by a dotted line. Below threshold: nothing rendered (no '0 views' embarrassment).
E2E verified all four scenarios:
fresh session → n=1 ✓
re-hit same session → n=1 (dedup) ✓
Googlebot UA → n=1 (bot skipped) ✓
another fresh session → n=2 ✓
46/46 tests still pass.
---
db/migrations/009_business_views.sql | 15 +++++++++++++++
routes/admin.js | 6 +++++-
routes/public.js | 37 +++++++++++++++++++++++++++++-------
views/admin/dashboard.ejs | 5 +++++
views/public/business.ejs | 16 +++++++++++++---
5 files changed, 68 insertions(+), 11 deletions(-)
diff --git a/db/migrations/009_business_views.sql b/db/migrations/009_business_views.sql
new file mode 100644
index 0000000..899465a
--- /dev/null
+++ b/db/migrations/009_business_views.sql
@@ -0,0 +1,15 @@
+-- Per-business profile view counter. One row per (business, day, count).
+-- The 30d sum gives the dashboard "views this month" stat; total is rarely
+-- needed but cheap to compute on demand.
+--
+-- Bot/dedup: route handler checks user-agent + session before incrementing.
+
+CREATE TABLE IF NOT EXISTS business_views (
+ business_id BIGINT NOT NULL REFERENCES businesses(id) ON DELETE CASCADE,
+ day DATE NOT NULL,
+ n INTEGER NOT NULL DEFAULT 0,
+ PRIMARY KEY (business_id, day)
+);
+
+CREATE INDEX IF NOT EXISTS business_views_recent_idx
+ ON business_views (business_id, day DESC);
diff --git a/routes/admin.js b/routes/admin.js
index 7d69edc..1ac601d 100644
--- a/routes/admin.js
+++ b/routes/admin.js
@@ -25,7 +25,11 @@ router.get('/', async (req, res, next) => {
COUNT(*) FILTER (WHERE delivered_at IS NOT NULL)::int AS delivered,
COUNT(*) FILTER (WHERE delivered_at IS NULL)::int AS pending,
COUNT(*) FILTER (WHERE read_at IS NULL)::int AS unread,
- COUNT(*) FILTER (WHERE created_at > now() - interval '30 days')::int AS last_30d
+ COUNT(*) FILTER (WHERE created_at > now() - interval '30 days')::int AS last_30d,
+ (SELECT COALESCE(SUM(n), 0)::int FROM business_views
+ WHERE business_id = $1 AND day > CURRENT_DATE - interval '30 days') AS views_30d,
+ (SELECT COALESCE(SUM(n), 0)::int FROM business_views
+ WHERE business_id = $1 AND day = CURRENT_DATE) AS views_today
FROM business_interest WHERE business_id = $1
`, [biz.id]);
const recent = await db.many(`
diff --git a/routes/public.js b/routes/public.js
index f9fb5c3..c8d2a1d 100644
--- a/routes/public.js
+++ b/routes/public.js
@@ -112,15 +112,38 @@ router.get('/business/:slug', async (req, res, next) => {
`, [req.params.slug]);
if (!biz) return res.status(404).render('public/404', { title: 'Not found' });
- // Activity stat — only surface when meaningful. Threshold = 3 so newly
- // listed businesses with zero traction don't show "0 messages received"
- // which is worse than showing nothing.
+ // Track view — one increment per session per business per 30min window.
+ // Bot user-agents skipped (heuristic, not perfect; better than nothing).
+ // Logged-in business owner viewing their own profile doesn't count.
+ const ua = String(req.headers['user-agent'] || '').toLowerCase();
+ const isBot = /bot|crawler|spider|slurp|preview|fetch|monitor|pingdom|uptime|headlesschrome/.test(ua);
+ const isOwner = res.locals.currentBusiness && res.locals.currentBusiness.id === biz.id;
+ if (!isBot && !isOwner && req.session) {
+ const seenKey = 'viewed_' + biz.id;
+ const lastSeen = req.session[seenKey];
+ const now = Date.now();
+ if (!lastSeen || (now - lastSeen) > 30 * 60 * 1000) {
+ req.session[seenKey] = now;
+ // Fire-and-forget; don't block render on a counter write.
+ db.query(
+ `INSERT INTO business_views (business_id, day, n)
+ VALUES ($1, CURRENT_DATE, 1)
+ ON CONFLICT (business_id, day) DO UPDATE SET n = business_views.n + 1`,
+ [biz.id]
+ ).catch(e => console.warn('[views] write fail', e.message));
+ }
+ }
+
+ // Activity stat — only surface when meaningful. Threshold = 3 messages OR
+ // 25 views so newly listed businesses with zero traction don't show "0".
const activity = await db.one(`
- SELECT COUNT(*)::int AS msgs_30d
- FROM business_interest
- WHERE business_id = $1 AND created_at > now() - interval '30 days'
+ SELECT
+ (SELECT COUNT(*)::int FROM business_interest
+ WHERE business_id = $1 AND created_at > now() - interval '30 days') AS msgs_30d,
+ (SELECT COALESCE(SUM(n), 0)::int FROM business_views
+ WHERE business_id = $1 AND day > CURRENT_DATE - interval '30 days') AS views_30d
`, [biz.id]);
- const showActivity = activity.msgs_30d >= 3;
+ const showActivity = activity.msgs_30d >= 3 || activity.views_30d >= 25;
res.render('public/business', {
title: `${biz.business_name} · ${BY_KEY[biz.vertical]?.label || biz.vertical} on Ventura Blvd`,
diff --git a/views/admin/dashboard.ejs b/views/admin/dashboard.ejs
index b4268de..1ddf3d8 100644
--- a/views/admin/dashboard.ejs
+++ b/views/admin/dashboard.ejs
@@ -34,6 +34,11 @@
<p style="font-family:var(--serif);font-size:36px;color:<%= stats.unread > 0 ? '#dc2626' : 'var(--fg-muted)' %>;margin:0"><%= stats.unread %></p>
<% if (stats.unread > 0) { %><a href="/admin/leads?filter=unread" style="font-size:11px;color:var(--brass);border:none">Triage →</a><% } %>
</div>
+ <div class="side-card" style="text-align:center">
+ <h3>Profile views</h3>
+ <p style="font-family:var(--serif);font-size:36px;color:var(--brass);margin:0"><%= stats.views_30d %></p>
+ <p style="font-size:11px;color:var(--fg-muted);margin:4px 0 0;letter-spacing:0.06em;text-transform:uppercase">last 30 days · <%= stats.views_today %> today</p>
+ </div>
</div>
<%
diff --git a/views/public/business.ejs b/views/public/business.ejs
index 66ef3d5..b41128a 100644
--- a/views/public/business.ejs
+++ b/views/public/business.ejs
@@ -127,9 +127,19 @@
<aside class="profile-side">
<% if (typeof showActivity !== 'undefined' && showActivity) { %>
- <div class="side-card" style="text-align:center;background:var(--bg);border-color:var(--brass)">
- <p style="font-family:var(--serif);font-size:32px;color:var(--brass);margin:0;line-height:1"><%= activity.msgs_30d %></p>
- <p style="margin:6px 0 0;font-size:12px;letter-spacing:0.06em;text-transform:uppercase;color:var(--fg-muted)">messages routed in the last 30 days</p>
+ <div class="side-card" style="background:var(--bg);border-color:var(--brass)">
+ <% if (activity.msgs_30d >= 3) { %>
+ <div style="text-align:center;<%= activity.views_30d >= 25 ? 'border-bottom:1px dotted var(--border);padding-bottom:12px;margin-bottom:12px' : '' %>">
+ <p style="font-family:var(--serif);font-size:32px;color:var(--brass);margin:0;line-height:1"><%= activity.msgs_30d %></p>
+ <p style="margin:6px 0 0;font-size:11px;letter-spacing:0.06em;text-transform:uppercase;color:var(--fg-muted)">messages routed · last 30 days</p>
+ </div>
+ <% } %>
+ <% if (activity.views_30d >= 25) { %>
+ <div style="text-align:center">
+ <p style="font-family:var(--serif);font-size:32px;color:var(--brass);margin:0;line-height:1"><%= activity.views_30d %></p>
+ <p style="margin:6px 0 0;font-size:11px;letter-spacing:0.06em;text-transform:uppercase;color:var(--fg-muted)">profile views · last 30 days</p>
+ </div>
+ <% } %>
</div>
<% } %>
<div class="side-card">
← 2bd5453 yolo tick 20: breadcrumb nav + BreadcrumbList JSON-LD on /bu
·
back to Ventura Claw Leads
·
yolo tick 22: /admin/leads pagination — 50/page with prev/ne 77c0ec3 →