[Mobile] Permanent FOREIGN KEY constraint failure syncing assets from external library after pre-sync reset

### I have searched the existing issues, both open and closed, to make sure this is not a duplicate report. - [x] Yes ### The bug **Describe the bug** After upgrading server + mobile app to `v3.1.0` (matching versions), the mobile app permanently fails to sync with `SqliteException(787): FOREIGN KEY constraint failed` on `remote_asset_entity.library_id`. The error is always triggered by the same asset (the first one processed after a sync reset), and sync never progresses past it — a full logout + app reinstall does not fix it. Diagnosis points to a client-side gap: assets belonging to an **external library** fail to sync because the local `library` row isn't (re-)populated before the app tries to insert assets that reference it, after a local pre-sync reset runs. Since my entire library is external, this blocks mobile sync completely with no workaround. Web app is unaffected. **To Reproduce** 1. Have an account with an external library attached (mine has ~9,700 assets). 2. Trigger a local mobile DB reset (happened for me via a large version upgrade; also reproduced via full logout + reinstall). 3. Log back in and let sync run. 4. Sync fails immediately and permanently on the first asset belonging to the external library, with the same FK error on every retry. **Expected behavior** Sync should populate the local `library` row before syncing assets that reference it, or otherwise not fail permanently on assets belonging to external libraries. **Diagnostics ruling out a server-side issue** Verified directly against Postgres: - The referenced `library` row exists and is valid. - The referenced `owner_id` (user) exists. - No orphaned assets referencing missing libraries or owners (`0` in both checks). - `updateId` (UUIDv7) ordering is correct — the library's `updateId` is chronologically earlier than the asset's, ruling out a simple backfill/ordering bug server-side. - Server logs show no error at all for this — the server considers the sync request successful; the failure happens purely client-side after the response, when writing into the local SQLite DB. **Mobile log** Repeats identically on every sync attempt (~5 times over an hour, always the same asset): ``` severe | DriftSyncStreamRepository | Error: updateAssetsV2 - user | SqliteException(787): while executing statement, FOREIGN KEY constraint failed, constraint failed (code 787) Causing statement: INSERT INTO "remote_asset_entity" (..., "owner_id", ..., "library_id", ...) VALUES (...) ON CONFLICT("id") DO UPDATE SET ... #0 throwException (package:sqlite3/src/implementation/exception.dart:101) #1 StatementImplementation._execute (package:sqlite3/src/implementation/statement.dart:80) #2 StatementImplementation.executeWith (package:sqlite3/src/implementation/statement.dart:302) #3 CommonPreparedStatement.execute (package:sqlite3/src/statement.dart:81) #4 _LeasedContext._executeBatch.<anonymous closure> (package:sqlite_async/src/native/database/native_sqlite_database.dart:411) #5 _LeasedContext._wrapDbClosure.<anonymous closure> (package:sqlite_async/src/native/database/native_sqlite_database.dart:396) #6 new Future.sync (dart:async/future.dart:325) #7 WorkItem.handle.<anonymous closure> (package:sqlite_async/src/native/database/worker.dart:80) ``` Preceding context — pre-sync reset tasks run just before the first failure, with no corresponding library reset task listed: ``` info | SplashScreenPage | Missing crucial offline login info - Logging out completely info | SyncStreamService | Remote sync request for user info | SyncStreamService | Running pre-sync task: v20260701_ResetAlbumsV1 info | SyncStreamService | Running pre-sync task: v20260128_ResetExifV1 info | SyncStreamService | Running pre-sync task: v20260128_ResetAssetV1 info | SyncStreamService | Running pre-sync task: v20260597_ResetAssetV1AssetV2 info | SyncStreamService | Updated pre-sync migration status: [v20260701_ResetAlbumsV1, v20260128_ResetExifV1, v20260128_ResetAssetV1, v20260128_CopyExifWidthHeightToAsset, v20260597_ResetAssetV1AssetV2] ``` **Versions** - Server: v3.1.0 - Mobile: v3.1.0 (matching) - Platform: Android - Deployment: Docker - 1 external library configured (~9,700 assets) **Additional context** Given the ongoing "library unification" refactor (e.g. #27818, #27666, discussion #28342), this looks like a transitional gap in the mobile sync/reset logic around re-populating the local `library` table for external libraries before syncing dependent assets. Happy to provide further diagnostics (DB and log access available). ### The OS that Immich Server is running on Debian GNU/Linux 12 (bookworm) ### Version of Immich Server 3.1.0 ### Version of Immich Mobile App 3.1.0 ### Platform with the issue - [ ] Server - [ ] Web - [x] Mobile ### Device make and model Samsung S26+ ### Your docker-compose.yml content ```YAML # # WARNING: To install Immich, follow our guide: https://immich.app/docs/install/docker-compose # # Make sure to use the docker-compose.yml of the current release: # # https://github.com/immich-app/immich/releases/latest/download/docker-compose.yml # # The compose file on main may not be compatible with the latest release. name: immich services: immich-server: container_name: immich_server image: ghcr.io/immich-app/immich-server:${IMMICH_VERSION:-release} # extends: # file: hwaccel.transcoding.yml # service: cpu # set to one of [nvenc, quicksync, rkmpp, vaapi, vaapi-wsl] for accelerated transcoding volumes: # Do not edit the next line. If you want to change the media storage location on your system, edit the value of UPLOAD_LOCATION in the .env file - ${UPLOAD_LOCATION}:/usr/src/app/upload - /etc/localtime:/etc/localtime:ro - /srv/dev-disk-by-uuid-31fd3266-adce-4a82-a5b8-402c9a485163/Photos:/srv/dev-disk-by-uuid-31fd3266-adce-4a82-a5b8-402c9a485163/Photos:ro env_file: - Immich.env ports: - '2283:2283' depends_on: - redis - database restart: always healthcheck: disable: false immich-machine-learning: container_name: immich_machine_learning # For hardware acceleration, add one of -[armnn, cuda, rocm, openvino, rknn] to the image tag. # Example tag: ${IMMICH_VERSION:-release}-cuda image: ghcr.io/immich-app/immich-machine-learning:${IMMICH_VERSION:-release} # extends: # uncomment this section for hardware acceleration - see https://immich.app/docs/features/ml-hardware-acceleration # file: hwaccel.ml.yml # service: cpu # set to one of [armnn, cuda, rocm, openvino, openvino-wsl, rknn] for accelerated inference - use the `-wsl` version for WSL2 where applicable volumes: - model-cache:/cache env_file: - Immich.env restart: always healthcheck: disable: false redis: container_name: immich_redis image: docker.io/valkey/valkey:8-bookworm@sha256:42cba146593a5ea9a622002c1b7cba5da7be248650cbb64ecb9c6c33d29794b1 healthcheck: test: redis-cli ping || exit 1 restart: always database: container_name: immich_postgres image: pgvector/pgvector:pg16 environment: POSTGRES_PASSWORD: ${DB_PASSWORD} POSTGRES_USER: ${DB_USERNAME} POSTGRES_DB: ${DB_DATABASE_NAME} POSTGRES_INITDB_ARGS: '--data-checksums' volumes: - ${DB_DATA_LOCATION}:/var/lib/postgresql/data healthcheck: test: >- pg_isready --dbname="$${POSTGRES_DB}" --username="$${POSTGRES_USER}" || exit 1; Chksum="$$(psql --dbname="$${POSTGRES_DB}" --username="$${POSTGRES_USER}" --tuples-only --no-align --command='SELECT COALESCE(SUM(checksum_failures), 0) FROM pg_stat_database')"; echo "checksum failure count is $$Chksum"; [ "$$Chksum" = '0' ] || exit 1 interval: 5m start_interval: 30s start_period: 5m command: >- postgres -c shared_preload_libraries=vector -c 'search_path="$$user", public, vectors' -c logging_collector=on -c max_wal_size=2GB -c shared_buffers=512MB -c wal_compression=on restart: always volumes: model-cache: ``` ### Your .env content ```Shell # You can find documentation for all the supported env variables at https://immich.app/docs/install/environment-variables # The location where your uploaded files are stored UPLOAD_LOCATION=/srv/dev-disk-by-uuid-31fd3266-adce-4a82-a5b8-402c9a485163/Photos/Immich/library # The location where your database files are stored. Network shares are not supported for the database DB_DATA_LOCATION=/srv/dev-disk-by-uuid-31fd3266-adce-4a82-a5b8-402c9a485163/Photos/Immich/postgres_new # To set a timezone, uncomment the next line and change Etc/UTC to a TZ identifier from this list: https://en.wikipedia.org/wiki/List_of_tz_database_time_zones#List # TZ=Etc/UTC # The Immich version to use. You can pin this to a specific version like "v1.71.0" IMMICH_VERSION=release # Connection secret for postgres. You should change it to a random password # Please use only the characters `A-Za-z0-9`, without special characters or spaces DB_PASSWORD=********* # The values below this line do not need to be changed ################################################################################### DB_USERNAME=postgres DB_DATABASE_NAME=immich ``` ### Reproduction steps 1. 2. 3. ... ### Relevant log output ```shell ``` ### Additional information _No response_