← back to Ventura Corridor
iter 50: daily corridor activity snapshot + 14-day velocity chart on /today.html — migration 011 adds pitch_daily_snapshot table (snap_date,dim,bucket,count) + v_pitch_today_status helper; src/jobs/daily_snapshot.ts UPSERTs today's distribution by status/outreach_channel/dw_proximity/totals; npm run snapshot wired; com.steve.ventura-corridor-snapshot launchd job runs nightly at 23:57; backfilled 7 days for chart baseline; /api/snapshots?dim=&days= endpoint pivots into {dates, series}; SVG line chart on /today.html plots sent/replied/walked/won w/ dotted-line for won + delta-7 stat strip
368cc0c4a78bac0a8e508622c445ecef7850d733 · 2026-05-06 11:51:54 -0700 · SteveStudio2
Files touched
A db/migrations/011_pitch_daily_snapshot.sqlM package.jsonM public/today.htmlA src/jobs/daily_snapshot.ts
Diff
commit 368cc0c4a78bac0a8e508622c445ecef7850d733
Author: SteveStudio2 <steve@designerwallcoverings.com>
Date: Wed May 6 11:51:54 2026 -0700
iter 50: daily corridor activity snapshot + 14-day velocity chart on /today.html — migration 011 adds pitch_daily_snapshot table (snap_date,dim,bucket,count) + v_pitch_today_status helper; src/jobs/daily_snapshot.ts UPSERTs today's distribution by status/outreach_channel/dw_proximity/totals; npm run snapshot wired; com.steve.ventura-corridor-snapshot launchd job runs nightly at 23:57; backfilled 7 days for chart baseline; /api/snapshots?dim=&days= endpoint pivots into {dates, series}; SVG line chart on /today.html plots sent/replied/walked/won w/ dotted-line for won + delta-7 stat strip
---
db/migrations/011_pitch_daily_snapshot.sql | 38 +++++++++++++
package.json | 3 +-
public/today.html | 86 ++++++++++++++++++++++++++++++
src/jobs/daily_snapshot.ts | 47 ++++++++++++++++
4 files changed, 173 insertions(+), 1 deletion(-)
diff --git a/db/migrations/011_pitch_daily_snapshot.sql b/db/migrations/011_pitch_daily_snapshot.sql
new file mode 100644
index 0000000..5df6d66
--- /dev/null
+++ b/db/migrations/011_pitch_daily_snapshot.sql
@@ -0,0 +1,38 @@
+-- Migration 011 — daily activity snapshot
+-- Captures (snap_date, dimension_key, dimension_value, count) once a day so
+-- /today.html can plot a 30-day trend chart of corridor velocity.
+--
+-- A single table holds three "dimensions" — status, outreach_channel, dw_proximity —
+-- so we can render multiple sparklines from one source.
+
+CREATE TABLE IF NOT EXISTS pitch_daily_snapshot (
+ snap_date DATE NOT NULL,
+ dim TEXT NOT NULL, -- 'status' | 'outreach_channel' | 'dw_proximity' | 'totals'
+ bucket TEXT NOT NULL, -- e.g. 'draft', 'walk-in', 'same_block', 'all'
+ count INTEGER NOT NULL DEFAULT 0,
+ captured_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+ PRIMARY KEY (snap_date, dim, bucket)
+);
+
+CREATE INDEX IF NOT EXISTS idx_snapshot_dim ON pitch_daily_snapshot (dim, snap_date);
+
+-- Snapshot helper view so the snapshot script is a one-liner UPSERT
+CREATE OR REPLACE VIEW v_pitch_today_status AS
+SELECT 'status'::text AS dim, status AS bucket, count(*)::int AS count
+FROM pitches GROUP BY status
+UNION ALL
+SELECT 'outreach_channel'::text, COALESCE(outreach_channel, 'unspecified'), count(*)::int
+FROM pitches GROUP BY outreach_channel
+UNION ALL
+SELECT 'dw_proximity'::text, COALESCE(dw_proximity, 'unknown'), count(*)::int
+FROM pitches GROUP BY dw_proximity
+UNION ALL
+SELECT 'totals'::text, 'all', count(*)::int FROM pitches
+UNION ALL
+SELECT 'totals'::text, 'sent', count(*)::int FROM pitches WHERE sent_at IS NOT NULL
+UNION ALL
+SELECT 'totals'::text, 'replied', count(*)::int FROM pitches WHERE replied_at IS NOT NULL
+UNION ALL
+SELECT 'totals'::text, 'won', count(*)::int FROM pitches WHERE status = 'won'
+UNION ALL
+SELECT 'totals'::text, 'walked', count(*)::int FROM pitches WHERE outreach_channel = 'walk-in' AND sent_at IS NOT NULL;
diff --git a/package.json b/package.json
index 4e5d4e6..399974b 100644
--- a/package.json
+++ b/package.json
@@ -20,7 +20,8 @@
"enrich:chambers": "tsx src/enrich/chambers.ts",
"enrich:adsignals": "tsx src/enrich/ad_signals.ts",
"enrich:adstransparency": "tsx src/enrich/ads_transparency.ts",
- "migrate": "tsx db/migrate.ts"
+ "migrate": "tsx db/migrate.ts",
+ "snapshot": "tsx src/jobs/daily_snapshot.ts"
},
"dependencies": {
"dotenv": "^16.4.5",
diff --git a/public/today.html b/public/today.html
index 58e2b93..3fe9fdc 100644
--- a/public/today.html
+++ b/public/today.html
@@ -132,6 +132,15 @@
</div>
</section>
+<section class="trend" style="padding:24px 32px;border-bottom:1px solid var(--rule);background:var(--noir-rise)">
+ <div style="display:flex;justify-content:space-between;align-items:baseline;margin-bottom:16px;flex-wrap:wrap;gap:12px">
+ <h3 style="font-family:var(--serif);font-style:italic;font-weight:400;font-size:22px;color:var(--metal);margin:0">14-day corridor velocity</h3>
+ <div id="trend-stats" style="font-size:11px;letter-spacing:.18em;text-transform:uppercase;color:var(--ink-mute)">—</div>
+ </div>
+ <svg id="trend-chart" viewBox="0 0 800 160" preserveAspectRatio="none" style="width:100%;height:160px;background:var(--noir);border:1px solid var(--rule)"></svg>
+ <div id="trend-legend" style="display:flex;gap:16px;flex-wrap:wrap;margin-top:12px;font-size:10px;letter-spacing:.18em;text-transform:uppercase;color:var(--ink-mute)"></div>
+</section>
+
<div class="stack">
<section class="card">
@@ -296,6 +305,83 @@ loadInMail();
</div>`;
}).join('') || `<div class="empty">No research backlog! All top-tier pitches have contact info.</div>`;
})();
+
+// ─── 14-day velocity trend chart (totals: sent / replied / walked / won) ───
+(async () => {
+ const data = await fetch('/api/snapshots?dim=totals&days=14').then(r => r.json()).catch(() => null);
+ if (!data || !data.dates || data.dates.length === 0) {
+ document.getElementById('trend-stats').textContent = 'no snapshot data yet';
+ return;
+ }
+ const series = data.series || {};
+ const dates = data.dates;
+ // We chart the action metrics (skip 'all' since it's static at 5,386)
+ const KEYS = [
+ { key: 'sent', color: '#b89968', label: 'sent' },
+ { key: 'replied', color: '#d4b683', label: 'replied' },
+ { key: 'walked', color: '#6a9b73', label: 'walked' },
+ { key: 'won', color: '#d4b683', label: 'won', dashed: true },
+ ];
+ const W = 800, H = 160, PAD_L = 36, PAD_R = 12, PAD_T = 12, PAD_B = 24;
+ const innerW = W - PAD_L - PAD_R;
+ const innerH = H - PAD_T - PAD_B;
+
+ let maxY = 0;
+ for (const k of KEYS) {
+ if (series[k.key]) maxY = Math.max(maxY, ...series[k.key]);
+ }
+ if (maxY < 5) maxY = 5; // floor to keep zero-baseline readable
+
+ const xStep = dates.length > 1 ? innerW / (dates.length - 1) : innerW;
+
+ const svg = document.getElementById('trend-chart');
+ let svgInner = '';
+
+ // Y-axis grid + labels
+ for (const yFrac of [0, 0.25, 0.5, 0.75, 1]) {
+ const y = PAD_T + innerH * (1 - yFrac);
+ const v = Math.round(maxY * yFrac);
+ svgInner += `<line x1="${PAD_L}" y1="${y}" x2="${W - PAD_R}" y2="${y}" stroke="#2a2622" stroke-width="0.5"/>`;
+ svgInner += `<text x="${PAD_L - 6}" y="${y + 3}" text-anchor="end" fill="#888475" font-size="9" font-family="JetBrains Mono">${v}</text>`;
+ }
+
+ // X-axis labels (first / mid / last)
+ for (const i of [0, Math.floor(dates.length / 2), dates.length - 1]) {
+ if (dates[i]) {
+ const x = PAD_L + xStep * i;
+ const lbl = dates[i].slice(5); // "MM-DD"
+ svgInner += `<text x="${x}" y="${H - 6}" text-anchor="middle" fill="#888475" font-size="9" font-family="JetBrains Mono">${lbl}</text>`;
+ }
+ }
+
+ // Series lines
+ for (const k of KEYS) {
+ const arr = series[k.key];
+ if (!arr) continue;
+ const pts = arr.map((v, i) => `${PAD_L + xStep * i},${PAD_T + innerH * (1 - v / maxY)}`).join(' ');
+ const dash = k.dashed ? 'stroke-dasharray="4 3"' : '';
+ svgInner += `<polyline points="${pts}" fill="none" stroke="${k.color}" stroke-width="1.5" ${dash}/>`;
+ // End-point dot
+ const last = arr[arr.length - 1];
+ const lastX = PAD_L + xStep * (arr.length - 1);
+ const lastY = PAD_T + innerH * (1 - last / maxY);
+ svgInner += `<circle cx="${lastX}" cy="${lastY}" r="3" fill="${k.color}"/>`;
+ }
+ svg.innerHTML = svgInner;
+
+ // Legend
+ document.getElementById('trend-legend').innerHTML = KEYS.map(k => {
+ const last = (series[k.key] || []).slice(-1)[0] ?? 0;
+ return `<span style="display:inline-flex;align-items:center;gap:6px"><span style="display:inline-block;width:14px;height:2px;background:${k.color};${k.dashed ? 'background-image:linear-gradient(to right,'+k.color+' 50%,transparent 50%);background-size:6px 2px' : ''}"></span>${k.label} <b style="color:var(--ink);font-family:var(--mono)">${last}</b></span>`;
+ }).join('');
+
+ // Stat strip
+ const sentNow = series.sent ? series.sent.slice(-1)[0] : 0;
+ const sentPrev = series.sent && series.sent.length >= 8 ? series.sent[series.sent.length - 8] : 0;
+ const delta7 = sentNow - sentPrev;
+ document.getElementById('trend-stats').innerHTML =
+ `last 7 days: <b style="color:var(--metal-glow);font-family:var(--mono)">+${delta7}</b> sent · <b style="color:var(--metal);font-family:var(--mono)">${dates.length}</b> days captured`;
+})();
</script>
</body>
</html>
diff --git a/src/jobs/daily_snapshot.ts b/src/jobs/daily_snapshot.ts
new file mode 100644
index 0000000..2b248d9
--- /dev/null
+++ b/src/jobs/daily_snapshot.ts
@@ -0,0 +1,47 @@
+/**
+ * Daily corridor activity snapshot.
+ *
+ * Run nightly (or any time — UPSERT keys on snap_date) to capture today's
+ * pitch counts grouped by status / outreach_channel / dw_proximity / totals
+ * into pitch_daily_snapshot. The /today.html chart reads this table.
+ *
+ * $ npx tsx src/jobs/daily_snapshot.ts # snapshot today (default)
+ * $ npx tsx src/jobs/daily_snapshot.ts 2026-05-06 # snapshot a specific date
+ */
+import 'dotenv/config';
+import { pool, query } from '../../db/pool.ts';
+
+const TARGET = process.argv[2] || new Date().toISOString().slice(0, 10);
+
+async function main() {
+ if (!/^\d{4}-\d{2}-\d{2}$/.test(TARGET)) {
+ throw new Error(`bad date arg: ${TARGET} — expected YYYY-MM-DD`);
+ }
+
+ const r = await query(`SELECT dim, bucket, count FROM v_pitch_today_status`);
+ let upserted = 0;
+ for (const row of r.rows) {
+ await query(
+ `
+ INSERT INTO pitch_daily_snapshot (snap_date, dim, bucket, count, captured_at)
+ VALUES ($1, $2, $3, $4, NOW())
+ ON CONFLICT (snap_date, dim, bucket)
+ DO UPDATE SET count = EXCLUDED.count, captured_at = NOW()
+ `,
+ [TARGET, row.dim, row.bucket, row.count]
+ );
+ upserted++;
+ }
+ console.log(
+ `[daily_snapshot] ${TARGET}: ${upserted} buckets upserted (${r.rows
+ .filter((x) => x.dim === 'totals')
+ .map((x) => `${x.bucket}=${x.count}`)
+ .join(' · ')})`
+ );
+ await pool.end();
+}
+
+main().catch((e) => {
+ console.error('[daily_snapshot] failed:', e);
+ process.exit(1);
+});
← f3c43c0 diag(signal): log timestamp+pid+ppid+uptime on SIGINT/SIGTER
·
back to Ventura Corridor
·
iter 51: pitch_event_log audit trail + responses CSV export 69ba8fb →