database-sqlite

name	database-sqlite
description	SQLite best practices, connection management, and migration system for PolyFlup.

Database Best Practices

Connection Management

ALWAYS use the db_connection() context manager:

from src.data.db_connection import db_connection

with db_connection() as conn:
    c = conn.cursor()
    c.execute("SELECT * FROM trades")
    # Commit happens automatically on success

• NEVER call conn.commit() manually.
• Deadlock Prevention: When calling write functions (like execute_trade) from within an existing transaction, MUST pass the active cursor.

Migration System

The migration system tracks versions in the schema_version table.

Adding a New Migration:

1. Create a migration function in src/data/migrations.py.
2. Register it in the MIGRATIONS list.
3. Update the base schema in src/data/database.py.

Rules:

• Check if columns/indices exist before creating.
• Migrations must be idempotent.
• Never delete or modify existing migrations.

Schema Overview

Main table: trades

• Core Fields: id, timestamp, symbol, side, entry_price, size, bet_usd, edge
• Order Tracking: order_id, order_status, limit_sell_order_id, scale_in_order_id
• Position Management: scaled_in, is_reversal, target_price, reversal_triggered, reversal_triggered_at
• Settlement: settled, settled_at, exited_early, final_outcome, exit_price, pnl_usd, roi_pct
• Timing: window_start, window_end, last_scale_in_at
• Market Data: slug, token_id, p_yes, best_bid, best_ask, imbalance, funding_bias
• Bayesian Comparison (v0.5.0+): additive_confidence, additive_bias, bayesian_confidence, bayesian_bias, market_prior_p_up

Key Database Patterns

Position Queries

# Get open positions with all relevant data
c.execute("""
    SELECT id, symbol, token_id, side, entry_price, size, bet_usd, 
           limit_sell_order_id, scale_in_order_id, scaled_in, edge, 
           last_scale_in_at, window_end
    FROM trades 
    WHERE settled = 0 AND exited_early = 0 
    AND datetime(window_end) > datetime(?)
""", (now.isoformat(),))

Trade Updates

# Update position size after scale-in
c.execute("""
    UPDATE trades 
    SET size = ?, bet_usd = ? * entry_price, 
        scaled_in = 1, last_scale_in_at = ?
    WHERE id = ?
""", (new_size, new_size, now.isoformat(), trade_id))

Settlement

# Settle position with exit data
c.execute("""
    UPDATE trades 
    SET settled = 1, exited_early = 1, exit_price = ?, 
        pnl_usd = ?, roi_pct = ?, settled_at = ?
    WHERE id = ?
""", (exit_price, pnl_usd, roi_pct, now.isoformat(), trade_id))

WAL Mode

• Database uses Write-Ahead Logging (WAL) mode for better concurrency
• Enabled on initialization: PRAGMA journal_mode=WAL
• Allows concurrent readers while writes are in progress

Migration History

• Migration 007 (v0.5.0): Added Bayesian confidence comparison columns (additive_confidence, additive_bias, bayesian_confidence, bayesian_bias, market_prior_p_up) for A/B testing
• Migration 006 (v0.4.x): Added raw signal score columns (up_total, down_total, momentum_score, momentum_dir, flow_score, flow_dir, divergence_score, divergence_dir, vwm_score, vwm_dir, pm_mom_score, pm_mom_dir, adx_score, adx_dir, lead_lag_bonus) for confidence formula calibration
• Migration 005: Added last_scale_in_at column for tracking scale-in timing
• Migration 004: Added reversal_triggered_at column for timing reversals
• Migration 003: Added reversal_triggered column for reversal tracking
• Migration 002: Added timestamp verification
• Migration 001: Added scale_in_order_id column for tracking pending scale-in orders