Expert Query

⚠ Read this first: Trust model. Expert mode bypasses Opencell's SecuredBusinessEntity row-level filter entirely.

Expert Query is the privileged-only mode that runs native PostgreSQL directly against the platform database. It exists for forensic and audit queries that need columns or tables the JPA model doesn't expose. It is async-only by design — every submission goes through a background job, and the SQL never runs on a request thread.

Who can use it

• The user must have the queryExpert Keycloak role (in addition to queryUser).
• Without that role, the Expert segment in the mode toggle is hidden, and the underlying endpoint returns 403.

The role is intentionally hard to grant. See the trust model checklist before assigning it on a multi-tenant deployment.

How a submission flows

1. You write a SELECT (or WITH, or EXPLAIN) in the Monaco editor. Autocomplete pulls DB table names + column names from GET /api/v1/workbench/schema/entities/{entity} — including the dbMapping block — plus a small set of PostgreSQL helper functions (coalesce, now, date_trunc, extract, lower, upper).
2. You click Submit async. The frontend POSTs to /api/v1/workbench/expert/execute:

   { "query": "SELECT count(*) FROM billing_account", "limit": 1000, "format": "CSV" }

3. The server validates, applies the LIMIT, calls the governor, pre-creates a QueryExecutionResult row (status null, endDate null), and returns:

   { "queryExecutionResultId": 12345, "status": "ACCEPTED" }

4. The actual SQL runs in an @Asynchronous EJB method. The result is written to:

   {providerRoot}/exports/queryStudio/{user}/{date}/expert-{queryExecutionResultId}.csv

   The QER row is updated with status / line count / duration / file path.
5. The frontend polls every 2 s. When the QER's endDate is set, you see the success / failure status and a CSV download link.

What's blocked at validation time

The validator rejects, with a 400 BadRequest:

• Anything that doesn't start with SELECT, WITH, or EXPLAIN (case-insensitive).
• Any presence of: delete, update, insert, drop, alter, create, truncate, grant, revoke, exec, execute, call, merge.
• ; (multi-statement separator).
• -- and /* (comment markers — block-comment evasion).
• pg_* and information_schema (system catalogs — schema sniffing is not allowed).

What's enforced at execution time

• Read-only JDBC transaction — the connection is set read-only.
• Forced LIMIT — appended to the query if missing, capped at workbench.governor.maxRowsAsync (default 1 000).
• Statement timeout — set local statement_timeout set to workbench.governor.maxQueryDurationAsyncSec (default 600 s).
• Audit log on every submission and result: {user, timestamp, queryHash, query, rowCount, executionMs, status}.

Examples

A safe row count:

SELECT provider_id, COUNT(*) AS rows_per_tenant
FROM billing_account
GROUP BY provider_id

A custom-field jsonb sniff (a column that's deliberately not exposed via JPA):

SELECT id, code, cf_values
FROM customer_account
WHERE cf_values @> '{"PX_BILLING_GROUP": "BG2"}'::jsonb
LIMIT 50

A schema check that gets rejected:

-- 400 BadRequest: "system catalogs not permitted"
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'billing_account'

For schema discovery, use the safe path: Schema reference or GET /api/v1/workbench/schema/entities/{entity}.

Limits (consolidated)

Knob	Default	What it gates
workbench.governor.maxRowsAsync	1 000 000	Async row hard cap (overridden to maxRowsSync if set lower)
workbench.governor.maxQueryDurationAsyncSec	600	Async statement timeout
workbench.governor.maxConcurrentAsyncPerUser	3	Per-user in-flight async queries
workbench.governor.maxQueriesPerMinutePerUser	30	Per-minute rate (Expert counts toward this)

See Governor limits for the full table.

What this mode is NOT for

• Not for everyday analytics — use Quick or Deep. Expert leaves an audit trail every operator will ask about.
• Not for write paths — DDL and DML are blocked at the validator. Mutations go through the platform's REST APIs.
• Not for data exfiltration — every submission is logged with the SQL text, the user, and the row count. Treat it like a privileged shell.
• Not safe on multi-tenant deployments without the V2 RLS/FLS work — see the trust model.

Saving

A saved Expert query persists queryType=NATIVE_SQL and the editor body in generated_query. Reopening loads the editor in Expert mode (assuming the user still has queryExpert). System templates (is_system=true) cannot be Expert queries — only queryManagement users can edit those, and the seeding pipeline only ships VISUAL templates.

Scheduling

A scheduled Expert query runs as a JobInstance at the configured cadence — same flow as Quick / Deep schedules, with the same pruning rules under {providerRoot}/exports/queryStudio/{user}/{date}/.