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 library

See where your data lives for how that path is resolved.

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,
    FOREIGN KEY (location_cluster_id) REFERENCES location_clusters(id)
        ON DELETE RESTRICT ON UPDATE RESTRICT
);

This is the table’s shape. Schema 3 libraries are upgraded to schema 4 on a writable open. 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
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: exif_date when present and not 0000-*, otherwise modified_at. That is the same rule videre dedupe uses to pick which copy to keep.

hash is the join key, not path

Faces, classifications and embeddings are all keyed by hash, never by path. That is what lets duplicate copies of one photo share a single face detection or embedding, and it means a file that moves keeps its work as soon as it is re-scanned.

faces

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, including IDs retained in journal and question provenance. 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

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.

marks

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

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

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

One row per tracked command, not an append-only log, so it holds the last run of each. 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
);

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.

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

Duplicate groups, largest first:

SELECT hash, COUNT(*) n, SUM(size_bytes)/1048576.0 mb
FROM 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.0
FROM (SELECT size_bytes, COUNT(*) cnt FROM file_hashes GROUP BY hash HAVING cnt > 1);

Photos per named person:

SELECT person_label, COUNT(DISTINCT hash) photos
FROM faces WHERE confirmed = 1 AND person_label IS NOT NULL
GROUP BY person_label ORDER BY photos DESC;

What is in the library, by type:

SELECT ext, COUNT(*) n, SUM(size_bytes)/1073741824.0 gb
FROM 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.

NULL and application/octet-stream mean different things

mime IS NULL means the file was never processed: no scan has read its bytes, or one started and did not finish.

mime = 'application/octet-stream' means it was read and its type could not be identified. That is a finished result, not a failure.

The distinction is what keeps an incremental scan from retrying the same unidentifiable file on every run forever. When deciding how to handle a file, videre treats the sentinel exactly like NULL and falls back to the extension, so an unrecognised file is still processed normally.

-- genuinely unfinished
SELECT COUNT(*) FROM file_hashes WHERE mime IS NULL;
-- read, but unidentifiable
SELECT COUNT(*) FROM file_hashes WHERE mime = 'application/octet-stream';

Enforced relationships

videre turns PRAGMA foreign_keys = ON on every connection it opens itself, so these four relationships are checked by SQLite, not just by the application:

• faces.person_label references people(name)
• file_hashes.location_cluster_id references location_clusters(id)
• face_learning_event_faces.event_id references face_learning_events(id)
• face_learning_question_faces.question_id references face_learning_questions(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 and invalidates their teaching evidence first; when the location clusters are recomputed, the file references are cleared before the old clusters are removed.

Two relationships are deliberately not foreign keys, because their parents are not the rows a child belongs to:

• file_hashes.hash is 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 one file_hashes row must not cascade into the others.
• face_learning_event_faces.face_id and the question equivalent are historical provenance: they record which faces an action was about. Prune keeps those references even when the face row is removed, but marks an affected event ineligible with source_face_pruned. This preserves the journal without allowing missing source content to train a new profile.

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

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.