← back to Ventura Claw Leads

yolo tick 14: /admin/leads search-within-messages

294e68a9db16a77e2502194d81be27a64cb24174 · 2026-05-06 19:53:46 -0700 · Steve Abrams

routes/admin.js GET /admin/leads now accepts ?q=… (lowercased ILIKE on
consumer_name / consumer_email / message / zip). Combines freely with
?filter=unread + ?range=7|30|90|all — q is preserved across all the
existing chip clicks (rangeHref + filterHref helpers updated to round-trip).

views/admin/leads.ejs adds a search form between the title and the chip
row. Hidden inputs persist filter+range when the user submits a fresh
search. 'Clear search' button appears when q is non-empty.

Useful for active businesses needing to find a specific lead by name/email
or recall a project mentioned in the message body. 200-row LIMIT preserved
so search results stay snappy.

46/46 tests still pass.

Files touched

• M routes/admin.js
• M views/admin/leads.ejs

Diff

commit 294e68a9db16a77e2502194d81be27a64cb24174
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Wed May 6 19:53:46 2026 -0700

    yolo tick 14: /admin/leads search-within-messages
    
    routes/admin.js GET /admin/leads now accepts ?q=… (lowercased ILIKE on
    consumer_name / consumer_email / message / zip). Combines freely with
    ?filter=unread + ?range=7|30|90|all — q is preserved across all the
    existing chip clicks (rangeHref + filterHref helpers updated to round-trip).
    
    views/admin/leads.ejs adds a search form between the title and the chip
    row. Hidden inputs persist filter+range when the user submits a fresh
    search. 'Clear search' button appears when q is non-empty.
    
    Useful for active businesses needing to find a specific lead by name/email
    or recall a project mentioned in the message body. 200-row LIMIT preserved
    so search results stay snappy.
    
    46/46 tests still pass.
---
 routes/admin.js       | 10 ++++++++--
 views/admin/leads.ejs | 12 ++++++++++++
 2 files changed, 20 insertions(+), 2 deletions(-)

diff --git a/routes/admin.js b/routes/admin.js
index 98f06c9..8b75c9c 100644
--- a/routes/admin.js
+++ b/routes/admin.js
@@ -107,10 +107,16 @@ router.get('/leads', async (req, res, next) => {
     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 q = String(req.query.q || '').trim().slice(0, 120);
 
     const where = ['business_id = $1'];
+    const params = [bizId];
     if (filter === 'unread') where.push('read_at IS NULL');
     if (rangeDays) where.push(`created_at > now() - interval '${rangeDays} days'`);
+    if (q) {
+      params.push(`%${q.toLowerCase()}%`);
+      where.push(`(LOWER(consumer_name) LIKE $${params.length} OR LOWER(consumer_email) LIKE $${params.length} OR LOWER(message) LIKE $${params.length} OR LOWER(zip) LIKE $${params.length})`);
+    }
 
     const leads = await db.many(`
       SELECT id, consumer_name, consumer_email, consumer_phone, message, zip, source,
@@ -120,7 +126,7 @@ router.get('/leads', async (req, res, next) => {
        WHERE ${where.join(' AND ')}
        ORDER BY (read_at IS NOT NULL), created_at DESC
        LIMIT 200
-    `, [bizId]);
+    `, params);
     const counts = await db.one(`
       SELECT COUNT(*)::int AS total,
              COUNT(*) FILTER (WHERE read_at IS NULL)::int AS unread,
@@ -129,7 +135,7 @@ router.get('/leads', async (req, res, next) => {
              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, range: rangeKey });
+    res.render('admin/leads', { title: 'Leads · Ventura Claw', leads, filter, counts, range: rangeKey, q });
   } catch (err) { next(err); }
 });
 
diff --git a/views/admin/leads.ejs b/views/admin/leads.ejs
index 8208cc0..e8d21f8 100644
--- a/views/admin/leads.ejs
+++ b/views/admin/leads.ejs
@@ -11,6 +11,7 @@
       var p = new URLSearchParams();
       if (filter === 'unread') p.set('filter', 'unread');
       if (r !== 'all') p.set('range', r);
+      if (q) p.set('q', q);
       var s = p.toString();
       return '/admin/leads' + (s ? '?' + s : '');
     }
@@ -18,10 +19,21 @@
       var p = new URLSearchParams();
       if (f === 'unread') p.set('filter', 'unread');
       if (range !== 'all') p.set('range', range);
+      if (q) p.set('q', q);
       var s = p.toString();
       return '/admin/leads' + (s ? '?' + s : '');
     }
   %>
+
+  <form method="get" action="/admin/leads" style="margin-top:12px;display:flex;gap:8px;flex-wrap:wrap;align-items:center">
+    <% if (filter === 'unread') { %><input type="hidden" name="filter" value="unread"><% } %>
+    <% if (range !== 'all') { %><input type="hidden" name="range" value="<%= range %>"><% } %>
+    <input type="search" name="q" value="<%= q %>" placeholder="Search name, email, message, ZIP…" style="flex:1;min-width:240px;padding:8px 12px;border:1px solid var(--border);border-radius:4px;font-size:13px">
+    <button type="submit" class="btn btn-ghost btn-sm">Search</button>
+    <% if (q) { %>
+      <a href="<%= filterHref(filter) %>" class="btn btn-ghost btn-sm" style="font-size:11px">Clear search</a>
+    <% } %>
+  </form>
   <div style="display:flex;gap:8px;flex-wrap:wrap;align-items:center;margin-top:12px">
     <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>

← 65ee0ec yolo tick 13: /map pin hover preview + /admin/billing recent  ·  back to Ventura Claw Leads  ·  yolo tick 15: home-page 'How it works' 3-step explainer 4e2663d →