retention: #9415's five tables prune via full seq scan + sort, hourly — no PK mapping, no indexes, no migration

[JSONbored]

Summary

#9415 (dffe46db6) bounded the five tables that filled the hosted D1 and moved the prune to hourly — but shipped no migration, and did not add the five tables to RETENTION_PK_COLUMN. As a result each of those five prunes runs as a full sequential scan plus a full sort, hourly, on the two largest tables in the database. This is the exact pathology #9083's own comment warns permanently stalls retention.

The deployed image (orb-v3.5.0-beta.10) predates #9415 — the prune still runs daily at 03:03 — so this lands when the next build deploys. Fixing it before that deploy is the point of this issue.

Mechanism (verified at HEAD, 776c414)

Rules added by #9415 — src/db/retention.ts:73-77:

check_summaries (updated_at, 30d), pull_request_files (updated_at, 30d),
repo_github_totals_snapshots (fetched_at, 30d), recent_merged_pull_requests (updated_at, 30d),
orb_pr_outcomes (occurred_at, 90d)

RETENTION_PK_COLUMN (src/db/retention.ts:91-106) does not list any of them, though all five have a single-column id TEXT PRIMARY KEY. So pkColumnFor() falls back to rowid, which the pg shim rewrites to ctid (src/selfhost/pg-dialect.ts:192-194). Every 1000-row batch therefore becomes:

• outer ctid IN (subquery) ⇒ full sequential scan of the whole table, and
• inner SELECT … ORDER BY <timestamp> LIMIT 1000 ⇒ full scan + sort, because none of the five has an index on its retention column.

migrations/0193_retention_column_indexes.sql covers only the pre-#9415 policy. The rationale at src/index.ts:332 for the hourly cadence — "each rule is an indexed range delete" — is simply false for these five.

On the SQLite backend the adapter is synchronous (src/selfhost/d1-adapter.ts:43-45, nodeSqliteDriver), so each scan blocks the event loop: HTTP, /health and webhook intake all stall.

Scale (measured on edge-nl-01, 2026-07-27)

table	size	live rows
check_summaries	511 MB	180,820 (+27,103 dead)
pull_request_files	179 MB	63,091 (+4,493 dead)

Draining an accumulated backlog means up to 250 batches × 2 full scans × 5 tables, every hour. Even at steady state, the first (empty) batch still full-scans and sorts each table hourly.

Deliverables

• Add the five id entries to RETENTION_PK_COLUMN.
• Add a migration (next contiguous NNNN_*.sql) creating indexes on check_summaries(updated_at), pull_request_files(updated_at), repo_github_totals_snapshots(fetched_at), recent_merged_pull_requests(updated_at), orb_pr_outcomes(occurred_at) — same pattern as 0193.
• Add a guard so the class cannot recur: a unit test asserting every table in RETENTION_POLICY has an entry in RETENTION_PK_COLUMN and a corresponding index in the migration set. This is the check whose absence let fix(retention): bound the five unpruned tables that filled the hosted D1 #9415 ship incomplete.
• Correct the comment at src/index.ts:332.

Tests (must fail against current main)

• Policy/PK-map/index coverage test (as above) fails today for the five tables.
• Query-plan assertion (or an equivalent guard) that the prune's delete uses the primary key and the scan uses the timestamp index.

Expected outcome

Every retention rule is an indexed range delete on both backends, as index.ts already claims, and the hourly cadence is safe to deploy.

[linear-code]

JSO-6536