← back to Norma
app/api/radar/route.ts
209 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/radar — Unified Topic Radar ─────────────────────────────── */
export async function GET(req: NextRequest) {
const auth = requireRole(req, 'admin', 'staff');
if (auth instanceof NextResponse) return auth;
try {
const orgId = auth.role === 'admin' ? getOrgId(req) : auth.orgId;
// Fetch from all 3 sources in parallel
const [topicsRes, pipelineRes, actionsRes] = await Promise.all([
// 1. Pulse AI topics (name column, article_count/sentiment in geo_heat)
query(`
SELECT
t.id,
t.name AS title,
t.description,
t.category,
t.urgency,
COALESCE((SELECT SUM(gh.article_count) FROM pulse_geo_heat gh WHERE gh.topic_id = t.id), 0) AS article_count,
COALESCE((SELECT AVG(gh.sentiment_avg) FROM pulse_geo_heat gh WHERE gh.topic_id = t.id), 0) AS avg_sentiment,
t.created_at,
COALESCE(
(SELECT json_agg(DISTINCT gh.state) FROM pulse_geo_heat gh WHERE gh.topic_id = t.id AND gh.heat_score > 5),
'[]'
) AS geo_states,
COALESCE(
(SELECT SUM(gh.heat_score) FROM pulse_geo_heat gh WHERE gh.topic_id = t.id),
0
) AS total_heat
FROM pulse_topics t
ORDER BY
CASE t.urgency
WHEN 'critical' THEN 0
WHEN 'high' THEN 1
WHEN 'medium' THEN 2
WHEN 'low' THEN 3
ELSE 4
END
`),
// 2. Pipeline petition suggestions (filtered by org_id)
orgId
? query(`
SELECT
id, title, body, target, category, platform, tone, status,
ai_confidence, signature_count, signature_goal, geo_states,
created_at, updated_at
FROM petition_pipeline
WHERE org_id = $1
ORDER BY created_at DESC
LIMIT 50
`, [orgId])
: query(`
SELECT
id, title, body, target, category, platform, tone, status,
ai_confidence, signature_count, signature_goal, geo_states,
created_at, updated_at
FROM petition_pipeline
ORDER BY created_at DESC
LIMIT 50
`),
// 3. Agent monitoring data — recent social discoveries
query(`
SELECT
id,
agent,
action_type,
platform,
content,
response_data,
created_at
FROM sdcc_agent_actions
WHERE action_type IN ('discover', 'monitor')
ORDER BY created_at DESC
LIMIT 30
`),
]);
// Build unified radar items
const radarItems: RadarItem[] = [];
// Map pulse topics
for (const t of topicsRes.rows) {
const geoStates = Array.isArray(t.geo_states) ? t.geo_states.filter(Boolean) : [];
radarItems.push({
id: t.id,
source: 'pulse',
title: t.title,
description: t.description,
category: t.category || 'general',
urgency: t.urgency || 'medium',
score: computeScore(t),
article_count: t.article_count || 0,
avg_sentiment: parseFloat(t.avg_sentiment) || 0,
total_heat: parseFloat(t.total_heat) || 0,
geo_states: geoStates,
geo_state_count: geoStates.length,
platform: null,
pipeline_status: null,
ai_confidence: null,
signature_count: null,
social_mentions: 0,
created_at: t.created_at,
});
}
// Map pipeline items
for (const p of pipelineRes.rows) {
const geoStates = Array.isArray(p.geo_states) ? p.geo_states : [];
radarItems.push({
id: p.id,
source: 'pipeline',
title: p.title,
description: p.body?.substring(0, 200) || null,
category: p.category || 'petition',
urgency: statusToUrgency(p.status),
score: (p.ai_confidence || 0) * 100,
article_count: 0,
avg_sentiment: 0,
total_heat: 0,
geo_states: geoStates,
geo_state_count: geoStates.length,
platform: p.platform,
pipeline_status: p.status,
ai_confidence: p.ai_confidence,
signature_count: p.signature_count || 0,
social_mentions: 0,
created_at: p.created_at,
});
}
// Count social mentions per topic from agent actions
const agentActions = actionsRes.rows || [];
for (const topic of topicsRes.rows) {
const keyword = (topic.title || '').toLowerCase().split(' ')[0];
if (!keyword) continue;
const mentions = agentActions.filter((act: Record<string, unknown>) => {
const text = [act.content, act.response_data ? JSON.stringify(act.response_data) : '']
.join(' ')
.toLowerCase();
return text.includes(keyword);
}).length;
const item = radarItems.find(r => r.id === topic.id);
if (item) item.social_mentions = mentions;
}
return NextResponse.json({
items: radarItems,
total: radarItems.length,
sources: {
pulse: topicsRes.rows.length,
pipeline: pipelineRes.rows.length,
social_actions: agentActions.length,
},
});
} catch (err) {
console.error('[radar] Error:', err);
return NextResponse.json({ error: 'Failed to fetch radar data' }, { status: 500 });
}
}
/* ─── Types ──────────────────────────────────────────────────────────────── */
interface RadarItem {
id: string;
source: 'pulse' | 'pipeline' | 'social';
title: string;
description: string | null;
category: string;
urgency: string;
score: number;
article_count: number;
avg_sentiment: number;
total_heat: number;
geo_states: string[];
geo_state_count: number;
platform: string | null;
pipeline_status: string | null;
ai_confidence: number | null;
signature_count: number | null;
social_mentions: number;
created_at: string;
}
/* ─── Helpers ────────────────────────────────────────────────────────────── */
function computeScore(topic: Record<string, unknown>): number {
const urgencyWeight: Record<string, number> = { critical: 100, high: 75, medium: 50, low: 25 };
const base = urgencyWeight[topic.urgency as string] || 50;
const articleBonus = Math.min(((topic.article_count as number) || 0) * 5, 50);
const heatBonus = Math.min(((parseFloat(topic.total_heat as string)) || 0) / 20, 30);
const sentimentPenalty = ((parseFloat(topic.avg_sentiment as string)) || 0) < -0.3 ? 10 : 0;
return Math.round(Math.min(base + articleBonus + heatBonus + sentimentPenalty, 100));
}
function statusToUrgency(status: string): string {
switch (status) {
case 'live':
case 'posting': return 'critical';
case 'approved': return 'high';
case 'reviewed': return 'medium';
default: return 'low';
}
}