Cameron CordesandClaude Opus 5.5
044240037a
photo_versions links a version's content_hash to its primary original's; photo_version_references keeps provenance-only reference photos; photo_version_display holds the global display choice. VersionDao covers linking (chains flatten to the root), the display choice, per-original summaries for /photos, the hidden-row set for non-SQL listings, and the copy of the original's date/GPS/camera onto version rows. Also the SQL predicate listings use to hide a version while its original is reachable. Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
42 lines
1.8 KiB
SQL
42 lines
1.8 KiB
SQL
-- Photo versions (#191): an AI restoration, upscale or edit that sits on
|
|
-- disk near its original and is shown as the *same photo*.
|
|
--
|
|
-- The link is hash-keyed like every other derived table (CLAUDE.md →
|
|
-- "Multi-library data model"): a version records the blake3
|
|
-- `content_hash` of its primary original, so the link survives moves,
|
|
-- renames and an archive handoff. File name and location are hints only.
|
|
-- The version keeps its own `image_exif` row, so it still downloads and
|
|
-- thumbnails on its own; listings hide it while its original is reachable
|
|
-- in the current view (same rule as `duplicate_of_hash`).
|
|
|
|
-- A file is a version of at most one original.
|
|
CREATE TABLE photo_versions (
|
|
version_hash TEXT PRIMARY KEY NOT NULL,
|
|
original_hash TEXT NOT NULL,
|
|
kind TEXT NOT NULL, -- restored | upscale | edit
|
|
source TEXT NOT NULL, -- xmp | name | backfill | api
|
|
created_at BIGINT NOT NULL,
|
|
-- Denormalized back-ref to a live image_exif row for the version, kept
|
|
-- current by the back-ref refresh pass (same pattern as face_detections).
|
|
library_id INTEGER NOT NULL,
|
|
rel_path TEXT NOT NULL
|
|
);
|
|
CREATE INDEX idx_photo_versions_original ON photo_versions(original_hash);
|
|
|
|
-- Reference photos (extra context fed to the model). Provenance only: a
|
|
-- reference is never hidden or grouped because of the version.
|
|
CREATE TABLE photo_version_references (
|
|
version_hash TEXT NOT NULL,
|
|
reference_hash TEXT NOT NULL,
|
|
PRIMARY KEY (version_hash, reference_hash)
|
|
);
|
|
CREATE INDEX idx_photo_version_references_ref ON photo_version_references(reference_hash);
|
|
|
|
-- The global (not per-user) choice of which file represents a photo.
|
|
-- No row = show the original.
|
|
CREATE TABLE photo_version_display (
|
|
original_hash TEXT PRIMARY KEY NOT NULL,
|
|
display_hash TEXT NOT NULL,
|
|
decided_at BIGINT NOT NULL
|
|
);
|