immich-photo-flow/docs/superpowers/specs/2026-06-27-pgvector-embedding-spike-design.md

pgvector embedding feasibility spike — design

Date: 2026-06-27 Milestone dependency: unblocks M1.5 (visual similarity). See docs/ROADMAP.md. Status: passed 2026-06-27 — Req 0–4 answered (see findings doc). Final coverage snapshot pending CLIP re-run completion; see "Running the spike" / "Definition of done" below.

Why this exists

The roadmap narrative claims M1 "runs a read-only feasibility spike" and that M1.5's dependency is "verified in M1." It is not: the M1 plan explicitly deferred the spike (docs/superpowers/plans/2026-06-27-immich-photo-flow-m1.md:3710 — "pgvector spike → out of this plan by decision (tracked separately; M1.5 dependency)"). So M1.5 is blocked on a prerequisite that never ran. This spec defines that prerequisite.

M1.5 wants to cluster photos by visual similarity using the CLIP embeddings Immich already computes — the rescue signal for the GPS-poor old library where timestamp/GPS are weakest. The working assumption is that Immich's REST API does not cleanly expose raw embedding vectors, so the viable path is read-only access to Immich's Postgres pgvector embeddings. That assumption is load-bearing — it must be tested, not asserted (see Requirement 0). Before M1.5 can build clustering on that path, this spike must prove the path exists and pin its shape.

Scope

Feasibility only. Read-only. Throwaway probe. The spike:

• does not build clustering, a tested module, or any shared/ reader;
• does not write to Immich or its database (only SELECT);
• exists to turn unknowns into pinned facts and hand M1.5 a verified contract.

Requirements (pass/fail)

The spike succeeds when it answers all of the following against the live database and records the answers in the findings doc. Phrased as plain questions:

0. Does M1.5 actually need raw vectors (vs. a REST similarity query)? Before relying on DB access, state what M1.5's clustering needs — raw embedding vectors or a pairwise similarity-neighbor query — and record which Immich REST endpoints were checked (e.g. /api/search/smart, any asset-similarity/duplicate endpoint) and why each is insufficient. This turns the load-bearing "REST can't expose embeddings" premise into a documented finding; if a REST path suffices, the DB-access path below is unnecessary.
1. Can we read the CLIP embeddings at all? Connect read-only over the LAN and read the embedding vectors Immich stores. This de-risks access only: if the embeddings are not readable, M1.5 needs a different plan. It does not prove the signal is useful (see the signal-usefulness note below).
2. Can we join each embedding back to a photo we already track? Confirm inside Postgres that each embedding row's key references assets.id — the join that must hold for the vectors to be usable. That asset ID is the same key our SQLite store uses, so a cross-check against SQLite (shared/photoflow/core) is optional confirmation and must degrade gracefully when the store is unpopulated (a fresh checkout has no ingest run), rather than failing this requirement for the wrong reason.
3. What are the exact shapes? Record the table name, embedding column, vector dimension, and the correct pgvector distance operator for similarity. These are model- and version-dependent (and have drifted across Immich versions), so they must be read from this DB, not assumed — and the recorded shape is valid only for the model + Immich version observed (see Requirement 4 / Risks).
4. What is the coverage? What fraction of the embeddable library currently has an embedding. Compute against the image/embeddable population, not all assets — count(distinct embedding.assetId) / count(assets WHERE type = 'IMAGE') (or Immich's equivalent asset-type filter) — since videos and other non-image rows CLIP never embeds would otherwise deflate the ratio. Record both the raw and image-only ratios. Tells M1.5 how much it can lean on the signal — but the user is re-running CLIP with a stronger model, so coverage (and the dimension in Requirement 3) is a snapshot of whichever model is live when the probe runs.

Signal-usefulness is M1.5's first task, not this spike's. Passing Requirements 1–4 proves the embeddings are reachable and well-shaped; it does not prove visual similarity actually rescues trip detection in the GPS-poor library. Before building clustering, M1.5 must validate the signal (e.g. eyeball nearest-neighbour quality on a sample of the GPS-poor set).

What we already know (to verify, not assume)

Immich historically stores CLIP vectors in a smart_search table with an embedding column of pgvector type vector, keyed by assetId referencing assets.id; similarity uses cosine distance (<=>); the legacy default model (ViT-B-32) produced 512-dim vectors. Names and dimension have changed across versions and the user's re-run uses a stronger model, so the probe discovers these rather than trusting them — the list above is only the set of candidates to probe first.

This is Immich's undocumented, internal schema with no deprecation contract: it can be renamed or restructured on any Immich upgrade. The probe therefore records the exact Immich version observed alongside the shape, and M1.5 must budget a re-probe / version-guard on every Immich upgrade — the "contract" the findings doc hands M1.5 is version-pinned, not durable.

Approach

Disposable read-only probe + two durable artifacts (chosen over a throwaway-only or a build-the-module-now approach: feasibility-only honors the roadmap, but capturing the DSN and a written contract is the cheap part that saves M1.5 from guessing).

Connection

• New optional env var IMMICH_DB_URL (a Postgres DSN), added to .env.example and loaded by config.py as an optional field (REST creds stay required; the DSN is only needed for the spike / M1.5). config.py reads only os.environ, and the repo loads .env solely via docker-compose's env_file, so a standalone host-run probe must populate the environment itself — either run it via docker compose run (so env_file applies) or load .env explicitly first (e.g. set -a; source .env; set +a). If run in-container, confirm container-to-Immich-Postgres network reachability.
• Read-only must be enforced at the DB/session layer, not just by which statements the script issues: open the session read-only (SET default_transaction_read_only = on / SET TRANSACTION READ ONLY) or connect via a role granted only SELECT. The simplest credentials that work are Immich's existing Postgres user, but that user is write/DDL-capable against the source-of-truth DB, so the session-level guard is required to remove write capability while the spike runs. A dedicated least-privilege read-only role remains recommended hardening for M1.5.
• Driver: psycopg (psycopg3), plus the pgvector Python package. psycopg3 returns a vector column as a string unless the adapter is registered, so call pgvector.psycopg.register_vector(conn) after connect — or read the dimension server-side (SELECT vector_dims(embedding) … / catalog atttypmod) and run the <=> check in SQL, which needs no adapter. Add these to the spike's dependencies; M1.5 will formalize them.

The probe

A single disposable script, scripts/pgvector_spike.py, that is idempotent and read-only. It:

1. connects using IMMICH_DB_URL;
2. discovers candidate embedding tables/columns from the catalog (probe smart_search / embedding first, then fall back to scanning information_schema for vector-typed columns) — so it survives version drift;
3. reads one embedding and reports its dimension — via the registered pgvector adapter or server-side vector_dims(embedding), not the length of a raw string — and a sample of the distance operator working (e.g. a ... ORDER BY embedding <=> embedding LIMIT 5 self-similarity sanity check, casting literals to ::vector as needed);
4. verifies the asset-ID join inside Postgres: the embedding row's key references assets.id. As optional confirmation it also checks whether that ID is one we store in SQLite (shared/photoflow/core), degrading gracefully (not failing the check) when the store is unpopulated;
5. computes coverage against the embeddable population: count(distinct embedding.assetId) / count(assets WHERE type = 'IMAGE') (or Immich's equivalent asset-type filter), recording both the raw and image-only ratios;
6. prints a human-readable report and writes/refreshes the findings doc.

It hardcodes nothing destructive, takes no write path, and is safe to re-run.

Deliverables

1. scripts/pgvector_spike.py — disposable read-only probe (removed or left as a documented one-off after M1.5 internalizes its findings).
2. IMMICH_DB_URL in .env.example; optional immich_db_url field in config.py. Spike dependencies added: psycopg (psycopg3) and the pgvector Python package.
3. docs/superpowers/specs/2026-06-27-pgvector-embedding-findings.md — the one-page schema that becomes M1.5's contract: table, embedding column, vector dimension, distance operator, the embedding → assetId → SQLite asset join, coverage % (raw and image-only), the model and Immich version observed, and the (optional) read-only-role recipe. The doc must state that this is Immich's unsupported internal schema, valid only for the recorded model + version, and that M1.5 re-probes / version-guards on every Immich upgrade.
4. Roadmap correction: fix both false claims named in "Why this exists" — the narrative that M1 "runs a read-only feasibility spike" (→ "spike defined/pending per this spec") and M1.5's status note "verified in M1" (→ "dependency spike pending/this spec").

Running the spike

Prereqs: read access to Immich's Postgres and IMMICH_DB_URL set — put it in .env at the repo root; the probe searches upward from the working directory to find it. DSN form: postgresql://USER:PASSWORD@HOST:PORT/DBNAME (Immich defaults: user postgres, db immich; URL-encode special chars in the password). Immich's stock compose does not publish Postgres to the host, so either map its port or run where the DB is reachable on the LAN.

From the repo root (a fresh checkout / new worktree has no .venv):

python3 -m venv .venv
.venv/bin/python -m pip install -r scripts/requirements-spike.txt
.venv/bin/python scripts/pgvector_spike.py        # read-only, idempotent, safe to re-run

The probe prints a report and (re)writes the findings doc. Flags: --no-write (report only, leave the doc untouched), --sqlite PATH (optional SQLite cross-check target; skips gracefully when absent), --findings PATH (write the doc elsewhere). The DB session is opened read-only at the session layer, so the probe cannot mutate Immich even via the write-capable postgres user.

Definition of done

The spike is done once Requirements 0–4 are answered and recorded in the findings doc: REST insufficiency documented (R0), embeddings readable (R1), join to asset confirmed (R2), shape pinned — table / column / dimension / distance operator (R3), and coverage recorded both raw and image-only (R4). Coverage value does not gate done-ness — per Risks, near-zero coverage is a recorded fact, not a failure. By these criteria the spike already passed on 2026-06-27 (see the findings doc); ROADMAP.md reflects this.

Picking it up when the CLIP re-run reaches ~100%: the only remaining action is to refresh the coverage snapshot in the contract doc. Once the re-run completes, run scripts/pgvector_spike.py (no flags) so the committed findings doc records the final full-library coverage, then commit the regenerated doc. Nothing else changes — readability, the assetId → asset.id join, and the vector shape are already locked and are re-verified on every run. (If a later Immich upgrade changes the schema, the probe's catalog discovery adapts; re-run and re-commit the doc — that is M1.5's version-guard duty.)

Out of scope (explicitly M1.5)

CLIP clustering; a tested shared/photoflow/immich/db.py reader; nearest-neighbor queries at scale; any use of the embeddings beyond the feasibility checks above.

Risks

• Embeddings unreadable / not joinable → M1.5 blocked; spike's whole point is to surface this early and cheaply.
• Schema differs from the known shape → expected and handled by catalog discovery, not hardcoded names.
• Coverage near zero (re-run unfinished) → not a spike failure; recorded as a fact so M1.5 can sequence around it.