ADR-020: Community DevOps Best Practices Integration

Status

Proposed

Date

2024-12-07

Deciders

• Norbert Bede (idempiere-cli maintainer)

Context and Problem Statement

The iDempiere community has developed several mature DevOps tools maintained by core developers:

• hengsin/idempiere-dev-setup - Development environment automation (Heng Sin Low - founder)
• chuboe/idempiere-installation-script - Production server deployment (Chuck Boecking)
• chuboe/chuboe-system-configurator - Server administration standards
• globalqss/idempiere-stuff - Database utilities and CI/CD scripts (Carlos Ruiz - PMC Chair)

These tools represent years of accumulated best practices but are scattered across repositories, use different interfaces (bash scripts, property files), and require manual coordination. The idempiere-cli can serve as a unified interface to these capabilities, providing a consistent developer experience.

Decision Drivers

• Consolidation: Single CLI entry point for common DevOps tasks
• Discoverability: Developers should discover best practices through CLI help
• Consistency: Unified command structure instead of multiple bash scripts
• Interoperability: CLI commands should integrate with existing tooling
• Attribution: Acknowledge and reference original community tools

Considered Options

1. Full Integration - Reimplement all functionality natively in Java
2. Wrapper Integration - CLI wraps existing scripts with unified interface
3. Reference Documentation - Just document how to use existing tools
4. Hybrid Approach - Native implementation of core features, wrappers for specialized tools

Decision Outcome

Chosen option: "Hybrid Approach", because it provides immediate value through native implementations of high-value features while maintaining compatibility with existing community scripts through optional wrapper integration.

Confirmation

• New commands are documented in USER_GUIDE.md
• Commands reference original community tools in help text
• Integration tests validate core functionality
• Feature matrix updated in FEATURES.md

Analysis of Community Tools

1. hengsin/idempiere-dev-setup

Purpose: Automated iDempiere development environment setup

Key Features:

Proposed Commands:

ide setup dev              # Full dev environment wizard
ide setup docker-postgres  # Create PostgreSQL container
ide setup eclipse-ws       # Configure Eclipse workspace

2. chuboe/idempiere-installation-script

Purpose: Production iDempiere server deployment

Key Features:

Proposed Commands:

server install            # Production server installer
server service create     # Create systemd service
server service status     # Check service status
server tune postgres      # Apply PostgreSQL optimizations
server ssl setup          # Generate/configure SSL
server backup configure   # Configure backup strategy

3. chuboe/chuboe-system-configurator

Purpose: Server administration standardization

Key Features:

Proposed Commands:

server backup schedule    # Setup automated backups
server backup run         # Execute backup now
server backup restore     # Restore from backup

4. globalqss/idempiere-stuff

Purpose: Database utilities and CI/CD automation

Key Features:

Proposed Commands:

db validate               # Compare DB vs Application Dictionary
db validate-records       # Validate record IDs
db client backup          # Backup specific client
db client delete          # Delete client (with safety)
db generate-fk            # Generate missing foreign keys

Implementation Roadmap

Phase 0: Database Performance Commands (Critical Priority)

Rationale: Database performance degrades significantly as data grows. Production iDempiere systems with millions of records suffer from:

• Missing indexes causing full table scans
• Table bloat from UPDATE/DELETE operations
• Stale statistics leading to poor query plans
• Missing foreign keys preventing optimizer hints

These commands provide immediate, measurable performance improvements.

0.1 Index Management

db index validate              # Compare AD_TableIndex vs actual DB indexes
db index create-missing        # Create indexes defined in AD but missing in DB
db index recommend             # Suggest indexes based on slow query analysis
db index unused                # Find indexes that are never used (candidates for removal)

Implementation SQL (PostgreSQL):

-- Find AD_TableIndex definitions without corresponding DB indexes
SELECT ti.name AS index_name,
       t.tablename,
       ti.isunique,
       string_agg(ic.columnname, ', ' ORDER BY ic.seqno) AS columns
FROM ad_tableindex ti
JOIN ad_table t ON ti.ad_table_id = t.ad_table_id
JOIN ad_indexcolumn ic ON ti.ad_tableindex_id = ic.ad_tableindex_id
JOIN ad_column c ON ic.ad_column_id = c.ad_column_id
WHERE ti.isactive = 'Y'
  AND NOT EXISTS (
    SELECT 1 FROM pg_indexes
    WHERE indexname = lower(ti.name)
  )
GROUP BY ti.name, t.tablename, ti.isunique;

Reference: Create Table Index (Process ID-200057)

0.2 Foreign Key Generation

db fk validate                 # Show missing FKs based on AD_Reference
db fk generate --dry-run       # Preview ALTER TABLE statements
db fk generate --apply         # Create missing foreign keys

Implementation: Port globalqss generateForeignKeys_pg.sql

Foreign keys provide:

• Implicit indexes on referencing columns
• Query optimizer hints for JOIN operations
• Referential integrity enforcement

SQL Logic:

-- Find columns that should have FK but don't (AD_Reference types 18, 19, 30)
SELECT t.tablename AS source_table,
       c.columnname AS source_column,
       rt.tablename AS target_table,
       'ALTER TABLE ' || t.tablename ||
       ' ADD CONSTRAINT ' || substr(c.columnname || '_' || t.tablename, 1, 30) ||
       ' FOREIGN KEY (' || c.columnname || ') REFERENCES ' || rt.tablename ||
       ' DEFERRABLE INITIALLY DEFERRED;' AS ddl
FROM ad_column c
JOIN ad_table t ON c.ad_table_id = t.ad_table_id
JOIN ad_reference r ON c.ad_reference_id = r.ad_reference_id
LEFT JOIN ad_ref_table rft ON c.ad_reference_value_id = rft.ad_reference_id
LEFT JOIN ad_table rt ON rft.ad_table_id = rt.ad_table_id
WHERE c.ad_reference_id IN (18, 19, 30)  -- Table, TableDirect, Search
  AND t.isview = 'N'
  AND c.columnname NOT IN ('AD_Client_ID', 'AD_Org_ID', 'CreatedBy', 'UpdatedBy')
  AND NOT EXISTS (
    SELECT 1 FROM pg_constraint pc
    JOIN pg_class cls ON pc.conrelid = cls.oid
    WHERE pc.contype = 'f'
      AND cls.relname = lower(t.tablename)
      AND pc.conname LIKE '%' || lower(c.columnname) || '%'
  );

0.3 Table Health Analysis

db health bloat                # Show table/index bloat statistics
db health vacuum [TABLE]       # Run VACUUM on table(s)
db health vacuum-full [TABLE]  # Run VACUUM FULL (reclaim disk space)
db health analyze [TABLE]      # Update table statistics
db health reindex [TABLE]      # Rebuild indexes
db health autovacuum-status    # Check autovacuum configuration and activity

Implementation SQL:

-- Table bloat analysis
SELECT schemaname, relname AS table_name,
       pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
       n_live_tup AS live_rows,
       n_dead_tup AS dead_rows,
       CASE WHEN n_live_tup > 0
            THEN round(n_dead_tup * 100.0 / n_live_tup, 2)
            ELSE 0 END AS dead_pct,
       last_vacuum,
       last_autovacuum,
       last_analyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC
LIMIT 20;

-- Index bloat estimation
SELECT schemaname, tablename, indexname,
       pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
       idx_scan AS times_used,
       idx_tup_read,
       idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;

Reference: PostgreSQL VACUUM Best Practices

0.4 PostgreSQL Auto-Tuning

db tune recommend              # Show recommended postgresql.conf settings
db tune apply                  # Apply settings (requires restart)
db tune current                # Show current vs recommended values

Implementation: Use pgconfig.org API or embedded tuning rules.

Tuning Parameters by System Memory:

Autovacuum Tuning (critical for large tables):

-- Per-table autovacuum settings for high-transaction tables
ALTER TABLE c_orderline SET (
    autovacuum_vacuum_scale_factor = 0.01,   -- 1% instead of 20%
    autovacuum_analyze_scale_factor = 0.005,
    autovacuum_vacuum_cost_limit = 1000
);

0.5 Slow Query Analysis & EXPLAIN

db queries slow [--threshold 1s]     # Show queries exceeding threshold
db queries frequent [--limit 20]     # Most frequently executed queries
db queries explain <SQL|file>        # EXPLAIN ANALYZE for a query
db queries explain --format json     # JSON format for visualization tools
db queries auto-explain enable       # Enable automatic EXPLAIN logging
db queries enable-stats              # Enable pg_stat_statements extension
db queries log-settings              # Show/configure log_min_duration_statement

Requires: pg_stat_statements and auto_explain extensions

-- Enable extensions
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
LOAD 'auto_explain';

-- Find slow queries
SELECT round(total_exec_time::numeric, 2) AS total_ms,
       calls,
       round(mean_exec_time::numeric, 2) AS avg_ms,
       round(stddev_exec_time::numeric, 2) AS stddev_ms,
       rows,
       query
FROM pg_stat_statements
WHERE mean_exec_time > 1000  -- > 1 second average
ORDER BY total_exec_time DESC
LIMIT 20;

EXPLAIN ANALYZE Command:

db queries explain "SELECT * FROM c_order WHERE c_bpartner_id = 123"
db queries explain --file problematic_query.sql
db queries explain --format json --buffers --timing "SELECT ..."

Implementation:

-- Full EXPLAIN with all metrics
EXPLAIN (ANALYZE, BUFFERS, TIMING, COSTS, VERBOSE, FORMAT JSON)
SELECT o.documentno, ol.line, p.name
FROM c_order o
JOIN c_orderline ol ON o.c_order_id = ol.c_order_id
JOIN m_product p ON ol.m_product_id = p.m_product_id
WHERE o.dateordered > '2024-01-01';

Output Analysis (CLI parses and highlights):

┌─────────────────────────────────────────────────────────────────────┐
│ Query Plan Analysis                                                  │
├─────────────────────────────────────────────────────────────────────┤
│ Total Time: 2,345 ms                                                │
│ Planning Time: 12 ms                                                │
│ Execution Time: 2,333 ms                                            │
├─────────────────────────────────────────────────────────────────────┤
│ ⚠ WARNINGS:                                                         │
│   • Seq Scan on c_order (cost=0..15234) - Missing index?           │
│   • Nested Loop (actual rows=50000) - Consider Hash Join           │
│   • Buffers shared read=12456 - High I/O, increase shared_buffers  │
├─────────────────────────────────────────────────────────────────────┤
│ RECOMMENDATIONS:                                                     │
│   1. CREATE INDEX idx_c_order_dateordered ON c_order(dateordered); │
│   2. Run ANALYZE c_order; (stats may be stale)                     │
│   3. Consider increasing work_mem for this session                  │
└─────────────────────────────────────────────────────────────────────┘

Auto-Explain Configuration (for automatic logging):

-- In postgresql.conf or via ALTER SYSTEM
ALTER SYSTEM SET auto_explain.log_min_duration = '1s';
ALTER SYSTEM SET auto_explain.log_analyze = true;
ALTER SYSTEM SET auto_explain.log_buffers = true;
ALTER SYSTEM SET auto_explain.log_timing = true;
ALTER SYSTEM SET auto_explain.log_nested_statements = true;

-- Enable for session (no restart needed)
SET auto_explain.log_min_duration = '1s';
SET auto_explain.log_analyze = true;

Integration with iDempiere Logs:

db queries from-log /path/to/idempiere.log   # Extract SQLs from server log
db queries from-log --slow-only              # Only queries marked slow

The CLI can parse iDempiere server logs to extract SQL statements and automatically run EXPLAIN ANALYZE on them.

0.6 Anomaly Detection

db monitor baseline                  # Establish baseline metrics (run for 24-48h)
db monitor anomalies                 # Detect current anomalies vs baseline
db monitor watch [--interval 60s]    # Continuous monitoring (stdout)
db monitor report [--period 7d]      # Historical anomaly report

What Gets Monitored:

Implementation:

-- Establish baseline (store in cli metadata table or local file)
CREATE TABLE IF NOT EXISTS idempiere_cli_baseline (
    metric_name VARCHAR(100),
    measured_at TIMESTAMP DEFAULT now(),
    avg_value NUMERIC,
    stddev_value NUMERIC,
    min_value NUMERIC,
    max_value NUMERIC,
    sample_count INTEGER
);

-- Capture baseline for query times
INSERT INTO idempiere_cli_baseline (metric_name, avg_value, stddev_value, min_value, max_value, sample_count)
SELECT 'query_exec_time',
       avg(mean_exec_time),
       stddev(mean_exec_time),
       min(mean_exec_time),
       max(mean_exec_time),
       count(*)
FROM pg_stat_statements
WHERE calls > 10;

-- Detect anomalies: queries running 3× slower than baseline
WITH baseline AS (
    SELECT avg_value, stddev_value
    FROM idempiere_cli_baseline
    WHERE metric_name = 'query_exec_time'
    ORDER BY measured_at DESC LIMIT 1
)
SELECT query,
       round(mean_exec_time::numeric, 2) AS current_avg_ms,
       round(b.avg_value::numeric, 2) AS baseline_avg_ms,
       round((mean_exec_time / NULLIF(b.avg_value, 0))::numeric, 1) AS times_slower,
       CASE
           WHEN mean_exec_time > b.avg_value + (10 * b.stddev_value) THEN 'CRITICAL'
           WHEN mean_exec_time > b.avg_value + (3 * b.stddev_value) THEN 'WARNING'
           ELSE 'OK'
       END AS status
FROM pg_stat_statements, baseline b
WHERE mean_exec_time > b.avg_value + (3 * b.stddev_value)
ORDER BY mean_exec_time DESC;

Real-Time Spike Detection:

-- Detect queries that suddenly spiked (last hour vs last 24h)
WITH recent AS (
    SELECT queryid, query,
           mean_exec_time AS recent_time,
           calls AS recent_calls
    FROM pg_stat_statements
),
historical AS (
    -- Compare with stored snapshot from 24h ago
    SELECT queryid, mean_exec_time AS historical_time
    FROM idempiere_cli_query_snapshot
    WHERE snapshot_time > now() - interval '24 hours'
)
SELECT r.query,
       round(r.recent_time::numeric, 2) AS current_ms,
       round(h.historical_time::numeric, 2) AS yesterday_ms,
       round((r.recent_time / NULLIF(h.historical_time, 0))::numeric, 1) AS spike_factor
FROM recent r
JOIN historical h ON r.queryid = h.queryid
WHERE r.recent_time > h.historical_time * 3  -- 3× slower
ORDER BY spike_factor DESC;

Long-Running Query Detection (real-time):

-- Queries running longer than threshold
SELECT pid,
       now() - query_start AS duration,
       state,
       wait_event_type,
       wait_event,
       left(query, 100) AS query_preview,
       CASE
           WHEN now() - query_start > interval '10 minutes' THEN 'CRITICAL'
           WHEN now() - query_start > interval '5 minutes' THEN 'WARNING'
           ELSE 'INFO'
       END AS alert_level
FROM pg_stat_activity
WHERE state = 'active'
  AND query NOT LIKE '%pg_stat%'
  AND now() - query_start > interval '1 minute'
ORDER BY duration DESC;

Lock Contention Detection:

-- Detect blocking chains
SELECT blocked.pid AS blocked_pid,
       blocked.query AS blocked_query,
       blocking.pid AS blocking_pid,
       blocking.query AS blocking_query,
       now() - blocked.query_start AS wait_duration
FROM pg_stat_activity blocked
JOIN pg_locks blocked_locks ON blocked.pid = blocked_locks.pid
JOIN pg_locks blocking_locks ON blocked_locks.locktype = blocking_locks.locktype
    AND blocked_locks.relation = blocking_locks.relation
    AND blocked_locks.pid != blocking_locks.pid
JOIN pg_stat_activity blocking ON blocking_locks.pid = blocking.pid
WHERE NOT blocked_locks.granted
  AND blocking_locks.granted;

CLI Output Example:

$ idempiere-cli db monitor anomalies

╔══════════════════════════════════════════════════════════════════════╗
║                    DATABASE ANOMALY REPORT                            ║
║                    2024-12-07 14:32:15                                ║
╠══════════════════════════════════════════════════════════════════════╣
║ 🔴 CRITICAL ALERTS (2)                                                ║
╠══════════════════════════════════════════════════════════════════════╣
║ Query Spike: SELECT * FROM c_invoice WHERE ...                       ║
║   Current: 12,450 ms | Baseline: 245 ms | 50.8× slower              ║
║   Recommendation: Check for missing index on c_invoice.dateacct      ║
║                                                                      ║
║ Long Running: PID 28451 running for 18 minutes                       ║
║   Query: UPDATE m_storageonhand SET qtyonhand = ...                  ║
║   Waiting on: transactionid lock (PID 28320)                         ║
╠══════════════════════════════════════════════════════════════════════╣
║ ⚠️  WARNINGS (3)                                                      ║
╠══════════════════════════════════════════════════════════════════════╣
║ Connection Spike: 156/200 connections (78% - approaching limit)      ║
║ Dead Tuple Growth: c_orderline has 2.3M dead tuples (45% of table)  ║
║ Query Frequency: Invoice report running 340×/hour (normal: 12×)     ║
╠══════════════════════════════════════════════════════════════════════╣
║ ✅ HEALTHY METRICS                                                    ║
║   • Cache hit ratio: 99.2% (good > 95%)                              ║
║   • Index usage: 98.7% (good > 95%)                                  ║
║   • Checkpoint frequency: normal                                      ║
╚══════════════════════════════════════════════════════════════════════╝

Watch Mode (continuous monitoring):

$ idempiere-cli db monitor watch --interval 30s

[14:32:15] Monitoring... (Ctrl+C to stop)
[14:32:45] ✅ All metrics normal
[14:33:15] ✅ All metrics normal
[14:33:45] ⚠️  Query spike detected: c_invoice SELECT (3.2× baseline)
[14:34:15] 🔴 CRITICAL: Query now 8.5× baseline
[14:34:45] ⚠️  Query returning to normal (2.1× baseline)

Output Formats (for external monitoring integration):

db monitor anomalies --format json           # JSON for log aggregators
db monitor anomalies --format prometheus     # Prometheus metrics
db monitor watch --format json               # Stream JSON events

0.7 Notification & Logging Integration

Status: NOT PLANNED - Notification/alerting is better handled by external monitoring infrastructure (Prometheus/Grafana/AlertManager) rather than built into a CLI tool.

Alternative approach: CLI provides JSON output that can be consumed by existing monitoring tools:

db health bloat --format json | jq .      # Pipe to monitoring
db queries slow --format json             # Export for analysis
db monitor anomalies --format prometheus  # Prometheus metrics format

For future consideration: A Prometheus exporter endpoint (db metrics serve --port 9090) could provide metrics for external monitoring systems.

0.8 Connection Pool Analysis

db connections active          # Show active connections
db connections idle            # Show idle connections
db connections blocked         # Show blocked/waiting queries
db connections kill <pid>      # Terminate a connection

-- Connection overview
SELECT state, usename, application_name,
       client_addr, count(*)
FROM pg_stat_activity
WHERE datname = current_database()
GROUP BY state, usename, application_name, client_addr;

-- Long-running queries
SELECT pid, now() - pg_stat_activity.query_start AS duration,
       query, state
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes'
  AND state != 'idle';

Phase 1: High-Priority Native Commands

Commands to implement natively in Java:

1.1 Docker PostgreSQL Setup

ide docker postgres create [--name NAME] [--version VERSION] [--port PORT]
ide docker postgres start
ide docker postgres stop

Implementation: Use ProcessBuilder to execute Docker commands, similar to existing patterns in the codebase.

Reference: hengsin/idempiere-dev-setup/docker-postgres.sh

1.2 Database Validation

db validate [--fix] [--client ID]

Implementation: Execute SQL queries comparing AD_Column/AD_Table against information_schema. Already have database connectivity in JdbcTemplateService.

Reference: globalqss/idempiere-stuff/CheckDatabaseVsDictionary.sql

1.3 Client Backup/Delete

db client backup --client-id ID --output FILE
db client delete --client-id ID [--confirm]

Implementation: Port SQL scripts to parameterized queries with safety checks.

Reference:

• BackupDataClient_pg.sh
• DeleteIdempiereClient_pg.sql

1.4 Service Management

server service create [--user USER] [--memory MEMORY]
server service start
server service stop
server service status

Implementation: Generate systemd unit files from templates, execute systemctl commands.

Reference: chuboe/idempiere-installation-script service setup

Phase 2: Medium-Priority Commands

2.1 PostgreSQL Performance Tuning

server tune postgres [--memory SIZE] [--connections N] [--apply]

Implementation: Use pgconfig.org API or embedded tuning rules to generate postgresql.conf recommendations.

2.2 Backup Configuration

server backup configure --destination (local|s3|rsync) [--schedule CRON]
server backup run [--type full|incremental]
server backup list
server backup restore --from BACKUP

2.3 SSL Setup

server ssl generate --domain DOMAIN
server ssl install --cert FILE --key FILE

Phase 3: Wrapper Integration

For complex tools that don't warrant reimplementation:

ide external dev-setup [ARGS...]     # Wraps hengsin/idempiere-dev-setup
ide external install [ARGS...]       # Wraps chuboe/idempiere-installation-script

Implementation: Clone repositories to ~/.idempiere-cli/external/, forward arguments to scripts.

Command Structure Integration

Integrate with existing command hierarchy (per ADR-019):

idempiere-cli
├── ide
│   ├── init              # Existing
│   ├── setup             # NEW: Dev environment setup
│   │   ├── dev           # Full wizard
│   │   ├── docker-postgres
│   │   └── eclipse-ws
│   └── external          # NEW: Wrapper commands
│       ├── dev-setup
│       └── install
├── server
│   ├── db                # Existing
│   ├── install           # NEW: Production installer
│   ├── service           # NEW: Systemd management
│   │   ├── create
│   │   ├── start
│   │   ├── stop
│   │   └── status
│   ├── tune              # NEW: Performance tuning
│   │   └── postgres
│   ├── ssl               # NEW: SSL management
│   │   ├── generate
│   │   └── install
│   └── backup            # NEW: Backup management
│       ├── configure
│       ├── run
│       ├── list
│       └── restore
└── db                    # NEW command group
    ├── validate          # Schema vs AD validation
    ├── validate-records  # Record ID validation
    ├── index             # NEW: Index management
    │   ├── validate      # AD_TableIndex vs actual
    │   ├── create-missing
    │   ├── recommend
    │   └── unused
    ├── fk                # NEW: Foreign key management
    │   ├── validate
    │   └── generate
    ├── health            # NEW: Database health
    │   ├── bloat
    │   ├── vacuum
    │   ├── vacuum-full
    │   ├── analyze
    │   ├── reindex
    │   └── autovacuum-status
    ├── tune              # NEW: PostgreSQL tuning
    │   ├── recommend
    │   ├── apply
    │   └── current
    ├── queries           # NEW: Query analysis
    │   ├── slow
    │   ├── frequent
    │   ├── explain
    │   ├── auto-explain
    │   ├── from-log
    │   └── enable-stats
    ├── monitor           # NEW: Anomaly detection
    │   ├── baseline      # Establish normal metrics
    │   ├── anomalies     # Detect current anomalies
    │   ├── watch         # Continuous monitoring (stdout/JSON)
    │   └── report        # Historical report
    ├── connections       # NEW: Connection management
    │   ├── active
    │   ├── idle
    │   ├── blocked
    │   └── kill
    └── client            # Multi-tenant operations
        ├── backup
        └── delete

Pros and Cons of the Options

Option 1: Full Integration

Reimplement everything in Java

• Good, because unified codebase and testing
• Good, because no external dependencies
• Bad, because significant development effort
• Bad, because may diverge from upstream improvements

Option 2: Wrapper Integration

CLI just wraps existing bash scripts

• Good, because immediate availability
• Good, because stays in sync with upstream
• Bad, because inconsistent user experience
• Bad, because requires bash/shell on all platforms

Option 3: Reference Documentation

Just document existing tools

• Good, because no development effort
• Bad, because no value-add from CLI
• Bad, because users must learn multiple tools

Option 4: Hybrid Approach (Chosen)

Native core features + optional wrappers

• Good, because prioritizes high-value features
• Good, because native implementations work cross-platform
• Good, because wrappers provide escape hatch
• Good, because references and honors original work
• Neutral, because requires ongoing synchronization
• Bad, because some complexity in architecture

Implementation Notes

Native Command Template

@Command(name = "validate",
         description = "Validate database schema against Application Dictionary")
public class DbValidateCommand implements Callable<Integer> {

    @Option(names = "--fix", description = "Attempt to fix discrepancies")
    boolean fix;

    @Option(names = "--client", description = "Validate specific client")
    Integer clientId;

    @Inject
    JdbcTemplateService jdbc;

    @Override
    public Integer call() {
        // Reference: globalqss/idempiere-stuff/CheckDatabaseVsDictionary.sql
        String sql = """
            SELECT c.columnname, c.ad_column_id,
                   CASE WHEN col.column_name IS NULL THEN 'Missing'
                        ELSE 'OK' END as status
            FROM ad_column c
            JOIN ad_table t ON c.ad_table_id = t.ad_table_id
            LEFT JOIN information_schema.columns col
                   ON lower(t.tablename) = col.table_name
                  AND lower(c.columnname) = col.column_name
            WHERE t.isview = 'N' AND c.isactive = 'Y'
            ORDER BY t.tablename, c.columnname
            """;
        // Execute and report...
    }
}

Attribution Guidelines

Every command derived from community tools must:

1. Reference the original tool in description annotation
2. Include URL in --help extended description
3. Document in USER_GUIDE.md with credits

Example:

@Command(name = "docker-postgres",
         description = "Create PostgreSQL Docker container",
         footer = "%nBased on: https://github.com/hengsin/idempiere-dev-setup")

Related ADRs

• ADR-019 - CLI command structure and grouping
• ADR-005 - Migration scripts
• ADR-001 - Version compatibility

References

Community Tools

• hengsin/idempiere-dev-setup - Development environment automation
• chuboe/idempiere-installation-script - Production installation
• chuboe/chuboe-system-configurator - Server standardization
• globalqss/idempiere-stuff - Database utilities
• globalqss/generateForeignKeys_pg.sql - Foreign key generation

iDempiere Documentation

• iDempiere Wiki - Installation - Official installation docs
• Create Table Index (Process ID-200057) - Index management
• ERP Academy - Chuck Boecking's iDempiere tutorials

PostgreSQL Performance

• PostgreSQL VACUUM Best Practices - VACUUM guide
• Autovacuum Tuning Basics - Autovacuum configuration
• PostgreSQL Performance Tuning Best Practices - Parameter tuning
• pgconfig.org - PostgreSQL configuration API
• pg_stat_statements - Query statistics extension
• auto_explain - Automatic EXPLAIN logging

Appendix: Feature Mapping Matrix

Phase 0: Database Performance (Critical Priority)

Phase 1-3: DevOps Integration

Appendix: Community Tool Comparison