All application database work should use the centralized database service, repositories, transactions, and provider adapters. Queries must use explicit column lists; SELECT * is not allowed. Organization scope must be applied deliberately rather than inferred from a global default.

SQLite and PostgreSQL compatibility requires portable SQL or provider-specific adapter behavior. Public IDs are stable application identifiers; internal row IDs and database-specific implementation details should not leak into user-facing workflows.

Database architecture

Blackcap has moved from mostly file-backed runtime state toward a provider-aware operational data model. SQLite supports Raspberry Pi/appliance deployments and PostgreSQL supports GCP deployments. The shared service and adapter boundaries provide safer maintenance, search/index behavior, background jobs, auditability, organization isolation, and multi-worker operation.

Current direction

Runtime database-backed areas include:

recipes, recipe edit content, recipe ingredients, recipe tags, and recipe search/FTS data
meal planner settings, slots, entries, review links, and generated shopping-list provenance
shopping lists, shopping-list items, shopping-list item sources, Household List provider-source rows, and external shopping-list sync metadata
Let’s Cook sessions, session details, timers, display state, and history summaries
background jobs and scoped job/status metadata
app state
emoji registry/cache metadata
cache statuses
authentication, users, invites, memberships, API tokens, and audit data
cloud-backup provider token payloads, backup status/history, and backup run history
organization-scoped Noun Project footer rules
scoped configuration/settings and setting audit rows

Generated binary/cache artifacts still live on disk. Examples include recipe PDFs/images, display preview images, Noun Project downloaded icon images, emoji image assets, local backup ZIPs, cloud upload/download staging files, and restore staging files.

Core identifier model

Core entity primary keys are now prefixed public text IDs. Runtime code should use the primary key directly and should not depend on old numeric IDs or duplicate public_id columns.

organizations.id       = org_...
devices.id             = dvc_...
displays.id            = disp_...
display_clients.id     = dcli_...
connections.id         = conn_...
provider_configs.id    = pcfg_...
users.id               = usr_...

Cleanup migration 086 removed the temporary numeric-to-text alias tables. Cleanup migration 087 removed redundant public_id columns from the core tables above.

recipe_shares.share_public_id remains intentionally separate because it is an external share-link identifier, not a duplicate table primary key.

Schema management

Schema changes live under:

database/migrations/

Database utility scripts live under:

tools/init_database.py
tools/verify_database.py
tools/vacuum_database.py

Recent cleanup/data-location migrations:

086_drop_legacy_id_alias_tables.sql
087_drop_redundant_public_id_columns.sql
088_cloud_backup_db_state_and_tokens.sql
089_backfill_platform_backup_runs.sql
090_noun_project_rules_db.sql
091_backup_and_restore_purge_policies.sql

Admin service architecture

The Database Admin UI follows the same modular Admin UI direction as the rest of Blackcap Pi:

Blueprint route
  → service layer
  → resource registry / action registry
  → database provider
  → SQLite

Templates should not directly query the database or enforce permissions by themselves.

Resource-oriented administration

The admin UI is resource-oriented rather than table-oriented. Resources define display columns, filters, primary keys, safe editable fields, organization scoping, hidden/masked fields, and required permissions. This reduces the risk of exposing raw database tables in unsafe ways.

Secure resources such as backup provider tokens, backup state JSON, provider configuration secrets, password hashes, token hashes, connection token references, and secret setting values should be masked or hidden in Database Admin and managed through purpose-built UI flows.

Backup data boundaries

Backup ZIP artifacts remain filesystem/cloud artifacts. Mutable backup control data is database-backed:

cloud_backup_provider_tokens stores Dropbox/Google Drive OAuth token payloads encrypted in SQLite.
cloud_backup_state stores active status/history by organization.
organization_backup_runs stores relational backup/export run history. In the Default organization, it also includes full/platform backup runs with backup_scope='platform'.

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

Noun Project rules

Noun Project keyword/rule configuration lives in noun_project_rules and is organization-scoped. The noun_cache/ folder remains the downloaded icon image cache only. Legacy noun_cache/NounProjectWords.csv is not active runtime configuration.

Meal Planner data

Meal Planner uses database-backed, organization-scoped tables for settings, slots, entries, and optional shopping-list provenance links. The service layer owns all reads/writes and keeps route handlers thin. Recipe-backed entries reference recipes by active organization plus recipe id, and manual meal items are stored as ingredient-style candidates so they can participate in shopping-list canonical review.

Meal Planner does not introduce file-backed state for planning data. Generated meal-plan review PDFs are transient output and should not become the source of truth.

Household / External Shopping List data

The Household List is represented as a normal organization-scoped shopping list header with list_kind='household' and is_builtin=1. Its visible items are stored in shopping_list_items so existing shopping-list rendering and Shop With flows can include them.

External provider association state is split intentionally:

shopping_list_items.external_source_* fields are compact rollup fields for the primary active provider source used by card/list display.
household_list_item_external_sources is the authoritative source-association table. It allows one Household item to be associated with multiple provider connections, such as the same item appearing in both Google Keep and Amazon/Alexa.
household_list_browser_bridge_imports stores attended extension import run history.
external_shopping_lists, external_shopping_list_items, shopping_list_external_item_links, and external_shopping_sync_runs remain available for provider-neutral/API-style external list integrations and future official providers.

When services reconcile external lists, they should update the Household item and all source rows through the service layer. Route handlers should not write these tables directly.

Recipe cleanup metadata

Cleanup rating is stored as recipe metadata rather than a separate table. Import/capture and recipe cache rebuild generate automatic cleanup fields, while Made It feedback can override the visible rating by setting cleanup_source='made_it'. Automatic details such as pots, pans, tools, mess factors, reducers, and summary should be preserved when Made It changes the displayed rating.

SQLite and PostgreSQL compatibility

SQLite-specific and PostgreSQL-specific behavior must remain isolated in the provider/service layer. UI templates and normal feature logic must not depend on raw backend-specific SQL details.

The provider abstraction is a current production boundary: SQLite remains fully supported for Pi/appliance use, while PostgreSQL is supported for GCP. New features must preserve both unless the feature is explicitly and correctly documented as provider-specific.

Direct SQL audit

Blackcap now includes a manual direct SQL audit for System Admins. The audit is intended to keep normal feature code from bypassing the database service/repository layer while Blackcap maintains its current SQLite/PostgreSQL data-access boundary.

Goal

The target rule is:

No direct SQL in normal application feature logic outside approved persistence boundaries, and no SELECT * / alias.* projections in executable application SQL.

The goal is not zero SQL everywhere. SQL is expected in persistence boundaries, migrations, SQL console/database inspection tools, developer/admin tooling, and tests. Runtime application queries must name their columns explicitly. COUNT(*) remains valid because it does not fetch every row column.

Explicit projection rule

Runtime application SQL must not use SELECT * or alias.*, including inside otherwise approved repository and database-service modules. Use a purpose-specific explicit column list, or the centralized database_column_registry.columns_sql() helper when a full-row compatibility projection is genuinely required.

This prevents schema additions—especially JSON, HTML, request/response, image, and other large fields—from silently increasing database reads and Python object size on existing pages. The pytest guard scans executable application Python, and the System-page audit reports wildcard projections as Needs refactor even when the SQL is inside an approved persistence boundary.

Approved boundaries

The scanner classifies SQL in these areas as approved:

inky_admin/services/database_service.py and other database/admin inspection services
inky_admin/services/*_db_service.py
repository modules under inky_admin/repositories/
existing domain persistence services under inky_admin/services/*_service.py
auth, MFA, settings, organization, config-push, data-purge, regression harness, support, subscription, recipe-share, and similar persistence services
database migrations under database/migrations/
tests and regression fixtures
isolated developer/admin tools under tools/

The broad service allowlist reflects Blackcap's current architecture. It should be narrowed over time as domain SQL moves from mixed domain services into repositories or *_db_service.py modules, while preserving both SQLite and PostgreSQL behavior.

Refactors included with the first audit pass

The first pass moved direct SQL out of two normal feature/UI helpers:

recipe_edit_utils.py now delegates editable recipe persistence to inky_admin/services/recipe_edit_content_db_service.py.
inky_admin/blueprints/user_management.py now asks auth_service.auth_provider_presence_by_user_ids() for batched linked-provider state instead of querying auth_provider_accounts directly.

System page action

System Admins can run the audit manually:

System -> Direct SQL Audit -> Run Direct SQL Audit

The audit runs as a tracked background job. The System page shows queued/running/completed/failed status, progress percentage, progress messages, duration, and the latest result. The page remains usable while the scan runs and automatically refreshes the result when the job reaches a terminal state.

The result view includes:

formatted last-run duration and scanned-file count;
formatted approved, needs-refactor, unclear, and total finding counts;
grouped finding details;
copyable JSON output.

Status and the latest result are stored in the existing background_jobs operational table and follow the normal background-job retention policy. No separate audit-history table is created.

Command-line usage

No standalone scripts/ command is included. The intended production entry point is the System page action. For development-only validation of the scanner behavior, run the focused unit test directly:

python -m pytest -q tests/test_direct_sql_audit_service.py

Cross-provider maintenance notes

This audit helps identify SQLite- or PostgreSQL-specific assumptions in application feature logic. Remaining SQL is intentionally isolated in service/repository/tooling boundaries. Ongoing cleanup should:

narrowing the broad inky_admin/services/*_service.py allowlist as persistence code moves into repositories or *_db_service.py modules
centralizing placeholder, upsert, date/time, JSON, and schema-inspection differences in the database service/adapter layer
keeping SQLite-only PRAGMA, INSERT OR REPLACE, lastrowid, rowid, and ON CONFLICT behavior out of routes/templates/normal helpers
moving one domain at a time behind repository methods to avoid large behavior regressions

Migration scope

No migrations were squashed, renamed, reordered, archived, consolidated, or removed. No schema change was required for the first audit pass.

On this page