Database Schema
Every table in db.py's SQLite schema: what each column means,
whether it's required, and — where it isn't obvious — why the field exists. Companion to
db_handler, which documents the Python methods that read and
write these tables.
db.py's _run_migrations() applies the same migration chain to
both. 18 tables total.
How it fits together
Three keys thread through most of this schema:
order_id— a single time-based id (next_order_id()), the join key acrossorders,order_status,execution, andstrategy_event. One order, oneorder_id, referenced everywhere something happens to or because of that order.exec_id— a broker-assigned fill identifier, unique acrossexecution, and the join key intocommission(one fill, at most one commission row).strategy_id/broker_id— references (not DB-enforced via a foreign key —PRAGMA foreign_keys=ONis set, but most columns below have noREFERENCESclause to enforce) into the smallstrategyandbrokertables, present on almost every other table so a row always knows which strategy and which broker it belongs to.
A normal trade's life: a strategy client reserves an order_id, writes an
orders row and a strategy_event (status PENDING),
submits to the broker, and the broker's fill comes back through a reconciliation client as
an execution row (plus a commission row), which flips the matching
strategy_event to CONFIRMED once the sum of executions for that
order reaches orders.total_quantity. order_status is a separate,
append-only log of the broker's own status transitions for the order, independent of that
confirm step.
orders.order_type, execution.side,
strategy_event.event_type, etc.) are validated by a pair of
BEFORE INSERT/BEFORE UPDATE triggers built from a
VALID_* Python tuple — changing the tuple and re-running migrations is enough
to change what's allowed, no table rebuild. Two older columns instead bake the enum directly
into a CHECK constraint in the original CREATE TABLE statement:
orders.side and order_status.status. Those can't be extended by
changing a Python tuple; they need a migration that rebuilds the column.
strategy
Every strategy that can own an order, a fill, or a position — including sentinel rows that
aren't real strategies, so every other table can key off strategy_id uniformly
instead of needing a separate "unattributed" code path.
| Field | Type | Required | Notes |
|---|---|---|---|
strategy_id | INTEGER | PK, autoincrement | Two sentinel rows are seeded: -1 ("None" — unattributed) and
-2 ("Void" — deliberately excluded from attribution).
db.py also defines MANUAL_BUCKET_STRATEGY_ID = 0 as a
reserved constant, but no strategy_id=0 row is actually seeded — confirm
a row exists before relying on it. |
name | TEXT | NOT NULL, UNIQUE | The human-readable name shown throughout the UI and used to look up
strategy_id via get_strategy_id(name). |
description | TEXT | nullable | Free text. |
capital_allocation | REAL | nullable | Seed capital, combined with the strategy's own trade history (not a separately
maintained cash balance) to compute its current compounded capital.
NULL means capital tracking isn't configured — not zero. |
create_time | TEXT | defaults to now | |
update_time | TEXT | nullable, no default | Stays NULL until something explicitly sets it. |
broker
Every broker that can place an order. Same sentinel-row pattern as strategy.
| Field | Type | Required | Notes |
|---|---|---|---|
broker_id | INTEGER | PK, autoincrement | Sentinel row -1 ("None") is seeded. |
name | TEXT | NOT NULL, UNIQUE | Used to look up broker_id via get_broker_id(name). |
full_name | TEXT | nullable | |
create_time | TEXT | defaults to now | |
update_time | TEXT | defaults to now |
orders
One row per submitted order — the canonical, post-hoc audit record.
| Field | Type | Required | Notes |
|---|---|---|---|
seq_id | INTEGER | PK, autoincrement | Internal row id — not the order id used anywhere else. |
order_id | INTEGER | NOT NULL, UNIQUE | From next_order_id() — the real join key. |
strategy_id | INTEGER | NOT NULL | |
broker_id | INTEGER | NOT NULL | |
account | TEXT | nullable | The broker account used. |
symbol | TEXT | NOT NULL | |
contract_id | TEXT | nullable | Broker-specific instrument id. |
instrument_type | TEXT | nullable, enum | STOCK / CRYPTO / PERPETUAL / FUTURE / OPTION. |
side | TEXT | NOT NULL, CHECK enum | BUY / SELL — the older CHECK-constraint mechanism, see the callout above. |
order_type | TEXT | NOT NULL, enum | Canonical name — MARKET, LIMIT, MOC, MOO, STOP, STOP_LIMIT, TRAILING, plus the Trade-Ops-only provenance markers SYNTHETIC/MANUAL_LINKED/MANUAL_ADOPTED. Never the broker's wire-format string. |
total_quantity | REAL | NOT NULL | Always positive. |
limit_price | REAL | nullable | Per order type. |
stop_price | REAL | nullable | Per order type. |
trail_amount | REAL | nullable | Per order type. |
trail_type | TEXT | nullable, enum | AMOUNT / PERCENT — units for trail_amount. |
time_in_force | TEXT | nullable, enum | DAY/GTC/IOC/FOK/GTD. NULL where timing is implied by order_type (MOC, MOO). |
expire_time | TEXT | nullable | Required in practice when time_in_force=GTD. |
post_only | INTEGER | nullable, 0/1 | |
extended_hours | INTEGER | nullable, 0/1 | |
notional_value | REAL | nullable | Informational once resolved to total_quantity. |
contingency_type | TEXT | nullable, enum | NONE/OCO/OTO/BRACKET, pairs with parent_order_id. |
parent_order_id | INTEGER | nullable | References another row's order_id, not seq_id. |
supersedes_order_id | INTEGER | nullable | Which order a synthetic replacement order superseded (Trade Ops link-executions workflow). |
external_order_id | TEXT | nullable | The broker's own order id — arrives late, set via update_order_external_id. |
client_reference | TEXT | nullable | Our order_id as a string, echoed to the broker — the primary reconciliation match key. |
broker_ack_time | TEXT | nullable | The broker's own received/acknowledged timestamp, distinct from create_time. |
metadata | TEXT | nullable | JSON escape hatch for broker-specific fields with no canonical column. |
create_time | TEXT | defaults to now | |
update_time | TEXT | defaults to now |
order_status
Append-only log of the broker's own status transitions for an order.
| Field | Type | Required | Notes |
|---|---|---|---|
order_status_id | INTEGER | PK, autoincrement | |
order_id | INTEGER | NOT NULL, FK | References orders(order_id). |
status | TEXT | NOT NULL, CHECK enum | NEW / PENDING_SUBMIT / SUBMITTED / PENDING_CANCEL / CANCELLED / PARTIALLY_FILLED / FILLED / REJECTED / EXPIRED / INACTIVE / REPLACED / STOPEXEC — the older CHECK-constraint mechanism. |
filled_quantity | REAL | nullable | |
remaining_quantity | REAL | nullable | |
avg_fill_price | REAL | nullable | |
last_fill_price | REAL | nullable | |
external_order_id | TEXT | nullable | e.g. IBKR permId. |
client_reference | TEXT | nullable | e.g. IBKR orderRef. |
parent_order_id | INTEGER | nullable | Brackets, OCO, trailing. |
reason_held | TEXT | nullable | e.g. IBKR whyHeld = 'locate'. |
cap_price | REAL | nullable | |
metadata | TEXT | nullable | JSON. |
event_time | TEXT | defaults to now | When this status actually changed. |
create_time | TEXT | defaults to now | When this row was written. |
execution
One row per fill.
| Field | Type | Required | Notes |
|---|---|---|---|
seq_id | INTEGER | PK, autoincrement | |
exec_id | TEXT | NOT NULL, UNIQUE | The broker's own execution id — the idempotency key. |
order_id | INTEGER | NOT NULL | |
strategy_id | INTEGER | NOT NULL | |
broker_id | INTEGER | NOT NULL | |
symbol | TEXT | NOT NULL | |
contract_id | TEXT | nullable | |
instrument_type | TEXT | nullable, enum | |
side | TEXT | NOT NULL, enum | BUY / SELL — trigger-based here, unlike orders.side. |
quantity | REAL | NOT NULL | Always positive. |
price | REAL | NOT NULL | Fill price. |
exec_time | TEXT | NOT NULL | |
exchange | TEXT | nullable | e.g. NYSE, ARCA, BINANCE. |
liquidity_flag | TEXT | nullable | MAKER / TAKER / etc. |
order_type | TEXT | nullable, enum | |
cum_qty | REAL | nullable | Broker-reported cumulative fill quantity. |
avg_price | REAL | nullable | Broker-reported average price. |
is_liquidation | INTEGER | nullable | Forced-liquidation flag. |
external_order_id | TEXT | nullable | The broker's own order id. |
external_exec_id | TEXT | nullable | The broker's own execution id. |
open_close_indicator | TEXT | nullable | Richer per-fill accounting / tax-lot data. Not every source populates every field. |
holding_period_start | TEXT | nullable | Richer per-fill accounting / tax-lot data. Not every source populates every field. |
cost_basis | REAL | nullable | Richer per-fill accounting / tax-lot data. Not every source populates every field. |
proceeds | REAL | nullable | Richer per-fill accounting / tax-lot data. Not every source populates every field. |
net_cash | REAL | nullable | Richer per-fill accounting / tax-lot data. Not every source populates every field. |
isin | TEXT | nullable | Richer per-fill accounting / tax-lot data. Not every source populates every field. |
cusip | TEXT | nullable | Richer per-fill accounting / tax-lot data. Not every source populates every field. |
description | TEXT | nullable | Richer per-fill accounting / tax-lot data. Not every source populates every field. |
metadata | TEXT | nullable | JSON. |
create_time | TEXT | defaults to now | |
update_time | TEXT | defaults to now |
commission
1:1 with execution via exec_id.
| Field | Type | Required | Notes |
|---|---|---|---|
commission_id | INTEGER | PK, autoincrement | |
exec_id | TEXT | NOT NULL, UNIQUE | Duplicate inserts are silently ignored — one commission row per fill, by construction. |
amount | REAL | NOT NULL | |
currency | TEXT | NOT NULL | |
fee_type | TEXT | NOT NULL, enum | Only COMMISSION today. |
realized_pnl | REAL | nullable | Account-level, not strategy-scoped — mixes in every other strategy/manual trade sharing that account+symbol. Use the strategy_execution_pnl view for a strategy's own attributed PnL instead. |
tax_amount | REAL | nullable | A column on the same row, not a second commission row for the same exec_id. |
metadata | TEXT | nullable | JSON. |
create_time | TEXT | defaults to now | |
update_time | TEXT | defaults to now |
strategy_event
The intent/confirmation log — what a strategy meant to do, and whether it's been confirmed against real fills.
| Field | Type | Required | Notes |
|---|---|---|---|
strategy_event_id | INTEGER | PK, autoincrement | |
event_time | TEXT | defaults to now | |
strategy_id | INTEGER | NOT NULL | |
broker_id | INTEGER | NOT NULL | |
order_id | INTEGER | nullable | |
symbol | TEXT | NOT NULL | |
contract_id | TEXT | nullable | |
position | REAL | NOT NULL | Target/resulting position — what get_confirmed_position is derived from. |
event_type | TEXT | nullable, enum | OPEN/CLOSE/FLIP/PARTIAL_CLOSE/INCREASE/NONE (NONE is for a planned event that filled zero). Recomputed from the real fill at confirm time — may differ from what was originally planned. |
original_type | TEXT | nullable | What was planned, written once at insert and never touched again — preserved separately from event_type, which gets corrected. |
group_label | TEXT | nullable | Caller-defined grouping tag. |
metadata | TEXT | nullable | JSON. |
status | TEXT | nullable, enum | PENDING / CONFIRMED / FAILED. No other states. |
manual_correction | INTEGER | DEFAULT 0 | Flags an event created by an operator via Trade Ops' manual-confirm workflow rather than by the strategy itself. |
create_time | TEXT | defaults to now | |
update_time | TEXT | defaults to now |
execution_request and position_event were dropped from the schema
entirely — both superseded and confirmed unread by any live client. Neither table, nor any
of their db_handler methods, exist any more.
position_external
Append-only snapshots of what the broker itself reports holding — independent of any strategy's own confirmed position.
| Field | Type | Required | Notes |
|---|---|---|---|
position_external_id | INTEGER | PK, autoincrement | |
account | TEXT | NOT NULL | |
contract_id | TEXT | nullable | |
position | REAL | nullable | |
avg_cost | REAL | nullable | |
metadata | TEXT | nullable | JSON. |
create_time | TEXT | defaults to now | |
update_time | TEXT | nullable, no default |
mtm_price
Daily OHLCV bars used for mark-to-market.
| Field | Type | Required | Notes |
|---|---|---|---|
mtm_price_id | INTEGER | PK, autoincrement | |
date | TEXT | NOT NULL | 'YYYY-MM-DD' — the trading day this price is for. |
symbol | TEXT | NOT NULL | |
contract_id | TEXT | nullable | |
open | REAL | nullable | |
high | REAL | nullable | |
low | REAL | nullable | |
close | REAL | nullable | |
adj_close | REAL | nullable | |
volume | REAL | nullable | |
metadata | TEXT | nullable | JSON. |
create_time | TEXT | defaults to now | |
update_time | TEXT | defaults to now |
UNIQUE(date, symbol) — inserting the same pair again replaces the row.
report
Backs the digest system in email_manager — see that page
for the Python methods a client actually calls (report.add_section,
report.add_table, ...). This table and the two below it are written through
that API, not directly.
| Field | Type | Required | Notes |
|---|---|---|---|
report_id | INTEGER | PK, autoincrement | |
name | TEXT | NOT NULL | Keyed with date, not alone — more than one distinct report can exist on the same day. |
date | TEXT | nullable | |
status | TEXT | nullable | open until report.mark_sent flips it to sent. |
type | TEXT | nullable | |
recipients | TEXT | nullable | |
create_time | TEXT | defaults to now | |
update_time | TEXT | nullable, no default |
report_section
| Field | Type | Required | Notes |
|---|---|---|---|
section_id | INTEGER | PK, autoincrement | |
report_id | INTEGER | FK, ON DELETE CASCADE | References report(report_id). |
name | TEXT | nullable | The section title. |
type | TEXT | nullable | list or table — a layout switch, not a severity marker. |
source | TEXT | nullable | Which client contributed this section — shown in the rendered digest. |
create_time | TEXT | defaults to now | |
update_time | TEXT | nullable, no default |
report_item
| Field | Type | Required | Notes |
|---|---|---|---|
item_id | INTEGER | PK, autoincrement | |
section_id | INTEGER | FK, ON DELETE CASCADE | References report_section(section_id). |
text | TEXT | nullable | A plain string for a list section; a JSON-encoded row (header row included) for a table section. |
item_key | TEXT | nullable | Opt-in, caller-defined identity — only report.set_table_row populates it, for "current state" rows that should replace an earlier one instead of appending. NULL for ordinary append-only items. |
create_time | TEXT | defaults to now | |
update_time | TEXT | nullable, no default |
email_account
Backs email_manager — see that page for the
EmailAccount dataclass and sender/receiver distinction.
| Field | Type | Required | Notes |
|---|---|---|---|
email_account_id | INTEGER | PK, autoincrement | |
name | TEXT | NOT NULL, UNIQUE | |
provider | TEXT | nullable | |
smtp_server | TEXT | NOT NULL | Empty string, not NULL, on a receiver row — these columns predate the receiver account type and were never relaxed to nullable. |
smtp_port | INTEGER | NOT NULL, DEFAULT 587 | |
use_tls | INTEGER | NOT NULL, DEFAULT 1 | |
username | TEXT | NOT NULL | Empty string on a receiver row. |
password | TEXT | NOT NULL | Empty string on a receiver row. Obfuscated (base64 + rotation), not encrypted. |
from_address | TEXT | NOT NULL | On a receiver row, reused as the contact's email. |
from_name | TEXT | NOT NULL | On a receiver row, reused as the contact's display name. |
is_default | INTEGER | NOT NULL, DEFAULT 0 | Scoped per account_type — a default sender and a default receiver coexist independently. |
account_type | TEXT | NOT NULL, DEFAULT 'sender' | sender or receiver. |
created_at | TEXT | defaults to now | |
updated_at | TEXT | defaults to now |
advisory_lock
A generic, DB-backed mutual-exclusion primitive, serializing access to a shared resource
(e.g. one IBKR session) across every client — replaced a module-level
threading.Lock() that could silently fragment into multiple, mutually-unaware
lock objects across the clients tree.
| Field | Type | Required | Notes |
|---|---|---|---|
name | TEXT | PK | The resource being locked. |
holder_token | TEXT | NOT NULL | Checked on release — a caller that overstayed its TTL and got reclaimed can't delete the new holder's claim. |
caller | TEXT | NOT NULL | |
acquired_at | TEXT | NOT NULL | |
expires_at | TEXT | NOT NULL | |
pid | INTEGER | nullable | |
thread_name | TEXT | nullable |
client_status
Which clients an operator has explicitly stopped, so a service restart doesn't silently bring them back. Only stopped clients get a row — running is the implicit default.
| Field | Type | Required | Notes |
|---|---|---|---|
client_name | TEXT | PK | |
stopped_at | TEXT | defaults to now |
setting_node
Backs settings — the settings tree itself, moved from
config.json to the database.
| Field | Type | Required | Notes |
|---|---|---|---|
path | TEXT | PK | Full path, e.g. Settings/IBKR Flex Web Service/TOKEN_PAPER — the lookup key settings.get_by_path uses. |
node_type | TEXT | NOT NULL, enum | folder or setting. |
value_type | TEXT | nullable | text/float/bool/list/dict/secret. |
source | TEXT | nullable | direct or env. |
env_var | TEXT | nullable | Set when source='env'. |
value | TEXT | nullable | JSON-encoded; a secret value holds obfuscated ciphertext either way. |
locked | INTEGER | NOT NULL, DEFAULT 0 | Folders only — the folder's structure can't be deleted or renamed. Doesn't make settings inside it read-only. |
description | TEXT | nullable | |
create_time | TEXT | defaults to now | |
update_time | TEXT | defaults to now |
order_id_state
Singleton row backing next_order_id() — never read or written directly.
| Field | Type | Required | Notes |
|---|---|---|---|
id | INTEGER | PK, CHECK(id = 1) | Always exactly one row. |
last_id | INTEGER | NOT NULL, DEFAULT 0 | |
created_at | TEXT | defaults to now |
version
Append-only history of every schema version this database has ever passed through, oldest first.
| Field | Type | Required | Notes |
|---|---|---|---|
version | TEXT | NOT NULL | |
description | TEXT | nullable | |
create_time | TEXT | defaults to now |