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 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?
mkdir -p ~/.claude/skills/clickhouse # Save SKILL.md to ~/.claude/skills/clickhouse/SKILL.md
- 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.
- 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.
- 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.
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.