When using upsert to update data, duplicate rows were inserted

[VentanYu]

Table definition:

-- 0.000s
CREATE TABLE "events" (
    "id" TEXT PRIMARY KEY NOT NULL ON CONFLICT REPLACE DEFAULT (uuid()),
    "habitId" TEXT NOT NULL REFERENCES "habits"("id") ON DELETE CASCADE,
    "date" TEXT NOT NULL,
    "targetValue" REAL NOT NULL ON CONFLICT REPLACE DEFAULT 1.0,
    "completionValue" REAL NOT NULL ON CONFLICT REPLACE DEFAULT 0.0,
    "isPaused" INTEGER NOT NULL ON CONFLICT REPLACE DEFAULT 0,
    "status" INTEGER AS (
        CASE
            WHEN "completionValue" > 0 AND "completionValue" < "targetValue" THEN 1
            WHEN "completionValue" >= "targetValue" THEN 2
            ELSE 0
        END
    ) STORED
) STRICT

Sql called by upsert (a record already exists before calling it, using this record to modify and then call upsert):

-- 0.000s
INSERT INTO "events" ("id", "habitId", "date", "targetValue", "completionValue", "isPaused") VALUES ('3839E2A7-37C4-44FF-96F9-42602F6C71C8', 'D9102FDD-F33A-4477-B0F1-B1E6A4676B2D', '"2026\/01\/09"', 1.0, 1.0, 0) ON CONFLICT ("id") DO UPDATE SET "habitId" = "excluded"."habitId", "date" = "excluded"."date", "targetValue" = "excluded"."targetValue", "completionValue" = "excluded"."completionValue", "isPaused" = "excluded"."isPaused"

Print the updated query results:

[
  DataKit.Event(id: 3839E2A7-37C4-44FF-96F9-42602F6C71C8, habitId: D9102FDD-F33A-4477-B0F1-B1E6A4676B2D, date: 2026/01/09, targetValue: 1.0, completionValue: 0.0, isPaused: false, status: DataKit.Event.Status.todo), 
  DataKit.Event(id: 3839E2A7-37C4-44FF-96F9-42602F6C71C8, habitId: D9102FDD-F33A-4477-B0F1-B1E6A4676B2D, date: 2026/01/09, targetValue: 1.0, completionValue: 1.0, isPaused: false, status: DataKit.Event.Status.done)
]

It can be seen that the result did not update the original data, but instead inserted a row with a duplicate primary key.

The current package versions are all the latest. I've been debugging for a long time but still can't find any clue.

[mbrandonw]

Hi @VentanYu, can you please share more info on how this happened? What you are showing should not be possible. If two records with the same primary key are being saved that would be a pretty serious SQLite bug, but I doubt that is the case.

[VentanYu]

I probably know what the problem is. This is my original model id definition:

    // Event
    public typealias ID = Tagged<Self, UUID>
    public typealias StoredID = Tagged<Self, UUID.UppercasedRepresentation>

    @Column(as: StoredID.self)
    public let id: ID

The data may have written UUIDs with different capitalizations but the same actual values during two processing processes (initialization and modification), which caused it to pass the text verification in the database, but when converted to UUID in Swift, it becomes a duplicate value.

When I removed all the content of StoredID, the problem disappeared.

[mbrandonw]

Yeah that makes sense. Do you know what code paths caused two different representations of the uuid to be used? One of them must have not been using our tools since our tools make sure to use the correct representation.

You could make your id column “collate no case” but that does bloat the index size. It would be better to just use a consistent representation of UUIDs.