Pentaho MCP

Production-grade Model Context Protocol (MCP) for Pentaho Kettle (.kjb / .ktr) artifacts: Lossless span-based XML inspection and editing, embedded Pentaho knowledge catalog, static structural validation, file repository management, and optional phase-gated local PDI runtime execution.

🌐 Language / Ngôn ngữ: English | Tiếng Việt

📑 Table of Contents

• Overview

• Key Features

• 41 Production Tools Surface

• System Architecture

• Recommended 5-Phase Workflow

• Quickstart

  • 1. Source Mode (For Developers)

  • 2. Self-Contained Windows Executable (.exe)

• Client Configuration

  • Claude Code

  • Cursor

  • Windsurf

  • Kiro

  • Codex CLI

• Configuration & Environment Variables

• Companion Skill

• Non-Goals & Safety Invariants

• Documentation Hub

• Contributing & License

Related MCP server: LogicMap MCP Server

💡 Overview

Pentaho MCP (advertised as kettle-mcp-dte on stdio) is a specialized Model Context Protocol (MCP) tool suite that enables AI coding assistants (such as Claude Code, Cursor, Windsurf, Kiro, and Codex) to safely inspect, create, edit, validate, and execute Pentaho Data Integration (PDI / Kettle) Jobs (.kjb) and Transformations (.ktr).

The system follows a strict architectural separation: Deterministic Primitives + External Reasoning:

• Pentaho MCP: Provides deterministic, lossless primitives: byte-exact XML span editing, hop coordinate calculations, schema-compliant step insertion, static graph validation, and repository reference migration.

• The AI Client / Superpowers Workflow: Performs the high-level reasoning: requirement analysis, design brainstorming, formal specification, and execution planning.

✨ Key Features

• 🎯 Lossless Span-Based XML Editing: Uses fast-xml-parser to pinpoint element byte offsets and only modifies the targeted byte spans. Preserves 100% of the original tag ordering, formatting, XML comments, whitespace, and CRLF/LF line endings expected by Pentaho Spoon.

• 🧠 Embedded Knowledge-First Catalog: Bundles a rich knowledge catalog (catalog.yaml and Markdown specifications in src/knowledge/pentaho/). AI agents never hallucinate Pentaho XML tags; they inspect canonical templates via kettle_knowledge_get before creating or updating steps/entries.

• ⚡ Zero-PDI Core Dependency: All read, create, edit, hop wiring, parameter modification, repository tracking, and static validation operations run on pure Node.js — no Java, PDI, or Spoon installation required for core features.

• 🛡️ Canonical Workspace Boundary Containment: Enforces strict directory boundaries via src/workspace/boundary.js. Rejects path traversals (..), sibling-prefix escapes, and symlink/junction escapes outside the resolved workspace root.

• 🗄️ Full Repository & Database Connection Support: Native handling for Pentaho File Repositories, internal path resolution (${Internal.Entry.Current.Directory}), .kdb shared database connection files, and automatic Spoon repositories.xml detection.

• 🚦 Phase-Gated Runtime Execution: Optional local PDI execution (Kitchen.bat / Pan.bat) protected by dual safeguards: environment opt-in (PENTAHO_ENABLE_EXECUTE=1), invocation-level confirmation (confirmed: true), and mandatory pre-run static validation.

🛠️ 41 Production Tools Surface

The production surface exposes exactly 41 tools (strictly enforced via set-equality check in verify:profile), categorized into 8 functional groups:

👉 For full parameters, input schemas, and call examples, see the 41 Tools Reference Guide (docs/tools-reference.md).

🏛️ System Architecture

flowchart LR
    subgraph Client_Layer[Client & Reasoning Layer]
        AI[AI Assistant: Claude Code / Cursor / Codex]
        SP[Superpowers / Companion Skill] -. guides .-> AI
    end

    subgraph MCP_Layer[Pentaho MCP / kettle-mcp-dte]
        STDIO[Stdio Transport JSON-RPC]
        SRV[Protocol Core: src/server.js]
        REG[Registry: 9 Tool Factories]
        BND[Boundary Policy: src/workspace/boundary.js]
        
        STDIO --> SRV --> REG
        REG --> BND
        
        CORE[Core Engine: Span-Based XML & Graph]
        KNOW[Knowledge Base: catalog.yaml + Markdown]
        REPO[Repository Engine: Paths & References]
        RT[Runtime Engine: Kitchen / Pan Process Runner]
        
        REG --> CORE
        REG --> KNOW
        REG --> REPO
        REG --> RT
    end

    subgraph Storage[Workspace & Local Environment]
        WS[(Workspace: .kjb / .ktr / .kdb)]
        PDI[(Local PDI: Kitchen.bat / Pan.bat)]
        SPOON[(Spoon: ~/.kettle/repositories.xml)]
    end

    CORE --> WS
    REPO --> WS
    REPO -. sync .-> SPOON
    RT -. phase-gated execution .-> PDI

🔄 Recommended 5-Phase Workflow

To ensure reliable, error-free ETL artifacts, development is structured into a 5-phase lifecycle guarded by a Dual Mutation Gate:

flowchart TD
    P1[Phase 1: BA Requirement & Brainstorming] -->|Design Approval| P2[Phase 2: Formal Specification]
    P2 -->|Spec Approval| P3[Phase 3: Implementation Planning]
    P3 -->|Plan Approval| GATE{DUAL MUTATION GATE<br>Both Spec & Plan approved?}
    GATE -->|Yes| P4[Phase 4: Knowledge-First XML Execution]
    GATE -->|No| P3
    P4 --> P5[Phase 5: Static Validation & Phase-Gated Runtime]
    P5 --> Handoff[Final Handoff & Review]

1. Phase 1 — Requirements & Design: Clarify sources, targets, parameters, and failure strategies. Only read-only tools (kettle_list, kettle_summary, kettle_knowledge_*) may be invoked.

2. Phase 2 — Specification: Draft a formal specification covering boundaries, artifact inventory, variables, job/transformation definitions, and acceptance criteria.

3. Phase 3 — Planning: Produce an artifact-by-artifact execution plan: leaf .ktr transformations first -> dependent .ktr -> orchestrating .kjb jobs last.

4. Phase 4 — Execution: The mutation gate opens only after both Spec and Plan are approved. Before configuring any step or entry, inspect its canonical schema with kettle_knowledge_get.

5. Phase 5 — Verification & Handoff: Run kettle_validate across individual files and the entire workspace tree. Only when zero structural errors remain can optional runtime verification (loadcheck, execute) be performed.

👉 Read the complete playbook in the 5-Phase Workflow Guide (docs/workflow-guide.md).

🚀 Quickstart

1. Source Mode (For Developers)

Prerequisites: Node.js >= 20.

# 1. Clone repository and install dependencies
git clone https://github.com/TumRoyal/pentaho-mcp-server.git
cd pentaho-mcp-server
npm install

# 2. Run full test suite and verify production tool profile
npm test
npm run verify:profile

# 3. Run on stdio
node src/index.js

2. Self-Contained Windows Executable (.exe, For End Users)

No local Node.js or npm install needed. Pentaho MCP, all runtime dependencies, and the full knowledge base are bundled into a single binary.

# 1. Build release package
npm run build:release -- --version 1.0.0

# 2. Verify health and dependencies with the doctor script
.\build\release\doctor.ps1 -PentahoHome C:\Pentaho\data-integration

Extract the release ZIP from dist/ into a stable path (e.g. C:\Tools\dte-pentaho-mcp\) to configure with your AI clients.

🔌 Client Configuration

Workspace root: no environment variable sets the workspace root. The server auto-detects it on startup, mirroring Spoon: a file repository declared in ~/.kettle/repositories.xml (the default repository wins, otherwise the first repository whose base_directory exists), else a repositories.xml inside PENTAHO_HOME, else the server process's working directory. Because there is no env override, the only ways to pin a project are to register it in ~/.kettle/repositories.xml, or to start the client with the project as its working directory. All paths passed to tools are still validated against the resolved root by the workspace boundary.

Claude Code

Add to your project's .mcp.json:

{
  "mcpServers": {
    "dte-pentaho": {
      "command": "node",
      "args": ["C:/path/to/pentaho-mcp-server/src/index.js"],
      "env": {
        "PENTAHO_HOME": "C:/Pentaho/data-integration",
        "PENTAHO_ENABLE_EXECUTE": "0"
      }
    }
  }
}

(For the standalone .exe, set command to C:/Tools/dte-pentaho-mcp/dte-pentaho-mcp.exe and args to [])

Cursor

Add to .cursor/mcp.json (or via Settings -> MCP Servers):

{
  "mcpServers": {
    "dte-pentaho": {
      "command": "C:/Tools/dte-pentaho-mcp/dte-pentaho-mcp.exe",
      "args": [],
      "env": {
        "PENTAHO_HOME": "C:/Pentaho/data-integration"
      }
    }
  }
}

Windsurf

Add to ~/.codeium/windsurf/mcp_config.json:

{
  "mcpServers": {
    "dte-pentaho": {
      "command": "node",
      "args": ["C:/path/to/pentaho-mcp-server/src/index.js"],
      "env": {
        "PENTAHO_HOME": "C:/Pentaho/data-integration"
      }
    }
  }
}

Kiro

Configure in .kiro/settings/mcp.json (workspace) or %USERPROFILE%/.kiro/settings/mcp.json (global):

{
  "mcpServers": {
    "dte-pentaho": {
      "command": "C:/Tools/dte-pentaho-mcp/dte-pentaho-mcp.exe",
      "args": [],
      "env": {
        "PENTAHO_HOME": "C:/Pentaho/data-integration"
      }
    }
  }
}

Codex CLI

Add to .codex/config.toml:

[mcp_servers.dte-pentaho]
command = "C:/Tools/dte-pentaho-mcp/dte-pentaho-mcp.exe"
args = []

[mcp_servers.dte-pentaho.env]
PENTAHO_HOME = "C:/Pentaho/data-integration"

⚙️ Configuration & Environment Variables

The workspace root is auto-detected per start-up and cannot be set by an environment variable — see Client Configuration. All tool paths stay confined to that root by the workspace boundary; relative paths resolve against it, and absolute paths are valid only when contained within it.

👉 For containment rules and security details, see the Configuration Guide (docs/configuration.md).

🧩 Companion Skill

This repository includes a specialized companion skill located at skills/developing-pentaho-jobs/:

• SKILL.md: 5-phase lifecycle orchestration, mutation gating, knowledge lookups, and static acceptance criteria.

• references/pentaho-spec-template.md: Formal Pentaho specification template.

• references/pentaho-plan-template.md: Artifact-by-artifact implementation plan template.

Note: Skills are not automatically activated by being present in the repository; copy the skill directory into your client's designated skills path (see Installation Guide).

🚫 Non-Goals & Safety Invariants

To keep Pentaho MCP deterministic, robust, and safe:

• ❌ No Automated Deployments or Git Mutations: Pentaho MCP never commits, pushes, or alters Git branches.

• ❌ No Runtime Knowledge Mutation: The embedded knowledge catalog is immutable and read-only.

• ❌ No Business Data Validation: kettle_validate verifies structural XML correctness for Spoon/Kitchen execution; it does not audit ETL data business logic.

• ❌ No MCP Prompts or Resources: Pentaho MCP advertises capabilities = { tools: {} } only, with no prompts or resources.

📖 Documentation Hub

Detailed documentation is available in the docs/ directory:

• 🗺️ Documentation Hub (docs/README.md): Role-based reading paths and navigation map.

• 🏗️ System Architecture: Deep dive into the span-based XML engine, module map, and containment policy.

• ⚙️ Configuration & Boundaries: Workspace confinement, environment variables, and process limits.

• 🛠️ 41 Tools Reference Guide: Complete parameter and schema documentation for all 41 tools.

• 📋 5-Phase Workflow Playbook: Step-by-step guide for turning BA requirements into validated Kettle artifacts.

• 💻 Development & Contribution: Repository setup, testing conventions, and Windows release packaging.

• 🚀 Installation Manual: Detailed setup instructions for source and packaged modes.

• 🩺 Operations & Troubleshooting: Health checks via doctor.ps1, log retention, and diagnostic procedures.

• 🔍 Documentation Facts: Single Source of Truth reference table.

• 📊 PDI 9.4 Compatibility Evidence & File Repository Evidence: Verified test evidence on Pentaho 9.4.

🤝 Contributing & License

• Contributions: Please read CONTRIBUTING.md for development workflows, testing guidelines, and PR checklists.

• License: Distributed under the MIT License.