← back to La Socrata Ingester
yoloforever c1: LA-data freshness+rowcount canary (baseline-aware, read-only)
9a4905f602100a3cb8b9997538290793789cd649 · 2026-08-11 14:06:51 -0700 · steve
Flags row-count regressions vs a ratcheting baseline + stale feeds (max-age per
table). Report-only; CNCP/George alerting is a gated follow-up. Also: parcel tails
now 100% complete (12,099,614 = full 12.1M, all 5 roll years, OID-cursor fix).
Files touched
A data/canary-baseline.jsonA scripts/canary-freshness.js
Diff
commit 9a4905f602100a3cb8b9997538290793789cd649
Author: steve <steve@designerwallcoverings.com>
Date: Tue Aug 11 14:06:51 2026 -0700
yoloforever c1: LA-data freshness+rowcount canary (baseline-aware, read-only)
Flags row-count regressions vs a ratcheting baseline + stale feeds (max-age per
table). Report-only; CNCP/George alerting is a gated follow-up. Also: parcel tails
now 100% complete (12,099,614 = full 12.1M, all 5 roll years, OID-cursor fix).
---
data/canary-baseline.json | 9 ++++++
scripts/canary-freshness.js | 70 +++++++++++++++++++++++++++++++++++++++++++++
2 files changed, 79 insertions(+)
diff --git a/data/canary-baseline.json b/data/canary-baseline.json
new file mode 100644
index 0000000..d34bf3a
--- /dev/null
+++ b/data/canary-baseline.json
@@ -0,0 +1,9 @@
+{
+ "la_building_permits_raw": 405688,
+ "la_assessor_parcels_raw": 12099614,
+ "la_parcel_geom": 421684,
+ "la_gis_features": 102039,
+ "la_code_enforcement_raw": 850164,
+ "la_business_registrations_raw": 631589,
+ "cslb_raw": 243555
+}
\ No newline at end of file
diff --git a/scripts/canary-freshness.js b/scripts/canary-freshness.js
new file mode 100644
index 0000000..5f2d17d
--- /dev/null
+++ b/scripts/canary-freshness.js
@@ -0,0 +1,70 @@
+// LA-feed freshness + row-count canary (READ-ONLY, $0 local).
+// Baseline-aware, per Steve's canary doctrine: flag a REGRESSION (row count dropped
+// below the stored baseline, or a feed's newest record aged past its max) — not
+// absolute thresholds. First run establishes the baseline and reports OK.
+// Writes tmp/canary/latest.json (heartbeat). Report-only: it alerts nobody and
+// writes no source data. Alerting (CNCP/George) is a separate, gated follow-up.
+import { q, pool } from '../src/db.js';
+import fs from 'fs';
+import path from 'path';
+import { fileURLToPath } from 'url';
+
+const __dirname = path.dirname(fileURLToPath(import.meta.url));
+const BASE = path.resolve(__dirname, '../data/canary-baseline.json');
+const OUT = path.resolve(__dirname, '../tmp/canary');
+
+// table -> { where?, freshCol?, maxAgeDays? } (freshCol omitted = no freshness check)
+const MANIFEST = {
+ la_building_permits_raw: { freshCol: 'issue_date', where: "dataset_id='pi9x-tg5x'", maxAgeDays: 10 },
+ la_assessor_parcels_raw: {},
+ la_parcel_geom: {},
+ la_gis_features: {},
+ la_code_enforcement_raw: { freshCol: 'add_dttm', maxAgeDays: 10 },
+ la_business_registrations_raw:{}, // location_start_date is future-dated; skip freshness
+ cslb_raw: {},
+};
+const DROP_TOLERANCE = 0.97; // count below baseline*0.97 = regression
+
+async function main() {
+ const baseline = fs.existsSync(BASE) ? JSON.parse(fs.readFileSync(BASE, 'utf8')) : {};
+ const firstRun = Object.keys(baseline).length === 0;
+ const results = [];
+
+ for (const [table, cfg] of Object.entries(MANIFEST)) {
+ const whereSql = cfg.where ? `WHERE ${cfg.where}` : '';
+ const count = Number((await q(`SELECT count(*) c FROM ${table} ${whereSql}`)).rows[0].c);
+
+ let ageDays = null, freshest = null;
+ if (cfg.freshCol) {
+ const r = (await q(`SELECT max(${cfg.freshCol}) m, EXTRACT(EPOCH FROM (now() - max(${cfg.freshCol})))/86400 age FROM ${table} ${whereSql}`)).rows[0];
+ freshest = r.m; ageDays = r.age == null ? null : Math.round(Number(r.age) * 10) / 10;
+ }
+
+ const base = baseline[table] ?? count;
+ const floor = Math.floor(base * DROP_TOLERANCE);
+ let status = 'OK', reason = '';
+ if (count < floor) { status = 'LOW'; reason = `count ${count.toLocaleString()} < floor ${floor.toLocaleString()} (baseline ${base.toLocaleString()})`; }
+ else if (ageDays != null && ageDays > cfg.maxAgeDays) { status = 'STALE'; reason = `newest record ${ageDays}d old (> ${cfg.maxAgeDays}d)`; }
+
+ results.push({ table, count, baseline: base, ageDays, freshest, status, reason });
+ // baseline ratchets UP (never down) so a bad reload can't quietly reset the floor
+ baseline[table] = Math.max(base, count);
+ }
+
+ fs.mkdirSync(path.dirname(BASE), { recursive: true });
+ fs.writeFileSync(BASE, JSON.stringify(baseline, null, 2));
+ fs.mkdirSync(OUT, { recursive: true });
+ const worst = results.some(r => r.status === 'LOW') ? 'LOW' : results.some(r => r.status === 'STALE') ? 'STALE' : 'OK';
+ fs.writeFileSync(path.join(OUT, 'latest.json'), JSON.stringify({ checked_at: new Date().toISOString?.() || null, verdict: worst, firstRun, results }, null, 2));
+
+ // report
+ console.log(`\nLA data canary — ${firstRun ? 'BASELINE established' : 'verdict ' + worst}\n`);
+ for (const r of results) {
+ const mark = r.status === 'OK' ? '✔' : r.status === 'STALE' ? '⏳' : '✖';
+ const fresh = r.ageDays != null ? ` fresh ${r.ageDays}d` : '';
+ console.log(` ${mark} ${r.table.padEnd(30)} ${r.count.toLocaleString().padStart(12)}${fresh}${r.reason ? ' — ' + r.reason : ''}`);
+ }
+ console.log('');
+}
+
+main().catch(e => { console.error('canary error:', e.message); process.exitCode = 1; }).finally(() => pool.end());
← 02a2dc3 Add monetization-readiness checklist (viewer/MONETIZATION-RE
·
back to La Socrata Ingester
·
yoloforever c1 FIX (Cody gate): canary reads la_ingest_state 3687729 →