← back to Ca Donations
ca-donations: freshness+completeness canary + data-quality audit (cycle 2)
e220fe934e7d7e0c40e0eb84ec9147aec3e13ea8 · 2026-08-22 17:24:08 -0700 · Steve
- scripts/freshness-canary.mjs: per-source freshness (stale run) AND row-presence
(ran-ok-but-0-rows) checks; emits PASS/WARN/FAIL to data/latest.json (fleet-health vocab)
- caught a real bug on first run: fec_bulk reported ok/5000 but 0 rows landed in
political_contributions (federal contributions silently missing)
- audit: 6.95M state contributions (2018-2026), 31995 legit negative amounts (refunds),
17 future-dated + 125 null-donor artifacts already filtered from serving (cycle 1)
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Files touched
A data/latest.jsonA scripts/freshness-canary.mjs
Diff
commit e220fe934e7d7e0c40e0eb84ec9147aec3e13ea8
Author: Steve <steve@designerwallcoverings.com>
Date: Sat Aug 22 17:24:08 2026 -0700
ca-donations: freshness+completeness canary + data-quality audit (cycle 2)
- scripts/freshness-canary.mjs: per-source freshness (stale run) AND row-presence
(ran-ok-but-0-rows) checks; emits PASS/WARN/FAIL to data/latest.json (fleet-health vocab)
- caught a real bug on first run: fec_bulk reported ok/5000 but 0 rows landed in
political_contributions (federal contributions silently missing)
- audit: 6.95M state contributions (2018-2026), 31995 legit negative amounts (refunds),
17 future-dated + 125 null-donor artifacts already filtered from serving (cycle 1)
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
data/latest.json | 53 ++++++++++++++++++++++++++++++++++++++++++++
scripts/freshness-canary.mjs | 47 +++++++++++++++++++++++++++++++++++++++
2 files changed, 100 insertions(+)
diff --git a/data/latest.json b/data/latest.json
new file mode 100644
index 0000000..97cf7dc
--- /dev/null
+++ b/data/latest.json
@@ -0,0 +1,53 @@
+{
+ "service": "ca-donations-freshness",
+ "verdict": "FAIL",
+ "status": "FAIL",
+ "checked_at": "2026-08-23T00:23:26.040Z",
+ "findings": [
+ {
+ "source": "calaccess",
+ "table": "political_contributions",
+ "rows": 6947645,
+ "last_ok": "2026-08-21T15:30:22.566Z",
+ "age_days": 1.4,
+ "verdict": "PASS",
+ "why": ""
+ },
+ {
+ "source": "fec_bulk",
+ "table": "political_contributions",
+ "rows": 0,
+ "last_ok": "2026-08-21T15:24:19.036Z",
+ "age_days": 1.4,
+ "verdict": "FAIL",
+ "why": "ran ok but 0 rows in political_contributions (silent-empty)"
+ },
+ {
+ "source": "irs_990",
+ "table": "charitable_grants",
+ "rows": 40514,
+ "last_ok": "2026-08-21T15:28:40.378Z",
+ "age_days": 1.4,
+ "verdict": "PASS",
+ "why": ""
+ },
+ {
+ "source": "ca_ag",
+ "table": "charitable_orgs",
+ "rows": 161285,
+ "last_ok": "2026-08-21T15:11:50.911Z",
+ "age_days": 1.4,
+ "verdict": "PASS",
+ "why": ""
+ },
+ {
+ "source": "propublica",
+ "table": "charitable_orgs",
+ "rows": 125,
+ "last_ok": "2026-08-21T15:09:56.156Z",
+ "age_days": 1.4,
+ "verdict": "PASS",
+ "why": ""
+ }
+ ]
+}
\ No newline at end of file
diff --git a/scripts/freshness-canary.mjs b/scripts/freshness-canary.mjs
new file mode 100644
index 0000000..cf52c13
--- /dev/null
+++ b/scripts/freshness-canary.mjs
@@ -0,0 +1,47 @@
+// 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();
← 8340af8 ca-donations: FIX-FIRST hardening (Cody gate) — rate limit +
·
back to Ca Donations
·
auto-data-snapshot: 2026-08-22T18:14:40 (1 data files) — dat b63091b →