Cross-object atomic batch write (master-detail parent+children in one transaction)

[xuyushun441-sys]

Follow-up from master-detail (ObjectUI ADR-0001). Children-create is already atomic via createMany / /data/:object/batch (defaultAtomic). Missing: a cross-object transactional write (parent object A + children object B in one transaction). Proposal: POST /api/v1/data/batch accepting { operations:[{object,action,data}], atomic:true }, wrapping ops in engine.transaction(); masterDetailTx uses it when present, else falls back to client orchestration. Touches rest-server.ts + protocol (hot files) — dedicated reviewed change.

[xuyushun441-sys]

Update — implemented & joint-tested; uncovered a blocking engine-level deadlock

I implemented the cross-object transactional batch endpoint (POST /api/v1/data/batch style) in rest-server.ts using the existing engine: resolve objectQLProvider → ql.transaction(cb, execCtx) → loop ql.insert/update/delete(object, data, { context: trxCtx }), with intra-batch { $ref: <opIndex> } resolution so children can reference a parent created earlier in the same transaction (master-detail). No protocol-layer changes needed.

What worked (joint browser tests against app-showcase)

• ✅ Single-op create returns the created record.
• ✅ A batch where a later op fails (validation) rolls back — the earlier create does not persist (true atomicity).

What's blocked — multiple successful writes deadlock

• ❌ A batch with two successful writes + commit (e.g. project + task, or even two no-FK showcase_category creates) hangs. The transaction holds SQLite's single connection; an internal query during the 2nd write (validation / hook / FK check) is NOT threaded with the open transaction (.transacting(trx)), so it waits on the held connection → deadlock.
• ⚠️ Worse, the hung transaction leaves the connection wedged, so subsequent writes (even single-op) also hang until restart.

engine.ts already threads the transaction into the driver via buildDriverOptions for the top-level write, and even warns about this deadlock (around L1811), but the internal reads performed during a write don't all reuse the transaction connection.

Conclusion

The endpoint design is correct and the front-end (masterDetailTx) is ready to use it, but it's blocked by an engine-level limitation: multi-write transactions deadlock on the single-connection driver. I reverted the route (shipping an endpoint that can wedge the backend is unacceptable).

Real fix needed (dedicated engine work): thread the open transaction through all internal queries performed during a write (validation predicates, hook api calls, FK/uniqueness checks), or give transactions their own connection from the pool. Once multi-write transactions are robust, this endpoint + the front-end wiring can land as-is.