SQLite persistence: a sync transaction whose durable write fails still changes the public Collection #2210

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

Validated on main at 31dad2dd3: @tanstack/db 0.15.1, @tanstack/db-sqlite-persistence-core 0.4.11, @tanstack/node-db-sqlite-persistence 0.2.36, @tanstack/query-db-collection 1.4.7, Node 24.19.0, macOS.

Describe the bug

On a SQLite-persisted collection with a sync source, a sync transaction whose durable SQLite write fails is still applied to the public Collection. The commit receipt rejects with PersistedCollectionDurabilityError and the Collection enters error, but the transaction's delete has already removed the row from collection.keys(). SQLite still stores the row. After the failure, the public Collection and durable storage disagree.

The glossary defines an accepted sync transaction as one "that core, or a wrapping sync after its durable step, has taken". A transaction whose durable step fails is not accepted, so none of its changes should become visible.

To Reproduce

A self-contained Vitest repro. A SQLite trigger makes the second transaction's delete fail inside SQLite. I ran it in the monorepo with the database from new BetterSqlite3SQLiteDriver({ filename: ':memory:' }).getDatabase(); it is shown here with better-sqlite3 directly:

import Database from 'better-sqlite3'
import { createCollection } from '@tanstack/db'
import type { SyncConfig } from '@tanstack/db'
import {
  createNodeSQLitePersistence,
  persistedCollectionOptions,
} from '@tanstack/node-db-sqlite-persistence'
import { expect, it } from 'vitest'

type Item = { id: string; name: string }
type SyncParams = Parameters<SyncConfig<Item, string>[`sync`]>[0]

it(`keeps a row public when its sync delete fails to persist`, async () => {
  const database = new Database(`:memory:`)
  let sync!: SyncParams
  const collection = createCollection(
    persistedCollectionOptions<Item, string>({
      id: `durable-delete-failure`,
      getKey: (item: Item) => item.id,
      sync: {
        sync: (params: SyncParams) => {
          sync = params
          params.begin()
          params.write({ type: `insert`, value: { id: `a`, name: `A` } })
          params.write({ type: `insert`, value: { id: `b`, name: `B` } })
          params.commit()
          params.markReady()
        },
      },
      persistence: createNodeSQLitePersistence({ database }),
    }),
  )
  await collection.preload()

  // Make the next durable delete fail inside SQLite.
  const { tbl_name: table } = database
    .prepare(
      `select tbl_name from sqlite_master where type = 'trigger' and name like '%_key_evidence_delete'`,
    )
    .get() as { tbl_name: string }
  const storedRows = () =>
    (database.prepare(`select count(*) as n from ${table}`).get() as { n: number }).n
  database.exec(
    `create trigger fail_delete before delete on ${table} begin select raise(abort, 'storage failed'); end`,
  )

  sync.begin()
  sync.write({ type: `delete`, key: `a` })
  const outcome = await Promise.resolve(sync.commit()).then(
    () => `applied`,
    (error: unknown) => (error instanceof Error ? error.message : String(error)),
  )
  await new Promise((resolve) => setTimeout(resolve, 10))

  expect({
    outcome,
    status: collection.status,
    publicRows: [...collection.keys()].sort(),
    storedRows: storedRows(),
  }).toEqual({
    outcome: `Failed to durably persist collection "durable-delete-failure": storage failed`,
    status: `error`,
    publicRows: [`a`, `b`], // actual: [`b`]
    storedRows: 2,
  })
})

Observed:

{"outcome":"Failed to durably persist collection \"durable-delete-failure\": storage failed","status":"error","publicRows":["b"],"storedRows":2}

Expected behavior

The receipt rejects and the Collection enters error, which already happens. The public Collection keeps showing the last durable state, ["a", "b"], because the delete never became durable.

Where it does and does not happen

All of these used one in-memory better-sqlite3 database and one sync transaction that deletes a:

So this is specific to the sync-present path when the real SQLite adapter fails. A fake adapter that rejects from applyCommittedTx does not reproduce it, even when the rejection is delayed, so the difference is in what the SQLite adapter path does around the failed write, not in timing. I have not found the root cause.

How it surfaces through Query DB

The same path affects @tanstack/query-db-collection with SQLite persistence (syncMode: 'on-demand', persistedGcTime: 0). Load a subset, make SQLite reject the delete, then call unloadSubset. The released row disappears from the public Collection, a peer subset's row stays, SQLite keeps both rows, and the Collection enters error. A later loadSubset rejects with the same durability error. After cleanup() and preload(), the restarted Collection serves the row from storage again, and a successful release removes it from both the Collection and storage. Query DB's own ownership oracle uses a controlled adapter, which keeps the row public, so it cannot see this. See the open cells in the stale reacquisition review record from #2206.

Additional context

• The durability error comes from toPersistedCollectionDurabilityError (via SingleProcessCoordinator.requestApplyCommittedTx or the direct applyCommittedTx path in packages/db-sqlite-persistence-core/src/persisted.ts). Both report the failure correctly; the question is why the transaction's changes have already reached core state by then on the SQLite path.
• The Collection is terminal after the failure, so the stale view lasts only until a restart. Until then, reads, live queries, and subscribers see a row as deleted that storage still holds, and a host that keeps rendering the last state shows the wrong rows.
• A fix should come with a receiving witness in the SQLite persistence oracles that fails a durable sync write through the real adapter and compares public rows with stored rows. As far as I can tell, no current oracle owns that case.