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.

#	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 Multi-asset design: 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 Market database (market.db))	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).