my avatar

Strategies and Considerations for Implementing Account Balances in Financial Applications

When building an application that requires a wallet system to handle financial operations, one of the questions developers face is how to store wallet or account balances. In this blog post, we'll go over some ways these could be represented in software systems and consider the trade-offs of each approach. This post is also inspired by Shalvah's blog post on Transactions and Side Effects which you can read here.

One of the easiest mistakes to make when storing balances is to forget that they are actually value objects consisting of two parts: a currency and a value. This is because the balance of two wallets or accounts can only be said to be equal if and only if they are of the same currency and have the same monetary value. When you identify a balance as a value object, using a money library or package to manipulate it in the future becomes easy as you're already storing the components the library would need.

Account Balance as a Database Column

The first thing that comes to mind when storing balances is to use database columns to represent them. In MySQL, this can be easily achieved with the Data Definition Language (DDL) below

-- other columns omitted for brevity
CREATE TABLE `accounts` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `currency` varchar(4) COLLATE utf8mb4_unicode_ci NOT NULL,
  `balance` bigint NOT NULL DEFAULT '0'
);

Creating a new account is as simple as doing the below in NodeJS. Note that we do not need to specify the balance of the account when creating it. This is due to the default value we set when creating the table.

const account = new Account();

account.currency = 'USD';

await account.save();

The above DDL looks good until we realise that we've made one not-so-obvious mistake: our balance column can hold both positive and negative integers. Meaning that we could as well do the following when creating an account

const account = new Account();

account.currency = 'USD';
account.balance = -500;

await account.save();

The most important takeaway here is that we could at some point in the future end up with an overdrawn account; that is an account owner spending more money than they have in their account. To avoid this, we need to update our DDL like so

CREATE TABLE `accounts` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `currency` varchar(4) COLLATE utf8mb4_unicode_ci NOT NULL,
  `balance` bigint unsigned NOT NULL DEFAULT '0'
);

By making the balance column unsigned, we have instructed MySQL or any other SQL variant that supports unsigned integers that the smallet value allowed for the balance column is 0. The code below would now throw a database error when the save method is executed.

const account = new Account();

account.currency = 'USD';
account.balance = -500

// data truncation: Out of range value for column 'balance' 
await account.save();

Now, we're storing the account balance as a database field, but what could go wrong? For starters, this field now requires an update every time the account is credited or debited. Since our accounts table isn't designed to hold transaction details, we'll need to create a separate database table to manage that data.

CREATE TABLE `transactions` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `account_id` bigint unsigned NOT NULL,
  `type` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL,
  `currency` varchar(4) COLLATE utf8mb4_unicode_ci NOT NULL,
  `amount` bigint unsigned NOT NULL,
  `memo` varchar(400) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  CONSTRAINT `transactions_account_id_foreign` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE
);

The code below creates a new transaction and also updates the balance of the account.

if (account.balance < amount) {
    throw Error('Insufficient account balance');
}

const amount = 120;
const transaction = new Transaction();

transaction.account_id = account.id;
transaction.type = 'debit';
transaction.memo = 'Payment for services';
transaction.currency = account.currency;
transaction.amount = amount;
account.balance = account.balance - amount;

await transaction.save();
await account.save()

The problem with the code above is that one of the save operations may fail while the other succeeds. So this is a prime use case for database transactions.

if (account.balance < amount) {
    throw Error('Insufficient account balance');
}

try {
    // Start database transaction
    const dbTransaction = db.transaction();

    const amount = 120;
    const transaction = new Transaction();
    
    transaction.account_id = account.id;
    transaction.type = 'debit';
    transaction.memo = 'Payment for services';
    transaction.currency = account.currency;
    transaction.amount = amount;
    account.balance = account.balance - amount;
    
    await transaction.save();
    await account.save();

    // commit the database transaction
    await dbTransaction.commit();
} catch (error) {
    // rollback the transaction if any error occurs, 
    await dbTransaction.rollback();
}

Now, we have a database transaction that ensures both operations either succeed or fail together. However, there's still a critical mistake that could lead to significant financial loss: we're completely overlooking concurrent requests and how they result in database inconsistency.

Concurrent requests can lead to database inconsistencies when multiple requests occur simultaneously, and the outcome of one should have affected the others but didn't. Let me illustrate this in the context of the application we're building. Suppose an account has a balance of USD 20, and the account owner initiates 5 concurrent requests to transfer USD 20 out of their account. Ideally, our app should process only the first request and fail the subsequent ones. However, instead of failing, our system processes all 5 requests but only updates the account balance once. This inconsistency would go unnoticed until the user reports it or the finance department completes their reconciliation for the day, month, or quarter.

There are a few methods to enforce database consistency which Frank de Jonge wrote about here. I prefer to use mutexes implemented with Redis for database locks. Let's see what that could look like in code.

// Acquire the lock for 5 seconds
let accountLock = await lock.acquire([account.getLockKey()], 5000);

if (account.balance < amount) {
    throw new Error('Insufficient account balance');
}

try {
    const dbTransaction = db.transaction();

    const accountTransaction = new Transaction();
    accountTransaction.account_id = account.id;
    accountTransaction.type = 'debit';
    accountTransaction.memo = 'Payment for services';
    accountTransaction.currency = account.currency;
    accountTransaction.amount = amount;

    account.balance = account.balance - amount;

    await accountTransaction.save();
    await account.save();

    // Extend the lock if necessary
    accountLock = await lock.extend(5000);

    await dbTransaction.commit();
} catch (error) {
    await dbTransaction.rollback();
} finally {
    // Release the lock
    await lock.release();
}

To add an extra layer of correctness, we could write a background job to reconcile the balances of all accounts.

So by just deciding to use a database column to store balances, we've unknowingly decided to do all this work to ensure data consistency and prevent financial loss. So while this approach is more intuitive and offers great simplicity, it introduces several challenges such as

  • Manual Balance Updates: Requires updates to the balance column with each transaction, increasing the risk of errors and requiring additional logic for transaction management.
  • Risk of Overdrafts: Without additional constraints, the balance column can hold negative values, leading to potential overdrafts. Defining the balance column as an unsigned integer eliminates the possibility of negative balances, but this may not be ideal for all use cases.
  • Performance Issues: The thing with locks is that in high-throughput systems, deadlocks are almost impossible. So this approach may become inefficient with high transaction volumes, as frequent updates and locking can impact performance.
  • Inconsistent Data: Without proper transaction management and concurrency controls, multiple simultaneous operations can lead to data inconsistencies.

Account Balance as a Materialized View

An alternative approach is to compute an account's balance as the net sum of all its transactions. In this method, the transactions table forms the core for calculating account balances. We can utilize materialized views and database triggers to facilitate this, a feature supported by various SQL databases. PostgreSQL, MS SQL, and Oracle DB natively support materialized views, while MySQL 8.0 and later can achieve similar functionality through plugins or by combining tables and triggers. We'll demonstrate how to set up materialized views in PostgreSQL.

First, we'll create the necessary database tables and materialised view.

-- create the necessary database tables
CREATE TABLE accounts (
    id bigserial NOT NULL,
    currency varchar(4) NOT NULL,
    CONSTRAINT accounts_pkey PRIMARY KEY (id)
);

CREATE TABLE transactions (
    id bigserial NOT NULL,
    account_id int8 NOT NULL,
    "type" varchar(10) NOT NULL,
    currency varchar(4) NOT NULL,
    amount bigint NOT NULL,
    memo varchar(400) DEFAULT NULL::character varying NULL,
    CONSTRAINT transactions_pkey PRIMARY KEY (id),
    CONSTRAINT transactions_account_id_foreign FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE RESTRICT ON UPDATE CASCADE
);

-- create the materialised view
CREATE MATERIALIZED VIEW account_balances AS
SELECT 
    account_id,
    MAX(currency) AS currency,
    SUM(CASE WHEN type = 'credit' THEN amount ELSE 0 END) AS total_credit,
    SUM(CASE WHEN type = 'debit' THEN amount ELSE 0 END) AS total_debit,
    SUM(CASE WHEN type = 'credit' THEN amount ELSE -amount END) AS balance
FROM 
    transactions
GROUP BY 
    account_id;

In the DDL above, we first set up the accounts and transactions tables, and then create a materialized view named account_balances. Since the currency column in the transactions table should consistently hold one value for each account_id, using MAX(currency) effectively captures this value. This allows us to include the currency column in the SELECT statement of the GROUP BY clause without grouping by it explicitly.

PostgreSQL does not automatically update the materialized view with new data. To handle this, we need to define a database trigger which is a piece of code executed in response to specific events on a table. Here’s how our trigger would be defined:

-- Create a function to refresh the materialized view
CREATE OR REPLACE FUNCTION refresh_account_balances()
RETURNS TRIGGER AS $$
BEGIN
    REFRESH MATERIALIZED VIEW account_balances;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- Create a trigger to call the refresh function
CREATE TRIGGER refresh_account_balances_trigger
AFTER INSERT OR UPDATE OR DELETE ON transactions
FOR EACH STATEMENT
EXECUTE FUNCTION refresh_account_balances();

We created a PostgreSQL function refresh_account_balances to update the account_balances view using the REFRESH MATERIALIZED VIEW command. The trigger refresh_account_balances_trigger activates after any INSERT, UPDATE, or DELETE operation on the transactions table, ensuring the materialized view is refreshed automatically with changes.

However, during the refresh process, we might not want to block other database connections from reading existing data from the view. To address this, we update our function as follows:

CREATE OR REPLACE FUNCTION refresh_account_balances()
RETURNS TRIGGER AS $$
BEGIN
    REFRESH MATERIALIZED VIEW CONCURRENTLY account_balances;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

The CONCURRENTLY option allows the refresh occur without blocking other queries accessing the view. Note that this option requires a unique index on the materialized view. We need to add that index:

CREATE UNIQUE INDEX idx_account_balances_account_id ON account_balances(account_id);

Our application code to create a new transaction would look like this:

// assuming there is an accountBalance relationship on the account object
if (account.accountBalance.balance < amount) {
    throw Error('Insufficient account balance');
}

const transaction = new Transaction();
transaction.account_id = account.id;
transaction.type = 'debit';
transaction.memo = 'Payment for services';
transaction.currency = account.currency;
transaction.amount = 120;

await transaction.save();

Although this method offers accurate balances, simplified application code and fast results when querying the materialized view, there are some drawbacks to consider, such as:

  • Refresh Overhead: Frequent refreshes of the materialized view can impact performance in high-volume systems.
  • Database Support: Not all databases natively support materialized views. Alternative approaches may be necessary depending on your chosen database.
  • Increased Storage Usage: Materialized views consume additional storage space because they store pre-computed query results. For large datasets, this can lead to increased storage requirements.

Account Balance as a Projection

Another approach is to model account transactions using an event-sourced system. In this approach, transactions are recorded as immutable events — representing actions that have taken place in the past — in an append-only store. This immutability is based on the principle that history cannot be changed, meaning that events cannot be modified or deleted. This approach closely aligns with the nature of financial records, where each transaction directly impacts the current state of an account.

In this system, an account's balance is treated as a derived state, which can be reconstructed by replaying the chronological sequence of transactions associated with that account. To calculate the current balance, events are replayed from the start, applying each transaction to update the balance. This is achieved using projections, which are derived data structures built by processing and aggregating event streams from an event store. Let’s explore how to implement this using (EventStoreDB)https://www.eventstore.com.

First, we need to create the necessary events to be persisted in the store:

class AccountCreated {
    constructor(name, currency) {
        this.type = 'AccountCreated';
        this.data = { name, currency };
    }
}

class AccountCredited {
    constructor(amount, currency) {
        this.type = 'AccountCredited';
        this.data = { amount, currency };
    }
}

class AccountDebited {
    constructor(amount, currency) {
        this.type = 'AccountDebited';
        this.data = { amount, currency };
    }
}

Next, we define handler functions to create these events and persist them to the store:

async function handleAccountCreated(accountId, name, currency) {
    const event = new AccountCreated(name, currency);
    await client.appendToStream(accountId, event);
}

async function handleAccountCredited(accountId, amount, currency) {
    const event = new AccountCredited(amount, currency);
    await client.appendToStream(accountId, event);
}

async function handleAccountDebited(accountId, amount, currency) {
    const event = new AccountDebited(amount, currency);
    await client.appendToStream(accountId, event);
}

Finally, we create a projection to calculate the account's balance by replaying the events for that account:

async function getAccountBalance(accountId) {
    const events = client.readStream(accountId);

    let state = {
            balance: 0,
            totalCredit: 0,
            totalDebit: 0,
            currency: null
        };

    for await (const resolvedEvent of events) {
        const event = resolvedEvent.event;
        
        switch (event.type) {
            case 'AccountCreated':
                state.currency = event.data.currency;
                break;
            case 'AccountCredited':
                state.balance += event.data.amount;
                state.totalCredit += event.data.amount;
                break;
            case 'AccountDebited':
                state.balance -= event.data.amount;
                state.totalDebit += event.data.amount;
                break;
        }
    }

    return state;
}

Creating an account is straightforward:

 // Import uuid for generating unique IDs
const { v4: uuid } = require('uuid');

const accountId = uuid();

await handleAccountCreated(accountId, 'Arya Stark', 'GOT');

And recording a transaction can be done like this:

const account = await getAccountBalance(accountId);

// this guard clause could be refactored into the debit handler
if (account.balance < amount) {
    throw Error('Insufficient account balance');
}

await handleAccountDebited(accountId, 200, 'GOT');

Using projections offers several benefits, including auditability, performance, and scalability. However, event sourcing introduces challenges to the development process such as:

  • Complexity: Understanding event sourcing and its approach to building applications can be a steep learning curve for many developers. It also requires additional code to maintain projections.
  • Latency: In some cases, projections may not be immediately up-to-date, leading to discrepancies between the actual balance and the projected balance.
  • Storage and maintenance: Event stores are usually the source of truth for event-sourced applications. However, in some applications, the event store is combined with a compatible database (MongoDB, PostgresSQL etc) to provide easy views of the event store's data. All of these increase database storage and maintenance costs.
  • Error handling: Since projections are derived from events, errors in event handling can lead to incorrect balances and other data consistency issues. The event store remains accurate but misinterpretations of the data can cause problems.

Implementing account balances in financial applications is a critical task that requires careful consideration of various factors such as the scale of your application, performance requirements, regulatory compliance needs, and your team's expertise. It's also important to consider not just the ease of implementation, but also long-term maintainability, scalability, and data integrity.

Whether you choose to store balances directly in a database column, leverage materialized views, or adopt an event-sourced model with projections, each approach comes with its own set of challenges and benefits. Whichever method you choose, ensure that it can handle concurrent transactions, prevent data inconsistencies, and provide accurate, up-to-date balance information at all times.

Remember, the robustness of your account balance system is fundamental to the trust and reliability of your financial application.

As financial technology continues to evolve, staying informed about best practices and emerging patterns in handling account balances will be key to building resilient and efficient financial systems. What approach have you used in your projects, and what challenges did you face? Share your experiences with me on X or LinkedIn and let's continue this conversation.