FX Rate Import

Overview

The FX system supports multiple exchange rate providers to pre-populate the database with historical rates. This allows ticket imports to use cached rates instead of making live API calls.

Providers

FRED (Recommended for USD)

The Federal Reserve Economic Data (FRED) API provides free historical exchange rates from 2000+.

Setup

1. Get a free API key: https://fred.stlouisfed.org/docs/api/api_key.html
2. Add to your .env:

   FRED_API_KEY=your_api_key_here

Bulk Import

# Import rates from 2020 to today (default)
bun run packages/api/src/scripts/fx-import.ts

# Custom date range
FX_START_DATE=2022-01-01 FX_END_DATE=2024-12-31 bun run packages/api/src/scripts/fx-import.ts

Supported Currencies

FRED provides USD-based rates for major currencies:

USD (base), EUR, GBP, JPY, CHF, AUD, CAD, SEK, NOK, DKK,
NZD, ZAR, BRL, CNY, HKD, INR, KRW, MXN, MYR, SGD, THB, TWD

Daily Updates (Cron)

To keep rates fresh, run the daily job:

# Manually
bun run apps/server/src/jobs/fx-daily.ts

# Via cron (6 AM ET daily, after FRED updates at ~5 PM ET previous day)
0 11 * * * FRED_API_KEY=xxx bun run /path/to/apps/server/src/jobs/fx-daily.ts

ECB (European Central Bank)

The ECB API provides free historical rates with EUR as the base currency.

Usage

# From packages/api directory
bun run fx:import --start 2020-01-01

# With options
bun run fx:import --start 2020-01-01 --end 2024-12-31 --currencies USD,GBP,CHF

Options

Supported Currencies

ECB publishes daily rates for 32 currencies against EUR:

EUR (base), USD, GBP, JPY, CHF, AUD, CAD, SEK, NOK, DKK,
PLN, CZK, HUF, RON, BGN, HRK, ISK, TRY, NZD, ZAR,
BRL, CNY, HKD, IDR, ILS, INR, KRW, MXN, MYR, PHP, SGD, THB

OpenExchangeRates (Fallback)

OpenExchangeRates is a paid service that provides 170+ currencies including exotic ones not available from FRED or ECB.

Setup

1. Sign up at https://openexchangerates.org/signup
2. Add to your .env:

   OXR_APP_ID=your_app_id_here

When It's Used

OXR is automatically used as a fallback when:

• The requested currency pair isn't supported by FRED or ECB
• Historical rates aren't cached in the database

Pricing

• Free tier: 1,000 requests/month, hourly updates
• Developer: $12/month, 10,000 requests, hourly updates
• Enterprise: Higher limits, minute-level updates

How the FX Service Works

1. Cache Check: First looks for cached rate in FxRate table
2. Provider Selection: Chooses best provider based on currency support:
   • ECB for EUR-based pairs
   • FRED for USD-based pairs (when configured)
   • OXR as fallback for exotic currencies
3. Cross-Rate Calculation: Computes rates between any two currencies:

   GBP → JPY = (JPY/USD) / (GBP/USD)

4. Cache Storage: Stores fetched rates for future use

Import Optimization

During ticket imports, the system:

1. Collects all unique currency/date pairs needed
2. Batch-fetches cached rates from DB in one query
3. Uses in-memory cache during row processing
4. Only calls external APIs for cache misses

You'll see logs like:

[import] FX cache: 176/176 rates found in DB

Environment Variables

Notes

• ECB and FRED don't publish rates on weekends/holidays
• All imports are idempotent (safe to re-run)
• Rates are stored with isIntraday: false (end-of-day rates)
• For best coverage, configure both FRED and OXR