ClickScope

A read-only ClickHouse MCP server that lets an agent explore your schema and cost a query before running it.

Most database MCP servers give a model one blunt instrument: "run this SQL". On a table with billions of rows that is how you get a surprise full scan. ClickScope exposes ClickHouse's own planner instead — EXPLAIN ESTIMATE tells you how many parts, rows and marks a query would read, before a single byte is touched.

Works with any ClickHouse: self-hosted, clustered, local, or Cloud.

Install

As a Claude Code plugin (recommended)

/plugin marketplace add sgr-xd/clickscope
/plugin install clickscope

While the repository is private, use a local checkout instead — /plugin marketplace add /path/to/clickscope — or the standalone route below.

Claude Code then asks for four things:

Field	Example
Server	clickhouse.internal, host:port, or https://abc.clickhouse.cloud
Username	readonly_user
Password	masked; goes to your operating system's secure storage — keychain on macOS, the credential store on Windows — never to a settings file
Database	my_db

Port and TLS come from whatever you put in Server, so there is nothing else to set.

This also installs two skills: clickhouse-query-craft, which teaches the explore → estimate → run discipline the tools are built around, and clickhouse-cluster-health, a one-shot sweep of replication, disks, parts, merges, mutations and errors that reports whether the cluster is healthy right now.

As a standalone MCP server

claude mcp add clickscope --scope user \
  -e CLICKHOUSE_HOST=clickhouse.internal \
  -e CLICKHOUSE_USER=readonly_user \
  -e CLICKHOUSE_PASSWORD_FILE=$HOME/.config/clickscope/password \
  -e CLICKHOUSE_DATABASE=my_db \
  -- uvx clickscope

Works with any MCP client, not only Claude Code — point it at uvx clickscope over stdio.

From PyPI

pip install clickscope    # or: uvx clickscope

From source

git clone https://github.com/sgr-xd/clickscope && cd clickscope
uv venv && uv pip install -e ".[dev]"
pytest -m "not integration"      # unit tests, no database needed
pytest                           # adds live tests against a real server

Configure

Only needed for the standalone and from-source paths; the plugin asks instead.

export CLICKHOUSE_HOST=clickhouse.internal          # or host:port, or a full URL
export CLICKHOUSE_USER=readonly_user
export CLICKHOUSE_PASSWORD_FILE=~/.config/clickscope/password   # preferred
export CLICKHOUSE_DATABASE=my_db

CLICKHOUSE_HOST accepts three forms, so port and TLS usually need no separate setting:

You enter	Host	Port	HTTPS
clickhouse.internal	clickhouse.internal	8123	no
clickhouse.internal:9440	clickhouse.internal	9440	no
https://abc.clickhouse.cloud:8443	abc.clickhouse.cloud	8443	yes
https://abc.clickhouse.cloud	abc.clickhouse.cloud	8443	yes

CLICKHOUSE_PORT and CLICKHOUSE_SECURE still work and take precedence if you set them.

The password is read from CLICKHOUSE_PASSWORD_FILE if set, otherwise CLICKHOUSE_PASSWORD. It is never accepted as a command-line argument — arguments leak through shell history and ps.

Optional, environment only (not offered in the plugin dialog, to keep it to four fields):

Variable	Purpose
CLICKSCOPE_PROFILES	Path to a multi-cluster profiles file — see below
CLICKSCOPE_AUDIT_LOG	Path to a JSONL record of every query decision

Using it

Ask in plain language — the tools are named in the responses, not in your question.

Learn the shape of a table first.

What are the biggest tables in my database?

list_tables returns each table with its sorting_key and partition_key. Those two fields decide everything about cost: a filter matching the leading sorting-key column, or pinning a partition, is the difference between reading a few granules and reading the lot.

Then cost the query before running it.

How many events belong to tenant acme-corp since August 1st?

estimate_query_cost reports what the query would read, without reading it:

verdict=selective   parts=4   rows=4,445 of 43,374   (10.2% of table)

Then it runs. Compare that with a filter that misses the key:

verdict=full_scan   parts=13  rows=43,373 of 43,374  (100.0% of table)
   → Add a filter on the partition key or the leading sorting-key column.

Same table, same row count returned — 10× the work. The verdict arrives before the query runs, which is the entire point: an agent can correct itself instead of discovering the cost afterwards.

When something is expensive, ask why.

Why is that query slow?

explain_plan measures how much the indexes actually pruned, stage by stage:

uses_index=False   granules 110/113 (97.3%) — effectively a full scan
    MinMax      parts 17/17   granules 113/113
    Partition   parts 17/17   granules 113/113
    PrimaryKey  parts 14/17   granules 110/113

Note uses_index comes from the ratio, not from the presence of an Indexes: section — ClickHouse emits that section even when the index removed nothing.

Writes simply are not available.

Drop the events table

Query rejected by ClickScope policy:
  - query must begin with one of SELECT, WITH, SHOW, DESC, DESCRIBE, EXPLAIN; got DROP
  - forbidden keyword: DROP

Rejected before any network call.

What comes back. Every response names the cluster that answered and the SQL actually executed, so a rewritten LIMIT is visible rather than silent:

{"cluster": "prod",
 "executed_sql": "SELECT tenant_id FROM events WHERE tenant_id = 'acme-corp' LIMIT 100",
 "row_count": 100, "rows_read": 9845, "elapsed_ms": 62,
 "policy_notes": ["no LIMIT present; appended LIMIT 100",
                  "result reached the limit of 100; there may be more rows"]}

Tools

Discovery — schema exploration, no user SQL accepted:

Tool	Purpose
list_clusters	Configured clusters, which is default, and each one's limits
list_databases	Databases visible to your user
list_tables	Tables with engine, row count, size, sorting key, partition key
describe_table	Columns, types, codecs, TTL, per-column compression
show_create_table	Full CREATE TABLE DDL
list_partitions	Parts, rows and bytes per partition
profile_column	One column's nulls, distinct count, range and most common values

Query:

Tool	Purpose
run_select_query	Execute a SELECT under enforced caps
sample_rows	Preview rows from a table (SQL built server-side)

Operations — what the server is doing, and whether its background work is keeping up:

Tool	Purpose
list_running_queries	Queries executing now, longest first
search_query_log	Slowest completed queries, or failures grouped by exception
table_storage_stats	Tables ranked by part count and size — where storage pressure is
background_operations	Merges in flight, and mutations that are stuck retrying
replication_status	Read-only replicas, lag and queue backlog

Cost & validation — these read no data:

Tool	Purpose
validate_query	EXPLAIN SYNTAX — is the SQL legal?
estimate_query_cost	EXPLAIN ESTIMATE — parts / rows / marks it would read, as a share of the table
explain_plan	EXPLAIN PLAN indexes=1 — how much the indexes actually pruned

Safety

ClickScope is read-only, enforced in three independent layers:

1. Statement policy — single statement only; must open with SELECT, WITH…SELECT, SHOW, DESCRIBE or EXPLAIN; DDL/DML keywords, INTO OUTFILE and inline SETTINGS overrides are rejected. A missing LIMIT is appended; an oversized one is clamped.
2. Server-enforced caps — every query runs with readonly=1 plus max_execution_time, max_result_rows, max_rows_to_read, max_bytes_to_read, max_memory_usage and max_threads. These bind inside ClickHouse, so they hold even if layer 1 is bypassed.
3. Database grants — connect as a user with only SELECT. This is the layer that cannot be argued with, and the one you should not skip:

CREATE USER readonly_user IDENTIFIED BY '…' SETTINGS readonly = 1;
GRANT SELECT ON my_db.* TO readonly_user;

The policy layer rejects, it does not rewrite — the sole exception being LIMIT injection. Query rewriting that tries to be clever about filters is deployment-specific and fails in ways that are hard to predict, so ClickScope does not do it.

Tuning the caps

Variable	Default
CLICKSCOPE_MAX_EXECUTION_TIME	30 (seconds)
CLICKSCOPE_MAX_RESULT_ROWS	10000
CLICKSCOPE_MAX_ROWS_TO_READ	100000000
CLICKSCOPE_MAX_BYTES_TO_READ	10000000000 (10 GB)
CLICKSCOPE_MAX_MEMORY_USAGE	4000000000 (4 GB)
CLICKSCOPE_MAX_THREADS	4
CLICKSCOPE_DEFAULT_LIMIT	100
CLICKSCOPE_MAX_LIMIT	10000
CLICKSCOPE_AUDIT_LOG	unset — set a path to record every decision as JSONL

Several clusters

Point CLICKSCOPE_PROFILES at a JSON file, and every tool gains an optional cluster argument.

The file holds references, never values — so it is safe to commit, and safe to read over someone's shoulder:

{
  "default": "prod",
  "limits": { "max_limit": 5000 },
  "clusters": {
    "prod": {
      "description": "primary analytics cluster",
      "host":     "${env:PROD_CH_HOST}",
      "port":     "${env:PROD_CH_PORT:-8123}",
      "user":     "${env:PROD_CH_USER:-readonly}",
      "password": "${cmd:vault kv get -field=password secret/ch-prod}",
      "database": "${env:PROD_CH_DB}",
      "limits":   { "default_limit": 25, "max_limit": 200 }
    },
    "staging": {
      "host":     "clickhouse-staging.example.com",
      "password": "${file:~/.config/clickscope/staging.pw}",
      "database": "events"
    }
  }
}

Secret references

Any string may contain ${scheme:argument}, resolved once at startup.

Scheme	Example	Notes
env	${env:PROD_CH_PASSWORD}	${env:VAR:-default} supplies a fallback
file	${file:~/.config/clickscope/prod.pw}	Trailing newline stripped. What Kubernetes and Docker secret mounts give you
cmd	${cmd:vault kv get -field=password secret/ch-prod}	stdout of a command

cmd is deliberately the only secret-manager integration. Depending on a cloud SDK would serve one vendor's users and strand the rest; shelling out to the tool you already run serves everyone:

${cmd:gcloud secrets versions access latest --secret=ch-prod}
${cmd:aws secretsmanager get-secret-value --secret-id ch-prod --query SecretString --output text}
${cmd:az keyvault secret show --vault-name v --name ch-prod --query value -o tsv}
${cmd:op read op://vault/ch-prod/password}
${cmd:pass show clickhouse/prod}

Commands run without a shell — the string is split and executed directly, so a value cannot chain another command with ; or open a subshell. clickscope.secrets.register_scheme is available if you want a native backend instead.

Rules worth knowing:

• A literal password is rejected, with an error pointing at the reference syntax. A warning would not do: people commit past warnings. This is what makes "safe to commit" a property of the format rather than a convention.
• A resolved value is never re-expanded. A secret whose contents happen to look like ${env:OTHER} is data, not a reference — otherwise control of one secret would grant reads of others.
• A profiles file writable by anyone but its owner is refused. A ${cmd:...} reference means the file decides what gets executed, so its integrity matters more than its confidentiality. SSH refuses a private key on the same grounds.
• Host, port, user and database may be literals or references; only password is forced to be a reference.

Limits and routing

Limits layer: environment defaults → file-wide limits → per-cluster limits, so production can be stricter than staging without restating every field.

Connections are lazy — an unreachable cluster does not stop the server starting and does not affect the others; you get a normal error payload naming the cluster.

There is deliberately no use_cluster tool. Selection is an argument on every call, never stored state, and every response carries the cluster that answered it. A mode you can forget you are in is how you read production believing you are on staging.

list_clusters                                    → what exists, and which is default
run_select_query(sql="…")                        → the default cluster
run_select_query(sql="…", cluster="staging")     → explicitly staging

The single-cluster environment variables above keep working unchanged; they simply produce one cluster named default.

Releasing

Changes are recorded in CHANGELOG.md.

Always bump the version when shipping code, documentation and skills included. Not doing so is silent: users keep running the old build, and nothing reports an error to reveal it.

Three files carry the version, and they must agree:

.claude-plugin/plugin.json       "version"
.claude-plugin/marketplace.json  plugins[0].version
pyproject.toml                   version

Then update an installed copy from /plugin in Claude Code:

/plugin  →  ClickScope  →  Update now

That preserves configuration, including the keychain-held password. There is no CLI equivalent — claude plugin has no update subcommand, and install over an existing plugin is a no-op. For a scripted environment the only option is uninstall and reinstall, which erases configuration, so pass the non-sensitive values back:

claude plugin uninstall clickscope@clickscope
claude plugin install clickscope@clickscope --scope user \
  --config CLICKHOUSE_HOST=... --config CLICKHOUSE_USER=... --config CLICKHOUSE_DATABASE=...
# then re-enter the password via /plugin configure

Why each step is there

marketplace update alone does nothing to the installed copy. The plugin cache lives at plugins/cache/<marketplace>/<plugin>/<version>/. With the version unchanged the installer considers the cached copy current, so a successful "update" can leave the old source in place. The version is the cache key.

Uninstalling erases the configuration, which is why Update now is the path to prefer. Uninstall empties pluginConfigs and takes the keychain entry with it, with no warning. An update keeps both.

Directory-sourced installs need their build cache cleared too. When a marketplace points at a local directory, ${CLAUDE_PLUGIN_ROOT} is a path that never changes between versions, so uvx reuses the environment it built the first time — a fix committed hours earlier can still reproduce, and /reload-plugins does not help (it starts a new process against the same stale environment, and leaves the old one running). To confirm what a running server actually imports:

ps -eo pid,command | grep clickscope     # note the archive-v0/<hash>/bin/python path
grep -r "the-fix-you-expect" <that env>/lib/python3.*/site-packages/clickscope/

The fix is uv cache clean clickscope, kill the server processes, then reload. Installs from GitHub are immune, because their plugin root is version-scoped and a bump forces a rebuild.

Before publishing

• pytest — the full suite, including live tests against a real server
• clickscope --version — confirms which build a given install is actually running, which is the quickest way to catch a stale plugin cache
• Claim the name on PyPI if you intend to use uvx clickscope in the docs; an unclaimed name can be taken by anyone once the repository is public

License

Apache-2.0