Persistence

SQLite persistence for crash recovery, position snapshots, and audit trail.

Horizon uses SQLite with WAL mode for crash recovery and an audit trail. All fills, order events, position snapshots, and risk events are persisted automatically.

Quick Start

Persistence is enabled by default. When you call hz.run(name="my_strategy"), a database is created at ./my_strategy.db.

# Default: creates ./my_strategy.db
hz.run(name="my_strategy", ...)

# Custom path
hz.run(name="my_strategy", db_path="/data/horizon.db", ...)

# Disable persistence
hz.run(name="my_strategy", db_path=None, ...)

Configuration

Method	Behavior
db_path not specified	Uses HORIZON_DB env var, or ./{name}.db
db_path="/path/to/db"	Uses the specified path
db_path=None	Disables persistence entirely
HORIZON_DB env var	Overrides the default path

SQLite Schema

The database contains five tables:

fills - Append-only fill journal

Source of truth for position reconstruction. Uses INSERT OR IGNORE on fill_id for idempotent dedup.

CREATE TABLE fills (
    fill_id TEXT PRIMARY KEY,
    order_id TEXT NOT NULL,
    market_id TEXT NOT NULL,
    side TEXT NOT NULL,
    order_side TEXT NOT NULL,
    price REAL NOT NULL,
    size REAL NOT NULL,
    fee REAL NOT NULL DEFAULT 0.0,
    timestamp REAL NOT NULL,
    token_id TEXT,
    exchange TEXT NOT NULL DEFAULT '',
    inserted_at REAL NOT NULL
);

order_events - Order lifecycle log

One row per status change (append-only).

CREATE TABLE order_events (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    order_id TEXT NOT NULL,
    market_id TEXT NOT NULL,
    side TEXT NOT NULL,
    order_side TEXT NOT NULL,
    price REAL NOT NULL,
    size REAL NOT NULL,
    filled_size REAL NOT NULL DEFAULT 0.0,
    remaining_size REAL NOT NULL,
    order_type TEXT NOT NULL,
    time_in_force TEXT NOT NULL,
    status TEXT NOT NULL,
    status_reason TEXT,
    exchange TEXT NOT NULL DEFAULT '',
    created_at REAL NOT NULL,
    recorded_at REAL NOT NULL
);

position_snapshots - Periodic snapshots

Periodic snapshots for fast recovery. Each snapshot is a batch of positions with a shared timestamp.

CREATE TABLE position_snapshots (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    snapshot_batch REAL NOT NULL,
    market_id TEXT NOT NULL,
    side TEXT NOT NULL,
    size REAL NOT NULL,
    avg_entry_price REAL NOT NULL,
    realized_pnl REAL NOT NULL,
    unrealized_pnl REAL NOT NULL,
    token_id TEXT,
    exchange TEXT NOT NULL DEFAULT ''
);

risk_events - Risk event log

Kill switch activations, deactivations, and violations.

CREATE TABLE risk_events (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    event_type TEXT NOT NULL,
    details TEXT,
    timestamp REAL NOT NULL
);

strategy_runs - Run audit trail

Records strategy run start/end with exchange info.

CREATE TABLE strategy_runs (
    run_id TEXT PRIMARY KEY,
    strategy_name TEXT NOT NULL,
    exchange TEXT NOT NULL,
    started_at REAL NOT NULL,
    ended_at REAL,
    config TEXT
);

Crash Recovery

Recovery happens automatically on startup when persistence is enabled:

1. Load latest snapshot

   The engine loads the most recent position snapshot from position_snapshots.

2. Replay fills

   Fills from the fills table with timestamp >= snapshot_timestamp are replayed to update positions.

3. Detect orphaned orders

   Orders that were open when the previous run ended are detected and logged as warnings.

# Manual recovery (when using Engine directly)
engine = Engine(db_path="./my_strategy.db")
recovered = engine.recover_state()
print(f"Recovered {recovered} positions")

# Check for orphaned orders
orphaned = engine.db_open_order_ids()

Engine Persistence Methods

# Snapshot positions to DB
count = engine.snapshot_positions()

# Recover state from DB
count = engine.recover_state()

# Record strategy run start/end
engine.start_run("my_strategy")
engine.end_run()

# Check persistence status
engine.has_persistence()  # True/False

# Get current run ID
engine.db_run_id()  # UUID string

# Get orphaned order IDs
engine.db_open_order_ids()  # list[str]

Automatic Behavior

When hz.run() manages the engine:

Event	Action
Startup	recover_state(): load snapshot + replay fills
Every 50 cycles	snapshot_positions(): save current positions
Shutdown	snapshot_positions() + end_run()
Engine drop	Final snapshot + end run + cancel all

Performance

The database is configured for low-latency trading workloads:

PRAGMA journal_mode = WAL;       -- Write-ahead logging
PRAGMA synchronous = NORMAL;     -- Balanced durability/speed
PRAGMA cache_size = -8000;       -- 8MB cache
PRAGMA busy_timeout = 5000;      -- 5s busy retry
PRAGMA temp_store = MEMORY;      -- In-memory temp tables
PRAGMA mmap_size = 268435456;    -- 256MB memory-mapped I/O

Hot-path queries use prepare_cached for prepared statement reuse.

CLI Database Commands

Query the database from the command line:

# Show recent fills
python -m horizon fills --db ./my_strategy.db --limit 50

# Show fills for a specific market
python -m horizon fills --db ./my_strategy.db --market will-btc-hit-100k

# Show latest position snapshot
python -m horizon positions --db ./my_strategy.db

# Show orders (open only by default)
python -m horizon orders --db ./my_strategy.db
python -m horizon orders --db ./my_strategy.db --all