Data divergence between devices after concurrent offline edits

[lukaskubanek]

Description

While experimenting with the library in preparation for #272, I ran into a case where a concurrently edited field diverged between devices.

Let’s consider a single TEXT column of a table (I was using Reminder.title in the Reminders example app) and two devices, A and B, editing the field of the same row concurrently. To simulate a real-world scenario, device A is put offline during the editing phase.

Scenario 1

In the first scenario, device B makes its edit before device A. Everything works as expected. When device A comes online, it’s prevented from sending its value until it downloads device B’s update. Since device A’s change is considered the last write, it’s then correctly propagated to device B.

Device A	Device B
foo	foo
offline
	fooB
	send
fooA
online
send 💥
fetch
send
	fetch
	fooA

Scenario 2

In the second scenario, the steps are similar, only the order of edits is swapped. When device A comes online, it also fetches device B’s value. But this time, since device B’s edit is the last write, device A should adopt that value and drop its local change. However, this doesn’t happen. Device A keeps its local value, which causes the SQLite database to diverge from the CloudKit record, as the local value correctly isn’t synced with CloudKit anymore.

Device A	Device B
foo	foo
offline
fooA
	fooB
	send
online
send 💥
fetch ⚠️
send ⚠️
	fetch

It seems that device A’s local value is still considered a modification during the fetch. The expected behavior would be for the logic to recognize that the local change is outdated and let it be overwritten by the server value.

This might be better addressed with an abstraction built as part of #272, but I think it’s better to tackle it in piecemeal steps.

Checklist

• I have determined whether this bug is also reproducible in a vanilla SwiftUI project.
• I have determined whether this bug is also reproducible in a vanilla GRDB project.
• If possible, I've reproduced the issue using the main branch of this package.
• This issue hasn't been addressed in an existing GitHub issue or discussion.

Expected behavior

No response

Actual behavior

No response

Reproducing project

No response

SQLiteData version information

main

Sharing version information

No response

GRDB version information

No response

Destination operating system

iOS 18

Xcode version information

26.1 β3

Swift Compiler version information

swift-driver version: 1.127.14.1 Apple Swift version 6.2 (swiftlang-6.2.0.19.9 clang-1700.3.19.1) Target: arm64-apple-macosx15.0

[lukaskubanek]

As part of elaborating on the customizable conflict-resolution work in #272, I looked deeper into the divergence described in this issue, especially scenario 2. I initially assumed that device A wouldn’t process the fetch correctly when it had a local modification and would stale it out, but it turns out that device A does send its change to the server. The divergence actually emerges on device B, which doesn’t accept the change when it arrives from the server.

---

Let’s zoom in on the scenario from the Reminders sample app:

1. Both devices start with a reminder with ID 3169aa9e-0b40-48af-8f0b-a50c3fb04a72 and the title set to foo. The server also holds foo.

2. Device A goes offline.

3. Device A changes the title of the reminder to fooA.

INSERT INTO "reminders" ("id", "title", …)
VALUES ('3169aa9e-0b40-48af-8f0b-a50c3fb04a72', 'fooA', …)
ON CONFLICT ("id") DO UPDATE SET
  "title" = "excluded"."title",
  …;

4. Device B (still online) changes the title of the reminder to fooB.

INSERT INTO "reminders" ("id", "title", …)
VALUES ('3169aa9e-0b40-48af-8f0b-a50c3fb04a72', 'fooB', …)
ON CONFLICT ("id") DO UPDATE SET
  "title" = "excluded"."title",
  …;

5. Device B’s sync engine uploads fooB to the server. CloudKit then reports the same record back to the device (as it always does after a successful save), but this round-trip is effectively a no-op. The server now holds fooB.

SQLiteData (private.db) nextRecordZoneChangeBatch: scheduled
  ┏━━━━━━━━━━━┳━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
  ┃ event     ┃ recordType ┃ recordName                                     ┃
  ┡━━━━━━━━━━━╇━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┩
  │ ➡️ Sending │ reminders  │ 3169aa9e-0b40-48af-8f0b-a50c3fb04a72:reminders │
  └───────────┴────────────┴────────────────────────────────────────────────┘

SQLiteData (private.db) sentRecordZoneChanges
  ┏━━━━━━━━━┳━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
  ┃ action  ┃ recordType ┃ recordName                                     ┃
  ┡━━━━━━━━━╇━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┩
  │ ✅ Saved │ reminders  │ 3169aa9e-0b40-48af-8f0b-a50c3fb04a72:reminders │
  └─────────┴────────────┴────────────────────────────────────────────────┘

SQLiteData (private.db) fetchedDatabaseChanges
  ┏━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━┓
  ┃ action     ┃ zoneName                            ┃ ownerName        ┃
  ┡━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━┩
  │ ✅ Modified │ co.pointfree.SQLiteData.defaultZone │ __defaultOwner__ │
  └────────────┴─────────────────────────────────────┴──────────────────┘
  
SQLiteData (private.db) fetchedRecordZoneChanges
  ┏━━━━━━━━━━━━━┳━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
  ┃ action      ┃ recordType ┃ recordName                                     ┃
  ┡━━━━━━━━━━━━━╇━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┩
  │ ✅ Modified │ reminders  │ 3169aa9e-0b40-48af-8f0b-a50c3fb04a72:reminders │
  └─────────────┴────────────┴────────────────────────────────────────────────┘

6. Device A comes back online.

7. Device A schedules a send that includes its local change. Since it hasn’t yet seen device B’s update, it tries to apply fooA over the last-known server record value foo. But because the server now holds fooB, the save fails. (Depending on timing, this failure may occur before or after device A receives the server’s updates in the next step.)

SQLiteData (private.db) nextRecordZoneChangeBatch: scheduled
  ┏━━━━━━━━━━━┳━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
  ┃ event     ┃ recordType ┃ recordName                                     ┃
  ┡━━━━━━━━━━━╇━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┩
  │ ➡️ Sending │ reminders  │ 3169aa9e-0b40-48af-8f0b-a50c3fb04a72:reminders │
  └───────────┴────────────┴────────────────────────────────────────────────┘

SQLiteData (private.db) sentRecordZoneChanges
  ┏━━━━━━━━━━━━━━━┳━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━┓
  ┃ action        ┃ recordType ┃ recordName                                     ┃ error                    ┃
  ┡━━━━━━━━━━━━━━━╇━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━┩
  │ 🛑 Save failed │ reminders  │ 3169aa9e-0b40-48af-8f0b-a50c3fb04a72:reminders │ serverRecordChanged (14) │
  └───────────────┴────────────┴────────────────────────────────────────────────┴──────────────────────────┘

8. Device A receives the updated record from the server and updates its last-known server record to fooB. Because the local modification is still present and isRowValueModified is true, the upsert logic in SyncEngine.upsertFromServerRecord(…) and CKRecord.update(with:row:columnNames:parentForeignKey:) skips applying the server value to the row in the database. The server now holds fooB, device A’s last-known server record reflects fooB, but the row itself still contains fooA.

SQLiteData (private.db) fetchedDatabaseChanges
  ┏━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━┓
  ┃ action     ┃ zoneName                            ┃ ownerName        ┃
  ┡━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━┩
  │ ✅ Modified │ co.pointfree.SQLiteData.defaultZone │ __defaultOwner__ │
  └────────────┴─────────────────────────────────────┴──────────────────┘
  
SQLiteData (private.db) fetchedRecordZoneChanges
  ┏━━━━━━━━━━━━┳━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
  ┃ action     ┃ recordType ┃ recordName                                     ┃
  ┡━━━━━━━━━━━━╇━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┩
  │ ✅ Modified │ reminders  │ 3169aa9e-0b40-48af-8f0b-a50c3fb04a72:reminders │
  └────────────┴────────────┴────────────────────────────────────────────────┘

9. Device A schedules another save, as the sync engine is still trying to push the pending save through. At this point, the last-known server record reflects fooB, so the outgoing change is treated as updating from fooB to fooA. This time the save succeeds. The server, device A’s last-known server record, and the row in the database all now contain fooA.

SQLiteData (private.db) nextRecordZoneChangeBatch: scheduled
  ┏━━━━━━━━━━━┳━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
  ┃ event     ┃ recordType ┃ recordName                                     ┃
  ┡━━━━━━━━━━━╇━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┩
  │ ➡️ Sending │ reminders  │ 3169aa9e-0b40-48af-8f0b-a50c3fb04a72:reminders │
  └───────────┴────────────┴────────────────────────────────────────────────┘
  
SQLiteData (private.db) sentRecordZoneChanges
  ┏━━━━━━━━━┳━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
  ┃ action  ┃ recordType ┃ recordName                                     ┃
  ┡━━━━━━━━━╇━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┩
  │ ✅ Saved │ reminders  │ 3169aa9e-0b40-48af-8f0b-a50c3fb04a72:reminders │
  └─────────┴────────────┴────────────────────────────────────────────────┘

10. Next, device B fetches this change from the server and attempts to upsert from the fetched server record. Remember that device B holds onto fooB in both the last-known server record as well as the row. Although a modification is reported, the upsert is a no-op: updating the title value via CKRecord.setValue(…) on the fetched server record with values from device B’s last-known server record overwrites fooA with fooB, because fooB has the newer timestamp. Since the didSet flag is now set, the title column is excluded from updates. As a result, the last-known server record ends up storing fooB (even though the server now holds fooA), and the row also continues to contain fooB.

SQLiteData (private.db) fetchedDatabaseChanges
  ┏━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━┓
  ┃ action     ┃ zoneName                            ┃ ownerName        ┃
  ┡━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━┩
  │ ✅ Modified │ co.pointfree.SQLiteData.defaultZone │ __defaultOwner__ │
  └────────────┴─────────────────────────────────────┴──────────────────┘
  
SQLiteData (private.db) fetchedRecordZoneChanges
  ┏━━━━━━━━━━━━┳━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
  ┃ action     ┃ recordType ┃ recordName                                     ┃
  ┡━━━━━━━━━━━━╇━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┩
  │ ✅ Modified │ reminders  │ 3169aa9e-0b40-48af-8f0b-a50c3fb04a72:reminders │
  └────────────┴────────────┴────────────────────────────────────────────────┘

INSERT INTO "reminders" ("id", "title", …)
VALUES ('3169aa9e-0b40-48af-8f0b-a50c3fb04a72', 'fooB', …)
ON CONFLICT ("id") DO UPDATE SET
  "title" = "excluded"."title",
  …;

11. And boom, the value has diverged! The state only repairs itself after modifying the broken field directly. Changing any other field does not cause the sync engine to work it out.

---

Obviously, ending up with diverged values is suboptimal. But the question is what the correct behavior should be here. According to my mental model, we’re running into a conflict in step 7 or 8, be it on send or on fetch, as the last-known server (aka ancestor) version (foo), the server version (fooB), and the database row (aka client) version (fooA) differ. With a customizable three-way merge per #272, one could plug in any strategy to resolve it, e.g. a string-based diff that produces fooAB. We’re not there yet, but I’d say this logic applies to the current built-in per-field LWW strategy as well. In other words, receiving a newer fooB from the server in steps 7 or 8 should replace the local modification.

@mbrandonw @stephencelis Does this make sense?

[lukaskubanek]

Over in #272, I’ve broken down the exact (non-)conflict scenarios that get executed on each device, ultimately leading to the divergence described in this issue. It comes down to the combination of scenario C1 on device A and scenario NC4 on device B. I also propose some high-level adjustments to how the LWW strategy could be applied to avoid this class of divergence.