This page documents the PostgreSQL database schema used by the Immich server: the key tables and their purposes, how schema definitions are organized in TypeScript, the vector extension setup for ML embeddings, migration management, and the session management architecture.
Immich uses PostgreSQL with Kysely as the type-safe SQL query builder. The database interface is accessed through the DB TypeScript type exported from server/src/schema/index.ts, which aggregates all table definitions server/src/repositories/search.repository.ts10
All repository classes receive a Kysely<DB> instance injected via the @InjectKysely() decorator server/src/repositories/search.repository.ts3 server/src/repositories/search.repository.ts209
| Layer | Technology |
|---|---|
| Database engine | PostgreSQL server/src/utils/database.ts49-58 |
| Query builder | Kysely server/src/repositories/search.repository.ts2 |
| Dialect | PostgresJSDialect server/src/utils/database.ts49 |
| Connection | createPostgres from @immich/sql-tools server/src/utils/database.ts50 |
Sources: server/src/repositories/search.repository.ts1-210 server/src/utils/database.ts47-75
Schema definitions are organized as TypeScript interfaces representing table structures. These definitions reside in server/src/schema/tables/ and are mapped to the DB type server/src/schema/index.ts1
Key Table Definitions:
| Table Name | DTO/Type Reference | Description |
|---|---|---|
asset | Asset server/src/database.ts109-124 | Core asset records (photos, videos, live photos) |
asset_exif | Exif server/src/database.ts240 | Metadata extracted from assets (GPS, camera info, file size) |
asset_face | AssetFace server/src/database.ts257-273 | Bounding boxes and references for detected faces |
person | Person server/src/database.ts242-255 | Aggregated identity for grouped faces |
album | Album server/src/database.ts193-195 | User-created and shared collections |
user | UserAdmin server/src/database.ts135-147 | User accounts, quotas, and admin status |
session | Session server/src/database.ts228-238 | Active authentication sessions and device details |
smart_search | N/A | Vector storage for CLIP embeddings server/src/queries/search.repository.sql209-211 |
Sources: server/src/database.ts1-303 server/src/queries/search.repository.sql1-212
The following diagram maps the logical search and authentication flows to the underlying database entities and service classes.
Search and Auth Flow Mapping
Sources: server/src/services/auth.service.ts58-76 server/src/services/search.service.ts143-183 server/src/repositories/search.repository.ts264-280 server/src/queries/search.repository.sql187-198
Immich utilizes vector extensions to perform high-dimensional similarity searches.
Smart search utilizes the <=> (cosine distance) operator to rank assets based on their proximity to a query embedding server/src/queries/search.repository.sql197
The SearchRepository implements several specialized search patterns:
inner join "asset_exif" to filter by camera properties like lensModel server/src/queries/search.repository.sql35-38asset with smart_search to find assets matching a CLIP embedding server/src/queries/search.repository.sql187-189Sources: server/src/queries/search.repository.sql33-242 server/src/repositories/search.repository.ts208-280
Authentication state is persisted across three primary tables: user, session, and api_key.
The session table tracks active logins, including device metadata and OAuth tokens.
deviceOS, deviceType, and appVersion server/src/database.ts233-235oauthBearerToken is stored to facilitate backchannel logouts server/src/services/auth.service.ts81-83hasElevatedPermission) and sync reset flags server/src/database.ts199 server/src/database.ts237Sources: server/src/database.ts22-29 server/src/database.ts75-82 server/src/database.ts126-147 server/src/database.ts197-200 server/src/database.ts228-238
UQ_assets_owner_checksum prevents the same user from uploading the same file twice server/src/utils/database.ts98-102video_stream_session_pkey manages active transcoding sessions server/src/utils/database.ts99-106The codebase includes utility functions to build complex SQL fragments:
asVector: Casts number arrays to the PostgreSQL vector type server/src/utils/database.ts84withExif: A query builder plugin that left-joins the EXIF table and converts the result to JSON server/src/utils/database.ts118-120withSmartSearch: Configures the database session (e.g., vchordrq.probes) before executing vector queries server/src/utils/database.ts204-206Sources: server/src/utils/database.ts78-206
Refresh this wiki
This wiki was recently refreshed. Please wait 5 days to refresh again.