Reference database (reference.db)

Slow-changing master data: what can be traded, in which books, by whom. It is small, critical and rarely written, so it is kept apart from the fast-growing market and result tables. Models: src/trade_engine/models/reference.py; data access: repositories/reference.py.

Instruments follow a neutral-core-plus-subtype design; see Multi-asset design for how it extends to other asset classes.

instruments: neutral core

One row per tradable instrument, whatever its asset class.

Column	Type	Reason
instrument_id	integer PK	Surrogate key; target of subtype foreign keys.
symbol	string(32), unique, indexed	Unique internal code used by every other database. Unique so a symbol resolves to exactly one instrument; indexed for the per-trade validation lookup.
asset_class	string(16), not null, indexed, CHECK in (FX, CRYPTO, BOND, EQUITY)	Selects the subtype table and any class-specific behaviour. The database rejects unknown classes; indexed for class-level reporting.
quote_ccy	string(8), not null	Currency (or quote asset) of prices, trades and P&L. A trade’s ccy must equal it.
price_multiplier	float, not null, default 1.0, CHECK > 0	Normalises quoting conventions (e.g. 0.01 for bonds quoted in percent of par). Used by P&L and notional.
description	string(256), nullable	Human-readable label shown on the blotter.
active	boolean, default true	Soft retirement: history stays valid, new trades are rejected.

fx_instruments: FX subtype

One row per instrument whose asset_class is FX.

Column	Type	Reason
instrument_id	integer PK and FK to instruments	One-to-one with the core row; same database, so the foreign key is enforceable.
base_ccy	string(8), not null	The currency being bought or sold; with quote_ccy it defines the pair.
pip_size	float, default 0.0001	Quote precision; per-instrument because it differs (e.g. JPY pairs).
lot_size	float, default 100000	Standard trade size.

pip_size and lot_size should be effective-dated if they can change (see the roadmap).

books

Column	Type	Reason
book_id	integer PK	Surrogate key.
name	string(64), unique	Books are the unit of position, P&L and limits, so the name is a stable, unique code.
description	string(256), nullable	Free text.

traders

Column	Type	Reason
trader_id	integer PK	Surrogate key.
name	string(128), unique	Unique so a booked trader can be validated and attributed unambiguously.

How other code uses it

• Booking: TradeService.book_trade checks that the instrument exists and is active, that the book and trader exist, and that the trade’s ccy matches the instrument’s quote_ccy. This is the application-layer replacement for the foreign keys that cannot cross databases.

• P&L and risk: both look up quote_ccy and price_multiplier per symbol and fail with a clear error if an instrument is unknown.

• Blotter: joins instrument description and asset_class onto trades (see Reporting).

Seed data (scripts/init_db.py)

services/seeding.py creates, idempotently, the EURUSD instrument (class FX, quote USD, multiplier 1.0) with its fx_instruments row (base EUR, pip 0.0001, lot 100,000), the book MAIN and the trader SYSTEM.

Invariants

• symbol, books.name and traders.name are immutable codes. Retire with active = false (instruments) rather than renaming or deleting.

• Every fx_instruments row has a matching instruments row with asset_class = 'FX'. The foreign key guarantees the first half; the second is enforced by the seeding code and is a candidate for a database check.