A database schema for a (ChipperCash-like) fin-tech multi-currency wallet app with types, accounts and transactions using either MySQL, SQLite or PostgreSQL as primary database

low_budget_fintech_wallet_ledger_app_design_database.sql

-- MySQL v8.0.16
-- PostgresSQL v16.9.2

CREATE DATABASE IF NOT EXISTS `test`
DEFAULT CHARACTER SET utf8 -- utf8mb4
DEFAULT COLLATE utf8_general_ci;  -- utf8mb4_unicode_ci

SET default_storage_engine = INNODB;

CREATE TABLE user_details (
	id bigint unsigned NOT NULL,
	email varchar(50) NOT NULL UNIQUE,
  	first_name varchar(90) NOT NULL,
  	last_name varchar(90) NOT NULL,
  	profile_avatar_url varchar(130), -- start with value of NULL and use background job to upload image to `cloudinary` OR `aws-s3` an then later write url value back
  	gender enum('male', 'female') NOT NULL,
  	date_of_birth date NOT NULL,
  	registered_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  	CONSTRAINT userunq UNIQUE (id, first_name, last_name),
  	PRIMARY KEY (id)
);

CREATE INDEX idx_namex
ON user_details (id, last_name, registered_at);

CREATE TABLE user_accounts (
	id bigint unsigned NOT NULL,
  	email varchar(50) NOT NULL UNIQUE, -- data redundancy for easier access
	full_name varchar(150) NOT NULL, -- data redundancy for easier access
  	password varchar(36) NOT NULL,
	last_login_at datetime,
  	is_active tinyint(1) NOT NULL DEFAULT 1,
	email_verified tinyint(1) NOT NULL DEFAULT 0,
	phone_number varchar(16) UNIQUE NULL,
  	PRIMARY KEY (id),
  	FOREIGN KEY (id) REFERENCES user_details(id) ON DELETE CASCADE
);

CREATE INDEX idx_authx
ON user_accounts (id, email, password);

CREATE TABLE ledger_types (
  	id bigint unsigned NOT NULL AUTO_INCREMENT,
  	operational_attributes json NOT NULL DEFAULT (JSON_OBJECT()),
  	currency enum('NGN', 'USD', 'KSH', 'GBP') NOT NULL,
  	PRIMARY KEY (id)
);

CREATE TABLE ledger_accounts (
	id bigint unsigned NOT NULL,
  	type_id bigint unsigned NOT NULL,
	currency enum('NGN-kobo', 'USD-cents', 'KSH-shillings', 'GBP-shillings') NOT NULL,
	account_number varchar(10) NOT NULL,
  	owner_id bigint unsigned NOT NULL,
	account_created_at date NOT NULL,
	balance_updated_at datetime NOT NULL,
	balance_credit bigint unsigned NOT NULL DEFAULT 0, -- PN Counter modelling (from CRDTs)
	balance_debit bigint unsigned NOT NULL DEFAULT 0, -- PN Counter modelling (from CRDTs)
	PRIMARY KEY (id),
  	FOREIGN KEY (type_id) REFERENCES ledger_types(id) ON DELETE CASCADE,
  	FOREIGN KEY (owner_id) REFERENCES user_details(id) ON DELETE CASCADE
);

ALTER TABLE ledger_accounts ADD CONSTRAINT chk_balance_debit CHECK (balance_debit < balance_credit)

CREATE TABLE ledger_transactions (
	id bigint unsigned NOT NULL,
	type enum('debit', 'credit') NOT NULL,
	reference varchar(30) NOT NULL,
	narration text,
	amount bigint unsigned NOT NULL,
	entry_created_at datetime NOT NULL,
	idempotency_key varchar(38) NOT NULL,
	wallet_id bigint unsigned NOT NULL,
	has_pay_gateway_webhook_resolved tinyint(1) NOT NULL DEFAULT 0,
	external_transaction_ref varchar(20) NOT NULL,
	CONSTRAINT trnxunq UNIQUE (id, type, amount, reference, wallet_id, idempotency_key),
	PRIMARY KEY (id),
	FOREIGN KEY (wallet_id) REFERENCES ledger_accounts (id) ON DELETE CASCADE,
);


-- Transactional Outbox Pattern - Outbox Table
CREATE TABLE ledger_account_transactions_outbox_events (
	account_id bigint unsigned NOT NULL,
	transaction_id bigint unsigned NOT NULL,
	payload json NOT NULL DEFAULT (JSON_OBJECT()),
	sent_at timestamp,
	event_type enum('account_debited', 'account_credited') NOT NULL,
	next_run timestamp,
	interval_in_seconds bigint NOT NULL DEFAULT -1,
	created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY (account_id, transaction_id),
	FOREIGN KEY (account_id) REFERENCES ledger_accounts(id) ON DELETE CASCADE,
	FOREIGN KEY (transaction_id) REFERENCES ledger_transactions(id) ON DELETE CASCADE
);

CREATE TABLE ledger_account_virtual_cards (
	id bigint unsigned NOT NULL,
    wallet_id bigint unsigned NOT NULL,
    card_number varchar(16) NOT NULL UNIQUE,
    expiry_date varchar(5) NOT NULL,
    cvv varchar(3) NOT NULL UNIQUE,
    card_status enum('active', 'inactive', 'suspended') NOT NULL DEFAULT 'active',
    created_at timestamp DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    INDEX wallet_indx (wallet_id),
    FOREIGN KEY (wallet_id) REFERENCES  ledger_accounts (wallet_id) ON DELETE CASCADE
);

INSERT INTO user_details (id, first_name, last_name, gender, profile_avatar_url, date_of_birth, email) VALUES
(1, 'Akin', 'Lewi-Soyinka', 'male', 'https://me.assets/image/sBSd65749', '1988-04-11', 'akinbaba345@gmail.com'),
(2, 'Tabitha', 'Ngorh', 'female', 'https://me.assets/image/o84Z8401Rytr', '1994-10-02', 'tabbysweetie20@yahoo.com'),
(3, 'Eze', 'Barnabas', 'male', 'https://me.assets/image/pO74Judc748', '1990-08-25', 'eze4reel@hotmail.com'),
(4, 'Destiny', 'Okondo', 'female', 'https://me.assets/image/placeholder', '1991-09-12', 'destiny.oko1@gmail.com');

INSERT INTO user_accounts (id, email, password, last_login_at, full_name, email_verified, is_active, phone_number) VALUES
(2, 'tabbysweetie20@yahoo.com', 'xbu648dbs8849', '2025-03-11T00:00:00Z', 'Tabitha Ngorh', 1, 1, '+2348136682310'),
(4, 'destiny.oko1@gmail.com', 'sby378bT%#3bjZ', '2025-04-23T00:00:00Z', 'Destiny Okondo', 1, 1, '08034211563'),
(3, 'eze4reel@hotmail.com', 'cBv$33!LodMre', NULL, 'Eze Barnabas', 1, 1, '09037642119'),
(1, 'akinbaba345@gmail.com', '', NULL, 'Akin Lewi-Soyinka', 0, 0, '+2347050901336');

INSERT INTO ledger_types (id, operational_attributes, currency) VALUES
(1, '{ "overdraft_enabled": false, "cost_of_transaction": "0%", "activity_alert_channels": ["email"], "max_inflow_limit": 100_000, "min_outflow_limit": 0, "maintenance_charges": ["e-cash_transfer_levy"] }', 'NGN'),
(3, '{ "overdraft_enabled": true, "cost_of_transaction": "10%", "activity_alert_channels": ["sms", "email"], "max_inflow_limit": 3_000_000_000, "min_outflow_limit": 100_000, "maintenance_charges": ["alert_channels", "standing_instruction"] }', 'USD'),
(4, '{ "overdraft_enabled": false, "cost_of_transaction": "0%", "activity_alert_channels": ["email"], "max_inflow_limit": 500_000, "min_outflow_limit": 0, "maintenance_charges": ["alert_channels"] }', 'NGN')

INSERT INTO ledger_accounts (id, type_id, currency, owner_id, account_created_at, balance_updated_at, balance_credit, balance_debit, account_number) VALUES
(1, 1, 'NGN-kobo', 2, '2024-08-18', '2024-09-24T00:00:00Z', 0, 0, '0128993994'),
(2, 1, 'NGN-kobo', 4, '2024-07-21', '2024-11-30T00:00:00Z', 0, 0, '0127833455'),
(3, 3, 'USD-cents', 3, '2025-02-10', '2025-02-10T00:00:00Z', 0, 0, '0136609342'),
(4, 4, 'NGN-kobo', 3, '2024-10-10', '2024-11-30T00:00:00Z', 100000, 0, '0125633801')

INSERT INTO ledger_transactions (id, type, reference, narration, amount, entry_created_at, wallet_id, has_pay_gateway_webhook_resolved, external_transaction_ref, idempotency_key) VALUES
(1, 'credit', 'dF538jKla372mNv90', 'pocket money from sis', 200000, '2024-08-22T00:00:00Z', 1, 1, '', ''),
(2, 'credit', 'pu834kAbX950rTy21', 'birthday gift cash', 560000, '2024-09-15T00:00:00Z', 1, 1, '', ''),
(3, 'debit', 'qW665eHvC437dXz54', 'nil', 150000, '2024-09-24T00:00:00Z', 1, 0, '', ''),
(4, 'credit', 'iJ187lFcO967gIq32', 'mobile tfr to access', 3450000, '2024-11-30T00:00:00Z', 2, 0, '', ''),
(5, 'debit', 'dF538jKla372mNv90', 'pocket money from sis', 200000, '2024-08-22T00:00:00Z', 2, 1, '', ''), -- double-entry book-keeping
(6, 'debit', 'pu834kAbX950rTy21', 'birthday gift cash', 560000, '2024-09-15T00:00:00Z', 2, 1, '', ''), -- double-entry book-keeping
(7, 'credit', 'qW665eHvC437dXz54', 'nil', 150000, '2024-09-24T00:00:00Z', 2, 0, '', ''), -- double-entry book-keeping
(8, 'debit', 'iJ187lFcO967gIq32', 'mobile tfr to access', 3450000, '2024-11-30T00:00:00Z', 4, 0, '', '') -- double-entry book-keeping

-- INSERT INTO ledger_account_transactions_outbox_events (`account_id`, `transaction_id`, `payload`. `sent_at`, `event_type`) VALUES (...) ON CONFLICT (sent_at) DO NOTHING

INSERT INTO ledger_account_transactions_outbox_events (account_id, transaction_id, payload, sent_at, event_type, next_run) VALUES
(1, 1, '{ "type": "credit", "amount": 200000, "owner_id": 2, "currency": "NGN-kobo", "reference": "dF538jKla372mNv90" }', NULL, 'account_credited', NULL),
(2, 5, '{ "type": "debit", "amount": 200000, "owner_id": 4, "currency": "NGN-kobo", "reference": "dF538jKla372mNv90" }', NULL, 'account_debited', NULL),
(1, 2, '{ "type": "credit", "amount": 560000, "owner_id": 2, "currency": "NGN-kobo", "reference": "pu834kAbX950rTy21" }', NULL, 'account_credited', NULL),
(2, 4, '{ "type": "credit", "amount": 3450000, "owner_id": 4, "currency": "NGN-kobo", "reference": "iJ187lFcO967gIq32" }', NULL, 'account_credited', NULL)

-- Fetch balance (one-time) at the first load after wallet app login
SELECT
    ledger_accounts.balance_credit - ledger_accounts.balance_debit AS balance,
    ledger_accounts.balance_updated_at AS balance_last_modified_at,
    ledger_types.operational_attributes
FROM 
    ledger_accounts
INNER JOIN ledger_types ON ledger_accounts.type_id = ledger_types.id
WHERE ledger_accounts.owner_id = 3
AND ledger_accounts.currency = 'NGN-kobo'
AND ledger_accounts.id = 2;


-- Fetch outbox events using a read lock
SELECT
   *
FROM
   ledger_account_transactions_outbox_events
WHERE sent_at IS NULL
ORDER BY created_at
FOR UPDATE SKIP LOCKED
LIMIT 100;


-- Debit transaction for 2,000 Naira (or 200,000 kobo)
UPDATE 
    ledger_accounts
SET balance_debit = CASE
WHEN balance_debit + 200000 > balance_credit
THEN balance_debit
ELSE balance_debit + 200000 END,
balance_updated_at = NOW()
WHERE owner_id = 2
AND currency = 'NGN-kobo'
AND id = 1;


-- Credit transaction for 9,850 Naira (or 985,000 kobo)
UPDATE 
    ledger_accounts
SET balance_credit = balance_credit + 985000,
balance_updated_at = NOW()
WHERE owner_id = 2
AND currency = 'NGN-kobo'
AND id = 1;


-- Fetch balance (repetitive) anytime the up-to-date balance is subsequently requested after wallet app login and first load
-- @NOTE: that after this query returns, the first item (in the list of transactions returned) is deleted (since its sorted by time) and the `entry_created_at` column of the last item (in the list of transactions returned) is used as the new `balance_last_modified_at` column after the new balance is calculated by summing all credits transaction amount and debit transaction amounts separately and finding their difference and adding that difference to the existing balance as the new balance. 
SELECT
    type,
    amount,
    entry_created_at
FROM
    ledger_transactions
WHERE entry_created_at >= '2026-11-18T11:36:29.000Z' -- `2026-11-18T11:36:29.000Z` is the 'balance_last_modified_at' from the earlier SELECT query
AND account_id = 1
ORDER BY entry_created_at
GROUP BY type, has_pay_gateway_webhook_resolved;

Read: https://kaperskyguru.substack.com/p/how-would-you-design-a-globally-consistent?r=6bmjcc&utm_campaign=post&utm_medium=web&triedRedirect=true