Fixed Reports for People, Fenced SQL for Agents: Designing an MCP Server for Data

Managers Will Point Their Own AI Client at Your Data. Decide What It Can Say.

We built a private MCP server for a Medicare sales floor. Sales managers add it to the AI client they already use (Claude, ChatGPT and others), then ask about hours, sales, quality and rosters for their teams. Their client is the agent. Our server only answers.

We also run a contained analyst agent that writes its own SQL against the same database (covered here). Both designs are in production, and they follow different rules.

In short: people's clients get fixed reports whose definitions travel with the numbers. A contained agent may get free SQL behind a fence. Permissions live in the database, not in the server's good manners. A person signs in. An agent gets a service identity.

Free SQL Lets the Model Choose the Definition

Ask a model for "close rate" and it writes a formula. Ask next week and it may write another. Nobody sees the choice. The number looks like a number. The published evidence points the same way.

Read these with care: dbt sells the semantic layer, and its test is one dataset and 11 questions. The second study found that written context moved accuracy 17 to 23 points and that model choice within the tier did not.

The sharper point is how each fails. dbt writes that with text-to-SQL, "failure looks like a plausible but incorrect answer." A governed tool fails by refusing: unknown metric, unknown team, range too long. A refusal costs a retry. A plausible wrong number costs a decision. For what benchmark scores do and do not tell you, see the benchmarks piece.

Ousterhout's test for interfaces fits here. A report tool is a deep module: much machinery behind a small interface.

”

"A deep module is a good abstraction because only a small fraction of its internal complexity is visible to its users." — John Ousterhout, A Philosophy of Software Design (§ "4: Modules Should Be Deep")

Free SQL is the reverse. Its interface is the whole dialect plus every table and column, and the caller must still guess what each column means. We kept the tool count low too: seven report tools, three canned reports and a health check. Ousterhout again: "more, or larger, interfaces are not necessarily better."

Every Figure Ships With Its Definition and a Date

Each report answers in one shape, whatever the tool:

{
  "request_id": "...",
  "rows": [], "columns": [], "truncated": false,
  "metrics": {
    "<metric_id>": {
      "label": "...", "unit": "%", "definition": "...", "source": "<relation>",
      "sales_basis": "board",
      "data_through": { "runs_to": "...", "complete_through": "..." }
    }
  },
  "period": { "start": "...", "end": "...", "time_zone": "America/New_York" },
  "data_through": { "<relation>": "<date>" },
  "notes": ["Today is not over, so every figure for today is part of a day."],
  "partial": true,
  "partial_metrics": ["<metric_id>"]
}

The server instructions tell the client to read the definition and data_through before it states a figure. The rest of the contract is a set of small rules, each aimed at a mistake we expect a model to make:

Three of these took more work than the table suggests.

Who counts is data. "The floor" means the real sales teams. We first coded that as a rule (a named lead and a share of inbound calls), judged by each report on its own last day. One week produced two different hour totals depending on the report's end date, because a team crossed the threshold in between. The fix was a dated table: one row per team saying whether it counts as a sales team from that date. A person decides and a table records it.

Names never reach SQL. A typed name or email is matched in Python against a directory read with no input. The database logs bound values for slow statements, and free text should not land there.

Errors teach. A bad argument returns the argument name, the rule it broke and the valid choices, never the caller's own value:

{ "error": { "argument": "who.agent",
             "rule": "matches no agent; give an agent_key, a name or an email",
             "choices": [] } }

Unknown fields are rejected, not ignored. Anthropic's tool guidance asks for the same: errors that "clearly communicate specific and actionable improvements."

Read-Only Is the Smallest Permission. The Tier Is the Real One.

Our managers' clients may have no BAA with us, so the server must not reach member data. That rests on the database role the server signs in as: a staff tier with no login and SELECT on named relations, granted one at a time.

CREATE ROLE staff_reader NOLOGIN;
GRANT USAGE ON SCHEMA analytics, metrics TO staff_reader;
GRANT SELECT ON analytics.dim_agent, metrics.agent_time_daily,
                metrics.team_time_daily  -- ...about a dozen, each by name
  TO staff_reader;

Every relation holds one row per agent, team or day, never one per call or member. A test in the ordinary suite checks four things for each granted relation: no column name matches a deny list (phone, member, address, date of birth, transcript, recording); a declared grain at agent, team or alias level (no grain fails); a named review (who, when, why); and no other SQL file grants to the role.

That is our security policy, and it fits on a page. Anderson asks for no more:

”

"A security policy model is a succinct statement of the protection properties that a system must have. Its key points can typically be written down in a page or less." — Ross Anderson, Security Engineering (§ "9.2. What Is a Security Policy Model?")

Adding a relation takes a review, then a deploy of an image that lists it, then the grant. Never the grant first. The server lists the relations it expects, and refuses to start if its login can read one more, so an old image will not boot against a new grant. At boot it reads the catalogue as itself:

row = conn.execute(GUARD_SQL).fetchone()      # has_table_privilege(), pg_has_role(), ...
if row.extra_reads or row.extra_writes or row.can_create_objects \
        or row.extra_roles or row.security_definer_functions:
    raise SystemExit("login can reach more than its list")

A grant added by mistake stops the server. It does not widen it silently.

The rest is layers, none of them clever:

• Read-only by default, 30 second timeout, one writable table. A second role may insert into the request log and update its outcome columns. It cannot delete or rewrite who asked what, and it reads no staff data.
• Tool guards: read-only annotations, strictly typed arguments, a per-minute limit for each person, and a 15 second wait for a connection before the answer is "busy".
• Scrubbing. The staff tier holds no phone numbers, so this pass should never match. It is the backstop: any run of seven or more digits in a result is cut to its last four before the response leaves.

”

"Treat humans and machines the same way: give them only the privileges and data they need to do their jobs, and only for the timespan when needed." — Joe Reis and Matt Housley, Fundamentals of Data Engineering (§ "The Principle of Least Privilege")

We follow the rule and break the symmetry on purpose. The person and the agent get different identities, because they differ in how contained they are.

Access follows how contained the holder is, not how senior the user is. A manager's client runs on a laptop or a vendor's cloud we do not control, so it gets the narrowest tier and no query language. The analyst runs on a VM we locked down (egress, region pins and the SQL guard are in the HIPAA piece), so it can hold a wider role and write SQL. Our plan for it: reports first, free SQL only for questions no report covers, with the assumptions stated on the first line.

If you later want raw SQL for people, filter in the database, not in the server. Postgres row security does not cover materialized views, so the route is filtered views driven by a per-transaction setting that the SQL guard stops a query from changing. Server-side filtering holds for reports and fails for raw SQL, and raw SQL is the reason to filter at all.

A Private MCP Server Is an OAuth Resource Server With a Short List of Duties

We checked the public MCP authorization spec on 13 November 2026. The revision it serves as latest is 2026-07-28. The parts that matter to a private server:

• The MCP server must publish Protected Resource Metadata (RFC 9728) and answer an unauthenticated request with a 401 whose WWW-Authenticate header points to it.
• The server must accept only tokens issued for it as audience, and must not accept or pass on any other token. Clients must send the resource parameter (RFC 8707) on both the authorization and token requests.
• The authorization server must publish metadata (RFC 8414 or OpenID Connect discovery).
• Clients should prefer pre-registered credentials, then Client ID Metadata Documents, then Dynamic Client Registration. The revision marks DCR deprecated, kept for compatibility.

A client first meets our server as a 401:

HTTP/1.1 401 Unauthorized
WWW-Authenticate: Bearer resource_metadata="https://<host>/.well-known/oauth-protected-resource/mcp"

The client then reads the metadata, registers, sends the person to a consent page and redeems a code with a PKCE verifier. How we built each step:

A small authorization server, with the identity provider as the login only. Our server issues its own opaque tokens. Clerk, our identity provider, only signs the person in on the consent page. That settled a question we could not answer in advance (does the provider bind the token audience to our resource?), because our tokens work nowhere else. A token from another of our MCP servers gets a 401 here, and the reverse.

Organization membership is the gate. Whoever may open the company's analytics app may connect. At the consent click the server checks the provider's session token (RS256, our issuer, an authorized party equal to our exact origin, a session token and not an access token) and reads membership again past the cache. On every later request it checks membership, and that the user is neither banned nor locked, with a 60 second cache. Any doubt refuses. Removing someone from the organization cuts access within about a minute.

The consent page distrusts the client name. Registration is open, so anyone can register a client under any name. The page shows the redirect host as the main fact and says the app chose its own name. A bad client or redirect shows an error and never redirects.

Codes and tokens are stored as hashes. A code lives five minutes and works once, enforced by one conditional UPDATE so two racing requests cannot both win. A token is 256 random bits stored as SHA-256, valid 90 days, with no refresh token. Each request reads the token row, so setting revoked_at works at once. A person holds at most ten live tokens.

The log names the person, taking the name from the token. Identity comes from the token row and the provider, never from tool arguments. The server writes the request row before any report SQL runs, and refuses the call if the write fails. Afterward it records outcome, duration and row count. Fixed choices are logged as given. Free text becomes a keyed hash and a length. Database error text never enters the log. An application name set on each transaction puts the user in the database's own logs.

What carries over to your server

A background agent cannot use a person's sign-in. The consent step needs a browser and a human. The token carries that person's entitlement, so the agent would hold everything that person may do and keep working until the token expires, whatever happens to the person's job. Give the agent a service identity. For us that is an IAM database user whose token the platform mints for minutes at a time, with no password to leak, a read-only role and its own audit file. The audit names the agent, which is accurate. A shared key or a borrowed login would name a person who did not ask.

For what an agent can do inside the fence, see the attack surface and containment. For why MCP is the protocol worth securing, see the protocol that won.

Definitions Are Most of the Bytes, and That Is the Price

In a field test of the server, the figures were a median of 1 to 12 percent of each response, depending on the tool. The rest was definitions and notes. By byte count that is a poor ratio, and it is why the numbers can be trusted. Strip the definitions to save tokens and the client is back to choosing its own.

Three things cut the cost without cutting the meaning.

compact: true. It keeps rows, columns, data_through and the warnings about this result (partial figures, a window past the data), and drops definitions and standing notes. Compact is right once the client has read a metric's definition in this conversation. It is unsafe on the first call, and nothing in the server enforces that order: the instructions only say what the switch drops.

Canned reports. team_day, team_weeks and league return several labelled sections in one call and state each definition once. A manager's usual question ("how did my team do these last few days?") costs one call, not five repeats of the same definitions.

Caps. Fifty rows, a 120 day range, top_n for rankings, and an error that says to add filters when a result is too large. Anthropic's guidance agrees: a concise and a detailed mode (its Slack example is 72 tokens against 206), a response cap (Claude Code's default is 25,000 tokens) and truncation messages that steer the model toward narrower calls.

One idea we have not built or measured: remember which definitions a session already received and send a reference after the first. That keeps first-call safety without trusting the client to ask for compact. The wider cost of tool definitions and intermediate results is in the MCP context tax; the cost of the wrong number nobody notices is in the hallucination tax.

What to Do Monday