← back to Ventura Corridor
iter 49: response tracking — migration 010 adds reply_text/reply_channel/won_value_usd/lost_reason/next_followup_at/followup_notes; v_response_funnel + v_followups_due views; /responses.html viewer with funnel stats per channel, response list with edit modal (status/reply text/won $/lost reason/follow-up date), follow-ups-due section; PATCH endpoint extended + auto-backfills sent_at when reply lands so funnel math holds
ce211194b1069da9f9c10e53cdbe6d2b20050442 · 2026-05-06 11:36:54 -0700 · SteveStudio2
Files touched
A db/migrations/010_response_tracking.sqlM public/linkedin.htmlM public/pitches.htmlA public/responses.htmlM public/sales-nav-signup.htmlM public/today.htmlM public/walk-route.htmlM src/server/index.ts
Diff
commit ce211194b1069da9f9c10e53cdbe6d2b20050442
Author: SteveStudio2 <steve@designerwallcoverings.com>
Date: Wed May 6 11:36:54 2026 -0700
iter 49: response tracking — migration 010 adds reply_text/reply_channel/won_value_usd/lost_reason/next_followup_at/followup_notes; v_response_funnel + v_followups_due views; /responses.html viewer with funnel stats per channel, response list with edit modal (status/reply text/won $/lost reason/follow-up date), follow-ups-due section; PATCH endpoint extended + auto-backfills sent_at when reply lands so funnel math holds
---
db/migrations/010_response_tracking.sql | 54 ++++
public/linkedin.html | 1 +
public/pitches.html | 1 +
public/responses.html | 443 ++++++++++++++++++++++++++++++++
public/sales-nav-signup.html | 1 +
public/today.html | 1 +
public/walk-route.html | 1 +
src/server/index.ts | 93 ++++++-
8 files changed, 590 insertions(+), 5 deletions(-)
diff --git a/db/migrations/010_response_tracking.sql b/db/migrations/010_response_tracking.sql
new file mode 100644
index 0000000..8d13b4b
--- /dev/null
+++ b/db/migrations/010_response_tracking.sql
@@ -0,0 +1,54 @@
+-- Migration 010 — pitch response & outcome tracking
+-- Once Steve walks the corridor with /walk-route.html or DMs via /linkedin.html,
+-- replies/wins/losses need a structured DB home. Free-form `notes` already exists,
+-- but the pipeline needs queryable response text + reply channel + win amount
+-- + structured loss reasons + scheduled follow-up.
+
+ALTER TABLE pitches
+ ADD COLUMN IF NOT EXISTS reply_text TEXT,
+ ADD COLUMN IF NOT EXISTS reply_channel TEXT,
+ ADD COLUMN IF NOT EXISTS won_value_usd NUMERIC(10,2),
+ ADD COLUMN IF NOT EXISTS lost_reason TEXT,
+ ADD COLUMN IF NOT EXISTS next_followup_at TIMESTAMPTZ,
+ ADD COLUMN IF NOT EXISTS followup_notes TEXT;
+
+CREATE INDEX IF NOT EXISTS idx_pitches_replied_at ON pitches (replied_at) WHERE replied_at IS NOT NULL;
+CREATE INDEX IF NOT EXISTS idx_pitches_followup_at ON pitches (next_followup_at) WHERE next_followup_at IS NOT NULL AND closed_at IS NULL;
+
+-- View: response funnel by channel
+CREATE OR REPLACE VIEW v_response_funnel AS
+SELECT
+ COALESCE(outreach_channel, 'unspecified') AS channel,
+ count(*) FILTER (WHERE sent_at IS NOT NULL) AS sent,
+ count(*) FILTER (WHERE replied_at IS NOT NULL) AS replied,
+ count(*) FILTER (WHERE status = 'won') AS won,
+ count(*) FILTER (WHERE status = 'lost') AS lost,
+ COALESCE(SUM(won_value_usd) FILTER (WHERE status = 'won'), 0) AS won_value_usd,
+ ROUND(
+ 100.0 * count(*) FILTER (WHERE replied_at IS NOT NULL)
+ / NULLIF(count(*) FILTER (WHERE sent_at IS NOT NULL), 0),
+ 1
+ ) AS reply_rate_pct
+FROM pitches
+WHERE sent_at IS NOT NULL
+GROUP BY outreach_channel
+ORDER BY sent DESC;
+
+-- View: today's follow-ups (and any overdue)
+CREATE OR REPLACE VIEW v_followups_due AS
+SELECT
+ p.id, p.business_id, b.name, b.address, b.city, p.pitch_type, p.status,
+ p.outreach_channel, p.next_followup_at, p.followup_notes, p.replied_at,
+ p.contact_name, p.email, p.phone, p.linkedin,
+ CASE
+ WHEN p.next_followup_at::date < CURRENT_DATE THEN 'overdue'
+ WHEN p.next_followup_at::date = CURRENT_DATE THEN 'today'
+ ELSE 'upcoming'
+ END AS due_bucket,
+ (CURRENT_DATE - p.next_followup_at::date) AS days_overdue
+FROM pitches p
+JOIN businesses b ON b.id = p.business_id
+WHERE p.next_followup_at IS NOT NULL
+ AND p.closed_at IS NULL
+ AND p.status NOT IN ('won','lost','skip')
+ORDER BY p.next_followup_at ASC NULLS LAST;
diff --git a/public/linkedin.html b/public/linkedin.html
index 657be4c..9d978bc 100644
--- a/public/linkedin.html
+++ b/public/linkedin.html
@@ -75,6 +75,7 @@
<a href="/atlas.html">atlas</a>
<a href="/pitches.html">pitches</a>
<a href="/walk-route.html">walk route</a>
+ <a href="/responses.html">responses</a>
<a href="/linkedin.html" class="active">linkedin</a>
<span class="theme-toggle-host" style="display:inline-flex;align-items:center;margin-left:6px"></span>
</nav>
diff --git a/public/pitches.html b/public/pitches.html
index 5531af7..f1d4679 100644
--- a/public/pitches.html
+++ b/public/pitches.html
@@ -109,6 +109,7 @@
<a href="/pitches.html" class="active">pitches</a>
<a href="/today.html">today</a>
<a href="/walk-route.html">walk route</a>
+ <a href="/responses.html">responses</a>
<a href="/linkedin.html">linkedin</a>
<span class="theme-toggle-host" style="display:inline-flex;align-items:center;margin-left:6px"></span>
</nav>
diff --git a/public/responses.html b/public/responses.html
new file mode 100644
index 0000000..bc79fe5
--- /dev/null
+++ b/public/responses.html
@@ -0,0 +1,443 @@
+<!doctype html>
+<html lang="en">
+<head>
+<meta charset="utf-8">
+<title>Responses · DW · Ventura Corridor</title>
+<meta name="viewport" content="width=device-width,initial-scale=1">
+<script>(function(){try{var t=localStorage.getItem('vc-theme');document.documentElement.dataset.theme=(t==='light'||t==='dark')?t:'dark';}catch(e){document.documentElement.dataset.theme='dark';}})();</script>
+<link rel="stylesheet" href="/theme.css">
+<script src="/theme.js" defer></script>
+<style>
+ :root {
+ --noir: #0a0a0c; --noir-rise: #131316; --ink: #f0ece2;
+ --ink-mute: #888475; --metal: #b89968; --metal-glow: #d4b683;
+ --rule: #2a2622; --green: #6a9b73; --red: #b66565;
+ --serif: 'Cormorant Garamond', 'Times New Roman', serif;
+ --sans: 'Inter', -apple-system, system-ui, sans-serif;
+ --mono: 'JetBrains Mono', ui-monospace, monospace;
+ }
+ @import url('https://fonts.googleapis.com/css2?family=Cormorant+Garamond:ital,wght@0,300;0,400;1,300;1,400&family=Inter:wght@200;300;400;600&family=JetBrains+Mono:wght@200;300;400&display=swap');
+ html, body { margin: 0; background: var(--noir); color: var(--ink); font-family: var(--sans); font-weight: 300; }
+ * { box-sizing: border-box; }
+ header { position: sticky; top: 0; z-index: 10;
+ background: linear-gradient(180deg, rgba(10,10,12,.97) 70%, rgba(10,10,12,0));
+ padding: 22px 32px 16px; border-bottom: 1px solid var(--rule);
+ display: flex; justify-content: space-between; align-items: baseline; gap: 24px; flex-wrap: wrap; }
+ header h1 { font-family: var(--serif); font-weight: 400; font-size: 28px; margin: 0; letter-spacing: -0.01em; }
+ header h1 em { font-style: italic; color: var(--metal); }
+ .sub { font-size: 11px; letter-spacing: .26em; text-transform: uppercase; color: var(--ink-mute); margin-top: 2px; }
+ nav.tabs { display: flex; gap: 4px; flex-wrap: wrap; }
+ nav.tabs a { font-size: 9px; letter-spacing: .26em; text-transform: uppercase;
+ color: var(--ink-mute); text-decoration: none; padding: 5px 10px; border: 1px solid var(--rule); }
+ nav.tabs a.active, nav.tabs a:hover { color: var(--metal); border-color: var(--metal); }
+
+ /* Funnel */
+ .funnel {
+ padding: 32px; border-bottom: 1px solid var(--rule);
+ background: radial-gradient(ellipse at top, rgba(184,153,104,0.06), transparent 70%);
+ }
+ .funnel-totals {
+ display: grid; grid-template-columns: repeat(auto-fit, minmax(160px, 1fr));
+ gap: 24px; margin-bottom: 28px;
+ }
+ .stat-card {
+ padding: 18px 20px; background: var(--noir-rise); border: 1px solid var(--rule);
+ }
+ .stat-card .num { font-family: var(--serif); font-style: italic; font-size: 36px; color: var(--metal-glow); line-height: 1; }
+ .stat-card .lbl { font-size: 9px; letter-spacing: .26em; text-transform: uppercase; color: var(--ink-mute); margin-top: 6px; }
+ .stat-card.win .num { color: var(--green); }
+ .stat-card.value .num { color: var(--metal-glow); font-size: 30px; }
+
+ table.channel-funnel {
+ width: 100%; border-collapse: collapse; background: var(--noir-rise);
+ border: 1px solid var(--rule); font-size: 12px;
+ }
+ table.channel-funnel th { text-align: left; font-size: 9px; letter-spacing: .26em;
+ text-transform: uppercase; color: var(--ink-mute); padding: 10px 14px; border-bottom: 1px solid var(--rule); }
+ table.channel-funnel td { padding: 12px 14px; border-bottom: 1px solid var(--rule); }
+ table.channel-funnel td.num { text-align: right; font-family: var(--mono); }
+ table.channel-funnel td.channel { font-family: var(--serif); font-size: 15px; color: var(--metal); }
+
+ /* Filter bar */
+ .filter-bar {
+ padding: 16px 32px; display: flex; gap: 8px; flex-wrap: wrap; align-items: center;
+ border-bottom: 1px solid var(--rule);
+ }
+ .filter-bar .pill {
+ font-size: 9px; letter-spacing: .26em; text-transform: uppercase;
+ color: var(--ink-mute); padding: 6px 14px; border: 1px solid var(--rule); cursor: pointer;
+ background: transparent;
+ }
+ .filter-bar .pill.active { color: var(--metal); border-color: var(--metal); }
+ .filter-bar .pill:hover { border-color: var(--metal); }
+
+ /* Response cards */
+ main { padding: 24px 32px; }
+ .response {
+ margin-bottom: 16px; padding: 20px 22px;
+ background: var(--noir-rise); border: 1px solid var(--rule);
+ }
+ .response.won { border-left: 3px solid var(--green); }
+ .response.lost { border-left: 3px solid var(--red); opacity: 0.75; }
+ .response.replied { border-left: 3px solid var(--metal); }
+ .response-head { display: flex; gap: 18px; align-items: baseline; flex-wrap: wrap; margin-bottom: 12px; }
+ .response-head .name { font-family: var(--serif); font-size: 22px; color: var(--ink); margin: 0; }
+ .response-head .pill { font-size: 9px; letter-spacing: .2em; text-transform: uppercase;
+ color: var(--metal); padding: 3px 8px; border: 1px solid var(--rule); }
+ .response-head .pill.status-won { color: var(--green); border-color: var(--green); }
+ .response-head .pill.status-lost { color: var(--red); border-color: var(--red); }
+ .response-head .meta { font-size: 11px; color: var(--ink-mute); letter-spacing: .12em; }
+
+ .reply-quote {
+ margin: 12px 0;
+ padding: 14px 18px;
+ background: var(--noir); border-left: 2px solid var(--metal);
+ font-family: var(--serif); font-style: italic; font-size: 15px; line-height: 1.55; color: var(--ink);
+ }
+ .reply-quote .label { display: block; font-family: var(--sans); font-style: normal;
+ font-size: 9px; letter-spacing: .26em; color: var(--metal); margin-bottom: 6px; text-transform: uppercase; }
+
+ .response-meta-grid {
+ display: grid; grid-template-columns: repeat(auto-fit, minmax(180px, 1fr)); gap: 12px;
+ margin-top: 12px; font-size: 11px;
+ }
+ .response-meta-grid .field .lbl {
+ font-size: 8px; letter-spacing: .26em; text-transform: uppercase; color: var(--ink-mute);
+ margin-bottom: 3px;
+ }
+ .response-meta-grid .field .val { color: var(--ink); font-family: var(--mono); font-size: 11px; }
+
+ .response-actions { display: flex; gap: 8px; margin-top: 12px; flex-wrap: wrap; }
+ .response-actions a, .response-actions button {
+ background: transparent; border: 1px solid var(--rule); color: var(--ink-mute);
+ font-size: 9px; letter-spacing: .2em; text-transform: uppercase; padding: 5px 12px;
+ cursor: pointer; text-decoration: none; font-family: var(--sans);
+ }
+ .response-actions a:hover, .response-actions button:hover { color: var(--metal); border-color: var(--metal); }
+ .response-actions .btn-edit { color: var(--metal); border-color: var(--metal); }
+
+ .empty { text-align: center; padding: 60px 32px; color: var(--ink-mute); }
+ .empty .big { font-family: var(--serif); font-style: italic; font-size: 28px; color: var(--metal); margin-bottom: 12px; }
+
+ /* Followups */
+ .followups-section {
+ margin: 32px 0; padding: 20px 22px;
+ background: var(--noir-rise); border: 1px solid var(--rule);
+ }
+ .followups-section h2 {
+ font-family: var(--serif); font-style: italic; font-weight: 400; font-size: 22px;
+ color: var(--metal); margin: 0 0 14px;
+ }
+ .followup-row {
+ display: grid; grid-template-columns: auto 1fr auto auto; gap: 16px;
+ padding: 10px 12px; align-items: center; border-bottom: 1px solid var(--rule);
+ font-size: 12px;
+ }
+ .followup-row:last-child { border-bottom: none; }
+ .followup-row .due {
+ font-family: var(--mono); font-size: 10px; letter-spacing: .15em;
+ padding: 4px 8px; border: 1px solid var(--rule);
+ text-transform: uppercase;
+ }
+ .followup-row .due.overdue { color: var(--red); border-color: var(--red); }
+ .followup-row .due.today { color: var(--metal-glow); border-color: var(--metal-glow); }
+ .followup-row .name { font-family: var(--serif); font-size: 16px; }
+
+ /* Edit modal */
+ .modal-bg {
+ position: fixed; inset: 0; background: rgba(0,0,0,.85); z-index: 100;
+ display: none; align-items: flex-start; justify-content: center; padding: 60px 20px; overflow: auto;
+ }
+ .modal-bg.open { display: flex; }
+ .modal {
+ background: var(--noir-rise); border: 1px solid var(--metal);
+ padding: 28px 30px; max-width: 600px; width: 100%;
+ }
+ .modal h3 { font-family: var(--serif); font-weight: 400; font-size: 22px; margin: 0 0 16px; color: var(--metal); }
+ .modal label { display: block; font-size: 9px; letter-spacing: .26em; text-transform: uppercase; color: var(--ink-mute); margin-top: 14px; margin-bottom: 4px; }
+ .modal textarea, .modal input, .modal select {
+ width: 100%; background: var(--noir); border: 1px solid var(--rule); color: var(--ink);
+ padding: 10px 12px; font-family: var(--sans); font-size: 13px; border-radius: 0; resize: vertical;
+ }
+ .modal textarea { min-height: 80px; }
+ .modal .row { display: grid; grid-template-columns: 1fr 1fr; gap: 14px; }
+ .modal-actions { margin-top: 22px; display: flex; gap: 10px; justify-content: flex-end; }
+ .modal-actions button {
+ background: transparent; border: 1px solid var(--metal); color: var(--metal);
+ padding: 9px 18px; font-size: 10px; letter-spacing: .26em; text-transform: uppercase; cursor: pointer;
+ }
+ .modal-actions button.cancel { border-color: var(--rule); color: var(--ink-mute); }
+ .modal-actions button.save:hover { background: rgba(184,153,104,0.1); }
+</style>
+</head>
+<body>
+
+<header>
+ <div>
+ <h1>Responses <em>+ outcomes</em></h1>
+ <div class="sub">replies · wins · losses · scheduled follow-ups</div>
+ </div>
+ <nav class="tabs">
+ <a href="/">map</a>
+ <a href="/today.html">today</a>
+ <a href="/pitches.html">pitches</a>
+ <a href="/walk-route.html">walk route</a>
+ <a href="/linkedin.html">linkedin</a>
+ <a href="/responses.html" class="active">responses</a>
+ <span class="theme-toggle-host" style="display:inline-flex;align-items:center;margin-left:6px"></span>
+ </nav>
+</header>
+
+<section class="funnel">
+ <div class="funnel-totals" id="totals">—</div>
+ <table class="channel-funnel">
+ <thead><tr>
+ <th>Channel</th><th class="num">Sent</th><th class="num">Replied</th>
+ <th class="num">Reply rate</th><th class="num">Won</th><th class="num">Lost</th><th class="num">Won $</th>
+ </tr></thead>
+ <tbody id="channel-funnel"></tbody>
+ </table>
+</section>
+
+<div class="filter-bar">
+ <button class="pill active" data-filter="all">All</button>
+ <button class="pill" data-filter="replied">Replied (open)</button>
+ <button class="pill" data-filter="won">Won</button>
+ <button class="pill" data-filter="lost">Lost</button>
+</div>
+
+<main>
+ <div class="followups-section" id="followups-section" style="display:none">
+ <h2>Follow-ups due</h2>
+ <div id="followups-list"></div>
+ </div>
+ <div id="responses-list">
+ <div class="empty"><div class="big">Loading…</div></div>
+ </div>
+</main>
+
+<div class="modal-bg" id="modal-bg" onclick="if(event.target.id==='modal-bg')closeModal()">
+ <div class="modal">
+ <h3>Log response</h3>
+ <input type="hidden" id="m-id">
+ <div id="m-name" style="font-family:var(--serif);font-size:18px;color:var(--metal-glow);margin-bottom:6px"></div>
+ <label>Status</label>
+ <select id="m-status">
+ <option value="replied">Replied (still open)</option>
+ <option value="won">Won — closed</option>
+ <option value="lost">Lost — closed</option>
+ </select>
+ <label>Reply text — paste their message</label>
+ <textarea id="m-reply-text" placeholder="Hey Steve — yes, please come by…"></textarea>
+ <div class="row">
+ <div>
+ <label>Reply channel</label>
+ <select id="m-reply-channel">
+ <option value="">— same as outreach —</option>
+ <option value="walk-in">Walk-in / in-person</option>
+ <option value="email">Email</option>
+ <option value="linkedin-dm">LinkedIn DM</option>
+ <option value="inmail">InMail</option>
+ <option value="phone">Phone call</option>
+ <option value="text">Text</option>
+ <option value="postcard">Postcard reply</option>
+ <option value="other">Other</option>
+ </select>
+ </div>
+ <div>
+ <label>Won $ (only if won)</label>
+ <input type="number" id="m-won-value" placeholder="e.g. 1850" step="0.01">
+ </div>
+ </div>
+ <div id="m-lost-row" style="display:none">
+ <label>Lost reason (only if lost)</label>
+ <select id="m-lost-reason">
+ <option value="">—</option>
+ <option value="price">Price</option>
+ <option value="not-interested">Not interested</option>
+ <option value="competitor">Went with competitor</option>
+ <option value="timing">Timing — try again later</option>
+ <option value="no-decision-maker">Couldn't reach decision-maker</option>
+ <option value="bad-fit">Bad fit / not a wallcovering project</option>
+ <option value="ghosted">Ghosted after reply</option>
+ <option value="other">Other</option>
+ </select>
+ </div>
+ <div class="row">
+ <div>
+ <label>Next follow-up date</label>
+ <input type="date" id="m-followup-date">
+ </div>
+ <div>
+ <label>Follow-up notes</label>
+ <input id="m-followup-notes" placeholder="Drop sample, mention X">
+ </div>
+ </div>
+ <div class="modal-actions">
+ <button class="cancel" onclick="closeModal()">Cancel</button>
+ <button class="save" onclick="saveResponse()">Save</button>
+ </div>
+ </div>
+</div>
+
+<script>
+function escapeHtml(s) { return String(s ?? '').replace(/[&<>"']/g, c => ({ '&': '&', '<': '<', '>': '>', '"': '"', "'": ''' }[c])); }
+function fmtDate(d) { if (!d) return ''; const dt = new Date(d); return dt.toISOString().slice(0, 10); }
+function fmtDollars(v) { if (!v) return ''; return '$' + Number(v).toLocaleString('en-US', { minimumFractionDigits: 0, maximumFractionDigits: 0 }); }
+
+let currentFilter = 'all';
+
+async function loadFunnel() {
+ const data = await fetch('/api/responses/funnel').then(r => r.json());
+ const t = data.totals || {};
+ document.getElementById('totals').innerHTML = `
+ <div class="stat-card"><div class="num">${t.sent || 0}</div><div class="lbl">Pitches sent</div></div>
+ <div class="stat-card"><div class="num">${t.replied || 0}</div><div class="lbl">Replies</div></div>
+ <div class="stat-card"><div class="num">${t.reply_rate_pct ?? '—'}${t.reply_rate_pct ? '%' : ''}</div><div class="lbl">Reply rate</div></div>
+ <div class="stat-card win"><div class="num">${t.won || 0}</div><div class="lbl">Won</div></div>
+ <div class="stat-card"><div class="num">${t.lost || 0}</div><div class="lbl">Lost</div></div>
+ <div class="stat-card value"><div class="num">${fmtDollars(t.won_value_usd) || '$0'}</div><div class="lbl">Won value</div></div>
+ `;
+ document.getElementById('channel-funnel').innerHTML = (data.by_channel || []).map(r => `
+ <tr>
+ <td class="channel">${escapeHtml(r.channel)}</td>
+ <td class="num">${r.sent}</td>
+ <td class="num">${r.replied}</td>
+ <td class="num">${r.reply_rate_pct ?? '—'}${r.reply_rate_pct ? '%' : ''}</td>
+ <td class="num">${r.won}</td>
+ <td class="num">${r.lost}</td>
+ <td class="num">${fmtDollars(r.won_value_usd)}</td>
+ </tr>`).join('') || '<tr><td colspan="7" style="padding:20px;text-align:center;color:var(--ink-mute)">No outreach sent yet</td></tr>';
+}
+
+async function loadFollowups() {
+ const data = await fetch('/api/followups').then(r => r.json());
+ if ((data.rows || []).length === 0) {
+ document.getElementById('followups-section').style.display = 'none';
+ return;
+ }
+ document.getElementById('followups-section').style.display = 'block';
+ document.getElementById('followups-list').innerHTML = data.rows.map(r => `
+ <div class="followup-row">
+ <span class="due ${r.due_bucket}">${r.due_bucket === 'overdue' ? r.days_overdue + 'd late' : r.due_bucket}</span>
+ <div>
+ <div class="name">${escapeHtml(r.name)}</div>
+ <div style="color:var(--ink-mute);font-size:10px;letter-spacing:.12em">${escapeHtml(r.pitch_type)} · ${escapeHtml(r.outreach_channel || '—')} · ${escapeHtml(r.followup_notes || '')}</div>
+ </div>
+ <div style="font-family:var(--mono);font-size:10px;color:var(--ink-mute)">${fmtDate(r.next_followup_at)}</div>
+ <button onclick="openModalById(${r.id})">Log</button>
+ </div>
+ `).join('');
+}
+
+async function loadResponses() {
+ const data = await fetch('/api/responses?filter=' + currentFilter).then(r => r.json());
+ const list = document.getElementById('responses-list');
+ if ((data.rows || []).length === 0) {
+ list.innerHTML = `<div class="empty"><div class="big">No responses yet.</div><div>Once Steve walks the corridor or sends DMs, replies log here automatically when status advances.</div></div>`;
+ return;
+ }
+ list.innerHTML = data.rows.map(r => {
+ const statusClass = r.status === 'won' ? 'status-won' : r.status === 'lost' ? 'status-lost' : '';
+ return `
+ <div class="response ${r.status}">
+ <div class="response-head">
+ <h3 class="name">${escapeHtml(r.name)}</h3>
+ <span class="pill ${statusClass}">${escapeHtml(r.status)}</span>
+ <span class="pill">${escapeHtml(r.pitch_type || '')}</span>
+ <span class="pill">${escapeHtml(r.outreach_channel || 'unspecified')}</span>
+ ${r.dw_proximity ? `<span class="pill">${escapeHtml(r.dw_proximity)}</span>` : ''}
+ <span class="meta">${escapeHtml(r.address || '')}</span>
+ </div>
+ ${r.reply_text ? `<div class="reply-quote"><span class="label">Their reply ${r.reply_channel ? '· via ' + escapeHtml(r.reply_channel) : ''}</span>${escapeHtml(r.reply_text)}</div>` : ''}
+ ${r.notes ? `<div style="color:var(--ink-mute);font-size:12px;margin-bottom:8px"><strong style="color:var(--metal)">Notes:</strong> ${escapeHtml(r.notes)}</div>` : ''}
+ <div class="response-meta-grid">
+ <div class="field"><div class="lbl">Sent</div><div class="val">${fmtDate(r.sent_at) || '—'}</div></div>
+ <div class="field"><div class="lbl">Replied</div><div class="val">${fmtDate(r.replied_at) || '—'}</div></div>
+ ${r.closed_at ? `<div class="field"><div class="lbl">Closed</div><div class="val">${fmtDate(r.closed_at)}</div></div>` : ''}
+ ${r.won_value_usd ? `<div class="field"><div class="lbl">Won $</div><div class="val" style="color:var(--green)">${fmtDollars(r.won_value_usd)}</div></div>` : ''}
+ ${r.lost_reason ? `<div class="field"><div class="lbl">Lost reason</div><div class="val">${escapeHtml(r.lost_reason)}</div></div>` : ''}
+ ${r.next_followup_at ? `<div class="field"><div class="lbl">Next follow-up</div><div class="val" style="color:var(--metal-glow)">${fmtDate(r.next_followup_at)}</div></div>` : ''}
+ ${r.contact_name ? `<div class="field"><div class="lbl">Contact</div><div class="val">${escapeHtml(r.contact_name)}</div></div>` : ''}
+ </div>
+ <div class="response-actions">
+ <button class="btn-edit" onclick='openModal(${JSON.stringify(r).replace(/'/g, "'")})'>✎ Edit</button>
+ <a href="/pitches.html?id=${r.id}" target="_blank">Pitch detail ↗</a>
+ ${r.linkedin ? `<a href="${r.linkedin}" target="_blank">LinkedIn ↗</a>` : ''}
+ ${r.email ? `<a href="mailto:${r.email}">Email ↗</a>` : ''}
+ </div>
+ </div>`;
+ }).join('');
+}
+
+function openModal(row) {
+ document.getElementById('m-id').value = row.id;
+ document.getElementById('m-name').textContent = row.name;
+ document.getElementById('m-status').value = row.status === 'won' || row.status === 'lost' ? row.status : 'replied';
+ document.getElementById('m-reply-text').value = row.reply_text || '';
+ document.getElementById('m-reply-channel').value = row.reply_channel || '';
+ document.getElementById('m-won-value').value = row.won_value_usd || '';
+ document.getElementById('m-followup-date').value = fmtDate(row.next_followup_at);
+ document.getElementById('m-followup-notes').value = row.followup_notes || '';
+ document.getElementById('m-lost-reason').value = row.lost_reason || '';
+ toggleLostRow();
+ document.getElementById('modal-bg').classList.add('open');
+}
+
+async function openModalById(id) {
+ const data = await fetch('/api/pitches?id=' + id).then(r => r.json());
+ const row = (data.rows || []).find(r => String(r.id) === String(id));
+ if (row) openModal(row);
+ else alert('Could not load pitch ' + id);
+}
+
+function closeModal() { document.getElementById('modal-bg').classList.remove('open'); }
+
+document.getElementById('m-status').addEventListener('change', toggleLostRow);
+function toggleLostRow() {
+ const isLost = document.getElementById('m-status').value === 'lost';
+ document.getElementById('m-lost-row').style.display = isLost ? 'block' : 'none';
+}
+
+async function saveResponse() {
+ const id = document.getElementById('m-id').value;
+ const body = {
+ status: document.getElementById('m-status').value,
+ reply_text: document.getElementById('m-reply-text').value || null,
+ reply_channel: document.getElementById('m-reply-channel').value || null,
+ won_value_usd: document.getElementById('m-won-value').value ? Number(document.getElementById('m-won-value').value) : null,
+ lost_reason: document.getElementById('m-lost-reason').value || null,
+ next_followup_at: document.getElementById('m-followup-date').value || null,
+ followup_notes: document.getElementById('m-followup-notes').value || null,
+ };
+ const res = await fetch('/api/pitches/' + id, {
+ method: 'PATCH',
+ headers: { 'Content-Type': 'application/json' },
+ body: JSON.stringify(body)
+ });
+ if (!res.ok) {
+ const t = await res.text();
+ alert('Save failed: ' + t);
+ return;
+ }
+ closeModal();
+ loadFunnel();
+ loadFollowups();
+ loadResponses();
+}
+
+document.querySelectorAll('.filter-bar .pill').forEach(p => {
+ p.addEventListener('click', () => {
+ document.querySelectorAll('.filter-bar .pill').forEach(b => b.classList.remove('active'));
+ p.classList.add('active');
+ currentFilter = p.dataset.filter;
+ loadResponses();
+ });
+});
+
+loadFunnel();
+loadFollowups();
+loadResponses();
+</script>
+</body>
+</html>
diff --git a/public/sales-nav-signup.html b/public/sales-nav-signup.html
index 1c4aff8..bea9487 100644
--- a/public/sales-nav-signup.html
+++ b/public/sales-nav-signup.html
@@ -93,6 +93,7 @@
<a href="/today.html">today</a>
<a href="/pitches.html">pitches</a>
<a href="/walk-route.html">walk route</a>
+ <a href="/responses.html">responses</a>
<a href="/linkedin.html">linkedin</a>
<a href="/sales-nav-signup.html" class="active">signup</a>
<span class="theme-toggle-host" style="display:inline-flex;align-items:center;margin-left:6px"></span>
diff --git a/public/today.html b/public/today.html
index b8a3b73..58e2b93 100644
--- a/public/today.html
+++ b/public/today.html
@@ -114,6 +114,7 @@
<a href="/linkedin.html">linkedin</a>
<a href="/pitches-map.html">map</a>
<a href="/walk-route.html">walk route</a>
+ <a href="/responses.html">responses</a>
<a href="/today.html" class="active">today</a>
<span class="theme-toggle-host" style="display:inline-flex;align-items:center;margin-left:6px"></span>
</nav>
diff --git a/public/walk-route.html b/public/walk-route.html
index d801515..fbc9377 100644
--- a/public/walk-route.html
+++ b/public/walk-route.html
@@ -155,6 +155,7 @@
<a href="/pitches.html">pitches</a>
<a href="/pitches-map.html">map</a>
<a href="/walk-route.html" class="active">walk route</a>
+ <a href="/responses.html">responses</a>
<a href="/linkedin.html">linkedin</a>
<span class="theme-toggle-host" style="display:inline-flex;align-items:center;margin-left:6px"></span>
</nav>
diff --git a/src/server/index.ts b/src/server/index.ts
index 819df4b..2979d14 100644
--- a/src/server/index.ts
+++ b/src/server/index.ts
@@ -58,6 +58,9 @@ const ADMIN_PATHS = [
/^\/pitches-map(\.html)?\/?$/i,
/^\/sales-nav-signup(\.html)?\/?$/i,
/^\/walk-route(\.html)?\/?$/i,
+ /^\/responses(\.html)?\/?$/i,
+ /^\/api\/responses(\/.*)?$/i,
+ /^\/api\/followups\/?$/i,
];
app.use((req, res, next) => {
if (!ADMIN_PATHS.some((re) => re.test(req.path))) return next();
@@ -830,21 +833,43 @@ app.get('/api/pitches/hot-list.csv', async (_req, res) => {
app.patch('/api/pitches/:id', async (req, res) => {
try {
const id = parseInt(req.params.id, 10);
- const allowed = ['status','subject','body','observation','why_dw_fits','outcome','notes','pitch_md_path','contact_name','email','phone','website','instagram','linkedin'];
+ const allowed = [
+ 'status','subject','body','observation','why_dw_fits','outcome','notes','pitch_md_path',
+ 'contact_name','email','phone','website','instagram','linkedin','outreach_channel',
+ 'reply_text','reply_channel','won_value_usd','lost_reason','next_followup_at','followup_notes'
+ ];
const sets: string[] = [];
const params: any[] = [];
for (const k of allowed) {
if (k in (req.body || {})) {
- params.push(req.body[k]);
+ let v = req.body[k];
+ // Coerce empty string → null for nullable optional fields so the UI
+ // can clear a field by submitting "".
+ if (v === '' && k !== 'status' && k !== 'pitch_type') v = null;
+ params.push(v);
sets.push(`${k} = $${params.length}`);
}
}
// Auto-stamp lifecycle timestamps when status advances
if (req.body?.status === 'scrubbed') sets.push('scrubbed_at = NOW()');
if (req.body?.status === 'approved') sets.push('approved_at = NOW()');
- if (req.body?.status === 'sent') sets.push('sent_at = NOW()');
- if (req.body?.status === 'replied') sets.push('replied_at = NOW()');
- if (req.body?.status === 'won' || req.body?.status === 'lost') sets.push('closed_at = NOW()');
+ if (req.body?.status === 'sent') sets.push('sent_at = COALESCE(sent_at, NOW())');
+ if (req.body?.status === 'replied') sets.push('replied_at = COALESCE(replied_at, NOW())');
+ if (req.body?.status === 'won' || req.body?.status === 'lost') sets.push('closed_at = COALESCE(closed_at, NOW())');
+ // If reply_text supplied without explicit status, still stamp replied_at
+ if (req.body?.reply_text && req.body?.status !== 'replied' && !sets.some(s => s.startsWith('replied_at'))) {
+ sets.push('replied_at = COALESCE(replied_at, NOW())');
+ }
+ // A reply implies a prior send happened — backfill sent_at if missing
+ // so v_response_funnel includes it. Same for won/lost.
+ if (
+ req.body?.status === 'replied' ||
+ req.body?.status === 'won' ||
+ req.body?.status === 'lost' ||
+ req.body?.reply_text
+ ) {
+ sets.push('sent_at = COALESCE(sent_at, NOW())');
+ }
if (!sets.length) return res.status(400).json({ error: 'no fields' });
params.push(id);
await query(`UPDATE pitches SET ${sets.join(', ')} WHERE id = $${params.length}`, params);
@@ -854,6 +879,64 @@ app.patch('/api/pitches/:id', async (req, res) => {
}
});
+// ─── Responses: who replied, who won, who lost, scheduled follow-ups ─
+// Powers /responses.html.
+app.get('/api/responses/funnel', async (_req, res) => {
+ try {
+ const r = await query(`SELECT * FROM v_response_funnel`);
+ const totalSent = r.rows.reduce((a, x) => a + Number(x.sent || 0), 0);
+ const totalReplied = r.rows.reduce((a, x) => a + Number(x.replied || 0), 0);
+ const totalWon = r.rows.reduce((a, x) => a + Number(x.won || 0), 0);
+ const totalLost = r.rows.reduce((a, x) => a + Number(x.lost || 0), 0);
+ const totalValue = r.rows.reduce((a, x) => a + Number(x.won_value_usd || 0), 0);
+ res.json({
+ by_channel: r.rows,
+ totals: {
+ sent: totalSent, replied: totalReplied, won: totalWon, lost: totalLost,
+ won_value_usd: totalValue,
+ reply_rate_pct: totalSent ? Math.round(1000 * totalReplied / totalSent) / 10 : null
+ }
+ });
+ } catch (e: any) {
+ res.status(500).json({ error: e.message });
+ }
+});
+
+app.get('/api/responses', async (req, res) => {
+ try {
+ const filter = String(req.query.filter || 'all'); // all | replied | won | lost
+ const where: string[] = ['p.replied_at IS NOT NULL OR p.status IN (\'won\',\'lost\')'];
+ if (filter === 'replied') where.push("p.status = 'replied' OR (p.replied_at IS NOT NULL AND p.status NOT IN ('won','lost'))");
+ if (filter === 'won') where.push("p.status = 'won'");
+ if (filter === 'lost') where.push("p.status = 'lost'");
+ const sql = `
+ SELECT p.id, p.business_id, b.name, b.address, b.city, p.pitch_type, p.status,
+ p.outreach_channel, p.reply_channel, p.reply_text,
+ p.sent_at, p.replied_at, p.closed_at, p.won_value_usd, p.lost_reason,
+ p.contact_name, p.email, p.phone, p.linkedin, p.next_followup_at,
+ p.followup_notes, p.notes, p.dw_proximity
+ FROM pitches p
+ JOIN businesses b ON b.id = p.business_id
+ WHERE ${where.join(' AND ')}
+ ORDER BY COALESCE(p.closed_at, p.replied_at) DESC NULLS LAST
+ LIMIT 300
+ `;
+ const r = await query(sql);
+ res.json({ count: r.rowCount, rows: r.rows });
+ } catch (e: any) {
+ res.status(500).json({ error: e.message });
+ }
+});
+
+app.get('/api/followups', async (_req, res) => {
+ try {
+ const r = await query(`SELECT * FROM v_followups_due LIMIT 200`);
+ res.json({ count: r.rowCount, rows: r.rows });
+ } catch (e: any) {
+ res.status(500).json({ error: e.message });
+ }
+});
+
// ─── Ghosts — businesses whose website 404s, DNS-fails, or won't load ─
// Powers /eulogy.html, the memorial wall.
let _ghostCache: any = null;
← 36dcc74 iter 48: ship /walk-route.html — printable door-by-door rout
·
back to Ventura Corridor
·
diag(signal): log timestamp+pid+ppid+uptime on SIGINT/SIGTER f3c43c0 →