ADR-053: Build-Time Model Discovery for Chat API Tools

Status: Proposed Date: 2025-12-12 Context: Fix LLM hallucination of column names in searchRecords() tool Related: ADR-048 (iDempiere AI Hub Integration), ADR-009 (OpenAPI REST Client)

Context and Problem Statement

The Chat API's searchRecords() tool experiences LLM hallucinations when constructing OData filter expressions. The LLM invents column names like "Category eq 'Product'" instead of using actual database column names like "M_Product_Category_ID eq 103".

Root Cause:

• LLM has no knowledge of actual table schemas at inference time
• Tool descriptions only provide generic examples
• LLM guesses column names based on English semantics

Current Workaround (2025-12-12):

• Added getTableColumns() tool that queries database at runtime
• LLM must call this tool first to discover schema
• Adds extra LLM roundtrip and database load

Decision Drivers

1. Eliminate hallucinations - LLM must know actual column names
2. Performance - Avoid runtime database queries for metadata
3. Offline capability - Tools should work without live DB connection
4. Standard formats - Use existing OpenAPI/REST standards
5. Type safety - Generate validated types for tools
6. Security - Respect iDempiere's security model (don't bypass REST API)

Considered Options

Option 1: Runtime Database Queries (Current Implementation)

Query AD_Table and AD_Column tables at runtime via RegistryToolLogic.listColumns().

Pros:

• ✅ Always up-to-date with schema changes
• ✅ Simple implementation (already working)
• ✅ No build-time dependencies

Cons:

• ❌ Requires runtime database connection
• ❌ Extra LLM roundtrip for every new table
• ❌ Database load for metadata queries
• ❌ Doesn't work offline

Option 2: Build-Time Discovery via /models/{tableName}/yaml (Recommended)

Query iDempiere REST API at Maven build time to fetch OpenAPI schemas for all tables.

Pros:

• ✅ Uses official REST API (respects security, tenancy)
• ✅ Standard OpenAPI format (well-documented, type-safe)
• ✅ No runtime queries (metadata embedded in JAR)
• ✅ Works offline
• ✅ LLM gets schema in system prompt (better performance)
• ✅ Endpoint already exists: GET /models/{tableName}/yaml
• ✅ Client already generated: ModelsApi.modelsTableNameYamlGet()

Cons:

• ❌ Requires iDempiere server running at build time
• ❌ Schemas become stale if AD changes after build
• ❌ Larger JAR size (metadata embedded)
• ❌ Build time increases

Option 3: Hybrid Approach

Use build-time discovery as baseline, fallback to runtime queries for missing/updated tables.

Pros:

• ✅ Best of both worlds
• ✅ Handles schema evolution

Cons:

• ❌ Complex implementation
• ❌ Harder to debug (which source is being used?)

Option 4: OData $metadata EDMX Endpoint (RECOMMENDED) ⭐

Use standard OData $metadata endpoint that returns complete EDMX/CSDL document with ALL service metadata.

Pros:

• ✅ Industry standard - OData EDMX is universal metadata format
• ✅ Single request - Returns ALL tables/columns in one call (vs N+1 queries for YAML)
• ✅ More comprehensive - Includes relationships, navigation properties, complex types, functions
• ✅ Better for LLM - Complete schema context in one document
• ✅ Beyond tables - Supports views, functions, actions, not just tables
• ✅ Proven - Used in clde-angular-ecommerce (ai-workspaced branch)
• ✅ Standardized parsing - Many EDMX libraries available
• ✅ Works offline - Metadata embedded in JAR

Cons:

• ⚠️ Different contract - Uses cloudempiere-openapi-v10.yaml (not idempiere-openapi-current.yml)
• ⚠️ EDMX parser - Need to integrate EDMX/CSDL parser library
• ⚠️ Contract source - Located in clde-angular-ecommerce repo (clde-openapi-client folder)

OData EDMX Structure:

<?xml version="1.0" encoding="UTF-8"?>
<edmx:Edmx Version="4.0" xmlns:edmx="http://docs.oasis-open.org/odata/ns/edmx">
  <edmx:DataServices>
    <Schema Namespace="iDempiere" xmlns="http://docs.oasis-open.org/odata/ns/edm">

      <!-- Entity Types (Tables) -->
      <EntityType Name="M_Product">
        <Key>
          <PropertyRef Name="M_Product_ID"/>
        </Key>
        <Property Name="M_Product_ID" Type="Edm.Int32" Nullable="false"/>
        <Property Name="Value" Type="Edm.String" MaxLength="40"/>
        <Property Name="Name" Type="Edm.String" MaxLength="60"/>
        <Property Name="M_Product_Category_ID" Type="Edm.Int32"/>

        <!-- Navigation Properties (Foreign Keys) -->
        <NavigationProperty Name="ProductCategory"
                           Type="iDempiere.M_Product_Category"
                           Partner="Products"/>
      </EntityType>

      <EntityType Name="M_Product_Category">
        <Key>
          <PropertyRef Name="M_Product_Category_ID"/>
        </Key>
        <Property Name="M_Product_Category_ID" Type="Edm.Int32" Nullable="false"/>
        <Property Name="Value" Type="Edm.String"/>
        <Property Name="Name" Type="Edm.String"/>

        <NavigationProperty Name="Products"
                           Type="Collection(iDempiere.M_Product)"
                           Partner="ProductCategory"/>
      </EntityType>

      <!-- Entity Container (Collections) -->
      <EntityContainer Name="Container">
        <EntitySet Name="M_Products" EntityType="iDempiere.M_Product">
          <NavigationPropertyBinding Path="ProductCategory" Target="M_Product_Categories"/>
        </EntitySet>
        <EntitySet Name="M_Product_Categories" EntityType="iDempiere.M_Product_Category">
          <NavigationPropertyBinding Path="Products" Target="M_Products"/>
        </EntitySet>
      </EntityContainer>

    </Schema>
  </edmx:DataServices>
</edmx:Edmx>

Decision

UPDATED 2025-12-12: After analyzing overlap with ADR-008 (Application Dictionary Registry), we've identified a more powerful approach.

Selected Approach: Hybrid ADR-008 + ADR-053 Integration ⭐

Use ADR-008's RegistryToolLogic as the data source for build-time metadata generation instead of REST API.

Rationale:

1. ADR-008 is already proven - RegistryToolLogic is in production (v1.23.0)
2. Already integrated - ChatToolProvider uses registryLogic.listColumns() in the searchRecords fix
3. More comprehensive - Includes relationships, patterns, translations, windows/tabs
4. Simpler implementation - Reuse existing JDBC queries instead of REST API calls
5. No REST API dependency - Direct database access at build time

Implementation Architecture

We will implement a Maven plugin goal that:

1. Connect to iDempiere database via JDBC (same as RegistryToolLogic)
2. Instantiate RegistryToolLogic to query Application Dictionary
3. Extract comprehensive metadata:
   • listTables() - All tables with descriptions
   • describeTable(tableName) - Full table metadata + relationships
   • listColumns(tableName) - Column details with types, lengths, descriptions
   • Pattern detection (document, master-data, transaction-line)
   • Window/Tab usage information
   • Translation support (multi-language names/descriptions)
4. Generate enhanced model-metadata.json with:
   • Tables, columns, types
   • Foreign key relationships
   • Pattern classifications
   • Window/Tab associations
   • Reference type mappings
5. Embed in JAR resources at target/classes/model-metadata.json

Integration with ChatToolProvider:

• Load embedded metadata at @PostConstruct
• Inject schema hints into @Tool descriptions dynamically
• Hybrid approach: embedded metadata first, runtime fallback via RegistryToolLogic
• Generate type-safe parameter validations

See: docs/adr/053-build-time-model-discovery-ADR008-comparison.md for detailed comparison and integration strategy.

Implementation Architecture

UPDATED 2025-12-12: Using RegistryToolLogic (ADR-008) instead of REST API.

┌─────────────────────────────────────────────────────────────────┐
│ Maven Build Phase                                               │
├─────────────────────────────────────────────────────────────────┤
│                                                                  │
│  1. ModelDiscoveryMojo (custom Maven plugin)                    │
│     ├─ Connect to iDempiere database (JDBC)                     │
│     │  URL from ${idempiere.db.url} or config                   │
│     │                                                            │
│     ├─ Instantiate RegistryToolLogic (ADR-008)                  │
│     │  Uses existing production-tested code                     │
│     │                                                            │
│     ├─ Extract comprehensive metadata:                          │
│     │  ├─ registryLogic.listTables(null, false, null)           │
│     │  │  Returns: All active tables with descriptions          │
│     │  │                                                         │
│     │  └─ For each table:                                       │
│     │     ├─ registryLogic.describeTable(tableName)             │
│     │     │  Returns: Metadata + windows/tabs + relationships   │
│     │     │                                                      │
│     │     └─ registryLogic.listColumns(tableName)               │
│     │        Returns: Column names, types, refs, descriptions   │
│     │                                                            │
│     ├─ Detect patterns (document, master-data, transaction)     │
│     │  Based on column presence (DocStatus, Value, Parent_ID)   │
│     │                                                            │
│     ├─ Generate enhanced model-metadata.json                    │
│     │  Structure:                                               │
│     │  {                                                        │
│     │    "generatedAt": "2025-12-12T10:00:00Z",                 │
│     │    "idempiereVersion": "13.0.0",                          │
│     │    "tables": {                                            │
│     │      "M_Product": {                                       │
│     │        "id": 208,                                         │
│     │        "name": "Product",                                 │
│     │        "description": "Product, Service, Item",           │
│     │        "accessLevel": "Client+Organization",              │
│     │        "pattern": "master-data",                          │
│     │        "defaultWindowId": 140,                            │
│     │        "columns": {                                       │
│     │          "M_Product_ID": {                                │
│     │            "id": 3668,                                    │
│     │            "name": "Product",                             │
│     │            "type": "ID",                                  │
│     │            "referenceId": 13,                             │
│     │            "mandatory": true,                             │
│     │            "key": true                                    │
│     │          },                                               │
│     │          "M_Product_Category_ID": {                       │
│     │            "id": 3670,                                    │
│     │            "name": "Product Category",                    │
│     │            "type": "Table Direct",                        │
│     │            "referenceId": 19,                             │
│     │            "mandatory": true,                             │
│     │            "foreignTable": "M_Product_Category"           │
│     │          }                                                │
│     │        },                                                 │
│     │        "windows": [                                       │
│     │          {"id": 140, "name": "Product", "type": "M"}      │
│     │        ]                                                  │
│     │      }                                                    │
│     │    },                                                     │
│     │    "patterns": {                                          │
│     │      "document": ["C_Order", "C_Invoice", ...],           │
│     │      "master-data": ["M_Product", "C_BPartner", ...]      │
│     │    }                                                      │
│     │  }                                                        │
│     │                                                            │
│     └─ Write to target/classes/model-metadata.json              │
│                                                                  │
└─────────────────────────────────────────────────────────────────┘
                            │
                            ↓
┌─────────────────────────────────────────────────────────────────┐
│ Runtime - ChatToolProvider                                       │
├─────────────────────────────────────────────────────────────────┤
│                                                                  │
│  @Inject                                                        │
│  RegistryToolLogic registryLogic;  // For runtime fallback      │
│                                                                  │
│  private Map<String, TableMetadata> embeddedMetadata;           │
│                                                                  │
│  @PostConstruct                                                 │
│  void loadModelMetadata() {                                     │
│    InputStream is = getClass()                                  │
│      .getResourceAsStream("/model-metadata.json");              │
│    this.embeddedMetadata = parseMetadata(is);                   │
│    log.info("Loaded metadata for " + embeddedMetadata.size() + │
│                " tables");                                      │
│  }                                                              │
│                                                                  │
│  @Tool("Search records with OData filter. " +                   │
│        "Available columns for M_Product: " +                    │
│        "M_Product_ID, Value, Name, M_Product_Category_ID, ...")  │
│  String searchRecords(String tableName, String filter) {        │
│    // LLM gets column names directly in tool description!      │
│    // Embedded metadata used for validation                    │
│    return apiFactory.models().modelsTableNameGet(...);          │
│  }                                                              │
│                                                                  │
│  @Tool("Get columns for a table (with embedded metadata)")      │
│  String getTableColumns(String tableName) {                     │
│    // HYBRID APPROACH:                                          │
│    // 1. Try embedded metadata first (fast, offline)           │
│    if (embeddedMetadata.containsKey(tableName)) {              │
│      return toJson(embeddedMetadata.get(tableName));            │
│    }                                                            │
│    // 2. Fallback to runtime query (handles new tables)        │
│    return registryLogic.listColumns(tableName).toJson();        │
│  }                                                              │
│                                                                  │
└─────────────────────────────────────────────────────────────────┘

Configuration

pom.xml

<plugin>
  <groupId>org.idempiere.cli</groupId>
  <artifactId>model-discovery-maven-plugin</artifactId>
  <version>${project.version}</version>
  <executions>
    <execution>
      <id>discover-models</id>
      <phase>generate-resources</phase>
      <goals>
        <goal>discover</goal>
      </goals>
      <configuration>
        <apiUrl>${idempiere.api.url}</apiUrl>
        <apiToken>${idempiere.api.token}</apiToken>
        <outputFile>src/main/resources/model-metadata.json</outputFile>
        <includeTables>
          <!-- Optional: only include specific tables -->
          <table>C_BPartner</table>
          <table>M_Product</table>
        </includeTables>
        <excludeTables>
          <!-- Optional: exclude system tables -->
          <table>AD_*</table>
        </excludeTables>
      </configuration>
    </execution>
  </executions>
</plugin>

Maven Properties

# In ~/.m2/settings.xml or build command
-Didempiere.api.url=http://localhost:8080/api/v1
-Didempiere.api.token=<your-token>

Implementation Steps

Phase 1: Basic Discovery (MVP)

1. Create ModelDiscoveryMojo Maven plugin
2. Implement table listing via /models
3. Fetch YAML schemas via /models/{tableName}/yaml
4. Generate model-metadata.json
5. Load metadata in ChatToolProvider at startup
6. Update @Tool descriptions with column hints

Phase 2: Enhanced Tool Generation

1. Generate dynamic @Tool methods per table
2. Create type-safe parameter classes
3. Add validation based on schema constraints
4. Generate OData filter builders

Phase 3: Incremental Updates

1. Cache schemas with timestamps
2. Only fetch changed tables (compare ETags)
3. Merge runtime discoveries with build-time baseline

Consequences

Positive

• ✅ Eliminates hallucinations - LLM has actual schema
• ✅ Better performance - No runtime metadata queries
• ✅ Offline capability - Works without DB connection
• ✅ Type safety - Generated from OpenAPI schemas
• ✅ Standard approach - Uses official REST API
• ✅ Security compliant - Respects REST API authorization

Negative

• ❌ Stale schemas - Must rebuild when AD changes
• ❌ Build dependency - Requires iDempiere server running
• ❌ JAR size increase - Metadata embedded (estimated +500KB-1MB)
• ❌ CI/CD complexity - Build server needs API access

Neutral

• ⚠️ Build time - Adds 1-5 minutes (one-time per build)
• ⚠️ Cache management - Need strategy for schema freshness
• ⚠️ Multi-tenant - Need to handle per-client schemas

Alternatives Considered and Rejected

Direct Database Access at Build Time

Query AD_Table/AD_Column directly from Maven plugin.

Rejected because:

• Bypasses REST API security
• Requires database credentials in build
• Doesn't respect validation rules or tenant filters
• Breaks iDempiere security model

Hardcoded Schema Definitions

Manually write table/column definitions in Java.

Rejected because:

• Huge maintenance burden
• Immediately stale
• Error-prone
• Doesn't scale to 800+ tables

GraphQL Introspection

Use GraphQL introspection if available.

Rejected because:

• iDempiere REST API is not GraphQL
• Would require separate GraphQL gateway
• Adds complexity

References

OData EDMX Standard

• OData $metadata Endpoint Documentation
• OData CSDL XML Representation v4.01
• Microsoft: Obtain Service Metadata (EDMX) Document
• OData Common Schema Definition Language (CSDL)
• EDMX Schema XSD

CloudEmpiere Implementation

• Repository: clde-angular-ecommerce (nx monorepo)
• Branch: ai-workspaced
• Contract Location: clde-openapi-client/cloudempiere-openapi-v10.yaml
• $metadata Endpoint: Based on EDMX standard, provides comprehensive service metadata
• Current Usage: Production use by Angular e-commerce frontend for OData queries

Standard iDempiere Implementation

• Repository: hengsin/idempiere-rest
• Contract: idempiere-openapi-current.yml (used by this CLI)
• Alternative Endpoint: /models/{tableName}/yaml (per-table schemas)
• iDempiere REST API Models Endpoint
• hengsin's Swagger UI

Related ADRs

• ADR-008: Application Dictionary Registry (FOUNDATION for this ADR)
  • Provides RegistryToolLogic for metadata queries
  • Already integrated into ChatToolProvider
  • Production-proven since v1.23.0
• ADR-009: OpenAPI REST Client
• ADR-048: iDempiere AI Hub Integration Architecture

Integration Documentation

• 053-build-time-model-discovery-ADR008-comparison.md - Comprehensive comparison of ADR-008 vs ADR-053, integration strategy, and unified architecture

Libraries

• Apache Olingo - Java OData library with EDMX parser
• odata4j - Alternative Java OData library

Notes

• OData $metadata endpoint - Already implemented in CloudEmpiere
  • Contract Location: clde-angular-ecommerce repo → clde-openapi-client folder
  • Contract File: cloudempiere-openapi-v10.yaml
  • Active Branch: ai-workspaced
  • Current Usage: Already in production use by Angular e-commerce frontend
• Two Separate Contracts:
  • idempiere-openapi-current.yml - Standard iDempiere REST API (used by this repo)
  • cloudempiere-openapi-v10.yaml - CloudEmpiere extensions with $metadata support
• Fallback option - The /models/{tableName}/yaml endpoint exists in standard iDempiere contract
  • Generated client method modelsTableNameYamlGet() is available
• Discovery trigger - This approach was discovered during fix of searchRecords() hallucination bug (2025-12-12)
• Current interim solution - getTableColumns() runtime tool is in place
• Long-term goal - Build-time EDMX discovery with embedded metadata
• Implementation Path:
  1. Option A (Preferred): Use CloudEmpiere contract
     • Copy cloudempiere-openapi-v10.yaml from clde-angular-ecommerce
     • Generate client with $metadata support
     • Create Maven plugin to call $metadata at build time
  2. Option B (Fallback): Use standard iDempiere contract
     • Use existing /models/{tableName}/yaml endpoint
     • Iterate over all tables (N+1 queries)
     • Slower but works with current contract

Implementation Example

// ModelDiscoveryMojo.java
@Mojo(name = "discover", defaultPhase = LifecyclePhase.GENERATE_RESOURCES)
public class ModelDiscoveryMojo extends AbstractMojo {

    @Parameter(property = "idempiere.api.url", required = true)
    private String apiUrl;

    @Parameter(defaultValue = "${project.build.directory}/generated-resources/model-metadata.json")
    private File outputFile;

    public void execute() throws MojoExecutionException {
        GeneratedOpenApiFactory apiFactory = createApiFactory();

        // 1. List all tables
        String tablesJson = apiFactory.models().modelsGet(...);
        List<String> tables = parseTableNames(tablesJson);

        Map<String, TableMetadata> metadata = new HashMap<>();

        // 2. Fetch schema for each table
        for (String table : tables) {
            String yaml = apiFactory.models().modelsTableNameYamlGet(table);
            TableMetadata tableMeta = parseYamlSchema(yaml);
            metadata.put(table, tableMeta);
        }

        // 3. Write JSON
        objectMapper.writeValue(outputFile, metadata);

        getLog().info("Discovered " + tables.size() + " tables");
    }
}

Success Criteria

1. LLM can query any table without hallucinating column names
2. No runtime database queries for metadata
3. Build completes successfully with valid metadata
4. JAR size increase < 2MB
5. Tool descriptions include actual column names
6. Works offline (no API required at runtime)

Approval Status: Pending Review Reviewers: @norbertbede Implementation Target: v1.65.0