Plan: Event Outbox/Inbox Retention Sweep

On this page

Status

Step Description Status

1

Plan file + nav entry (this file); resolve the Open Decisions below at review

Done (2026-07-26) — D-A/D-B/D-C resolved in #1129 A1 (user classification; notes 3603012745/3603012780)

2

Typed retention settings (window + disable) threaded through spawn_outbox_worker

Done (2026-07-26) — #1129 A2

3

OutboxWorker::prune_expired — batched, advisory-locked, published-only outbox + inbox, anomaly-capped

Done (2026-07-26) — #1129 A2

4

Worker-loop integration (drain-before-prune, hourly cadence, jittered) + prune metric

Done (2026-07-26) — #1129 A2

5

Docs: ADR-022 §Retention/§D3.1 correction (inbox ≥ outbox; enforced) + supersede note for migration comments

Done (2026-07-26) — ADR-058 + ADR-022 correction + config-reference + CHANGELOG (#1129 A5)

6

Devstack-gated integration test: published-only, batching, window asymmetry, ordering

Done (2026-07-26) — scratch-DB suites in craig-mq + exchange + financial (#1129 A2-A4)

7

CHANGELOG, commit, push, MR

Done (2026-07-26) — MR !1062 merged (a3e8a459); #965 closed with the AC walk

Issue: #965 (MR-A of the #1129 retention program — Closes #965 + Relates to #1129)
Program plan: Retention Enforcement (#1129) — carries the program decisions (D1–D20), the archive-then-prune half, the watchdog invariants, and the review-disposition index; THIS plan remains the authoritative spec for the sweep mechanics below
Branch: feature/1129-retention-sweeps
Related: #777 (inbox concurrency); #491 / Plan E (Epic &35 — the OutboxWorker graceful-shutdown loop this extends, outbox.rs:94); #811 (recent worker-cancellation work in the same family — the scanner/idempotency workers, a useful decomposition precedent)
Follow-ups to file: outbox dead-letter / retry-cap (the publish-failure branch has none — see D-B); a created_at partial index migration (see Deferred, below) if prune latency shows up

This plan was iterated after a contextless adversarial design review that found the first draft unsafe in three ways (deleting undelivered events, an unbounded first delete, and a §D3.1 boundary break). The design below is the corrected result; the review’s findings are folded into the Decisions and Design so the plan↔intent diff is zero.

Context

event_outbox and event_inbox carry a documented retention guarantee that no code enforces — there is no prune loop or scheduled DELETE anywhere (the only DELETE FROM event_outbox hits are test-only; there is no DELETE FROM event_inbox at all). The guarantee is asserted in ADR-022 §Retention ("`event_outbox`: 30 days for both published_at IS NOT NULL and NULL rows"; "`event_inbox`: 30 days, matching outbox"), in every service’s …​_event_outbox.sql / …​_event_inbox.sql migration comment, and in crates/craig-mq/src/inbox.rs.

Why it matters: (1) unbounded growth of the two hottest tables in each service DB (index bloat
autovacuum pressure); (2) a doc-vs-reality gap (J5) — a guarantee the code never provides.

Open Decisions (resolve at plan review)

# Decision

D-A: Legal hold / statutory retention — RESOLVED (user-directed 2026-07-26)

The classification was confirmed by the user as part of the #1129 retention program (recorded on #965 note 3603012745 and #1129 note 3603012780): event_outbox/event_inbox (and the other transport tables) are operational delivery/dedup artifacts, NOT records of the system of truth — the domain facts live in the domain tables + the audit trail. The 30-day hard-delete window below is therefore approved, with this plan’s published-only + asymmetric-window posture. The audit-class tables (audit_log, dead_letter_audit, rule_evaluations) are explicitly OUTSIDE this classification and get archive-then-prune instead — see the #1129 program plan, which absorbs this plan as its MR-A spec.

D-B: Published-only, not "both" (RECOMMENDED: published-only)

The docs say "30 days for both published and pending." Deleting pending (never-published) outbox rows is unsafe: the outbox publish-failure branch (outbox.rs:173-190) has no retry cap and no dead-letter path (asymmetric with the inbox’s INBOX_MAX_RETRIES + DLX), so a persistently-failing envelope (oversized frame, routing/permission error) or a worker outage leaves rows pending — and the pending row + last_error is the only evidence a cross-service notification about a case/placement/ safety event failed to propagate. Deleting it silently loses the event and erases the failure record. §D3.1 does not require deleting pending rows (they were never published, own no inbox dedup record). So sweep published rows only: WHERE published_at IS NOT NULL AND created_at < now() - <outbox_window>. The migration comments' "both" then becomes inaccurate; they are immutable (sqlx checksums file bytes → editing breaks sqlx migrate), so ADR-022 is made the authoritative spec and gains a note that the migration comments predate the sweep and are superseded (Step 5). CAVEAT: published_at IS NOT NULL ≠ "delivered" — drain_once force-stamps malformed envelopes published_at = now() (outbox.rs:192-208), so even published-only deletes those never-delivered rows at the window; acceptable (they are logged with last_error when stamped), and a proper outbox dead-letter surface is a filed follow-up.

Deferred (engineering call, not a user decision): idx_event_outbox_pending is partial (WHERE published_at IS NULL), so deleting published rows by created_at has no supporting index → a seq scan each cycle. Once retention is enforced the table is bounded (~30d) and the delete is batched
hourly, so a seq scan is acceptable initially. Adding an index is a new migration (append-only — allowed, unlike editing one) ×8 services; defer to a filed follow-up, add only if prune latency shows up.

The §D3.1 dedup-horizon invariant (corrected)

ADR-022 says outbox/inbox windows must "match"/"move together." The precise, safe invariant is inbox lifetime ≥ outbox lifetime + (max prune cadence + clock skew), not equality. Timeline: an event published at t0 has an outbox row eligible for delete at t0 + outbox_window and physically gone by up to one prune cadence later; its inbox dedup record (received at ~t0) must still exist while that outbox row is replayable (D4 admin replay re-nulls published_at and re-publishes — with no dedup record the consumer re-executes side effects). Equal 30d/30d fails at the boundary because the two tables are pruned asynchronously by two different service DBs. Fix: outbox window 30 days, inbox window 31 days (inbox strictly longer than outbox_window + hourly cadence + skew). Each table is pruned by its own DB’s clock (the clock that stamped its timestamp), and a consumer’s received_at is always ≥ the producer’s created_at (you cannot receive before staging); the 1-day inbox margin then dominates both the hourly prune cadence and any NTP skew between service DBs, so the inbox dedup record always outlives the replayable outbox row. ADR-022’s "match"/"equal" wording is corrected to "inbox ≥ outbox" in Step 5.

Design

Program extensions (#1129 A3–A5, specified in the program plan): the batch-delete helper (prune_table) is exposed from craig-mq for two service-local terminal-state sweeps (exchange exchange_send_jobs, financial’s settled subsidy_reconcile_queue rows), the admin-replay range is mechanically clamped to the outbox window, four report-only watchdog invariants land with red-harness coverage, and ADR-058 (both retention classes) ships with this MR.

Retention settings (Step 2) — no hardcoded policy, with a kill-switch

A retention window is operational/legal policy, so it is a typed setting, not a const (coding-conventions: "validate required fields at startup, never silently default"; ops/legal may need to lengthen it or disable the sweep across all 8 services without a redeploy). Add to craig_common::settings::ServiceSettings:

  • event_outbox_retention_days: u32 (default 30; 0 disables the outbox sweep)

  • event_inbox_retention_days: u32 (default 31; 0 disables the inbox sweep)

env: CRAIG_<SVC>__EVENT_OUTBOX_RETENTION_DAYS / …​_EVENT_INBOX_RETENTION_DAYS. Validation home: ServiceSettings::load() (settings.rs:323) today does only try_deserialize — add the check inside load() after deserialize (there is no separate validate() hook; putting it in load() keeps it centralized, run once per service via bootstrap, so the 8 mains carry zero validation logic): if both windows are non-zero, require inbox ≥ outbox + 1 (the §D3.1 margin) and return a config::ConfigError::Message naming the offending values otherwise. Thread both windows into spawn_outbox_worker(db, publisher, name, shutdown, retention) → OutboxWorker fields.

OutboxWorker::prune_expired (Step 3)

/// Max rows deleted per statement — bounds lock/WAL and lets the drain
/// loop interleave. The first prune after enabling retention can face a
/// large never-pruned backlog; batching keeps each delete cheap.
const PRUNE_BATCH: i64 = 5_000;
/// Anomaly cap: if a single cycle would delete more than this, stop and
/// warn rather than trust a possibly-jumped clock (NTP glitch / VM
/// migration → `now() - 30d` could match ~everything). Bounds blast radius.
const PRUNE_CYCLE_CAP: u64 = 1_000_000;

prune_expired(&self): 1. Single-pruner guard — a DETACHED connection whose drop closes the socket. pg_try_advisory_lock is session-scoped, so it is a latent bug to call it "on the pool": the lock is taken on one checked-out connection, that connection returns to the pool, and the subsequent batched DELETE`s
any unlock run on different pooled connections — the lock sticks on an idle connection, and after the first cycle every replica’s try-lock returns false forever → retention silently self-disables. An async `pg_advisory_unlock cannot run inside Drop, so the as-built mechanism (matching the subsidy-import-finalize detached-lease precedent, finalize.rs:48): let mut conn = pool.acquire().await?.detach(); → SELECT pg_try_advisory_lock($KEY) on conn; false → close the connection and skip this cycle (another replica holds it — avoids B blocking on A’s row locks); true → run every batch DELETE on that same detached conn, and simply DROP it at the end — the socket closes, the session dies, and Postgres releases the lock on every exit path (early return, ?, panic). $KEY is a fixed i64 constant (each service has its own DB, so a constant key fences only that service’s own replicas — it is not derived from service_name). pg_advisory_xact_lock is deliberately NOT used (the deletes are intentionally not one tx). (The stale bootstrap/lib.rs comment claiming service_name is "used for advisory locking" is corrected in the same commit.) 2. Outbox first, then inbox — a load-bearing order (pin with a comment): a partial failure then leaves (outbox pruned, inbox kept) = the §D3.1-safe direction (dedup record outlives the deleted outbox row). Never reorder; do not wrap both in one tx (one tx doubles the lock/WAL window). 3. Each table: loop DELETE …​ WHERE <ts> < now() - ($window || ' days')::interval …​ LIMIT PRUNE_BATCH (via ctid IN (SELECT ctid …​ LIMIT n) or id IN (…​)), summing rows_affected, tokio::task::yield_now().await between batches, stopping at < PRUNE_BATCH or when the running total exceeds PRUNE_CYCLE_CAP (then warn! an anomaly and stop — do not delete more). - outbox predicate: published_at IS NOT NULL AND created_at < … (Decision D-B, published-only). - inbox predicate: received_at < … (all aged; inbox window = 31d). 4. Return (outbox_deleted, inbox_deleted); record the prune metric (below).

Window value comes from settings (skip the table’s sweep when its *_days == 0). The interval is built from a validated u32, never user input.

Worker-loop integration (Step 4)

Extend the post-#811 spawn loop. Drain before prune each tick (a recovered worker must publish its backlog before anything is deleted — with published-only this is belt-and-suspenders, but the ordering is still correct). Gate the prune to hourly with startup jitter so 8 services × 2 replicas don’t all fire at tick=0:

// jitter: 0..PRUNE_EVERY_TICKS, seeded off service_name hash (deterministic,
// no Math.random) so replicas of one service still align but services stagger.
let mut tick: u64 = prune_jitter(&self.service_name);
loop {
    select! { biased; cancelled => break; sleep(POLL_INTERVAL) => {} }
    if let Err(e) = self.drain_once().await { warn!(...) }      // drain FIRST
    if tick % PRUNE_EVERY_TICKS == 0 {
        match self.prune_expired().await {
            Ok((o,i)) if o>0||i>0 => { info!(...); metrics::event_rows_pruned(o,i); }
            Ok(_) => {}
            Err(e) => warn!(error=%e, "event retention prune failed"),
        }
    }
    tick = tick.wrapping_add(1);   // cadence only; wrap benign (J7)
}

PRUNE_EVERY_TICKS = 3600 (hourly at the 1s poll). Metric: a event_rows_pruned_total{table} counter (mirror the jws_replay_rows_deleted_total pattern in craig_common::metrics) so an anomalous prune volume is observable/alertable — deleting audit-relevant rows silently is not acceptable.

Zero per-service / per-migration wiring (beyond settings)

prune_expired lives on OutboxWorker, spawned by every stateful service through the single shared spawn_outbox_worker — so activation is automatic on all 8 services once Steps 2–4 land, with no per-service main.rs logic (only the settings plumbing) and no migration edits (Decision D-B keeps migrations untouched; the created_at index, if adopted, is new migrations per Decision D-C).

Docs (Step 5)

  • ADR-022: §Retention — outbox 30d published-only (pending retained as delivery-failure evidence), inbox 31d; correct "match/equal" to inbox ≥ outbox + cadence and state the enforcement (batched hourly prune in OutboxWorker, advisory-locked, configurable/disable-able). §Status — record #965. Add a note that the …​_event_outbox.sql "30 days for both" comments predate the sweep and are superseded by this section (they are immutable / sqlx-checksummed).

  • outbox.rs / inbox.rs module docs: the prune, the windows, the published-only + inbox≥outbox rationale, the outbox-before-inbox ordering invariant.

  • CHANGELOG: == Unreleased fix(mq): entry.

Test plan (Step 6)

Devstack-gated integration tests (mirror crates/craig-mq/tests/inbox_retry.rs / outbox_concurrent.rs: #[ignore = "requires devstack"] #[tokio::test], throwaway DB, inline schema — omit the published_at >= created_at CHECK as outbox_concurrent.rs does, or satisfy it). Explicit past timestamps are insertable (an INSERT overrides a DEFAULT now()).

  • published-only — aged published outbox row (created_at = now()-31d, published_at set) → deleted; aged pending row (published_at IS NULL, created_at = now()-31d) → kept (the D-B contract); fresh rows → kept.

  • inbox window — inbox row at received_at = now()-32d → deleted; at now()-30d → kept (proves the 31d window, distinct from outbox’s 30d).

  • batching — insert > PRUNE_BATCH aged published rows; assert all deleted across batches and the returned count is exact.

  • ordering resilience (unit-level reasoning) — assert prune_expired deletes outbox before inbox (documented invariant); a comment pins why.

  • cadence const — PRUNE_EVERY_TICKS as u32 * POLL_INTERVAL == 1 hour (Duration impls Mul<u32> only → cast).

  • disable — *_days == 0 → the corresponding table is not touched.

Quality-budget guardrails

B3a (Value, 148) / B4 (#[allow], 80): none added. Keep prune_expired cohesive (extract a prune_table helper for the batch loop so the method stays one-clause and under 40 lines — the batch loop + advisory lock + two tables will exceed the budget inline). spawn stays thin (extract log_prune if needed, as #811 did). J7: tick.wrapping_add (cadence-only) with justifying comment; batch/window arithmetic uses u32/i64 with checked_* or documented bounds.

Rollout

MR-A of the #1129 program: branch feature/1129-retention-sweeps, Closes #965
Relates to #1129 — settings + craig-mq (method + loop + metric + test) + spawn_outbox_worker signature + 8 service main.rs settings-plumbing lines + the A3–A5 program extensions (service-local sweeps, replay clamp, watchdog invariants, ADR-058) + ADR-022 correction + module docs + CHANGELOG. Safe-tranche (retention/cleanup; no auth surface); Decision D-A is RESOLVED (2026-07-26, recorded on both issues). Implement → full battery → present → merge under standing per-MR discipline.

Post-merge

Close #965 with impl + merge SHAs + checked criteria; file the follow-ups (outbox DLL/retry-cap; created_at index if D-C deferred); move this plan Active → Archive, Status → Done. P3-low fix ⇒ no .claude/CLAUDE.md status-table entry.

Edit this page · latest