root@immich:~# docker exec -i immich_postgres psql --dbname=immich --username=postgres \d assets Table "public.assets" Column | Type | Collation | Nullable | Default ------------------+--------------------------+-----------+----------+----------------------------------- id | uuid | | not null | uuid_generate_v4() deviceAssetId | character varying | | not null | ownerId | uuid | | not null | deviceId | character varying | | not null | type | character varying | | not null | originalPath | character varying | | not null | fileCreatedAt | timestamp with time zone | | not null | fileModifiedAt | timestamp with time zone | | not null | isFavorite | boolean | | not null | false duration | character varying | | | encodedVideoPath | character varying | | | ''::character varying checksum | bytea | | not null | livePhotoVideoId | uuid | | | updatedAt | timestamp with time zone | | not null | now() createdAt | timestamp with time zone | | not null | now() originalFileName | character varying | | not null | sidecarPath | character varying | | | thumbhash | bytea | | | isOffline | boolean | | not null | false libraryId | uuid | | | isExternal | boolean | | not null | false deletedAt | timestamp with time zone | | | localDateTime | timestamp with time zone | | not null | stackId | uuid | | | duplicateId | uuid | | | status | assets_status_enum | | not null | 'active'::assets_status_enum updateId | uuid | | not null | immich_uuid_v7() visibility | asset_visibility_enum | | not null | 'timeline'::asset_visibility_enum Indexes: "PK_da96729a8b113377cfb6a62439c" PRIMARY KEY, btree (id) "IDX_16294b83fa8c0149719a1f631e" btree ("livePhotoVideoId") "IDX_2c5ac0d6fb58b238fd2068de67" btree ("ownerId") "IDX_4d66e76dada1ca180f67a205dc" btree ("originalFileName") "IDX_8d3efe36c0755849395e6ea866" btree (checksum) "IDX_9977c3c1de01c3d848039a6b90" btree ("libraryId") "IDX_asset_id_stackId" btree (id, "stackId") "IDX_assets_duplicateId" btree ("duplicateId") "IDX_assets_update_id" btree ("updateId") "IDX_f15d48fa3ea5e4bda05ca8ab20" btree ("stackId") "IDX_originalPath_libraryId" btree ("originalPath", "libraryId") "UQ_assets_owner_checksum" UNIQUE, btree ("ownerId", checksum) WHERE "libraryId" IS NULL "UQ_assets_owner_library_checksum" UNIQUE, btree ("ownerId", "libraryId", checksum) WHERE "libraryId" IS NOT NULL "idx_asset_file_created_at" btree ("fileCreatedAt") "idx_local_date_time" btree ((("localDateTime" AT TIME ZONE 'UTC'::text)::date)) "idx_local_date_time_month" btree ((date_trunc('MONTH'::text, ("localDateTime" AT TIME ZONE 'UTC'::text)) AT TIME ZONE 'UTC'::text)) "idx_originalfilename_trigram" gin (f_unaccent("originalFileName"::text) gin_trgm_ops) Foreign-key constraints: "FK_16294b83fa8c0149719a1f631ef" FOREIGN KEY ("livePhotoVideoId") REFERENCES assets(id) ON UPDATE CASCADE ON DELETE SET NULL "FK_2c5ac0d6fb58b238fd2068de67d" FOREIGN KEY ("ownerId") REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE "FK_9977c3c1de01c3d848039a6b90c" FOREIGN KEY ("libraryId") REFERENCES libraries(id) ON UPDATE CASCADE ON DELETE CASCADE "FK_f15d48fa3ea5e4bda05ca8ab207" FOREIGN KEY ("stackId") REFERENCES asset_stack(id) ON UPDATE CASCADE ON DELETE SET NULL Referenced by: TABLE "asset_faces" CONSTRAINT "FK_02a43fd0b3c50fb6d7f0cb7282c" FOREIGN KEY ("assetId") REFERENCES assets(id) ON UPDATE CASCADE ON DELETE CASCADE TABLE "albums" CONSTRAINT "FK_05895aa505a670300d4816debce" FOREIGN KEY ("albumThumbnailAssetId") REFERENCES assets(id) ON UPDATE CASCADE ON DELETE SET NULL TABLE "assets" CONSTRAINT "FK_16294b83fa8c0149719a1f631ef" FOREIGN KEY ("livePhotoVideoId") REFERENCES assets(id) ON UPDATE CASCADE ON DELETE SET NULL TABLE "asset_job_status" CONSTRAINT "FK_420bec36fc02813bddf5c8b73d4" FOREIGN KEY ("assetId") REFERENCES assets(id) ON UPDATE CASCADE ON DELETE CASCADE TABLE "albums_assets_assets" CONSTRAINT "FK_4bd1303d199f4e72ccdf998c621" FOREIGN KEY ("assetsId") REFERENCES assets(id) ON UPDATE CASCADE ON DELETE CASCADE TABLE "shared_link__asset" CONSTRAINT "FK_5b7decce6c8d3db9593d6111a66" FOREIGN KEY ("assetsId") REFERENCES assets(id) ON UPDATE CASCADE ON DELETE CASCADE TABLE "memories_assets_assets" CONSTRAINT "FK_6942ecf52d75d4273de19d2c16f" FOREIGN KEY ("assetsId") REFERENCES assets(id) ON UPDATE CASCADE ON DELETE CASCADE TABLE "activity" CONSTRAINT "FK_8091ea76b12338cb4428d33d782" FOREIGN KEY ("assetId") REFERENCES assets(id) ON UPDATE CASCADE ON DELETE CASCADE TABLE "asset_stack" CONSTRAINT "FK_91704e101438fd0653f582426dc" FOREIGN KEY ("primaryAssetId") REFERENCES assets(id) TABLE "exif" CONSTRAINT "FK_c0117fdbc50b917ef9067740c44" FOREIGN KEY ("assetId") REFERENCES assets(id) ON DELETE CASCADE TABLE "asset_files" CONSTRAINT "FK_e3e103a5f1d8bc8402999286040" FOREIGN KEY ("assetId") REFERENCES assets(id) ON UPDATE CASCADE ON DELETE CASCADE TABLE "tag_asset" CONSTRAINT "FK_f8e8a9e893cb5c54907f1b798e9" FOREIGN KEY ("assetsId") REFERENCES assets(id) ON UPDATE CASCADE ON DELETE CASCADE TABLE "smart_search" CONSTRAINT "smart_search_assetId_fkey" FOREIGN KEY ("assetId") REFERENCES assets(id) ON DELETE CASCADE Triggers: assets_delete_audit AFTER DELETE ON assets REFERENCING OLD TABLE AS old FOR EACH STATEMENT WHEN (pg_trigger_depth() = 0) EXECUTE FUNCTION assets_delete_audit() assets_updated_at BEFORE UPDATE ON assets FOR EACH ROW EXECUTE FUNCTION updated_at() select * from version_history; id | createdAt | version --------------------------------------+-------------------------------+--------- 187c6cb7-4f5c-4db9-9143-23d293bc787c | 2024-10-03 06:20:41.069076+00 | 1.117.0 ccb2313a-b5c3-4f91-8afe-72de3a0ac82e | 2024-10-17 06:23:32.589488+00 | 1.118.2 f3ec3915-fda2-4ef5-852e-a239b810eac6 | 2024-11-25 18:49:47.939349+00 | 1.121.0 944cb901-8539-4a34-81cc-e9bbf1d0cc9d | 2024-12-11 21:07:49.475136+00 | 1.122.2 e1cdbe4c-2d31-4981-9945-7ee6aa26d0d5 | 2025-01-11 00:46:42.590644+00 | 1.124.2 eba39a86-23d8-45aa-b8d2-d8c21edd41d6 | 2025-01-30 23:15:50.905739+00 | 1.125.7 2d43539c-28f8-415f-8338-7de94c0e9d1b | 2025-03-08 04:59:26.452685+00 | 1.129.0 97c40fb0-3435-4811-95f8-a9b964633bc8 | 2025-04-05 07:36:11.828057+00 | 1.131.3 898687ff-1726-4182-aea0-16e730c83bcc | 2025-06-01 18:59:13.59319+00 | 1.134.0 9373ee8c-fc98-4056-bdce-df70b45c95ad | 2025-08-30 16:16:09.836283+00 | 1.131.3 (10 rows)