MCP Tool Reference

The complete reference for the Colrows MCP tools. Colrows serves two tool catalogs: six intent tools on the client connector (/api/mcp/mcp-clients) for apps such as ChatGPT and Claude, and 13 lower-level tools on the agent endpoint (/api/mcp/agents) for agents you build. Every agent tool also has a plain REST route under /api/agents/v1. For endpoints, OAuth, and scopes, see the MCP integration guide.

Client connector tools

Call these with tools/call at POST /api/mcp/mcp-clients, using an MCP access token (cr_mcp_at_…). The examples below show the arguments object for each tool.

No client tool accepts SQL text; arguments containing sql are rejected. Call listDatasources to get IDs for the query tools.

listDatasources

{}

Returns data as an array of { "datasourceId", "name" } for every datasource the caller may read.

askMetadata

{
  "question": "Where can I find revenue, and how do orders join customers?",
  "datasourceIds": ["238b1c06-a155-4fc3-b229-2cc657500891"]
}

Returns data.answer as text, or a clarification. datasourceIds and schema are optional; omit datasourceIds to use every accessible datasource.

askData

{
  "datasourceId": "238b1c06-a155-4fc3-b229-2cc657500891",
  "schema": "public",
  "question": "Show monthly net revenue for this year",
  "maxRows": 100
}

askData does not accept SQL text. Colrows generates the SQL, validates it, executes it under the caller's data-access rules, and returns the generated sql, an optional executionId, a confidenceScore and confidenceScoreExplanation, semantic sources, columns (each with columnName and javaScriptType), rows, rowCount, and a truncated flag. Each source carries its entity type plus available names and descriptions; source IDs are omitted. maxRows defaults to 1000 and cannot exceed 5000.

When the query returns no rows, the result also carries emptyResultAdvisory: a summary and likelyCauses, each with type (for example VALUE_FILTER, JOIN, or DATA_AVAILABILITY), evidence, and recommendedCheck. The summary is also added as a second text content block, so text-only clients see it.

Verified queries

Curators create and edit Verified Queries on the Verified Queries page or through the Verified Queries REST API. They follow the same versioning, lineage, and semantic-validity rules as other semantic entities. Only the latest valid version of a query can be found or run. Unlike askData, which sends each question for fresh SQL generation, these three tools find and run stored, curator-approved SQL.

A verified query can declare typed parameters, written in its SQL as :name placeholders. The caller supplies values for them; the saved SQL never changes. Parameter types are STRING, INTEGER, DECIMAL, BOOLEAN, DATE and TIMESTAMP.

searchVerifiedQueries

Scope metadata:read. question is required; datasourceId is optional; limit defaults to 5 and cannot exceed 20.

{ "question": "Revenue for the West region this year", "datasourceId": "sales-prod", "limit": 5 }

The result contains matches. Each match has queryId, version, label, question, datasourceId, an optional schema, and parameters (each with name, type and an optional description). Search doesn't return SQL text or run a query, and it searches only datasources the caller can access.

{
  "matches": [
    {
      "queryId": "query-id",
      "version": 2,
      "label": "Revenue by region",
      "question": "What is the revenue for a region from a given date?",
      "datasourceId": "sales-prod",
      "schema": "public",
      "parameters": [
        { "name": "region", "type": "STRING", "description": "Sales region" },
        { "name": "from_date", "type": "DATE", "description": "First date to include" }
      ]
    }
  ]
}

getVerifiedQuery

Scope metadata:read. Returns the current valid version of one query the caller can access, with the same fields as a search match. Use it to refresh the version and parameters, for example after a VERSION_CHANGED error. It doesn't return SQL text.

{ "queryId": "query-id" }

executeVerifiedQuery

Scope data:query. queryId and the current version are required. Put exactly the declared parameters in params, and omit params for a query without parameters. maxRows defaults to 1000 and cannot exceed 5000.

{
  "queryId": "query-id",
  "version": 2,
  "params": { "region": "West", "from_date": "2026-01-01" },
  "maxRows": 100
}

The server loads the stored SQL; the caller can't submit SQL text. The result contains queryId, version, datasourceId, the saved SQL template in sql (parameter values are not written into it), columns, rows, rowCount, and a truncated flag. The query's validity and the caller's data access are checked when it runs, and the normal execution path applies row and column policies, read-only checks and the query cost guard.

A missing, extra or wrongly typed parameter fails with INVALID_INPUT. If the stored version is newer than the one you sent, the call fails with VERSION_CHANGED: call getVerifiedQuery and try again with the current version.

Client result envelope

Success:

{
  "structuredContent": { "data": { /* tool-specific result */ } },
  "content": [{ "type": "text", "text": "{\"data\":{...}}" }],
  "isError": false
}

Tool error - the message stays generic and carries no internal detail, and the kind travels in _meta["colrows/errorCode"]:

{
  "content": [{ "type": "text", "text": "You do not have access to the data this request needs" }],
  "isError": true,
  "_meta": { "colrows/errorCode": "ACCESS_DENIED" }
}

A tool error is a normal JSON-RPC result, so check isError, not only the HTTP status. Authentication, scope, and malformed requests use HTTP and JSON-RPC errors instead; see MCP integration.

Clarification & continuation

askMetadata and askData may require clarification before Colrows can compile a proven query.

{
  "structuredContent": {
    "data": {
      "status": "clarification_required",
      "continuationToken": "cr_mcp_cont_...",
      "expiresAt": 1782200000000,
      "clarification": {
        "id": "best-product-metric",
        "question": "Define what metric “best product” should mean for this query."
      }
    }
  },
  "isError": false
}

Resume by calling the same tool with the token and your answer:

{
  "continuationToken": "cr_mcp_cont_...",
  "clarificationResponse": { "answer": "Best product means highest order volume." }
}

If clarificationResponse is not an object, Colrows normalizes it to { "answer": <value> }. The original datasource, schema, and question stay bound to the token and are replayed automatically; do not send clarificationHistory yourself. You may set a new maxRows when you resume askData.

• A token expires 15 minutes after it is issued (expiresAt, epoch milliseconds) and works only for the same user, organization, and tool.
• A question allows at most three clarification rounds.
• Resending the same answer with a token that already completed returns the original result. A different answer for a completed token fails.

Agent tools and REST routes

Each agent tool is available two ways: as an MCP tool at POST /api/mcp/agents, and as a REST route under /api/agents/v1. Both run the same services and access checks, with the same scopes. The token is an OAuth access token from an Acts for users app (cr_mcp_at_…) or a client credentials token from a Server-to-server client (cr_sem_at_…), both registered by an administrator. See agent scopes.

POST https://cloud.colrows.com/api/agents/v1/queries/validate
Authorization: Bearer <access-token>
Content-Type: application/json

Agent result envelope

REST wraps every result in data and meta. meta always has requestId and apiVersion: "1"; some routes add the applied limit or warnings.

{
  "data": { /* operation result */ },
  "meta": { "requestId": "req-123", "apiVersion": "1" }
}

MCP returns the same result in structuredContent (not under data), adds requestId and contractVersion: "1" to it, and repeats it as text content. Times are epoch milliseconds.

Discovery and semantics

listDatasources

REST takes cursor and limit as query parameters; MCP takes them as arguments. limit defaults to 50, maximum 200. Returns items, nextCursor, and truncated. Each item has datasourceId, name, description, dialect, readiness (READY, BUILDING, DEGRADED, or UNAVAILABLE), readinessExplanation, and semanticRevision. Readiness reports semantic publication state; it does not test the connection.

searchSemanticEntities

{
  "query": "net revenue by region",
  "datasourceIds": ["sales-prod"],
  "schemas": ["public"],
  "entityTypes": ["METRIC", "TABLE", "COLUMN"],
  "limit": 10
}

Only query (up to 4,000 characters) is required. MCP calls the count maxResults, REST calls it limit: default 25, range 1 to 50. Filter arrays take up to 50 items. Returns entities, each with id, entityType, label, description, datasourceId, schema, and table. No SQL is returned or run.

getSemanticEntities

{
  "entities": [
    { "entityType": "METRIC", "ids": ["metric-revenue"] },
    { "entityType": "VERIFIED_QUERY", "ids": ["vq-123"] }
  ]
}

Reads the latest, non-deleted version of up to 100 entities in one call; each type may appear once. Supported types: BUSINESS_TERM, METRIC, TABLE, COLUMN, DATASET, VERIFIED_QUERY, TABLE_RELATIONSHIP, BUSINESS_ENTITY, EVENT, ACTION, RELATIONSHIP, CATEGORY, ASSOCIATION, and CATEGORY_SET. Returns entitiesByType in request order and unavailable. Each entity has id, entityType, integer version, and usable (false for an invalid record). An unavailable reference gives no reason, so it doesn't reveal whether the record is missing or restricted. Table views list only the columns the caller may read. Metric, dataset, and verified-query views include their authored SQL, which is why this tool needs semantic:sql.

getJoinGraph

{
  "datasourceId": "sales-prod",
  "seeds": [{ "type": "METRIC", "id": "metric-revenue" }],
  "targetTableIds": ["table-customers"],
  "maxHops": 3,
  "maxPaths": 5
}

Up to 50 seeds of type TABLE, COLUMN, TABLE_RELATIONSHIP, METRIC, BUSINESS_TERM, or DATASET, and up to 10 target tables. maxHops defaults to 3 (1 to 6); maxPaths defaults to 5 (1 to 20). Returns nodes, edges (with join predicates, cardinality, optionality, and origin), paths, status (COMPLETE, PARTIAL, or NO_JOIN_PATH), ambiguous, truncated, and warnings. Only tables, relationships, and join columns the caller can access are used. When several paths connect a pair, ambiguous is true and Colrows does not pick one.

resolveValues

{
  "datasourceId": "sales-prod",
  "values": [{ "key": "r1", "text": "west", "expectedType": "STRING" }],
  "limit": 5
}

1 to 20 phrases with unique keys; each may name up to 50 columnIds. limit is per phrase, default 5, maximum 20. expectedType is one of STRING, INTEGER, DECIMAL, BOOLEAN, DATE, TIME, TIMESTAMP. Returns items, one per key, with status (RESOLVED, NO_MATCH, or PARTIAL), ambiguous, truncated, and candidates. Each candidate gives the column, the typed value, the storedValue, matchType (EXACT, SYNONYM, VECTOR, or FUZZY), and a search score. It searches existing indexes and never runs SQL against your data. Use the returned values as query parameters, not as SQL text.

searchSqlResolutions and findSqlResolutionMatch

{ "datasourceId": "sales-prod", "question": "Revenue by region last quarter", "schema": "public" }

Questions take up to 4,000 characters. Search also takes a count (REST limit, MCP maxResults; default 5, maximum 20) and returns ranked candidates, each with resolutionId, resolvedQuestion, sql, rank, and versioned sourceRefs, plus searchStatus (COMPLETE or PARTIAL). A rank does not prove that the questions mean the same. Match confirms one cached resolution that answers the same question and returns cacheHit and match (null on a miss); it may evict resolutions whose sources are stale. Neither runs SQL.

Queries

Query input

validateSQL and executeSQL take a tagged query:

{ "kind": "SQL", "datasourceId": "sales-prod", "schema": "public",
  "sql": "SELECT region, SUM(amount) AS revenue FROM sales WHERE sale_date >= :from_date GROUP BY region",
  "params": { "from_date": { "type": "DATE", "value": "2026-01-01" } } }

{ "kind": "VERIFIED_QUERY", "queryId": "vq-123", "version": 3,
  "params": { "region": "West", "from_date": "2026-01-01" } }

SQL is one read-only statement of up to 100,000 characters. Parameters are :name placeholders: at most 20, with a JSON size of at most 40,000 characters. Raw SQL parameters carry an explicit type (STRING, INTEGER, DECIMAL, BOOLEAN, DATE, TIMESTAMP); a verified query takes its types from its saved contract. Values are bound through the database driver and are never written into the SQL. Missing, extra, and untyped parameters are rejected. A verified query must be the current version. Either form may add expectedSemanticRevision, which must match the datasource's published revision.

compileSQL

{ "datasourceId": "sales-prod", "schema": "public", "prompt": "Total sales by region for last quarter" }

prompt takes up to 10,000 characters; datasourceId and schema up to 256. Optional expectedSemanticRevision. The generated SQL is validated under the caller's access before it is returned. Returns status, datasourceId, schema, sql, resolvedQuestion, clarification, and warnings. COMPILED carries SQL; NEEDS_CLARIFICATION carries a clarification and no SQL, so send a new prompt that answers it. Compiling never runs the query, and the SQL it returns is not an execution grant.

validateSQL

{ "query": { /* tagged query input */ }, "expectedSemanticRevision": 42 }

Checks statement safety, syntax, parameters, datasource and column access, and estimated cost without running the answer query. Returns valid, errors and warnings (each with code, message, field), checks (each PASSED, FAILED, UNKNOWN, or UNSUPPORTED), physicalAssets, semanticRefs, outputColumns (currently always empty), policyVerdict (ALLOWED or DENIED), and cost (estimated row units, a tier of LOW, MODERATE, HIGH, BLOCKED, or UNKNOWN, and a verdict). valid: false is a successful response, not an error. Validation has a 10-second deadline. A passing report does not grant execution.

executeSQL

POST /api/agents/v1/queries
Idempotency-Key: customer-request-123

{
  "query": { "kind": "VERIFIED_QUERY", "queryId": "vq-123", "version": 3,
             "params": { "region": "West", "from_date": "2026-01-01" } },
  "limits": { "maxRows": 1000, "timeoutMs": 30000 },
  "waitMs": 2000
}

Submits one asynchronous execution. An idempotency key of up to 256 characters is required: REST takes the Idempotency-Key header or idempotencyKey in the body (they must match if both are sent), MCP takes idempotencyKey. Repeating the same request with the same key returns the original execution; a different request with the same key fails with IDEMPOTENCY_CONFLICT. maxRows defaults to 1,000 (maximum 10,000); timeoutMs defaults to 30,000 (1 to 60,000), and the datasource's own query timeout applies if it is lower. waitMs (0 to 5,000, default 0) waits briefly for the result.

The response is an execution object: executionId, status, createdAt, updatedAt, limits, provenance (datasourceId, schema, queryKind, queryHash, verifiedQueryId, verifiedQueryVersion, semanticRevision), returnedRows, truncated, and a safe error on failure. It never contains rows. REST returns 202 while the execution is pending and 200 once it is finished. Status is one of QUEUED, RUNNING, SUCCEEDED, FAILED, CANCEL_REQUESTED, CANCELLED, or EXPIRED.

Colrows checks the query before it accepts it and again just before it runs: read-only SQL, datasource and column access, the saved version, and any expected semantic revision. The query then runs as the token's principal with current row filters, column restrictions, and masking, after the query cost guardrail. A server node runs at most four agent executions at once; when it is full, the call returns 503 UNAVAILABLE.

getSQLExecutionStatus and getSQLExecutionResult

GET /api/agents/v1/queries/{executionId}
GET /api/agents/v1/queries/{executionId}/results?pageSize=100&cursor=...

Only the principal that submitted an execution can read it; anyone else gets NOT_FOUND. Status adds outcome (the original terminal status), expiresAt, and resultAvailable. Results take pageSize (default 100, maximum 1,000) and cursor; keep the same page size when you follow nextCursor. A result page has columns (name, type, and nativeType, nullable, masked when known), rows as arrays in column order, returnedRows, totalRows, totalRowsExact, availableRows, nextCursor, truncated, expiresAt, limits, and provenance. DECIMAL values and very large integers are strings, to keep their precision.

Results are kept for a limited time from submission (240 minutes by default) and reads do not extend it. After that, status reports EXPIRED while outcome stays SUCCEEDED. Reads never run SQL again. If the caller's access has changed since the execution, a result read fails with ACCESS_DENIED.

cancelSQLExecution

POST /api/agents/v1/queries/{executionId}/cancel
Idempotency-Key: cancel-123

Requests cancellation of an execution you own. It needs an idempotency key, the same way as executeSQL; MCP takes executionId and idempotencyKey as arguments. The response is the execution object: CANCEL_REQUESTED until the worker stops (REST 202), then CANCELLED (REST 200). Repeating the request returns the current state. A finished execution keeps its outcome. Statement cancellation depends on the database driver, so it is not an immediate stop.

Error codes

Client connector

Agent tools

REST errors use application/problem+json:

{
  "type": "about:blank",
  "title": "IDEMPOTENCY_CONFLICT",
  "status": 409,
  "detail": "...",
  "code": "IDEMPOTENCY_CONFLICT",
  "requestId": "req-123",
  "retryable": false
}

MCP returns the same code as a tool result with isError: true and _meta["colrows/errorCode"]. Missing tokens (401) and missing scopes (403 with the scope in WWW-Authenticate) are refused before the route runs and do not use this body.

executeSQL can also fail with a validation code such as SYNTAX_ERROR, UNSAFE_SQL, UNKNOWN_REFERENCE, or INVALID_PARAMETER (400), with the database or parser message in detail so an agent can repair the SQL, or with ACL_EVALUATION_ERROR (403) when the caller may not read a table or column the query uses. Retry reads with bounded backoff, and retry executeSQL or cancelSQLExecution with the same idempotency key after an uncertain response.