Database Console

Database Console

The Database console screen (Administration → Database, /admin/database, ADMIN-only) gives authorised administrators read-only SQL access to the entire shared PostgreSQL database. All service schemas live in the single PostgreSQL instance that the master-data datasource reaches, so every schema is visible from one place.

UI

Navigate to Administration → Database in the sidebar.

Schema / table tree (left panel)

Lists every non-system schema returned by GET /api/master-data/admin/db/schemas. Each schema is collapsible; the first schema that has tables opens automatically. Clicking a table:

1. Inserts select * from <schema>.<table> limit 100 into the SQL editor.
2. Runs it immediately.
3. Shows the table's column names and types beneath the selected table name (also available as a hover tooltip).

SQL editor (main panel)

A monospace editor accepts a single SQL statement. Execute with the Run button or Cmd/Ctrl+Enter. The last query is saved to localStorage (openwcs.admindb.sql) so the console reopens where you left off.

Results appear in the shared DataTable (client-side filter / sort / paging) together with:

• Row count and, when the cap was hit, a truncated badge.
• Execution time in milliseconds.
• An error banner that surfaces the PostgreSQL message verbatim when the query is rejected or fails.

Backend

/api/master-data/admin/db — ADMIN-only (gateway enforces X-Auth-Roles).

Endpoint	Description
GET /schemas	Non-system schemas with their tables and column name / type from information_schema.
POST /query	Execute a read-only query. Body: { "sql": "…", "maxRows": 200 }. Returns { columns, rows, rowCount, truncated, durationMs }.

POST /query safety layers

1. Statement validator — masks string literals, quoted identifiers, dollar-quoted blocks, and nested comments, then rejects: multi-statement input (; after the first statement), and any keyword that modifies data or schema — INSERT, UPDATE, DELETE, MERGE, TRUNCATE, DROP, CREATE, ALTER, EXECUTE, DO, COPY, CALL, EXPLAIN, and data-modifying CTEs. Violations return HTTP 400 with a plain-English message.
2. Read-only transaction — the query runs with autocommit off after SET TRANSACTION READ ONLY and is always rolled back, so anything that slips past the validator (e.g. nextval()) fails at the database level.
3. Timeout — SET LOCAL statement_timeout = 10s keeps runaway queries from tying up the connection pool.
4. Row cap — default 200 rows, configurable up to a hard maximum of 1 000. The response includes truncated: true when the cap was hit.

Response value serialisation

Values are typed as string, number, boolean, or null. Timestamps become ISO-8601 strings; exotic PostgreSQL types fall back to their text representation.

Audit logging

Full SQL is logged at DEBUG only. Every executed query produces an INFO audit line with the acting user (from X-Auth-User), duration, and row count — no query content is written at INFO.

Access

ADMIN role required. Gated by the screen permission catalog (auth/screens.ts) on the frontend and by X-Auth-Roles header enforcement in AdminDbController on the backend. The endpoint lives on the master-data service because that datasource reaches the shared database.

Related: System Info · Services · Security.