← back to Norma
app/api/data-explorer/route.ts
229 lines
import { NextRequest, NextResponse } from 'next/server';
import { query } from '@/lib/db';
import { requireRole } from '@/lib/require-role';
/**
* GET /api/data-explorer
*
* Unified data explorer endpoint. Returns aggregated stats across all
* government data sources: federal grants, CFPB complaints, Census education,
* College Scorecard, BLS earnings, Socrata datasets, meeting minutes, community posts.
*/
export async function GET(request: NextRequest) {
const auth = requireRole(request, 'admin', 'staff');
if (auth instanceof NextResponse) return auth;
try {
const [
sourceSyncStatus,
federalGrantStats,
cfpbStats,
censusStats,
scorecardStats,
blsStats,
socrataStats,
minutesStats,
communityStats,
] = await Promise.all([
// Data source sync status
query(`SELECT source_key, display_name, source_type, last_sync_at, last_sync_status, last_sync_count FROM data_sources WHERE is_active = true ORDER BY source_type, display_name`),
// Federal grants summary
query(`SELECT
COUNT(*) as total,
COUNT(*) FILTER (WHERE close_date >= CURRENT_DATE) as open,
COUNT(DISTINCT agency) as agencies,
COALESCE(AVG(award_ceiling), 0) as avg_ceiling
FROM federal_grants`),
// CFPB complaints summary
query(`SELECT
COUNT(*) as total,
COUNT(DISTINCT company) as companies,
COUNT(DISTINCT state) as states,
COUNT(*) FILTER (WHERE date_received >= CURRENT_DATE - INTERVAL '30 days') as last_30d
FROM cfpb_complaints`),
// Census education summary
query(`SELECT
COUNT(*) as total,
COUNT(DISTINCT geo_level) as levels,
COUNT(*) FILTER (WHERE geo_level = 'state') as states,
COUNT(*) FILTER (WHERE geo_level = 'county') as counties,
ROUND(AVG(bachelors_pct), 1) as avg_bachelors_pct
FROM census_education`),
// College Scorecard summary
query(`SELECT
COUNT(*) as total,
COUNT(DISTINCT state) as states,
ROUND(AVG(median_debt)) as avg_median_debt,
ROUND(AVG(pell_grant_rate)::numeric, 3) as avg_pell_rate,
ROUND(AVG(avg_net_price)) as avg_net_price,
ROUND(AVG(median_earnings_6yr)) as avg_earnings_6yr
FROM college_scorecard`),
// BLS earnings summary
query(`SELECT
COUNT(*) as total,
COUNT(DISTINCT education_level) as levels,
MIN(year) as earliest_year,
MAX(year) as latest_year
FROM bls_education_earnings`),
// Socrata datasets summary
query(`SELECT
COUNT(*) as total,
COUNT(DISTINCT portal_domain) as portals,
COUNT(*) FILTER (WHERE portal_level = 'state') as state_datasets,
COUNT(*) FILTER (WHERE portal_level = 'city') as city_datasets
FROM socrata_datasets`),
// Meeting minutes summary
query(`SELECT
COUNT(*) as total,
COUNT(DISTINCT source_type) as source_types,
COUNT(*) FILTER (WHERE mentions_student_debt = true) as student_debt_mentions,
COUNT(*) FILTER (WHERE mentions_education = true) as education_mentions
FROM meeting_minutes`),
// Community posts summary
query(`SELECT
COUNT(*) as total,
COUNT(DISTINCT subreddit) as subreddits,
COUNT(*) FILTER (WHERE sentiment = 'frustrated') as frustrated,
COUNT(*) FILTER (WHERE sentiment = 'positive') as positive,
COUNT(*) FILTER (WHERE is_alumni = true) as alumni_posts,
ROUND(AVG(score)) as avg_score
FROM community_posts WHERE platform = 'reddit'`),
]);
return NextResponse.json({
data_sources: sourceSyncStatus.rows,
summary: {
federal_grants: federalGrantStats.rows[0],
cfpb_complaints: cfpbStats.rows[0],
census_education: censusStats.rows[0],
college_scorecard: scorecardStats.rows[0],
bls_earnings: blsStats.rows[0],
socrata_datasets: socrataStats.rows[0],
meeting_minutes: minutesStats.rows[0],
community_posts: communityStats.rows[0],
},
});
} catch (err) {
console.error('[data-explorer] GET error:', (err as Error).message);
return NextResponse.json({ error: 'Failed to load data explorer' }, { status: 500 });
}
}
/**
* POST /api/data-explorer
*
* Query specific data tables with filters.
* Body: { table: string, filters?: Record<string, any>, limit?: number, offset?: number }
*/
export async function POST(request: NextRequest) {
const auth = requireRole(request, 'admin', 'staff');
if (auth instanceof NextResponse) return auth;
try {
const body = await request.json();
const { table, filters = {}, limit = 50, offset = 0 } = body as {
table: string;
filters?: Record<string, string | number | boolean>;
limit?: number;
offset?: number;
};
// Whitelist of queryable tables
const allowedTables: Record<string, string[]> = {
cfpb_complaints: ['state', 'company', 'issue'],
census_education: ['geo_level', 'state_fips', 'year'],
college_scorecard: ['state', 'ownership'],
bls_education_earnings: ['education_level', 'year'],
socrata_datasets: ['portal_level', 'portal_domain'],
meeting_minutes: ['source_type', 'geo_level'],
community_posts: ['platform', 'subreddit', 'sentiment'],
federal_grants: ['source_system', 'agency'],
drafts: ['lane', 'status', 'email_type', 'voice_mode', 'created_by'],
news_items: ['outlet', 'source_type', 'is_used'],
petitions: ['status', 'platform', 'category', 'is_featured'],
contacts: ['source', 'enrichment_status', 'location'],
organizations: ['type', 'category', 'state', 'onboard_status'],
journalists: ['outlet', 'beat', 'is_active'],
grants: ['status', 'priority', 'funder', 'is_bookmarked'],
sessions: ['status', 'created_by'],
audit_events: ['event_type', 'entity_type', 'actor'],
scheduled_dispatches: ['status', 'platform', 'content_type', 'created_by'],
pulse_topics: ['category', 'urgency'],
trending_topics: ['source', 'category'],
politicians: ['party', 'state', 'office_level'],
statements: ['status', 'tone', 'category', 'urgency', 'event_type'],
donations: ['source', 'donor_type', 'channel', 'recurring'],
email_campaigns: ['status', 'geo_state', 'ai_generated'],
x_posts: ['platform', 'status'],
library_items: ['category', 'source_type'],
discovered_apis: ['status', 'category', 'data_type', 'auth_required'],
newspaper_frontpages: ['country', 'region', 'source_type'],
nonprofit_statements: ['category', 'sentiment'],
foundations: ['type', 'state', 'accepts_applications', 'source'],
federal_grant_history: ['awarding_agency', 'source_system'],
grant_proposals: ['status', 'priority'],
moveon_petitions: ['status', 'category'],
trending_petitions: ['platform', 'category'],
partner_posts: ['platform', 'status'],
outreach_pipeline: ['status', 'channel'],
};
if (!allowedTables[table]) {
return NextResponse.json({ error: `Table "${table}" is not queryable` }, { status: 400 });
}
const conditions: string[] = [];
const values: (string | number | boolean)[] = [];
let paramIdx = 1;
for (const [key, val] of Object.entries(filters)) {
if (allowedTables[table].includes(key) && val !== undefined && val !== '') {
conditions.push(`${key} = $${paramIdx}`);
values.push(val);
paramIdx++;
}
}
const whereClause = conditions.length > 0 ? 'WHERE ' + conditions.join(' AND ') : '';
const safeLimit = Math.min(Math.max(1, limit), 500);
const safeOffset = Math.max(0, offset);
values.push(safeLimit);
values.push(safeOffset);
// Some tables use different timestamp columns for ordering
const orderColumnOverrides: Record<string, string> = {
federal_grant_history: 'fetched_at',
newspaper_frontpages: 'scraped_date',
};
const orderCol = orderColumnOverrides[table] || 'created_at';
const sql = `SELECT * FROM ${table} ${whereClause} ORDER BY ${orderCol} DESC NULLS LAST LIMIT $${paramIdx} OFFSET $${paramIdx + 1}`;
const countSql = `SELECT COUNT(*) as total FROM ${table} ${whereClause}`;
const [dataResult, countResult] = await Promise.all([
query(sql, values),
query(countSql, values.slice(0, -2)),
]);
return NextResponse.json({
table,
rows: dataResult.rows,
total: parseInt(countResult.rows[0]?.total ?? '0', 10),
limit: safeLimit,
offset: safeOffset,
});
} catch (err) {
console.error('[data-explorer] POST error:', (err as Error).message);
return NextResponse.json({ error: 'Query failed' }, { status: 500 });
}
}