API
Open Dashboard
A fresh install and an upgraded database converge on the same shape by construction — 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 across orders, order_status, execution, and strategy_event. One order, one order_id, referenced everywhere something happens to or because of that order.
  • exec_id — a broker-assigned fill identifier, unique across execution, and the join key into commission (one fill, at most one commission row).
  • strategy_id / broker_id — references (not DB-enforced via a foreign key — PRAGMA foreign_keys=ON is set, but most columns below have no REFERENCES clause to enforce) into the small strategy and broker tables, 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.

Two enum-enforcement mechanisms coexist, and it matters which one a given column uses. Most enum columns (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.

FieldTypeRequiredNotes
strategy_idINTEGERPK, 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.
nameTEXTNOT NULL, UNIQUE The human-readable name shown throughout the UI and used to look up strategy_id via get_strategy_id(name).
descriptionTEXTnullableFree text.
capital_allocationREALnullable 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_timeTEXTdefaults to now
update_timeTEXTnullable, no default Stays NULL until something explicitly sets it.

broker

Every broker that can place an order. Same sentinel-row pattern as strategy.

FieldTypeRequiredNotes
broker_idINTEGERPK, autoincrement Sentinel row -1 ("None") is seeded.
nameTEXTNOT NULL, UNIQUE Used to look up broker_id via get_broker_id(name).
full_nameTEXTnullable
create_timeTEXTdefaults to now
update_timeTEXTdefaults to now

orders

One row per submitted order — the canonical, post-hoc audit record.

FieldTypeRequiredNotes
seq_idINTEGERPK, autoincrementInternal row id — not the order id used anywhere else.
order_idINTEGERNOT NULL, UNIQUEFrom next_order_id() — the real join key.
strategy_idINTEGERNOT NULL
broker_idINTEGERNOT NULL
accountTEXTnullableThe broker account used.
symbolTEXTNOT NULL
contract_idTEXTnullableBroker-specific instrument id.
instrument_typeTEXTnullable, enumSTOCK / CRYPTO / PERPETUAL / FUTURE / OPTION.
sideTEXTNOT NULL, CHECK enumBUY / SELL — the older CHECK-constraint mechanism, see the callout above.
order_typeTEXTNOT NULL, enumCanonical 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_quantityREALNOT NULLAlways positive.
limit_priceREALnullablePer order type.
stop_priceREALnullablePer order type.
trail_amountREALnullablePer order type.
trail_typeTEXTnullable, enumAMOUNT / PERCENT — units for trail_amount.
time_in_forceTEXTnullable, enumDAY/GTC/IOC/FOK/GTD. NULL where timing is implied by order_type (MOC, MOO).
expire_timeTEXTnullableRequired in practice when time_in_force=GTD.
post_onlyINTEGERnullable, 0/1
extended_hoursINTEGERnullable, 0/1
notional_valueREALnullableInformational once resolved to total_quantity.
contingency_typeTEXTnullable, enumNONE/OCO/OTO/BRACKET, pairs with parent_order_id.
parent_order_idINTEGERnullableReferences another row's order_id, not seq_id.
supersedes_order_idINTEGERnullableWhich order a synthetic replacement order superseded (Trade Ops link-executions workflow).
external_order_idTEXTnullableThe broker's own order id — arrives late, set via update_order_external_id.
client_referenceTEXTnullableOur order_id as a string, echoed to the broker — the primary reconciliation match key.
broker_ack_timeTEXTnullableThe broker's own received/acknowledged timestamp, distinct from create_time.
metadataTEXTnullableJSON escape hatch for broker-specific fields with no canonical column.
create_timeTEXTdefaults to now
update_timeTEXTdefaults to now

order_status

Append-only log of the broker's own status transitions for an order.

FieldTypeRequiredNotes
order_status_idINTEGERPK, autoincrement
order_idINTEGERNOT NULL, FKReferences orders(order_id).
statusTEXTNOT NULL, CHECK enumNEW / PENDING_SUBMIT / SUBMITTED / PENDING_CANCEL / CANCELLED / PARTIALLY_FILLED / FILLED / REJECTED / EXPIRED / INACTIVE / REPLACED / STOPEXEC — the older CHECK-constraint mechanism.
filled_quantityREALnullable
remaining_quantityREALnullable
avg_fill_priceREALnullable
last_fill_priceREALnullable
external_order_idTEXTnullablee.g. IBKR permId.
client_referenceTEXTnullablee.g. IBKR orderRef.
parent_order_idINTEGERnullableBrackets, OCO, trailing.
reason_heldTEXTnullablee.g. IBKR whyHeld = 'locate'.
cap_priceREALnullable
metadataTEXTnullableJSON.
event_timeTEXTdefaults to nowWhen this status actually changed.
create_timeTEXTdefaults to nowWhen this row was written.

execution

One row per fill.

FieldTypeRequiredNotes
seq_idINTEGERPK, autoincrement
exec_idTEXTNOT NULL, UNIQUEThe broker's own execution id — the idempotency key.
order_idINTEGERNOT NULL
strategy_idINTEGERNOT NULL
broker_idINTEGERNOT NULL
symbolTEXTNOT NULL
contract_idTEXTnullable
instrument_typeTEXTnullable, enum
sideTEXTNOT NULL, enumBUY / SELL — trigger-based here, unlike orders.side.
quantityREALNOT NULLAlways positive.
priceREALNOT NULLFill price.
exec_timeTEXTNOT NULL
exchangeTEXTnullablee.g. NYSE, ARCA, BINANCE.
liquidity_flagTEXTnullableMAKER / TAKER / etc.
order_typeTEXTnullable, enum
cum_qtyREALnullableBroker-reported cumulative fill quantity.
avg_priceREALnullableBroker-reported average price.
is_liquidationINTEGERnullableForced-liquidation flag.
external_order_idTEXTnullableThe broker's own order id.
external_exec_idTEXTnullableThe broker's own execution id.
open_close_indicatorTEXTnullableRicher per-fill accounting / tax-lot data. Not every source populates every field.
holding_period_startTEXTnullableRicher per-fill accounting / tax-lot data. Not every source populates every field.
cost_basisREALnullableRicher per-fill accounting / tax-lot data. Not every source populates every field.
proceedsREALnullableRicher per-fill accounting / tax-lot data. Not every source populates every field.
net_cashREALnullableRicher per-fill accounting / tax-lot data. Not every source populates every field.
isinTEXTnullableRicher per-fill accounting / tax-lot data. Not every source populates every field.
cusipTEXTnullableRicher per-fill accounting / tax-lot data. Not every source populates every field.
descriptionTEXTnullableRicher per-fill accounting / tax-lot data. Not every source populates every field.
metadataTEXTnullableJSON.
create_timeTEXTdefaults to now
update_timeTEXTdefaults to now

commission

1:1 with execution via exec_id.

FieldTypeRequiredNotes
commission_idINTEGERPK, autoincrement
exec_idTEXTNOT NULL, UNIQUEDuplicate inserts are silently ignored — one commission row per fill, by construction.
amountREALNOT NULL
currencyTEXTNOT NULL
fee_typeTEXTNOT NULL, enumOnly COMMISSION today.
realized_pnlREALnullableAccount-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_amountREALnullableA column on the same row, not a second commission row for the same exec_id.
metadataTEXTnullableJSON.
create_timeTEXTdefaults to now
update_timeTEXTdefaults to now

strategy_event

The intent/confirmation log — what a strategy meant to do, and whether it's been confirmed against real fills.

FieldTypeRequiredNotes
strategy_event_idINTEGERPK, autoincrement
event_timeTEXTdefaults to now
strategy_idINTEGERNOT NULL
broker_idINTEGERNOT NULL
order_idINTEGERnullable
symbolTEXTNOT NULL
contract_idTEXTnullable
positionREALNOT NULLTarget/resulting position — what get_confirmed_position is derived from.
event_typeTEXTnullable, enumOPEN/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_typeTEXTnullableWhat was planned, written once at insert and never touched again — preserved separately from event_type, which gets corrected.
group_labelTEXTnullableCaller-defined grouping tag.
metadataTEXTnullableJSON.
statusTEXTnullable, enumPENDING / CONFIRMED / FAILED. No other states.
manual_correctionINTEGERDEFAULT 0Flags an event created by an operator via Trade Ops' manual-confirm workflow rather than by the strategy itself.
create_timeTEXTdefaults to now
update_timeTEXTdefaults 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.

FieldTypeRequiredNotes
position_external_idINTEGERPK, autoincrement
accountTEXTNOT NULL
contract_idTEXTnullable
positionREALnullable
avg_costREALnullable
metadataTEXTnullableJSON.
create_timeTEXTdefaults to now
update_timeTEXTnullable, no default

mtm_price

Daily OHLCV bars used for mark-to-market.

FieldTypeRequiredNotes
mtm_price_idINTEGERPK, autoincrement
dateTEXTNOT NULL'YYYY-MM-DD' — the trading day this price is for.
symbolTEXTNOT NULL
contract_idTEXTnullable
openREALnullable
highREALnullable
lowREALnullable
closeREALnullable
adj_closeREALnullable
volumeREALnullable
metadataTEXTnullableJSON.
create_timeTEXTdefaults to now
update_timeTEXTdefaults 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.

FieldTypeRequiredNotes
report_idINTEGERPK, autoincrement
nameTEXTNOT NULLKeyed with date, not alone — more than one distinct report can exist on the same day.
dateTEXTnullable
statusTEXTnullableopen until report.mark_sent flips it to sent.
typeTEXTnullable
recipientsTEXTnullable
create_timeTEXTdefaults to now
update_timeTEXTnullable, no default

report_section

FieldTypeRequiredNotes
section_idINTEGERPK, autoincrement
report_idINTEGERFK, ON DELETE CASCADEReferences report(report_id).
nameTEXTnullableThe section title.
typeTEXTnullablelist or table — a layout switch, not a severity marker.
sourceTEXTnullableWhich client contributed this section — shown in the rendered digest.
create_timeTEXTdefaults to now
update_timeTEXTnullable, no default

report_item

FieldTypeRequiredNotes
item_idINTEGERPK, autoincrement
section_idINTEGERFK, ON DELETE CASCADEReferences report_section(section_id).
textTEXTnullableA plain string for a list section; a JSON-encoded row (header row included) for a table section.
item_keyTEXTnullableOpt-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_timeTEXTdefaults to now
update_timeTEXTnullable, no default

email_account

Backs email_manager — see that page for the EmailAccount dataclass and sender/receiver distinction.

FieldTypeRequiredNotes
email_account_idINTEGERPK, autoincrement
nameTEXTNOT NULL, UNIQUE
providerTEXTnullable
smtp_serverTEXTNOT NULLEmpty string, not NULL, on a receiver row — these columns predate the receiver account type and were never relaxed to nullable.
smtp_portINTEGERNOT NULL, DEFAULT 587
use_tlsINTEGERNOT NULL, DEFAULT 1
usernameTEXTNOT NULLEmpty string on a receiver row.
passwordTEXTNOT NULLEmpty string on a receiver row. Obfuscated (base64 + rotation), not encrypted.
from_addressTEXTNOT NULLOn a receiver row, reused as the contact's email.
from_nameTEXTNOT NULLOn a receiver row, reused as the contact's display name.
is_defaultINTEGERNOT NULL, DEFAULT 0Scoped per account_type — a default sender and a default receiver coexist independently.
account_typeTEXTNOT NULL, DEFAULT 'sender'sender or receiver.
created_atTEXTdefaults to now
updated_atTEXTdefaults 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.

FieldTypeRequiredNotes
nameTEXTPKThe resource being locked.
holder_tokenTEXTNOT NULLChecked on release — a caller that overstayed its TTL and got reclaimed can't delete the new holder's claim.
callerTEXTNOT NULL
acquired_atTEXTNOT NULL
expires_atTEXTNOT NULL
pidINTEGERnullable
thread_nameTEXTnullable

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.

FieldTypeRequiredNotes
client_nameTEXTPK
stopped_atTEXTdefaults to now

setting_node

Backs settings — the settings tree itself, moved from config.json to the database.

FieldTypeRequiredNotes
pathTEXTPKFull path, e.g. Settings/IBKR Flex Web Service/TOKEN_PAPER — the lookup key settings.get_by_path uses.
node_typeTEXTNOT NULL, enumfolder or setting.
value_typeTEXTnullabletext/float/bool/list/dict/secret.
sourceTEXTnullabledirect or env.
env_varTEXTnullableSet when source='env'.
valueTEXTnullableJSON-encoded; a secret value holds obfuscated ciphertext either way.
lockedINTEGERNOT NULL, DEFAULT 0Folders only — the folder's structure can't be deleted or renamed. Doesn't make settings inside it read-only.
descriptionTEXTnullable
create_timeTEXTdefaults to now
update_timeTEXTdefaults to now

order_id_state

Singleton row backing next_order_id() — never read or written directly.

FieldTypeRequiredNotes
idINTEGERPK, CHECK(id = 1)Always exactly one row.
last_idINTEGERNOT NULL, DEFAULT 0
created_atTEXTdefaults to now

version

Append-only history of every schema version this database has ever passed through, oldest first.

FieldTypeRequiredNotes
versionTEXTNOT NULL
descriptionTEXTnullable
create_timeTEXTdefaults to now