1 Gated Queries
Anton Nesterov edited this page 2026-11-09 15:09:32 +01:00

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

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, WITH CTEs, 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

# once, as admin: save a parameterized query
curl -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 list
curl -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

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 / pull like any other resource.

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 a dryRun EXPLAIN mode). That is the console's Execute button, not this endpoint.

Infra as Code

Gated queries are a global IaC kind, converged like collections or cron:

# vski/queries.yaml
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 caller

null ≡ "" (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

  • 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