← back to Norma
app/api/scraper/run/route.ts
317 lines
import { NextRequest, NextResponse } from 'next/server';
import { query } from '@/lib/db';
import { requireRole } from '@/lib/require-role';
import { scrapeAllSocial, type TrendingTopic } from '@/lib/social-scraper';
import { scrapeAllPetitions, type TrendingPetition } from '@/lib/petition-scraper';
import { scrapeAllStudentDebt, type StudentDebtMention } from '@/lib/student-debt-scraper';
/**
* POST /api/scraper/run
* Master endpoint that runs ALL scrapers (social + petitions + student debt).
* Generates a scrape_batch ID, runs scrapers in parallel, stores results in DB.
* Requires auth.
*/
export async function POST(request: NextRequest) {
const auth = requireRole(request, 'admin', 'staff');
if (auth instanceof NextResponse) return auth;
const overallStart = Date.now();
// Generate batch ID: batch_YYYYMMDD_HHmmss
const now = new Date();
const pad = (n: number) => String(n).padStart(2, '0');
const batchId = `batch_${now.getFullYear()}${pad(now.getMonth() + 1)}${pad(now.getDate())}_${pad(now.getHours())}${pad(now.getMinutes())}${pad(now.getSeconds())}`;
const timings: Record<string, number> = {};
const counts: Record<string, number> = {};
const errors: Record<string, string> = {};
// Run all 3 scrapers in parallel
const [socialResult, petitionResult, studentDebtResult] = await Promise.allSettled([
(async () => {
const start = Date.now();
const topics = await scrapeAllSocial(100);
timings.social = Date.now() - start;
return topics;
})(),
(async () => {
const start = Date.now();
const petitions = await scrapeAllPetitions(100);
timings.petitions = Date.now() - start;
return petitions;
})(),
(async () => {
const start = Date.now();
const mentions = await scrapeAllStudentDebt(100);
timings.student_debt = Date.now() - start;
return mentions;
})(),
]);
// ── Store social/trending topics ─────────────────────────────────────────
if (socialResult.status === 'fulfilled') {
const topics: TrendingTopic[] = socialResult.value;
counts.social = topics.length;
for (const topic of topics) {
try {
await query(
`INSERT INTO trending_topics
(source, topic, category, score, mentions, velocity, url, sample_posts, related_topics, scrape_batch)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)`,
[
topic.source,
topic.topic.slice(0, 1000),
topic.category,
topic.score,
topic.mentions,
topic.velocity,
topic.url,
JSON.stringify(topic.samplePosts),
topic.relatedTopics,
batchId,
],
);
} catch (err) {
console.error('[scraper/run] Failed to insert trending topic:', (err as Error).message);
}
}
} else {
errors.social = String(socialResult.reason);
counts.social = 0;
}
// ── Store trending petitions ─────────────────────────────────────────────
if (petitionResult.status === 'fulfilled') {
const petitions: TrendingPetition[] = petitionResult.value;
counts.petitions = petitions.length;
for (const p of petitions) {
try {
// UPSERT: if we have a platform+petition_id, update existing record
if (p.petitionId) {
await query(
`INSERT INTO trending_petitions
(platform, petition_id, title, description, target, signature_count,
signature_goal, growth_rate, growth_pct, rank, category, url,
creator, created_date, is_climber, scrape_batch)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16)
ON CONFLICT (platform, petition_id) DO UPDATE SET
title = EXCLUDED.title,
description = COALESCE(EXCLUDED.description, trending_petitions.description),
target = COALESCE(EXCLUDED.target, trending_petitions.target),
signature_count = GREATEST(EXCLUDED.signature_count, trending_petitions.signature_count),
signature_goal = COALESCE(EXCLUDED.signature_goal, trending_petitions.signature_goal),
growth_rate = EXCLUDED.growth_rate,
growth_pct = EXCLUDED.growth_pct,
rank = EXCLUDED.rank,
category = COALESCE(EXCLUDED.category, trending_petitions.category),
url = COALESCE(EXCLUDED.url, trending_petitions.url),
is_climber = EXCLUDED.is_climber,
scrape_batch = EXCLUDED.scrape_batch,
scraped_at = NOW()`,
[
p.platform,
p.petitionId,
p.title.slice(0, 1000),
p.description?.slice(0, 5000) ?? null,
p.target,
p.signatureCount,
p.signatureGoal,
p.growthRate,
p.growthPct,
p.rank,
p.category,
p.url,
p.creator,
p.createdDate,
p.isClimber,
batchId,
],
);
} else {
// No petition_id: insert without UPSERT
await query(
`INSERT INTO trending_petitions
(platform, title, description, target, signature_count,
signature_goal, growth_rate, growth_pct, rank, category, url,
creator, created_date, is_climber, scrape_batch)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15)`,
[
p.platform,
p.title.slice(0, 1000),
p.description?.slice(0, 5000) ?? null,
p.target,
p.signatureCount,
p.signatureGoal,
p.growthRate,
p.growthPct,
p.rank,
p.category,
p.url,
p.creator,
p.createdDate,
p.isClimber,
batchId,
],
);
}
} catch (err) {
console.error('[scraper/run] Failed to insert trending petition:', (err as Error).message);
}
}
} else {
errors.petitions = String(petitionResult.reason);
counts.petitions = 0;
}
// ── Store student debt mentions ──────────────────────────────────────────
if (studentDebtResult.status === 'fulfilled') {
const mentions: StudentDebtMention[] = studentDebtResult.value;
counts.student_debt = mentions.length;
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,
],
);
}
} catch (err) {
console.error('[scraper/run] Failed to insert student debt mention:', (err as Error).message);
}
}
} else {
errors.student_debt = String(studentDebtResult.reason);
counts.student_debt = 0;
}
const totalDuration = Date.now() - overallStart;
return NextResponse.json({
batch_id: batchId,
counts,
timings,
errors: Object.keys(errors).length > 0 ? errors : undefined,
total_duration_ms: totalDuration,
});
}
/**
* GET /api/scraper/run
* Returns scraper run history — last 10 batches with counts.
*/
export async function GET(request: NextRequest) {
const auth = requireRole(request, 'admin', 'staff');
if (auth instanceof NextResponse) return auth;
try {
// Get distinct batch IDs with counts from all 3 tables
const result = await query(
`WITH batches AS (
SELECT scrape_batch, 'trending_topics' AS source_table, COUNT(*) AS cnt, MAX(scraped_at) AS scraped_at
FROM trending_topics
WHERE scrape_batch IS NOT NULL
GROUP BY scrape_batch
UNION ALL
SELECT scrape_batch, 'trending_petitions', COUNT(*), MAX(scraped_at)
FROM trending_petitions
WHERE scrape_batch IS NOT NULL
GROUP BY scrape_batch
UNION ALL
SELECT scrape_batch, 'student_debt_mentions', COUNT(*), MAX(scraped_at)
FROM student_debt_mentions
WHERE scrape_batch IS NOT NULL
GROUP BY scrape_batch
)
SELECT
scrape_batch,
MAX(scraped_at) AS scraped_at,
SUM(CASE WHEN source_table = 'trending_topics' THEN cnt ELSE 0 END) AS topic_count,
SUM(CASE WHEN source_table = 'trending_petitions' THEN cnt ELSE 0 END) AS petition_count,
SUM(CASE WHEN source_table = 'student_debt_mentions' THEN cnt ELSE 0 END) AS mention_count,
SUM(cnt) AS total_count
FROM batches
GROUP BY scrape_batch
ORDER BY MAX(scraped_at) DESC
LIMIT 10`,
);
return NextResponse.json({ batches: result.rows });
} catch (err) {
console.error('[api/scraper/run] GET error:', (err as Error).message);
return NextResponse.json({ error: 'Failed to fetch batch history' }, { status: 500 });
}
}