`server.js`

212 lines
// ca-donations — Basic-Auth searchable data product over CA donation public records.
// Two families: charitable (orgs + grants) and political (donor-level contributions).
import express from 'express';
import path from 'node:path';
import { q } from './lib/db.js';

const app = express();
const PORT = process.env.PORT || 9926;
const USER = process.env.BASIC_AUTH_USER || 'admin';
const PASS = process.env.BASIC_AUTH_PASS || 'DW2024!';

// Public tier is OFF by default — when unset/'0' the server is byte-for-byte the
// original fail-closed Basic-Auth app. The public surface is charitable-only:
// ALL political endpoints (raw /api/political AND aggregate /api/political/agg)
// require Basic-Auth in every mode. The aggregate is fast (reads political_agg)
// but authenticated — not public.
const PUBLIC_TIER = process.env.PUBLIC_TIER === '1';

// STRICT POSITIVE ALLOWLIST. There is deliberately NO "anything not under /api/"
// fallback: the gate opens a path only when it EXACTLY matches a known-public API
// route, or is a genuine safe static asset resolving inside public/. Anything that
// falls through returns false and stays Basic-Auth gated — so a future file like
// public/political-export.json can never be silently served to the public.

// Exact-match public charitable/org API endpoints. NO political endpoint is on
// this list — raw /api/political AND aggregate /api/political/agg both require
// Basic-Auth in all modes. Org detail is the one prefix (/api/org/:ein).
const PUBLIC_API_EXACT = new Set(['/api/stats', '/api/orgs', '/api/grants']);
const publicApiPath = (p) =>
  PUBLIC_API_EXACT.has(p) ||
  p.startsWith('/api/org/');

// Genuine static assets only: no /api prefix, a known-safe extension, and the
// resolved path must stay inside public/ (blocks traversal / encoded escapes).
const STATIC_EXT = new Set(['.html', '.css', '.js', '.mjs', '.txt', '.ico', '.svg',
  '.png', '.jpg', '.jpeg', '.gif', '.webp', '.woff', '.woff2', '.map']);
const PUBLIC_ROOT = path.resolve('public');
const isSafeStaticPath = (p) => {
  if (p.startsWith('/api')) return false;
  let rel;
  try { rel = p === '/' ? 'index.html' : decodeURIComponent(p).replace(/^\/+/, ''); }
  catch { return false; }
  if (!STATIC_EXT.has(path.extname(rel).toLowerCase())) return false;
  const resolved = path.resolve(PUBLIC_ROOT, rel);
  return resolved === PUBLIC_ROOT || resolved.startsWith(PUBLIC_ROOT + path.sep);
};

const isPublicPath = (p) => {
  if (p === '/healthz') return true;
  if (!PUBLIC_TIER) return false;
  if (publicApiPath(p)) return true;
  if (isSafeStaticPath(p)) return true;
  return false;
};

// --- Basic Auth gate (401 = healthy). Open paths per the tier allowlist. ---
app.use((req, res, next) => {
  if (isPublicPath(req.path)) return next();
  const h = req.headers.authorization || '';
  const [, b64] = h.split(' ');
  if (b64) {
    const [u, p] = Buffer.from(b64, 'base64').toString().split(':');
    if (u === USER && p === PASS) return next();
  }
  res.set('WWW-Authenticate', 'Basic realm="ca-donations"');
  return res.status(401).send('Auth required');
});

// Dependency-free in-memory rate limiter on /api/* — caps bulk scraping of donor rows.
const RL_WINDOW = 60_000, RL_MAX = 120, rl = new Map();
app.use('/api', (req, res, next) => {
  const ip = req.ip || req.socket.remoteAddress || 'unknown';
  const now = Date.now();
  const rec = rl.get(ip) || { n: 0, reset: now + RL_WINDOW };
  if (now > rec.reset) { rec.n = 0; rec.reset = now + RL_WINDOW; }
  rec.n++; rl.set(ip, rec);
  if (rl.size > 5000) for (const [k, v] of rl) if (now > v.reset) rl.delete(k); // GC
  if (rec.n > RL_MAX) { res.set('Retry-After', '60'); return res.status(429).json({ error: 'rate limited' }); }
  next();
});

const like = (s) => `%${String(s).trim()}%`;
// Baseline data-quality guard — never serve raw-feed artifacts as fact about named people.
const POL_CLEAN = `donor_name IS NOT NULL AND donor_name <> '' AND (contribution_date IS NULL OR contribution_date <= CURRENT_DATE)`;
const GRANT_CLEAN = `grantee_name IS NOT NULL AND grantee_name !~* 'see (schedule|attached|statement)|eligible patients|various'`;
// Whitelisted ORDER BY per endpoint — the UI's sort dropdown must actually sort.
const ORDER = {
  orgs:      { name: 'name ASC', status: 'ca_ag_status ASC, name ASC', ntee: 'ntee_code ASC NULLS LAST, name ASC' },
  grants:    { amount: 'amount DESC NULLS LAST', year: 'tax_year DESC NULLS LAST, amount DESC NULLS LAST', grantor: 'grantor_name ASC' },
  political: { date: 'contribution_date DESC NULLS LAST', amount: 'amount DESC NULLS LAST', donor: 'donor_name ASC', recipient: 'recipient_name ASC' },
};
const orderBy = (which, sort, fallbackKey) => ORDER[which][sort] || ORDER[which][fallbackKey];

app.get('/healthz', (_req, res) => res.json({ ok: true, service: 'ca-donations' }));

// Counts per family — the honest "what's loaded" signal for the UI.
app.get('/api/stats', async (_req, res) => {
  try {
    const [orgs] = await q('SELECT count(*)::int n FROM charitable_orgs');
    const [grants] = await q('SELECT count(*)::int n FROM charitable_grants');
    const [pol] = await q('SELECT count(*)::int n FROM political_contributions');
    const runs = await q(
      `SELECT source_slug, status, rows_upsert, finished_at FROM ingest_runs
       ORDER BY id DESC LIMIT 10`);
    res.json({ charitable_orgs: orgs.n, charitable_grants: grants.n, political_contributions: pol.n, runs });
  } catch (e) { res.status(500).json({ error: e.message }); }
});

// Charitable orgs search — drillable: each org links to /api/org/:ein.
app.get('/api/orgs', async (req, res) => {
  try {
    const { q: term = '', status = '', ntee = '', sort = 'name', limit = 100 } = req.query;
    const where = [], params = [];
    if (term)   { params.push(like(term));   where.push(`name ILIKE $${params.length}`); }
    if (status) { params.push(status);        where.push(`ca_ag_status = $${params.length}`); }
    if (ntee)   { params.push(like(ntee));    where.push(`ntee_code ILIKE $${params.length}`); }
    params.push(Math.min(+limit || 100, 500));
    const rows = await q(
      `SELECT ein,name,city,state,ntee_code,subsection,ca_ag_status
       FROM charitable_orgs ${where.length ? 'WHERE ' + where.join(' AND ') : ''}
       ORDER BY ${orderBy('orgs', sort, 'name')} LIMIT $${params.length}`, params);
    res.json({ rows });
  } catch (e) { res.status(500).json({ error: e.message }); }
});

// Org detail + its grants (grantor and grantee sides).
app.get('/api/org/:ein', async (req, res) => {
  try {
    res.set('X-Robots-Tag', 'noindex');
    const [org] = await q(
      `SELECT ein,name,city,state,ntee_code,subsection,ca_ag_status
       FROM charitable_orgs WHERE ein=$1`, [req.params.ein]);
    const grantsOut = await q(
      `SELECT grantor_ein,grantor_name,grantee_name,grantee_city,amount,tax_year,grant_type
       FROM charitable_grants WHERE grantor_ein=$1
       ORDER BY tax_year DESC, amount DESC LIMIT 200`, [req.params.ein]);
    res.json({ org: org || null, grants_out: grantsOut });
  } catch (e) { res.status(500).json({ error: e.message }); }
});

// Charitable grants search (donor->recipient records from 990-PF/Sched I/F).
app.get('/api/grants', async (req, res) => {
  try {
    const { grantor = '', grantee = '', year = '', sort = 'amount', limit = 100 } = req.query;
    const where = [GRANT_CLEAN], params = [];
    if (grantor) { params.push(like(grantor)); where.push(`grantor_name ILIKE $${params.length}`); }
    if (grantee) { params.push(like(grantee)); where.push(`grantee_name ILIKE $${params.length}`); }
    if (year)    { params.push(+year);          where.push(`tax_year = $${params.length}`); }
    params.push(Math.min(+limit || 100, 500));
    const rows = await q(
      `SELECT grantor_ein,grantor_name,grantee_name,grantee_city,amount,tax_year,grant_type
       FROM charitable_grants ${where.length ? 'WHERE ' + where.join(' AND ') : ''}
       ORDER BY ${orderBy('grants', sort, 'amount')} LIMIT $${params.length}`, params);
    res.json({ rows });
  } catch (e) { res.status(500).json({ error: e.message }); }
});

// Political donor-level contributions (above CA $100 itemization threshold).
app.get('/api/political', async (req, res) => {
  try {
    const { donor = '', recipient = '', jurisdiction = '', employer = '', sort = 'date', limit = 100 } = req.query;
    const where = [POL_CLEAN], params = [];
    if (donor)        { params.push(like(donor));        where.push(`donor_name ILIKE $${params.length}`); }
    if (recipient)    { params.push(like(recipient));    where.push(`recipient_name ILIKE $${params.length}`); }
    if (employer)     { params.push(like(employer));     where.push(`donor_employer ILIKE $${params.length}`); }
    if (jurisdiction) { params.push(jurisdiction);       where.push(`jurisdiction = $${params.length}`); }
    params.push(Math.min(+limit || 100, 500));
    const rows = await q(
      `SELECT donor_name,donor_employer,donor_city,amount,contribution_date,recipient_name,office,jurisdiction,form
       FROM political_contributions ${where.length ? 'WHERE ' + where.join(' AND ') : ''}
       ORDER BY ${orderBy('political', sort, 'date')} LIMIT $${params.length}`, params);
    res.json({ rows });
  } catch (e) { res.status(500).json({ error: e.message }); }
});

// Aggregate-only political rollups — NO per-donor rows ever leave here. Served
// from the materialized political_agg table (indexed, sub-second), which the
// ingest bakes with a HAVING count(distinct donor_name) >= 5 k-anon floor so a
// group can never single out fewer than 5 people. AUTHENTICATED, not public:
// this endpoint is NOT in the allowlist above, so it requires Basic-Auth in every
// mode exactly like raw /api/political — the public surface is charitable-only.
const AGG_DIMENSIONS = {
  recipient:    1,
  employer:     1,
  city:         1,
  jurisdiction: 1,
};
const AGG_SORT = { amount: 'total_amount DESC NULLS LAST', count: 'contribution_count DESC NULLS LAST' };
app.get('/api/political/agg', async (req, res) => {
  try {
    const by = req.query.by;
    if (typeof by !== 'string' || !Object.hasOwn(AGG_DIMENSIONS, by))
      return res.status(400).json({ error: 'by must be one of: ' + Object.keys(AGG_DIMENSIONS).join('|') });
    const sort = typeof req.query.sort === 'string' && Object.hasOwn(AGG_SORT, req.query.sort)
      ? req.query.sort : 'amount';
    const limit = Math.min(+req.query.limit || 100, 500);
    const rows = await q(
      `SELECT group_key, total_amount, contribution_count, distinct_donors
       FROM political_agg
       WHERE dimension = $1
       ORDER BY ${AGG_SORT[sort]}
       LIMIT $2`, [by, limit]);
    res.json({ by, sort, k_anonymity_floor: 5, rows });
  } catch (e) {
    res.status(500).json({ error: e.message });
  }
});

app.use(express.static('public'));

app.listen(PORT, () => console.log(`ca-donations on :${PORT}`));