db_handler
db.py's DBHandler — the singleton every page and
every client script shares, imported as db_handler from
quantstrip. Methods are listed table by table, matching
database schema — admin-only tables (reports, email
accounts, settings, locks) are covered there but have no client-facing methods here, since
a trading client has no reason to call them directly.
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.
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.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.
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.
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
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).
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'.
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).
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).
(event_type, new_position). event_type
is one of OPEN / CLOSE / FLIP /
PARTIAL_CLOSE / INCREASE / NONE.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 apandas.DataFrame— except the handful explicitly documented above as returning adictor scalar. A query matching zero rows returns an empty DataFrame, notNone— check.emptyorlen(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_orderabove 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.