MCP Integration: Connect AI Agents to Colrows

Colrows exposes the semantic execution layer over the Model Context Protocol (MCP) - the open standard for connecting AI applications to external tools. Claude Code, Cursor, Codex, and any custom MCP client can discover governed metadata, generate proven SQL, and execute read-only queries through the same compile-then-execute pipeline that backs the HTTP API and JDBC driver.

Why MCP, not a custom connector

Agent frameworks 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 Claude, Cursor, Codex, LangChain, and LlamaIndex agents can all reach it without a rewrite. Colrows implements that server so agents inherit compile-time governance automatically - an agent cannot query a table, column, or row it isn't authorized to see, because the unauthorized plan is never generated in the first place.

Endpoint & protocol

POST https://api.colrows.com/api/mcp
Content-Type: application/json
Authorization: Bearer <mcp-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 the server stateless and horizontally scalable. Clients that only speak Server-Sent Events receive the same JSON-RPC response framed as a single SSE message event.

• Supported protocol versions: 2025-06-18, 2025-03-26, 2024-11-05.
• Supported methods: initialize, ping, tools/list, tools/call - all dispatched through the one endpoint above.
• GET and DELETE on the endpoint return 405 Method Not Allowed. Notifications return 202 Accepted with no body.

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 full tool catalog - names, input schemas, and annotations - with tools/list. Every Colrows tool publishes readOnlyHint: true and destructiveHint: false; Colrows also enforces read-only behavior server-side, so the hint is a guarantee, not a suggestion.

Authentication

MCP clients authenticate with OAuth 2.0. An unauthenticated request returns 401 Unauthorized with a WWW-Authenticate header pointing to protected-resource metadata - discover endpoints from there rather than constructing OAuth URLs by hand.

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

• Public OAuth clients - no client secret. Dynamic client registration is supported.
• Authorization-code grant with PKCE S256 mandatory.
• Authorization codes expire after 5 minutes. Access tokens expire after 15 minutes. Refresh tokens expire after 30 days and rotate on use.
• HTTPS redirect URIs; HTTP is allowed only for loopback hosts (local dev clients).
• The user authenticates through the normal Colrows browser login and explicitly approves agent access.

Scopes

If no scope is requested during authorization, Colrows grants both scopes after user approval - the same RBAC/ABAC identity that governs the browser UI governs every MCP tool call.

Tool catalog

Nine tools, split across the two scopes above. Every tool is read-only - see read-only & security guarantees. Metadata tools resolve against the exact entities and definitions curators manage in Entity Manager - whatever a curator names and defines there is what an agent's search_metadata or ask_metadata call will see.

Calling a tool

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

Every non-MCP-native client can call the same tools over a thin REST wrapper instead of speaking JSON-RPC directly - see the MCP Tools REST API.

Response envelope

Agents should read structuredContent.data. The content array carries the same payload as text for MCP hosts that only render text blocks.

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

Tool-level failures set isError: true with an actionable message in content rather than throwing a raw exception - an agent can read the message and adjust the next call.

Clarification & continuation

Tools that delegate natural-language interpretation - generate_sql and ask_data - may need more information before they can compile a proven query. Instead of guessing, Colrows returns a typed clarification and an opaque continuation token:

{
  "status": "clarification_required",
  "continuationToken": "cr_mcp_cont_...",
  "expiresAt": 1782200000000,
  "clarification": {
    "id": "revenue-definition",
    "question": "Which revenue definition should be used: gross revenue before deductions or net revenue after deductions?"
  }
}

The client resumes the same tool call with the token and the user's answer; Colrows replays the original request with the accumulated clarification history:

{
  "name": "generate_sql",
  "arguments": {
    "continuationToken": "cr_mcp_cont_...",
    "clarificationResponse": { "answer": "Use net revenue after deductions." }
  }
}

• Tokens are opaque, cryptographically random, expire after 15 minutes, and are bound to the user, organization, and original tool.
• Any Colrows node can resume the tool call - no server affinity required.
• At most three clarification rounds are allowed per query.
• A different answer, wrong tool, wrong identity, expired token, or concurrent replay is rejected.

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 this surface.
• execute_query and ask_data reject any statement that isn't SELECT. Mutation attempts fail compilation before the warehouse ever sees them.
• Every executed query passes through the same ACL, row-level security, and redaction paths as the Colrows UI and API.
• MCP telemetry never logs OAuth tokens, authorization codes, PKCE verifiers, natural-language questions, generated SQL, or query result rows.

Building agents on Colrows

• Discover available datasources with list_datasources, then inspect schemas with list_schemas and get_schema.
• Find business concepts by name with search_metadata before writing a query - don't invent datasource IDs, schemas, table, or column names.
• Use generate_sql when SQL is the artifact you want and it must not run yet; use execute_query once you already have validated SQL.
• Use ask_data for open-ended analytics questions where the caller expects text, data, or both.
• Treat truncated: true on any result as an incomplete row transfer - don't draw conclusions from a partial set.
• Retry transient infrastructure failures with bounded backoff; do not retry validation or authorization errors without changing the request.

Error handling

See these connections in action

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