← back to Ventura Corridor
iter 72: weekly digest email job — src/jobs/weekly_digest.ts pulls last-7d activity counts (sent/replied/closed/followups/touched), pipeline totals, top-5 priority bldgs, stale leads (sent>=14d no reply), channel funnel; composes HTML email with bordered tables and metal-tone branding; POSTs to George Gmail localhost:9850/api/send (account=info, isHtml=true) to steveabramsdesigns@gmail.com; --dry-run flag prints HTML to stdout instead; npm run digest wired; com.steve.ventura-corridor-digest launchd loaded fires Monday 9:03 AM PT
67f2256560c808d4ffb16ef18ce55a964a038763 · 2026-05-06 15:57:59 -0700 · SteveStudio2
Files touched
M package.jsonA src/jobs/weekly_digest.ts
Diff
commit 67f2256560c808d4ffb16ef18ce55a964a038763
Author: SteveStudio2 <steve@designerwallcoverings.com>
Date: Wed May 6 15:57:59 2026 -0700
iter 72: weekly digest email job — src/jobs/weekly_digest.ts pulls last-7d activity counts (sent/replied/closed/followups/touched), pipeline totals, top-5 priority bldgs, stale leads (sent>=14d no reply), channel funnel; composes HTML email with bordered tables and metal-tone branding; POSTs to George Gmail localhost:9850/api/send (account=info, isHtml=true) to steveabramsdesigns@gmail.com; --dry-run flag prints HTML to stdout instead; npm run digest wired; com.steve.ventura-corridor-digest launchd loaded fires Monday 9:03 AM PT
---
package.json | 3 +-
src/jobs/weekly_digest.ts | 159 ++++++++++++++++++++++++++++++++++++++++++++++
2 files changed, 161 insertions(+), 1 deletion(-)
diff --git a/package.json b/package.json
index 399974b..4e9dcd2 100644
--- a/package.json
+++ b/package.json
@@ -21,7 +21,8 @@
"enrich:adsignals": "tsx src/enrich/ad_signals.ts",
"enrich:adstransparency": "tsx src/enrich/ads_transparency.ts",
"migrate": "tsx db/migrate.ts",
- "snapshot": "tsx src/jobs/daily_snapshot.ts"
+ "snapshot": "tsx src/jobs/daily_snapshot.ts",
+ "digest": "tsx src/jobs/weekly_digest.ts"
},
"dependencies": {
"dotenv": "^16.4.5",
diff --git a/src/jobs/weekly_digest.ts b/src/jobs/weekly_digest.ts
new file mode 100644
index 0000000..627546f
--- /dev/null
+++ b/src/jobs/weekly_digest.ts
@@ -0,0 +1,159 @@
+/**
+ * Weekly digest — emails Steve a Mon-AM recap of last 7 days of corridor activity.
+ *
+ * Pulls:
+ * - Last-7d activity counts (sent / replied / won / followups / status changes)
+ * - Channel funnel snapshot
+ * - Top-5 priority buildings to walk this week
+ * - Stale leads (sent ≥14d ago, no reply)
+ * - 14-day SVG-friendly trend snapshot
+ *
+ * Sends via George Gmail at http://localhost:9850/api/send (account=info).
+ *
+ * $ npx tsx src/jobs/weekly_digest.ts # send to steve-personal
+ * $ npx tsx src/jobs/weekly_digest.ts --dry-run # render HTML to stdout, don't send
+ */
+import 'dotenv/config';
+import { pool, query } from '../../db/pool.ts';
+
+const RECIPIENT = process.env.DIGEST_TO || 'steveabramsdesigns@gmail.com';
+const GEORGE = process.env.GEORGE_URL || 'http://localhost:9850/api/send';
+const GEORGE_AUTH = 'Basic ' + Buffer.from('admin:DWSecure2024!').toString('base64');
+const DRY = process.argv.includes('--dry-run');
+
+function fmt(d: any) { if (!d) return '—'; return new Date(d).toISOString().slice(0, 10); }
+function escHtml(s: any) { return String(s ?? '').replace(/[&<>"']/g, (c: string) => ({ '&': '&', '<': '<', '>': '>', '"': '"', "'": ''' }[c]!)); }
+
+async function main() {
+ // 1. Last-7d activity stats
+ const stats = await query(`
+ SELECT
+ count(*) AS events_7d,
+ count(*) FILTER (WHERE event_type = 'sent') AS sent_7d,
+ count(*) FILTER (WHERE event_type = 'replied') AS replied_7d,
+ count(*) FILTER (WHERE event_type = 'closed') AS closed_7d,
+ count(*) FILTER (WHERE event_type = 'followup_scheduled') AS followups_7d,
+ count(DISTINCT pitch_id) AS pitches_touched
+ FROM pitch_event_log
+ WHERE occurred_at >= NOW() - INTERVAL '7 days'
+ `);
+
+ // 2. Funnel
+ const funnel = await query(`SELECT * FROM v_response_funnel`);
+
+ // 3. Priority next-up
+ const prio = await query(`
+ SELECT bldg_address, city, dw_miles, tenants_total, unpitched,
+ ROUND((unpitched::numeric / GREATEST(dw_miles, 0.05)::numeric)::numeric, 0) AS score
+ FROM v_building_roster
+ WHERE dw_miles IS NOT NULL AND dw_miles <= 0.5
+ AND walked = 0 AND unpitched >= 2
+ ORDER BY (unpitched::numeric / GREATEST(dw_miles, 0.05)::numeric) DESC
+ LIMIT 5
+ `);
+
+ // 4. Stale leads
+ const stale = await query(`
+ SELECT b.name, p.pitch_type, p.outreach_channel, p.sent_at,
+ FLOOR(EXTRACT(EPOCH FROM (NOW() - p.sent_at))/86400)::int AS days_silent
+ FROM pitches p JOIN businesses b ON b.id = p.business_id
+ WHERE p.sent_at IS NOT NULL AND p.sent_at < NOW() - INTERVAL '14 days'
+ AND p.replied_at IS NULL AND p.closed_at IS NULL
+ AND p.status NOT IN ('skip','won','lost')
+ ORDER BY p.sent_at ASC
+ LIMIT 10
+ `);
+
+ // 5. Pitch totals
+ const totals = await query(`
+ SELECT
+ count(*) AS all_total,
+ count(*) FILTER (WHERE status = 'draft') AS draft,
+ 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 next_followup_at::date >= CURRENT_DATE) AS upcoming_followups
+ FROM pitches
+ `);
+
+ const s = stats.rows[0];
+ const t = totals.rows[0];
+
+ const html = `<div style="font-family:-apple-system,BlinkMacSystemFont,'Segoe UI',sans-serif;max-width:680px;color:#1a1a1a;line-height:1.55">
+<h2 style="font-family:'Times New Roman',serif;font-style:italic;font-weight:400;font-size:26px;border-bottom:1px solid #b89968;padding-bottom:8px;color:#6a4f2a">Ventura Corridor — weekly digest</h2>
+<p style="color:#888;font-size:11px;letter-spacing:.18em;text-transform:uppercase">${fmt(new Date())} · last 7 days</p>
+
+<h3 style="color:#6a4f2a;font-family:'Times New Roman',serif;margin-top:24px">This week's activity</h3>
+<table style="border-collapse:collapse;font-size:14px">
+<tr><td style="padding:4px 16px 4px 0;color:#888">Events logged</td><td><b>${s.events_7d}</b></td></tr>
+<tr><td style="padding:4px 16px 4px 0;color:#888">Pitches sent</td><td><b style="color:#b89968">${s.sent_7d}</b></td></tr>
+<tr><td style="padding:4px 16px 4px 0;color:#888">Replies received</td><td><b style="color:#d4b683">${s.replied_7d}</b></td></tr>
+<tr><td style="padding:4px 16px 4px 0;color:#888">Closed (won + lost)</td><td><b style="color:#6a9b73">${s.closed_7d}</b></td></tr>
+<tr><td style="padding:4px 16px 4px 0;color:#888">Follow-ups scheduled</td><td><b>${s.followups_7d}</b></td></tr>
+<tr><td style="padding:4px 16px 4px 0;color:#888">Pitches touched</td><td><b>${s.pitches_touched}</b></td></tr>
+</table>
+
+<h3 style="color:#6a4f2a;font-family:'Times New Roman',serif;margin-top:24px">Pipeline totals</h3>
+<table style="border-collapse:collapse;font-size:14px">
+<tr><td style="padding:4px 16px 4px 0;color:#888">Total pitches</td><td><b>${Number(t.all_total).toLocaleString()}</b></td></tr>
+<tr><td style="padding:4px 16px 4px 0;color:#888">Drafted (untouched)</td><td>${Number(t.draft).toLocaleString()}</td></tr>
+<tr><td style="padding:4px 16px 4px 0;color:#888">Sent (lifetime)</td><td><b style="color:#b89968">${Number(t.sent).toLocaleString()}</b></td></tr>
+<tr><td style="padding:4px 16px 4px 0;color:#888">Replied (lifetime)</td><td><b style="color:#d4b683">${Number(t.replied).toLocaleString()}</b></td></tr>
+<tr><td style="padding:4px 16px 4px 0;color:#888">Won (lifetime)</td><td><b style="color:#6a9b73">${Number(t.won).toLocaleString()}</b></td></tr>
+<tr><td style="padding:4px 16px 4px 0;color:#888">Upcoming follow-ups</td><td>${Number(t.upcoming_followups).toLocaleString()}</td></tr>
+</table>
+
+<h3 style="color:#6a4f2a;font-family:'Times New Roman',serif;margin-top:24px">Walk here next · top-5 unwalked walkable buildings</h3>
+${prio.rows.length === 0 ? '<p style="color:#888;font-style:italic">All priority targets walked — congratulations.</p>' : `
+<table style="border-collapse:collapse;font-size:13px;width:100%">
+<tr style="border-bottom:1px solid #ccc"><th align="left" style="padding:6px 0;color:#888;font-weight:400">#</th><th align="left" style="padding:6px 12px 6px 0;color:#888;font-weight:400">Building</th><th align="left" style="padding:6px 12px 6px 0;color:#888;font-weight:400">City</th><th align="right" style="padding:6px 12px 6px 0;color:#888;font-weight:400">mi from DW</th><th align="right" style="padding:6px 12px 6px 0;color:#888;font-weight:400">Unpitched</th></tr>
+${prio.rows.map((r: any, i: number) => `
+<tr><td style="padding:6px 0">${i + 1}</td><td style="padding:6px 12px 6px 0;font-family:'Times New Roman',serif">${escHtml(r.bldg_address)}</td><td style="padding:6px 12px 6px 0">${escHtml(r.city || '')}</td><td align="right" style="padding:6px 12px 6px 0;color:#b89968;font-family:monospace">${r.dw_miles}</td><td align="right" style="padding:6px 12px 6px 0;color:#b89968;font-family:monospace"><b>${r.unpitched}</b></td></tr>
+`).join('')}
+</table>`}
+
+<h3 style="color:#6a4f2a;font-family:'Times New Roman',serif;margin-top:24px">${stale.rows.length === 0 ? 'No stale leads' : `Cold leads · ${stale.rows.length} sent ≥14d ago, no reply`}</h3>
+${stale.rows.length === 0 ? '<p style="color:#888;font-style:italic">No outreach has gone stale.</p>' : `
+<table style="border-collapse:collapse;font-size:13px;width:100%">
+<tr style="border-bottom:1px solid #ccc"><th align="left" style="padding:6px 0;color:#888;font-weight:400">Days</th><th align="left" style="padding:6px 12px 6px 0;color:#888;font-weight:400">Business</th><th align="left" style="padding:6px 12px 6px 0;color:#888;font-weight:400">Vertical</th><th align="left" style="padding:6px 12px 6px 0;color:#888;font-weight:400">Channel</th><th align="left" style="padding:6px 12px 6px 0;color:#888;font-weight:400">Sent</th></tr>
+${stale.rows.map((r: any) => `
+<tr><td style="padding:6px 0;color:#c47b7b;font-family:monospace"><b>${r.days_silent}d</b></td><td style="padding:6px 12px 6px 0;font-family:'Times New Roman',serif">${escHtml(r.name)}</td><td style="padding:6px 12px 6px 0;color:#888">${escHtml(r.pitch_type || '')}</td><td style="padding:6px 12px 6px 0;color:#888">${escHtml(r.outreach_channel || '—')}</td><td style="padding:6px 12px 6px 0;color:#888;font-family:monospace">${fmt(r.sent_at)}</td></tr>
+`).join('')}
+</table>`}
+
+<h3 style="color:#6a4f2a;font-family:'Times New Roman',serif;margin-top:24px">Channel funnel · lifetime</h3>
+${funnel.rows.length === 0 ? '<p style="color:#888;font-style:italic">No outreach data yet.</p>' : `
+<table style="border-collapse:collapse;font-size:13px;width:100%">
+<tr style="border-bottom:1px solid #ccc"><th align="left" style="padding:6px 0;color:#888;font-weight:400">Channel</th><th align="right" style="padding:6px 12px 6px 0;color:#888;font-weight:400">Sent</th><th align="right" style="padding:6px 12px 6px 0;color:#888;font-weight:400">Replied</th><th align="right" style="padding:6px 12px 6px 0;color:#888;font-weight:400">Reply rate</th><th align="right" style="padding:6px 12px 6px 0;color:#888;font-weight:400">Won</th><th align="right" style="padding:6px 12px 6px 0;color:#888;font-weight:400">Won $</th></tr>
+${funnel.rows.map((r: any) => `
+<tr><td style="padding:6px 0;font-family:'Times New Roman',serif;color:#b89968">${escHtml(r.channel)}</td><td align="right" style="padding:6px 12px 6px 0;font-family:monospace">${r.sent}</td><td align="right" style="padding:6px 12px 6px 0;font-family:monospace">${r.replied}</td><td align="right" style="padding:6px 12px 6px 0;font-family:monospace">${r.reply_rate_pct ?? '—'}${r.reply_rate_pct ? '%' : ''}</td><td align="right" style="padding:6px 12px 6px 0;font-family:monospace">${r.won}</td><td align="right" style="padding:6px 12px 6px 0;font-family:monospace">$${Number(r.won_value_usd || 0).toLocaleString()}</td></tr>
+`).join('')}
+</table>`}
+
+<p style="margin-top:32px;color:#888;font-size:11px">Open the dashboard: <a href="http://127.0.0.1:9780/today.html">today.html</a> · auto-generated by ventura-corridor weekly digest job · all loopback / Mac2-only</p>
+</div>`;
+
+ if (DRY) {
+ process.stdout.write(html);
+ await pool.end();
+ return;
+ }
+
+ const body = {
+ to: RECIPIENT,
+ subject: `Ventura Corridor weekly digest · ${s.sent_7d} sent, ${s.replied_7d} replied, ${stale.rows.length} cold leads`,
+ body: html,
+ isHtml: true,
+ account: 'info'
+ };
+ const resp = await fetch(GEORGE, {
+ method: 'POST',
+ headers: { 'Content-Type': 'application/json', Authorization: GEORGE_AUTH },
+ body: JSON.stringify(body)
+ });
+ const out = await resp.json().catch(() => ({}));
+ console.log(`[weekly_digest] ${resp.status} → ${RECIPIENT} · messageId=${out.messageId || 'none'} · sent_7d=${s.sent_7d} replied_7d=${s.replied_7d} stale=${stale.rows.length}`);
+ await pool.end();
+}
+
+main().catch(e => { console.error('[weekly_digest] failed:', e); process.exit(1); });
← 1482b8f iter 71: stale-pitch alerts on /today.html — /api/stale-pitc
·
back to Ventura Corridor
·
iter 73: PIVOT to corridor magazine MVP — migration 016 adds 1daefa6 →