ADR-047: Satellite-iDempiere Integration Architecture (Revised)

Status

Proposed (Replaces ADR-046)

Date

2025-12-11

Context and Problem Statement

ADR-043 through ADR-046 proposed a Chat API Service architecture by extracting components from com.cloudempiere.ai OSGi plugin. However, ADR-046 assumed the Satellite would call iDempiere for data persistence (metrics, audit).

The actual requirement is different: Satellite must serve as an AI backend provider for iDempiere ERP, requiring:

iDempiere → Satellite: ZK UI calls Satellite for AI chat completions
Satellite → iDempiere: Tools need to query/mutate iDempiere data
Bidirectional integration: Real-time data access during AI processing
Existing infrastructure: Reuse iDempiere REST API (bxservice/hengsin)

Key Insights

Existing Infrastructure (Already Implemented)

iDempiere REST API (bxservice + hengsin implementations):
- api/v1/models/{table} - CRUD operations
- api/v1/windows/{window} - Window metadata
- api/v1/processes/{process} - Process execution
- api/v1/auth/tokens - JWT authentication
- Full OData query support ($filter, $expand, $select)
RestDataToolLogic (ADR-015, already implemented):
- Hybrid REST/SQL backend
- LangChain4j @Tool annotations
- Already wraps GeneratedOpenApiFactory
- Production-ready in idempiere-cli
MCP Tools (from hengsin/idempiere-mcp evaluation):
- Tool patterns validated by community
- 35+ tool capabilities identified
- Multi-tenant token authentication

What's Missing

Satellite REST API endpoints (OpenAI-compatible)
Integration of RestDataToolLogic into Satellite agents
Direct SQL access configuration for complex queries
iDempiere context propagation (Client, User, Role)

Decision

Use hybrid integration strategy:

REST API (existing) for CRUD, processes, metadata
Direct SQL (read-only) for complex analytical queries
Reuse RestDataToolLogic for tool implementations
No new iDempiere endpoints required

Architecture

Overall Flow

┌─────────────────────────────────────────────────────────────────────┐
│                    IDEMPIERE ERP (Java 11)                          │
│                                                                     │
│  User → ZK UI → com.cloudempiere.ai (thin bridge)                  │
│                         │                                           │
│                         │ HTTP POST /v1/chat/completions            │
│                         │ Headers:                                  │
│                         │   X-iDempiere-Client-ID: 1000000          │
│                         │   X-iDempiere-User-ID: 100                │
│                         │   X-iDempiere-Role-ID: 102                │
│                         │   X-iDempiere-Language: en_US             │
│                         │   Authorization: Bearer &lt;token&gt;           │
│                         ▼                                           │
└─────────────────────────┼───────────────────────────────────────────┘
                          │
                          │
┌─────────────────────────▼───────────────────────────────────────────┐
│           CHAT API SERVICE (idempiere-cli, Java 17+)            │
│                                                                     │
│  ┌──────────────────────────────────────────────────────────────┐  │
│  │  POST /v1/chat/completions (OpenAI-compatible)               │  │
│  │       │                                                      │  │
│  │       ├─ Extract ChatContext (from headers)            │  │
│  │       ├─ Check budget (via REST API)                        │  │
│  │       ├─ Apply guardrails (InputGuard, OutputGuard)         │  │
│  │       ├─ Route to LangChain4j agent                         │  │
│  │       └─ Record metrics (async)                             │  │
│  └──────────────────┬───────────────────────────────────────────┘  │
│                     │                                               │
│  ┌──────────────────▼───────────────────────────────────────────┐  │
│  │  ChatAgent (@RegisterAiService)                         │  │
│  │  - Uses Claude/OpenAI/Ollama                                 │  │
│  │  - Equipped with tools:                                      │  │
│  │    • DatabaseQueryTool → Direct SQL                          │  │
│  │    • TableMetadataTool → REST API                            │  │
│  │    • RecordQueryTool → REST API                              │  │
│  │    • ProcessExecutionTool → REST API                         │  │
│  │    • KnowledgeSearchTool → RAG                               │  │
│  └──────────────────┬───────────────────────────────────────────┘  │
│                     │                                               │
│  ┌──────────────────▼───────────────────────────────────────────┐  │
│  │  RestDataToolLogic (ADR-015, EXISTING)                       │  │
│  │  - Wraps GeneratedOpenApiFactory                             │  │
│  │  - listModels(), listProcessesRest()                         │  │
│  │  - getServerJob(), toggleServerJob()                         │  │
│  │  - checkHealth()                                             │  │
│  └────┬─────────────────────────────┬─────────────────────────────┘  │
│       │                             │                               │
│       │ REST API                    │ Direct SQL                    │
│       ▼                             ▼                               │
└───────┼─────────────────────────────┼───────────────────────────────┘
        │                             │
        ▼                             ▼
┌────────────────────────┐   ┌────────────────────────┐
│  iDempiere REST API    │   │  PostgreSQL Database   │
│  (bxservice/hengsin)   │   │  (read-only user)      │
│                        │   │                        │
│  api/v1/models/{table} │   │  SELECT queries only   │
│  api/v1/processes/{id} │   │  Complex analytics     │
│  api/v1/windows/{id}   │   │  Direct AD access      │
└────────────────────────┘   └────────────────────────┘

Backend Selection Strategy

Operation	Backend	Reason
Complex SQL queries	Direct PostgreSQL	Performance, flexibility
CRUD operations	REST API	Business logic, validation, audit
Process execution	REST API	iDempiere context required
Window metadata	REST API	No schema dependency
Server jobs	REST API	Requires running iDempiere
AD metadata queries	Direct SQL	Already implemented (RegistryToolLogic)

Tool Mapping

Chat API Tools → Existing Infrastructure

Chat API Tool (Planned)	Implementation	Backend
DatabaseQueryTool	New (direct SQL)	PostgreSQL read-only
TableMetadataTool	`RestDataToolLogic.listModels()`	REST API
RecordQueryTool	`GeneratedOpenApiFactory.models()`	REST API
ProcessExecutionTool	`GeneratedOpenApiFactory.process()`	REST API
WindowMetadataTool	`GeneratedOpenApiFactory.windows()`	REST API
ServerJobTool	`RestDataToolLogic.listServerJobs()`	REST API
KnowledgeSearchTool	Existing `RagService`	Vector DB
RegistryQueryTool	Existing `RegistryToolLogic`	Direct SQL

Implementation Plan

Phase 1: Core Integration (Week 3-4)

Reuse existing components:

// satellite/tool/impl/TableMetadataTool.java
@ApplicationScoped
public class TableMetadataTool implements ISatelliteTool {

    @Inject
    RestDataToolLogic restLogic;  // EXISTING

    @Override
    public ToolResult execute(Map&lt;String, Object&gt; parameters) {
        String tableName = (String) parameters.get(&quot;tableName&quot;);

        // Reuse existing implementation
        return restLogic.listModels(&quot;modelName eq &#39;&quot; + tableName + &quot;&#39;&quot;);
    }
}

New component:

// satellite/tool/impl/DatabaseQueryTool.java
@ApplicationScoped
public class DatabaseQueryTool implements ISatelliteTool {

    @Inject
    @Named(&quot;idempiere&quot;)
    DataSource dataSource;  // Read-only connection

    @Inject
    ExecutionGuard executionGuard;

    @Override
    public ToolResult execute(Map&lt;String, Object&gt; parameters) {
        String sql = (String) parameters.get(&quot;sql&quot;);

        // Validate SQL (SELECT only, no mutations)
        if (!executionGuard.validateSql(sql)) {
            return ToolResult.error(&quot;Only SELECT queries allowed&quot;);
        }

        // Execute via JDBC
        try (Connection conn = dataSource.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            // Convert to JSON
            return ToolResult.success(&quot;Query executed&quot;)
                    .data(&quot;rows&quot;, resultSetToJson(rs));
        }
    }
}

Phase 2: Context Propagation (Week 4)

Extract iDempiere context from headers:

// satellite/context/ContextExtractor.java
@Provider
public class ContextFilter implements ContainerRequestFilter {

    @Override
    public void filter(ContainerRequestContext requestContext) {
        ChatContext ctx = ChatContext.builder()
            .clientId(getHeader(&quot;X-iDempiere-Client-ID&quot;))
            .userId(getHeader(&quot;X-iDempiere-User-ID&quot;))
            .roleId(getHeader(&quot;X-iDempiere-Role-ID&quot;))
            .language(getHeader(&quot;X-iDempiere-Language&quot;))
            .build();

        // Store in context for tools
        requestContext.setProperty(&quot;satelliteContext&quot;, ctx);
    }
}

Use context in tools:

// satellite/tool/impl/RecordQueryTool.java
public ToolResult execute(Map&lt;String, Object&gt; parameters) {
    ChatContext ctx = getContext();  // From request context

    // Add client filter to REST API call
    String filter = &quot;AD_Client_ID eq &quot; + ctx.getClientId();

    return restLogic.listModels(filter);
}

Phase 3: Satellite API Endpoints (Week 5-6)

OpenAI-compatible endpoints:

// satellite/api/SatelliteApiResource.java
@Path(&quot;/v1&quot;)
@ApplicationScoped
public class SatelliteApiResource {

    @Inject
    ChatAgentService agentService;

    @POST
    @Path(&quot;/chat/completions&quot;)
    @Produces(MediaType.APPLICATION_JSON)
    public Response chatCompletions(
            @HeaderParam(&quot;X-iDempiere-Client-ID&quot;) Integer clientId,
            @HeaderParam(&quot;X-iDempiere-User-ID&quot;) Integer userId,
            ChatCompletionRequest request) {

        // Build context
        ChatContext ctx = ChatContext.builder()
            .clientId(clientId)
            .userId(userId)
            .build();

        // Process with agent
        SatelliteResponse response = agentService.process(request, ctx);

        return Response.ok(response).build();
    }

    @GET
    @Path(&quot;/models&quot;)
    public Response listModels() {
        // Return available AI models
        return Response.ok(modelRegistry.list()).build();
    }
}

Configuration

Satellite `application.properties`

# ==================== iDempiere REST API Integration ====================

# Base URL of iDempiere REST API
idempiere.api.base-url=${IDEMPIERE_API_URL:http://localhost:8080}
idempiere.api.token=${IDEMPIERE_API_TOKEN:}

# REST client (EXISTING, from ADR-009)
quarkus.rest-client.idempiere-api.url=${idempiere.api.base-url}
quarkus.rest-client.idempiere-api.connect-timeout=5000
quarkus.rest-client.idempiere-api.read-timeout=30000

# ==================== Direct PostgreSQL Access ====================

# Read-only database connection
quarkus.datasource.idempiere.db-kind=postgresql
quarkus.datasource.idempiere.jdbc.url=${IDEMPIERE_DB_URL:jdbc:postgresql://localhost:5432/idempiere}
quarkus.datasource.idempiere.username=${IDEMPIERE_DB_USER:chatapi_readonly}
quarkus.datasource.idempiere.password=${IDEMPIERE_DB_PASSWORD:}

# Enforce read-only
quarkus.datasource.idempiere.jdbc.additional-jdbc-properties.readOnly=true

# Connection pool
quarkus.datasource.idempiere.jdbc.min-size=2
quarkus.datasource.idempiere.jdbc.max-size=10

# ==================== Satellite Server ====================

# Server port
quarkus.http.port=${SATELLITE_PORT:8081}

# Enable CORS for iDempiere
quarkus.http.cors=true
quarkus.http.cors.origins=${IDEMPIERE_URL:http://localhost:8080}

# ==================== Tool Configuration ====================

# Enable direct SQL queries
satellite.tools.sql.enabled=true
satellite.tools.sql.max-results=1000

# Enable REST API tools
satellite.tools.rest.enabled=true
satellite.tools.rest.cache-ttl=5m

# ==================== Metrics Integration ====================

# How to send metrics back to iDempiere
satellite.metrics.mode=rest  # or &quot;queue&quot; or &quot;disabled&quot;
satellite.metrics.batch-size=50
satellite.metrics.flush-interval=10s

iDempiere PostgreSQL Setup

-- Create read-only user for Satellite
CREATE ROLE chatapi_readonly WITH LOGIN PASSWORD &#39;secure_password&#39;;

-- Grant SELECT on all tables
GRANT SELECT ON ALL TABLES IN SCHEMA public TO chatapi_readonly;

-- Grant future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT ON TABLES TO chatapi_readonly;

-- Optionally restrict sensitive tables
REVOKE SELECT ON AD_User FROM chatapi_readonly;  -- No password hashes
REVOKE SELECT ON AD_UserBPAccess FROM chatapi_readonly;

Sequence Diagram: Chat Request Flow

┌──────────┐  ┌─────────────┐  ┌─────────────┐  ┌──────────┐  ┌─────────┐
│ ZK UI    │  │  Satellite  │  │ LangChain4j │  │ iDempiere│  │ Claude  │
│ (Java11) │  │  (Java17+)  │  │    Agent    │  │ REST API │  │   API   │
└─────┬────┘  └──────┬──────┘  └──────┬──────┘  └─────┬────┘  └────┬────┘
      │              │                 │               │            │
      │ POST /v1/chat│                 │               │            │
      │  + context   │                 │               │            │
      │─────────────&gt;│                 │               │            │
      │              │                 │               │            │
      │              │ Extract context │               │            │
      │              │ Check budget    │               │            │
      │              │────────────────&gt;│               │            │
      │              │                 │ GET /budgets  │            │
      │              │                 │──────────────────────────&gt;│
      │              │                 │ &lt;──────────────────────────
      │              │                 │               │            │
      │              │ Apply guards    │               │            │
      │              │ Route to agent  │               │            │
      │              │────────────────&gt;│               │            │
      │              │                 │               │            │
      │              │                 │ Tool: searchRecords        │
      │              │                 │ GET /api/v1/models/C_Order │
      │              │                 │──────────────────────────&gt;│
      │              │                 │ &lt;──────────────────────────
      │              │                 │               │            │
      │              │                 │ Tool: complexQuery         │
      │              │                 │ Direct SQL (PostgreSQL)    │
      │              │                 │──────────────&gt;│            │
      │              │                 │ &lt;──────────────            │
      │              │                 │               │            │
      │              │                 │ Call Claude                │
      │              │                 │───────────────────────────&gt;│
      │              │                 │ &lt;───────────────────────────
      │              │                 │               │            │
      │              │ &lt;───────────────│               │            │
      │              │                 │               │            │
      │ &lt;────────────│                 │               │            │
      │ JSON response│                 │               │            │
      │              │                 │               │            │
      │              │ Async: Record metrics           │            │
      │              │ POST /api/v1/ai/metrics         │            │
      │              │───────────────────────────────&gt;│            │
      │              │                 │               │            │

Comparison: ADR-046 vs ADR-047

Aspect	ADR-046 (Old)	ADR-047 (This)
Direction	Satellite → iDempiere	iDempiere ↔ Satellite (bidirectional)
Purpose	Persist metrics/audit	AI backend provider
New Endpoints	6+ new iDempiere endpoints	None (reuse existing)
Tool Backend	Not specified	REST + Direct SQL hybrid
RestDataToolLogic	Not mentioned	Reused (already implemented)
SQL Access	Not planned	Direct PostgreSQL (read-only)
Community	bxservice only	bxservice + hengsin patterns

Migration from ADR-046

Changes to CHAT-API-IMPLEMENTATION-ROADMAP.md:

  ### Phase 5: Tool Framework (Week 5)

  **Deliverables:**
  | File | Description | Priority |
  |------|-------------|----------|
- | `DatabaseQueryTool.java` | SQL execution | P0 |
- | `TableMetadataTool.java` | AD metadata | P1 |
+ | `DatabaseQueryTool.java` | Direct SQL (new) | P0 |
+ | `TableMetadataTool.java` | Wraps RestDataToolLogic (existing) | P1 |
+ | `RecordQueryTool.java` | Wraps GeneratedOpenApiFactory (existing) | P0 |

  **Tasks:**
- - [ ] Create `satellite/tool/` package
- - [ ] Define tool interface
- - [ ] Implement CDI registry
- - [ ] Port tool implementations
- - [ ] Integrate with existing `RagService`
+ - [ ] Reuse `RestDataToolLogic` from ADR-015
+ - [ ] Create thin wrappers implementing `ISatelliteTool`
+ - [ ] Add DatabaseQueryTool for direct SQL
+ - [ ] Configure read-only PostgreSQL access

Consequences

Positive

✅ Reuses existing infrastructure - RestDataToolLogic, GeneratedOpenApiFactory
✅ No new iDempiere endpoints required - Use bxservice/hengsin REST API
✅ Hybrid backend - REST for CRUD, SQL for analytics
✅ Community-aligned - Follows hengsin tool patterns
✅ Production-ready - REST API battle-tested in production
✅ Type-safe - Generated OpenAPI client

Negative

⚠️ Direct SQL access - Requires read-only PostgreSQL user
⚠️ Schema coupling - Direct SQL queries depend on schema
⚠️ Network dependency - Satellite needs access to both REST API and PostgreSQL

Neutral

📝 Configuration complexity - Two connection types (REST + SQL)
📝 Security model - Must manage both API tokens and DB credentials

ADR-009: OpenAPI REST Client - GeneratedOpenApiFactory
ADR-014: Heng Sin's idempiere-mcp Evaluation - Tool patterns
ADR-015: REST Data Tools Facade - RestDataToolLogic (EXISTING)
ADR-043: Satellite Service Architecture Evolution
ADR-044: Component Extraction Strategy
ADR-045: idempiere-cli Integration Architecture
~~ADR-046: Satellite-iDempiere REST Integration~~ (superseded by this ADR)

References

ADR-047 | Version 1.0 | 2025-12-11 Status: Proposed Supersedes: ADR-046