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.

e2e/src/specs/maintenance/server/database-backups.e2e-spec.ts

@@ -2,7 +2,7 @@ import { LoginResponseDto, ManualJobName } from '@immich/sdk';
 import { errorDto } from 'src/responses';
 import { app, utils } from 'src/utils';
 import request from 'supertest';
-import { afterAll, beforeAll, describe, expect, it } from 'vitest';
+import { afterAll, beforeAll, beforeEach, describe, expect, it } from 'vitest';
 
 describe('/admin/database-backups', () => {
   let cookie: string | undefined;
@@ -13,6 +13,9 @@ describe('/admin/database-backups', () => {
     admin = await utils.adminSetup({
       onboarding: false,
     });
+  });
+
+  beforeEach(async () => {
     await utils.resetBackups(admin.accessToken);
   });
 

e2e/src/utils.ts

@@ -568,6 +568,8 @@ export const utils = {
       name: ManualJobName.BackupDatabase,
     });
 
+    await utils.waitForQueueFinish(accessToken, 'backupDatabase');
+
     return utils.poll(
       () => request(app).get('/admin/database-backups').set('Authorization', `Bearer ${accessToken}`),
       ({ status, body }) => status === 200 && body.backups.length === 1,

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/lib/presentation/widgets/action_buttons/base_action_button.widget.dart

@@ -35,10 +35,11 @@ class BaseActionButton extends ConsumerWidget {
     final miniWidth = minWidth ?? (context.isMobile ? context.width / 4.5 : 75.0);
     final iconTheme = IconTheme.of(context);
     final iconSize = iconTheme.size ?? 24.0;
-    final iconColor = this.iconColor ?? iconTheme.color ?? context.themeData.iconTheme.color;
     final textColor = context.themeData.textTheme.labelLarge?.color;
 
     if (iconOnly) {
+      final iconColor = this.iconColor ?? iconTheme.color ?? context.themeData.iconTheme.color;
+
       return IconButton(
         onPressed: onPressed,
         icon: Icon(iconData, size: iconSize, color: iconColor),
@@ -46,17 +47,18 @@ class BaseActionButton extends ConsumerWidget {
     }
 
     if (menuItem) {
-      final theme = context.themeData;
-      final effectiveIconColor = iconColor ?? theme.iconTheme.color ?? theme.colorScheme.onSurfaceVariant;
+      final iconColor = this.iconColor;
 
       return MenuItemButton(
         style: MenuItemButton.styleFrom(alignment: Alignment.centerLeft, padding: const EdgeInsets.all(16)),
-        leadingIcon: Icon(iconData, color: effectiveIconColor),
+        leadingIcon: Icon(iconData, color: iconColor),
         onPressed: onPressed,
-        child: Text(label, style: theme.textTheme.labelLarge?.copyWith(fontSize: 16, color: iconColor)),
+        child: Text(label, style: TextStyle(fontSize: 16, color: iconColor)),
       );
     }
 
+    final iconColor = this.iconColor ?? iconTheme.color ?? context.themeData.iconTheme.color;
+
     return ConstrainedBox(
       constraints: BoxConstraints(maxWidth: maxWidth),
       child: MaterialButton(

mobile/lib/presentation/widgets/asset_viewer/viewer_kebab_menu.widget.dart

@@ -50,7 +50,7 @@ class ViewerKebabMenu extends ConsumerWidget {
       timelineOrigin: timelineOrigin,
     );
 
-    final menuChildren = ActionButtonBuilder.buildViewerKebabMenu(actionContext, context);
+    final menuChildren = ActionButtonBuilder.buildViewerKebabMenu(actionContext, context, ref);
 
     return MenuAnchor(
       consumeOutsideTap: true,

mobile/lib/utils/action_button.utils.dart

@@ -330,7 +330,7 @@ class ActionButtonBuilder {
     return _actionTypes.where((type) => type.shouldShow(context)).map((type) => type.buildButton(context)).toList();
   }
 
-  static List<Widget> buildViewerKebabMenu(ActionButtonContext context, BuildContext buildContext) {
+  static List<Widget> buildViewerKebabMenu(ActionButtonContext context, BuildContext buildContext, WidgetRef ref) {
     final visibleButtons = defaultViewerKebabMenuOrder
         .where((type) => !defaultViewerBottomBarButtons.contains(type) && type.shouldShow(context))
         .toList();
@@ -346,7 +346,7 @@ class ActionButtonBuilder {
       if (lastGroup != null && type.kebabMenuGroup != lastGroup) {
         result.add(const Divider(height: 1));
       }
-      result.add(type.buildButton(context, buildContext, false, true));
+      result.add(type.buildButton(context, buildContext, false, true).build(buildContext, ref));
       lastGroup = type.kebabMenuGroup;
     }
 

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'));
+    });
+  });
 }

server/src/repositories/job.repository.ts

@@ -171,8 +171,8 @@ export class JobRepository {
         options: this.getJobOptions(item) || undefined,
       } as JobItem & { data: any; options: JobsOptions | undefined };
 
-      if (job.options?.jobId) {
-        // need to use add() instead of addBulk() for jobId deduplication
+      if (job.options?.jobId || job.options?.deduplication) {
+        // need to use add() instead of addBulk() for jobId/deduplication to take effect
         promises.push(this.getQueue(queueName).add(item.name, item.data, job.options));
       } else {
         itemsByQueue[queueName] = itemsByQueue[queueName] || [];
@@ -230,10 +230,13 @@ export class JobRepository {
         return { priority: 1 };
       }
       case JobName.FacialRecognitionQueueAll: {
-        return { jobId: JobName.FacialRecognitionQueueAll };
+        return { deduplication: { id: JobName.FacialRecognitionQueueAll } };
       }
       case JobName.VersionCheck: {
-        return { jobId: JobName.VersionCheck };
+        return { deduplication: { id: JobName.VersionCheck } };
+      }
+      case JobName.DatabaseBackup: {
+        return { deduplication: { id: JobName.DatabaseBackup } };
       }
       default: {
         return null;

web/src/routes/(user)/explore/+page.svelte

@@ -4,15 +4,18 @@
   import OnEvents from '$lib/components/OnEvents.svelte';
   import EmptyPlaceholder from '$lib/components/shared-components/EmptyPlaceholder.svelte';
   import SingleGridRow from '$lib/components/shared-components/SingleGridRow.svelte';
+  import { assetViewerManager } from '$lib/managers/asset-viewer-manager.svelte';
   import { Route } from '$lib/route';
   import { getAssetMediaUrl, getPeopleThumbnailUrl } from '$lib/utils';
-  import { AssetMediaSize, type SearchExploreResponseDto } from '@immich/sdk';
+  import { getAssetInfo, AssetMediaSize, type SearchExploreResponseDto } from '@immich/sdk';
+  import { authManager } from '$lib/managers/auth-manager.svelte';
   import { Icon } from '@immich/ui';
   import { mdiHeart } from '@mdi/js';
   import { t } from 'svelte-i18n';
   import type { PageData } from './$types';
   import { toTimelineAsset } from '$lib/utils/timeline-util';
   import { getAltText } from '$lib/utils/thumbnail-util';
+  import Portal from '$lib/elements/Portal.svelte';
 
   interface Props {
     data: PageData;
@@ -40,6 +43,15 @@
       }
     }
   };
+
+  const onViewAsset = async (id: string) => {
+    const asset = await getAssetInfo({ ...authManager.params, id });
+    assetViewerManager.setAsset(asset);
+  };
+
+  const assetCursor = $derived({
+    current: assetViewerManager.asset!,
+  });
 </script>
 
 <OnEvents {onPersonThumbnailReady} />
@@ -122,15 +134,20 @@
           draggable="false">{$t('view_all')}</a
         >
       </div>
-      <div class="flex h-24 flex-wrap gap-x-1 overflow-hidden md:h-42">
+      <div class="flex h-24 max-w-fit flex-wrap gap-x-1 overflow-hidden md:h-42">
         {#each recents as item (item.data.id)}
-          <a class="relative h-full flex-auto" href={Route.viewAsset({ id: item.data.id })} draggable="false">
+          <button
+            type="button"
+            class="relative h-full flex-auto"
+            onclick={() => onViewAsset(item.data.id)}
+            draggable="false"
+          >
             <img
               src={getAssetMediaUrl({ id: item.data.id, size: AssetMediaSize.Thumbnail })}
               alt={$getAltText(toTimelineAsset(item.data))}
               class="size-full min-w-max rounded-xl object-cover"
             />
-          </a>
+          </button>
         {/each}
       </div>
     </div>
@@ -140,3 +157,15 @@
     <EmptyPlaceholder text={$t('no_explore_results_message')} class="mx-auto mt-10" />
   {/if}
 </UserPageLayout>
+
+{#if assetViewerManager.isViewing}
+  {#await import('$lib/components/asset-viewer/AssetViewer.svelte') then { default: AssetViewer }}
+    <Portal target="body">
+      <AssetViewer
+        cursor={assetCursor}
+        showNavigation={false}
+        onClose={() => assetViewerManager.showAssetViewer(false)}
+      />
+    </Portal>
+  {/await}
+{/if}