# hq-demo

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

## Setup

```bash
npm install
```

### 1. ClickHouse credentials

```bash
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:

```bash
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

```bash
npm run seed
```

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

### 3. Run it

```bash
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`):
   ```bash
   npm run generate:types
   ```
2. Scaffold datasets from the real tables, then curate the names:
   ```bash
   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):
   ```bash
   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](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:

```sql
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](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

```bash
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:

```bash
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](.mcp.json) is committed, so all you need is:

```bash
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:

```bash
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:

```json
{
  "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:

```bash
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](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:

```bash
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.