Skip to content

SPEC.md

Latest commit

 

History

History
360 lines (259 loc) · 24.7 KB

SPEC.md

File metadata and controls

360 lines (259 loc) · 24.7 KB
Raw

QueryMesh — Implementation Specification

A standalone, multi-tenant MCP server that gives AI agents read-only, scope-controlled, fully audited access to production data across many products, services, and databases.

Status: Draft for implementation · Owner: Platform · Companion doc: mcp-data-service-design.md

Resolved decisions (v1): config lives in a config service backed by QueryMesh's own MySQL database (O1); daily_summary is out of scope — query is the only tool (O2); limits are optional, defaulting to 5000 rows / 10000 ms (O3); audit goes to a MySQL table with 180-day retention (O4); institute_id → schema resolution is agent-supplied, validated against the node allowlist (O5); keys are issued by a CLI, no auto-rotation (O6); source secrets use the env backend (O7). QueryMesh's service DB holds three tables: registry_nodes, api_keys, audit_log.

1. Scope & Goals

QueryMesh is a shared infrastructure service — not tied to any one product (Edumix is the first consumer, not the only one). Any product's agent can be granted a scoped API key and query the data it is entitled to, through one uniform tool surface.

1.1 Functional goals

# Capability Notes
F1 Resolve an API key to a scope set, on every request No anonymous access.
F2 Inject a pre-filtered registry (only in-scope nodes) into the agent Agent cannot enumerate what it can't reach.
F3 query tool — validated, read-only ad-hoc query against one project/service/db Per-engine guards.
F4 Layered read-only enforcement (app guard + DB credential) Defense in depth.
F5 Structured audit log to a store separate from the audited DBs Successes and denials.
F6 Add a project/service/db by config change, not code change An INSERT into registry_nodes, no deploy.

1.2 Non-goals (v1)

  • No write operations of any kind.
  • No daily_summary tool (O2) — query is the only tool for v1.
  • No registry-discovery tools (list_*) — the scoped registry is injected upfront (revisit if context size grows).
  • No natural-language→SQL translation inside QueryMesh; the agent composes queries, QueryMesh validates/executes them.
  • No caching of result sets.
  • No multi-region replication of QueryMesh itself (single deployment per environment for v1).

1.3 Success criteria

  • A new consumer product can be onboarded with only a registry entry + an API key entry.
  • No code path can execute a write against any registered source.
  • Every tool call — allowed or denied — appears in the audit store within the flush window.

2. Architecture

┌────────────┐   MCP over Streamable HTTP    ┌──────────────────────────────────────┐
│  AI Agent  │  Authorization: Bearer <key>  │             QueryMesh                 │
│ (product X)│ ────────────────────────────► │                                       │
└────────────┘                               │  Express edge                         │
      ▲                                      │   └─ auth middleware (key → principal) │
      │  injected scoped registry            │  MCP server (SDK)                      │
      │  + tool results                      │   ├─ tool: query                       │
      └───────────────────────────────────── │   ├─ scope validator                   │
                                             │   ├─ connector layer ── pool manager   │
                                             │   └─ audit pipeline ─────────┐         │
                                             └──────────────────────────────┼─────────┘
                                                     │ read-only, replica-preferred    │
                                          ┌──────────┴───────────┐          ▼
                                          ▼                      ▼    ┌─────────────┐
                                    ┌──────────┐          ┌──────────┐│ Audit store │
                                    │  MySQL   │          │ MongoDB  ││ (separate)  │
                                    │ replicas │          │ replicas │└─────────────┘
                                    └──────────┘          └──────────┘

2.1 Transport decision

  • Streamable HTTP transport (from @modelcontextprotocol/sdk), fronted by Express. Chosen over stdio because QueryMesh is a shared network service consumed by multiple remote agents.
  • Express owns: TLS termination boundary (behind a load balancer), authentication middleware, rate limiting, health/readiness endpoints.
  • The MCP SDK owns: protocol framing, tool registration, session management.
  • One MCP session per authenticated connection. The resolved principal (key id, label, scope set) is bound to the session at handshake and is the source of truth for all subsequent tool calls in that session.

2.2 Process model

  • Long-lived Node process. Connection pools are held in memory for the process lifetime, keyed by registry path. This is a primary reason for Node over a per-request PHP model.
  • Stateless with respect to horizontal scaling except for pools (each instance maintains its own) and in-flight MCP sessions (sticky routing required at the LB, or session affinity via the SDK's session id).

3. Configuration & Registry

3.1 Sources — the config service (O1)

Config lives in QueryMesh's own MySQL database, not in files. Three tables (src/store/schema.sql):

  1. registry_nodes — the Project → Service → Database tree, one flat row per DB node. Non-secret.
  2. api_keys — key id → label + scopes + HMAC hash (never the raw key, see §4.1).
  3. node_secrets — secret_ref → source connection creds ({host, port, user, password, database?}), see §4.5.
  4. audit_log — the audit store (§8).

ConfigStore reads registry_nodes + api_keys at boot, re-validates each row through the same Zod schema (fatal on bad data), and re-reads on an interval (CONFIG_REFRESH_MS, default 30 s) so registry/key edits take effect without a restart. Adding a data source or a key is an INSERT — no deploy. Because scopes are resolved live per request (§4.2), granting a key access to a new dataset takes effect within one refresh cycle with no re-authorization or reconnect.

Secrets (source DB hosts, credentials) live in QueryMesh's own DB, in node_secrets, keyed by the secret_ref each registry_nodes row names; a SecretResolver (db backend, default) fetches the host/user/password at pool-creation time. Only QueryMesh's own DB creds come from QM_DB_* env vars, and the key-HMAC pepper from QM_KEY_PEPPER. (An env backend — QM_SECRET_<ref> JSON blobs — remains available via SECRET_BACKEND=env for setups that prefer a secret manager over the DB.)

3.2 Node shape (Zod-validated on load; the row's JSON columns mirror this)

projects:
  edumix:
    services:
      api:
        databases:
          api_cluster1:
            engine: mysql
            secretRef: edumix/api/api_cluster1        # → SecretResolver
            description: "Primary API cluster 1"
            limits: { maxRows: 5000, timeoutMs: 10000 }   # optional per-db override
          api_cluster2:
            engine: mysql
            secretRef: edumix/api/api_cluster2
            description: "Primary API cluster 2"
      eduvid:
        databases:
          videocdn:
            engine: mongo
            secretRef: edumix/eduvid/videocdn
            description: "Video CDN delivery data, per-institute schema"
            tenancy:                                   # see §3.4
              mode: institute
              paramName: institute_id
              allowlist: [inst_101, inst_102, inst_103]

Rules

  • Any load-time schema violation is fatal — QueryMesh refuses to start with an invalid registry.
  • description is agent-facing documentation; keep it accurate (the agent uses it to pick a DB).
  • limits fall back to engine defaults (§6.4) when omitted.

3.3 Registry path

A node is addressed by the tuple project/service/db. The Registry class exposes:

  • resolve(path): DatabaseNode | undefined
  • filterByScopes(scopes): Registry → a new registry containing only in-scope nodes (used for injection).
  • toInjectedView() → the agent-facing JSON (engine, description, tenancy param name & allowlist — never secretRef or host).

3.4 Multi-tenant (per-institute) resolution

Some Mongo sources use a dedicated schema/DB per institute. Per O5, resolution is agent-driven: the agent supplies the concrete schema as institute_id.

  • The node declares tenancy: { mode: institute, paramName, allowlist }.
  • The guard rejects any institute_id not on the node's allowlist, before any query is built; the accepted value is used directly as the Mongo database name (resolvedTenant). There is no server-side id→schema translation table.
  • The allowlist is therefore the security boundary — keep it accurate in registry_nodes.tenancy_json.

4. Authentication & Authorization

4.1 API keys

  • Keys are opaque high-entropy strings issued by the CLI (npm run issue-key, O6) — no automatic rotation; reissue + disable to rotate. The raw key is printed once.
  • QueryMesh stores only HMAC-SHA256(pepper, rawKey) (auth/hash.ts); the pepper lives only in QM_KEY_PEPPER. HMAC (not per-key-salted argon2) is deliberate — it allows O(1) lookup-by-hash against api_keys while keeping raw keys unrecoverable.
  • Incoming Authorization: Bearer <key> → HMAC → lookup → principal { keyId, label, scopes[] }. Unknown/disabled key → 401, and an audit denied entry with reason unknown_key.
  • Raw keys are never logged and never leave the auth boundary.

4.2 Scopes

A scope is a registry-path prefix at project, service, or database granularity:

api_keys:
  key_abc123:
    label: "daily-summary-agent"
    hash: "<argon2id hash>"
    scopes:
      - edumix.api                 # whole service (all DBs beneath)
      - edumix.eduvid.videocdn     # single DB
  key_def456:
    label: "eduvid-analytics-bot"
    hash: "<argon2id hash>"
    scopes:
      - edumix.eduvid              # whole service

Scope matching (auth/scope.ts): a request path p/s/d is in-scope iff some scope is a prefix of it at a node boundary. edumix.api matches edumix/api/api_cluster1; edumix.api does not match edumix/apikeys (boundary-aware, not string-prefix).

Live scope resolution: a request's effective scopes are not the snapshot captured at connect time. Both verifyAccessToken and the query/list_sources tools resolve the key's current scopes from the (hot-reloaded) ConfigStore by keyId on every call. Consequences: (a) granting a key a new dataset takes effect within one CONFIG_REFRESH_MS cycle — no re-authorization, no reconnect; (b) disabling/removing a key invalidates its live OAuth tokens within the same window. list_sources is the authoritative current view; the query tool's embedded source list is a connect-time hint only.

4.3 Two-line enforcement

  1. App layer — every tool call re-validates project/service/db against the session principal's scopes before touching a connector. Out-of-scope → denied, audited with denial_reason.
  2. DB layer — the credential behind each node is read-only and DB/schema-scoped. Even if the app check were bypassed, the credential cannot write or reach outside its own scope.

Injection is pre-filtered (§3.3): the agent's registry view contains only in-scope nodes, so it cannot enumerate — let alone name — anything it isn't entitled to.

4.4 OAuth 2.1 (Claude UI / connector flow)

For clients that can't send a static bearer header (the claude.ai / Claude Desktop custom connector UI), QueryMesh is its own OAuth 2.1 Authorization + Resource Server, built on the SDK's auth framework (mcpAuthRouter + an OAuthServerProvider). It reuses the existing API-key model rather than adding a second identity system.

Endpoints (mounted at the root by mcpAuthRouter, verified working):

  • /.well-known/oauth-authorization-server (RFC 8414) and /.well-known/oauth-protected-resource/mcp (RFC 9728) — discovery.
  • /register — Dynamic Client Registration (RFC 7591), clients persisted in oauth_clients.
  • /authorize — renders an interactive login page.
  • /token — PKCE (S256) code exchange, validated by the SDK.
  • /oauth/login — the login page's POST target (QueryMesh-specific).

Flow: client discovers metadata via the WWW-Authenticate on a 401 from /mcp → registers → opens /authorize → the user pastes a QueryMesh API key on the login page → /oauth/login validates the key and mints a one-time code (oauth_codes) bound to that key's principal + PKCE challenge → client exchanges code at /token → QueryMesh issues an opaque access token (oauth_tokens) that records the key id → client calls /mcp with that token. The token stores a scope snapshot for reference, but the scopes enforced on each request are re-resolved live from the key's current grants (§4.2), so the token never goes stale after a scope change.

One verifier for both worlds: verifyAccessToken accepts either an issued OAuth token or a raw API key, so the UI (OAuth) and CLI/curl (raw key) share a single auth path; requireBearerAuth populates req.auth, and the edge derives the Principal (keyId, label, scopes) from it — everything downstream (scope checks, audit) is unchanged. For an OAuth token, an unknown/disabled key id makes the token verify as invalid (401).

Refresh + revocation: the code exchange returns an access token (QM_OAUTH_TOKEN_TTL, default 1h) and a refresh token (QM_OAUTH_REFRESH_TTL, default 30d). /token with grant_type=refresh_token issues a fresh pair and rotates the refresh token (the presented one is consumed on use, so a leaked refresh token has a short window and reuse is detectable). Refreshes rebind to the key's current scopes and may narrow but never widen them. /revoke deletes an access or refresh token; a daily job purges expired codes/tokens.

Notes / hardening: tokens are opaque and DB-verified per request. /oauth/login re-validates redirect_uri against the registered client. The OAuth issuer (QM_PUBLIC_URL) must be the public https origin in production (the UI won't reach localhost — use a tunnel/LB); http://localhost is accepted for dev.

4.5 Source credentials (node_secrets)

Each registry_nodes row names a secret_ref; the actual source connection creds live in node_secrets(secret_ref → secret_json) in QueryMesh's own DB (SECRET_BACKEND=db, the default). A DbSecretResolver reads and Zod-validates {host, port, user, password, database?} at pool-creation time (once per registry path). A secret_ref can be shared by multiple nodes. add-node prompts for the creds and upserts both the node and its secret in one run.

Access control: creds are stored as-is, so the node_secrets table is protected by DB-level least-privilege — the querymesh app user is its only reader, and the injected agent view (§3.3) never exposes secret_ref, host, or creds. For deployments that prefer a dedicated secret manager, SECRET_BACKEND=env keeps the original QM_SECRET_<ref> JSON-blob resolver (and AWS SM / Vault resolvers can implement the same SecretResolver interface).

5. Tool Surface

Small, generic, registry-path-parameterized. Defined with Zod input schemas so the SDK advertises them and validates arguments.

5.1 query

query(project: string, service: string, db: string, operation: object) → QueryResult
  • operation shape depends on engine (validated by the connector, not the tool):
    • MySQL: { sql: string, params?: unknown[] } — a single SELECT.
    • Mongo: { collection: string, kind: "find" | "aggregate", filter?/pipeline?, projection?, limit?, institute_id? }.
  • Flow: scope check → connector resolves node → guard validates read-only + limits → execute on pool → shape result → audit → return.
  • Returns { rows, rowCount, truncated, executionTimeMs }.
  • For tenant-scoped Mongo nodes, the agent supplies the concrete schema as institute_id; the guard validates it against the node's allowlist (O5 — no server-side id→schema map). Anything off the allowlist is denied.

5.2 Errors

Uniform result envelope so the agent can reason about failures: { status: "success" | "denied" | "error" | "timeout", ... , message? }. denied and timeout are first-class, not exceptions.

6. Per-Engine Read-Only Enforcement

6.1 MySQL guard (connectors/guards/mysql-guard.ts)

  • Parse/validate that the statement is a single SELECT (or WITH … SELECT). Reject ;-separated multi-statements (also disable multi-statements at the driver level).
  • Reject any DML/DDL keyword at statement root (INSERT|UPDATE|DELETE|REPLACE|MERGE|CALL|CREATE|ALTER|DROP|TRUNCATE|GRANT|...).
  • Enforce/append LIMIT past the node's maxRows; set MAX_EXECUTION_TIME per timeoutMs.
  • Always use parameterized values (params), never string interpolation.

6.2 MongoDB guard (connectors/guards/mongo-guard.ts)

  • Whitelist operations: find, aggregate only. No runCommand, eval, mapReduce, findAndModify, etc.
  • In aggregate pipelines, block $out and $merge (they write despite being "aggregate"), and block $function/$where/$accumulator (JS execution). Recurse into $lookup/$facet/$unionWith sub-pipelines.
  • Enforce limit ≤ node maxRows; apply maxTimeMS per timeoutMs.
  • Resolve institute_id against the allowlist before selecting the schema/DB.

6.3 Credential posture (DB layer)

  • MySQL: dedicated user with GRANT SELECT only, scoped to the specific schema(s).
  • Mongo: dedicated read role, scoped per institute schema/DB.
  • Both: prefer read replicas over primaries; connect with least privilege.

6.4 Default limits

Engine maxRows timeoutMs
mysql 5000 10000
mongo 5000 10000

Overridable per node via limits. The stricter of node-limit and hard ceiling wins.

7. Connection Pool Management

  • PoolManager keeps one pool per registry path, created lazily on first use, keyed by project/service/db (+ resolved tenant schema for Mongo where the driver requires it).
  • Pool config (size, idle timeout) from node config with sane defaults; credentials from SecretResolver at creation.
  • Health: periodic ping; on repeated failure, drop and lazily rebuild. Expose pool state to /readyz.
  • Graceful shutdown drains and closes all pools.

8. Audit Logging

  • QueryMesh's own MySQL audit_log table (O4) — the same service DB as the config service, but a store no audited source can reach or tamper with. Written by MysqlAuditSink.
  • Written for every tool call: success, denied, error, timeout.
  • Buffered + async flush (AuditLogger); audit-write failure must not silently drop — falls back to stderr and (TODO) a metric.
  • Retention 180 days (AUDIT_RETENTION_DAYS): a daily job runs MysqlAuditSink.purgeOlderThan(days).

8.1 Record

Field Notes
timestamp ISO-8601 UTC
keyId / label who — never the raw key
project / service / db resolved path (may be partial on early denials)
tool query
queryText / filterParams exact query/filter — hashed/truncated for PII-flagged nodes (§8.2)
rowCount result size
executionTimeMs perf/cost signal
status success | denied | error | timeout
denialReason populated on denied
sourceIp / sessionId request origin

8.2 Policy decisions (make explicit at implementation)

  • PII nodes: mark nodes/collections as pii: true; for those, store a hash + truncated query rather than full text.
  • Retention: default 180 days, configurable; a spike in denied from one key is a misuse signal — keep denials at least as long.

8a. Admin portal (/admin)

A server-rendered (plain HTML + PRG, no client build, no new deps) multi-user portal mounted at /admin on the same Express app. Cookie sessions (admin_sessions, 8 h), CSRF on every mutation (HMAC(pepper, sessionId)), passwords hashed with Node scrypt. Secure cookies when QM_PUBLIC_URL is https.

Roles & capabilities

  • superadmin: create users (username + initial password + role + optional scopes); CRUD connections (registry_nodes + node_secrets credentials in one form); assign scopes to a user; reset a user's password; disable/re-enable users; view any user's query history.
  • user: view their own query history; change their own password.
  • All users: the initial password is set by a superadmin and must be changed on first sign-in (must_change_password), enforced by middleware before any other page.

User ↔ scopes ↔ history. A portal user owns an API key (api_keys.user_id). "Assign scopes" upserts that key with the given scopes (empty scopes disable the key — a zero-scope key would fail config validation); the raw key is shown once for the user to paste into the Claude connector. "History" is the audit_log rows for the user's key(s). This reuses the existing principal model rather than adding a parallel one, and combines with live scope resolution (§4.2) so a scope change in the portal reaches a connected agent within one refresh cycle.

Bootstrap. The first superadmin is created from the CLI: npm run create-admin (interactive; that account picks its own password and isn't force-changed). Thereafter, superadmins create users in the portal. Tables: admin_users, admin_sessions (src/store/schema.sql); code under src/admin/.

9. Cross-Cutting

  • Config validation: Zod schemas; fatal on invalid registry/keys at boot.
  • Rate limiting: per-key request ceiling at the Express edge (429 + audited).
  • Observability: structured logs (pino), Prometheus metrics (querymesh_tool_calls_total{tool,status}, ..._query_duration_ms, ..._denied_total{reason}, pool gauges), /healthz + /readyz.
  • Secrets: SecretResolver interface with an env-var impl for dev and a pluggable manager impl (AWS SM / Vault) for prod.
  • Testing: unit tests for guards (adversarial query corpus), scope matcher, registry filter; integration tests against ephemeral MySQL/Mongo containers proving reads work and writes are refused at both layers.

10. Milestones

M Deliverable
M1 Service DB schema + config store + registry model + scope matcher (+ unit tests)
M2 Connector interface, pool manager, MySQL connector + guard
M3 Mongo connector + guard, tenancy allowlist
M4 MCP server wiring, query tool, registry injection, Express auth edge
M5 MySQL audit sink + retention purge
M6 Key-issuance CLI, seed/migrate tooling
M7 Observability, rate limiting, hardening, integration test suite
M8 Admin portal (§8a): multi-user, roles, connections CRUD, scope assignment, history

11. Decisions (resolved) & remaining hardening

All v1 open questions are resolved:

# Decision
O1 Config service backed by QueryMesh's own MySQL DB (registry_nodes, api_keys).
O2 daily_summary dropped — query is the only tool.
O3 Per-node limits optional, default 5000 rows / 10000 ms; stricter of override/default wins.
O4 Audit → MySQL audit_log, 180-day retention purge.
O5 Tenant schema is agent-supplied, validated against the node allowlist (no server-side map).
O6 Keys issued via CLI (issue-key); rotate by reissue + disable, no auto-rotation.
O7 Source secrets via env backend (QM_SECRET_*); pluggable resolver kept for future AWS SM/Vault.

Remaining before production (not blocking the skeleton): per-key rate limiting at the edge; audit-write-failure metric; /readyz reflecting pool health; integration suite against ephemeral MySQL/Mongo proving writes are refused at both layers; hardening the MySQL guard's single-SELECT parse against edge cases.