sqlite-compiler: bare "id" in trigger WHEN clauses breaks DELETE for on-demand PowerSync collections

[simonboisset]

• I've validated the bug against the latest version of DB packages (@tanstack/powersync-db-collection@0.1.40, @tanstack/db@0.6.2)

---

Describe the bug

In the @tanstack/powersync-db-collection package, the compileSQLite function in sqlite-compiler.ts generates incorrect SQL when compiling the id column reference inside trigger WHEN clauses for on-demand sync collections.

When powerSyncCollectionOptions is used with syncMode: 'on-demand', the package creates TEMP AFTER triggers on the internal ps_data__* tables to track diffs. These triggers use compileSQLite with { jsonColumn: 'OLD.data' } or { jsonColumn: 'NEW.data' } to build WHEN clauses.

For non-id columns, the output is correct: json_extract(OLD.data, '$.column_name'). But for the id column, the code skips json_extract (since id lives on the row itself, not inside the JSON data column) and falls through to quoteIdentifier(columnName), producing a bare "id" — without the required OLD. or NEW. prefix.

SQLite cannot resolve a bare column reference inside a trigger body. It needs OLD."id" or NEW."id". As a result, the TEMP diff-tracking triggers silently fail, and DELETE operations are never applied to the PowerSync CRUD queue for any on-demand collection whose stream WHERE clause references id.

The bug is completely silent — no error is thrown, no console warning. The row disappears optimistically from TanStack DB's in-memory state, then reappears after the next PowerSync sync cycle because the delete was never persisted to SQLite.

---

Root cause

In sqlite-compiler.ts, lines 97–101:

const columnName = exp.path[0]!
if (compileOptions?.jsonColumn && columnName !== `id`) {
  return `json_extract(${compileOptions.jsonColumn}, '$.${columnName}')`
}
return quoteIdentifier(columnName)

When jsonColumn is OLD.data and columnName is id, the condition columnName !== 'id' is false. The code falls through to quoteIdentifier(columnName) → "id". The OLD. prefix from jsonColumn is lost.

This is called from powersync.ts, lines 549–556 when building trigger WHEN clauses with { jsonColumn: 'NEW.data' } and { jsonColumn: 'OLD.data' }.

---

To Reproduce

1. Set up a PowerSync-backed TanStack DB collection with on-demand sync and a stream that filters on id:

const items = createCollection(
  powerSyncCollectionOptions({
    database,
    table: schema.props.items,
    syncMode: 'on-demand',
    onLoadSubset: async (options) => {
      // subscribes to a stream like "items_by_id" using id from the WHERE clause
    },
  }),
);

2. Query the collection with a WHERE id = ? filter so data is synced from the server
3. Call items.delete(someId) on a synced row
4. The row disappears optimistically from the UI
5. After the next PowerSync sync cycle, the row reappears — the DELETE was silently lost

---

Expected behavior

items.delete(someId) should persist the deletion to the local PowerSync SQLite database. The TEMP diff-tracking triggers should detect the change and propagate it through the PowerSync CRUD upload queue.

---

Why collections filtering on non-id columns are not affected

Collections whose on-demand stream WHERE clause only uses non-id columns (e.g. parent_id, user_id) are not affected — those columns correctly go through json_extract(OLD.data, '$.parent_id'). Only collections with a stream filtering on id are broken, which makes the bug particularly hard to spot since most child tables filter by a foreign key rather than id.

---

Suggested fix

Quick fix

The minimal fix is to extract the OLD/NEW prefix from jsonColumn when falling through for the id column:

const columnName = exp.path[0]!
if (compileOptions?.jsonColumn && columnName !== `id`) {
  return `json_extract(${compileOptions.jsonColumn}, '$.${columnName}')`
}
if (compileOptions?.jsonColumn) {
  const prefix = compileOptions.jsonColumn.split('.')[0]!
  return `${prefix}.${quoteIdentifier(columnName)}`
}
return quoteIdentifier(columnName)

This produces OLD."id" or NEW."id" in trigger context, and preserves the existing bare "id" when jsonColumn is not set (non-trigger context).

Limitations of this approach

1. split('.')[0] is fragile

Parsing the OLD/NEW prefix from jsonColumn implicitly couples the fix to the OLD.data / NEW.data format. A more robust solution would make the prefix explicit via a new option in CompileSQLiteOptions:

interface CompileSQLiteOptions {
  jsonColumn?: string;
  columnPrefix?: string; // "OLD" | "NEW"
}

And on the call site in powersync.ts:

compileSQLite(where, { jsonColumn: 'OLD.data', columnPrefix: 'OLD' })

This removes the implicit parsing and makes the contract explicit.

2. id is still hardcoded

The condition columnName !== 'id' is a hardcode that both the original code and this fix maintain. If PowerSync ever adds other "real" columns stored on the row itself (outside the JSON data blob), the same bug will resurface for those columns. A more future-proof approach would be to pass the set of non-JSON columns explicitly:

interface CompileSQLiteOptions {
  jsonColumn?: string;
  columnPrefix?: string;
  realColumns?: Set<string>; // columns that live on the row, not inside jsonColumn
}

Recommendation

The quick fix above resolves the immediate bug. Happy to open a PR that includes:

• The quick fix
• The columnPrefix option to remove the implicit split('.') parsing
• Tests covering id column references in trigger WHEN clauses for both OLD and NEW contexts

---

Workaround

Currently working around this with a bun patch on @tanstack/powersync-db-collection. Happy to open a PR with the fix if that's preferred.

---

Environment

OS	macOS 15
Browser	Chrome 135
@tanstack/powersync-db-collection	0.1.39 and 0.1.40 (same code on main)
@tanstack/db	0.5.33 locally (bug confirmed on main → 0.6.2)
@powersync/web	1.36.0