# Fintech Data Model Design for Multi-Currency Wallets

> How to design a fintech data model for multi-currency wallets — accounts, balances, FX handling, and the schema decisions that hold up under regulatory scrutiny.

- **Published:** 2025-02-21
- **Author:** Clixo
- **Reading time:** 5 min read
- **Tags:** fintech, data model, multi-currency, wallets, product engineering
- **Canonical URL:** https://clixo.sh/blog/fintech-data-model-design-for-multi-currency-wallets

Multi-currency wallet support is one of those features that sounds like a configuration option and turns out to be a schema redesign. Teams that add currency support to an existing single-currency product encounter the same set of problems in the same order: broken balance queries, incorrect FX fee calculations, and a reconciliation process that cannot determine what a historical balance was when expressed in a different currency.

Designing for multi-currency from the start avoids most of this. If you are building a product that will support more than one currency — even eventually — this is the data model to get right early.

## The Core Principle: Currency Lives on Every Amount

Every table that stores a monetary amount must also store the currency. An `amount` column without a `currency` column is a single-currency assumption baked into the schema. Adding currency later means migrating every row in every financial table and verifying that every piece of code that reads amounts handles the new column correctly.

The practical starting point:

```
amount        BIGINT NOT NULL       -- store in minor units (cents, pence)
currency_code CHAR(3) NOT NULL      -- ISO 4217 code: USD, EUR, GBP
```

Use integer amounts in the minor unit for the currency (cents for USD, pence for GBP). Floating-point arithmetic on monetary amounts produces rounding errors that compound. Store integers, convert to decimal only for display.

## Account Design for Multi-Currency Wallets

In a single-currency system, a user has one account with one balance. In a multi-currency system, a user has one account per currency. The account itself carries the currency; all ledger entries on an account are in that currency.

**Accounts table** should include a `currency_code` column. A user holding USD, EUR, and GBP has three accounts. Account creation is triggered when a user first receives or holds funds in a new currency.

This design means that balance queries are always single-currency — you never need to sum across currencies, because each account is implicitly in one currency. Cross-currency reporting is an aggregation operation, not a balance query.

```mermaid
erDiagram
  USER ||--o{ ACCOUNT : has
  ACCOUNT {
    string currency_code
    string status
  }
  ACCOUNT ||--o{ LEDGER_ENTRY : contains
  LEDGER_ENTRY {
    bigint amount
    string currency_code
    string direction
    timestamp created_at
  }
  LEDGER_ENTRY }o--o| FX_TRANSACTION : "linked by"
  FX_TRANSACTION {
    string source_currency
    string dest_currency
    decimal rate
    string rate_source
  }
```

## Fintech Data Model Design: Handling FX Conversions

An FX conversion in a double-entry ledger is a transaction that involves two entries in two different accounts: a debit in the source currency account and a credit in the destination currency account.

Because the debit and credit are in different currencies, the standard sum-to-zero invariant does not hold across them in nominal terms. You have two options:

### Option A: Record the FX transaction as two linked single-currency transactions

Create one transaction debiting the user's USD account and a separate transaction crediting the user's EUR account. Link them with a shared `fx_transaction_id` foreign key. Each transaction independently satisfies the sum-to-zero invariant in its own currency.

### Option B: Use a platform FX account as an intermediary

A USD-denominated "FX clearing" account receives the source currency. An EUR-denominated "FX clearing" account funds the destination currency. The exchange rate is recorded on a metadata table linked to the transaction pair. This preserves the double-entry structure and makes the implicit FX gain or loss of the platform explicit and auditable.

Option B is more complex but produces a correct accounting picture of the platform's FX position and margin. If you charge a spread on FX, you need to see that margin as a revenue line — Option B gives you that. Option A hides it.

## Storing Exchange Rates

Exchange rates must be stored with each transaction, not computed at query time. A transaction settled on a given date used the rate available at that time. If you recompute the rate today, you get a different number, which means your historical records are wrong.

Store the exchange rate on the FX transaction metadata: source currency, destination currency, rate (as a decimal with sufficient precision), rate source, and the timestamp at which the rate was obtained. This record is immutable.

## Rounding and FX Precision

FX rates have many decimal places. The converted amount may not divide cleanly into the minor unit of the destination currency. You need a documented rounding convention (round half-up, round half-even) applied consistently everywhere FX conversion happens.

The rounding convention also determines who absorbs rounding differences — the user or the platform. In most implementations, the platform absorbs rounding in a designated rounding account. That account accumulates small balances over time, which are reconciled and recognized as revenue or expense on a schedule.

## Regulatory Reporting Across Currencies

Regulators typically require financial reports in a base currency (usually the jurisdiction's local currency). Your reporting layer needs to translate multi-currency balances and transaction volumes into the reporting currency using the appropriate rate — end-of-period rate for balance sheet items, average rate for income statement items, in most accounting standards.

This translation happens at the reporting layer, not in the ledger. The ledger stores amounts in their original currency. The reporting layer applies the appropriate rate for each period. Keeping these concerns separate means your ledger is always accurate in original terms, and your reports are always accurate in the reporting currency.

Multi-currency wallet design is one of the fintech data model decisions with the least tolerance for shortcuts — errors compound across every transaction and become very visible in reconciliation and reporting. If you are designing a multi-currency product and want a review of your data model, [Clixo works with fintech engineering teams](https://clixo.sh/#contact) to get this right before it hits production.

---

Clixo · 1141 W Bryn Mawr Ave, Itasca, IL 60143, US · [hello@clixo.sh](mailto:hello@clixo.sh)
[Start a build](https://clixo.sh/#contact) · [All services](https://clixo.sh/services) · [Agent guide (llms.txt)](https://clixo.sh/llms.txt)