Query Studio

Query Studio is the in-app workspace for running ad-hoc and saved queries against the Billerang object model. It lives under Workbench → Query Studio in the sidebar.

⚠ Read this first if you are an operator: Trust model. Query Studio's MVP assumes a single trust boundary per deployment.

Three modes

Mode	What you write	When to use
Quick Query	Point-and-click — entity + fields + filter chips	Everyday slices, exports, ops dashboards
Deep Query	Hibernate Query Language (HQL)	Multi-entity joins, aggregations, polymorphic queries
Expert Query	Native PostgreSQL (super-admin role only, async-only)	Forensic / one-off audit queries needing raw column access

The UI never exposes the words "HQL" or "SQL" — those are internal labels. The brand is always Quick / Deep / Expert.

Roles

Keycloak role	Grants
queryUser	Open Query Studio, run Quick + Deep, save own queries, schedule own queries, share own queries
queryManagement	Everything in queryUser plus: edit/delete other users' queries, manage system templates
queryExpert	Adds the Expert mode tab and the right to submit Expert queries

Roles compose. A user who needs Expert access typically has queryUser + queryExpert. Templates can only be edited by users with queryManagement.

Assign roles in the Keycloak admin console under Realms → billerang → Users → {user} → Role mappings.

Saving and replaying

Every query (regardless of mode) can be saved to the report_query table via the same POST /api/rest/v2/reportQueries endpoint. The saved row carries:

• code — the user-friendly id (e.g. MY_OPEN_INVOICES_PX). Append-only convention is BLR_* for system templates, anything else for user queries.
• description, tags, targetEntity
• visibility: PRIVATE (only you) / PROTECTED (your org) / PUBLIC (any signed-in user)
• queryType: VISUAL / HQL / NATIVE_SQL (internal — surfaced as the mode brand in the UI)
• advancedQuery (jsonb) for Quick — the filter / projection / sort
• generatedQuery (long text) for Deep / Expert — the actual HQL or SQL

Opening a saved query rehydrates the builder in the right mode. Saving an existing system template (is_system=true) is rejected for non-admin users — clone via "Use template" first.

Sync vs Async

Aspect	Sync	Async
Where it runs	Request thread, bound by sync timeout	Background job, polled via QueryExecutionResult
Result delivery	Inline JSON	File on disk + email notification (optional)
Default cap	1 000 rows	1 000 000 rows
Default timeout	30 s	600 s
When auto-suggested	—	When estimated row count > 1 000

The Quick + Deep modes support both. Expert is async-only — the server always submits a job and returns {queryExecutionResultId, status: "ACCEPTED"} for client polling. The QueryResultView page (/workbench/queryStudio/{code}/results/{id}) handles the polling and download.

Scheduling

Any saved query can be scheduled to run on a cron-like timer and email the result file:

1. Open the saved query in the builder.
2. Click Schedule.
3. Pick frequency (Daily / Weekly / Monthly), hour + minute, file format (CSV / Excel / PDF / JSON), and recipient emails.
4. Save.

Behind the scenes this creates a QueryScheduler row tied to a JobInstance. The job runs at the chosen cadence; results are written under {providerRoot}/exports/queryStudio/{user}/{date}/. Files are pruned automatically by the nightly cleanup job — see Governor limits for the retention knob.

Sharing

Every saved query has a stable code, so the URL /workbench/queryStudio/{code} is the canonical shareable link. The receiver:

• Must be authenticated (no anonymous public links in MVP).
• Sees the query only if their visibility allows it (PRIVATE only the creator, PROTECTED the same org, PUBLIC any signed-in user).

The "Copy link" action gives the URL plus a small toast indicating who can open it.

Templates

The Templates tab lists system queries seeded on every backend startup from YAML files in billerang-admin/ejbs/src/main/resources/templates/queries/. They cover the common ops slices (open invoices, failed payments, new customers, draft transactions, …). Click Use template to clone one into a new private editable query owned by you.

Templates can be customised per deployment by mounting an override directory at ${workbench.templates.configDir} — files there override the shipped defaults on the next bootstrap.

Architecture map

Frontend (billerang-frontend)
  /workbench                                hub
  /workbench/queryStudio                    list (Mine | Shared | Public | Templates)
  /workbench/queryStudio/new                builder
  /workbench/queryStudio/:code              builder, prefilled from saved row
  /workbench/queryStudio/:code/results/:id  result view + download

Backend (billerang-backend)
  GET  /api/v1/workbench/schema/entities                    entity catalog (drives the EntityPicker + autocomplete)
  GET  /api/v1/workbench/schema/entities/{entity}           per-entity field + DB-mapping metadata
  POST /api/v1/workbench/expert/execute                     Expert Query (async-only, role-gated)
  POST /api/rest/v2/generic/all/{entity}                    Quick Query data path
  GET  /api/rest/query?query=...                            Deep Query data path (HQL)
  GET  /api/rest/v2/reportQueries                           list + load saved queries
  POST /api/rest/v2/reportQueries/{id}/execute              run a saved query (async or sync)
  POST /api/rest/v2/reportQueries/{id}/queryScheduler       schedule
  GET  /api/rest/v2/reportQueries/queryExecutionResults/{id}  poll a result
  GET  .../{id}/download/{format}                           CSV / EXCEL / PDF / JSON download

Next

• Quick Query walkthrough
• Deep Query language guide
• Expert Query (privileged)
• Governor limits
• Trust model — required reading before enabling on multi-tenant deployments