← back to Norma
app/api/intelligence/digest/route.ts
443 lines
import { NextRequest, NextResponse } from 'next/server';
import nodemailer from 'nodemailer';
import { query } from '@/lib/db';
import { requireRole } from '@/lib/require-role';
import { getOrgId } from '@/lib/orgId';
/**
* POST /api/intelligence/digest
* Generates and sends the weekly intelligence email digest to an org.
*
* Body: { org_id: UUID, week_start?: DATE (YYYY-MM-DD) }
*
* Sections:
* 1. Top Contacts This Week (from weekly_intelligence)
* 2. Notable News Triggers (from news_items last 7 days)
* 3. POWER Score Changes (from org_activity type=score_change last 7 days)
*
* Returns: { success: true, sent_to: email }
*/
// ── Types ─────────────────────────────────────────────────────────────────────
interface WeeklyRec {
id: string;
entity_type: string;
entity_name: string;
power_score: string | number;
reasoning: string;
priority: number;
contact_strategy: string;
}
interface NewsItem {
id: string;
headline: string;
outlet: string | null;
published_at: string | null;
url: string | null;
}
interface ActivityItem {
entity_name: string | null;
entity_type: string | null;
summary: string;
delta: { previous?: number; current?: number; delta?: number } | null;
created_at: string;
}
// ── Color palette (dark theme) ────────────────────────────────────────────────
const COLORS = {
bg: '#0C0F1A',
surface: '#131829',
elevated: '#1C2237',
primary: '#10b981',
secondary: '#f59e0b',
danger: '#f43f5e',
text: '#f0f0f5',
textSub: '#a1a1b5',
border: '#2A3048',
};
const TYPE_BADGE: Record<string, { bg: string; label: string }> = {
politician: { bg: '#6366f1', label: 'Politician' },
journalist: { bg: '#f59e0b', label: 'Journalist' },
foundation: { bg: '#10b981', label: 'Foundation' },
organization: { bg: '#06b6d4', label: 'Organization' },
};
const STRATEGY_LABEL: Record<string, string> = {
legislative: 'Legislative Outreach',
media: 'Media Outreach',
coalition: 'Coalition Building',
general: 'General Contact',
};
// ── HTML builder ──────────────────────────────────────────────────────────────
function buildEmailHtml(
orgName: string,
weekStart: string,
recs: WeeklyRec[],
newsItems: NewsItem[],
activities: ActivityItem[],
): string {
// ── Top Contacts section ──
const contactRows = recs.map((rec, i) => {
const badge = TYPE_BADGE[rec.entity_type] ?? { bg: '#6b7280', label: rec.entity_type };
const strategy = STRATEGY_LABEL[rec.contact_strategy] ?? rec.contact_strategy;
const score = Math.round(Number(rec.power_score));
// Score bar (0-100)
const barPct = Math.max(0, Math.min(100, score));
const barColor =
barPct >= 70 ? COLORS.primary :
barPct >= 40 ? COLORS.secondary :
COLORS.danger;
return `
<tr>
<td style="padding:12px 16px;border-bottom:1px solid ${COLORS.border};vertical-align:top;">
<div style="font-size:11px;color:${COLORS.textSub};margin-bottom:2px;">#${i + 1}</div>
<div style="font-size:16px;font-weight:600;color:${COLORS.text};">${escHtml(rec.entity_name)}</div>
<span style="display:inline-block;margin-top:4px;padding:2px 8px;border-radius:4px;font-size:11px;font-weight:600;background:${badge.bg};color:#fff;">${badge.label}</span>
<span style="display:inline-block;margin-top:4px;margin-left:6px;padding:2px 8px;border-radius:4px;font-size:11px;background:${COLORS.elevated};color:${COLORS.textSub};">${escHtml(strategy)}</span>
</td>
<td style="padding:12px 16px;border-bottom:1px solid ${COLORS.border};vertical-align:top;min-width:120px;">
<div style="font-size:22px;font-weight:700;color:${barColor};">${score}<span style="font-size:13px;color:${COLORS.textSub};">/100</span></div>
<div style="margin-top:4px;height:6px;border-radius:3px;background:${COLORS.elevated};">
<div style="height:6px;border-radius:3px;width:${barPct}%;background:${barColor};"></div>
</div>
<div style="margin-top:3px;font-size:11px;color:${COLORS.textSub};">POWER Score</div>
</td>
<td style="padding:12px 16px;border-bottom:1px solid ${COLORS.border};vertical-align:top;">
<div style="font-size:13px;color:${COLORS.textSub};line-height:1.6;">${escHtml(rec.reasoning)}</div>
<a href="http://45.61.58.125:7400" style="display:inline-block;margin-top:8px;padding:5px 12px;border-radius:6px;background:${COLORS.primary};color:#fff;font-size:12px;font-weight:600;text-decoration:none;">View in Norma →</a>
</td>
</tr>`;
}).join('');
// ── News Triggers section ──
const newsRows = newsItems.slice(0, 8).map((n) => {
const dateStr = n.published_at
? new Date(n.published_at).toLocaleDateString('en-US', { month: 'short', day: 'numeric' })
: '';
const outlet = n.outlet ? `<span style="color:${COLORS.textSub};font-size:12px;"> · ${escHtml(n.outlet)}</span>` : '';
const link = n.url
? `<a href="${escHtml(n.url)}" style="color:${COLORS.primary};text-decoration:none;font-size:13px;font-weight:500;">${escHtml(n.headline)}</a>`
: `<span style="color:${COLORS.text};font-size:13px;">${escHtml(n.headline)}</span>`;
return `
<tr>
<td style="padding:10px 16px;border-bottom:1px solid ${COLORS.border};">
${link}${outlet}
${dateStr ? `<div style="font-size:11px;color:${COLORS.textSub};margin-top:2px;">${dateStr}</div>` : ''}
</td>
</tr>`;
}).join('');
// ── Score Changes section ──
const activityRows = activities.slice(0, 10).map((a) => {
const delta = a.delta?.delta ?? 0;
const prev = a.delta?.previous ?? 0;
const curr = a.delta?.current ?? 0;
const color = delta > 0 ? COLORS.primary : COLORS.danger;
const sign = delta > 0 ? '+' : '';
const entityLabel = a.entity_name
? `<strong style="color:${COLORS.text};">${escHtml(a.entity_name)}</strong>`
: '';
const typeLabel = a.entity_type
? `<span style="color:${COLORS.textSub};font-size:12px;"> (${escHtml(a.entity_type)})</span>`
: '';
return `
<tr>
<td style="padding:10px 16px;border-bottom:1px solid ${COLORS.border};">
${entityLabel}${typeLabel}
<div style="margin-top:4px;font-size:12px;color:${COLORS.textSub};">${escHtml(a.summary)}</div>
</td>
<td style="padding:10px 16px;border-bottom:1px solid ${COLORS.border};text-align:right;white-space:nowrap;vertical-align:top;">
<span style="font-size:18px;font-weight:700;color:${color};">${sign}${Math.round(delta)}</span>
<div style="font-size:11px;color:${COLORS.textSub};">${Math.round(prev)} → ${Math.round(curr)}</div>
</td>
</tr>`;
}).join('');
const noActivity = activities.length === 0
? `<tr><td style="padding:16px;color:${COLORS.textSub};font-style:italic;">No significant score changes this week.</td></tr>`
: '';
const noNews = newsItems.length === 0
? `<tr><td style="padding:16px;color:${COLORS.textSub};font-style:italic;">No recent news items found.</td></tr>`
: '';
const noRecs = recs.length === 0
? `<tr><td colspan="3" style="padding:16px;color:${COLORS.textSub};font-style:italic;">No recommendations generated yet. Run the intelligence cron first.</td></tr>`
: '';
return `<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<title>Norma Weekly Intelligence Digest — ${escHtml(orgName)}</title>
</head>
<body style="margin:0;padding:0;background:${COLORS.bg};font-family:-apple-system,BlinkMacSystemFont,'Segoe UI',Roboto,sans-serif;color:${COLORS.text};">
<!-- Header -->
<table width="100%" cellpadding="0" cellspacing="0" style="background:linear-gradient(135deg,${COLORS.surface} 0%,#0f172a 100%);">
<tr>
<td style="padding:40px 32px 32px;">
<div style="font-size:11px;font-weight:700;letter-spacing:2px;color:${COLORS.primary};text-transform:uppercase;margin-bottom:8px;">Norma Power Intelligence</div>
<div style="font-size:28px;font-weight:700;color:${COLORS.text};">Weekly Intelligence Digest</div>
<div style="font-size:14px;color:${COLORS.textSub};margin-top:6px;">
${escHtml(orgName)} · Week of ${weekStart}
</div>
</td>
</tr>
</table>
<!-- Body wrapper -->
<table width="100%" cellpadding="0" cellspacing="0">
<tr>
<td style="padding:32px;">
<!-- Section 1: Top Contacts -->
<div style="margin-bottom:8px;font-size:11px;font-weight:700;letter-spacing:1.5px;color:${COLORS.primary};text-transform:uppercase;">
Top Contacts This Week
</div>
<div style="margin-bottom:4px;font-size:13px;color:${COLORS.textSub};">
Ranked by POWER Score — highest leverage opportunities for ${escHtml(orgName)}.
</div>
<table width="100%" cellpadding="0" cellspacing="0" style="border:1px solid ${COLORS.border};border-radius:10px;overflow:hidden;margin-bottom:32px;">
<thead>
<tr style="background:${COLORS.elevated};">
<th style="padding:10px 16px;text-align:left;font-size:11px;font-weight:600;color:${COLORS.textSub};text-transform:uppercase;letter-spacing:1px;">Contact</th>
<th style="padding:10px 16px;text-align:left;font-size:11px;font-weight:600;color:${COLORS.textSub};text-transform:uppercase;letter-spacing:1px;">Score</th>
<th style="padding:10px 16px;text-align:left;font-size:11px;font-weight:600;color:${COLORS.textSub};text-transform:uppercase;letter-spacing:1px;">Why Contact Now</th>
</tr>
</thead>
<tbody>
${noRecs || contactRows}
</tbody>
</table>
<!-- Section 2: News Triggers -->
<div style="margin-bottom:8px;font-size:11px;font-weight:700;letter-spacing:1.5px;color:${COLORS.secondary};text-transform:uppercase;">
Notable News Triggers
</div>
<div style="margin-bottom:4px;font-size:13px;color:${COLORS.textSub};">
Recent news items from the past 7 days that may require action.
</div>
<table width="100%" cellpadding="0" cellspacing="0" style="border:1px solid ${COLORS.border};border-radius:10px;overflow:hidden;margin-bottom:32px;">
<tbody>
${noNews || newsRows}
</tbody>
</table>
<!-- Section 3: POWER Score Changes -->
<div style="margin-bottom:8px;font-size:11px;font-weight:700;letter-spacing:1.5px;color:${COLORS.textSub};text-transform:uppercase;">
POWER Score Changes
</div>
<div style="margin-bottom:4px;font-size:13px;color:${COLORS.textSub};">
Significant influence shifts detected in your network this week.
</div>
<table width="100%" cellpadding="0" cellspacing="0" style="border:1px solid ${COLORS.border};border-radius:10px;overflow:hidden;margin-bottom:32px;">
<thead>
<tr style="background:${COLORS.elevated};">
<th style="padding:10px 16px;text-align:left;font-size:11px;font-weight:600;color:${COLORS.textSub};text-transform:uppercase;letter-spacing:1px;">Entity</th>
<th style="padding:10px 16px;text-align:right;font-size:11px;font-weight:600;color:${COLORS.textSub};text-transform:uppercase;letter-spacing:1px;">Change</th>
</tr>
</thead>
<tbody>
${noActivity || activityRows}
</tbody>
</table>
<!-- Footer -->
<table width="100%" cellpadding="0" cellspacing="0" style="border-top:1px solid ${COLORS.border};margin-top:16px;">
<tr>
<td style="padding:24px 0 8px;">
<a href="http://45.61.58.125:7400" style="display:inline-block;padding:12px 28px;background:${COLORS.primary};color:#fff;font-size:14px;font-weight:600;border-radius:8px;text-decoration:none;">
Open Power Intelligence Tab →
</a>
</td>
</tr>
<tr>
<td style="padding:16px 0 0;">
<div style="font-size:11px;color:${COLORS.textSub};">
This digest was generated automatically by the Norma Intelligence Engine for ${escHtml(orgName)}.<br/>
Manage digest settings at <a href="http://45.61.58.125:7400" style="color:${COLORS.primary};">http://45.61.58.125:7400</a> → Settings → Intelligence.
</div>
</td>
</tr>
</table>
</td>
</tr>
</table>
</body>
</html>`;
}
function escHtml(str: string | null | undefined): string {
if (!str) return '';
return str
.replace(/&/g, '&')
.replace(/</g, '<')
.replace(/>/g, '>')
.replace(/"/g, '"')
.replace(/'/g, ''');
}
// ── Route handler ─────────────────────────────────────────────────────────────
export async function POST(request: NextRequest) {
const auth = requireRole(request, 'admin', 'staff');
if (auth instanceof NextResponse) return auth;
let body: { org_id?: string; week_start?: string } = {};
try {
body = await request.json();
} catch {
return NextResponse.json({ error: 'Invalid JSON body' }, { status: 400 });
}
const org_id = getOrgId(request) || body.org_id;
const { week_start } = body;
if (!org_id) {
return NextResponse.json({ error: 'org_id is required' }, { status: 400 });
}
try {
// ── Resolve week_start ──
let weekStart = week_start;
if (!weekStart) {
const now = new Date();
const dayOfWeek = now.getDay();
const diffToMon = dayOfWeek === 0 ? -6 : 1 - dayOfWeek;
const monday = new Date(now);
monday.setDate(now.getDate() + diffToMon);
monday.setHours(0, 0, 0, 0);
weekStart = monday.toISOString().slice(0, 10);
}
// ── Fetch org + digest email ──
const { rows: orgRows } = await query<{
id: string;
org_name: string;
contact_email: string;
api_connections: Record<string, unknown> | null;
}>(
`SELECT id, org_name, contact_email, api_connections
FROM nonprofit_accounts
WHERE id = $1::uuid`,
[org_id],
);
if (orgRows.length === 0) {
return NextResponse.json({ error: 'Organization not found' }, { status: 404 });
}
const org = orgRows[0];
const intelConfig = (org.api_connections?.intelligence ?? {}) as {
digest_enabled?: boolean;
digest_email?: string;
};
const digestEmail = intelConfig.digest_email || org.contact_email;
if (!digestEmail) {
return NextResponse.json({ error: 'No digest email configured for this org' }, { status: 400 });
}
// ── Fetch top 10 weekly_intelligence for org + week ──
const { rows: recs } = await query<WeeklyRec>(
`SELECT id, entity_type, entity_name, power_score, reasoning, priority, contact_strategy
FROM weekly_intelligence
WHERE org_id = $1::uuid
AND week_start = $2::date
ORDER BY priority ASC, power_score DESC
LIMIT 10`,
[org_id, weekStart],
);
// ── Fetch recent news_items (last 7 days) ──
const { rows: newsItems } = await query<NewsItem>(
`SELECT id, headline, outlet, published_at, url
FROM news_items
WHERE (org_id = $1::uuid OR org_id IS NULL)
AND published_at >= NOW() - INTERVAL '7 days'
ORDER BY relevance_score DESC NULLS LAST, published_at DESC
LIMIT 20`,
[org_id],
);
// ── Fetch score_change org_activity (last 7 days) ──
const { rows: activities } = await query<ActivityItem>(
`SELECT entity_name, entity_type, summary, delta, created_at
FROM org_activity
WHERE org_id = $1::uuid
AND activity_type = 'score_change'
AND created_at >= NOW() - INTERVAL '7 days'
ORDER BY ABS((delta->>'delta')::numeric) DESC NULLS LAST, created_at DESC
LIMIT 10`,
[org_id],
);
// ── Build HTML ──
const html = buildEmailHtml(org.org_name, weekStart, recs, newsItems, activities);
// ── Send via nodemailer ──
const smtpHost = process.env.SMTP_HOST;
const smtpPort = process.env.SMTP_PORT;
const smtpUser = process.env.SMTP_USER;
const smtpPass = process.env.SMTP_PASS;
if (!smtpHost || !smtpPort || !smtpUser || !smtpPass) {
// No SMTP configured — return the HTML for preview instead
return NextResponse.json({
success: false,
error: 'SMTP not configured. Set SMTP_HOST, SMTP_PORT, SMTP_USER, SMTP_PASS to enable sending.',
preview_html: html,
would_send_to: digestEmail,
recs_count: recs.length,
news_count: newsItems.length,
activity_count: activities.length,
}, { status: 200 });
}
const transporter = nodemailer.createTransport({
host: smtpHost,
port: parseInt(smtpPort, 10),
secure: parseInt(smtpPort, 10) === 465,
auth: { user: smtpUser, pass: smtpPass },
});
await transporter.sendMail({
from: `"Norma Intelligence" <${smtpUser}>`,
to: digestEmail,
subject: `Weekly Intelligence Digest — ${org.org_name} (${weekStart})`,
html,
text: `Weekly Intelligence Digest for ${org.org_name}\nWeek of ${weekStart}\n\n` +
`Top ${recs.length} recommended contacts — open http://45.61.58.125:7400 for details.`,
});
return NextResponse.json({
success: true,
sent_to: digestEmail,
week_start: weekStart,
recs_count: recs.length,
news_count: newsItems.length,
activity_count: activities.length,
});
} catch (err) {
console.error('[api/intelligence/digest] POST error:', (err as Error).message);
return NextResponse.json({ error: 'Failed to generate or send digest' }, { status: 500 });
}
}