Primary photo (child record created with parent) silently dropped on receiving device due to FK ordering

[camdenwebster]

Summary

When a parent record and an associated child record (with a Data BLOB column) are created in the same database.write transaction on Device A, the child record is silently dropped on Device B during CloudKit sync. Child records created in later, separate transactions sync correctly.

Environment

• SQLiteData version: latest (as of March 2026)
• Platform: iOS 18+

Reproduction

Schema

@Table("inventoryItems")
struct SQLiteInventoryItem: Identifiable {
    let id: UUID
    var title: String = ""
    // ...
}

@Table("inventoryItemPhotos")
struct SQLiteInventoryItemPhoto: Identifiable {
    let id: UUID
    var inventoryItemID: SQLiteInventoryItem.ID
    var data: Data      // full-resolution JPEG, typically 1–3 MB
    var sortOrder: Int = 0
}

CREATE TABLE "inventoryItems" (
    "id" TEXT PRIMARY KEY NOT NULL ON CONFLICT REPLACE DEFAULT (uuid()),
    "title" TEXT NOT NULL ...
) STRICT

CREATE TABLE "inventoryItemPhotos" (
    "id" TEXT PRIMARY KEY NOT NULL ON CONFLICT REPLACE DEFAULT (uuid()),
    "inventoryItemID" TEXT NOT NULL REFERENCES "inventoryItems"("id") ON DELETE CASCADE,
    "data" BLOB NOT NULL,
    "sortOrder" INTEGER NOT NULL ON CONFLICT REPLACE DEFAULT 0
) STRICT

SyncEngine registration (both tables present, FK enforcement enabled):

try SyncEngine(
    for: database,
    tables: SQLiteInventoryItem.self, SQLiteInventoryItemPhoto.self, /* ... */
    startImmediately: false
)

// appDatabase():
configuration.foreignKeysEnabled = true

Write path that triggers the bug

// Item + primary photo created together in one transaction
try await database.write { db in
    try SQLiteInventoryItem.insert { item }.execute(db)
    try SQLiteInventoryItemPhoto.insert {
        SQLiteInventoryItemPhoto(id: UUID(), inventoryItemID: item.id, data: imageData, sortOrder: 0)
    }.execute(db)
}

Write path that works correctly

// Item already exists in the database; photo added in a separate transaction
try await database.write { db in
    try SQLiteInventoryItemPhoto.insert {
        SQLiteInventoryItemPhoto(id: UUID(), inventoryItemID: item.id, data: imageData, sortOrder: 1)
    }.execute(db)
}

Observed behaviour

• Device A (origin): item + primary photo both visible ✓
• Device B (sync recipient, same iCloud account): item metadata visible ✓, primary photo missing ✗, even after 30+ minutes
• Any photo added to the same item in a later, separate transaction appears correctly on Device B ✓

Root cause hypothesis

"inventoryItemPhotos" < "inventoryItems" alphabetically ('P' < 's'). If the SyncEngine inserts incoming CKRecords sorted by recordType name, the photo row is attempted before its parent item row exists in the local SQLite database. With foreignKeysEnabled = true this causes a FK constraint violation; the row is silently dropped and the item row succeeds.

This theory is consistent with:

• Child records created in the same transaction as the parent → arrive in the same CloudKit batch → subject to ordering issue ✗
• Child records created in later transactions → parent already in local DB when they arrive → FK satisfied ✓

The same pattern likely affects any table pair where the child table name sorts alphabetically before the parent:

• homePhotos < homes ('P' < 's')
• inventoryLocationPhotos < inventoryLocations

Expected behaviour

The SyncEngine should ensure parent records are written to the local database before child records that reference them, regardless of how incoming CKRecords are ordered by CloudKit. This could be achieved by topologically sorting records by their FK dependencies before inserting, or by temporarily deferring FK checks during batch inserts.

Workaround

A per-constraint DEFERRABLE INITIALLY DEFERRED FK causes SQLite to defer the check until transaction commit, by which point all records in the batch should be present:

migrator.registerMigration("Make inventoryItemPhotos FK deferrable") { db in
    try db.execute(sql: """
        CREATE TABLE "inventoryItemPhotos_new" (
            "id" TEXT PRIMARY KEY NOT NULL ON CONFLICT REPLACE DEFAULT (uuid()),
            "inventoryItemID" TEXT NOT NULL
                REFERENCES "inventoryItems"("id") ON DELETE CASCADE
                DEFERRABLE INITIALLY DEFERRED,
            "data" BLOB NOT NULL,
            "sortOrder" INTEGER NOT NULL ON CONFLICT REPLACE DEFAULT 0
        ) STRICT
    """)
    try db.execute(sql: "INSERT INTO inventoryItemPhotos_new SELECT * FROM inventoryItemPhotos")
    try db.execute(sql: "DROP TABLE inventoryItemPhotos")
    try db.execute(sql: "ALTER TABLE inventoryItemPhotos_new RENAME TO inventoryItemPhotos")
}

This resolves the symptom. If the ordering behaviour is intentional or the root cause is elsewhere, happy to provide more diagnostic information.

[mbrandonw]

Note: Please disclose when using AI to file issues, and ideally rewrite this yourself to make it more succinct and actually confirm its content is correct.

Hi @camdenwebster, I am not able to reproduce this. The reminders demo app has assets, and to test I updated the entry point of the app to immediately create a list + image:

Task {
  @Dependency(\.defaultDatabase) var database
  try await database.write { db in
    let id = try RemindersList.insert {
      RemindersList.Draft(title: "TEST")
    }
    .returning(\.id)
    .fetchOne(db)!
    try RemindersListAsset.insert {
      RemindersListAsset.Draft(remindersListID: id, coverImage: Data([0xde,0xad,0xbe,0xef]))
    }
    .execute(db)
  }
}

That synchronized to iCloud just fine, and when I delete and reinstall the app from scratch that list + asset came through. I also tested this by creating a reminders list, asset, and a bunch of reminders all at the same time, and that too synchronized just fine.

"inventoryItemPhotos" < "inventoryItems" alphabetically ('P' < 's'). If the SyncEngine inserts incoming CKRecords sorted by recordType name, the photo row is attempted before its parent item row exists in the local SQLite database. With foreignKeysEnabled = true this causes a FK constraint violation; the row is silently dropped and the item row succeeds.

That is not how the SyncEngine works. It topologically sorts tables by their foreign key relationships, so if TableA is a parent of TableB, then it will synchronize TableA rows before TableB. We have lots of test coverage on this. For example, the test suite has a "parents" table with a "childWithOnDeleteSetNulls" child table. This is an example of a child table that lexicographically sorts before its parent, but it creating parent+child records at once synchronizes just fine.

Workaround

A per-constraint DEFERRABLE INITIALLY DEFERRED FK causes SQLite to defer the check until transaction commit, by which point all records in the batch should be present:

This would not fix anything because by the very nature of the remote synchronizing it is possible to receive child records long before parent records are received, and so will not be apart of the same transaction.

Since I'm not sure this is an issue with the library I am going to convert it to a discussion. Please provide more information on how to reproduce and we can take another look at it.