AegisQuery — a governed analytics MCP server

A production-grade Model Context Protocol server that gives LLM agents safe, read-only access to an analytics data warehouse — mediated by a layered safety pipeline (AST validation, column/row governance, PII masking, cost limits, result truncation, audit) over both stdio and an OAuth-secured Streamable HTTP transport.

Built in TypeScript against the official @modelcontextprotocol/sdk, targeting MCP spec revision 2025-11-25 and designed stateless-first for the direction the protocol is heading.

Its differentiator is the security story: it reproduces the class of bug that defeated Anthropic's own reference Postgres MCP server — a stacked-statement injection (COMMIT; DROP SCHEMA public CASCADE;) that bypassed a naive READ ONLY transaction — and then defeats it with structural AST validation plus defense-in-depth. See docs/ for the full SRS / HLD / LLD / Design documents.

🔴 Live demo

web-beta-rosy-0ovpsr1q8m.vercel.app — a coffee-themed web console (light + dark) that runs the same governance engine on Vercel serverless. Switch roles (analyst / regional / finance / admin) and watch the same query behave differently: PII masked for an analyst but raw for an admin, cost denied for an analyst but visible to finance, and adversarial SQL rejected on the spot. Source in web/.

Quickstart

npm install
npm run seed          # build a local SQLite warehouse (deterministic synthetic data)
npm run smoke         # end-to-end verification incl. an adversarial SQL corpus
npm run eval          # golden-task scorecard vs an independent oracle

# run the server
npm run build && npm start          # stdio (local, env-key auth)
npm start -- --http                 # Streamable HTTP (remote, bearer/OAuth auth)

Point an MCP client (e.g. Claude Desktop, MCP Inspector) at node dist/index.js, with AEGIS_API_KEY=devkey in the environment.

What it exposes

Tools (workflow-oriented, not endpoint mirrors):

Resources — the warehouse schema (aegis://catalog, aegis://schema/{table}), annotated per-policy with allow / mask / deny. Prompts — analyze_question, steering agents to discover schema, prefer curated metrics, and treat results as data.

The safety pipeline

Every query passes the same ordered stages before a single row is returned:

parse (AST) → read-only guard → reference extraction → table/column allowlist
  → PII masking → row-policy injection → cost guard (LIMIT/scan budget)
  → execute (read-only role) → truncate to token budget → audit

• AST read-only guard — only a single SELECT/WITH/UNION passes; multiple statements parse to an array and are rejected. This structurally prevents the stacked-statement bypass.

• Defense in depth — the SQLite connection is opened read-only, so even a parser gap cannot mutate data.

• Governance — deny-by-default table/column allowlists; PII columns masked (substr(x,1,2)||'***') and refused if used outside the projection; restricted columns (cost, url) require an elevated role.

• Row-level policy — mandatory WHERE predicates injected from principal attributes (e.g. a regional_analyst is scoped to their region).

• Cost & context — planner-based scan estimate rejects runaway queries; every result is LIMIT-capped and truncated to a ~25k-token budget.

• Audit — one structured JSON record per invocation (principal, raw + rewritten SQL, decision, rows, ms).

Auth

• stdio — credentials from the environment (AEGIS_API_KEY), per the spec (stdio servers should not use OAuth).

• HTTP — bearer token validated and mapped to a role/policy; unauthenticated requests get 401 with RFC 9728 Protected Resource Metadata. Stateless (no session id) so it scales behind a round-robin load balancer.

Key map format (AEGIS_API_KEYS): key:role[:attr=val;attr=val], e.g. devkey:analyst, eu-key:regional_analyst:region=EU, admin-key:admin.

Configuration

Architecture

transports/{stdio,http}  →  server (McpServer)  →  tools ─┐
                                    │                      ├─ pipeline ─ sql/analyzer (AST)
                                auth (principal)           │              policy (governance)
                                catalog (resources)        │              db/adapter → sqlite
                                                           └─ audit

The warehouse sits behind a read-only ReadOnlyAdapter interface — SQLite locally, Postgres in production — so governance logic never touches a specific engine.

Learning-ordered roadmap

This repo is built as a learning vehicle; each milestone is independently demoable and teaches one MCP / AI-engineering competency.

Verification

npm run typecheck   # strict TS, src + scripts
npm run smoke       # 20 assertions incl. 9 adversarial rejections + integrity check
npm run eval        # dev + held-out scorecard (accuracy, tokens, latency, errors)

Documentation

Full design suite (generated, colorful .docx) in docs/: SRS, HLD, LLD, Design. Regenerate with npm run docs. All four are driven by a single source of truth in scripts/docgen/spec.ts.

License

MIT.