MCP Integration: Connect AI Agents & MCP Clients to Colrows

Colrows exposes the semantic execution layer over the Model Context Protocol (MCP) - the open standard for connecting AI applications to external tools. Two governed endpoints serve two kinds of caller: the client connector for MCP client apps such as ChatGPT, Claude, Cursor, and Codex, and the agent endpoint for custom AI agents you build yourself. Both discover governed metadata and run read-only queries through the same compile-then-execute pipeline that backs the Colrows app and the HTTP API.

Two endpoints, one governed pipeline

Colrows serves MCP at two endpoints. They share the OAuth server and the governed execution path, but each has its own tool catalog, scopes, and result contract. Tools from one endpoint are not available at the other.

Why MCP, not a custom connector

Agent frameworks and AI apps keep multiplying, and every one of them wants its own connector to your data. MCP collapses that into a single contract: build one server, and ChatGPT, Claude, Cursor, Codex, and any custom agent can all reach it without a rewrite. Colrows implements that server so callers inherit compile-time governance automatically - a caller cannot query a table, column, or row it isn't authorized to see, because the unauthorized plan is never generated in the first place.

Endpoints & protocol

POST https://cloud.colrows.com/api/mcp/mcp-clients   # client connector
POST https://cloud.colrows.com/api/mcp/agents        # agent endpoint
Content-Type: application/json
Authorization: Bearer <access-token>

Messages are JSON-RPC 2.0 over HTTPS POST. There is no long-lived MCP session - each request is self-contained, which keeps both endpoints stateless and horizontally scalable. Clients that accept only Server-Sent Events receive the same JSON-RPC response framed as a single SSE message event.

• Supported methods: initialize, ping, tools/list, tools/call on both endpoints.
• GET and DELETE return 405 Method Not Allowed. Notifications return 202 Accepted with no body. Tool-list changes are not pushed (tools.listChanged: false).

Send the negotiated version in the MCP-Protocol-Version header after initialize.

Authentication

MCP uses OAuth 2.0; the full flow is below. For how every Colrows surface authenticates - REST, embedding, and MCP - see the Authentication reference.

Every caller authenticates with OAuth 2.0. An unauthenticated request returns 401 Unauthorized with a WWW-Authenticate header pointing to the endpoint's protected-resource metadata - discover endpoints from there rather than constructing OAuth URLs by hand.

/.well-known/oauth-protected-resource/api/mcp/mcp-clients
/.well-known/oauth-protected-resource/api/mcp/agents
/.well-known/oauth-authorization-server/api/oauth

The authorization server (issuer /api/oauth) advertises these endpoints:

• MCP client apps register themselves through dynamic client registration and are public clients with no secret (token_endpoint_auth_method none). Apps and clients your administrator registers send a secret with client_secret_basic or client_secret_post.
• Grants: authorization code with PKCE S256 mandatory, refresh token, and client credentials (for server-to-server OAuth clients).
• Access tokens (cr_mcp_at_…) expire after 15 minutes. Refresh tokens (cr_mcp_rt_…) expire after 30 days and rotate on use. Authorization codes expire after 5 minutes.
• HTTPS redirect URIs; HTTP is allowed only for loopback hosts (local dev clients). Cursor's native callback cursor://anysphere.cursor-mcp/oauth/callback is explicitly allowed.
• The user authenticates through the normal Colrows browser login and explicitly approves access.

Client connector scopes

If a self-registered app requests no scope, Colrows grants both after user approval - the same RBAC/ABAC identity that governs the browser UI governs every MCP tool call. A self-registered app can never be granted the agent scopes below.

Agent endpoint scopes

The agent endpoint accepts two kinds of token, both issued only to credentials an organization administrator creates in Administration → Settings → OAuth clients:

• Acts for users apps: authorization code with PKCE plus the app's secret. The token (cr_mcp_at_…) runs as the user who approved the app, with that user's access and masking rules. See Agent Query API for Your Users.
• Server-to-server clients: client credentials grant. The token (cr_oauth_cc_at_…) runs as an organization service principal. See Authentication.

These scopes do not replace one another: sql:execute validates but cannot run a query, and data:sql does not grant reads of SQL definitions. A data:query grant held by a self-registered client app does not reach the agent endpoint. A token without any agent scope gets 403 with the required scope in WWW-Authenticate.

Connect an MCP client app

For ChatGPT, Claude, and Cursor there is no code to write. Point the app at the connector URL and it runs the OAuth flow for you; you approve access on the Colrows consent screen.

• ChatGPT - add a custom MCP connector pointing at https://cloud.colrows.com/api/mcp/mcp-clients. ChatGPT opens the Colrows consent page, and after you allow access the tools appear in the conversation.
• Claude - add Colrows as a custom connector with the same URL, or from Claude Code:

claude mcp add --transport http colrows https://cloud.colrows.com/api/mcp/mcp-clients

• Cursor - add the connector to your MCP config:

{
  "mcpServers": {
    "colrows": {
      "url": "https://cloud.colrows.com/api/mcp/mcp-clients"
    }
  }
}

See and disconnect connected apps

Every app you approve on the consent screen keeps its access until it stops using it, you disconnect it, or an administrator revokes it. An app your organization registered also loses access when its registration expires, however often it uses its access. To see what can currently act for you, open Personal settings → Connected apps.

Each app you approved appears once, with:

• its name, and who registered it: Colrows application (such as the Excel add-in), Registered by your organization, or Connected by you for an app that registered itself, such as ChatGPT or Claude;
• what it can do as you, in the same words as the consent screen;
• when you connected it, and when its access ends: either when its sign-in lapses because the app stopped using it, or, for an app your organization registered, when the app's registration expires, whichever comes first. The page says which one applies.

Select Disconnect and confirm to end every approval you gave that app. Its access and refresh tokens stop working for you at once; other users of the same app are not affected. To use the app again, connect it and approve it again. Apps that an administrator revoked, or that expired, no longer appear, because their access has already ended.

Initialize & discover tools

{
  "jsonrpc": "2.0",
  "id": 1,
  "method": "initialize",
  "params": {
    "protocolVersion": "2025-06-18",
    "capabilities": {},
    "clientInfo": { "name": "my-agent", "version": "1.0.0" }
  }
}

Discover the tool catalog - names, input schemas, output schemas, and annotations - with tools/list. Every tool publishes destructiveHint: false. Client connector tools all publish readOnlyHint: true. On the agent endpoint, findSqlResolutionMatch (it can evict stale cache entries) and cancelSQLExecution publish readOnlyHint: false; no tool on either endpoint changes your data, and Colrows enforces read-only SQL server-side.

Tool catalog

Client connector tools

Six tools at /api/mcp/mcp-clients. Metadata tools resolve against the exact entities and definitions curators manage in Catalog, and query tools compile through the same governed pipeline as the rest of Colrows.

• listDatasources - list datasources accessible to the caller (metadata:read)
• askMetadata - answer a natural-language question about accessible metadata, relationships, and joins (metadata:read)
• askData - generate and run a read-only query from a question; SQL text is not accepted (data:query)
• searchVerifiedQueries - find valid, curator-approved queries for a question, with their parameter definitions (metadata:read)
• getVerifiedQuery - get the current version and parameter definitions of a verified query by ID (metadata:read)
• executeVerifiedQuery - run a verified query by ID and version, with values for its declared parameters (data:query)

No client connector tool accepts SQL text. Use askData for an open-ended question, or searchVerifiedQueries then executeVerifiedQuery when you want a governed, curator-approved query run by ID and version. Pass values for the query's parameters in params; the saved SQL never changes.

Agent endpoint tools

Thirteen tools at /api/mcp/agents. tools/list returns only the tools the token's scopes allow, and each call checks scope and resource access again. Structured results carry requestId and contractVersion: "1".

Calling a tool

{
  "jsonrpc": "2.0",
  "id": 3,
  "method": "tools/call",
  "params": {
    "name": "askData",
    "arguments": {
      "datasourceId": "238b1c06-a155-4fc3-b229-2cc657500891",
      "schema": "public",
      "question": "Show net revenue by region for the previous month",
      "maxRows": 100
    }
  }
}

A client connector tool call returns a governed result envelope (structuredContent.data plus a text copy), and askMetadata and askData may first return a typed clarification with a continuation token you resume with the user's answer. A continuation token expires after 15 minutes, works only for the same user and tool, and allows at most three clarification rounds. The full envelope, the _meta error codes, and the clarification flow are documented in the MCP Tool Reference.

Calling an agent tool

{
  "jsonrpc": "2.0",
  "id": 7,
  "method": "tools/call",
  "params": {
    "name": "executeSQL",
    "arguments": {
      "query": { "kind": "VERIFIED_QUERY", "queryId": "vq-123", "version": 3,
                 "params": { "region": "West", "from_date": "2026-01-01" } },
      "idempotencyKey": "customer-request-123",
      "limits": { "maxRows": 1000, "timeoutMs": 30000 },
      "waitMs": 2000
    }
  }
}

Agent tools return their result directly in structuredContent, with requestId and contractVersion, and the same JSON as text content. executeSQL returns an execution object, not rows: poll getSQLExecutionStatus, then page through rows with getSQLExecutionResult. Agent tools do not use continuation tokens; when compileSQL returns NEEDS_CLARIFICATION, call it again with a prompt that includes the answer.

Read-only & security guarantees

• MCP tools never create, edit, or delete user-facing Colrows resources - no dashboards, metrics, business terms, policies, users, or datasources can be mutated through either endpoint.
• Client connector tools never accept SQL text; a request containing a sql field is rejected. askData and executeVerifiedQuery only run SQL Colrows itself produced or a curator approved.
• On the agent endpoint, SQL text is accepted only by validateSQL (with sql:execute) and executeSQL (with data:query and data:sql). It must be one read-only statement of at most 100,000 characters, and parameter values are bound through the database driver, never written into the SQL.
• Every executed query passes through the same ACL, row-level security, and masking paths as the Colrows UI and API, and the query cost guardrail runs before it.
• Client connector telemetry never records OAuth tokens, authorization codes, PKCE verifiers, natural-language questions, generated SQL, or query result rows. Client tool calls are kept for audit with the question and SQL, but never the result rows.

Building agents on Colrows

On the client connector:

• Discover available datasources with listDatasources, then use a returned datasourceId - don't invent IDs, schema, table, or column names.
• Use askMetadata for questions about the semantic layer, relationships, or governance; use its answer or surface its clarification to the user.
• Use askData when the user wants query results from an open-ended question. When it returns no rows, read emptyResultAdvisory for likely causes before you tell the user there is no data.
• Use searchVerifiedQueries then executeVerifiedQuery when a governed, curator-approved query is the right answer. Supply each declared parameter in params, using the name and type from the search result. If you get VERSION_CHANGED, call getVerifiedQuery for the current version and its parameters, then run it again.

On the agent endpoint:

1. Call listDatasources and pick an accessible, READY source.
2. Call searchSemanticEntities for the relevant concepts, then getSemanticEntities in one batch for the definitions you need. Check usable on each.
3. Call getJoinGraph and resolveValues as needed. Use findSqlResolutionMatch or searchSqlResolutions when cached SQL helps.
4. Draft SQL, call compileSQL, or pick a verified query and its current version. Call validateSQL if you hold sql:execute.
5. Call executeSQL with an idempotency key, poll getSQLExecutionStatus, and read pages with getSQLExecutionResult.

• Treat truncated: true on any result as an incomplete row transfer - don't draw conclusions from a partial set.
• Retry transient failures with bounded backoff. Retry executeSQL and cancelSQLExecution with the same idempotency key, so a retry never starts a second query. Do not retry validation or authorization errors without changing the request.

Error handling

Client connector error codes are INVALID_INPUT, NOT_FOUND, ACCESS_DENIED, VERSION_CHANGED, COULD_NOT_VALIDATE, and FAILED. Agent tools use INVALID_INPUT, NOT_FOUND, ACCESS_DENIED, UNAVAILABLE, and the query codes such as IDEMPOTENCY_CONFLICT and STALE_REFERENCE. Full details for every tool are in the MCP Tool Reference.

See these connections in action

MCP is one door into the same governed compiler. Two reads pair with this page: