ADR-008 vs ADR-053: Comparison and Integration Strategy

Date: 2025-12-12 Context: Analyzing overlap between existing AD Registry (ADR-008) and proposed Build-Time Discovery (ADR-053)

Executive Summary

Key Finding: ADR-008's RegistryToolLogic is more powerful than ADR-053's REST API approach and should be the foundation for build-time metadata generation.

Recommendation: Integrate ADR-008 and ADR-053 by using RegistryToolLogic as the data source for build-time metadata generation, instead of querying REST API.

Side-by-Side Comparison

Aspect ADR-008 (Registry) ADR-053 (Build-Time Discovery) Winner
Status ✅ Implemented (v1.23.0) ⏸️ Proposed ADR-008
Data Source Direct JDBC → AD tables REST API → /models/{tableName}/yaml or $metadata ADR-008
Metadata Scope Tables, Columns, Windows, Tabs, Processes, References, Elements, Patterns Tables, Columns (from OData schema) ADR-008
Relationships Full traversal (Window→Tab→Field→Column) Limited (OData navigation properties) ADR-008
Patterns Document, master-data, transaction-line Not included ADR-008
Translations Multi-language via _trl tables Not supported ADR-008
Runtime Queries Yes (direct JDBC) No (embedded metadata) ADR-053
Offline Capability No (requires DB connection) Yes (metadata in JAR) ADR-053
Performance Fast (direct DB), runtime cost Zero runtime cost (pre-generated) ADR-053
Freshness Always current Stale after build ADR-008
Build Dependency None Requires iDempiere/DB at build time ADR-008
Security Direct AD access (bypasses REST) Respects REST API security ADR-053
Implementation RegistryToolLogic class Maven plugin + metadata generation ADR-008

Capabilities Comparison

ADR-008 RegistryToolLogic Methods

// Statistics
ToolResult getStatistics()

// Tables
ToolResult listTables(String pattern, boolean customOnly, Integer limit, String language)
ToolResult describeTable(String tableName, String language)
ToolResult listColumns(String tableName)  // ✅ Already used in ChatToolProvider!

// Windows
ToolResult listWindows(String pattern, Integer limit)

// Processes
ToolResult listProcesses(String pattern, Integer limit)

// References
ToolResult listReferences(String pattern, Integer limit)

// Search
ToolResult search(String query)  // Cross-entity SQL search
ToolResult semanticSearch(String query, String sourceType, Integer limit)  // RAG-powered!
ToolResult smartSearch(String query, Integer limit)  // Intelligent strategy selection

// RAG Status
boolean isRagAvailable()
ToolResult getRagStatus()

Additional Features:

ADR-053 Proposed Capabilities

// Maven Plugin (proposed)
ModelDiscoveryMojo.execute()
  - Fetch table list from REST API
  - Download YAML/EDMX schemas
  - Generate model-metadata.json
  - Embed in JAR

// Runtime (proposed)
void loadModelMetadata()  // Load embedded JSON
String getColumnsForTable(String tableName)  // Extract from metadata

Features:

Evidence of ADR-008's Power

Proof: Already Used in ChatToolProvider Fix

The searchRecords() hallucination bug was fixed using ADR-008:

// ChatToolProvider.java (src/main/java/org/idempiere/cli/chatapi/tool/impl/ChatToolProvider.java:71)
@Inject
org.idempiere.cli.ai.shared.RegistryToolLogic registryLogic;

@Tool("Get the column/field names and metadata for an iDempiere table. " +
      "Use this BEFORE searchRecords to discover actual column names. " +
      "Returns column names, types, and descriptions to prevent hallucinations.")
public String getTableColumns(
        @P("Table or model name (e.g., 'M_Product')") String tableName) {
    log.info("Tool: getTableColumns - table=" + tableName);
    org.idempiere.cli.ai.shared.ToolResult result = registryLogic.listColumns(tableName);
    return result.toJson();
}

This proves:

  1. ADR-008 already provides exactly what ADR-053 needs
  2. The integration is trivial (already working!)
  3. No REST API needed

What RegistryToolLogic.listColumns() Returns

SELECT c.ad_column_id, c.columnname, c.name, c.description,
       r.name as reference_name, c.fieldlength, c.ismandatory, c.iskey
FROM ad_column c
JOIN ad_table t ON c.ad_table_id = t.ad_table_id
LEFT JOIN ad_reference r ON c.ad_reference_id = r.ad_reference_id
WHERE t.tablename = ?
AND c.isactive='Y'
ORDER BY c.seqno, c.columnname

Result:

{
  "success": true,
  "message": "Found 45 column(s) for M_Product",
  "data": {
    "table": "M_Product",
    "count": 45,
    "columns": [
      {
        "id": 3668,
        "columnName": "M_Product_ID",
        "name": "Product",
        "description": "Product, Service, Item",
        "reference": "ID",
        "length": 10,
        "mandatory": true,
        "key": true
      },
      {
        "id": 3670,
        "columnName": "M_Product_Category_ID",
        "name": "Product Category",
        "description": "Category of a Product",
        "reference": "Table Direct",
        "length": 10,
        "mandatory": true,
        "key": false
      }
      // ... 43 more columns
    ]
  }
}

This is EXACTLY what LLM needs to prevent hallucinations!

ADR-008's Advantages Over REST API

1. Comprehensive Metadata

ADR-008 (Direct JDBC):

describeTable("M_Product", "de_DE")
// Returns:
// - Table metadata (ID, name, description, help, flags)
// - ALL columns (45 fields with types, lengths, descriptions)
// - Default window (ID, name, description)
// - ALL windows/tabs using this table (with parent/child relationships)
// - Column count, access level, entity type
// - Localized German translations
// - Hints and related tables (via ADContextService)

ADR-053 (REST API):

GET /models/M_Product/yaml
// Returns:
// - Table schema (columns with types)
// - Limited metadata (no window info, no relationships, no translations)

2. Pattern Recognition

ADR-008 knows:

ADR-053: No pattern awareness

3. Relationship Traversal

ADR-008 provides:

{
  "table": "M_Product",
  "windowsAndTabs": [
    {
      "windowId": 140,
      "windowName": "Product",
      "tabId": 180,
      "tabName": "Product",
      "tabLevel": 0,
      "isReadOnly": false
    },
    {
      "windowId": 143,
      "windowName": "Product Info",
      "tabId": 495,
      "tabName": "Product",
      "tabLevel": 0,
      "linkColumn": "M_Product_ID",
      "parentTabName": "Business Partner"
    }
  ],
  "usedInWindowCount": 2
}

ADR-053: Not available

4. Semantic Search (RAG Integration)

ADR-008 has:

semanticSearch("tables related to sales", null, 10)
// Uses vector embeddings to find C_Order, C_OrderLine, M_InOut, etc.
// Even without exact keyword matches!

ADR-053: No semantic capability

Integration Strategy: Best of Both Worlds

Proposed Unified Architecture

┌─────────────────────────────────────────────────────────────────┐
│                     Maven Build Phase                            │
├─────────────────────────────────────────────────────────────────┤
│                                                                  │
│  ModelDiscoveryMojo (custom Maven plugin)                       │
│  ├─ Connect to iDempiere database (JDBC)                        │
│  │  Uses: ${idempiere.db.url} from config                       │
│  │                                                               │
│  ├─ Instantiate RegistryToolLogic                               │
│  │  (reuse existing ADR-008 infrastructure!)                    │
│  │                                                               │
│  ├─ Extract comprehensive metadata:                             │
│  │  └─ registryLogic.listTables(null, false, null)              │
│  │     For each table:                                          │
│  │       ├─ registryLogic.describeTable(tableName)              │
│  │       ├─ registryLogic.listColumns(tableName)                │
│  │       └─ Get windows/tabs, patterns, relationships           │
│  │                                                               │
│  ├─ 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,                                       │
│  │            "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": "Maintain"}  │
│  │        ]                                                     │
│  │      }                                                       │
│  │    },                                                        │
│  │    "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 queries       │
│                                                                  │
│  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 (runtime fallback)")            │
│  String getTableColumns(String tableName) {                     │
│    // First check embedded metadata                            │
│    if (embeddedMetadata.containsKey(tableName)) {              │
│      return embeddedMetadata.get(tableName).toJson();           │
│    }                                                            │
│    // Fallback to runtime query (handles new tables)           │
│    return registryLogic.listColumns(tableName).toJson();        │
│  }                                                              │
│                                                                  │
└─────────────────────────────────────────────────────────────────┘

Implementation Steps

Phase 1: Maven Plugin (Build-Time)

  1. Create ModelDiscoveryMojo in new Maven plugin module
  2. Inject RegistryToolLogic via CDI or direct instantiation
  3. Extract metadata for all tables using ADR-008 methods
  4. Generate comprehensive model-metadata.json
  5. Copy to target/classes/ for JAR embedding

Phase 2: Runtime Integration (ChatToolProvider)

  1. Load embedded metadata at @PostConstruct
  2. Inject column names into @Tool descriptions dynamically
  3. Keep RegistryToolLogic injection for runtime fallback
  4. Implement hybrid approach: embedded first, runtime fallback

Phase 3: Advanced Features

  1. Generate per-table @Tool methods from metadata
  2. Create OData filter builders with column validation
  3. Add pattern-based tool suggestions
  4. Implement metadata refresh command

Benefits of Unified Approach

✅ Combines Strengths

Feature Source
Comprehensive metadata ADR-008 (RegistryToolLogic)
Offline capability ADR-053 (embedded JSON)
Relationship traversal ADR-008
Zero runtime queries ADR-053
Pattern recognition ADR-008
Type safety ADR-053
Semantic search ADR-008 (RAG)
Standards-based ADR-053 (OData)

✅ Eliminates Weaknesses

Weakness How Unified Approach Fixes It
ADR-008: Requires runtime DB Embed metadata at build time
ADR-053: REST API overhead Use direct JDBC via RegistryToolLogic
ADR-053: Limited metadata Use ADR-008's comprehensive queries
ADR-008: Runtime cost Pre-generate and embed
ADR-053: Build dependency Use existing DB config (already needed)

✅ Reuses Proven Infrastructure

Migration Path

Current State (After searchRecords Fix)

// Runtime queries for every table lookup
@Tool("Get table columns...")
public String getTableColumns(String tableName) {
    return registryLogic.listColumns(tableName).toJson();
}

Step 1: Add Build-Time Generation

<!-- pom.xml -->
<plugin>
  <artifactId>model-discovery-maven-plugin</artifactId>
  <executions>
    <execution>
      <phase>generate-resources</phase>
      <goals><goal>discover</goal></goals>
    </execution>
  </executions>
</plugin>

Step 2: Load Embedded Metadata

@PostConstruct
void init() {
    loadEmbeddedMetadata();  // From JAR
    // Keep registryLogic for fallback
}

Step 3: Hybrid Queries

@Tool("Get table columns...")
public String getTableColumns(String tableName) {
    // Try embedded first (fast, offline)
    if (metadata.containsKey(tableName)) {
        return metadata.get(tableName).toJson();
    }
    // Fallback to runtime (handles new tables)
    return registryLogic.listColumns(tableName).toJson();
}

Step 4: Dynamic Tool Descriptions

// Generate tool description with actual column names
String toolDesc = "Search " + tableName + " records. " +
                  "Available columns: " +
                  String.join(", ", metadata.get(tableName).getColumnNames());

Recommendation

Update ADR-053 to specify:

  1. Data Source: Use RegistryToolLogic (ADR-008) instead of REST API
  2. Build Plugin: Call RegistryToolLogic methods directly via JDBC
  3. Metadata Format: Enhanced JSON including patterns, relationships, windows
  4. Runtime Strategy: Hybrid (embedded first, runtime fallback)
  5. Integration Point: ChatToolProvider already uses RegistryToolLogic

Benefits:

Action Items:

  1. Update ADR-053 with integration strategy
  2. Create ModelDiscoveryMojo plugin
  3. Generate metadata at build time
  4. Update ChatToolProvider to load embedded metadata
  5. Document hybrid approach in ADR-053

Conclusion

ADR-008 is not overlapping with ADR-053 — it's the FOUNDATION for ADR-053!

Instead of querying REST API at build time, we should:

  1. Use RegistryToolLogic to extract comprehensive AD metadata
  2. Generate enhanced model-metadata.json at Maven build time
  3. Embed in JAR for offline capability
  4. Load at runtime to inject into LLM tool descriptions
  5. Keep runtime fallback for schema evolution

This unified approach gives us:

Next Step: Update ADR-053 to document this integration strategy.

Path: /docs/developers/architecture/idempiere-hub/053-build-time-model-discovery-ADR008-comparison