← back to Norma
app/api/articles/discover/route.ts
211 lines
import { NextRequest, NextResponse } from 'next/server';
import { query } from '@/lib/db';
import { requireRole } from '@/lib/require-role';
/* ═══════════════════════════════════════════════════════════════════════════
POST /api/articles/discover
Body: { journalist_name: string, org_id?: UUID }
Searches journalist_articles WHERE journalist name ILIKE journalist_name.
For each article, scores relevance against the org's connected entities
(from mind_map_links) using mentioned_orgs, mentioned_people, mentioned_topics.
Returns: {
articles: [{
id, title, url, outlet, published_date, summary, journalist_name,
journalist_id, journalist_outlet, relevance_score,
matching_entities: [{ type, name, match_type }]
}],
journalists_found: [{ id, name, outlet }],
org_entities: number // total org connections used for scoring
}
═══════════════════════════════════════════════════════════════════════════ */
export async function POST(req: NextRequest) {
const auth = requireRole(req, 'admin', 'staff');
if (auth instanceof NextResponse) return auth;
try {
const body = await req.json();
const { journalist_name, org_id } = body as {
journalist_name: string;
org_id?: string;
};
if (!journalist_name || journalist_name.trim().length < 2) {
return NextResponse.json({ error: 'journalist_name must be at least 2 characters' }, { status: 400 });
}
const namePattern = `%${journalist_name.trim()}%`;
// Step 1: Find matching journalists by name
const { rows: matchedJournalists } = await query(
`SELECT id, name, outlet, beat
FROM journalists
WHERE name ILIKE $1 AND is_active = true
ORDER BY name ASC
LIMIT 20`,
[namePattern]
);
if (!matchedJournalists.length) {
return NextResponse.json({
articles: [],
journalists_found: [],
org_entities: 0,
message: `No journalists found matching "${journalist_name}"`,
});
}
const journalistIds = matchedJournalists.map((j) => (j as { id: string }).id);
// Step 2: Fetch all articles for those journalists
const { rows: articles } = await query(
`SELECT
ja.id,
ja.journalist_id,
ja.title,
ja.url,
ja.outlet,
ja.published_date,
ja.summary,
ja.tags,
ja.topics,
ja.mentioned_orgs,
ja.mentioned_people,
ja.mentioned_topics,
j.name AS journalist_name,
j.outlet AS journalist_outlet
FROM journalist_articles ja
JOIN journalists j ON j.id = ja.journalist_id
WHERE ja.journalist_id = ANY($1)
ORDER BY ja.published_date DESC
LIMIT 200`,
[journalistIds]
);
// Step 3: Load org-connected entity names for scoring
const effectiveOrgId = org_id || '53e2a0e7-c2b3-47c7-a292-9aeda183f934';
// Collect entity labels from mind_map_links where source or target is the org
const { rows: orgLinks } = await query(
`SELECT DISTINCT
LOWER(source_label) AS label, source_type AS type
FROM mind_map_links
WHERE source_type = 'organization'
AND source_id = $1
AND source_label != ''
UNION
SELECT DISTINCT
LOWER(target_label) AS label, target_type AS type
FROM mind_map_links
WHERE target_type != 'organization'
AND target_id IN (
SELECT target_id FROM mind_map_links WHERE source_type = 'organization' AND source_id = $1
)
AND target_label != ''
LIMIT 500`,
[effectiveOrgId]
);
// Also pull org's journalist_orgs relationships for direct org name matching
const { rows: orgJournalistOrgs } = await query(
`SELECT DISTINCT LOWER(jo.org_name) AS label, 'organization' AS type
FROM journalist_orgs jo
WHERE jo.journalist_id = ANY($1)`,
[journalistIds]
);
// Build a map of label → type for fast lookup
const entityMap = new Map<string, string>();
for (const row of [...orgLinks, ...orgJournalistOrgs]) {
if (row.label) entityMap.set(row.label, row.type as string);
}
const orgEntityCount = entityMap.size;
// Step 4: Score each article
interface ScoredArticle {
id: string;
journalist_id: string;
title: string;
url: string | null;
outlet: string | null;
published_date: string;
summary: string | null;
journalist_name: string;
journalist_outlet: string;
relevance_score: number;
matching_entities: Array<{ type: string; name: string; match_type: string }>;
tags: string[];
topics: string[];
}
const scoredArticles: ScoredArticle[] = [];
for (const art of articles) {
const matchingEntities: Array<{ type: string; name: string; match_type: string }> = [];
const seen = new Set<string>();
const checkAndAdd = (names: string[], matchType: string) => {
for (const name of names) {
if (!name) continue;
const lower = name.toLowerCase();
const entityType = entityMap.get(lower);
if (entityType && !seen.has(lower)) {
seen.add(lower);
matchingEntities.push({ type: entityType, name, match_type: matchType });
}
}
};
checkAndAdd(art.mentioned_orgs || [], 'mentioned_org');
checkAndAdd(art.mentioned_people || [], 'mentioned_person');
checkAndAdd(art.mentioned_topics || [], 'mentioned_topic');
checkAndAdd(art.tags || [], 'tag');
checkAndAdd(art.topics || [], 'topic');
// Base relevance: article has a non-empty summary = small base bonus
let score = 0;
if (matchingEntities.length > 0) {
score = Math.min(0.3 + matchingEntities.length * 0.15, 1.0);
} else if (art.summary) {
score = 0.1;
}
scoredArticles.push({
id: art.id as string,
journalist_id: art.journalist_id as string,
title: art.title as string,
url: (art.url as string) || null,
outlet: (art.outlet as string) || null,
published_date: art.published_date as string,
summary: (art.summary as string) || null,
journalist_name: art.journalist_name as string,
journalist_outlet: art.journalist_outlet as string,
relevance_score: score,
matching_entities: matchingEntities,
tags: (art.tags as string[]) || [],
topics: (art.topics as string[]) || [],
});
}
// Sort by relevance desc, then by date desc
scoredArticles.sort((a, b) => {
if (b.relevance_score !== a.relevance_score) return b.relevance_score - a.relevance_score;
return new Date(b.published_date).getTime() - new Date(a.published_date).getTime();
});
return NextResponse.json({
articles: scoredArticles,
journalists_found: matchedJournalists,
org_entities: orgEntityCount,
});
} catch (err: unknown) {
const msg = err instanceof Error ? err.message : 'Unknown error';
console.error('[articles/discover POST]', msg);
return NextResponse.json({ error: 'Internal server error' }, { status: 500 });
}
}