[PowerSync] collection.update persists the whole in-memory row, reverting changes the collection has not seen yet (lost update)

[AliNaqiAnsari]

• I've validated the bug against the latest version of DB packages

@tanstack/powersync-db-collection 0.1.67 (latest), @tanstack/db 0.9.0, @powersync/common 1.57.3, @powersync/node 0.19.5. The same handleUpdate code is on main.

Describe the bug

PowerSyncTransactor.handleUpdate persists mutation.modified — the whole row as the collection holds it in memory — with UPDATE <table> SET <every column> = ? WHERE id = ?. PowerSync then records a PATCH containing every column whose value differs from local SQLite.

The collection learns about SQLite changes through a diff trigger whose listener is throttled (DEFAULT_WATCH_THROTTLE_MS, 30 ms). For that window after any write it has not flushed yet, SQLite is newer than the collection's memory. An update made inside the window writes the stale in-memory values of fields the user never touched back to SQLite, and they are uploaded as if the user had changed them.

In a synced app the earlier write is usually another user's change arriving through sync, so the result is a silent lost update on the backend. In our tests a user changing a row's status reverted a colleague's newer assignee in 20 of 20 attempts, timed to land inside the window; the same change made with db.execute lost nothing. We first noticed it as unexpected columns in uploads from @powersync/web 1.39.1.

To Reproduce

No PowerSync service needed — a local @powersync/node database:

import { mkdtempSync } from 'node:fs'
import { tmpdir } from 'node:os'
import { join } from 'node:path'
import { PowerSyncDatabase, Schema, Table, column } from '@powersync/node'
import { createCollection } from '@tanstack/db'
import { powerSyncCollectionOptions } from '@tanstack/powersync-db-collection'

const AppSchema = new Schema({
  todos: new Table({ title: column.text, assignee: column.text, done: column.integer }),
})

const db = new PowerSyncDatabase({
  schema: AppSchema,
  database: { dbFilename: 'repro.sqlite', dbLocation: mkdtempSync(join(tmpdir(), 'ps-lost-update-')) },
})
await db.init()
await db.execute(`INSERT INTO todos (id, title, assignee, done) VALUES ('t1', 'Write report', NULL, 0)`)

const todos = createCollection(powerSyncCollectionOptions({ database: db, table: AppSchema.props.todos }))
await todos.preload()

// 1. Another writer sets `assignee` (in an app: a synced change from another user).
await db.execute(`UPDATE todos SET assignee = 'alice' WHERE id = 't1'`)
console.log('collection memory right after that write:', { assignee: todos.get('t1')?.assignee })

// 2. Before the collection has flushed that change into memory, the user changes a DIFFERENT field.
const tx = todos.update('t1', (draft) => {
  draft.done = 1
})
await tx.isPersisted.promise
await new Promise((r) => setTimeout(r, 200))

console.log('row in SQLite:', await db.get(`SELECT assignee, done FROM todos WHERE id = 't1'`))
console.log(
  'queued uploads:',
  JSON.stringify((await db.getAll(`SELECT data FROM ps_crud ORDER BY id`)).map((c: any) => JSON.parse(c.data))),
)

Output:

collection memory right after that write: { assignee: null }
row in SQLite: { assignee: null, done: 1 }
queued uploads: [{"op":"PUT","id":"t1","type":"todos","data":{"done":0,"title":"Write report"}},{"op":"PATCH","id":"t1","type":"todos","data":{"assignee":"alice"}},{"op":"PATCH","id":"t1","type":"todos","data":{"assignee":null,"done":1}}]

The last PATCH uploads assignee: null, although the update only changed done, and assignee is back to null locally.

Expected behavior

An update persists only the fields the mutation changed (mutation.changes): the PATCH is {"done":1} and assignee stays "alice".

Additional context

Overriding handleUpdate to persist mutation.changes fixed it in our tests: 0 lost updates in the same 20 timed trials, with the change still visible optimistically and isPersisted resolving as before.

class ChangesOnlyTransactor extends PowerSyncTransactor {
  protected override async handleUpdate(mutation, context, waitForCompletion = false) {
    return this.handleOperationWithCompletion(mutation, context, waitForCompletion, async (tableName, mutation, serializeValue) => {
      const values = serializeValue(mutation.changes)
      const keys = Object.keys(values).filter((key) => key !== 'id')
      if (keys.length === 0) return
      await context.execute(
        `UPDATE ${tableName} SET ${keys.map((key) => `${sanitizeSQL`${key}`} = ?`).join(', ')} WHERE id = ?`,
        [...keys.map((key) => values[key]), asPowerSyncRecord(mutation.original).id],
      )
    })
  }
}

One thing we have not verified with that approach: when changes is empty nothing is written, and we don't know whether the pending-operation tracking still resolves in that case.

Environment: Node 22.22.1 on macOS 26.6 (arm64).