API
Open Dashboard

run_query(sql_str, params = ())

Not tied to one table — the single sanctioned entry point for any SQL not covered by a named method below, read or write. Every typed method on this class is a thin wrapper around it internally. Reach for this instead of inventing a plausibly-named method, and instead of opening your own sqlite3.connect(...) — connection pooling, WAL mode, and thread-safety are already handled underneath it.

Returns a pandas.DataFrame for anything with a result set (a SELECT, or an INSERT/UPDATE with a RETURNING clause). Returns the affected row count (int) for a plain write with no result set. Runs in autocommit mode — no explicit commit needed, and calling one is incorrect.
run_querypython
rows = db_handler.run_query(
    "SELECT COALESCE(SUM(quantity),0) "
    "FROM execution WHERE order_id=?",
    (order_id,),
)
run_query executes whatever SQL you pass it, with no read-only guard. Prefer a typed method below wherever one exists.

strategy

get_strategy_id(name)

Looks up a strategy's id by its unique name. Resolve by name, never hardcode a numeric id — strategy_id is database-assigned and not stable across installs. Returns int, or None.

get_strategies()

Every strategy row, including the sentinel rows. Returns a pandas.DataFrame.

insert_strategy(name, description, capital_allocation = None)

Creates a strategy row — same operation available from Trade Operations' Static Data tab.

update_strategy(strategy_id, name, description, capital_allocation = None)

Renames or updates a strategy row.

delete_strategy(strategy_id)

Removes a strategy row.

get_strategy_capital(strategy_id)

The strategy's current compounded capital: its capital_allocation seed combined with its own trade history. None means capital tracking isn't configured — not zero.

Resolve by namepython
strategy_id = db_handler.get_strategy_id(self.name)
if strategy_id is None:
    logger.error(f"Strategy '{self.name}' not found in DB")
    return

broker

get_broker_id(name)

Looks up a broker's id by its unique name. Returns int, or None.

get_brokers()

Every broker row. Returns a pandas.DataFrame.

insert_broker(name, full_name)

Creates a broker row.

update_broker(broker_id, name, full_name)

Renames or updates a broker row.

delete_broker(broker_id)

Removes a broker row.

Resolve by namepython
broker_id = db_handler.get_broker_id(self.broker_name)
if broker_id is None:
    logger.error(f"Broker '{self.broker_name}' not found in DB")
    return
A strategy or broker referenced by insert_order/insert_execution/ insert_strategy_event must already exist in these two tables — create it first.

orders

next_order_id()

Allocates the correlation key used across orders, order_status, execution, and strategy_event. Backed by the single-row order_id_state table, not orders itself. Epoch-based, thread-safe and process-safe — never generate your own order-id scheme as a substitute. Returns int.

insert_order(order_id, strategy_id, broker_id, account, symbol, side, order_type, total_quantity, contract_id = None, limit_price = None, stop_price = None, trail_amount = None, instrument_type = None, parent_order_id = None, client_reference = None, supersedes_order_id = None, broker_ack_time = None, metadata = None, external_order_id = None)

Records the canonical order. order_type is always the canonical name ("MARKET", "MOO", ...), never a broker's wire-format string — translate immediately before the broker call, not before this insert.

update_order_external_id(order_id, external_order_id)

Records the broker's own order id once known, after submission.

get_order(order_id = None, strategy_id = None, symbol = None)

Flexible filtering, but not a composed AND filter: checks order_id first (if given, every other argument is ignored), then strategy_id+symbol together, then strategy_id alone. For a real multi-column filter outside these combinations, use run_query. Returns a pandas.DataFrame (possibly empty, never None).

Reserve, insert, submitpython
order_id = db_handler.next_order_id()

db_handler.insert_order(
    order_id=order_id, strategy_id=strategy_id,
    broker_id=broker_id, account=account_id,
    symbol=symbol, side=side, order_type=order_type,
    total_quantity=quantity,
    client_reference=str(order_id),
)

# ... submit to the broker ...

db_handler.update_order_external_id(
    order_id, str(external_id),
)

order_status

insert_order_status(order_id, status, filled_quantity = None, remaining_quantity = None, avg_fill_price = None, last_fill_price = None, external_order_id = None, client_reference = None, parent_order_id = None, reason_held = None, cap_price = None, metadata = None, event_time = None)

Appends a new order-status event. Append-only — never overwrites a prior row.

get_order_status(order_id = None)

All status events for an order, newest first.

get_current_order_status(order_id)

Just the most recent status row for an order.

execution

insert_execution(exec_id, order_id, strategy_id, broker_id, symbol, instrument_type, contract_id, side, quantity, price, exec_time, exchange = None, liquidity_flag = None, order_type = None, cum_qty = None, avg_price = None, is_liquidation = None, external_order_id = None, external_exec_id = None, open_close_indicator = None, holding_period_start = None, cost_basis = None, proceeds = None, net_cash = None, isin = None, cusip = None, description = None, metadata = None)

Inserts a fill. exec_id (the broker's own execution id) is UNIQUE — the idempotency key a reconciliation client relies on.

get_executions(order_id = None, exec_id = None)

The only fetch for execution rows — there is no execution_exists or get_filled_quantity method. Check existence or sum quantity yourself from what this returns, or use run_query directly (e.g. SELECT COALESCE(SUM(quantity),0) FROM execution WHERE order_id=?).

commission

insert_commission(exec_id, amount, currency, fee_type, realized_pnl = None, tax_amount = None, metadata = None)

Inserts the commission/fee record tied 1:1 to an execution via exec_id. Duplicate inserts for the same exec_id are silently ignored (ON CONFLICT DO NOTHING).

get_commission(exec_id)

The commission row for one execution.

strategy_event

insert_strategy_event(strategy_id, broker_id, symbol, position, event_type, order_id = None, contract_id = None, group_label = None, status = 'PENDING', metadata = None, manual_correction = False, event_time = None)

Records the intent/confirmation log entry — write this before submitting to the broker, with status='PENDING'.

insert_strategy_eventpython
db_handler.insert_strategy_event(
    strategy_id=strategy_id, broker_id=broker_id,
    symbol=symbol, position=target_position,
    event_type=event_type, order_id=order_id,
    status="PENDING",
)

update_strategy_event_status(order_id, status = 'CONFIRMED')

Flips a strategy_event to CONFIRMED or FAILED. Confirmation is order-fill based — the sum of executions for the order has reached orders.total_quantity — not position-based.

get_last_strategy_event(strategy_id, symbol = None)

The most recent strategy_event row for a strategy, optionally scoped to a symbol. This is the pre-trade gate: before placing an order for a symbol, this must return None (inception) or a row with status == 'CONFIRMED' — otherwise block the run and alert. Returns a dict, or None.

get_last_strategy_event_by_order(order_id)

Same shape, looked up by order_id instead of strategy/symbol.

get_confirmed_position(strategy_id, symbol)

The trusted "what does this strategy hold right now" — 0.0 if the strategy has never traded the symbol, excludes PENDING events. Prefer this over reading strategy_event.position by hand. Returns float.

get_currently_held_symbols()

Every symbol any strategy holds a nonzero confirmed position in, deduped across strategies — useful for a client that needs to fetch a price once per symbol (an MtM-style client).

Pre-trade gatepython
ev = db_handler.get_last_strategy_event(
    strategy_id, symbol=TRADE_SYMBOL,
)
if ev is not None and ev.get("status") != "CONFIRMED":
    logger.warning("blocked - unconfirmed event")
    return

held = db_handler.get_confirmed_position(
    strategy_id, TRADE_SYMBOL,
)

classify_position_transition(prior_position, signed_qty)

A module-level function in db.py, not a db_handler method — import it separately. Classifies a fill into an event_type given the position before the fill and the fill's signed quantity (positive for buys, negative for sells).

Returns (event_type, new_position). event_type is one of OPEN / CLOSE / FLIP / PARTIAL_CLOSE / INCREASE / NONE.
classify_position_transitionpython
from db import classify_position_transition

event_type, new_position = classify_position_transition(
    prior_position, signed_qty,
)
execution_request and position_event were dropped from the schema entirely — neither table, nor any of their db_handler methods, exist any more.

position_external

Used by reconciliation-style clients; rarely called by a new trading strategy directly.

get_positions_external(account = None)

Append-only snapshots of what the broker itself reports holding, independent of any strategy's own confirmed position. Returns a pandas.DataFrame.

insert_position_external(account, contract_id, position, avg_cost, metadata = None)

Records one broker-reported position snapshot.

mtm_price

Used by mark-to-market-style clients; rarely called by a new trading strategy directly.

get_mtm_prices(symbol = None, start_date = None, end_date = None)

Daily OHLCV bars used for mark-to-market. Returns a pandas.DataFrame.

insert_mtm_price(date, symbol, contract_id = None, open_price = None, high = None, low = None, close = None, adj_close = None, volume = None, metadata = None)

Records one daily OHLCV bar. One row per (date, symbol) — inserting the same pair again replaces it.

Calling conventions

Patterns in how these methods behave, not just their signatures — getting the usage pattern wrong is just as real a failure as calling a name that doesn't exist.

  • Every get_* method returns a pandas.DataFrame — except the handful explicitly documented above as returning a dict or scalar. A query matching zero rows returns an empty DataFrame, not None — check .empty or len(df). Access values by column name, never by position.
  • Flexible-filter getters take the first matching filter, they don't AND-combine every argument — see get_order above for the exact precedence.
  • Connections are pooled, WAL-mode, autocommit, and thread-safe already. Never open your own sqlite3.connect(...) or manage your own transaction.
  • next_order_id() is already strictly-increasing, thread-safe, and process-safe.