← back to Norma
app/api/orgs/enrich/route.ts
289 lines
import { NextRequest, NextResponse } from 'next/server';
import { query } from '@/lib/db';
import { requireRole } from '@/lib/require-role';
import { auditLog } from '@/lib/audit';
const GEMINI_KEY = process.env.GEMINI_API_KEY || '';
/**
* Extract mission/about text from a webpage using Gemini.
*/
async function extractMission(url: string, orgName: string): Promise<{ mission: string; description: string } | null> {
try {
// Fetch the page
const controller = new AbortController();
const timeout = setTimeout(() => controller.abort(), 12000);
const res = await fetch(url, {
signal: controller.signal,
headers: { 'User-Agent': 'Mozilla/5.0 (compatible; Norma-Bot/1.0)' },
});
clearTimeout(timeout);
if (!res.ok) return null;
const html = await res.text();
// Strip script/style tags, take first 8000 chars of text
const cleaned = html
.replace(/<script[\s\S]*?<\/script>/gi, '')
.replace(/<style[\s\S]*?<\/style>/gi, '')
.replace(/<[^>]+>/g, ' ')
.replace(/\s+/g, ' ')
.trim()
.slice(0, 8000);
if (cleaned.length < 50) return null;
// Use Gemini to extract mission/description
const geminiRes = await fetch(
`https://generativelanguage.googleapis.com/v1beta/models/gemini-2.0-flash:generateContent?key=${GEMINI_KEY}`,
{
method: 'POST',
headers: { 'Content-Type': 'application/json' },
body: JSON.stringify({
contents: [{
parts: [{ text: `Extract the mission statement and a brief description for the nonprofit "${orgName}" from this webpage text. Return ONLY valid JSON (no markdown, no code blocks):\n{"mission": "the org's mission statement or purpose (1-2 sentences)", "description": "brief description of what the org does (2-3 sentences)"}\n\nIf you cannot find a mission statement, write one based on the available information.\n\nWebpage text:\n${cleaned}` }],
}],
generationConfig: { temperature: 0.1, maxOutputTokens: 300 },
}),
},
);
if (!geminiRes.ok) return null;
const geminiData = await geminiRes.json();
const text = geminiData?.candidates?.[0]?.content?.parts?.[0]?.text || '';
// Parse JSON from response (handle possible markdown wrapping)
const jsonStr = text.replace(/```json\n?/g, '').replace(/```\n?/g, '').trim();
const parsed = JSON.parse(jsonStr);
return {
mission: parsed.mission || '',
description: parsed.description || '',
};
} catch {
return null;
}
}
/**
* Search for an org's website using Google Custom Search or fallback.
*/
async function findOrgWebsite(orgName: string, ein?: string): Promise<string | null> {
try {
// Try a simple Google search via Gemini
const prompt = `What is the official website URL for the nonprofit organization "${orgName}"${ein ? ` (EIN: ${ein})` : ''}? Return ONLY the URL, nothing else. If you don't know, return "unknown".`;
const res = await fetch(
`https://generativelanguage.googleapis.com/v1beta/models/gemini-2.0-flash:generateContent?key=${GEMINI_KEY}`,
{
method: 'POST',
headers: { 'Content-Type': 'application/json' },
body: JSON.stringify({
contents: [{ parts: [{ text: prompt }] }],
generationConfig: { temperature: 0.1, maxOutputTokens: 100 },
}),
},
);
if (!res.ok) return null;
const data = await res.json();
const text = (data?.candidates?.[0]?.content?.parts?.[0]?.text || '').trim();
if (text === 'unknown' || !text.includes('.')) return null;
// Extract URL
const urlMatch = text.match(/https?:\/\/[^\s"'<>]+/);
if (urlMatch) return urlMatch[0].replace(/[.,;)]+$/, '');
// If just a domain like "example.org", prepend https
if (text.match(/^[a-z0-9.-]+\.[a-z]{2,}$/i)) return `https://${text}`;
return null;
} catch {
return null;
}
}
/**
* POST /api/orgs/enrich
* Enrich organizations with website URLs and mission statements.
*
* Body:
* org_id - enrich a single org by ID
* batch - true to enrich next N orgs without website/mission (default: false)
* batch_size - how many to process in batch mode (default: 5, max: 20)
*/
export async function POST(request: NextRequest) {
const auth = requireRole(request, 'admin', 'staff');
if (auth instanceof NextResponse) return auth;
try {
const body = await request.json();
const orgId = body.org_id;
const batch = body.batch === true;
const batchSize = Math.min(parseInt(body.batch_size || '5'), 20);
interface OrgRow { id: string; name: string; ein: string | null; website: string | null; source_url: string | null }
let orgsToEnrich: OrgRow[] = [];
if (orgId) {
const result = await query(
'SELECT id, name, ein, website, source_url FROM organizations WHERE id = $1',
[orgId],
);
if (result.rowCount === 0) {
return NextResponse.json({ error: 'Organization not found' }, { status: 404 });
}
orgsToEnrich = result.rows as OrgRow[];
} else if (batch) {
const result = await query(
`SELECT id, name, ein, website, source_url FROM organizations
WHERE (website IS NULL OR website = '' OR mission IS NULL OR mission = '')
AND key_issues IS NOT NULL AND array_length(key_issues, 1) > 0
ORDER BY name
LIMIT $1`,
[batchSize],
);
orgsToEnrich = result.rows as OrgRow[];
} else {
return NextResponse.json({ error: 'Provide org_id or set batch=true' }, { status: 400 });
}
const results: Array<{
id: string;
name: string;
website: string | null;
mission: string | null;
description: string | null;
enriched: boolean;
}> = [];
for (const org of orgsToEnrich) {
let website = org.website;
let mission: string | null = null;
let description: string | null = null;
let enriched = false;
// Step 1: Find website if missing
if (!website) {
website = await findOrgWebsite(org.name, org.ein || undefined);
if (website) {
await query('UPDATE organizations SET website = $1, updated_at = NOW() WHERE id = $2', [website, org.id]);
enriched = true;
}
}
// Step 2: Extract mission from website
if (website) {
const extracted = await extractMission(website, org.name);
if (extracted) {
mission = extracted.mission;
description = extracted.description;
const updates: string[] = [];
const params: unknown[] = [];
let idx = 1;
if (mission) {
updates.push(`mission = $${idx++}`);
params.push(mission);
}
if (description) {
updates.push(`description = $${idx++}`);
params.push(description);
}
if (updates.length > 0) {
updates.push(`updated_at = NOW()`);
params.push(org.id);
await query(
`UPDATE organizations SET ${updates.join(', ')} WHERE id = $${idx}`,
params,
);
enriched = true;
}
}
}
// Step 3: If no website found, try to get a description via Gemini knowledge
if (!website && !mission) {
try {
const res = await fetch(
`https://generativelanguage.googleapis.com/v1beta/models/gemini-2.0-flash:generateContent?key=${GEMINI_KEY}`,
{
method: 'POST',
headers: { 'Content-Type': 'application/json' },
body: JSON.stringify({
contents: [{ parts: [{ text: `Provide a brief 2-sentence description and mission for the nonprofit "${org.name}"${org.ein ? ` (EIN: ${org.ein})` : ''}. Return ONLY valid JSON: {"mission": "...", "description": "..."}. If you have no information about this organization, return {"mission": "", "description": ""}` }] }],
generationConfig: { temperature: 0.1, maxOutputTokens: 200 },
}),
},
);
if (res.ok) {
const data = await res.json();
const text = (data?.candidates?.[0]?.content?.parts?.[0]?.text || '').trim();
const jsonStr = text.replace(/```json\n?/g, '').replace(/```\n?/g, '').trim();
const parsed = JSON.parse(jsonStr);
if (parsed.mission || parsed.description) {
mission = parsed.mission || null;
description = parsed.description || null;
const updates: string[] = [];
const params: unknown[] = [];
let idx = 1;
if (mission) { updates.push(`mission = $${idx++}`); params.push(mission); }
if (description) { updates.push(`description = $${idx++}`); params.push(description); }
if (updates.length > 0) {
updates.push(`updated_at = NOW()`);
params.push(org.id);
await query(`UPDATE organizations SET ${updates.join(', ')} WHERE id = $${idx}`, params);
enriched = true;
}
}
}
} catch { /* non-critical */ }
}
results.push({
id: org.id,
name: org.name,
website,
mission,
description,
enriched,
});
}
const enrichedCount = results.filter(r => r.enriched).length;
const ip = request.headers.get('x-forwarded-for') || request.headers.get('x-real-ip') || undefined;
await auditLog('org.enrich', 'batch', null, { enriched: enrichedCount, total: results.length }, ip);
return NextResponse.json({
results,
enriched: enrichedCount,
total: results.length,
});
} catch (err) {
console.error('[api/orgs/enrich] Error:', (err as Error).message);
return NextResponse.json({ error: 'Enrichment failed' }, { status: 500 });
}
}
/**
* GET /api/orgs/enrich
* Check enrichment stats — how many orgs need enrichment.
*/
export async function GET(request: NextRequest) {
const auth = requireRole(request, 'admin', 'staff');
if (auth instanceof NextResponse) return auth;
try {
const stats = await query(`
SELECT
COUNT(*) as total,
COUNT(*) FILTER (WHERE website IS NOT NULL AND website != '') as has_website,
COUNT(*) FILTER (WHERE mission IS NOT NULL AND mission != '') as has_mission,
COUNT(*) FILTER (WHERE description IS NOT NULL AND description != '') as has_description,
COUNT(*) FILTER (WHERE email IS NOT NULL AND email != '') as has_email,
COUNT(*) FILTER (WHERE (website IS NULL OR website = '') AND (mission IS NULL OR mission = '')) as needs_enrichment
FROM organizations
WHERE key_issues IS NOT NULL AND array_length(key_issues, 1) > 0
`);
return NextResponse.json({ stats: stats.rows[0] });
} catch (err) {
console.error('[api/orgs/enrich] GET Error:', (err as Error).message);
return NextResponse.json({ error: 'Failed to get stats' }, { status: 500 });
}
}