← back to Norma
app/api/scraper/suggestions/route.ts
577 lines
import { NextRequest, NextResponse } from 'next/server';
import { query } from '@/lib/db';
import { requireRole } from '@/lib/require-role';
import { auditLog } from '@/lib/audit';
import { getBrand } from '@/lib/brand';
const GEMINI_API_KEY = process.env.GEMINI_API_KEY || '';
const GEMINI_URL = `https://generativelanguage.googleapis.com/v1beta/models/gemini-2.0-flash:generateContent?key=${GEMINI_API_KEY}`;
/**
* Petition suggestion target map — maps categories to likely petition targets.
*/
const CATEGORY_TARGETS: Record<string, string> = {
politics: 'U.S. Congress',
education: 'Department of Education',
economy: 'Federal Reserve / Treasury Department',
health: 'Department of Health and Human Services',
environment: 'Environmental Protection Agency',
technology: 'Federal Trade Commission',
civil_rights: 'Department of Justice',
debt_cancellation: 'President of the United States',
consumer_protection: 'Consumer Financial Protection Bureau',
};
interface GeminiSuggestion {
title: string;
description: string;
target: string;
category: string;
urgency: 'hot' | 'rising' | 'steady';
reasoning: string;
viability_score: number;
}
/**
* GET /api/scraper/suggestions
* Fetch AI-generated petition suggestions with optional filters.
* Query params:
* ?status=suggested — filter by status (suggested, approved, dismissed, created)
* &urgency=hot — filter by urgency (hot, rising, steady)
* &limit=20 — max results (default 20, max 100)
* &offset=0 — pagination offset
*/
export async function GET(request: NextRequest) {
const auth = requireRole(request, 'admin', 'staff');
if (auth instanceof NextResponse) return auth;
try {
const { searchParams } = new URL(request.url);
const status = searchParams.get('status');
const urgency = searchParams.get('urgency');
const limit = Math.min(Math.max(parseInt(searchParams.get('limit') || '20', 10) || 20, 1), 100);
const offset = Math.max(parseInt(searchParams.get('offset') || '0', 10) || 0, 0);
const conditions: string[] = [];
const values: unknown[] = [];
let paramIndex = 1;
if (status) {
conditions.push(`status = $${paramIndex}`);
values.push(status);
paramIndex++;
}
if (urgency) {
conditions.push(`urgency = $${paramIndex}`);
values.push(urgency);
paramIndex++;
}
const whereClause = conditions.length > 0 ? `WHERE ${conditions.join(' AND ')}` : '';
// Fetch rows
const countValues = [...values]; // Copy for count query
values.push(limit);
const limitParam = paramIndex++;
values.push(offset);
const offsetParam = paramIndex++;
const result = await query(
`SELECT * FROM petition_suggestions
${whereClause}
ORDER BY viability_score DESC NULLS LAST, generated_at DESC
LIMIT $${limitParam} OFFSET $${offsetParam}`,
values,
);
// Fetch total count with same filter
const countResult = await query(
`SELECT COUNT(*) as total FROM petition_suggestions ${whereClause}`,
countValues,
);
const total = parseInt(countResult.rows[0]?.total || '0', 10);
return NextResponse.json({
suggestions: result.rows,
rows: result.rows, // Backward compat
count: result.rows.length,
total,
});
} catch (err) {
console.error('[api/scraper/suggestions] GET error:', (err as Error).message);
return NextResponse.json({ error: 'Failed to fetch suggestions' }, { status: 500 });
}
}
/**
* POST /api/scraper/suggestions
* Generate new petition suggestions by analyzing trending data.
* Two modes:
* - Default: gap analysis + Gemini AI for polished suggestions
* - ?mode=gap: gap analysis only (no AI call)
*
* Reads latest trending_topics and trending_petitions, finds topics
* with high scores but no existing petitions, and generates 10-20 suggestions.
* Requires auth.
*/
export async function POST(request: NextRequest) {
const auth = requireRole(request, 'admin', 'staff');
if (auth instanceof NextResponse) return auth;
try {
const { searchParams } = new URL(request.url);
const mode = searchParams.get('mode') || 'ai';
// 1. Fetch latest trending topics (top 50 by score from last 48h)
const topicsResult = await query(
`SELECT * FROM trending_topics
WHERE scraped_at > NOW() - INTERVAL '48 hours'
ORDER BY score DESC LIMIT 50`,
);
const trendingTopics = topicsResult.rows;
// 2. Fetch latest trending petitions (top 50 by signatures)
const petitionsResult = await query(
`SELECT * FROM trending_petitions
ORDER BY scraped_at DESC, signature_count DESC LIMIT 50`,
);
const trendingPetitions = petitionsResult.rows;
// 3. Get existing petition suggestions to avoid duplicates (last 7 days)
const existingSuggestions = await query(
`SELECT LOWER(title) AS title_lower FROM petition_suggestions
WHERE status != 'rejected' AND status != 'dismissed'
AND generated_at > NOW() - INTERVAL '7 days'`,
);
const existingTitleSet = new Set(
existingSuggestions.rows.map((r) => (r as Record<string, string>).title_lower),
);
// 4. Also get active petitions from the main petitions table
const existingPetitions = await query(
`SELECT LOWER(title) AS title_lower FROM petitions WHERE status = 'active'`,
);
const activePetitionTitles = new Set(
existingPetitions.rows.map((r) => (r as Record<string, string>).title_lower),
);
let insertedSuggestions;
if (mode === 'gap') {
// ── Gap-analysis-only mode (no AI) ───────────────────────────────────
insertedSuggestions = await generateGapSuggestions(
trendingTopics,
trendingPetitions,
existingTitleSet,
activePetitionTitles,
);
} else {
// ── AI-powered mode (Gemini) ─────────────────────────────────────────
insertedSuggestions = await generateAiSuggestions(
trendingTopics,
trendingPetitions,
existingTitleSet,
);
}
// 5. Audit log the action
const ip =
request.headers.get('x-forwarded-for') ||
request.headers.get('x-real-ip') ||
undefined;
await auditLog(
'suggestions.generated',
'petition_suggestion',
null,
{
count: insertedSuggestions.length,
mode,
user: auth.username,
topics_used: trendingTopics.length,
petitions_used: trendingPetitions.length,
},
ip,
);
return NextResponse.json({
suggestions: insertedSuggestions,
count: insertedSuggestions.length,
analyzed: {
topics_reviewed: trendingTopics.length,
petitions_compared: trendingPetitions.length,
mode,
},
});
} catch (err) {
console.error('[api/scraper/suggestions] POST error:', (err as Error).message);
return NextResponse.json({ error: 'Failed to generate suggestions' }, { status: 500 });
}
}
/**
* PATCH /api/scraper/suggestions
* Update suggestion status. Requires auth.
* Body: { id: string, status: 'approved' | 'dismissed' | 'created' }
*/
export async function PATCH(request: NextRequest) {
const auth = requireRole(request, 'admin', 'staff');
if (auth instanceof NextResponse) return auth;
try {
const body = await request.json();
if (!body.id || typeof body.id !== 'string') {
return NextResponse.json({ error: 'id is required' }, { status: 400 });
}
const validStatuses = ['approved', 'dismissed', 'created'];
if (!body.status || !validStatuses.includes(body.status)) {
return NextResponse.json(
{ error: `status must be one of: ${validStatuses.join(', ')}` },
{ status: 400 },
);
}
const result = await query(
`UPDATE petition_suggestions
SET status = $1, approved_by = $2, approved_at = NOW()
WHERE id = $3
RETURNING *`,
[body.status, auth.username, body.id],
);
if (result.rows.length === 0) {
return NextResponse.json({ error: 'Suggestion not found' }, { status: 404 });
}
const suggestion = result.rows[0];
const ip =
request.headers.get('x-forwarded-for') ||
request.headers.get('x-real-ip') ||
undefined;
await auditLog(
`suggestion.${body.status}`,
'petition_suggestion',
suggestion.id,
{ title: suggestion.title, status: body.status, user: auth.username },
ip,
);
return NextResponse.json({ suggestion });
} catch (err) {
console.error('[api/scraper/suggestions] PATCH error:', (err as Error).message);
return NextResponse.json({ error: 'Failed to update suggestion' }, { status: 500 });
}
}
// ── AI-powered suggestion generation (Gemini) ─────────────────────────────
async function generateAiSuggestions(
trendingTopics: Record<string, unknown>[],
trendingPetitions: Record<string, unknown>[],
existingTitleSet: Set<string>,
): Promise<Record<string, unknown>[]> {
const brand = await getBrand();
const prompt = `You are a petition strategist for ${brand.name}, a nonprofit focused on ${brand.focusAreas.join(', ')}. Based on these trending topics and existing petitions, suggest 10 NEW petitions that would gain traction right now. Focus on timely issues, gaps in existing petitions, and high-impact targets.
Return a JSON array of objects with fields: title, description, target, category, urgency (hot|rising|steady), reasoning, viability_score (0-100).
Categories must be one of: politics, education, economy, health, environment, technology, civil_rights, debt_cancellation, consumer_protection.
TRENDING TOPICS (last 48 hours):
${JSON.stringify(trendingTopics.slice(0, 20).map((t) => ({
topic: t.topic,
source: t.source,
category: t.category,
score: t.score,
})), null, 2)}
EXISTING TRENDING PETITIONS:
${JSON.stringify(trendingPetitions.slice(0, 20).map((p) => ({
title: p.title,
platform: p.platform,
signature_count: p.signature_count,
category: p.category,
target: p.target,
})), null, 2)}
Return ONLY a valid JSON array of 10 petition suggestion objects. No markdown, no code fences, just the JSON array.`;
try {
const geminiResponse = await fetch(GEMINI_URL, {
method: 'POST',
headers: { 'Content-Type': 'application/json' },
signal: AbortSignal.timeout(30000),
body: JSON.stringify({
contents: [{ parts: [{ text: prompt }] }],
generationConfig: {
temperature: 0.7,
maxOutputTokens: 8192,
responseMimeType: 'application/json',
},
}),
});
if (!geminiResponse.ok) {
const errorText = await geminiResponse.text();
console.error('[scraper/suggestions] Gemini API error:', errorText);
// Fall back to gap analysis
return [];
}
const geminiData = await geminiResponse.json();
const rawText = geminiData?.candidates?.[0]?.content?.parts?.[0]?.text;
if (!rawText) {
console.error('[scraper/suggestions] Empty Gemini response');
return [];
}
let suggestions: GeminiSuggestion[];
try {
suggestions = JSON.parse(rawText);
if (!Array.isArray(suggestions)) throw new Error('Not an array');
} catch {
console.error('[scraper/suggestions] Invalid Gemini JSON:', rawText.slice(0, 500));
return [];
}
// Build source context for DB storage
const sourceTopics = JSON.stringify(
trendingTopics.slice(0, 10).map((t) => ({
topic: t.topic,
score: t.score,
})),
);
const sourcePetitions = JSON.stringify(
trendingPetitions.slice(0, 10).map((p) => ({
title: p.title,
signatures: p.signature_count,
})),
);
const inserted = [];
for (const s of suggestions) {
const titleLower = (s.title || '').toLowerCase();
if (existingTitleSet.has(titleLower)) continue;
const validUrgency = ['hot', 'rising', 'steady'];
const urgency = validUrgency.includes(s.urgency) ? s.urgency : 'steady';
const score = Math.min(Math.max(Number(s.viability_score) || 0, 0), 100);
try {
const result = await query(
`INSERT INTO petition_suggestions
(title, description, target, category, urgency, reasoning,
source_topics, source_petitions, viability_score, status)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, 'suggested')
RETURNING *`,
[
s.title || 'Untitled Suggestion',
s.description || null,
s.target || null,
s.category || null,
urgency,
s.reasoning || null,
sourceTopics,
sourcePetitions,
score,
],
);
inserted.push(result.rows[0]);
} catch (err) {
console.error('[scraper/suggestions] Insert failed:', (err as Error).message);
}
}
return inserted;
} catch (err) {
console.error('[scraper/suggestions] Gemini call failed:', (err as Error).message);
return [];
}
}
// ── Gap-analysis-based suggestion generation (no AI) ───────────────────────
async function generateGapSuggestions(
trendingTopics: Record<string, unknown>[],
trendingPetitions: Record<string, unknown>[],
existingTitleSet: Set<string>,
activePetitionTitles: Set<string>,
): Promise<Record<string, unknown>[]> {
// Build a set of petition topic words for gap detection
const petitionTopicWords = new Set<string>();
for (const p of trendingPetitions) {
const words = String(p.title ?? '').toLowerCase().split(/\s+/);
for (const w of words) {
if (w.length > 3) petitionTopicWords.add(w);
}
}
interface SuggestionCandidate {
title: string;
description: string;
target: string;
category: string;
urgency: 'hot' | 'rising' | 'steady';
reasoning: string;
sourceTopics: Array<{ topic: string; score: number }>;
sourcePetitions: Array<{ title: string; signatures: number }>;
viabilityScore: number;
}
const candidates: SuggestionCandidate[] = [];
for (const topic of trendingTopics) {
const topicStr = String(topic.topic ?? '');
const category = String(topic.category ?? '');
if (!category) continue;
// Check overlap with existing petitions
const topicWords = topicStr.toLowerCase().split(/\s+/).filter((w) => w.length > 3);
const overlapCount = topicWords.filter((w) => petitionTopicWords.has(w)).length;
const overlapRatio = topicWords.length > 0 ? overlapCount / topicWords.length : 0;
// Gap = trending topic with low overlap to existing petitions
if (overlapRatio >= 0.4) continue;
const title = generatePetitionTitle(topicStr, category);
const titleLower = title.toLowerCase();
if (existingTitleSet.has(titleLower)) continue;
if (activePetitionTitles.has(titleLower)) continue;
// Check for near-duplicate candidates
const isDuplicate = candidates.some((c) => {
const cWords = new Set(c.title.toLowerCase().split(/\s+/));
const tWords = title.toLowerCase().split(/\s+/);
const shared = tWords.filter((w) => cWords.has(w)).length;
return shared / Math.max(tWords.length, 1) > 0.6;
});
if (isDuplicate) continue;
const score = Number(topic.score) || 0;
const mentions = Number(topic.mentions) || 1;
const velocity = Number(topic.velocity) || 0;
let urgency: 'hot' | 'rising' | 'steady' = 'steady';
if (score > 10000 || velocity > 5000) urgency = 'hot';
else if (score > 2000 || velocity > 1000) urgency = 'rising';
const engagementFactor = Math.min(score / 50000, 1) * 50;
const petitionabilityFactor = category ? 30 : 10;
const gapBonus = (1 - overlapRatio) * 20;
const viabilityScore = Math.round(Math.min(engagementFactor + petitionabilityFactor + gapBonus, 100));
// Find related petitions
const relatedPetitions = trendingPetitions
.filter((p) => {
const pWords = String(p.title ?? '').toLowerCase().split(/\s+/);
return topicWords.some((w) => pWords.includes(w));
})
.slice(0, 3)
.map((p) => ({
title: String(p.title ?? ''),
signatures: Number(p.signature_count) || 0,
}));
candidates.push({
title,
description: `This topic is currently trending with ${mentions} mention${mentions !== 1 ? 's' : ''} across social media. ` +
`The issue of "${topicStr}" relates to ${category.replace(/_/g, ' ')} and has significant public engagement. ` +
`A well-timed petition could channel this attention into meaningful policy action.`,
target: CATEGORY_TARGETS[category] || 'U.S. Congress',
category,
urgency,
reasoning: generateReasoning(topic, urgency),
sourceTopics: [{ topic: topicStr, score }],
sourcePetitions: relatedPetitions,
viabilityScore,
});
if (candidates.length >= 20) break;
}
// Sort by viability and insert
candidates.sort((a, b) => b.viabilityScore - a.viabilityScore);
const topCandidates = candidates.slice(0, 20);
const inserted = [];
for (const c of topCandidates) {
try {
const result = await query(
`INSERT INTO petition_suggestions
(title, description, target, category, urgency, reasoning,
source_topics, source_petitions, viability_score, status)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, 'suggested')
RETURNING *`,
[
c.title,
c.description,
c.target,
c.category,
c.urgency,
c.reasoning,
JSON.stringify(c.sourceTopics),
JSON.stringify(c.sourcePetitions),
c.viabilityScore,
],
);
inserted.push(result.rows[0]);
} catch (err) {
console.error('[scraper/suggestions] Insert failed:', (err as Error).message);
}
}
return inserted;
}
// ── Helper: generate petition title from trending topic ────────────────────
function generatePetitionTitle(topic: string, category: string): string {
const target = CATEGORY_TARGETS[category] || 'our leaders';
const cleanTopic = topic.replace(/\[.*?\]/g, '').replace(/\(.*?\)/g, '').trim();
const shortTopic = cleanTopic.length > 80 ? cleanTopic.slice(0, 77) + '...' : cleanTopic;
const prefixes: Record<string, string> = {
politics: `Demand ${target} take action on`,
education: `Tell the ${target} to address`,
economy: `Urge ${target} to act on`,
health: `Call on ${target} to respond to`,
environment: `Petition ${target} to act on`,
technology: `Demand ${target} regulate`,
civil_rights: `Call on ${target} to protect rights regarding`,
debt_cancellation: `Demand the ${target} cancel student debt:`,
consumer_protection: `Tell the ${target} to protect consumers from`,
};
const prefix = prefixes[category] || 'Take action on';
return `${prefix} ${shortTopic}`;
}
// ── Helper: generate reasoning ─────────────────────────────────────────────
function generateReasoning(
topic: Record<string, unknown>,
urgency: string,
): string {
const score = Number(topic.score) || 0;
const mentions = Number(topic.mentions) || 1;
const source = String(topic.source || 'social media');
let reasoning = `Trending on ${source} with a score of ${score.toLocaleString()} and ${mentions} mention${mentions !== 1 ? 's' : ''}. `;
if (urgency === 'hot') {
reasoning += 'This is a hot-button issue with extremely high engagement -- launching a petition now would capture peak attention. ';
} else if (urgency === 'rising') {
reasoning += 'Engagement is rising rapidly, making this an ideal window to launch a petition before attention plateaus. ';
} else {
reasoning += 'Steady interest suggests a durable issue that could sustain a petition campaign over time. ';
}
reasoning += 'No existing petition adequately covers this specific angle, creating an opportunity to lead.';
return reasoning;
}