10 - Database & Sync Tables

PostgreSQL schema with 75 tables managed via EF Core. Plus 22 reference tables that must be synced from the existing MOSL data lake / legacy DB.

Database Setup

Table Categories (75 tables total)

Key Tables - Detailed

leads

The root entity. Every journey starts with a row here.

provider_configurations

Runtime provider chain config. Read by ProviderChainExecutor on every call.

api_audit_log

Every external provider call is logged here with full request/response for audit and debugging.

journey_activity_logs

Human-readable activity log per lead. Powered by async channel + ActivityLogWriterService hosted service for batched writes.

bank_auto_verifies

Results of Decentro mobile-to-bank auto-verify. Triggered at Stage 2 (OTP verified). Contains encrypted account number, IFSC, beneficiary name, account type. BankVerificationService.GetBankDetailsAsync() auto-promotes successful active savings records to a verified bank_accounts row.

Sync Tables (From Data Lake / Legacy DB)

The authoritative list and sync strategy is documented in the BRD Analysis folder:

• Sync Tables Master List - the full 22-table list with source system, sync frequency, owner, and status
• DB Actions Checklist - all DB setup tasks required for go-live
• PostgreSQL Compatibility Audit - Oracle/SQL Server → PostgreSQL porting notes

Sync Strategy Summary

Why sync tables matter

• Dedup & fraud checks: suspicious_contacts, negative_list_entries, duplicate_bypass_whitelist need to be populated or dedup checks will miss bad actors
• Client code resolution: client_master and branch_master drive Stage 14 account creation; stale data causes CBOS failures
• PAN whitelists: employee_pan_master, franchise_pan_whitelist bypass certain checks for internal users
• IFSC lookup: bank_ifsc_masters powers auto-fill of bank and branch name at Stage 6
• Pincode / state / city: used in address validation and manual entry forms
• KRA status codes: nsdl_error_masters, cvl_status_mappings decode provider response codes into user-friendly messages

For the detailed list of each table - its columns, source system, sync job details, and expected row count - open the Sync Tables Master List.

EF Core DbContext

public class EkycDbContext : DbContext
{
    public DbSet<Lead> Leads => Set<Lead>();
    public DbSet<LeadSession> LeadSessions => Set<LeadSession>();
    public DbSet<OtpVerification> OtpVerifications => Set<OtpVerification>();
    public DbSet<PanVerification> PanVerifications => Set<PanVerification>();
    public DbSet<BankAccount> BankAccounts => Set<BankAccount>();
    public DbSet<BankAutoVerify> BankAutoVerifies => Set<BankAutoVerify>();
    public DbSet<ProviderConfiguration> ProviderConfigurations => Set<ProviderConfiguration>();
    public DbSet<ApiAuditLog> ApiAuditLogs => Set<ApiAuditLog>();
    // ... 75 DbSets total

    protected override void OnModelCreating(ModelBuilder mb)
    {
        mb.HasDefaultSchema("ekyc");
        mb.ApplyConfigurationsFromAssembly(typeof(EkycDbContext).Assembly);
    }

    public override async Task<int> SaveChangesAsync(CancellationToken ct = default)
    {
        // Auto-update Lead.UpdatedAt on any modification
        foreach (var entry in ChangeTracker.Entries<Lead>())
            if (entry.State == EntityState.Modified)
                entry.Entity.UpdatedAt = DateTimeOffset.UtcNow;

        return await base.SaveChangesAsync(ct);
    }
}

Useful DB Queries

SELECT current_stage, COUNT(*) AS count
FROM ekyc.leads
WHERE created_at::date = CURRENT_DATE
GROUP BY current_stage
ORDER BY current_stage;

SELECT from_state, to_state, trigger_action, transitioned_at
FROM ekyc.lead_state_transitions
WHERE lead_id = '8f2a1c9e-...'
ORDER BY transitioned_at;

SELECT provider_name, success_count, failure_count,
       avg_duration_ms, last_failure_at
FROM ekyc.provider_health_metrics
ORDER BY success_count DESC;

SELECT provider, api_name, response_status_code, duration_ms, is_success, is_mock, created_at
FROM ekyc.api_audit_log
WHERE lead_id = '8f2a1c9e-...'
ORDER BY created_at DESC;