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

fintech_wallet_ledger_design_database.sql

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 NOT NULL,
  	first_name varchar(100) NOT NULL,
  	last_name varchar(100) NOT NULL,
  	profile_avatar_url text,
  	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 NOT NULL,
  	email varchar(50) NOT NULL,
	full_name varchar(200) NOT NULL,
  	password varchar(26) NOT NULL,
    	last_login_at date,
  	is_active tinyint(1) NOT NULL DEFAULT 1,
	email_verified tinyint(1) NOT NULL DEFAULT 0,
  	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 NOT NULL AUTO_INCREMENT,
  	operational_attributes json NOT NULL, -- { "overdraft_enabled": true, "cost_of_transaction": "free" }
  	currency enum('NGN', 'USD', 'KSH', 'GBP') NOT NULL,
  	PRIMARY KEY (id)
);

CREATE TABLE ledger_accounts (
	id bigint NOT NULL,
  	type_id bigint NOT NULL,
  	user_id bigint NOT NULL,
  	user_account_id bigint NOT NULL,
	created_at date NOT NULL,
	PRIMARY KEY (id),
  	FOREIGN KEY (type_id) REFERENCES ledger_types(id) ON DELETE CASCADE,
  	FOREIGN KEY (user_id) REFERENCES user_details(id) ON DELETE CASCADE,
  	FOREIGN KEY (user_account_id) REFERENCES user_details(id) ON DELETE CASCADE
);

CREATE TABLE ledger_transactions (
	id bigint NOT NULL,
	type enum('debit', 'credit') NOT NULL,
	reference varchar(40) NOT NULL,
	narration text,
	amount bigint NOT. NULL,
	created_at date NOT NULL,
	CONSTRAINT trnxunq UNIQUE (id, type, amount, reference),
	PRIMARY KEY (id)
);

CREATE TABLE ledger_account_transactions (
	account_id bigint NOT NULL,
	transaction_id bigint NOT NULL,
	FOREIGN KEY (account_id) REFERENCES ledger_accounts(id) ON DELETE CASCADE,
	FOREIGN KEY (transaction_id) REFERENCES ledger_transactions(id) ON DELETE CASCADE
);