SQL API for Your Users

Your application can ask Colrows to write read-only SQL from a question, and to run read-only SQL, 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 redaction rules apply, exactly as in the Colrows SQL editor.

When to use it

A client credentials token can't call this API, because it identifies no user whose rules could apply.

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/ai/generate-sql to turn the user's question into SQL, and POST /api/data-query/execute-sql to run it. Both calls use 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. Choose the permission Generate and run read-only SQL (sql:execute), and an expiry date within one year.
5. Copy the client ID and client secret. Colrows shows the secret only once; keep it on your server.

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": ["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=sql: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": "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. Generate SQL from a question

POST /api/ai/generate-sql
Authorization: Bearer ACCESS_TOKEN
Content-Type: application/json

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

When Colrows can answer, the response carries the SQL and, when it rephrased the question, the question it answered:

{
  "meta": { "ctime": "2026-09-30T10:15:30.123" },
  "result": { "status": "SUCCESS", ... },
  "payload": {
    "sql": "SELECT region, SUM(amount) AS total_sales FROM orders WHERE ... GROUP BY region",
    "resolvedQuestion": "Total sales by region for the last quarter"
  }
}

The user's access is checked while the SQL is written. On a datasource with restricted access, the call returns 403 straight away if the user can see none of its tables. The SQL is then checked against the user's table and column access as it is generated. If that check fails, the call returns 403 with the code INSUFFICIENT_PRIVILEGE, and no SQL. The message is the same for every access failure, so it doesn't reveal which table or column was denied. Colrows also checks that the result is a single read-only query of at most 100,000 characters. If it can't confirm that the query is safe to run, the call returns 500 with the code COULD_NOT_VALIDATE, and no SQL.

Clarifications

When the question is ambiguous, Colrows asks for more detail instead of guessing. The payload then holds a clarification (primitive, blocking, instruction, and options, each option with an id and a meaning), or a clarification object. Show it to the user and call generate-sql again with a prompt that includes the missing detail. The API keeps no conversation between calls.

SQL generation uses your organization's AI token allowance. When it is used up, the call returns 403 with TOKEN_QUOTA_EXHAUSTED. Generation can take up to two minutes.

4. Run SQL

POST /api/data-query/execute-sql
Authorization: Bearer ACCESS_TOKEN
Content-Type: application/json

{
  "datasourceId": "DATASOURCE_ID",
  "schema": "sales",
  "sql": "SELECT region, SUM(amount) AS total_sales FROM orders GROUP BY region",
  "maxRows": 500
}

The response has the same shape as a query result in the SQL editor:

{
  "meta": { ... },
  "result": { "status": "SUCCESS", ... },
  "payload": {
    "typeInfo": [
      { "columnName": "region", "javaScriptType": "STRING" },
      { "columnName": "total_sales", "javaScriptType": "DECIMAL" }
    ],
    "data": [
      { "region": "EMEA", "total_sales": 1284000.5 },
      { "region": "APAC", "total_sales": 962310.0 }
    ],
    "rowCount": 2,
    "overflow": false
  }
}

data holds one object per row. overflow is true when the query had more rows than were returned.

The query runs as the user: row filters, column restrictions, and redaction apply to what comes back. Before it runs, the query cost guardrail estimates how much data it touches, and this API always refuses a query in the blocked tier with 400. By default that is a query touching about 50 million rows, or one returning more than a million rows without a LIMIT. A query Colrows can't estimate still runs, within the datasource's query timeout.

How the query is checked

Before a query runs, Colrows lists every column it reads and checks each one against the user's column access. If any column is denied, the call returns 403 with the code INSUFFICIENT_PRIVILEGE and nothing runs. As with generate-sql, the message doesn't say which column was denied.

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.

Names are matched to tables the way the database matches them. A correlated subquery can read a column of the query around it, and that column is checked on the outer table. A column that a CTE only filters on, without selecting it, is not one of the CTE's outputs. And once a table has an alias, its original name no longer refers to it. Where a name could mean either of two columns, Colrows checks both. So a query can be refused for a column it doesn't actually read, but never runs with a column that wasn't checked.

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), LATERAL, UNNEST, and VALUES in the FROM clause, and names that match no column. Functions that take a bare keyword argument, such as Snowflake's DATEADD(day, 1, d), are refused for the same reason.

Row filters are added to every SELECT in the query that reads a filtered table, including CTEs, subqueries, derived tables, and set-operation branches.

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 redaction 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. With sql:execute, the token can call these two routes and nothing else in the Colrows REST API. It can use the MCP tools only if the user also approved the MCP scopes.
• A registered, authenticated app. Only an app your administrator registered can get sql:execute. It must send its secret with every code exchange and refresh, and users are sent back only to its registered callback URLs.
• 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

Errors from the REST routes carry a JSON body with code and message; errors from /api/oauth/token use the OAuth error and error_description fields.