`scripts/freshness-canary.mjs`

48 lines
// ca-donations freshness + completeness canary. READ-ONLY.
// Catches two silent-failure classes: (1) a source's ingest stopped running (stale),
// and (2) an ingest reported ok but 0 rows actually landed (the FEC-missing bug found
// in the cycle-2 audit — a run said ok/5000 yet political_contributions had 0 federal).
// Emits PASS/WARN/FAIL to data/latest.json in fleet-health-rollup vocabulary.
import { q, pool } from '../lib/db.js';
import { writeFileSync, mkdirSync } from 'fs';
import { fileURLToPath } from 'url';
import { dirname, join } from 'path';

const ROOT = join(dirname(fileURLToPath(import.meta.url)), '..');
// source -> { table, source_col, expect_min (rows we expect if the ingest truly worked), max_age_days }
const SOURCES = {
  calaccess:   { table: 'political_contributions', expect_min: 1_000_000, max_age_days: 3 },
  fec_bulk:    { table: 'political_contributions', expect_min: 1,         max_age_days: 400 }, // per-cycle bulk
  irs_990:     { table: 'charitable_grants',        expect_min: 1_000,    max_age_days: 40  },
  ca_ag:       { table: 'charitable_orgs',          expect_min: 50_000,   max_age_days: 40  },
  propublica:  { table: 'charitable_orgs',          expect_min: 100,      max_age_days: 40  },
};

async function main() {
  const now = Date.now();
  const findings = [];
  for (const [slug, cfg] of Object.entries(SOURCES)) {
    const [run] = await q(
      `SELECT max(finished_at) AS last_ok FROM ingest_runs WHERE source_slug=$1 AND status='ok'`, [slug]);
    const [rows] = await q(
      `SELECT count(*)::int n FROM ${cfg.table} WHERE source_slug=$1`, [slug]);
    const ageDays = run.last_ok ? (now - new Date(run.last_ok).getTime()) / 86_400_000 : null;
    let verdict = 'PASS', why = '';
    if (ageDays === null)                 { verdict = 'FAIL'; why = 'no successful ingest run ever'; }
    else if (rows.n === 0)                { verdict = 'FAIL'; why = `ran ok but 0 rows in ${cfg.table} (silent-empty)`; }
    else if (rows.n < cfg.expect_min)     { verdict = 'WARN'; why = `only ${rows.n} rows (< expected ${cfg.expect_min})`; }
    else if (ageDays > cfg.max_age_days)  { verdict = 'WARN'; why = `stale: last ok ${ageDays.toFixed(1)}d ago (> ${cfg.max_age_days}d)`; }
    findings.push({ source: slug, table: cfg.table, rows: rows.n, last_ok: run.last_ok, age_days: ageDays && +ageDays.toFixed(1), verdict, why });
  }
  const worst = findings.some(f => f.verdict === 'FAIL') ? 'FAIL'
              : findings.some(f => f.verdict === 'WARN') ? 'WARN' : 'PASS';
  const out = { service: 'ca-donations-freshness', verdict: worst, status: worst, checked_at: new Date(now).toISOString(), findings };
  mkdirSync(join(ROOT, 'data'), { recursive: true });
  writeFileSync(join(ROOT, 'data', 'latest.json'), JSON.stringify(out, null, 2));
  console.log(`FRESHNESS: ${worst}`);
  for (const f of findings) console.log(`  [${f.verdict}] ${f.source}: ${f.rows} rows` + (f.why ? ` — ${f.why}` : ` (fresh, ${f.age_days}d)`));
  await pool.end();
  process.exit(worst === 'FAIL' ? 1 : 0);
}
main();