ADR-021: RAG Architecture for Knowledge-Augmented AI Assistance

Status

Implemented

Date

2025-12-07

Deciders

Development Team

Context and Problem Statement

The idempiere-cli AI assistant (ADR-013) currently relies solely on the LLM's training data for iDempiere knowledge. This has limitations:

Outdated Knowledge: LLM training cutoffs may miss recent iDempiere features
Incomplete Coverage: Not all wiki pages, KB articles are in training data
No Internal Knowledge: CloudEmpiere-specific K_Entry articles are proprietary
No AD Context: Application Dictionary metadata (tables, windows, processes) not available

We need Retrieval-Augmented Generation (RAG) to provide the AI with up-to-date, contextual knowledge from multiple sources.

Decision Drivers

Accuracy: AI responses should reference actual documentation and metadata
Freshness: Knowledge should be updatable without retraining models
Privacy: CloudEmpiere internal knowledge (K_Entry) must stay internal
Cost Efficiency: Local embeddings avoid per-query API costs
Performance: Sub-second retrieval for interactive CLI use
Consistency: Use existing PostgreSQL infrastructure when possible

Considered Options

PGVector + Local Embeddings (bge-small-en-q) - PostgreSQL vector extension with in-process ONNX model
Qdrant Cloud - Managed vector database with API-based embeddings
ChromaDB + OpenAI Embeddings - Lightweight local store with cloud embeddings
Pinecone - Fully managed serverless vector database

Decision Outcome

Chosen option: "PGVector + Local Embeddings (bge-small-en-q)", because:

Reuses existing PostgreSQL infrastructure (no additional database)
Local ONNX model means zero API costs for embeddings
All data stays on-premise (important for CloudEmpiere KB)
LangChain4j provides excellent pgvector integration

Confirmation

The decision is confirmed when:

knowledge ingest successfully embeds all sources
knowledge search returns relevant results with <1s latency
ask command uses RAG context for improved answers
Knowledge base can be refreshed without affecting existing data

Pros and Cons of the Options

Option 1: PGVector + Local Embeddings (chosen)

PostgreSQL vector extension with bge-small-en-q (384 dimensions, ~25MB ONNX model).

Good, because reuses existing PostgreSQL (no new infrastructure)
Good, because zero embedding API costs (local model)
Good, because data stays on-premise
Good, because LangChain4j has native pgvector support
Good, because 384-dimension vectors are storage-efficient
Neutral, because requires pgvector extension installed
Bad, because embedding quality may be lower than cloud models

Option 2: Qdrant Cloud

Managed vector database with cloud-based embeddings.

Good, because managed infrastructure
Good, because high-quality embeddings
Bad, because adds external dependency
Bad, because per-query costs for embeddings
Bad, because data leaves on-premise environment

Option 3: ChromaDB + OpenAI Embeddings

Lightweight local vector store with cloud embeddings.

Good, because simple local setup
Good, because high-quality OpenAI embeddings
Bad, because per-query embedding costs
Bad, because ChromaDB less mature than pgvector
Bad, because separate process/database

Option 4: Pinecone

Fully managed serverless vector database.

Good, because zero infrastructure management
Good, because automatic scaling
Bad, because highest cost option
Bad, because data leaves on-premise
Bad, because vendor lock-in

Architecture

┌─────────────────────────────────────────────────────────────────────┐
│                        CLI / AI Agent                                │
│  ┌─────────────┐  ┌─────────────┐  ┌─────────────┐                  │
│  │ knowledge   │  │   ask       │  │ MCP Server  │                  │
│  │   command   │  │  command    │  │  (future)   │                  │
│  └──────┬──────┘  └──────┬──────┘  └──────┬──────┘                  │
│         │                │                │                          │
│         ▼                ▼                ▼                          │
│  ┌──────────────────────────────────────────────┐                   │
│  │              RagService (Facade)              │                   │
│  │  - ingest()  - search()  - getStatistics()   │                   │
│  └──────────────────────────────────────────────┘                   │
│         │                │                │                          │
│         ▼                ▼                ▼                          │
│  ┌────────────┐  ┌────────────┐  ┌────────────────┐                 │
│  │   Wiki     │  │  K_Entry   │  │  AD Metadata   │                 │
│  │  Ingestor  │  │  Ingestor  │  │   Ingestor     │                 │
│  └──────┬─────┘  └──────┬─────┘  └───────┬────────┘                 │
│         │                │                │                          │
│         └────────────────┼────────────────┘                          │
│                          ▼                                           │
│  ┌──────────────────────────────────────────────┐                   │
│  │       EmbeddingStoreProvider (PGVector)       │                   │
│  │  ┌──────────────────────────────────────┐    │                   │
│  │  │      EmbeddingModelProvider          │    │                   │
│  │  │   (bge-small-en-q, 384 dimensions)   │    │                   │
│  │  └──────────────────────────────────────┘    │                   │
│  └──────────────────────────────────────────────┘                   │
│                          │                                           │
└──────────────────────────┼───────────────────────────────────────────┘
                           ▼
┌──────────────────────────────────────────────────┐
│          PostgreSQL + pgvector                    │
│  ┌────────────────────────────────────────────┐  │
│  │          cli_embeddings table              │  │
│  │  - embedding_id (UUID)                     │  │
│  │  - text_segment (TEXT)                     │  │
│  │  - embedding (vector(384))                 │  │
│  │  - metadata (JSONB)                        │  │
│  │  - source_type (VARCHAR)                   │  │
│  │  - source_id (VARCHAR)                     │  │
│  └────────────────────────────────────────────┘  │
└──────────────────────────────────────────────────┘

Knowledge Sources

1. iDempiere Wiki (`source_type: wiki`)

External documentation from wiki.idempiere.org using MediaWiki API.

Discovery: Uses MediaWiki API (/w-en/api.php) for intelligent page discovery:

Discovers pages from configured categories (Developer, New_Features)
Fetches revision timestamps for incremental updates
Skips unchanged pages (session-scoped cache)

Default categories:

Developer - Development guides and tutorials
New_Features - iDempiere version feature documentation

Ingestion: MediaWiki API discovery → HTTP fetch → Apache Tika (HTML→text) → Document splitter → Embeddings

Configuration:

idempiere.cli.rag.wiki.enabled=true
idempiere.cli.rag.wiki.api-url=https://wiki.idempiere.org/w-en/api.php
idempiere.cli.rag.wiki.base-url=https://wiki.idempiere.org/en/
idempiere.cli.rag.wiki.discover-pages=true
idempiere.cli.rag.wiki.categories=Developer,New_Features

2. CloudEmpiere K_Entry (`source_type: k_entry`)

Internal knowledge base articles (AD_Client_ID = 1000014).

SQL Query:

SELECT k_entry_id, name, keywords, textmsg, descriptionurl
FROM k_entry
WHERE ad_client_id = 1000014 AND isactive = &#39;Y&#39;

Document format: {Name}\n{Keywords}\n{TextMsg}

3. Application Dictionary (`source_type: ad_table, ad_window, ad_process, ad_tab, ad_field`)

Metadata about iDempiere tables, windows, processes, tabs, and fields.

Source types:

ad_table: Table definitions with column summaries
ad_window: Window definitions with tab summaries
ad_process: Process/Report definitions with parameters
ad_tab: Tab help text (context within windows)
ad_field: Field help text (DISTINCT by columnname)

Note: AD_Element ingestion is disabled. AD_Field with DISTINCT ON columnname provides better context-specific help without duplicates.

Examples:

ad_table: "Table C_BPartner: Business Partner. Columns: Name, Value, TaxID..."
ad_window: "Window Business Partner: Manage customers and vendors. Tabs: Partner, Location..."
ad_process: "Process Complete Document: Completes the document workflow..."
ad_tab: "Tab Order Line: Line items for sales order. Window: Sales Order..."
ad_field: "Field C_BPartner_ID: Business Partner. Window: Sales Order. Tab: Order..."

Configuration:

idempiere.cli.rag.ad-metadata.enabled=true
# Multiple languages can be ingested in same cycle (default: en_US,sk_SK,hu_HU)
idempiere.cli.rag.ad-metadata.languages=en_US,sk_SK,hu_HU

Translation support: Uses iDempiere's _Trl tables with COALESCE(trl.column, base.column) pattern for fallback to base language.

CLI Commands

# Initialize database (creates pgvector extension + embeddings table)
idempiere-cli knowledge init
idempiere-cli knowledge init --force  # Drop and recreate table

# Ingest all knowledge sources
idempiere-cli knowledge ingest --source all

# Ingest specific source
idempiere-cli knowledge ingest --source wiki
idempiere-cli knowledge ingest --source k_entry
idempiere-cli knowledge ingest --source ad_metadata

# Force re-ingestion (clear existing first)
idempiere-cli knowledge ingest --source all --force

# Search knowledge base
idempiere-cli knowledge search &quot;how to create a callout&quot;
idempiere-cli knowledge search &quot;invoice process&quot; --source k_entry

# Show statistics
idempiere-cli knowledge status

# Validate sources
idempiere-cli knowledge validate

# Clear embeddings
idempiere-cli knowledge clear --source wiki -y

LangChain4j Tools

The following @Tool methods are exposed to the AI agent:

Tool	Description
`searchKnowledge(query, sourceFilter)`	Semantic search across all knowledge
`getKnowledgeStats()`	Get embedding counts by source
`findADEntity(name, type)`	Look up specific AD table/window/process
`lookupDevelopmentDocs(topic)`	Find development documentation

Configuration

Environment Variables

The CLI uses TWO separate PostgreSQL databases:

# 1. iDempiere Database (IDEMPIERE_DB_*) - Application data
export IDEMPIERE_DB_HOST=localhost      # PostgreSQL host
export IDEMPIERE_DB_PORT=5433           # PostgreSQL port (iDempiere default)
export IDEMPIERE_DB_NAME=idempiere      # Database name
export IDEMPIERE_DB_USER=adempiere      # Database user
export IDEMPIERE_DB_PASSWORD=adempiere  # Database password

# 2. RAG Vector Database (RAG_VECTORDB_PG_*) - Semantic search embeddings
#    NOTE: &quot;PG&quot; suffix clarifies this is PostgreSQL with pgvector extension
export RAG_VECTORDB_PG_HOST=localhost   # PostgreSQL host
export RAG_VECTORDB_PG_PORT=5432        # PostgreSQL port
export RAG_VECTORDB_PG_NAME=vector      # Database name
export RAG_VECTORDB_PG_USER=postgres    # Database user
export RAG_VECTORDB_PG_PASSWORD=postgres # Database password

Application Properties

# Embedding Store (PGVector)
idempiere.cli.rag.embedding.table=cli_embeddings
idempiere.cli.rag.embedding.dimension=1024
idempiere.cli.rag.embedding.create-table=true

# Wiki Ingestion (MediaWiki API)
idempiere.cli.rag.wiki.enabled=true
idempiere.cli.rag.wiki.api-url=https://wiki.idempiere.org/w-en/api.php
idempiere.cli.rag.wiki.base-url=https://wiki.idempiere.org/en/
idempiere.cli.rag.wiki.discover-pages=true
idempiere.cli.rag.wiki.categories=Developer,New_Features

# K_Entry Ingestion
idempiere.cli.rag.k-entry.enabled=true
idempiere.cli.rag.k-entry.client-id=1000014

# AD Metadata Ingestion
idempiere.cli.rag.ad-metadata.enabled=true

# Retrieval Settings
idempiere.cli.rag.retrieval.max-results=5
idempiere.cli.rag.retrieval.min-score=0.7

# Document Splitting
idempiere.cli.rag.splitter.max-segment-size=500
idempiere.cli.rag.splitter.max-overlap-size=50

Database Schema

-- Enable pgvector extension
CREATE EXTENSION IF NOT EXISTS vector;

-- Embeddings table (auto-created if create-table=true)
CREATE TABLE IF NOT EXISTS cli_embeddings (
    embedding_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    text_segment TEXT NOT NULL,
    embedding vector(384),
    metadata JSONB,
    source_type VARCHAR(50) NOT NULL,
    source_id VARCHAR(255),
    created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Indexes for efficient retrieval
CREATE INDEX idx_cli_embeddings_vector ON cli_embeddings
    USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);
CREATE INDEX idx_cli_embeddings_source ON cli_embeddings (source_type, source_id);

Prerequisites

PostgreSQL with pgvector: Target database must have pgvector extension
Network Access: For wiki ingestion, requires internet access to wiki.idempiere.org
Database Access: For K_Entry, requires access to CloudEmpiere database

Dependencies Added

&lt;!-- pom.xml additions --&gt;
&lt;dependency&gt;
    &lt;groupId&gt;dev.langchain4j&lt;/groupId&gt;
    &lt;artifactId&gt;langchain4j-pgvector&lt;/artifactId&gt;
    &lt;version&gt;${langchain4j.version}&lt;/version&gt;
&lt;/dependency&gt;
&lt;dependency&gt;
    &lt;groupId&gt;dev.langchain4j&lt;/groupId&gt;
    &lt;artifactId&gt;langchain4j-embeddings-bge-small-en-q&lt;/artifactId&gt;
    &lt;version&gt;${langchain4j.version}&lt;/version&gt;
&lt;/dependency&gt;
&lt;dependency&gt;
    &lt;groupId&gt;dev.langchain4j&lt;/groupId&gt;
    &lt;artifactId&gt;langchain4j-document-parser-apache-tika&lt;/artifactId&gt;
    &lt;version&gt;${langchain4j.version}&lt;/version&gt;
&lt;/dependency&gt;
&lt;dependency&gt;
    &lt;groupId&gt;dev.langchain4j&lt;/groupId&gt;
    &lt;artifactId&gt;langchain4j-easy-rag&lt;/artifactId&gt;
    &lt;version&gt;${langchain4j.version}&lt;/version&gt;
&lt;/dependency&gt;

Implementation Files

File	Purpose
`rag/RagConfig.java`	Configuration properties
`rag/RagService.java`	Main facade
`rag/embedding/EmbeddingStoreProvider.java`	PGVector store
`rag/embedding/EmbeddingModelProvider.java`	BGE model (ONNX)
`rag/ingest/KnowledgeIngestor.java`	Ingestor interface
`rag/ingest/WikiIngestor.java`	Wiki pages via MediaWiki API
`rag/ingest/MediaWikiClient.java`	MediaWiki API client (discovery, timestamps)
`rag/ingest/KEntryIngestor.java`	K_Entry table
`rag/ingest/ADMetadataIngestor.java`	AD metadata (tables, windows, processes, tabs, fields)
`ai/langchain/tools/RagTools.java`	@Tool methods
`commands/RagCommand.java`	CLI commands