← back to Norma
app/api/journalists/[id]/entity-flow/route.ts
340 lines
import { NextRequest, NextResponse } from 'next/server';
import { query } from '@/lib/db';
import { requireRole } from '@/lib/require-role';
/* ═══════════════════════════════════════════════════════════════════════════
GET /api/journalists/[id]/entity-flow
Returns entity connection flow for a journalist:
- Their recent articles
- Entities connected through mind_map_links (politicians, orgs, foundations)
- AI-detected patterns: entities that appear across multiple articles
Response:
{
journalist: { id, name, outlet, beat },
articles: [{ id, title, date, url, entities: [{ type, name, id, link_type }] }],
patterns: [{ description, entities: [{type, name}], overlap_count, articles: [title] }],
stats: { articles, entities, politicians, organizations, foundations }
}
═══════════════════════════════════════════════════════════════════════════ */
const ENTITY_TYPE_MAP: Record<string, string> = {
politician: 'politician',
organization: 'organization',
foundation: 'foundation',
journalist: 'journalist',
district: 'district',
};
export async function GET(
request: NextRequest,
{ params }: { params: Promise<{ id: string }> },
) {
const auth = requireRole(request, 'admin', 'staff');
if (auth instanceof NextResponse) return auth;
try {
const { id } = await params;
// Validate UUID
const uuidRegex = /^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$/i;
if (!uuidRegex.test(id)) {
return NextResponse.json({ error: 'Invalid journalist ID' }, { status: 400 });
}
// Fetch journalist
const jResult = await query(
'SELECT id, name, outlet, beat, bio, twitter_handle, email FROM journalists WHERE id = $1',
[id],
);
if (!jResult.rowCount) {
return NextResponse.json({ error: 'Journalist not found' }, { status: 404 });
}
const journalist = jResult.rows[0];
// Fetch recent articles (limit 20 for performance)
const articlesResult = await query(
`SELECT id, title, url, outlet, published_date, summary, topics, mentioned_orgs, mentioned_people
FROM journalist_articles
WHERE journalist_id = $1
ORDER BY published_date DESC NULLS LAST
LIMIT 20`,
[id],
);
const rawArticles = articlesResult.rows;
// ── Fetch all mind_map_links where this journalist is connected ───────────
// Direct links: journalist → entity
const directLinks = await query(
`SELECT source_type, source_id, source_label, target_type, target_id, target_label,
link_type, weight, metadata
FROM mind_map_links
WHERE (source_type = 'journalist' AND source_id = $1::uuid)
OR (target_type = 'journalist' AND target_id = $1::uuid)
ORDER BY weight DESC
LIMIT 200`,
[id],
);
// ── Resolve connected entity names ────────────────────────────────────────
// Group entity IDs by type for bulk lookup
const entityRefs: Map<string, { type: string; id: string; label?: string }> = new Map();
for (const link of directLinks.rows) {
const isSource = link.source_id === id;
const connectedId = isSource ? link.target_id : link.source_id;
const connectedType = isSource ? link.target_type : link.source_type;
const connectedLabel = isSource ? link.target_label : link.source_label;
if (connectedType !== 'journalist' && ENTITY_TYPE_MAP[connectedType]) {
entityRefs.set(connectedId, { type: connectedType, id: connectedId, label: connectedLabel });
}
}
// Bulk fetch entity names
const entityMap = new Map<string, { type: string; name: string; id: string; extra?: Record<string, unknown> }>();
const byType: Record<string, string[]> = {};
for (const ref of entityRefs.values()) {
if (!byType[ref.type]) byType[ref.type] = [];
byType[ref.type].push(ref.id);
}
const lookups: Promise<void>[] = [];
if (byType.politician?.length) {
lookups.push(
query(
`SELECT id, name, party, state, title FROM politicians WHERE id = ANY($1)`,
[byType.politician],
).then((res) => {
for (const r of res.rows) {
entityMap.set(r.id, { type: 'politician', name: r.name, id: r.id, extra: { party: r.party, state: r.state, title: r.title } });
}
}),
);
}
if (byType.organization?.length) {
lookups.push(
query(
`SELECT id, name, type, category, state FROM organizations WHERE id = ANY($1)`,
[byType.organization],
).then((res) => {
for (const r of res.rows) {
entityMap.set(r.id, { type: 'organization', name: r.name, id: r.id, extra: { orgType: r.type, category: r.category, state: r.state } });
}
}),
);
}
if (byType.district?.length) {
lookups.push(
query(
`SELECT id, state_abbr, district_number, representative_name, opportunity_score
FROM congressional_districts WHERE id = ANY($1)`,
[byType.district],
).then((res) => {
for (const r of res.rows) {
entityMap.set(r.id, {
type: 'district',
name: `${r.state_abbr}-${r.district_number}: ${r.representative_name || 'Unknown'}`,
id: r.id,
extra: { state: r.state_abbr, district: r.district_number, opportunity_score: r.opportunity_score },
});
}
}),
);
}
await Promise.all(lookups);
// Fall back to label from link row for anything not in entityMap
for (const ref of entityRefs.values()) {
if (!entityMap.has(ref.id) && ref.label) {
entityMap.set(ref.id, { type: ref.type, name: ref.label, id: ref.id });
}
}
// ── Build entity list for each article ────────────────────────────────────
// Strategy: match article text topics/orgs/people against entities we found
// Also use direct links from mind_map_links
// Build direct entity connections from mind_map_links
const directEntityIds = new Set<string>();
const linksByEntity = new Map<string, string>(); // entityId → link_type
for (const link of directLinks.rows) {
const isSource = link.source_id === id;
const connectedId = isSource ? link.target_id : link.source_id;
const connectedType = isSource ? link.target_type : link.source_type;
if (connectedType !== 'journalist') {
directEntityIds.add(connectedId);
linksByEntity.set(connectedId, link.link_type);
}
}
// ── Match articles to entities via topics/orgs overlap ──────────────────
// For each article, find which entities are mentioned (by name substring matching)
const articles = rawArticles.map((a) => {
const articleText = [
a.title || '',
a.summary || '',
...(Array.isArray(a.mentioned_orgs) ? a.mentioned_orgs : []),
...(Array.isArray(a.mentioned_people) ? a.mentioned_people : []),
...(Array.isArray(a.topics) ? a.topics : []),
].join(' ').toLowerCase();
const matchedEntities: Array<{ type: string; name: string; id: string; link_type: string; extra?: Record<string, unknown> }> = [];
for (const [entityId, entity] of entityMap) {
const nameLower = entity.name.toLowerCase();
// Check if entity name (or meaningful part) appears in article text
const nameParts = nameLower.split(' ').filter((p: string) => p.length > 3);
const hasMatch = nameLower.length > 4 && (
articleText.includes(nameLower) ||
(nameParts.length >= 2 && nameParts.slice(0, 2).every((p: string) => articleText.includes(p)))
);
if (hasMatch) {
matchedEntities.push({
type: entity.type,
name: entity.name,
id: entityId,
link_type: linksByEntity.get(entityId) || 'mentioned',
extra: entity.extra,
});
}
}
// If no article-text matches, use direct entity connections (top 5)
if (matchedEntities.length === 0 && directEntityIds.size > 0) {
const topEntities = Array.from(directEntityIds).slice(0, 5);
for (const eid of topEntities) {
const entity = entityMap.get(eid);
if (entity) {
matchedEntities.push({
type: entity.type,
name: entity.name,
id: eid,
link_type: linksByEntity.get(eid) || 'connected',
extra: entity.extra,
});
}
}
}
return {
id: a.id,
title: a.title,
date: a.published_date,
url: a.url,
outlet: a.outlet,
summary: a.summary,
topics: a.topics || [],
entities: matchedEntities.slice(0, 12), // cap per article
};
});
// ── Detect patterns: entities referenced across multiple articles ─────────
type EntityMapValue = { type: string; name: string; id: string; extra?: Record<string, unknown> };
const entityArticleMap = new Map<string, { count: number; articles: string[]; entity: EntityMapValue }>();
for (const article of articles) {
for (const entity of article.entities) {
const key = entity.id;
if (!entityArticleMap.has(key)) {
const ent = entityMap.get(key);
if (!ent) continue;
entityArticleMap.set(key, { count: 0, articles: [], entity: ent });
}
const rec = entityArticleMap.get(key)!;
rec.count++;
if (rec.articles.length < 4) rec.articles.push(article.title.slice(0, 60));
}
}
// Build patterns: entities appearing in 2+ articles
const patterns: Array<{
description: string;
entity: { type: string; name: string; id: string };
overlap_count: number;
articles: string[];
}> = [];
const sortedEntities = Array.from(entityArticleMap.entries())
.filter(([, rec]) => rec.count >= 2)
.sort((a, b) => b[1].count - a[1].count)
.slice(0, 8);
for (const [, rec] of sortedEntities) {
const typeLabel = rec.entity.type === 'politician' ? 'Congress member'
: rec.entity.type === 'organization' ? 'organization'
: rec.entity.type === 'district' ? 'congressional district'
: rec.entity.type;
patterns.push({
description: `${rec.entity.name} (${typeLabel}) appears in ${rec.count} articles — recurring connection`,
entity: { type: rec.entity.type, name: rec.entity.name, id: rec.entity.id },
overlap_count: rec.count,
articles: rec.articles,
});
}
// ── Cross-entity patterns: find surprising overlaps ──────────────────────
// Find pairs of entities (politician + org) that co-appear in articles
const coOccurrences = new Map<string, { count: number; entityA: string; entityB: string; entityAType: string; entityBType: string; articles: string[] }>();
for (const article of articles) {
const pols = article.entities.filter((e) => e.type === 'politician');
const orgs = article.entities.filter((e) => e.type === 'organization' || e.type === 'foundation');
for (const pol of pols) {
for (const org of orgs) {
const key = [pol.id, org.id].sort().join('|');
if (!coOccurrences.has(key)) {
coOccurrences.set(key, { count: 0, entityA: pol.name, entityB: org.name, entityAType: 'politician', entityBType: org.type, articles: [] });
}
const co = coOccurrences.get(key)!;
co.count++;
if (co.articles.length < 3) co.articles.push(article.title.slice(0, 60));
}
}
}
const surprisingOverlaps = Array.from(coOccurrences.values())
.filter((co) => co.count >= 2)
.sort((a, b) => b.count - a.count)
.slice(0, 4)
.map((co) => ({
description: `${co.entityA} + ${co.entityB} — co-referenced in ${co.count} articles`,
entity: { type: 'overlap', name: `${co.entityA} ↔ ${co.entityB}`, id: '' },
overlap_count: co.count,
articles: co.articles,
}));
const allPatterns = [...patterns, ...surprisingOverlaps];
// ── Stats ─────────────────────────────────────────────────────────────────
const allEntities = Array.from(entityMap.values());
const stats = {
articles: rawArticles.length,
entities: allEntities.length,
politicians: allEntities.filter((e) => e.type === 'politician').length,
organizations: allEntities.filter((e) => e.type === 'organization').length,
districts: allEntities.filter((e) => e.type === 'district').length,
patterns: allPatterns.length,
};
return NextResponse.json({
journalist,
articles,
allEntities: allEntities.map((e) => ({ ...e, link_type: linksByEntity.get(e.id) || 'connected' })),
patterns: allPatterns,
stats,
});
} catch (err) {
console.error('[journalists/[id]/entity-flow] Error:', (err as Error).message);
return NextResponse.json({ error: 'Failed to load entity flow' }, { status: 500 });
}
}