← back to Ventura Corridor
src/jobs/weekly_digest.ts
160 lines
/**
* 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); });