Refactor outbox drainer to lease-based three-phase pattern (#477 follow-up)

Summary

The current OutboxDrainer::drain_once at crates/canopy-mq/src/outbox_drainer.rs:101 holds one Postgres transaction across N RabbitMQ publishes per batch. Under workspace integration concurrency this serialises foreground COMMITs behind WAL fsync, producing multi-second tail latency (~6.7s commits captured via wait-event instrumentation; LWLock:WALWrite + IO:WalSync waits on foreground COMMITs while drainer txes hold idle in transaction on UPDATE event_outbox SET published_at = now() WHERE id = $1).

Architectural rule violated: broker I/O inside a DB transaction. This issue tracks the refactor to fix it.

Plan

Detailed design + step-by-step in xref:plans/outbox-drainer-lease-refactor.adoc[docs/modules/ROOT/pages/plans/outbox-drainer-lease-refactor.adoc].

Three-phase lease-based design:

  1. Claim (one short DB tx): single CTE UPDATE … FROM (SELECT … FOR UPDATE SKIP LOCKED) RETURNING … sets claimed_at/claimed_by.
  2. Publish (no DB tx): channel-per-batch with confirm_select, bounded pipelining, no mandatory=true, defensive handling of Confirmation::Ack(Some(BasicReturnMessage)).
  3. Mark (two short DB txes): bulk UPDATE for successes; bulk UPDATE for failures (clear claim + bump attempts + record last_error). Both guarded by claimed_by = $drainer_id.

Lease recovery is via the claim path's claimed_at < now() - lease_ttl predicate, not the hourly janitor. Crashes mid-batch are recovered on the next drain tick with attempts intact.

Scope

  • 19 services × new migration (event_outbox columns + lease-aware partial index)
  • crates/canopy-mq/src/outbox_drainer.rs rewrite + 4 in-source unit tests
  • ADR-018 amendment, CHANGELOG, shared-crates.md updates
  • 3 new env vars: CANOPY_MQ_DRAINER_BATCH_SIZE, CANOPY_MQ_DRAINER_LEASE_TTL_SECS, CANOPY_MQ_DRAINER_PIPELINE_DEPTH

Quantitative success criteria

  • canopy-rules persist_ms p99 under workspace load: from observed ≥6.7s peak → < 50ms
  • idle in transaction rows on UPDATE event_outbox …: from bursts → 0
  • Workspace integration suite: from intermittent 7-12 failures → 1725/1725 passing

Sequencing

This issue depends on the publish_tx migration MR (branch chore/centralize-sqlx-migrate-bootstrap) merging first. See the plan's "Sequencing" section for decision points.

Origin

Diagnosis trail: #477 (closed) (publish_tx migration MR) → wait_event poller captured the held-tx-during-publish pattern → architectural review approved this lease-based refactor.