Data Model

Database schema

OptionsLab uses TimescaleDB (PostgreSQL-compatible) as its primary database. The schema is managed with Alembic migrations. Core trading data lives in the public schema; backtesting and agent memory use isolated schemas for multi-tenancy and permission clarity.

Schema namespaces

public

Core trading entities: users, accounts, symbols, orders, positions, webhooks.

backtesting

Backtest run metadata, equity curves, and trade-level simulation results.

agentic

LLM agent memory, tool call logs, and agent execution history.

public

users

System users (traders, admins). Passwords are bcrypt-hashed; role controls dashboard access level.

Column	Type	Notes
id	serial	Primary key.
email	text	Unique login identity.
password_hash	text	bcrypt hash — never stored in plaintext.
role	text	'admin' or 'trader'.
created_at	timestamptz	Account creation timestamp.

public

accounts

Broker accounts. Each account maps to one IB account number and holds its own watchlist, settings, and order history.

Column	Type	Notes
id	serial	Primary key.
user_id	int → users	Owning user.
name	text	Human-readable label.
ib_account	text	IB account number (e.g. DU1234567).
ib_client_id	int	IB API client ID — must be unique per connected client.
settings	jsonb	Per-account configuration: default lot size, bracket params.

public

account_symbols

Watchlist entries — symbols an account is tracking. Stores mode (auto/manual) and optional pinned contract for options.

Column	Type	Notes
id	serial	Primary key (used as entryId in URLs).
account_id	int → accounts	Parent account.
symbol	text	Ticker symbol (e.g. SPX, AAPL).
mode	text	'auto' or 'manual'.
settings	jsonb	Symbol-level overrides for position sizing, brackets.
pinned_contract	jsonb	Pinned options contract details (expiry, strike, right).
added_at	timestamptz	When the symbol was added to the watchlist.

public

orders

Order log. Every order submitted through OptionsLab (via webhook or direct API) is recorded here regardless of IB status.

Column	Type	Notes
id	serial	Primary key.
account_id	int → accounts	Account that placed the order.
ib_order_id	int	IB-assigned order ID. Null until IB confirms placement.
symbol	text	Underlying ticker.
action	text	'BUY' or 'SELL'.
quantity	numeric	Number of contracts or shares.
order_type	text	'MKT', 'LMT', etc.
status	text	Last known IB status (Submitted, Filled, Cancelled…).
filled_at	timestamptz	Execution timestamp (null if not filled).
avg_fill_price	numeric	Volume-weighted average fill price.
created_at	timestamptz	Order creation time.

backtesting

runs

Backtest run metadata. Lives in the 'backtesting' schema, which maps to a separate TimescaleDB schema for isolation.

Column	Type	Notes
id	uuid	Run ID — used in URLs and API responses.
account_id	int → accounts	Account the strategy config was pulled from.
status	text	'pending', 'running', 'completed', 'failed'.
strategy_config	jsonb	Full strategy parameters snapshot at run time.
result_summary	jsonb	CAGR, max drawdown, Sharpe, trade count.
created_at	timestamptz	Run submission time.
completed_at	timestamptz	Run finish time.

agentic

agent_memory

LLM agent long-term memory store. Each entry is a scoped fact or observation the agent persisted for future reasoning.

Column	Type	Notes
id	uuid	Primary key.
agent_id	text	Agent identifier (e.g. 'market-scanner-v1').
account_id	int → accounts	Tenant scope — memories are isolated per account.
key	text	Semantic key for retrieval (e.g. 'spx_regime_2026-05').
value	jsonb	Stored fact, observation, or structured data.
created_at	timestamptz	When the memory was written.
expires_at	timestamptz	Optional TTL — null means permanent.

Migrations

Schema changes are managed with Alembic. All migration files live in alembic/versions/. Run pending migrations after deploying a new version:

docker compose exec bot alembic upgrade head

See the Deployment guide for full upgrade procedures including zero-downtime strategies.