I am currently working on a wallet system that allows transfers of money between users. I came up with the following structure, intending for the system to be robust, scalable, and capable of handling various types of financial transactions with proper tracking and integrity.

I am using PostgreSQL 16 and this is my schema:

CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR NOT NULL,
    email VARCHAR UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE wallets (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID REFERENCES users(id),
    balance NUMERIC(15, 2) DEFAULT 0.00,
    currency VARCHAR(3) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE transactions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    wallet_id UUID REFERENCES wallets(id),
    transaction_type VARCHAR NOT NULL CHECK (transaction_type IN ('credit', 'debit', 'transfer', 'refund', 'void')),
    amount NUMERIC(15, 2) NOT NULL,
    currency VARCHAR(3) NOT NULL,
    status VARCHAR NOT NULL CHECK (status IN ('pending', 'completed', 'voided', 'refunded')),
    reference_id UUID REFERENCES transactions(id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE transfers (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    sender_wallet_id UUID REFERENCES wallets(id),
    receiver_wallet_id UUID REFERENCES wallets(id),
    transaction_id UUID REFERENCES transactions(id),
    amount NUMERIC(15, 2) NOT NULL,
    currency VARCHAR(3) NOT NULL,
    status VARCHAR NOT NULL CHECK (status IN ('pending', 'completed', 'voided', 'refunded')),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

In order to know the balance of a wallet, my application can do the following query:

SELECT 
    COALESCE(SUM(
        CASE 
            WHEN transaction_type = 'credit' THEN amount 
            WHEN transaction_type = 'debit' THEN -amount 
            WHEN transaction_type = 'transfer' AND wallet_id = '$walletid' THEN -amount 
            WHEN transaction_type = 'transfer' AND wallet_id != '$walletid' THEN amount 
            ELSE 0 
        END
    ), 0) AS balance
FROM transactions
WHERE wallet_id = '$walletid' AND status = 'completed';

As wallets grow older, how to speed up the balance query? The more active a wallet is, the slower balance calculation is going to be. It always needs to calculate every single credit/debit since the beginning of time.