clickhouse setup

[BilalG1]

Summary by CodeRabbit

• New Features

  • Added an analytics query API with configurable timeouts, query execution timing (CPU and wall‑clock) and explicit analytics error handling.

• Chores

  • Integrated ClickHouse into local/dev infrastructure and CI; added related env vars, startup configuration, migrations runner, and a ClickHouse app tile.

• Tests

  • Added comprehensive end-to-end tests for analytics queries, validations, timeouts, access restrictions, and updated snapshot redactions.

_{✏️ Tip: You can customize this high-level summary in your review settings.}

[vercel[bot]]

The latest updates on your projects. Learn more about Vercel for GitHub.

Project	Deployment	Review	Updated (UTC)
stack-backend	Error		Dec 17, 2025 2:23am
stack-dashboard	Ready	Preview, Comment	Dec 17, 2025 2:23am
stack-demo	Ready	Preview, Comment	Dec 17, 2025 2:23am
stack-docs	Error		Dec 17, 2025 2:23am

[coderabbitai[bot]]

[!NOTE]

Other AI code review bot(s) detected

CodeRabbit has detected other AI code review bot(s) in this pull request and will avoid duplicating their findings in the review comments. This may lead to a less comprehensive review.

Walkthrough

Integrates ClickHouse analytics database into the backend with a new POST API endpoint for executing parameterized queries, Docker infrastructure support, client utilities, error handling, comprehensive tests, and associated configuration.

Changes

Cohort / File(s)	Summary
ClickHouse Configuration & Dependencies apps/backend/.env.development, docker/server/.env.example, apps/backend/package.json	Added ClickHouse environment variables (URL, credentials, database) and installed "@clickhouse/client" ^1.14.0 dependency.
Database Migrations & Initialization apps/backend/scripts/clickhouse-migrations.ts, apps/backend/scripts/db-migrations.ts	Created new ClickHouse migration script that initializes limited_user with privilege grants/revokes; integrated into main migration flow after Prisma.
Analytics Query API Implementation apps/backend/src/app/api/latest/analytics/query/route.tsx, apps/backend/src/lib/clickhouse.tsx	Added POST endpoint for analytics queries with timeout handling, parameterized query execution, and query timing stats retrieval via new ClickHouse client factory utilities.
Error Handling packages/stack-shared/src/known-errors.tsx	Introduced two new error types: AnalyticsQueryTimeout (with configurable timeout_ms) and AnalyticsQueryError (with error details).
Docker Infrastructure docker/dependencies/docker.compose.yaml, .github/workflows/docker-server-build-run.yaml	Added ClickHouse service definition (image 25.10, ports 8123/9000, credentials, persistent volume), and GitHub Actions step to initialize ClickHouse during CI/CD.
Testing & Development apps/e2e/tests/backend/endpoints/api/v1/analytics-query.test.ts, apps/e2e/tests/snapshot-serializer.ts, apps/dev-launchpad/public/index.html	Added comprehensive e2e test suite covering success paths, timeouts, validation, SQL restrictions, and error scenarios; updated snapshot serializer to strip timing fields; added ClickHouse tile to dev launchpad.

Sequence Diagram

sequenceDiagram
    actor Client
    participant API as Analytics Query<br/>Route Handler
    participant CH as ClickHouse Client
    participant System as system.query_log

    Client->>API: POST /api/latest/analytics/query<br/>{query, params, timeout_ms}
    activate API
    
    API->>CH: createClickhouseClient("external")<br/>with timeout
    activate CH
    
    API->>CH: execute(query, params, queryId)
    Note over API,CH: Wraps in Result.fromPromise
    CH-->>API: rows result
    deactivate CH
    
    alt Error: Timeout
        API-->>Client: 400 ANALYTICS_QUERY_TIMEOUT<br/>{timeout_ms}
    else Error: Other
        API-->>Client: 400 ANALYTICS_QUERY_ERROR<br/>{error}
    else Success
        API->>CH: createClickhouseClient("admin")
        activate CH
        
        API->>CH: SYSTEM FLUSH LOGS
        CH->>System: flush logs
        System-->>CH: ack
        
        API->>CH: SELECT cpu_time_ms, wall_clock_time_ms<br/>FROM system.query_log WHERE query_id=?
        CH->>System: fetch timing stats
        System-->>CH: stats
        CH-->>API: {cpu_time_ms, wall_clock_time_ms}
        deactivate CH
        
        API-->>Client: 200 OK<br/>{result, stats}
    end
    
    deactivate API

Estimated code review effort

🎯 4 (Complex) | ⏱️ ~50 minutes

• API route handler logic: Timeout detection, error mapping (AnalyticsQueryTimeout vs. AnalyticsQueryError), and result formatting require careful verification of error conditions and response structure.
• ClickHouse client utilities: Credentials handling, timeout configuration, and timing stats retrieval via system.query_log add domain-specific complexity.
• E2E test coverage: Broad scenario matrix (success paths, timeout handling, query restrictions, DDL/DML prevention, parameter validation) warrants verification of test assertions and SQL restriction enforcement.
• Docker and CI/CD integration: ClickHouse service configuration, port mapping, credential management, and GitHub Actions step placement should be validated for consistency.
• Known errors mapping: Ensure AnalyticsQueryTimeout and AnalyticsQueryError are properly serialized, deserialized, and propagated through the stack.

Suggested reviewers

• N2D4

Poem

Logs flow through ClickHouse with queries so bright, 🐰 Timing stats bloom in the system's soft light, Error bounds caught with a hop and a bound, Analytics queries spinning around! Docker composes a symphony neat, Our e2e tests make this feature complete! ✨

Pre-merge checks and finishing touches

❌ Failed checks (2 warnings, 1 inconclusive)

Check name	Status	Explanation	Resolution
Description check	⚠️ Warning	The PR description contains only a template comment with no substantive information about the changes, objectives, or rationale for the implementation.	Provide a detailed description explaining what ClickHouse integration is being added, why it's needed, and how the analytics query API works. Reference the CONTRIBUTING.md guidelines after adding meaningful content.
Docstring Coverage	⚠️ Warning	Docstring coverage is 0.00% which is insufficient. The required threshold is 80.00%.	You can run @coderabbitai generate docstrings to improve docstring coverage.
Title check	❓ Inconclusive	The title 'clickhouse setup' is partially related to the changeset but lacks specificity and clarity about the main change.	Use a more descriptive title that specifies the primary change, such as 'Add ClickHouse integration and analytics query API endpoint' to better communicate the scope of changes.

✨ Finishing touches

• [ ] 📝 Generate docstrings

🧪 Generate unit tests (beta)

• [ ] Create PR with unit tests
• [ ] Post copyable unit tests in a comment
• [ ] Commit unit tests in branch clickhouse-setup

---

Thanks for using CodeRabbit! It's free for OSS, and your support helps us grow. If you like it, consider giving us a shout-out.

❤️ Share

• X
• Mastodon
• Reddit
• LinkedIn

_{Comment @coderabbitai help to get the list of available commands and usage tips.}

[greptile-apps[bot]]

Greptile Overview

Greptile Summary

This PR establishes the foundational ClickHouse integration for analytics, adding database infrastructure, client library, API endpoint, and comprehensive security controls.

Major Changes:

• Added ClickHouse service to Docker Compose with proper volume management and port configuration
• Implemented two-tier authentication system: admin user for migrations/system operations and limited_user for query execution with restricted permissions
• Created /api/latest/analytics/query endpoint with SmartRouteHandler that enforces server-level auth, parameterized queries, configurable timeouts, and tenancy isolation
• Added migration script that creates limited_user with SELECT-only permissions (references non-existent allowed_table1)
• Implemented query timing stats retrieval using admin privilege escalation to access system.query_log
• Added comprehensive E2E tests covering security boundaries (no CREATE, INSERT, or system table access)

Issues Found:

• Migration grants SELECT on non-existent analytics.allowed_table1, which will cause migration failure
• Missing fallback handling in getQueryTimingStats when query doesn't appear in query_log (will throw runtime error accessing undefined)

Confidence Score: 2/5

• This PR has critical issues that will cause migration failures in production
• Score reflects two critical bugs: the migration references a non-existent table which will fail on execution, and missing error handling in stats retrieval that will cause runtime crashes. The security model and API design are solid, but the implementation has blocking issues.
• Pay close attention to apps/backend/scripts/clickhouse-migrations.ts (references non-existent table) and apps/backend/src/lib/clickhouse.tsx (missing error handling)

Important Files Changed

File Analysis

Filename	Score	Overview
apps/backend/scripts/clickhouse-migrations.ts	2/5	Added ClickHouse migration script with user creation and permissions, but references non-existent table
apps/backend/src/lib/clickhouse.tsx	3/5	Implemented ClickHouse client factory and query timing stats retrieval with admin escalation
apps/backend/src/app/api/latest/analytics/query/route.tsx	4/5	Added analytics query endpoint with proper auth, timeout handling, and error management

Sequence Diagram

sequenceDiagram
    participant Client
    participant AnalyticsAPI
    participant ClickHouse
    participant AdminConnection

    Client->>AnalyticsAPI: POST analytics query
    AnalyticsAPI->>ClickHouse: Execute with limited user
    
    alt Success
        ClickHouse-->>AnalyticsAPI: Result data
        AnalyticsAPI->>AdminConnection: Get timing stats
        AdminConnection-->>AnalyticsAPI: Stats
        AnalyticsAPI-->>Client: Result with stats
    else Timeout or Error
        ClickHouse-->>AnalyticsAPI: Error
        AnalyticsAPI-->>Client: Error response
    end

[cmux-agent[bot]]

Preview Screenshots

Open Workspace (1 hr expiry) · Open Dev Browser (1 hr expiry) · Open Diff Heatmap

Captured 3 screenshots for commit 20b597f (2025-12-08 18:39:32.957 UTC).

Element screenshot of the new ClickHouse service tile at port 8133

Full page screenshot of the Dev Launchpad showing the new ClickHouse and MCPJam Inspector service tiles added in this PR, along with ClickHouse in the Background services list

Element screenshot of the new MCPJam Inspector service tile at port 8126

---

Generated by cmux preview system