Sooner or later, every app that uses an exchange rates API needs its own copy of the rates. Invoices must be reproducible next year. Reports must use the rate of the transaction date, not today's. And you do not want a page view to depend on a network call. This guide gives a schema that holds up: one table, the right keys, an as-of lookup, idempotent loading and a retention plan. The SQL is for PostgreSQL, with notes for MySQL and SQLite at the end.

The table

CREATE TABLE fx_rate ( base CHAR ( 3 ) NOT NULL , -- ISO 4217, e.g. 'USD' quote CHAR ( 3 ) NOT NULL , -- 1 base = rate quote rate NUMERIC ( 20 , 10 ) NOT NULL CHECK ( rate > 0 ) , rate_type TEXT NOT NULL CHECK ( rate_type IN ( 'mid' , 'official' ) ) , source TEXT NOT NULL , -- 'allratestoday', 'ecb', 'rbi', ...…