feat: user agnostic database restore (#29512)

* fix(mobile): properly handle live photos locally

* Added basic motion photo data tests

* Cover mismatched platforms and motion photos

* Revert unnecessary storage.repository changes

---------

Co-authored-by: Alex <alex.tran1502@gmail.com>

.github/workflows/cli.yml

@@ -78,10 +78,10 @@ jobs:
         uses: docker/setup-qemu-action@96fe6ef7f33517b61c61be40b68a1882f3264fb8 # v4.2.0
 
       - name: Set up Docker Buildx
-        uses: docker/setup-buildx-action@d7f5e7f509e45cec5c76c4d5afdd7de93d0b3df5 # v4.1.0
+        uses: docker/setup-buildx-action@bb05f3f5519dd87d3ba754cc423b652a5edd6d2c # v4.2.0
 
       - name: Login to GitHub Container Registry
-        uses: docker/login-action@650006c6eb7dba73a995cc03b0b2d7f5ca915bee # v4.2.0
+        uses: docker/login-action@c99871dec2022cc055c062a10cc1a1310835ceb4 # v4.3.0
         if: ${{ !github.event.pull_request.head.repo.fork }}
         with:
           registry: ghcr.io
@@ -96,7 +96,7 @@ jobs:
 
       - name: Generate docker image tags
         id: metadata
-        uses: docker/metadata-action@80c7e94dd9b9319bd5eb7a0e0fe9291e23a2a2e9 # v6.1.0
+        uses: docker/metadata-action@dc802804100637a589fabce1cb79ff13a1411302 # v6.2.0
         with:
           flavor: |
             latest=false
@@ -107,7 +107,7 @@ jobs:
             type=raw,value=latest,enable=${{ github.event_name == 'release' && !github.event.release.prerelease }}
 
       - name: Build and push image
-        uses: docker/build-push-action@f9f3042f7e2789586610d6e8b85c8f03e5195baf # v7.2.0
+        uses: docker/build-push-action@53b7df96c91f9c12dcc8a07bcb9ccacbed38856a # v7.3.0
         with:
           file: packages/cli/Dockerfile
           platforms: linux/amd64,linux/arm64

.github/workflows/codeql-analysis.yml

@@ -57,7 +57,7 @@ jobs:
 
       # Initializes the CodeQL tools for scanning.
       - name: Initialize CodeQL
-        uses: github/codeql-action/init@8aad20d150bbac5944a9f9d289da16a4b0d87c1e # v4.36.2
+        uses: github/codeql-action/init@54f647b7e1bb85c95cddabcd46b0c578ec92bc1a # v4.36.3
         with:
           languages: ${{ matrix.language }}
           # If you wish to specify custom queries, you can do so here or in a config file.
@@ -70,7 +70,7 @@ jobs:
       # Autobuild attempts to build any compiled languages  (C/C++, C#, or Java).
       # If this step fails, then you should remove it and run the build manually (see below)
       - name: Autobuild
-        uses: github/codeql-action/autobuild@8aad20d150bbac5944a9f9d289da16a4b0d87c1e # v4.36.2
+        uses: github/codeql-action/autobuild@54f647b7e1bb85c95cddabcd46b0c578ec92bc1a # v4.36.3
 
       # ℹ️ Command-line programs to run using the OS shell.
       # 📚 See https://docs.github.com/en/actions/using-workflows/workflow-syntax-for-github-actions#jobsjob_idstepsrun
@@ -83,6 +83,6 @@ jobs:
       #   ./location_of_script_within_repo/buildscript.sh
 
       - name: Perform CodeQL Analysis
-        uses: github/codeql-action/analyze@8aad20d150bbac5944a9f9d289da16a4b0d87c1e # v4.36.2
+        uses: github/codeql-action/analyze@54f647b7e1bb85c95cddabcd46b0c578ec92bc1a # v4.36.3
         with:
           category: '/language:${{matrix.language}}'

.github/workflows/docker.yml

@@ -60,7 +60,7 @@ jobs:
         suffix: ['', '-cuda', '-rocm', '-openvino', '-armnn', '-rknn']
     steps:
       - name: Login to GitHub Container Registry
-        uses: docker/login-action@650006c6eb7dba73a995cc03b0b2d7f5ca915bee # v4.2.0
+        uses: docker/login-action@c99871dec2022cc055c062a10cc1a1310835ceb4 # v4.3.0
         with:
           registry: ghcr.io
           username: ${{ github.repository_owner }}
@@ -90,7 +90,7 @@ jobs:
         suffix: ['']
     steps:
       - name: Login to GitHub Container Registry
-        uses: docker/login-action@650006c6eb7dba73a995cc03b0b2d7f5ca915bee # v4.2.0
+        uses: docker/login-action@c99871dec2022cc055c062a10cc1a1310835ceb4 # v4.3.0
         with:
           registry: ghcr.io
           username: ${{ github.repository_owner }}

mobile/ios/Runner.xcodeproj/xcshareddata/xcschemes/Runner.xcscheme

@@ -70,7 +70,8 @@
       debugDocumentVersioning = "YES"
       debugServiceExtension = "internal"
       enableGPUValidationMode = "1"
-      allowLocationSimulation = "YES">
+      allowLocationSimulation = "YES"
+      queueDebuggingEnabled = "NO">
       <BuildableProductRunnable
          runnableDebuggingMode = "0">
          <BuildableReference

mobile/lib/domain/models/asset/base_asset.model.dart

@@ -27,7 +27,6 @@ sealed class BaseAsset {
   final int? height;
   final int? durationMs;
   final bool isFavorite;
-  final String? livePhotoVideoId;
   final bool isEdited;
 
   const BaseAsset({
@@ -40,32 +39,15 @@ sealed class BaseAsset {
     this.height,
     this.durationMs,
     this.isFavorite = false,
-    this.livePhotoVideoId,
     required this.isEdited,
   });
 
   bool get isImage => type == AssetType.image;
   bool get isVideo => type == AssetType.video;
 
-  bool get isMotionPhoto => livePhotoVideoId != null;
+  bool get isMotionPhoto => playbackStyle == AssetPlaybackStyle.livePhoto;
   bool get isAnimatedImage => playbackStyle == AssetPlaybackStyle.imageAnimated;
 
-  AssetPlaybackStyle get playbackStyle {
-    if (isVideo) {
-      return AssetPlaybackStyle.video;
-    }
-    if (isMotionPhoto) {
-      return AssetPlaybackStyle.livePhoto;
-    }
-    if (isImage && durationMs != null && durationMs! > 0) {
-      return AssetPlaybackStyle.imageAnimated;
-    }
-    if (isImage) {
-      return AssetPlaybackStyle.image;
-    }
-    return AssetPlaybackStyle.unknown;
-  }
-
   Duration get duration {
     final durationMs = this.durationMs;
     if (durationMs != null) {
@@ -86,6 +68,7 @@ sealed class BaseAsset {
   String? get localId;
   String? get remoteId;
   String get heroTag;
+  AssetPlaybackStyle get playbackStyle;
 
   @override
   String toString() {

mobile/lib/domain/models/asset/local_asset.model.dart

@@ -25,7 +25,6 @@ class LocalAsset extends BaseAsset {
     super.height,
     super.durationMs,
     super.isFavorite = false,
-    super.livePhotoVideoId,
     this.orientation = 0,
     required this.playbackStyle,
     this.adjustmentTime,

mobile/lib/domain/models/asset/remote_asset.model.dart

@@ -10,6 +10,7 @@ class RemoteAsset extends BaseAsset {
   final AssetVisibility visibility;
   final String ownerId;
   final String? stackId;
+  final String? livePhotoVideoId;
   final DateTime? uploadedAt;
   final DateTime? deletedAt;
 
@@ -29,7 +30,7 @@ class RemoteAsset extends BaseAsset {
     super.isFavorite = false,
     this.thumbHash,
     this.visibility = AssetVisibility.timeline,
-    super.livePhotoVideoId,
+    this.livePhotoVideoId,
     this.stackId,
     required super.isEdited,
     this.deletedAt,
@@ -38,6 +39,23 @@ class RemoteAsset extends BaseAsset {
   @override
   String? get localId => localAssetId;
 
+  @override
+  AssetPlaybackStyle get playbackStyle {
+    if (isVideo) {
+      return AssetPlaybackStyle.video;
+    }
+    if (livePhotoVideoId != null) {
+      return AssetPlaybackStyle.livePhoto;
+    }
+    if (isImage && durationMs != null && durationMs! > 0) {
+      return AssetPlaybackStyle.imageAnimated;
+    }
+    if (isImage) {
+      return AssetPlaybackStyle.image;
+    }
+    return AssetPlaybackStyle.unknown;
+  }
+
   @override
   String? get remoteId => id;
 
@@ -90,6 +108,7 @@ class RemoteAsset extends BaseAsset {
         thumbHash == other.thumbHash &&
         visibility == other.visibility &&
         stackId == other.stackId &&
+        livePhotoVideoId == other.livePhotoVideoId &&
         uploadedAt == other.uploadedAt &&
         deletedAt == other.deletedAt;
   }
@@ -103,6 +122,7 @@ class RemoteAsset extends BaseAsset {
       thumbHash.hashCode ^
       visibility.hashCode ^
       stackId.hashCode ^
+      livePhotoVideoId.hashCode ^
       uploadedAt.hashCode ^
       deletedAt.hashCode;
 

mobile/lib/domain/services/asset.service.dart

@@ -26,6 +26,10 @@ class AssetService {
     return _localRepository.getByChecksum(checksum);
   }
 
+  Future<LocalAsset?> getLocalAsset(String id) {
+    return _localRepository.get(id);
+  }
+
   Future<RemoteAsset?> getRemoteAssetByChecksum(String checksum) {
     return _remoteRepository.getByChecksum(checksum);
   }

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

@@ -33,6 +33,7 @@ class StorageRepository {
     return file;
   }
 
+  // TODO(agg23): Unify these methods
   Future<File?> getMotionFileForAsset(LocalAsset asset) async {
     File? file;
     final log = Logger('StorageRepository');

mobile/lib/presentation/pages/drift_asset_troubleshoot.page.dart

@@ -114,7 +114,6 @@ class _AssetPropertiesSectionState extends ConsumerState<_AssetPropertiesSection
       _PropertyItem(label: 'Height', value: asset.height?.toString()),
       _PropertyItem(label: 'Duration', value: asset.durationMs != null ? '${asset.durationMs} ms' : null),
       _PropertyItem(label: 'Is Favorite', value: asset.isFavorite.toString()),
-      _PropertyItem(label: 'Live Photo Video ID', value: asset.livePhotoVideoId),
       _PropertyItem(label: 'Is Edited', value: asset.isEdited.toString()),
     ]);
   }
@@ -151,6 +150,7 @@ class _AssetPropertiesSectionState extends ConsumerState<_AssetPropertiesSection
       _PropertyItem(label: 'Thumb Hash', value: asset.thumbHash),
       _PropertyItem(label: 'Visibility', value: asset.visibility.toString()),
       _PropertyItem(label: 'Stack ID', value: asset.stackId),
+      _PropertyItem(label: 'Live Photo Video ID', value: asset.livePhotoVideoId),
     ];
 
     properties.insertAll(4, additionalProps);

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

@@ -122,9 +122,14 @@ class _NativeVideoViewerState extends ConsumerState<NativeVideoViewer> with Widg
         );
       }
 
-      if (videoAsset.hasLocal && videoAsset.livePhotoVideoId == null) {
-        final id = videoAsset is LocalAsset ? videoAsset.id : (videoAsset as RemoteAsset).localId!;
-        final file = await StorageRepository().getFileForAsset(id);
+      // Attempt to retrieve LocalAsset, falling back to remote if it cannot be found
+      final localAsset = await _localPlaybackAsset(videoAsset);
+
+      if (localAsset != null) {
+        final file = localAsset.isMotionPhoto
+            ? await StorageRepository().getMotionFileForAsset(localAsset)
+            : await StorageRepository().getFileForAsset(localAsset.id);
+
         if (!mounted) {
           return null;
         }
@@ -141,14 +146,13 @@ class _NativeVideoViewerState extends ConsumerState<NativeVideoViewer> with Widg
         );
       }
 
-      final remoteId = (videoAsset as RemoteAsset).id;
+      final remoteAsset = videoAsset as RemoteAsset;
 
       final serverEndpoint = Store.get(StoreKey.serverEndpoint);
       final isOriginalVideo = ref.read(appConfigProvider).viewer.loadOriginalVideo;
       final String postfixUrl = isOriginalVideo ? 'original' : 'video/playback';
-      final String videoUrl = videoAsset.livePhotoVideoId != null
-          ? '$serverEndpoint/assets/${videoAsset.livePhotoVideoId}/$postfixUrl'
-          : '$serverEndpoint/assets/$remoteId/$postfixUrl';
+      final String assetId = remoteAsset.livePhotoVideoId ?? remoteAsset.id;
+      final String videoUrl = '$serverEndpoint/assets/$assetId/$postfixUrl';
 
       return VideoSource.init(path: videoUrl, type: VideoSourceType.network, headers: ApiService.getRequestHeaders());
     } catch (error) {
@@ -157,6 +161,43 @@ class _NativeVideoViewerState extends ConsumerState<NativeVideoViewer> with Widg
     }
   }
 
+  Future<LocalAsset?> _localPlaybackAsset(BaseAsset baseAsset) async {
+    if (!baseAsset.hasLocal) {
+      return null;
+    }
+
+    LocalAsset? localAsset;
+
+    if (baseAsset is LocalAsset) {
+      localAsset = baseAsset;
+    } else {
+      final localId = (baseAsset as RemoteAsset).localId;
+      localAsset = localId != null ? await ref.read(assetServiceProvider).getLocalAsset(localId) : null;
+    }
+
+    if (localAsset == null) {
+      _log.severe(
+        'Invariant violation: asset ${baseAsset.name} (${baseAsset.localId}) is marked `hasLocal` but local asset could not be retrieved',
+      );
+
+      return null;
+    }
+
+    // Clients (local) may not correctly recognize a given asset as a motion photo. This allows for a scenario where both remote and local
+    // have the same asset (hash), but only the remote properly recognizes it as a motion asset
+    // If this scenario occurs, fall back to using the remote asset
+    if (baseAsset.isMotionPhoto && !localAsset.isMotionPhoto) {
+      // Platform mismatch for motion photo, use remote instead
+      _log.warning(
+        'Mismatched local and remote motion states on ${baseAsset.name} (${baseAsset.localId}), local = ${localAsset.isMotionPhoto}, remote = ${baseAsset.isMotionPhoto}',
+      );
+
+      return null;
+    }
+
+    return localAsset;
+  }
+
   void _onPlaybackReady() async {
     if (!mounted || !widget.isCurrent) {
       return;

mobile/lib/presentation/widgets/images/thumbnail_tile.widget.dart

@@ -297,8 +297,7 @@ class _AssetTypeIcons extends StatelessWidget {
 
   @override
   Widget build(BuildContext context) {
-    final remoteAsset = asset is RemoteAsset ? asset as RemoteAsset : null;
-    final isLivePhoto = remoteAsset?.livePhotoVideoId != null;
+    final isLivePhoto = asset.isMotionPhoto;
 
     return Column(
       mainAxisSize: MainAxisSize.min,

mobile/openapi/lib/model/system_config_integrity_checksum_job.dart

@@ -28,8 +28,8 @@ class SystemConfigIntegrityChecksumJob {
   /// Percentage limit of the integrity checksum job
   ///
   /// Minimum value: 0
-  /// Maximum value: 9007199254740991
-  int percentageLimit;
+  /// Maximum value: 1
+  double percentageLimit;
 
   /// How long the integrity checksum job may run for
   ///
@@ -75,7 +75,7 @@ class SystemConfigIntegrityChecksumJob {
       return SystemConfigIntegrityChecksumJob(
         cronExpression: mapValueOfType<String>(json, r'cronExpression')!,
         enabled: mapValueOfType<bool>(json, r'enabled')!,
-        percentageLimit: mapValueOfType<int>(json, r'percentageLimit')!,
+        percentageLimit: mapValueOfType<double>(json, r'percentageLimit')!,
         timeLimit: mapValueOfType<int>(json, r'timeLimit')!,
       );
     }

mobile/test/medium/repositories/timeline_repository_test.dart

@@ -70,4 +70,36 @@ void main() {
       expect((assets.first as RemoteAsset).id, asset.id);
     });
   });
+
+  group('live photos', () {
+    test('remote-only live photo contains livePhotoVideoId and is marked as a motion photo', () async {
+      final user = await ctx.newUser();
+      final asset = await ctx.newRemoteAsset(ownerId: user.id, livePhotoVideoId: 'motion-photo-1');
+
+      final assets = await sut.main([user.id], .day).assetSource(0, 10);
+
+      expect(assets, hasLength(1));
+      final remote = assets.single as RemoteAsset;
+      expect(remote.id, asset.id);
+      expect(remote.livePhotoVideoId, 'motion-photo-1');
+      expect(remote.isMotionPhoto, isTrue);
+      expect(remote.localId, isNull);
+    });
+
+    test('merged live photo resolves localId and is marked as a motion photo', () async {
+      final user = await ctx.newUser();
+      const checksum = 'shared-live-photo-checksum';
+      final asset = await ctx.newRemoteAsset(ownerId: user.id, checksum: checksum, livePhotoVideoId: 'motion-photo-2');
+      final local = await ctx.newLocalAsset(checksum: checksum);
+
+      final assets = await sut.main([user.id], .day).assetSource(0, 10);
+
+      expect(assets, hasLength(1));
+      final remote = assets.single as RemoteAsset;
+      expect(remote.id, asset.id);
+      expect(remote.livePhotoVideoId, 'motion-photo-2');
+      expect(remote.isMotionPhoto, isTrue);
+      expect(remote.localId, local.id);
+    });
+  });
 }

open-api/immich-openapi-specs.json

@@ -25917,9 +25917,10 @@
           },
           "percentageLimit": {
             "description": "Percentage limit of the integrity checksum job",
-            "maximum": 9007199254740991,
+            "format": "double",
+            "maximum": 1,
             "minimum": 0,
-            "type": "integer"
+            "type": "number"
           },
           "timeLimit": {
             "description": "How long the integrity checksum job may run for",

server/src/dtos/system-config.dto.ts

@@ -74,7 +74,12 @@ const SystemConfigIntegrityJobSchema = z
 
 const SystemConfigIntegrityChecksumJobSchema = SystemConfigIntegrityJobSchema.extend({
   timeLimit: z.int().nonnegative().describe('How long the integrity checksum job may run for'),
-  percentageLimit: z.int().nonnegative().describe('Percentage limit of the integrity checksum job'),
+  percentageLimit: z
+    .float32()
+    .nonnegative()
+    .max(1)
+    .describe('Percentage limit of the integrity checksum job')
+    .meta({ format: 'double' }),
 })
   .describe('Integrity checksum job config')
   .meta({ id: 'SystemConfigIntegrityChecksumJob' });

server/src/services/database-backup.service.ts

@@ -407,7 +407,7 @@ export class DatabaseBackupService {
         progressCb?.('restore', progress);
       });
 
-      await pipeline(sqlStream, progressSource, psql, progressSink);
+      await pipeline(sqlStream, createSqlOwnerTransformStream(databaseUsername), progressSource, psql, progressSink);
 
       try {
         progressCb?.('migrations', 0.9);
@@ -572,3 +572,69 @@ function createSqlProgressStreams(cb: (progress: number) => void) {
 
   return [source, sink];
 }
+
+function createSqlOwnerTransformStream(databaseUsername: string) {
+  const OWNER_MARKER_START = new TextEncoder().encode('OWNER TO ');
+  const DATA_MARKER_START = new TextEncoder().encode('FROM stdin');
+  const LINE_END = new TextEncoder().encode(';');
+
+  const owner = new TextEncoder().encode(databaseUsername);
+
+  let ownerSequenceIndex = 0;
+
+  let replacingOwnerIndex = 0;
+  let replacingOwner = false;
+
+  let readingDataIndex = 0;
+
+  let dataPart = false;
+
+  return new PassThrough({
+    transform(chunk, _encoding, callback) {
+      let result = chunk;
+      if (!dataPart) {
+        for (let index = 0; index < result.length; index++) {
+          if (replacingOwner) {
+            if (result[index] === LINE_END[0]) {
+              result = Buffer.concat([result.slice(0, index), owner.slice(replacingOwnerIndex), result.slice(index)]);
+              replacingOwnerIndex = owner.length;
+            } else {
+              result[index] = owner[replacingOwnerIndex];
+              replacingOwnerIndex++;
+            }
+          }
+
+          if (replacingOwnerIndex === owner.length) {
+            replacingOwner = false;
+          }
+
+          if (result[index] === OWNER_MARKER_START[ownerSequenceIndex]) {
+            ownerSequenceIndex++;
+          } else {
+            ownerSequenceIndex = 0;
+          }
+
+          if (ownerSequenceIndex === OWNER_MARKER_START.length) {
+            ownerSequenceIndex = 0;
+            replacingOwner = true;
+            replacingOwnerIndex = 0;
+          }
+
+          if (result[index] === DATA_MARKER_START[readingDataIndex]) {
+            readingDataIndex++;
+          } else {
+            readingDataIndex = 0;
+          }
+
+          if (readingDataIndex === DATA_MARKER_START.length) {
+            dataPart = true;
+            break;
+          }
+        }
+      }
+
+      this.push(result);
+      callback();
+    },
+  });
+}