← back to La Socrata Ingester
yoloforever c3 FIX (Cody): green-able canary + real CSLB load + time-anchored bleed
0377c1a189ec2e18c597d748994121f1a85ebb69 · 2026-08-11 15:05:09 -0700 · steve
Cody c3 (all verified): (1) permanently-FAIL canary = anti-canary -> demote KNOWN_BROKEN
sources (gis_zoning, documented) to WARNING so verdict can be OK/WARN; a NEW error still
FAILs. (2) bleed floor was run-count anchored (walks up, misses slow decline) -> time-anchored
to ~30d. (3) CSLB state was a planted timestamp that lies -> scripts/cslb-load.sh writes
real state on actual load. Verdict now WARN (was perpetual FAIL); green = signal again.
Files touched
M scripts/canary-freshness.jsA scripts/cslb-load.sh
Diff
commit 0377c1a189ec2e18c597d748994121f1a85ebb69
Author: steve <steve@designerwallcoverings.com>
Date: Tue Aug 11 15:05:09 2026 -0700
yoloforever c3 FIX (Cody): green-able canary + real CSLB load + time-anchored bleed
Cody c3 (all verified): (1) permanently-FAIL canary = anti-canary -> demote KNOWN_BROKEN
sources (gis_zoning, documented) to WARNING so verdict can be OK/WARN; a NEW error still
FAILs. (2) bleed floor was run-count anchored (walks up, misses slow decline) -> time-anchored
to ~30d. (3) CSLB state was a planted timestamp that lies -> scripts/cslb-load.sh writes
real state on actual load. Verdict now WARN (was perpetual FAIL); green = signal again.
---
scripts/canary-freshness.js | 31 ++++++++++++++++++++++---------
scripts/cslb-load.sh | 22 ++++++++++++++++++++++
2 files changed, 44 insertions(+), 9 deletions(-)
diff --git a/scripts/canary-freshness.js b/scripts/canary-freshness.js
index 3ac227d..a6f971e 100644
--- a/scripts/canary-freshness.js
+++ b/scripts/canary-freshness.js
@@ -35,8 +35,13 @@ async function main() {
// observation (widest window); flag BLEED on a sustained shrink the floor would miss.
const HIST = path.resolve(__dirname, '../data/canary-history.jsonl');
const history = fs.existsSync(HIST) ? fs.readFileSync(HIST, 'utf8').trim().split('\n').filter(Boolean).map(l => JSON.parse(l)) : [];
- const oldest = history[0]?.counts || {};
const nowTs = (await q(`SELECT now() n`)).rows[0].n;
+ // TIME-anchored bleed reference (Cody c3): the newest history entry OLDER than 30d
+ // (else the earliest available). Run-count anchoring let a slow decline walk the
+ // floor up; a time anchor holds regardless of run cadence / the 120-run trim.
+ const cutoff = new Date(new Date(nowTs).getTime() - 30 * 86400 * 1000).toISOString();
+ const older = history.filter(h => String(h.ts) <= cutoff);
+ const anchor = ((older.length ? older[older.length - 1] : history[0]) || {}).counts || {};
const curCounts = {};
for (const [table, cfg] of Object.entries(MANIFEST)) {
@@ -52,7 +57,7 @@ async function main() {
const base = baseline[table] ?? count;
const floor = Math.floor(base * DROP_TOLERANCE);
curCounts[table] = count;
- const ref = oldest[table]; // earliest observed count
+ const ref = anchor[table]; // time-anchored (~30d) reference count
let status = 'OK', reason = '';
if (count < floor) { status = 'LOW'; reason = `count ${count.toLocaleString()} < floor ${floor.toLocaleString()} (baseline ${base.toLocaleString()})`; }
else if (ref != null && history.length >= 1 && count < Math.floor(ref * 0.99)) { status = 'BLEED'; reason = `count ${count.toLocaleString()} < earliest observed ${Number(ref).toLocaleString()} (slow decline the floor misses)`; }
@@ -67,12 +72,19 @@ async function main() {
// error" signal. Covers the count-only tables the row check alone can't see stale, and
// surfaces any source whose last run errored (Cody c1: count-only = false comfort).
const SRC_MAX_AGE = 14; // days; generous until a scheduled refresh job exists
+ // Known-broken upstreams: demoted to WARNING (BROKEN) so one unfixable source can't
+ // hold the whole canary red forever (Cody c3: a permanently-FAIL canary is ignored).
+ // Document the reason + date; remove the entry when the source is fixed.
+ const KNOWN_BROKEN = {
+ gis_zoning: 'NavigateLA ArcGIS gateway 502s past offset 44k; ~75% partial load; full LA zoning is fragmented across per-community Zone_Builder services (noted 2026-08-11)',
+ };
const srcRows = (await q(`SELECT source, last_status, EXTRACT(EPOCH FROM (now()-last_run))/86400 age FROM la_ingest_state`)).rows;
const sources = srcRows.map(r => {
const age = r.age == null ? null : Math.round(Number(r.age) * 10) / 10;
const errored = String(r.last_status || '').startsWith('error');
- const status = errored ? 'ERRORED' : (age != null && age > SRC_MAX_AGE ? 'STALE' : 'OK');
- return { source: r.source, ageDays: age, last_status: r.last_status, status };
+ const status = errored ? (KNOWN_BROKEN[r.source] ? 'BROKEN' : 'ERRORED')
+ : (age != null && age > SRC_MAX_AGE ? 'STALE' : 'OK');
+ return { source: r.source, ageDays: age, last_status: r.last_status, status, note: KNOWN_BROKEN[r.source] };
}).sort((a, b) => (b.ageDays || 0) - (a.ageDays || 0));
fs.mkdirSync(path.dirname(BASE), { recursive: true });
@@ -82,11 +94,11 @@ async function main() {
const trimmed = fs.readFileSync(HIST, 'utf8').trim().split('\n').filter(Boolean);
if (trimmed.length > 120) fs.writeFileSync(HIST, trimmed.slice(-120).join('\n') + '\n');
fs.mkdirSync(OUT, { recursive: true });
- const anyErr = sources.some(s => s.status === 'ERRORED');
- const anyStaleSrc = sources.some(s => s.status === 'STALE');
+ const anyErr = sources.some(s => s.status === 'ERRORED'); // excludes known-broken
+ const anyWarnSrc = sources.some(s => s.status === 'STALE' || s.status === 'BROKEN');
const anyBleed = results.some(r => r.status === 'BLEED');
const worst = (results.some(r => r.status === 'LOW') || anyErr) ? 'FAIL'
- : (results.some(r => r.status === 'STALE') || anyStaleSrc || anyBleed) ? 'STALE' : 'OK';
+ : (results.some(r => r.status === 'STALE') || anyWarnSrc || anyBleed) ? 'WARN' : 'OK';
fs.writeFileSync(path.join(OUT, 'latest.json'), JSON.stringify({ verdict: worst, firstRun, results, sources }, null, 2));
// report
@@ -99,9 +111,10 @@ async function main() {
}
console.log('\n SOURCE last-ingest (la_ingest_state):');
for (const s of sources) {
- const mark = s.status === 'OK' ? '✔' : s.status === 'STALE' ? '⏳' : '✖';
+ const mark = s.status === 'OK' ? '✔' : s.status === 'STALE' ? '⏳' : s.status === 'BROKEN' ? '⚠' : '✖';
const age = s.ageDays != null ? `${s.ageDays}d ago` : 'never';
- console.log(` ${mark} ${s.source.padEnd(30)} ${age.padStart(10)}${s.status !== 'OK' ? ' — ' + (s.status === 'ERRORED' ? s.last_status : 'stale') : ''}`);
+ const detail = s.status === 'OK' ? '' : ' — ' + (s.status === 'BROKEN' ? `known-broken: ${s.note}` : s.status === 'ERRORED' ? s.last_status : 'stale');
+ console.log(` ${mark} ${s.source.padEnd(30)} ${age.padStart(10)}${detail}`);
}
console.log('');
}
diff --git a/scripts/cslb-load.sh b/scripts/cslb-load.sh
new file mode 100755
index 0000000..fe0116d
--- /dev/null
+++ b/scripts/cslb-load.sh
@@ -0,0 +1,22 @@
+#!/bin/bash
+# CSLB full refresh: download the free portal Master CSV, load into cslb_raw, and
+# record the load in la_ingest_state so the canary's staleness check reflects REALITY
+# (Cody c3: a manually-seeded state row lies; state must be written by the load path).
+set -euo pipefail
+cd "$(dirname "$0")/.."
+DB="${REALESTATE_DB:-realestate}"
+
+echo "1/3 download CSLB Master CSV…"
+node src/cslb/download.js
+
+echo "2/3 load into cslb_raw (truncate + copy)…"
+psql "$DB" -q -c "TRUNCATE cslb_raw;"
+psql "$DB" -q -c "\copy cslb_raw FROM 'tmp/cslb-master.csv' WITH (FORMAT csv, HEADER true)"
+N=$(psql "$DB" -tAc "SELECT count(*) FROM cslb_raw")
+
+echo "3/3 record load in la_ingest_state…"
+psql "$DB" -q -c "INSERT INTO la_ingest_state (source, dataset_id, last_run, rows_upserted, last_status)
+ VALUES ('cslb_master','cslb-portal', now(), $N, 'ok')
+ ON CONFLICT (source) DO UPDATE SET last_run=now(), rows_upserted=$N, last_status='ok';"
+
+echo "✔ CSLB loaded: $N contractors; la_ingest_state.cslb_master updated (real load timestamp)."
← f794850 yoloforever c3: close Cody-c1 canary gaps — CSLB tracking +
·
back to La Socrata Ingester
·
yoloforever c4: newsworthy permit deals feed (scripts/deals- 909b171 →