Verified Queries

Verified Queries are curator-approved, reusable SQL examples - each paired with the business question it answers and the datasource it runs on. When a question matches one, Colrows AI and agents run the stored, governed query by ID and version instead of generating SQL from scratch. It's how you turn "the query we trust for revenue by region" into a first-class, reusable asset.

Why verified queries

Generated SQL is right most of the time. For the questions your business asks constantly - board metrics, regulatory reports, the numbers a room will argue over - "most of the time" isn't enough. A verified query pins the exact, reviewed SQL for a known question, so the answer is identical every time and traceable to a human who approved it.

How curators manage them

Verified queries live in Catalog. Open Consensus → Catalog and pick Verified Query in the type list. The list can be searched by label, description, SQL, or alternate question, and filtered by datasource. The older Verified Queries URL now redirects to this Catalog view.

• Add a verified query - choose Create, then give a label, the business question (as a description), the read-only SQL, the datasource it targets, a schema when the SQL uses unqualified table names, optional alternate questions that help search find it, and any typed parameters the SQL uses as :name placeholders. The server checks the SQL and the parameter definitions before it saves.
• Per-row actions - view, edit, delete, and inspect the full version and lineage history of any query.
• Parameter columns - the detail pane shows the Catalog column each parameter is compared with. Colrows resolves these from the SQL when you save; if it cannot resolve a parameter to one column, the pane says so.

How agents use them at runtime

Verified queries are exposed to AI agents and MCP clients as three tools on the client MCP endpoint (see MCP for AI Agents):

• searchVerifiedQueries - find valid queries for a question. Returns matches (query ID, version, label, question, datasource, schema and parameter definitions) - never SQL text, and it never runs anything. It searches only datasources the caller may access, and returns 5 matches by default (up to 20).
• getVerifiedQuery - get the current version and parameter definitions of one query by ID, for example after a VERSION_CHANGED error.
• executeVerifiedQuery - run a query by queryId and version, with values for its parameters in params. The server loads the stored SQL; the caller cannot submit SQL. The result carries columns, rows, row count, and a truncated flag. maxRows defaults to 1,000 and can be up to 5,000.

The search and get tools need the metadata:read scope; execute needs data:query. These tools are MCP-only: the earlier REST mirror of the client MCP tools has been removed.

Agents built on the Colrows agent interface (agent REST and agent MCP) use verified queries through the general operations instead: getSemanticEntities reads a verified query's definition, and validateSQL and executeSQL accept an exact-version verified query with typed parameter values in place of raw SQL.

The Excel add-in uses the client MCP tools to search and load results into a workbook.

Governance, versioning & validity

• Verified queries follow the same rules as every other semantic entity: immutable versions, lineage, and a validity lifecycle (Active, Invalid, Deleted). Only active, valid queries are searchable and executable.
• Parameters let one query serve changing values (a region, a start date). Curators declare each parameter with a name, a type (STRING, INTEGER, DECIMAL, BOOLEAN, DATE or TIMESTAMP) and a description, and write it in the SQL as :name. A query can declare up to 20 parameters. Parameters stand for scalar values only - not table names, column names, SQL fragments, or IN lists - and every declared parameter is required. Callers supply the values; the saved SQL never changes. A missing, extra or wrongly typed value is rejected.
• Editing a query creates a new version. If a caller asks for a stale version, it gets the VERSION_CHANGED signal and fetches the current one with getVerifiedQuery.
• Execution checks semantic validity and the caller's data access at run time, binds parameter values through the database driver, and applies row and column policies, read-only SQL checks, and the query cost guard - the same governance as the rest of Colrows.
• When a table or column a verified query depends on changes, the change-record pipeline re-checks the query; one whose required inputs broke becomes Invalid and appears in Needs Attention until it is repaired.

Manage via API

Verified queries are managed from the console, and the same operations are available on the platform REST API. Any signed-in user can read, and creating, updating and deleting need the admin or curator role. An OAuth client can call the single-query routes with the semantic:read scope for reads and semantic:read-write for changes (clients that already hold the legacy semantic:write scope keep working, but new token requests must use semantic:read-write). The REST API Reference documents each request and response in full.

The separate GET /api/consensus/verified-queries list route has been removed. Listing now uses the shared Catalog filter endpoint, which takes page, size (1 to 100, default 25), an optional datasourceId, and an optional searchString. It is a console route and is not open to OAuth client tokens.