Skip to main content

Skema Database Apache Fineract

Ringkasan Eksekutif​

Apache Fineract menggunakan arsitektur database multi-tenant dengan shared schema, di mana data dari multiple tenant disimpan dalam database yang sama dengan pemisahan melalui tenant identifier. Schema database dirancang untuk mendukung operasi keuangan mikro yang kompleks dengan integritas referensial yang kuat dan performance yang optimal.

Arsitektur Database Multi-Tenant​

Shared Database, Shared Schema Model​

Tenant Management Tables​

Tenant Store Structure​

-- Tenant store for multi-tenancy management
CREATE TABLE fineract_tenants (
    id INT PRIMARY KEY AUTO_INCREMENT,
    identifier VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(255) NOT NULL,
    timezone VARCHAR(100) DEFAULT 'UTC',
    country_code VARCHAR(10) DEFAULT 'US',
    version INT DEFAULT 1,
    created_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_by VARCHAR(255),
    last_modified_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    last_modified_by VARCHAR(255),
    is_deleted TINYINT(1) DEFAULT 0,
    checksum VARCHAR(64)
);

-- Database connection configuration per tenant
CREATE TABLE fineract_tenants_database_configuration (
    id INT PRIMARY KEY AUTO_INCREMENT,
    tenant_id INT NOT NULL,
    database_name VARCHAR(255) NOT NULL,
    connection_url VARCHAR(500),
    username VARCHAR(255),
    password_encrypted VARCHAR(255),
    pool_type VARCHAR(100) DEFAULT 'hikari',
    pool_size INT DEFAULT 10,
    validation_query VARCHAR(500) DEFAULT 'SELECT 1',
    is_active TINYINT(1) DEFAULT 1,
    created_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (tenant_id) REFERENCES fineract_tenants(id),
    UNIQUE KEY unique_tenant_id (tenant_id)
);

-- Schema version tracking
CREATE TABLE fineract_tenants_schema_version (
    tenant_id INT PRIMARY KEY,
    current_version VARCHAR(50),
    last_migration TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    migration_status ENUM('PENDING', 'IN_PROGRESS', 'COMPLETED', 'FAILED') DEFAULT 'PENDING',
    migration_notes TEXT,
    FOREIGN KEY (tenant_id) REFERENCES fineract_tenants(id)
);

Core Schema Structure​

1. Master Data Tables​

Office/Hierarchy Management​

-- Office/branch structure
CREATE TABLE m_office (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    external_id VARCHAR(100) UNIQUE,
    name VARCHAR(100) NOT NULL,
    name_decorated VARCHAR(100),
    office_type ENUM('OFFICE') DEFAULT 'OFFICE',
    opening_date DATE NOT NULL,
    hierarchy VARCHAR(50) NOT NULL,
    parent_id BIGINT,
    image_key BIGINT,
    is_deleted TINYINT(1) DEFAULT 0,
    inheritance_path VARCHAR(400),
    created_by BIGINT NOT NULL,
    created_date DATETIME NOT NULL,
    last_updated_by BIGINT,
    last_updated_date DATETIME,
    FOREIGN KEY (parent_id) REFERENCES m_office(id),
    INDEX idx_office_hierarchy (hierarchy),
    INDEX idx_office_parent (parent_id)
);

-- Staff management
CREATE TABLE m_staff (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    external_id VARCHAR(100) UNIQUE,
    firstname VARCHAR(100) NOT NULL,
    lastname VARCHAR(100) NOT NULL,
    display_name VARCHAR(200) GENERATED ALWAYS AS (CONCAT(firstname, ' ', lastname)) STORED,
    office_id BIGINT NOT NULL,
    isloan officer TINYINT(1) DEFAULT 0,
    isnacco TINYINT(1) DEFAULT 0,
    mobile_no VARCHAR(20),
    is_active TINYINT(1) DEFAULT 1,
    employment_date DATE,
    joining_date DATE,
    FOREIGN KEY (office_id) REFERENCES m_office(id),
    INDEX idx_staff_office (office_id),
    INDEX idx_staff_active (is_active)
);

-- Roles and permissions
CREATE TABLE m_role (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) UNIQUE NOT NULL,
    description TEXT,
    is_active TINYINT(1) DEFAULT 1,
    created_by BIGINT NOT NULL,
    created_date DATETIME NOT NULL,
    last_updated_by BIGINT,
    last_updated_date DATETIME,
    UNIQUE KEY unique_role_name (name)
);

CREATE TABLE m_permission (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    code VARCHAR(100) UNIQUE NOT NULL,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    is_active TINYINT(1) DEFAULT 1,
    created_by BIGINT NOT NULL,
    created_date DATETIME NOT NULL,
    last_updated_by BIGINT,
    last_updated_date DATETIME,
    UNIQUE KEY unique_permission_code (code)
);

CREATE TABLE m_role_permission (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    role_id BIGINT NOT NULL,
    permission_id BIGINT NOT NULL,
    is_active TINYINT(1) DEFAULT 1,
    created_by BIGINT NOT NULL,
    created_date DATETIME NOT NULL,
    FOREIGN KEY (role_id) REFERENCES m_role(id),
    FOREIGN KEY (permission_id) REFERENCES m_permission(id),
    UNIQUE KEY unique_role_permission (role_id, permission_id)
);

-- User management
CREATE TABLE m_appuser (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(255) UNIQUE NOT NULL,
    firstname VARCHAR(100) NOT NULL,
    lastname VARCHAR(100) NOT NULL,
    password VARCHAR(255) NOT NULL,
    is_self_service_user TINYINT(1) DEFAULT 0,
    is_non_expiry_locked TINYINT(1) DEFAULT 0,
    is_enabled TINYINT(1) DEFAULT 1,
    organisationwide_access TINYINT(1) DEFAULT 0,
    last_login TIMESTAMP NULL,
    previous_access TIMESTAMP NULL,
    created_date DATETIME NOT NULL,
    last_activated_date DATETIME,
    last_loan_transacted_on DATE,
    last_savings_transacted_on DATE,
    created_by BIGINT NOT NULL,
    account_locked_until_date TIMESTAMP NULL,
    attempts_remaining INT DEFAULT 0,
    nonexpired_password TINYINT(1) DEFAULT 1,
    nonexpired_credentials TINYINT(1) DEFAULT 1,
    nonexpired_account TINYINT(1) DEFAULT 1,
    unlocked_date TIMESTAMP NULL,
    account_creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    twofactor_enabled TINYINT(1) DEFAULT 0,
    twofactor_secret VARCHAR(500),
    FOREIGN KEY (created_by) REFERENCES m_appuser(id),
    INDEX idx_appuser_email (email),
    INDEX idx_appuser_enabled (is_enabled)
);

CREATE TABLE m_appuser_role (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    appuser_id BIGINT NOT NULL,
    role_id BIGINT NOT NULL,
    is_active TINYINT(1) DEFAULT 1,
    start_date DATE,
    end_date DATE,
    created_by BIGINT NOT NULL,
    created_date DATETIME NOT NULL,
    FOREIGN KEY (appuser_id) REFERENCES m_appuser(id),
    FOREIGN KEY (role_id) REFERENCES m_role(id),
    UNIQUE KEY unique_appuser_role (appuser_id, role_id)
);

2. Client Management Tables​

Individual Client Structure​

-- Client master data
CREATE TABLE m_client (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    external_id VARCHAR(100) UNIQUE,
    account_no VARCHAR(50) UNIQUE NOT NULL,
    firstname VARCHAR(100) NOT NULL,
    lastname VARCHAR(100) NOT NULL,
    middlename VARCHAR(100),
    mobile_no VARCHAR(20),
    email VARCHAR(100),
    office_id BIGINT NOT NULL,
    transfer_to_office_id BIGINT,
    client_type ENUM('INDIVIDUAL', 'JLG_GROUP') DEFAULT 'INDIVIDUAL',
    date_of_birth DATE,
    gender ENUM('MALE', 'FEMALE', 'OTHER', 'UNKNOWN'),
    client_provisioning_strategy ENUM('INVOLVEMENT', 'INVOLVEMENT_STRATEGY') DEFAULT 'INVOLVEMENT',
    is_staff TINYINT(1) DEFAULT 0,
    is_active TINYINT(1) DEFAULT 1,
    activation_date DATE,
    closure_date DATE,
    closure_reason_id BIGINT,
    image_key BIGINT,
    service_type ENUM('MICROFINANCE', 'RETAIL') DEFAULT 'MICROFINANCE',
    status ENUM('PENDING', 'SUBMITTED', 'REJECTED', 'APPROVED', 'DELETED', 'WITHDRAWN', 'DECLINED') DEFAULT 'PENDING',
    submitted_on_date DATE,
    submitted_till_date DATE,
    rejected_on_date DATE,
    rejected_reason_id BIGINT,
    withdrawal_on_date DATE,
    withdrawal_reason_id BIGINT,
    deleted_on_date DATE,
    deleted_reason_id BIGINT,
    closure_reason TEXT,
    rejection_reason_id BIGINT,
    created_by BIGINT NOT NULL,
    created_date DATETIME NOT NULL,
    last_updated_by BIGINT,
    last_updated_date DATETIME,
    hwa_name VARCHAR(100),
    status_enum BIGINT NOT NULL,
    family_members INT DEFAULT 0,
    is_center_lead TINYINT(1) DEFAULT 0,
    sol_id VARCHAR(100),
    is_flat_tfx TINYINT(1) DEFAULT 0,
    FOREIGN KEY (office_id) REFERENCES m_office(id),
    FOREIGN KEY (transfer_to_office_id) REFERENCES m_office(id),
    INDEX idx_client_office (office_id),
    INDEX idx_client_account_no (account_no),
    INDEX idx_client_status (status),
    INDEX idx_client_active (is_active)
);

-- Client address information
CREATE TABLE m_client_address (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    client_id BIGINT NOT NULL,
    address_type ENUM('HOME', 'BUSINESS', 'CORRESPONDENCE') DEFAULT 'HOME',
    street VARCHAR(200),
    address_line_1 VARCHAR(200),
    address_line_2 VARCHAR(200),
    city VARCHAR(100),
    state_province VARCHAR(100),
    postal_code VARCHAR(20),
    country_id BIGINT,
    latitude DECIMAL(10, 8),
    longitude DECIMAL(11, 8),
    is_active TINYINT(1) DEFAULT 1,
    created_by BIGINT NOT NULL,
    created_date DATETIME NOT NULL,
    last_updated_by BIGINT,
    last_updated_date DATETIME,
    FOREIGN KEY (client_id) REFERENCES m_client(id),
    FOREIGN KEY (country_id) REFERENCES m_country(id),
    INDEX idx_client_address_client (client_id)
);

-- Client identifier documents
CREATE TABLE m_client_identifier (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    client_id BIGINT NOT NULL,
    document_type_id BIGINT NOT NULL,
    document_key VARCHAR(100) NOT NULL,
    status ENUM('ACTIVE', 'INACTIVE', 'INVALID') DEFAULT 'ACTIVE',
    location VARCHAR(100),
    description TEXT,
    created_by BIGINT NOT NULL,
    created_date DATETIME NOT NULL,
    last_updated_by BIGINT,
    last_updated_date DATETIME,
    FOREIGN KEY (client_id) REFERENCES m_client(id),
    FOREIGN KEY (document_type_id) REFERENCES m_document_type(id),
    INDEX idx_client_identifier_client (client_id),
    INDEX idx_client_identifier_type (document_type_id),
    UNIQUE KEY unique_client_document (client_id, document_type_id, document_key)
);

-- Client business activities
CREATE TABLE m_client_business_activity (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    client_id BIGINT NOT NULL,
    business_activity_id BIGINT NOT NULL,
    foreign_currency DECIMAL(19, 6),
    local_currency DECIMAL(19, 6),
    remarks VARCHAR(500),
    created_by BIGINT NOT NULL,
    created_date DATETIME NOT NULL,
    last_updated_by BIGINT,
    last_updated_date DATETIME,
    FOREIGN KEY (client_id) REFERENCES m_client(id),
    FOREIGN KEY (business_activity_id) REFERENCES m_business_activity(id),
    INDEX idx_client_activity_client (client_id),
    INDEX idx_client_activity_type (business_activity_id),
    UNIQUE KEY unique_client_activity (client_id, business_activity_id)
);

Group Management Structure​

-- Group structure
CREATE TABLE m_group (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    external_id VARCHAR(100) UNIQUE,
    account_no VARCHAR(50) UNIQUE NOT NULL,
    name VARCHAR(100) NOT NULL,
    status ENUM('PENDING', 'SUBMITTED', 'REJECTED', 'APPROVED', 'DELETED', 'WITHDRAWN', 'DECLINED') DEFAULT 'PENDING',
    activation_date DATE,
    closed_on_date DATE,
    closure_reason_id BIGINT,
    office_id BIGINT NOT NULL,
    submittedon_date DATE,
    submitted_till_date DATE,
    rejected_on_date DATE,
    rejected_reason_id BIGINT,
    withdrawal_on_date DATE,
    withdrawal_reason_id BIGINT,
    deleted_on_date DATE,
    deleted_reason_id BIGINT,
    activation_date_derived DATE,
    center_id BIGINT,
    level_id BIGINT,
    group_type ENUM('GROUP', 'CENTER') DEFAULT 'GROUP',
    is_center TINYINT(1) DEFAULT 0,
    staff_id BIGINT,
    status_enum BIGINT NOT NULL,
    created_by BIGINT NOT NULL,
    created_date DATETIME NOT NULL,
    last_updated_by BIGINT,
    last_updated_date DATETIME,
    FOREIGN KEY (office_id) REFERENCES m_office(id),
    FOREIGN KEY (center_id) REFERENCES m_group(id),
    FOREIGN KEY (staff_id) REFERENCES m_staff(id),
    INDEX idx_group_office (office_id),
    INDEX idx_group_center (center_id),
    INDEX idx_group_staff (staff_id)
);

-- Group-client relationships
CREATE TABLE m_group_client (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    group_id BIGINT NOT NULL,
    client_id BIGINT NOT NULL,
    role ENUM('CLIENT', 'LEAD_CLIENT') DEFAULT 'CLIENT',
    status ENUM('ACTIVE', 'INACTIVE') DEFAULT 'ACTIVE',
    submitted_date DATE,
    activation_date DATE,
    is_loan_office TINYINT(1) DEFAULT 0,
    last_loan_application_date DATE,
    created_by BIGINT NOT NULL,
    created_date DATETIME NOT NULL,
    FOREIGN KEY (group_id) REFERENCES m_group(id),
    FOREIGN KEY (client_id) REFERENCES m_client(id),
    INDEX idx_group_client_group (group_id),
    INDEX idx_group_client_client (client_id),
    UNIQUE KEY unique_group_client (group_id, client_id)
);

3. Product Management Tables​

Loan Products​

-- Loan product definitions
CREATE TABLE m_product_loan (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    external_id VARCHAR(100) UNIQUE,
    name VARCHAR(100) NOT NULL,
    short_name VARCHAR(4) NOT NULL,
    description TEXT,
    fund_id BIGINT,
    is_linked_to_floating_interest_rates TINYINT(1) DEFAULT 0,
    allow_variabe_installments TINYINT(1) DEFAULT 0,
    can_use_for_topup TINYINT(1) DEFAULT 0,
    is_interest_deduction_at_disbursement TINYINT(1) DEFAULT 0,
    is_paid_together TINYINT(1) DEFAULT 0,
    is_fund_source_required TINYINT(1) DEFAULT 0,
    is_hold_reference_data_on_trigger TINYINT(1) DEFAULT 0,
    hold_guarantee_funds TINYINT(1) DEFAULT 0,
    include_in_borrow_cycle TINYINT(1) DEFAULT 1,
    due_date_color_type_id BIGINT,
    overdue_days_for_npa INT DEFAULT 0,
    multi_disburse_loan TINYINT(1) DEFAULT 0,
    max_number_of_dislosals INT,
    overdue_on_day_action_type_id BIGINT,
    accounting_rule ENUM('NONE', 'CASH_BASED', 'ACCURAL_BASED_PERIODIC', 'ACCURAL_BASED_TRANSACTION') DEFAULT 'NONE',
    is_interface_provided TINYINT(1) DEFAULT 0,
    is_waiting_period_applicable TINYINT(1) DEFAULT 0,
    waiting_period_value_type_id BIGINT,
    waiting_period_value INT,
    is_fixed_emaily_fax TINYINT(1) DEFAULT 0,
   loan_transaction_strategy_id BIGINT,
    withdrawal_fee_transaction_strategy_id BIGINT,
    annual_fee_transaction_strategy_id BIGINT,
    waiver_transaction_strategy_id BIGINT,
    charge_off_transaction_strategy_id BIGINT,
    transfer_in_interest_fee_transaction_strategy_id BIGINT,
    created_by BIGINT NOT NULL,
    created_date DATETIME NOT NULL,
    last_updated_by BIGINT,
    last_updated_date DATETIME,
    is_active TINYINT(1) DEFAULT 1,
    require_gl_evidence TINYINT(1) DEFAULT 0,
    allow_approve_for_dropp_down TINYINT(1) DEFAULT 0,
    allow_gla_accnt_trnfr_for_bulk_entry TINYINT(1) DEFAULT 0,
    is_floating_rate TINYINT(1) DEFAULT 0,
    floating_rates_id BIGINT,
    interest_rate_charge_type_for_odt BIGINT,
    FOREIGN KEY (fund_id) REFERENCES m_fund.id,
    INDEX idx_loan_product_name (name),
    INDEX idx_loan_product_active (is_active)
);

-- Loan product configuration
CREATE TABLE m_product_loan_configurable_attributes (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    product_id BIGINT NOT NULL,
    is_annual_fee_enabled TINYINT(1) DEFAULT 0,
    annual_fee_at_alert_frequency_types_id BIGINT,
    is_link_overdue_to_installments TINYINT(1) DEFAULT 0,
    overdue_frequency_number INT,
    overdue_frequency_type_id BIGINT,
    overdue_frequency_basis_type_id BIGINT,
    is_npa_processing_enabled TINYINT(1) DEFAULT 0,
    npa_delay_action_type_id BIGINT,
    npa_delay frequency_value INT,
    npa_delay frequency_type_id BIGINT,
    installment_grace_period INT DEFAULT 0,
    is_reschedule_future_installments_enabled TINYINT(1) DEFAULT 0,
    is_reschedule_independent_of_installment_due_date TINYINT(1) DEFAULT 0,
    is_pay_third_party_enabled TINYINT(1) DEFAULT 0,
    is_recalculate_interest_for_due_amounts TINYINT(1) DEFAULT 0,
    FOREIGN KEY (product_id) REFERENCES m_product_loan(id),
    UNIQUE KEY unique_product_config (product_id)
);

-- Loan product interest rate configuration
CREATE TABLE m_product_loan_interest_rate_charge_attributes (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    product_id BIGINT NOT NULL,
    amount DECIMAL(19, 6),
    percentage DECIMAL(19, 6),
    charge_calculation_type ENUM('FLAT', 'PERCENT_OF_AMOUNT', 'PERCENT_OF_INTEREST') DEFAULT 'FLAT',
    charge_application_type ENUM('ON_ADVANCE_DISBURSEMENT', 'ON_DISBURSEMENT', 'ON_MATURITY') DEFAULT 'ON_DISBURSEMENT',
    charge_time_type ENUM('DISBURSEMENT', 'SPECIFIC_DUE_DATE', 'OVERDUE_INSTALLMENT', 'OVERDUE_TILL_PAYMENT', 'INTEREST') DEFAULT 'DISBURSEMENT',
    FOREIGN KEY (product_id) REFERENCES m_product_loan(id)
);

-- Savings products
CREATE TABLE m_product_savings (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    external_id VARCHAR(100) UNIQUE,
    name VARCHAR(100) NOT NULL,
    short_name VARCHAR(4) NOT NULL,
    description TEXT,
    fund_id BIGINT,
    nace_code VARCHAR(10),
    accounting_rule ENUM('NONE', 'CASH_BASED', 'ACCRUAL_BASED') DEFAULT 'CASH_BASED',
    is_dormancy_tracking_active TINYINT(1) DEFAULT 0,
    days_to_inactive INT DEFAULT 0,
    days_to_dormancy INT DEFAULT 0,
    days_to_escheat INT DEFAULT 0,
    is_hold_amount_supported TINYINT(1) DEFAULT 0,
    min_balance_for_interest_calculation DECIMAL(19, 6) DEFAULT 0,
    min_overdraft_for_interest_calculation DECIMAL(19, 6) DEFAULT 0,
    gauranteed_interest_calculated_on_period ENUM('INVALID', 'INVALID_DATE') DEFAULT 'INVALID_DATE',
    interest_on_cheques TINYINT(1) DEFAULT 0,
    selected_currency BOOLEAN DEFAULT FALSE,
    currency_digits INT,
    in_multiples_of DECIMAL(19, 6),
    is_penetration_rate_enabled TINYINT(1) DEFAULT 0,
    penetration_rate_differential DECIMAL(19, 6),
    penetration_rate_start_amount DECIMAL(19, 6),
    penetration_rate_end_amount DECIMAL(19, 6),
    created_by BIGINT NOT NULL,
    created_date DATETIME NOT NULL,
    last_updated_by BIGINT,
    last_updated_date DATETIME,
    is_active TINYINT(1) DEFAULT 1,
    is_daily_interest_posting_required TINYINT(1) DEFAULT 0,
    s_nbc_tiers_selected BOOLEAN DEFAULT FALSE,
    s_nbc_tiers [JSON],
    FOREIGN KEY (fund_id) REFERENCES m_fund.id,
    INDEX idx_savings_product_name (name),
    INDEX idx_savings_product_active (is_active)
);

-- Charge definitions
CREATE TABLE m_charge (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    external_id VARCHAR(100) UNIQUE,
    name VARCHAR(100) NOT NULL,
    international_name VARCHAR(100),
    type ENUM('SHA_DISBURSEMENT', 'SHA_APPROVAL', 'SHA_SPECIFIC_TIME', 'SHA_INSTALLMENT', 'SHA_OVERDUE', 'SHA_TRANCH_DISBURSEMENT') DEFAULT 'SHA_DISBURSEMENT',
    charge_time_type ENUM('DISBURSEMENT', 'SPECIFIC_DUE_DATE', 'OVERDUE_INSTALLMENT', 'OVERDUE_TILL_PAYMENT', 'TRANCH_DISBURSEMENT') DEFAULT 'DISBURSEMENT',
    charge_applies_to ENUM('LOAN', 'SAVINGS', 'CLIENT') DEFAULT 'LOAN',
    charge_calculation_type ENUM('FLAT', 'PERCENT_OF_AMOUNT', 'PERCENT_OF_AMOUNT_AND_INTEREST', 'PERCENT_OF_INTEREST', 'PERCENT_OF_PRINCIPAL_AND_INTEREST', 'PERCENT_OF_DISBURSEMENT') DEFAULT 'FLAT',
    charge_payment_type ENUM('REGULAR', 'TENOR_BASED') DEFAULT 'REGULAR',
    amount DECIMAL(19, 6),
    percentage DECIMAL(19, 6),
    charge_waiver_enum INT DEFAULT 0,
    is_active TINYINT(1) DEFAULT 1,
    is_penalty TINYINT(1) DEFAULT 0,
    is_business_rule_enabled TINYINT(1) DEFAULT 0,
    income_or_liability_account_id BIGINT,
    created_by BIGINT NOT NULL,
    created_date DATETIME NOT NULL,
    last_updated_by BIGINT,
    last_updated_date DATETIME,
    is_free_withdrawal BOOLEAN DEFAULT FALSE,
    free_withdrawal_count INT DEFAULT 0,
    is_business_loan_specific BOOLEAN DEFAULT FALSE,
    created_date DATETIME,
    FOREIGN KEY (income_or_liability_account_id) REFERENCES m_gl_account.id,
    INDEX idx_charge_name (name),
    INDEX idx_charge_type (type),
    INDEX idx_charge_applies_to (charge_applies_to),
    INDEX idx_charge_active (is_active)
);

4. Loan Management Tables​

Loan Accounts Structure​

-- Main loan table
CREATE TABLE m_loan (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    account_no VARCHAR(50) UNIQUE NOT NULL,
    external_id VARCHAR(100) UNIQUE,
    client_id BIGINT NOT NULL,
    group_id BIGINT,
    loan_product_id BIGINT NOT NULL,
    is_grouploan_product TINYINT(1) DEFAULT 0,
    is_floating_rate TINYINT(1) DEFAULT 0,
    floating_rates_id BIGINT,
    interest_rate_charge_type_for_odt BIGINT,
    loan_type ENUM('INDIVIDUAL', 'GROUP') DEFAULT 'INDIVIDUAL',
    currency_code VARCHAR(3) NOT NULL,
    principal_amount DECIMAL(19, 6) NOT NULL,
    proposed_amount DECIMAL(19, 6),
    term_frequency INT,
    term_period_frequency_type ENUM('DAYS', 'WEEKS', 'MONTHS', 'YEARS') DEFAULT 'MONTHS',
    number_of_repayments INT,
    repayment_frequency_type ENUM('DAYS', 'WEEKS', 'MONTHS', 'YEARS') DEFAULT 'MONTHS',
    repayment_frequency_nth_day_type ENUM('FIRST', 'SECOND', 'THIRD', 'FOURTH', 'LAST') DEFAULT 'LAST',
    repayment_frequency_day_of_week_type ENUM('MONDAY', 'TUESDAY', 'WEDNESDAY', 'THURSDAY', 'FRIDAY', 'SATURDAY', 'SUNDAY') DEFAULT 'FRIDAY',
    interest_rate_per_period DECIMAL(19, 6),
    interest_rate_per_period_type ENUM('DAY', 'WEEK', 'MONTH', 'YEAR') DEFAULT 'MONTH',
    interest_charge_from_partial_disbursement TINYINT(1) DEFAULT 0,
    expected_disbursement_date DATE,
    actual_disbursement_date DATE,
    interest_calculated_from DATE,
    first_repayment_date DATE,
    submitted_on_date DATE,
    rejected_on_date DATE,
    withdrawn_on_date DATE,
    approved_on_date DATE,
    expected_first_repayment_on_date DATE,
    expected_maturity_date DATE,
    interest_recalculation_enabled TINYINT(1) DEFAULT 0,
    interest_recalculation_compounding_method ENUM('NONE', 'FEE', 'INTEREST') DEFAULT 'NONE',
    interest_recalculation_frequency_type ENUM('NONE', 'DAILY', 'WEEKLY', 'MONTHLY', 'QUARTERLY', 'ANNUAL') DEFAULT 'NONE',
    interest_recalculation_frequency_number INT,
    interest_recalculation_frequency_on_day INT,
    interest_recalculation_frequency_on_day_of_week_type ENUM('MONDAY', 'TUESDAY', 'WEDNESDAY', 'THURSDAY', 'FRIDAY', 'SATURDAY', 'SUNDAY') DEFAULT 'MONDAY',
    interest_recalculation_compounding_frequency_type ENUM('NONE', 'DAILY', 'WEEKLY', 'MONTHLY', 'QUARTERLY', 'ANNUAL') DEFAULT 'NONE',
    interest_recalculation_compounding_frequency_number INT,
    interest_recalculation_compounding_frequency_on_day INT,
    interest_recalculation_compounding_frequency_on_day_of_week_type ENUM('MONDAY', 'TUESDAY', 'WEDNESDAY', 'THURSDAY', 'FRIDAY', 'SATURDAY', 'SUNDAY') DEFAULT 'MONDAY',
    interest_recalculation_on_closing_date TINYINT(1) DEFAULT 0,
    allow_interest_recalculation_when_reversing TINYINT(1) DEFAULT 0,
    pre_closure_interest_calculation_strategy ENUM('TILL_PRECLOSURE_DATE', 'TILL_REST_DATE', 'TILL_INSTALLMENT_DATE') DEFAULT 'TILL_PRECLOSURE_DATE',
    reschedule_strategy_method ENUM('REDUCE_EMI', 'REDUCE_NUMBER_OF_INSTALLMENTS', 'RESCHEDULE_TO_FLEXIBLE', 'RESCHEDULE_TO_TENOR') DEFAULT 'REDUCE_NUMBER_OF_INSTALLMENTS',
    reschedule_frequency_type ENUM('DAYS', 'WEEKS', 'MONTHS', 'QUARTERLY', 'ANNUAL') DEFAULT 'MONTHS',
    reschedule_frequency_number INT,
    reschedule_frequency_on_day INT,
    reschedule_frequency_on_day_of_week_type ENUM('MONDAY', 'TUESDAY', 'WEDNESDAY', 'THURSDAY', 'FRIDAY', 'SATURDAY', 'SUNDAY') DEFAULT 'FRIDAY',
    repayable_enums ENUM('REPAY_TO_TENOR', 'REPAY_TO_INSTALLMENTS', 'REPAY_TILL_FLEXIBLE') DEFAULT 'REPAY_TILL_FLEXIBLE',
    repayable_variable_installments TINYINT(1) DEFAULT 0,
    repayable_custom_installments TINYINT(1) DEFAULT 0,
    repay_per_cycle ENUM('EMI', 'EPI') DEFAULT 'EMI',
    principal_amount_change_on_recalculation TINYINT(1) DEFAULT 0,
    grand_total_allow_multi_disburse TINYINT(1) DEFAULT 0,
    multi_disburse_emix_amount_advanced DECIMAL(19, 6) DEFAULT 0,
    principal_amount_percentage_threshold DECIMAL(19, 6) DEFAULT 100,
    outstanding_loan_balance_derived DECIMAL(19, 6),
    total_overpaid_derived DECIMAL(19, 6) DEFAULT 0,
    overpaid_amount_derived DECIMAL(19, 6) DEFAULT 0,
    is_paid_ahead TINYINT(1) DEFAULT 0,
    complete_loan_cycle ENUM('COMPLETED_ONE_CYCLE', 'NOT_COMPLETE_ONE_CYCLE') DEFAULT 'NOT_COMPLETE_ONE_CYCLE',
    loan_transaction_strategy_id BIGINT,
    withdrawal_fee_transaction_strategy_id BIGINT,
    annual_fee_transaction_strategy_id BIGINT,
    waiver_transaction_strategy_id BIGINT,
    charge_off_transaction_strategy_id BIGINT,
    transfer_in_interest_fee_transaction_strategy_id BIGINT,
    is_buy_down_enabled TINYINT(1) DEFAULT 0,
    buy_down_interest_chart_data_id BIGINT,
    max_buy_down_amount DECIMAL(19, 6),
    is_record_id_required TINYINT(1) DEFAULT 0,
    is_loan_tranche_creation_required TINYINT(1) DEFAULT 0,
    topup_amount DECIMAL(19, 6),
    is_topup TINYINT(1) DEFAULT 0,
    topup_on_loan_id BIGINT,
    closure_loan_id BIGINT,
    status ENUM('PENDING', 'SUBMITTED', 'REJECTED', 'WITHDRAWN', 'APPROVED', 'DISBURSED', 'CLOSED_WRITTENOFF', 'CLOSED_OBLIGATIONS_MET', 'CLOSED_RESCHEDULE', 'OVERPAID', 'CANCELLED') DEFAULT 'PENDING',
    status_enum BIGINT NOT NULL,
    account_close_date DATE,
    closed_on_date DATE,
    reopen_date DATE,
    reopening_transaction_id BIGINT,
    is_npa TINYINT(1) DEFAULT 0,
    accrued_till DECIMAL(19, 6) DEFAULT 0,
    office_id BIGINT NOT NULL,
    is_splitloantransaction ENUM('SPLITLOAN', 'NO_SPLITLOAN') DEFAULT 'NO_SPLITLOAN',
    created_by BIGINT NOT NULL,
    created_date DATETIME NOT NULL,
    last_updated_by BIGINT,
    last_updated_date DATETIME,
    interest_calculated_from_is_future TINYINT(1) DEFAULT 0,
    FOREIGN KEY (client_id) REFERENCES m_client(id),
    FOREIGN KEY (group_id) REFERENCES m_group(id),
    FOREIGN KEY (loan_product_id) REFERENCES m_product_loan(id),
    FOREIGN KEY (office_id) REFERENCES m_office(id),
    FOREIGN KEY (topup_on_loan_id) REFERENCES m_loan.id),
    FOREIGN KEY (closure_loan_id) REFERENCES m_loan.id),
    INDEX idx_loan_client (client_id),
    INDEX idx_loan_product (loan_product_id),
    INDEX idx_loan_office (office_id),
    INDEX idx_loan_status (status),
    INDEX idx_loan_expected_disbursement (expected_disbursement_date)
);

-- Loan repayment schedule
CREATE TABLE m_loan_repayment_schedule (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    loan_id BIGINT NOT NULL,
    installment DECIMAL(10, 0) NOT NULL,
    from_date DATE NOT NULL,
    due_date DATE NOT NULL,
    complete_date DATE,
    principal_amount DECIMAL(19, 6) DEFAULT 0,
    principal_writtenoff DECIMAL(19, 6) DEFAULT 0,
    principal_outstanding_derived DECIMAL(19, 6) DEFAULT 0,
    interest_amount DECIMAL(19, 6) DEFAULT 0,
    interest_writtenoff DECIMAL(19, 6) DEFAULT 0,
    interest_waived DECIMAL(19, 6) DEFAULT 0,
    interest_outstanding_derived DECIMAL(19, 6) DEFAULT 0,
    fee_charges_amount DECIMAL(19, 6) DEFAULT 0,
    fee_charges_writtenoff DECIMAL(19, 6) DEFAULT 0,
    fee_charges_waived DECIMAL(19, 6) DEFAULT 0,
    fee_charges_outstanding_derived DECIMAL(19, 6) DEFAULT 0,
    penalty_charges_amount DECIMAL(19, 6) DEFAULT 0,
    penalty_charges_writtenoff DECIMAL(19, 6) DEFAULT 0,
    penalty_charges_waived DECIMAL(19, 6) DEFAULT 0,
    penalty_charges_outstanding_derived DECIMAL(19, 6) DEFAULT 0,
    total_due DECIMAL(19, 6) DEFAULT 0,
    total_paid DECIMAL(19, 6) DEFAULT 0,
    total_paid_in_advance DECIMAL(19, 6) DEFAULT 0,
    total_paid_late DECIMAL(19, 6) DEFAULT 0,
    total_waived DECIMAL(19, 6) DEFAULT 0,
    total_writtenoff DECIMAL(19, 6) DEFAULT 0,
    total_outstanding DECIMAL(19, 6) DEFAULT 0,
    total_overdue DECIMAL(19, 6) DEFAULT 0,
    is_overdue_derived TINYINT(1) DEFAULT 0,
    completed_derived TINYINT(1) DEFAULT 0,
    due_date_attempts BIGINT DEFAULT 0,
    last_due_date_attempt_date DATE,
    accrual_transaction_details_calculated TINYINT(1) DEFAULT 0,
    notional_interest_amount DECIMAL(19, 6) DEFAULT 0,
    loan_interest_repayment_rules BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (loan_id) REFERENCES m_loan.id),
    INDEX idx_repayment_schedule_loan (loan_id),
    INDEX idx_repayment_schedule_due_date (due_date),
    INDEX idx_repayment_schedule_installment (installment)
);

-- Loan transactions
CREATE TABLE m_loan_transaction (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    external_id VARCHAR(100) UNIQUE,
    loan_id BIGINT NOT NULL,
    office_id BIGINT NOT NULL,
    is_reversed TINYINT(1) DEFAULT 0,
    transaction_date DATE NOT NULL,
    created_date DATETIME NOT NULL,
    amount DECIMAL(19, 6) NOT NULL,
    principal_portion DECIMAL(19, 6) DEFAULT 0,
    interest_portion DECIMAL(19, 6) DEFAULT 0,
    fee_charges_portion DECIMAL(19, 6) DEFAULT 0,
    penalty_charges_portion DECIMAL(19, 6) DEFAULT 0,
    overpayment_portion DECIMAL(19, 6) DEFAULT 0,
    action ENUM('DISBURSEMENT', 'REPAYMENT', 'REVERSAL', 'WAIVER', 'WAIVER_INTEREST', 'WAIVER_PRINCIPAL', 'WAIVER_FEES', 'WAIVER_PENALTY', 'INTEREST_POSTING', 'WRITTEN_OFF', 'CONVERT_TO_STALE_DATED_CHECK', 'PAY_OFF', 'CREDIT_BALANCE_REFUND', 'GUARANTEE_ADJUSTMENT', 'CHARGEBACK', 'CHARGEBACK_REVERSAL', 'EMERGENCY_STOP', 'REFUND', 'REFUND_OF_APPROVED_AMOUNT', 'CHARGE_ADJUSTMENT', 'CHARGE_REFUND', 'APPROVE_AMOUNT', 'APPROVE_ACCOUNT_LOCK', 'DISAPPROVE_ACCOUNT_LOCK', 'UNLOCK', 'PAYAHEAD', 'SHORTFALL') DEFAULT 'REPAYMENT',
    transaction_type ENUM('DISBURSEMENT', 'REPAYMENT', 'REVERSAL', 'WAIVER', 'WRITTEN_OFF', 'INTEREST_POSTING', 'CONVERT_TO_STALE_DATED_CHECK', 'PAY_OFF', 'CREDIT_BALANCE_REFUND', 'GUARANTEE_ADJUSTMENT', 'CHARGEBACK', 'CHARGEBACK_REVERSAL', 'EMERGENCY_STOP', 'REFUND', 'REFUND_OF_APPROVED_AMOUNT', 'CHARGE_ADJUSTMENT', 'CHARGE_REFUND', 'APPROVE_AMOUNT', 'APPROVE_ACCOUNT_LOCK', 'DISAPPROVE_ACCOUNT_LOCK', 'UNLOCK', 'PAYAHEAD', 'SHORTFALL') DEFAULT 'REPAYMENT',
    amount_to_loan DECIMAL(19, 6) DEFAULT 0,
    processed TINYINT(1) DEFAULT 0,
    user_id BIGINT,
    is_future_prediction TINYINT(1) DEFAULT 0,
    interested_posting_date DATE,
    transaction_sequence_id BIGINT,
    constraint_03_check CHECK ((fee_charges_portion + penalty_charges_portion) = 0),
    constraints_02_check CHECK ((amount - (principal_portion + interest_portion + fee_charges_portion + penalty_charges_portion + overpayment_portion)) = 0),
    constraints_01_check CHECK ((amount - (principal_portion + interest_portion + fee_charges_portion + penalty_charges_portion + overpayment_portion)) = 0),
    FOREIGN KEY (loan_id) REFERENCES m_loan.id),
    FOREIGN KEY (office_id) REFERENCES m_office.id),
    FOREIGN KEY (user_id) REFERENCES m_appuser(id),
    INDEX idx_loan_transaction_loan (loan_id),
    INDEX idx_loan_transaction_date (transaction_date),
    INDEX idx_loan_transaction_type (transaction_type)
);

5. Savings Management Tables​

Savings Accounts Structure​

-- Savings accounts
CREATE TABLE m_savings_account (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    external_id VARCHAR(100) UNIQUE,
    account_no VARCHAR(50) UNIQUE NOT NULL,
    savings_product_id BIGINT NOT NULL,
    client_id BIGINT,
    group_id BIGINT,
    field_officer_id BIGINT,
    account_type ENUM('INDIVIDUAL_ACCOUNT', 'JLG_LOAN_ACCOUNT', 'GROUP_SAVINGS_ACCOUNT') DEFAULT 'INDIVIDUAL_ACCOUNT',
    savings_account_type ENUM('FROM_SAVINGS_DEPOSIT', 'RECURRING_DEPOSIT', 'FIXED_DEPOSIT') DEFAULT 'FROM_SAVINGS_DEPOSIT',
    currency_code VARCHAR(3) NOT NULL,
    nominal_annual_interest_rate DECIMAL(19, 6) DEFAULT 0,
    interest_compounding_period_type ENUM('DAILY', 'WEEKLY', 'BIWEEKLY', 'MONTHLY', 'BIMONTHLY', 'QUARTERLY', 'SEMIANNUAL', 'ANNUAL') DEFAULT 'MONTHLY',
    interest_posting_period_type ENUM('ANNUAL', 'SEMIANNUAL', 'QUARTERLY', 'MONTHLY', 'WEEKLY') DEFAULT 'MONTHLY',
    interest_calculation_type ENUM('DAILY_BALANCE', 'AVERAGE_BALANCE') DEFAULT 'DAILY_BALANCE',
    interest_calculation_days_in_year_type ENUM('360', '365', '366') DEFAULT '365',
    min_required_balance DECIMAL(19, 6) DEFAULT 0,
    min_required_opening_balance DECIMAL(19, 6) DEFAULT 0,
    account_chart_id BIGINT,
    dormancy_tracking_custom_field_id BIGINT,
    enforce_minimum_balance TINYINT(1) DEFAULT 0,
    is_tiered_for_business_rule TINYINT(1) DEFAULT 0,
    withdrawal_fee_for_transfer TINYINT(1) DEFAULT 0,
    activate_standalone_account TINYINT(1) DEFAULT 0,
    allow_additional_deposits TINYINT(1) DEFAULT 1,
    allow_withdrawals TINYINT(1) DEFAULT 1,
    allow_overdraft TINYINT(1) DEFAULT 0,
    min_overdraft_amount DECIMAL(19, 6) DEFAULT 0,
    max_overdraft_amount DECIMAL(19, 6),
    overdraft_interest_rate DECIMAL(19, 6) DEFAULT 0,
    overdraft_control_account_id BIGINT,
    savings_control_account_id BIGINT,
    transfer_in_suspense_id BIGINT,
    payoff_date DATE,
    on_hold_amount DECIMAL(19, 6) DEFAULT 0,
    paid_ahead ENUM('YES', 'NO') DEFAULT 'NO',
    total_deposits DECIMAL(19, 6) DEFAULT 0,
    total_withdrawals DECIMAL(19, 6) DEFAULT 0,
    total_interest_posted DECIMAL(19, 6) DEFAULT 0,
    account_balance_derived DECIMAL(19, 6) DEFAULT 0,
    available_balance_derived DECIMAL(19, 6) DEFAULT 0,
    written_off_interest_derived DECIMAL(19, 6) DEFAULT 0,
    interest_on_cheques DECIMAL(19, 6) DEFAULT 0,
    pre_closure_applicable TINYINT(1) DEFAULT 0,
    pre_closure_penal DECIMAL(19, 6) DEFAULT 0,
    summary_id BIGINT,
    status ENUM('PENDING', 'SUBMITTED', 'REJECTED', 'WITHDRAWN', 'APPROVED', 'ACTIVE', 'DORMANT', 'CLOSED', 'TRANSFERRED_TO_DORMANT', 'REJECTED', 'REOPEN') DEFAULT 'PENDING',
    status_enum BIGINT NOT NULL,
    account_sub_status ENUM('NONE', 'INACTIVE', 'DORMANT', 'BLACKLISTED', 'ESCHEAT', 'FREEZED', 'BLOCK') DEFAULT 'NONE',
    account_sub_status_enum BIGINT DEFAULT 0,
    on_hold_funds_derived DECIMAL(19, 6) DEFAULT 0,
    locked_in_avings_amount_derived DECIMAL(19, 6) DEFAULT 0,
    account_closure_date DATE,
    reopen_date DATE,
    reopened_on_date DATE,
    reopen_reason_id BIGINT,
    closed_on_date DATE,
    closed_freed INTEGER DEFAULT 0,
    can_manually_close TINYINT(1) DEFAULT 1,
    total_fees_charge DECIMAL(19, 6) DEFAULT 0,
    total_penalty_charge DECIMAL(19, 6) DEFAULT 0,
    total_withhold_tax DECIMAL(19, 6) DEFAULT 0,
    is_dormancy_tracking_required TINYINT(1) DEFAULT 0,
    dormancy_last_trade_date DATE,
    max_allowed_overdraft_amount DECIMAL(19, 6) DEFAULT 0,
    allowed_overdraft_limit DECIMAL(19, 6) DEFAULT 0,
    interest_posted_in_advance DECIMAL(19, 6) DEFAULT 0,
    interest_posted_derive DECIMAL(19, 6) DEFAULT 0,
    interest_available_for_withdrawal_derived DECIMAL(19, 6) DEFAULT 0,
    advance_interest_forfeited DECIMAL(19, 6) DEFAULT 0,
    hold_amount_derived DECIMAL(19, 6) DEFAULT 0,
    on_hold_amount_reversed_derived DECIMAL(19, 6) DEFAULT 0,
    rejected_on_date DATE,
    rejected_reason_id BIGINT,
    rejected_till_date DATE,
    withdrawal_frequency INT DEFAULT 0,
    withdrawal_frequency_enum INT,
    withdrawal_reminder_day_1 INT DEFAULT 0,
    withdrawal_reminder_day_2 INT DEFAULT 0,
    withdrawal_reminder_day_3 INT DEFAULT 0,
    auto_withdrawal_interest TINYINT(1) DEFAULT 0,
    balance_before_last_staff_action_on_derived DECIMAL(19, 6) DEFAULT 0,
    last_staff_action_approved_by BIGINT,
    created_by BIGINT NOT NULL,
    created_date DATETIME NOT NULL,
    last_updated_by BIGINT,
    last_updated_date DATETIME,
    office_id BIGINT NOT NULL,
    FOREIGN KEY (savings_product_id) REFERENCES m_product_savings.id),
    FOREIGN KEY (client_id) REFERENCES m_client(id),
    FOREIGN KEY (group_id) REFERENCES m_group(id),
    FOREIGN KEY (field_officer_id) REFERENCES m_staff.id),
    FOREIGN KEY (office_id) REFERENCES m_office.id),
    INDEX idx_savings_client (client_id),
    INDEX idx_savings_product (savings_product_id),
    INDEX idx_savings_office (office_id),
    INDEX idx_savings_status (status)
);

-- Savings transactions
CREATE TABLE m_savings_account_transaction (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    external_id VARCHAR(100) UNIQUE,
    account_id BIGINT NOT NULL,
    office_id BIGINT NOT NULL,
    is_reversed TINYINT(1) DEFAULT 0,
    transaction_date DATE NOT NULL,
    created_date DATETIME NOT NULL,
    amount DECIMAL(19, 6) NOT NULL,
    running_balance DECIMAL(19, 6) NOT NULL,
    cumulative_transaction_amount DECIMAL(19, 6) DEFAULT 0,
    balance_end_date_derived DATE,
    is_accrual TINYINT(1) DEFAULT 0,
    is_manual_balance TINYINT(1) DEFAULT 0,
    release_poa_date DATE,
    transaction_type ENUM('DEPOSIT', 'WITHDRAWAL', 'INTEREST_POSTING', 'ANNUAL_FEE', 'ACCOUNT_TRANSFER', 'WRITE_OFF', 'OVERDRAFT', 'WITHHOLD_TAX', 'EXCESS_CREDIT', 'GUARANTEE_RESERVE', 'CHARGE') DEFAULT 'DEPOSIT',
    transaction_number VARCHAR(100),
    reason_for_transaction VARCHAR(100),
    value_date DATE,
    accumulating_balance_id BIGINT,
    created_by BIGINT,
    user_id BIGINT,
    allow_negative_balance TINYINT(1) DEFAULT 0,
    interest_on_cheques DECIMAL(19, 6) DEFAULT 0,
    is_opern_balance TINYINT(1) DEFAULT 0,
    template_id BIGINT,
    tax_payment_details_id BIGINT,
    over_draft_amount_derived DECIMAL(19, 6) DEFAULT 0,
    constraint CHECK ((transaction_type <> 'CHARGE') OR (transaction_type = 'CHARGE' AND charge_id IS NOT NULL)),
    FOREIGN KEY (account_id) REFERENCES m_savings_account.id),
    FOREIGN KEY (office_id) REFERENCES m_office.id),
    FOREIGN KEY (user_id) REFERENCES m_appuser(id),
    FOREIGN KEY (created_by) REFERENCES m_appuser(id),
    INDEX idx_savings_transaction_account (account_id),
    INDEX idx_savings_transaction_date (transaction_date),
    INDEX idx_savings_transaction_type (transaction_type)
);

6. Financial Accounting Tables​

General Ledger Structure​

-- Chart of accounts
CREATE TABLE m_gl_account (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    parent_id BIGINT,
    gl_code VARCHAR(45) UNIQUE NOT NULL,
    disabled TINYINT(1) DEFAULT 0,
    manual_journal_entries TINYINT(1) DEFAULT 1,
    account_type ENUM('ASSET', 'LIABILITY', 'EQUITY', 'INCOME', 'EXPENSE') NOT NULL,
    account_usage ENUM('HEADER', 'LEDGER') DEFAULT 'LEDGER',
    classification ENUM('NOT_DEFINED', 'CASH', 'CASH_IFRS') DEFAULT 'NOT_DEFINED',
    system_defined ENUM('CUSTOMER_FUNDING_SOURCES', 'ACCRUAL_ACCRUED_EXPENSE', 'ACCRUAL_ACCRUED_INTEREST', 'CASH_CLEARING_ACCOUNTS', 'INTEREST_PAYABLE', 'INTEREST_RECEIVABLE') DEFAULT NULL,
    header_account_id BIGINT,
    account_type_enum INT DEFAULT 0,
    manual_journal_allowed BOOLEAN DEFAULT TRUE,
    tag_id BIGINT,
    description TEXT,
    is_cash_basis_tally_derived TINYINT(1) DEFAULT 0,
    can_be_used_in_journal_entries TINYINT(1) DEFAULT 1,
    organization_access_type ENUM('ALL', 'INDIVIDUAL_ENTRY_ONLY') DEFAULT 'ALL',
    FOREIGN KEY (parent_id) REFERENCES m_gl_account(id),
    FOREIGN KEY (header_account_id) REFERENCES m_gl_account(id),
    INDEX idx_gl_account_code (gl_code),
    INDEX idx_gl_account_type (account_type),
    INDEX idx_gl_account_parent (parent_id)
);

-- Journal entries
CREATE TABLE m_gl_journal_entry (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    office_id BIGINT NOT NULL,
    transaction_id VARCHAR(100) UNIQUE,
    unpe_run_id BIGINT,
    batch_id BIGINT,
    entry_date DATE NOT NULL,
    gl_account_id BIGINT NOT NULL,
    account_entry_date DATE NOT NULL,
    form_action ENUM('ACCEPT', 'REJECT') DEFAULT 'ACCEPT',
    journal_entry_type ENUM('CR', 'DR') NOT NULL,
    amount DECIMAL(19, 6) NOT NULL,
    user_id BIGINT,
    is_reversed TINYINT(1) DEFAULT 0,
    reversed_on_date DATE,
    transaction_id_reversed_by VARCHAR(100),
    comments TEXT,
    created_by_user_id BIGINT,
    created_date DATETIME NOT NULL,
    manually_created TINYINT(1) DEFAULT 0,
    entity_type VARCHAR(45),
    entity_id BIGINT,
    reference_number VARCHAR(100),
    retry_count INT DEFAULT 0,
    original_ignored TINYINT(1) DEFAULT 0,
    is_flexible_type ENUM('YES', 'NO') DEFAULT 'NO',
    FOREIGN KEY (office_id) REFERENCES m_office.id),
    FOREIGN KEY (gl_account_id) REFERENCES m_gl_account.id),
    FOREIGN KEY (user_id) REFERENCES m_appuser.id),
    INDEX idx_journal_entry_office (office_id),
    INDEX idx_journal_entry_date (entry_date),
    INDEX idx_journal_entry_account (gl_account_id),
    INDEX idx_journal_entry_transaction (transaction_id)
);

-- Journal entry lines
CREATE TABLE m_gl_journal_entry_line (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    journal_entry_id BIGINT NOT NULL,
    gl_account_id BIGINT NOT NULL,
    entry_date DATE NOT NULL,
    description TEXT,
    amount DECIMAL(19, 6) NOT NULL,
    transaction_date DATE,
    enterprise_year TIMESTAMP,
    enterprise_month INT,
    undepr_till 0,
    FOREIGN KEY (journal_entry_id) REFERENCES m_gl_journal_entry(id),
    FOREIGN KEY (gl_account_id) REFERENCES m_gl_account.id),
    INDEX idx_journal_line_entry (journal_entry_id),
    INDEX idx_journal_line_account (gl_account_id)
);

7. Audit and Versioning Tables​

Audit Trail Structure​

-- Audit trail for all entity changes
CREATE TABLE m_audit (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    entity_name VARCHAR(200) NOT NULL,
    entity_id BIGINT NOT NULL,
    resource_id VARCHAR(200) NOT NULL,
    action ENUM('INSERT', 'UPDATE', 'DELETE') NOT NULL,
    changed_by_user_id BIGINT NOT NULL,
    changed_at DATETIME NOT NULL,
    office_id BIGINT,
    working_client_id BIGINT,
    hash VARCHAR(100),
    after_json TEXT,
    before_json TEXT,
    FOREIGN KEY (changed_by_user_id) REFERENCES m_appuser.id),
    FOREIGN KEY (office_id) REFERENCES m_office.id),
    FOREIGN KEY (working_client_id) REFERENCES m_client.id),
    INDEX idx_audit_entity (entity_name, entity_id),
    INDEX idx_audit_user (changed_by_user_id),
    INDEX idx_audit_date (changed_at)
);

-- Entity audit version tracking
CREATE TABLE m_entity_audit (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    entity_type VARCHAR(100) NOT NULL,
    entity_id BIGINT NOT NULL,
    version_number INT NOT NULL,
    created_by BIGINT NOT NULL,
    created_date DATETIME NOT NULL,
    changes TEXT,
    FOREIGN KEY (created_by) REFERENCES m_appuser.id),
    INDEX idx_entity_audit_type (entity_type),
    INDEX idx_entity_audit_entity (entity_id, version_number)
);

-- Business rule validation log
CREATE TABLE m_validation_run (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    run_date DATE NOT NULL,
    start_time DATETIME NOT NULL,
    end_time DATETIME,
    processed_records INT DEFAULT 0,
    error_records INT DEFAULT 0,
    status ENUM('RUNNING', 'COMPLETED', 'FAILED') DEFAULT 'RUNNING',
    error_message TEXT,
    created_by BIGINT NOT NULL,
    FOREIGN KEY (created_by) REFERENCES m_appuser.id),
    INDEX idx_validation_run_date (run_date),
    INDEX idx_validation_run_status (status)
);

-- Job execution tracking
CREATE TABLE m_batch_job_execution (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    job_name VARCHAR(100) NOT NULL,
    job_instance_id BIGINT,
    execution_context TEXT,
    start_time DATETIME NOT NULL,
    end_time DATETIME,
    status ENUM('STARTING', 'COMPLETED', 'FAILED', 'STOPPED', 'ROLLBACK') DEFAULT 'STARTING',
    exit_code VARCHAR(50),
    exit_message TEXT,
    last_updated DATETIME NOT NULL,
    job_count INT DEFAULT 0,
    skip_count INT DEFAULT 0,
    commit_count INT,
    rollback_count INT,
    created_by BIGINT,
    FOREIGN KEY (created_by) REFERENCES m_appuser.id),
    INDEX idx_job_execution_name (job_name),
    INDEX idx_job_execution_status (status),
    INDEX idx_job_execution_date (start_time)
);

Indexing Strategy​

Performance Optimization Indexes​

-- Multi-tenant indexing strategy
CREATE INDEX idx_m_client_tenant ON m_client (office_id, status);
CREATE INDEX idx_m_loan_tenant ON m_loan (office_id, status);
CREATE INDEX idx_m_savings_account_tenant ON m_savings_account (office_id, status);

-- Query optimization indexes
CREATE INDEX idx_loan_status_date ON m_loan (status, expected_disbursement_date);
CREATE INDEX idx_loan_client_status ON m_loan (client_id, status);
CREATE INDEX idx_loan_product_status ON m_loan (loan_product_id, status);

CREATE INDEX idx_savings_client_status ON m_savings_account (client_id, status);
CREATE INDEX idx_savings_transaction_date ON m_savings_account_transaction (transaction_date, account_id);

CREATE INDEX idx_journal_entry_date_type ON m_gl_journal_entry (entry_date, journal_entry_type);
CREATE INDEX idx_audit_date_user ON m_audit (changed_at, changed_by_user_id);

-- Composite indexes for complex queries
CREATE INDEX idx_loan_repayment_schedule_period ON m_loan_repayment_schedule (loan_id, due_date, completed_derived);
CREATE INDEX idx_loan_transaction_loan_date ON m_loan_transaction (loan_id, transaction_date, is_reversed);

Data Integrity Constraints​

Foreign Key Relationships​

-- Enforce referential integrity
ALTER TABLE m_loan ADD CONSTRAINT fk_loan_client 
    FOREIGN KEY (client_id) REFERENCES m_client(id) ON DELETE CASCADE;

ALTER TABLE m_loan ADD CONSTRAINT fk_loan_product 
    FOREIGN KEY (loan_product_id) REFERENCES m_product_loan(id) ON DELETE RESTRICT;

ALTER TABLE m_loan_repayment_schedule ADD CONSTRAINT fk_schedule_loan 
    FOREIGN KEY (loan_id) REFERENCES m_loan(id) ON DELETE CASCADE;

ALTER TABLE m_loan_transaction ADD CONSTRAINT fk_transaction_loan 
    FOREIGN KEY (loan_id) REFERENCES m_loan(id) ON DELETE CASCADE;

ALTER TABLE m_savings_account ADD CONSTRAINT fk_savings_client 
    FOREIGN KEY (client_id) REFERENCES m_client(id) ON DELETE RESTRICT;

Check Constraints​

-- Business rule validation
ALTER TABLE m_loan ADD CONSTRAINT chk_loan_principal_positive 
    CHECK (principal_amount > 0);

ALTER TABLE m_loan ADD CONSTRAINT chk_loan_interest_rate_valid 
    CHECK (interest_rate_per_period >= 0 AND interest_rate_per_period <= 100);

ALTER TABLE m_savings_account ADD CONSTRAINT chk_savings_balance_positive 
    CHECK (account_balance_derived >= 0);

ALTER TABLE m_gl_journal_entry ADD CONSTRAINT chk_journal_amount_positive 
    CHECK (amount > 0);

Migration Strategy​

Schema Version Management​

-- Schema version tracking
CREATE TABLE schema_migration (
    version VARCHAR(50) PRIMARY KEY,
    script_name VARCHAR(200) NOT NULL,
    checksum VARCHAR(64),
    executed_on DATETIME DEFAULT CURRENT_TIMESTAMP,
    execution_time BIGINT,
    success TINYINT(1) DEFAULT 1,
    error_message TEXT,
    FOREIGN KEY (executed_by) REFERENCES m_appuser(id)
);

-- Migration history
CREATE TABLE m_schema_version (
    schema_version VARCHAR(50) PRIMARY KEY,
    applied_on DATETIME DEFAULT CURRENT_TIMESTAMP,
    description TEXT,
    created_by BIGINT,
    FOREIGN KEY (created_by) REFERENCES m_appuser(id)
);

Data Partitioning​

Time-Based Partitioning​

-- Partition large tables by date for better performance
ALTER TABLE m_loan_transaction PARTITION BY RANGE (YEAR(transaction_date)) (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

ALTER TABLE m_audit PARTITION BY RANGE (YEAR(changed_at)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

Security Considerations​

Data Masking dan Encryption​

-- Encrypted storage for sensitive data
CREATE TABLE m_client_sensitive_data (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    client_id BIGINT NOT NULL,
    national_id_encrypted VARBINARY(500),
    bank_account_encrypted VARBINARY(500),
    phone_encrypted VARBINARY(100),
    email_encrypted VARBINARY(200),
    created_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (client_id) REFERENCES m_client(id),
    INDEX idx_sensitive_client (client_id)
);

-- Data retention policies
CREATE TABLE m_data_retention_policy (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    table_name VARCHAR(100) NOT NULL,
    retention_period_years INT NOT NULL,
    archive_location VARCHAR(255),
    is_active TINYINT(1) DEFAULT 1,
    created_date DATETIME DEFAULT CURRENT_TIMESTAMP
);

Kesimpulan​

Arsitektur database Apache Fineract memberikan foundation yang solid untuk platform mikrofians dengan:

Kelebihan Arsitektur:​

  1. Multi-Tenancy: Efficient tenant isolation dengan shared infrastructure
  2. Data Integrity: Strong referential integrity dan business rule validation
  3. Performance: Strategic indexing dan query optimization
  4. Scalability: Partitioning strategy untuk large-scale deployments
  5. Audit Trail: Comprehensive change tracking untuk compliance
  6. Flexibility: Extensible schema design untuk customization

Best Practices:​

  1. Indexing Strategy: Query optimization melalui strategic indexes
  2. Data Validation: Check constraints untuk business rule enforcement
  3. Migration Management: Version control untuk schema changes
  4. Security: Encrypted storage untuk sensitive information
  5. Performance: Time-based partitioning untuk high-volume data
  6. Monitoring: Audit tables untuk system monitoring dan debugging

Design Patterns:​

  1. Normalization: 3NF database design untuk data consistency
  2. Archival Strategy: Data retention policies untuk compliance
  3. Audit Pattern: Comprehensive change tracking
  4. Multi-Tenant Pattern: Shared schema dengan tenant isolation
  5. Time-Based Partitioning: Historical data management
  6. Soft Delete: Logical deletion dengan timestamps

Arsitektur ini mendukung complex financial operations dengan maintainability, performance, dan data integrity yang tinggi.

Dokumentasi ini menjelaskan schema database secara detail. Implementasi spesifik dapat bervariasi berdasarkan konfigurasi deployment dan customization requirements.