fix(db): RETENTION_POLICY omits webhook_events, leaving closed issue #3896 incompletely implemented

[JSONbored]

Context

src/db/retention.ts's RETENTION_POLICY is the hardcoded list of append-only/log tables that pruneExpiredRecords age-prunes on the daily maintenance cron (src/queue/retention.ts, wired via prune-retention in src/selfhost/maintenance-admission.ts's MAINTENANCE_JOB_TYPES). It currently covers exactly four tables:

export const RETENTION_POLICY: readonly RetentionRule[] = [
  { table: "audit_events", column: "created_at", days: 90 },
  { table: "ai_usage_events", column: "created_at", days: 90 },
  { table: "product_usage_events", column: "occurred_at", days: 180 },
  { table: "github_rate_limit_observations", column: "observed_at", days: 30 },
  { table: "signal_snapshots", column: "generated_at", days: 90 },
  { table: "score_previews", column: "generated_at", days: 90 },
  { table: "repo_snapshots", column: "fetched_at", days: 90 },
  { table: "agent_context_snapshots", column: "created_at", days: 30 },
];

Closed issue #3896 ("Add bounded retention for webhook_events, audit_events, ai_usage_events, product_usage_events, agent_context_snapshots") explicitly required five tables, citing webhookEvents (src/db/schema.ts) by name as "one row per inbound webhook delivery, never deduped or aged out." The other four of that issue's five landed in RETENTION_POLICY above — webhook_events did not. It has no pruning path anywhere in the codebase today (confirmed via grep -rn "webhookEvents\b" src/ — only the schema definition, the insert/upsert in src/db/repositories.ts, and the read-back by delivery id; no delete path exists at all), so it grows completely unbounded, exactly the incident #3896 was filed to prevent.

This also silently narrows src/selfhost/d1-size-probe.ts's central-cloud D1 monitoring: DEFAULT_MONITORED_TABLES is derived directly from RETENTION_POLICY.map((rule) => rule.table) specifically so the two "never drift apart" (its own comment). Because webhook_events is missing from the policy, it's also invisible to the D1 size/row-count probe that exists to catch exactly this kind of unbounded-growth table before it re-triggers the account-storage-cap incident #3810 was filed for.

webhookEvents (src/db/schema.ts) has a received_at timestamp column (text("received_at").notNull().$defaultFn(() => nowIso())) — the same shape every other RETENTION_POLICY entry already keys on.

Requirements

• Add a webhook_events entry to RETENTION_POLICY in src/db/retention.ts, keyed on the received_at column.
• Choose a retention window consistent with the other high-volume log tables already in the policy (e.g. matching audit_events/ai_usage_events's 90-day window is a reasonable default — webhook_events is used for short-lived delivery-id idempotency lookups, not long-lived history, so a 90-day window is generous, not risky).
• Do not change retentionWhere()'s special-casing (only audit_events currently has a durable-type carve-out; webhook_events needs no equivalent — every row is safely prunable once past the window).
• No other code changes are required: pruneExpiredRecords, d1-size-probe.ts's DEFAULT_MONITORED_TABLES, and the prune-retention cron job all consume RETENTION_POLICY directly, so adding the entry alone wires pruning and D1-probe monitoring for webhook_events correctly.

Deliverables

• A { table: "webhook_events", column: "received_at", days: <N> } entry added to RETENTION_POLICY in src/db/retention.ts.

Test Coverage Requirements

src/db/retention.ts is under src/**, Codecov patch-gated at 99%, branch-counted (this change is a data addition to an existing exported array, but the array's own consumers — pruneExpiredRecords's loop and retentionWhere's branch — must still exercise the new entry, not just rely on existing coverage of the array shape):

• Extend the existing pruneExpiredRecords test coverage (see whichever test/unit/*.test.ts file already exercises RETENTION_POLICY/pruneExpiredRecords) with a case confirming webhook_events rows older than the cutoff are deleted and rows within the window are retained — mirroring the existing per-table assertions for audit_events/repo_snapshots/etc.
• Confirm (or add, if not already covered) a test asserting d1-size-probe.ts's DEFAULT_MONITORED_TABLES now includes webhook_events, since that list derives directly from RETENTION_POLICY.

Expected Outcome

webhook_events rows are pruned on the same daily cadence as every other high-volume log table, closing the exact gap #3896 was filed and closed to fix, and the central-cloud D1 size probe (d1-size-probe.ts) now monitors webhook_events's row count alongside the rest of the retention-governed tables.

Links & Resources

• src/db/retention.ts — RETENTION_POLICY to extend.
• src/db/schema.ts — webhookEvents table definition (receivedAt column).
• src/selfhost/d1-size-probe.ts — DEFAULT_MONITORED_TABLES (derives from RETENTION_POLICY, no separate change needed).
• Closed issue feat(selfhost): add bounded retention for webhook_events, audit_events, ai_usage_events, product_usage_events, agent_context_snapshots #3896 — the original spec citing webhook_events by name, of which this table alone was never actually implemented.
• Closed issue obs(selfhost): monitor Cloudflare D1 database size and add bounded dedup retention for signal_snapshots #3810 — the D1 account-storage-cap incident that motivated the size probe this table is currently invisible to.

[linear-code]

JSO-5882