Database Architecture Overview

Overview

The system uses three separate PostgreSQL databases, each serving a distinct bounded context in the Domain-Driven Design (DDD) architecture:

Database Name	Purpose	Primary Tables	Microservices
app_core_db	Core operational data	resources, engagements, intakes, records, orders, reports	Data Owner BC, Data Consumer BC, Scheduling BC
app_iam_db	Identity & Access Management	users, roles, permissions, user_roles, role_permissions, policies	IAM Service, Auth Service
app_master_db	Master/Reference data	labels, regions, districts, sub_districts, departments, classification_codes	MasterData Service

Why Three Separate Databases?

1. Domain-Driven Design (DDD) Bounded Contexts

Each database represents a distinct bounded context with clear domain boundaries:

graph TB
    subgraph "app_core_db"
        A[Resource Management]
        B[Records]
        C[Scheduling]
        D[Core Operations]
    end

    subgraph "app_iam_db"
        E[User Management]
        F[Authentication]
        G[Authorization]
        H[Access Control]
    end

    subgraph "app_master_db"
        I[Reference Data]
        J[Lookup Tables]
        K[Code Sets]
        L[Standards]
    end

    style A fill:#e1f5ff
    style B fill:#e1f5ff
    style C fill:#e1f5ff
    style D fill:#e1f5ff
    style E fill:#fff4e1
    style F fill:#fff4e1
    style G fill:#fff4e1
    style H fill:#fff4e1
    style I fill:#e8f5e9
    style J fill:#e8f5e9
    style K fill:#e8f5e9
    style L fill:#e8f5e9

Benefits:

Clear Ownership: Each bounded context owns its data domain
Independent Evolution: Schemas can evolve independently
Reduced Coupling: Changes in one domain don’t affect others
Single Source of Truth: Each entity has one authoritative location

2. Microservice Autonomy

Each microservice can:

Scale Independently: Different databases can be scaled based on their specific load patterns
Deploy Independently: Database migrations don’t create cross-service dependencies
Fail Independently: Failure in the IAM database doesn’t bring down core operations
Optimize Differently: Each database can have specific indexes, partitioning, and tuning

Example: The IAM database experiences high read traffic during authentication but low writes. The Core database has constant writes (operational records) but moderate reads. Separate databases allow different optimization strategies.

3. Security & Compliance

Data Segregation:

IAM Database: Contains highly sensitive authentication data (password hashes, tokens, security policies)
Core Database: Contains protected operational data subject to regulatory requirements
Master Database: Contains non-sensitive reference data (can have different backup/encryption policies)

Access Control:

// Different database connections with different credentials
const coreConnection = {
    host: process.env.DB_CORE_HOST,
    username: process.env.DB_CORE_USER, // Limited to core operations
    password: process.env.DB_CORE_PASSWORD,
    database: 'app_core_db',
};

const iamConnection = {
    host: process.env.DB_IAM_HOST,
    username: process.env.DB_IAM_USER, // Only IAM service has access
    password: process.env.DB_IAM_PASSWORD,
    database: 'app_iam_db',
};

4. Data Lifecycle Management

Different databases have different data lifecycle requirements:

Database	Retention Policy	Backup Frequency	Archive Strategy
Core	10+ years (operational records)	Every 4 hours	Cold storage after 5 years
IAM	2 years (audit logs)	Daily	Delete after 2 years
Master	Indefinite (reference data)	Weekly	Never archived

5. CAP Theorem Trade-offs

Each database can make different CAP theorem choices:

Core Database: Prioritizes Consistency (C) + Availability (A) — Critical for operational accuracy
IAM Database: Prioritizes Consistency (C) + Partition Tolerance (P) — Authentication must be accurate
Master Database: Prioritizes Availability (A) + Partition Tolerance (P) — Reference data rarely changes

Architecture Overview

flowchart TB
    subgraph "Client Layer"
        WebApp[Web Application]
        MobileApp[Mobile Application]
    end

    subgraph "API Gateway"
        Kong[Kong API Gateway]
    end

    subgraph "Microservices Layer"
        AuthService[Auth Service]
        IAMService[IAM Service]
        DataOwnerService[Data Owner BC]
        DataConsumerService[Data Consumer BC]
        MasterService[MasterData Service]
    end

    subgraph "Database Layer"
        CoreDB[(app_core_db)]
        IAMDB[(app_iam_db)]
        MasterDB[(app_master_db)]
    end

    subgraph "Lookup/Read Models in Core"
        LabelsLookup[labels_lookup]
        RegionsLookup[regions_lookup]
        DistrictsLookup[districts_lookup]
    end

    WebApp --> Kong
    MobileApp --> Kong
    Kong --> AuthService
    Kong --> DataOwnerService
    Kong --> DataConsumerService

    AuthService --> IAMDB
    IAMService --> IAMDB
    DataOwnerService --> CoreDB
    DataConsumerService --> CoreDB
    MasterService --> MasterDB

    MasterDB -.->|Event-Driven Sync| LabelsLookup
    MasterDB -.->|Event-Driven Sync| RegionsLookup
    MasterDB -.->|Event-Driven Sync| DistrictsLookup

    LabelsLookup --> CoreDB
    RegionsLookup --> CoreDB
    DistrictsLookup --> CoreDB

    style CoreDB fill:#e1f5ff,stroke:#0288d1,stroke-width:3px
    style IAMDB fill:#fff4e1,stroke:#ff9800,stroke-width:3px
    style MasterDB fill:#e8f5e9,stroke:#4caf50,stroke-width:3px
    style LabelsLookup fill:#ffe0e0,stroke:#f44336,stroke-width:2px
    style RegionsLookup fill:#ffe0e0,stroke:#f44336,stroke-width:2px
    style DistrictsLookup fill:#ffe0e0,stroke:#f44336,stroke-width:2px

Lookup/Read Model Pattern

The Challenge

Problem: Microservices should not directly access other services’ databases, but we need master data in the core database for:

SQL Reporting: Tools like Jasper Reports can’t JOIN across databases
Performance: Avoid microservice calls for every query
Data Locality: Keep frequently accessed data close to where it’s needed

Anti-Pattern ❌:

-- This is NOT possible in PostgreSQL
SELECT
    r.reference_number,
    r.first_name,
    l.display_name  -- labels table is in a different database
FROM app_core_db.resources r
JOIN app_master_db.labels l ON r.label_code = l.code;

The Solution: Lookup Tables (Read Models)

We replicate read-only copies of master data into the core database. These are called Lookup Tables or Read Models (from the CQRS pattern).

sequenceDiagram
    participant Master as MasterData Service<br/>(app_master_db)
    participant EventBus as Event Bus<br/>(RabbitMQ/Redis)
    participant Core as Data Owner BC<br/>(app_core_db)
    participant Reports as Reporting Tool

    Note over Master: Admin creates new label
    Master->>Master: INSERT INTO labels
    Master->>EventBus: Emit 'label.created' event
    EventBus->>Core: Handle 'label.created' event
    Core->>Core: UPSERT INTO labels_lookup

    Note over Reports: Report needs resource with label
    Reports->>Core: SQL Query (JOIN resources + labels_lookup)
    Core-->>Reports: ✅ Result with label name

Key Principle: Master data is the Source of Truth, lookup tables are Cached Copies for read operations.

Example: Label Master Data

Master Table (Source of Truth)

Location: app_master_db.labels

@Entity({
    name: 'labels',
    database: AppDatabases.APP_MASTER,
})
export class Label implements ITimestamp {
    @PrimaryGeneratedColumn('uuid')
    id: string;

    @Column({
        type: 'varchar',
        length: 10,
        unique: true,
        comment: 'Label code (e.g., MR, MRS, DR)',
    })
    code: string;

    @Column({
        type: 'varchar',
        length: 50,
        comment: 'Display label',
    })
    label: string;

    @Column({
        type: 'varchar',
        length: 50,
        comment: 'Display name (e.g., Mister, Mistress)',
    })
    display_name: string;

    @Column({
        type: 'varchar',
        length: 50,
        nullable: true,
        comment: 'Abbreviated form (e.g., Mr., Mrs., Dr.)',
    })
    abbreviation: string | null;

    @Column({
        type: 'int',
        default: 0,
        comment: 'Display order in dropdowns',
    })
    display_order: number;

    @Column({
        type: 'boolean',
        default: true,
        comment: 'Is this label active?',
    })
    is_active: boolean;

    @CreateDateColumn({ type: 'timestamptz' })
    created_at: Date;

    @UpdateDateColumn({ type: 'timestamptz' })
    updated_at: Date;

    @Column({ type: 'uuid', nullable: true })
    created_by: string | null;

    @Column({ type: 'uuid', nullable: true })
    updated_by: string | null;

    @DeleteDateColumn({ type: 'timestamptz', nullable: true })
    deleted_at: Date | null;
}

Sample Data:

-- app_master_db.labels
INSERT INTO labels (id, code, label, display_name, abbreviation, display_order, is_active) VALUES
('uuid-001', 'MR',   'Mr.',   'Mister',   'Mr.',   1, true),
('uuid-002', 'MRS',  'Mrs.',  'Mistress', 'Mrs.',  2, true),
('uuid-003', 'MISS', 'Miss',  'Miss',     'Miss',  3, true),
('uuid-004', 'DR',   'Dr.',   'Doctor',   'Dr.',   4, true);

Lookup Table (Read Model)

Location: app_core_db.labels_lookup

@Entity({
    name: 'labels_lookup',
    database: AppDatabases.APP_CORE,
})
export class LabelLookup {
    /**
     * Primary key is 'code' (not UUID) for efficient JOINs
     */
    @PrimaryColumn({
        type: 'varchar',
        length: 10,
        comment: 'Label code from master table',
    })
    code: string;

    @Column({
        type: 'varchar',
        length: 50,
        comment: 'Display label (synced from master)',
    })
    label: string;

    @Column({
        type: 'varchar',
        length: 50,
        comment: 'Display name (synced from master)',
    })
    display_name: string;

    @Column({
        type: 'varchar',
        length: 50,
        nullable: true,
        comment: 'Abbreviated form',
    })
    abbreviation: string | null;

    @Column({
        type: 'int',
        default: 0,
        comment: 'Display order',
    })
    display_order: number;

    @Column({
        type: 'boolean',
        default: true,
        comment: 'Is active (synced from master)',
    })
    is_active: boolean;

    /**
     * Critical: Track when this record was last synced
     */
    @UpdateDateColumn({
        type: 'timestamptz',
        comment: 'Last synchronization time from master',
    })
    synced_at: Date;

    /**
     * Optional: Store source UUID for debugging
     */
    @Column({
        type: 'uuid',
        nullable: true,
        comment: 'Original UUID from master table (for reference)',
    })
    master_id: string | null;
}

Key Differences from Master Table:

✅ Primary key is code (not uuid) — optimized for JOINs
✅ Contains only essential fields for reading (no audit fields like created_by, updated_by)
✅ Has synced_at timestamp to track freshness
✅ Stores master_id (original UUID) for traceability
❌ No deleted_at — records are hard-deleted when master marks inactive
❌ Simpler schema — no need for full audit trail

Sample Data:

-- app_core_db.labels_lookup (synced copy)
INSERT INTO labels_lookup (code, label, display_name, abbreviation, display_order, is_active, synced_at, master_id) VALUES
('MR',   'Mr.',   'Mister',   'Mr.',   1, true, '2025-01-15 10:30:00', 'uuid-001'),
('MRS',  'Mrs.',  'Mistress', 'Mrs.',  2, true, '2025-01-15 10:30:00', 'uuid-002'),
('MISS', 'Miss',  'Miss',     'Miss',  3, true, '2025-01-15 10:30:00', 'uuid-003'),
('DR',   'Dr.',   'Doctor',   'Dr.',   4, true, '2025-01-15 10:30:00', 'uuid-004');

Usage Example: Resource with Label

Resource Entity (Core Database)

@Entity({
    name: 'resources',
    database: AppDatabases.APP_CORE,
})
export class Resource implements ITimestamp {
    @PrimaryGeneratedColumn('uuid')
    id: string;

    @Column({ type: 'varchar', length: 11, unique: true })
    reference_number: string;

    /**
     * Foreign key to labels_lookup (not labels in master DB)
     */
    @Column({
        type: 'varchar',
        length: 10,
        nullable: true,
        comment: 'References labels_lookup.code',
    })
    label_code: string | null;

    @Column({ type: 'varchar', length: 50 })
    first_name: string;

    @Column({ type: 'varchar', length: 50 })
    last_name: string;

    // ... other fields
}

SQL Query (Now Possible)

-- ✅ This query works because both tables are in app_core_db
SELECT
    r.reference_number,
    r.first_name,
    r.last_name,
    l.label,           -- From lookup table
    l.abbreviation     -- From lookup table
FROM resources r
LEFT JOIN labels_lookup l ON r.label_code = l.code
WHERE r.reference_number = 'REF001';

Result:

 reference_number | first_name | last_name | label | abbreviation
------------------+------------+-----------+-------+--------------
 REF001           | John       | Smith     | Mr.   | Mr.

Schema Comparison: Master vs Lookup

Aspect	Master Table (`labels`)	Lookup Table (`labels_lookup`)
Database	`app_master_db`	`app_core_db`
Primary Key	`id` (UUID)	`code` (VARCHAR)
Purpose	Source of truth, full CRUD	Read-only cache for queries
Ownership	MasterData Service	Data Owner BC (consumer)
Write Access	MasterData Service only	Sync Service only
Audit Fields	`created_by`, `updated_by`, `deleted_at`	None (simplified)
Sync Tracking	N/A	`synced_at`, `master_id`
Indexing	Standard B-tree on `id`, unique on `code`	Optimized for JOINs on `code`
Data Volume	Complete historical data	Active records only
Update Frequency	Low (admin changes)	Event-driven (within seconds)

More Lookup Table Examples

1. Regions Lookup

Master: app_master_db.regions

@Entity({ name: 'regions', database: AppDatabases.APP_MASTER })
export class Region {
    @PrimaryGeneratedColumn('uuid')
    id: string;

    @Column({ type: 'varchar', length: 10, unique: true })
    code: string; // e.g., "US-CA" for California

    @Column({ type: 'varchar', length: 100 })
    name: string; // "California"

    @Column({ type: 'varchar', length: 10 })
    zone: string; // "West", "East", "Central", etc.

    // ... audit fields
}

Lookup: app_core_db.regions_lookup

@Entity({ name: 'regions_lookup', database: AppDatabases.APP_CORE })
export class RegionLookup {
    @PrimaryColumn({ type: 'varchar', length: 10 })
    code: string;

    @Column({ type: 'varchar', length: 100 })
    name: string;

    @Column({ type: 'varchar', length: 10 })
    zone: string;

    @UpdateDateColumn({ type: 'timestamptz' })
    synced_at: Date;

    @Column({ type: 'uuid', nullable: true })
    master_id: string | null;
}

Usage:

-- Resource addresses with region names
SELECT
    r.reference_number,
    r.first_name,
    a.address_line1,
    rg.name AS region_name
FROM resources r
LEFT JOIN resource_addresses a ON r.id = a.resource_id
LEFT JOIN regions_lookup rg ON a.region_code = rg.code;

2. Classification Codes Lookup

Master: app_master_db.classification_codes

@Entity({ name: 'classification_codes', database: AppDatabases.APP_MASTER })
export class ClassificationCode {
    @PrimaryGeneratedColumn('uuid')
    id: string;

    @Column({ type: 'varchar', length: 10, unique: true })
    code: string; // e.g., "E11.9"

    @Column({ type: 'text' })
    description: string; // "Type 2 diabetes mellitus without complications"

    @Column({ type: 'varchar', length: 50 })
    category: string; // "Endocrine, nutritional and metabolic diseases"

    // ... audit fields
}

Lookup: app_core_db.classification_codes_lookup

@Entity({ name: 'classification_codes_lookup', database: AppDatabases.APP_CORE })
export class ClassificationCodeLookup {
    @PrimaryColumn({ type: 'varchar', length: 10 })
    code: string;

    @Column({ type: 'text' })
    description: string;

    @Column({ type: 'varchar', length: 50 })
    category: string;

    @UpdateDateColumn({ type: 'timestamptz' })
    synced_at: Date;

    @Column({ type: 'uuid', nullable: true })
    master_id: string | null;
}