epic(retention): data-retention and DB-correctness defects — including confirmed production data loss

[JSONbored]

Summary

Tracking epic for data-retention and database-correctness defects found in the 2026-07-27 adversarial audit. One is empirically confirmed corrupting data in production; the rest are the same unbounded-growth class that has already caused two fleet-wide outages.

Sub-issues

Issue	Severity	Status
#9470 — dedupeSignalSnapshots keeps the stale row on Postgres	Critical	Confirmed live on the production DB
#9472 — #9415's five tables prune via full scan, hourly	High	Lands when the next build deploys
#9473 — four more unbounded tables in the outage class	High	Latent, growing
#9474 — pruning breaks ledger verification and the public counter	Medium	Dated: ~2026-10-25 for the counter

Context

• Primary DB is Postgres on edge-nl-01 (3842 MB total). The hosted D1 (gittensory) is the one that hit the 10 GB cap on 2026-07-06 and 2026-07-26.
• Retention is running correctly on schedule (last prune 2026-07-27 03:03 UTC; oldest rows match their windows) — none of these are "the prune is broken", they are "the prune deletes the wrong row" (retention: dedupeSignalSnapshots keeps the STALE row on Postgres (MAX(rowid)→MAX(ctid)) — confirmed deleting fresh snapshots in production #9470), "the prune is unaffordable" (retention: #9415's five tables prune via full seq scan + sort, hourly — no PK mapping, no indexes, no migration #9472), "the prune does not cover this" (retention: four more unbounded tables in the outage class #9415 addressed (incl. two whose siblings are already pruned) #9473), and "the prune changes a published meaning" (retention: pruning breaks two consumers that assume permanence — ledger verification (false tamper signal) and the cumulative public counter #9474).
• Largest tables measured 2026-07-27: signal_snapshots 818 MB, github_rate_limit_observations 628 MB / 1,040,277 rows, audit_events 560 MB, check_summaries 511 MB.

Two write-rate observations (not bugs, sizing decisions)

• signal_snapshots averages ~62 KB per row and its 90-day window is not yet full (oldest row 2026-07-02) — it projects to roughly 2.9 GB at steady state. The lever is payload size, not retention.
• github_rate_limit_observations takes 15,368 writes/day and holds 628 MB of rate-limit telemetry in the primary database at a full 30-day window. Worth deciding whether this belongs in Postgres at all, or should be sampled / moved to the metrics pipeline.

Both are worth a decision; neither is filed as a defect.

Acceptance criteria

• All four sub-issues closed.
• A build-time check that every src/-written table either has a retention rule or an explicit, documented exemption (delivered in retention: four more unbounded tables in the outage class #9415 addressed (incl. two whose siblings are already pruned) #9473).
• A real-Postgres test in CI for the dialect-shim class of bug (delivered in retention: dedupeSignalSnapshots keeps the STALE row on Postgres (MAX(rowid)→MAX(ctid)) — confirmed deleting fresh snapshots in production #9470), since a SQLite-only suite structurally cannot catch it.

[linear-code]

JSO-6539

[JSONbored]

All child issues closed and merged. Closing the epic.