Database Migrations
Audience: Developer, Deployment Administrator Related: Database Service · Sqlite And Postgresql · Application Updates
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.
Migration 237 — Plan Meals and Dietary Preferences
237_plan_meals_and_dietary_preferences.py is a provider-aware Python migration for SQLite and PostgreSQL. It adds the extensible dietary preference catalog and organization assignments/custom note, durable organization-scoped Meal Recipe Suggestions (including safe source/import hints, primary/side role, ordering, lifecycle, and Plan Meals provenance), and the separate plan_meals AI use case with seeded Economy/Standard/Enhanced profile rows. It is additive and does not rewrite existing Meal Planner entries or the single-slot plan_meal use case.
The application schema version advanced to 238 with 238_plan_meals_ui_cleanup.py. Migration 238 retires the superseded disabled Meal Plan Suggestions placeholder use case now that Plan Meals is the supported multi-date planning feature, and updates the built-in dietary catalog symbols used by the shared dietary-preferences UI. Historical migration 213 remains unchanged; the cleanup is additive migration history rather than a rewrite.
Migration 239_plan_meals_auto_added_recipe_provenance.py adds durable provenance for Recipes automatically added by Plan Meals. Migration 240_plan_meals_shopping_ready_dynamic_seed_pricing.py adds first-class Plan Meals Quality Profile pricing fields (base Seeds, included slots, additional-slot block size, and Seeds per block) plus queryable AI Usage pricing/workload telemetry used by trusted quotes, reconciliation exports, and AI Seed Cost Alignment. Existing Plan Meals profiles migrate safely from their previous fixed Seed value as the new base price; historical AI Usage rows keep the new workload fields null when that information was not recorded. Migration 241_plan_meals_find_add_seed_surcharge.py advances the application schema version to 241 and adds the per-profile Find & Add Complete Recipes surcharge. Standard starts at +4 AI Seeds from the first matched production cost comparison; other Plan Meals profiles remain at zero until their own comparable evidence supports a change.
Migration 242_recipe_source_site_tracking.py adds the platform-scoped recipe_source_site_stats table used to crowdsource Recipe website quality signals across Blackcap. The table stores a normalized domain, one query/fragment-free example URL, total touches, AI Plan Meals recommendation touches, other URL-backed Add Recipe touches, and first/last involvement timestamps. On first migration it backfills current URL-backed Recipes as historical Other Add Recipe touches; historical AI-provider recommendations are not reconstructable and therefore start from zero.
Migration 243_recipe_search_rules.py adds Blackcap-owned Recipe Search include/exclude rules used by interactive Recipe Discovery, Agent Search synchronization, and Plan Meals source verification. Migration 244_plan_meals_recipe_conversion_provenance.py adds exact Plan Meals job/Find & Add provenance used to safely offer deletion of a freshly auto-created Recipe only for the originating job and 24-hour window. Migration 245_plan_meals_original_context.py separates the canonical attached Recipe title from the AI provider's original planned name and retains bounded Plan Meals discovery context so Recipe → Meal Item → Recipe Idea corrections recover the original intent/hints. Migration 246_retire_recipe_discovery_api_keys.py advances the application schema to 246 and removes the retired Custom Search JSON API key, Agent Search API key, and Agent Search auth-mode setting. Google Programmable Search is browser/Search-Engine-ID only; Agent Search is ADC/IAM only.
Active Recipe Suggestions are Meal Planner product data and are not AI-retention artifacts.
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_..., andusr_... - 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/*.jsonbackup_state.jsonnoun_cache/NounProjectWords.csv- duplicate core-table
public_idcolumns - 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
Recent Recipe Discovery / Inventory migrations (247–250)
- 247 — regression system-admin context options: persists the regression controls used to exercise initiating-System-Admin organization-context behavior.
- 248 — inventory package expiration: adds optional physical-package freshness date fields and supporting indexes/metadata.
- 249 — USDA FoodKeeper shelf life: adds the platform FoodKeeper shelf-life catalog used for Estimated Best By calculations.
- 250 — retire Recipe Discovery search-provider master toggle: removes
recipe_discovery.search_provider_enabledfrom activesettingsandsetting_definitionswhile preserving historicalsetting_audit_logrows. Recipe Discovery enablement is represented entirely byrecipe_discovery.search_provider_type(manual_onlydisables search; a concrete provider enables it).
APP_DB_SCHEMA_VERSION is 266 and must continue to match the highest active migration. Migration 251 introduces the common durable AI execution state: ai_jobs.phase, database lease/recovery fields, progress/error checkpoints, and the child ai_job_provider_interactions table. Migration 252 registers the durable AI-assisted Create SQL Query use case and its Quality Profiles without adding another application data table. Migration 253 adds the organization-configured Meal Slot → Recipe Type mapping used by Plan Meals candidate ranking and Find & Add Recipe creation. Migration 254 adds the Blackcap-owned canonical ingredient catalog, aliases, external source mappings, and cached image provenance used by Platform → Ingredients and TheMealDB synchronization. Migration 255 adds reusable per-user standard data-table column visibility and ordering preferences. Migration 256 adds the persisted SQLite-only Periodic Database Vacuum schedule/status table; PostgreSQL records the same migration version without creating that SQLite-only table. Migration 257 adds Recipe adaptation links, independent AI job deduplication, Held original lifecycle fields, and the disabled-by-default 🪄 Adapt Recipe AI use case with configurable Quality Profiles. Migration 262 independently persists the scheduled regression synthetic/real AI-provider setting; migration 263 registers the Create Recipe AI use case and its three independent Quality Profiles, inheriting provider choices from matching Adapt Recipe profiles when configured. Migration 263 adds no new tables; migration 264 creates Recipe Memory progression and round history, and migration 265 adds saved trivia rounds, question progress, and game preferences for SQLite and PostgreSQL. Pi and GCP advance app_meta.schema_version to 266 during database initialization. Migration 259 persists regression AI Seed totals after organization cleanup; migration 260 separates the Recipe Adaptation source from ordinary Manual recipes; migration 261 repairs missing or zero bulk AI Seed pricing fields for Adapt Recipe while preserving configured nonzero base prices. It applies on SQLite and PostgreSQL. These migrations do not add a systemd worker; existing Gunicorn processes execute work while the application database arbitrates ownership and restart recovery. The Brave/Tavily search-provider adapters, Curated Recipe Search Rules filtering/pagination, provider-neutral Plan Meals classification, and generic Recipe-title fallback do not require additional database schema.
Migration 252 — Create SQL Query AI use case
252_database_sql_query_ai.py advances the application schema to 252 and registers Database Admin's durable Create SQL Query AI use case with Economy/Standard/Enhanced Quality Profiles. The use case sends the user's request plus the current provider-aware Export Schema Markdown to the configured AI provider, records durable provider interactions and AI Usage evidence, and returns validated read-only SQL to the editor without executing it. No new data table is required because the shared AI job, interaction, usage, and Seed infrastructure is reused. The SQL profiles use a SQL-specific full-schema input budget (up to 500,000 characters) rather than the smaller Plan Meal prompt ceiling; runtime normalization also raises older 65,000/120,000-character SQL profile values to a safe minimum without requiring another migration.
Migration 253 — Meal Slot Recipe Type mapping
253_meal_slot_recipe_type_mapping.py advances the application schema to 253 by adding meal_slots.recipe_type. Existing Meal Slots are backfilled conservatively from their names (for example Breakfast → Breakfast, Brunch → Breakfast, Supper → Dinner, Dessert → Dessert); after migration, the organization-owned saved mapping is authoritative. Plan Meals uses it to rank existing Recipe Library candidates for a selected Meal Slot and assigns it to a Recipe newly created by Find & Add Complete Recipes. Resolving an already-existing Recipe by URL does not silently change that Recipe's type.
Migration 258 standardizes historical 30-day AI use-case data-retention defaults to seven days, retaining all other configured retention values. Because the historical rows do not track whether 30 was explicitly selected, an explicit 30-day setting is also normalized to seven once; administrators can set it back afterward.
Migration 264 — Recipe Memory user progression
264_recipe_memory_game_progress.py creates the cross-backend recipe_memory_progress and recipe_memory_round_history tables. Recipe Memory progress is user-and-organization scoped, with a resumable saved board and validated completion history; no existing data is modified. Application schema 266 is current after the subsequent shared arcade game migration.
Migration 265 — Trivia progress and game preferences
265_games_user_progress_and_settings.py adds game_trivia_progress for saved per-question trivia completion, scoring and an active shuffled round, and game_user_settings for reusable user/organization-scoped game display preferences. Both tables support SQLite and PostgreSQL, do not modify old recipes or memory progress, and are idempotent on replay.
Migration 266 — Shared arcade, puzzle, and card progression
266_shared_arcade_progress.py adds game_shared_progress and game_shared_sessions to SQLite and PostgreSQL. Each row belongs to a user, organization and stable game ID. Active sessions keep a seed, stage, round, versioned JSON game state, score, moves, elapsed time and explicit completion/failure/abandoned status. Server revision checks block stale writes and enforce single-award outcomes. The migration is idempotent and preserves prior trivia and Recipe Memory tables and progress. No new workers or external dependencies are required. Individual game win conditions are checked on completion but full anti-cheat replay validation is not implemented.