[PowerSync] Support integer primary keys: the collection key must be a string id

[Jaime02]

@tanstack/powersync-db-collection requires the collection key to be the string id column. A backend with integer primary keys (a Postgres identity or serial column) therefore has to use string keys in TanStack DB. Its integer foreign keys then no longer match: eq(part.id, line.partId) compares "5" with 5.

The obvious fix is to convert id back to a number in deserializationSchema. The adapter rejects that:

Error: Record must have a string id field
    at asPowerSyncRecord (helpers.js)
    at Object.getKey (powersync.js)

Reproduction

@tanstack/db and @tanstack/powersync-db-collection 1.0.0-rc.1.4, @powersync/node 0.21.0, @powersync/common 2.5.0, Node 24. The same code is on main (873a560).

const APP_SCHEMA = new Schema({ products: new Table({ name: column.text, category: column.text }) })
// db = new PowerSyncDatabase({ schema: APP_SCHEMA, database: { implementation: { type: 'node:sqlite' }, ... } })
await db.execute(`INSERT INTO products (id, name, category) VALUES ('1', 'A', 'x'), ('2', 'B', 'x')`)

const row = z.object({ id: z.coerce.number(), name: z.string().nullable(), category: z.string().nullable() })
const collection = createCollection(
  powerSyncCollectionOptions({
    database: db,
    table: APP_SCHEMA.props.products,
    schema: row,
    deserializationSchema: row,
    onDeserializationError: () => {},
  }),
)
await createLiveQueryCollection({
  query: (q) => q.from({ product: collection }).where(({ product }) => eq(product.category, 'x')),
}).preload()
// Eager: "Source collection '…' failed its first load: Record must have a string id field"
// On-demand: "Initial subset load failed: Record must have a string id field"

The same script with id: z.string() loads and reloads correctly.

Where the string key is assumed

• helpers.ts#L69: asPowerSyncRecord throws for a non-string id, and getKey goes through it. The types enforce the same thing: AnyTableColumnType has id: string, and the config type omits getKey.

Removing that check alone wouldn't be enough. Two on-demand paths compare the raw SQLite id (always text) with collection keys:

• powersync.ts#L818: collection.has(rowId) never matches a numeric key. A held row that changed would then be written as an insert and throw DuplicateKeySyncError.
• powersync.ts#L830: missing?.delete(row.id) would remove nothing, so recovering from a tracking outage would delete every held row.

Ongoing changes (flushDiffRecordsWithContext) and deletes already derive the key from the deserialized row.

Proposal

Let the key come from the deserialized row. For example, allow id to deserialize to string | number, or accept a getKey option. Then use that key in the two on-demand paths above, e.g. getKey(deserializeSyncRow(row)) instead of row.id.

Local writes through the collection would still need a string id for SQLite. So this could be limited to collections that convert id in deserializationSchema and only read, with writes going through the app's API, which is the case for server-generated integer IDs. PowerSync's service already converts integer id columns to text when syncing (idFromData in sync-rules/src/cast.ts), so only the client adapter needs the change.