Dexie Cloud SaaS: duplicate-key insert semantics and transaction atomicity with dexie-cloud-addon 4.4.13

[Chenhao-SHIWO]

We are preparing a reviewed add-only operation using dexie-cloud-addon 4.4.13 and the v3 /sync protocol. We need to confirm the server-side contract before enabling a production write.

Assume an authenticated user with permission to add records in their personal realm. The keys are ordinary globally unique string IDs, not private singleton IDs starting with '#'. A client's local snapshot is stale: an ordinary primary key already exists on the server, while the client submits a type="insert" for that key with different field values.

A. When /sync receives this insert, exactly how does Dexie Cloud SaaS handle the existing primary key?

B. Is the existing row guaranteed to remain unchanged? Does the server reject the insert, skip it, replace/merge fields, or apply another rule? Please distinguish this behavior from IndexedDB's local ConstraintError and from upsert operations.

C. Suppose one client Dexie transaction contains that duplicate-key insert and a second insert with a genuinely new key, possibly in another table. The mutation records carry the same transaction ID. Is the entire transaction guaranteed to have no effect on the server, including the new row? If a duplicate insert is skipped rather than rejected, can the new insert still succeed? How is the result represented in the sync response?

D. Could you provide the applicable server-side contract, an official test covering this case, or explicit maintainer confirmation for Dexie Cloud SaaS used with addon 4.4.13? Please clarify any relevant server-version or configuration dependency.

This is a draft technical question. It includes no real database endpoint, database identifier, user identifier, business data, token, or management credential. No duplicate-key experiment has been performed against production.

[dfahlander]

I was about to answer you in self-confidence that the server would reject the transaction along with all other operations in it, forcing the client to rollback its transaction.

The correct behavior is that the server will throw a ConstraintError and revert all accompanied changes in the same transaction:

1. Client adds new object locally (not yet aware of that it's primary key will collide once it reaches the server)
2. Client syncs its transaction(s) (POST /sync)
3. The server throws ConstraintError and refuses to apply any of the operations in the same transaction.
4. Server responds HTTP 200 ok with:

   {
     "rejections": [
       {
         "name": "ConstraintError",
         "message": "Key already exists: myTable/duplicate-id",
         "txid": "client-tx-123"
        }
      ],
      "changes": { ...changes that deletes the object on client and rollbacks any other ops in same transaction }
   }

5. Client applies the changes

However, @liz709 found that this behavior was not correct anymore since a server deploy in late July. A fix for a server crash was done in a rush and introduced a changed behavior. Instead, in step 3, the server accepted the "insert" operation from the client and converted it to an "upsert" operation.

In practice, the server has had a non-intended behavior during almost 3 months now, to accept conflicting inserts as upserts.

A fix is about to be rolled out asap. So, my intended behavior above will be the expected behavior once this fix reaches production.

This is not tied to any specific version of dexie-cloud-addon. It's a server-side fix only.

Thanks for bringing up the discussion.

[dfahlander]

Regarding your request for official test coverage (question D):

Since the dexie-cloud server repository is private, we cannot link directly to the PR or CI pipeline. However, here is the unit test added in the fix to permanently guard this contract in the server test suite:

it('should throw ConstraintError when inserting an existing object in a regular table', () => {
  const existingTodo = {
    id: 'todo-1',
    text: 'Existing todo',
    realmId: 'realm-1',
    owner: 'user-1'
  };
  const snapshot = MemDBSnapshot([
    {
      tbl: 'todos',
      id: 'todo-1',
      obj: existingTodo,
      realm_id: 'realm-1'
    }
  ]);

  const tx: ClientTransactionWithPseudoOps = {
    txid: 'tx-1',
    time: Date.now(),
    lastClRevs: {},
    baseRev: null,
    ops: [
      {
        table: 'todos',
        op: {
          type: 'insert',
          keys: ['todo-1'],
          values: [
            {
              id: 'todo-1',
              text: 'New duplicate todo',
              realmId: 'realm-1',
              owner: 'user-1'
            }
          ]
        }
      }
    ]
  };

  expect(() => applyClientChangesInMem(user, tx, snapshot)).toThrow(
    ConstraintError
  );
  try {
    applyClientChangesInMem(user, tx, snapshot);
  } catch (err: any) {
    expect(err.name).toBe('ConstraintError');
    expect(err.message).toBe('Key already exists: todos/todo-1');
  }
});

I will update this thread as soon as the fix is deployed to Dexie Cloud SaaS production so you can safely verify your add-only flow.

[dfahlander]

dexie-cloud-server@3.1.17 is now in production and includes the corrected behavior of constraint error rejections.