orb(db): 3.4 GB in one month — no retention target has a usable index, and four caches have no delete path at all

[JSONbored]

Live sizes (verified on edge-nl-01, PG 16.14)

table	rows	size
signal_snapshots	18,002	787 MB (~44 KB/row)
github_rate_limit_observations	1,022,300	571 MB
audit_events	598,590	521 MB
check_summaries	177,357	510 MB
advisories	134,863	329 MB
pull_request_files	60,947	175 MB
grounding_file_content_cache	10,615	110 MB
webhook_events	305,660	97 MB

That is one month of operation for ~12.5k reviews.

1. Every retention DELETE is a full table scan

src/db/retention.ts ~88:

DELETE FROM ${table} WHERE rowid IN (SELECT rowid FROM ${table} WHERE ${col} < ? LIMIT 1000)

(a) No retention-target table has an index whose leading column is its retention timestamp. webhook_events.received_at has no index at all (only (status)); audit_events, ai_usage_events, product_usage_events, github_rate_limit_observations, signal_snapshots, score_previews, repo_snapshots, agent_context_snapshots, notification_deliveries all have the timestamp only in a trailing position.

(b) On Postgres rowid → ctid (src/selfhost/pg-dialect.ts ~120), and DELETE … WHERE ctid IN (SELECT ctid … LIMIT n) becomes a hash semi-join whose outer side is a Seq Scan of the whole table — the TID-scan path only fires for constant quals, not a sub-SELECT. So each of up to 50 batches re-scans the entire table.

prune-retention is a maintenance job with a 30-minute timeout. Once the combined scan cost exceeds it, the job is reclaimed as stuck and retried forever with retention permanently behind. There is also a hard MAX_DELETED_PER_TABLE = 50_000 per run: once a table exceeds 50k expiring rows/day, retention never catches up.

2. Four caches store large blobs and are never deleted

grounding_file_content_cache stores whole file bodies keyed (repo, path, head_sha) with a read-side 24h TTL but no delete anywhere — no DELETE FROM in src/, not in RETENTION_POLICY. Already 110 MB of which nearly all is dead. Every push mints a new head_sha, so a PR with 20 pushes x 15 files leaves 300 permanently-retained blobs.

Same shape: ai_review_cache.notes, ai_slop_cache.finding_json, linked_issue_satisfaction_cache.result_json. (impact_map_query_cache is the only cache with a prune.)

3. ~25 append-only tables have no retention rule at all

Including review_audit (146k rows / 70 MB), decision_records, orb_webhook_events, gate_outcomes, agent_runs, check_summaries, advisories, pull_request_files.

Fix

1. CREATE INDEX CONCURRENTLY on each retention column, and replace the ctid semi-join with a PK/ordered-range delete (WHERE pk IN (SELECT pk … ORDER BY col LIMIT 1000)).
2. Add the four caches to RETENTION_POLICY (grounding at 2d, AI caches at 30d).
3. Triage the ~25 unretained append-only tables; signal_snapshots at 44 KB/row deserves its own look — it already filled D1's 10 GB cap once.

Refs #9054 (the webhook volume feeding this).

[linear-code]

JSO-6284