← back to Norma
app/api/ingest/community/route.ts
331 lines
import { NextRequest, NextResponse } from 'next/server';
import { query } from '@/lib/db';
import { requireRole } from '@/lib/require-role';
import {
searchRedditPosts,
fetchSubredditHot,
searchCongressionalRecords,
searchFederalRegisterMeetings,
searchStateLegislatureMinutes,
STUDENT_DEBT_SUBREDDITS,
type RedditPost,
type CongressionalRecord,
type FederalRegisterNotice,
type StateLegislatureResult,
} from '@/lib/community-data';
/**
* POST /api/ingest/community
*
* Ingests data from Reddit student debt communities and government meeting minutes.
* Accepts optional `sources` array: ['reddit', 'reddit_hot', 'congress', 'fed_register', 'state_minutes']
*/
export async function POST(request: NextRequest) {
const auth = requireRole(request, 'admin', 'staff');
if (auth instanceof NextResponse) return auth;
let body: { sources?: string[] } = {};
try {
body = await request.json();
} catch {
// no body = ingest all
}
const requestedSources = body.sources ?? ['reddit', 'reddit_hot', 'congress', 'fed_register'];
const stats: Record<string, { count: number; errors: string[] }> = {};
const startTime = Date.now();
// ── Helper: insert a Reddit post ────────────────────────────────────
async function insertRedditPost(post: RedditPost, statKey: string) {
try {
await query(
`INSERT INTO community_posts (
platform, source_id, subreddit, author, title, body, url,
score, num_comments, posted_at,
mentions_servicer, mentions_program, debt_amount,
sentiment, is_alumni, school_mentioned, tags, raw_data
) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18)
ON CONFLICT (platform, source_id) DO UPDATE SET
score = EXCLUDED.score,
num_comments = EXCLUDED.num_comments`,
[
'reddit',
post.id,
post.subreddit,
post.author,
post.title,
post.selftext || null,
post.permalink ? `https://www.reddit.com${post.permalink}` : post.url,
post.score,
post.num_comments,
post.created_utc ? new Date(post.created_utc * 1000) : null,
post.mentions_servicer?.join(', ') || null,
post.mentions_program?.join(', ') || null,
post.debt_amount || null,
post.sentiment || null,
post.is_alumni || false,
post.school_mentioned || null,
[post.subreddit, post.sentiment].filter(Boolean),
JSON.stringify(post),
],
);
stats[statKey].count++;
} catch (err) {
stats[statKey].errors.push('insert: ' + (err as Error).message);
}
}
// ── 1. Reddit Search ────────────────────────────────────────────────
if (requestedSources.includes('reddit')) {
stats.reddit_search = { count: 0, errors: [] };
try {
const searchQueries = [
'student loan forgiveness',
'student debt crisis',
'PSLF approved',
'MOHELA problems',
'IDR payment',
'SAVE plan',
'student loan default',
];
for (const q of searchQueries) {
try {
const result = await searchRedditPosts({
query: q,
sort: 'new',
time: 'month',
limit: 25,
});
for (const item of result.items) {
await insertRedditPost(item as RedditPost, 'reddit_search');
}
// Rate limit: wait 2s between Reddit requests
await new Promise((r) => setTimeout(r, 2000));
} catch (err) {
stats.reddit_search.errors.push(`search_${q}: ` + (err as Error).message);
}
}
} catch (err) {
stats.reddit_search.errors.push('reddit: ' + (err as Error).message);
}
}
// ── 2. Reddit Hot Posts from Key Subreddits ─────────────────────────
if (requestedSources.includes('reddit_hot')) {
stats.reddit_hot = { count: 0, errors: [] };
const subs = STUDENT_DEBT_SUBREDDITS.slice(0, 5); // Top 5
for (const sub of subs) {
try {
const result = await fetchSubredditHot(sub, 10);
for (const item of result.items) {
await insertRedditPost(item as RedditPost, 'reddit_hot');
}
await new Promise((r) => setTimeout(r, 2000));
} catch (err) {
stats.reddit_hot.errors.push(`hot_${sub}: ` + (err as Error).message);
}
}
}
// ── 3. Congressional Records ────────────────────────────────────────
if (requestedSources.includes('congress')) {
stats.congress = { count: 0, errors: [] };
try {
const queries = ['student debt', 'student loan', 'higher education act', 'pell grant'];
for (const q of queries) {
const result = await searchCongressionalRecords(q);
for (const item of result.items) {
const rec = item as CongressionalRecord;
try {
await query(
`INSERT INTO meeting_minutes (
source_type, source_name, geo_level, geo_name, meeting_date,
title, url, full_text,
mentions_student_debt, mentions_education,
extracted_data_points, tags, raw_data
) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13)
ON CONFLICT DO NOTHING`,
[
'federal_committee',
'Congressional Record',
'federal',
'United States',
rec.date || null,
rec.title,
rec.url || null,
rec.summary || null,
q.includes('debt') || q.includes('loan'),
true,
JSON.stringify(rec.data_points || []),
['congress', 'federal', ...q.split(' ')],
JSON.stringify(rec),
],
);
stats.congress.count++;
} catch (err) {
stats.congress.errors.push('insert: ' + (err as Error).message);
}
}
await new Promise((r) => setTimeout(r, 1000));
}
} catch (err) {
stats.congress.errors.push('search: ' + (err as Error).message);
}
}
// ── 4. Federal Register Meeting Notices ─────────────────────────────
if (requestedSources.includes('fed_register')) {
stats.fed_register = { count: 0, errors: [] };
try {
const queries = [
'student loan committee meeting',
'higher education advisory committee',
'negotiated rulemaking student',
'FAFSA simplification',
];
for (const q of queries) {
const result = await searchFederalRegisterMeetings(q);
for (const item of result.items) {
const notice = item as FederalRegisterNotice;
try {
await query(
`INSERT INTO meeting_minutes (
source_type, source_name, geo_level, geo_name, meeting_date,
title, url, full_text,
mentions_student_debt, mentions_education,
tags, raw_data
) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12)
ON CONFLICT DO NOTHING`,
[
'federal_committee',
notice.agencies?.[0] || 'Federal Register',
'federal',
'United States',
notice.publication_date || null,
notice.title,
notice.html_url || null,
notice.abstract || null,
true,
true,
['federal-register', 'meeting-notice', 'education'],
JSON.stringify(notice),
],
);
stats.fed_register.count++;
} catch (err) {
stats.fed_register.errors.push('insert: ' + (err as Error).message);
}
}
}
} catch (err) {
stats.fed_register.errors.push('search: ' + (err as Error).message);
}
}
// ── 5. State Legislature Minutes ────────────────────────────────────
if (requestedSources.includes('state_minutes')) {
stats.state_minutes = { count: 0, errors: [] };
try {
// NY Senate has a public API
const result = await searchStateLegislatureMinutes('NY', 'student loan');
for (const item of result.items) {
const rec = item as StateLegislatureResult;
try {
await query(
`INSERT INTO meeting_minutes (
source_type, source_name, geo_level, geo_name, meeting_date,
title, url, full_text,
mentions_student_debt, mentions_education,
tags, raw_data
) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12)
ON CONFLICT DO NOTHING`,
[
'state_legislature',
rec.source || 'New York State Senate',
'state',
rec.state || 'New York',
rec.date || null,
rec.title,
rec.url || null,
rec.body || null,
true,
true,
['state-legislature', rec.state?.toLowerCase() || 'ny', 'student-loan'],
JSON.stringify(rec),
],
);
stats.state_minutes.count++;
} catch (err) {
stats.state_minutes.errors.push('ny_insert: ' + (err as Error).message);
}
}
} catch (err) {
stats.state_minutes.errors.push('state: ' + (err as Error).message);
}
}
const elapsed = ((Date.now() - startTime) / 1000).toFixed(1);
// Update data source sync status
try {
if (stats.reddit_search || stats.reddit_hot) {
const totalReddit = (stats.reddit_search?.count ?? 0) + (stats.reddit_hot?.count ?? 0);
await query(
`UPDATE data_sources SET last_sync_at = NOW(), last_sync_status = 'success', last_sync_count = $1 WHERE source_key = 'reddit'`,
[totalReddit],
);
}
if (stats.congress || stats.fed_register) {
const totalMinutes = (stats.congress?.count ?? 0) + (stats.fed_register?.count ?? 0);
await query(
`UPDATE data_sources SET last_sync_at = NOW(), last_sync_status = 'success', last_sync_count = $1 WHERE source_key = 'meeting_minutes'`,
[totalMinutes],
);
}
} catch {
// Non-critical
}
return NextResponse.json({
success: true,
elapsed_seconds: elapsed,
stats,
message: Object.entries(stats)
.map(([k, v]) => `${k}: ${v.count} records`)
.join(', '),
});
}
/* ─── GET: Return community data summary ───────────────────────────── */
export async function GET(request: NextRequest) {
const auth = requireRole(request, 'admin', 'staff');
if (auth instanceof NextResponse) return auth;
try {
const [redditStats, minutesStats, topSubreddits, sentimentBreakdown] = await Promise.all([
query(`SELECT COUNT(*) as total, COUNT(DISTINCT subreddit) as subreddits, AVG(score) as avg_score FROM community_posts WHERE platform = 'reddit'`),
query(`SELECT COUNT(*) as total, COUNT(DISTINCT source_type) as source_types FROM meeting_minutes`),
query(`SELECT subreddit, COUNT(*) as cnt, AVG(score) as avg_score FROM community_posts WHERE platform='reddit' GROUP BY subreddit ORDER BY cnt DESC LIMIT 10`),
query(`SELECT sentiment, COUNT(*) as cnt FROM community_posts WHERE sentiment IS NOT NULL GROUP BY sentiment ORDER BY cnt DESC`),
]);
return NextResponse.json({
reddit: {
...redditStats.rows[0],
top_subreddits: topSubreddits.rows,
sentiment: sentimentBreakdown.rows,
},
meeting_minutes: minutesStats.rows[0],
});
} catch (err) {
console.error('[ingest/community] GET error:', (err as Error).message);
return NextResponse.json({ error: 'Failed to fetch community data' }, { status: 500 });
}
}