---
name: clickhouse
description: Rules when working with ClickHouse database in Gram for analytics and telemetry features
---

## 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
   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.
