retention: four more unbounded tables in the outage class #9415 addressed (incl. two whose siblings are already pruned)

[JSONbored]

Summary

The audit found four more tables with the same growth profile as the five #9415 just bounded — per-event GitHub mirrors and per-commit ledgers with INSERT per event and no delete path anywhere in src/ (verified by a full DELETE FROM sweep). This table class has already caused two fleet-wide outages when the hosted D1 hit its 10 GB cap. Two of them have a pruned sibling, which is what makes their omission clearly unintentional.

The four

1. pull_request_reviews — migrations/0003_data_spine.sql:74-87, one row per review submission with a payload_json blob, synced by the same backfill segment machinery as pull_request_files and check_summaries. It is the sixth member of #9415's own "re-derivable GitHub mirror" family and was simply missed. Suggested: 30 d.

2. predicted_gate_calibration_ledger — src/review/predicted-gate-calibration-ledger.ts:108-130, one row per (login, project, PR, commit). Its sibling predicted_gate_calls is pruned at 90 d (src/db/retention.ts:37). Note :148 computes an all-time per-login agreement rate — that read must be windowed as part of adding the rule, or the metric changes meaning.

3. contributor_gate_history — src/review/contributor-calibration.ts:57-64, one row per (login, project, PR, head_sha), so every push adds a row. All readers are already windowed (src/review/contributor-gate-eval.ts:94, src/review/predicted-gate-agreement.ts:106 both use created_at >= ?), so aged rows are pure dead weight. Its only index is on login (migrations/0126:45), so those windowed reads also full-scan.

4. decision_replay_inputs — src/review/decision-replay.ts:201-208, one replay_json blob per decision record. Its parent decision_records is pruned at 180 d (src/db/retention.ts:57), so these rows are permanently orphaned — they outlive the thing they describe.

Lower-rate members of the same class

Worth bundling if cheap, or splitting out if not: alert_dedup_claims (src/review/alerts.ts:233,:249 — one row per agent-hour and per anomaly-hour), override_audit (src/review/auto-apply.ts:310 — an audit log, unpruned while audit_events gets 90 d), github_agent_command_answers / _feedback (src/db/repositories.ts:2561), decision_audit_labels, decision_ledger_anchors (one row per attempt including failures), orb_reuse_counters, and self-host DLQ dead rows past the revive ceiling (src/selfhost/pg-queue.ts:732-736 — full webhook payloads retained until an operator purges by hand; an error storm leaves them forever).

Also unpruned on disk: private-config backups. atomicWriteWithBackup (src/selfhost/private-config.ts:453) mints a .bak-<ts> on every admin write and nothing ever removes them — listConfigBackupsForScope only lists.

Dormant tables (drop candidates)

Schema-only, no writers at all — the same cleanup #141 did for scoring-intelligence: sync_runs, agent_runs, agent_actions, repo_queue_trend_snapshots, registry_snapshots, burden_forecasts, official_miner_detections, orb_events, registry_drift_events, issue_quality_reports. Confirm each has no writer before dropping.

Deliverables

• Add retention rules for the four primary tables, each with a RETENTION_PK_COLUMN entry and a timestamp index (coordinate with retention: #9415's five tables prune via full seq scan + sort, hourly — no PK mapping, no indexes, no migration #9472, which fixes the same omission for fix(retention): bound the five unpruned tables that filled the hosted D1 #9415's five — ideally one migration covers both sets).
• Window the all-time read at predicted-gate-calibration-ledger.ts:148 before its table becomes windowed.
• Add an age-based prune for DLQ dead rows.
• Prune private-config .bak-* files (keep the most recent N per scope).
• Decide and act on the dormant-table list — drop, or document why each is retained.
• Extend the coverage test from retention: #9415's five tables prune via full seq scan + sort, hourly — no PK mapping, no indexes, no migration #9472 into a completeness check: enumerate tables written by src/ that have no delete path and no retention rule, and fail (or warn with an allowlist) on new ones. This is what turns a recurring outage class into a build-time error.

Expected outcome

Every table that grows per event, per review, per commit or per PR is either bounded by policy or explicitly and durably exempted. A new unbounded table cannot be introduced without the check noticing.

[linear-code]

JSO-6537