Ephemeral SQL Sandbox and Query Profiler for AI Agents

id: mcp-ephemeral-sql-sandbox
namespace: company.ai

description: |
  Expose an in-memory DuckDB SQL sandbox as an MCP tool for AI coding agents to execute,
  benchmark, and inspect query execution plans safely before touching production.

inputs:
  - id: query_sql
    type: STRING
    displayName: Proposed SQL Query
    description: Analytical SQL query submitted by the AI agent to profile and
      execute in the sandbox.
    defaults: "SELECT c.customer_id, count(o.order_id) as total_orders,
      sum(o.amount) as total_spend FROM customers c JOIN orders o ON
      c.customer_id = o.customer_id GROUP BY 1 ORDER BY 3 DESC LIMIT 10;"

  - id: max_rows
    type: INT
    displayName: Maximum Output Rows
    description: Safety ceiling on the number of result rows returned to the agent.
    defaults: 25

  - id: capture_explain_plan
    type: BOOL
    displayName: Capture Query Plan
    description: When true, captures the DuckDB EXPLAIN execution plan to inspect
      join algorithms and scan costs.
    defaults: true

tasks:
  - id: sandbox_workspace
    type: io.kestra.plugin.core.flow.WorkingDirectory
    description: Isolated working directory housing the ephemeral DuckDB sandbox file.
    tasks:
      - id: initialize_sandbox_schema
        type: io.kestra.plugin.jdbc.duckdb.Queries
        description: Seeds realistic dimensional tables with sample records for join and
          aggregation testing.
        url: "jdbc:duckdb:sandbox.duckdb"
        fetchType: NONE
        sql: |
          CREATE TABLE customers (
            customer_id VARCHAR PRIMARY KEY,
            company_name VARCHAR,
            account_tier VARCHAR,
            region VARCHAR
          );

          INSERT INTO customers VALUES
            ('CUST-101', 'Acme Corp', 'ENTERPRISE', 'US-EAST'),
            ('CUST-102', 'Stripe Inc', 'ENTERPRISE', 'US-WEST'),
            ('CUST-103', 'Vercel Labs', 'PRO', 'EU-WEST'),
            ('CUST-104', 'Linear Orbit', 'GROWTH', 'US-EAST'),
            ('CUST-105', 'Supabase Cloud', 'PRO', 'EU-CENTRAL');

          CREATE TABLE orders (
            order_id VARCHAR PRIMARY KEY,
            customer_id VARCHAR,
            amount DOUBLE,
            status VARCHAR,
            order_date DATE
          );

          INSERT INTO orders VALUES
            ('ORD-5001', 'CUST-101', 1250.00, 'COMPLETED', DATE '2026-09-01'),
            ('ORD-5002', 'CUST-101', 3400.50, 'COMPLETED', DATE '2026-09-15'),
            ('ORD-5003', 'CUST-102', 820.00, 'COMPLETED', DATE '2026-09-20'),
            ('ORD-5004', 'CUST-103', 150.00, 'COMPLETED', DATE '2026-09-22'),
            ('ORD-5005', 'CUST-101', 940.00, 'REFUNDED', DATE '2026-09-25'),
            ('ORD-5006', 'CUST-104', 2100.00, 'COMPLETED', DATE '2026-10-01'),
            ('ORD-5007', 'CUST-102', 450.00, 'PENDING', DATE '2026-10-02');

      - id: explain_query_plan
        type: io.kestra.plugin.jdbc.duckdb.Query
        description: Extracts query plan diagnostics to detect unindexed scans or
          Cartesian joins.
        url: "jdbc:duckdb:sandbox.duckdb"
        fetchType: FETCH
        runIf: "{{ inputs.capture_explain_plan }}"
        sql: "EXPLAIN {{ inputs.query_sql }}"

      - id: execute_sandbox_query
        type: io.kestra.plugin.jdbc.duckdb.Query
        description: Executes the agent query and captures the resulting rows.
        url: "jdbc:duckdb:sandbox.duckdb"
        fetchType: FETCH
        sql: "{{ inputs.query_sql }}"

  - id: format_agent_response
    type: io.kestra.plugin.core.output.OutputValues
    description: Packages result rows, row counts, and plan details for the MCP tool
      output envelope.
    values:
      row_count: "{{ outputs.execute_sandbox_query.rows is not null ?
        outputs.execute_sandbox_query.rows | length : 0 }}"
      preview: "{{ outputs.execute_sandbox_query.rows is not null ?
        (outputs.execute_sandbox_query.rows | slice(0, inputs.max_rows)) : []
        }}"
      execution_status: "SUCCESS"
      query_plan: "{{ outputs.explain_query_plan.rows ?? [] }}"

  - id: log_verification_summary
    type: io.kestra.plugin.core.log.Log
    description: Records query execution stats in the Kestra execution audit log.
    message: "MCP SQL Sandbox executed successfully. Returned {{
      outputs.format_agent_response.values.row_count }} rows (capped to {{
      inputs.max_rows }} preview rows)."

outputs:
  - id: status
    type: STRING
    displayName: Execution Status
    description: Verification status returned to the agent.
    value: "{{ outputs.format_agent_response.values.execution_status ?? 'FAILED' }}"

  - id: row_count
    type: INT
    displayName: Returned Row Count
    description: Total number of rows returned by the sandbox query.
    value: "{{ outputs.format_agent_response.values.row_count ?? 0 }}"

  - id: result_preview
    type: JSON
    displayName: Query Result Preview
    description: Slice of returned records formatted as JSON for the agent.
    value: "{{ outputs.format_agent_response.values.preview ?? [] }}"

  - id: query_plan
    type: JSON
    displayName: DuckDB Execution Plan
    description: Query execution plan details for profiling join algorithms.
    value: "{{ outputs.format_agent_response.values.query_plan ?? [] }}"

triggers:
  - id: mcp_sql_sandbox
    type: io.kestra.plugin.core.trigger.McpToolTrigger
    toolName: test_sql_in_sandbox
    title: Ephemeral SQL Sandbox and Profiler
    toolDescription: >
      Executes and profiles an analytical SQL query inside an ephemeral DuckDB
      database sandbox. Call this tool when proposing or testing SQL queries to
      verify syntax, check row outputs, inspect query plans, and validate join
      logic before committing changes to production code.
    mcpServer: default
    annotations:
      readOnly: true
      destructive: false
      idempotent: true
      openWorld: false
      returnDirect: false

errors:
  - id: handle_sandbox_failure
    type: io.kestra.plugin.core.log.Log
    description: Logs syntax or execution errors encountered during query evaluation.
    message: "SQL Sandbox query evaluation failed. Review query syntax and column
      references."