[PowerSync]: $synced reflects local SQLite persistence, not backend upload status

[vitorcamachoo]

Description

When using @tanstack/powersync-db-collection, the $synced virtual property on rows becomes true as soon as the optimistic mutation is resolved against the local SQLite database (via the diff trigger in PowerSyncTransactor.applyTransaction). It does not reflect whether the row has actually been uploaded to the backend via PowerSync's uploadData connector method.

This makes $synced unsuitable for determining whether a row has pending changes that need to be sent to the server -- which is the primary use case when building offline-first UIs (e.g. showing a "pending sync" indicator, or allowing edits only on unsynced rows).

Current behaviour

1. collection.insert(row) -> row enters optimisticUpserts -> $synced = false
2. onInsert calls transactor.applyTransaction() -> writes to local SQLite
3. SQLite diff trigger fires -> TanStack DB sync handler picks up the change
4. optimisticUpserts is cleared -> $synced = true

This entire flow is local. Step 4 happens almost immediately after step 1, regardless of whether PowerSync is connected or whether uploadData has been called.

Expected behaviour

For the PowerSync integration, $synced should remain false until the row has been confirmed by the backend (i.e. the CRUD entry has been removed from ps_crud and the data has been synced back via the PowerSync sync stream).

Alternatively, a separate virtual property (e.g. $uploaded or $serverConfirmed) could indicate whether the row's mutations have been acknowledged by the server, distinct from the local optimistic resolution.

Workaround

The only reliable way to determine pending upload status is to query the ps_crud table directly:

const rows = await powerSyncDb.getAll<{ data: string }>('SELECT data FROM ps_crud');
const pendingIds = new Set<string>();
for (const row of rows) {
  const parsed = JSON.parse(row.data);
  if (parsed.type === 'my_table' && parsed.id) pendingIds.add(parsed.id);
}

This works but requires polling and is not reactive.

Context

• @tanstack/db v0.6.5
• @tanstack/powersync-db-collection
• @powersync/react-native

Related: #900 (also about $synced resolving before server confirmation with PowerSync)