[umbrella] Expand transaction support: multi-repo transactions, KV/queue/text-index surfaces, and the best-effort backends

[sroussey]

Umbrella issue. Gap 1 was split out to #708 because it is gated on #702 while Gaps 2-4 are not — this issue would otherwise be unclosable until the consolidation lands. Downstream adoption: workglow-dev/sec#247, sroussey/embarc-data#34.

Context

ITabularStorage.withTransaction (packages/storage/src/tabular/ITabularStorage.ts:522-581) is the library's only transaction surface, and it is scoped to one storage instance = one table. Where we are today:

backend	withTransaction
SQLite, Postgres, DuckDB	real BEGIN/COMMIT/ROLLBACK, tx-bound proxy handle, deferred event emit
Supabase	explicit best-effort fn(this) (SupabaseTabularStorage.ts:1235) — PostgREST exposes no session transaction
IndexedDB	inherits the base default, despite IDB having native transactions
InMemory, SharedInMemory, FsFolder, HuggingFace	base default: return await fn(this) (BaseTabularStorage.ts:1098)
Cached, Telemetry, Scoped wrappers	forward to inner

Vector storage inherits the surface (IVectorStorage extends ITabularStorage). IKvStorage, IQueueStorage, and ITextIndex have none at all.

This is about expanding that surface. #702 item #2 consolidates the existing per-backend scaffolding — related, different work.

Gap 1 — a transaction cannot span repos → #708

Moved. Summary: a sibling instance touching the shared connection during an open transaction throws ConnectionReentryError (defineConnectionMutex.ts:92), and the documented refactor is "collapse to a single storage instance" — impossible for repos on different tables. Gated on #702.

Gap 2 — only the tabular surface has a transaction

Because IKvStorage has none, builder's packages/api/src/scoped/ScopedKvStorage.ts:148-165 reaches past the KV abstraction to the raw backing tabular storage (its ErasedInnerTabular alias) to run its delete-then-insert upsert atomically, and adds a hand-rolled putChain on top because the in-memory backend's withTransaction is a no-op. That is the abstraction leaking for want of one method.

• IKvStorage.withTransaction — delegation for KvViaTabularStorage (the backing tabular already has it), best-effort for InMemory/FsFolder; then revert ScopedKvStorage's reach-around and its putChain.
• IQueueStorage.withTransaction — the claim/complete/retry paths write job rows plus status plus progress.
• ITextIndex participation. reindexText had to invent beginRebuild/commitRebuild/abortRebuild (ITextIndex.ts:92-115) as a bespoke transaction analogue; fold backends with real server-side state into the general seam and keep the JSON-snapshot path only for genuinely in-memory indexes. (The cross-storage half of this — KnowledgeBase.deleteDocument at KnowledgeBase.ts:478 spanning chunk and document storage — needs Connection-scoped multi-repo transactions: let sibling storages join one BEGIN instead of throwing ConnectionReentryError #708.)

Gap 3 — the best-effort backends

• IndexedDB — highest value, because it is the browser/builder default and today it silently is not atomic while claiming a withTransaction. IDB has native transactions and the per-op paths already use them (Rewrite IndexedDbTabularStorage.putBulk as a single transaction #572 rewrote putBulk as one). A real implementation must hold one readwrite IDBTransaction across the callback, which IDB auto-commits once its request queue drains — so this needs a documented restriction (await only ops on that tx, never a foreign promise) and a loud failure mode, not silent best-effort.
• InMemory / SharedInMemory — a write journal or snapshot rollback is cheap here, and it matters more than it looks: most tests and builder's local mode run in-memory, so the paths that are "atomic in production" are exercised by a no-op in CI. embarc-data monkey-patches withTransaction in three test files specifically because rollback is unobservable in-memory (sroussey/embarc-data#34 item 3).
• FsFolder — likely stays best-effort; the ask is that it declares that rather than inheriting it silently.
• Supabase — stays best-effort (no session transaction over PostgREST). Document the stored-procedure/RPC escape hatch as the supported answer.

Gap 4 — contract coverage and capability reporting

• Backends should declare their level (e.g. "native" | "best-effort" | "none") as a queryable capability. Right now the only signal is a doc comment, so a consumer that needs atomicity cannot branch on it — which is exactly why ScopedKvStorage carries a JS-level chain "in case."
• withTransactionRollback.ts is the only assertion in the tabular contract, and supportsTransactions is just a skip flag (contract/tabular-storage/types.ts:62,83). Missing: deferred event-emit ordering (no listener sees a row before COMMIT), per-backend nested-call semantics, sibling-instance semantics, and tx-handle misuse errors. (Multi-participant coverage lives in Connection-scoped multi-repo transactions: let sibling storages join one BEGIN instead of throwing ConnectionReentryError #708.)
• TelemetryTabularStorage and CachedTabularStorage declare supportsTransactions: false in their contract wiring even though both forward to the inner storage — the wrappers' forwarding is untested against a native-tx inner.

Sequencing

Gaps 2-4 are independent of #702 and #708 and can proceed now. Gap 3's in-memory rollback unblocks embarc-data's test cleanup; Gap 2's KV surface unblocks reverting builder's ScopedKvStorage workaround.

Verification

• Extend the tabular-storage contract suite with the assertions above and run it across every backend, including the wrappers.
• bun scripts/test.ts storage vitest, plus the job-queue and knowledge-base sections.

Refs: #708 (Gap 1), #702 (scaffolding consolidation), #572 (IDB putBulk single transaction), workglow-dev/sec#247, sroussey/embarc-data#34.