This PR is an experiment to investigate dropping the optimistic state after TanStackDB collection mutations have been applied to the backend database and synced back to clients via PowerSync (and then back to the TanStackDB collection).

For context: The current integration works in an offline-first mode. Where optimistic state is resolved once mutations have reflected in the PowerSync SQLite DB.

This adds a new mode to the PowerSyncTransactor which handles TanStackDB collection mutations.

const onlineTransactor = new PowerSyncTransactor({
  database: db,
  mode: TransactorMode.ONLINE,
  timeoutMs: 30_000,
})

const confirmedTx = createTransaction({
  autoCommit: false,
  mutationFn: async ({ transaction }) => {
    await onlineTransactor.applyTransaction(transaction)
  },
})

confirmedTx.mutate(() => {
  collection.insert({ id: randomUUID(), name: `confirmed-write` })
})

await confirmedTx.commit()
await confirmedTx.isPersisted.promise
// At this point the mutation has been uploaded and synced back down.

The logic here is achieved by tapping in to the PowerSyncDatabase Write Checkpoint targeting and application state. This is currently achieved by spying on the internal ps_buckets state with temporary SQLite triggers. The checkpoint observer implementation here is LAZY and cuts many corners for the sake of experimentation. In an ideal world, this would use the requestCheckpoint logic from https://github.com/orgs/powersync-ja/discussions/324. The CheckpointObserver is also currently probably over-engineered, we might be able to use a simple watched query on ps_buckets for this functionality - the method here was eager to avoid missing any potential state changes.

A large portion of this PR was assisted by Codex GPT 5.5