CQRS Implementation for Leave Management Module

​

Overview

​

Architecture

​

Components

​

1. Read Models (Denormalized)

• Denormalized with user info (name, email)
• Denormalized with leave type info (name, code)
• Includes computed fields: year, month, is_active
• Optimized for fast queries

• Denormalized with user and leave type info
• Includes computed fields:
  • usage_percentage = (used_days / total_days) * 100
  • is_low_balance = remaining_days < 3
• Enables quick dashboard queries

​

2. Query Repositories

• FindRequestsByUser - Get all requests for a user
• FindRequestsByStatus - Filter by PENDING/APPROVED/REJECTED
• FindRequestsByDateRange - Filter by date range
• FindPendingRequests - Quick access to pending requests

• FindBalancesByUser - Get all balances for a user
• FindLowBalances - Find users with low balances (< 3 days)

​

3. Query Service

• ListUserLeaveRequests - Paginated user requests with filters
• ListPendingRequests - Paginated pending requests
• GetUserBalances - Get all balances for a user
• ListLowBalances - Paginated low balance alerts

​

4. Event Consumer

• Listens to LeaveRequestCreated events
• Listens to LeaveBalanceUpdated events
• Automatically syncs read models when write operations occur
• Ensures eventual consistency

​

Benefits

​

✅ Performance

• Fast Reads: Denormalized data eliminates joins
• Optimized Indexes: Read collections can have different indexes
• Computed Fields: Pre-calculated values (usage_percentage, is_low_balance)

​

✅ Scalability

• Separate Scaling: Read and write databases can scale independently
• Read Replicas: Easy to add read replicas for query workload
• Caching: Read models are cache-friendly

​

✅ Flexibility

• Different Data Models: Write model (normalized) vs Read model (denormalized)
• Query Optimization: Each query can have its own optimized structure
• Multiple Read Models: Can create different read models for different use cases

​

Collections

​

Write Tables (Normalized - Postgres)

leave_requests       -- Command model
leave_balances       -- Command model
leave_types          -- Reference data

​

Read Tables (Denormalized - Postgres)

leave_requests_read  -- Query model with denormalized user info
leave_balances_read  -- Query model with pre-computed summaries

​

Identity Tables (Read-Only - Better Auth)

users                -- Platform users
organizations        -- Organizations
members              -- Organization memberships & roles

​

Usage Examples

​

Command (Write)

// Create leave request (writes to normalized DB)
request, err := leaveService.CreateLeaveRequest(ctx, params)
// Publishes "LeaveRequestCreated" event

​

Query (Read)

// List user requests (reads from denormalized DB)
result, err := leaveQueryService.ListUserLeaveRequests(ctx, userID, orgID, params)

// Get low balances (uses pre-computed is_low_balance field)
balances, err := leaveQueryService.ListLowBalances(ctx, orgID, year, params)

​

Event Flow

1. Command → Write to normalized DB → Publish event
2. Event Consumer → Listen to event → Update read model
3. Query → Read from denormalized DB → Return fast results

​

Indexes (Recommended)

​

leave_requests_read

CREATE INDEX idx_leave_requests_read_user_org ON leave_requests_read (user_id, organization_id, created_at DESC);
CREATE INDEX idx_leave_requests_read_org_status ON leave_requests_read (organization_id, status, created_at DESC);

​

leave_balances_read

CREATE INDEX idx_leave_balances_read_user_org ON leave_balances_read (user_id, organization_id, year);

​

Next Steps

1. Publish Events: Update LeaveService to publish events when creating/updating requests
2. Add Indexes: Create database indexes for optimal query performance
3. Add Query Endpoints: Create HTTP handlers for query operations
4. Monitoring: Add metrics for read/write performance
5. Caching: Add Redis caching layer for frequently accessed queries

​

Trade-offs

​

Pros

• ✅ Extremely fast reads
• ✅ Scalable architecture
• ✅ Flexible query models

​

Cons

• ❌ Eventual consistency (small delay between write and read)
• ❌ More complex (two data models to maintain)
• ❌ Storage overhead (data duplication)