Clickhouse

When to use this skill

Use Clickhouse when an AI agent needs a reusable SKILL.md workflow for this job: Rules when working with ClickHouse database in Gram for analytics and telemetry features, including editing the ClickHouse schema (server/clickhouse/schema.sql) and creating or fixing ClickHouse migrations

When not to use it

Skip Clickhouse when the task is outside the coding category, or when a more specific skill in this directory already covers the same workflow with clearer triggers.

How to install

1. Personal install: create ~/.claude/skills/clickhouse/SKILL.md (and any bundled scripts) so Claude Code, Claude Desktop, and compatible agents can load it in every project.
2. Project install: commit the same folder at .claude/skills/clickhouse/ so teammates get the skill with the repo.
3. Restart the agent session after copying files so it re-scans the skills directory, then ask for the task in words that match the skill description.

Full install guide for Claude, Cursor, and Codex

What this skill does

## Schema design and evolution

The ClickHouse schema is defined in `server/clickhouse/schema.sql`. To change it, edit that file and generate a migration:

``` mise clickhouse:diff <migration-name> ```

This produces migrations in **two flavors** that must always stay in sync:

- `server/clickhouse/migrations/` — **Atlas** format. This is the source of truth: only these migrations are carried forward and applied in production. - `server/clickhouse/local/golang_migrate/` — **golang-migrate** format (`.up.sql`/`.down.sql` pairs). Used only for local development, so contributors without an Atlas Pro login can still run migrations.

`mise clickhouse:diff` generates both flavors together. If you ever hand-edit a migration (or the diff output), make the equivalent change in **both** directories, then run `mise clickhouse:hash` to regenerate `atlas.sum`. Apply pending migrations locally with `mise clickhouse:migrate`.

**No semicolons in `COMMENT '...'` strings.** golang-migrate splits statements naively, so a semicolon inside a column/table comment breaks its parser and the local migrations fail to replay. Rephrase the comment instead.

Two CI checks guard this on every PR:

- **atlas-lint** — runs the Atlas migration linter, plus a porcelain check that migrations were generated and are up to date with both the Postgres and ClickHouse schemas. If you edited `schema.sql` without running `mise clickhouse:diff`, this fails. - **golang-migrate-clickhouse** — replays all golang-migrate migrations against a real ClickHouse instance to prove they're valid. If the two flavors drifted, this is where it shows up.

## ClickHouse Queries (Telemetry Package)

The `server/internal/telemetry` package uses ClickHouse for high-performance analytics queries. Unlike PostgreSQL queries, **ClickHouse queries are NOT auto-generated by SQLc** (SQLc doesn't support ClickHouse). We use the **squirrel** query builder for dynamic query construction.

> **CRITICAL:** The squirrel query builder is ONLY permitted for ClickHouse queries in the telemetry package. **DO NOT use squirrel for PostgreSQL queries** - all PostgreSQL queries MUST use SQLc-generated code. This is the only acceptable exception to our "no query builders" policy.

### Read from summary views, not raw `telemetry_logs`

The pre-aggregated **materialized views are the default read path** for anything that powers a dashboard, analytics surface, or filter control: `trace_summaries`, `metrics_summaries`, `attribute_metrics_summaries`, `attribute_keys`, `chat_token_summaries`. Query the **raw `telemetry_logs`** table **only** in the rare cases where per-log detail is genuinely required:

- individual log records / a single trace's full log list (detail/inspection views), - free-text search over the log `body`, - arbitrary user-supplied attribute-path filters (e.g. `@user.region`) that a fixed-column summary cannot express, - an event shape not yet captured by any summary (e.g. traceless trigger events).

**Why it matters:** raw `telemetry_logs` reads are full-range table scans with per-row JSON extraction; the summary views are pre-aggregated and cheap. A filter dropdown, summary card, count, or default list that scans raw logs on every page load is a **performance bug** — the unified Tool Logs page regressed exactly this way before being moved back onto `trace_summaries`. If a summary is missing a column you need, prefer **extending the MV (+ a backfill migration)** over falling back to a raw-log scan.

**Reading `trace_summaries`** (one row per `trace_id`, `AggregatingMergeTree`): `GROUP BY trace_id`, use `*Merge` combinators for `AggregateFunction` columns (`anyIfMerge(http_status_code)`) and plain `any()`/`min()`/`sum()`/`max()` for `SimpleAggregateFunction` columns. Tool calls carry a real `trace_id` (recorded by the gateway in `ToolProxy.Do`), so hosted MCP, shadow MCP, skill, and local tool events are all present in the view.

> **Gotcha — `ILLEGAL_AGGREGATION` (code 184):** when a grouped read is wrapped in a CTE/subquery that a _caller_ then aggregates over (e.g. `uniqExact(tool_name)` over a `WITH normalized_events AS (... GROUP BY trace_id ...)`), ClickHouse merges the subquery back into the outer aggregate **if an aggregate alias shadows a base column** (`any(gram_urn) AS gram_urn`). Alias grouped aggregates to non-colliding names — prefix them, e.g. `any(gram_urn) AS g_gram_urn` — so the boundary holds.

### File Structure

- **`queries.sql.go`**: Query implementations using squirrel - **`pagination.go`**: Cursor pagination helpers (`withPagination`, `withOrdering`, etc.) - **`README.md`**: Detailed documentation and patterns specific to ClickHouse

### Adding ClickHouse Queries

When asked to add a new ClickHouse query to the telemetry package:

1. **Create a params struct** for query inputs 2. **Build the query using squirrel** (the `sq` var in queries.sql.go is pre-configured for ClickHouse):

```go type GetMetricsParams struct { ProjectID string DeploymentID string // optional Limit int }

func (q *Queries) GetMetrics(ctx context.Context, arg GetMetricsParams) ([]Metric, error) { sb := sq.Select("id", "value", "timestamp"). From("metrics"). Where("project_id = ?", arg.ProjectID)

// Optional filters - explicit conditionals for clarity if arg.DeploymentID != "" { sb = sb.Where(squirrel.Eq{"deployment_id": arg.DeploymentID}) }

sb = sb.Limit(uint64(arg.Limit))

query, args, err := sb.ToSql() if err != nil { return nil, fmt.Errorf("building query: %w", err) }

rows, err := q.conn.Query(ctx, query, args...) // ... handle rows } ```

3. **Use pagination helpers** from `pagination.go`: - `withPagination(sb, cursor, sortOrder)` - cursor pagination - `withOrdering(sb, sortOrder, primaryCol, secondaryCol)` - ORDER BY

### Testing ClickHouse Queries

- Use `testenv.Launch()` in `TestMain` for infrastructure setup - Add `time.Sleep(100 * time.Millisecond)` after inserts (ClickHouse eventual consistency) - Use table-driven tests with descriptive "it" prefix names - Create helper functions for test data insertion

See `server/internal/telemetry/README.md` for comprehensive documentation.

Intended uses

• Use Clickhouse when this documented workflow matches the task.