← back to Norma
app/api/scraper/student-debt/route.ts
286 lines
import { NextRequest, NextResponse } from 'next/server';
import { query } from '@/lib/db';
import { requireRole } from '@/lib/require-role';
import { scrapeAllStudentDebt, type StudentDebtMention } from '@/lib/student-debt-scraper';
/**
* POST /api/scraper/student-debt
* Run student debt scraper only. Stores results in student_debt_mentions.
* Requires auth.
*/
export async function POST(request: NextRequest) {
const auth = requireRole(request, 'admin', 'staff');
if (auth instanceof NextResponse) return auth;
try {
const start = Date.now();
// Generate batch ID
const now = new Date();
const pad = (n: number) => String(n).padStart(2, '0');
const batchId = `sd_${now.getFullYear()}${pad(now.getMonth() + 1)}${pad(now.getDate())}_${pad(now.getHours())}${pad(now.getMinutes())}${pad(now.getSeconds())}`;
const mentions: StudentDebtMention[] = await scrapeAllStudentDebt(100);
let insertedCount = 0;
let errorCount = 0;
for (const m of mentions) {
try {
if (m.platformId) {
await query(
`INSERT INTO student_debt_mentions
(source, platform_id, title, body, url, author, subreddit,
engagement_score, comment_count, share_count, view_count,
sentiment, topics, servicers_mentioned, programs_mentioned,
debt_amount, is_viral, posted_at, scrape_batch)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19)
ON CONFLICT (source, platform_id) DO UPDATE SET
title = COALESCE(EXCLUDED.title, student_debt_mentions.title),
body = COALESCE(EXCLUDED.body, student_debt_mentions.body),
engagement_score = GREATEST(EXCLUDED.engagement_score, student_debt_mentions.engagement_score),
comment_count = GREATEST(EXCLUDED.comment_count, student_debt_mentions.comment_count),
sentiment = COALESCE(EXCLUDED.sentiment, student_debt_mentions.sentiment),
topics = EXCLUDED.topics,
servicers_mentioned = EXCLUDED.servicers_mentioned,
programs_mentioned = EXCLUDED.programs_mentioned,
debt_amount = COALESCE(EXCLUDED.debt_amount, student_debt_mentions.debt_amount),
is_viral = EXCLUDED.is_viral,
scrape_batch = EXCLUDED.scrape_batch,
scraped_at = NOW()`,
[
m.source,
m.platformId,
m.title?.slice(0, 1000) ?? null,
m.body?.slice(0, 10000) ?? null,
m.url,
m.author,
m.subreddit,
m.engagementScore,
m.commentCount,
m.shareCount,
m.viewCount,
m.sentiment,
m.topics,
m.servicersMentioned,
m.programsMentioned,
m.debtAmount,
m.isViral,
m.postedAt,
batchId,
],
);
} else {
await query(
`INSERT INTO student_debt_mentions
(source, title, body, url, author, subreddit,
engagement_score, comment_count, share_count, view_count,
sentiment, topics, servicers_mentioned, programs_mentioned,
debt_amount, is_viral, posted_at, scrape_batch)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18)`,
[
m.source,
m.title?.slice(0, 1000) ?? null,
m.body?.slice(0, 10000) ?? null,
m.url,
m.author,
m.subreddit,
m.engagementScore,
m.commentCount,
m.shareCount,
m.viewCount,
m.sentiment,
m.topics,
m.servicersMentioned,
m.programsMentioned,
m.debtAmount,
m.isViral,
m.postedAt,
batchId,
],
);
}
insertedCount++;
} catch (err) {
errorCount++;
console.error('[scraper/student-debt] Insert failed:', (err as Error).message);
}
}
const duration = Date.now() - start;
return NextResponse.json({
batch_id: batchId,
scraped: mentions.length,
inserted: insertedCount,
errors: errorCount,
duration_ms: duration,
sources: {
reddit: mentions.filter((m) => m.source === 'reddit').length,
news: mentions.filter((m) => m.source === 'news').length,
},
});
} catch (err) {
console.error('[api/scraper/student-debt] POST error:', (err as Error).message);
return NextResponse.json({ error: 'Failed to run student debt scraper' }, { status: 500 });
}
}
/**
* GET /api/scraper/student-debt
* Returns student debt mentions with filters and aggregate stats.
* Query params:
* ?source=reddit — filter by source (reddit, news)
* &sentiment=frustrated — filter by sentiment (frustrated, hopeful, informational, angry, confused)
* &viral_only=true — only return viral mentions
* &limit=50 — max results (default 50, max 200)
* &offset=0 — pagination offset
* &hours=168 — time window in hours (default 168 = 7 days)
*/
export async function GET(request: NextRequest) {
const auth = requireRole(request, 'admin', 'staff');
if (auth instanceof NextResponse) return auth;
try {
const { searchParams } = new URL(request.url);
const source = searchParams.get('source');
const sentiment = searchParams.get('sentiment');
const limit = Math.min(Math.max(parseInt(searchParams.get('limit') || '50', 10) || 50, 1), 200);
const offset = Math.max(parseInt(searchParams.get('offset') || '0', 10) || 0, 0);
const viralOnly = searchParams.get('viral_only') === 'true';
const hours = Math.max(parseInt(searchParams.get('hours') || '168', 10) || 168, 1);
// Build parameterized query with dynamic conditions
const conditions: string[] = [];
const values: unknown[] = [];
let paramIndex = 1;
// Time window filter
conditions.push(`scraped_at > NOW() - ($${paramIndex} || ' hours')::interval`);
values.push(hours.toString());
paramIndex++;
if (source) {
conditions.push(`source = $${paramIndex}`);
values.push(source);
paramIndex++;
}
if (sentiment) {
conditions.push(`sentiment = $${paramIndex}`);
values.push(sentiment);
paramIndex++;
}
if (viralOnly) {
conditions.push(`is_viral = true`);
}
const whereClause = conditions.length > 0 ? `WHERE ${conditions.join(' AND ')}` : '';
// Fetch filtered rows
values.push(limit);
const limitParam = paramIndex++;
values.push(offset);
const offsetParam = paramIndex++;
const result = await query(
`SELECT * FROM student_debt_mentions
${whereClause}
ORDER BY engagement_score DESC NULLS LAST
LIMIT $${limitParam} OFFSET $${offsetParam}`,
values,
);
// Build stats query (same conditions, no limit/offset)
const statsValues: unknown[] = [hours.toString()];
const statsConditions: string[] = [`scraped_at > NOW() - ($1 || ' hours')::interval`];
let statsParamIndex = 2;
if (source) {
statsConditions.push(`source = $${statsParamIndex}`);
statsValues.push(source);
statsParamIndex++;
}
if (sentiment) {
statsConditions.push(`sentiment = $${statsParamIndex}`);
statsValues.push(sentiment);
statsParamIndex++;
}
if (viralOnly) {
statsConditions.push(`is_viral = true`);
}
const statsWhere = statsConditions.length > 0 ? `WHERE ${statsConditions.join(' AND ')}` : '';
// Get total count and viral count
const countResult = await query(
`SELECT
COUNT(*) as total,
COUNT(*) FILTER (WHERE is_viral) as viral_count,
COUNT(DISTINCT source) as sources
FROM student_debt_mentions
${statsWhere}`,
statsValues,
);
// Get top servicers mentioned
const servicersResult = await query(
`SELECT s as servicer, COUNT(*) as cnt
FROM student_debt_mentions, unnest(servicers_mentioned) s
WHERE scraped_at > NOW() - ($1 || ' hours')::interval
GROUP BY s ORDER BY cnt DESC LIMIT 10`,
[hours.toString()],
);
// Get top programs mentioned
const programsResult = await query(
`SELECT p as program, COUNT(*) as cnt
FROM student_debt_mentions, unnest(programs_mentioned) p
WHERE scraped_at > NOW() - ($1 || ' hours')::interval
GROUP BY p ORDER BY cnt DESC LIMIT 10`,
[hours.toString()],
);
// Get sentiment breakdown
const sentimentResult = await query(
`SELECT sentiment, COUNT(*) as cnt
FROM student_debt_mentions
WHERE scraped_at > NOW() - ($1 || ' hours')::interval
AND sentiment IS NOT NULL
GROUP BY sentiment ORDER BY cnt DESC`,
[hours.toString()],
);
const stats = {
total: parseInt(countResult.rows[0]?.total || '0', 10),
viral_count: parseInt(countResult.rows[0]?.viral_count || '0', 10),
sources: parseInt(countResult.rows[0]?.sources || '0', 10),
top_servicers: servicersResult.rows.map((r) => ({
name: (r as Record<string, string>).servicer,
count: parseInt((r as Record<string, string>).cnt, 10),
})),
top_programs: programsResult.rows.map((r) => ({
name: (r as Record<string, string>).program,
count: parseInt((r as Record<string, string>).cnt, 10),
})),
sentiment_breakdown: sentimentResult.rows.map((r) => ({
sentiment: (r as Record<string, string>).sentiment,
count: parseInt((r as Record<string, string>).cnt, 10),
})),
};
return NextResponse.json({
mentions: result.rows,
rows: result.rows, // Backward compat
count: result.rows.length,
total: stats.total,
stats,
});
} catch (err) {
console.error('[api/scraper/student-debt] GET error:', (err as Error).message);
return NextResponse.json({ error: 'Failed to fetch student debt mentions' }, { status: 500 });
}
}