powersync-db-collection: applyTransaction hangs forever when a transaction mixes delete+insert on one collection (ORDER BY timestamp tie-break)

[mattriese]

• I've validated the bug against the latest version of DB packages (the flawed readback query is present verbatim in @tanstack/powersync-db-collection@0.1.52 and in current main — PowerSyncTransactor.ts L287)

Describe the bug

When a manual TanStack DB transaction (createTransaction({ autoCommit: false, mutationFn: ({ transaction }) => transactor.applyTransaction(transaction) })) contains a delete of one key and an insert of a different key on the same PowerSync collection — the standard delete-and-recreate pattern — PowerSyncTransactor.applyTransaction frequently never resolves, so tx.commit() / tx.isPersisted.promise hang forever with no timeout. The SQLite writes themselves commit fine; only the completion signal is lost.

Originally hit in a production Capacitor/React app (@powersync/web, wa-sqlite on Chromium), where it reproduced deterministically twice in a row including a retry. Reproduced standalone on @powersync/node: 9/25 such transactions hung on 0.1.43 as published, 0/25 with the one-line fix below.

Mechanism

1. The diff (tracked) table created by @powersync/common's TriggerManagerImpl.createDiffTrigger has operation_id INTEGER PRIMARY KEY AUTOINCREMENT (true write order) and timestamp TEXT filled by strftime('%Y-%m-%dT%H:%M:%fZ','now') — millisecond precision, evaluated per statement.

2. For the last mutation per collection, handleOperationWithCompletion reads back "that mutation's diff row" with:

   SELECT id, timestamp FROM <trackedTableName> ORDER BY timestamp DESC LIMIT 1

   and builds the pending operation from two different sources: id/timestamp from the picked diff row, but operation from the mutation type.

3. After the write transaction commits, it awaits PendingOperationStore.GLOBAL.waitFor(pendingOp), and the sync flush's resolvePendingFor only resolves when all four fields match exactly (tableName, operation, id, timestamp).

4. For [delete(a), insert(b)], both trigger rows usually land in the same millisecond, so ORDER BY timestamp DESC is a tie — and SQLite's pick among tied rows is unspecified. When it returns the DELETE row (deterministic on the builds we tested: first row in scan order wins the tie, verified on SQLite 3.53.2), the registered pending op is the chimera {id: "a", operation: INSERT, timestamp: T}. The flush then emits {a, DELETE, T} and {b, INSERT, T} — neither matches — and applyTransaction never returns.

Real captured output showing exactly this (from the repro's Proof B):

iteration  7: HANG (tx.commit() unresolved after 10000ms)
  PendingOperationStore traffic for this transaction:
    waitFor registered:      {table: docs, id: a7, op: INSERT, ts: 2026-07-06T23:39:18.219Z}
    resolvePendingFor sent:  {table: docs, id: a7, op: DELETE, ts: 2026-07-06T23:39:18.219Z}
    resolvePendingFor sent:  {table: docs, id: b7, op: INSERT, ts: 2026-07-06T23:39:18.219Z}

To Reproduce

Full standalone reproduction (three independently runnable proofs, @powersync/node, no sync server): https://github.com/mattriese/powersync-db-collection-hang-repro

git clone https://github.com/mattriese/powersync-db-collection-hang-repro
cd powersync-db-collection-hang-repro
pnpm run setup
npm run proof-a   # forensic: tracked-table dump — the two trigger rows tie at ms precision,
                  # ORDER BY timestamp DESC picks the DELETE row, operation_id DESC the INSERT row
npm run proof-b   # unpatched 0.1.43: 25× [delete(a_i), insert(b_i)] → 9/25 hung (>10s, i.e. forever)
npm run proof-c   # patched build: same loop → 0/25 hung, all rows correctly persisted

Minimal failing shape (note: it must delete one key and insert a different key — same-key delete+insert trips the unrelated Unhandled mutation combination: delete-insert in @tanstack/db's mergePendingMutations first, see #1068):

const tx = createTransaction({
  autoCommit: false,
  mutationFn: ({ transaction }) => transactor.applyTransaction(transaction),
})
tx.mutate(() => {
  collection.delete("a")
  collection.insert({ id: "b", name: "row b" })
})
await tx.commit() // ← hangs forever whenever the two diff rows share a millisecond

Expected behavior

applyTransaction resolves once the transaction's mutations are flushed — regardless of how statement timestamps fall across millisecond boundaries.

Proposed fix (one line)

Order the readback by the tracked table's operation_id — the AUTOINCREMENT primary key and therefore the true write order — instead of the millisecond-precision timestamp:

-      sanitizeSQL`SELECT id, timestamp FROM ${trackedTableName} ORDER BY timestamp DESC LIMIT 1`
+      sanitizeSQL`SELECT id, timestamp FROM ${trackedTableName} ORDER BY operation_id DESC LIMIT 1`

With this, the picked row is genuinely the row produced by the awaited mutation, so all four waitFor match fields come from a single coherent row. We've been running this patch in production (via pnpm patchedDependencies) and the repro's Proof C confirms it: 0/25 hangs vs 9/25 unpatched.

Worth noting the fix is strictly more correct beyond the hang: with same-millisecond batches of same-type operations, the current readback can silently resolve against the wrong row's emission (id from a different row, but operation/timestamp coincide, so it matches and nobody notices); the hang only surfaces when the operation types differ. ORDER BY operation_id DESC removes the entire ambiguity class.

Environment

• Repro: macOS arm64, Node v22.18.0, @tanstack/powersync-db-collection@0.1.43, @tanstack/db@0.6.5, @powersync/common@1.57.0, @powersync/node@0.19.2, better-sqlite3 12.11.1 (SQLite 3.53.2)
• Original occurrence: @powersync/web (wa-sqlite) on Chromium in a Capacitor app

Additional context

Happy to open a PR with the fix if that's welcome.

[Chriztiaan]

Thank you for raising this @mattriese, we didn't apply the ORDER BY operation_id everywhere when the field became available. I have verified the issue with a unit test and can confirm your suggestion fixes the issue on our end. I have made a PR that's tagged.