Skip to main content
Agent skill

Clickhouse

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

Get started

Get the complete skill folder, including its scripts and reference files.

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

Where will you use it?

Install scope
Claude Code folder setup
mkdir -p ~/.claude/skills/clickhouse
# Save SKILL.md to ~/.claude/skills/clickhouse/SKILL.md
  1. Claude Code personal install: place the skill and its bundled files at ~/.claude/skills/clickhouse/SKILL.md. For a shared project, commit the folder at .claude/skills/clickhouse/ instead.
  2. Current Claude Code detects SKILL.md changes in watched directories during the session. If you just created a top-level skills directory, run /reload-skills. Check that the skill is listed before testing its trigger.
  3. For Claude chat or Cowork in the Desktop app, manage uploaded skills in Customize > Skills; copying files into a local Claude Code folder is not a Desktop install. Other agents may use different install locations.

Full install guide for Claude, Cursor, and Codex →

Try a starter prompt

After installing the skill, paste this starter prompt into your agent to verify it triggers on a small task.

Starter prompt
Use the Clickhouse skill for this task: Use Clickhouse when this documented workflow matches the task. Confirm any required inputs or files before making changes.

What supports this recommendation?

70/100 provisional content score. This measures documentation, practical detail and source signals. It is not a measured task success rate or a security certification.

Documentation
77/100
Practical detail
68/100
Source evidence
62/100
Source signals
70/100
Model compatibility evidence
  • claudeuntested

    No model-specific signal or recorded compatibility test was found.

  • chatgptuntested

    No model-specific signal or recorded compatibility test was found.

  • geminiuntested

    No model-specific signal or recorded compatibility test was found.

  • copilotuntested

    No model-specific signal or recorded compatibility test was found.

  • llamauntested

    No model-specific signal or recorded compatibility test was found.

  • perplexityuntested

    No model-specific signal or recorded compatibility test was found.

  • mistraluntested

    No model-specific signal or recorded compatibility test was found.

  • grokuntested

    No model-specific signal or recorded compatibility test was found.

Inferred compatibility is based on content signals. Check the app, its available tools and setup requirements before running a skill.

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.

Implementation notes

  • Review the source instructions and adapt inputs before running the workflow.

Try it, then tell us how it went

A copied prompt does not tell us whether the task worked. Report your result after checking the output. We collect your tool and outcome, never your files or prompt.