bug(server): column reference "id" is ambiguous in AssetDetectDuplicates job

[tluj]

I have searched the existing issues, both open and closed, to make sure this is not a duplicate report.

• Yes

The bug

Every upload fails the AssetDetectDuplicates job with PostgresError: column reference "id" is ambiguous. 28 out of 28 uploads hit this. Uploads, thumbnails, and ML processing all work fine, only duplicate detection is broken.

The cause is an unqualified 'id' in the .select() array of getForSearchDuplicatesJob in server/src/repositories/asset-job.repository.ts (line 33). The query LEFT JOINs asset with smart_search, and since VectorChord added an integer id PK to smart_search, PostgreSQL can't tell which table's id is meant.

This is the same class of bug fixed in PR #18433 (@mertalev, May 2025), which qualified "assetId" in a different JOIN in the same file. At that time smart_search had no id column so this JOIN was safe. Related closed issues: #18437, #18716.

The OS that Immich Server is running on

Fedora 42

Version of Immich Server

v2.5.6

Version of Immich Mobile App

v2.5.6 build.246

Platform with the issue

• Server
• Web
• Mobile

Device make and model

No response

Your docker-compose.yml content

Not using Docker Compose. Running containers via Podman (rootless). The relevant container images are:

Server: ghcr.io/immich-app/immich-server:release
Database: ghcr.io/immich-app/postgres:14-vectorchord0.3.0-pgvectors0.3.0

Your .env content

Not applicable (Podman, no .env file). Standard Immich configuration, nothing custom that would affect query generation.

Reproduction steps

1. Upload any photo or video
2. Check server logs
3. Every upload produces: Unable to run job handler (AssetDetectDuplicates): PostgresError: column reference "id" is ambiguous

Relevant log output

[Nest] 2  - 02/14/2026, 9:42:21 AM   ERROR [Microservices:{"source":"upload","id":"30d01795-1db3-4fee-847d-6bd65452831f"}] Unable to run job handler (AssetDetectDuplicates): PostgresError: column reference "id" is ambiguous

Additional information

Generated SQL showing the ambiguity:

select
  "id",              -- ambiguous: asset.id (UUID) vs smart_search.id (integer)
  "type",
  "ownerId",
  "duplicateId",
  "stackId",
  "visibility",
  "smart_search"."embedding"
from
  "asset"
  left join "smart_search" on "asset"."id" = "smart_search"."assetId"
where
  "asset"."id" = $1::uuid

smart_search schema:

  Column   |    Type     | Nullable |                 Default
-----------+-------------+----------+------------------------------------------
 id        | integer     | not null | nextval('smart_search_id_seq'::regclass)
 assetId   | uuid        | not null |
 embedding | vector(768) |          |

Extensions: vchord 0.3.0, vector 0.8.0.
Fix is one word: change 'id' to 'asset.id' on line 33 of 'asset-job.repository.ts, then regenerate with sync:sql. Same pattern as #18433. I have a PR ready.

[tluj]

Already worked on a fix, PR incoming.

[tluj]

Sorry to waste anyone time with this, especially to @mertalev who was right and this is a local DB issue. The id column on smart_search predates my oldest available pg_dump and isn't from any Immich migration, VectorChord, or reindexing code path. Likely introduced during an early setup or restore I don't have records of.

Apologies for the noise, and thanks for the quick review.