Most applications that convert currency store something like this:

amount_eur NUMERIC, fx_rate NUMERIC

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 );
  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"])

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