Skip to content

HOW_TO.md

Latest commit

 

History

History
334 lines (232 loc) · 11.6 KB

HOW_TO.md

File metadata and controls

334 lines (232 loc) · 11.6 KB
Raw

QueryMesh — How To

A hands-on guide to installing QueryMesh, issuing scoped keys, connecting a client, and running queries. For the why and the architecture, see README.md.

Contents

  1. Prerequisites
  2. Install & configure
  3. Create the service database
  4. Register your data sources
  5. Issue an API key
  6. Run the server
  7. Connect a client
  8. Using the tools: list_sources and query
  9. Reading the audit log
  10. Operations & troubleshooting

1. Prerequisites

  • Node.js 20+
  • A MySQL instance for QueryMesh's own service database (config + audit). This is separate from any data source you expose.
  • For each data source you want to expose: a read-only DB account and network reachability from the QueryMesh host.
  • (Optional) A MongoDB instance if you're exposing Mongo sources.

Golden rule: the credentials you give QueryMesh for a source must be read-only. The query guard is your second line of defense, not your only one.

2. Install & configure

git clone <your-fork-url> querymesh
cd querymesh
npm install
cp .env.example .env

Open .env and set at minimum:

# QueryMesh's own service database
QM_DB_HOST=127.0.0.1
QM_DB_PORT=3306
QM_DB_USER=querymesh
QM_DB_PASSWORD=<a strong password>
QM_DB_NAME=querymesh

# Required — server-side pepper for hashing API keys. Keep stable and secret.
QM_KEY_PEPPER=<a long random string>

# Secret backend for the audited *sources* (dev uses inline env JSON)
SECRET_BACKEND=env

.env is loaded automatically and is gitignored. Real shell environment variables, if set, take precedence over .env.

3. Create the service database

QueryMesh's service user has least privilege — it can manage its own database but can't create databases or accounts. So the one-time creation is done by a MySQL admin:

# Edit src/store/bootstrap.sql first: replace REPLACE_WITH_QM_DB_PASSWORD
# with the same value you put in QM_DB_PASSWORD.
mysql -u root -p < src/store/bootstrap.sql

This creates the querymesh database and the querymesh user (for both @localhost and @%).

Then create the tables as the service user:

npm run migrate

Optionally seed some example registry nodes for local experimentation:

npm run seed

This adds three demo nodes (two MySQL, one tenant-scoped Mongo) under the edumix project — handy for testing before you wire up real sources.

4. Register your data sources

A data source is a registry_nodes row identified by its project / service / db path. Use the interactive CLI:

npm run add-node

It prompts for:

Prompt Notes
Project / Service / Database name The three-part logical path agents will use.
Engine mysql or mongo.
Secret ref Logical name resolved to real credentials by the secret backend (defaults to project/service/db). Never the raw secret.
Description Shown to the agent so it knows what the source contains. Write a good one — it's how the model picks the right source.
Contains PII? If yes, the audit log stores a truncated/marked query instead of the full text.
Limit overrides Optional maxRows / timeoutMs (defaults: 5000 rows / 10000 ms).
Per-tenant schema Optional. For sources with one schema/db per tenant — the agent supplies a tenant id, validated against an allowlist.

After saving, it prints the exact env variable you must set for the credentials. The mapping is QM_SECRET_ + the secret ref upper-snake-cased. For secret ref edumix/api/api_cluster1:

QM_SECRET_EDUMIX_API_API_CLUSTER1={"host":"127.0.0.1","port":3306,"user":"ro_user","password":"...","database":"api_cluster1"}

The running server hot-reloads the new node within CONFIG_REFRESH_MS (default 30s) — no deploy or restart needed for registry changes. (New QM_SECRET_* env values do require the process to pick them up, so restart after editing .env.)

You can also INSERT into registry_nodes directly — the CLI just adds validation and a preview.

5. Issue an API key

Each agent gets its own key with only the scopes it needs:

npm run issue-key -- --label "daily-summary-agent" --scopes edumix.api,edumix.eduvid.videocdn

Scopes are dot-delimited path prefixes:

Scope Grants
edumix Entire edumix project
edumix.api All databases under the api service
edumix.eduvid.videocdn Just that one database

The command prints the raw key once — store it immediately. Only its HMAC hash is saved; it cannot be recovered. Issue a new key if you lose it.

Example output:

Issued API key
  key_id: key_a1b2c3d4e5f6
  label:  daily-summary-agent
  scopes: edumix.api, edumix.eduvid.videocdn

  RAW KEY (shown once — store it now):
  qm_XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

To revoke a key, set disabled = 1 on its row in api_keys (rotation is manual — see the roadmap).

6. Run the server

npm run dev          # tsx watch, for development
# or, for a build + run:
npm run build
npm start

You should see QueryMesh listening on :8080. Health endpoints:

curl http://localhost:8080/healthz     # {"ok":true}
curl http://localhost:8080/readyz      # {"ok":true}  (pool health is a TODO)

7. Connect a client

QueryMesh speaks the MCP Streamable HTTP transport at POST /mcp, and supports two auth paths that share one verifier.

A. Raw bearer key (CLI / curl / agents)

Simplest — pass the raw key as a bearer token.

Claude Code CLI:

claude mcp add querymesh --transport http http://localhost:8080/mcp \
  --header "Authorization: Bearer qm_XXXXXXXX..."

curl (raw MCP JSON-RPC — initialize, then call a tool):

curl -sS http://localhost:8080/mcp \
  -H "Authorization: Bearer qm_XXXXXXXX..." \
  -H "Content-Type: application/json" \
  -H "Accept: application/json, text/event-stream" \
  -d '{"jsonrpc":"2.0","id":1,"method":"initialize",
       "params":{"protocolVersion":"2025-06-18","capabilities":{},
                 "clientInfo":{"name":"curl","version":"0"}}}'

The response carries an Mcp-Session-Id header — pass it back on subsequent calls.

B. OAuth connector (claude.ai / Claude Desktop UI)

QueryMesh is its own OAuth 2.1 Authorization + Resource Server, so it can be added as a custom connector. Two requirements:

  1. Public HTTPS URL. The connector UI can't reach localhost. Expose the server via a tunnel and set QM_PUBLIC_URL to that HTTPS origin (it becomes the OAuth issuer):

    cloudflared tunnel --url http://localhost:8080
# or: ngrok http 8080

    Set QM_PUBLIC_URL=https://your-tunnel-url in .env and restart.

  2. Add the connector. In claude.ai or Claude Desktop → Settings → Connectors → Add custom connector, register https://your-tunnel-url/mcp.

During the OAuth flow QueryMesh shows a login page — paste an API key issued by issue-key. The access token is then bound to that key's scopes.

8. Using the tools: list_sources and query

Once connected, the agent sees exactly two tools, plus a querymesh://registry resource — all pre-filtered to the key's scopes. It cannot see or name anything outside them.

list_sources

No arguments. Returns the exact project / service / db values to use, plus each source's engine and description. The agent should call this first if unsure of the path.

query

Runs one validated, read-only query against a single source. Arguments:

Field Type Notes
project string Must exactly match a source (case-sensitive).
service string "
db string "
operation object Engine-specific — see below.

MySQL — operation is { sql, params? }, a single SELECT (or WITH … SELECT). Writes, DDL, multiple statements, and SHOW/DESCRIBE are rejected — use information_schema for metadata. A LIMIT is appended automatically if you omit one.

{
  "project": "edumix",
  "service": "api",
  "db": "api_cluster1",
  "operation": {
    "sql": "SELECT id, email FROM users WHERE created_at > ? LIMIT 100",
    "params": ["2026-01-01"]
  }
}

MongoDB — operation is { collection, kind, filter | pipeline, projection?, limit?, institute_id? } where kind is find or aggregate. Write/JS stages ($out, $merge, $function, $where, $accumulator) are blocked, including inside nested $lookup / $facet / $unionWith pipelines.

{
  "project": "edumix",
  "service": "eduvid",
  "db": "videocdn",
  "operation": {
    "collection": "views",
    "kind": "find",
    "filter": { "status": "completed" },
    "limit": 50,
    "institute_id": "inst_101"
  }
}

For tenant-scoped sources, institute_id (or whatever the node's param is named) is required and must be in the node's allowlist — it selects the concrete per-tenant schema/db.

Response envelope

Every call returns:

{ "status": "success", "rows": [...], "rowCount": 12, "truncated": false, "executionTimeMs": 8 }

status is one of success | denied | error | timeout. truncated: true means the row limit was hit — narrow your query or paginate. Denials (out_of_scope, unknown_node, guard failures) return a message explaining why.

9. Reading the audit log

Every call — success and denial — lands in audit_log in the service database. Query it directly:

SELECT ts, label, project, service, db_name, tool, status, denial_reason,
       row_count, execution_time_ms, source_ip
FROM audit_log
ORDER BY ts DESC
LIMIT 50;

Notes:

  • For sources flagged PII, query_text is truncated and marked rather than stored in full.
  • Rows older than AUDIT_RETENTION_DAYS (default 180) are purged daily by the server.
  • Filter denials with WHERE status = 'denied' to spot agents probing outside their scope.

10. Operations & troubleshooting

Access denied for user 'querymesh'@'localhost' The service DB/user weren't created, or the password doesn't match QM_DB_PASSWORD. Run bootstrap.sql as root, or reset the password:

ALTER USER 'querymesh'@'localhost' IDENTIFIED BY '<your QM_DB_PASSWORD>';
ALTER USER 'querymesh'@'%'         IDENTIFIED BY '<your QM_DB_PASSWORD>';

Agent says a source doesn't exist / "not in your scope" The key's scopes don't cover that path, or project/service/db doesn't match exactly (case-sensitive). Have the agent call list_sources, and check the key's scopes_json in api_keys.

New source doesn't show up Registry changes hot-reload within CONFIG_REFRESH_MS. But a new QM_SECRET_* value in .env needs a process restart to be read.

OAuth connector can't reach the server QM_PUBLIC_URL must be the public HTTPS origin (not localhost), and the connector URL must be https://…/mcp. Restart after changing QM_PUBLIC_URL.

Wrong client IP in audit / rate limiting Behind a reverse proxy, set TRUST_PROXY to the number of proxy hops (default 1), "false" if directly exposed, or a subnet list for CDN+LB chains.

Verify the read-only guarantees

npm test        # adversarial scope + guard tests

These prove writes and out-of-scope paths are refused. Keep them green — and remember the DB credential itself should also be read-only, so a guard bug still can't mutate data.