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.