ADR-015: Hybrid REST/SQL Data Tools Facade for AI Integration

Status

Implemented

Date

2025-12-06

Context

Following the evaluation of Heng Sin's idempiere-mcp project (ADR-014), we identified useful tool patterns for data access via REST API. While we won't adopt Heng Sin's code directly (different architecture), we can implement similar tools using our existing infrastructure:

• GeneratedOpenApiFactory (ADR-009) - Type-safe REST client with 18 APIs
• LangChain4j @Tool annotations (ADR-013) - AI integration framework
• Shared Tool Logic pattern - org.idempiere.cli.ai.shared.*

Inspiration from Heng Sin's idempiere-mcp

Heng Sin's project exposes 35+ MCP tools for iDempiere data access. Key patterns we adopt:

Reference: https://github.com/hengsin/idempiere-mcp

Decision

Create RestDataToolLogic facade that wraps GeneratedOpenApiFactory APIs and exposes them as LangChain4j @Tool methods.

Architecture - Hybrid Backend

The facade supports two backends:

• REST API: When iDempiere server is running (production, runtime operations)
• Direct SQL: When only PostgreSQL is available (development, AD queries)

┌─────────────────────────────────────────────────────────────────┐
│              LangChain4j CliRouterAgent                          │
│              (Natural language routing)                          │
└──────────────────────────┬──────────────────────────────────────┘
                           │ @Tool annotations
┌──────────────────────────▼──────────────────────────────────────┐
│              RestDataToolLogic (IMPLEMENTED)                     │
│  ┌────────────────────────────────────────────────────────────┐ │
│  │ Model Operations:          Server Operations (REST only):  │ │
│  │  • listModels(filter)       • listServerJobs()             │ │
│  │                             • getServerJob(jobId)          │ │
│  │ Process Operations:         • getServerJobLogs(jobId)      │ │
│  │  • listProcessesRest()      • toggleServerJobState(jobId)  │ │
│  │                             • runServerJob(jobId)          │ │
│  │ Backend Status:             • reloadServerJobs()           │ │
│  │  • getBackendStatus()                                      │ │
│  │  • checkAvailability()     Health:                         │ │
│  │  • checkHealth()            • checkHealth()                │ │
│  └────────────────────────────────────────────────────────────┘ │
└────────────────────┬─────────────────────┬──────────────────────┘
                     │                     │
       ┌─────────────▼─────────┐ ┌────────▼────────┐
       │ GeneratedOpenApiFactory│ │ Direct JDBC     │
       │ (REST API client)     │ │ (PostgreSQL)    │
       └─────────────┬─────────┘ └────────┬────────┘
                     │                     │
             ┌───────▼───────┐    ┌───────▼───────┐
             │ iDempiere     │    │  PostgreSQL   │
             │ REST API      │    │  Database     │
             └───────────────┘    └───────────────┘

Backend Selection:

• REST-only: Server jobs, health check, process execution (require running iDempiere)
• SQL-only: Application Dictionary queries via RegistryToolLogic
• Hybrid: Model listing can use either backend

Tool Definitions

1. Record Operations (ModelsApi)

@Tool(name = "searchRecords",
      description = "Search records in an iDempiere table/model with optional filter")
public ToolResult searchRecords(
    @P("Model/table name (e.g., C_BPartner, M_Product)") String model,
    @P("OData filter expression (e.g., Name eq 'Test')") String filter,
    @P("Maximum records to return (default 100)") Integer limit
) {
    // Uses GeneratedOpenApiFactory.models().modelsModelNameGet(...)
}

@Tool(name = "getRecord",
      description = "Get a single record by ID from an iDempiere table")
public ToolResult getRecord(
    @P("Model/table name") String model,
    @P("Record ID") Integer id
) {
    // Uses GeneratedOpenApiFactory.models().modelsModelNameIdGet(...)
}

@Tool(name = "createRecord",
      description = "Create a new record in an iDempiere table")
public ToolResult createRecord(
    @P("Model/table name") String model,
    @P("Record data as JSON") String jsonData
) {
    // Uses GeneratedOpenApiFactory.models().modelsModelNamePost(...)
}

2. Process Operations (ProcessApi)

@Tool(name = "getProcessInfo",
      description = "Get metadata about an iDempiere process (parameters, description)")
public ToolResult getProcessInfo(
    @P("Process slug or ID") String processId
) {
    // Uses GeneratedOpenApiFactory.process().processesSlugGet(...)
}

@Tool(name = "runProcess",
      description = "Execute an iDempiere process with parameters")
public ToolResult runProcess(
    @P("Process slug or ID") String processId,
    @P("Process parameters as JSON") String parametersJson
) {
    // Uses GeneratedOpenApiFactory.process().processesSlugPost(...)
}

3. Server Operations (ServerJobsApi)

@Tool(name = "listServerJobs",
      description = "List all server background jobs")
public ToolResult listServerJobs() {
    // Uses GeneratedOpenApiFactory.serverJobs().serversJobsGet()
}

@Tool(name = "toggleServerJob",
      description = "Enable or disable a server job")
public ToolResult toggleServerJob(
    @P("Job ID") String jobId,
    @P("Enable (true) or disable (false)") Boolean enabled
) {
    // Uses GeneratedOpenApiFactory.serverJobs()...
}

4. Window Navigation (WindowsApi)

@Tool(name = "listWindows",
      description = "List iDempiere windows matching a pattern")
public ToolResult listWindows(
    @P("Name pattern to filter (optional)") String pattern
) {
    // Uses GeneratedOpenApiFactory.windows().windowsGet(...)
}

@Tool(name = "getWindowTabs",
      description = "Get tabs for a specific window")
public ToolResult getWindowTabs(
    @P("Window slug or ID") String windowId
) {
    // Uses GeneratedOpenApiFactory.windows().windowsSlugTabsGet(...)
}

File Structure

src/main/java/org/idempiere/cli/ai/shared/
├── ToolResult.java                 # Existing
├── RegistryToolLogic.java          # Existing (direct DB)
├── QueryToolLogic.java             # Existing (direct DB)
├── RestDataToolLogic.java          # NEW: REST API facade
└── ...

Implementation Notes

1. ToolResult consistency: All methods return ToolResult for consistent AI responses
2. Error handling: Graceful handling of REST API errors (connection, auth, not found)
3. Availability check: Methods should check if REST API is configured/available
4. JSON serialization: Use Jackson for JSON parameters and responses

Comparison: Direct DB vs REST API Tools

Consequences

Positive

• Reuses existing GeneratedOpenApiFactory infrastructure
• Type-safe REST client (generated from OpenAPI)
• Consistent with Heng Sin's tool patterns (community alignment)
• Full CRUD operations (not just read-only)
• Process execution capability
• Server job management

Negative

• Requires running iDempiere server with REST plugin
• Network latency vs direct DB
• Additional authentication setup (API tokens)

Neutral

• Complements existing direct DB tools (different use cases)
• Can be used alongside RegistryToolLogic and QueryToolLogic

Related Decisions

• ADR-009: OpenAPI REST Client - Generated API client
• ADR-013: LangChain4j Integration - @Tool framework
• ADR-014: Heng Sin's idempiere-mcp Evaluation - Tool pattern inspiration

References

• Heng Sin's idempiere-mcp - Tool patterns reference
• iDempiere REST Web Services
• LangChain4j Tools