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):

Tool	Purpose
search_catalog	Rank tables/columns relevant to a natural-language question (with access levels).
revenue_by_period	Curated metric — gross/net revenue by day/week/month. No model-authored SQL.
preview_query_cost	Validate a query and return plan + cost + violations without executing.
run_analytics_query	Governed ad-hoc read-only SELECT. Full safety pipeline applies.

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

Env var	Default	Meaning
AEGIS_TRANSPORT / --http	stdio	Transport selection.
AEGIS_DB_PATH	data/warehouse.db	SQLite warehouse path.
AEGIS_MAX_ROWS	200	Hard row cap (injected LIMIT).
AEGIS_MAX_SCAN_ROWS	500000	Reject queries whose plan exceeds this.
AEGIS_RESPONSE_TOKEN_BUDGET	25000	Result truncation budget.
AEGIS_API_KEYS	devkey:analyst	Principal key map.
AEGIS_HTTP_PORT	3333	HTTP bind port.

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.

M	Build	Learn
M0	stdio server + a tool	JSON-RPC lifecycle, tools/list·call
M1	read-only query + schema resources	resources vs tools, zod I/O, structured content
M2	reproduce the CVE → AST + read-only enforcement	why appsec applies; defense-in-depth
M3	allowlist, PII masking, row policy	data governance
M4	cost guard, truncation, pagination	context engineering, token economics
M5	curated metrics + catalog search	Anthropic tool-authoring principles
M6	audit log, error taxonomy	production ops/observability
M7	Streamable HTTP + OAuth 2.1, stateless	remote MCP, auth
M8	golden-task eval harness	eval — the top hiring signal

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.