← back to Norma
app/api/ai-chat/route.ts
380 lines
import { NextRequest, NextResponse } from 'next/server';
import { requireRole } from '@/lib/require-role';
import { query } from '@/lib/db';
import { checkRateLimit } from '@/lib/rate-limit';
import { getOrgId } from '@/lib/orgId';
const GEMINI_API_KEY = process.env.GEMINI_API_KEY;
/* ─── GET /api/ai-chat — return last 100 messages (oldest first) ────────── */
export async function GET(request: NextRequest) {
const auth = requireRole(request, 'admin', 'staff');
if (auth instanceof NextResponse) return auth;
try {
const orgId = auth.role === 'admin' ? getOrgId(request) : auth.orgId;
const result = orgId
? await query(
`SELECT id, role, content, created_at
FROM chat_messages
WHERE org_id = $1
ORDER BY created_at DESC
LIMIT 100`,
[orgId],
)
: await query(
`SELECT id, role, content, created_at
FROM chat_messages
ORDER BY created_at DESC
LIMIT 100`,
);
// Reverse so oldest message is first
const messages = result.rows.reverse();
return NextResponse.json({ messages });
} catch (err) {
console.error('[ai-chat] GET error:', (err as Error).message);
return NextResponse.json(
{ error: 'Failed to load chat history' },
{ status: 500 },
);
}
}
/* ─── POST /api/ai-chat — send message, get AI reply ───────────────────── */
export async function POST(request: NextRequest) {
const auth = requireRole(request, 'admin', 'staff');
if (auth instanceof NextResponse) return auth;
const { limited, resetIn } = checkRateLimit(request);
if (limited) {
return NextResponse.json(
{ error: 'Rate limit exceeded', retryAfter: Math.ceil(resetIn / 1000) },
{ status: 429 },
);
}
try {
const { message } = await request.json();
if (!message || typeof message !== 'string' || !message.trim()) {
return NextResponse.json(
{ error: 'message is required' },
{ status: 400 },
);
}
const orgId = auth.role === 'admin' ? getOrgId(request) : auth.orgId;
/* ── Gather live DB context in parallel ──────────────────────────────── */
const safeQuery = async <T = Record<string, unknown>>(
sql: string,
fallback: T,
): Promise<T> => {
try {
const r = await query(sql);
return (r.rows[0] as T) ?? fallback;
} catch {
return fallback;
}
};
const safeQueryRows = async (sql: string): Promise<Record<string, unknown>[]> => {
try {
const r = await query(sql);
return r.rows;
} catch {
return [];
}
};
/* ── Detect if user is asking about historical emails/workshops ──────── */
const lowerMsg = message.trim().toLowerCase();
const wantsArchive = /\b(histor|past email|old email|previous email|workshop|template|campaign|use.*(email|workshop)|show.*(email|workshop)|find.*(email|workshop)|reference|reuse|adapt|based on)/i.test(lowerMsg);
const safeQueryP = async <T = Record<string, unknown>>(
sql: string,
params: unknown[],
fallback: T,
): Promise<T> => {
try {
const r = await query(sql, params);
return (r.rows[0] as T) ?? fallback;
} catch {
return fallback;
}
};
const safeQueryRowsP = async (sql: string, params: unknown[]): Promise<Record<string, unknown>[]> => {
try {
const r = await query(sql, params);
return r.rows;
} catch {
return [];
}
};
const [draftStats, grantStats, partnerStats, petitionStats, newsStats, recentDrafts, archiveStats, archiveList] =
await Promise.all([
safeQueryP(
`SELECT COUNT(*) as total,
COUNT(*) FILTER (WHERE status='sent') as sent,
COUNT(*) FILTER (WHERE status='draft') as drafts
FROM drafts WHERE ($1::uuid IS NULL OR org_id = $1::uuid)`,
[orgId],
{ total: 0, sent: 0, drafts: 0 },
),
safeQueryP(
`SELECT COUNT(*) as total,
COUNT(*) FILTER (WHERE status='submitted') as submitted
FROM grants WHERE ($1::uuid IS NULL OR org_id = $1::uuid)`,
[orgId],
{ total: 0, submitted: 0 },
),
safeQuery(
`SELECT COUNT(*) as total,
COUNT(*) FILTER (WHERE status='active') as active
FROM partners`,
{ total: 0, active: 0 },
),
safeQueryP(
`SELECT COUNT(*) as total FROM petitions WHERE ($1::uuid IS NULL OR org_id = $1::uuid)`,
[orgId],
{ total: 0 },
),
safeQueryP(
`SELECT COUNT(*) as total,
COUNT(*) FILTER (WHERE created_at > NOW() - INTERVAL '7 days') as recent
FROM news_items WHERE ($1::uuid IS NULL OR org_id = $1::uuid)`,
[orgId],
{ total: 0, recent: 0 },
),
safeQueryRowsP(
`SELECT id, subject, status, lane, created_at
FROM drafts WHERE ($1::uuid IS NULL OR org_id = $1::uuid)
ORDER BY created_at DESC
LIMIT 5`,
[orgId],
),
safeQuery(
`SELECT COUNT(*) as total,
COUNT(*) FILTER (WHERE archive_type='email') as emails,
COUNT(*) FILTER (WHERE archive_type='workshop') as workshops,
COUNT(*) FILTER (WHERE archive_type='newsletter') as newsletters,
COUNT(*) FILTER (WHERE archive_type='campaign') as campaigns
FROM historical_archives`,
{ total: 0, emails: 0, workshops: 0, newsletters: 0, campaigns: 0 },
),
wantsArchive
? safeQueryRows(
`SELECT id, archive_type, title, subject, campaign_name, date_sent, tags, content_text
FROM historical_archives
ORDER BY is_featured DESC, date_sent DESC NULLS LAST
LIMIT 10`,
)
: Promise.resolve([]),
]);
/* ── Build context strings ───────────────────────────────────────────── */
const statsBlock = [
`- Drafts: ${draftStats.total} total, ${draftStats.sent} sent, ${draftStats.drafts} in draft`,
`- Grants: ${grantStats.total} total, ${grantStats.submitted} submitted`,
`- Partners: ${partnerStats.total} total, ${partnerStats.active} active`,
`- Petitions: ${petitionStats.total} total`,
`- News Items: ${newsStats.total} total, ${newsStats.recent} from the last 7 days`,
].join('\n');
const draftsBlock = recentDrafts.length > 0
? recentDrafts
.map(
(d) =>
` - [${d.status}] ${d.subject || '(no subject)'} (Lane ${(d.lane as string || '?').toUpperCase()}, ${new Date(d.created_at as string).toLocaleDateString()})`,
)
.join('\n')
: ' (none)';
/* ── Build archive context ──────────────────────────────────────────── */
const archiveStatsBlock = `- Historical Archives: ${archiveStats.total} total (${archiveStats.emails} emails, ${archiveStats.workshops} workshops, ${archiveStats.newsletters} newsletters, ${archiveStats.campaigns} campaigns)`;
let archiveBlock = '';
if (archiveList.length > 0) {
archiveBlock = '\nHISTORICAL EMAIL/WORKSHOP ARCHIVE (user asked about these):\n' +
archiveList.map((a, i) => {
const date = a.date_sent ? new Date(a.date_sent as string).toLocaleDateString() : 'undated';
const preview = (a.content_text as string || '').slice(0, 300).replace(/\n/g, ' ');
return ` ${i + 1}. [${(a.archive_type as string).toUpperCase()}] "${a.title}" (${date}, campaign: ${a.campaign_name || 'none'})\n Tags: ${(a.tags as string[])?.join(', ') || 'none'}\n Preview: ${preview}...`;
}).join('\n') +
'\n\nWhen the user asks to use a historical email or workshop, show them the full content and offer to adapt it for their current needs. Suggest modifications based on current context.';
}
/* ── Action detection: try to parse actionable commands ─────────────── */
let action: { type: string; success: boolean; summary: string; details?: string } | null = null;
// Detect org setting updates (phone, email, website, mission, etc.)
const phoneMatch = message.match(/phone\s*(?:number|#)?\s*(?:is|:)?\s*\(?\d{3}\)?[\s.-]?\d{3}[\s.-]?\d{4}/i);
const phoneDigits = message.match(/\(?\d{3}\)?[\s.-]?\d{3}[\s.-]?\d{4}/);
const emailMatch = message.match(/(?:email|contact)\s+(?:is|:)\s+([^\s]+@[^\s]+)/i);
const websiteMatch = message.match(/(?:website|url|site)\s+(?:is|:)\s+(https?:\/\/[^\s]+)/i);
const missionMatch = message.match(/(?:mission|mission statement)\s+(?:is|:)\s+"?(.{20,})"?/i);
const settingUpdates: { column: string; value: string; label: string }[] = [];
if (phoneMatch && phoneDigits) {
const formatted = phoneDigits[0].replace(/[^\d]/g, '').replace(/(\d{3})(\d{3})(\d{4})/, '($1) $2-$3');
settingUpdates.push({ column: 'org_phone', value: formatted, label: `phone → ${formatted}` });
}
if (emailMatch) {
settingUpdates.push({ column: 'org_email', value: emailMatch[1], label: `email → ${emailMatch[1]}` });
}
if (websiteMatch) {
settingUpdates.push({ column: 'website', value: websiteMatch[1], label: `website → ${websiteMatch[1]}` });
}
if (missionMatch) {
settingUpdates.push({ column: 'mission', value: missionMatch[1].trim(), label: `mission updated` });
}
// Execute setting updates
if (settingUpdates.length > 0) {
try {
for (const upd of settingUpdates) {
if (orgId) {
await query(`UPDATE nonprofit_accounts SET ${upd.column} = $1 WHERE id = $2`, [upd.value, orgId]);
} else {
await query(`UPDATE nonprofit_accounts SET ${upd.column} = $1 WHERE id = (SELECT id FROM nonprofit_accounts WHERE is_active = true ORDER BY created_at ASC LIMIT 1)`, [upd.value]);
}
}
action = {
type: 'setting_update',
success: true,
summary: settingUpdates.map(u => u.label).join(', '),
details: `Updated ${settingUpdates.length} setting(s) in nonprofit_accounts`,
};
} catch (err) {
action = {
type: 'setting_update',
success: false,
summary: `Failed to update: ${(err as Error).message}`,
};
}
}
// Detect app_settings updates (people, org names, competitors)
const addPersonMatch = message.match(/add\s+(?:person|employee|staff|team member)\s*:?\s+"?([^"]+)"?\s+(?:as|role:?)\s+"?([^"]+)"?/i);
if (addPersonMatch) {
try {
const name = addPersonMatch[1].trim();
const role = addPersonMatch[2].trim();
await query(
`INSERT INTO app_settings (setting_key, setting_value)
VALUES ('staff_member', $1::jsonb)
ON CONFLICT DO NOTHING`,
[JSON.stringify({ name, role })],
);
action = {
type: 'add_staff',
success: true,
summary: `Added ${name} (${role}) to staff`,
};
} catch (err) {
action = {
type: 'add_staff',
success: false,
summary: `Failed: ${(err as Error).message}`,
};
}
}
/* ── Build system prompt ─────────────────────────────────────────────── */
const actionContext = action
? `\n\nACTION TAKEN: ${action.success ? 'SUCCESS' : 'FAILED'} — ${action.summary}\nAcknowledge this action in your response. If successful, confirm what was updated. If failed, explain what went wrong.`
: '';
const systemPrompt = `You are the Norma Assistant — an AI helper built into the advocacy platform.
You can answer questions AND take actions to update the platform.
CURRENT DATA SNAPSHOT:
${statsBlock}
${archiveStatsBlock}
RECENT DRAFTS:
${draftsBlock}
${archiveBlock}
${actionContext}
CAPABILITIES — QUERIES:
- Answer questions about platform data (drafts, grants, partners, news, petitions)
- Suggest email content and strategies
- Help with grant proposal ideas
- Browse historical emails, workshops, newsletters
CAPABILITIES — ACTIONS (executed automatically when detected):
- Update org settings: "phone number is (555) 123-4567", "email is info@org.com", "website is https://org.com"
- Update mission: "mission is: [statement]"
- Add staff: "add person: Name as Role"
- More action types coming soon
When an action was taken, confirm it clearly. Be concise, friendly, and data-driven.
If asked about something not in your data, say so honestly.`;
/* ── Call Gemini ─────────────────────────────────────────────────────── */
const geminiUrl = `https://generativelanguage.googleapis.com/v1beta/models/gemini-2.0-flash:generateContent?key=${GEMINI_API_KEY}`;
const geminiRes = await fetch(geminiUrl, {
method: 'POST',
headers: { 'Content-Type': 'application/json' },
body: JSON.stringify({
contents: [
{
role: 'user',
parts: [{ text: `${systemPrompt}\n\nUser message: ${message.trim()}` }],
},
],
generationConfig: {
temperature: 0.7,
maxOutputTokens: 2048,
},
}),
});
if (!geminiRes.ok) {
const errBody = await geminiRes.text();
console.error('[ai-chat] Gemini error:', geminiRes.status, errBody);
return NextResponse.json(
{ error: 'AI service temporarily unavailable', action },
{ status: 502 },
);
}
const data = await geminiRes.json();
const reply =
data.candidates?.[0]?.content?.parts?.[0]?.text ||
'Sorry, I could not generate a response. Please try again.';
/* ── Persist both messages ───────────────────────────────────────────── */
await query(
`INSERT INTO chat_messages (role, content, org_id) VALUES ('user', $1, $2)`,
[message.trim(), orgId],
);
await query(
`INSERT INTO chat_messages (role, content, metadata, org_id) VALUES ('assistant', $1, $2, $3)`,
[
reply,
JSON.stringify({
model: 'gemini-2.0-flash',
action: action || null,
context_tables: ['drafts', 'partners', 'news_items'],
}),
orgId,
],
);
return NextResponse.json({ reply, action });
} catch (err) {
console.error('[ai-chat] POST error:', (err as Error).message);
return NextResponse.json(
{ error: 'AI chat request failed' },
{ status: 500 },
);
}
}