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.

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
   1type GetMetricsParams struct {
   2    ProjectID    string
   3    DeploymentID string  // optional
   4    Limit        int
   5}
   6
   7func (q *Queries) GetMetrics(ctx context.Context, arg GetMetricsParams) ([]Metric, error) {
   8    sb := sq.Select("id", "value", "timestamp").
   9        From("metrics").
   10        Where("project_id = ?", arg.ProjectID)
   11
   12    // Optional filters - explicit conditionals for clarity
   13    if arg.DeploymentID != "" {
   14        sb = sb.Where(squirrel.Eq{"deployment_id": arg.DeploymentID})
   15    }
   16
   17    sb = sb.Limit(uint64(arg.Limit))
   18
   19    query, args, err := sb.ToSql()
   20    if err != nil {
   21        return nil, fmt.Errorf("building query: %w", err)
   22    }
   23
   24    rows, err := q.conn.Query(ctx, query, args...)
   25    // ... handle rows
   26}

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.