PTG.PPPlus3 | Pension Management System

Thời gian: 03/2023 – 10/2024
Vai trò: Backend Developer
Công ty: FPT Software
Dự án: Pension Management System (PMS) cho 200+ team members

Tổng quan dự án

Bối cảnh

Pension Management System (PMS) là hệ thống toàn diện quản lý lương hưu cho tổ chức lớn với:

• Hàng trăm ngàn members
• Phức tạp trong tính toán benefits
• Nhiều regulatory requirements
• Integration với nhiều external systems (tax, banking, HR)

Vấn đề chính

Hệ thống ban đầu gặp các vấn đề về performance:

1. Report generation: Mất 15-20 giây để tạo báo cáo
2. Calculation performance: Tính toán pension payout mất 5-10 giây cho mỗi member
3. Database load: Heavy queries làm chậm hệ thống trong giờ cao điểm
4. User experience: Users phải wait cho synchronous processing

Yêu cầu

Functional Requirements

1. Member Management

   • CRUD operations cho members
   • Employment history tracking
   • Salary history và contribution tracking

2. Benefit Calculation

   • Pension payout calculation (nhiều công thức phức tạp)
   • COLA (Cost of Living Adjustment)
   • Survivor benefits
   • Lump-sum payments

3. Reporting

   • Monthly/Quarterly/Annual reports
   • Regulatory reports (government compliance)
   • Ad-hoc reports với custom filters
   • Export (PDF, Excel, CSV)

4. Payment Processing

   • Monthly payment runs
   • Direct deposit integration
   • Payment adjustments
   • Arrears calculation

5. Integration

   • HR systems (employee data)
   • Tax authorities (tax reporting)
   • Banks (payment processing)
   • Insurance companies

Non-Functional Requirements

• Performance: Report generation < 3s, Calculation < 1s
• Accuracy: 100% chính xác cho financial calculations
• Auditability: Full audit trail cho mọi transaction
• Scalability: Support 500,000+ members
• Availability: 99.95% uptime (critical financial system)

Kiến trúc & Công nghệ

Technology Stack

┌─────────────────────────────────────────────────────────────────────┐
│                         Frontend (Angular)                           │
│  ┌──────────────┐  ┌──────────────┐  ┌──────────────────────────┐   │
│  │  Member      │  │  Calculation │  │  Reporting               │   │
│  │  Management  │  │  Simulator   │  │  Dashboard               │   │
│  └──────────────┘  └──────────────┘  └──────────────────────────┘   │
└─────────────────────────────────────────────────────────────────────┘
                            ↕ REST API / SignalR
┌─────────────────────────────────────────────────────────────────────┐
│                     Backend (.NET Core)                               │
│  ┌──────────────┐  ┌──────────────┐  ┌──────────────────────────┐   │
│  │  Member      │  │  Calculation │  │  Report                  │   │
│  │  Service     │  │  Engine      │  │  Generation Service      │   │
│  └──────────────┘  └──────────────┘  └──────────────────────────┘   │
│  ┌──────────────┐  ┌──────────────┐  ┌──────────────────────────┐   │
│  │  Payment     │  │  Integration │  │  Audit                   │   │
│  │  Service     │  │  Service     │  │  Service                 │   │
│  └──────────────┘  └──────────────┘  └──────────────────────────┘   │
└─────────────────────────────────────────────────────────────────────┘
         ↕                                       ↕
┌─────────────────────┐              ┌──────────────────────────────┐
│   Data Stores       │              │   Background Processing      │
│  ┌──────────────┐   │              │  ┌────────────────────────┐  │
│  │  SQL Server  │   │              │  │  Azure Service Bus     │  │
│  │  (Primary DB)│   │              │  │  (Queues & Topics)     │  │
│  └──────────────┘   │              │  └────────────────────────┘  │
│  ┌──────────────┐   │              │         ↕                     │
│  │  Redis       │   │              │  ┌────────────────────────┐  │
│  │  (Cache)    │   │              │  │  Hangfire              │  │
│  └──────────────┘   │              │  │  (Job Scheduler)       │  │
│  ┌──────────────┐   │              │  └────────────────────────┘  │
│  │  Azure Blob  │   │              │                              │
│  │  (Reports)   │   │              │                              │
│  └──────────────┘   │              │                              │
└─────────────────────┘              └──────────────────────────────┘

Giải pháp kỹ thuật chi tiết

1. Background Job Architecture cho Report Generation

Vấn đề: Report generation synchronous làm blocking API responses.

Giải pháp: Event-driven background processing với progress tracking.

// Report Generation Request/Response
public class ReportGenerationRequest
{
    public string ReportType { get; set; } // MonthlyStatement, AnnualSummary, Regulatory
    public DateTime FromDate { get; set; }
    public DateTime ToDate { get; set; }
    public ReportFormat Format { get; set; } // PDF, Excel, CSV
    public ReportFilter Filters { get; set; }
    public string RequestedBy { get; set; }
}

public class ReportJobDto
{
    public string JobId { get; set; }
    public string ReportType { get; set; }
    public JobStatus Status { get; set; } // Pending, Processing, Completed, Failed
    public int ProgressPercentage { get; set; }
    public string BlobUrl { get; set; } // Download URL khi hoàn thành
    public DateTime CreatedAt { get; set; }
    public DateTime? CompletedAt { get; set; }
    public string ErrorMessage { get; set; }
}

// Report Service với Background Job
public interface IReportGenerationService
{
    Task<ReportJobDto> RequestReportAsync(ReportGenerationRequest request);
    Task<ReportJobDto> GetJobStatusAsync(string jobId);
    Task<byte[]> GetReportResultAsync(string jobId);
}

public class ReportGenerationService : IReportGenerationService
{
    private readonly IReportJobRepository _jobRepository;
    private readonly IBackgroundJobClient _jobClient;
    private readonly IBlobStorageService _blobStorage;
    private readonly ISignalRHubContext<ReportHub> _hubContext;
    
    public async Task<ReportJobDto> RequestReportAsync(ReportGenerationRequest request)
    {
        // Create job record
        var job = new ReportJob
        {
            Id = Guid.NewGuid().ToString(),
            ReportType = request.ReportType,
            Status = JobStatus.Pending,
            ProgressPercentage = 0,
            RequestData = JsonSerializer.Serialize(request),
            RequestedBy = request.RequestedBy,
            CreatedAt = DateTime.UtcNow
        };
        
        await _jobRepository.CreateAsync(job);
        
        // Queue background job với priority
        var queueName = GetQueueNameForReportType(request.ReportType);
        await _jobClient.Enqueue(
            queueName,
            () => ProcessReportJobAsync(job.Id, request)
        );
        
        // Return job info immediately (non-blocking)
        return MapToDto(job);
    }
    
    private async Task ProcessReportJobAsync(string jobId, ReportGenerationRequest request)
    {
        try
        {
            // Update status to Processing
            await UpdateJobProgressAsync(jobId, JobStatus.Processing, 0);
            
            // Step 1: Fetch data in batches
            await UpdateJobProgressAsync(jobId, JobStatus.Processing, 10, "Fetching data...");
            
            var batches = await GetDataBatchesAsync(request);
            var totalBatches = batches.Count;
            var processedBatches = 0;
            
            // Step 2: Process each batch
            var reportData = new List<ReportDataRow>();
            foreach (var batch in batches)
            {
                var batchData = await ProcessBatchAsync(batch, request);
                reportData.AddRange(batchData);
                
                processedBatches++;
                var progress = 10 + (processedBatches * 70 / totalBatches); // 10-80%
                await UpdateJobProgressAsync(jobId, JobStatus.Processing, progress);
                
                // Real-time progress update via SignalR
                await _hubContext.Clients.User(request.RequestedBy)
                    .SendAsync("ReportProgress", new
                    {
                        JobId = jobId,
                        Progress = progress,
                        Message = $"Processed {processedBatches}/{totalBatches} batches"
                    });
            }
            
            // Step 3: Generate report file
            await UpdateJobProgressAsync(jobId, JobStatus.Processing, 85, "Generating report...");
            
            byte[] reportFile;
            switch (request.Format)
            {
                case ReportFormat.PDF:
                    reportFile = await GeneratePdfReportAsync(reportData, request);
                    break;
                case ReportFormat.Excel:
                    reportFile = await GenerateExcelReportAsync(reportData, request);
                    break;
                case ReportFormat.CSV:
                    reportFile = await GenerateCsvReportAsync(reportData, request);
                    break;
                default:
                    throw new InvalidOperationException($"Unsupported format: {request.Format}");
            }
            
            // Step 4: Upload to Blob Storage
            await UpdateJobProgressAsync(jobId, JobStatus.Processing, 95, "Uploading report...");
            
            var blobName = $"reports/{jobId}/{GetFileName(request)}";
            var blobUrl = await _blobStorage.UploadAsync(blobName, reportFile, new Dictionary<string, string>
            {
                ["ContentType"] = GetContentType(request.Format),
                ["JobId"] = jobId,
                ["ReportType"] = request.ReportType
            });
            
            // Step 5: Mark job as completed
            await UpdateJobCompletedAsync(jobId, blobUrl);
            
            // Notify completion via SignalR
            await _hubContext.Clients.User(request.RequestedBy)
                .SendAsync("ReportCompleted", new { JobId = jobId, BlobUrl = blobUrl });
        }
        catch (Exception ex)
        {
            await UpdateJobFailedAsync(jobId, ex.Message);
            
            await _hubContext.Clients.User(request.RequestedBy)
                .SendAsync("ReportFailed", new { JobId = jobId, Error = ex.Message });
            
            throw;
        }
    }
    
    private async Task<List<DataBatch>> GetDataBatchesAsync(ReportGenerationRequest request)
    {
        // Split data into manageable batches (1000 records each)
        var totalCount = await GetTotalRecordCountAsync(request);
        var batchSize = 1000;
        var batches = new List<DataBatch>();
        
        for (int skip = 0; skip < totalCount; skip += batchSize)
        {
            batches.Add(new DataBatch
            {
                Skip = skip,
                Take = batchSize,
                Filters = request.Filters
            });
        }
        
        return batches;
    }
    
    private async Task<List<ReportDataRow>> ProcessBatchAsync(DataBatch batch, ReportGenerationRequest request)
    {
        // Use batching techniques để giảm database round trips
        var query = BuildReportQuery(request)
            .Skip(batch.Skip)
            .Take(batch.Take);
        
        // Use AsNoTracking() cho read-only queries
        // Use Bulk fetch thay vì N+1
        return await query
            .AsNoTracking()
            .ToListAsync();
    }
    
    private async Task UpdateJobProgressAsync(
        string jobId, 
        JobStatus status, 
        int progress, 
        string message = null)
    {
        await _jobRepository.UpdateAsync(jobId, new ReportJobUpdate
        {
            Status = status,
            ProgressPercentage = progress,
            StatusMessage = message
        });
    }
    
    private async Task UpdateJobCompletedAsync(string jobId, string blobUrl)
    {
        await _jobRepository.UpdateAsync(jobId, new ReportJobUpdate
        {
            Status = JobStatus.Completed,
            ProgressPercentage = 100,
            BlobUrl = blobUrl,
            CompletedAt = DateTime.UtcNow
        });
    }
    
    private async Task UpdateJobFailedAsync(string jobId, string errorMessage)
    {
        await _jobRepository.UpdateAsync(jobId, new ReportJobUpdate
        {
            Status = JobStatus.Failed,
            ErrorMessage = errorMessage
        });
    }
}

// Hangfire configuration cho priority queues
public static class HangfireConfig
{
    public static void Configure(IServiceCollection services)
    {
        services.AddHangfire(config => config
            .UseSqlServerStorage(connectionString)
            .UseFilter(new AutomaticRetryAttribute { Attempts = 3 })
            .UseFilter(new LogJobFilter()));
        
        services.AddHangfireServer(options =>
        {
            // Configure queues với priorities
            options.Queues = new[]
            {
                "critical",    // Priority 1: Payment processing
                "high",        // Priority 2: Regulatory reports
                "default",     // Priority 3: Regular reports
                "background"   // Priority 4: Cleanup jobs
            };
            options.WorkerCount = Environment.ProcessorCount * 2;
        });
    }
}

2. Pre-calculation Strategy cho Pension Calculations

Vấn đề: Tính toán pension payout phức tạp mất 5-10 giây cho mỗi member.

Giải pháp: Shift từ on-the-fly calculation sang pre-calculation với event-driven updates.

// Calculation Result Cache
public class PensionCalculationResult
{
    public int MemberId { get; set; }
    public decimal MonthlyBenefit { get; set; }
    public decimal LumpSumAmount { get; set; }
    public DateTime CalculationDate { get; set; }
    public string CalculationVersion { get; set; } // Version of calculation rules
    public Dictionary<string, object> InputData { get; set; } // Snapshot of inputs
    public Dictionary<string, decimal> Breakdown { get; set; } // Line items
    public bool IsValid { get; set; }
    public DateTime ValidUntil { get; set; }
}

// Pre-calculation Service
public interface IPensionPreCalculationService
{
    Task ScheduleCalculationAsync(int memberId, CalculationTrigger trigger);
    Task ScheduleBatchCalculationAsync(CalculationBatchRequest batch);
    Task<PensionCalculationResult> GetCachedResultAsync(int memberId);
    Task RecalculateAllAsync(DateTime effectiveDate);
}

public class PensionPreCalculationService : IPensionPreCalculationService
{
    private readonly ICalculationRepository _calcRepository;
    private readonly IMemberRepository _memberRepository;
    private readonly IBackgroundJobClient _jobClient;
    private readonly IEventBus _eventBus;
    private readonly IPensionCalculationEngine _calculationEngine;
    private readonly IDistributedCache _cache;
    
    // Schedule calculation khi có thay đổi
    public async Task ScheduleCalculationAsync(int memberId, CalculationTrigger trigger)
    {
        // Check if already scheduled
        var existing = await _calcRepository.GetPendingCalculationAsync(memberId);
        if (existing != null)
        {
            // Update existing with new trigger
            existing.Triggers.Add(trigger);
            await _calcRepository.UpdatePendingCalculationAsync(existing);
            return;
        }
        
        // Create new pending calculation
        var pendingCalc = new PendingCalculation
        {
            MemberId = memberId,
            Triggers = new List<CalculationTrigger> { trigger },
            ScheduledAt = DateTime.UtcNow,
            Status = CalculationStatus.Pending
        };
        
        await _calcRepository.CreatePendingCalculationAsync(pendingCalc);
        
        // Schedule job (run immediately or batch)
        if (trigger.IsUrgent)
        {
            // Urgent: Calculate within 5 minutes
            await _jobClient.Enqueue<ICalculationProcessor>(processor =>
                processor.CalculateMemberAsync(memberId)
            );
        }
        else
        {
            // Non-urgent: Batch with others, run at off-peak hours
            await _jobClient.Schedule<ICalculationProcessor>(processor =>
                processor.CalculateMemberAsync(memberId),
                TimeSpan.FromMinutes(30)
            );
        }
    }
    
    // Batch calculation cho scheduled jobs
    public async Task ScheduleBatchCalculationAsync(CalculationBatchRequest batch)
    {
        // Group by calculation type
        var groupedJobs = batch.MemberIds.GroupBy(m => m.CalculationType);
        
        foreach (var group in groupedJobs)
        {
            // Create batch job
            var batchJob = new CalculationBatchJob
            {
                Id = Guid.NewGuid().ToString(),
                CalculationType = group.Key,
                MemberIds = group.Select(m => m.MemberId).ToList(),
                EffectiveDate = batch.EffectiveDate,
                Status = BatchJobStatus.Scheduled,
                ScheduledRunTime = batch.RunAt
            };
            
            await _calcRepository.CreateBatchJobAsync(batchJob);
            
            // Schedule with Hangfire
            await _jobClient.Schedule<ICalculationProcessor>(processor =>
                processor.ProcessBatchAsync(batchJob.Id),
                batch.RunAt - DateTime.UtcNow
            );
        }
    }
    
    // Get cached result
    public async Task<PensionCalculationResult> GetCachedResultAsync(int memberId)
    {
        // Try cache first
        var cacheKey = $"pension:calc:{memberId}";
        var cached = await _cache.GetAsync<PensionCalculationResult>(cacheKey);
        if (cached != null && cached.IsValid && cached.ValidUntil > DateTime.UtcNow)
        {
            return cached;
        }
        
        // Fallback to database
        var result = await _calcRepository.GetLatestCalculationAsync(memberId);
        if (result != null)
        {
            // Cache for next time
            await _cache.SetAsync(cacheKey, result, TimeSpan.FromHours(1));
        }
        
        return result;
    }
    
    // Recalculate all members (for rule changes)
    public async Task RecalculateAllAsync(DateTime effectiveDate)
    {
        var memberIds = await _memberRepository.GetAllMemberIdsAsync();
        
        // Chunk into batches of 1000
        var batches = memberIds.Chunk(1000).Select((chunk, index) => new CalculationBatchRequest
        {
            MemberIds = chunk.Select(id => new MemberCalculationItem { MemberId = id }).ToList(),
            EffectiveDate = effectiveDate,
            RunAt = effectiveDate.AddDays(-1).AddHours(2) // Run at 2 AM before effective date
        }).ToList();
        
        foreach (var batch in batches)
        {
            await ScheduleBatchCalculationAsync(batch);
        }
    }
}

// Calculation Processor
public interface ICalculationProcessor
{
    Task CalculateMemberAsync(int memberId);
    Task ProcessBatchAsync(string batchJobId);
}

public class PensionCalculationProcessor : ICalculationProcessor
{
    private readonly IPensionCalculationEngine _engine;
    private readonly ICalculationRepository _repository;
    private readonly IDistributedCache _cache;
    private readonly ILogger<PensionCalculationProcessor> _logger;
    
    public async Task CalculateMemberAsync(int memberId)
    {
        try
        {
            // Fetch member data
            var member = await _repository.GetMemberDataAsync(memberId);
            if (member == null) return;
            
            // Get pending triggers
            var pendingCalc = await _repository.GetPendingCalculationAsync(memberId);
            if (pendingCalc == null) return;
            
            // Run calculation
            var result = await _engine.CalculateAsync(member, pendingCalc.Triggers);
            
            // Save result
            await _repository.SaveCalculationResultAsync(result);
            
            // Update cache
            var cacheKey = $"pension:calc:{memberId}";
            await _cache.SetAsync(cacheKey, result, TimeSpan.FromHours(24));
            
            // Mark pending as completed
            await _repository.MarkCalculationCompletedAsync(memberId);
            
            // Publish event for downstream systems
            await _eventBus.PublishAsync(new PensionCalculatedEvent
            {
                MemberId = memberId,
                NewBenefit = result.MonthlyBenefit,
                EffectiveDate = result.CalculationDate,
                PreviousBenefit = pendingCalc.PreviousBenefit
            });
            
            _logger.LogInformation("Calculated pension for member {MemberId}. New benefit: {Benefit}", 
                memberId, result.MonthlyBenefit);
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Failed to calculate pension for member {MemberId}", memberId);
            
            // Mark as failed for manual review
            await _repository.MarkCalculationFailedAsync(memberId, ex.Message);
            throw;
        }
    }
    
    public async Task ProcessBatchAsync(string batchJobId)
    {
        var batchJob = await _repository.GetBatchJobAsync(batchJobId);
        if (batchJob == null) return;
        
        _logger.LogInformation("Starting batch calculation job {JobId} with {Count} members", 
            batchJobId, batchJob.MemberIds.Count);
        
        var stopwatch = Stopwatch.StartNew();
        var successCount = 0;
        var failureCount = 0;
        
        foreach (var memberId in batchJob.MemberIds)
        {
            try
            {
                await CalculateMemberAsync(memberId);
                successCount++;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Failed to calculate member {MemberId} in batch", memberId);
                failureCount++;
            }
            
            // Update batch progress
            var progress = (successCount + failureCount) * 100 / batchJob.MemberIds.Count;
            await _repository.UpdateBatchJobProgressAsync(batchJobId, progress);
        }
        
        stopwatch.Stop();
        
        await _repository.CompleteBatchJobAsync(batchJobId, successCount, failureCount, stopwatch.Elapsed);
        
        _logger.LogInformation(
            "Batch job {JobId} completed. Success: {Success}, Failed: {Failed}, Duration: {Duration}", 
            batchJobId, successCount, failureCount, stopwatch.Elapsed);
    }
}

// Calculation Engine với Strategy Pattern
public interface IPensionCalculationEngine
{
    Task<PensionCalculationResult> CalculateAsync(MemberData member, List<CalculationTrigger> triggers);
}

public class PensionCalculationEngine : IPensionCalculationEngine
{
    private readonly IEnumerable<ICalculationStrategy> _strategies;
    private readonly ICalculationRuleContext _context;
    
    public async Task<PensionCalculationResult> CalculateAsync(
        MemberData member, 
        List<CalculationTrigger> triggers)
    {
        // Select appropriate strategy based on member type
        var strategy = _strategies.FirstOrDefault(s => s.CanHandle(member));
        if (strategy == null)
        {
            throw new InvalidOperationException($"No calculation strategy found for member type {member.MemberType}");
        }
        
        // Build calculation context
        var context = new CalculationContext
        {
            Member = member,
            Triggers = triggers,
            EffectiveDate = triggers.Max(t => t.EffectiveDate),
            PreviousResult = await GetPreviousCalculationAsync(member.Id)
        };
        
        // Execute calculation
        var result = await strategy.CalculateAsync(context);
        
        // Validate result
        ValidateCalculation(result, context);
        
        return result;
    }
    
    private void ValidateCalculation(PensionCalculationResult result, CalculationContext context)
    {
        // Business rules validation
        if (result.MonthlyBenefit < 0)
        {
            throw new CalculationValidationException("Monthly benefit cannot be negative");
        }
        
        if (result.MonthlyBenefit > context.Member.SalaryHistory.Max() * 0.9m)
        {
            // Warning: Benefit > 90% of max salary (unusual)
            _logger.LogWarning("Unusual benefit for member {MemberId}: {Benefit}", 
                context.Member.Id, result.MonthlyBenefit);
        }
    }
}

// Event triggers cho recalculation
public class CalculationTrigger
{
    public TriggerType Type { get; set; }
    public DateTime EffectiveDate { get; set; }
    public bool IsUrgent { get; set; }
    public string TriggeredBy { get; set; } // User or System
    public Dictionary<string, object> Data { get; set; }
}

public enum TriggerType
{
    SalaryUpdate,
    ServiceYearAdded,
    RuleChange,
    MemberStatusChange,
    COLAAdjustment,
    YearEndRollover,
    ManualRecalculation
}

// Event handlers
public class MemberEventHandler
{
    private readonly IPensionPreCalculationService _preCalcService;
    
    [EventHandler]
    public async Task HandleMemberSalaryUpdatedAsync(MemberSalaryUpdatedEvent @event)
    {
        // Schedule recalculation when salary changes
        await _preCalcService.ScheduleCalculationAsync(@event.MemberId, new CalculationTrigger
        {
            Type = TriggerType.SalaryUpdate,
            EffectiveDate = @event.EffectiveDate,
            IsUrgent = false, // Can be batched
            Data = new Dictionary<string, object>
            {
                ["OldSalary"] = @event.OldSalary,
                ["NewSalary"] = @event.NewSalary
            }
        });
    }
    
    [EventHandler]
    public async Task HandleYearEndRolloverAsync(YearEndRolloverEvent @event)
    {
        // Schedule recalculation for all active members
        await _preCalcService.ScheduleBatchCalculationAsync(new CalculationBatchRequest
        {
            MemberIds = await GetAllActiveMemberIdsAsync(),
            EffectiveDate = @event.EffectiveDate,
            RunAt = @event.EffectiveDate.AddDays(-1).AddHours(2) // 2 AM
        });
    }
}

3. Optimized Data Retrieval với Batch Queries

// Batch query optimization
public class OptimizedReportDataFetcher
{
    private readonly ApplicationDbContext _context;
    
    public async Task<List<ReportDataRow>> FetchReportDataAsync(ReportGenerationRequest request)
    {
        // BAD: N+1 queries
        // var members = await _context.Members.ToListAsync();
        // foreach (var member in members)
        // {
        //     member.SalaryHistory = await _context.SalaryHistory
        //         .Where(s => s.MemberId == member.Id)
        //         .ToListAsync();
        // }
        
        // GOOD: Single query with Include
        var members = await _context.Members
            .AsNoTracking()
            .Include(m => m.SalaryHistory)
            .Include(m => m.ServiceHistory)
            .Include(m => m.BeneficiaryDesignations)
            .Where(m => m.Status == MemberStatus.Active)
            .ToListAsync();
        
        // BAD: Multiple queries in loop
        // foreach (var memberId in memberIds)
        // {
        //     var calc = await _context.Calculations
        //         .Where(c => c.MemberId == memberId)
        //         .FirstOrDefaultAsync();
        // }
        
        // GOOD: Bulk fetch
        var memberIds = members.Select(m => m.Id).ToList();
        var calculations = await _context.Calculations
            .AsNoTracking()
            .Where(c => memberIds.Contains(c.MemberId))
            .ToListAsync();
        
        var calculationLookup = calculations.ToDictionary(c => c.MemberId);
        
        // Combine data
        return members.Select(m => new ReportDataRow
        {
            MemberId = m.Id,
            MemberName = m.FullName,
            MonthlyBenefit = calculationLookup.TryGetValue(m.Id, out var calc) 
                ? calc.MonthlyBenefit 
                : 0,
            LastSalary = m.SalaryHistory.Max(s => s.SalaryAmount),
            YearsOfService = CalculateYearsOfService(m.ServiceHistory)
        }).ToList();
    }
    
    // Split large queries into chunks
    public async Task<List<ReportDataRow>> FetchLargeReportAsync(ReportGenerationRequest request)
    {
        var totalCount = await GetTotalCountAsync(request);
        var batchSize = 5000;
        var allData = new List<ReportDataRow>();
        
        for (int skip = 0; skip < totalCount; skip += batchSize)
        {
            var batch = await _context.Members
                .AsNoTracking()
                .Skip(skip)
                .Take(batchSize)
                .Select(m => new ReportDataRow
                {
                    MemberId = m.Id,
                    MemberName = m.FullName,
                    // ... projection
                })
                .ToListAsync();
            
            allData.AddRange(batch);
            
            // Log progress
            _logger.LogInformation("Fetched {Count} records, total so far: {Total}", 
                batch.Count, allData.Count);
        }
        
        return allData;
    }
}

// SqlBulkCopy for intermediate storage
public class BulkDataLoader
{
    public async Task LoadToStagingAsync(List<ReportDataRow> data, string stagingTable)
    {
        using var connection = new SqlConnection(connectionString);
        await connection.OpenAsync();
        
        using var bulkCopy = new SqlBulkCopy(connection)
        {
            DestinationTableName = stagingTable,
            BatchSize = 10000,
            BulkCopyTimeout = 300
        };
        
        // Map columns
        bulkCopy.ColumnMappings.Add("MemberId", "MemberId");
        bulkCopy.ColumnMappings.Add("MemberName", "MemberName");
        bulkCopy.ColumnMappings.Add("MonthlyBenefit", "MonthlyBenefit");
        // ...
        
        // Convert to DataTable
        var table = new DataTable();
        table.Columns.Add("MemberId", typeof(int));
        table.Columns.Add("MemberName", typeof(string));
        table.Columns.Add("MonthlyBenefit", typeof(decimal));
        // ...
        
        foreach (var row in data)
        {
            table.Rows.Add(row.MemberId, row.MemberName, row.MonthlyBenefit);
        }
        
        await bulkCopy.WriteToServerAsync(table);
    }
}

4. Report Generation với Pre-generated JSON

// Pre-generated JSON report storage
public interface IReportStorageService
{
    Task<string> GenerateAndStoreAsync(ReportGenerationRequest request);
    Task<byte[]> GetReportFileAsync(string reportId);
    Task<ReportMetadataDto> GetMetadataAsync(string reportId);
}

public class JsonReportStorageService : IReportStorageService
{
    private readonly IBlobStorageService _blobStorage;
    private readonly IReportDataSerializer _serializer;
    
    public async Task<string> GenerateAndStoreAsync(ReportGenerationRequest request)
    {
        var reportId = Guid.NewGuid().ToString();
        
        // Fetch data
        var data = await FetchReportDataAsync(request);
        
        // Serialize to JSON
        var jsonContent = _serializer.SerializeToJson(data, new JsonSerializerOptions
        {
            PropertyNamingPolicy = JsonNamingPolicy.CamelCase,
            WriteIndented = false // Minified for smaller size
        });
        
        // Compress with GZip
        var compressedContent = CompressGZip(jsonContent);
        
        // Store in Blob Storage
        var blobName = $"reports/pre-generated/{reportId}.json.gz";
        await _blobStorage.UploadAsync(blobName, compressedContent, new Dictionary<string, string>
        {
            ["ContentType"] = "application/json",
            ["ContentEncoding"] = "gzip",
            ["ReportType"] = request.ReportType,
            ["GeneratedAt"] = DateTime.UtcNow.ToString("O")
        });
        
        // Also generate PDF for download
        var pdfBytes = await GeneratePdfFromJsonAsync(jsonContent, request);
        var pdfBlobName = $"reports/pre-generated/{reportId}.pdf";
        await _blobStorage.UploadAsync(pdfBlobName, pdfBytes);
        
        // Store metadata
        await StoreReportMetadataAsync(new ReportMetadata
        {
            Id = reportId,
            ReportType = request.ReportType,
            GeneratedAt = DateTime.UtcNow,
            BlobName = blobName,
            PdfBlobName = pdfBlobName,
            RecordCount = data.Count,
            FileSizeBytes = compressedContent.Length
        });
        
        return reportId;
    }
    
    public async Task<byte[]> GetReportFileAsync(string reportId)
    {
        var metadata = await GetMetadataAsync(reportId);
        if (metadata == null) return null;
        
        // Return pre-generated PDF
        return await _blobStorage.DownloadAsync(metadata.PdfBlobName);
    }
    
    private byte[] CompressGZip(string content)
    {
        var bytes = Encoding.UTF8.GetBytes(content);
        using var output = new MemoryStream();
        using (var gzip = new GZipStream(output, CompressionLevel.Optimal))
        using (var input = new MemoryStream(bytes))
        {
            input.CopyTo(gzip);
        }
        return output.ToArray();
    }
}

// API Controller cho async report retrieval
[ApiController]
[Route("api/[controller]")]
public class ReportsController : ControllerBase
{
    private readonly IReportGenerationService _reportService;
    private readonly IReportStorageService _storageService;
    
    [HttpPost("generate")]
    [ProducesResponseType(typeof(ReportJobDto), 202)]
    public async Task<ActionResult<ReportJobDto>> GenerateReport(
        [FromBody] ReportGenerationRequest request)
    {
        // Queue report generation (non-blocking)
        var job = await _reportService.RequestReportAsync(request);
        
        // Return 202 Accepted with job location
        return AcceptedAtAction(
            nameof(GetJobStatus),
            new { jobId = job.JobId },
            job
        );
    }
    
    [HttpGet("{jobId}/status")]
    [ProducesResponseType(typeof(ReportJobDto), 200)]
    [ProducesResponseType(404)]
    public async Task<ActionResult<ReportJobDto>> GetJobStatus(string jobId)
    {
        var job = await _reportService.GetJobStatusAsync(jobId);
        if (job == null) return NotFound();
        
        return Ok(job);
    }
    
    [HttpGet("{jobId}/download")]
    [ProducesResponseType(typeof(FileContentResult), 200)]
    [ProducesResponseType(404)]
    public async Task<IActionResult> DownloadReport(string jobId)
    {
        var job = await _reportService.GetJobStatusAsync(jobId);
        if (job == null) return NotFound();
        
        if (job.Status != JobStatus.Completed)
        {
            return BadRequest("Report is not ready yet");
        }
        
        var fileBytes = await _storageService.GetReportFileAsync(jobId);
        if (fileBytes == null) return NotFound();
        
        return File(fileBytes, "application/pdf", $"report-{jobId}.pdf");
    }
}

Thách thức & Giải pháp

Challenge 1: Transactional Outbox Pattern cho Event Consistency

Vấn đề: Đảm bảo events không bị mất khi system crash sau khi update database.

Giải pháp:

public class TransactionalOutboxService
{
    private readonly ApplicationDbContext _context;
    private readonly IEventBus _eventBus;
    
    public async Task UpdateMemberAndPublishEventAsync(
        int memberId, 
        MemberUpdateRequest request)
    {
        using var transaction = await _context.Database.BeginTransactionAsync();
        
        try
        {
            // 1. Update member data
            var member = await _context.Members.FindAsync(memberId);
            member.Update(request);
            
            // 2. Create outbox event (in same transaction)
            var outboxEvent = new OutboxEvent
            {
                Id = Guid.NewGuid(),
                EventType = "MemberUpdated",
                Payload = JsonSerializer.Serialize(new MemberUpdatedEvent
                {
                    MemberId = memberId,
                    UpdatedFields = request.GetChangedFields(),
                    UpdatedAt = DateTime.UtcNow
                }),
                Status = OutboxEventStatus.Pending,
                CreatedAt = DateTime.UtcNow
            };
            
            _context.OutboxEvents.Add(outboxEvent);
            
            // 3. Commit transaction (atomic)
            await _context.SaveChangesAsync();
            await transaction.CommitAsync();
            
            // 4. Outbox processor will publish event asynchronously
        }
        catch
        {
            await transaction.RollbackAsync();
            throw;
        }
    }
}

// Background outbox processor
public class OutboxProcessor : BackgroundService
{
    private readonly ApplicationDbContext _context;
    private readonly IEventBus _eventBus;
    
    protected override async Task ExecuteAsync(CancellationToken stoppingToken)
    {
        while (!stoppingToken.IsCancellationRequested)
        {
            // Fetch pending events
            var pendingEvents = await _context.OutboxEvents
                .Where(e => e.Status == OutboxEventStatus.Pending)
                .OrderBy(e => e.CreatedAt)
                .Take(100)
                .ToListAsync();
            
            foreach (var evt in pendingEvents)
            {
                try
                {
                    // Publish event
                    var eventData = JsonSerializer.Deserialize(evt.Payload, evt.EventType);
                    await _eventBus.PublishAsync(eventData);
                    
                    // Mark as processed
                    evt.Status = OutboxEventStatus.Processed;
                    evt.ProcessedAt = DateTime.UtcNow;
                }
                catch (Exception ex)
                {
                    evt.Status = OutboxEventStatus.Failed;
                    evt.ErrorMessage = ex.Message;
                    evt.RetryCount++;
                    
                    if (evt.RetryCount >= 3)
                    {
                        // Move to dead letter after 3 retries
                        evt.Status = OutboxEventStatus.DeadLetter;
                    }
                }
            }
            
            await _context.SaveChangesAsync(stoppingToken);
            
            // Wait before next batch
            await Task.Delay(TimeSpan.FromSeconds(5), stoppingToken);
        }
    }
}

Challenge 2: Handling Calculation Rule Changes

Vấn đề: Khi regulatory rules thay đổi, cần recalculate hàng ngàn members.

Giải pháp: Versioned calculation rules với effective dating.

public class CalculationRule
{
    public int Id { get; set; }
    public string RuleCode { get; set; } // e.g., "BASE_PENSION_FORMULA"
    public string RuleName { get; set; }
    public string Formula { get; set; } // e.g., "avgSalary * yearsOfService * 0.02"
    public Dictionary<string, decimal> Parameters { get; set; }
    public DateTime EffectiveFrom { get; set; }
    public DateTime? EffectiveTo { get; set; }
    public bool IsActive { get; set; }
    public string Version { get; set; }
}

public async Task<PensionCalculationResult> CalculateWithVersioningAsync(
    MemberData member, 
    DateTime asOfDate)
{
    // Get rules effective at calculation date
    var rules = await _context.CalculationRules
        .Where(r => r.RuleCode == "BASE_PENSION_FORMULA"
                 && r.EffectiveFrom <= asOfDate
                 && (r.EffectiveTo == null || r.EffectiveTo > asOfDate)
                 && r.IsActive)
        .OrderByDescending(r => r.EffectiveFrom)
        .FirstOrDefaultAsync();
    
    if (rules == null)
    {
        throw new InvalidOperationException($"No rule found effective at {asOfDate}");
    }
    
    // Execute formula with parameters
    var result = ExecuteFormula(rules.Formula, member, rules.Parameters);
    result.RuleVersion = rules.Version;
    
    return result;
}

// When rules change, schedule recalculation
public async Task HandleRuleChangeAsync(RuleChangeEvent @event)
{
    // Find affected members
    var affectedMembers = await FindAffectedMembersAsync(@event.RuleCode);
    
    // Schedule recalculation with new effective date
    await _preCalcService.ScheduleBatchCalculationAsync(new CalculationBatchRequest
    {
        MemberIds = affectedMembers.Select(id => new MemberCalculationItem { MemberId = id }).ToList(),
        EffectiveDate = @event.NewRule.EffectiveFrom,
        RunAt = @event.NewRule.EffectiveFrom.AddDays(-1).AddHours(2)
    });
}

Kết quả & Impact

Metrics

Business Impact

• 50% reduction in report generation time
• 90% faster pension calculations
• 60% reduction in support tickets related to slow performance
• Zero data loss với transactional outbox pattern
• 100% audit compliance với full calculation history

Bài học kinh nghiệm

Technical Learnings

1. Pre-calculation is key: Don’t calculate on-the-fly cho complex business logic
2. Event-driven architecture: Decouple components với message queues
3. Batch processing: Group operations để giảm database round trips
4. Caching strategy: Multi-level caching (memory, distributed, database)
5. Observability: Instrument mọi thứ từ đầu (metrics, logs, traces)

Architecture Learnings

1. Transactional Outbox: Đảm bảo event consistency
2. Saga Pattern: Handle distributed transactions
3. CQRS: Separate read/write models cho optimization
4. Versioning: Rule versioning cho regulatory changes

Soft Skills

1. Working in large team: Clear APIs, documentation, code reviews
2. Stakeholder communication: Translate technical to business value
3. Incremental improvement: Optimize gradually, measure impact

Câu hỏi phỏng vấn

Q1: Bạn đã design background job architecture như thế nào?

A:

┌─────────────┐    ┌──────────────┐    ┌──────────────┐
│  API Layer  │ →  │  Job Queue   │ →  │  Job Worker  │
│  (Request)  │    │  (Service    │    │  (Hangfire)  │
│             │    │   Bus)       │    │              │
└─────────────┘    └──────────────┘    └──────────────┘
                          │                    │
                          ▼                    ▼
                   ┌──────────────┐    ┌──────────────┐
                   │  Job Status  │    │  Progress    │
                   │  Tracking    │    │  Updates     │
                   └──────────────┘    └──────────────┘

Key design decisions:

• Priority queues: Critical (payment), High (regulatory), Default, Background
• Retry policy: Exponential backoff với max 3 retries
• Idempotency: Job deduplication với unique job keys
• Monitoring: Application Insights + custom dashboards

Q2: Làm sao để đảm bảo calculation accuracy 100%?

A:

1. Unit tests: Test từng calculation component với known inputs/outputs
2. Integration tests: End-to-end tests với test data sets
3. Parallel run: Run new system alongside old system, compare results
4. Audit trail: Lưu full calculation history với input snapshots
5. Reconciliation: Daily reconciliation reports
6. Manual review: Flag unusual results (> 2 std dev from mean)

// Calculation audit log
public class CalculationAuditLog
{
    public int MemberId { get; set; }
    public DateTime CalculationDate { get; set; }
    public string RuleVersion { get; set; }
    public Dictionary<string, object> InputSnapshot { get; set; }
    public Dictionary<string, decimal> ResultBreakdown { get; set; }
    public decimal FinalBenefit { get; set; }
    public string CalculatedBy { get; set; } // User or System
    public string ReviewStatus { get; set; } // AutoApproved, ManualReview, Rejected
    public string ReviewNotes { get; set; }
}

Q3: Bạn xử lý concurrent updates như thế nào?

A:

// Optimistic concurrency với row version
public class Member
{
    public int Id { get; set; }
    public string Name { get; set; }
    // ...
    
    [Timestamp]
    public byte[] RowVersion { get; set; }
}

public async Task UpdateMemberAsync(int memberId, MemberUpdateRequest request)
{
    var member = await _context.Members.FindAsync(memberId);
    if (member == null) return;
    
    // Apply changes
    member.Update(request);
    
    try
    {
        await _context.SaveChangesAsync();
    }
    catch (DbUpdateConcurrencyException ex)
    {
        // Concurrency conflict
        _logger.LogWarning("Concurrency conflict for member {MemberId}", memberId);
        
        // Option 1: Reload and retry
        var entry = ex.Entries.Single();
        var databaseValues = await entry.GetDatabaseValuesAsync();
        
        if (databaseValues == null)
        {
            throw new NotFoundException($"Member {memberId} not found");
        }
        
        // Refresh original values
        entry.OriginalValues.SetValues(databaseValues);
        
        // Retry
        await _context.SaveChangesAsync();
        
        // Option 2: Return conflict to user
        // throw new ConcurrencyException("Record was modified by another user");
    }
}

Q4: Trade-offs giữa Pre-calculation vs On-the-fly calculation?

A:

Decision for Pension System:

• Read-heavy: Users query benefits frequently
• Complex calculation: Many factors, business rules
• Acceptable staleness: Results valid until next event → Pre-calculation wins

Q5: Nếu phải redesign lại system, bạn sẽ thay đổi gì?

A:

1. Event Sourcing: Store events instead of current state → full audit trail, temporal queries
2. GraphQL API: More flexible queries cho frontend
3. Microservices: Split by bounded contexts (Member, Calculation, Payment)
4. Data Lake: Store historical data cho analytics và ML
5. Better testing: More property-based tests cho calculation logic
6. Feature flags: Safer rollouts cho rule changes

← SKCC Project - FPT | Xem Interview Q&A →