Blackcap migrations are ordered, idempotent, provider-aware, and executed before Gunicorn workers start. tools/run_database_migrations.py serializes schema initialization and bounded backfill work. Migrations must be safe to rerun and must not depend on one deployment’s data.

Retired or superseded migrations remain documented through the migration registry and Git history; ordinary application updates should not require manual ad hoc SQL.

Migration registry

Blackcap migration numbers are ordered identifiers, not a promise that every number will exist. Gaps such as 094 and 146-159 may remain when work is abandoned or consolidated before release. Do not add empty migrations solely to fill a gap.

Active migration versions must be unique for each database backend. The migration loader rejects duplicate active versions before applying anything.

Retired duplicate migrations

retired_migrations/011_scoped_configuration_foundation.sql is an obsolete pre-release draft. Migration 010_scoped_configuration_foundation.sql is the stable scoped-configuration foundation, and 011_configuration_audit_log.sql is the active version 011 migration. The retired draft creates conflicting table and column shapes, including the old scoped_settings table, and must never be replayed.

A second historical duplicate existed at version 119. 119_organization_transfer_invite_recovery.sql remains the active historical version 119 migration. The skipped 119_x_auth_email_scope.sql is retired, and its intended cross-backend data repair is applied by 207_x_auth_email_scope.py.

The loader ignores both retired filenames if they remain in database/migrations on a copy-based deployment. New deployments should remove those old active-directory copies.

SQL migration backend rules

Historical .sql migrations are SQLite-only. PostgreSQL schema changes should use provider-aware Python migrations. A small SQL-only PostgreSQL migration must use the explicit *_postgres.sql suffix. The runner rejects a migration sent to the wrong backend rather than allowing SQLite metadata or syntax to fail later.

Serialized startup migrations

Migration 210_startup_migration_coordination.py moves the remaining API-token organization backfill and auth schema-version marker into the migration stream. Production systemd units run tools/run_database_migrations.py through ExecStartPre before Gunicorn starts. SQLite initialization also uses the same filesystem lock internally so manual starts and older service files cannot run schema work concurrently in multiple workers.

Migration 209 creates the compact regression performance sample table but does not scan historical step rows. The pre-start runner backfills missing samples for at most the most recent 25 completed runs, one run per short transaction.

Completed SQLite migration status

Blackcap Pi has moved primary runtime application state from JSON/CSV/filesystem-backed configuration into SQLite.

SQLite is canonical for

Core entity IDs and relationships, using prefixed text primary keys such as org_..., dvc_..., disp_..., dcli_..., conn_..., pcfg_..., and usr_...
Recipes
Recipe edit content
Recipe ingredients
Recipe search / FTS
Ingredient search / FTS
Recipe cache status and cache queue state
Shopping lists
Shopping list items
Shopping list item sources
Meal Planner settings, slots, entries, and shopping-list links
Let’s Cook sessions, details, timers, display state, and history
Background jobs
Cloud-backup provider tokens, backup state/history, and backup run records
Noun Project keyword/rule configuration
Emoji registry and emoji picker metadata
Emoji asset availability/path metadata
Scoped settings and setting audit log
Users, memberships, API tokens, auth-provider links, invites, and audit data
Schema migrations

Filesystem remains canonical for generated artifacts

These should stay on disk and should not be stored as BLOBs in SQLite:

Recipe PDFs
Recipe PNG previews/rendered images
Captured source images
E-ink/display output images
display_previews/ render artifacts
Emoji monochrome/color image assets
Downloaded Noun Project icon images in noun_cache/
Backup ZIP artifacts
Cloud upload/download staging files
Restore staging files
Other binary caches

Legacy file-backed runtime state

These are no longer active runtime state:

cloud_backup/tokens/*.json
backup_state.json
noun_cache/NounProjectWords.csv
duplicate core-table public_id columns
numeric-to-text ID alias tables

Migrations 086 through 091 removed runtime ID compatibility tables, removed redundant public ID columns, moved cloud backup state/tokens into SQLite, backfilled Default/platform backup run history, moved Noun Project rules into SQLite, and added purge defaults for backup run metadata/status history plus restore staging cleanup.

JSON/CSV may still exist for export/debug/bootstrap

JSON or CSV references may still exist for:

Legacy import or restore paths before a migration runs
Rollback/reference
Optional export/debug tooling
Generated editable recipe cache artifacts when intentionally exported
Emoji registry/picker export/bootstrap files when intentionally enabled
Attribution/license output such as generated Noun Project attribution CSVs

Normal runtime recipe, shopping list, cache queue, emoji picker, backup state/token, and Noun Project rule behavior should use SQLite first.

Useful commands

/home/pi/inky_env/bin/python3 tools/init_database.py
/home/pi/inky_env/bin/python3 tools/sync_emoji_cache_to_sqlite.py --json
/home/pi/inky_env/bin/python3 tools/audit_json_runtime_usage.py
/home/pi/inky_env/bin/python3 tools/verify_database.py

Notes

tools/audit_json_runtime_usage.py is intentionally conservative. It reports JSON references for review and does not modify files.

To intentionally export legacy emoji JSON files for distribution/debugging, run:

BLACKCAP_EMOJI_EXPORT_JSON=1 /home/pi/inky_env/bin/python3 emoji_asset_cache.py --refresh-registry --export-json

On this page