`scripts/ingest-ca-ag-status.mjs`

74 lines
// Ingest CA Attorney General Registry of Charitable Trusts status lists into charitable_orgs.
// Sets ca_ag_status per source file, keyed by FEIN (normalized to strein XX-XXXXXXX so it
// joins ProPublica orgs). Reversible local load. MAX_ROWS bounds the proof; unset = full.
// Source: https://oag.ca.gov/charities/reports  (updated 1st & 3rd Wed monthly)
import { parse } from 'csv-parse';
import { q, startRun, upsertSource } from '../lib/db.js';
import { pool } from '../lib/db.js';

const FILES = {
  'may-operate':      'charities-may-operate.csv',
  'may-not-operate':  'charities-may-not-operate.csv',
  'undetermined':     'charities-undetermined-status.csv',
  'not-operating':    'charities-not-operating.csv',
};
const WHICH = process.env.CA_AG_LIST || 'may-operate';
const MAX_ROWS = process.env.MAX_ROWS ? parseInt(process.env.MAX_ROWS, 10) : Infinity;
const BASE = 'https://oag.ca.gov/sites/all/files/agweb/pdfs/charities/reports/';
const SLUG = 'ca_ag';

const clean = (s) => (s == null ? null : String(s).trim() || null);
const toStrein = (fein) => {
  const d = clean(fein)?.replace(/\D/g, '');
  return d && d.length === 9 ? `${d.slice(0, 2)}-${d.slice(2)}` : (clean(fein) || null);
};

async function main() {
  const file = FILES[WHICH];
  if (!file) throw new Error(`Unknown CA_AG_LIST '${WHICH}'`);
  await upsertSource({
    slug: SLUG, name: 'CA AG Registry of Charitable Trusts', url: 'https://oag.ca.gov/charities/reports',
    jurisdiction: 'state', family: 'charitable', granularity: 'org', access: 'csv',
    notes: 'Registration/status lists (may-operate/may-not/undetermined/not-operating). Monthly CSV.',
  });
  const run = await startRun(SLUG);
  let seen = 0, upserted = 0;
  try {
    const res = await fetch(BASE + file, { headers: { 'User-Agent': 'ca-donations/0.1 (public-records)' } });
    if (!res.ok) throw new Error(`CA AG HTTP ${res.status} for ${file}`);
    // This government CSV has stray, unbalanced double-quotes inside fields. It is not
    // genuinely quoted, so strip all " and parse as plain comma-delimited (quote off).
    const text = (await res.text()).replace(/"/g, '');
    const records = await new Promise((resolve, reject) => {
      parse(text, { columns: true, skip_empty_lines: true, relax_column_count: true, quote: false, trim: true },
        (err, out) => (err ? reject(err) : resolve(out)));
    });
    for (const rec of records) {
      if (seen >= MAX_ROWS) break;
      seen++;
      const ein = toStrein(rec.FEIN);
      const name = clean(rec.Name);
      if (!name) continue;
      // Prefer keying on EIN; fall back to a synthetic key when FEIN is blank.
      const r = await q(
        `INSERT INTO charitable_orgs (ein,name,city,state,ca_ag_status,source_slug)
         VALUES ($1,$2,$3,$4,$5,$6)
         ON CONFLICT (ein) DO UPDATE SET ca_ag_status=EXCLUDED.ca_ag_status,
           name=COALESCE(charitable_orgs.name, EXCLUDED.name)
         RETURNING id`,
        [ein, name, clean(rec.City), clean(rec.State), WHICH, SLUG],
      );
      if (r.length) upserted++;
    }
    await run.done(seen, upserted, 'ok', `list=${WHICH}`);
    console.log(`CA AG ${WHICH}: ${seen} rows read, ${upserted} orgs upserted. $0 (free CSV).`);
  } catch (e) {
    await run.done(seen, upserted, 'error', String(e.message));
    console.error('FAILED:', e.message);
    process.exitCode = 1;
  } finally {
    await pool.end();
  }
}
main();