DEV Community

chathuranga basnayaka
chathuranga basnayaka

Posted on

Five Fields Every Stored Exchange Rate Needs (or Your Auditor Will Ask)

Most applications that convert currency store something like this:

amount_eur NUMERIC, fx_rate NUMERIC
Enter fullscreen mode Exit fullscreen mode

A year later, someone asks where did 0.9215 come from? and nobody can answer. Here is a small schema that avoids that.

The five fields

CREATE TABLE fx_applied (
  id            BIGSERIAL PRIMARY KEY,
  rate          NUMERIC(20,10) NOT NULL,  -- full precision, never rounded for storage
  base_ccy      CHAR(3) NOT NULL,
  quote_ccy     CHAR(3) NOT NULL,
  rate_kind     TEXT NOT NULL,            -- 'mid-market' | 'official' | 'bank-board'
  source        TEXT NOT NULL,            -- e.g. 'ecb', 'hmrc', 'lk/ndb', 'live'
  rate_date     DATE,                     -- the date an official rate applies to
  observed_at   TIMESTAMPTZ NOT NULL      -- when your system fetched it
);
Enter fullscreen mode Exit fullscreen mode
  1. Full precision. Round for display, never for storage.
  2. Kind. A mid-market rate, an official rate and a bank's buy price are different things. Say which one you stored.
  3. Source. "Official" is not enough. Record which institution's rate it was.
  4. Rate date. Official rates apply to a date, not a moment. On weekends and holidays, the rate in force is the last business day's, which is what compliance rules expect.
  5. Observed at. When you fetched it, so you can reproduce what your system knew.

Filling it from an API

These fields map directly onto AllRatesToday's responses, because every layer carries its own metadata:

import requests
from datetime import datetime, timezone

H = {"Authorization": "Bearer your_api_key"}

# Official
o = requests.get("https://allratestoday.com/api/v1/central-bank/ecb/latest",
                 params={"source": "USD", "target": "EUR"}, headers=H).json()
row = dict(rate=o["rate"], base_ccy="USD", quote_ccy="EUR", rate_kind="official",
           source=o["bank"], rate_date=o["rate_date"],
           observed_at=datetime.now(timezone.utc))

# Mid-market: one element per pair, with its own observation time
m = requests.get("https://allratestoday.com/api/v1/rates",
                 params={"source": "USD", "target": "EUR"}, headers=H).json()[0]
row2 = dict(rate=m["rate"], base_ccy="USD", quote_ccy="EUR", rate_kind="mid-market",
            source="live", rate_date=None, observed_at=m["time"])
Enter fullscreen mode Exit fullscreen mode

Bank boards add captured_at, the bank's own bank_updated_at, and a basis (transfer or cash), so a bank-board row is just as traceable.

Why the kind column matters

The most common audit finding in FX handling is a mismatch: a mid-market figure booked where the policy says "official", or a central bank rate quoted to a customer who received a bank's rate. If all three layers come from one API with consistent fields, mixing them up is hard to do by accident, and easy to spot when it happens.

Docs: allratestoday.com/docs ยท Free key: allratestoday.com/register

Top comments (0)