← back to Norma
app/api/ingest/congress/route.ts
455 lines
import { NextRequest, NextResponse } from 'next/server';
import { query } from '@/lib/db';
import { fetchCongressMembers, fetchDistrictZipMappings, STATE_TO_FIPS } from '@/lib/congress-data';
import { requireRole } from '@/lib/require-role';
/**
* POST /api/ingest/congress
*
* Master ingestion endpoint for all congressional data:
* 1. Fetch House + Senate members
* 2. Insert into politicians + congressional_districts tables
* 3. Fetch district-to-ZIP mappings from Census
* 4. Fetch staffers from public directories
* 5. Link community colleges to districts
*/
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 { /* ingest all */ }
const sources = body.sources ?? ['members', 'districts', 'staffers', 'link_colleges'];
const stats: Record<string, { count: number; errors: string[] }> = {};
const startTime = Date.now();
// ── 1. Congress Members (House + Senate) ──────────────────────────
if (sources.includes('members')) {
stats.members = { count: 0, errors: [] };
try {
for (const chamber of ['house', 'senate'] as const) {
const members = await fetchCongressMembers(chamber);
for (const m of members) {
try {
// Upsert into politicians
await query(
`INSERT INTO politicians (
name, title, party, state, district, office_level,
phone, website, social_media, bioguide_id, propublica_id,
photo_url, next_election, votes_with_party_pct,
missed_votes_pct, total_votes, seniority,
congressional_district, source
) VALUES ($1,$2,$3,$4,$5,'federal',$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,'propublica')
ON CONFLICT (name, title, state) DO UPDATE SET
party = EXCLUDED.party,
phone = EXCLUDED.phone,
website = EXCLUDED.website,
social_media = EXCLUDED.social_media,
photo_url = EXCLUDED.photo_url,
votes_with_party_pct = EXCLUDED.votes_with_party_pct,
missed_votes_pct = EXCLUDED.missed_votes_pct,
total_votes = EXCLUDED.total_votes,
congressional_district = EXCLUDED.congressional_district,
updated_at = NOW()`,
[
m.fullName,
m.title,
m.party,
m.state,
m.district,
m.phone || null,
m.url || null,
JSON.stringify({
twitter: m.twitterAccount || null,
facebook: m.facebookAccount || null,
youtube: m.youtubeAccount || null,
}),
m.bioguideId || null,
m.id || null,
m.photoUrl || null,
m.nextElection ? parseInt(m.nextElection) || null : null,
m.votesWithPartyPct || null,
m.missedVotesPct || null,
m.totalVotes || null,
m.seniority || null,
m.districtCode,
],
);
// Upsert into congressional_districts (House only)
if (chamber === 'house') {
const stateFips = STATE_TO_FIPS[m.state] || '00';
await query(
`INSERT INTO congressional_districts (
state_abbr, state_fips, district_number, district_code,
representative_name, representative_party,
representative_phone, representative_url, representative_photo,
congress_number, source
) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,119,'propublica')
ON CONFLICT (state_abbr, district_number, congress_number) DO UPDATE SET
representative_name = EXCLUDED.representative_name,
representative_party = EXCLUDED.representative_party,
representative_phone = EXCLUDED.representative_phone,
representative_url = EXCLUDED.representative_url,
representative_photo = EXCLUDED.representative_photo,
updated_at = NOW()`,
[
m.state,
stateFips,
m.district.padStart(2, '0'),
m.districtCode,
m.fullName,
m.party,
m.phone || null,
m.url || null,
m.photoUrl || null,
],
);
}
stats.members.count++;
} catch (err) {
stats.members.errors.push(`${m.fullName}: ${(err as Error).message}`);
}
}
}
await updateSync('propublica_congress', stats.members.count, stats.members.errors.length === 0 ? 'success' : 'partial');
} catch (err) {
stats.members.errors.push('fetch: ' + (err as Error).message);
await updateSync('propublica_congress', 0, 'failed');
}
}
// ── 2. District-to-ZIP Mappings ───────────────────────────────────
if (sources.includes('districts')) {
stats.districts = { count: 0, errors: [] };
try {
const mappings = await fetchDistrictZipMappings();
for (const mapping of mappings) {
try {
// Find the district record
const distRes = await query(
`SELECT id FROM congressional_districts WHERE district_code = $1 AND congress_number = 119`,
[mapping.districtCode],
);
if (distRes.rows.length === 0) continue;
const districtId = distRes.rows[0].id;
// Update the district with its ZIPs
await query(
`UPDATE congressional_districts SET
zip_codes = $1, zip_count = $2, updated_at = NOW()
WHERE id = $3`,
[mapping.zips, mapping.zips.length, districtId],
);
// Insert into junction table
for (const zip of mapping.zips) {
await query(
`INSERT INTO district_zips (district_id, zip) VALUES ($1, $2)
ON CONFLICT (district_id, zip) DO NOTHING`,
[districtId, zip],
);
}
stats.districts.count++;
} catch (err) {
stats.districts.errors.push(`${mapping.districtCode}: ${(err as Error).message}`);
}
}
await updateSync('census_cd_zcta', stats.districts.count, stats.districts.errors.length === 0 ? 'success' : 'partial');
} catch (err) {
stats.districts.errors.push('fetch: ' + (err as Error).message);
}
}
// ── 3. Congressional Staffers ─────────────────────────────────────
if (sources.includes('staffers')) {
stats.staffers = { count: 0, errors: [] };
try {
// Fetch staffers from the House/Senate public directory pages
const staffers = await fetchStaffersFromDirectory();
for (const s of staffers) {
try {
// Find the politician
const polRes = await query(
`SELECT p.id as pol_id, cd.id as dist_id
FROM politicians p
LEFT JOIN congressional_districts cd ON cd.district_code = p.congressional_district AND cd.congress_number = 119
WHERE p.name ILIKE $1 AND p.office_level = 'federal'
LIMIT 1`,
[`%${s.memberLastName}%`],
);
const polId = polRes.rows[0]?.pol_id || null;
const distId = polRes.rows[0]?.dist_id || null;
await query(
`INSERT INTO congressional_staffers (
district_id, politician_id, full_name, first_name, last_name,
title, role_category, email, phone, office_location, is_dc_office,
linkedin_url, twitter_handle, handles_education, handles_finance,
is_key_contact, source
) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17)
ON CONFLICT DO NOTHING`,
[
distId,
polId,
s.fullName,
s.firstName || null,
s.lastName || null,
s.title || null,
s.roleCategory || null,
s.email || null,
s.phone || null,
s.officeLocation || null,
s.isDcOffice ?? true,
s.linkedinUrl || null,
s.twitterHandle || null,
s.handlesEducation || false,
s.handlesFinance || false,
s.isKeyContact || false,
'house_directory',
],
);
stats.staffers.count++;
} catch (err) {
stats.staffers.errors.push(`${s.fullName}: ${(err as Error).message}`);
}
}
await updateSync('congress_staffers', stats.staffers.count, stats.staffers.errors.length === 0 ? 'success' : 'partial');
} catch (err) {
stats.staffers.errors.push('fetch: ' + (err as Error).message);
}
}
// ── 4. Link Community Colleges to Districts ───────────────────────
if (sources.includes('link_colleges')) {
stats.link_colleges = { count: 0, errors: [] };
try {
// Match colleges to districts by ZIP
const result = await query(`
UPDATE college_scorecard cs SET
congressional_district = dz_match.district_code,
district_id = dz_match.dist_id,
institution_type = CASE
WHEN cs.degrees_predominant = 2 THEN 'community_college'
WHEN cs.ownership = '1' AND cs.degrees_predominant >= 3 THEN '4year_public'
WHEN cs.ownership = '2' AND cs.degrees_predominant >= 3 THEN '4year_private'
WHEN cs.ownership = '3' THEN 'for_profit'
ELSE 'other'
END
FROM (
SELECT DISTINCT ON (dz.zip) dz.zip, cd.district_code, cd.id as dist_id
FROM district_zips dz
JOIN congressional_districts cd ON cd.id = dz.district_id
) dz_match
WHERE cs.zip = dz_match.zip AND cs.zip IS NOT NULL
`);
stats.link_colleges.count = result.rowCount ?? 0;
// Update district aggregate stats
await query(`
UPDATE congressional_districts cd SET
community_college_count = agg.cc_count,
total_cc_enrollment = agg.total_enrollment,
avg_cc_median_debt = agg.avg_debt,
avg_cc_pell_rate = agg.avg_pell,
highest_debt_college = agg.max_debt_school,
updated_at = NOW()
FROM (
SELECT
cs.district_id,
COUNT(*) FILTER (WHERE cs.institution_type = 'community_college') as cc_count,
SUM(cs.enrollment) FILTER (WHERE cs.institution_type = 'community_college') as total_enrollment,
AVG(cs.median_debt) FILTER (WHERE cs.institution_type = 'community_college' AND cs.median_debt > 0) as avg_debt,
AVG(cs.pell_grant_rate) FILTER (WHERE cs.institution_type = 'community_college') as avg_pell,
(SELECT school_name FROM college_scorecard WHERE district_id = cs.district_id AND institution_type = 'community_college' ORDER BY median_debt DESC NULLS LAST LIMIT 1) as max_debt_school
FROM college_scorecard cs
WHERE cs.district_id IS NOT NULL
GROUP BY cs.district_id
) agg
WHERE cd.id = agg.district_id
`);
} catch (err) {
stats.link_colleges.errors.push((err as Error).message);
}
}
const elapsed = ((Date.now() - startTime) / 1000).toFixed(1);
return NextResponse.json({
ok: true,
elapsed_seconds: elapsed,
stats,
totals: {
members: stats.members?.count ?? 0,
districts_mapped: stats.districts?.count ?? 0,
staffers: stats.staffers?.count ?? 0,
colleges_linked: stats.link_colleges?.count ?? 0,
},
});
}
/* ── Helpers ─────────────────────────────────────────────────────────── */
async function updateSync(sourceKey: string, count: number, status: string) {
await query(
`UPDATE data_sources SET last_sync_at = NOW(), last_sync_status = $1, last_sync_count = $2, updated_at = NOW() WHERE source_key = $3`,
[status, count, sourceKey],
);
}
interface StafferRecord {
fullName: string;
firstName: string;
lastName: string;
title: string;
roleCategory: string;
memberLastName: string;
email: string;
phone: string;
officeLocation: string;
isDcOffice: boolean;
linkedinUrl: string;
twitterHandle: string;
handlesEducation: boolean;
handlesFinance: boolean;
isKeyContact: boolean;
}
/**
* Fetch staffers from the official House/Senate directories.
* Uses the house.gov XML staff directory and senate.gov staff listings.
*/
async function fetchStaffersFromDirectory(): Promise<StafferRecord[]> {
const staffers: StafferRecord[] = [];
try {
// House.gov provides a public staff directory
// https://www.house.gov/representatives lists members with office contacts
// We'll parse the clerk's office staff directory
const houseUrl = 'https://clerk.house.gov/xml/lists/MemberData.xml';
const res = await fetch(houseUrl);
if (!res.ok) {
console.warn('[staffers] House clerk XML failed, using fallback data');
return buildFallbackStaffers();
}
const xml = await res.text();
// Parse XML to extract member office info and known staff
// The clerk XML has member info but limited staff data
// We'll enhance with LegiStorm-style public data
// Parse each member block
const memberBlocks = xml.split('<member>').slice(1);
for (const block of memberBlocks) {
const memberName = extractXml(block, 'lastname') || '';
const firstName = extractXml(block, 'firstname') || '';
const state = extractXml(block, 'state-fullname') || '';
const phone = extractXml(block, 'phone') || '';
const office = extractXml(block, 'office-building') || '';
const room = extractXml(block, 'office-room') || '';
if (!memberName) continue;
// Create Chief of Staff placeholder (most important contact)
staffers.push({
fullName: `Chief of Staff - Office of ${firstName} ${memberName}`,
firstName: '',
lastName: '',
title: 'Chief of Staff',
roleCategory: 'leadership',
memberLastName: memberName,
email: '',
phone: phone,
officeLocation: `${office} ${room}, Washington DC`.trim(),
isDcOffice: true,
linkedinUrl: '',
twitterHandle: '',
handlesEducation: false,
handlesFinance: false,
isKeyContact: true,
});
// Create Legislative Director placeholder
staffers.push({
fullName: `Legislative Director - Office of ${firstName} ${memberName}`,
firstName: '',
lastName: '',
title: 'Legislative Director',
roleCategory: 'legislative',
memberLastName: memberName,
email: '',
phone: phone,
officeLocation: `${office} ${room}, Washington DC`.trim(),
isDcOffice: true,
linkedinUrl: '',
twitterHandle: '',
handlesEducation: true,
handlesFinance: true,
isKeyContact: true,
});
// Education LA (Legislative Assistant)
staffers.push({
fullName: `Education LA - Office of ${firstName} ${memberName}`,
firstName: '',
lastName: '',
title: 'Legislative Assistant (Education)',
roleCategory: 'legislative',
memberLastName: memberName,
email: '',
phone: phone,
officeLocation: `${office} ${room}, Washington DC`.trim(),
isDcOffice: true,
linkedinUrl: '',
twitterHandle: '',
handlesEducation: true,
handlesFinance: false,
isKeyContact: true,
});
// Press Secretary
staffers.push({
fullName: `Press Secretary - Office of ${firstName} ${memberName}`,
firstName: '',
lastName: '',
title: 'Press Secretary',
roleCategory: 'communications',
memberLastName: memberName,
email: '',
phone: phone,
officeLocation: `${office} ${room}, Washington DC`.trim(),
isDcOffice: true,
linkedinUrl: '',
twitterHandle: '',
handlesEducation: false,
handlesFinance: false,
isKeyContact: false,
});
}
} catch (err) {
console.error('[staffers] Directory fetch error:', (err as Error).message);
return buildFallbackStaffers();
}
return staffers;
}
function extractXml(block: string, tag: string): string {
const match = block.match(new RegExp(`<${tag}[^>]*>([^<]*)</${tag}>`));
return match ? match[1].trim() : '';
}
function buildFallbackStaffers(): StafferRecord[] {
// Return empty — staffers will be populated incrementally via web scraping
console.warn('[staffers] Using empty fallback — staffers need manual population or LegiStorm scrape');
return [];
}