Core Banking System: Database First

The brief

A small private bank in Seychelles has run on an old DOS system for decades. New regulations require it to join the country's common banking switch, so it needs a real core banking system. Our job was the first phase: branches, accounts, internal transfers, fixed deposits and loans. The course was clear about what mattered most: the database. The UI only had to be enough for QA testers to exercise it.

The rules came with the brief. Savings accounts pay 10-13% depending on the customer's age band, with a minimum balance for each. Current accounts pay nothing. A fixed deposit pays interest every 30 days into a linked savings account. Loans made at a branch need a manager's approval. Online loans are instant, but only for customers with a fixed deposit and only up to 60% of it. Managers need reports on branch transactions and late loan payments.

The schema

Twenty-five tables, grouped around accounts, users, transactions and loans. Customers are either individuals or organisations, and both inherit from a shared customer table. Accounts work the same way: a savings_account or checking_account row extends a base account, so balances, branches and status sit in one place and each account type only carries what is specific to it. Every money movement is a row in transaction, with a deposit, withdrawal or transfer row recording the branch or the beneficiary.

Rules in the database, not the app

The point of the course was to make the database guarantee correctness on its own, so the Express server only calls it.

Money moves in stored procedures. MoneyTransfer checks that both accounts are active and that the sender can afford it. It then debits one account, credits the other and writes both transaction rows inside a single transaction. Any error rolls the whole thing back. WithdrawFunds, DepositFunds and loan repayments follow the same pattern, so there is no path through the app that updates a balance without writing a matching record.

Loans follow the rules in SQL. ApplyLoan sets the interest rate from the loan type and term. GetCreditLimit works out 60% of a customer's fixed deposits for online loans. approve_loan calls an is_manager function and refuses anyone else. Once a loan has installments, a trigger creates the matching payment record with its due date. Paying late adds a penalty from a penalty_types table.

Time-based work runs as events. MySQL's event scheduler runs the jobs that would otherwise need a cron server: fixed-deposit interest every day for deposits on a 30-day boundary, savings interest every 30 days, and a daily pass that marks overdue installments as unpaid.

Reports are views. Managers see branch transactions and late loan payments through views, so a report is a SELECT against a view rather than SQL rebuilt in the app. Indexes cover the lookups the app makes constantly: account numbers, customer NIC and licence numbers, user email and transaction dates. Triggers keep an audit log whenever customer details change.

The app on top

A React and Material UI front end talks to an Express API. Login uses bcrypt password hashes, short-lived JWT access tokens and refresh tokens stored in the database. The user's role decides which app they get. Customers see their accounts, history, transfers and loans. Employees open accounts and register customers. Managers also approve or reject loans and read the branch reports.

Built by team Cybernetica5: Dineth Edirisinghe, Semini Harinakshi, Denuwan Hendalage, Suraja Hasarinda and me.

What I took from it

Putting the rules in the database changes how you think about bugs. If a transfer can only happen inside a procedure that wraps the debit, credit and log in one transaction, a half-finished transfer cannot happen, whatever the app does. Looking back, the next step would be row locks (SELECT ... FOR UPDATE) on the balance checks. Without them, two withdrawals arriving at the same moment could both pass the check before either one updates the balance.