Risk database (risk.db)

Position limits and the end-of-day risk results measured against them. Models: src/trade_engine/models/risk.py; data access: repositories/risk.py; calculation: services/risk.py. How the numbers are calculated is described in Risk calculations.

position_limits

Column	Type	Reason
limit_id	integer PK	Referenced by breaches.
symbol, book	string, both indexed	A limit applies to one instrument in one book. Class-level limits (e.g. total equity notional) are a future extension.
max_net_quantity	float	Cap on absolute net position.
max_notional_usd	float	Cap on absolute USD notional. Quantity and USD value diverge as price moves, so there are two independent limits.
active	boolean, default true	Retire a limit without losing breach history that references it.

The seed (scripts/init_db.py) creates one limit: EURUSD in MAIN, 5,000,000 net quantity and 7,500,000 USD notional.

risk_metrics_eod: one row per (calc_date, symbol, book)

Column	Type	Reason
id	integer PK	Surrogate key.
calc_date, symbol, book	date, string, string; all indexed	The grain; same reasons as pnl_eod.
net_quantity	float	Copied from P&L so the risk record is a self-contained snapshot.
notional_usd	float	Signed USD notional (net_quantity * mark * price_multiplier).
var_95_1d	float	Historical 1-day 95% VaR in USD, as a positive number.
created_at	datetime, database default	When this result was produced.

Amounts are in USD, the reporting currency. Non-USD instruments are rejected until currency conversion exists.

limit_breaches: one row per exceeded limit per date

Column	Type	Reason
breach_id	integer PK	Surrogate key.
calc_date	date, indexed	Date of the run that detected it.
limit_id	integer	The limit that was exceeded. Not yet a foreign key although it is in the same database (roadmap item 4).
symbol	string(32), indexed	Business key.
observed_value, limit_value	float	Both are stored, not just a flag: limits change over time and the breach must remain explainable afterwards.
breach_type	string(32)	NET_QUANTITY or NOTIONAL_USD.
created_at	datetime, database default	When it was logged.

Re-run behaviour

A risk run for a calc_date deletes that date’s risk_metrics_eod and limit_breaches rows and inserts the new ones in one transaction, so it is safe to repeat. It reads positions from the persisted pnl_eod rows of the trade database, which is a separate commit; see Data architecture for the cross-database consistency trade-off and the planned eod_runs ledger.

Planned changes

Unique (calc_date, symbol, book) on metrics, unique (symbol, book) on limits, the limit_breaches.limit_id foreign key, and propagation of the simulated or observed label to results. See the roadmap.