The database
Everything videre knows lives in one ordinary SQLite file. No proprietary format, no daemon holding it open, nothing you need videre itself to read. If you want to answer a question videre has no command for, write the query.
sqlite3 .videre/hashes.db # run from inside the librarySee where your data lives for how that path is resolved.
file_hashes
Section titled “file_hashes”One row per file path. This is what videre scan writes and
what nearly everything else reads.
CREATE TABLE file_hashes ( path TEXT PRIMARY KEY, hash TEXT NOT NULL, meta_hash TEXT, size_bytes INTEGER, created_at TEXT, modified_at TEXT, ext TEXT, mime TEXT, phash INTEGER, exif_date TEXT, gps_lat REAL, gps_lon REAL, width INTEGER, height INTEGER, duration_secs REAL, codec TEXT, location_name TEXT, location_cluster_id INTEGER, xmp_sidecar_mtime TEXT, capture_date TEXT, date_source TEXT, gps_source TEXT, dims_oriented INTEGER, FOREIGN KEY (location_cluster_id) REFERENCES location_clusters(id) ON DELETE RESTRICT ON UPDATE RESTRICT);This is the table’s shape at schema 6. A schema 3, 4 or 5 library is upgraded
in place on a writable open, with no other videre command running in it;
schema 5 adds the three date and GPS source columns and schema 6 adds
dims_oriented, both filled by the next scan without rehashing. Older
libraries with incompatible content keys are refused:
remove their .videre directory and run videre scan.
| Column | Notes |
|---|---|
path |
Absolute path. The primary key, so re-scanning updates in place |
hash |
Identity of the file’s content: BLAKE3 over the file with its metadata left out (EXIF, XMP, comments and similar). The same image with different metadata shares one hash |
meta_hash |
BLAKE3 over the metadata alone; changes when only metadata does. NULL for a file whose format is not parsed, whose hash is then the whole file’s |
mime |
Detected from the file’s leading bytes, not its name |
phash |
Near-duplicate fingerprint (64-bit dHash), written by embed. NULL until then, and again after the file’s content changes |
exif_date |
Camera-local, no timezone. 0000-* values are discarded as absent |
capture_date |
The one date for the file, a local wall clock (YYYY-MM-DDTHH:MM:SS), from the first of: EXIF, a video’s Apple creation date, a Google Takeout sidecar, a video’s mvhd time, the file’s modification time |
date_source |
Where capture_date came from: exif, video, sidecar, mvhd or mtime. NULL until the next scan resolves it |
gps_source |
Where the coordinates came from: exif, or sidecar for a Takeout sidecar’s geoData. NULL with no GPS |
width, height |
The size the file is shown at: the stored pixels turned by the file’s rotation mark (a HEIC’s irot, the EXIF orientation, a video’s display matrix), so a portrait photo a phone stored landscape is taller than wide. A gallery rotation swaps them |
dims_oriented |
1 once width and height are the shown size. NULL on a row stored before that, which the next scan corrects from the file’s header without rehashing |
location_name |
Filled in lazily, not by scan. See watch --location |
location_cluster_id |
Set by videre locations |
created_at is always empty on Linux; the birth time needs a macOS syscall.
Date filters on videre search do not match exif_date
directly. They match the effective date: capture_date, and for a row not
yet resolved, exif_date when present and not 0000-*, otherwise
modified_at. That is the same rule
videre dedupe uses to pick which copy to keep.
One row per detected face, written by videre faces.
CREATE TABLE faces ( id INTEGER PRIMARY KEY AUTOINCREMENT, hash TEXT NOT NULL, bbox TEXT NOT NULL, landmark TEXT, embedding BLOB NOT NULL, cluster_id INTEGER, person_label TEXT, confirmed INTEGER DEFAULT 0, is_primary INTEGER DEFAULT 0, det_score REAL, blur REAL, oriented INTEGER, FOREIGN KEY (person_label) REFERENCES people(name) ON DELETE RESTRICT ON UPDATE RESTRICT);bbox and landmark are JSON. embedding is a 512-dimension ArcFace vector
stored as raw f16, so 1024 bytes. cluster_id is assigned by grouping;
person_label and confirmed are what the labeling UI writes, and
search --person reads.
Face IDs never reuse a deleted ID. Schema 4 reserves historical IDs during
its one-time upgrade from schema 3.
oriented is always 1: bbox and landmark are measured on the display
canvas, the orientation a person sees.
CREATE TABLE people ( name TEXT PRIMARY KEY, full_name TEXT NOT NULL);A person has two names. name is who they are: lowercase, ASCII, spaces as
underscores, punctuation removed. It is what faces.person_label holds and what
appears in the URL, /people/person/isil_ozyegin. full_name is what you see:
exactly what was typed, so Işıl Özyeğin keeps its spelling.
That split is why Alice and alice are one person rather than two, and why
adding a surname later changes nothing but the label on screen.
Accented letters are folded rather than dropped, so Şefik becomes sefik, not
efik. Names differing only in case or accent therefore share an identity, which
is the intent; the primary key on name is what makes that guaranteed rather
than merely likely.
Libraries created before this table are migrated on the first run of a command
that writes faces, keeping the original spelling as full_name.
CREATE TABLE faces_scanned ( hash TEXT PRIMARY KEY, scanned_at TEXT DEFAULT (datetime('now')));This is what makes face detection resumable. Every processed image is recorded
here including images with no faces, which produce no faces row at all.
Without it, every photo of a landscape would be re-examined on every run.
classifications
Section titled “classifications”Written by videre classify.
CREATE TABLE classifications ( model_id TEXT NOT NULL, hash TEXT NOT NULL, category TEXT NOT NULL, confidence REAL NOT NULL, classified_at TEXT NOT NULL, PRIMARY KEY (model_id, hash));category is photo, screenshot, document, meme, or unknown. The key
includes model_id, so two models can classify the same
library without overwriting each other.
Written by videre mark, the gallery, and scan/watch
when they read XMP. One row per marked photo; an unmarked photo has no row, and
a row left with no marks is deleted rather than kept empty.
CREATE TABLE marks ( hash TEXT PRIMARY KEY, rating INTEGER, pick INTEGER, label TEXT, liked INTEGER NOT NULL DEFAULT 0, updated_at TEXT NOT NULL);rating is 0-5 (0 is stored as NULL, meaning unrated). pick is 1 keep / 0
reject / NULL. label is a colour name. liked is a boolean. Keyed by hash,
so a mark follows a photo across duplicates and moves, and
videre prune drops a row once no path references its hash.
Only rating and label are portable to XMP; pick and liked stay here.
photo_tags
Section titled “photo_tags”Free-form tags, written by videre tag and by scan/watch
when they read dc:subject keywords from XMP. One row per (photo, tag) pair,
keyed by hash.
CREATE TABLE photo_tags ( hash TEXT NOT NULL, tag TEXT NOT NULL, PRIMARY KEY (hash, tag));A tag is one flat string; hierarchy is not modelled. Tags round-trip through XMP
dc:subject, so videre export --xmp writes them back out
for digiKam or Lightroom.
location_clusters and geocode_cache
Section titled “location_clusters and geocode_cache”CREATE TABLE location_clusters ( id INTEGER PRIMARY KEY, centroid_lat REAL NOT NULL, centroid_lon REAL NOT NULL, name TEXT, photo_count INTEGER NOT NULL, radius_km REAL NOT NULL, created_at TEXT NOT NULL);
CREATE TABLE geocode_cache ( query TEXT PRIMARY KEY, lat REAL NOT NULL, lon REAL NOT NULL, resolved_at TEXT NOT NULL);videre locations rebuilds location_clusters from
scratch on every run, so id is not stable between runs.
geocode_cache is the one table written by a network call: it remembers place
names looked up by search --location so a repeated query
never repeats the request.
pipeline_runs
Section titled “pipeline_runs”One row per tracked command, not an append-only log, so it holds the last run
of each; earlier runs are kept in pipeline_run_history. This is what videre stats reports.
CREATE TABLE pipeline_runs ( command TEXT PRIMARY KEY, started_at TEXT NOT NULL, finished_at TEXT, status TEXT NOT NULL, duration_ms INTEGER, summary TEXT, history_id INTEGER);Times are UTC. history_id is the run’s own entry in pipeline_run_history,
when it was kept there, so status does not list it twice.
status is stored as running, success, failed or interrupted. A fifth
value, crashed, is never written: it is computed when reading, when a row says
running but no live process holds that command’s lock.
pipeline_run_history
Section titled “pipeline_run_history”The finished runs of each command, newest kept: every run is copied here when it
ends, and only the newest run_history (default 3) per command remain. Runs of
a stage by videre watch are not copied: watch reruns its stages whenever
files change, and those runs would push out the long ones. A
running row found when the next run starts belonged to a run that died, and is
copied here as crashed. summary holds a failure’s message or
13 file(s) skipped.
CREATE TABLE pipeline_run_history ( id INTEGER PRIMARY KEY AUTOINCREMENT, command TEXT NOT NULL, started_at TEXT NOT NULL, finished_at TEXT, status TEXT NOT NULL, duration_ms INTEGER, summary TEXT);decode_failures
Section titled “decode_failures”Files a stage tried and could not turn into pixels, per stage (embed,
faces, thumbnail). A file is skipped once fail_count reaches 2, so one
that hangs QuickLook stops costing its timeout on every run; any success clears
its row. kind is why it failed, an error kind:
a decode_failed file is skipped for good, and any other failure, a timeout
above all, only for a day after last_failed_at, then tried again.
embed --reprocess tries embed’s skipped files at once.
videre status reports these files as skipped.
CREATE TABLE decode_failures ( hash TEXT NOT NULL, stage TEXT NOT NULL, error TEXT NOT NULL, fail_count INTEGER NOT NULL DEFAULT 1, last_failed_at TEXT DEFAULT (datetime('now')), kind TEXT, PRIMARY KEY (hash, stage));pixel_signatures
Section titled “pixel_signatures”A small greyscale copy of an image, kept to confirm that two files of different
sizes are one picture resized (dedupe --kind resized).
Made only for candidates, and forgotten when the image is rotated.
CREATE TABLE pixel_signatures ( hash TEXT PRIMARY KEY, luma BLOB NOT NULL, width INTEGER NOT NULL, height INTEGER NOT NULL);library_state
Section titled “library_state”Small bookkeeping values the library keeps about itself, such as the GPS
fingerprint the last videre locations ran on and how
long the last regroup took. Internal; nothing to edit.
CREATE TABLE library_state ( key TEXT PRIMARY KEY, value INTEGER NOT NULL);The embeddings database
Section titled “The embeddings database”Embeddings are not in the main file. Each library and model pair gets its
own database under <library>/.videre/embeddings/, attached when needed:
CREATE TABLE embeddings ( hash TEXT PRIMARY KEY NOT NULL, model_id TEXT NOT NULL, embedding BLOB NOT NULL, embedded_at TEXT NOT NULL);embedding is an L2-normalized f16 vector. Search models
explains the layout and why it is split out.
To query it alongside the main database, attach it yourself:
ATTACH DATABASE '.videre/embeddings/<owner>--<model>.db' AS emb;SELECT COUNT(*) FROM emb.embeddings;Useful queries
Section titled “Useful queries”Duplicate groups, largest first:
SELECT hash, COUNT(*) n, SUM(size_bytes)/1048576.0 mbFROM file_hashes GROUP BY hash HAVING n > 1 ORDER BY mb DESC;Total space wasted by duplicates, in MB:
SELECT SUM(size_bytes * (cnt - 1))/1048576.0FROM (SELECT size_bytes, COUNT(*) cnt FROM file_hashes GROUP BY hash HAVING cnt > 1);Photos per named person:
SELECT person_label, COUNT(DISTINCT hash) photosFROM faces WHERE confirmed = 1 AND person_label IS NOT NULLGROUP BY person_label ORDER BY photos DESC;What is in the library, by type:
SELECT ext, COUNT(*) n, SUM(size_bytes)/1073741824.0 gbFROM file_hashes GROUP BY ext ORDER BY n DESC;Files scanned but never given a type, meaning a scan did not finish them:
SELECT COUNT(*) FROM file_hashes WHERE mime IS NULL;Those are what an incremental scan picks up.
Enforced relationships
Section titled “Enforced relationships”videre turns PRAGMA foreign_keys = ON on every connection it opens itself,
so these two relationships are checked by SQLite, not just by the
application:
faces.person_labelreferencespeople(name)file_hashes.location_cluster_idreferenceslocation_clusters(id)
Deleting a parent that rows still reference fails with a constraint error rather than silently orphaning children. When you delete a person, videre unassigns their faces first; when the location clusters are recomputed, the file references are cleared before the old clusters are removed.
One relationship is deliberately not a foreign key, because its parent is not the row a child belongs to:
file_hashes.hashis not a key into anything: many derived rows (embeddings, decode failures, classification results) key on the hash, and one hash can appear on several paths. Deleting onefile_hashesrow must not cascade into the others.
Writing to it yourself
Section titled “Writing to it yourself”videre opens every connection in WAL mode, so one writer and many readers
coexist. You can safely run sqlite3 queries while
videre watch is running.
Reading is entirely safe. If you write, note that videre assumes hash is a
the content key videre computes for the file at path, and videre prune
deletes embeddings and cached thumbnails whose hash no longer appears in
file_hashes. Deleting rows by hand therefore discards the derived work for
those photos too. With foreign keys enforced, a DELETE of a person that
faces still reference, or of a location cluster that files still point at,
fails with a foreign-key constraint error: unassign or clear the children
first, the way videre’s own writers do.
Video columns
Section titled “Video columns”duration_secs (seconds, fractional) and codec (the container’s format tag,
such as avc1 or hvc1) are populated for video and left empty for images.
Both arrived in v0.14.0, so rows written by an earlier version have them empty
until the library is scanned again.