← back to Ventura Claw Leads
scripts/weekly-digest.js
187 lines
#!/usr/bin/env node
// Weekly digest — Monday-morning summary email per claimed business with
// activity in the past 7 days. Sent via Purelymail SMTP, audited to
// comms_send_audit, suppression-scrubbed, RFC 8058 unsubscribe headers.
//
// Default mode: --dry-run (no email, just log). Pass --live to actually send.
// Cron suggestion (after first manual --live verification):
// 0 8 * * 1 /usr/bin/node /root/Projects/ventura-claw-leads/scripts/weekly-digest.js --live >> /var/log/vcl-weekly-digest.log 2>&1
//
// Skip list:
// - Businesses with no email on file
// - Businesses with zero activity in the past 7 days (nothing to summarize)
// - Recipients on the suppression list (consumer or business unsubscribed)
const path = require('node:path');
require('dotenv').config({ path: path.resolve(__dirname, '..', '.env') });
const db = require('../lib/db');
const { sendEmail } = require('../lib/email');
const compliance = require('../lib/compliance');
const LIVE = process.argv.includes('--live');
const DRY = !LIVE || process.argv.includes('--dry-run');
const LIMIT = (() => {
const i = process.argv.indexOf('--limit');
return i >= 0 ? parseInt(process.argv[i + 1], 10) || 1000 : 1000;
})();
const PUBLIC_URL = (process.env.PUBLIC_URL || 'https://leads.venturaclaw.com').replace(/\/+$/, '');
const CAMPAIGN = 'weekly_digest';
function escapeHtml(s) { return String(s || '').replace(/&/g, '&').replace(/</g, '<').replace(/>/g, '>').replace(/"/g, '"'); }
function buildEmail({ business, stats, leads, unsubscribeUrl }) {
const subject = `Your Ventura Claw week — ${stats.msgs_7d} new message${stats.msgs_7d === 1 ? '' : 's'}, ${stats.views_7d} profile view${stats.views_7d === 1 ? '' : 's'}`;
const profileUrl = `${PUBLIC_URL}/business/${encodeURIComponent(business.slug)}`;
const dashboardUrl = `${PUBLIC_URL}/admin`;
const leadRows = leads.slice(0, 5).map(l => `
<tr>
<td style="padding:8px 0;color:#666;width:90px;font-size:12px">${new Date(l.created_at).toLocaleDateString('en-US', { weekday:'short', month:'short', day:'numeric' })}</td>
<td style="padding:8px 0;font-size:13px"><strong>${escapeHtml(l.consumer_name)}</strong> — <a href="mailto:${escapeHtml(l.consumer_email)}" style="color:#0e0e0e">${escapeHtml(l.consumer_email)}</a></td>
</tr>`).join('');
const html = `<div style="font-family:Georgia,serif;max-width:560px;margin:0 auto;padding:32px 24px;color:#0e0e0e">
<p style="font-size:11px;letter-spacing:0.16em;text-transform:uppercase;color:#b8860b;margin:0 0 8px">This week on Ventura Claw</p>
<h1 style="font-size:24px;font-weight:400;margin:0 0 16px;line-height:1.2">${escapeHtml(business.business_name)}</h1>
<p style="font-size:14px;line-height:1.55;color:#666;margin:0 0 24px">Quick recap of ${escapeHtml(business.business_name)}'s last 7 days on Ventura Claw — what came in, who messaged, how the listing performed.</p>
<table style="width:100%;border-collapse:collapse;margin:0 0 28px;border-top:1px solid #ddd;border-bottom:1px solid #ddd">
<tr>
<td style="padding:14px 8px;width:33%;vertical-align:top;border-right:1px solid #eee">
<p style="font-family:Georgia,serif;font-size:32px;color:#b8860b;margin:0;line-height:1">${stats.msgs_7d}</p>
<p style="font-size:11px;letter-spacing:0.06em;text-transform:uppercase;color:#888;margin:4px 0 0">new messages</p>
</td>
<td style="padding:14px 8px;width:33%;vertical-align:top;border-right:1px solid #eee">
<p style="font-family:Georgia,serif;font-size:32px;color:#b8860b;margin:0;line-height:1">${stats.views_7d}</p>
<p style="font-size:11px;letter-spacing:0.06em;text-transform:uppercase;color:#888;margin:4px 0 0">profile views</p>
</td>
<td style="padding:14px 8px;width:33%;vertical-align:top">
<p style="font-family:Georgia,serif;font-size:32px;color:${stats.msgs_unread > 0 ? '#dc2626' : '#888'};margin:0;line-height:1">${stats.msgs_unread}</p>
<p style="font-size:11px;letter-spacing:0.06em;text-transform:uppercase;color:#888;margin:4px 0 0">unread${stats.msgs_unread > 0 ? ' — triage' : ''}</p>
</td>
</tr>
</table>
${leads.length > 0 ? `
<h2 style="font-family:Georgia,serif;font-size:18px;font-weight:500;margin:0 0 12px">This week's new messages</h2>
<table style="width:100%;border-collapse:collapse;margin:0 0 24px">${leadRows}</table>
${leads.length > 5 ? `<p style="font-size:12px;color:#888;margin:0 0 24px">… and ${leads.length - 5} more.</p>` : ''}
` : ''}
<p style="margin:24px 0">
<a href="${dashboardUrl}" style="display:inline-block;background:#0e0e0e;color:#fff;padding:12px 22px;text-decoration:none;font-size:14px;letter-spacing:0.04em;border-radius:2px">Open dashboard →</a>
</p>
<p style="font-size:13px;color:#666;line-height:1.55;margin:24px 0 0">Your live profile: <a href="${profileUrl}" style="color:#666">${profileUrl.replace(/^https?:\/\//, '')}</a></p>
${compliance.complianceFooter({ campaign: CAMPAIGN, unsubscribeUrl })}
</div>`;
const text = `Your Ventura Claw week — ${business.business_name}
${stats.msgs_7d} new messages · ${stats.views_7d} profile views · ${stats.msgs_unread} unread
Open dashboard: ${dashboardUrl}
Live profile: ${profileUrl}
---
Unsubscribe: ${unsubscribeUrl}`;
return { subject, html, text };
}
(async () => {
if (!DRY) await compliance.assertSendCompliance({ campaign: CAMPAIGN });
// Pull every claimed business with email + at least some activity in past 7 days.
const candidates = await db.many(`
SELECT
b.id, b.slug, b.business_name, b.email,
(SELECT COUNT(*)::int FROM business_interest
WHERE business_id = b.id AND created_at > now() - interval '7 days') AS msgs_7d,
(SELECT COUNT(*)::int FROM business_interest
WHERE business_id = b.id AND read_at IS NULL) AS msgs_unread,
(SELECT COALESCE(SUM(n), 0)::int FROM business_views
WHERE business_id = b.id AND day > CURRENT_DATE - interval '7 days') AS views_7d
FROM businesses b
WHERE b.status = 'active'
AND b.claim_status IN ('self','claimed')
AND b.email IS NOT NULL AND b.email != ''
ORDER BY b.id ASC
LIMIT $1
`, [LIMIT]);
console.log(`[weekly-digest] ${candidates.length} claimed business(es) with email${DRY ? ' (dry run)' : ''}`);
let sent = 0, skipped_no_activity = 0, suppressed = 0, errors = 0;
for (const biz of candidates) {
if (biz.msgs_7d === 0 && biz.views_7d === 0) {
skipped_no_activity++;
continue; // Nothing to report — silent skip, don't spam.
}
const recipient = String(biz.email).trim().toLowerCase();
const supr = await compliance.isSuppressed({ channel: 'email', identifier: recipient });
if (supr) {
suppressed++;
console.log(` · ${recipient} → SUPPRESSED, skipping`);
continue;
}
const recentLeads = biz.msgs_7d > 0 ? await db.many(`
SELECT consumer_name, consumer_email, created_at
FROM business_interest
WHERE business_id = $1 AND created_at > now() - interval '7 days'
ORDER BY created_at DESC LIMIT 10
`, [biz.id]) : [];
const unsubToken = DRY
? 'dry-run-token'
: await compliance.mintUnsubscribeToken({
channel: 'email', identifier: recipient,
campaign: CAMPAIGN, businessId: biz.id
});
const unsubscribeUrl = `${PUBLIC_URL}/unsubscribe?t=${unsubToken}`;
const { subject, html, text } = buildEmail({
business: { slug: biz.slug, business_name: biz.business_name },
stats: { msgs_7d: biz.msgs_7d, views_7d: biz.views_7d, msgs_unread: biz.msgs_unread },
leads: recentLeads,
unsubscribeUrl
});
if (DRY) {
console.log(` · ${recipient} → would send: "${subject}" (${biz.msgs_7d}m / ${biz.views_7d}v / ${biz.msgs_unread} unread)`);
sent++;
continue;
}
const result = await sendEmail({
to: recipient,
subject, html, text,
extraHeaders: compliance.listUnsubscribeHeader(unsubscribeUrl)
});
if (result && result.ok) {
sent++;
await compliance.recordAudit({
channel: 'email', campaign: CAMPAIGN, recipient, businessId: biz.id,
decision: 'sent', subject, messageId: result.messageId || null,
payload: { msgs_7d: biz.msgs_7d, views_7d: biz.views_7d, msgs_unread: biz.msgs_unread }
});
console.log(` · ${recipient} → sent (msg ${(result.messageId || '').slice(0, 28)}…)`);
} else {
errors++;
await compliance.recordAudit({
channel: 'email', campaign: CAMPAIGN, recipient, businessId: biz.id,
decision: 'failed', reason: (result && result.error) || 'unknown',
subject, messageId: null,
payload: { msgs_7d: biz.msgs_7d, smtp_code: result && result.code }
});
console.error(` · ${recipient} → ERROR ${(result && result.code) || ''}`);
}
}
console.log(`[weekly-digest] done — sent=${sent} suppressed=${suppressed} skipped_no_activity=${skipped_no_activity} errors=${errors}`);
})().catch(err => {
console.error('[weekly-digest] FATAL:', err.message);
process.exit(1);
}).finally(() => {
setTimeout(() => process.exit(0), 200);
});