← back to Patty
app/api/orbit/rss/route.ts
296 lines
import { NextRequest, NextResponse } from 'next/server';
import { query } from '@/lib/db';
import { verifyAuth } from '@/lib/auth';
import { auditLog } from '@/lib/audit';
import { callGemini } from '@/lib/gemini';
// ---- Simple RSS/XML parser using regex ----
interface FeedItem {
title: string;
link: string;
description: string;
pubDate: string | null;
}
function extractTag(xml: string, tag: string): string {
// Handle both <tag>content</tag> and CDATA
const re = new RegExp(`<${tag}[^>]*>(?:<!\\[CDATA\\[)?(.*?)(?:\\]\\]>)?</${tag}>`, 's');
const m = xml.match(re);
return m ? m[1].trim() : '';
}
function parseRSS(xml: string): FeedItem[] {
const items: FeedItem[] = [];
// Match both <item> (RSS) and <entry> (Atom) elements
const itemRegex = /<(?:item|entry)[\s>]([\s\S]*?)<\/(?:item|entry)>/gi;
let match;
while ((match = itemRegex.exec(xml)) !== null) {
const block = match[1];
const title = extractTag(block, 'title');
// RSS uses <link>, Atom uses <link href="..."/>
let link = extractTag(block, 'link');
if (!link) {
const linkHref = block.match(/<link[^>]+href=["']([^"']+)["']/);
if (linkHref) link = linkHref[1];
}
const description = extractTag(block, 'description') || extractTag(block, 'summary') || extractTag(block, 'content');
const pubDate = extractTag(block, 'pubDate') || extractTag(block, 'published') || extractTag(block, 'updated') || null;
if (title && link) {
items.push({
title: title.replace(/<[^>]+>/g, '').trim(),
link: link.replace(/<[^>]+>/g, '').trim(),
description: description.replace(/<[^>]+>/g, '').substring(0, 500).trim(),
pubDate,
});
}
}
return items;
}
// ---- Gemini sentiment scoring ----
async function scoreSentiment(
titles: string[],
): Promise<Array<{ sentiment: string; score: number; relevance: number; tags: string[] }>> {
if (titles.length === 0) return [];
const prompt = `Analyze these news headlines for sentiment and political/advocacy relevance.
For each headline, return:
- sentiment: "positive", "negative", "neutral", or "mixed"
- score: float from -1.0 (very negative) to 1.0 (very positive)
- relevance: 0-100 score of how relevant this is to political advocacy, petitions, civic engagement
- tags: 1-3 topic tags
Headlines:
${titles.map((t, i) => `${i + 1}. ${t}`).join('\n')}
Return ONLY a JSON array with one object per headline, in the same order. Example:
[{"sentiment":"negative","score":-0.6,"relevance":85,"tags":["healthcare","policy"]}]`;
// 2026-05-05 (tick 29): migrated to lib/gemini.ts wrapper.
const r = await callGemini<unknown>({ prompt, maxTokens: 4096, temperature: 0.3, timeoutMs: 30_000 });
if (!r.ok) {
console.error('[orbit/rss] gemini failed:', r.reason, r.detail);
return titles.map(() => ({ sentiment: 'neutral', score: 0, relevance: 50, tags: [] }));
}
if (Array.isArray(r.data)) return r.data;
return titles.map(() => ({ sentiment: 'neutral', score: 0, relevance: 50, tags: [] }));
}
// ---- Match articles to Kalshi markets by keyword overlap ----
function matchToMarkets(articleTitle: string, markets: Array<{ ticker: string; title: string }>): string[] {
const matched: string[] = [];
const words = articleTitle.toLowerCase().split(/\W+/).filter((w) => w.length > 3);
for (const m of markets) {
const marketWords = m.title.toLowerCase().split(/\W+/).filter((w) => w.length > 3);
const overlap = words.filter((w) => marketWords.includes(w));
if (overlap.length >= 2) {
matched.push(m.ticker);
}
}
return matched;
}
/**
* GET /api/orbit/rss
* List feeds + recent articles
*/
export async function GET(request: NextRequest) {
const user = verifyAuth(request);
if (!user) return NextResponse.json({ error: 'Unauthorized' }, { status: 401 });
try {
const url = new URL(request.url);
const category = url.searchParams.get('category');
const limit = Math.min(parseInt(url.searchParams.get('limit') || '50', 10), 200);
// Fetch feeds
let feedsSql = `SELECT * FROM orbit_feeds`;
const feedsParams: unknown[] = [];
if (category && ['national', 'local', 'wire'].includes(category)) {
feedsParams.push(category);
feedsSql += ` WHERE category = $1`;
}
feedsSql += ` ORDER BY category, name`;
const feedsRes = await query(feedsSql, feedsParams);
// Fetch articles
let articlesSql = `SELECT oa.*, of.name as feed_name, of.station, of.category as feed_category
FROM orbit_articles oa
JOIN orbit_feeds of ON oa.feed_id = of.id`;
const articlesParams: unknown[] = [];
if (category && ['national', 'local', 'wire'].includes(category)) {
articlesParams.push(category);
articlesSql += ` WHERE of.category = $1`;
}
articlesParams.push(limit);
articlesSql += ` ORDER BY oa.published_at DESC NULLS LAST, oa.created_at DESC LIMIT $${articlesParams.length}`;
const articlesRes = await query(articlesSql, articlesParams);
// Stats
const statsRes = await query(
`SELECT
(SELECT COUNT(*)::int FROM orbit_feeds) as total_feeds,
(SELECT COUNT(*)::int FROM orbit_articles) as total_articles,
(SELECT MAX(last_fetched_at) FROM orbit_feeds) as last_fetch`,
);
return NextResponse.json({
feeds: feedsRes.rows,
articles: articlesRes.rows,
stats: statsRes.rows[0],
});
} catch (err) {
console.error('[api/orbit/rss] GET error:', (err as Error).message);
return NextResponse.json({ error: 'Failed to fetch RSS data' }, { status: 500 });
}
}
/**
* POST /api/orbit/rss
* Trigger manual RSS feed refresh
*/
export async function POST(request: NextRequest) {
const user = verifyAuth(request);
if (!user) return NextResponse.json({ error: 'Unauthorized' }, { status: 401 });
const errors: string[] = [];
let totalFetched = 0;
let totalNew = 0;
try {
// Get all active feeds
const feedsRes = await query(`SELECT * FROM orbit_feeds WHERE is_active = true ORDER BY name`);
const feeds = feedsRes.rows;
// Get current markets for keyword matching
const marketsRes = await query(`SELECT ticker, title FROM orbit_markets`);
const markets = marketsRes.rows as Array<{ ticker: string; title: string }>;
// Process feeds in parallel batches of 5
const batchSize = 5;
for (let i = 0; i < feeds.length; i += batchSize) {
const batch = feeds.slice(i, i + batchSize);
const results = await Promise.allSettled(
batch.map(async (feed: any) => {
try {
const res = await fetch(feed.url, {
headers: {
'User-Agent': 'PattyPetitions/1.0 (RSS Reader)',
Accept: 'application/rss+xml, application/xml, text/xml, application/atom+xml',
},
signal: AbortSignal.timeout(15000),
});
if (!res.ok) {
throw new Error(`HTTP ${res.status} for ${feed.name}`);
}
const xml = await res.text();
const items = parseRSS(xml);
totalFetched += items.length;
// Score sentiment in batches of 20
const newArticles: FeedItem[] = [];
for (const item of items) {
// Check if already exists
const existing = await query(
`SELECT id FROM orbit_articles WHERE feed_id = $1 AND url = $2`,
[feed.id, item.link],
);
if (existing.rows.length === 0) {
newArticles.push(item);
}
}
if (newArticles.length === 0) {
// Update last_fetched_at even if no new articles
await query(`UPDATE orbit_feeds SET last_fetched_at = NOW() WHERE id = $1`, [feed.id]);
return { feed: feed.name, fetched: items.length, new: 0 };
}
// Score new articles with Gemini (batch of up to 20)
const titlesToScore = newArticles.slice(0, 20).map((a) => a.title);
const scores = await scoreSentiment(titlesToScore);
// Insert new articles
let inserted = 0;
for (let j = 0; j < newArticles.length; j++) {
const article = newArticles[j];
const score = scores[j] || { sentiment: 'neutral', score: 0, relevance: 50, tags: [] };
const matchedMarkets = matchToMarkets(article.title, markets);
try {
await query(
`INSERT INTO orbit_articles (feed_id, title, url, summary, published_at,
sentiment, sentiment_score, relevance_score, matched_markets, tags)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
ON CONFLICT (feed_id, url) DO NOTHING`,
[
feed.id,
article.title,
article.link,
article.description || null,
article.pubDate ? new Date(article.pubDate).toISOString() : null,
score.sentiment,
score.score,
score.relevance,
matchedMarkets.length > 0 ? matchedMarkets : null,
score.tags?.length > 0 ? score.tags : null,
],
);
inserted++;
} catch (insertErr) {
console.error('[orbit/rss] Insert error:', (insertErr as Error).message);
}
}
totalNew += inserted;
// Update feed stats
await query(
`UPDATE orbit_feeds SET last_fetched_at = NOW(), article_count = article_count + $2 WHERE id = $1`,
[feed.id, inserted],
);
return { feed: feed.name, fetched: items.length, new: inserted };
} catch (feedErr) {
const msg = `${feed.name}: ${(feedErr as Error).message}`;
errors.push(msg);
return { feed: feed.name, fetched: 0, new: 0, error: msg };
}
}),
);
// Collect results
for (const r of results) {
if (r.status === 'rejected') {
errors.push(r.reason?.message || 'Unknown feed error');
}
}
}
await auditLog('orbit.rss_refresh', 'orbit_feed', null, {
feeds_processed: feeds.length,
total_fetched: totalFetched,
total_new: totalNew,
errors_count: errors.length,
});
return NextResponse.json({
fetched: totalFetched,
new_articles: totalNew,
feeds_processed: feeds.length,
errors,
});
} catch (err) {
console.error('[api/orbit/rss] POST error:', (err as Error).message);
return NextResponse.json({ error: 'Failed to refresh RSS feeds' }, { status: 500 });
}
}