← back to Norma
app/api/graph/mindmap-enhanced/route.ts
811 lines
import { NextRequest, NextResponse } from 'next/server';
import { query } from '@/lib/db';
import { requireRole } from '@/lib/require-role';
// ─── Types ───────────────────────────────────────────────────────────────────
interface EnhancedNode {
id: string;
type:
| 'category'
| 'topic'
| 'petition'
| 'suggestion'
| 'debt_mention'
| 'nonprofit'
| 'foundation'
| 'country'
| 'grant'
| 'statement'
| 'data_source'
| 'contact'
| 'journalist';
label: string;
data: Record<string, unknown>;
}
interface EnhancedEdge {
source: string;
target: string;
type: string;
weight: number;
}
// ─── Helpers ──────────────────────────────────────────────────────────────────
async function safeQuery<T extends Record<string, unknown>>(
sql: string,
params?: unknown[],
): Promise<T[]> {
try {
const { rows } = await query<T>(sql, params);
return rows;
} catch (err: unknown) {
const msg = (err as Error).message ?? '';
if (msg.includes('does not exist') || msg.includes('undefined_table')) {
return [];
}
throw err;
}
}
function parseJsonbIds(value: unknown): string[] {
if (!value) return [];
if (Array.isArray(value)) {
return value.map((v) =>
typeof v === 'object' && v !== null
? (v as Record<string, string>).id ?? String(v)
: String(v),
);
}
return [];
}
// ─── GET /api/graph/mindmap-enhanced ─────────────────────────────────────────
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 hours = Math.max(1, parseInt(searchParams.get('hours') ?? '48', 10) || 48);
const minScore = parseFloat(searchParams.get('min_score') ?? '0') || 0;
// ── 1. Base: trending topics ────────────────────────────────────────────
const topics = await safeQuery<{
id: string;
topic: string;
source: string;
category: string | null;
score: number;
mentions: number;
velocity: number | null;
url: string | null;
related_topics: string[] | null;
}>(
`SELECT id, topic, source, category, score, mentions, velocity, url, related_topics
FROM trending_topics
WHERE scraped_at > NOW() - ($1 || ' hours')::interval
AND COALESCE(score, 0) >= $2
ORDER BY score DESC
LIMIT 200`,
[String(hours), minScore],
);
// ── 2. Base: trending petitions ─────────────────────────────────────────
const petitions = await safeQuery<{
id: string;
title: string;
platform: string;
category: string | null;
signature_count: number;
growth_rate: number | null;
is_climber: boolean;
target: string | null;
url: string | null;
}>(
`SELECT id, title, platform, category, signature_count, growth_rate, is_climber, target, url
FROM trending_petitions
ORDER BY signature_count DESC
LIMIT 100`,
);
// ── 3. Base: petition suggestions ───────────────────────────────────────
const suggestions = await safeQuery<{
id: string;
title: string;
category: string | null;
urgency: string | null;
viability_score: number | null;
reasoning: string | null;
source_topics: unknown;
source_petitions: unknown;
status: string;
}>(
`SELECT id, title, category, urgency, viability_score, reasoning,
source_topics, source_petitions, status
FROM petition_suggestions
WHERE status IN ('suggested', 'approved')
ORDER BY viability_score DESC NULLS LAST
LIMIT 50`,
);
// ── 4. Base: student debt mentions ──────────────────────────────────────
const debtMentions = await safeQuery<{
id: string;
source: string;
title: string | null;
url: string | null;
sentiment: string | null;
engagement_score: number | null;
is_viral: boolean;
}>(
`SELECT id, source, title, url, sentiment, engagement_score, is_viral
FROM student_debt_mentions
WHERE scraped_at > NOW() - ($1 || ' hours')::interval
ORDER BY engagement_score DESC NULLS LAST
LIMIT 30`,
[String(hours)],
);
// ── 5. NEW: nonprofits ──────────────────────────────────────────────────
const nonprofits = await safeQuery<{
id: string;
name: string;
ntee_code: string | null;
state: string | null;
mission: string | null;
category: string | null;
annual_revenue: number | null;
}>(
`SELECT id, name, ntee_code, state, mission, category, annual_revenue
FROM organizations
WHERE type = 'nonprofit'
ORDER BY annual_revenue DESC NULLS LAST
LIMIT 60`,
);
// ── 6. NEW: foundations ─────────────────────────────────────────────────
const foundations = await safeQuery<{
id: string;
name: string;
focus_area: string | null;
total_grants_usd: number | null;
state: string | null;
website: string | null;
}>(
`SELECT id, name, focus_area, total_grants_usd, state, website
FROM organizations
WHERE type = 'foundation'
ORDER BY total_grants_usd DESC NULLS LAST
LIMIT 40`,
);
// ── 7. NEW: country profiles ────────────────────────────────────────────
const countries = await safeQuery<{
id: string;
country_name: string;
region: string | null;
debt_policy_score: number | null;
advocacy_index: number | null;
}>(
`SELECT id, country_name, region, debt_policy_score, advocacy_index
FROM country_profiles
ORDER BY advocacy_index DESC NULLS LAST
LIMIT 30`,
);
// ── 8. NEW: grants ──────────────────────────────────────────────────────
const grants = await safeQuery<{
id: string;
title: string;
funder: string | null;
amount_usd: number | null;
category: string | null;
deadline: string | null;
relevance_score: number | null;
}>(
`SELECT id, title, funder, amount_usd, category, deadline, relevance_score
FROM grants
WHERE status IN ('open', 'active')
ORDER BY relevance_score DESC NULLS LAST
LIMIT 40`,
);
// ── 9. NEW: nonprofit statements ────────────────────────────────────────
const statements = await safeQuery<{
id: string;
org_name: string;
topic: string | null;
stance: string | null;
published_at: string | null;
relevance_score: number | null;
}>(
`SELECT id, org_name, topic, stance, published_at, relevance_score
FROM nonprofit_statements
ORDER BY relevance_score DESC NULLS LAST, published_at DESC NULLS LAST
LIMIT 40`,
);
// ── 10. NEW: discovered APIs ────────────────────────────────────────────
const dataSources = await safeQuery<{
id: string;
api_name: string;
category: string | null;
relevance_score: number | null;
status: string | null;
matched_topics: unknown;
}>(
`SELECT id, api_name, category, relevance_score, status, matched_topics
FROM discovered_apis
ORDER BY relevance_score DESC NULLS LAST
LIMIT 30`,
);
// ── 11. NEW: mind_map_links (cross-modal edges) ──────────────────────────
// Use UNION to guarantee contact links are always included (not drowned by
// high-weight issue_alignment links that fill LIMIT 500 slots).
const mindMapLinks = await safeQuery<{
source_type: string;
source_id: string;
target_type: string;
target_id: string;
link_type: string;
weight: number | null;
}>(
`(SELECT source_type, source_id, target_type, target_id, link_type, COALESCE(weight, 0.5) AS weight
FROM mind_map_links
WHERE source_type IN ('contact', 'journalist') OR target_type IN ('contact', 'journalist'))
UNION ALL
(SELECT source_type, source_id, target_type, target_id, link_type, COALESCE(weight, 0.5) AS weight
FROM mind_map_links
WHERE weight >= 0.7
AND source_type NOT IN ('contact', 'journalist') AND target_type NOT IN ('contact', 'journalist')
ORDER BY weight DESC
LIMIT 200)`,
);
// ── Build graph ──────────────────────────────────────────────────────────
const nodes: EnhancedNode[] = [];
const edges: EnhancedEdge[] = [];
const nodeIdSet = new Set<string>();
const topicNodeMap = new Map<string, string>();
const maxTopicScore = topics.reduce((mx, t) => Math.max(mx, Number(t.score) || 0), 1);
// Category nodes (virtual)
const categoryScores = new Map<string, number>();
for (const t of topics) {
const cat = t.category ?? 'uncategorized';
categoryScores.set(cat, (categoryScores.get(cat) ?? 0) + (Number(t.score) || 0));
}
for (const p of petitions) {
const cat = p.category ?? 'uncategorized';
if (!categoryScores.has(cat)) categoryScores.set(cat, 0);
}
for (const [catName, catScore] of Array.from(categoryScores.entries())) {
const nodeId = `cat-${catName}`;
nodes.push({
id: nodeId,
type: 'category',
label: catName,
data: { score: catScore, category: catName },
});
nodeIdSet.add(nodeId);
}
// Topic nodes
for (const t of topics) {
const nodeId = `topic-${t.id}`;
const topicScore = Number(t.score) || 0;
nodes.push({
id: nodeId,
type: 'topic',
label: t.topic,
data: {
source: t.source,
category: t.category,
score: topicScore,
mentions: t.mentions,
velocity: t.velocity,
url: t.url,
},
});
nodeIdSet.add(nodeId);
topicNodeMap.set(t.topic.toLowerCase(), nodeId);
const catId = `cat-${t.category ?? 'uncategorized'}`;
if (nodeIdSet.has(catId)) {
edges.push({
source: nodeId,
target: catId,
type: 'topic_to_category',
weight: maxTopicScore > 0 ? topicScore / maxTopicScore : 0,
});
}
}
// Cross-link topics via shared related_topics
const relatedIndex = new Map<string, string[]>();
for (const t of topics) {
const nodeId = `topic-${t.id}`;
for (const rt of t.related_topics ?? []) {
const key = rt.toLowerCase();
if (!relatedIndex.has(key)) relatedIndex.set(key, []);
relatedIndex.get(key)!.push(nodeId);
}
}
const crossLinked = new Set<string>();
for (const [, linkedNodes] of Array.from(relatedIndex.entries())) {
if (linkedNodes.length < 2) continue;
for (let i = 0; i < linkedNodes.length; i++) {
for (let j = i + 1; j < linkedNodes.length; j++) {
const edgeKey = [linkedNodes[i], linkedNodes[j]].sort().join('|');
if (crossLinked.has(edgeKey)) continue;
crossLinked.add(edgeKey);
edges.push({ source: linkedNodes[i], target: linkedNodes[j], type: 'shared_related', weight: 0.3 });
}
}
}
for (const t of topics) {
const nodeId = `topic-${t.id}`;
for (const rt of t.related_topics ?? []) {
const targetNodeId = topicNodeMap.get(rt.toLowerCase());
if (targetNodeId && targetNodeId !== nodeId) {
const edgeKey = [nodeId, targetNodeId].sort().join('|');
if (!crossLinked.has(edgeKey)) {
crossLinked.add(edgeKey);
edges.push({ source: nodeId, target: targetNodeId, type: 'shared_related', weight: 0.3 });
}
}
}
}
// Petition nodes
for (const p of petitions) {
const nodeId = `pet-${p.id}`;
nodes.push({
id: nodeId,
type: 'petition',
label: p.title,
data: {
category: p.category,
score: Number(p.signature_count) || 0,
platform: p.platform,
growth_rate: p.growth_rate,
is_climber: p.is_climber,
target: p.target,
url: p.url,
},
});
nodeIdSet.add(nodeId);
const catId = `cat-${p.category ?? 'uncategorized'}`;
if (nodeIdSet.has(catId)) {
edges.push({ source: nodeId, target: catId, type: 'petition_to_category', weight: 0.5 });
}
}
// Suggestion nodes
const petNodeIds = new Set(petitions.map((p) => p.id));
for (const s of suggestions) {
const nodeId = `sug-${s.id}`;
nodes.push({
id: nodeId,
type: 'suggestion',
label: s.title,
data: {
category: s.category,
score: Number(s.viability_score) || 0,
urgency: s.urgency,
reasoning: s.reasoning,
status: s.status,
},
});
nodeIdSet.add(nodeId);
for (const stId of parseJsonbIds(s.source_topics)) {
const targetNodeId = `topic-${stId}`;
if (nodeIdSet.has(targetNodeId)) {
edges.push({ source: nodeId, target: targetNodeId, type: 'suggestion_to_topic', weight: 0.8 });
}
}
for (const spId of parseJsonbIds(s.source_petitions)) {
const targetNodeId = `pet-${spId}`;
if (petNodeIds.has(spId) || nodeIdSet.has(targetNodeId)) {
edges.push({ source: nodeId, target: targetNodeId, type: 'suggestion_to_petition', weight: 0.6 });
}
}
}
// Debt mention nodes
for (const d of debtMentions) {
const nodeId = `debt-${d.id}`;
nodes.push({
id: nodeId,
type: 'debt_mention',
label: d.title ?? '(untitled mention)',
data: {
source: d.source,
score: Number(d.engagement_score) || 0,
sentiment: d.sentiment,
is_viral: d.is_viral,
url: d.url,
},
});
nodeIdSet.add(nodeId);
const targetCat = nodeIdSet.has('cat-student_debt')
? 'cat-student_debt'
: nodeIdSet.has('cat-education')
? 'cat-education'
: null;
if (targetCat) {
edges.push({ source: nodeId, target: targetCat, type: 'debt_to_category', weight: 0.4 });
}
}
// Nonprofit nodes
for (const n of nonprofits) {
const nodeId = `np-${n.id}`;
nodes.push({
id: nodeId,
type: 'nonprofit',
label: n.name,
data: {
score: n.annual_revenue ? Math.min(1, Number(n.annual_revenue) / 10_000_000) : 0.3,
ntee_code: n.ntee_code,
state: n.state,
mission: n.mission,
category: n.category,
},
});
nodeIdSet.add(nodeId);
const catId = `cat-${n.category ?? 'uncategorized'}`;
if (nodeIdSet.has(catId)) {
edges.push({ source: nodeId, target: catId, type: 'org_connection', weight: 0.5 });
}
}
// Foundation nodes
for (const f of foundations) {
const nodeId = `fnd-${f.id}`;
nodes.push({
id: nodeId,
type: 'foundation',
label: f.name,
data: {
score: f.total_grants_usd ? Math.min(1, Number(f.total_grants_usd) / 1_000_000) : 0,
focus_area: f.focus_area,
total_grants_usd: f.total_grants_usd,
state: f.state,
website: f.website,
},
});
nodeIdSet.add(nodeId);
}
// Country profile nodes
for (const c of countries) {
const nodeId = `ctr-${c.id}`;
nodes.push({
id: nodeId,
type: 'country',
label: c.country_name,
data: {
score: Number(c.advocacy_index) || 0,
region: c.region,
debt_policy_score: c.debt_policy_score,
advocacy_index: c.advocacy_index,
},
});
nodeIdSet.add(nodeId);
}
// Grant nodes
for (const g of grants) {
const nodeId = `grt-${g.id}`;
nodes.push({
id: nodeId,
type: 'grant',
label: g.title,
data: {
score: Number(g.relevance_score) || 0,
funder: g.funder,
amount_usd: g.amount_usd,
category: g.category,
deadline: g.deadline,
},
});
nodeIdSet.add(nodeId);
// Connect grants to category nodes
const catId = `cat-${g.category ?? 'uncategorized'}`;
if (nodeIdSet.has(catId)) {
edges.push({ source: nodeId, target: catId, type: 'funding_flow', weight: 0.6 });
}
// Connect grants to matching foundations
for (const f of foundations) {
if (
f.focus_area &&
g.category &&
f.focus_area.toLowerCase().includes(g.category.toLowerCase())
) {
const fndId = `fnd-${f.id}`;
if (nodeIdSet.has(fndId)) {
edges.push({ source: nodeId, target: fndId, type: 'funding_flow', weight: 0.7 });
}
}
}
}
// Statement nodes
for (const s of statements) {
const nodeId = `stmt-${s.id}`;
nodes.push({
id: nodeId,
type: 'statement',
label: `${s.org_name}: ${s.topic ?? 'statement'}`,
data: {
score: Number(s.relevance_score) || 0,
org_name: s.org_name,
topic: s.topic,
stance: s.stance,
published_at: s.published_at,
},
});
nodeIdSet.add(nodeId);
// Connect statements to topics by name match
if (s.topic) {
const matchedTopicId = topicNodeMap.get(s.topic.toLowerCase());
if (matchedTopicId && nodeIdSet.has(matchedTopicId)) {
edges.push({ source: nodeId, target: matchedTopicId, type: 'topic_match', weight: 0.5 });
}
}
// Connect statements to nonprofits by org name match
for (const n of nonprofits) {
if (n.name.toLowerCase() === s.org_name.toLowerCase()) {
const npId = `np-${n.id}`;
if (nodeIdSet.has(npId)) {
edges.push({ source: nodeId, target: npId, type: 'advocacy_chain', weight: 0.8 });
}
}
}
}
// Data source nodes
for (const ds of dataSources) {
const nodeId = `ds-${ds.id}`;
nodes.push({
id: nodeId,
type: 'data_source',
label: ds.api_name,
data: {
score: Number(ds.relevance_score) || 0,
category: ds.category,
status: ds.status,
matched_topics: ds.matched_topics,
},
});
nodeIdSet.add(nodeId);
const catId = `cat-${ds.category ?? 'uncategorized'}`;
if (nodeIdSet.has(catId)) {
edges.push({ source: nodeId, target: catId, type: 'issue_overlap', weight: 0.4 });
}
}
// ── 12. NEW: contacts (LinkedIn network layer) ─────────────────────────
const contactRows = await safeQuery<{
id: string;
first_name: string;
last_name: string;
company: string | null;
headline: string | null;
matched_org_id: string | null;
relevance_score: number | null;
enrichment_status: string | null;
skills: string[] | null;
}>(
`SELECT id, first_name, last_name, company, headline, matched_org_id,
relevance_score, enrichment_status, skills
FROM contacts
WHERE enrichment_status IN ('enriched', 'pending')
ORDER BY relevance_score DESC NULLS LAST, created_at DESC
LIMIT 60`,
);
for (const ct of contactRows) {
const nodeId = `con-${ct.id}`;
nodes.push({
id: nodeId,
type: 'contact',
label: `${ct.first_name} ${ct.last_name}`,
data: {
score: Number(ct.relevance_score) || 0.3,
company: ct.company,
headline: ct.headline,
enrichment_status: ct.enrichment_status,
skills: ct.skills,
},
});
nodeIdSet.add(nodeId);
// employed_at: connect contact to matched org node
if (ct.matched_org_id) {
const orgNodeId = `np-${ct.matched_org_id}`;
if (nodeIdSet.has(orgNodeId)) {
edges.push({ source: nodeId, target: orgNodeId, type: 'employed_at', weight: 0.9 });
}
}
// interested_in: match skills → topic names, categories, and nonprofit missions
if (ct.skills && ct.skills.length > 0) {
const lowerSkills = ct.skills.map(s => s.toLowerCase());
const skillWords = lowerSkills.flatMap(s => s.split(/\s+/));
// Match against topic names (full substring)
for (const [topicName, topicNodeId] of Array.from(topicNodeMap.entries())) {
if (lowerSkills.some(sk => topicName.includes(sk) || sk.includes(topicName))) {
edges.push({ source: nodeId, target: topicNodeId, type: 'interested_in', weight: 0.4 });
}
}
// Match against category nodes (keyword overlap)
for (const [catName] of Array.from(categoryScores.entries())) {
const catLower = catName.toLowerCase().replace(/_/g, ' ');
if (lowerSkills.some(sk => catLower.includes(sk) || sk.includes(catLower))
|| skillWords.some(w => w.length > 3 && catLower.includes(w))) {
const catNodeId = `cat-${catName}`;
if (nodeIdSet.has(catNodeId)) {
edges.push({ source: nodeId, target: catNodeId, type: 'interested_in', weight: 0.3 });
}
}
}
// Match against nonprofit missions (keyword overlap for relevance)
for (const n of nonprofits) {
if (!n.mission) continue;
const missionLower = n.mission.toLowerCase();
const matches = lowerSkills.filter(sk =>
sk.split(/\s+/).some(w => w.length > 3 && missionLower.includes(w))
);
if (matches.length >= 2) {
const npNodeId = `np-${n.id}`;
if (nodeIdSet.has(npNodeId) && npNodeId !== `np-${ct.matched_org_id}`) {
edges.push({ source: nodeId, target: npNodeId, type: 'interested_in', weight: 0.35 });
}
}
}
}
}
// ── 13. NEW: journalists (reporter intelligence layer) ─────────────────
const journalistRows = await safeQuery<{
id: string;
name: string;
outlet: string;
beat: string | null;
article_count: number;
}>(
`SELECT j.id, j.name, j.outlet, j.beat,
COUNT(ja.id)::int as article_count
FROM journalists j
LEFT JOIN journalist_articles ja ON ja.journalist_id = j.id
WHERE j.is_active = true
GROUP BY j.id
ORDER BY COUNT(ja.id) DESC, j.created_at DESC
LIMIT 40`,
);
for (const j of journalistRows) {
const nodeId = `jrn-${j.id}`;
nodes.push({
id: nodeId,
type: 'journalist',
label: j.name,
data: {
score: Math.min(1, (Number(j.article_count) || 0) / 20),
outlet: j.outlet,
beat: j.beat,
article_count: j.article_count,
},
});
nodeIdSet.add(nodeId);
}
// co_workers edges: connect contacts sharing employer
const contactsByCompany = new Map<string, string[]>();
for (const ct of contactRows) {
if (!ct.company) continue;
const key = ct.company.toLowerCase();
if (!contactsByCompany.has(key)) contactsByCompany.set(key, []);
contactsByCompany.get(key)!.push(`con-${ct.id}`);
}
for (const [, ctIds] of Array.from(contactsByCompany.entries())) {
if (ctIds.length < 2) continue;
for (let i = 0; i < Math.min(ctIds.length, 4); i++) {
for (let j = i + 1; j < Math.min(ctIds.length, 4); j++) {
edges.push({ source: ctIds[i], target: ctIds[j], type: 'co_workers', weight: 0.5 });
}
}
}
// Cross-modal edges from mind_map_links table
// Map entity types to graph node prefixes for proper ID resolution
const typePrefixMap: Record<string, string> = {
contact: 'con', organization: 'np', topic: 'topic', journalist: 'jrn',
petition: 'pet', foundation: 'fnd', grant: 'grt',
statement: 'stmt', data_source: 'ds', country: 'ctr',
};
for (const link of mindMapLinks) {
const srcPrefix = typePrefixMap[link.source_type] ?? link.source_type;
const tgtPrefix = typePrefixMap[link.target_type] ?? link.target_type;
const srcNodeId = `${srcPrefix}-${link.source_id}`;
const tgtNodeId = `${tgtPrefix}-${link.target_id}`;
if (nodeIdSet.has(srcNodeId) && nodeIdSet.has(tgtNodeId)) {
edges.push({
source: srcNodeId,
target: tgtNodeId,
type: link.link_type,
weight: Number(link.weight) || 0.5,
});
}
}
// Geo match edges: connect nonprofits in same state
const stateNpMap = new Map<string, string[]>();
for (const n of nonprofits) {
if (!n.state) continue;
if (!stateNpMap.has(n.state)) stateNpMap.set(n.state, []);
stateNpMap.get(n.state)!.push(`np-${n.id}`);
}
for (const [, npIds] of Array.from(stateNpMap.entries())) {
if (npIds.length < 2) continue;
for (let i = 0; i < Math.min(npIds.length, 3); i++) {
for (let j = i + 1; j < Math.min(npIds.length, 3); j++) {
edges.push({ source: npIds[i], target: npIds[j], type: 'geo_match', weight: 0.3 });
}
}
}
// Deduplicate edges: keep highest weight for each source+target+type triple
const edgeMap = new Map<string, EnhancedEdge>();
for (const e of edges) {
const key = `${e.source}|${e.target}|${e.type}`;
const existing = edgeMap.get(key);
if (!existing || e.weight > existing.weight) {
edgeMap.set(key, e);
}
}
const dedupedEdges = Array.from(edgeMap.values());
const stats = {
total_nodes: nodes.length,
total_edges: dedupedEdges.length,
categories: categoryScores.size,
topics: topics.length,
petitions: petitions.length,
suggestions: suggestions.length,
debt_mentions: debtMentions.length,
nonprofits: nonprofits.length,
foundations: foundations.length,
countries: countries.length,
grants: grants.length,
statements: statements.length,
data_sources: dataSources.length,
contacts: contactRows.length,
journalists: journalistRows.length,
};
return NextResponse.json({ nodes, edges: dedupedEdges, stats });
} catch (err: unknown) {
console.error('[api/graph/mindmap-enhanced] Error:', (err as Error).message);
return NextResponse.json({ error: (err as Error).message }, { status: 500 });
}
}