fix(mobile): dedupe stale remote_asset rows on sync

queue a pre-delete on the matching partial-index tuple before each
upsert in updateAssetsV1/V2 so the second insert does not crash on
SqliteException(2067) when the server re-issues a new id for the
same (ownerId, checksum). closes #22522 #27186.

mobile/lib/infrastructure/repositories/sync_stream.repository.dart

@@ -197,6 +197,16 @@ class SyncStreamRepository extends DriftDatabaseRepository {
     try {
       await _db.batch((batch) {
         for (final asset in data) {
+          // Avoid SqliteException(2067) when server re-issues a new id for
+          // the same (ownerId, checksum). #22522 #27186
+          _enqueueRemoteAssetDedupe(
+            batch,
+            id: asset.id,
+            ownerId: asset.ownerId,
+            checksum: asset.checksum,
+            libraryId: asset.libraryId,
+          );
+
           final companion = RemoteAssetEntityCompanion(
             name: Value(asset.originalFileName),
             type: Value(asset.type.toAssetType()),
@@ -236,6 +246,15 @@ class SyncStreamRepository extends DriftDatabaseRepository {
     try {
       await _db.batch((batch) {
         for (final asset in data) {
+          // See updateAssetsV1 for why this dedupe is required. #22522 #27186
+          _enqueueRemoteAssetDedupe(
+            batch,
+            id: asset.id,
+            ownerId: asset.ownerId,
+            checksum: asset.checksum,
+            libraryId: asset.libraryId,
+          );
+
           final companion = RemoteAssetEntityCompanion(
             name: Value(asset.originalFileName),
             type: Value(asset.type.toAssetType()),
@@ -271,6 +290,39 @@ class SyncStreamRepository extends DriftDatabaseRepository {
     }
   }
 
+  /// Queues a DELETE that prunes any stale remote_asset row matching the
+  /// partial UNIQUE index for the incoming asset:
+  ///   - libraryId IS NULL  -> (owner_id, checksum)
+  ///   - libraryId NOT NULL -> (owner_id, library_id, checksum)
+  /// The current id is excluded so a same-id update does not delete itself.
+  void _enqueueRemoteAssetDedupe(
+    Batch batch, {
+    required String id,
+    required String ownerId,
+    required String checksum,
+    required String? libraryId,
+  }) {
+    if (libraryId == null) {
+      batch.deleteWhere(
+        _db.remoteAssetEntity,
+        (row) =>
+            row.ownerId.equals(ownerId) &
+            row.checksum.equals(checksum) &
+            row.libraryId.isNull() &
+            row.id.equals(id).not(),
+      );
+    } else {
+      batch.deleteWhere(
+        _db.remoteAssetEntity,
+        (row) =>
+            row.ownerId.equals(ownerId) &
+            row.checksum.equals(checksum) &
+            row.libraryId.equals(libraryId) &
+            row.id.equals(id).not(),
+      );
+    }
+  }
+
   Future<void> updateAssetsExifV1(Iterable<SyncAssetExifV1> data, {String debugLabel = 'user'}) async {
     try {
       await _db.batch((batch) {

mobile/test/domain/repositories/sync_stream_repository_test.dart

@@ -28,6 +28,7 @@ SyncAssetV1 _createAsset({
   String ownerId = 'user-1',
   int? width,
   int? height,
+  String? libraryId,
 }) {
   return SyncAssetV1(
     id: id,
@@ -45,7 +46,38 @@ SyncAssetV1 _createAsset({
     height: height,
     deletedAt: null,
     duration: null,
-    libraryId: null,
+    libraryId: libraryId,
+    livePhotoVideoId: null,
+    stackId: null,
+    thumbhash: null,
+    isEdited: false,
+  );
+}
+
+SyncAssetV2 _createAssetV2({
+  required String id,
+  required String checksum,
+  required String fileName,
+  String ownerId = 'user-1',
+  String? libraryId,
+}) {
+  return SyncAssetV2(
+    id: id,
+    checksum: checksum,
+    originalFileName: fileName,
+    type: AssetTypeEnum.IMAGE,
+    ownerId: ownerId,
+    isFavorite: false,
+    fileCreatedAt: DateTime(2024, 1, 1),
+    fileModifiedAt: DateTime(2024, 1, 1),
+    createdAt: DateTime(2024, 1, 1),
+    localDateTime: DateTime(2024, 1, 1),
+    visibility: AssetVisibility.timeline,
+    width: null,
+    height: null,
+    deletedAt: null,
+    duration: 0,
+    libraryId: libraryId,
     livePhotoVideoId: null,
     stackId: null,
     thumbhash: null,
@@ -240,4 +272,82 @@ void main() {
       expect(after.backupSelection, equals(BackupSelection.none));
     });
   });
+
+  group('SyncStreamRepository - updateAssetsV1 dedupe (#22522 #27186)', () {
+    test('replaces stale row when new id arrives with same (ownerId, checksum) and library is null', () async {
+      await sut.updateUsersV1([_createUser()]);
+      await sut.updateAssetsV1([_createAsset(id: 'old-id', checksum: 'AAA', fileName: 'photo.jpg')]);
+
+      // Server re-issues a new id for the same content (replace-with-upload, immich-go, etc.)
+      await sut.updateAssetsV1([_createAsset(id: 'new-id', checksum: 'AAA', fileName: 'photo.jpg')]);
+
+      final rows = await db.remoteAssetEntity.select().get();
+      expect(rows, hasLength(1));
+      expect(rows.single.id, equals('new-id'));
+      expect(rows.single.checksum, equals('AAA'));
+    });
+
+    test('replaces stale row by (ownerId, libraryId, checksum) when library is not null', () async {
+      await sut.updateUsersV1([_createUser()]);
+      await sut.updateAssetsV1([
+        _createAsset(id: 'old-id', checksum: 'AAA', fileName: 'photo.jpg', libraryId: 'lib-1'),
+      ]);
+
+      await sut.updateAssetsV1([
+        _createAsset(id: 'new-id', checksum: 'AAA', fileName: 'photo.jpg', libraryId: 'lib-1'),
+      ]);
+
+      final rows = await db.remoteAssetEntity.select().get();
+      expect(rows, hasLength(1));
+      expect(rows.single.id, equals('new-id'));
+      expect(rows.single.libraryId, equals('lib-1'));
+    });
+
+    test('library and non-library rows with same (ownerId, checksum) coexist', () async {
+      await sut.updateUsersV1([_createUser()]);
+      await sut.updateAssetsV1([
+        _createAsset(id: 'lib-row', checksum: 'AAA', fileName: 'photo.jpg', libraryId: 'lib-1'),
+        _createAsset(id: 'main-row', checksum: 'AAA', fileName: 'photo.jpg'),
+      ]);
+
+      final rows = await db.remoteAssetEntity.select().get();
+      expect(rows, hasLength(2), reason: 'library NULL and NOT NULL match different partial indexes');
+      expect(rows.map((r) => r.id).toSet(), equals({'lib-row', 'main-row'}));
+    });
+
+    test('different owners with same checksum coexist', () async {
+      await sut.updateUsersV1([_createUser(id: 'user-1')]);
+      await sut.updateUsersV1([_createUser(id: 'user-2')]);
+      await sut.updateAssetsV1([
+        _createAsset(id: 'a-id', checksum: 'AAA', fileName: 'photo.jpg', ownerId: 'user-1'),
+        _createAsset(id: 'b-id', checksum: 'AAA', fileName: 'photo.jpg', ownerId: 'user-2'),
+      ]);
+
+      final rows = await db.remoteAssetEntity.select().get();
+      expect(rows, hasLength(2));
+    });
+
+    test('same id arriving again updates in place (no self-delete)', () async {
+      await sut.updateUsersV1([_createUser()]);
+      await sut.updateAssetsV1([_createAsset(id: 'same-id', checksum: 'AAA', fileName: 'photo.jpg')]);
+
+      await sut.updateAssetsV1([_createAsset(id: 'same-id', checksum: 'AAA', fileName: 'renamed.jpg')]);
+
+      final rows = await db.remoteAssetEntity.select().get();
+      expect(rows, hasLength(1));
+      expect(rows.single.id, equals('same-id'));
+      expect(rows.single.name, equals('renamed.jpg'), reason: 'ON CONFLICT(id) DO UPDATE path still works');
+    });
+
+    test('updateAssetsV2 dedupes the same way', () async {
+      await sut.updateUsersV1([_createUser()]);
+      await sut.updateAssetsV2([_createAssetV2(id: 'old-id', checksum: 'AAA', fileName: 'photo.jpg')]);
+
+      await sut.updateAssetsV2([_createAssetV2(id: 'new-id', checksum: 'AAA', fileName: 'photo.jpg')]);
+
+      final rows = await db.remoteAssetEntity.select().get();
+      expect(rows, hasLength(1));
+      expect(rows.single.id, equals('new-id'));
+    });
+  });
 }