Discussion: Enrich chDB support in mcp-clickhouse (introspection + federated catalog + DataFrame query)

## Summary

mcp-clickhouse (0.4.0) ships **minimal chDB support**: a single entry point `run_chdb_select_query` plus the `chdb_initial_prompt`, gated by `CHDB_ENABLED=true`. There are no chDB introspection tools, no federated source catalog, and no result-size / file-safety controls on the chDB side — while the ClickHouse-server side already has `list_databases` / `list_tables` / `run_query`.

This issue opens a discussion on **enriching chDB's tool surface** in mcp-clickhouse, in a way that leaves both-mode behavior frozen and identical to 0.4.0. Full design is attached at the bottom.

## Why enrich chDB in mcp-clickhouse

1. **chDB's agent experience is far behind the ClickHouse-server side.** On the server side an agent can discover databases/tables, inspect schemas, and sample data (`list_databases` / `list_tables` / `run_query`). On the chDB side it can only blind-fire a single `run_chdb_select_query` — no way to introspect what's queryable. For an embedded, zero-infrastructure ClickHouse-SQL engine this is the most natural thing to fix first.

2. **chDB is the universal data-access tool the MCP ecosystem is missing.** A single chDB statement federates across S3, Postgres, MySQL, MongoDB, remote ClickHouse, Iceberg/Delta and local files via ClickHouse table functions — no server, no network setup. Today that power is reachable only by hand-writing each source's table function inline, which also **inlines credentials into the SQL** (visible to the model, in tool arguments, and in logs — the very pattern `chdb_initial_prompt` still teaches). A source catalog turns those into ordinary named tables with server-side secrets.

3. **There is no safety baseline on the chDB path.** No `readonly` enforcement, no result byte cap, no file allowlist, no table-function scanning. Before exposing richer tools we need a chDB session security baseline (`SET readonly=2`, result truncation, `max_execution_time`, optional file allowlist).

4. **Zero blast radius for existing users.** All new tools register **only in chDB-only mode** (`CLICKHOUSE_ENABLED=false`, `CHDB_ENABLED=true`). In both mode nothing changes — `run_query` and `run_chdb_select_query` keep their exact 0.4.0 behavior. New code lives in self-contained modules (`chdb_tools.py` / `chdb_safety.py`) with no dependency from the ClickHouse path.

5. **The highest-leverage emerging deployment for a chDB-backed MCP server is serverless / in-process — and it needs exactly these tools.** A fast-growing pattern runs the agent, the MCP server, and chDB **in one serverless process**, one isolated microVM per request or per tenant (AWS Lambda, Firecracker microVMs, Bedrock AgentCore). It is compelling because:
- Agents make many tool calls per turn, and every network database hop compounds latency. An in-process engine collapses federated SQL, vector search, and cross-source JOINs to CPU speed — zero data-plane network round-trips.
- Each tenant/request gets its own microVM and chDB Session: hardware-enforced isolation, no shared database to scale or overload.
- Concrete agent scenarios fit directly: customer-360 (join CRM + S3 event data + remote ClickHouse in one query), real-time fraud / surge-pricing scoring, multi-tenant SaaS analytics.

This is precisely where the richer chDB surface pays off: **introspection** so the agent can discover what's queryable, a **federated source catalog** so credentials stay server-side instead of inlined into SQL, and the co-located **`run_chdb_dataframe_query`** for zero-copy querying of in-process DataFrames. The minimal 0.4.0 surface — a single blind query tool — cannot support this class of MCP server. (The design's *Deployment Topology* section details this co-located single-process model.)

## What's proposed (at a glance)

- **Introspection (P1):** `list_databases` / `list_tables` / `describe_table` / `get_sample_data` / `list_functions` — bare canonical names (collision-free since the CH-server tools aren't registered in chDB-only mode), plus the chDB-session security baseline.
- **Federated source catalog (P2):** a named-collection catalog (`CHDB_SOURCES_CONFIG`) + `attach_file` so federated sources appear as ordinary named tables, with credentials kept server-side.
- **DataFrame query (P3):** `run_chdb_dataframe_query`, registered only in co-located/embedded deployments (`CHDB_DATAFRAME_QUERY=true`), aligned with `chdb.datastore`.

A dedicated remote-ClickHouse tool is intentionally **deferred** (both mode + L2 catalog already cover the mainstream); see §9 of the design.

## Goals of this discussion

- Validate the **chDB-only mode** boundary as the place to add these tools (both mode frozen).
- Get feedback on the **naming** (bare canonical introspection names vs `chdb_`-prefixed) and the **source-catalog config shape**.
- Agree on the **security baseline** before any richer tool ships.

---

Full design — chDB in mcp-clickhouse

## Summary

This design extends `mcp-clickhouse` chDB support from a single query entry point into a richer chDB-only tool surface: introspection, governed source access, and optional DataFrame querying.

The existing behavior remains frozen in both-engine mode. New APIs are registered only when `CLICKHOUSE_ENABLED=false` and `CHDB_ENABLED=true`.

## Background

`mcp-clickhouse` 0.4.0 ships minimal chDB support:

- `run_chdb_select_query`
- `chdb_initial_prompt`
- gated by `CHDB_ENABLED=true`

Missing today:

- chDB introspection tools
- source catalog
- result-size controls on the chDB side
- file-safety controls on the chDB side

ClickHouse-server tools such as `list_databases`, `list_tables`, and `run_query` target only the ClickHouse server and are not available to chDB.

## Baseline

### Core Value

1. Zero-infrastructure ClickHouse SQL entry point

Install and use; no server and no data-plane network dependency.

2. Universal data-access tool for MCP

One governed SQL statement can federate across many sources.

### Mode Registration

| Mode | Env | Registered tools |
| --- | --- | --- |
| both | `CLICKHOUSE_ENABLED=true` / `CHDB_ENABLED=true` | `run_query` / `run_chdb_select_query` |
| chDB-only | `CLICKHOUSE_ENABLED=false` / `CHDB_ENABLED=true` | `run_chdb_select_query` + new APIs in this design |

Both mode is unchanged from 0.4.0: no new tools are registered.

### Naming Rules in chDB-only Mode

- Query tool remains `run_chdb_select_query`.
- New introspection APIs use bare canonical names:
- `list_databases`
- `list_tables`
- `describe_table`
- `get_sample_data`
- `list_functions`
- This is collision-free because ClickHouse-server tools are not registered when `CLICKHOUSE_ENABLED=false`.
- DataFrame tool is `run_chdb_dataframe_query`.
- `run_query` is never present in chDB-only mode.

### Security Baseline

| Control | Purpose |
| --- | --- |
| `SET readonly=2` | Reject persistent-table writes while keeping table functions usable |
| result byte cap | Bound response size from the engine side |
| Python `truncate()` | Final guardrail on serialized output size |
| `max_execution_time` | Prevent unbounded queries |
| optional file allowlist | Gate file access through `attach_file` and table-function scanning |
| dynamic table-function scanner | Detect unsafe file-like source access |
| `quote_ident` / `quote_string` | Avoid unsafe SQL string construction |

### Decoupling

New self-contained modules:

- `chdb_tools.py`
- chDB-only APIs
- source access
- `chdb_safety.py`
- pure helper functions

ClickHouse server tool paths and both-mode behavior remain untouched.

## Tool Surface

### Query Tools

| Tool | Signature | Status |
| --- | --- | --- |
| `run_chdb_select_query` | `(query, format="JSON")` | existing; unchanged |
| `run_chdb_dataframe_query` | `(query, df_ref)` | new; co-located only |

### Introspection Tools

| Tool | Signature | Naming |
| --- | --- | --- |
| `list_databases` | `()` | bare canonical |
| `list_tables` | `(database)` | bare canonical |
| `describe_table` | `(database, table)` | bare canonical |
| `get_sample_data` | `(database, table, limit=10)` | bare canonical |
| `list_functions` | `(pattern=None)` | bare canonical |

### Source Catalog Tool

| Tool | Signature | Purpose |
| --- | --- | --- |
| `attach_file` | `(name, path, format=None)` | Add a local file to the runtime catalog |

In both mode, none of the new rows are registered. Only `run_query` and `run_chdb_select_query` exist, matching 0.4.0.

Vector similarity retrieval remains plain SQL through `run_chdb_select_query`, for example `cosineDistance` or `L2Distance` with `ORDER BY ... LIMIT`.

## Source Access

All federation flows through `run_chdb_select_query`.

### Access Layers

| Layer | Scenario | Form |
| --- | --- | --- |
| L1 | public or local ad-hoc access | inline table function inside `run_chdb_select_query` |
| L2 | credentialed, production, or session-scoped access | named-collection catalog plus `attach_file` runtime catalog |

### L1: Inline Federation

A single chDB statement can federate across sources using ClickHouse table functions.

```python
run_chdb_select_query(query="""
SELECT u.tier, avg(t.amount)
FROM s3('s3://lake/txn.parquet','Parquet') AS t
JOIN postgresql('rds:5432','app','users','ro','pwd') AS u USING(uid)
GROUP BY u.tier
""")
```

### L2: Source Catalog and Runtime Attach

L2 exposes federated sources as named tables or databases. The operator configures sources once; agents then query plain table names.

#### Why L2 Exists

L1 works, but it has two costs:

- The agent hand-writes every table function.
- Credentials can be inlined in SQL, tool arguments, and logs.

L2 removes both by using:

- Named tables and databases such as `lake` or `appdb.users`.
- Server-side credentials through named collections and `CHDB_CRED_*` environment variables.

#### Config File

`CHDB_SOURCES_CONFIG` points to a JSON file on the server filesystem.

```json
{
"sources": [
{
"name": "lake",
"address": "s3://lake/events/*.parquet",
"credential": "aws_lake"
},
{
"name": "sales",
"address": "/data/sales.parquet"
},
{
"name": "appdb",
"address": "postgres://rds:5432/app",
"credential": "pg_ro"
},
{
"name": "users",
"address": "postgres://rds:5432/app/users",
"credential": "pg_ro"
}
]
}
```

#### Credential Environment Variables

Secrets are keyed by credential name and never stored in the config file.

```bash
CHDB_CRED_AWS_LAKE_KEY=...
CHDB_CRED_AWS_LAKE_SECRET=...

CHDB_CRED_PG_RO_USER=ro
CHDB_CRED_PG_RO_PASSWORD=...
```

#### Agent Query Example

```python
run_chdb_select_query(
query="SELECT u.tier, sum(e.amount) FROM lake e JOIN appdb.users u ON u.uid=e.uid GROUP BY u.tier"
)
```

#### Runtime `attach_file`

```python
attach_file(name="report", path="/data/report.parquet")
run_chdb_select_query(query="SELECT * FROM report LIMIT 10")
```

Runtime catalog lifecycle is the main hardening item:

- per-session isolation
- eviction
- restart behavior

#### Discovery

Catalog sources are ordinary databases and tables, discoverable through:

- `list_databases()`
- `list_tables(database)`
- `describe_table(database, table)`

### Internal Statements

During privileged initialization, before `SET readonly=2`, the server emits ClickHouse-native statements.

```sql
CREATE NAMED COLLECTION aws_lake AS
url='...',
access_key_id='...',
secret_access_key='...',
format='Parquet';

CREATE VIEW lake AS
SELECT * FROM s3(aws_lake);

CREATE DATABASE appdb
ENGINE = PostgreSQL(pg_app);
```

### Remote ClickHouse Path Selection

Multi-source access is already covered by existing paths, so a dedicated `run_chdb_query_remote_clickhouse` tool is not part of the mainstream design right now.

#### Recommended Paths

| Need | Use |
| --- | --- |
| Query any table on a ClickHouse cluster, chosen dynamically at call time | both mode: `run_query` — native connection, credentials in `CLICKHOUSE_*` env, `list_tables` discovery, server-side execution |
| Join specific known remote ClickHouse tables with files, S3, or other sources | L2 catalog — declare the remote tables as named tables, then query them through `run_chdb_select_query` |
| Join a dynamically chosen remote ClickHouse table with local or other sources under an allowlist sandbox | Not currently provided — this is the only gap a dedicated remote-ClickHouse chDB tool would cover |

#### Why No Dedicated Tool Now

`run_chdb_query_remote_clickhouse` would overlap with two existing mechanisms:

- `run_query` already handles dynamic remote ClickHouse access in both mode.
- L2 catalog already handles governed federation against known remote ClickHouse tables.

The dedicated tool would add one narrow capability: dynamic remote-table selection plus cross-source joins plus sandboxing. That is useful, but it is not the common path, and it introduces extra policy surface:

- allowlisting remote hosts, databases, and tables
- credential handling separate from `CLICKHOUSE_*` and named collections
- discovery semantics distinct from both `run_query` and L2 catalog tables
- query rewriting or table-function scanning for remote ClickHouse sources
- another tool name for agents to choose among similar options

#### Decision

Defer `run_chdb_query_remote_clickhouse` until user demand proves the narrow gap is worth the additional API and safety surface. If added later, it should be explicitly sandboxed and positioned as the dynamic cross-source remote-ClickHouse escape hatch, not as the default ClickHouse query path.

## Configuration

Engine-specific settings use the `CHDB_` prefix. MCP-level settings stay global.

### chDB Engine Settings

| Parameter | Default | Purpose |
| --- | --- | --- |
| `CHDB_ENABLED` | `false` | Enable chDB engine |
| `CHDB_DATA_PATH` | `:memory:` | Session data path |
| `CHDB_ALLOW_WRITE_ACCESS` | `false` | Allow writes; when false, use `SET readonly=2` |
| `CHDB_MAX_RESULT_BYTES` | `1048576` | Engine result cap plus Python truncation |
| `CHDB_FILE_ALLOWLIST` | unset | Colon-separated path prefixes |
| `CHDB_SOURCES_CONFIG` | unset | Path to source catalog JSON |
| `CHDB_DATAFRAME_QUERY` | `false` | Register DataFrame query tool only for co-located deployments |

### Shared MCP Setting

| Parameter | Default | Purpose |
| --- | --- | --- |
| `query_timeout` | shared | Query timeout; intentionally not `CHDB_` prefixed |

### Credential Rule

Credentials are not `CHDB_` environment variables. They live in ClickHouse named collections and are populated from `CHDB_CRED_*` environment variables.

### Where Config Lives

Operators set configuration in:

- MCP host config `env` block
- deployment environment
- project README Configuration section

This is operator-facing configuration. It does not belong in an agent-facing file.

## DataFrame Query

`run_chdb_dataframe_query` is only for co-located deployments where chDB and the in-memory DataFrames share a process.

It is registered only when:

```bash
CHDB_DATAFRAME_QUERY=true
```

Example:

```python
run_chdb_dataframe_query(
query="SELECT category, avg(price) FROM {df} GROUP BY category",
df_ref="orders_df"
)
```

This aligns with `chdb.datastore` and chDB's zero-copy `Python()` table function.

## Deployment Topology

`run_chdb_dataframe_query` requires the agent runtime, MCP server, and chDB to run in one process.

```
┌──────────────────┐
│ LLM inference │ reasoning is always remote
└────────▲─────────┘
│ model inference, 1 hop
┌───────────────────┼────────────────────────────────────────┐
│ Serverless microVM, single invocation │
│ │
│ one process │
│ agent orchestrator = MCP client │
│ ├─ in-process MCP server, chDB tools │
│ ├─ chDB engine, _chdb.so │
│ └─ pandas DataFrames, same memory │
│ │
│ zero-copy Python() federation │
│ │ │ │
│ ▼ ▼ │
│ query DataFrames s3 / pg / remote CH │
└────────────────────────────────────────────────────────────┘
```

Notes:

- "No network" refers to the data plane for tool execution.
- LLM inference remains a remote call.
- Each tenant or invocation can get its own microVM and chDB session.
- If `CHDB_DATAFRAME_QUERY` is unset, the tool is absent.

## Implementation Phases

| Phase | Delivers |
| --- | --- |
| P1 | introspection APIs plus security baseline alongside `run_chdb_select_query` |
| P2 | named-collection catalog plus `attach_file` |
| P3 | co-located `run_chdb_dataframe_query` |

## Integration Checklist

| Existing piece | Treatment |
| --- | --- |
| `run_query` | unchanged ClickHouse-server tool |
| `run_chdb_select_query` | unchanged chDB query tool |
| new chDB-only APIs | register only when `CLICKHOUSE_ENABLED=false` and `CHDB_ENABLED=true` |
| `ChDBConfig` | add `allow_write_access`, `max_result_bytes`, `file_allowlist`, `sources_config` |
| `_serialize_tool_result` | reuse |
| `QUERY_EXECUTOR` | reuse |
| `chdb_tools.py` | new self-contained chDB tool module |
| `chdb_safety.py` | new pure helper module |
| `_init_chdb_client` | inject safety settings and materialize catalog before readonly mode |

## Design Decisions

1. **Both mode is frozen.** `run_query` and `run_chdb_select_query` keep 0.4.0 behavior. New APIs appear only in chDB-only mode.

2. **chDB-only naming is canonical.** Query remains `run_chdb_select_query`. Introspection tools use bare canonical names. DataFrame tool is `run_chdb_dataframe_query`.

3. **L2 uses native ClickHouse named collections.** Credentials are managed by the engine. No bespoke credential store is introduced.

4. **Federation is the universal-data-tool value.** It is delivered through inline SQL, named catalog tables, and runtime `attach_file`.

5. **DataFrame query is co-located only.** This aligns with `datastore` and prevents misuse in standard separate-process MCP deployments.

6. **Runtime catalog lifecycle is the main hardening item.** The important unresolved areas are per-session isolation, eviction, and restart behavior.

7. **Dedicated remote-ClickHouse tool is deferred.** `run_chdb_query_remote_clickhouse` is not added now. Both mode already covers dynamic remote ClickHouse access through `run_query`; L2 catalog covers governed federation with known remote ClickHouse tables. The only uncovered case is dynamic remote-table selection plus cross-source join plus sandbox, which is a narrow niche and should be added only if demand justifies the extra API and safety surface.

Start by reading the full design, especially Mode Registration, Security Baseline, and Tool Surface. Use the proposed chdb_tools.py and chdb_safety.py boundaries to assess the chDB-only APIs and safety helpers without changing both-mode behavior. Done means agreement on the mode boundary, naming, source-catalog configuration, and security baseline.

Written by the indexing model from the issue text.