hq-demo

A minimal Next.js (App Router) app showing hypequery end to end: a typed query builder, a semantic layer (datasets + metrics), a validated HTTP API, and fully typed React hooks calling it.

There is deliberately no dashboard UI — every result is logged (server console, browser console) and dumped as JSON on the page.

What's in here

Related MCP server: ClickHouse MCP Agent

Setup

npm install

1. ClickHouse credentials

cp .env.example .env.local

Fill in:

CLICKHOUSE_URL=https://your-instance.clickhouse.cloud:8443
CLICKHOUSE_DATABASE=default
CLICKHOUSE_USERNAME=default
CLICKHOUSE_PASSWORD=...

No ClickHouse to hand? A throwaway local one works:

docker run -d --rm --name hq-demo-ch -p 8123:8123 clickhouse/clickhouse-server:latest

…then use CLICKHOUSE_URL=http://localhost:8123, user default, empty password.

2. Seed the demo tables

npm run seed

Creates orders (2,000 rows) and events (5,000 rows).

3. Run it

npm run dev

Open http://localhost:3000. Server results are printed in the terminal running the dev server; hook results are printed in the browser console. Both are also rendered as JSON on the page.

Pointing it at your own tables

1. Regenerate schema types from your live database (writes analytics/schema.ts):

   npm run generate:types

2. Scaffold datasets from the real tables, then curate the names:

   npm run generate:datasets

3. Update analytics/metrics.ts and analytics/api.ts to match.

4. Regenerate the React route manifest (the hooks read it to resolve URLs):

   npm run generate:manifest

Step 4 matters: lib/analytics.ts imports analytics/hypequery-manifest.json rather than the serve API itself, so no server code — and no ClickHouse driver — ends up in the browser bundle. Wire it into prebuild if you don't want to remember it.

Where the types come from

Nothing in this repo hand-writes a result interface.

• db.table('orders').select(['country']) — columns checked against IntrospectedSchema, row type inferred.

• analytics.execute(Orders, { measures: ['revenue'] }) — checked against the dataset definition.

• useDataset('events', { measures: ['revenue'] }) — compile error: events has eventCount and users, not revenue.

• useMetric('recentOrders') — compile error: that's a query, not a metric.

Run npm run typecheck after breaking one of them on purpose.

Scripts

Filters: three different things

Metric definitions take no filters — Orders.metric() accepts only measure, label, description and metadata. Filtering happens in three other places, and they do different jobs:

The dataset filters block is an allow-list, not a set of presets. It gives you no named shorthand — you still pass { field, operator, value } at the call site. What it does is restrict the vocabulary.

It also flips the default. Omit it and every dimension with filterable !== false is filterable with any operator. Declare it and only the listed fields and operators are accepted. In analytics/datasets.ts customerId is deliberately excluded: an agent can group by customer, but cannot probe for a specific one.

Verified against the running API:

Query filters work on derived metrics too, pushed into the CTE rather than applied after aggregation:

WITH base AS (
  SELECT country, SUM(if((status='refunded'), amount, 0)) AS refundedRevenue, …
  FROM orders WHERE channel = ? GROUP BY country
)
SELECT country, (refundedRevenue) / (NULLIF(completedRevenue, 0)) AS refundRate FROM base

Caveat — the allow-list is runtime-only. Dimensions and measures are narrowed in the hook types, but filter fields and operators are not: DatasetInstance has no TFilters type parameter, so the declaration is erased before the types are built. useMetric('revenue', { filters: [{ field: 'customerId', … }] }) compiles cleanly and fails with a 400 on the wire.

Metadata for AI

Every dataset, dimension, measure and metric carries description plus examples, synonyms, unit, currency, sensitivity, timezone and freshness. This is what an agent reads to pick the right field, so the text is written for someone who has never seen the table — including the traps (revenue is gross, customers is non-additive across time buckets).

Validation is enforced at definition time: currency must be a three-letter uppercase code, sensitivity is one of public / internal / confidential / restricted, examples and synonyms must be duplicate-free, and each string is capped at 4 KiB. A bad value throws when the dataset is defined, not on first query. Note that examples and synonyms are deduplicated and sorted, so don't encode meaning in their order.

Where it surfaces, measured on this project:

See analytics/catalog.ts to print exactly what an agent would see. It also documents the one sharp edge: metrics must be attached to the catalog source explicitly ({ ...Orders, metrics: { ... } }) or they are dropped silently. serve() handles this for you; MCP does not.

Explore the API without the app

npm run hq:dev

Serves the whole API on port 4000 with docs and an OpenAPI document, no Next.js involved:

• docs UI — http://localhost:4000/api/analytics/docs

• OpenAPI — http://localhost:4000/api/analytics/openapi.json

• full semantic contract — http://localhost:4000/api/analytics/contract

It registers 9 routes: the 4 metrics, 2 dataset query endpoints, recentOrders (both its auto-registered /queries/recentOrders path and the explicit /recent-orders one), and /contract. It watches for changes, so editing a dataset re-registers immediately.

MCP — exposing this to an AI agent

The same analytics/api.ts becomes an MCP server with no extra code:

npm install @hypequery/mcp
npm run mcp:check

mcp:check runs --self-test: it loads the entrypoint, reports the datasets, and exits without speaking MCP.

There is no localhost port to open. @hypequery/mcp speaks stdio only — the MCP client spawns npm run mcp as a child process and talks to it over the pipe. Running it in a terminal yourself just leaves a process waiting for JSON-RPC on stdin. (createMCPProtocolServer is transport-neutral if you want to wire an HTTP transport from the MCP SDK yourself, but nothing ships one.)

Claude Code

Already wired up — .mcp.json is committed, so all you need is:

cd /path/to/hq-demo && claude

Claude Code asks once whether to trust the project's MCP server; approve it, then /mcp shows hq-demo connected. (claude mcp list will report ⏸ Pending approval until you have done this — project-scoped servers are never auto-trusted, since a .mcp.json can arrive with a clone.)

If you would rather register it yourself, or want it outside this directory:

claude mcp add hq-demo --scope local -- npx hypequery mcp analytics/api.ts

Then ask: "what datasets do you have, and what was net revenue by country?" It will call list_datasets, then get_dataset_schema, then query_metric.

No credentials in the MCP config. The hypequery CLI loads .env.local itself, so .mcp.json carries an empty env block and stays committable. Verified by running the server with every CLICKHOUSE_* variable stripped from its environment — query_metric still returned rows.

Claude Desktop, or any client reading JSON config

Claude Desktop does not run from this directory, so point it here explicitly:

{
  "mcpServers": {
    "hq-demo": {
      "command": "npm",
      "args": ["--prefix", "/absolute/path/to/hq-demo", "run", "--silent", "mcp"]
    }
  }
}

--silent is not optional. Without it npm run prints > hq-demo@0.1.0 mcp to stdout, which is the JSON-RPC channel. Measured — first stdout line:

Lenient clients skip the junk line; a strict one fails the handshake. This will bite any npm-script-wrapped MCP server, not just this one.

Seeing it in a browser

If you want a clickable UI rather than a chat client, the official MCP Inspector spawns the same stdio command and gives you a local web page to drive it:

npx @modelcontextprotocol/inspector npx hypequery mcp analytics/api.ts

It prints a http://localhost:6274 URL. That port belongs to the Inspector, not to hypequery — the hypequery process underneath is still stdio.

Tools

Four tools are exposed: list_datasets, get_dataset_schema, query_metric, query_dataset. No raw SQL tool — an agent can only ask for dimensions and measures the datasets declare, so a prompt-injected "drop table" has nothing to call. get_dataset_schema returns the descriptions and synonyms from analytics/datasets.ts, which is how the agent picks the right field.

For a tenant-scoped deployment, pin the tenant at the process boundary so the agent cannot choose it:

hypequery mcp analytics/api.ts --tenant acme

Not covered here

• Multi-tenancy. Add tenantKey: 'tenant_id' to a dataset and it fails closed until you supply runtime.tenant. Over HTTP that means configuring tenant extraction on initServe. The seeded tables already carry a tenant_id column if you want to try it.

• Relationships / joins between datasets (belongsTo, hasMany).

• MCP. @hypequery/mcp exposes these same datasets to agents as bounded tools rather than raw SQL.