Retention janitor: sweep the oldest runs of CardScanLog abstention history

[WilfordGrimley]

Owner ruling (2026-07-29)

Retention is a housekeeping question, not a correctness one, and the answer is keep N runs and sweep the oldest — never delete wholesale.

"how many runs of abstention history do we want to keep?" — this is the correct framing.
"i think this is safer than any form of deleting. we can create a janitor that sweeps the oldest runs."

Why this became a question

CardScanLog is the "we looked and did not vote" table. It does two jobs:

1. Audit trail — "join-key examined this card and abstained because the OCR was blank." Genuinely valuable: it distinguishes we have not tried from we tried and could not decide.
2. Eligibility suppression — calculators exclude cards carrying a non-rescannable skip reason for their own anonymous_id.

Job 2 is being removed by the run-scoped eligibility work (prior runs must not suppress a fresh run; only the current run's own output should, so a killed run resumes rather than redoing completed batches). Once eligibility is run-scoped, job 2 disappears without deleting anything and job 1 survives intact.

That leaves only growth.

Measured state (2026-07-29)

• CardScanLog: 2,731,572 rows, and it is append-only — no unique_together, no constraints, unlike CardPrintingTag / CardTagVote / CardArtistVote which all carry unique constraints and therefore converge.
• stage-d-join-key-v1 alone holds 647,142 rows against ~230k cards — it has already re-scanned the catalog ~2.8x.
• A from-scratch pass is projected to add 1.0–1.6M rows (+37% to +60%), so the table roughly doubles every two passes.
• Every calculator's eligibility subquery scans this table. Measured at batch 25: the fallback eligibility query costs 945ms and slow-path 1,152ms; in BULK mode (card_ids=None) both exceed a 180-second statement timeout.

What this issue asks for

A janitor that retains the N most recent runs of abstention history per calculator and sweeps older ones.

Design notes:

• Retention is per-run, not per-row-age. "Keep the last N runs" is meaningful; "keep 90 days" is not, because run cadence is irregular.
• run_id must be reliable first. It is not today: all 28,112 deductive-backfill-v1 CardPrintingTag rows carry run_id = NULL, so at least one writer never set it. Audit which CardScanLog writers populate run_id before building anything that keys on it — rows with a null run cannot be assigned to a generation. (PR Re-scope the deductive-backfill zero-weight rule from the METHOD to the COHORT #570 fixes the backfill writer specifically and is a template.)
• N ≥ 1 is the floor, per the same ruling that at least one prior generation is retained.
• The sweep is a write to production audit data and should therefore be an explicit, authorised operator action with a dry-run mode reporting what it would remove, not an implicit background task.
• Consider whether the sweep should be per-calculator — a dormant calculator's few rows are cheap to keep, whereas join-key's 647k dominate.

Related

• PR Adopt a *_SKIP_REASON declaration convention; tether the roster to a doc #567 made the skip-reason roster statically enumerable and tethered it to docs/reference/skip-reasons.md, so the vocabulary being retained is now documented (38 values from 55 constants; 23 distinct values live in production).
• PR Gate warm_catalog_stats on an in-flight sweep; index CardScanLog(anonymous_id, skip_reason) #568 (separate session) adds a CardScanLog index, which addresses the scan cost independently of retention.
• The monolith consolidation spec identifies unscoped eligibility subqueries as the dominant per-batch cost; scoping those is a larger win than retention and is independent of it.