Trade database (``trade.db``)
=============================

The log of trades (the blotter's source) and the end-of-day P&L computed from
it. Models: ``src/trade_engine/models/trade.py``; data access:
``repositories/trade.py``; booking: ``services/trades.py``.

The **blotter is not a table.** It is a reporting query over ``trades``, joined
with reference data at the application layer (see
:doc:`/reporting`).

``trades``: one row per booked trade
------------------------------------

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

   * - Column
     - Type
     - Reason
   * - ``trade_id``
     - integer PK
     - Stable trade identifier; also the final tie-breaker when replaying trades in order.
   * - ``symbol``
     - string(32), indexed
     - Business key to reference and market data.
   * - ``trade_date``
     - date, indexed
     - Determines which EOD runs include the trade (``trade_date <= calc_date``); indexed for date-bounded reads and the blotter's recent-first listing.
   * - ``value_date``
     - date, nullable
     - Settlement date; validated to be on or after ``trade_date``.
   * - ``side``
     - string(8)
     - ``BUY`` or ``SELL``, validated in the application.
   * - ``quantity``, ``price``
     - float
     - Positive values; ``side`` carries direction, which avoids sign ambiguity. Units follow the instrument (see :doc:`/database/multi_asset`).
   * - ``ccy``
     - string(8), default ``USD``
     - Currency of ``price``; must equal the instrument's ``quote_ccy``. Booking fills it from the instrument when omitted.
   * - ``book``
     - string(64), default ``MAIN``
     - Business key validated against reference data at booking.
   * - ``trader``
     - string(128), default ``SYSTEM``
     - Business key validated against reference data at booking.
   * - ``status``
     - string(16), default ``ACTIVE``
     - ``CANCELLED`` trades are excluded from P&L without deleting the record. Nothing in a trade log is physically deleted.
   * - ``entry_timestamp``
     - datetime, database default
     - When it was booked (as opposed to when it traded); part of the replay ordering.
   * - ``notes``
     - string(512), nullable
     - Free text; also used to tag seeded sample trades.

**Replay order** for P&L is ``(symbol, book, trade_date, entry_timestamp, trade_id)``,
which is deterministic even when several trades share a date.

Booking rules
~~~~~~~~~~~~~

``NewTrade`` (``domain/trade.py``) rejects, before any database access: blank
symbol, book or trader; non-finite or non-positive quantity or price;
``value_date`` earlier than ``trade_date``; notes over 512 characters.
``TradeService.book_trade`` then checks the instrument is known and active, the book
and trader exist, and ``ccy`` matches the instrument's quote currency. New trades
are always ``ACTIVE``; the API does not accept a status.

``pnl_eod``: persisted per-position P&L for a calculation date
--------------------------------------------------------------

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

   * - Column
     - Type
     - Reason
   * - ``id``
     - integer PK
     - Surrogate key.
   * - ``calc_date``, ``symbol``, ``book``
     - date, string, string; all indexed
     - The grain: one row per ``(calc_date, symbol, book)``. Indexed for the by-date reads and replace-by-date deletes the batch and UI use.
   * - ``ccy``
     - string(8), not null
     - Currency of every amount in the row (the instrument's ``quote_ccy``).
   * - ``net_quantity``
     - float
     - Signed position at ``calc_date``.
   * - ``avg_price``
     - float
     - Weighted-average cost of the open position (0 when flat).
   * - ``mark_price``
     - float
     - The close used, stored so the figure is explainable without re-querying market data, which may later be corrected.
   * - ``realized_pnl``, ``unrealized_pnl``, ``total_pnl``
     - float
     - Stored separately so reports can show each without recomputation; ``total_pnl`` is their sum. Include the instrument's ``price_multiplier``.
   * - ``created_at``
     - datetime, database default
     - When this result was produced.

``realized_pnl`` and ``total_pnl`` are **cumulative since the first trade**, not the
day's change. See :doc:`/pnl`.

Reruns replace all rows for the ``calc_date`` (delete then insert in one
transaction). A unique key on ``(calc_date, symbol, book)`` is on the
:doc:`roadmap </database/hardening_roadmap>` to make that guarantee a database fact.

Planned changes
---------------

Append-only trade events (amendments and cancels as new linked rows with who,
when and why), fees, cash flows and an ``eod_runs`` ledger. See the
:doc:`roadmap </database/hardening_roadmap>` and :doc:`/database/multi_asset`.