Schema hardening roadmap
========================

The current schema is correct for the v1 workflow but relies on Python for
guarantees that a production database should also enforce. These changes are
required before the system is relied on for real positions, and should be
delivered as Alembic migrations.

   Until Alembic exists, schema changes are not applied to existing database files:
   delete the old ``db/*.db`` files and run ``scripts/init_db.py`` again.

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

   * - #
     - Change
     - Reason
   * - 1
     - ``Float`` to ``Numeric(p, s)`` for prices, quantities, P&L and notionals (e.g. prices ``NUMERIC(19,8)``, quantities and amounts ``NUMERIC(24,8)`` so crypto fits; final scales fixed per column), with ``Decimal`` in ``domain/``
     - Exact decimal arithmetic and explicit rounding; floats cannot represent most decimal prices exactly.
   * - 2
     - Remaining ``CHECK`` constraints: ``quantity > 0``, ``price > 0``, ``side IN (...)``, ``status IN (...)``, ``breach_type IN (...)``, ``high >= low``, OHLC bounds, ``value_date >= trade_date``. *Done:* ``instruments.asset_class``, ``instruments.price_multiplier > 0``, ``prices_eod.data_origin``
     - Invariants hold even if a writer bypasses the service layer.
   * - 3
     - Unique ``(calc_date, symbol, book)`` on ``pnl_eod`` and ``risk_metrics_eod``; unique ``(symbol, book)`` on ``position_limits``
     - Makes the idempotent-replace guarantee a database fact; prevents duplicate or ambiguous limits.
   * - 4
     - Foreign key ``limit_breaches.limit_id`` to ``position_limits.limit_id`` (same database)
     - Within one database, foreign keys are enforceable and should be.
   * - 5
     - Timezone-aware UTC timestamps (``TIMESTAMP WITH TIME ZONE``)
     - Naive timestamps are ambiguous across hosts and daylight-saving changes.
   * - 6
     - ``BIGINT`` primary keys on growth tables (``trades``, ``prices_eod``, ``pnl_eod``, ``risk_metrics_eod``, ``limit_breaches``)
     - Avoid 32-bit exhaustion at scale.
   * - 7
     - Append-only trade events: amendments and cancels as new rows linked to the original trade, with ``created_by`` and reason; optimistic-locking ``version`` where rows are still updated
     - Full audit trail of who changed what and when; no destructive updates.
   * - 8
     - ``eod_runs`` ledger (run id, ``calc_date``, status, started and finished times, input fingerprint such as latest trade id and price-set version); results reference their ``run_id``; an advisory lock prevents concurrent runs
     - Detects the partial-failure case across databases, makes reruns visible, and lets any past figure be reproduced.
   * - 9
     - Price corrections as new versions (``version``, ``superseded_at``) instead of skip-if-exists; drop the redundant single-column ``symbol`` index on ``prices_eod`` (covered by the unique ``(symbol, px_date)``)
     - Vendor corrections must be traceable, and real data must be able to supersede simulated data; fewer indexes on a write-heavy table.
   * - 10
     - Composite trade-replay index ``(symbol, book, trade_date, entry_timestamp, trade_id)``
     - Serves the exact ordering the P&L replay uses.
   * - 11
     - Effective-dating for instrument attributes that can change (``pip_size``, ``lot_size``, coupon schedules)
     - Historical calculations must use the attributes valid at that time.
   * - 12
     - Alembic baseline of the current schema, then all changes above as migrations
     - Repeatable, reviewable, reversible schema change; replaces ``create_all`` outside tests.
   * - 13
     - Propagate ``data_origin`` to ``pnl_eod`` and ``risk_metrics_eod`` (a result is ``SIMULATED`` if any input price is)
     - A P&L figure derived from simulated prices must not be readable as real.
   * - 14
     - The multi-asset tables listed in :doc:`/database/multi_asset`: ``crypto_instruments``, ``bond_instruments``, ``equity_instruments``, ``instrument_identifiers``, ``trade_fees``, ``cashflows``, ``corporate_actions``, ``trading_calendars``
     - Added when the first instrument of each class is introduced, not before.
   * - 15
     - Price basis: ``prices_eod.price_basis`` (``BID``, ``ASK``, ``MID``, ``LAST``) with unique ``(symbol, px_date, price_basis)``, required loader argument, bid/ask consistency checks, ``pnl_eod.mark_basis``, and a basis argument on the price queries (see :doc:`/database/market`)
     - A mark or return series with an unstated basis can be off by half the spread, and mixing bases corrupts VaR. Bid, ask and mid serve different purposes (execution, liquidation value, reference valuation).