Currency Conversion Service Low-Level Design: Exchange Rate Ingestion, Caching, and Historical Rates

What Is a Currency Conversion Service?

A currency conversion service ingests exchange rates from multiple financial data providers, stores them with full history, computes cross-rates, and serves real-time and historical conversions to other systems. At low level it must handle provider staleness, cross-rate computation from a common base currency, locked rates for checkout flows, spread markup separation, and immutable daily snapshots for accounting compliance.

Rate Ingestion Architecture

Rates are ingested by a cron job running every 15 minutes from multiple providers:

European Central Bank (ECB): publishes daily EUR-base rates; free, authoritative for EUR pairs.
OpenExchangeRates: USD-base rates updated hourly; paid tier for real-time.
Fixer.io: EUR-base rates with real-time updates on paid tier.

Each ingestion run fetches all available currency pairs from each provider and inserts rows into ExchangeRate with provider attribution and fetch timestamp. The system stores raw provider rates rather than a merged rate, allowing provider-specific analysis and outlier detection.

Schema

CREATE TABLE Currency (
  code           CHAR(3) PRIMARY KEY,
  name           VARCHAR(64) NOT NULL,
  decimal_places INT NOT NULL DEFAULT 2
);

CREATE TABLE ExchangeRate (
  id          BIGSERIAL PRIMARY KEY,
  base        CHAR(3) NOT NULL,
  quote       CHAR(3) NOT NULL,
  rate        NUMERIC(20, 10) NOT NULL,
  provider    VARCHAR(32) NOT NULL,
  fetched_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_rate_lookup ON ExchangeRate (base, quote, fetched_at DESC);

CREATE TABLE LockedRate (
  id          BIGSERIAL PRIMARY KEY,
  base        CHAR(3) NOT NULL,
  quote       CHAR(3) NOT NULL,
  rate        NUMERIC(20, 10) NOT NULL,
  spread_rate NUMERIC(20, 10) NOT NULL,  -- display rate with markup
  locked_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  expires_at  TIMESTAMPTZ NOT NULL,
  order_id    BIGINT
);

CREATE TABLE DailyRateSnapshot (
  snapshot_date DATE NOT NULL,
  base          CHAR(3) NOT NULL,
  quote         CHAR(3) NOT NULL,
  rate          NUMERIC(20, 10) NOT NULL,
  PRIMARY KEY (snapshot_date, base, quote)
);

Multi-Provider Aggregation and Outlier Filtering

When multiple providers report rates for the same pair at similar times, the aggregated rate is computed as the median rather than the average. The median is robust against a single provider publishing a stale or erroneous rate. If provider rates diverge by more than 1%, an alert is fired for manual review — this can indicate a provider outage or a genuine market event.

from statistics import median

def aggregate_rates(rates: list[float]) -> float:
    """Compute median rate across provider reports for outlier resistance."""
    if not rates:
        raise ValueError("No rates available")
    return median(rates)

Cross-Rate Computation

All rates are stored with USD as the base currency. A cross-rate between two non-USD currencies is computed as:

cross_rate(A, B) = rate(USD, A) / rate(USD, B)

# Example: EUR/GBP = rate(USD/GBP) / rate(USD/EUR)
# If USD/EUR = 0.9200 and USD/GBP = 0.7800
# EUR/GBP = 0.7800 / 0.9200 = 0.8478

This means only N rates need to be stored (USD to each currency) rather than N^2 pairs. Cross-rates are computed on read rather than persisted, except for DailyRateSnapshot which materializes all pairs for reporting.

Python Implementation

import requests
from datetime import datetime, date, timedelta
from decimal import Decimal
from db import get_db

SPREAD_MARKUP = Decimal('0.015')  # 1.5% spread on display rates
LOCK_TTL_MINUTES = 15
STALENESS_THRESHOLD_MINUTES = 30

def fetch_rates(provider: str) -> list[dict]:
    """Fetch current rates from a provider. Returns list of {base, quote, rate} dicts."""
    if provider == 'openexchangerates':
        resp = requests.get(
            'https://openexchangerates.org/api/latest.json',
            params={'app_id': 'OXR_APP_ID', 'base': 'USD'},
            timeout=5
        )
        data = resp.json()
        return [
            {'base': 'USD', 'quote': code, 'rate': Decimal(str(r))}
            for code, r in data['rates'].items()
        ]
    raise ValueError(f"Unknown provider: {provider}")

def get_rate(base: str, quote: str, as_of: datetime | None = None) -> Decimal:
    """
    Get exchange rate. If as_of is None, returns latest rate.
    All rates stored as USD base; cross-rates computed on the fly.
    """
    db = get_db()

    if base == 'USD':
        row = _fetch_direct_rate(db, 'USD', quote, as_of)
        return Decimal(str(row['rate']))

    if quote == 'USD':
        row = _fetch_direct_rate(db, 'USD', base, as_of)
        return Decimal('1') / Decimal(str(row['rate']))

    # Cross-rate: USD/quote / USD/base
    usd_to_base  = get_rate('USD', base,  as_of)
    usd_to_quote = get_rate('USD', quote, as_of)
    return usd_to_quote / usd_to_base

def _fetch_direct_rate(db, base: str, quote: str, as_of: datetime | None) -> dict:
    if as_of:
        row = db.execute("""
            SELECT rate FROM ExchangeRate
            WHERE base = %s AND quote = %s AND fetched_at  STALENESS_THRESHOLD_MINUTES:
        # Log alert; return last known good rate anyway
        import logging
        logging.warning(f"Rate {base}/{quote} is {age_minutes:.0f} minutes old")

    return row

def lock_rate(base: str, quote: str, order_id: int, ttl_minutes: int = LOCK_TTL_MINUTES) -> dict:
    """Lock current rate for checkout. Returns locked rate record with expiry."""
    db = get_db()
    settlement_rate = get_rate(base, quote)
    display_rate    = settlement_rate * (1 + SPREAD_MARKUP)
    expires_at      = datetime.utcnow() + timedelta(minutes=ttl_minutes)

    row_id = db.execute("""
        INSERT INTO LockedRate (base, quote, rate, spread_rate, expires_at, order_id)
        VALUES (%s, %s, %s, %s, %s, %s) RETURNING id
    """, (base, quote, settlement_rate, display_rate, expires_at, order_id)).fetchone()[0]
    db.commit()

    return {
        'locked_rate_id': row_id,
        'base': base,
        'quote': quote,
        'settlement_rate': float(settlement_rate),
        'display_rate':    float(display_rate),
        'expires_at':      expires_at.isoformat()
    }

def convert(amount: Decimal, from_currency: str, to_currency: str) -> Decimal:
    """Convert amount using latest mid-market rate (no spread)."""
    rate = get_rate(from_currency, to_currency)
    return (amount * rate).quantize(Decimal('0.01'))

def take_daily_snapshot(snapshot_date: date):
    """Materialize all cross-rates for a given date into DailyRateSnapshot."""
    db = get_db()
    currencies = [r['code'] for r in db.execute("SELECT code FROM Currency").fetchall()]
    as_of = datetime.combine(snapshot_date, datetime.max.time())

    for base in currencies:
        for quote in currencies:
            if base == quote:
                continue
            try:
                rate = get_rate(base, quote, as_of=as_of)
                db.execute("""
                    INSERT INTO DailyRateSnapshot (snapshot_date, base, quote, rate)
                    VALUES (%s, %s, %s, %s)
                    ON CONFLICT (snapshot_date, base, quote) DO NOTHING
                """, (snapshot_date, base, quote, rate))
            except LookupError:
                pass
    db.commit()

Display Rate vs Settlement Rate

The rate shown to a user at checkout includes a spread markup (typically 1-2%) above the mid-market rate. This covers foreign exchange risk during the payment processing window. The settlement rate — the actual rate used for accounting — is the mid-market rate at the time of the transaction. The locked rate record stores both values with the expiry time. If the user does not complete checkout before expiry, a new lock is required at the current market rate.

{
“@context”: “https://schema.org”,
“@type”: “FAQPage”,
“mainEntity”: [
{
“@type”: “Question”,
“name”: “How is a cross-rate computed between two non-USD currencies?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “All rates are stored as USD-base pairs. A cross-rate between currency A and currency B is computed as rate(USD/B) divided by rate(USD/A). For example, EUR/GBP = rate(USD/GBP) / rate(USD/EUR). This approach requires storing only N rates instead of N-squared pairs while supporting any currency pair conversion.”
}
},
{
“@type”: “Question”,
“name”: “What happens when a locked rate expires before the user completes checkout?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “When the locked rate record passes its expires_at timestamp, the checkout flow must re-lock at the current rate. The UI shows a notification that the exchange rate has been refreshed and displays the new rate for confirmation before proceeding. The new lock is created with a fresh TTL. If the new rate has moved significantly (configurable threshold, e.g. 0.5%), an explicit user confirmation step is required before proceeding.”
}
},
{
“@type”: “Question”,
“name”: “How does the staleness fallback work when all providers are unavailable?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “If the latest rate for a pair is older than the staleness threshold (30 minutes), the system logs an alert and returns the last known good rate from the database rather than failing. For display purposes this is acceptable since exchange rates rarely move dramatically in 30 minutes. For settlement, a stale rate flag is attached to the transaction record, triggering a post-settlement reconciliation job that re-evaluates the rate once providers recover and logs any material difference for compliance review.”
}
},
{
“@type”: “Question”,
“name”: “How is the spread markup applied and why is it separate from the settlement rate?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “The spread markup is applied multiplicatively to the mid-market rate to produce the display rate shown at checkout. It is stored separately because accounting and settlement must use the actual mid-market rate for financial reporting, while revenue from the spread is tracked as foreign exchange income. Mixing display and settlement rates in one field would corrupt both P&L reporting and customer-facing rate transparency disclosures.”
}
}
]
}

{
“@context”: “https://schema.org”,
“@type”: “FAQPage”,
“mainEntity”: [
{
“@type”: “Question”,
“name”: “How are cross-rates computed from a USD base?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “If all rates are stored as USD/X, then rate(A to B) = rate(USD/B) / rate(USD/A); this avoids storing N^2 pairs and computes any cross-rate on demand from two lookups.”
}
},
{
“@type”: “Question”,
“name”: “How is rate staleness detected and handled?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “A background monitor checks that the latest ExchangeRate row for each pair is within the expected fetch interval; stale pairs trigger an alert and the system falls back to the last known good rate rather than returning an error.”
}
},
{
“@type”: “Question”,
“name”: “What is the difference between a display rate and a settlement rate?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “The display rate (with spread markup) is shown to the user at browse time; the settlement rate is locked at checkout with a short TTL (e.g., 15 minutes) to protect against rate movements during payment.”
}
},
{
“@type”: “Question”,
“name”: “How are historical rates stored for accounting?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “A daily snapshot job inserts one DailyRateSnapshot row per currency pair per day; these rows are immutable and serve as the authoritative rates for invoice reconciliation and financial reporting.”
}
}
]
}