REST API, Headless Metrics & MCP

REST API, headless metrics and MCP serversOpen the platform to scripts, notebooks, other BI tools and AI agents — and connect BigQuery, PostgreSQL, Jira and Confluence through the Model Context Protocol — without opening a second, weaker door. A BI platform that can only be used through its own screens becomes an island: the numbers get copied out by hand, retyped in a notebook, and drift. ATX AnalyticsTable can be used from outside as well as inside. The public REST API at /api/v1 gives a script, a scheduler, a notebook, Power Automate, another BI tool or an AI agent read access to project tables, saved connections, dashboard data and governed metrics. Headless metrics are the semantic layer served as a service: ask for revenue by region and get the same number the dashboard shows, with the SQL the planner wrote, because both read the one definition. And ATX AnalyticsTable is a Model Context Protocol client: an administrator registers MCP servers — Google BigQuery and PostgreSQL through Google's MCP Toolbox for Databases, Jira and Confluence through Atlassian's Rovo MCP server or the self-hosted mcp-atlassian — once, from a template, and their tools become available to the API and, when the data policy allows it, to Ask Greg. None of this is a side entrance. Every call goes through the same sign-in, roles, object grants, row- and column-level security, licence and tamper-evident audit trail as the application, and every SQL endpoint accepts one read-only statement, for every role, because a credential held by a script can leak and a leaked one should not be able to write.

What it does

A versioned REST API at /api/v1 on the same host and port as the application: no new listener, no new port, published through your reverse proxy
An OpenAPI 3.1 document at /api/v1/openapi.json and a self-contained HTML reference at /api/v1/docs that works on an air-gapped server, both generated from the same catalogue the contract test checks
Three credentials: scoped service tokens with their own name and role (stored as SHA-256 digests, revoked by editing a file, no restart), Microsoft Entra ID access tokens, and the installation token for break-glass administration
Read project tables and run one read-only DuckDB statement on the project database; read saved connections and run one SELECT or WITH on them — credentials are never returned
Dashboard definitions and the data behind any widget, under the dashboard's own grant
Headless metrics: list the catalogue, see which dimensions a measure can be sliced by, and query measures by dimensions with governed filters, typed parameters, ordering before the limit, and a dry run that returns the SQL without running it
RFC 9457 problem documents with a stable code and a retryable flag for every error, including refusals from sign-in, roles and the licence gate
Per-credential rate limits with IETF RateLimit headers and Retry-After, Idempotency-Key on every POST with a 24-hour replay window, cursor paging up to 50,000 rows a page, and JSON, CSV (formula-safe) or NDJSON
An MCP client over local processes (stdio) and streamable HTTP, speaking protocol revisions 2025-11-25, 2025-06-18, 2025-03-26 and 2024-11-05
Templates for Google BigQuery, PostgreSQL, Jira Cloud, Confluence Cloud, Jira and Confluence Server / Data Center, and a custom server of either transport — a new built-in template is one entry in one list
Every MCP tool classed read, SQL or write; write tools refused on a read-only server; SQL arguments checked to be one SELECT or WITH before anything is sent; allow-lists and name patterns to narrow a server further
Passwords and Authorization headers sealed in the encrypted vault, a local server started with an allow-listed environment that never includes the installation token, and remote servers reached only through the egress guard and proxy
A circuit breaker, a per-server bulkhead, deadlines, a safe reconnect that never re-sends a write, idle shutdown and a result cap, with health on /api/v1/health and Prometheus metrics
Ask Greg can list and call the tools of opted-in servers — with every result reduced to the user's data level, masked and audited — once an administrator turns on two switches
The other direction too: with ANALYTICS_MCP_SERVER=1, ATX AnalyticsTable is itself an MCP server at /api/v1/mcp, so Claude, Copilot Studio, Cursor or your own agent can search, describe and query governed metrics — read-only, no SQL, with the caller's rights (chapter 27)

How it works

An administrator issues a scoped service token for each integration: generate 32 random bytes, put the SHA-256 digest with a name and a role in the file named by ANALYTICS_API_TOKENS_FILE, and hand the token to the integration through its own secret store. GET /api/v1/me with the token shows the identity, role, permissions and rate limit it resolves to. Viewer is enough for everything except calling an MCP tool, which needs analyst.
A script sends Authorization: Bearer <token>. To read a project table it posts one SELECT to /api/v1/query with the project's path in X-Project-Path and a page size; the answer carries the rows and a nextCursor, and the script sends the same body plus the cursor until the cursor is null. ?format=csv returns the same rows as CSV with formula-looking cells neutralised.
Anything that is not one read-only statement — an INSERT, two statements, read_csv of a file — is refused with 422 read_only_sql, whatever the token's role. Each refusal and each success is in the audit trail with the SQL's SHA-256 hash, the row count and the duration, never the data.
For governed numbers, the script posts measures and dimensions to /api/v1/metrics/query instead of writing SQL. The semantic planner joins datasets only through declared joins, writes the SQL for the connection's dialect, renders parameters as checked literals, applies ordering before the limit, and returns the plan beside the rows — with notes such as a warning that a to-many join may double-count.
When something goes wrong the answer is a problem document: a stable code such as rate_limited, upstream_timeout or planner_error, a human-readable detail, the request id, and retryable. A client retries only what is retryable, honours Retry-After, and sends an Idempotency-Key on a POST so that a retry after a lost answer is replayed rather than run twice.
To connect BigQuery, an administrator opens Settings → MCP servers & API and chooses the Google BigQuery template. A Parameters table under the form explains every line of the Environment box: BIGQUERY_PROJECT is the project id from the service-account key file, GOOGLE_APPLICATION_CREDENTIALS is the PATH of that key file on the server (never the JSON itself; empty means Application Default Credentials), BIGQUERY_LOCATION is where the datasets live, and optional lines set a row limit, a per-query cost cap and the Toolbox's own write block. Test checks those values first — an empty project or a pasted key is reported in plain words — then starts the MCP Toolbox, lists its tools and reports the protocol version; Save keeps it. Tools then shows every tool with its class: list_dataset_ids, list_table_ids, get_table_info are reads; execute_sql is SQL, checked read-only.
Jira and Confluence Cloud are one remote server at https://mcp.atlassian.com/v2/mcp; the only thing to enter is the Authorization header, marked secret, as Basic with an e-mail and API token or Bearer with a service-account key. The template's tool pattern keeps only Jira or only Confluence tools, and write tools such as creating an issue are refused because the server is read-only.
If a server starts failing, five failures in a row open its circuit: for 30 seconds every call is refused at once with 503 and Retry-After instead of each waiting out a timeout, then one trial call decides whether it has recovered. A slow server can run at most four calls at once; the rest are refused rather than queued without end. The screen shows Healthy, Paused after errors or Recovering, and Reset closes the circuit after a fix.
To let Ask Greg use a server, the administrator turns on Available to Ask Greg on that server and Let the agent use MCP servers in Ask Greg → Data policy. Greg then sees the opted-in servers and two tools, list_mcp_tools and call_mcp_tool. Every result is reduced to the person's effective data level — text withheld at schema-only, cut to 4,000, 12,000 or 24,000 characters otherwise — and masked, and a ticket that contains instructions is reported on, never obeyed.

Practices that keep it reliable

Give every integration its own scoped service token with the lowest role that works — viewer for reading, analyst only where it calls MCP tools — and never use the installation token in a script
Use headless metrics rather than copying a metric's SQL into a notebook; a definition changed in the semantic model then changes every consumer at once
Read nextCursor until it is null instead of asking for one enormous page; a large page meets the result-size limit and costs the server memory
Retry only what retryable says, honour Retry-After, and send an Idempotency-Key with every POST
Give database MCP servers a login that can only SELECT — the read-only check is the second line of defence, not the first
Reference a service-account key by the PATH of its file, readable only by the account that runs ATX AnalyticsTable — never paste the key into the Environment box, which is stored as plain text and refuses keys on save
Set BIGQUERY_MAXIMUM_BYTES_BILLED to a sensible cap so an agent's question can never scan a whole warehouse
On a shared server set ANALYTICS_MCP_STDIO=0 or limit ANALYTICS_MCP_ALLOWED_COMMANDS, so only remote servers or approved executables can run
Narrow each server with Allowed tools or a Tool name pattern, and review a custom server's tools before making it available to Ask Greg
Alert on analytics_mcp_circuit_open and on a rising share of 429 and 503 answers, and give API gateways /api/v1/health as their synthetic check

In a regulated environment

One security model on every path: the API is a facade over the application's own routes, so roles, object grants, row- and column-level security, admission lanes, time limits and the licence apply unchanged — and a test fails if a route's role disagrees with the published catalogue.
Read-only by construction: every SQL endpoint and every SQL-class MCP tool accepts exactly one SELECT or WITH, for every role, refused before it reaches the database. Verified against a real BigQuery project: DELETE, DROP, UPDATE and CREATE TABLE AS were refused in under two seconds and the row count was unchanged.
Secrets never travel: scoped tokens are stored as SHA-256 digests, MCP passwords and Authorization headers are sealed in the credential vault, and none of them is returned by any endpoint, written to a log or recorded in the audit trail.
Evidence for an access review: query.run records the SQL's hash, row count and duration; mcp.tool.call records server, tool, caller, route (api, agent or settings) and a hash of the arguments; mcp.server.* records every configuration change. None of them holds data.
Standards an API gateway and a procurement review recognise: OpenAPI 3.1, RFC 9457 problem details, IETF RateLimit and Idempotency-Key headers, versioned paths with additive-only changes in v1, and the Model Context Protocol for AI interoperability.
Stated plainly: the API reads, it does not write; scoped tokens are managed in a file rather than issued from a screen; and Ask Greg uses MCP only after an administrator turns on two switches.

Screens

Tools of a real BigQuery server. execute_sql is allowed because its SQL is checked to be one read-only statement before it is sent; tools outside the template's allow-list are refused, and say why.
The BigQuery template: one KEY=value per line, an empty value meaning not set, the key file referenced by its path — never pasted — and Add the missing lines for a server saved before the template gained them.
Ask Greg → Data policy → Let the agent use MCP servers. Off by default; Ask Greg also only sees servers marked Available to Ask Greg.
An answer read from BigQuery through MCP: every tool call listed (one failed and corrected), the SQL shown, the result passed through the same data policy and audit as project data.

Glossary terms on this page

ATX Institute on LinkedIn

This site needs JavaScript for the full experience. Every page above is reachable without it.