Agent Query API for Your Users

Your application can ask Colrows to write SQL from a question, check SQL without running it, and run governed read-only queries, as one of its users. Each user signs in to Colrows once and approves your app. From then on, every query runs as that user, so their data access, row and column rules, and masking rules apply, exactly as in the Colrows SQL editor. Your app calls the agent query routes under /api/agents/v1/queries.

When to use it

How it works

1. An organization administrator registers your app in Colrows. You get a client ID and a client secret.
2. Your app sends a user to the Colrows consent page. The user signs in to Colrows and approves your app.
3. Colrows sends the user back to your app with a one-time code. Your backend exchanges the code for an access token and a refresh token.
4. Your backend calls POST /api/agents/v1/queries/compile to turn the user's question into SQL, optionally POST /api/agents/v1/queries/validate to check it, and POST /api/agents/v1/queries to run it. It then reads the result with GET /api/agents/v1/queries/{executionId}/results. Every call uses that user's access token.
5. Your backend refreshes the access token before it expires, and stores the refresh token against its own user ID.

Your app never needs Colrows user IDs, and Colrows needs no mapping from yours. The user who signed in on the consent page is the user every query runs as.

1. Register your app

An organization administrator registers the app once, in Administration → Settings → OAuth clients:

1. Select Add OAuth client and choose Acts for users.
2. Enter the name users will see when they approve it.
3. Add your app's callback URLs, one per line. Each must be an HTTPS URL. Colrows sends users back only to these addresses.
4. Select the permissions the app needs, and an expiry date within one year:
   • data:query - compile and run read-only queries, read results, and cancel executions. Running a verified query needs only this.
   • data:sql - submit raw SQL. Also requires data:query.
   • sql:execute - validate SQL without running the answer query.
   • semantic:read and semantic:sql - the semantic tools of the agent API.
5. Copy the client ID and client secret. Colrows shows the secret only once; keep it on your server.

To compile a question and run the SQL Colrows wrote, an app needs data:query and data:sql. Add sql:execute if you also want to validate SQL before you run it. Administrators can do the same through the API with an admin session:

curl -X POST https://cloud.colrows.com/api/sys/org/oauth-apps \
  -H "Authorization: Bearer $ADMIN_SESSION_TOKEN" -H "Content-Type: application/json" \
  -d '{"name": "Acme Analytics",
       "redirectUris": ["https://acme.example/colrows/callback"],
       "scopes": ["data:query", "data:sql", "sql:execute"],
       "expiresAt": 1798761600000}'

GET /api/sys/org/oauth-apps lists your organization's apps, and DELETE /api/sys/org/oauth-apps/{clientId} revokes one. Revoking an app ends every approval users gave it, so its tokens stop working at once. An app also stops working on its expiry date.

2. Connect a user (authorization code with PKCE)

Send the user to Colrows

For each connection, create a random PKCE code_verifier of 43 to 128 characters, and its S256 code_challenge (the base64url-encoded SHA-256 hash of the verifier). Also create a random state value and keep both on your server. Then send the user's browser to:

https://cloud.colrows.com/oauth/authorize
  ?response_type=code
  &client_id=CLIENT_ID
  &redirect_uri=https://acme.example/colrows/callback
  &scope=data:query%20data:sql%20sql:execute
  &code_challenge=CHALLENGE
  &code_challenge_method=S256
  &state=STATE

Your app may ask for any of the scopes the administrator allowed; without scope, it gets all of them. The user signs in if needed, sees what your app will be able to do, and chooses Allow access or Deny. Colrows then sends the browser to your callback with code and state, or with error=access_denied. Check that state matches the value you created.

Exchange the code

Within 5 minutes, exchange the code on your server. Authenticate with the client ID and secret, using HTTP Basic or client_id and client_secret form fields:

curl -X POST https://cloud.colrows.com/api/oauth/token \
  -u "$CLIENT_ID:$CLIENT_SECRET" \
  -d grant_type=authorization_code \
  -d code=CODE \
  -d redirect_uri=https://acme.example/colrows/callback \
  -d code_verifier=VERIFIER

{
  "access_token": "cr_mcp_at_...",
  "refresh_token": "cr_mcp_rt_...",
  "token_type": "Bearer",
  "expires_in": 900,
  "scope": "data:query data:sql sql:execute"
}

A code works once. A wrong or missing secret returns 401 with invalid_client; an expired, reused, or mismatched code returns 400 with invalid_grant.

Refresh and revoke

Access tokens last 15 minutes. Before one expires, get a new one with the refresh token and the same client authentication:

curl -X POST https://cloud.colrows.com/api/oauth/token \
  -u "$CLIENT_ID:$CLIENT_SECRET" \
  -d grant_type=refresh_token \
  -d refresh_token=REFRESH_TOKEN

Refresh tokens last 30 days and rotate: each refresh returns a new refresh token and invalidates the old one. Store only the newest one, encrypted, against your user's ID. If an old refresh token is used again, Colrows treats it as stolen and ends the whole approval; the user must connect again.

To disconnect a user from your side, call POST /api/oauth/revoke with token= the access or refresh token. Users can also disconnect your app themselves in Personal settings → Connected apps; see See and disconnect connected apps.

3. Compile SQL from a question

Needs data:query. The old sql:generate grant does not authorize this route.

POST /api/agents/v1/queries/compile
Authorization: Bearer ACCESS_TOKEN
Content-Type: application/json

{
  "datasourceId": "DATASOURCE_ID",
  "schema": "sales",
  "prompt": "Show total sales by region for last quarter"
}

Any other field is rejected with 400.

{
  "data": {
    "status": "COMPILED",
    "datasourceId": "DATASOURCE_ID",
    "schema": "sales",
    "sql": "SELECT region, SUM(amount) AS total_sales FROM orders WHERE ... GROUP BY region",
    "resolvedQuestion": "Total sales by region for the last quarter",
    "clarification": null,
    "warnings": []
  },
  "meta": { "requestId": "req-123", "apiVersion": "1" }
}

The user's access to the datasource and schema is checked before anything is written, and the generated SQL is validated under the user's access before it is returned. When the question is ambiguous, status is NEEDS_CLARIFICATION, sql is null, and clarification says what is missing. Show it to the user and compile again with a prompt that includes the answer. The API keeps no conversation between calls.

4. Validate SQL (optional)

Needs sql:execute. Checks a query without running the answer query: statement safety, syntax, parameters, datasource and column access, and estimated cost.

POST /api/agents/v1/queries/validate

{
  "query": {
    "kind": "SQL",
    "datasourceId": "DATASOURCE_ID",
    "schema": "sales",
    "sql": "SELECT region, SUM(amount) AS total_sales FROM orders WHERE order_date >= :from GROUP BY region",
    "params": { "from": { "type": "DATE", "value": "2026-07-01" } }
  }
}

The response has valid, errors and warnings (each with code, message, and field), checks, the physicalAssets the query reads, policyVerdict, and a cost estimate with its tier. valid: false is a normal 200 response. Validation has a 10-second deadline. The query input and every field are described in the MCP Tool Reference.

5. Run a query

Needs data:query. Raw SQL also needs data:sql; a verified query does not. The sql:execute grant does not authorize this route.

POST /api/agents/v1/queries
Authorization: Bearer ACCESS_TOKEN
Idempotency-Key: 6f1c2a7e-run-1
Content-Type: application/json

{
  "query": {
    "kind": "SQL",
    "datasourceId": "DATASOURCE_ID",
    "schema": "sales",
    "sql": "SELECT region, SUM(amount) AS total_sales FROM orders GROUP BY region"
  },
  "limits": { "maxRows": 500, "timeoutMs": 30000 },
  "waitMs": 2000
}

The response is an execution, not rows. REST returns 202 while it is still running and 200 once it has finished:

{
  "data": {
    "executionId": "3f0c...",
    "status": "SUCCEEDED",
    "createdAt": 1791800000000,
    "updatedAt": 1791800001200,
    "limits": { "maxRows": 500, "timeoutMs": 30000 },
    "provenance": { "datasourceId": "DATASOURCE_ID", "schema": "sales", "queryKind": "SQL", "queryHash": "...",
                    "verifiedQueryId": null, "verifiedQueryVersion": null, "semanticRevision": 12 },
    "returnedRows": 2,
    "truncated": false,
    "error": null
  },
  "meta": { "requestId": "req-123", "apiVersion": "1" }
}

Retry with the same key after a timeout or network error: you get the original execution back, and the query does not run twice. Reusing a key for a different request returns 409 IDEMPOTENCY_CONFLICT. A failed execution keeps a safe error; for SQL mistakes it carries the parser or database message so you can fix the query.

Read the result

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

Poll the first route until status is SUCCEEDED, FAILED, or CANCELLED, then read pages from the second. Pages hold columns and rows as arrays in column order, with nextCursor for the next page; pageSize defaults to 100, maximum 1,000. Only the user who ran the query can read it. Results are kept for 240 minutes by default; after that, status reports EXPIRED. Reads never run the query again. To stop a running query, call POST /api/agents/v1/queries/{executionId}/cancel with an idempotency key. Full fields are in the MCP Tool Reference.

How the query is checked

Colrows checks a query when you submit it and again just before it runs: it must be one read-only statement, the user must still have access to the datasource, and a verified query must still be the version you named. The query then runs as the user. Before it runs, Colrows lists every column it reads and checks each one against the user's column access. If any column is denied, nothing runs and the call fails with 403 and the code ACL_EVALUATION_ERROR.

A column counts as read wherever the query uses it, not only in the select list:

• Every clause: WHERE, JOIN ... ON and USING, GROUP BY, HAVING, ORDER BY, QUALIFY, and window definitions (PARTITION BY, ORDER BY in OVER).
• Inside any expression: function arguments, arithmetic, every branch of a CASE, casts, and aggregates.
• Every part of the query: common table expressions (WITH), subqueries in any clause, derived tables, and each branch of UNION, INTERSECT, and EXCEPT.
• Stars: SELECT * reads every column of every table in the FROM clause. A star inside a function, such as to_jsonb(t.*), OBJECT_CONSTRUCT(*), or COUNT(DISTINCT t.*), reads every column of the tables it covers. Only a plain COUNT(*) reads no column.

A query Colrows can't fully analyze is refused rather than run with a partial check. This includes NATURAL JOIN (join ON or USING named columns instead). Row filters are added to every SELECT in the query that reads a filtered table, and masking rules apply to what comes back.

The query cost guardrail estimates the query's cost before it runs. When your Colrows deployment enforces cost limits, a query in the blocked tier fails; otherwise the estimate is only reported. A query Colrows can't estimate still runs, within its time limit.

What Colrows enforces

• The user's own access. Every query runs as the user who approved your app, with their data access, row and column, and masking rules.
• Every column checked. Each column a query reads, in any clause or part of the query, is checked before it runs. See How the query is checked.
• Only what was approved. Each route needs its own scope, and the token reaches only the agent routes and the MCP endpoints, within its scopes. It cannot call the rest of the Colrows REST API.
• A registered, authenticated app. Only an app your administrator registered can get these scopes. It must send its secret with every code exchange and refresh, and users are sent back only to its registered callback URLs.
• Your executions only. Status, results, and cancellation work only for the user who submitted the execution.
• Current directory groups. Colrows reads a user's directory (AD) groups when they sign in. Your app's access uses the groups from the user's latest sign-in, for at most 7 days after they were read. After that, calls fail with 401 and refreshes fail with invalid_grant until the user signs in to Colrows again. Users without directory groups are not affected.
• Revocable at any time. The user can disconnect your app, and an administrator can revoke it. Either way, its tokens stop working at once.

Errors

Route errors use application/problem+json with code, detail, status, requestId, and retryable. Errors from /api/oauth/token use the OAuth error and error_description fields.

Migrate from the old SQL API

• New scopes. sql:generate no longer authorizes SQL generation, and sql:execute now means validate only. Ask your administrator to give the app data:query and data:sql, then have users approve it again so their tokens carry the new scopes.
• Request fields. Compile still takes datasourceId, schema, and prompt. Execution wraps SQL in query with "kind": "SQL", moves maxRows into limits, and needs an idempotency key.
• Responses. Results come in data and meta, not payload. Compile returns status instead of a clarification payload. Execution returns an executionId; read rows as arrays from the results route instead of objects in payload.data.
• Row limit. The maximum is still 10,000 rows.