Market database (``market.db``) =============================== Daily price history for every instrument. It is the fastest-growing database (rows scale with symbols times days), which is why it is isolated and can be archived or partitioned independently. Models: ``src/trade_engine/models/market.py``; data access: ``repositories/market.py``; loading: ``services/market_data.py``. ``prices_eod``: one daily OHLC bar per symbol --------------------------------------------- .. list-table:: :header-rows: 1 * - Column - Type - Reason * - ``id`` - integer PK - Surrogate key. * - ``symbol`` - string(32), indexed - Business key (no foreign key to another database). * - ``px_date`` - date, indexed - Daily grain. A ``date``, not a timestamp: an EOD bar has no time of day. * - ``open``, ``high``, ``low``, ``close`` - float, not null - Validated before insert (see below). ``close`` is the mark for P&L and the input to VaR. Meaning follows the instrument's quoting convention (e.g. percent of par for bonds). **The price basis (bid, ask, mid or last) is not recorded yet**; see `Bid, ask and mid (planned)`_. * - ``source`` - string(128), not null - Free-form provenance detail: vendor, file or generator label, e.g. ``gbm-seed-42``. * - ``data_origin`` - string(16), not null, ``CHECK`` in (``OBSERVED``, ``SIMULATED``) - Whether the bar is real or generated. No default, so it is always a deliberate choice. * - ``loaded_at`` - datetime, database default - When the bar entered the system, for audit and correction analysis. **Constraint:** unique ``(symbol, px_date)`` (``uq_prices_eod_symbol_px_date``). This is the idempotency guarantee for reloads, and it makes "latest close at or before date D" a single index seek. It is also the look-ahead guard: queries never read beyond the calculation date. Loading rules ------------- ``MarketDataService.load_csv(symbol, csv_path, origin, source)`` stages the file in Redis (``stage_csv``) and then processes the queue (``process_staged``); the two steps can also run separately with ``scripts/load_market_data.py --stage-only`` and ``scripts/process_staging.py``. See :doc:`/database/architecture` (Data tiers). 1. **Provenance first.** ``origin`` is mandatory. A file under ``data/generated/`` can only be loaded as ``SIMULATED``. ``source`` must be 1 to 128 characters. This is checked when the file is staged. Rows are staged as the original strings, without price validation. 2. **Validate the whole batch** when it is processed (``domain/market_data.py``) before writing anything: - required columns ``date, open, high, low, close`` (names are case-insensitive); - rows for other symbols are filtered out when a ``symbol`` column exists, and a file with no rows for the requested symbol is an error; - dates and prices must parse, be finite, and prices must be positive; - duplicate dates are an error (never silently de-duplicated); - ``high >= max(open, close)`` and ``low <= min(open, close)``. 3. **Insert only new dates.** Dates already stored for the symbol are skipped, so reloading is safe. The result reports ``loaded`` and ``skipped`` counts. 4. **All or nothing.** The insert runs in one transaction; an invalid batch stores nothing and is moved to the rejected stream in Redis with the reason, so ``load_csv`` raises ``DataValidationError`` for it. .. **Caution:** because existing dates are skipped, loading ``OBSERVED`` data for a symbol and dates that already hold ``SIMULATED`` rows does **not** replace them. Use a different symbol or a fresh database until price versioning exists (see :doc:`/simulation` and the :doc:`roadmap `, item 9). Query patterns -------------- .. list-table:: :header-rows: 1 * - Repository method - Purpose * - ``latest_close(symbol, as_of)`` - Mark price: most recent close at or before ``as_of``. * - ``closes_through(symbol, as_of)`` - Chronological closes up to ``as_of``, the VaR history. * - ``latest_date()`` - Default EOD date (latest bar across all symbols). * - ``recent(limit)`` - Most recent bars across symbols, for the market view. Bid, ask and mid (planned) -------------------------- **Status: Required before relying on bid/ask-sensitive results; not implemented.** Today ``prices_eod`` holds one OHLC series with an unstated basis. Everything below describes the intended design (roadmap item 15). What each basis is for ~~~~~~~~~~~~~~~~~~~~~~ .. list-table:: :header-rows: 1 * - ``price_basis`` - Meaning - Used for * - ``BID`` - Price at which the market buys from you (you sell) - Sell fills; marking long positions at liquidation value * - ``ASK`` - Price at which the market sells to you (you buy) - Buy fills; marking short positions at liquidation value * - ``MID`` - ``(bid + ask) / 2``; not tradable - Reference valuation, unrealized P&L, VaR returns, limit notional, signals * - ``LAST`` - Last traded price (exchange-traded instruments with no quoted bid/ask bar) - Equities, futures and exchange crypto where only trades are available Schema change ~~~~~~~~~~~~~ Add one column and widen the unique key, so a symbol and date can hold one bar per basis (long format, not eight sparse ``bid_*``/``ask_*`` columns): .. list-table:: :header-rows: 1 * - Change - Reason * - ``prices_eod.price_basis`` string(8), not null, ``CHECK`` in (``BID``, ``ASK``, ``MID``, ``LAST``), no default - Same rule as ``data_origin``: the basis is always a deliberate choice, so two conventions can never be silently mixed. * - Unique ``(symbol, px_date, price_basis)`` replaces unique ``(symbol, px_date)`` - One bar per basis per day; reloading a basis stays idempotent. * - Existing rows are backfilled as ``MID`` (the simulated series is a mid path), as an Alembic migration - Existing data keeps its meaning and no history is lost. A long format keeps vendors that supply only one basis (for example bid-only retail history) representable, and adding a basis later needs no new columns. One query reads several bases with a join or a ``GROUP BY`` on ``px_date``; the DuckDB analytics export keeps ``price_basis`` so it can be pivoted there. Loading ~~~~~~~ - ``--price-basis BID|ASK|MID|LAST`` is required, exactly like ``--origin``, and a ``price_basis`` field is staged with each batch. One file carries one basis. - A vendor that supplies both sides is loaded as two files (a bid file and an ask file) with the same ``source``. - **Deriving mid:** when ``BID`` and ``ASK`` rows exist for a date, ``MID`` can be derived as ``(bid + ask) / 2`` per field and stored with ``source`` set to ``derived:``. For ``open`` and ``close`` this is exact. For ``high`` and ``low`` it is an approximation, because the bid high and the ask high may occur at different times in the day. Derived mid bars must say so in ``source``. - **Validation, in addition to the existing OHLC rules:** for any date with both sides, ``ask >= bid`` for every field; a ``MID`` bar lies between the bid and ask bars; the batch is rejected otherwise. ``data_origin`` and ``price_basis`` are independent: a simulated bid is ``SIMULATED`` and ``BID``. Query changes ~~~~~~~~~~~~~ The repository methods gain a ``basis`` argument (``latest_close(symbol, as_of, basis)`` and ``closes_through(symbol, as_of, basis)``). The look-ahead guard is unchanged: every basis is read with ``px_date <= as_of``. A series used for VaR returns must use **one** basis throughout; returns that mix bases are wrong. Which basis each calculation uses ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ .. list-table:: :header-rows: 1 * - Calculation - Basis - Note * - Unrealized P&L (default) - ``MID`` - Reference valuation; the default policy. Stored as ``pnl_eod.mark_basis`` so a figure can be reproduced. * - Unrealized P&L (liquidation value) - ``BID`` for long, ``ASK`` for short - Optional stricter policy; shows what closing the position would realize. * - Historical VaR returns - ``MID`` - One consistent series; avoids bid/ask bounce in the returns. * - Notional and limit checks - ``MID`` - Matches the valuation mark. * - Backtest and simulated fills - ``ASK`` to buy, ``BID`` to sell - If only ``MID`` exists, apply an explicit half-spread and label the fill as modelled. * - Booked trades - none - ``trades.price`` is the executed price and already includes the spread paid. Recording ``mid_at_execution`` for slippage analysis is a later, optional addition. Growth and planned changes -------------------------- Roughly one row per symbol per business day (crypto: per calendar day). Volume or open interest, needed for equities and crypto, would be added as nullable columns, a safe additive migration. Price corrections as versioned rows, and dropping the redundant single-column ``symbol`` index, are on the :doc:`roadmap `.