Database Administration
Audience: System Admin, Developer, Support Related: Database Service · Sqlite And Postgresql · Database
Database Admin provides provider-aware inspection, maintenance, schema export, saved queries, SQL Console safeguards, data-problem review, resource relationships, restore actions, and parity diagnostics. It is an operational tool and must preserve organization boundaries and role checks.
Application queries use explicit column lists. SELECT * is prohibited in application code and blocked in the SQL Console. Writes should use the centralized database service, provider adapters, transactions, and idempotent migrations.
Licensing metadata
License / entitlement diagnostics are intentionally not displayed on Database Admin. System Admins can review the same safe derived status, online status, application/scope/period, License Number/Reference, fingerprint, entitlement revision/generation, Trial timing/fallback-lock state when applicable, highest trusted time, organization usage, and compliance from System → License → View License Details. The full License Key and encrypted credential remain excluded from these diagnostic surfaces.
The Organizations resource displays, searches, and filters organization_purpose, which is used by Limited Use entitlement accounting (default, normal, regression, diagnostic_clone). The internal license repository/state row remains an internal resource rather than a normal editable Database Admin resource.
Database parity validation treats licensing metadata as a runtime requirement. It checks organizations.organization_purpose, allowed purpose values, the Default Organization purpose, and the presence of the non-secret blackcap_license state/locking row. This applies to both SQLite and PostgreSQL and helps catch incomplete restores or schema drift before entitlement-consuming writes are attempted.
Database Admin workflows
The Database Admin UI is an authenticated operational interface for inspecting, filtering, maintaining, and selectively updating Blackcap resources on either supported database backend: SQLite on Raspberry Pi deployments or PostgreSQL on GCP/Cloud SQL deployments.
It is intentionally not a generic unrestricted database client. The active provider is detected at runtime, and provider-specific health details, maintenance actions, schema metadata, and SQL-writing guidance are exposed through the same permission-controlled interface.
Regression and diagnostic-clone diagnostics
System Admin Database Admin includes Regression Diagnostic Clones under Operations. It exposes the clone public ID, owning run, source organization, isolated diagnostic organization, status, creator, and timestamps with configuration/data-copy option and summary JSON available in the detail view for troubleshooting; the internal ID-map summary remains hidden from the normal resource view. Regression Test Runs also surfaces suite_version and warning_count, and Regression Test Schedules surfaces the scheduled Use real AI providers flag (synthetic by default), the optional Code & schema and Browser / Chrome Extension flags, including browser deep diagnostics. These fields are useful when a repeated diagnostic pass behaves differently from clone creation or a scheduled/full run.
For recipe duplicate troubleshooting, Recipes now shows source_type and created_via, while Recipe Edit Content shows original_ingredients_text and original_instructions_text. Manual and Recipe Photo/OCR deduplication does not store a separate fingerprint field: Blackcap computes the manual-ocr-content-v1 SHA-256 identity on demand from those immutable original text fields. Later edits and converted ingredient/instruction fields are intentionally not the identity basis. URL/social/discovery/share recipes do not use this additional content matcher.
For Let’s Cook completion diagnostics, Recipes also exposes the shared Recipe-level Made It fields made_it_rating, made_it_rating_updated_at, made_it_image_path, cleanup_difficulty, cleanup_score, and cleanup_source. Recipe Edit Content exposes manual_notes_text, which is where the editable Made It notes are persisted. These are Recipe-level values: repeated cooks and different users update the same Recipe metadata rather than creating a Made It row per user or per Let’s Cook session.
For Component Recipe diagnostics, Recipes also exposes the first-class yield_quantity and yield_unit fields. Database Admin intentionally does not model or derive parent↔Component associations: Component matching remains an opportunistic runtime decision in Shopping Lists, Meal Planner, Let’s Cook, and deliberate sharing. Missing yield, duplicate matching Component names, and incompatible/missing quantity units are supported runtime states rather than Database Admin Data Problems.
AI original-recipe and combination diagnostics (schema 263)
The AI resource group already contains the records needed for Create Recipe, Combine Recipes, Bulk Adapt, and Plan Meals-generated originals; no new table is necessary. In AI Jobs, filter use_case_key to create_recipe, adapt_recipe, plan_meals or manual_recipe_image_generation and inspect organization, provider/model, status, stage, durable lease and job ID. For Combine Recipes, the comparison and generation are separate provider jobs and reservations; inspect AI Job Provider Interactions by job_id and purpose, then AI Seed Reservations and AI Seed Ledger Entries by job ID to distinguish reserved, settled and released Seeds. An AI quote is a ceiling, not a ledger transaction.
When original creation is enabled, Plan Meals reserves the approved maximum once; its planning charge settles on the parent and actual create_recipe and image child jobs draw transferred allocations from that hold. These jobs retain independent metering/AI Usage events without double-reserving account balances. Check the parent AI Seed reservation and child ledger entries when reconciling release of unused allowance. The Plan Meals result contains original_recipes_created and original_images_created (plus their failure counts). Do not assume a missing image is a recipe-save failure. Check its separate image job, candidate and AI Recipe Image State, then confirm the selected image was accepted with the recipe. Use AI Usage → What Happened for authorized comparison choices and the actual recipe inputs/outputs; generic Database Admin intentionally hides input_manifest_json, raw prompts and private evidence. API Tester exposes the read-only Create Recipe options/lookup and status surfaces for bounded diagnostics, but provider submission is excluded from transactional Multi API.
AI-authored Recipes (Create, Adapt, Bulk Adapt, Combine and Plan Meals original creation) receive human-readable recipe_id values derived from the final saved name, with organization-local suffixes for collisions. Their source classification (ai_generated_recipe or recipe_adaptation) is not a web import: source URL fields are blanked on save and View Source is suppressed even for older AI-authored records carrying legacy links. Website-imported Recipes retain valid external source links. Existing saved recipe IDs are not renamed. No additional Database Admin resource or migration is required for these presentation/source rules.
Social recipe diagnostics
Database Admin includes a Social Recipes resource group with organization-scoped resources for imports, sources, captures, candidates, and review issues. System Admin also receives AI Media Artifacts under the AI group for bounded video/frame/thumbnail/description diagnostics.
Safe operational fields such as provider/post ID, status, stage, candidate counts, artifact type, MIME type, byte size, duration, retention state, and provider request linkage are visible. Raw captured page payloads, recipe/evidence JSON, storage paths, content hashes, and free-form metadata remain protected. Relationship links connect imports to resulting recipes, background jobs, AI jobs, candidates, and review issues.
These pages are intended to answer questions such as whether Blackcap bound a capture to the exact social post, which evidence types reached the server, whether deterministic extraction produced a candidate, and why manual completion was required—without exposing browser captures or provider payloads.
🧭 Where it lives
The UI is available from the authenticated Admin UI for users with database permissions. It does not add mobile routes, mobile templates, or mobile navigation.
Core files:
inky_admin/blueprints/database_admin.py
inky_admin/services/database_admin_service.py
inky_admin/services/database_provider.py
inky_admin/services/database_resources.py
inky_admin/services/database_highlight_service.py
inky_admin/services/database_relationship_service.py
inky_admin/services/database_saved_query_service.py
inky_admin/services/database_user_preferences_service.py
inky_admin/services/database_data_problem_service.py
inky_admin/services/maintenance_actions.py
inky_admin/templates/admin/database/_base_database.html
inky_admin/templates/admin/database/resource_explorer.html
inky_admin/templates/admin/database/maintenance.html
inky_admin/templates/admin/database/sql_console.html
inky_admin/templates/admin/database/partials/detail_drawer.html
inky_admin/templates/admin/database/partials/index_panel.html
inky_admin/templates/admin/database/partials/maintenance_panel.html
inky_admin/templates/admin/database/partials/sql_console_panel.html
inky_admin/templates/admin/database/partials/data_problems_panel.html
inky_admin/static/database_admin.css
inky_admin/static/database_admin.js
Schema and utility support:
database/migrations/
tools/init_database.py
tools/verify_database.py
tools/vacuum_database.py
🔎 Resource Explorer
The Resource Explorer provides paginated, searchable, filterable views of operational resources such as:
- organizations, including preferred time zone/time format/locale, devices, displays, scoped settings, and setting definitions
- connection types, provider configs, and connection metadata
- recipes, including source type/creation path, shared Made It rating/photo/cleanup fields, and manual/OCR duplicate eligibility
- recipe edit content, including immutable original ingredient/instruction text used as the manual/OCR fingerprint basis
- recipe ingredients
- recipe tags
- ingredient search/index data
- meal planner settings, slots, entries, and shopping-list links
- shopping lists
- shopping-list items
- shopping-list item sources
- Household List browser-import history and external provider source rows
- provider-neutral external shopping-list lists, item snapshots, links, and sync runs
- grocery departments
- inventory locations
- Kitchen Inventory items, Products, physical Packages, activity/monthly usage summaries, temporary Product/overview photo staging, and platform Ingredient Density Profiles, USDA FoodKeeper Shelf Life Profiles, and canonical Ingredient Catalog/source/image metadata
- item department and storage mappings
- organization memberships
- organization backup runs
- cloud backup provider tokens and cloud backup state
- organization-scoped Noun Project rules
- organization lifecycle events for system/platform review
- regression diagnostic clone tracking, regression runs/steps, and regression schedules for System Admin troubleshooting
- background jobs
- async cache jobs
- cache status
- app state
- emoji registry and emoji cache-state data
- schema migrations
- Database Admin saved-query metadata and per-user column-preference tracking tables
- users and organization/system resources where permitted
Resources are declared in inky_admin/services/database_resources.py. Hovering over a resource in the sidebar shows its backing table name so support users can quickly connect friendly labels to SQL table names. Each resource can define:
- display name
- backing table/query
- primary key column
- display columns
- searchable fields
- filterable fields
- finite-value dropdown filters
- safe editable fields
- hidden columns that should never appear in Database Admin detail views
- masked columns that can show that a value exists without exposing the value
- conditional masking, such as masking
settings.valuewhenis_secret=1 - scope type: platform/global, organization, display/device, user/org membership, or join-backed organization scope
- direct organization column, parent ownership join, scoped-settings resolver, or key-suffix resolver
- system-only visibility
- required permissions
- count, search, filter, detail, edit, and export behavior
Organization-scoped resources now fail closed if an active organization context is not available. Counts, lists, filter dropdowns, detail drawers, safe edits, and direct route calls all use the same provider-level scope rules.
Filtering and pagination are server-side. Large resources are never loaded into the browser all at once. The Database shell now renders with lightweight resource metadata first, then lazily fetches resource row counts and the aggregate header metrics from /admin/database/api/resource-counts. This keeps the first paint fast while still filling in sidebar counts, Total Rows, Largest Resource, and Empty Resources shortly after load. The Resources, Maintenance, Data Problems, and SQL Console controls behave as in-page tabs after the first Database Admin load. Switching tabs fetches only the right-hand panel fragment and reuses the already-rendered sidebar context instead of reloading the whole Database Admin page. Direct links to /admin/database, /admin/database/maintenance, /admin/database/data-problems, and /admin/database/sql still work normally.
When the SQL Console tab is active, clicking a resource in the left sidebar inserts that resource's backing table into the SQL editor rather than navigating away from the SQL tab. The editor uses table aliases derived from table initials, such as r for recipes, ri for recipe_ingredients, o for organizations, u for users, and rca for recipe_cache_artifacts; duplicate initials get numbered suffixes. A blank editor becomes a select <alias>.* from <table> <alias> query without adding a SQL limit clause because the console already has a separate row-limit box. Adding another table appends that table's <alias>.* to the select list when needed, reorders known parent/child tables, and builds left joins for common Blackcap relationships. Explicit rules cover common debugging pairs such as meal_plan_entries plus recipes (meal-plan entries stay primary and recipes are left joined), recipes plus recipe_ingredients, shopping lists plus shopping list items, organization memberships plus users/organizations, and display assignments plus recipes/displays. If no safe relationship can be inferred, the table is added to the from list as a comma-separated source.
Protected fields are handled by the resource registry before rows are rendered. Examples include users.password_hash, recipe-share token hashes/encrypted tokens, connection token references, provider config secret references, provider config JSON, encrypted cloud-backup token payloads, backup state JSON, token metadata JSON, and secret setting values. These fields should be managed through their purpose-built configuration/security pages instead of the generic Database Admin detail drawer.
Household/external shopping-list metadata is intentionally reviewable in Database Admin. Shopping-list item rows now expose provider rollup columns such as external_source_item_id, external_source_url, external_source_logo, external_source_status, and external_source_metadata_json in the resource detail/search model, and Household/external source resources expose raw metadata JSON for support diagnosis. Pinned Household List state is stored in the shopping-list payload/legacy JSON for the built-in Household List, so inspect the Household List row detail when debugging pin/session behavior.
Row actions use compact emoji-only buttons with tooltips so dense record lists remain readable. Current conventions include:
- 🔍 opens the row details drawer.
- ♻️ restores an inactive recipe when restore is available.
- ➡️ opens a purpose-built application page when a resource provides that shortcut.
Ingredient-density and Inventory photo diagnostics
System Admin Database Admin exposes Ingredient Density Profiles (ingredient_density_profiles) as platform-owned configuration data. The resource shows the stable key, display name, density_g_per_ml, aliases, fallback/active flags, source, and update metadata. The normal edit surface remains Platform Configuration → Ingredient Densities; Database Admin is primarily for inspection/parity diagnosis.
System Admin Database Admin also exposes the master ingredient catalog as four platform resources: Master Ingredients (ingredient_catalog), Ingredient Aliases (ingredient_aliases), Ingredient Source Mappings (ingredient_sources), and Ingredient Images (ingredient_images). These resources retain canonical identity, aliases, provider source identity/status, cached-image provenance, preference/override metadata, and timestamps for diagnostics. The normal management surface remains Platform Configuration → Ingredients, where TheMealDB synchronization, source review, visual selection, and canonical edits enforce the ingredient-domain rules; Database Admin should not be used to bypass that workflow.
Database Admin also exposes USDA FoodKeeper Shelf Life (foodkeeper_shelf_life_profiles) as platform-owned configuration data. Rows retain the USDA FoodKeeper id/source text alongside Blackcap canonical matching, aliases, Pantry/Refrigerator/Freezer planning days, active/customized state, source (usda_foodkeeper, usda_foodkeeper_fallback, or manual), and update metadata. Normal edits and USDA refreshes belong in Platform Configuration → FoodKeeper Shelf Life rather than direct SQL. A successful full refresh supersedes non-customized fallback rows and disables non-customized USDA rows removed upstream instead of deleting them; customized rows are preserved. Physical Packages retain only their resulting date fields, so Estimated Best By on the Package records that the date was generated from catalog guidance rather than manually entered and does not maintain a live foreign-key dependency on the FoodKeeper row.
Kitchen Inventory resources also expose logical items, retained Products, physical Packages, quantity/activity/monthly-summary data, and temporary inventory-photo staging/link rows. Physical Inventory Packages also expose the optional freshness fields date_type, date_precision, date_year, date_month, date_day, and date_other_label for diagnosis. Derived Current/Expiring Soon/Expired state is intentionally calculated by the Inventory expiration service rather than stored as another database column. Product-photo captures remain tied to one logical item/package review, while Kitchen Inventory overview captures are intentionally staged without one item association because one image can produce several reviewed findings. AI Usage/AI Jobs provide the provider-side history; staged image rows are temporary and are not durable Product media.
Kitchen Inventory lifecycle safe edit
The Kitchen Inventory Items resource exposes status, archived_at, archived_by_user_id, and archived_was_on_hand for lifecycle diagnosis. Users who have both Database Admin edit access and inventory.delete see Lifecycle Status in the detail drawer with the choices Active / Restored and Archived / Recently Deleted.
This is not a raw status-column update. Database Admin routes the transition through the Kitchen Inventory domain service so archive clears the active on-hand state, remembers whether the item was on hand, maintains lifecycle timestamps, enforces active-organization scope, and writes the normal inventory audit events. Changing Lifecycle Status back to Active restores the remembered on-hand state. If a different current state is needed, save a separate On Hand safe edit after reactivation. Permanent removal remains a confirmed Data Cleanup operation.
The detail drawer explains this behavior beside the safe-edit fields. Users without inventory.delete can still inspect permitted Kitchen Inventory fields, but Lifecycle Status is not offered as an editable field.
Search highlighting, saved views, columns, links, and CSV export
Resource searches remain server-side and resource-specific. When a search term is present, visible cell matches are highlighted case-insensitively. If a row matched because of a safe searchable column that is currently hidden by the user's column preferences, the row gets a compact Matched summary with a short escaped excerpt. Protected/hidden/masked fields are not exposed through highlighting or match summaries.
Each resource view has a Saved Queries tool that saves the current resource search/filter/sort/page-size state to the signed-in user's account. Saved queries are user-scoped by default and appear as compact rows with emoji actions: ✏️ loads the saved query back into the resource filters/SQL editor for editing, ▶️ runs it, and 🗑️ deletes it. Saved-query descriptions stay out of the dense list and are available from the saved-query name tooltip along with saved/updated/last-run timestamps. Saving while editing a saved query asks for confirmation before overwriting that saved query. Saved queries still re-run through the same Database Admin resource or SQL Console route, so they do not bypass organization scoping, SQL safety, or permission checks. The backing database_saved_queries table is also available as a System Admin Database Admin resource named Saved Database Queries. That diagnostic resource intentionally shows metadata only; raw query text and JSON payload fields remain hidden.
Each resource view has a Columns tool that stores per-user, per-resource visibility and display order in user_database_page_preferences. The collapsed summary shows the visible/available column count. The expanded editor lists one column per row with a visibility checkbox, a draggable column-name pill, and a short sample value from the current page. Dragging a column-name pill changes the full preferred order, including hidden columns, and Save persists visibility and order together. Visible columns can also be reordered directly from the data table by dragging the column name itself; header reordering saves immediately and refreshes the resource view. While dragging, holding near the top or bottom edge of the Columns list automatically scrolls that list, and holding near the left or right edge of a wide data table automatically scrolls the table so columns outside the current viewport can be reached without dropping first. Clicking a table column name still sorts normally. Hidden columns retain their saved place in the full order so re-enabling one returns it to the expected position rather than appending it arbitrarily. Available/orderable columns are limited to columns the Database Admin resource model allows for that user. Hidden/protected columns remain unavailable even if the user attempts to post them manually. Resetting a resource removes the user's preference row and returns both visibility and ordering to the resource defaults. The backing preference table is available as a System Admin Database Admin resource named Database Column Preferences. The JSON arrays that store visible/hidden/order state remain hidden in the diagnostic list/detail view so the Database page itself remains the normal editor.
Curated foreign-key-style links are rendered for common diagnostic relationships, such as recipe ingredients to recipes, meal-plan entries to recipes/slots, shopping-list items to shopping lists/recipes/grocery departments, Kitchen Inventory Products/Packages/Activity back to their logical Inventory item, Kitchen Inventory items to storage locations and grocery departments, Let’s Cook session rows back to their session/recipe/display, display content assignments to displays and conditionally to recipes or Let’s Cook sessions, voice mappings to displays/connections, and organization/user relationships where the target resource is visible to the current user. The relationship resolver is shared by the list table and the row detail drawer: if a field is a foreign-key link in list view, that same field is clickable in the detail popup. Links navigate to the target resource with a safe search for the key value; missing or inaccessible targets degrade to normal text/search behavior rather than exposing extra data.
Resource views include Export CSV for the current filtered result set. The export uses the user's currently visible/allowed columns, applies the same server-side organization scope and search/filter parameters as the resource table, caps output at 10,000 rows, escapes CSV normally, prefixes spreadsheet-formula-looking values with an apostrophe, and writes an audit event with counts/metadata only.
API Tester and regression coverage
The API Tester registry includes the Database Admin endpoints behind resource fragments, resource CSV export, SQL CSV export, saved-query save/run/delete, column preference save/reset, and the on-demand Data Problems tab. The entries are intentionally marked as form-backed or file-download behavior where appropriate; mutation-like endpoints such as saved-query run and column preference save remain safe_to_run=false because they update audit/last-run/preference metadata.
The regression harness includes System Admin Database Admin checks for the two diagnostic resources, async column visibility/order preference save/reset (including verifying the rendered header order), async saved-query save/run/delete, the on-demand Data Problems tab, and API Tester registry coverage for the Database Admin endpoints. These checks sit alongside the existing SQL Console schema, protected-field, schema export, and read-only SQL guardrail tests.
Data Problems tab
Database Admin includes a separate on-demand Data Problems tab rather than embedding the diagnostics card underneath loaded resource tables. This keeps resource views focused on their own table, pagination, and horizontal scrolling behavior. The tab intentionally does not run checks on every page load. Checks are scoped to the active organization and include recipe ingredients without recipes, meal-plan entries without recipes, shopping-list items without lists, display assignments pointing at missing recipe/Let’s Cook content, and voice display mappings without displays.
Display integrity checks also report:
- an organization with zero active displays;
- more than one active display marked as default;
- active displays with no active default display;
- current/default assignments whose display no longer exists; and
- scheduled assignments whose display was deleted. The scheduled-orphan check is intentionally separate so a failure in the transactional display-deletion cleanup path is immediately visible.
When issues are found, the tab displays one card per issue type with severity, affected row count, a short explanation, and a link to the relevant filtered Database resource where practical. Recipe cache health is not duplicated here because the Organization page already owns detailed recipe-cache status.
🍽️ Recipe restore and converted content inspection
Database Admin includes a recipe restore workflow for archived/deleted recipes. On Resources → Recipes, the ♻️ restore action appears only when the recipe status is not active. Restore is organization-scoped and should go through recipe_restore_service, not direct SQL.
Restore behavior:
- sets the recipe back to
active; - clears archived/deleted lifecycle fields when present;
- attempts to revive related recipe-owned lifecycle rows when those tables expose archived/deleted columns;
- rebuilds recipe/search indexes;
- queues a normal recipe-cache build;
- attempts to regenerate thumbnails from preserved source images, including Paprika/file-import image paths.
Recipe restore cannot recreate image bytes that were already deleted before source-image preservation was added. In that case the recipe content can still be restored, but the thumbnail requires a backup, original import file, or another image source.
recipe_edit_content exposes derived inspection columns for optional original/converted recipe body fields. The canonical editable payload remains content_json, but Database Admin can show text mirrors such as original_ingredients_text, converted_ingredients_text, original_instructions_text, converted_instructions_text, and related notes fields so support/debugging does not require manually reading JSON.
☁️ Backup and Noun Project resources
Database Admin exposes the current DB-backed backup and icon-rule resources without exposing raw secure payloads:
cloud_backup_provider_tokensis organization-scoped. The encryptedtoken_jsonvalue is masked, and provider metadata that could expose account details is hidden.cloud_backup_stateis organization-scoped. The rawstate_jsonpayload is hidden; use the Backup page for normal status/history review.organization_backup_runsis organization-scoped. In the Default organization, full/platform backup attempts are included withbackup_scope='platform'.noun_project_rulesis organization-scoped and replaces the old file-backedNounProjectWords.csvrule table.
Provider app credentials, OAuth client secrets, SMTP secrets, and Noun Project API secrets remain managed through purpose-built configuration screens rather than raw Database Admin edits.
🏢 Organization regional preferences
The Database Admin Organizations resource exposes preferred_time_zone, preferred_time_format, and preferred_locale in its list/detail model so System Admin users can review the regional settings that drive org-scoped timestamp labels. preferred_time_format is filterable and should be either 12h or 24h. Normal edits should still happen on the purpose-built Organization pages rather than by raw database changes.
🖥️ Display timed Meal Planner behavior
Migration 170 adds displays.timed_meal_plan_behavior, which is exposed on the Database Admin Displays resource. The column is visible in the list, filterable, and safely editable with the allowed values off, recipe, and lets_cook. This is the database-backed value behind the Display Rendering Settings radio group. Let’s Cook is effective for all display types: kiosk displays and touch-enabled hosted web receivers show the interactive Let’s Cook UI, while local e-ink, non-touch hosted receivers, mock displays, and remote clients render the display-only Let’s Cook preview.
Attached and Remote Display Client Resources
The existing display resources now describe the active attached-client implementation rather than a future placeholder:
- Displays exposes
current_statusfor troubleshooting states such asclient_waiting,client_linked,client_displayed, andclient_error. - Display Content Assignments documents client delivery states, including
blocked_by_client_mode. Scheduled rows remain stored while Client content is active but are suspended from the compact Upcoming view. - Display Clients contains remote pairing, token-hash, heartbeat, synchronization, and physical-display acknowledgement state.
- Display Client Jobs contains transient remote delivery work. These jobs are not replayed by cross-environment organization restores.
Do not use Database Admin editing to place an attached display into or out of Client content. The normal Assign Content flow ends active Let’s Cook state, updates ownership/status consistently, and applies the local-hardware push guard. Database Admin is for inspection and narrowly controlled repairs, not for bypassing that domain flow.
No new database columns were added for the rapid Shop a List checkoff stabilization. Those changes use the existing shopping-list payload and a backend-aware atomic mutation helper.
📅 Meal Planner resources
Blackcap application schema 266 is current. Migration 266 adds game_shared_progress and game_shared_sessions for scoped, versioned arcade/puzzle/card saves and game statistics on SQLite and PostgreSQL. Migration 265 stores per-user game display preferences and trivia progress across SQLite and PostgreSQL. Migration 264 persists Recipe Memory boards, user/org progression, and completion history in SQLite and PostgreSQL. Migrations 260–261 reclassify Recipe Adaptation sources and backfill Adapt Recipe bulk AI Seed pricing; migration 262 records scheduled regression AI-provider mode, and migration 263 registers independent Create Recipe AI Quality Profiles. The migrations are idempotent on SQLite and PostgreSQL. Migration 251 adds database-coordinated durable AI job phases/leases and per-provider interaction checkpoints; migration 250 remains the Recipe Discovery close-out migration. Migration 246 removes the retired Recipe Discovery Custom Search JSON API key, Agent Search API key, and Agent Search authentication-mode setting from both settings and setting_definitions; Agent Search uses runtime ADC/IAM. Migration 250 removes the obsolete recipe_discovery.search_provider_enabled value/definition so recipe_discovery.search_provider_type is the sole enablement/selection control. Historical setting-audit rows are deliberately retained. The Recipe Search Rules Database resource is the platform-owned rule set shared by browser Programmable Search, Agent Search, Brave, Tavily, and Plan Meals Find & Add; Agent Search mirrors enabled rules automatically while Brave/Tavily receive compatible filters at request time and Blackcap always performs the final local rule check. Provider API keys remain secret/masked. The recent provider-neutral Plan Meals classification and generic Recipe-title fallback are application behavior and require no additional schema beyond 250; durable AI execution is added by migration 251. Migration 252 registers the AI Seed-tracked, durable Create SQL Query use case and its Economy/Standard/Enhanced Quality Profiles; it adds no new application tables because jobs, interactions, usage evidence, and Seed accounting reuse the shared AI infrastructure. Migration 253 adds meal_slots.recipe_type, the organization-configured mapping Plan Meals uses for existing-Recipe candidate ranking and Find & Add Recipe creation. Migration 254 adds the platform-owned canonical ingredient catalog (ingredient_catalog), aliases (ingredient_aliases), external provider mappings (ingredient_sources), and cached-image provenance (ingredient_images) used by Platform Configuration → Ingredients and TheMealDB synchronization. Migration 255 adds user_data_table_preferences, the reusable per-user visibility/order store for standard Blackcap data tables outside the Database Admin resource explorer. Migration 256 adds the SQLite-only persisted Periodic Database Vacuum schedule and last-run metrics; PostgreSQL advances through migration 256 without creating the SQLite maintenance table. Migration 259 retains aggregate regression-run AI Seed spending and approximate per-test spend attribution so export summaries remain available after regression-organization cleanup. Migration 257 introduces organization-scoped Adapt Recipe parent links, Held recipe retention, idempotent AI acceptance keys, and Adapt Recipe Quality Profiles on both database backends.
Meal Planner introduces organization-scoped planning data for week start settings, meal slots, meal plan entries, persistent Meal Identities, saved AI Meal Plans, and links between meal plans and generated shopping lists. Database Admin exposes the recent Meal-domain tables explicitly:
- Meal Identities (
meal_plan_meals) — dated Meal Slot identity, optional custom name, name-overridden state, image source/provenance, and provisional Component preferences. The image filesystem path is hidden from normal Resource Explorer presentation. - AI Meal Plans (
meal_slot_ai_plans) — accepted plan status/schema, expected timing, Serve At/Suggested Start, Quality Profile, AI job/usage correlation, and stale state. Raw plan/snapshot/fingerprint JSON remains hidden. - AI Meal Plan Recipe References (
meal_slot_ai_plan_recipes) — protected Recipe/Component source identities and content fingerprints used to determine whether a saved plan is still current. - Let’s Cook Meal Items (
lets_cook_session_meal_items) — session-scoped Meal Item snapshot rows; legacy ready/task columns are retained for compatibility but Meal Items are header-only context. - Meal Recipe Suggestions (
meal_recipe_suggestions) — durable unresolved/resolved/dismissed Plan Meals placeholders with organization/date/Meal Slot, suggested name, non-authoritative ingredient hints, safe source/import hint, primary/side role, ordering, and Plan Meals correlation. Ingredient hints are diagnostic/discovery context and are not Shopping or Let’s Cook data. - Meal Plan Entries (
meal_plan_entries) retain the visible planned item plus Plan Meals provenance. Recipe-backed entries display the current Recipe Library title, whileplan_meals_original_namekeeps the AI-proposed name and hiddenplan_meals_context_jsonkeeps the original discovery hints/source/dietary/role context so Recipe ⇄ Meal Item ⇄ Recipe Idea corrections do not lose planning intent. - Dietary Preference Catalog (
dietary_preference_catalog) — platform-defined extensible preference keys/names/icons used by meal planning. - Organization Dietary Preferences / Settings (
organization_dietary_preferences,organization_dietary_settings) — organization assignments plus the bounded custom dietary note.
Operational rename/image/Plan Meal changes belong in Meal Planner and the purpose-built AI workflows rather than direct Database Admin editing. These resources should follow the same Resource Explorer safety rules as recipes and shopping lists:
- active-organization filtering;
- hidden/masked protection for any future sensitive fields;
- safe edits only for explicitly allowed fields;
- no cross-org direct detail/edit access;
- dependency-aware recipe archive/delete behavior through purpose-built services, not ad-hoc SQL.
Operational planning changes should generally happen through the Meal Planner UI and Configuration page. Database Admin is for inspection, safe repair, and data stewardship.
🎛️ Filter behavior
Text filters remain available for high-cardinality fields. Fields with a finite or reasonably small set of values are shown as dropdowns. Examples include:
- recipe type
- status
- cache status
- layout
- source
- job status
This keeps common operational filtering fast and reduces typo-prone searches.
Filter-option discovery projects each distinct value as normalized text and orders that projected alias. This provider-neutral query shape is required for PostgreSQL, which does not allow a SELECT DISTINCT result to be ordered by a different cast expression that is absent from the select list; SQLite is more permissive. A failure while deriving one optional dropdown is logged and omitted rather than turning the entire resource request into HTTP 500, so the protected resource rows and remaining filters still load.
🧂 Ingredient Canonical Management
Ingredient cleanup is built directly into the record list experience for ingredient-related resources.
Supported workflow:
- Open the ingredient-related resource list.
- Select multiple records from the list.
- Enter the target canonical value.
- Run the canonical merge/update action.
The action updates all records using the selected canonical values, not only the selected rows. This means selecting representative rows for duplicate canonicals can clean up the full matching set.
Example:
boneless chicken breast
boneless skinless chicken breast
chicken breasts
→ chicken breast
Where enabled, the workflow can also update matching shopping-list items so generated recipe data and shopping workflows stay aligned.
All canonical update operations should be audited.
🧭 Item Department & Storage Map
Database Admin includes an Item Department & Storage Map resource backed by canonical_ingredient_department_map.
This resource is the data-management home for canonical item routing:
- canonical item name;
- grocery department used by Shop a List;
- default Kitchen Inventory storage location;
- source/confidence metadata.
The resource also includes a Review Uncategorized workflow. It surfaces canonical items from recipes, shopping-list rows, and Kitchen Inventory records when the item has no mapping or still maps to Other / Uncategorized.
The workflow is intentionally Database Admin-only because it is data stewardship, not shopping execution. A data admin can review one suggestion at a time, choose the correct grocery department, choose the default storage location, and save the mapping so future shopping lists and Kitchen Inventory records are categorized better.
🛒 Household and External Shopping List resources
Database Admin exposes the newer Household / External Shopping List data model so support can reconcile what the UI shows against the underlying rows. The important resources are:
shopping_lists: includes normal lists plus the built-in Household List. Household rows uselist_kind='household'andis_builtin=1.shopping_list_items: includes normal list rows and Household List rows. For Household items, theexternal_source_*columns are a rollup of the primary active provider source only.household_list_item_external_sources: the authoritative many-source bridge for Household items. One Household List item can have several active provider sources when, for example, both Amazon/Alexa and Google Keep containmilk. Use this resource to inspect provider/source count, active/inactive source state, primary source selection, missing-provider state, and source favicon/label metadata.household_list_browser_bridge_imports: attended Chrome-extension import history for Google Keep, Amazon/Alexa, and Custom URL provider pages.external_shopping_lists,external_shopping_list_items,shopping_list_external_item_links, andexternal_shopping_sync_runs: provider-neutral foundation tables for future official/API-style providers and any legacy/foundation flows that use durable external item snapshots. The current browser-bridge implementation primarily stores live source association state inhousehold_list_item_external_sources.
When diagnosing Household List issues, start with the Household List row in shopping_lists, then inspect shopping_list_items for the visible item row, then inspect household_list_item_external_sources for all associated provider sources. Do not assume the rollup columns on shopping_list_items represent every provider; they intentionally represent only the primary active source for compact display.
Ignored Household items are local Blackcap shopping-list state. They can remain associated to provider source rows but should be hidden from Admin Shop a List, Mobile Shop a List, and Shop With surfaces.
🧹 Maintenance Center
Maintenance actions are defined in inky_admin/services/maintenance_actions.py and filtered by the active provider before they are rendered or executed. The Maintenance tab formats health-card labels and numbers for readability, and each action includes tooltip guidance about expected duration, risk, and benefit.
SQLite actions currently include:
- Verify Database — full
PRAGMA integrity_check. - Quick Database Check — lighter
PRAGMA quick_check. - Analyze Database — refresh SQLite query-planner statistics.
- Run WAL Checkpoint — flush and truncate the SQLite WAL file.
- Vacuum Database — rebuild the SQLite file to reclaim free space.
PostgreSQL actions currently include:
- Verify PostgreSQL Connection — confirm the connection and current schema are reachable.
- Analyze Database — refresh PostgreSQL planner statistics.
SQLite-only integrity, WAL, file-path, and file-vacuum operations are never offered for PostgreSQL. PostgreSQL health focuses on server/database identity, connection and pool state, database/table sizes, live/dead tuple estimates, analyze/vacuum timestamps, and schema version where available. SQLite health focuses on the local database path and size, journal/WAL state, page/free-page counts, foreign-key enforcement, busy timeout, and schema/user versions. The SQLite Maintenance health cards also show Free Pages as both a page count and percentage of the database, plus Reclaimable Space, an estimate calculated from freelist_count × page_size. These values make it easier to recognize when a database has accumulated enough unused pages for VACUUM to be worthwhile without manually interpreting SQLite PRAGMA output. When free pages reach 25% of the database, those two diagnostic cards receive a subtle attention treatment; this is an operational cue, not a failure state or an automatic requirement to vacuum immediately.
For SQLite, Periodic Database Vacuum is available in both Database → Maintenance and Configuration → Platform → Database. Both panels edit the same persisted schedule. Supported cadences are weekly, monthly (day 1–28), every X weeks, every X months, and every X days, all with a time of day and Enabled/Disabled control. A fixed cron checker runs every 15 minutes and evaluates the persisted schedule instead of rewriting cron. If Blackcap was offline across scheduled occurrences, the next due check runs one vacuum and advances to the next future occurrence rather than replaying every missed run.
Manual Vacuum Database and scheduled vacuum use the same protected implementation: a cross-process lock prevents overlap, free disk space is checked before starting, direct SQLite VACUUM runs against the active database while Blackcap stays online, and PRAGMA quick_check validates the rebuilt database afterward. Normal SQLite busy/lock contention is treated as Deferred so the next checker can retry. The panel records Last Run, Last Result, duration, database size before/after, and reclaimed space using the platform time zone. PostgreSQL neither renders nor executes this schedule, and server-side validation rejects attempts to configure or directly run the SQLite-only operation. After deploying a release that adds or changes a managed scheduled operation, rerun tools/install_blackcap_scheduled_operations.sh for the deployment mode so /etc/cron.d/blackcap matches the current application; see Background Jobs and Schedules.
Actions enforce permissions server-side and write audit entries. Higher-risk actions require confirmation in the UI. When opened from the Database Admin tab strip, the Maintenance Center is loaded as a panel fragment and uses a fast provider-specific health payload rather than recalculating every resource count. Maintenance actions submitted from that tab update the panel in place.
🧪 SQL Console
The SQL Console is System Admin only and read-only by default. Both providers allow a single SELECT or EXPLAIN statement. SQLite additionally permits the approved read-only PRAGMA forms used for inspection; PostgreSQL does not accept SQLite PRAGMA syntax.
Statements are validated server-side, single-statement only, capped by the configured result limit, and audited. Write-style SQL remains disabled.
Organization-scoped read queries are allowed for System Admin users, but the SQL Console does not automatically inject the active organization context into arbitrary SQL. When querying tenant data such as recipes, shopping_lists, or meal_plan_entries, include an explicit organization_id filter when the query should be scoped to one organization. The Resource Explorer remains the preferred place for scoped browsing because it applies the active organization context automatically.
To prevent the SQL Console from becoming a protected-field bypass, tables that contain hidden or masked fields can be queried, joined, and filtered, but protected fields cannot be selected. The SQL Console blocks SELECT * or alias.* for those tables and blocks explicit protected-column selections. Use the alias-aware field suggestions or Export Schema output to select only safe columns. Use the masked Resource Explorer pages or the purpose-built configuration/security pages when you need to review masked provider configs, OAuth records, secret settings, backup token state, and similar sensitive records.
SQL Console result sets render inside their own scrollable results panel. Wide ad-hoc queries keep the table's natural column width and use an anchored horizontal scrollbar instead of squeezing columns into the page. The horizontal scrollbar stays available while the result table is visible and docks above the returned-row footer when the footer is visible. The cloned/sticky field header remains aligned with the real columns while scrolling longer result sets, and the returned-row count is shown in the results footer so it stays separate from row data.
When opened from the Database Admin tab strip, the SQL Console is loaded as a panel fragment and does not recalculate resource counts. Query submissions from that tab update the panel in place. The row limit control is labeled as rows per page; for plain SELECT statements without an explicit SQL LIMIT, the console fetches one page at a time and offers Previous/Next controls instead of treating the value as a hard terminal limit. If the query itself includes LIMIT, the console honors that explicit SQL limit and does not add paging.
The SQL Console has user-scoped Saved SQL Queries. Saving requires SQL Console permission, stores the current editor text to the user's account, supports rename/delete actions, and running a saved SQL query goes back through the same read-only SQL validation and protected-column checks as manual execution. SQL result panels also expose Export CSV, capped at 10,000 rows and blocked by the same destructive-statement/protected-field rules. Export auditing records counts and a short query preview, not the CSV contents.
The SQL Console includes an Export Schema · SQLite or Export Schema · PostgreSQL secondary button beside an active-provider badge. It downloads a provider-named Markdown schema brief that begins with the active database provider, backend key, and SQL dialect, followed by provider-specific query-writing guidance.
The SQL Console also provides ✨ Create Query / Modify Query, a durable asynchronous AI-assisted drafting workflow. With an empty SQL editor, the user describes what the query should do. With SQL already present, the button becomes ✨ Modify Query and the user describes the requested change; Blackcap sends the current SQL as authoritative context so manual edits are preserved. Each turn chooses one configured Create SQL Query Quality Profile and either Organization or Personal AI Seeds and explicitly confirms the Seed spend. The popup presents Quality Profiles and funding sources with the same two-column selectable cards used by Plan Meals: profile cards show Seed cost, provider/model, input/output/reasoning capabilities and description, while funding cards show available, required, and remaining Seeds plus a short explanation of the selected balance. The initial turn sends the natural-language request together with the same Markdown schema document produced by Export Schema. Follow-up turns are linked by chain/iteration/previous-job metadata. When the same OpenAI or Gemini provider connection/model is selected and the schema fingerprint is unchanged, Blackcap references the prior stored provider turn instead of retransmitting the full schema; if provider state cannot be reused, Blackcap falls back to the complete original request + current SQL + new request + schema context. Gemini can use that server-side continuation to improve context caching/cost. OpenAI still bills prior chain input tokens, so Blackcap additionally supplies a stable schema-based prompt-cache key and places the full schema in a stable prompt prefix to maximize cached-input reuse on compatible OpenAI models. The UI keeps the recent drafting turns for the current browser session and 🧹 Clear resets both the editor and that convenience history, while AI Usage retains the durable audit trail. Provider responses are durably checkpointed before local validation. Blackcap accepts only one read-only SELECT, applies internal-object and protected-field safeguards, settles AI Seeds only after that response is valid, and then places the SQL into the editor. Create/Modify Query never runs the SQL automatically; the System Admin must still review it and press Run Query. AI Usage → What Happened retains the original/new request, current/generated query, schema fingerprint/provider metadata, chain/iteration linkage, provider-state reuse flag, and optional provider notes for reconciliation and troubleshooting. Interrupted jobs use the shared durable AI job/interaction recovery model and are never blindly resubmitted after an uncertain provider outcome.
Create SQL Query uses a SQL-specific full-schema input budget rather than the smaller Plan Meal prompt limit. Supported SQL profiles accept 300,000–500,000 characters of schema + request context; existing profiles created with the earlier 65,000/120,000-character setting are automatically treated as at least 300,000 characters when loaded, so the current full Export Schema document can be sent without requiring a migration. The Quality Profile editor shows this effective budget and saves the normalized value the next time the profile is edited.
The live exported type is authoritative. This is especially important on PostgreSQL because Blackcap's migrated schema can intentionally retain integer-backed logical flags, text-backed timestamps, or text-backed JSON even though PostgreSQL also has native boolean, timestamp, and JSON types. The PostgreSQL export therefore annotates live columns with guidance such as = 1 / = 0 for integer flags, IS TRUE / IS FALSE only for native booleans, safe blank-text-to-timestamptz normalization, JSON casts before -> / ->>, and explicit type alignment across UNION / UNION ALL branches. It also warns against blanket text casts on join keys when the live types already match because those casts can prevent index use.
SQLite exports explain integer boolean flags, SQLite date/time functions, GROUP_CONCAT, JSON functions, dynamic type affinity, and the lack of ILIKE. PostgreSQL exports explain ILIKE, intervals/date functions, STRING_AGG, newline literals, native versus text-backed JSON, explicit casts, and live-type compatibility. The remainder contains SQL-console-safe tables, suggested aliases, resource scope, descriptions, live provider column types, primary-key/required hints, SQL Console limitations, Blackcap-specific display-assignment guidance, regression/diagnostic organization filtering guidance, and common join examples. Tables with protected fields are included, but protected fields are omitted and those tables are marked as not safe for SELECT * / alias.*. Row sample values are not included in the export, so it is intended to be safe to paste into a generalized LLM or AI assistant when asking for help writing a read-only query. The export specifically calls out that WITH / CTE queries are not supported by the console, that durable display content assignments live in display_content_assignments, not in app_state JSON, and that production reporting should exclude rows from regression test and diagnostic clone organizations using the tracking tables rather than name-prefix guesses.
Production SQL Console queries should treat regression and diagnostic clone organizations as real data that must be filtered out unless the investigation is explicitly about testing. For an org-scoped alias such as r, add both NOT EXISTS (SELECT 1 FROM regression_test_organizations rto WHERE CAST(rto.organization_id AS TEXT) = CAST(r.organization_id AS TEXT)) and NOT EXISTS (SELECT 1 FROM regression_diagnostic_clones dc WHERE CAST(dc.diagnostic_org_id AS TEXT) = CAST(r.organization_id AS TEXT)). For the organizations table, compare both tracking tables to o.id. When joining multiple org-scoped tables, filter the primary org-scoped alias and keep joins constrained by matching organization_id so rows from different organizations cannot accidentally combine.
The SQL Console includes lightweight alias-aware field suggestions. A read-only schema metadata endpoint feeds the editor with SQL-console-safe tables and columns. It includes tables that contain hidden or masked fields, but it only lists columns that the SQL Console will allow to be selected. The browser parses simple FROM and JOIN aliases such as from recipes r or join recipe_ingredients ri; typing r. shows recipe fields, and typing r.re filters to recipe fields beginning with re. Suggestions include column name and data type/primary-key hints immediately. Example values are lazy-loaded only for the table currently being completed, and org-scoped resources use the active organization scope for those sample values. The editor does not show a separate alias drawer; suggestions appear only while typing a recognized alias. or alias.prefix expression. The suggestion list is intentionally compact, scrolls its own rows while there is more list content, then hands wheel/trackpad scrolling back to the main page when the list reaches its top or bottom. This is an editor convenience only; it does not change the read-only SQL validation, limit behavior, protected-field selection rules, or organization scoping rules.
🔐 Permissions
Database Admin permissions are enforced server-side through inky_admin/services/permissions_service.py. Examples include:
database.viewdatabase.view_org_resourcesdatabase.view_system_resourcesdatabase.edit_org_contentdatabase.run_org_maintenancedatabase.run_system_maintenancedatabase.run_readonly_sqldatabase.run_write_sqldatabase.export
System Admin users can access platform/global resources and maintenance tooling. Organization Admin users only see org-safe resources and org-scoped data.
For organization-scoped business data, system admins do not receive a platform-wide wildcard view. A system admin in the Default organization context sees only the active Default organization business data. To inspect another organization, the system admin must intentionally switch organization context first.
🏢 Organization isolation
Resource filtering happens server-side for every role. Organization-scoped resources are filtered to the active organization context, not to a broad system-admin wildcard. This protects support/troubleshooting workflows and makes SaaS-style isolation easier to test on a single Pi.
Current organization-scoped Database Admin resources include, among others:
- Recipes, recipe edit content, recipe ingredients, recipe tags, and recipe search/index data
- Shopping lists, shopping-list items, shopping-list item sources, Household external source rows/import history, grocery departments, Kitchen Inventory items, inventory locations, and item department/storage mappings
- Inventory Products, physical Inventory Packages, recent Inventory Activity, monthly Inventory Summaries, disposition rules, and Inventory photo metadata/links
- User Mobile Gesture Preferences for per-user swipe-left/swipe-right/long-press configuration
- Organization memberships and organization backup runs, including platform/full backup runs in the Default organization
- Recipe share links, recipient targets, notifications, consumptions, and share audit rows
- Background jobs and cache status
- Devices, displays, connections, cloud-backup provider tokens/state, and organization-owned Noun Project rules/cache records
- Settings where
scope_type='organization'andscope_idis the active organization - Platform settings in the Settings resource when viewed by a System Admin, so platform + active-org settings can be reviewed together without exposing other organizations
- Setting Audit Log rows for organization settings, plus platform setting-audit rows when a System Admin is in the Default organization
- App State rows whose key belongs to the active organization, such as
recipe_cart:org:<organization_id>
Platform/global resources remain system-admin-only where appropriate. Examples include organizations, organization lifecycle events, setting definitions, connection types, provider configuration metadata, schema migrations and platform-level emoji registry/cache metadata.
For package-managed Blackcap update policy inspection, a System Admin in the Default organization can use Resources → Settings to inspect updates.automatic_check_enabled and updates.auto_apply_on_restart. Resources → Setting Audit Log shows the corresponding platform-scoped setting changes alongside Default-organization setting changes, including actor/source and masked before/after values; the scope_type filter makes platform-only review easy. Tenant organization contexts do not receive those platform rows. Update execution/history itself is not a database resource: use System → Blackcap Updates → Update History for the bounded host-persistent update/restore history and the normal Audit Log for application_update.* application events.
Do not rely on hidden UI controls for data isolation. URL changes, crafted request parameters, and direct POST/API calls must still be blocked or filtered server-side.
🧱 Provider abstraction
inky_admin/services/database_provider.py provides the active SQLite or PostgreSQL provider behind a shared Database Admin interface. Provider-specific metadata discovery, paging, row counts, health checks, SQL dialect guidance, and maintenance operations stay behind this boundary so routes and templates do not need scattered backend checks. SQLite-only operations such as PRAGMA, WAL checkpointing, and file vacuuming remain isolated from PostgreSQL; PostgreSQL catalog, connection, table-size, and planner-statistics behavior remains isolated from SQLite.
🧪 Testing checklist
- Log in as System Admin and confirm the Database nav item appears.
- Open Resource Explorer and verify system resources are visible.
- Log in as Org Admin and confirm only org-safe resources appear.
- Confirm Org Admin cannot access
/admin/database/sql. - Confirm Org Admin cannot access system-only resources by URL.
- Confirm pagination and search work on Recipes and Shopping List Items.
- Confirm dropdown filters appear for finite-value fields.
- Confirm row details open from the 🔍 emoji button and the tooltip is present.
- Confirm floating headers stay aligned while horizontally scrolling.
- Confirm pagination stays anchored inside the right resource panel and centered to that panel.
- Confirm page number jump clamps to the available page range.
- Confirm clicking a column header sorts ascending first, then toggles ascending/descending on later clicks.
- Select multiple ingredient rows and merge/update their canonical value.
- Confirm matching rows using the same old canonical values are updated.
- Confirm shopping-list item sync works when selected.
- Confirm secret setting values show as masked in Settings and detail drawers, and cannot be edited from the masked Database Admin row.
- Confirm users do not expose
password_hashin the detail drawer. - Confirm provider/connection token references, cloud-backup token payloads, cloud-backup state JSON, and secret settings are masked or hidden.
- On SQLite, run Quick Database Check and confirm an audit entry is written; confirm PostgreSQL does not show that SQLite-only action.
- On PostgreSQL, run Verify PostgreSQL Connection and confirm an audit entry is written; confirm SQLite does not show that PostgreSQL-only action.
- Confirm resource counts and Total Rows/Largest Resource/Empty Resources load asynchronously after the page shell.
- Confirm Export Schema names the active provider and includes the matching live-type guidance.
- As System Admin in the Default organization context, confirm Recipes, Shopping List Items, App State, Users, Security, and Activity views only show Default organization records; confirm Setting Audit Log shows Default-organization rows plus platform setting-audit rows, but never another tenant organization's rows.
- As System Admin, confirm the Settings resource shows active-org settings plus platform settings, including the package update policy keys when applicable, but not settings from another organization.
- Switch organization context intentionally and confirm those same org-scoped resources change to the selected organization.
- Try crafted detail/edit URLs with another org's
organization_id; confirm the active context still wins. - Try the same
recipe_idin two organizations; confirm detail/edit resolves only within active context. - Confirm Shopping List Items require both the item row and parent shopping list to belong to the active organization.
- Confirm Household Item External Sources require both the source row and parent Household List to belong to the active organization.
- Confirm a Household item associated to multiple providers shows one
shopping_list_itemsrow and multiplehousehold_list_item_external_sourcesrows. - As System Admin, run a read-only SQL query against a platform table and confirm it works.
- As System Admin, run a read-only SQL query against an organization-scoped business table and confirm it is allowed but not automatically organization-scoped; then try a protected-field resource and confirm it is blocked with guidance to use Resource Explorer or a purpose-built UI.
- Confirm write statements remain blocked.
- Confirm inactive recipes show the ♻️ restore action and active recipes do not.
- Restore an archived recipe and confirm it becomes active, search indexes refresh, and a cache build is queued.
- Restore a Paprika/file-import recipe with a preserved source image and confirm a thumbnail can be regenerated.
- Confirm
recipe_edit_contentdisplays original/converted inspection fields when present. - Confirm no mobile UI routes or navigation were added for this feature.
🧹 Data purge resources
Database Admin includes read-only operational visibility for data_purge_runs and data_purge_run_items.
Use Admin → Data Cleanup for actual purge actions. Parent resources such as Recipes and Shopping Lists should not be mass-deleted directly from Database Admin because purge requires domain-aware cleanup of related rows, search indexes, shares, source references, and cache files.
Let’s Cook resources
Database Admin includes a Let’s Cook category for session debugging and support. Resources include:
lets_cook_sessions— active/ended session headers, render overrides, review/setup phase metadata, and restore state.lets_cook_session_history— compact recipe-cooked summaries retained separately from operational session rows.lets_cook_session_recipes— recipe snapshot/state for the session.lets_cook_session_steps— completion state for each instruction.lets_cook_session_ingredients— used/not-used state for ingredients.lets_cook_timers— timer definitions and final state.lets_cook_display_state— pending display refresh/retry state for attached/local display pushes.lets_cook_scheduled_content_deferrals— display-content deferrals while a cooking session is active.
Made It rating/cleanup/photo metadata is intentionally not stored in the Let’s Cook session tables. It belongs to the Recipe resource (with Made It notes in Recipe Edit Content), so Database Admin can distinguish durable cooking-session history from the current shared Recipe review. Ingredient-density conversions used during completion are reflected through Inventory Activity/package state and the platform Ingredient Density Profiles catalog.
These tables are organization-scoped through their own organization_id columns or through the parent lets_cook_sessions row. Use Database Admin for inspection and troubleshooting only; cleanup should go through Admin → Data Cleanup so the domain-aware purge framework can apply the 7-day operational session retention and 30-day history retention safely. Display-scoped Let’s Cook render settings are visible on the displays resource, including voice_lets_cook_display_mode and show_setup_step_in_full_lets_cook. The organization-level full-recipe review preference is stored in scoped settings as lets_cook.show_full_recipe_review_before_current_step rather than on the display row.
Voice Assistant resources
Database Admin includes Voice Assistant Connections, Display Mappings, Action Mappings, OAuth Codes, and Command Log resources. Token and authorization-code hashes are masked, raw OAuth tokens are never displayed, and raw provider request payloads are not stored in the command log.
Use these resources for voice troubleshooting:
voice_assistant_connections: one row per linked provider surface, including separate rows forask Blackcap, Alexa Smart Home, and Google Home. Capability flags distinguish custom-skill and smart-home style links.voice_assistant_display_mappings: provider endpoint/device mappings to Blackcap displays. Alexa Smart Home and Google Home use one room-assignable device endpoint per enabled Blackcap display.voice_assistant_scene_mappings: cached action mappings per display. The name is legacy from the first Alexa SceneController attempt; it now acts as the provider action cache for Alexa Smart Home and Google Home, while also preserving old Alexa scene endpoint ids if they still exist in a test account.voice_assistant_oauth_codes: short-lived hashed authorization codes from account linking.voice_assistant_command_log: sanitized command audit history with provider, skill type, endpoint id, display id, resolution source, intent, status, response summary, and error category.voice_assistant_access_activity: compact country/locale access rollups used by the Connections🌍UI. It stores provider locale/marketplace, derived country, request counts, and masked/hashed server-observed IP metadata, not raw provider payloads or tokens.
Database Admin dynamic interactions
The Database page uses fetch-based partial refreshes for the diagnostic controls that are expected to be adjusted repeatedly during troubleshooting:
- running a saved resource query refreshes the active resource panel in place and updates the browser URL with the saved filter/search parameters;
- editing a saved SQL query loads the saved SQL into the SQL Console panel without a full page refresh;
- running a saved SQL query refreshes the SQL Console panel in place and records the
last_run_attimestamp; - saving or overwriting a saved query refreshes only the Database panel content needed to show the saved-query edit state;
- saving or resetting column visibility refreshes the active resource panel in place using the current filter/search/sort/page arguments.
The column picker list and SQL editor textarea are nested scroll regions. They should consume wheel input while they can scroll; once they reach their top or bottom, wheel input hands off to the Database resource panel and then to the page. This keeps long column lists and long SQL statements usable without trapping the user inside either control.
Home/System dashboard database impact
The July 2026 Home dashboard, System page, Assign Content, Return to Default, and Today & Tomorrow display updates do not add database tables or columns. Database Admin does not need a new resource for these changes. Existing resources remain useful for troubleshooting:
displaysdisplay_content_assignmentslets_cook_sessionsmeal_plan_entriesshopping_listskitchen_inventory_itemsbackground_jobs
No schema migration is required for this dashboard work.
🤖 AI resources
Database Admin exposes the complete AI and Support Chat persistence foundation as a System Admin-only AI resource group:
- AI Provider Connections (
ai_provider_connections) - AI Use Cases (
ai_use_cases) - AI Profiles (
ai_profiles) - AI Seed Accounts (
ai_drupe_accounts) - AI Organization Entitlements (
ai_organization_entitlements) - AI Seed Buckets (
ai_drupe_buckets) - AI Jobs (
ai_jobs) - AI Media Artifacts (
ai_media_artifacts) - AI Seed Reservations (
ai_drupe_reservations) - AI Seed Ledger Entries (
ai_drupe_ledger_entries) - AI Generated Image Candidates (
ai_generated_image_candidates) — generated Recipe or whole-Meal candidates are distinguished byentity_type/entity_id; protected artifact paths remain hidden. - AI Usage Events (
ai_usage_events) - Support Chat Sessions (
support_chat_sessions) - Support Chat Messages (
support_chat_messages) - Support Chat Message Sources (
support_chat_message_sources) - AI Recipe Image State (
ai_recipe_image_state)
These resources are intended for inspection and support troubleshooting. Normal provider, profile, entitlement, balance, candidate, Support Chat transcript, and image-review actions must continue through the purpose-built AI services and administration pages.
Sensitive columns are excluded from the Database resource model, including encrypted provider credentials, raw prompts, provider snapshots/options, candidate file paths and provenance, reservation allocation JSON, and private metadata/provider-usage JSON. AI Usage Events exposes normalized provider request/token fields, friendly failure reason, cached input, reasoning, estimated provider cost, Support Chat session/message correlation, prompt version, and documentation corpus version within the System Admin Database permission boundary. AI Jobs is the retained provider-correlation resource (until the use case’s AI Data Retention period expires) and exposes both provider_request_id and provider_response_id; this is useful for Claude streaming and other provider surfaces where only one provider-owned identifier may be available on a given response object. AI Profiles exposes disable_reason and consecutive_failure_count so the automatic Failed twice circuit breaker can be diagnosed without exposing provider secrets. Migration 227 also stores input_evidence_json, output_evidence_json, and evidence_artifacts_json on AI Usage Events for Recipe Photo/Social Recipe/Inventory Photo Analysis What Happened, but those three private evidence columns are deliberately hidden from generic Database Admin. Use the organization-authorized AI Usage → 🔎 What Happened viewer instead.
The three Support Chat resources are intentionally metadata-oriented:
- Support Chat Sessions exposes lifecycle, organization/user/profile, resolution, turn, prompt/corpus, and retention metadata while hiding page snapshots plus support/AI context JSON.
- Support Chat Messages exposes sequence, role, processing status, provider request, AI Usage correlation, and error category while hiding transcript
content, idempotency keys, and private message metadata JSON. - Support Chat Message Sources exposes source/document/section/rank/relevance/link metadata while hiding documentation content/document hashes.
This separation keeps generic Database Admin useful for correlation and diagnosis without turning it into an alternate private transcript/evidence browser. Use AI Usage → 🛟 View support chat for authorized conversation content and AI Usage → 🔎 What Happened for retained Recipe Photo/Social Recipe/Inventory Photo Analysis input/output evidence.
Regression suite 2026.09.02.2 verifies that all 16 AI resources appear in the Database sidebar, that AI Profiles exposes the failure-guard columns, AI Jobs exposes both provider correlation columns, AI Usage Events exposes the friendly failure reason, and all three Support Chat metadata resources continue to open through normal server-side paging while protected JSON/content/hash fields remain hidden. Support Chat Sessions can be filtered directly by organization_id and user_id, and Support Chat Messages can be filtered by session_id, making the (organization_id, user_id) ownership boundary and one conversation's message metadata easier to troubleshoot without exposing transcript content.
Direct SQL and explicit projection rules
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.pyand other database/admin inspection servicesinky_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.pynow delegates editable recipe persistence toinky_admin/services/recipe_edit_content_db_service.py.inky_admin/blueprints/user_management.pynow asksauth_service.auth_provider_presence_by_user_ids()for batched linked-provider state instead of queryingauth_provider_accountsdirectly.
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.pyallowlist as persistence code moves into repositories or*_db_service.pymodules - 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, andON CONFLICTbehavior 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.
AI Meal Plan and Meal Let’s Cook resources
Database Admin exposes safe metadata for:
meal_slot_ai_plans— the current/stale durable accepted product Meal Plan and planning options;meal_slot_ai_plan_recipes— reverse Recipe references used to protect current plans from material source Recipe edits;lets_cook_session_meal_items— session-only Meal Item snapshot rows for Meal-level Let’s Cook; legacy ready/task fields are non-executable compatibility data.
Large accepted-plan JSON, source fingerprints/reference JSON, and other internal planning payloads are hidden from generic Database Admin where configured. Full historical provider evidence belongs to the authorized AI Usage What Happened surface, not generic database browsing.
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.
Recipe adaptation metadata (schema 257+)
The organization-scoped recipes record holds original_recipe_id, adaptation_job_id, and adaptation_metadata_json for independent recipe adaptations. Database Admin shows those stored fields through its existing permitted table/column browsing and configurable-column controls; no additional table is introduced for dietary tags. For a human-readable presentation use read-only Recipe Quick View or Edit Metadata / Edit Recipe. Metadata-only changes preserve manually_edited, cache job state, rendered PDF, image and the immutable AI Usage evidence. Migration 257 supplies the indexed (organization_id, original_recipe_id) relationship lookup on both SQLite and PostgreSQL.
Adaptation details in Recipes
The recipes resource includes original_recipe_id, adaptation_job_id, adaptation_metadata_json, and Held lifecycle fields added by migration 257. Recipe Library, Quick View, and both editors use the organization-scoped canonical record for these fields. A cache rebuild updates display artifacts, not the saved adaptation relationship, dietary selections, or request. These metadata-only edits do not mark recipe content as edited or enqueue a render.