← back to Ventura Claw Leads

yolo tick 9: lead read/unread tracking + dashboard unread count

0d288c75f03882aff4aa8f5c462fd140acacc6b4 · 2026-05-06 18:40:41 -0700 · Steve Abrams

DB:
- migrations/006_lead_read_state.sql: ADD COLUMN read_at TIMESTAMPTZ NULL on
  business_interest. Partial index on read_at IS NULL for fast unread queries.

Routes:
- GET /admin/leads now accepts ?filter=unread (whitelist; anything else = all).
  ORDER changed to (read_at IS NOT NULL), created_at DESC so unread floats up.
  Pulls counts {total, unread} for the filter chips.
- POST /admin/leads/:id/read   → set read_at = COALESCE(read_at, now())
- POST /admin/leads/:id/unread → set read_at = NULL
- POST /admin/leads/mark-all-read → bulk-mark current business
- Dashboard stats: + unread count, with red dot + Triage CTA when > 0

UI:
- views/admin/dashboard.ejs: 5th stat card 'Unread' — neutral when 0, red
  bordered when > 0 with 'Triage →' link to /admin/leads?filter=unread.
- views/admin/leads.ejs: All / Unread filter chips at top, brass dot in
  leftmost column on unread rows, brass-tinted background on unread rows,
  per-row 'Mark read' / 'Unread' button (POST + redirect-back), 'Mark all
  read' button when unread count > 0, success-state 'Inbox zero 🎉' empty
  state on the unread filter.

Migration applied local + prod via sudo -u postgres (vcl role doesn't own
the table). 46/46 tests still pass.

Files touched

• A db/migrations/006_lead_read_state.sql
• M routes/admin.js
• M views/admin/dashboard.ejs
• M views/admin/leads.ejs

Diff

commit 0d288c75f03882aff4aa8f5c462fd140acacc6b4
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Wed May 6 18:40:41 2026 -0700

    yolo tick 9: lead read/unread tracking + dashboard unread count
    
    DB:
    - migrations/006_lead_read_state.sql: ADD COLUMN read_at TIMESTAMPTZ NULL on
      business_interest. Partial index on read_at IS NULL for fast unread queries.
    
    Routes:
    - GET /admin/leads now accepts ?filter=unread (whitelist; anything else = all).
      ORDER changed to (read_at IS NOT NULL), created_at DESC so unread floats up.
      Pulls counts {total, unread} for the filter chips.
    - POST /admin/leads/:id/read   → set read_at = COALESCE(read_at, now())
    - POST /admin/leads/:id/unread → set read_at = NULL
    - POST /admin/leads/mark-all-read → bulk-mark current business
    - Dashboard stats: + unread count, with red dot + Triage CTA when > 0
    
    UI:
    - views/admin/dashboard.ejs: 5th stat card 'Unread' — neutral when 0, red
      bordered when > 0 with 'Triage →' link to /admin/leads?filter=unread.
    - views/admin/leads.ejs: All / Unread filter chips at top, brass dot in
      leftmost column on unread rows, brass-tinted background on unread rows,
      per-row 'Mark read' / 'Unread' button (POST + redirect-back), 'Mark all
      read' button when unread count > 0, success-state 'Inbox zero 🎉' empty
      state on the unread filter.
    
    Migration applied local + prod via sudo -u postgres (vcl role doesn't own
    the table). 46/46 tests still pass.
---
 db/migrations/006_lead_read_state.sql |  9 ++++++
 routes/admin.js                       | 52 ++++++++++++++++++++++++++++++++---
 views/admin/dashboard.ejs             |  5 ++++
 views/admin/leads.ejs                 | 41 +++++++++++++++++++++++----
 4 files changed, 97 insertions(+), 10 deletions(-)

diff --git a/db/migrations/006_lead_read_state.sql b/db/migrations/006_lead_read_state.sql
new file mode 100644
index 0000000..93d26d2
--- /dev/null
+++ b/db/migrations/006_lead_read_state.sql
@@ -0,0 +1,9 @@
+-- Lead read/unread state for the admin inbox. Businesses click "Mark as read"
+-- after they've followed up; the dashboard surfaces the unread count so they
+-- can triage. read_at NULL = unhandled; non-null = handled (with timestamp).
+
+ALTER TABLE business_interest
+  ADD COLUMN IF NOT EXISTS read_at TIMESTAMPTZ;
+
+CREATE INDEX IF NOT EXISTS business_interest_unread_idx
+  ON business_interest (business_id) WHERE read_at IS NULL;
diff --git a/routes/admin.js b/routes/admin.js
index 465a3d5..e6980c2 100644
--- a/routes/admin.js
+++ b/routes/admin.js
@@ -17,6 +17,7 @@ router.get('/', async (req, res, next) => {
         COUNT(*)::int AS total_leads,
         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
       FROM business_interest WHERE business_id = $1
     `, [biz.id]);
@@ -80,15 +81,58 @@ router.post('/profile', async (req, res, next) => {
 router.get('/leads', async (req, res, next) => {
   try {
     const bizId = res.locals.currentBusiness.id;
+    const filter = (req.query.filter === 'unread') ? 'unread' : 'all';
+    const where = ['business_id = $1'];
+    if (filter === 'unread') where.push('read_at IS NULL');
     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
+             created_at, delivered_at, delivery_email, delivery_msg_id, bill_amount_cents,
+             read_at
         FROM business_interest
-       WHERE business_id = $1
-       ORDER BY created_at DESC
+       WHERE ${where.join(' AND ')}
+       ORDER BY (read_at IS NOT NULL), created_at DESC
        LIMIT 200
     `, [bizId]);
-    res.render('admin/leads', { title: 'Leads · Ventura Claw', leads });
+    const counts = await db.one(`
+      SELECT COUNT(*)::int AS total, COUNT(*) FILTER (WHERE read_at IS NULL)::int AS unread
+        FROM business_interest WHERE business_id = $1
+    `, [bizId]);
+    res.render('admin/leads', { title: 'Leads · Ventura Claw', leads, filter, counts });
+  } catch (err) { next(err); }
+});
+
+router.post('/leads/:id(\\d+)/read', async (req, res, next) => {
+  try {
+    const bizId = res.locals.currentBusiness.id;
+    const leadId = parseInt(req.params.id, 10);
+    await db.query(
+      `UPDATE business_interest SET read_at = COALESCE(read_at, now()) WHERE id = $1 AND business_id = $2`,
+      [leadId, bizId]
+    );
+    res.redirect(req.body.next || '/admin/leads');
+  } catch (err) { next(err); }
+});
+
+router.post('/leads/:id(\\d+)/unread', async (req, res, next) => {
+  try {
+    const bizId = res.locals.currentBusiness.id;
+    const leadId = parseInt(req.params.id, 10);
+    await db.query(
+      `UPDATE business_interest SET read_at = NULL WHERE id = $1 AND business_id = $2`,
+      [leadId, bizId]
+    );
+    res.redirect(req.body.next || '/admin/leads');
+  } catch (err) { next(err); }
+});
+
+router.post('/leads/mark-all-read', async (req, res, next) => {
+  try {
+    const bizId = res.locals.currentBusiness.id;
+    await db.query(
+      `UPDATE business_interest SET read_at = now() WHERE business_id = $1 AND read_at IS NULL`,
+      [bizId]
+    );
+    res.redirect('/admin/leads');
   } catch (err) { next(err); }
 });
 
diff --git a/views/admin/dashboard.ejs b/views/admin/dashboard.ejs
index a62e928..01c74e4 100644
--- a/views/admin/dashboard.ejs
+++ b/views/admin/dashboard.ejs
@@ -29,6 +29,11 @@
       <h3>Pending</h3>
       <p style="font-family:var(--serif);font-size:36px;color:var(--brass);margin:0"><%= stats.pending %></p>
     </div>
+    <div class="side-card" style="text-align:center<%= stats.unread > 0 ? ';border-color:var(--brass)' : '' %>">
+      <h3>Unread<% if (stats.unread > 0) { %> <span style="color:#dc2626">●</span><% } %></h3>
+      <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>
 
   <h2>Recent leads</h2>
diff --git a/views/admin/leads.ejs b/views/admin/leads.ejs
index a495f06..24313fc 100644
--- a/views/admin/leads.ejs
+++ b/views/admin/leads.ejs
@@ -5,27 +5,56 @@
   <p class="kicker">Leads</p>
   <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>
-  <p style="margin-top:8px"><a href="/admin/leads.csv" class="btn btn-ghost btn-sm" download>Export CSV ↓</a></p>
+
+  <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="/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">
+        <input type="hidden" name="_csrf" value="<%= csrfToken %>">
+        <button type="submit" class="btn btn-ghost btn-sm">Mark all read</button>
+      </form>
+    <% } %>
+  </div>
 
   <% if (leads.length === 0) { %>
-    <p class="muted" style="margin-top:32px">No leads yet. Make sure your <a href="/admin/profile">profile</a> is filled out — the more complete the listing, the more inbound.</p>
+    <p class="muted" style="margin-top:32px">
+      <% if (filter === 'unread') { %>
+        🎉 Inbox zero — every lead is marked read. <a href="/admin/leads">Show all</a>.
+      <% } else { %>
+        No leads yet. Make sure your <a href="/admin/profile">profile</a> is filled out — the more complete the listing, the more inbound.
+      <% } %>
+    </p>
   <% } else { %>
     <table style="width:100%;border-collapse:collapse;margin:24px 0;font-size:13px">
       <thead>
         <tr style="border-bottom:2px solid var(--fg);text-align:left">
+          <th style="padding:10px 8px;font-size:11px;letter-spacing:0.08em;text-transform:uppercase;color:var(--fg-muted);font-weight:500;width:24px"></th>
           <th style="padding:10px 8px;font-size:11px;letter-spacing:0.08em;text-transform:uppercase;color:var(--fg-muted);font-weight:500">When</th>
           <th style="padding:10px 8px;font-size:11px;letter-spacing:0.08em;text-transform:uppercase;color:var(--fg-muted);font-weight:500">From</th>
           <th style="padding:10px 8px;font-size:11px;letter-spacing:0.08em;text-transform:uppercase;color:var(--fg-muted);font-weight:500">Message</th>
           <th style="padding:10px 8px;font-size:11px;letter-spacing:0.08em;text-transform:uppercase;color:var(--fg-muted);font-weight:500">Status</th>
+          <th style="padding:10px 8px;font-size:11px;letter-spacing:0.08em;text-transform:uppercase;color:var(--fg-muted);font-weight:500"></th>
         </tr>
       </thead>
       <tbody>
-        <% leads.forEach(function(l){ %>
-          <tr style="border-bottom:1px solid var(--border);vertical-align:top">
+        <% leads.forEach(function(l){
+          var unread = !l.read_at;
+        %>
+          <tr style="border-bottom:1px solid var(--border);vertical-align:top<%= unread ? ';background:rgba(184,134,11,0.04)' : '' %>">
+            <td style="padding:12px 4px;color:var(--brass);font-weight:bold;text-align:center"><% if (unread) { %>●<% } %></td>
             <td style="padding:12px 8px;color:var(--fg-muted);white-space:nowrap"><%= new Date(l.created_at).toLocaleString('en-US',{ month:'short', day:'numeric', hour:'numeric', minute:'2-digit' }) %></td>
-            <td style="padding:12px 8px"><strong><%= l.consumer_name %></strong><br><a href="mailto:<%= l.consumer_email %>"><%= l.consumer_email %></a><% if (l.consumer_phone) { %><br><a href="tel:<%= l.consumer_phone %>"><%= l.consumer_phone %></a><% } %><% if (l.zip) { %><br><span style="color:var(--fg-muted)">ZIP <%= l.zip %></span><% } %></td>
-            <td style="padding:12px 8px;max-width:380px"><%= l.message || '—' %></td>
+            <td style="padding:12px 8px"><strong style="<%= unread ? 'color:var(--fg)' : 'color:var(--fg-muted)' %>"><%= l.consumer_name %></strong><br><a href="mailto:<%= l.consumer_email %>"><%= l.consumer_email %></a><% if (l.consumer_phone) { %><br><a href="tel:<%= l.consumer_phone %>"><%= l.consumer_phone %></a><% } %><% if (l.zip) { %><br><span style="color:var(--fg-muted)">ZIP <%= l.zip %></span><% } %></td>
+            <td style="padding:12px 8px;max-width:380px;color:<%= unread ? 'var(--fg)' : 'var(--fg-muted)' %>"><%= l.message || '—' %></td>
             <td style="padding:12px 8px"><% if (l.delivered_at) { %><span style="color:#16a34a">Delivered</span><br><span style="color:var(--fg-muted);font-size:11px"><%= new Date(l.delivered_at).toLocaleString('en-US',{ month:'short', day:'numeric', hour:'numeric', minute:'2-digit' }) %></span><% } else { %><span style="color:var(--fg-muted)">Pending (≤5min)</span><% } %></td>
+            <td style="padding:12px 8px;white-space:nowrap">
+              <form method="post" action="/admin/leads/<%= l.id %>/<%= unread ? 'read' : 'unread' %>" style="margin:0">
+                <input type="hidden" name="_csrf" value="<%= csrfToken %>">
+                <input type="hidden" name="next" value="/admin/leads<%= filter === 'unread' ? '?filter=unread' : '' %>">
+                <button type="submit" class="btn btn-ghost btn-sm" style="font-size:11px;padding:4px 8px"><%= unread ? 'Mark read' : 'Unread' %></button>
+              </form>
+            </td>
           </tr>
         <% }); %>
       </tbody>

← 1a8b9b4 yolo tick 8: vertical-themed hero panels (no external image  ·  back to Ventura Claw Leads  ·  yolo tick 10: home-page social proof + /admin/leads date-ran f3a4936 →