Client ID Upsert

Clients that follow local-first persistence mint record IDs on the device, before the server has ever seen the record (see the Local-First Persistence pattern for the client half). The server must honour these IDs so that subsequent requests can find and update the same rows by ID.

Rules

1. Never mutate a loaded entity's primary key. existing.id = input.id followed by repo.save(existing) breaks ORM identity tracking (TypeORM and most active-record ORMs) — it attempts an INSERT instead of an UPDATE, causing duplicate-key violations.

2. Lookup by ID first. When the client supplies an id, look up by { id, userId }. If found, update fields in place. If not found, create with the client ID.

3. Natural-key entities need a replace path. Entities with a UNIQUE constraint on a natural key (e.g. (userId, day) for a one-row-per-day record) may have an existing row under a different ID. When the IDs differ: delete the old row, then create a new one with the client ID.

Patterns

ID-keyed entities

No natural-key uniqueness to worry about. Lookup by id:

const existing = input.id
  ? await repo.findOne({ where: { id: input.id, userId } })
  : null;

if (existing) {
  Object.assign(existing, { /* updated fields */ });
  await repo.save(existing);
} else {
  await repo.save(repo.create({ id: input.id, userId, ...fields }));
}

Natural-key entities

Lookup by natural key; replace when ID differs:

const existing = await repo.findOne({ where: { userId, day } });

if (existing) {
  if (input.id && existing.id !== input.id) {
    await repo.remove(existing);
    await repo.save(repo.create({ id: input.id, userId, day, state }));
  } else {
    existing.state = state;
    await repo.save(existing);
  }
} else {
  await repo.save(repo.create({ id: input.id, userId, day, state }));
}

Cleanup phase

After the upsert loop, delete any rows the client didn't send — this is a full-replace semantic:

const inputIds = items.filter(i => i.id).map(i => i.id);
if (inputIds.length > 0) {
  await repo.createQueryBuilder()
    .delete().from(Entity)
    .where('userId = :userId', { userId })
    .andWhere('id NOT IN (:...ids)', { ids: inputIds })
    .execute();
} else {
  await repo.delete({ userId });
}

Only apply the cleanup phase to collections the client sends in full; a partial or paged upload must never trigger it.