Heterogeneous Persistence in Enterprise Java: Orchestrating Derby, MySQL, and PostgreSQL in a Single System

The Monolithic Database Fallacy

In traditional web development tutorials, applications are built around a single centralized relational database. While convenient, real-world enterprise architectures often face conflicting requirements:

1. Security Isolation: Administrative authentication credentials and RBAC audit logs need strict physical network isolation to minimize the blast radius of SQL injections.
2. Transactional Business Logic: Job listings, company profiles, and candidate applications require standard ACID transactional consistency and relational foreign keys.
3. Telemetry & Analytical Telemetry: Curriculum tracking and real-time skill alignment queries generate frequent analytical scans that could lock transactional tables.

In AlignIT, our solution was a Heterogeneous Multi-Database Persistence Layer using native Jakarta EE enterprise patterns.

The Three-Tier Storage Model

               ┌─────────────────────────────────────┐
               │    Jakarta EE Web Application Core   │
               └──────────────────┬──────────────────┘
                                  │
          ┌───────────────────────┼───────────────────────┐
          ▼                       ▼                       ▼
┌──────────────────┐    ┌──────────────────┐    ┌──────────────────┐
│   Apache Derby   │    │      MySQL       │    │    PostgreSQL    │
│  (Embedded/Net)  │    │  (Transactional) │    │   (Telemetry)    │
├──────────────────┤    ├──────────────────┤    ├──────────────────┤
│ • User Passwords │    │ • Job Postings   │    │ • Skill Demand   │
│ • AES-GCM Salts  │    │ • Applications   │    │ • Curriculum Map │
│ • RBAC Roles     │    │ • Company Info   │    │ • Student Tracks │
└──────────────────┘    └──────────────────┘    └──────────────────┘

1. Apache Derby: Encrypted Credential Vault

Authentication and RBAC records are stored in Apache Derby. Because Derby can run as an isolated embedded engine or restricted internal network instance, sensitive user credential tables are physically isolated from public-facing job query surfaces. Sensitive data-at-rest is encrypted with authenticated AES-GCM before writing.

2. MySQL: Transactional Job Pipeline

Standard recruitment workflows (candidate applying for a position, recruiter status transitions, and interviews) execute against MySQL with InnoDB ACID transactional guarantees and indexed foreign key constraints.

3. PostgreSQL: Telemetry & Curriculum Alignment

PostgreSQL serves as our analytical telemetry engine. It stores complex competency matrices, university course learning outcomes, and market skill demands. PostgreSQL's robust window functions and JSONB capabilities enable rapid aggregation without impacting the primary MySQL transaction pool.

Automated Database Bootstrapping with `ServletContextListener`

Rather than relying on manual SQL imports, AlignIT boots itself cleanly using an automated migration manager bound to the servlet container lifecycle:

@WebListener
public class DatabaseBootstrapListener implements ServletContextListener {

    private static final Logger LOGGER = Logger.getLogger(DatabaseBootstrapListener.class.getName());

    @Override
    public void contextInitialized(ServletContextEvent sce) {
        LOGGER.info("Starting multi-database initialization sequence...");
        try {
            // Step 1: Initialize isolated Derby credential schema
            DerbyDatabaseManager.bootstrapSchema();
            
            // Step 2: Verify MySQL transactional tables
            MySQLDatabaseManager.bootstrapSchema();
            
            // Step 3: Run PostgreSQL curriculum analytics migrations
            PostgresTelemetryManager.bootstrapSchema();
            
            LOGGER.info("All heterogeneous databases bootstrapped successfully.");
        } catch (SQLException e) {
            LOGGER.log(Level.SEVERE, "Failed to initialize persistence layers", e);
            throw new RuntimeException("Application startup aborted due to DB failure", e);
        }
    }

    @Override
    public void contextDestroyed(ServletContextEvent sce) {
        // Gracefully close connection pools and shutdown Derby engine
        DerbyDatabaseManager.shutdown();
    }
}

Dynamic Landscape PDF Generation with iText

A key deliverable of AlignIT was generating comprehensive, audit-ready curriculum alignment reports for academic deans and accreditation boards.

Standard HTML print dialogs fail to guarantee page breaks, table column constraints, or cryptographic headers. We integrated iText PDF:

• Strict Landscape Pagination: Tables with 10+ columns dynamically calculate widths to prevent overflow truncation.
• Session & Audit Injection: Every generated PDF injects authenticated session metadata, timestamps, and watermarks to ensure chain-of-custody validity.
• Parameterized Filtering: Deans can filter by academic year, department, and skill taxonomy, streaming generated binary bytes directly to the HTTP response stream.

Key Architectural Takeaway

Decoupling persistence by access pattern—rather than forcing every domain into one overloaded database—provided immense stability. The analytical demands of student telemetry never slowed down candidate application processing, and credentials remained securely partitioned behind a hardened boundary.