ADR-011: com.cloudempiere.ai Integration for Deep iDempiere Context

Status: Proposed Date: 2025-12-01 Context: Leverage existing cloudempiere.ai OSGi plugin for deeper iDempiere context in CLI and MCP server

Context

The idempiere-cli currently provides AI integration through:

• ADR-008: Application Dictionary Registry - static/exported metadata
• ADR-010: MCP Server Architecture - AI tool interface

However, these approaches have limitations:

• Static registry - Exports are snapshots, not live data
• REST API - Limited to exposed endpoints, no deep ERP context
• Direct DB - No security model, no business logic awareness

The com.cloudempiere.ai Opportunity

We have an existing OSGi plugin (com.cloudempiere.ai) that provides:

1. Deep iDempiere integration - Runs inside iDempiere JVM with full context
2. Role-based security - Inherits iDempiere's entire permission model
3. Multi-provider AI - Claude, AWS Bedrock, Ollama support
4. Agent framework - Tool execution with safety boundaries
5. Audit trail - Complete logging for compliance

Current Architecture Gap

┌─────────────────────────────────────────────────────────────────┐
│                    Claude Code / Claude Desktop                  │
└──────────────────────────┬──────────────────────────────────────┘
                           │ JSON-RPC over stdio
┌──────────────────────────▼──────────────────────────────────────┐
│              idempiere-mcp-server (Planned - ADR-010)           │
│                                                                  │
│  ┌──────────────────────────────────────────────────────────┐   │
│  │ Backend Options:                                          │   │
│  │  - CLI subprocess (current)                               │   │
│  │  - REST API (limited)                                     │   │
│  │  - Direct DB (no security) ← GAP                          │   │
│  │  - com.cloudempiere.ai ← NEW (deep integration)           │   │
│  └──────────────────────────────────────────────────────────┘   │
└─────────────────────────────────────────────────────────────────┘

Why Reuse com.cloudempiere.ai?

Decision

Integrate com.cloudempiere.ai as an optional backend for the MCP server, providing deep iDempiere context while maintaining the CLI's standalone capabilities.

Architecture

Integration Options

Option A: HTTP API Gateway (Recommended)

The cloudempiere.ai plugin exposes HTTP endpoints that the MCP server calls:

┌─────────────────────────────────────────────────────────────────┐
│                    Claude Code / Claude Desktop                  │
└──────────────────────────┬──────────────────────────────────────┘
                           │ stdio (JSON-RPC)
┌──────────────────────────▼──────────────────────────────────────┐
│                    idempiere-mcp-server                          │
│  ┌────────────────────────────────────────────────────────────┐ │
│  │ AiBackend implements BackendAdapter                        │ │
│  │                                                             │ │
│  │   POST /api/ai/query                                        │ │
│  │   POST /api/ai/metadata                                     │ │
│  │   POST /api/ai/context                                      │ │
│  └────────────────────────────────────────────────────────────┘ │
└──────────────────────────┬──────────────────────────────────────┘
                           │ HTTPS
┌──────────────────────────▼──────────────────────────────────────┐
│                    iDempiere Server                              │
│  ┌────────────────────────────────────────────────────────────┐ │
│  │ com.cloudempiere.ai (OSGi Plugin)                          │ │
│  │                                                             │ │
│  │  - SecureDatabaseQueryExecutor                              │ │
│  │  - TableMetadataTool                                        │ │
│  │  - WindowContextProvider                                    │ │
│  │  - AIG_QueryAudit (audit trail)                             │ │
│  └────────────────────────────────────────────────────────────┘ │
│                                                                  │
│  iDempiere Core (MRole, AccessSqlParser, DB, Models)            │
└─────────────────────────────────────────────────────────────────┘

Pros:

• Clean separation of concerns
• MCP server remains lightweight
• Security handled inside iDempiere JVM
• Standard HTTP integration

Cons:

• Requires iDempiere server running
• Network latency for each call

Option B: Shared Library (Alternative)

Extract core security/metadata classes from cloudempiere.ai into a shared library:

┌─────────────────────────────────────────────────────────────────┐
│                    idempiere-mcp-server                          │
│  ┌────────────────────────────────────────────────────────────┐ │
│  │ cloudempiere-ai-core (shared library)                      │ │
│  │                                                             │ │
│  │  - SecureDatabaseQueryExecutor                              │ │
│  │  - ADMetadataService                                        │ │
│  │  - BoundaryValidator                                        │ │
│  └────────────────────────────────────────────────────────────┘ │
│                                                                  │
│  Direct JDBC → PostgreSQL                                        │
└─────────────────────────────────────────────────────────────────┘

Pros:

• Works without iDempiere server
• Lower latency (direct DB)
• Standalone operation

Cons:

• Missing runtime context (active sessions, callouts)
• Requires duplicating iDempiere security logic
• Version compatibility challenges

Recommended: Hybrid Approach

Support multiple backends that can be selected at runtime:

public interface BackendAdapter {
    // Core operations
    QueryResult executeSecureQuery(String sql, Map<String, Object> params);
    TableMetadata getTableMetadata(String tableName);
    List<WindowInfo> searchWindows(String pattern);

    // Context operations (only available with AI backend)
    Optional<JSONObject> getWindowContext(int windowNo);
    Optional<JSONObject> getChartContext(int chartId);
}

// Backend implementations
CliBackend          // Calls idempiere-cli subprocess
RestApiBackend      // Calls iDempiere REST API
DirectDbBackend     // Direct JDBC (existing)
AiBackend           // Calls cloudempiere.ai HTTP API (NEW)

Key Reusable Components from com.cloudempiere.ai

1. Secure Database Query Executor

Location: com.cloudempiere.ai.database.SecureDatabaseQueryExecutor

What it does:

• Loads AI user from provider configuration
• Applies logged-in user's role permissions via MRole.addAccessSQL()
• Validates SQL (SELECT only, no DML/DDL)
• Extracts and validates table access
• Applies row limits (default 100, max 1000)
• Redacts sensitive columns (PASSWORD, APIKEY, etc.)
• Logs to AIG_QueryAudit table

Reuse in CLI:

// In AiBackend
public QueryResult executeSecureQuery(String sql, Map<String, Object> params) {
    // POST to iDempiere: /api/ai/query
    // {
    //   "sql": "SELECT * FROM C_BPartner WHERE Name LIKE ?",
    //   "params": ["%test%"],
    //   "roleId": 102,
    //   "maxRows": 100
    // }
    // Returns: secure, filtered, audited results
}

2. Table Metadata Tool

Location: com.cloudempiere.ai.tool.TableMetadataTool

What it does:

• Queries AD_Table, AD_Column with full metadata
• Includes foreign key relationships
• Shows validation rules from AD_Reference
• Returns column help text for AI context

Reuse:

public TableMetadata getTableMetadata(String tableName) {
    // GET /api/ai/metadata/table/{tableName}
    // Returns:
    // - Table description, access level
    // - All columns with types, references
    // - Foreign key relationships
    // - Display logic, validation rules
}

3. Context Providers

Location: com.cloudempiere.ai.context.*

WindowContextProvider:

• Extracts current window, tab, record
• Includes field values and display state
• Shows active filters and selections

ChartContextProvider:

• Extracts chart data and configuration
• Includes series, axes, drill-down state

Reuse:

// Only available when connected to live iDempiere
public Optional<JSONObject> getWindowContext(int windowNo) {
    // GET /api/ai/context/window/{windowNo}
    // Returns current window state for AI context
}

4. Boundary Validator

Location: com.cloudempiere.ai.agent.BoundaryValidator

What it does:

• Validates context before tool execution
• Enforces cost limits (tokens, API calls)
• Enforces rate limits (calls per minute)
• Validates tool permissions
• Prevents privilege escalation

Reuse in MCP Server:

public class McpBoundaryValidator {
    // Adapt cloudempiere.ai patterns
    public void validateBeforeExecution(McpContext context, String toolName) {
        // Check cost limits
        if (context.getTotalCost() > context.getCostLimit()) {
            throw new CostLimitExceededException();
        }
        // Check rate limits
        if (context.getCallsThisMinute() > context.getRateLimit()) {
            throw new RateLimitExceededException();
        }
        // Validate tool permission
        if (!context.hasToolPermission(toolName)) {
            throw new PermissionDeniedException();
        }
    }
}

5. Audit Trail Model

Location: com.cloudempiere.ai.model.MAIQueryAudit

Fields tracked:

• AIG_Provider_ID - Which AI provider
• AD_User_ID - AI user who executed
• CreatedBy - Logged-in user who initiated
• AD_Role_ID - Role used for permissions
• AIG_QuerySQL - Original SQL
• AIG_SecuredSQL - SQL with security filters
• AIG_QueryStatus - Success/Error/PermissionDenied
• AIG_ErrorMessage - Error details
• AIG_RowCount - Results returned
• AIG_ExecutionTimeMs - Performance
• AIG_TablesAccessed - Which tables

Reuse: All queries through cloudempiere.ai are automatically audited. For CLI-only operations, implement similar audit logging.

Implementation Plan

Phase 1: API Endpoint Design (Week 1-2)

Define HTTP API contract between MCP server and cloudempiere.ai:

openapi: 3.0.0
info:
  title: cloudempiere.ai Integration API
  version: 1.0.0

paths:
  /api/ai/query:
    post:
      summary: Execute secure database query
      requestBody:
        content:
          application/json:
            schema:
              type: object
              properties:
                sql:
                  type: string
                params:
                  type: array
                roleId:
                  type: integer
                maxRows:
                  type: integer
                  default: 100
      responses:
        200:
          description: Query results
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/QueryResult'

  /api/ai/metadata/table/{tableName}:
    get:
      summary: Get table metadata
      responses:
        200:
          description: Table metadata
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/TableMetadata'

  /api/ai/metadata/window/{windowId}:
    get:
      summary: Get window metadata
      responses:
        200:
          description: Window metadata

  /api/ai/context/window/{windowNo}:
    get:
      summary: Get current window context
      responses:
        200:
          description: Window context

  /api/ai/health:
    get:
      summary: Health check
      responses:
        200:
          description: Service healthy

Phase 2: cloudempiere.ai HTTP Endpoints (Week 3-4)

Add REST endpoints to cloudempiere.ai plugin:

@Path("/api/ai")
@Produces(MediaType.APPLICATION_JSON)
public class AIIntegrationEndpoint {

    @Inject
    SecureDatabaseQueryExecutor queryExecutor;

    @Inject
    ADMetadataService metadataService;

    @POST
    @Path("/query")
    public Response executeQuery(QueryRequest request) {
        // Validate authentication
        // Execute secure query
        // Return results
    }

    @GET
    @Path("/metadata/table/{tableName}")
    public Response getTableMetadata(@PathParam("tableName") String tableName) {
        return Response.ok(metadataService.getTableMetadata(tableName)).build();
    }
}

Phase 3: MCP Server AiBackend (Week 5-6)

Implement AiBackend in idempiere-mcp-server:

public class AiBackend implements BackendAdapter {

    private final String aiApiUrl;
    private final OkHttpClient client;

    public AiBackend(String aiApiUrl, String apiToken) {
        this.aiApiUrl = aiApiUrl;
        this.client = new OkHttpClient.Builder()
            .addInterceptor(new AuthInterceptor(apiToken))
            .build();
    }

    @Override
    public QueryResult executeSecureQuery(String sql, Map<String, Object> params) {
        Request request = new Request.Builder()
            .url(aiApiUrl + "/api/ai/query")
            .post(RequestBody.create(toJson(sql, params), JSON))
            .build();

        try (Response response = client.newCall(request).execute()) {
            return parseQueryResult(response.body().string());
        }
    }

    @Override
    public TableMetadata getTableMetadata(String tableName) {
        Request request = new Request.Builder()
            .url(aiApiUrl + "/api/ai/metadata/table/" + tableName)
            .get()
            .build();

        try (Response response = client.newCall(request).execute()) {
            return parseTableMetadata(response.body().string());
        }
    }
}

Phase 4: Configuration & Documentation (Week 7-8)

MCP Server Configuration:

{
  "mcpServers": {
    "idempiere": {
      "command": "java",
      "args": ["-jar", "/path/to/idempiere-mcp-server.jar"],
      "env": {
        "BACKEND": "ai",
        "AI_API_URL": "http://localhost:8080",
        "AI_API_TOKEN": "${IDEMPIERE_AI_TOKEN}",
        "AI_ROLE_ID": "102"
      }
    }
  }
}

Backend Selection:

# Use CLI backend (default, no iDempiere server needed)
java -jar idempiere-mcp-server.jar --backend=cli

# Use REST API backend
java -jar idempiere-mcp-server.jar --backend=rest \
  --api-url=http://localhost:8080 \
  --api-token=xxx

# Use AI backend (deep integration)
java -jar idempiere-mcp-server.jar --backend=ai \
  --ai-api-url=http://localhost:8080 \
  --ai-api-token=xxx \
  --ai-role-id=102

Security Considerations

Authentication Flow

Claude Code Agent
    │
    │ 1. Connects via stdio
    ▼
MCP Server (idempiere-mcp-server)
    │
    │ 2. Reads AI_API_TOKEN from env
    │
    │ 3. HTTP request with Bearer token
    ▼
iDempiere (cloudempiere.ai plugin)
    │
    │ 4. Validates token → maps to AI User
    │
    │ 5. Loads Role from request
    │
    │ 6. Applies MRole.addAccessSQL()
    │
    │ 7. Logs to AIG_QueryAudit
    ▼
Database (with security filters)

Permission Model

AI User Account (AD_User)
    │
    ├─ Has Role (AD_Role)
    │   ├─ Table Access (AD_Table_Access)
    │   ├─ Column Access (AD_Column_Access)
    │   ├─ Record Access (AD_Record_Access)
    │   ├─ Window Access (AD_Window_Access)
    │   └─ Process Access (AD_Process_Access)
    │
    └─ Client/Org Restrictions
        ├─ AD_Client_ID filter
        └─ AD_Org_ID filter

Sensitive Data Handling

cloudempiere.ai automatically redacts:

• Columns ending in PASSWORD
• Columns ending in APIKEY
• Columns ending in SECRET
• Columns ending in CREDITCARD
• Columns ending in TOKEN

Benefits

For CLI Development

1. Deep AD Context - Access live Application Dictionary, not static exports
2. Security Inheritance - Leverage existing iDempiere security model
3. Audit Compliance - All AI operations automatically logged
4. Business Logic Awareness - Can understand validators, callouts
5. Multi-tenant Safe - Automatic Client/Org filtering

For AI Agents

1. Accurate Metadata - Always-current table/window information
2. Safe Queries - Cannot exceed role permissions
3. Cost Control - Built-in limits prevent runaway usage
4. Context Awareness - Can see what user is viewing

For iDempiere Ecosystem

1. Reuse Investment - Leverage existing cloudempiere.ai work
2. Consistent Security - Same model for UI and AI access
3. Unified Audit - Single place for compliance tracking
4. Gradual Adoption - Optional backend, not mandatory

Risks & Mitigations

Alternatives Considered

1. Fork cloudempiere.ai Security Code

Rejected - Would duplicate maintenance effort and miss runtime context.

2. Direct OSGi Integration

Rejected - MCP server would need to run inside iDempiere, too tightly coupled.

3. GraphQL Gateway

Deferred - HTTP REST is simpler for initial implementation, GraphQL could be added later.

Consequences

Positive

• Deep integration without duplicating security implementation
• Consistent audit trail across UI and AI access
• Leverages existing investment in cloudempiere.ai
• Flexible backend selection - users choose based on their setup
• Production-ready security from day one

Negative

• Runtime dependency on iDempiere for full features
• Additional HTTP hop increases latency
• Coordination required between two codebases
• Version compatibility must be maintained

Neutral

• CLI continues to work standalone (with CLI/REST backends)
• cloudempiere.ai remains an independent OSGi plugin
• MCP server abstracts backend choice from Claude Code

References

Related ADRs

• ADR-008: Application Dictionary Registry
• ADR-010: MCP Server Architecture
• ADR-006: CLI as M2M API

cloudempiere.ai Source Code

• Location: /Users/norbertbede/github/com.cloudempiere.ai/
• Key packages:
  • com.cloudempiere.ai.provider - AI provider implementations
  • com.cloudempiere.ai.agent - Agent framework
  • com.cloudempiere.ai.tool - Tool definitions
  • com.cloudempiere.ai.database - Secure query execution
  • com.cloudempiere.ai.context - Context extraction
  • com.cloudempiere.ai.model - Data models (AIG_*)

iDempiere Documentation

• Role-Based Security
• AccessSqlParser
• REST Web Services

External References

• Model Context Protocol
• Anthropic Claude API