clickhouse

name	clickhouse
description	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

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):

   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.