/clickhouse-autoresearch-campaign

name: clickhouse-autoresearch-campaign
description: Run a ClickHouse query optimization campaign on one git branch using pi-autoresearch, dynamic lanes and hypotheses, baseline result capture, correctness checks, and stagnation-aware lane/campaign review.

ClickHouse Autoresearch Campaign

This skill packages the orchestration for optimizing one ClickHouse query on one git branch.

Required reads

Before taking action, read `orchestration.md` (sibling of this file) completely. Treat it as the operating contract.

Preconditions

This skill assumes:

• `pi-autoresearch` is installed and its tools are available
• the current directory is a git repository
• you have a target query or enough context to identify one
• the operator will provide or help configure `.clickhouse-autoresearch/adapter.json`

Branch rule

One campaign = one git branch.

If the current branch is not a dedicated campaign branch yet, create one before initializing the workspace.

Workspace rule

Use a single workspace at:

.clickhouse-autoresearch/

The branch is the campaign boundary. The workspace is just the artifact layout.

If `autoresearch.config.json` exists in the current working directory, read its `workingDir` field and use that path as the workspace instead of the default above. Automated orchestrators (for example PostHog's `run_campaign.py`) initialize the workspace at `/tmp/autoresearch-campaign/` and write the config alongside it.

Pre-initialized workspace detection

**Before doing anything in the Setup sequence, check whether the workspace has already been prepared by an external orchestrator.** If the resolved workspace contains **all** of:

• `adapter.json`
• `baseline/metrics.json`
• `query/original.sql`

…then the workspace is pre-initialized. In that case:

• **Skip the entire Setup sequence (steps 1–6).** Do not ask the operator for a target query, connection details, or anything else — the orchestrator has already supplied them.
• Jump directly to step 7 of the Setup sequence (read the baseline and seed the first lanes and hypotheses), then continue with steps 8–9 and the normal campaign loop.
• Operate headlessly: at no point prompt the operator for input. If a decision requires judgment, apply the skill's default guidance and record the choice in `state.json` / `autoresearch.md`.

Only fall back to the interactive Setup sequence below when the workspace is empty or partially initialized.

Adapter capabilities (what you can and cannot run)

Campaign queries flow through the adapter configured in `adapter.json`. Every campaign script (`ch_capture_baseline.py`, `ch_run_candidate.py`, any ad-hoc probe) ultimately submits SQL through this adapter, and the adapter enforces what ClickHouse sees.

When `adapter.json` has `type: "coordinator"`, your SQL is routed to whichever ClickHouse the host-side coordinator is pointed at — typically a read-only test cluster or a local dev ClickHouse. Either way the cluster runs SQL under a profile that pins `readonly = 2`, so writes (INSERT, ALTER, CREATE, OPTIMIZE, SYSTEM, TRUNCATE, DROP, ATTACH, DETACH) will fail with a ClickHouse error. **Read `GET /v1/info` (or check `autoresearch.md` — the coordinator's prompt addendum is prepended there)** before issuing any predicate that depends on a specific `team_id`: the prompt addendum tells you which `team_id` the target cluster has data for, and you must rewrite team_id predicates to match it. For experiments treat every read-only statement form as available:

• `SELECT …` — arbitrary subqueries, CTEs, joins
• `WITH … SELECT …`
• `EXPLAIN …` — every variant ClickHouse supports. Use them before proposing rewrites:
• `EXPLAIN SELECT …` (default: PLAN)
• `EXPLAIN AST SELECT …`
• `EXPLAIN SYNTAX SELECT …`
• `EXPLAIN QUERY TREE SELECT …` (post-analyzer logical tree; invaluable on modern ClickHouse)
• `EXPLAIN PIPELINE SELECT …` — processor-level pipeline, headers, expressions
• `EXPLAIN ESTIMATE SELECT …` — per-part row/mark estimates before execution
• `EXPLAIN PLAN indexes = 1, actions = 1, json = 1 SELECT …` — primary-key and skip-index use, JSON for machine parsing
• `EXPLAIN TABLE OVERRIDE …`
• `SHOW …` — `SHOW CREATE TABLE events`, `SHOW COLUMNS FROM events`, `SHOW INDEX FROM events`, `SHOW SETTINGS ILIKE '%mark_cache%'`, etc.
• `DESCRIBE` / `DESC …`

**Timeout**: every submission is wrapped with `SETTINGS max_execution_time = 60`. Keep ad-hoc probes short. If the target query itself routinely exceeds 60s, use range narrowing (see Setup step 6) and only then start the campaign.

**Cluster scoping**: depends on the coordinator's `target`. The prompt addendum prepended to `autoresearch.md` tells you which `team_id` the target cluster has data for and asks you to rewrite team_id predicates accordingly. Read the addendum first.

**Profiling ClickHouse's perspective**:

After a run, the campaign scripts capture client-side `elapsed_ms`, `rows_read`, `bytes_read`, and the server-minted `query_id` from the proxy response (persisted as `query_id` in `runs/run-XXXX-*/metrics.json` and `baseline/metrics.json`). The `autoresearch` CH user can read four `system.*` profiling tables for its own queries (RESTRICTIVE row policies hide every other user's rows):

| Table | What's in it | | ------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------- | | `system.query_log` | One row per finished query: `query_duration_ms`, `read_rows`, `read_bytes`, `memory_usage`, `ProfileEvents` (map of low-level counters). | | `system.query_thread_log` | Per-thread breakdown of the query: CPU time, wait time, peak memory per thread. | | `system.text_log` | Server-side log lines tag