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

File	What it shows
analytics/schema.ts	Generated table → column → ClickHouse type map. Regenerate with npm run generate:types.
analytics/client.ts	createQueryBuilder<IntrospectedSchema> + createDatasetClient
analytics/datasets.ts	Two datasets (orders, events) with typed dimensions and measures
analytics/metrics.ts	Base metrics and a derived metric (revenue / orderCount)
analytics/catalog.ts	The agent-safe catalog — what an LLM reads
analytics/api.ts	initServe — one hand-written query plus generated metric/dataset endpoints
app/api/analytics/[...path]/route.ts	Mounts the API on the App Router
lib/analytics.ts	createAnalyticsHooks → useQuery / useMetric / useDataset
app/page.tsx	Server component: builder, dataset, metric and in-process query results
app/client-demo.tsx	Client component: the same data via hooks
scripts/seed.ts	Creates + fills the two demo tables

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

Script	Does
npm run dev	Next dev server
npm run build	Production build
npm run typecheck	tsc --noEmit
npm run seed	Create + fill the demo tables
npm run generate:types	Schema types from live ClickHouse
npm run generate:datasets	Scaffold dataset definitions from live ClickHouse
npm run generate:manifest	Rebuild the React route manifest
npm run hq:dev	hypequery dev server with docs + OpenAPI at /api/analytics/docs

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:

Where	Example	Who it binds
On a measure	measure.sum('amount', { filters: [eq('status','completed')] })	Everyone. Baked into the definition, so "completed revenue" means one thing everywhere. Compiles to SUM(if(…)).
On a query	useMetric('revenue', { filters: [{ field:'status', operator:'eq', value:'completed' }] })	This call only. Compiles to WHERE.
On the dataset	filters: { status: { __type:'filter_definition', field:'status', operators:['eq','neq','in'] } }	Governs what a query filter is allowed to ask for.

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:

Request	Result
status eq completed	200
country in ['US','GB']	200
customerId eq cust_1 (field not in the allow-list)	400 VALIDATION_ERROR
status like 'comp%' (operator not in the allow-list)	400 VALIDATION_ERROR

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:

Surface	Carries descriptions
GET /api/analytics/contract	Yes, including per-metric metadata
MCP get_dataset_schema / list_datasets	Yes
GET /api/analytics/openapi.json	No — field names only
GET /api/analytics/docs	No

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:

Command	First stdout line
npm run mcp	"> hq-demo@0.1.0 mcp"
npm run --silent mcp	{"result":{"protocolVersion":…

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.