Database

This page describes the server's storage: the two SQLite databases, every table and which migration made it, how migrations run, and the conventions to follow when you change the schema. It is for developers who add a table or a column, or who need to read the data directly. When you finish it you will be able to write a migration that fits in, and know what happens on a server when the code and the database disagree.

Two databases and some files

var/                       FRAME_VAR_DIR  (default: next to app/; set var_dir in config.php)
├── frames.db              Your decisions. The web user writes it; cron opens it read-only
├── sessions/              Admin sessions (directory mode 0700)
├── device.key             Signing key for trusted-device cookies (mode 0600)
├── login.lock             Lock that serializes password hashing
├── kiosk/<sha256>.apk     Kiosk releases, named by hash
├── notes/<sha256>.<ext>   Card covers and stamps, named by hash
└── photos/
    ├── files/<xx>/<sha256>.jpg and .thumb.jpg   Stored photos and thumbnails
    ├── tmp/               Uploads and decoder scratch space (cleaned automatically)
    └── .ingest.lock       One writer at a time

cache/                     FRAME_CACHE_DIR
├── cache.db               Fetched data. Only cron writes it; the web user opens it read-only
└── fetch.lock             Keeps two fetcher runs apart

Back up var/ (it holds frames.db, the photos, the card art and the kiosk releases) and keep the backup private. cache/ can be rebuilt by the fetcher.

Every connection turns on PRAGMA foreign_keys = ON and waits up to 5 seconds for a lock. The code never switches to write-ahead logging, so the default rollback journal is used. That keeps a read-only reader working without write access to a side file.

Check-then-write sequences run inside db_tx(), which opens the transaction with BEGIN IMMEDIATE so that no other writer can slip in between the check and the write.

How migrations run

Migrations are the numbered files web/app/migrations/NNN_name.sql. bin/migrate.php applies them.

flowchart TD
    start["php app/bin/migrate.php<br/>run as the web user"] --> read["Read PRAGMA user_version<br/>from frames.db, creating it if needed"]
    read --> loop{"Another file<br/>numbered above it?"}
    loop -->|yes| tx["BEGIN<br/>run the whole file<br/>PRAGMA user_version = N<br/>COMMIT"]
    tx -->|"error"| fail["ROLLBACK<br/>print the error, exit 1"]
    tx -->|"ok"| loop
    loop -->|no| check{"user_version equals<br/>SCHEMA_VERSION?"}
    check -->|no| bad["Exit 1:<br/>code and files disagree"]
    check -->|yes| warn["Ask each widget's checks hook<br/>print warnings"]

• The version of the database is SQLite's user_version. There are no down-migrations and no record of which file did what beyond that number.
• Each file runs in one transaction together with the version bump. SQLite's schema changes are transactional, so a failed migration leaves the database as it was.
• SCHEMA_VERSION in app/bootstrap.php is the number the code expects. It must equal the highest migration.
• Web requests never migrate. On the first connection of a request, db_frames() compares user_version with SCHEMA_VERSION. A mismatch throws SchemaMismatch, and the visitor sees 503 with Retry-After: 30 and {"error": "maintenance", "code": "maintenance"}. After a deploy that adds a migration, the site answers 503 until you run php app/bin/migrate.php. Frames keep their current page and retry.
• The command-line tools (user.php, kiosk-release.php) refuse to run on a mismatched database and tell you to run migrate.php first.
• migrate.php runs with umask(0027) so new files are not world-readable, and needs the var directory to exist.

Add a migration

1. Create NNN_short_name.sql with the next number. Never edit a migration that has been applied anywhere.
2. Write SQL only. A migration cannot call PHP.
3. Raise SCHEMA_VERSION in app/bootstrap.php to NNN.
4. Update tests/migrate_test.php (it checks the version and what each migration does). fresh_db() in the test runner applies every file in order, so every test sees the new schema.
5. If existing rows need a value, backfill in the same file, as migrations 005, 007 and 011 did.
6. Run the tests and PHPStan.

A widget that needs its own table must add a core migration today; widgets cannot ship migrations of their own.

Conventions

Tables, by migration

001 init (the original schema)

002 to 008 (frames grow)

009 to 011 (cards and widget order)

012 admin_users (the built-in login)

admin_users: username (unique; GLOB '[a-z0-9]*', 1 to 64 characters), password_hash, created_at, password_changed_at, last_login_at, session_epoch (16 characters; changes to sign the user out everywhere), totp_secret (base32, null when two-step sign-in is off) and totp_last_step (the replay guard). Users are different from card senders. See Security.

013 kiosk_releases (self-update)

014 photo_library (local photos)

Stored photos live in var/photos/files/, named by file. Several albums may share one file; it is deleted only when no row refers to it.

cache.db (created by the fetcher)

How the tables relate

erDiagram
    frames ||--o{ frame_widgets : "has"
    frames ||--o| frame_screenshots : "latest capture"
    frames ||--o| frame_photo_sets : "today's set"
    frames ||--o{ frame_photo_shown : "shown so far"
    frames ||--o| frame_notes : "card settings"
    frames ||--o{ note_senders : "card links"
    frames ||--o{ note_cards : "receives"
    frames ||--o{ kiosk_downloads : "fetched"
    frames }o--o| kiosk_releases : "target"
    kiosk_releases ||--o{ kiosk_downloads : "fetched by"
    note_senders ||--o{ note_cards : "sent"
    note_designs ||--o{ note_cards : "cover"
    note_stamps ||--o{ note_cards : "stamp"
    photo_albums ||--o{ photos : "contains"
    photo_albums ||--o{ photo_drop_links : "has"
    photo_drop_links ||--o{ photos : "brought"
    locations }o--o{ frame_widgets : "referenced in config"
    calendars }o--o{ frame_widgets : "referenced in config"
    admin_users
    rate_events

Two relationships are not foreign keys. A widget's config JSON names a location or calendar by id, and the application checks that on save and before it deletes a Library record (409 in_use). A frame's local-album setting names an album by id the same way.

Rate-limit buckets

rate_events is one table for every limit. A bucket is a name; the limit counts rows with that name inside a window. rate_purge() deletes rows older than a day, and several request paths call it.

The address key is the IPv4 address, or the IPv6 /64 as <16 hex>::/64.

Reading the data by hand

Open the file read-only, and do it as a user who may read it (the web user):

sudo -u www-data sqlite3 -readonly /srv/frame/var/frames.db \
  "SELECT id, name, status, kiosk_version_code FROM frames;"

Do not write to frames.db while the site is up. If you must, stop the web server first, or use the admin, which holds the right locks. Never copy the live file with cp and expect a consistent copy; use sqlite3 … ".backup '<file>'".

Related

• Developer start: run the tests and a development copy
• API reference: the endpoints that read and write these tables
• Writing a widget: frame_widgets and the settings JSON
• Install the frame server: create the database and set its permissions