Stax Digital Garden

EMB-1007 - SQLite Metadata Architecture for Household Media Archives

2 min read

Living Document Notice
Published 2026-09-16. The evolving architecture and revisions for this dispatch live in the Stax Digital Garden.

SQLite Metadata Architecture for Household Media Archives

SQLite Metadata Architecture for Household Media Archives: Warm sepia-white phosphor P4/P20 vector CRT macro showing relational SQLite database schema tables linked by foreign key vectors

Operating a separate PostgreSQL daemon for a personal media archive adds unnecessary resident memory overhead and backup complexity. Embers stores all application state in a single SQLite database configured with write-ahead logging and strict foreign key integrity.

The Single-File Database Substrate

Operating independent database servers consumes hundreds of megabytes of resident memory and requires specialized backup dumping utilities. For private household media libraries comprising 50,000 to 200,000 photos, SQLite provides unmatched reliability and operational simplicity.

Embers runs SQLite in WAL (Write-Ahead Logging) mode with PRAGMA synchronous = NORMAL. This configuration allows simultaneous concurrent web readers while background ingest workers commit new albums.

[Concurrent Web Reader 1] ---> [Shared Memory (.shm)] ---> [WAL Buffer (.wal)]
[Concurrent Web Reader 2] ---> [Shared Memory (.shm)]           |
                                                                v
[Ingest Worker Writer]    --------------------------> [Main Database File]

Pragmas and Relational Schema Definition

The database connection initialization script enforces strict durability and foreign key cascades:

-- Core operational pragmas for Embers database connection
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA foreign_keys = ON;
PRAGMA busy_timeout = 5000;
PRAGMA cache_size = -64000; -- 64MB memory cache
 
CREATE TABLE albums (
    id TEXT PRIMARY KEY,
    title TEXT NOT NULL,
    description TEXT,
    created_at INTEGER NOT NULL,
    updated_at INTEGER NOT NULL,
    cover_media_id TEXT
);
 
CREATE TABLE media_albums (
    media_id TEXT NOT NULL,
    album_id TEXT NOT NULL,
    position INTEGER NOT NULL DEFAULT 0,
    PRIMARY KEY (media_id, album_id),
    FOREIGN KEY(media_id) REFERENCES media_items(id) ON DELETE CASCADE,
    FOREIGN KEY(album_id) REFERENCES albums(id) ON DELETE CASCADE
);

Integrity Verification Command

Run SQLite structural checks during automated maintenance windows:

# Verify SQLite B-Tree and index integrity
$ sqlite3 /var/lib/embers/data.sqlite3 "PRAGMA quick_check;"
ok
 
# Backup live database using native online backup API
$ sqlite3 /var/lib/embers/data.sqlite3 ".backup /mnt/backup/embers-$(date +%F).sqlite3"
  • Directus Target: embers
  • Garden Source Reference: MOC - The Digital Necropolis and Cold Decadal Storage, MOC - Bosun PKM Tools
  • Garden Source Reference: sqlite-metadata-architecture-for-household-media-archives, sqlite-wal, database-durability, embers-data

Graph View