MCP agent in Open WebUI: tools and ReWOO

Building an MCP Agent in Open WebUI: From Basic Tools to a Planner

An agent in Open WebUI for MCP starts simple — we give the LLM a set of tools to interact with ClickHouse and watch what happens. Initially, three basic tools are available: list_databases, list_tables, and run_select_queries. These handle straightforward SQL queries, but as tasks grow more complex, the model struggles to understand data schemas.

Solution: A detailed prompt with concrete query examples. The structure includes variables (portfolio_name, start_date, end_date), instructions for LIKE filters, and patterns like Dt::date >= '{start_date}'. For example, calculating portfolio performance uses window functions:

WITH log_coef as (SELECT Portfolio, Dt,
    sum(log(TWR_dod+1)) OVER (
        PARTITION BY InvestmentPortfolioID
        ORDER BY Dt ASC ROWS BETWEEN UNBOUNDED PRECEDING AND 0 FOLLOWING
    ) AS TWR_cumulative_coef
FROM Contribution.contribution_twr_1s_mcp
WHERE lowerUTF8(Portfolio) LIKE '%{portfolio_name}%'
    AND Dt::date >= {start_date}
    AND Dt::date <= {end_date}
ORDER BY Dt DESC)
SELECT Portfolio, Dt, exp(TWR_cumulative_coef) - 1 AS TWR_cumulative
FROM log_coef;

Results improve, but remain unstable — syntax errors, incorrect metrics persist. RAG in Open WebUI doesn’t fix it.

Expanding the Toolkit: Custom Tools Without Hallucinations

Moving toward determinism: we build pre-defined tools using Pydantic schemas. The LLM only selects and parameterizes them; queries are fixed. Tool classes include:

• ClickHouseClientBase: wrapper around core MCP operations.
• ProfitTool: calculates returns (TWR by date, arithmetic/geometric).
• PortfolioDiscoveryTool: discovers portfolio attributes (list, types, strategies).
• PortfolioCashflowTool: tracks inflows/outflows, cash flow analysis.

Example schema for ClickHouseClientBaseParams:

class ClickHouseClientBaseParams(BaseModel):
    operation: Literal['list_databases', 'list_tables', 'run_select_query'] = Field(
        description='Operation type: list_databases, list_tables, or run_select_query'
    )
    database: Optional[str] = Field(default=None, description='Database name')
    query: Optional[str] = Field(default=None, description='SQL query')
    like: Optional[str] = Field(default=None, description='LIKE filter')
    not_like: Optional[str] = Field(default=None, description='NOT LIKE filter')


def clickhouse_client_base(params: ClickHouseClientBaseParams) -> str:
    # Logic for HTTP requests to http://clickhouse-mcp.services.kfim.int
    # Handles list_databases, list_tables, run_select_query
    # Auto-replaces contribution_twr_1s_mcp with full table name

The pydantic_to_openai_schema function converts these schemas into OpenAI-compatible format, eliminating hallucinations — tools always return predictable JSON.

Benefits of this approach:

• Determinism: same input → same output.
• Scalability: new metrics added as tools.
• Debugging: query logic is transparent, no black-box LLM behavior.

The Limits of Simplicity: When Tools Fall Short

A naive agent fails on complex tasks: multiple portfolios, aggregations, JOINs. The model confuses window functions and date filters. Even with examples, error rates hit 30–40%. Scaling portfolio analytics demands processing large datasets — thousands of TWR rows, cashflow records.

Enhancement: Splitting into Planner and Executor

Second iteration: agent as ReWOO — Reason + Act. The planner (a separate LLM) breaks down tasks into steps; the executor calls tools. This solves:

• Data filtering: full-text search across portfolios via PortfolioDiscoveryTool.
• Sequence control: chained queries (list → filter → aggregate).
• Context management: tables merged into one for UI rendering.

The planner generates a plan:

• Step 1: list portfolios LIKE '%name%'.
• Step 2: run_query for TWR.
• Step 3: aggregate and display.

The executor follows the plan strictly, no improvisation.

Full-Text Search in Action

For large tables — pre-filtering is key. The tool searches using lowerUTF8(Portfolio) LIKE '%query%', returns IDs/types. This speeds up run_select_query: instead of full scans, targeted queries.

Example chain:

• PortfolioDiscoveryTool(like='stocks') → returns ID list.
• ProfitTool(portfolios=ids, dates=range) → fetches TWR.
• Merge into Pandas/DataFrame for UI.

Key Takeaways

• Deterministic tools based on Pydantic reduce LLM errors in SQL.
• ReWOO approach separates planning and execution for complex workflows.
• Full-text search is essential for filtering in large ClickHouse datasets.
• Open WebUI integration enables sequential calls with final table rendering.
• Chain-of-thought prompts boost stability, even with weaker models.

— Editorial Team