Stop Updating Crypto Exchange Balances Like a Normal CRUD Field

In most web apps, “balance” is just another column.

You take an amount, you UPDATE the users SET balance = balance + x, and you move on.

If you try that on a crypto exchange or trading platform, you eventually “lose” money — not because the database is wrong, but because your model is.

In this post I’ll walk through how I now think about balances: as a ledger subsystem, not a normal CRUD field.

Most bugs I’ve seen in exchange projects can be traced back to one mistake:

Balances are treated as a cached number, not a consequence of recorded events.

Let’s fix that.

1. Why a simple balance column is not enough

A naive approach looks like this:

UPDATE users SET balance_btc = balance_btc + 0.1
WHERE id = 123;

You do this for:

• deposits
• withdrawals
• trades
• fees

It “works” until:

• two updates race each other -You need to reconcile with the blockchain or bank account
• a user disputes a transaction and you can’t explain their history

In a financial system, the question is never just “what is the balance?”
It’s always “how did we get to this balance?”

For that, you need a ledger.

2. Minimal ledger model for an exchange

You don’t need a monster design to start. A simple pattern:

CREATE TABLE accounts (
  id        BIGSERIAL PRIMARY KEY,
  user_id   BIGINT NOT NULL,
  asset     VARCHAR(32) NOT NULL,  -- BTC, USDT, INR, etc.
  UNIQUE (user_id, asset)
);

CREATE TABLE ledger_entries (
  id          BIGSERIAL PRIMARY KEY,
  account_id  BIGINT NOT NULL REFERENCES accounts(id),
  amount      NUMERIC(36, 18) NOT NULL, -- positive or negative
  entry_type  VARCHAR(32) NOT NULL,     -- deposit, trade_fill, fee, withdrawal...
  ref_type    VARCHAR(32) NOT NULL,     -- order, tx, ticket...
  ref_id      BIGINT NOT NULL,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

Then, balances become:
SELECT SUM(amount) as balance
FROM ledger_entries
WHERE account_id = :account_id;

You can materialize this into a balances table for performance, but the source of truth is the entries, not the derived number.

3. Define operations, not ad-hoc updates
Instead of letting each service mutate balances directly, define a tiny API for the ledger:

• credit(account, amount, entry_type, ref)
• debit(account, amount, entry_type, ref)
• transfer(account_from, account_to, amount, entry_type, ref)

In code (pseudo-TS):

async function credit(accountId, amount, type, ref) {
  await db.tx(async (t) => {
    await t.insert('ledger_entries', {
      account_id: accountId,
      amount,
      entry_type: type,
      ref_type: ref.type,
      ref_id: ref.id,
    });

    await t.none(
      `UPDATE balances
       SET amount = amount + $1
       WHERE account_id = $2`,
      [amount, accountId],
    );
  });
}

Every path that changes money goes through one of these functions.
Trading, deposits, withdrawals, bonuses, fees – all of them.

Now you can:

• explain every satoshi with a row
• rebuild balances if the cache is wrong
• run audits and sanity checks

4. Common mistakes that kill you later

a) Mixing wallet logic and ledger logic
Blockchains already have a notion of balance. So do banks. It’s tempting to tie your internal ledger directly to wallet events.

Don’t.

Your wallet system should produce events (“tx seen”, “tx confirmed”), and your ledger should decide what to do with them:

• create a deposit entry
• update a pending status
• maybe trigger extra checks for large amounts

Treat the ledger as your version of reality, not just a copy of external balances.

b) Using floats or imprecise types
If you store balances as DOUBLE or float, you’re asking for rounding issues.

Always:

• store as integers of the smallest unit (satoshis, wei, cents), or
• use high-precision decimal types like NUMERIC(36, 18).

Then expose nice formatting at the edges of your app. Inside, keep it boring and precise.

c) Having multiple sources of truth
I’ve seen systems where:

• users.balance_btc
• trading_accounts.balance_btc
• wallets.available_btc

…all exist at once, and none of them quite match.

If you need different views (e.g. available vs locked), derive them from the ledger with filters:
SELECT
SUM(CASE WHEN kind = 'locked' THEN amount ELSE 0 END) AS locked,
SUM(amount) AS total,
SUM(amount) - SUM(CASE WHEN kind = 'locked' THEN amount ELSE 0 END) AS available
FROM ledger_entries
WHERE account_id = :account_id;

One ledger, multiple interpretations. Not three half-broken ledgers.

5. Reconciling with the outside world
Once you have a ledger, reconciliation becomes a process, not a panic.

For each asset:

• Compute internal balances per user + total per asset.
• Compare total per asset with on-chain wallet balances or bank balances.
• Investigate any difference: missing entries? wrong amount? pending tx not handled yet?

You can even automate checks like:
SELECT asset, SUM(balance_internal) - sum(balance_external) AS diff
FROM ...
WHERE ABS(diff) > :threshold;

and alert when something drifts.

The important bit: you can reconcile, because you have a complete history.

6. What this changes for you as a backend dev

A ledger adds some work up front, but it pays off by:

• making weird bugs debuggable (“why is this user at −0.0001 BTC?”)
• keeping support and compliance people out of raw SQL
• giving you the confidence to change wallets, chains, or business rules later

If you’re working on anything that holds user funds – even a small exchange or internal trading tool – treating balances like a proper ledger is one of the highest-leverage decisions you can make.

If you’re building exchange-style systems and still relying on ad-hoc UPDATE balance statements, start by sketching a minimal ledger like this and routing one flow (deposits or trades) through it. You’ll immediately see which parts of your current design are assumptions, not guarantees — and that’s usually where the future bugs are hiding.