Connection-scoped multi-repo transactions: let sibling storages join one BEGIN instead of throwing ConnectionReentryError

[sroussey]

Split out of #707 (Gap 1), which stays as the umbrella. This half is separated because it is gated on #702 while the rest of #707 is not — lumping them means the umbrella cannot close until the consolidation lands.

The gap

withTransaction is scoped to one storage instance = one table. A sibling instance that touches the same connection handle while another holds a transaction throws ConnectionReentryError with mode: "sibling-op" (packages/storage/src/tabular/defineConnectionMutex.ts:92), and the documented refactor (c) is "collapse to a single storage instance, or give each instance its own connection." Repos on different tables can do neither.

So on SQLite/Postgres/DuckDB — where the database supports it exactly — there is no way to express "these rows across these N tables commit together."

The machinery is already most of the way there: defineConnectionMutex keys HandleState on the shared connection handle via WeakMap, so every instance bound to one handle already chains through a single promise. What's missing is a set of transaction owners rather than a single txOwner, and a tx-bound view per participant.

Proposed shape

A connection-scoped unit of work, e.g. withConnectionTransaction([...storages], fn):

• Open one BEGIN on the shared handle; reject participants that don't resolve to the same handle with a clear error (mixing two databases in one transaction is a caller bug, not something to paper over).
• Hand fn a tx-bound proxy per participant — the createTxView trick already in the SQLite/Postgres/DuckDB storages, generalized from 1 to N.
• Widen HandleState.txOwner: object | null to an owner set so classifyReentry inlines every participant instead of throwing, while a non-participant still throws exactly as it does now. Non-participants throwing is a feature — it's what catches "you forgot to enlist this repo."
• Flush every participant's deferred event queue after COMMIT; discard all of them on rollback. Ordering across participants needs a decision: enlistment order is the obvious one.
• Keep the current single-storage withTransaction as the one-participant case, unchanged for callers.
• Best-effort backends run fn with the untouched participants (today's semantics), and say so through the capability from [umbrella] Expand transaction support: multi-repo transactions, KV/queue/text-index surfaces, and the best-effort backends #707 Gap 4 rather than silently.

Constraints worth pinning before coding

• The ALS classification's known limit (a descendant resumed by an outside scheduler arrives with no store, is classified chain, and deadlocks on a slot only its own transaction can release) gets worse with N participants — more repos in the callback means more chances someone hands work to a job queue. Whatever lands should fail loudly here rather than hang.
• SQLite takes mutex-then-chain, DuckDB takes chain-then-mutex; Stage 6 of simplification audit: storage base-class consolidation (~1,100 LOC, audit storage §2–7, 10–11) — gated on #701 #702 notes chain→mutex is the verified deadlock-free order. Multi-participant enlistment must not reintroduce that divergence.
• ConnectionMutex.test.ts pins the inline/queue/throw semantics — the widened classification has to keep every existing case behaving identically.

Why it's gated on #702

#702 item #2 consolidates the copied mutex + _fooInternal + createTxView + runOnConnection scaffolding into BaseSqlTabularStorage behind serializeOps() / createTxView(deferred, extras). The multi-participant view is precisely what that hook wants. Building it first means writing it three times (sqlite/postgres/duckdb) and then merging three divergent versions during the consolidation.

Downstream consumers waiting on this

Tracked in workglow-dev/sec#247 and sroussey/embarc-data#34.

• sec (Adopt multi-repo transactions: atomic observation writes, atomic SPAC history, and retire the two hand-rolled raw-SQL transaction paths sec#247) — EntityObserver.observePerson writes across 5 repos with no boundary; SpacReportWriter.recordHistory closes a history row, opens the next, and writes N change-log rows across two repos, where a crash mid-way leaves a SPAC with no open history row. sec also hand-rolled two dual-dialect raw-SQL transaction paths (Form8KEventReplace.ts, SpacDealReplace.ts) to get atomicity the storage layer couldn't give it; both collapse into ordinary repo calls once this lands.
• embarc-data (sroussey/embarc-data#34) — approveAttribution mints a vehicle, saves the approved row, then demotes competing rows in a loop, unguarded, against an invariant its own doc states ("a Form-D can never end up approved against two rounds").
• knowledge-base — KnowledgeBase.deleteDocument spans the chunk vector storage and the document tabular storage.

sec#247's recomputeSpacDeals case is the natural first consumer: it is already called from inside SpacReportWriter, so it has to become an enlisted participant of a caller's transaction rather than opening its own — a good shape to validate the API against.

Verification

Extend the tabular contract with multi-participant commit and rollback across two and three storages on one handle, non-participant still throwing, event flush ordering, and the mixed-handle rejection. Run across every backend. bun scripts/test.ts storage vitest.

Refs: #707 (umbrella), #702 (gate), workglow-dev/sec#247, sroussey/embarc-data#34.