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 Reporting).

trades: one row per booked trade

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 Multi-asset design).
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

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 P&L and the EOD batch.

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 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 roadmap and Multi-asset design.