# Saved queries

A saved query is SQL that an admin wrote and approved for one datasource. It has typed parameters, and `db_query` runs it by name. When a saved query covers a question, the agent fills in values and writes no SQL, so it has no table or column name to guess. A report built on one returns the same answer every run.

```text
db_query(datasource: "reports-db", template: "active_orgs",
         params: '{"start_date": "2026-10-01", "end_date": "2026-11-01"}')
```

Questions that no saved query covers still go through ordinary `db_query` SQL.

## How the agent finds them

When at least one saved query is approved, `db_query` is always offered (pinned). Its description lists the approved queries with their datasource, description and parameters, for example:

```text
Saved queries, approved by an admin. When one answers the question, run it by name instead of writing SQL: …
- active_orgs [reports-db]: Organisations with at least min_requests events in a period — params: start_date date required, end_date date required, min_requests int ≥1 =100
```

The list shows at most 20 queries, or about 3000 characters; any further queries are listed by name only (30 names, then a count). It shows only the queries this run can actually run. In a delegated run, that means PostgreSQL datasources with a `delegated_role`. The agent picks one from the list. It does not depend on the question sharing words with the query, so a question in Mongolian finds an English description too.

Give the agent either `query` (its own SQL) or `template`, not both. A call with both is refused.

## Writing or drafting one

Saved queries run on PostgreSQL, MySQL and ClickHouse datasources, not on MongoDB. A saved query enters the list as an unapproved **draft** in three ways:

1. **On the dashboard.** Open **Integrations**, and click **Saved queries** on a datasource's card. The dialog lists the datasource's queries and shows each one's status: draft, approved version, approval that no longer holds, or deprecated. From there you can:
   - write a new query, or a new version of an existing one;
   - click **Mine from traces** to turn repeated successful `db_query` calls from recent traces into drafts;
   - **Test** a version with values (20 rows at most);
   - **Approve** it, which shows the exact SQL, parameters and hash first;
   - revoke, deprecate or delete it.
2. **Directly from the agent (`save_draft`).** In a conversation, the agent can pass `save_draft: "<name>"` and `description: "<one line>"` to `db_query` (writing parameters as `{name}` in `query` and passing sample values in `params`, or passing a plain query whose filter literals are extracted as parameters automatically). `db_query` runs the query against the database first; only if it succeeds is it saved as a draft version.
3. **Automatically from repeated runs.** When the same read-only query shape (excluding schema probes such as `SHOW`, `DESCRIBE`, `information_schema` and `LIMIT 1`) succeeds at least 3 times or across at least 2 sessions, GoClaw extracts its date, datetime, string and numeric filter literals into typed `{name}` parameters (with the observed values as defaults) and saves it as a draft. The daily `trace_prune` maintenance job also mines recurring queries before pruning old traces. Deleting a draft permanently vetoes that SQL shape so automatic mining does not recreate it.

**Through the API.** The dialog uses the API below, which is also open to any client with the gateway token.

Make saved queries through the dashboard API, `/api/saved-queries`. That API takes the gateway token.

```json
POST /api/saved-queries
{
  "action": "save",
  "datasource": "reports-db",
  "name": "active_orgs",
  "description": "Organisations with at least min_requests events in a period",
  "wiki_slug": "metrics/active-organisation",
  "body": "SELECT org_id, count(*) AS events FROM events WHERE day >= {start_date} AND day < {end_date} GROUP BY org_id HAVING count(*) >= {min_requests} ORDER BY events DESC",
  "params": [
    {"name": "start_date", "type": "date", "required": true},
    {"name": "end_date", "type": "date", "required": true},
    {"name": "min_requests", "type": "int", "default": 100, "min": 1}
  ],
  "note": "first version"
}
```

- **`name`.** Use 3–64 lower-case letters, digits and `_`, starting with a letter. A name is unique per datasource.
- **`description`.** One line of up to 200 characters, with no control characters. It is what the agent reads, so state what the query answers. Business terms such as "active" belong in the body, not in a parameter the agent has to choose. The headings goclaw writes into tool descriptions are refused here, and so are they in enum values.
- **`wiki_slug`** (optional). Names the org wiki page that defines the terms the query uses.
- **`body`.** One read-only query: `SELECT`, `WITH`, `SHOW`, `DESCRIBE` or `EXPLAIN`. Write each parameter as `{name}`.
  - A `{name}` inside a string literal, a quoted identifier or a comment is left alone.
  - Do not write ClickHouse's `{name:Type}` yourself, or `{ name }` with spaces. The type comes from the parameter's declaration.
  - Do not write the driver's own placeholders either: `$1` on PostgreSQL, `?` on MySQL. PostgreSQL's jsonb `?` operator is fine.
  - Placeholders and comments are found the way the datasource reads them. On MySQL and ClickHouse, `#` starts a comment and a backslash escapes inside quotes, so a `{name}` in a MySQL comment is not a placeholder.
  - Table and column names cannot be parameters.
- **`params`.** Each parameter is either `required` or has a `default`. The types are:

| type | value | PostgreSQL | ClickHouse | MySQL |
|---|---|---|---|---|
| `date` | `YYYY-MM-DD` | `$n::date` | `{p:Date}` | `?` |
| `datetime` | `YYYY-MM-DD HH:MM:SS` (UTC) or RFC 3339 (converted to UTC) | `$n::timestamptz` | `{p:DateTime('UTC')}` | `?`, as UTC text |
| `int` | a whole number; `min`, `max`. Above 2^53, give it as text in `params` | `$n::bigint` | `{p:Int64}` | `?` |
| `number` | a number; `min`, `max`. Text is bound exactly | `$n::numeric` | `{p:Float64}` | `?` |
| `string` | text, at most `max_len` (default 200) | `$n` | `{p:String}` | `?` |
| `enum` | one of `values` | `$n` | `{p:String}` | `?` |
| `bool` | `true` / `false` | `$n::boolean` | `{p:Bool}` | `?` |

Values are always passed to the driver as bound parameters, never spliced into the text. A value that is not of its parameter's type is refused, and the error names what is allowed.

- **PostgreSQL text.** `string` and `enum` are bound without a cast, so the server reads them as the column's own type, as it would a literal. An enum, `uuid` or `citext` column compares as itself. Where PostgreSQL cannot tell the type (`SELECT {q}`, `{q} IS NULL`), the Test run shows the error; write `{q}::text` in the body.
- **Never NULL, never NUL.** No parameter is ever bound as NULL, and text containing a NUL character is refused.
- **ClickHouse limits.**
  - ClickHouse reads a parameter's text in its escaped format, so a backslash or a control character in a ClickHouse `string` or `enum` value is refused.
  - A `date` must fall between 1970-01-01 and 2149-06-06, and a `datetime` between 1970 and 2105.
  - Parameters need ClickHouse 22.8 or newer on the native protocol. An older server fails the Test run.

When you save, the body is checked the same way `db_query` checks SQL. Every placeholder must be declared, and every declared parameter must be used. The query must be read-only, and it must not call anything that reaches past the database, such as `url()`, `pg_read_file()` or `query_to_xml()`.

## Versions and approval

Every save adds a version, and versions are never changed. A new version is a draft, and `db_query` keeps running the approved version until the new one is approved.

```json
POST /api/saved-queries {"action": "test", "datasource": "reports-db", "name": "active_orgs", "version": 2,
                         "values": {"start_date": "2026-10-01", "end_date": "2026-11-01"}}
POST /api/saved-queries {"action": "approve", "datasource": "reports-db", "name": "active_orgs", "version": 2, "hash": "<hash>"}
POST /api/saved-queries {"action": "mine", "datasource": "reports-db"}
```

- **`test`.** Runs any version, draft or approved, through the same path as `db_query` and returns at most 20 rows. The query is stopped there, not left to run to the end. No hook or trace sees a test run; the gateway log records it.
- **`approve`.** Takes the hash that the save or the listing (`GET /api/saved-queries?datasource=…`) showed. If the version differs from what you saw, the approval is refused with `409`.
  - The version is checked again against the rules of the running release.
  - The hash covers the version's content and the database the datasource reaches: its driver, host, port and database, without credentials.
  - When `db_query` runs a query, it works out the hash again. A query whose content changed in the database, or whose datasource now reaches another database, does not run. That includes a datasource that was deleted and added again under the same name, and a snapshot imported where the name means another database.
  - The listing marks such a query `approval_holds: false`. To use it on the new database, save it again and approve that version.
- **`revoke`.** Withdraws the approval.
- **`deprecate`.** Stops offering and running a query but keeps its approval. Approving a version again undoes it.
- **`delete`.** Withdraws the query and hides it from the listing. Its versions are kept. If a query of the same name is made again, its versions go on from the next number, so a version that an old trace names is never other SQL.
- **`mine`.** Scans `agent_traces` for repeated successful `db_query` calls on the datasource, parameterizes their filter literals and saves any new shapes as drafts.

No tool can approve a saved query. Only this API can, behind the gateway token. That token is shared by the dashboard and any other client of the API, so the approval records `approved_via: "api"` and not a person.

## What a run looks like

`db_query` resolves the saved query before the admin's hooks run. A hook on `db_query` therefore sees the query's SQL in `query`, and `args_match` works on saved queries as it does on SQL the agent writes. The cache key includes the template, its version and the values, and the trace records the template and the values.

The result starts with `Saved query <name> v<N> #<hash>:` on its first line. That line stays when the result is spilled or has no rows. If a saved query fails, the agent is told that it failed and not to write its own SQL in its place, because the failure is an admin's to fix.

A delegated run (a SOP acting for a person) runs a saved query exactly as it runs other SQL. That means PostgreSQL only, under the datasource's `delegated_role`, reading only the granted views. See [the SOP virtual level](../architecture/sop-virtual-level.md).

A saved query is held to the same limits on the database as other `db_query` SQL: a time limit the server enforces, and read-only where the engine can enforce it. See [Database Connections](tools_and_mcp.md#database-connections). A ClickHouse saved query cannot have a `SETTINGS` clause; one saved before this rule stops running until it is saved again without it.

## Binding to a SOP step

A [SOP step](automation/sop.md#step-configuration-reference) can bind one or more approved saved queries directly in its `queries` array (`[{template, datasource, params, as, max_rows}]`):

- When `queries_shadow` is false (the default), the step executes those saved queries directly through the worker pool and `db_query` with **zero LLM calls** and **zero tokens**.
- Each query publishes two named outputs for downstream steps: `<as>` (the Markdown table, defaulting to the `template` name when `as` is omitted) and `<as>_ref` (the `data_ref` ID in the output store, ready for `spreadsheet(action: "create", data_ref: ...)`).
- Parameter values in `params` may be literals, `{{run.date±Nd}}`, `{{run.time±Nh|m}}` (evaluated against the run's `StartedAt` in the SOP's `timezone`, or the server's local timezone when unset), or `{{steps.<id>.outputs.<name>}}`.
- Setting `queries_shadow: true` runs the bound queries in shadow mode alongside the normal LLM step and records the outcome (`version`, `hash`, `rows`, `bytes`, `ms`, `error`) in the step result's `query_runs` without altering the step's output.
- **`sop_only`.** A saved query marked `sop_only: true` (via the **SOP only** checkbox in the editor or `{"action": "sop_only", "sop_only": true}` on `/api/saved-queries`) is hidden from chat tool descriptions, does not pin `db_query` in chat, and runs only inside a SOP step.
- **Used-by tracking and audit log.** `GET /api/saved-queries` and lifecycle responses (`save`, `approve`, `revoke`, `deprecate`, `sop_only`, `delete`) include `used_by`, the list of SOP IDs referencing that query. Approving, revoking, deprecating or deleting a saved query appends a `saved_query_<action>` entry to each referencing SOP's `change_log`.
- **Auto-pausing a schedule on repeated pre-flight refusals.** If a SOP's pre-flight saved-query check fails 3 consecutive times with the same reason and the SOP is linked to a cron schedule, the schedule is automatically paused, a `cron_auto_paused` entry is recorded in the SOP's `change_log`, and a single alert is sent.

## Tool and MCP presets

A preset does for a read-only tool call what a saved query does for SQL: an admin approves the call once, with its arguments as a template, and the agent runs it by name with parameter values. Presets are managed through `POST /api/tool-presets` and the **Presets** dialog on the **Tools**, **MCP** and **Integrations** pages; `db_query` has saved queries instead.

A preset is:

| Field | Meaning |
|---|---|
| `kind` | `tool` for a registered tool, `mcp` for a tool on an MCP server. |
| `server` | For `mcp`: the MCP server's name in `config.yaml`. |
| `target` | The tool the preset calls: the registered tool's name, or the MCP tool's name on that server. |
| `name` | What the agent calls it by. Unique per tool (`tool`) or per server (`mcp`). |
| `description` | One line on what the call answers. |
| `args` | The call's arguments as a JSON object. A string value may be a placeholder, `{name}`. |
| `params` | What the placeholders take: name, type (`date`, `datetime`, `int`, `number`, `string`, `enum`, `bool`), required, default, min/max or values, as for a saved query. |

Rules:

- **Whole-value placeholders.** A placeholder stands for a whole argument value. A string that mixes a placeholder with text (`"project = {p}"`) is refused, so a query-language expression (JQL, UQL, PromQL, Lucene) is never built from a parameter value.
- **Read-only calls only.** A preset is saved and run only for a tool call the SOP effects journal counts as read-only (the same list), and for an MCP tool. For a tool with both reading and writing actions (`gitlab`, `git_workspace`) every value a parameter can take must keep the call read-only: a preset whose `enum` parameter has a value that would make it a write is refused when saved, and a call rendered from any preset is checked again when it runs.
- **Versions and approval, as for saved queries.** Every `save` is a new version; `test` runs one version with values; `approve` takes the version and its hash. The hash covers the kind, server, target, name, description, arguments, parameters and, for an MCP preset, the server's transport and address as configured, so a preset approved against one server does not run against another. `revoke`, `deprecate` and `delete` work as they do for saved queries.
- **Not in a delegated run.** A SOP acting for a person can neither list nor run presets.

Running one: a tool with an approved preset gets two more arguments, `preset` and `preset_params`, and its description lists the presets it has. The arguments the preset fixes are not given; `preset` with raw arguments (`arguments` for `mcp`, or another `action`) is refused.

```text
gitlab(preset: "open_blockers", preset_params: {"project": "platform/api"})
mcp(server: "jira", preset: "my_open_issues", preset_params: {"assignee": "alice"})
```

For `mcp`, `server` is needed only when the same preset name exists on more than one server.

Drafts come from two places, and an admin approves them as any version:

- **The agent.** In a conversation with a person (not a SOP, cron, webhook, subagent or delegated run), the agent can save a read-only call that just worked by adding `save_preset_draft: {"name": "...", "description": "...", "args": {...}, "params": [...]}` to it. `args` is the template with its placeholders; the draft is version 1, unapproved.
- **Mining.** `POST /api/tool-presets {"action": "mine"}` reads the last seven days of successful chat traces (`agent_traces`; the 500 newest, cron, webhook, SOP and subagent sessions left out), groups read-only tool calls and MCP `call_tool` calls by shape, with literal values turned into parameters, and saves a draft for every shape seen in at least two sessions by at least two people. `db_query` calls are left to saved-query mining. An existing or deleted preset of the mined name is never written over.

## Not yet

- There is no per-datasource cap on the rows or bytes a query may read (ClickHouse `max_rows_to_read`, `max_bytes_to_read`). A run stops reading at the row cap, and the database stops the query at its time limit.

## Storage

Saved queries are stored in `saved_queries` and `saved_query_versions`. Tool and MCP presets are stored in `tool_presets` and `tool_preset_versions`. All four tables are included in every `.gclaw` snapshot.