Summary

Altinity‑MCP should remain a universal, minimalistic intermediary between LLM and Clickhouse, whose only built‑in tool is execute_query. Using such a tool, any LLM can infer the database structure by SHOW commands and start exploring data by SELECT queries. If write access is allowed, LLM can also execute INSERT and ALTER using the very same tool. Granular permissions can be tuned on the Clickhouse side by GRANT commands.

MCP specification allows the creation of many tools, prompts, and resources. That makes the LLM get more specific knowledge and write better SQL code. However, such knowledge is related to a particular application, created on top of Clickhouse and the MCP server, and requires its own design and configuration. As our goal is to create a universal MCP server for any Clickhouse database, such additional configuration is out of the scope.

Goals

• Ship Altinity‑MCP with just execute_query by default.
• Support an opt‑in, zero‑extra‑service mechanism to register additional MCP prompts, resources, and (optionally) templated tools by reading metadata from ClickHouse.

Proposal

Introduce an optional metadata database (default name: mcp, configurable) inside ClickHouse.

Altinity‑MCP will:

1. On startup, try to discover and read the table mcp.meta with rows of different types covering 3 types of objects (prompt/resource/tool).
2. If an object is found, materialize it into MCP objects using the data and metadata inferred from the meta table
3. It's possible that the meta table does not contain static object content or a template but refers to a parametrized view for dynamic content. In this case, there should be a column referring the view by name and a column explaining parameters and their types.

F.e. to create a database schema resource, it should be a view like this:

CREATE OR REPLACE VIEW mcp.db_schema_resource AS
WITH ordered AS (
    SELECT database, name, create_table_query
    FROM system.tables
    WHERE database NOT IN ('system', 'information_schema', 'INFORMATION_SCHEMA')
      AND is_temporary = 0
    ORDER BY database, name
    )
SELECT 'DB Schema'                                                                                    AS name,
       'Concatenation of CREATE statements for all non‑system tables, ordered by database and table.' AS description,
       arrayStringConcat(groupArray(create_table_query), ';\n')    AS content
FROM ordered

and a row in the meta table, explaining that it's a dynamic resource without parameters.

Meta table example:

CREATE TABLE IF NOT EXISTS mcp.meta
(
    obj_type             Enum8('tool' = 1, 'resource' = 2, 'prompt' = 3),
    obj_name           String,
    uri_template      String,
    description        String,
    params               Map(String,(String,String)), --name/type/description
    template             String,
    view_name         String,
    updated_at        UInt32 materialized now(),
    is_deleted         UInt8
)
ENGINE = ReplacingMergeTree(updated_at,is_deleted)
ORDER BY (obj_type, obj_name);

row example:

INSERT INTO mcp.meta 
VALUES(obj_type,obj_name,uri_template,description,view_name)
('resource','db_schema','clickhouse://schema','DDL for all non‑system tables',
'mcp.db_schema_resource');