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.

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.

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
recipe edit content
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
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
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.value when is_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.

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 visible-column preferences in user_database_page_preferences. The collapsed summary shows the visible/available column count. The expanded editor lists one column per row with a checkbox and a short sample value from the current page when available, making noisy columns easier to identify before showing or hiding them. Available 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 to the resource default columns. 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 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. 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 preference save/reset, async saved-query save/run/delete, the on-demand Data Problems tab, and API Tester registry coverage for the new 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_tokens is organization-scoped. The encrypted token_json value is masked, and provider metadata that could expose account details is hidden.
cloud_backup_state is organization-scoped. The raw state_json payload is hidden; use the Backup page for normal status/history review.
organization_backup_runs is organization-scoped. In the Default organization, full/platform backup attempts are included with backup_scope='platform'.
noun_project_rules is organization-scoped and replaces the old file-backed NounProjectWords.csv rule 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_status for troubleshooting states such as client_waiting, client_linked, client_displayed, and client_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

Meal Planner introduces organization-scoped planning data for week start settings, meal slots, meal plan entries, and links between meal plans and generated shopping lists. 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 use list_kind='household' and is_builtin=1.
shopping_list_items: includes normal list rows and Household List rows. For Household items, the external_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 contain milk. 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, and external_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 in household_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.

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 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.view
database.view_org_resources
database.view_system_resources
database.edit_org_content
database.run_org_maintenance
database.run_system_maintenance
database.run_readonly_sql
database.run_write_sql
database.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
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' and scope_id is 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
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.

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_hash in 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, Setting Audit Log, Shopping List Items, App State, Users, Security, and Activity views only show Default organization records.
As System Admin, confirm the Settings resource shows active-org settings plus platform settings, 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_id in 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_items row and multiple household_list_item_external_sources rows.
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_content displays 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.

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 for ask 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_at timestamp;
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:

displays
display_content_assignments
lets_cook_sessions
meal_plan_entries
shopping_lists
kitchen_inventory_items
background_jobs

No schema migration is required for this dashboard work.

🤖 AI resources

Database Admin exposes the complete AI 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 Seed Reservations (ai_drupe_reservations)
AI Seed Ledger Entries (ai_drupe_ledger_entries)
AI Generated Image Candidates (ai_generated_image_candidates)
AI Usage Events (ai_usage_events)
AI Recipe Image State (ai_recipe_image_state)

These resources are intended for inspection and support troubleshooting. Normal provider, profile, entitlement, balance, and candidate 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. The AI Usage Events resource exposes normalized provider request/token columns only within the System Admin Database permission boundary.

Regression suite 2026.07.30.4 verifies that all 12 resources appear in the Database sidebar, that AI Usage Events opens through normal server-side resource paging, and that protected JSON fields remain hidden.

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.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

🧭 Start Here

🚀 Deploy Blackcap

🛠️ Administer Blackcap

🍽️ Use Recipes

📅 Plan Meals

🛒 Use Shopping Lists

🧺 Manage Kitchen Inventory

🖥️ Use Displays

🧑‍🍳 Cook with Let’s Cook

🤖 Use and Administer AI

🧩 Use the Chrome Extension

🎮 Play Games

🔌 Integrations

⚙️ Develop Blackcap

⚠️ Troubleshoot Blackcap

Database Administration