Synced data does not appear in a derived (live query) collection while there is a pending optimistic mutation.

[thruflo]

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

Describe the bug

Synced data from the source collection does not appear in a derived (live query) collection while there is a pending optimistic mutation on that derived collection.

When using createOptimisticAction to insert into a createLiveQueryCollection, any new items that sync into the source collection while the mutation is pending are invisible in the derived collection. The items are processed (the pipeline runs), but they don't appear in the collection's output.

This affects all derived collections, regardless of query complexity (simple passthrough, orderBy, fn.select, multi-stage pipelines).

To Reproduce

1. Create a source collection with sync
2. Create a derived collection via createLiveQueryCollection that queries the source
3. Use createOptimisticAction to insert an item into the derived collection
4. While the mutation is pending (mutationFn hasn't resolved), sync a NEW item into the source collection
5. Observe that the synced item is invisible in the derived collection

Minimal Reproduction Code

import {
 createCollection,
 createLiveQueryCollection,
 createOptimisticAction,
} from '@tanstack/db'

interface Item {
 id: string
 value: string
}

// 1. Create source collection with controllable sync
let begin, write, commit, markReady
const source = createCollection<Item>({
 id: 'source',
 getKey: (item) => item.id,
 startSync: true,
 sync: {
 sync: (params) => {
 begin = params.begin
 write = params.write
 commit = params.commit
 markReady = params.markReady
 },
 },
})

// 2. Create derived collection (simple passthrough)
const derived = createLiveQueryCollection<Item>({
 query: (q) => q.from({ item: source }),
 getKey: (item) => item.id,
 startSync: true,
})

// Mark source ready
markReady()

// 3. Create optimistic action
let resolveOptimistic: () => void
const optimisticInsert = createOptimisticAction<Item>({
 onMutate: (item) => {
 derived.insert(item)
 },
 mutationFn: async () => {
 // Keep mutation pending until we explicitly resolve
 await new Promise<void>((resolve) => {
 resolveOptimistic = resolve
 })
 },
})

// 4. Insert optimistic item (mutation will be PENDING)
optimisticInsert({ id: 'optimistic-1', value: 'optimistic' })

console.log('After optimistic insert:')
console.log(' derived.size:', derived.size) // 1 ✓
console.log(' has optimistic-1:', derived.has('optimistic-1')) // true ✓

// 5. Sync a NEW item into source (while optimistic mutation is pending)
begin()
write({ type: 'insert', value: { id: 'synced-1', value: 'synced' } })
commit()

console.log('After sync (mutation still pending):')
console.log(' derived.size:', derived.size) // Expected: 2, Actual: 1 ✗
console.log(' has optimistic-1:', derived.has('optimistic-1')) // true
console.log(' has synced-1:', derived.has('synced-1')) // Expected: true, Actual: false ✗

// BUG: synced-1 is INVISIBLE while optimistic mutation is pending!

// 6. Resolve the optimistic mutation
resolveOptimistic()

console.log('After mutation resolved:')
console.log(' derived.size:', derived.size) // 1 (optimistic dropped, synced appears)
console.log(' has synced-1:', derived.has('synced-1')) // true (finally visible)

Test Output

After optimistic insert:
 derived.size: 1
 has optimistic-1: true
After sync (mutation still pending):
 derived.size: 1 ← BUG: should be 2
 has optimistic-1: true
 has synced-1: false ← BUG: should be true
After mutation resolved:
 derived.size: 1
 has synced-1: true

Expected behavior

When new items sync into the source collection, they should appear in the derived collection immediately, regardless of whether there are pending optimistic mutations.

Expected output:

After sync (mutation still pending):
 derived.size: 2 ← Both items visible
 has optimistic-1: true
 has synced-1: true ← Synced item should be visible

Additional Testing

The bug occurs in all derived collection configurations:

Configuration	Bug Present
Simple passthrough (q.from({ item: source }))	Yes
With orderBy	Yes
With fn.select	Yes
Multi-stage pipeline (derived → derived)	Yes
Streaming (items arrive one at a time)	Yes

Impact

This bug causes data loss in applications that use optimistic updates with live query collections. In our case, it causes chat messages to intermittently disappear:

1. User sends message → optimistic insert (mutation pending, waiting for server sync)
2. Server syncs user message back
3. Assistant starts responding → synced into source collection
4. BUG: Assistant message is invisible because user's optimistic mutation is still pending
5. User sees only their message, assistant response is missing

The bug is intermittent because it only manifests when synced data arrives before the optimistic mutation resolves.

Environment

• OS: macOS (Darwin 24.6.0)
• Node: v22.x
• @tanstack/db: (linked local version from TanStack/db repo)
• Browser: N/A (Node.js tests)

[kevin-dp]

@KyleAMathews Could this be the same/similar bug as the one you solved in #1130 ?

[kevin-dp]

/reproduce

[claude]

Analysis and Fix

I've analyzed and fixed this issue. Here's a summary:

Root Cause

When a sync transaction commits while there's a persisting user transaction (optimistic mutation), commitPendingTransactions() in packages/db/src/collection/state.ts was designed to delay processing the sync transaction to avoid complex reconciliation. However, this also prevented change events from being emitted to subscribers, making the synced data invisible to live query collections that depend on those events.

The live query collection subscribes to change events from the source collection via subscribeChanges(). When no events are emitted, the D2 pipeline doesn't process the new data, and it remains invisible.

The Fix

Added an else branch to commitPendingTransactions() that emits change events for committed sync operations without modifying syncedData. This approach:

1. Preserves existing behavior: The source collection's state (syncedData) remains unchanged during the persisting transaction
2. Enables live query visibility: Subscribers receive events about the new synced data immediately
3. Avoids conflicts: Skips emitting events for keys that have pending optimistic mutations

Pull Requests

• Reproduction Test: [repro] synced data not appearing in derived collection during pending optimistic mutation (issue #1017) #1166 - Adds a test that reproduces the bug
• Fix: [fix] synced data not appearing in derived collection during pending optimistic mutation (issue #1017) #1167 - Implements the fix, stacked on the repro PR

Both PRs include tests that verify the expected behavior: synced data should appear in derived collections immediately, regardless of pending optimistic mutations.