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
:doc:`/risk`.

``position_limits``
-------------------

.. list-table::
   :header-rows: 1

   * - 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)``
---------------------------------------------------------------

.. list-table::
   :header-rows: 1

   * - 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
-------------------------------------------------------

.. list-table::
   :header-rows: 1

   * - 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 :doc:`/database/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 :doc:`roadmap </database/hardening_roadmap>`.