ADR-039: Code-to-Knowledge Extraction for Consultant Use

<!-- MADR 3.0 Template - Markdown Any Decision Records -->

Status

Proposed

Date

2025-12-11

Deciders

• Norbert Bede

Context and Problem Statement

CloudEmpiere consultants need to understand iDempiere behavior to:

1. Support customers - Answer "why did X happen?" questions
2. Train new consultants - Explain how the software works
3. Build knowledge bases - Create internal documentation following templates
4. Implement customizations - Understand existing behavior before extending

Currently, understanding software behavior requires:

• Reading Java source code (requires developer skills)
• Trial and error in the system
• Asking developers (bottleneck)
• Outdated documentation

We need a system that extracts knowledge from code and presents it in consultant-friendly language, then allows building structured knowledge bases from templates.

Decision Drivers

• Non-Developer Users: Consultants are ERP experts, not Java developers
• Business Language: Output must use business terms, not technical jargon
• Template-Based: Knowledge bases follow defined organizational templates
• Self-Service: Consultants can query without developer assistance
• Multi-Purpose: Same knowledge serves training, support, documentation
• Accuracy: Information must reflect actual code behavior, not assumptions

Considered Options

1. Manual documentation - Developers write documentation manually
2. AI-only analysis - Send code to LLM without structure
3. Structured extraction + AI transformation - Parse code, extract patterns, transform to business language

Decision Outcome

Chosen option: "Structured extraction + AI transformation", because it combines accurate code analysis with AI-powered natural language generation that consultants can understand.

Confirmation

• [ ] Consultant can query "What happens when I change QtyOrdered?" and get business-language answer
• [ ] Knowledge base templates can be populated from extracted knowledge
• [ ] No Java/technical terms in consultant-facing output
• [ ] Extracted knowledge matches actual system behavior

Architecture

Three-Stage Pipeline

═══════════════════════════════════════════════════════════════════════════════
 STAGE 1: CODE EXTRACTION (Technical - runs once)
═══════════════════════════════════════════════════════════════════════════════

 Input: iDempiere Java Source Code
 ┌─────────────────────────────────────────────────────────────────────────┐
 │ /idempiere/org.adempiere.base/src/org/compiere/model/MOrderLine.java   │
 └─────────────────────────────────────────────────────────────────────────┘
                                   │
                                   ▼
 ┌─────────────────────────────────────────────────────────────────────────┐
 │ JavaParser AST Analysis                                                 │
 │                                                                         │
 │ Extract:                                                                │
 │ ├── Class: MOrderLine                                                   │
 │ ├── Table: C_OrderLine                                                  │
 │ ├── Method: beforeSave()                                                │
 │ │   ├── Validation: QtyOrdered.signum() <= 0 → throws                  │
 │ │   ├── Calculation: LineNetAmt = QtyOrdered * PriceActual             │
 │ │   └── Trigger: parent.setGrandTotal() when LineNetAmt changes        │
 │ └── Method: afterSave()                                                 │
 │     └── Trigger: M_StorageReservation update                           │
 └─────────────────────────────────────────────────────────────────────────┘
                                   │
                                   ▼
 Output: Structured Technical Facts (JSON)
 ┌─────────────────────────────────────────────────────────────────────────┐
 │ {                                                                       │
 │   "entity": "Order Line",                                               │
 │   "table": "C_OrderLine",                                               │
 │   "behaviors": [                                                        │
 │     {                                                                   │
 │       "trigger": "before_save",                                         │
 │       "field": "QtyOrdered",                                            │
 │       "type": "validation",                                             │
 │       "condition": "value <= 0",                                        │
 │       "result": "error",                                                │
 │       "code_ref": "MOrderLine.java:245"                                 │
 │     },                                                                  │
 │     {                                                                   │
 │       "trigger": "before_save",                                         │
 │       "field": "LineNetAmt",                                            │
 │       "type": "calculation",                                            │
 │       "formula": "QtyOrdered * PriceActual",                            │
 │       "code_ref": "MOrderLine.java:252"                                 │
 │     }                                                                   │
 │   ]                                                                     │
 │ }                                                                       │
 └─────────────────────────────────────────────────────────────────────────┘

═══════════════════════════════════════════════════════════════════════════════
 STAGE 2: KNOWLEDGE TRANSFORMATION (AI-Powered)
═══════════════════════════════════════════════════════════════════════════════

 Input: Structured Technical Facts
                                   │
                                   ▼
 ┌─────────────────────────────────────────────────────────────────────────┐
 │ LangChain4j AI Service: KnowledgeTransformer                           │
 │                                                                         │
 │ @SystemMessage("""                                                      │
 │   You are translating technical software behavior into business         │
 │   language for ERP consultants.                                         │
 │                                                                         │
 │   Rules:                                                                │
 │   - NEVER use Java terms (method, class, exception, null)              │
 │   - NEVER mention code files or line numbers                           │
 │   - USE business terms (field, record, document, save)                 │
 │   - EXPLAIN in terms of user actions and system responses              │
 │   - FOCUS on "what happens" not "how it's coded"                       │
 │ """)                                                                    │
 │                                                                         │
 │ Transform:                                                              │
 │   "QtyOrdered.signum() <= 0 → throws AdempiereException"               │
 │   ↓                                                                     │
 │   "The Ordered Quantity must be greater than zero. If you enter        │
 │    zero or a negative number, the system will show an error and        │
 │    prevent saving the order line."                                     │
 └─────────────────────────────────────────────────────────────────────────┘
                                   │
                                   ▼
 Output: Business-Language Knowledge (stored in Vector DB)
 ┌─────────────────────────────────────────────────────────────────────────┐
 │ {                                                                       │
 │   "source_type": "code_knowledge",                                      │
 │   "entity": "Order Line",                                               │
 │   "field": "Ordered Quantity",                                          │
 │   "topic": "validation",                                                │
 │   "question": "What happens if I enter zero or negative quantity?",    │
 │   "answer": "The system prevents saving and shows an error. The        │
 │              Ordered Quantity must be a positive number.",              │
 │   "context": "Sales Order entry"                                        │
 │ }                                                                       │
 └─────────────────────────────────────────────────────────────────────────┘

═══════════════════════════════════════════════════════════════════════════════
 STAGE 3: KNOWLEDGE BASE GENERATION (Template-Based)
═══════════════════════════════════════════════════════════════════════════════

 Input: Business-Language Knowledge + Templates
                                   │
                                   ▼
 ┌─────────────────────────────────────────────────────────────────────────┐
 │ Template: Functional Documentation                                      │
 │ ────────────────────────────────────────────────────────────────────── │
 │ # {Entity Name}                                                         │
 │                                                                         │
 │ ## Overview                                                             │
 │ {entity_description}                                                    │
 │                                                                         │
 │ ## Fields                                                               │
 │ {#for field in fields}                                                  │
 │ ### {field.name}                                                        │
 │ {field.description}                                                     │
 │                                                                         │
 │ **Validation Rules:**                                                   │
 │ {#for rule in field.validations}                                        │
 │ - {rule}                                                                │
 │ {/for}                                                                  │
 │                                                                         │
 │ **Automatic Calculations:**                                             │
 │ {#for calc in field.calculations}                                       │
 │ - {calc}                                                                │
 │ {/for}                                                                  │
 │ {/for}                                                                  │
 │                                                                         │
 │ ## Business Rules                                                       │
 │ {#for rule in business_rules}                                           │
 │ - {rule}                                                                │
 │ {/for}                                                                  │
 └─────────────────────────────────────────────────────────────────────────┘
                                   │
                                   ▼
 Output: Structured Knowledge Base Document
 ┌─────────────────────────────────────────────────────────────────────────┐
 │ # Order Line                                                            │
 │                                                                         │
 │ ## Overview                                                             │
 │ An Order Line represents a single item on a Sales Order, containing    │
 │ product, quantity, and pricing information.                             │
 │                                                                         │
 │ ## Fields                                                               │
 │                                                                         │
 │ ### Ordered Quantity                                                    │
 │ The number of units being ordered.                                      │
 │                                                                         │
 │ **Validation Rules:**                                                   │
 │ - Must be greater than zero                                             │
 │ - Cannot be changed after the order is completed                        │
 │                                                                         │
 │ **Automatic Calculations:**                                             │
 │ - When changed, recalculates the Line Net Amount                        │
 │ - Triggers inventory reservation update                                 │
 │                                                                         │
 │ ### Line Net Amount                                                     │
 │ The total amount for this line before taxes.                            │
 │                                                                         │
 │ **Automatic Calculations:**                                             │
 │ - Calculated as: Ordered Quantity × Actual Price                        │
 │ - When changed, updates the Order Grand Total                           │
 │                                                                         │
 │ ## Business Rules                                                       │
 │ - Quantity changes on completed orders require re-opening the document │
 │ - Price changes cascade to update line and order totals                │
 └─────────────────────────────────────────────────────────────────────────┘

Consultant Query Flow

┌─────────────────────────────────────────────────────────────────────────────┐
│ CONSULTANT QUERY                                                             │
├─────────────────────────────────────────────────────────────────────────────┤
│                                                                              │
│ Consultant: "Why can't I change the quantity on my order?"                  │
│                                                                              │
│                        │                                                     │
│                        ▼                                                     │
│ ┌────────────────────────────────────────────────────────────────────────┐  │
│ │ 1. RAG Search (Vector DB)                                              │  │
│ │    Query: "change quantity order"                                      │  │
│ │    Filter: source_type = "code_knowledge"                              │  │
│ │                                                                         │  │
│ │    Matches:                                                            │  │
│ │    - "Quantity cannot be changed after document is completed"         │  │
│ │    - "Quantity field is read-only when order is processed"            │  │
│ │    - "Re-open document to modify quantities"                          │  │
│ └────────────────────────────────────────────────────────────────────────┘  │
│                        │                                                     │
│                        ▼                                                     │
│ ┌────────────────────────────────────────────────────────────────────────┐  │
│ │ 2. AI Response Generation                                              │  │
│ │                                                                         │  │
│ │    Based on the retrieved knowledge, generates consultant-friendly    │  │
│ │    answer combining relevant facts.                                    │  │
│ └────────────────────────────────────────────────────────────────────────┘  │
│                        │                                                     │
│                        ▼                                                     │
│                                                                              │
│ Answer: "The quantity cannot be changed because your order has already      │
│          been completed. Once a document is completed, quantity fields      │
│          become read-only to maintain data integrity.                       │
│                                                                              │
│          To modify the quantity, you need to:                               │
│          1. Re-open the order using the 'Re-Open' button                   │
│          2. Make your quantity changes                                      │
│          3. Complete the order again                                        │
│                                                                              │
│          Note: Re-opening may affect related shipments or invoices."        │
│                                                                              │
└─────────────────────────────────────────────────────────────────────────────┘

Knowledge Base Templates

Template Types

Template Structure

# templates/functional-spec.yaml
name: Functional Specification
description: Detailed functional documentation for an entity
sections:
  - name: overview
    title: Overview
    prompt: "Describe what {entity} is and its business purpose"

  - name: fields
    title: Fields
    for_each: field
    subsections:
      - name: description
        prompt: "Describe the {field} field in business terms"
      - name: validations
        title: Validation Rules
        source: field.validations
      - name: calculations
        title: Automatic Calculations
        source: field.calculations
      - name: dependencies
        title: Related Fields
        source: field.triggers

  - name: document_flow
    title: Document Flow
    condition: entity.is_document
    prompt: "Describe the document workflow: draft → complete → void"

  - name: business_rules
    title: Business Rules
    source: entity.rules
    format: bullet_list

  - name: common_issues
    title: Common Issues
    prompt: "List common problems and solutions for {entity}"

  - name: related_entities
    title: Related Entities
    source: entity.relationships

Template: Support FAQ

# templates/support-faq.yaml
name: Support FAQ
description: Frequently asked questions for support team
format: qa_pairs
sections:
  - name: validation_errors
    title: Validation Errors
    generate_from: field.validations
    question_pattern: "Why do I get an error when {action}?"
    answer_pattern: "{validation_explanation}. {how_to_fix}"

  - name: read_only_fields
    title: Read-Only Fields
    generate_from: field.read_only_conditions
    question_pattern: "Why can't I change {field}?"
    answer_pattern: "{field} is read-only because {reason}. {workaround}"

  - name: automatic_changes
    title: Automatic Changes
    generate_from: field.calculations
    question_pattern: "Why did {field} change automatically?"
    answer_pattern: "{field} is calculated based on {formula}. {explanation}"

  - name: missing_values
    title: Missing Values
    generate_from: field.mandatory_conditions
    question_pattern: "Why is {field} required?"
    answer_pattern: "{field} is required for {business_reason}."

CLI Commands

Extraction Commands

# Extract knowledge from iDempiere source code
idempiere-cli knowledge extract --source /path/to/idempiere --output ./knowledge

# Extract specific entity
idempiere-cli knowledge extract --entity C_OrderLine --output ./knowledge

# Extract with specific focus
idempiere-cli knowledge extract --focus validations --output ./knowledge
idempiere-cli knowledge extract --focus calculations --output ./knowledge

Query Commands

# Ask a question (consultant mode)
idempiere-cli knowledge ask "Why can't I change the quantity?"

# Ask about specific entity
idempiere-cli knowledge ask "How is line amount calculated?" --entity "Order Line"

# Interactive Q&A mode
idempiere-cli knowledge chat

Knowledge Base Generation

# Generate functional spec for an entity
idempiere-cli knowledge generate --template functional-spec --entity "Order Line"

# Generate support FAQ for a module
idempiere-cli knowledge generate --template support-faq --module "Sales"

# Generate training guide
idempiere-cli knowledge generate --template training-guide --entity "Sales Order"

# Generate all documentation for a module
idempiere-cli knowledge generate --template all --module "Sales" --output ./docs

Template Management

# List available templates
idempiere-cli knowledge templates

# Create custom template
idempiere-cli knowledge template create --name "my-template" --base functional-spec

# Validate template
idempiere-cli knowledge template validate ./templates/my-template.yaml

LangChain4j Integration

KnowledgeExtractor AI Service

@RegisterAiService(
    tools = {
        CodeAnalysisTools.class,
        RagTools.class
    }
)
public interface KnowledgeExtractor {

    @SystemMessage("""
        You are translating Java code behavior into business knowledge
        for ERP consultants who are NOT developers.

        STRICT RULES:
        1. NEVER use: method, class, exception, null, throws, boolean, void
        2. NEVER mention: file names, line numbers, package names
        3. NEVER use: technical jargon, programming terms
        4. ALWAYS use: field, record, document, save, error message
        5. ALWAYS explain: what the USER experiences, not what CODE does
        6. ALWAYS use: present tense, active voice

        Transform technical facts into:
        - What happens when user does X
        - Why user sees error Y
        - How system calculates Z
        """)
    @UserMessage("""
        Transform this technical behavior into consultant knowledge:

        Entity: {entity}
        Field: {field}
        Technical Fact: {technicalFact}
        Code Reference: {codeRef}

        Generate:
        1. A clear question a consultant might ask
        2. A business-friendly answer (no technical terms)
        3. Any relevant workarounds or tips
        """)
    ConsultantKnowledge transformToKnowledge(
        String entity,
        String field,
        String technicalFact,
        String codeRef
    );
}

record ConsultantKnowledge(
    String question,
    String answer,
    List<String> tips,
    String relatedTopic
) {}

KnowledgeBaseGenerator AI Service

@RegisterAiService(
    tools = { RagTools.class }
)
public interface KnowledgeBaseGenerator {

    @SystemMessage("""
        You are generating structured documentation from extracted knowledge.
        Follow the provided template exactly.
        Use only business language appropriate for ERP consultants.
        """)
    @UserMessage("""
        Generate documentation using this template:

        Template: {templateYaml}

        Entity: {entity}
        Available Knowledge: {knowledgeJson}

        Fill in all template sections with relevant content.
        If information is not available, note "To be documented".
        """)
    String generateFromTemplate(
        String templateYaml,
        String entity,
        String knowledgeJson
    );
}

Storage Schema

Vector DB: Consultant Knowledge

-- Embedded consultant-friendly knowledge
CREATE TABLE rag_embeddings (
    id UUID PRIMARY KEY,
    embedding VECTOR(1536),
    content TEXT,
    metadata JSONB
);

-- Metadata structure for code_knowledge source_type:
{
    "source_type": "code_knowledge",
    "entity": "Order Line",
    "entity_table": "C_OrderLine",
    "field": "Ordered Quantity",
    "field_column": "QtyOrdered",
    "topic": "validation",  -- validation, calculation, trigger, read_only
    "question": "What happens if I enter zero quantity?",
    "answer": "The system shows an error and prevents saving...",
    "module": "Sales",
    "language": "en_US",
    "extracted_from": "MOrderLine.java",  -- for traceability, not shown to user
    "extraction_date": "2025-12-11"
}

Relational: Knowledge Base Articles

-- Generated knowledge base articles
CREATE TABLE ce_knowledge_article (
    ce_knowledge_article_id SERIAL PRIMARY KEY,
    ad_client_id INT NOT NULL,
    ad_org_id INT NOT NULL,
    entity_name VARCHAR(100),
    template_name VARCHAR(60),
    title VARCHAR(255),
    content TEXT,
    module VARCHAR(60),
    language VARCHAR(6),
    version INT DEFAULT 1,
    is_published CHAR(1) DEFAULT 'N',
    created TIMESTAMP,
    updated TIMESTAMP
);

Implementation Plan

Phase 1: Code Extraction Engine

1. Implement JavaCodeExtractor using JavaParser
2. Extract: validations, calculations, triggers, read-only conditions
3. Output structured JSON for each entity
4. Store raw technical facts in database

Phase 2: Knowledge Transformation

1. Implement KnowledgeTransformer AI Service
2. Transform technical facts → consultant questions/answers
3. Store in Vector DB with business-language content
4. Enable RAG search

Phase 3: Query Interface

1. Add knowledge ask CLI command
2. Implement RAG-based Q&A
3. Add interactive chat mode
4. Support entity/module filtering

Phase 4: Knowledge Base Generation

1. Implement template system (YAML-based)
2. Create standard templates (functional-spec, FAQ, training)
3. Add knowledge generate CLI command
4. Support custom templates

Phase 5: Integration

1. Connect to K_Entry for publishing to iDempiere KB
2. Export to Markdown/HTML/PDF
3. Integration with Mattermost for support bot
4. Scheduled re-extraction for updated knowledge

More Information

Example: Technical → Consultant Transformation

Technical Input (from code):

if (getQtyOrdered().signum() <= 0)
    throw new AdempiereException("@QtyOrdered@ <= 0");

Intermediate JSON:

{
  "entity": "Order Line",
  "field": "QtyOrdered",
  "type": "validation",
  "condition": "value <= 0",
  "result": "throws_exception",
  "message_key": "@QtyOrdered@ <= 0"
}

Consultant Knowledge:

Question: What happens if I enter zero or negative quantity on an order line?

Answer: The system will display an error and prevent you from saving the
order line. The Ordered Quantity must be a positive number greater than zero.

Tips:
- Check for typos if you accidentally entered a negative sign
- If you need to cancel a line, use the Delete function instead of setting
  quantity to zero

SQL Layer Knowledge Extraction

Why SQL Layer Matters

Java code analysis captures application-level validations and calculations, but significant business logic resides in the database layer:

Two Sources of SQL Knowledge

┌─────────────────────────────────────────────────────────────────────────────┐
│ SOURCE 1: LIVE DATABASE SCHEMA                                              │
├─────────────────────────────────────────────────────────────────────────────┤
│                                                                              │
│ Query information_schema and pg_catalog to extract:                         │
│                                                                              │
│ ┌────────────────────────────────────────────────────────────────────────┐  │
│ │ Tables & Columns                                                       │  │
│ │ ├── Column names, types, nullable                                      │  │
│ │ ├── Default values (constant or expression)                            │  │
│ │ └── Column comments                                                    │  │
│ ├────────────────────────────────────────────────────────────────────────┤  │
│ │ Constraints                                                            │  │
│ │ ├── CHECK constraints (business rules!)                                │  │
│ │ ├── UNIQUE constraints                                                 │  │
│ │ ├── FOREIGN KEY relationships                                          │  │
│ │ └── PRIMARY KEY                                                        │  │
│ ├────────────────────────────────────────────────────────────────────────┤  │
│ │ Triggers                                                               │  │
│ │ ├── BEFORE/AFTER INSERT/UPDATE/DELETE                                  │  │
│ │ └── Trigger functions and their logic                                  │  │
│ ├────────────────────────────────────────────────────────────────────────┤  │
│ │ Functions & Procedures                                                  │  │
│ │ ├── bomqtyonhand() - BOM quantity calculations                         │  │
│ │ ├── currencyrate() - Currency conversion                               │  │
│ │ └── Custom business functions                                          │  │
│ └────────────────────────────────────────────────────────────────────────┘  │
│                                                                              │
└─────────────────────────────────────────────────────────────────────────────┘

┌─────────────────────────────────────────────────────────────────────────────┐
│ SOURCE 2: MIGRATION SCRIPTS                                                  │
├─────────────────────────────────────────────────────────────────────────────┤
│                                                                              │
│ Parse SQL migration files from:                                              │
│ - idempiere/migration/iDX.X/postgresql/*.sql                                │
│ - Custom plugin migrations                                                   │
│                                                                              │
│ Extract:                                                                     │
│ ┌────────────────────────────────────────────────────────────────────────┐  │
│ │ Schema Changes                                                         │  │
│ │ ├── ADD COLUMN (new fields)                                            │  │
│ │ ├── ALTER COLUMN (type/constraint changes)                             │  │
│ │ ├── DROP COLUMN (removed fields)                                       │  │
│ │ └── ADD CONSTRAINT (new business rules)                                │  │
│ ├────────────────────────────────────────────────────────────────────────┤  │
│ │ Data Changes (from INSERT statements)                                  │  │
│ │ ├── AD_Element inserts (field definitions)                             │  │
│ │ ├── AD_Column inserts (column metadata)                                │  │
│ │ ├── AD_Reference inserts (dropdown values)                             │  │
│ │ └── AD_Message inserts (error messages)                                │  │
│ ├────────────────────────────────────────────────────────────────────────┤  │
│ │ Version Context                                                        │  │
│ │ ├── When feature was introduced                                        │  │
│ │ ├── What version deprecated something                                  │  │
│ │ └── Historical evolution of an entity                                  │  │
│ └────────────────────────────────────────────────────────────────────────┘  │
│                                                                              │
└─────────────────────────────────────────────────────────────────────────────┘

SQL Knowledge Extraction Architecture

┌─────────────────────────────────────────────────────────────────────────────┐
│ SQL KNOWLEDGE EXTRACTION PIPELINE                                            │
├─────────────────────────────────────────────────────────────────────────────┤
│                                                                              │
│ ┌──────────────────┐     ┌──────────────────┐     ┌───────────────────┐     │
│ │ SchemaIngestor   │     │ MigrationIngestor│     │ SQLFunctionIngestor│    │
│ │                  │     │                  │     │                   │     │
│ │ Live DB schema   │     │ .sql files       │     │ PL/pgSQL          │     │
│ │ via JDBC         │     │ parsing          │     │ analysis          │     │
│ └────────┬─────────┘     └────────┬─────────┘     └─────────┬─────────┘     │
│          │                        │                        │                │
│          └────────────────────────┼────────────────────────┘                │
│                                   │                                         │
│                                   ▼                                         │
│          ┌─────────────────────────────────────────────────┐                │
│          │ Structured SQL Facts (JSON)                     │                │
│          │                                                 │                │
│          │ {                                               │                │
│          │   "table": "C_OrderLine",                       │                │
│          │   "constraint": "c_orderline_qtyordered_check", │                │
│          │   "type": "CHECK",                              │                │
│          │   "expression": "(qtyordered > 0)",             │                │
│          │   "columns": ["qtyordered"],                    │                │
│          │   "enforces": "positive_quantity"               │                │
│          │ }                                               │                │
│          └───────────────────────┬─────────────────────────┘                │
│                                  │                                          │
│                                  ▼                                          │
│          ┌─────────────────────────────────────────────────┐                │
│          │ AI Transformation                                │                │
│          │                                                 │                │
│          │ Technical: "CHECK (qtyordered > 0)"             │                │
│          │ ↓                                               │                │
│          │ Business: "The Ordered Quantity must be         │                │
│          │           greater than zero. This is enforced   │                │
│          │           at the database level and cannot be   │                │
│          │           bypassed."                            │                │
│          └───────────────────────┬─────────────────────────┘                │
│                                  │                                          │
│                                  ▼                                          │
│          ┌─────────────────────────────────────────────────┐                │
│          │ Vector DB Storage                                │                │
│          │                                                 │                │
│          │ source_type: "sql_knowledge"                    │                │
│          │ entity: "Order Line"                            │                │
│          │ field: "Ordered Quantity"                       │                │
│          │ knowledge_type: "constraint"                    │                │
│          │ enforcement_level: "database"                   │                │
│          └─────────────────────────────────────────────────┘                │
│                                                                              │
└─────────────────────────────────────────────────────────────────────────────┘

SchemaIngestor Implementation

@ApplicationScoped
public class SchemaIngestor implements KnowledgeIngestor {

    @Override
    public String getSourceType() {
        return "sql_schema";
    }

    /**
     * Extract knowledge from live database schema.
     */
    public int ingest(EmbeddingStore<TextSegment> store, EmbeddingModel model) {
        int count = 0;
        try (Connection conn = dataSource.getConnection()) {
            count += extractConstraints(conn, store, model);
            count += extractDefaults(conn, store, model);
            count += extractForeignKeys(conn, store, model);
            count += extractTriggers(conn, store, model);
            count += extractFunctions(conn, store, model);
        }
        return count;
    }

    /**
     * Extract CHECK constraints as business rules.
     */
    private int extractConstraints(Connection conn,
                                    EmbeddingStore<TextSegment> store,
                                    EmbeddingModel model) {
        String sql = """
            SELECT
                tc.table_name,
                cc.constraint_name,
                cc.check_clause,
                ARRAY_AGG(ccu.column_name) as columns
            FROM information_schema.table_constraints tc
            JOIN information_schema.check_constraints cc
                ON tc.constraint_name = cc.constraint_name
            JOIN information_schema.constraint_column_usage ccu
                ON tc.constraint_name = ccu.constraint_name
            WHERE tc.constraint_type = 'CHECK'
              AND tc.table_schema = 'adempiere'
              AND tc.table_name NOT LIKE 'ad_%'  -- Skip AD tables (metadata)
            GROUP BY tc.table_name, cc.constraint_name, cc.check_clause
            """;

        int count = 0;
        try (PreparedStatement stmt = conn.prepareStatement(sql);
             ResultSet rs = stmt.executeQuery()) {

            while (rs.next()) {
                String tableName = rs.getString("table_name");
                String constraintName = rs.getString("constraint_name");
                String checkClause = rs.getString("check_clause");
                Array columnsArray = rs.getArray("columns");
                String[] columns = (String[]) columnsArray.getArray();

                // Build metadata
                Map<String, Object> metadata = Map.of(
                    "source_type", "sql_knowledge",
                    "knowledge_type", "constraint",
                    "table", tableName,
                    "constraint_name", constraintName,
                    "columns", String.join(",", columns),
                    "enforcement_level", "database",
                    "raw_expression", checkClause
                );

                // Build content for embedding
                String content = String.format(
                    "Table %s has constraint %s: %s. Affected columns: %s",
                    humanizeTableName(tableName),
                    constraintName,
                    humanizeCheckClause(checkClause),
                    String.join(", ", humanizeColumns(columns))
                );

                TextSegment segment = TextSegment.from(content, new Metadata(metadata));
                Embedding embedding = model.embed(segment).content();
                store.add(embedding, segment);
                count++;
            }
        }
        return count;
    }

    /**
     * Extract column defaults that reveal automatic behavior.
     */
    private int extractDefaults(Connection conn,
                                 EmbeddingStore<TextSegment> store,
                                 EmbeddingModel model) {
        String sql = """
            SELECT
                c.table_name,
                c.column_name,
                c.column_default,
                c.is_nullable,
                c.data_type,
                pgd.description as column_comment
            FROM information_schema.columns c
            LEFT JOIN pg_catalog.pg_description pgd
                ON pgd.objoid = (c.table_schema || '.' || c.table_name)::regclass
               AND pgd.objsubid = c.ordinal_position
            WHERE c.table_schema = 'adempiere'
              AND c.column_default IS NOT NULL
              AND c.column_default NOT LIKE 'nextval%'  -- Skip sequences
            """;

        // Process defaults like:
        // - DEFAULT CURRENT_TIMESTAMP → "Created timestamp is set automatically"
        // - DEFAULT 'Y' → "This flag defaults to Yes"
        // - DEFAULT (expression) → "Calculated automatically as..."
        // ...
    }

    /**
     * Extract foreign key relationships.
     */
    private int extractForeignKeys(Connection conn,
                                    EmbeddingStore<TextSegment> store,
                                    EmbeddingModel model) {
        String sql = """
            SELECT
                kcu.table_name as child_table,
                kcu.column_name as child_column,
                ccu.table_name as parent_table,
                ccu.column_name as parent_column,
                rc.delete_rule,
                rc.update_rule
            FROM information_schema.key_column_usage kcu
            JOIN information_schema.constraint_column_usage ccu
                ON kcu.constraint_name = ccu.constraint_name
            JOIN information_schema.referential_constraints rc
                ON kcu.constraint_name = rc.constraint_name
            WHERE kcu.table_schema = 'adempiere'
            """;

        // Generates knowledge like:
        // "Order Line is linked to Sales Order. If you delete an Order,
        //  all its Order Lines will also be deleted (CASCADE)."
        // ...
    }
}

MigrationScriptIngestor Implementation

@ApplicationScoped
public class MigrationScriptIngestor implements KnowledgeIngestor {

    @Override
    public String getSourceType() {
        return "sql_migration";
    }

    /**
     * Parse migration SQL files to extract schema evolution.
     */
    public int ingest(Path migrationsPath,
                       EmbeddingStore<TextSegment> store,
                       EmbeddingModel model) {
        int count = 0;

        // Process files like: i9.0/postgresql/202301011200_IDEMPIERE-5678.sql
        try (Stream<Path> files = Files.walk(migrationsPath)) {
            List<Path> sqlFiles = files
                .filter(p -> p.toString().endsWith(".sql"))
                .sorted()
                .toList();

            for (Path sqlFile : sqlFiles) {
                count += processMigrationFile(sqlFile, store, model);
            }
        }
        return count;
    }

    private int processMigrationFile(Path sqlFile,
                                      EmbeddingStore<TextSegment> store,
                                      EmbeddingModel model) {
        String content = Files.readString(sqlFile);
        String fileName = sqlFile.getFileName().toString();

        // Extract version from path: i9.0/postgresql/...
        String version = extractVersion(sqlFile);

        // Parse SQL statements
        List<SqlStatement> statements = parseSqlStatements(content);

        int count = 0;
        for (SqlStatement stmt : statements) {
            if (isKnowledgeRelevant(stmt)) {
                Map<String, Object> metadata = Map.of(
                    "source_type", "sql_migration",
                    "version", version,
                    "file", fileName,
                    "statement_type", stmt.type(),
                    "table", stmt.tableName(),
                    "column", stmt.columnName() != null ? stmt.columnName() : ""
                );

                String knowledgeContent = generateMigrationKnowledge(stmt, version);

                TextSegment segment = TextSegment.from(knowledgeContent, new Metadata(metadata));
                Embedding embedding = model.embed(segment).content();
                store.add(embedding, segment);
                count++;
            }
        }
        return count;
    }

    /**
     * Determine if a SQL statement contains knowledge worth extracting.
     */
    private boolean isKnowledgeRelevant(SqlStatement stmt) {
        return switch (stmt.type()) {
            case "ALTER TABLE ADD COLUMN" -> true;     // New field
            case "ALTER TABLE ADD CONSTRAINT" -> true; // New rule
            case "CREATE TRIGGER" -> true;             // Automation
            case "CREATE FUNCTION" -> true;            // Business logic
            case "INSERT INTO AD_Element" -> true;     // Field definition
            case "INSERT INTO AD_Reference" -> true;   // Dropdown values
            case "INSERT INTO AD_Message" -> true;     // Error messages
            default -> false;
        };
    }

    /**
     * Generate consultant-friendly knowledge from migration statement.
     */
    private String generateMigrationKnowledge(SqlStatement stmt, String version) {
        return switch (stmt.type()) {
            case "ALTER TABLE ADD COLUMN" -> String.format(
                "In version %s, a new field '%s' was added to %s. %s",
                version,
                humanize(stmt.columnName()),
                humanize(stmt.tableName()),
                describeColumn(stmt)
            );
            case "ALTER TABLE ADD CONSTRAINT" -> String.format(
                "In version %s, a new validation rule was added to %s: %s",
                version,
                humanize(stmt.tableName()),
                humanizeConstraint(stmt.expression())
            );
            // ... other cases
            default -> "";
        };
    }

    record SqlStatement(
        String type,
        String tableName,
        String columnName,
        String expression,
        String rawSql
    ) {}
}

SQLFunctionIngestor: Database Business Logic

@ApplicationScoped
public class SQLFunctionIngestor implements KnowledgeIngestor {

    @Override
    public String getSourceType() {
        return "sql_function";
    }

    /**
     * Extract knowledge from PostgreSQL functions.
     */
    public int ingest(EmbeddingStore<TextSegment> store, EmbeddingModel model) {
        String sql = """
            SELECT
                p.proname as function_name,
                pg_catalog.pg_get_function_arguments(p.oid) as arguments,
                pg_catalog.pg_get_function_result(p.oid) as return_type,
                pg_catalog.pg_get_functiondef(p.oid) as definition,
                d.description
            FROM pg_catalog.pg_proc p
            JOIN pg_catalog.pg_namespace n ON p.pronamespace = n.oid
            LEFT JOIN pg_catalog.pg_description d ON p.oid = d.objoid
            WHERE n.nspname = 'adempiere'
              AND p.prokind = 'f'  -- functions only
              AND p.proname NOT LIKE 'uuid_%'  -- Skip utility functions
            """;

        // Key iDempiere functions to analyze:
        // - bomqtyonhand() - BOM available quantity
        // - bomqtyreserved() - BOM reserved quantity
        // - bomqtyordered() - BOM ordered quantity
        // - currencyconvert() - Currency conversion
        // - paymenttermdue() - Payment due date calculation
        // - invoiceopen() - Open invoice amount
        // - productattribute() - Product attribute handling

        // Generate knowledge like:
        // "The BOM Quantity on Hand is calculated by the database function
        //  bomqtyonhand(). It considers all components of a Bill of Materials
        //  and returns the maximum quantity that can be produced based on
        //  available component stock."
    }
}

Combining Java and SQL Knowledge

┌─────────────────────────────────────────────────────────────────────────────┐
│ COMPLETE KNOWLEDGE PICTURE: Order Line Quantity                              │
├─────────────────────────────────────────────────────────────────────────────┤
│                                                                              │
│ Java Layer (MOrderLine.java):                                                │
│ ┌────────────────────────────────────────────────────────────────────────┐  │
│ │ beforeSave(): if (getQtyOrdered().signum() <= 0) throw exception      │  │
│ │ → "Application validates quantity is positive before saving"           │  │
│ └────────────────────────────────────────────────────────────────────────┘  │
│                                                                              │
│ SQL Layer (c_orderline):                                                     │
│ ┌────────────────────────────────────────────────────────────────────────┐  │
│ │ CHECK (qtyordered > 0)                                                 │  │
│ │ → "Database also enforces positive quantity as a safety constraint"    │  │
│ │                                                                         │  │
│ │ FOREIGN KEY (c_order_id) REFERENCES c_order ON DELETE CASCADE         │  │
│ │ → "Deleting an order automatically removes all its lines"              │  │
│ │                                                                         │  │
│ │ DEFAULT 0 for qtydelivered, qtyinvoiced                                │  │
│ │ → "Delivered and Invoiced quantities start at zero"                    │  │
│ └────────────────────────────────────────────────────────────────────────┘  │
│                                                                              │
│ Migration Layer (i9.0):                                                      │
│ ┌────────────────────────────────────────────────────────────────────────┐  │
│ │ ALTER TABLE C_OrderLine ADD QtyEntered NUMERIC(10,2)                   │  │
│ │ → "In version 9.0, Entered Quantity was added to support UOM           │  │
│ │    conversion separate from Ordered Quantity"                          │  │
│ └────────────────────────────────────────────────────────────────────────┘  │
│                                                                              │
│ Combined Consultant Knowledge:                                               │
│ ┌────────────────────────────────────────────────────────────────────────┐  │
│ │ Q: What validations apply to Order Line quantity?                      │  │
│ │                                                                         │  │
│ │ A: The Ordered Quantity has multiple levels of validation:             │  │
│ │    1. Must be greater than zero (enforced by both application and     │  │
│ │       database - this cannot be bypassed)                              │  │
│ │    2. Delivered and Invoiced quantities start at zero and are         │  │
│ │       updated when shipments/invoices are created                      │  │
│ │    3. If you need to track quantities in a different unit of measure, │  │
│ │       use the Entered Quantity field (added in version 9.0)           │  │
│ │                                                                         │  │
│ │ Related: If you delete a Sales Order, all its Order Lines are         │  │
│ │ automatically deleted by the database.                                 │  │
│ └────────────────────────────────────────────────────────────────────────┘  │
│                                                                              │
└─────────────────────────────────────────────────────────────────────────────┘

CLI Commands for SQL Extraction

# Extract knowledge from live database schema
idempiere-cli knowledge extract --source db-schema

# Extract knowledge from migration scripts
idempiere-cli knowledge extract --source migrations --path /path/to/idempiere/migration

# Extract specific table's SQL knowledge
idempiere-cli knowledge extract --source db-schema --table C_OrderLine

# Extract all SQL knowledge (schema + migrations + functions)
idempiere-cli knowledge extract --source sql-all --migrations /path/to/migrations

# Combine Java + SQL extraction for complete picture
idempiere-cli knowledge extract \
  --source java --java-path /path/to/idempiere/src \
  --source sql-all --migrations /path/to/migrations \
  --output ./complete-knowledge

Vector DB Metadata for SQL Knowledge

-- Metadata structure for sql_knowledge source_type:
{
    "source_type": "sql_knowledge",
    "knowledge_type": "constraint|default|foreign_key|trigger|function",
    "enforcement_level": "database",      -- Important: cannot be bypassed
    "entity": "Order Line",
    "entity_table": "C_OrderLine",
    "field": "Ordered Quantity",
    "field_column": "QtyOrdered",
    "expression": "(qtyordered > 0)",     -- Raw SQL for traceability
    "version_introduced": "8.2",          -- If from migration
    "migration_file": "202301011200_IDEMPIERE-5678.sql",
    "language": "en_US"
}

Migration Script Registry: Timeline of Changes

The AD_MigrationScript table is a timeline registry that tracks when every database change was applied:

-- AD_MigrationScript: Timeline registry for schema evolution
SELECT
    name,                    -- Script filename (e.g., "202301011200_IDEMPIERE-5678.sql")
    projectname,             -- iDempiere version (e.g., "iDempiere 9.0")
    description,             -- What this migration does
    developername,           -- Who authored it
    releaseno,               -- Release number
    created                  -- When applied to this database
FROM ad_migrationscript
WHERE isapply = 'Y'
ORDER BY name;

Why This Matters for Consultants:

Timeline Knowledge Extraction:

@ApplicationScoped
public class MigrationRegistryIngestor implements KnowledgeIngestor {

    @Override
    public String getSourceType() {
        return "migration_registry";
    }

    /**
     * Extract timeline knowledge from AD_MigrationScript.
     */
    public int ingest(EmbeddingStore<TextSegment> store, EmbeddingModel model) {
        String sql = """
            SELECT
                ms.name as script_name,
                ms.projectname as version,
                ms.description,
                ms.releaseno,
                ms.created as applied_date
            FROM ad_migrationscript ms
            WHERE ms.isapply = 'Y'
              AND ms.isactive = 'Y'
            ORDER BY ms.name
            """;

        // Extract version from script name: 202301011200_IDEMPIERE-5678.sql
        // Parse JIRA ticket: IDEMPIERE-5678 → can link to issue tracker
        // Extract affected table from description or script content

        // Generate knowledge like:
        // "The QtyEntered field was added to Order Line in iDempiere 9.0
        //  (January 2023, IDEMPIERE-5678) to support UOM conversion."
    }
}

Consultant-Facing Timeline Query:

# Find when a field was added
idempiere-cli knowledge timeline --field "QtyEntered" --table "C_OrderLine"

# Output:
# QtyEntered was added in iDempiere 9.0 (IDEMPIERE-5678)
# Applied: January 2023
# Purpose: Support UOM conversion separate from Ordered Quantity
# Your version: 12.0 ✓ (field available)

# Check version compatibility
idempiere-cli knowledge timeline --version 10 --table "C_BPartner"

# Output:
# Changes to Business Partner since version 10:
# - v11: Added C_BPartner_UU (UUID support)
# - v12: Added IsManufacturer flag
# - v12: Added Logo_ID for partner logos

Knowledge Dimensions

Knowledge extraction operates across multiple dimensions that consultants need:

┌─────────────────────────────────────────────────────────────────────────────┐
│ KNOWLEDGE DIMENSIONS                                                         │
├─────────────────────────────────────────────────────────────────────────────┤
│                                                                              │
│ 1. TECHNICAL LAYER                                                           │
│ ┌────────────────────────────────────────────────────────────────────────┐  │
│ │ • Field validations (Java beforeSave, SQL constraints)                 │  │
│ │ • Calculations (formulas, defaults, triggers)                          │  │
│ │ • Data types and constraints                                           │  │
│ │ • API behavior and error codes                                         │  │
│ └────────────────────────────────────────────────────────────────────────┘  │
│                                                                              │
│ 2. BUSINESS VERTICALS                                                        │
│ ┌────────────────────────────────────────────────────────────────────────┐  │
│ │ Industry-specific knowledge:                                           │  │
│ │ • Manufacturing: BOM, MRP, production planning                         │  │
│ │ • Retail: POS, promotions, loyalty                                     │  │
│ │ • Distribution: warehousing, logistics, routing                        │  │
│ │ • Services: projects, timesheets, contracts                            │  │
│ │                                                                         │  │
│ │ Vertical-specific terminology and workflows                            │  │
│ └────────────────────────────────────────────────────────────────────────┘  │
│                                                                              │
│ 3. SINGLE PROCESS FLOW                                                       │
│ ┌────────────────────────────────────────────────────────────────────────┐  │
│ │ End-to-end flow of one document:                                       │  │
│ │                                                                         │  │
│ │ Sales Order Process:                                                   │  │
│ │ ┌─────────┐   ┌─────────┐   ┌─────────┐   ┌─────────┐   ┌─────────┐   │  │
│ │ │ Draft   │──▶│ In      │──▶│Complete │──▶│ Shipped │──▶│ Invoiced│   │  │
│ │ │         │   │ Progress│   │         │   │         │   │         │   │  │
│ │ └─────────┘   └─────────┘   └─────────┘   └─────────┘   └─────────┘   │  │
│ │                                                                         │  │
│ │ What happens at each step? What can/cannot be changed?                 │  │
│ └────────────────────────────────────────────────────────────────────────┘  │
│                                                                              │
│ 4. MULTI-PROCESS INTEGRATION                                                 │
│ ┌────────────────────────────────────────────────────────────────────────┐  │
│ │ Parallel processes working together:                                   │  │
│ │                                                                         │  │
│ │ ┌─────────────┐                                                        │  │
│ │ │ Sales Order │──┬──▶ Shipment ──▶ Delivery Confirmation               │  │
│ │ └─────────────┘  │                                                     │  │
│ │                  ├──▶ Invoice ──▶ Payment Receipt                      │  │
│ │                  │                                                     │  │
│ │                  └──▶ Reservation ──▶ Stock Movement                   │  │
│ │                                                                         │  │
│ │ How do processes interact? What triggers what?                         │  │
│ │ What happens if one fails while others succeed?                        │  │
│ └────────────────────────────────────────────────────────────────────────┘  │
│                                                                              │
│ 5. MODULE ORCHESTRATION                                                      │
│ ┌────────────────────────────────────────────────────────────────────────┐  │
│ │ Cross-module dependencies:                                             │  │
│ │                                                                         │  │
│ │ ┌──────────┐    ┌──────────┐    ┌──────────┐    ┌──────────┐          │  │
│ │ │  Sales   │◀──▶│Inventory │◀──▶│Purchasing│◀──▶│  Finance │          │  │
│ │ └──────────┘    └──────────┘    └──────────┘    └──────────┘          │  │
│ │       │              │              │              │                   │  │
│ │       └──────────────┴──────────────┴──────────────┘                   │  │
│ │                          │                                             │  │
│ │                   ┌──────────┐                                         │  │
│ │                   │Production│                                         │  │
│ │                   └──────────┘                                         │  │
│ │                                                                         │  │
│ │ "If I change a product price, what else is affected?"                  │  │
│ │ "Can I close the period if there are open orders?"                     │  │
│ └────────────────────────────────────────────────────────────────────────┘  │
│                                                                              │
└─────────────────────────────────────────────────────────────────────────────┘

Knowledge Templates by Dimension:

Extracting Process Integration Knowledge:

/**
 * Extract knowledge about how processes interact.
 * Sources: DocAction implementations, event handlers, model validators
 */
@ApplicationScoped
public class ProcessIntegrationIngestor implements KnowledgeIngestor {

    @Override
    public String getSourceType() {
        return "process_integration";
    }

    /**
     * Analyze what happens when a document is completed.
     * Extracts from: completeIt(), afterSave triggers, event handlers
     */
    public ProcessIntegration analyzeDocumentCompletion(String tableName) {
        // From MOrder.completeIt():
        // - Creates M_InOut (shipment) if auto-ship enabled
        // - Creates C_Invoice if auto-invoice enabled
        // - Updates M_StorageReservation
        // - Fires EVENT_AFTER_COMPLETE event

        // From event handlers:
        // - Credit check may block completion
        // - Notification sent to warehouse
        // - Approval workflow may be triggered

        return new ProcessIntegration(
            tableName,
            List.of("M_InOut", "C_Invoice", "M_StorageReservation"),
            List.of("credit_check", "warehouse_notification", "approval_workflow")
        );
    }
}

record ProcessIntegration(
    String sourceDocument,
    List<String> generatedDocuments,
    List<String> triggeredProcesses
) {}

CLI Commands for Process Knowledge:

# Single process flow
idempiere-cli knowledge process --document "Sales Order"

# Output:
# Sales Order Process Flow:
# 1. Draft: Create order, add lines, modify freely
# 2. In Progress: Reserved inventory, limited changes
# 3. Complete: Triggers shipment and/or invoice creation
# 4. Close: No further documents can be generated
#
# Reversible actions: Re-Open (if no shipments/invoices)

# Multi-process integration
idempiere-cli knowledge integration --trigger "Order Complete"

# Output:
# When Sales Order is completed:
# ├── Inventory: Creates reservation for ordered quantities
# ├── Shipping: Creates shipment (if auto-ship enabled)
# ├── Billing: Creates invoice (if auto-invoice enabled)
# ├── Credit: Checks customer credit limit
# └── Notifications: Sends to warehouse manager
#
# Failure handling:
# - Credit check failure: Order stays In Progress
# - Inventory shortage: Partial reservation, backorder created

# Module impact analysis
idempiere-cli knowledge impact --change "Product Price" --module "Sales"

# Output:
# Changing Product Price affects:
# ├── Open Quotes: Prices NOT automatically updated (manual refresh needed)
# ├── Draft Orders: Prices updated on next line save
# ├── Completed Orders: No change (historical price preserved)
# ├── Price Lists: Base price updated, date-effective versions unchanged
# └── Contracts: Dependent on contract terms (fixed vs. variable pricing)

Key Insight: Enforcement Levels

For consultants, it's critical to understand where a rule is enforced:

This helps consultants give accurate answers:

• "Can we import orders with zero quantity?" → "No, the database will reject them"
• "Can we delete a customer with open orders?" → "No, the foreign key constraint prevents this"

Related ADRs

• ADR-038 - AI-Assisted AD Documentation
• ADR-021 - RAG Architecture
• ADR-013 - LangChain4j Integration

References

• JavaParser - Java source code parsing
• LangChain4j - AI integration framework
• iDempiere Wiki - Official documentation
• PostgreSQL information_schema - Schema introspection
• iDempiere Database Functions - iDempiere DB functions