### 🏦 Why Financial Systems Rely on Double-Entry Ledger SQL Architecture Modern banking systems require absolute consistency, high auditing capabilities, and strict risk controls. Every cent deposited, transferred, or loaned must be tracked in a dual-aspect accounting layout: **Debits must always equal Credits**. In this premium masterclass case study, we dive deep into the schema design and advanced reporting queries of **ApexBank's Double-Entry Ledger Engine**. We'll cover everything from customer onboarding and transactional statements to non-performing asset (NPA) classification, monthly interest schedules, and fraud detection. --- ### 🗂️ Part 1: Core Relational Database Design The foundation of our retail bank utilizes eight highly normalized core tables. They handle client data, multi-tier account types, debit/credit transactions, loan products, dynamic amortization schedules, and the general ledger. | Table Name | Primary Responsibility | Key Fields | | :--- | :--- | :--- | | **customers** | Onboards individuals, storing PII & secure KYC identification | `customer_id`, `full_name`, `id_number`, `phone` | | **account_types**| Declares product features (Savings interest rates, fees, limits) | `type_id`, `type_name`, `interest_rate` | | **accounts** | Tracks real-time active balances linked to customers | `account_id`, `account_number`, `current_balance` | | **transactions** | Preserves immutable history of all cash flows (deposits, fees) | `transaction_id`, `amount`, `transaction_type` | | **loan_products**| Outlines base product terms (Mortgage, Personal, Auto) | `product_id`, `product_name`, `base_interest_rate` | | **loans** | Stores active consumer loan principal amounts & rates | `loan_id`, `principal_amount`, `outstanding_principal` | | **loan_payments**| Manages upcoming amortization installments & statuses | `payment_id`, `due_date`, `status` | | **ledger_entries**| Immutable double-entry general ledger posting registry | `entry_id`, `entry_date`, `debit_amount`, `credit_amount` | --- ### 🔍 Part 2: Level Zero – Foundational Banking Reports #### 2.1 Customer Account & Balance Overview To render user dashboards upon login, we compile customer demographic summaries alongside their real-time gross checking and savings balances: ```sql SELECT c.full_name, a.account_number, at.type_name, a.current_balance FROM customers c INNER JOIN accounts a ON c.customer_id = a.customer_id INNER JOIN account_types at ON a.type_id = at.type_id; ``` #### 2.2 Dynamic Mini-Statement (Last 5 Transactions) To fetch account statement timelines, we query transactional details sorted chronologically with strict fetch limiting: ```sql SELECT transaction_date, amount, transaction_type, description FROM transactions WHERE account_id = 1 ORDER BY transaction_date DESC LIMIT 5; ``` --- ### 🧱 Part 3: Intermediate – Ledger, Running Balances, Interest #### 3.1 Daily Ledger Report (All Entries with Running Balance) Double-entry accounting relies on tracking the net change of debits vs. credits. Running balance is cumulative per `account_code`. This report summarizes all postings for a given day: ```sql WITH daily_entries AS ( SELECT entry_date, account_code, description, debit_amount, credit_amount, (debit_amount - credit_amount) AS net_change FROM ledger_entries WHERE entry_date = '2026-01-15' ), running AS ( SELECT account_code, entry_date, description, debit_amount, credit_amount, SUM(net_change) OVER (PARTITION BY account_code ORDER BY entry_date) AS running_balance FROM daily_entries ) SELECT * FROM running ORDER BY account_code; ``` * **Logic Deep-Dive:** * **Net change:** calculated as `debit_amount - credit_amount`. A positive value indicates an increase in assets/expenses, and a negative value represents an increase in liabilities/equity. * **Window SUM:** compiles the cumulative balance per account code sequentially after each entry. #### 3.2 Calculate Monthly Interest on Savings Accounts Interest is calculated daily based on the active closing balance and credited monthly. We automate this using a PostgreSQL stored procedure: ```sql CREATE OR REPLACE FUNCTION credit_monthly_interest() RETURNS VOID AS $$ DECLARE acc RECORD; daily_rate NUMERIC; days_in_month INT; interest_earned NUMERIC; BEGIN FOR acc IN SELECT a.account_id, a.current_balance, at.interest_rate, COALESCE(a.last_interest_date, a.opened_date) AS last_date FROM accounts a JOIN account_types at ON a.type_id = at.type_id WHERE a.status = 'active' AND at.interest_rate > 0 LOOP -- Compute days since last interest (simplified - assumes current date as month-end) days_in_month := EXTRACT(DAY FROM (DATE_TRUNC('MONTH', CURRENT_DATE) + INTERVAL '1 MONTH - 1 day')::DATE); daily_rate := acc.interest_rate / 100.0 / 365.0; interest_earned := acc.current_balance * daily_rate * days_in_month; -- Insert transaction for interest credit INSERT INTO transactions (account_id, amount, transaction_type, description) VALUES (acc.account_id, interest_earned, 'interest', 'Monthly interest credit'); -- Update account balance UPDATE accounts SET current_balance = current_balance + interest_earned, last_interest_date = CURRENT_DATE WHERE account_id = acc.account_id; -- Post to ledger: Debit Interest Expense, Credit Customer Deposit Liability INSERT INTO ledger_entries (entry_date, account_code, description, debit_amount, credit_amount) VALUES (CURRENT_DATE, 'InterestExpense', 'Monthly savings interest accrual', interest_earned, 0), (CURRENT_DATE, 'CustomerDeposits', 'Interest credited', 0, interest_earned); END LOOP; END; $$ LANGUAGE plpgsql; ``` #### 3.3 Loan Amortization Schedule (Generating Future Payments) For a new loan, we pre-generate monthly installment amortization schedules using the standard Equated Monthly Installment (EMI) formula: $$\text{EMI} = P \times \frac{r(1+r)^n}{(1+r)^n - 1}$$ Where $P$ is the principal, $r$ is the monthly interest rate, and $n$ is the tenure in months. ```sql CREATE OR REPLACE FUNCTION generate_amortization_schedule(p_loan_id INT) RETURNS VOID AS $$ DECLARE v_principal NUMERIC; v_rate_monthly NUMERIC; v_tenure_months INT; v_emi NUMERIC; v_balance NUMERIC; v_interest NUMERIC; v_principal_part NUMERIC; v_due_date DATE; i INT; BEGIN -- Fetch loan details SELECT principal_amount, (interest_rate / 100.0 / 12.0), ((EXTRACT(YEAR FROM end_date)*12 + EXTRACT(MONTH FROM end_date)) - (EXTRACT(YEAR FROM start_date)*12 + EXTRACT(MONTH FROM start_date))) INTO v_principal, v_rate_monthly, v_tenure_months FROM loans WHERE loan_id = p_loan_id; -- Calculate EMI v_emi := v_principal * v_rate_monthly * POWER(1 + v_rate_monthly, v_tenure_months) / (POWER(1 + v_rate_monthly, v_tenure_months) - 1); v_balance := v_principal; v_due_date := (SELECT start_date FROM loans WHERE loan_id = p_loan_id); FOR i IN 1..v_tenure_months LOOP v_due_date := v_due_date + INTERVAL '1 month'; v_interest := v_balance * v_rate_monthly; v_principal_part := v_emi - v_interest; v_balance := v_balance - v_principal_part; INSERT INTO loan_payments (loan_id, due_date, installment_amount, principal_part, interest_part, status) VALUES (p_loan_id, v_due_date, v_emi, v_principal_part, v_interest, 'pending'); END LOOP; -- Update outstanding principal UPDATE loans SET outstanding_principal = v_principal WHERE loan_id = p_loan_id; END; $$ LANGUAGE plpgsql; ``` --- ### 🛡️ Part 4: Advanced Reports & Analytics (The "Hero") #### 4.1 Daily Ledger Reconciliation - Check Double-Entry Integrity Under strict regulatory compliance frameworks, banking systems must audit the general ledger daily to ensure total debits equal total credits. If a mismatch is detected, the database triggers a critical compliance alert: ```sql WITH reconciliation AS ( SELECT entry_date, SUM(debit_amount) AS total_debits, SUM(credit_amount) AS total_credits FROM ledger_entries GROUP BY entry_date ) SELECT entry_date, total_debits, total_credits, CASE WHEN total_debits = total_credits THEN 'OK' ELSE 'MISMATCH' END AS status FROM reconciliation ORDER BY entry_date DESC; ``` #### 4.2 Loan Risk Report - NPA (Non-Performing Assets) Classification According to RBI/BASEL guidelines, a loan asset becomes a Non-Performing Asset (NPA) if an installment remains overdue for more than 90 days. We classify overdue risk exposure into Substandard, Doubtful, or Loss bands: ```sql WITH overdue_summary AS ( SELECT l.loan_id, l.customer_id, l.outstanding_principal, MAX(julianday('2026-05-01') - julianday(lp.due_date)) AS max_days_overdue FROM loans l JOIN loan_payments lp ON l.loan_id = lp.loan_id WHERE lp.status = 'overdue' GROUP BY l.loan_id, l.customer_id, l.outstanding_principal ) SELECT loan_id, customer_id, outstanding_principal, max_days_overdue, CASE WHEN max_days_overdue <= 90 THEN 'Standard' WHEN max_days_overdue BETWEEN 91 AND 180 THEN 'Substandard' WHEN max_days_overdue BETWEEN 181 AND 365 THEN 'Doubtful' ELSE 'Loss' END AS npa_classification FROM overdue_summary WHERE max_days_overdue > 90 ORDER BY max_days_overdue DESC; ``` #### 4.3 Bank's Profitability Report - Interest Income vs. Interest Expense Monitors structural Net Interest Income (NII) margins by summing loan-interest earnings and subtracting deposit-interest expense from the general ledger records: ```sql SELECT (SELECT COALESCE(SUM(credit_amount), 0) FROM ledger_entries WHERE account_code = 'LoanInterestIncome' AND entry_date BETWEEN '2026-01-01' AND '2026-12-31') AS interest_income, (SELECT COALESCE(SUM(debit_amount), 0) FROM ledger_entries WHERE account_code = 'InterestExpense' AND entry_date BETWEEN '2026-01-01' AND '2026-12-31') AS interest_expense, (SELECT COALESCE(SUM(credit_amount), 0) FROM ledger_entries WHERE account_code = 'LoanInterestIncome' AND entry_date BETWEEN '2026-01-01' AND '2026-12-31') - (SELECT COALESCE(SUM(debit_amount), 0) FROM ledger_entries WHERE account_code = 'InterestExpense' AND entry_date BETWEEN '2026-01-01' AND '2026-12-31') AS net_interest_margin; ``` --- ### ⚠️ Common Pitfalls for Beginners * **Double-Counting Aggregate Joins:** When joining both accounts and loans to a customer, executing a direct SUM over transactions and loan payments inside a single level query generates **Cartesian duplicate products** (artificially inflating balances). Always aggregate separate metrics inside isolated Common Table Expressions (CTEs) before performing your final outer join! * **Loss of Precision in Interest Calculations:** Always store currencies as high-precision decimal fields (`NUMERIC(15,2)`) rather than floating-point floats or reals. Floating-point arithmetic introduces microscopic rounding losses over millions of rows, resulting in audit failures!