← back to Norma
app/api/articles/link/route.ts
225 lines
import { NextRequest, NextResponse } from 'next/server';
import { query } from '@/lib/db';
import { requireRole } from '@/lib/require-role';
import { getOrgId } from '@/lib/orgId';
/* ═══════════════════════════════════════════════════════════════════════════
GET /api/articles/link
Query: ?journalist_id=UUID&limit=20
Returns linked articles for a journalist (from journalist_articles table)
═══════════════════════════════════════════════════════════════════════════ */
export async function GET(req: NextRequest) {
const auth = requireRole(req, 'admin', 'staff');
if (auth instanceof NextResponse) return auth;
const { searchParams } = new URL(req.url);
const journalistId = searchParams.get('journalist_id');
if (!journalistId) {
return NextResponse.json({ error: 'journalist_id is required' }, { status: 400 });
}
let limit = parseInt(searchParams.get('limit') || '20', 10);
if (isNaN(limit) || limit < 1) limit = 20;
if (limit > 100) limit = 100;
try {
const { rows: articles } = await query(
`SELECT
ja.id,
ja.title,
ja.url,
ja.outlet,
ja.published_date,
ja.summary,
ja.tags,
ja.topics,
ja.sentiment,
ja.word_count,
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 = $1
ORDER BY ja.published_date DESC
LIMIT $2`,
[journalistId, limit]
);
// Count total linked for this journalist
const { rows: countRows } = await query(
`SELECT COUNT(*)::int AS total FROM journalist_articles WHERE journalist_id = $1`,
[journalistId]
);
return NextResponse.json({
articles,
total: countRows[0]?.total ?? 0,
});
} catch (err: unknown) {
const msg = err instanceof Error ? err.message : 'Unknown error';
console.error('[articles/link GET]', msg);
return NextResponse.json({ error: 'Internal server error' }, { status: 500 });
}
}
/* ═══════════════════════════════════════════════════════════════════════════
POST /api/articles/link
Body: {
journalist_id: UUID,
article: {
title: string,
url?: string,
outlet?: string,
published_date: string, // ISO date string
summary?: string,
tags?: string[],
topics?: string[],
mentioned_orgs?: string[],
mentioned_people?: string[],
},
entity_ids?: Array<{ type: string; id: string; name?: string }>
}
Actions:
1. Upsert article into journalist_articles linked to journalist
2. Create mind_map_links entries connecting journalist → entities
3. Create org_activity entry of type 'article_linked'
═══════════════════════════════════════════════════════════════════════════ */
export async function POST(req: NextRequest) {
const auth = requireRole(req, 'admin', 'staff');
if (auth instanceof NextResponse) return auth;
const orgId = auth.role === 'admin' ? getOrgId(req) : auth.orgId;
try {
const body = await req.json();
const { journalist_id, article, entity_ids = [] } = body as {
journalist_id: string;
article: {
title: string;
url?: string;
outlet?: string;
published_date: string;
summary?: string;
tags?: string[];
topics?: string[];
mentioned_orgs?: string[];
mentioned_people?: string[];
};
entity_ids?: Array<{ type: string; id: string; name?: string }>;
};
if (!journalist_id || !article?.title || !article?.published_date) {
return NextResponse.json(
{ error: 'journalist_id, article.title, and article.published_date are required' },
{ status: 400 }
);
}
// Verify journalist exists and get their name
const { rows: journalistRows } = await query(
`SELECT id, name, outlet FROM journalists WHERE id = $1`,
[journalist_id]
);
if (!journalistRows.length) {
return NextResponse.json({ error: 'Journalist not found' }, { status: 404 });
}
const journalist = journalistRows[0];
// 1. Upsert the article into journalist_articles
const { rows: articleRows } = await query(
`INSERT INTO journalist_articles
(journalist_id, title, url, outlet, published_date, summary, tags, topics, mentioned_orgs, mentioned_people)
VALUES ($1, $2, $3, $4, $5::date, $6, $7, $8, $9, $10)
ON CONFLICT (url) WHERE url IS NOT NULL
DO UPDATE SET
title = EXCLUDED.title,
summary = COALESCE(EXCLUDED.summary, journalist_articles.summary),
tags = COALESCE(EXCLUDED.tags, journalist_articles.tags),
topics = COALESCE(EXCLUDED.topics, journalist_articles.topics),
mentioned_orgs = COALESCE(EXCLUDED.mentioned_orgs, journalist_articles.mentioned_orgs),
mentioned_people = COALESCE(EXCLUDED.mentioned_people, journalist_articles.mentioned_people)
RETURNING id, title, url, outlet, published_date`,
[
journalist_id,
article.title,
article.url || null,
article.outlet || journalist.outlet,
article.published_date,
article.summary || null,
article.tags || [],
article.topics || [],
article.mentioned_orgs || [],
article.mentioned_people || [],
]
);
const savedArticle = articleRows[0];
// 2. Create mind_map_links: journalist → each entity
const linkedEntities: { type: string; id: string; name: string }[] = [];
for (const entity of entity_ids) {
if (!entity.type || !entity.id) continue;
try {
await query(
`INSERT INTO mind_map_links
(source_type, source_id, target_type, target_id, link_type, weight, source_label, target_label, auto_generated, metadata)
VALUES ('journalist', $1, $2, $3, 'article_mention', 0.8, $4, $5, false, $6)
ON CONFLICT (source_type, source_id, target_type, target_id, link_type) DO NOTHING`,
[
journalist_id,
entity.type,
entity.id,
journalist.name,
entity.name || entity.id,
JSON.stringify({ article_id: savedArticle.id, article_title: article.title }),
]
);
linkedEntities.push({ type: entity.type, id: entity.id, name: entity.name || entity.id });
} catch {
// Skip duplicate link errors silently
}
}
// 3. Create org_activity entry for article_linked
const effectiveOrgId = orgId || '53e2a0e7-c2b3-47c7-a292-9aeda183f934';
const pubDate = new Date(article.published_date).toLocaleDateString('en-US', {
month: 'short', day: 'numeric', year: 'numeric',
});
try {
await query(
`INSERT INTO org_activity
(org_id, activity_type, entity_type, entity_id, entity_name, summary, delta, relevance_score)
VALUES ($1, 'article_linked', 'journalist', $2, $3, $4, $5, $6)`,
[
effectiveOrgId,
journalist_id,
journalist.name,
`${journalist.name} (${journalist.outlet}) published "${article.title}" on ${pubDate}`,
JSON.stringify({ article_id: savedArticle.id, url: article.url || null, linked_entities: linkedEntities }),
linkedEntities.length > 0 ? Math.min(50 + linkedEntities.length * 10, 95) : 40,
]
);
} catch (actErr) {
// Don't fail the whole request if activity insert fails
console.warn('[articles/link] Activity insert failed:', actErr);
}
return NextResponse.json({
article: savedArticle,
linked_entities: linkedEntities,
mind_map_links_created: linkedEntities.length,
}, { status: 201 });
} catch (err: unknown) {
const msg = err instanceof Error ? err.message : 'Unknown error';
console.error('[articles/link POST]', msg);
return NextResponse.json({ error: 'Internal server error' }, { status: 500 });
}
}