How saved queries work

Analytics stores queries and runs them on request. A query is a definition you save once and execute many times with different parameter values, rather than a statement you send with each call.

Saved queries

A saved query belongs to exactly one app, addressed by a slug generated from its name when it is first created. The app comes from where the query is created, never from the query's own definition. The slug is stable — renaming a query changes its display name and leaves the slug alone, so callers that already reference it keep working.

Slugs are unique across the whole site, so a name already used by a query in another app gets a numbered slug, such as monthly-revenue-2.

Field	Holds
Query text	The statement itself
Connection mode	Internal or external
Connection secret	The secret's key — external queries only
Description and tags	Optional, for discovery; the description holds at most 1,000 characters

When a query is checked

The query text is checked against its connector every time the query is saved. A query that saves has passed these checks:

• it is exactly one statement, and that statement is a read — a write placed anywhere inside it, including inside a subquery or a WITH clause, is refused;
• it calls none of the blocked functions, which are functions that pause the database, read server files, or reach other systems;
• every placeholder follows the placeholder rules below;
• every stored secret it names exists for the site and app, and each one sits in a filter;
• for an internal query, every table it names is a data table of the same app and no other schema is named; and
• for Elasticsearch, the body is valid JSON with no script or runtime_mappings key at any depth.

No save check proves that a query returns the right rows; data, permissions, and connectivity are evaluated when it runs.

Connection modes

Every query declares one of two connection modes. This single choice decides whether credentials are involved, which connector — the piece of Analytics that knows how to talk to one specific kind of database, such as PostgreSQL or Elasticsearch — runs, and what the query can reach.

	Internal	External
Reaches	The calling site's own schema	A database outside the platform
Credentials	None	A connection secret
Table names	Logical names, resolved for you	The real names in that database
Moves between sites	Yes, without reconfiguration	Only if an equivalent secret exists

Internal

An internal query runs against the schema your app's data tables already live in. There is no second database and no credential to manage, which makes it the shortest path to a first result. Because the definition names no host, database, or schema, the same query resolves to each site's own data — this is what makes an internal query portable between the sites an app is installed in.

External

An external query runs against a database you operate. Its credentials live in a connection secret, and the type of that secret decides which connector handles the query. The query stores the secret's key, never the credential values. External is the default mode — a query that does not declare a mode is treated as external and therefore requires a connection secret.

Table references in internal queries

Internal query text uses the logical name given to the data table. Analytics resolves it to the underlying physical name when the query runs, so the app prefix is never required — physical names continue to work too, for a query that already uses them.

SELECT status, count(*) FROM orders GROUP BY status

When an internal query is saved, every table it names is checked against the data tables in the same app. A name that matches neither a logical nor a physical table is reported at that point, with the unrecognized names listed — and only tables in the same app are visible, so naming a table that belongs to a different app is an unknown table, not a permission error. A name the query defines for itself with WITH is not treated as a table, so it is never reported as unknown.

Placeholders and bound values

A placeholder marks a position in the query text where a value goes, written in double curly braces: {{ status }}. When the query runs, each value is sent to the database separately from the query text, so it is always treated as data and cannot change what the query does.

SELECT id, total FROM orders WHERE status = {{ status }} LIMIT 100

Placeholder	Written as	Value comes from
Caller parameter	{{ status }}	The params sent with the run
Stored secret	{{ secret.region_filter }}	A secret, resolved on the server
Field of a stored secret	{{ secret.warehouse.region }}	One field of a secret that stores JSON
User profile	{{ secret.user_profile.email }}	The authenticated caller

A placeholder names one value and nothing else. The rules follow from that:

Written	Result	Instead
'{{ status }}'	Accepted; the quotation marks are dropped	—
'%{{ term }}%'	Refused: a placeholder that is only part of a quoted text	Build the text in SQL, for example concat('%', {{ term }}, '%')
"{{ column }}" or FROM {{ table }}	Refused: a placeholder stands for a value, not a column or table name	Name the column or table in the query text
{{ limit }}::int	Refused: a :: cast directly after a placeholder	cast({{ limit }} as int), or a space before ::
{{ status | default('open') }}	Refused: filters and expressions are not supported	Do the transformation in SQL
{% if … %} or {# … #}	Refused: template control blocks are not supported	Write separate queries
{{ secret }}	Refused: names the secret store as a whole	Name one secret

Every caller parameter is required. A run that leaves one out fails with Query placeholder {{ status }} has no value.

A name used twice in one query gets the same value both times. A list value works with IN ({{ statuses }}); an empty list is refused.

Elasticsearch bodies

An Elasticsearch query body is JSON, so its placeholders sit inside JSON strings: "{{ status }}". A placeholder outside a string would not be valid JSON and is refused when the query is saved, and a placeholder cannot be used as a key. A value that is not text, such as a number, is sent as its JSON text inside that string. A list is sent as a JSON array.

Reference a secret

Separate from the connection secret an external query uses to authenticate — any query, internal or external, can reference a stored secret by key for a value rather than a connection. It must already exist when the query is saved, and is resolved on the server when the query runs:

SELECT * FROM orders WHERE region = {{ secret.region_filter }}

In SQL queries, a stored secret is allowed only in the parts of a statement that decide which rows are read: a WHERE condition, a JOIN ... ON condition, or a HAVING condition. A query that places one anywhere else, such as in the list of returned columns or an ORDER BY, is refused when it is saved.

Never pass a secret as a parameter: parameters come from callers, secrets come from storage, and a parameter name starting with secret. is rejected.

The built-in user profile

A built-in profile describes the authenticated caller — identifier, username, email address, first and last name, and whether the account is a platform administrator. It uses the same secret. syntax as a stored secret, but you never create or store it yourself — Analytics provides it automatically for every authenticated caller:

SELECT * FROM orders WHERE customer_email = {{ secret.user_profile.email }}

This lets one saved query serve every user while returning only that user's rows. The profile is only available to an authenticated caller — a query that references it cannot run anonymously. Because it describes the caller rather than stored data, the profile is not limited to filters and can appear in any value position.

Executions

Both SDKs return the full execution envelope: result rows are the top-level data, with total and execution_key beside them:

{
  "status": "success",
  "message": "Query executed successfully",
  "data": [{ "status": "paid", "revenue": 4820.5 }],
  "total": 1,
  "execution_key": "3f6b1d90-8c2a-4e77-a1b5-90c7e4d2f118"
}

The Console shows the same rows, but counts the rows it received instead of using total, and doesn't show execution_key. The execution response format describes the underlying HTTP response; use the SDK methods in your application.

A run that fails before it reaches the database — a missing connection secret, a deleted referenced secret, or a parameter named secret.* — is not recorded and returns no execution_key. Every other run is recorded with execution_key, the raw parameter values supplied, connection and referenced-secret key names, the outcome, and either a row total or an error message. Sensitive text is removed from error messages where possible, but don't pass sensitive values as parameters. execution_key identifies the run; past runs aren't shown in Console or returned by either SDK.

A run that would return more rows than its connector's maximum fails rather than returning part of the result — see Bound every query.

Connector	A run returns
Internal, PostgreSQL, MySQL, Redshift, ClickHouse	One row per matched record
Elasticsearch, no aggregations	One row per matching document
Elasticsearch, with aggregations	One row per aggregation bucket, tagged with the aggregation name

Related

• Create and manage a saved query
• Query your own site's data
• Connect an external database
• Run a query and read the results
• Security and limits
• Analytics reference
• Troubleshoot Analytics