← back to Norma
app/api/orgs/route.ts
426 lines
import { NextRequest, NextResponse } from 'next/server';
import { query } from '@/lib/db';
import { requireRole } from '@/lib/require-role';
import { auditLog } from '@/lib/audit';
import { scrapeAllOrganizations, type Organization } from '@/lib/org-scraper';
/**
* GET /api/orgs
* Search and list organizations with filtering, full-text search, pagination, and sorting.
*
* Query params:
* q - full-text search across name, description, mission
* city - filter by city
* state - filter by state
* metro_area - filter by metro area (DC, Chicago, LA, NYC)
* type - filter by org type (nonprofit, ngo, advocacy_org)
* category - filter by category
* political_leaning - filter by political leaning
* has_email - "true" to only return orgs with email
* has_phone - "true" to only return orgs with phone
* has_website - "true" to only return orgs with website
* limit - rows per page (default 50, max 200)
* offset - pagination offset (default 0)
* sort - column to sort by (name, revenue, city, updated_at; default name)
* sort_dir - asc or desc (default asc)
*/
export async function GET(request: NextRequest) {
const auth = requireRole(request, 'admin', 'staff');
if (auth instanceof NextResponse) return auth;
const { searchParams } = new URL(request.url);
const q = searchParams.get('q');
const city = searchParams.get('city');
const state = searchParams.get('state');
const metroArea = searchParams.get('metro_area');
const type = searchParams.get('type');
const category = searchParams.get('category');
const politicalLeaning = searchParams.get('political_leaning');
const hasEmail = searchParams.get('has_email');
const hasPhone = searchParams.get('has_phone');
const hasWebsite = searchParams.get('has_website');
let limit = parseInt(searchParams.get('limit') || '50', 10);
if (isNaN(limit) || limit < 1) limit = 50;
if (limit > 200) limit = 200;
let offset = parseInt(searchParams.get('offset') || '0', 10);
if (isNaN(offset) || offset < 0) offset = 0;
const sortRaw = searchParams.get('sort') || 'name';
const sortDirRaw = searchParams.get('sort_dir') || 'asc';
// Whitelist allowed sort columns to prevent SQL injection
const SORT_MAP: Record<string, string> = {
name: 'name',
revenue: 'annual_revenue',
annual_revenue: 'annual_revenue',
city: 'city',
state: 'state',
type: 'type',
category: 'category',
political_leaning: 'political_leaning',
created_at: 'created_at',
updated_at: 'updated_at',
};
const sortCol = SORT_MAP[sortRaw] || 'name';
const sortDir = sortDirRaw === 'desc' ? 'DESC' : 'ASC';
// Build dynamic WHERE
const conditions: string[] = [];
const params: unknown[] = [];
let idx = 1;
if (q) {
conditions.push(
`to_tsvector('english', coalesce(name,'') || ' ' || coalesce(description,'') || ' ' || coalesce(mission,'')) @@ plainto_tsquery('english', $${idx++})`,
);
params.push(q);
}
if (city) {
conditions.push(`LOWER(city) = LOWER($${idx++})`);
params.push(city);
}
// Multi-city filter (comma-separated)
const cities = searchParams.get('cities');
if (cities) {
const cityList = cities.split(',').map(c => c.trim()).filter(Boolean);
if (cityList.length > 0) {
const placeholders = cityList.map((_, i) => `LOWER($${idx + i})`).join(',');
conditions.push(`LOWER(city) IN (${placeholders})`);
params.push(...cityList);
idx += cityList.length;
}
}
if (state) {
conditions.push(`LOWER(state) = LOWER($${idx++})`);
params.push(state);
}
if (metroArea) {
conditions.push(`LOWER(metro_area) = LOWER($${idx++})`);
params.push(metroArea);
}
if (type) {
conditions.push(`type = $${idx++}`);
params.push(type);
}
if (category) {
conditions.push(`category = $${idx++}`);
params.push(category);
}
if (politicalLeaning) {
conditions.push(`political_leaning = $${idx++}`);
params.push(politicalLeaning);
}
if (hasEmail === 'true') {
conditions.push(`email IS NOT NULL AND email != ''`);
}
if (hasPhone === 'true') {
conditions.push(`phone IS NOT NULL AND phone != ''`);
}
if (hasWebsite === 'true') {
conditions.push(`website IS NOT NULL AND website != ''`);
}
const whereClause = conditions.length > 0 ? `WHERE ${conditions.join(' AND ')}` : '';
// Null handling for sort: push nulls to end
const nullsOrder = sortDir === 'ASC' ? 'NULLS LAST' : 'NULLS FIRST';
try {
// Get total count
const countResult = await query(
`SELECT COUNT(*) AS total FROM organizations ${whereClause}`,
params,
);
const total = parseInt(countResult.rows[0]?.total || '0', 10);
// Get rows
const dataResult = await query(
`SELECT * FROM organizations ${whereClause}
ORDER BY ${sortCol} ${sortDir} ${nullsOrder}
LIMIT $${idx++} OFFSET $${idx++}`,
[...params, limit, offset],
);
// Get filter options (unique values for dropdowns)
const [citiesResult, typesResult, categoriesResult] = await Promise.all([
query(`SELECT DISTINCT city FROM organizations WHERE city IS NOT NULL ORDER BY city`),
query(`SELECT DISTINCT type FROM organizations WHERE type IS NOT NULL ORDER BY type`),
query(`SELECT DISTINCT category FROM organizations WHERE category IS NOT NULL ORDER BY category`),
]);
return NextResponse.json({
rows: dataResult.rows,
total,
filters: {
cities: citiesResult.rows.map((r) => r.city),
types: typesResult.rows.map((r) => r.type),
categories: categoriesResult.rows.map((r) => r.category),
},
});
} catch (err) {
console.error('[api/orgs] GET error:', (err as Error).message);
return NextResponse.json({ error: 'Failed to fetch organizations' }, { status: 500 });
}
}
/**
* POST /api/orgs
* Trigger a scraper run that fetches orgs from ProPublica + seed data
* and upserts them into the organizations table.
* Requires auth.
*/
export async function POST(request: NextRequest) {
const auth = requireRole(request, 'admin', 'staff');
if (auth instanceof NextResponse) return auth;
try {
const { orgs, stats } = await scrapeAllOrganizations();
let inserted = 0;
let updated = 0;
let errored = 0;
const byCityCount: Record<string, number> = {};
const byTypeCount: Record<string, number> = {};
for (const org of orgs) {
try {
const metro = org.metro_area || 'unknown';
byCityCount[metro] = (byCityCount[metro] || 0) + 1;
byTypeCount[org.type] = (byTypeCount[org.type] || 0) + 1;
if (org.ein && !org.ein.startsWith('SEED-')) {
// Org has a real EIN — use ON CONFLICT for upsert
const result = await query(
`INSERT INTO organizations (
name, ein, type, category, subcategory, ntee_code,
city, state, zip, address, metro_area,
email, phone, website, description, mission,
founded_year, annual_revenue, total_assets, tax_status,
political_leaning, key_issues, executive_director,
source, source_id, source_url,
scraped_at, updated_at
) VALUES (
$1, $2, $3, $4, $5, $6,
$7, $8, $9, $10, $11,
$12, $13, $14, $15, $16,
$17, $18, $19, $20,
$21, $22, $23,
$24, $25, $26,
NOW(), NOW()
)
ON CONFLICT (ein) DO UPDATE SET
name = COALESCE(EXCLUDED.name, organizations.name),
type = COALESCE(EXCLUDED.type, organizations.type),
category = COALESCE(EXCLUDED.category, organizations.category),
subcategory = COALESCE(EXCLUDED.subcategory, organizations.subcategory),
ntee_code = COALESCE(EXCLUDED.ntee_code, organizations.ntee_code),
city = COALESCE(EXCLUDED.city, organizations.city),
state = COALESCE(EXCLUDED.state, organizations.state),
zip = COALESCE(EXCLUDED.zip, organizations.zip),
address = COALESCE(EXCLUDED.address, organizations.address),
metro_area = COALESCE(EXCLUDED.metro_area, organizations.metro_area),
email = COALESCE(EXCLUDED.email, organizations.email),
phone = COALESCE(EXCLUDED.phone, organizations.phone),
website = COALESCE(EXCLUDED.website, organizations.website),
description = COALESCE(EXCLUDED.description, organizations.description),
mission = COALESCE(EXCLUDED.mission, organizations.mission),
founded_year = COALESCE(EXCLUDED.founded_year, organizations.founded_year),
annual_revenue = COALESCE(EXCLUDED.annual_revenue, organizations.annual_revenue),
total_assets = COALESCE(EXCLUDED.total_assets, organizations.total_assets),
tax_status = COALESCE(EXCLUDED.tax_status, organizations.tax_status),
political_leaning = COALESCE(EXCLUDED.political_leaning, organizations.political_leaning),
key_issues = CASE WHEN array_length(EXCLUDED.key_issues, 1) > array_length(organizations.key_issues, 1) THEN EXCLUDED.key_issues ELSE organizations.key_issues END,
executive_director = COALESCE(EXCLUDED.executive_director, organizations.executive_director),
source = EXCLUDED.source,
source_id = COALESCE(EXCLUDED.source_id, organizations.source_id),
source_url = COALESCE(EXCLUDED.source_url, organizations.source_url),
scraped_at = NOW(),
updated_at = NOW()
RETURNING (xmax = 0) AS is_insert`,
[
org.name,
org.ein,
org.type,
org.category || null,
org.subcategory || null,
org.ntee_code || null,
org.city || null,
org.state || null,
org.zip || null,
org.address || null,
org.metro_area || null,
org.email || null,
org.phone || null,
org.website || null,
org.description || null,
org.mission || null,
org.founded_year || null,
org.annual_revenue || null,
org.total_assets || null,
org.tax_status || null,
org.political_leaning || null,
org.key_issues || [],
org.executive_director || null,
org.source,
org.source_id || null,
org.source_url || null,
],
);
if (result.rows[0]?.is_insert) {
inserted++;
} else {
updated++;
}
} else {
// Org has no real EIN (SEED- prefix or null) — check by name+state first
const existing = await query(
`SELECT id FROM organizations WHERE LOWER(name) = LOWER($1) AND LOWER(COALESCE(state,'')) = LOWER(COALESCE($2,''))`,
[org.name, org.state || ''],
);
if (existing.rowCount && existing.rowCount > 0) {
// Update existing record
await query(
`UPDATE organizations SET
type = COALESCE($2, type),
category = COALESCE($3, category),
subcategory = COALESCE($4, subcategory),
ntee_code = COALESCE($5, ntee_code),
city = COALESCE($6, city),
zip = COALESCE($7, zip),
address = COALESCE($8, address),
metro_area = COALESCE($9, metro_area),
email = COALESCE($10, email),
phone = COALESCE($11, phone),
website = COALESCE($12, website),
description = COALESCE($13, description),
mission = COALESCE($14, mission),
founded_year = COALESCE($15, founded_year),
annual_revenue = COALESCE($16, annual_revenue),
total_assets = COALESCE($17, total_assets),
tax_status = COALESCE($18, tax_status),
political_leaning = COALESCE($19, political_leaning),
key_issues = COALESCE($20, key_issues),
executive_director = COALESCE($21, executive_director),
source = $22,
source_id = COALESCE($23, source_id),
source_url = COALESCE($24, source_url),
scraped_at = NOW(),
updated_at = NOW()
WHERE id = $1`,
[
existing.rows[0].id,
org.type,
org.category || null,
org.subcategory || null,
org.ntee_code || null,
org.city || null,
org.zip || null,
org.address || null,
org.metro_area || null,
org.email || null,
org.phone || null,
org.website || null,
org.description || null,
org.mission || null,
org.founded_year || null,
org.annual_revenue || null,
org.total_assets || null,
org.tax_status || null,
org.political_leaning || null,
org.key_issues || [],
org.executive_director || null,
org.source,
org.source_id || null,
org.source_url || null,
],
);
updated++;
} else {
// Insert new — with NULL ein to avoid unique constraint issues
await query(
`INSERT INTO organizations (
name, ein, type, category, subcategory, ntee_code,
city, state, zip, address, metro_area,
email, phone, website, description, mission,
founded_year, annual_revenue, total_assets, tax_status,
political_leaning, key_issues, executive_director,
source, source_id, source_url,
scraped_at, updated_at
) VALUES (
$1, NULL, $2, $3, $4, $5,
$6, $7, $8, $9, $10,
$11, $12, $13, $14, $15,
$16, $17, $18, $19,
$20, $21, $22,
$23, $24, $25,
NOW(), NOW()
)`,
[
org.name,
org.type,
org.category || null,
org.subcategory || null,
org.ntee_code || null,
org.city || null,
org.state || null,
org.zip || null,
org.address || null,
org.metro_area || null,
org.email || null,
org.phone || null,
org.website || null,
org.description || null,
org.mission || null,
org.founded_year || null,
org.annual_revenue || null,
org.total_assets || null,
org.tax_status || null,
org.political_leaning || null,
org.key_issues || [],
org.executive_director || null,
org.source,
org.source_id || null,
org.source_url || null,
],
);
inserted++;
}
}
} catch (err) {
errored++;
console.error(`[api/orgs] Failed to upsert org "${org.name}":`, (err as Error).message);
}
}
await auditLog('orgs.scrape_run', 'organization', null, {
inserted,
updated,
errored,
total: orgs.length,
stats,
});
return NextResponse.json({
inserted,
updated,
errored,
total: orgs.length,
by_city: byCityCount,
by_type: byTypeCount,
scraper_stats: stats,
});
} catch (err) {
console.error('[api/orgs] POST error:', (err as Error).message);
return NextResponse.json(
{ error: `Scraper run failed: ${(err as Error).message}` },
{ status: 500 },
);
}
}