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

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 Data 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 Simulated data: why and how and the roadmap, item 9).

Query patterns

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

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):

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:<source>. 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

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 roadmap.