Database Handler

`db_handler`

The db_handler is a thread-safe singleton that manages all database operations for the Quantstrip trading system. It uses connection pooling for efficient concurrent access and implements the singleton pattern to ensure only one database handler exists per application instance.

Key Features

Thread-safe operations with connection pooling
Singleton pattern for shared database access
Time-based order ID generation
Canonical order and execution tracking with metadata support
Event-based position tracking (position_event and strategy_event)
External position reconciliation
Comprehensive reporting system with email account management

Initialization and Connection

Import db_handler from quantstrip

from quantstrip import db_handler as db

get_connection()

Context manager that retrieves a connection from the pool. This is the primary way to interact with the database directly.

Returns: Database connection object

Example:

with db.get_connection() as conn:
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM broker")
    results = cursor.fetchall()

Tip

The connection pool automatically manages connection lifecycle, returning connections to the pool when done or creating temporary connections if the pool is exhausted.

Running an SQL Query

run_query(sql_str, params=())

Executes a SELECT query and returns results as a pandas DataFrame. This is the recommended way to retrieve data from the database.

Parameters:

Parameter	Type	Description
`sql_str`	`str`	SQL query string
`params`	`tuple`	Query parameters for safe parameterized queries

Returns: pandas.DataFrame with query results

Example:

df = db.run_query(
    "SELECT * FROM execution WHERE order_id = ?", 
    (12345,)
)

Order ID Generation

next_order_id() Generates a strictly monotonic increasing order ID based on seconds since epoch (2020-01-01). This method is both thread-safe and process-safe.

Returns: int - Order ID (9 digits maximum)

Example:

order_id = db.next_order_id()
print(f"New order: {order_id}")  # e.g., 163847291

How It Works

Calculates seconds since epoch (2020-01-01)
Retrieves last used ID from database
Ensures new ID is strictly greater than last ID
Persists new ID to database for process safety
Handles edge cases like same-second calls or manual orders
Raises OverflowError if ID exceeds 9 digits

Order Management

insert_order(order_id, strategy_id, broker_id, account, symbol, side, order_type, total_quantity, limit_price=None, stop_price=None, trail_amount=None, instrument_type=None, parent_order_id=None, client_reference=None, external_order_id=None, metadata=None) Insert a canonical order record. The order_id is the Quantstrip-generated time-based ID from next_order_id().

Parameters:

Parameter	Type	Description
`order_id`	`int`	Quantstrip order ID (from next_order_id())
`strategy_id`	`int`	Strategy placing the order
`broker_id`	`int`	Broker to execute the order
`account`	`str`	Trading account identifier
`symbol`	`str`	Trading symbol
`side`	`str`	'BUY' or 'SELL'
`order_type`	`str`	MKT, LMT, STOP, STOP_LIMIT, IOC, FOK, TRAILING, etc.
`total_quantity`	`float`	Number of shares/contracts (always positive)
`limit_price`	`float`	Limit price (for limit orders)
`stop_price`	`float`	Stop price (for stop orders)
`trail_amount`	`float`	Trail amount (for trailing stops)
`instrument_type`	`str`	STOCK, CRYPTO, FUTURE, OPTION
`parent_order_id`	`int`	Parent order ID (for bracket orders)
`client_reference`	`str`	Client-provided reference/orderRef
`external_order_id`	`str`	Broker's order ID (IB orderId, Binance orderId)
`metadata`	`dict`	Extra broker-specific fields (stored as JSON)

Returns: int - order_id

Example:

# Generate order ID
order_id = db.next_order_id()

# Create order record
db.insert_order(
    order_id=order_id,
    strategy_id=1,
    broker_id=1,
    account="U1234567",
    symbol="AAPL",
    side="BUY",
    order_type="LMT",
    total_quantity=100,
    limit_price=150.50,
    instrument_type="STOCK",
    client_reference="tech_basket_entry",
    metadata={
        "signal_type": "momentum",
        "entry_date": "2025-01-17"
    }
)

update_order_external_id(order_id, external_order_id) Update the broker-assigned permanent order ID. This links the Quantstrip order_id with the broker's order ID.

Parameters:

Parameter	Type	Description
`order_id`	`int`	Quantstrip order ID
`external_order_id`	`str`	Broker's permanent order ID

Example:

# After placing order with broker
db.update_order_external_id(order_id=order_id, external_order_id="1234567890")

get_order(order_id=None, strategy_id=None, symbol=None) Get order records with flexible filtering.

Parameters:

Parameter	Type	Description
`order_id`	`int`	Filter by order ID (optional)
`strategy_id`	`int`	Filter by strategy (optional)
`symbol`	`str`	Filter by symbol (optional)

Returns: pandas.DataFrame with order details

Example:

# Get specific order
order = db.get_order(order_id=163847291)

# Get all orders for a strategy and symbol
orders = db.get_order(strategy_id=1, symbol="AAPL")

# Get all orders for a strategy
strategy_orders = db.get_order(strategy_id=1)

# Get all orders
all_orders = db.get_order()

Order Status Tracking

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) Insert a new order status event. This does NOT update previous rows - every broker status transition becomes a new row for complete audit trail.

Parameters:

Parameter	Type	Description
`order_id`	`int`	Order ID (foreign key to orders table)
`status`	`str`	NEW, SUBMITTED, PARTIALLY_FILLED, FILLED, CANCELLED, REJECTED, etc.
`filled_quantity`	`float`	Total filled so far
`remaining_quantity`	`float`	Remaining amount
`avg_fill_price`	`float`	Weighted avg fill price so far
`last_fill_price`	`float`	Price of the last fill event
`external_order_id`	`str`	Broker's order ID
`client_reference`	`str`	Client-provided reference
`parent_order_id`	`int`	For brackets/OCO/etc.
`reason_held`	`str`	IBKR whyHeld (e.g., "locate")
`cap_price`	`float`	IBKR market cap price or Binance price cap
`metadata`	`dict`	Broker-specific extras (stored as JSON)
`event_time`	`str`	Broker timestamp (else defaults to now)

Example:

# When orderStatus callback is received
db.insert_order_status(
    order_id=order_id,
    status="SUBMITTED",
    filled_quantity=0,
    remaining_quantity=100
)

# When order partially fills
db.insert_order_status(
    order_id=order_id,
    status="PARTIALLY_FILLED",
    filled_quantity=50,
    remaining_quantity=50,
    avg_fill_price=150.48,
    last_fill_price=150.48
)

# When order completely fills
db.insert_order_status(
    order_id=order_id,
    status="FILLED",
    filled_quantity=100,
    remaining_quantity=0,
    avg_fill_price=150.48,
    last_fill_price=150.49
)

get_order_status(order_id=None) Get all order status events, newest first. Returns complete history of status changes.

Parameters:

Parameter	Type	Description
`order_id`	`int`	Filter by order ID (optional)

Returns: pandas.DataFrame with order status details

Example:

# Get status history for specific order
status_history = db.get_order_status(order_id=163847291)

# Get all order statuses
all_statuses = db.get_order_status()

get_current_order_status(order_id) Get only the most recent status for an order.

Parameters:

Parameter	Type	Description
`order_id`	`int`	Order ID to query

Returns: Single row (dict-like) with latest status, or None

Example:

current = db.get_current_order_status(order_id=163847291)
if current:
    print(f"Current status: {current['status']}")

Strategy Event Tracking

Strategy events track intended position changes before they are executed. Used for planning and reconciliation.

insert_strategy_event(order_id, position, event_type, group_label=None, status="PENDING", metadata=None) Insert a canonical strategy event snapshot.

Parameters:

Parameter	Type	Description
`order_id`	`int`	Associated order ID (unique constraint)
`position`	`float`	Intended position size
`event_type`	`str`	OPEN, CLOSE, OPEN/CLOSE, FLIP, PARTIAL_CLOSE, etc.
`group_label`	`str`	Optional label for grouping related events
`status`	`str`	PENDING, EXECUTED, FAILED
`metadata`	`dict`	Additional event data (stored as JSON)

Example:

db.insert_strategy_event(
    order_id=order_id,
    position=100,
    event_type="OPEN",
    group_label="tech_basket",
    status="PENDING",
    metadata={"signal": "momentum_crossover"}
)

get_last_strategy_event(strategy_id, symbol) Get the most recent strategy event for a strategy-symbol pair.

Parameters:

Parameter	Type	Description
`strategy_id`	`int`	Strategy ID
`symbol`	`str`	Trading symbol

Returns: Dictionary with event details, or None

Example:

last_event = db.get_last_strategy_event(strategy_id=1, symbol="AAPL")
if last_event:
    print(f"Last event: {last_event['event_type']} at {last_event['event_time']}")

Position Event Tracking

Position events track actual executed position changes with execution details. This is the realized position history.

insert_position_event(event_time, strategy_id, broker_id, exec_id, order_id, symbol, position, avg_price, trade_quantity, trade_price, event_type, group_label=None, metadata=None) Insert a canonical position event snapshot.

Parameters:

Parameter	Type	Description
`event_time`	`str`	Event timestamp
`strategy_id`	`int`	Strategy ID
`broker_id`	`int`	Broker ID
`exec_id`	`str`	Links to execution.exec_id (unique)
`order_id`	`int`	Order ID
`symbol`	`str`	Trading symbol
`position`	`float`	New position size after event
`avg_price`	`float`	Cost basis after event
`trade_quantity`	`float`	Signed qty: +buy, -sell
`trade_price`	`float`	Execution price
`event_type`	`str`	OPEN, CLOSE, OPEN/CLOSE, FLIP, PARTIAL_CLOSE, etc.
`group_label`	`str`	Optional grouping label
`metadata`	`dict`	Additional event data (stored as JSON)

Example:

db.insert_position_event(
    event_time="2025-01-17 14:30:45",
    strategy_id=1,
    broker_id=1,
    exec_id="0001f4e8.65a1b2c3.01.01",
    order_id=order_id,
    symbol="AAPL",
    position=100,
    avg_price=150.25,
    trade_quantity=100,
    trade_price=150.25,
    event_type="OPEN",
    group_label="tech_basket"
)

get_last_position_event(strategy_id, symbol) Get the most recent position event for a strategy-symbol pair.

Parameters:

Parameter	Type	Description
`strategy_id`	`int`	Strategy ID
`symbol`	`str`	Trading symbol

Returns: Dictionary with event details, or None

Example:

last_position = db.get_last_position_event(strategy_id=1, symbol="AAPL")
if last_position:
    print(f"Current position: {last_position['position']} @ {last_position['avg_price']}")

External Position Management

External positions track actual positions reported by your broker. This table stores the raw position data from the broker API for reconciliation.

insert_position_external(account, contract_id, position, avg_cost, info_1="", info_2="", info_3="", info_4="", info_5="") Records a position update from the broker. Typically called when receiving position callbacks from broker API.

Parameters:

Parameter	Type	Description
`account`	`str`	Broker account number
`contract_id`	`int`	Broker contract ID
`position`	`float`	Current position size
`avg_cost`	`float`	Average cost basis
`info_1` to `info_5`	`str`	Custom metadata fields

Example:

# When receiving broker position callback
db.insert_position_external(
    account="U1234567",
    contract_id=12345,
    position=100,
    avg_cost=150.25,
    info_1="AAPL",
    info_2="STOCK"
)

get_positions_external(account=None) Retrieves external positions, optionally filtered by account.

Parameters:

Parameter	Type	Description
`account`	`str`	Filter by specific account (optional)

Returns: pandas.DataFrame with external positions

Example:

# Get all external positions
all_ext_positions = db.get_positions_external()

# Get positions for specific account
account_positions = db.get_positions_external(account="U1234567")

Execution Tracking

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, metadata=None) Records a trade execution with full details. This should be called from execution callbacks.

Parameters:

Parameter	Type	Description
`exec_id`	`str`	Unique execution ID
`order_id`	`int`	Quantstrip order ID (foreign key)
`strategy_id`	`int`	Strategy ID
`broker_id`	`int`	Broker ID
`symbol`	`str`	Trading symbol (e.g., SPY, AAPL, BTCUSDT)
`instrument_type`	`str`	STOCK, CRYPTO, FUTURE, OPTION
`contract_id`	`str`	IB conId, Binance alt ID
`side`	`str`	BUY or SELL
`quantity`	`float`	Execution quantity (always positive)
`price`	`float`	Execution price
`exec_time`	`str`	Execution timestamp
`exchange`	`str`	NYSE, ARCA, BINANCE, etc.
`liquidity_flag`	`str`	MAKER, TAKER, etc.
`order_type`	`str`	MKT, LMT, IOC, FOK
`cum_qty`	`float`	Cumulative filled quantity
`avg_price`	`float`	Broker-reported average price
`is_liquidation`	`int`	Forced liquidation flag (0 or 1)
`external_order_id`	`str`	Broker's order ID
`external_exec_id`	`str`	Broker's execution ID
`metadata`	`dict`	Additional data (stored as JSON)

Example:

# When receiving broker execDetails callback
db.insert_execution(
    exec_id="0001f4e8.65a1b2c3.01.01",
    order_id=order_id,
    strategy_id=1,
    broker_id=1,
    symbol="AAPL",
    instrument_type="STOCK",
    contract_id="265598",
    side="BUY",
    quantity=100,
    price=150.25,
    exec_time="2025-01-17 14:30:45",
    exchange="SMART",
    liquidity_flag="TAKER",
    order_type="LMT",
    cum_qty=100,
    avg_price=150.25,
    is_liquidation=0,
    external_order_id="1234567890",
    external_exec_id="0001f4e8.65a1b2c3.01.01"
)

get_executions(order_id=None, exec_id=None) Retrieves execution records with optional filtering.

Parameters:

Parameter	Type	Description
`order_id`	`int`	Filter by order ID (optional)
`exec_id`	`str`	Filter by execution ID (optional)

Returns: pandas.DataFrame with execution details

Example:

# Get all executions for an order
order_execs = db.get_executions(order_id=163847291)

# Get specific execution
specific_exec = db.get_executions(exec_id="0001f4e8.65a1b2c3.01.01")

# Get all executions
all_execs = db.get_executions()

Commission Tracking

insert_commission(exec_id, amount, currency, fee_type, realized_pnl=None, metadata=None) Records commission and fees for a specific execution. Called when receiving broker commission reports.

Parameters:

Parameter	Type	Description
`exec_id`	`str`	Links to execution record
`amount`	`float`	Commission/fee amount
`currency`	`str`	Commission currency (e.g., "USD")
`fee_type`	`str`	Commission type identifier
`realized_pnl`	`float`	Realized profit/loss
`metadata`	`dict`	Additional data (stored as JSON)

Example:

db.insert_commission(
    exec_id="0001f4e8.65a1b2c3.01.01",
    amount=1.25,
    currency="USD",
    fee_type="commission",
    realized_pnl=523.50,
    metadata={"rate": "tiered"}
)

Broker Management

insert_broker(name, full_name) Adds a new broker to the system.

Parameters:

Parameter	Type	Description
`name`	`str`	Short broker identifier (e.g., "IBKR")
`full_name`	`str`	Full broker name

Example:

db.insert_broker(
    name="IBKR",
    full_name="Interactive Brokers"
)

update_broker(broker_id, name, full_name) Updates an existing broker's details.

Example:

db.update_broker(
    broker_id=1,
    name="IBKR",
    full_name="Interactive Brokers LLC"
)

delete_broker(broker_id) Removes a broker from the system.

Example:

db.delete_broker(broker_id=2)

get_brokers() Returns all registered brokers.

Returns: pandas.DataFrame with broker_id, name, and full_name

Example:

brokers = db.get_brokers()
print(brokers)

get_broker_id(name) Looks up a broker's ID by name.

Returns: int or None

Example:

broker_id = db.get_broker_id("IBKR")

Strategy Management

get_strategies() Returns all registered trading strategies.

Returns: pandas.DataFrame with strategy_id, name, and description

Example:

strategies = db.get_strategies()
for idx, row in strategies.iterrows():
    print(f"{row['name']}: {row['description']}")

get_strategy_id(name) Looks up a strategy's ID by name.

Returns: int or None

Example:

strategy_id = db.get_strategy_id("momentum_v2")

Reporting System

The reporting system provides a hierarchical structure for generating trade reports: Report → Sections → Items.

insert_report(name, date, status, report_type, recipients) Creates a new report container. This is the top-level object for organizing report content.

Parameters:

Parameter	Type	Description
`name`	`str`	Report title
`date`	`str`	Report date
`status`	`str`	Status (e.g., "draft", "final", "sent")
`report_type`	`str`	Report type (e.g., "daily", "monthly")
`recipients`	`str`	Comma-separated recipient list

Returns: int - report_id

Example:

report_id = db.insert_report(
    name="Daily Trading Report - Jan 17",
    date="2025-01-17",
    status="draft",
    report_type="daily",
    recipients="trader@example.com,manager@example.com"
)

update_report(report_id, name=None, date=None, status=None, report_type=None, recipients=None) Updates report fields. Only provided parameters are updated.

Example:

# Mark report as final
db.update_report(report_id=5, status="final")

# Update multiple fields
db.update_report(
    report_id=5,
    status="sent",
    recipients="trader@example.com,compliance@example.com"
)

get_reports(report_id=None) Retrieves reports, optionally filtered by ID. Results are ordered by date (newest first).

Example:

# Get all reports
all_reports = db.get_reports()

# Get specific report
report = db.get_reports(report_id=5)

insert_report_section(report_id, name, section_type) Adds a section to a report. Sections organize content by category (e.g., "Summary", "Risk Analysis", "Trade List").

Parameters:

Parameter	Type	Description
`report_id`	`int`	Parent report ID
`name`	`str`	Section title
`section_type`	`str`	Section type identifier

Returns: int - section_id

Example:

summary_section = db.insert_report_section(
    report_id=5,
    name="Executive Summary",
    section_type="summary"
)

trades_section = db.insert_report_section(
    report_id=5,
    name="Today's Trades",
    section_type="trade_list"
)

get_report_sections(report_id) Retrieves all sections for a report, ordered by section_id.

Example:

sections = db.get_report_sections(report_id=5)
for idx, section in sections.iterrows():
    print(f"Section: {section['name']}")

insert_report_item(section_id, text) Adds content to a report section. Items contain the actual text/data.

Parameters:

Parameter	Type	Description
`section_id`	`int`	Parent section ID
`text`	`str`	Content text (can be markdown, JSON, etc.)

Returns: int - item_id

Example:

db.insert_report_item(
    section_id=summary_section,
    text="Traded 5 symbols today with 100% fill rate."
)

db.insert_report_item(
    section_id=trades_section,
    text="AAPL: BUY 100 @ $150.25"
)

get_report_items(section_id) Retrieves all items for a section, ordered by item_id.

Example:

items = db.get_report_items(section_id=summary_section)
for idx, item in items.iterrows():
    print(item['text'])

Email Account Management

The email account system manages SMTP configurations for sending reports and notifications.

get_all_email_accounts() Get all configured email accounts.

Returns: pandas.DataFrame with all email account details

Example:

accounts = db.get_all_email_accounts()
for idx, account in accounts.iterrows():
    print(f"{account['name']}: {account['from_address']}")

get_email_account_by_id(email_account_id) Get a specific email account by ID.

Parameters:

Parameter	Type	Description
`email_account_id`	`int`	Email account ID

Returns: Dictionary with account details, or None

Example:

account = db.get_email_account_by_id(1)
if account:
    print(f"SMTP: {account['smtp_server']}:{account['smtp_port']}")

get_email_account_by_name(name) Get a specific email account by name.

Parameters:

Parameter	Type	Description
`name`	`str`	Email account name

Returns: Dictionary with account details, or None

Example:

account = db.get_email_account_by_name("Gmail Trading")

get_default_email_account() Get the default email account.

Returns: Dictionary with account details, or None

Example:

default_account = db.get_default_email_account()
if default_account:
    print(f"Default: {default_account['from_address']}")

Complete Report Example

# Create a daily report
report_id = db.insert_report(
    name="Daily Report - Jan 17, 2025",
    date="2025-01-17",
    status="draft",
    report_type="daily",
    recipients="team@example.com"
)

# Add summary section
summary = db.insert_report_section(
    report_id=report_id,
    name="Executive Summary",
    section_type="summary"
)

db.insert_report_item(
    section_id=summary,
    text="Total P&L: $1,234.56"
)

db.insert_report_item(
    section_id=summary,
    text="Win rate: 65%"
)

# Add trades section
trades = db.insert_report_section(
    report_id=report_id,
    name="Executed Trades",
    section_type="trades"
)

# Get executions for the day
executions = db.get_executions()
for idx, exec in executions.iterrows():
    db.insert_report_item(
        section_id=trades,
        text=f"{exec['side']} {exec['quantity']} {exec['symbol']} @ ${exec['price']}"
    )

# Finalize report
db.update_report(report_id=report_id, status="final")

Complete Order Workflow Example

Here's a complete example showing the typical order lifecycle with all database operations:

from quantstrip import db_handler as db

# 1. Generate order ID
order_id = db.next_order_id()  # e.g., 163847291

# 2. Create order record
db.insert_order(
    order_id=order_id,
    strategy_id=1,
    broker_id=1,
    account="U1234567",
    symbol="AAPL",
    side="BUY",
    order_type="LMT",
    total_quantity=100,
    limit_price=150.50,
    instrument_type="STOCK",
    client_reference="tech_basket_entry",
    metadata={
        "signal_type": "momentum",
        "entry_date": "2025-01-17"
    }
)

# 3. Create strategy event (planned position change)
db.insert_strategy_event(
    order_id=order_id,
    position=100,
    event_type="OPEN",
    group_label="tech_basket",
    status="PENDING",
    metadata={"signal": "momentum_crossover"}
)

# 4. After placing order with broker, update external_order_id
db.update_order_external_id(order_id=order_id, external_order_id="1234567890")

# 5. When orderStatus callback is received (order submitted)
db.insert_order_status(
    order_id=order_id,
    status="SUBMITTED",
    filled_quantity=0,
    remaining_quantity=100,
    external_order_id="1234567890"
)

# 6. When order partially fills (orderStatus callback)
db.insert_order_status(
    order_id=order_id,
    status="PARTIALLY_FILLED",
    filled_quantity=50,
    remaining_quantity=50,
    avg_fill_price=150.48,
    last_fill_price=150.48
)

# 7. When execDetails callback is received (first fill)
db.insert_execution(
    exec_id="0001f4e8.65a1b2c3.01.01",
    order_id=order_id,
    strategy_id=1,
    broker_id=1,
    symbol="AAPL",
    instrument_type="STOCK",
    contract_id="265598",
    side="BUY",
    quantity=50,
    price=150.48,
    exec_time="2025-01-17 14:30:45",
    exchange="SMART",
    liquidity_flag="TAKER",
    order_type="LMT",
    cum_qty=50,
    avg_price=150.48,
    external_order_id="1234567890",
    external_exec_id="0001f4e8.65a1b2c3.01.01"
)

# 8. When commissionReport callback is received
db.insert_commission(
    exec_id="0001f4e8.65a1b2c3.01.01",
    amount=0.50,
    currency="USD",
    fee_type="commission",
    realized_pnl=0
)

# 9. Update position event (actual position change)
db.insert_position_event(
    event_time="2025-01-17 14:30:45",
    strategy_id=1,
    broker_id=1,
    exec_id="0001f4e8.65a1b2c3.01.01",
    order_id=order_id,
    symbol="AAPL",
    position=50,
    avg_price=150.48,
    trade_quantity=50,
    trade_price=150.48,
    event_type="OPEN",
    group_label="tech_basket"
)

# 10. When order completely fills (orderStatus callback)
db.insert_order_status(
    order_id=order_id,
    status="FILLED",
    filled_quantity=100,
    remaining_quantity=0,
    avg_fill_price=150.485,
    last_fill_price=150.49
)

# 11. Record second execution
db.insert_execution(
    exec_id="0001f4e8.65a1b2c3.01.02",
    order_id=order_id,
    strategy_id=1,
    broker_id=1,
    symbol="AAPL",
    instrument_type="STOCK",
    contract_id="265598",
    side="BUY",
    quantity=50,
    price=150.49,
    exec_time="2025-01-17 14:31:12",
    exchange="SMART",
    liquidity_flag="TAKER",
    order_type="LMT",
    cum_qty=100,
    avg_price=150.485,
    external_order_id="1234567890",
    external_exec_id="0001f4e8.65a1b2c3.01.02"
)

# 12. Update final position event
db.insert_position_event(
    event_time="2025-01-17 14:31:12",
    strategy_id=1,
    broker_id=1,
    exec_id="0001f4e8.65a1b2c3.01.02",
    order_id=order_id,
    symbol="AAPL",
    position=100,
    avg_price=150.485,
    trade_quantity=50,
    trade_price=150.49,
    event_type="OPEN",
    group_label="tech_basket"
)

# 13. Mark strategy event as executed
# (Note: This would require an update method for strategy_event,
#  which is not currently in the code but would be useful)

# 14. Query current position
last_position = db.get_last_position_event(strategy_id=1, symbol="AAPL")
print(f"Current position: {last_position['position']} @ {last_position['avg_price']}")

Position Reconciliation Example

Compare internal positions (strategy events and position events) with external positions from broker:

# Get internal position from position_event table
internal_position = db.get_last_position_event(strategy_id=1, symbol="AAPL")

# Get external position from broker
external_positions = db.get_positions_external(account="U1234567")
external_aapl = external_positions[external_positions['info_1'] == 'AAPL']

# Compare
if internal_position:
    internal_qty = internal_position['position']
    internal_avg = internal_position['avg_price']

    if not external_aapl.empty:
        external_qty = external_aapl.iloc[0]['position']
        external_avg = external_aapl.iloc[0]['avg_cost']

        if abs(internal_qty - external_qty) > 0.01:
            print(f"⚠️  Position mismatch for AAPL!")
            print(f"   Internal: {internal_qty} @ {internal_avg}")
            print(f"   External: {external_qty} @ {external_avg}")
        else:
            print(f"✓ Positions reconciled: {internal_qty} shares")

Database Tables Reference

Core Trading Tables

Table	Purpose
`orders`	Canonical order records with Quantstrip order_id
`order_status`	Complete history of order status changes (append-only)
`execution`	Individual trade executions with full details
`commission`	Commission and fee records linked to executions
`position_event`	Actual realized position changes (linked to executions)
`strategy_event`	Planned position changes (intent before execution)
`position_external`	Raw broker-reported positions for reconciliation

Reference Tables

Table	Purpose
`broker`	Registered brokers (IBKR, Binance, etc.)
`strategy`	Trading strategies
`order_id_state`	Order ID generation state (singleton record)
`mtm_price`	Mark-to-market prices for valuation

Reporting Tables

Table	Purpose
`report`	Report containers
`report_section`	Report sections within reports
`report_item`	Individual content items within sections
`email_account`	SMTP configurations for sending reports

Key Design Patterns

Event-Based Position Tracking

The system uses two complementary position tracking mechanisms:

Strategy Events (strategy_event table)
Records intended position changes
Created when strategy signals a trade
Status: PENDING → EXECUTED/FAILED
Used for planning and verification
Position Events (position_event table)
Records actual position changes
Created when executions occur
Linked to specific executions via exec_id
Provides complete audit trail of realized positions

This dual-tracking allows you to: - Compare intended vs. actual positions - Track execution slippage - Identify failed orders - Maintain complete position history

Append-Only Order Status

The order_status table is append-only - every status change creates a new row rather than updating existing rows. This provides:

Complete audit trail of order lifecycle
Ability to analyze timing of status changes
No data loss from updates
Easy debugging of order issues

Use get_current_order_status() to get the latest status, or get_order_status() to see complete history.

JSON Metadata Storage

Many tables include a metadata field that stores arbitrary JSON data. This allows:

Broker-specific fields without schema changes
Custom strategy parameters
Flexible data storage for evolving needs
Easy addition of new fields

Example:

metadata = {
    "algo_name": "VWAP",
    "urgency": "normal",
    "broker_specific_field": "value"
}

db.insert_order(
    order_id=order_id,
    # ... other fields ...
    metadata=metadata
)

Thread Safety and Connection Pooling

The DBHandler is a thread-safe singleton with connection pooling:

Singleton Pattern: Only one instance per application
Connection Pool: Pre-created connections for efficiency (pool size: 10)
Context Managers: Automatic connection lifecycle management
Lock Protection: Thread-safe order ID generation
WAL Mode: Write-Ahead Logging for better concurrency

Best Practices: - Always use get_connection() context manager for custom queries - Use the built-in methods for common operations - Don't hold connections longer than necessary - Connection pool automatically handles overflow with temporary connections