ADR-024: Application Dictionary Metadata Caching

<!-- MADR 3.0 Template - Markdown Any Decision Records --> <!-- Reference: https://adr.github.io/madr/ -->

Status

Proposed

Date

2025-12-07

Deciders

• Development Team

Context and Problem Statement

The RegistryToolLogic currently executes SQL queries against the live iDempiere database for every AD metadata request (describeTable, listTables, listWindows, etc.). This has several issues:

1. Repeated Queries: Same static data queried repeatedly (AD metadata rarely changes)
2. Latency: 3+ SQL queries per describeTable call (~50-100ms each)
3. Database Load: Unnecessary load on production database
4. Offline Unavailable: Cannot work without database connection

Application Dictionary metadata is static - it only changes during development or plugin installation. Caching this data provides significant performance and availability benefits.

Decision Drivers

• Performance: Sub-10ms response for cached lookups
• Offline Capability: Work without live database connection after initial indexing
• Consistency: Cache invalidation when AD changes (plugin install, table sync)
• Memory Efficiency: Don't cache entire AD (1000+ tables) if not needed
• Simplicity: Minimize infrastructure requirements
• Integration: Work with existing RAG infrastructure (ADR-021)

Considered Options

1. Quarkus Cache (@CacheResult) - In-memory caching with annotations
2. File-based JSON Cache - Export AD metadata to JSON files
3. RAG as Primary Store - Use vector embeddings for all lookups
4. Hybrid: Quarkus Cache + File Persistence - Memory cache backed by file
5. Redis/External Cache - Distributed cache server

Decision Outcome

Chosen option: "Hybrid: Quarkus Cache + File Persistence", because:

• Combines fast in-memory access with persistence across restarts
• Leverages Quarkus built-in caching infrastructure
• Works alongside existing RAG for semantic queries
• No additional infrastructure required
• Clear separation: Cache for exact lookups, RAG for semantic search

Confirmation

The decision is confirmed when:

• describeTable returns cached results in <10ms after first call
• Cache persists across CLI restarts
• registry cache refresh command rebuilds cache from database
• Cache automatically invalidates on detected AD changes
• Offline mode works with cached data

Pros and Cons of the Options

Option 1: Quarkus Cache (@CacheResult)

Simple annotation-based in-memory caching.

@CacheResult(cacheName = "ad-tables")
public ToolResult describeTable(String tableName, String language) {
    // SQL query - only executed on cache miss
}

• Good, because minimal code changes (just annotations)
• Good, because automatic cache key generation
• Good, because configurable TTL and size limits
• Neutral, because Quarkus Cache extension required
• Bad, because lost on application restart
• Bad, because no persistence mechanism

Option 2: File-based JSON Cache

Export AD metadata to JSON files on disk.

~/.idempiere-cli/cache/
├── ad-tables.json       # All table metadata
├── ad-windows.json      # All window metadata
├── ad-processes.json    # All process metadata
└── cache-meta.json      # Checksums, timestamps

• Good, because persists across restarts
• Good, because works fully offline
• Good, because human-readable cache files
• Good, because easy to inspect/debug
• Bad, because requires manual file I/O code
• Bad, because all-or-nothing loading (no lazy loading)
• Bad, because large file size for full AD (~10-20MB)

Option 3: RAG as Primary Store

Use existing vector store for all lookups, not just semantic search.

• Good, because leverages existing infrastructure (ADR-021)
• Good, because already indexed via knowledge ingest
• Good, because semantic search included
• Bad, because vectors optimized for similarity, not exact lookup
• Bad, because requires embedding model for queries
• Bad, because slower than direct key-value lookup
• Bad, because overkill for exact table name lookup

Option 4: Hybrid Quarkus Cache + File Persistence (chosen)

Combine in-memory cache with file-based persistence.

@ApplicationScoped
public class ADMetadataCache {

    @Inject
    @CacheName("ad-metadata")
    Cache cache;

    @ConfigProperty(name = "idempiere.cache.dir")
    String cacheDir;  // ~/.idempiere-cli/cache/

    @PostConstruct
    void loadFromFile() {
        // Load persisted cache on startup
    }

    @PreDestroy
    void persistToFile() {
        // Save cache to file on shutdown
    }

    @CacheResult(cacheName = "ad-tables")
    public TableMetadata getTable(String tableName) {
        // Fetch from DB on miss, cached thereafter
    }
}

• Good, because fast in-memory access (<1ms)
• Good, because persists across restarts
• Good, because lazy loading (only cache what's used)
• Good, because uses Quarkus standard caching
• Good, because clear cache invalidation strategy
• Neutral, because slightly more complex implementation
• Bad, because two storage mechanisms to maintain

Option 5: Redis/External Cache

Distributed cache server.

• Good, because shared across multiple CLI instances
• Good, because built-in persistence (RDB/AOF)
• Good, because mature ecosystem
• Bad, because requires external infrastructure
• Bad, because overkill for single-user CLI
• Bad, because network latency for local use

Architecture

Cache Layers

┌─────────────────────────────────────────────────────────────────────┐
│                         Query Request                                │
│                    describeTable("C_Order")                          │
└─────────────────────────────┬───────────────────────────────────────┘
                              │
                              ▼
                    ┌─────────────────────┐
                    │   L1: Memory Cache  │  ← Quarkus @CacheResult
                    │   (Caffeine)        │     ~0.1ms lookup
                    └─────────────────────┘
                              │ miss
                              ▼
                    ┌─────────────────────┐
                    │   L2: File Cache    │  ← JSON persistence
                    │   (~/.idempiere-cli)│     ~5ms lookup
                    └─────────────────────┘
                              │ miss
                              ▼
                    ┌─────────────────────┐
                    │   L3: Database      │  ← Live SQL query
                    │   (PostgreSQL)      │     ~50-100ms
                    └─────────────────────┘

Cache Key Strategy

Cache Key Format: {entity_type}:{identifier}:{language}

Examples:
- table:C_Order:en_US
- table:C_Order:de_DE
- window:Sales Order:en_US
- process:DocumentProcess:en_US

Cache Invalidation

Data Structures

/**
 * Cached table metadata - immutable after creation.
 */
public record CachedTable(
    int id,
    String tableName,
    String name,
    String description,
    String help,
    boolean isView,
    String accessLevel,
    String entityType,
    List<CachedColumn> columns,
    List<CachedWindowTab> windowsAndTabs,
    Instant cachedAt
) {}

/**
 * Cache metadata for invalidation.
 */
public record CacheMetadata(
    Instant lastRefresh,
    String adChecksum,  // Hash of AD_Table count + max Updated
    int tableCount,
    int windowCount,
    int processCount
) {}

Implementation Plan

Phase 1: Quarkus Cache Integration

1. Add quarkus-cache dependency
2. Annotate RegistryToolLogic methods with @CacheResult
3. Configure cache size and TTL in application.properties
4. Add cache statistics to getStatistics() result

Phase 2: File Persistence

1. Create ADMetadataCache service
2. Implement JSON serialization for cache entries
3. Load cache on startup, persist on shutdown
4. Add registry cache CLI commands

Phase 3: Smart Invalidation

1. Compute AD checksum (table count + max updated timestamp)
2. Compare checksums on startup to detect changes
3. Selective invalidation for specific tables
4. Background refresh for stale entries

CLI Commands

# Show cache status
idempiere-cli registry cache status

# Force refresh from database
idempiere-cli registry cache refresh

# Clear cache
idempiere-cli registry cache clear

# Export cache to file (for offline use)
idempiere-cli registry cache export --output ad-cache.json

# Import cache from file
idempiere-cli registry cache import --input ad-cache.json

Configuration

# application.properties

# Cache configuration
idempiere.cache.enabled=true
idempiere.cache.dir=${user.home}/.idempiere-cli/cache
idempiere.cache.ttl=24h
idempiere.cache.max-entries=5000

# Quarkus Cache (Caffeine)
quarkus.cache.caffeine.ad-tables.maximum-size=1000
quarkus.cache.caffeine.ad-tables.expire-after-access=24h
quarkus.cache.caffeine.ad-windows.maximum-size=500
quarkus.cache.caffeine.ad-processes.maximum-size=500

Integration with RAG

The caching layer complements RAG (ADR-021):

┌─────────────────────────────────────────────────────────────────────┐
│                        smartSearch(query)                            │
└─────────────────────────────┬───────────────────────────────────────┘
                              │
        ┌─────────────────────┼─────────────────────┐
        │                     │                     │
        ▼                     ▼                     ▼
┌───────────────┐     ┌───────────────┐     ┌───────────────┐
│ Exact Name    │     │ SQL Pattern   │     │ Natural Lang  │
│ "C_Order"     │     │ "%Order%"     │     │ "sales tables"│
└───────────────┘     └───────────────┘     └───────────────┘
        │                     │                     │
        ▼                     ▼                     ▼
┌───────────────┐     ┌───────────────┐     ┌───────────────┐
│    CACHE      │     │     SQL       │     │     RAG       │
│   (fastest)   │     │   (precise)   │     │  (semantic)   │
└───────────────┘     └───────────────┘     └───────────────┘

Metrics and Monitoring

// Cache statistics exposed via getStatistics()
{
  "cache": {
    "enabled": true,
    "hitRate": 0.85,
    "missRate": 0.15,
    "size": 150,
    "maxSize": 1000,
    "lastRefresh": "2025-12-07T10:00:00Z",
    "fileCache": {
      "exists": true,
      "size": "2.5MB",
      "entries": 850
    }
  }
}

References

• Quarkus Cache Guide
• Caffeine Cache
• ADR-021: RAG Architecture
• ADR-008: Application Dictionary Registry
• iDempiere Application Dictionary