← back to Ventura Corridor
iter 51: pitch_event_log audit trail + responses CSV export + cycle-time analytics — migration 012 adds pitch_event_log table + AFTER INSERT/UPDATE trigger that captures status_change/channel_change/sent/replied/closed/reply_text/followup_scheduled/created events; v_pitch_cycle_time view; /api/pitches/:id/timeline endpoint; /api/responses.csv (Content-Disposition attachment, PT timezone, CSV-safe escapes); /api/responses/cycle-time aggregates avg/median hours-to-reply + days-to-close per channel; /responses.html gets Download-CSV pill + collapsible Lifecycle-timeline section in edit modal; ADMIN_PATHS regex tightened to match /api/responses.csv
69ba8fbb26cfbd9f57163d92dbe239053a13cd8a · 2026-05-06 12:06:15 -0700 · SteveStudio2
Files touched
A db/migrations/012_pitch_event_log.sqlM public/responses.htmlM src/server/index.ts
Diff
commit 69ba8fbb26cfbd9f57163d92dbe239053a13cd8a
Author: SteveStudio2 <steve@designerwallcoverings.com>
Date: Wed May 6 12:06:15 2026 -0700
iter 51: pitch_event_log audit trail + responses CSV export + cycle-time analytics — migration 012 adds pitch_event_log table + AFTER INSERT/UPDATE trigger that captures status_change/channel_change/sent/replied/closed/reply_text/followup_scheduled/created events; v_pitch_cycle_time view; /api/pitches/:id/timeline endpoint; /api/responses.csv (Content-Disposition attachment, PT timezone, CSV-safe escapes); /api/responses/cycle-time aggregates avg/median hours-to-reply + days-to-close per channel; /responses.html gets Download-CSV pill + collapsible Lifecycle-timeline section in edit modal; ADMIN_PATHS regex tightened to match /api/responses.csv
---
db/migrations/012_pitch_event_log.sql | 85 +++++++++++++++++++++++++++++++++++
public/responses.html | 33 ++++++++++++++
src/server/index.ts | 84 +++++++++++++++++++++++++++++++++-
3 files changed, 201 insertions(+), 1 deletion(-)
diff --git a/db/migrations/012_pitch_event_log.sql b/db/migrations/012_pitch_event_log.sql
new file mode 100644
index 0000000..c8d7560
--- /dev/null
+++ b/db/migrations/012_pitch_event_log.sql
@@ -0,0 +1,85 @@
+-- Migration 012 — pitch_event_log audit trail
+-- Every change to a pitch's lifecycle (status, outreach_channel, sent/replied/closed/won)
+-- writes a row to pitch_event_log via trigger. This unblocks future analytics
+-- (cycle time, time-to-first-reply, time-to-close), legal-compliance recall
+-- ("when did Steve first contact this firm?"), and lifecycle visualization.
+
+CREATE TABLE IF NOT EXISTS pitch_event_log (
+ id BIGSERIAL PRIMARY KEY,
+ pitch_id BIGINT NOT NULL REFERENCES pitches(id) ON DELETE CASCADE,
+ event_type TEXT NOT NULL, -- 'status_change' | 'channel_change' | 'sent' | 'replied' | 'closed' | 'won' | 'lost' | 'reply_text' | 'followup_scheduled' | 'created'
+ old_value TEXT,
+ new_value TEXT,
+ source TEXT, -- typically NULL (we don't track operator yet); reserved
+ occurred_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
+);
+
+CREATE INDEX IF NOT EXISTS idx_pel_pitch ON pitch_event_log (pitch_id, occurred_at DESC);
+CREATE INDEX IF NOT EXISTS idx_pel_event_type ON pitch_event_log (event_type, occurred_at DESC);
+CREATE INDEX IF NOT EXISTS idx_pel_occurred_at ON pitch_event_log (occurred_at DESC);
+
+-- Trigger: fire on UPDATE of any tracked column
+CREATE OR REPLACE FUNCTION trg_pitch_event_log_fn() RETURNS trigger AS $$
+BEGIN
+ IF TG_OP = 'INSERT' THEN
+ INSERT INTO pitch_event_log (pitch_id, event_type, new_value)
+ VALUES (NEW.id, 'created', NEW.status);
+ RETURN NEW;
+ END IF;
+
+ IF NEW.status IS DISTINCT FROM OLD.status THEN
+ INSERT INTO pitch_event_log (pitch_id, event_type, old_value, new_value)
+ VALUES (NEW.id, 'status_change', OLD.status, NEW.status);
+ END IF;
+
+ IF NEW.outreach_channel IS DISTINCT FROM OLD.outreach_channel THEN
+ INSERT INTO pitch_event_log (pitch_id, event_type, old_value, new_value)
+ VALUES (NEW.id, 'channel_change', OLD.outreach_channel, NEW.outreach_channel);
+ END IF;
+
+ IF NEW.sent_at IS DISTINCT FROM OLD.sent_at AND NEW.sent_at IS NOT NULL THEN
+ INSERT INTO pitch_event_log (pitch_id, event_type, new_value)
+ VALUES (NEW.id, 'sent', COALESCE(NEW.outreach_channel, 'unspecified'));
+ END IF;
+
+ IF NEW.replied_at IS DISTINCT FROM OLD.replied_at AND NEW.replied_at IS NOT NULL THEN
+ INSERT INTO pitch_event_log (pitch_id, event_type, new_value)
+ VALUES (NEW.id, 'replied', COALESCE(NEW.reply_channel, NEW.outreach_channel, 'unspecified'));
+ END IF;
+
+ IF NEW.closed_at IS DISTINCT FROM OLD.closed_at AND NEW.closed_at IS NOT NULL THEN
+ INSERT INTO pitch_event_log (pitch_id, event_type, new_value)
+ VALUES (NEW.id, 'closed', NEW.status);
+ END IF;
+
+ IF NEW.reply_text IS DISTINCT FROM OLD.reply_text AND NEW.reply_text IS NOT NULL THEN
+ INSERT INTO pitch_event_log (pitch_id, event_type, new_value)
+ VALUES (NEW.id, 'reply_text', LEFT(NEW.reply_text, 200));
+ END IF;
+
+ IF NEW.next_followup_at IS DISTINCT FROM OLD.next_followup_at AND NEW.next_followup_at IS NOT NULL THEN
+ INSERT INTO pitch_event_log (pitch_id, event_type, new_value)
+ VALUES (NEW.id, 'followup_scheduled', NEW.next_followup_at::text);
+ END IF;
+
+ RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+
+DROP TRIGGER IF EXISTS trg_pitch_event_log ON pitches;
+CREATE TRIGGER trg_pitch_event_log
+ AFTER INSERT OR UPDATE ON pitches
+ FOR EACH ROW EXECUTE FUNCTION trg_pitch_event_log_fn();
+
+-- View: cycle-time analytics (time from sent to first reply / close)
+CREATE OR REPLACE VIEW v_pitch_cycle_time AS
+SELECT
+ p.id, p.business_id, b.name, p.outreach_channel, p.status,
+ p.sent_at, p.replied_at, p.closed_at,
+ EXTRACT(EPOCH FROM (p.replied_at - p.sent_at))/3600 AS hours_to_reply,
+ EXTRACT(EPOCH FROM (p.closed_at - p.sent_at))/86400 AS days_to_close,
+ EXTRACT(EPOCH FROM (p.closed_at - p.replied_at))/86400 AS days_reply_to_close
+FROM pitches p
+JOIN businesses b ON b.id = p.business_id
+WHERE p.sent_at IS NOT NULL
+ORDER BY p.sent_at DESC;
diff --git a/public/responses.html b/public/responses.html
index bc79fe5..e897d8f 100644
--- a/public/responses.html
+++ b/public/responses.html
@@ -204,6 +204,8 @@
<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>
+ <span style="flex:1"></span>
+ <a href="/api/responses.csv" class="pill" style="text-decoration:none;color:var(--metal);border-color:var(--metal)">⬇ Download CSV</a>
</div>
<main>
@@ -273,6 +275,10 @@
<input id="m-followup-notes" placeholder="Drop sample, mention X">
</div>
</div>
+ <details style="margin-top:18px;border-top:1px solid var(--rule);padding-top:14px">
+ <summary style="cursor:pointer;font-size:9px;letter-spacing:.26em;text-transform:uppercase;color:var(--ink-mute)">Lifecycle timeline</summary>
+ <div id="m-timeline" style="margin-top:10px;font-family:var(--mono);font-size:10px;line-height:1.6;color:var(--ink-mute);max-height:200px;overflow:auto"></div>
+ </details>
<div class="modal-actions">
<button class="cancel" onclick="closeModal()">Cancel</button>
<button class="save" onclick="saveResponse()">Save</button>
@@ -382,6 +388,33 @@ function openModal(row) {
document.getElementById('m-lost-reason').value = row.lost_reason || '';
toggleLostRow();
document.getElementById('modal-bg').classList.add('open');
+ loadTimeline(row.id);
+}
+
+const EVENT_LABELS = {
+ status_change: 'STATUS', channel_change: 'CHANNEL',
+ sent: 'SENT', replied: 'REPLIED', closed: 'CLOSED',
+ reply_text: 'REPLY-TEXT', followup_scheduled: 'FOLLOW-UP',
+ created: 'CREATED'
+};
+async function loadTimeline(pitchId) {
+ const el = document.getElementById('m-timeline');
+ el.textContent = 'loading…';
+ try {
+ const data = await fetch('/api/pitches/' + pitchId + '/timeline').then(r => r.json());
+ if (!data.events || data.events.length === 0) {
+ el.textContent = 'no events yet';
+ return;
+ }
+ el.innerHTML = data.events.map(ev => {
+ const ts = new Date(ev.occurred_at).toLocaleString('en-US', { month: 'short', day: '2-digit', hour: '2-digit', minute: '2-digit' });
+ const lbl = EVENT_LABELS[ev.event_type] || ev.event_type.toUpperCase();
+ const arrow = ev.old_value ? `${escapeHtml(ev.old_value)} → ${escapeHtml(ev.new_value || '')}` : escapeHtml(ev.new_value || '');
+ return `<div><span style="color:var(--metal)">${ts}</span> <span style="color:var(--metal-glow);min-width:80px;display:inline-block">${lbl}</span> ${arrow}</div>`;
+ }).join('');
+ } catch (e) {
+ el.textContent = 'error: ' + e.message;
+ }
}
async function openModalById(id) {
diff --git a/src/server/index.ts b/src/server/index.ts
index 561a3fa..51eb744 100644
--- a/src/server/index.ts
+++ b/src/server/index.ts
@@ -64,7 +64,7 @@ const ADMIN_PATHS = [
/^\/sales-nav-signup(\.html)?\/?$/i,
/^\/walk-route(\.html)?\/?$/i,
/^\/responses(\.html)?\/?$/i,
- /^\/api\/responses(\/.*)?$/i,
+ /^\/api\/responses(\.csv|\/.*)?$/i,
/^\/api\/followups\/?$/i,
/^\/api\/snapshots\/?$/i,
];
@@ -885,6 +885,88 @@ app.patch('/api/pitches/:id', async (req, res) => {
}
});
+// ─── Timeline / audit log: every lifecycle event for one pitch ──────
+app.get('/api/pitches/:id/timeline', async (req, res) => {
+ try {
+ const id = parseInt(req.params.id, 10);
+ if (!Number.isFinite(id)) return res.status(400).json({ error: 'bad id' });
+ const r = await query(
+ `SELECT id, event_type, old_value, new_value, occurred_at
+ FROM pitch_event_log
+ WHERE pitch_id = $1
+ ORDER BY occurred_at DESC, id DESC
+ LIMIT 200`,
+ [id]
+ );
+ res.json({ count: r.rowCount, events: r.rows });
+ } catch (e: any) {
+ res.status(500).json({ error: e.message });
+ }
+});
+
+// ─── CSV export of all responses (for accountant / external CRM) ────
+app.get('/api/responses.csv', async (_req, res) => {
+ try {
+ const r = await query(`
+ SELECT p.id, b.name, b.address, b.city, b.zip, p.pitch_type, p.status,
+ p.outreach_channel, p.reply_channel,
+ to_char(p.sent_at AT TIME ZONE 'America/Los_Angeles', 'YYYY-MM-DD HH24:MI') AS sent_pt,
+ to_char(p.replied_at AT TIME ZONE 'America/Los_Angeles', 'YYYY-MM-DD HH24:MI') AS replied_pt,
+ to_char(p.closed_at AT TIME ZONE 'America/Los_Angeles', 'YYYY-MM-DD HH24:MI') AS closed_pt,
+ p.won_value_usd, p.lost_reason,
+ p.contact_name, p.email, p.phone, p.linkedin,
+ to_char(p.next_followup_at, 'YYYY-MM-DD') AS next_followup,
+ p.followup_notes,
+ REPLACE(REPLACE(COALESCE(p.reply_text, ''), CHR(10), ' '), CHR(13), ' ') AS reply_text,
+ REPLACE(REPLACE(COALESCE(p.notes, ''), CHR(10), ' '), CHR(13), ' ') AS notes
+ FROM pitches p
+ JOIN businesses b ON b.id = p.business_id
+ WHERE p.replied_at IS NOT NULL OR p.status IN ('won','lost')
+ ORDER BY COALESCE(p.closed_at, p.replied_at) DESC NULLS LAST
+ `);
+ const headers = [
+ 'pitch_id','business','address','city','zip','pitch_type','status',
+ 'outreach_channel','reply_channel','sent_pt','replied_pt','closed_pt',
+ 'won_value_usd','lost_reason','contact_name','email','phone','linkedin',
+ 'next_followup','followup_notes','reply_text','notes'
+ ];
+ const rows = r.rows.map((row: any) => headers.map(h => {
+ const k = h === 'pitch_id' ? 'id' : h === 'business' ? 'name' : h;
+ const v = row[k];
+ if (v === null || v === undefined) return '';
+ const s = String(v);
+ return /[",\n]/.test(s) ? `"${s.replace(/"/g, '""')}"` : s;
+ }).join(','));
+ const csv = [headers.join(','), ...rows].join('\n');
+ res.setHeader('Content-Type', 'text/csv; charset=utf-8');
+ res.setHeader('Content-Disposition', `attachment; filename="ventura-corridor-responses-${new Date().toISOString().slice(0,10)}.csv"`);
+ res.send(csv);
+ } catch (e: any) {
+ res.status(500).json({ error: e.message });
+ }
+});
+
+// ─── Cycle-time analytics: how long sent → reply / sent → close ─────
+app.get('/api/responses/cycle-time', async (_req, res) => {
+ try {
+ const r = await query(`
+ SELECT
+ outreach_channel AS channel,
+ count(*) AS pitches,
+ ROUND(AVG(hours_to_reply)::numeric, 1) AS avg_hours_to_reply,
+ ROUND(percentile_cont(0.5) WITHIN GROUP (ORDER BY hours_to_reply)::numeric, 1) AS median_hours_to_reply,
+ ROUND(AVG(days_to_close)::numeric, 1) AS avg_days_to_close,
+ ROUND(percentile_cont(0.5) WITHIN GROUP (ORDER BY days_to_close)::numeric, 1) AS median_days_to_close
+ FROM v_pitch_cycle_time
+ GROUP BY outreach_channel
+ ORDER BY pitches DESC
+ `);
+ res.json({ by_channel: r.rows });
+ } catch (e: any) {
+ res.status(500).json({ error: e.message });
+ }
+});
+
// ─── Daily-snapshot trend feed ──────────────────────────────────────
// Powers the trend chart on /today.html. Returns last N days of pitch state.
app.get('/api/snapshots', async (req, res) => {
← 368cc0c iter 50: daily corridor activity snapshot + 14-day velocity
·
back to Ventura Corridor
·
iter 52: postcard preview + Lob template generator — public/ dbf656b →