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
:doc:`/database/multi_asset` for how it extends to other asset classes.

``instruments``: neutral core
-----------------------------

One row per tradable instrument, whatever its asset class.

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

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

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

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

``books``
---------

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

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

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

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