SQLite is the default Raspberry Pi database and uses WAL mode and application-controlled migrations. PostgreSQL is the supported GCP database and is normally reached through Cloud SQL Auth Proxy. Both providers use the same service interfaces while retaining provider-specific schema, date/time, locking, pooling, maintenance, and backup behavior.

Documentation and tests must not assume that a query valid on SQLite is automatically valid on PostgreSQL.

PostgreSQL behavior

Blackcap supports two first-class database deployments:

SQLite for Raspberry Pi and other local appliance-style installations.
PostgreSQL for GCP deployments, normally through Cloud SQL and the Cloud SQL Auth Proxy.

PostgreSQL support is additive. SQLite remains fully supported and keeps SQLite-specific connection, maintenance, WAL, and local-file behavior rather than inheriting unnecessary cloud-database logic.

Current status

The GCP/PostgreSQL deployment is supported for normal Blackcap operation and regression testing. The application uses a centralized database service/adapter layer so routes and feature services do not need scattered backend conditionals.

Implemented behavior includes:

Startup backend selection through [database].backend and host environment settings.
Split BLACKCAP_POSTGRES_* connection variables or a single PostgreSQL URL/DSN.
Cloud SQL Auth Proxy connectivity, including private-IP mode.
PostgreSQL connection pooling for GCP.
SQLite/PostgreSQL placeholder, row, transaction, and metadata adapters.
Current-schema PostgreSQL bootstrap for an empty database.
Cross-backend Python migrations for changes after PostgreSQL support was introduced.
Provider-aware Database Admin health, maintenance, resource browsing, SQL Console behavior, and schema export.
Live PostgreSQL type guidance for integer flags, native booleans, timestamps, JSON, joins, and UNION ALL compatibility.
PostgreSQL-compatible regression execution alongside the SQLite regression environment.

Backend selection

SQLite remains the default when no PostgreSQL configuration is present:

[database]
backend = sqlite
database_path = data/blackcap.db
sqlite_wal_mode = true
enable_fts5 = true

PostgreSQL uses:

[database]
backend = postgres
postgres_url_env = BLACKCAP_DATABASE_URL

For systemd and cloud deployments, split environment variables are preferred because passwords do not need URL encoding:

BLACKCAP_DATABASE_BACKEND=postgres
BLACKCAP_POSTGRES_HOST=127.0.0.1
BLACKCAP_POSTGRES_PORT=5432
BLACKCAP_POSTGRES_DB=blackcap
BLACKCAP_POSTGRES_USER=blackcap
BLACKCAP_POSTGRES_PASSWORD=...
BLACKCAP_POSTGRES_SSLMODE=disable

A single URL is also supported:

BLACKCAP_DATABASE_URL=postgresql://blackcap:password@127.0.0.1:5432/blackcap?sslmode=disable

When split variables and a URL are both present, the split variables take precedence. Keep credentials in /etc/blackcap/blackcap.env or another protected service environment file rather than in the repository or INI file.

Backend selection is a startup-level host decision. It is intentionally not controlled by DB-backed settings because the application must know how to connect before it can read those settings.

GCP / Cloud SQL architecture

The normal GCP path is:

Blackcap / Gunicorn
    -> PostgreSQL connection pool
    -> Cloud SQL Auth Proxy on 127.0.0.1
    -> Cloud SQL PostgreSQL

When Cloud SQL is private-IP-only, the proxy service must include --private-ip, and the VM must be attached to a network that can reach the Cloud SQL private address.

Use requirements-cloud.txt on GCP so Raspberry Pi GPIO/e-ink packages are not installed:

cd /opt/Blackcap
/opt/blackcap_env/bin/python -m pip install -r requirements-cloud.txt

See ../deployment/gcp.md for the complete GCP setup and service layout.

Connection pooling

The GCP deployment normally enables the PostgreSQL pool:

BLACKCAP_POSTGRES_POOL_ENABLED=1
BLACKCAP_POSTGRES_POOL_MIN_SIZE=1
BLACKCAP_POSTGRES_POOL_MAX_SIZE=4
BLACKCAP_POSTGRES_POOL_TIMEOUT=10
BLACKCAP_POSTGRES_POOL_CLOSE_TIMEOUT=15

Pool sizing should stay conservative unless the Cloud SQL instance and application concurrency justify increasing it. Each Gunicorn worker is a separate process, so pool limits apply per worker rather than once for the entire VM.

SQLite does not use this PostgreSQL pool and keeps its short-lived local connection model.

Schema bootstrap and migrations

A fresh PostgreSQL database is initialized from the current PostgreSQL bootstrap rather than replaying every historical SQLite migration. Historical SQL migrations remain part of the SQLite upgrade path.

New cross-backend changes should use Python migrations under database/migrations:

199_example_change.py
200_next_change.py

Each migration exposes:

def apply(context) -> None:
    context.execute("...")

The migration context provides the active backend, connection, placeholder style, execution helpers, and app metadata helpers.

Migration rules:

Migration version numbers must be unique.
APP_DB_SCHEMA_VERSION must equal the highest migration version expected by the application.
A normal application restart runs pending migrations.
Do not edit an already-released migration to repair one backend; add a new migration.
PostgreSQL startup must verify the current migration is recorded rather than trusting only a cached schema-version value.

Live PostgreSQL types

Do not assume a field uses PostgreSQL's most idiomatic native type merely because the backend is PostgreSQL. Some Blackcap columns intentionally preserve compatible physical types from the SQLite lineage.

Examples:

An integer logical flag must use = 1 / = 0, not IS TRUE / IS FALSE.
A native boolean can use TRUE, FALSE, IS TRUE, or IS FALSE.
A text-backed timestamp may need NULLIF(BTRIM(column::text), '')::timestamptz.
Text-backed JSON must be validated/cast before PostgreSQL JSON operators are used.
UNION / UNION ALL branches must return compatible types in every output position.
Do not cast matching join keys to text without a reason; unnecessary casts can prevent index use.

The SQL Console Export Schema · PostgreSQL output is generated from the live database and should be used as the source of truth when asking an LLM to write PostgreSQL queries.

Database Admin behavior

Database Admin identifies the active provider in its overview and SQL Console.

PostgreSQL Maintenance currently offers:

Verify PostgreSQL Connection
Analyze Database

PostgreSQL health can include database/schema identity, server version, database and table sizes, active connections, live/dead tuple estimates, analyze/vacuum timestamps, pool configuration, and schema version.

SQLite-only actions such as integrity checks, WAL checkpointing, and file vacuuming are not shown on PostgreSQL.

The SQL Console remains read-only and provider-aware. PostgreSQL queries must use PostgreSQL syntax, while SQLite-only PRAGMA statements are unavailable. Protected columns remain excluded from autocomplete and schema exports.

Search behavior

SQLite can use its local FTS5 search tables when enabled. PostgreSQL does not create SQLite virtual FTS tables. Feature services must use the centralized search/repository abstractions so each backend can use its supported implementation or fallback behavior.

Do not add direct FTS-table SQL to routes or general services.

Backup and recovery

Blackcap's application backup features and database-engine disaster recovery are separate concerns.

For SQLite:

The local database file and Blackcap backup workflows can be used for appliance recovery.
Restore testing should be performed periodically on a separate copy.

For PostgreSQL / Cloud SQL:

Use Cloud SQL automated backups and point-in-time recovery for full database recovery.
Test restoration into a separate Cloud SQL instance before relying on the recovery plan.
Organization export/import features remain useful for scoped application data, but they are not a substitute for a full PostgreSQL backup.
Do not treat a copied SQLite database file as a PostgreSQL backup format.

Database parity audit

The System page includes System Actions → Database Parity Audit. The audit runs as a monitored background job, so the page shows queued/running/completed/failed state, percentage progress, the current phase, duration, and the latest report.

The target database connection remains technically read-only:

SQLite uses URI mode=ro plus PRAGMA query_only;
PostgreSQL starts a READ ONLY transaction and rolls it back before returning the connection to the pool;
the fresh SQLite reference is created in an isolated child process and temporary directory;
initialization and repair are disabled during the audit.

Operational job status and the completed report are stored in background_jobs so monitoring survives page refreshes. That operational tracking is separate from the audited read-only connection.

The report can:

identify the active database and schema;
report the latest applied migration;
compare the active schema with a freshly migrated SQLite reference;
identify missing tables or columns required by runtime code;
check required display catalog rows;
check required grocery department and canonical ingredient mapping seeds;
report customized or absent Noun Project rule sets as informational differences rather than parity failures;
provide guidance and identify findings eligible for safe automatic repair.

Safe parity repair

When the report contains registered additive gaps, the System page offers Repair Safe Database Gaps as a separate confirmed background action. The repair action is intentionally narrower than a migration or generic schema synchronization. It may:

add provider-aware columns from the explicit safe-repair registry;
restore required display type/content mode catalog rows;
restore required grocery department and canonical ingredient mapping seed rows.

It never drops or renames tables/columns, deletes rows, rebuilds tables, changes primary keys, or changes Noun Project rules. Findings outside the safe registry remain manual and include migration guidance.

The same diagnostic remains available from the command line:

cd /opt/Blackcap
sudo /opt/blackcap_env/bin/python tools/audit_postgres_parity.py

Useful options include:

sudo /opt/blackcap_env/bin/python tools/audit_postgres_parity.py --json
sudo /opt/blackcap_env/bin/python tools/audit_postgres_parity.py --strict
sudo /opt/blackcap_env/bin/python tools/audit_postgres_parity.py --no-sqlite-reference

The command-line tool is diagnostic and read-only unless --initialize is explicitly supplied. Routine application startup already handles pending migrations, so that option should not be needed for ordinary deployments.

Operational verification

Useful GCP checks:

sudo systemctl status cloud-sql-proxy.service --no-pager
sudo systemctl status inky_admin.service --no-pager
sudo journalctl -u inky_admin.service -n 500 --no-pager | grep -Ei 'PostgreSQL|pool|slow|traceback|error'

Verify the proxy connection directly when needed:

PGPASSWORD="$BLACKCAP_POSTGRES_PASSWORD" psql \
  --host="${BLACKCAP_POSTGRES_HOST:-127.0.0.1}" \
  --port="${BLACKCAP_POSTGRES_PORT:-5432}" \
  --username="$BLACKCAP_POSTGRES_USER" \
  --dbname="$BLACKCAP_POSTGRES_DB" \
  --command='SELECT current_database(), current_schema(), current_user;'

Development rules

Keep database access behind the centralized service/adapter/repository boundaries.
Do not scatter backend checks through Flask routes.
Do not use SELECT * in runtime application SQL; select only the required columns.
Preserve SQLite-specific lightweight behavior instead of forcing every PostgreSQL compatibility step onto SQLite.
Add provider-specific behavior at the provider/adapter boundary.
Validate changes against both SQLite and PostgreSQL when they touch shared database code.

SQLite boundaries

Backups

Blackcap Pi backups include the primary application database:

data/blackcap.db
data/blackcap.db-wal when present
data/blackcap.db-shm when present

The backup process attempts a SQLite WAL checkpoint before collecting database files, then still includes sidecar files when they exist.

The database is now canonical for mutable backup control data:

cloud_backup_provider_tokens stores Dropbox/Google Drive provider token payloads encrypted in SQLite.
cloud_backup_state stores backup status/history by organization.
organization_backup_runs stores backup/export run history, including Default/full platform runs as backup_scope='platform'.

Legacy token/state files such as cloud_backup/tokens/*.json and backup_state.json are no longer active runtime state.

Filesystem artifacts intentionally included in backups

Backups include source/cache artifacts that are useful or expensive to regenerate:

recipe_cache/ or the active organization's scoped recipe-cache folder
noun_cache/ or the active organization's scoped downloaded-icon cache
emoji_cache/ for full/platform backups
local backup ZIP artifacts when retained by the backup destination

Shopping lists, shopping-list items, and recipe-cart state are also SQLite-only. New backups must not recreate or depend on shopping_lists/.

Noun Project rules are not stored in the icon cache folder; they live in noun_project_rules and are backed up through SQLite.

Filesystem artifacts intentionally excluded from backups

Generated display/render artifacts should not be treated as canonical backup data. They are regenerated by menu, recipe, meal-plan, receiver, and display-content push flows after restore.

Examples:

display_previews/
current_view.png
final_preview.png
recipe_preview.png
current_snippet.png
last_snippet.png
temp_full.png
ocr_preview.png
menu_crop_preview.png
cloud_backup/restore_staging/
migration_backups/
__pycache__/

Deprecated current recipe image

recipe_cache/current_recipe_image.png is deprecated and should no longer be created by recipe cache builds. Async cache generation only updates recipe-specific artifacts such as:

recipe_cache/<recipe_id>.pdf
recipe_cache/<recipe_id>.png
recipe_cache/<recipe_id>_rendered.png

Display-current state should be managed by display render/restore paths, not by background cache generation.

Auth database boundary

data/blackcap.db is the single primary SQLite database. Auth, user, membership, token, security, audit, application, configuration, backup, and Noun Project rule data now live in the same database.

Provider-aware platform backup

SQLite platform backups include the local database and available WAL sidecars. PostgreSQL platform backups contain Blackcap configuration and filesystem artifacts only; Cloud SQL automated backups and point-in-time recovery are the database recovery mechanism. Provider-specific platform backups cannot be restored across backends.

Scheduled backup due state and retention

Automatic backup due state is stored durably in the private internal service-state table and behaves the same on SQLite and PostgreSQL. Manual backups do not reset the scheduled interval. Failed scheduled backups retry after four hours, and a database lease prevents duplicate starts. Backup-history purge protects the newest configured runs per organization/scope/provider/destination plus the latest successful run.

SQLite runtime ownership

Blackcap Pi now treats SQLite as the runtime source of truth for migrated application data.

SQLite-owned runtime data

Recipes
Recipe metadata
Recipe ingredients
Editable recipe content
Recipe search / ingredient search indexes
Recipe cache status / queue state
Shopping lists
Shopping list items
Shopping list item sources
Emoji registry / picker metadata
Background jobs
App state such as recipe cart state

Filesystem-owned generated assets

Recipe PDFs
Recipe PNG previews/rendered images
Captured source images
E-ink output images
Emoji PNG/SVG assets

Legacy JSON files

Legacy JSON files may remain on disk for import/export, rollback reference, or debugging, but should not be read or written by normal runtime paths.

Use these tools for explicit imports:

/home/pi/inky_env/bin/python3 tools/sync_editable_recipes_to_sqlite.py --json
/home/pi/inky_env/bin/python3 tools/sync_emoji_cache_to_sqlite.py --json

Editable recipe content must exist in recipe_edit_content for cache rebuilds and recipe content edits. Missing DB content is now treated as a real error instead of falling back to recipe_cache/*.editable.json.

Cache Build Start Time Rule

When a cache build is queued or enters building/processing state, the top-level SQLite cache build start time must be reset to the current build start time. Older migrated metadata may contain stale cache_build_started_at values, especially for manually edited recipes, and those values must not be reused for a new build cycle.

Legacy cached recipe status repair

Older recipes that already had cached PDFs/PNGs but no editable-content row should be treated as cached, not queued. Run:

/home/pi/inky_env/bin/python3 tools/repair_cached_recipe_statuses.py --dry-run
/home/pi/inky_env/bin/python3 tools/repair_cached_recipe_statuses.py

Google Drive backup tokens are also sanitized so unused id_token values are not persisted, avoiding unattended auto-backup JWT parsing errors.

On this page