Query Studio power moves

Query Studio gives operators three doors into the platform data: Quick, Deep, and Expert. Most of the friction people hit is not "how do I write the SQL," it is not knowing which door to use, what gets silently truncated, and what the governor will do to you when you push too hard. These tips save the round trip.

Expert mode needs two roles, not one

Having queryUser is not enough to see the Expert tab. You also need queryExpert in Keycloak. Without it, the Expert segment in the mode toggle is simply hidden in the UI, and if you hit the endpoint directly anyway you get a 403. If a teammate says "I don't see Expert mode," check their roles before you check the frontend.

Expert is async only, always poll the QER

There is no synchronous path for Expert queries, by design. The submission flow is: validate, apply the forced LIMIT, pre-create a QueryExecutionResult row, kick off an @Asynchronous EJB method, then poll.

POST /api/v1/workbench/expert/execute
{ "query": "SELECT count(*) FROM billing_account", "limit": 1000, "format": "CSV" }

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

The frontend polls every 2 seconds until the QER's endDate is set. If you are scripting against this endpoint instead of using the UI, build the same poll loop, do not expect a body with results in the initial response.

Use the schema endpoint, not information_schema

information_schema and pg_* are explicitly blocked at validation (400, "system catalogs not permitted"). That is not an oversight, it is schema-sniffing prevention. For column and table discovery, use the safe path instead:

GET /api/v1/workbench/schema/entities/{entity}

This is also what the Monaco editor's autocomplete pulls from, including the dbMapping block, so anything the editor can suggest is fair game for a query.

The custom-field jsonb sniff is the reason Expert mode exists

Most Expert queries people actually need are forensic lookups against columns the JPA model does not expose, like custom field values stored as jsonb:

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

If you find yourself reaching for Expert mode for routine reporting instead of this kind of forensic dig, you are probably better served by Quick or Deep, both of which leave a much lighter audit footprint.

Sync and async have different row and time caps, know which one truncates silently

Knob	Default	Applies to
maxRowsSync	1,000	Quick / Deep sync — truncated silently, no error
maxRowsAsync	1,000,000	Expert / async — forces a LIMIT, not silent
maxQueryDurationSyncSec	30	Sync statement timeout
maxQueryDurationAsyncSec	600	Async job timeout

The dangerous one is maxRowsSync: a Quick query against a table with 50,000 matching rows will happily return exactly 1,000 with no warning. If a count looks suspiciously round, check whether you hit the sync cap before you trust it.

A 429 tells you exactly which knob you hit

The governor does not fail vaguely. GovernorViolationException maps to a 429 with a body that names the limit:

{
  "code": "GOVERNOR_LIMIT_EXCEEDED",
  "limit": "maxConcurrentAsyncPerUser",
  "current": 3,
  "max": 3,
  "retryAfterSeconds": 30,
  "message": "Async query concurrency limit reached (current=3, max=3)"
}

Concurrency is computed globally across nodes (a live SQL count against query_execution_result, not an in-memory counter), so you cannot dodge the concurrency cap by hitting a different app node. Rate limiting, by contrast, is per-node in-memory, so on a multi-node cluster the effective per-minute rate can be a little looser than the configured number suggests.

Every governor knob is hot-reloadable, including per-tenant

All workbench.governor.* settings read from the standard Billerang provider settings, so changing one does not need a WildFly restart. You can also scope a knob to a single tenant:

pluxee_be.workbench.governor.maxRowsSync=5000

Useful when one tenant legitimately needs bigger sync exports and you do not want to loosen the default for everyone else.

Do not disable the governor in production

workbench.governor.enabled=false short-circuits every check, with no upper bound on the load Query Studio can put on the database. It exists for incident-response diagnosis only, flip it back on the moment you are done. If you are tempted to disable it because legitimate queries keep 429ing, tighten or loosen individual knobs instead, that is what they are for.