90d purge has never fired - exercise it before it first runs ~2026-10-10

Sub-tickets

Activity

• wi-cli-venus created · 6w ago
• wi-cli-venus statusChanged · 5w ago
• wi-cli-venus audit-finding · 5w ago
  Exercise found a real defect, not a clean dry-run. DEFECT (new, not in the WI): the purge cuts by INSTANT while the rollup groups by whole UTC DAY, and the two disagree by the time of day the job runs. Night D at 03:00 would delete everything before (D-90) 03:00, leaving the last 21h of day D-90 in usageEvents. Night D+1 rollup is unbounded below, re-emits day D-90 from that 21h remnant alone, and its ON CONFLICT DO UPDATE SET hits = EXCLUDED.hits OVERWRITES the correct full-day total with the partial count. The night after, the remnant is deleted and the day vanishes from usageEvents, but the corrupted usageDaily row is permanent. Every day would have had its aggregate silently rewritten downward on the night before it disappeared. The WI premise of a self-healing backfill holds only for wholly-present days, and that is exactly what hides this: the rollup is doing what it was written to do. (a) DRY-RUN THE PREDICATE: 0 rows match under either the old or the new predicate today. usageEvents holds 18284 rows, 2026-07-12 05:00:52Z to now; the instant-purge first bites 2026-10-10 05:00:52Z. Fix therefore ships with a provably no-op purge. (b) BACKFILL COVERAGE: usageDaily recomputed from usageEvents and EXCEPT-compared BOTH directions. stored_not_in_recomputed = 0, so no aggregate is corrupted yet. The 34 recomputed-not-stored rows are all day 2026-09-11, the day tonight 03:00 run will cover; every earlier completed day is already rolled. usageDaily spans 2026-07-12..2026-09-10, 61 days, 2032 rows. (c) AUTOVACUUM: cannot be measured before the delete it reacts to exists, and DB access here is SELECT-only, so this becomes a POST-run read rather than a pre-run one. rollup.sh now selects relname, n_live_tup, n_dead_tup, last_autovacuum, last_autoanalyze from pg_stat_user_tables after the DELETE, so the journal captures the first real purge around 2026-10-10. Current state: usageEvents 18240 live / 0 dead / autovac 2026-09-10 21:03Z; usageDaily 2032 live / 0 dead / autovac 2026-09-11 03:00Z. (d) SHOULD THE PURGE REFUSE WHEN THE ROLLUP WROTE NOTHING: NO, and the question points at the wrong signal. With the boundaries aligned, the only way the purge eats an un-rolled day is the rollup FAILING, which set -euo pipefail plus ON_ERROR_STOP=1 already cover. And rollup-wrote-nothing is near-useless as an alarm: the rollup is a full idempotent backfill, so it re-emits thousands of rows every night whether or not ingest is alive, and writes zero only when the table is empty. Replaced with the liveness check that actually discriminates: count of usageEvents in the last 24h, and zero exits non-zero so systemd marks the unit failed. It never silently skips work, because a clean Stopped is the outage mode that hid the enamel applog relay for 16.8 days. FIX in /opt/telemetry-ingest/rollup.sh: rollup and purge now run in ONE psql session against ONE cutoff computed by gset, since two invocations each evaluate their own now() and can straddle a UTC midnight. DELETE predicate is ts < ((today - 90)::timestamp AT TIME ZONE UTC), the same day boundary the rollup groups by, written against bare ts so it stays sargable. EXPLAIN confirms Index Scan using usageEvents_ts_idx with Index Cond ts < 2026-06-14 00:00:00+00. Rejected GREATEST(old, new) as the guard: it would also block legitimate downward corrections and papers over the mismatch instead of removing it. Verified read-only: EXPLAIN on both statements, bash -n, no DML run. SECOND PURGE PATH ruled out: telemetry-retention.sh (whey-side) has zero references to webusage or usageEvents; the only other fleet-wide hit is a comment in agent-telemetry-ingest.py. Push pending: venus-host.push-gate held by coder02-mars-cc, waiting on the release DM.
• wi-cli-venus completed · 5w ago
  PUSHED AND WORKING. Commit 6388b93 in ElazarPimentel/telemetry-ingest, pushed, in sync with origin/main. EXERCISED LIVE, not just planned: systemctl --user start telemetry-webusage-rollup.service, Result=success ExecMainStatus=0. Journal: INSERT 0 2066 (the 2032 already-rolled days plus the 34 rows for 2026-09-11), DELETE 0, the new pg_stat_user_tables block, and "rollup+purge ok: 2026-09-12T00:32:57Z (events ingested in last 24h: 368)". The DELETE 0 is a no-op BY CONSTRUCTION, not a control - nothing is within 90 days of eligible until 2026-10-10 - so this run proves the script parses, connects, rolls up, purges, instruments and exits 0, and proves nothing about purge behaviour on real rows. That first real purge is the journal entry to read around 2026-10-10, and the pg_stat_user_tables read now in the script is what will carry the autovacuum answer. Post-run verification: usageDaily recomputed from usageEvents and EXCEPT-compared BOTH directions is now 0 and 0, i.e. every stored aggregate agrees exactly with a recomputation. usageDaily spans 2026-07-12..2026-09-11, 62 days, 2066 rows. Side fix in the same commit: this repo had no .gitpush-pre.sh, so gitpush.sh refused. Added one running bash -n rollup.sh and node --check server.mjs, with its own coverage limit written into the file - the SQL lives in a heredoc, so bash -n treats it as an opaque string and a malformed query would pass the pre-check and fail at 03:00 UTC. SQL changes here must be EXPLAINed against the live loopback DB before pushing. Two gitpush.sh observations for bin-venus-cc, not blocking: (1) the GITPUSH_INFO_VERSION=4.56 stamp was inserted as line 1 of a NEW .gitpush-pre.sh, ABOVE the shebang, leaving the file with no shebang at all; on the enamel repo the same stamp sits correctly on line 2 under the shebang, so the insert appears to differ for an untracked file. Corrected by hand before the push. (2) the no-.gitpush-pre.sh stop and the policy-version handshake are two separate stops, so a repo missing the file costs three gitpush runs.