Gated Queries
A gated query is a named SQL statement saved on the server and exposed
as a single stable endpoint, POST /api/query/{name}, behind the
platform’s authentication and a per-query access rule. Callers pass
parameters, never SQL: the statement is fixed by the record, the same
security property that makes gates safe.
Gated queries are deployment-global (rows of the _raw_queries system
table in the default database, unique by name) and each declares the
target database its SQL runs against.
When raw SQL is the right tool
Section titled “When raw SQL is the right tool”The records API answers point queries on one collection with rules-based filtering. Some reads simply do not fit that shape, and those are the use cases gated queries exist for:
- reports with joins: rows assembled from several collections (orders with line items and customer names) that no single-collection query can return;
- cross-collection aggregates: dashboards that count or sum across collections, grouped and filtered in one statement;
- expressive SQL: window functions,
WITHCTEs, ordered top-N feeds,UNIONs, or projections and expressions the schema does not expose as fields; - legacy or denormalized shapes: tables populated by plugins or pipelines that have no collection schema at all.
Write the SQL once, keep it on the server, and expose it as a governed endpoint with an access rule, instead of handing callers raw SQL execution or collection rules wide enough to leak the underlying tables.
Quick start
Section titled “Quick start”# once, as admin: save a parameterized querycurl -X POST "$VSKI_URL/api/collections/_raw_queries/records" \ -H "X-API-Key: $ADMIN_KEY" -H "Content-Type: application/json" \ -d '{ "name": "posts_by_author", "db": "default", "sql": "SELECT id, title FROM posts WHERE authorId = ? ORDER BY created DESC", "executeRule": null }'
# execute: the body is the positional parameter listcurl -X POST "$VSKI_URL/api/query/posts_by_author" \ -H "X-API-Key: $VIEWER_KEY" -H "Content-Type: application/json" \ -d '["u_123"]'The SDKs wrap the call side:
rows = client.query.execute("posts_by_author", ["u_123"])and in TypeScript:
const result = await client.query.execute("posts_by_author", ["u_123"]);Managing gated queries
Section titled “Managing gated queries”Gated queries are records of the _raw_queries system collection (admin
only; the system collection has empty default rules, which deny
non-admins):
| field | meaning |
|---|---|
name | unique, [a-zA-Z0-9_-]{1,64}; used in the route /api/query/{name} |
sql | the fixed SQL statement; ? placeholders bind to the call’s parameters |
db | target database the statement executes against (default unless set) |
executeRule | access rule: null/"" = admins only, "*" = any authenticated caller, or a rule expression |
Two supported workflows:
- Dashboard: the Databases view (SQL console) has a Gated Queries table under the storage stats. The Execute button’s dropdown has Create Gated Endpoint, which opens a dialog pre-filled with the console’s SQL and database and asks for the name (default access: admin only). The table’s edit action opens a two-tab form: SQL (database, name, statement) and Access (the execute rule, with the same admin-only / public / expression presets as collection rules).
- IaC: gated queries are a managed kind in declarative IaC (see
below), so they converge with
vski plan/apply/pulllike any other resource.
Calling a gated query
Section titled “Calling a gated query”Route: POST /api/query/{name}, behind the global auth middleware: every
caller must present an API key or a token (anonymous requests get 401
before the query is even looked up).
The query’s executeRule is then evaluated with the caller’s identity:
- admins always pass;
- an empty rule denies every non-admin (
403); this is the default; "*"allows any authenticated principal;- an expression (
@request.auth.id != "",'editor' in @request.auth.roles, …) is evaluated exactly like a collection rule, against the caller’s id and roles (see Rules and Access Control). Record data is not available to query rules.
The request body is a JSON array bound positionally to the ?
placeholders ([] when the statement takes none). Parameters are bound
through the SQL driver, so caller input cannot change the statement. The
response is the result set (columns + rows) as JSON.
The statement runs through the server’s write queue, so a gated query that writes also reaches the replication changeset log; replication is safe. Gated queries are intended for reads, though: the endpoint does not report rows affected.
Raw, unrestricted SQL for admins remains
POST /api/sql/execute(admin only, with adryRunEXPLAIN mode). That is the console’s Execute button, not this endpoint.
Infra as Code
Section titled “Infra as Code”Gated queries are a global IaC kind, converged like collections or cron:
version: 1
queries: posts_by_author: db: default # target database for the statement sql: "SELECT id, title FROM posts WHERE authorId = ? ORDER BY created DESC" rules: execute: "'editor' in @request.auth.roles" stats_total: sql: "SELECT count(*) AS total FROM posts" rules: execute: "*" # any authenticated callernull ≡ "" (admin-only), "*" is any authenticated caller, anything
else is a rule expression, the same conventions as collection rules.
state: absent deletes a query; --prune removes undeclared ones;
vski pull exports live queries to vski/queries.yaml. See
Infra as Code.
(Record data seeding with vski seed is a different
tool: seeds insert rows into user collections; system tables like
_raw_queries are managed by IaC.)
Security notes
Section titled “Security notes”- Only admins can create, list, update or delete gated queries.
- The statement is fixed by the record: a caller allowed by the rule can
run exactly that SQL with exactly the parameters they pass. Still,
treat query-creation rights as sensitive as raw SQL execution: an
executeRule: "*"query over a sensitive table is a data leak. - Prefer the narrowest rule that works; admins bypass rules entirely.
- Names are URL segments: keep to
[a-zA-Z0-9_-].
See also
Section titled “See also”- Infra as Code: the
querieskind - Rules and Access Control: rule syntax
- Gates: the same pattern for HTTP and plugin targets