DB Clean Rewrite — 2026-05-03

Status: Breaking change. Operators on a pre-2026-05-03 binary MUST follow this runbook before upgrading.

TL;DR

The historical V1 → V7 (state DB) and V4 → V8 (journal DB) migration ladders have been deleted and replaced with a single-shot installer. The new binary refuses to run against any SQLite file stamped at a legacy schema_version (V2..V8) and prints a pointer to this document. The fix: back up your old .db files and start fresh.

Why

TD-22 surfaced a structural conflict between the two ladders:

• db::schema::CURRENT_VERSION was 7, applying migrations V1..V7.
• journal::CURRENT_VERSION was 8, applying migrations V4..V8 plus re-running V1..V7 via db::schema::migrate(conn).
• Both stamped the same schema_version row. Production DBs ended up at a 2-ladder amalgam where the journal layer had already bumped schema_version = 8 while the base layer's idempotency check (from >= CURRENT_VERSION) silently skipped V4..V7.

A probe-based reconcile would have cost ~200 LOC of forensic SQL (PRAGMA table_info, SELECT name FROM sqlite_master WHERE type='index') plus a dedicated test matrix per ladder permutation. The clean rewrite collapses both ladders into a single init_schema + init_journal_schema pair stamped at schema_version = 1 (new "clean" semantic).

Trade-off: existing .db files are no longer usable. Trading-history data (orders_history, fills, positions_history, baskets, basket_legs, processed_trades, bot_kv_state) must be exported with sqlite3 before the file is dropped.

What ships in the new binary

Layer	Tables
Base (db::schema::init_schema)	orders_history, fills, positions_history, price_snapshots, strategy_stats, baskets, basket_legs, processed_trades, bot_kv_state
Journal (journal::init_journal_schema)	bot_sessions (with mode column), event_log, decisions, signals, pnl_snapshots, latency_snapshots, event_counters_snapshots, basket_states, view v_session_kpi (with win_rate + avg_latency_p95)
Bookkeeping	schema_version (single integer row, value 1)

Columns formerly added by V5 ALTERs ship as part of the initial CREATE: orders_history.session_id, orders_history.basket_id, fills.session_id, fills.fee_usd, fills.neg_risk, positions_history.session_id, positions_history.current_unrealized_pnl_usd, positions_history.max_drawdown_usd. bot_sessions.mode (formerly V7) is on the initial CREATE with DEFAULT 'live'.

What was removed

• All migrate_v0_to_v1 … migrate_v6_to_v7 functions in db/schema.rs.
• All journal/schema_v4.rs … journal/schema_v8.rs files.
• _archived_weather_observations, _archived_trade_weather_context, _archived_station_daily_state are NOT installed (legacy archive snapshots — operators that still need this data can extract it from the backed-up .db file).
• Per-version migration tests (test_v3_to_v4_preserves_data_in_archive, test_v4_to_v5_no_op_marker, test_v5_to_v6_archives_station_daily_state, test_v6_to_v7_creates_bot_kv_state, test_v1_to_v7_migration_from_v1, test_station_daily_state_insert_and_upsert_pre_v6).

Upgrade procedure (operator)

1. Stop the bot

# whatever your supervisor uses; example for systemd:
systemctl --user stop pm-bot
# or for a tmux/foreground run, send Ctrl-C and wait for graceful shutdown

2. Back up the existing DB files

cd ~/path/to/polymarket-engine     # wherever you run pm-bot from
ts=$(date -u +%Y%m%d-%H%M%S)
cp pm-bot.sqlite     "pm-bot.sqlite.backup-${ts}"
cp pm-bot.sqlite-wal "pm-bot.sqlite-wal.backup-${ts}" 2>/dev/null || true
cp pm-bot.sqlite-shm "pm-bot.sqlite-shm.backup-${ts}" 2>/dev/null || true

WAL/SHM may be absent if pm-bot was shut down cleanly — that's fine.

3. (Optional) Extract trading history before drop

If you want to keep historical trading rows from the legacy DB (this is observability data; pm-bot itself does not require it for restart-recovery), dump them to CSV:

sqlite3 "pm-bot.sqlite.backup-${ts}" <<'EOF'
.headers on
.mode csv
.output orders_history.csv
SELECT * FROM orders_history;
.output fills.csv
SELECT * FROM fills;
.output positions_history.csv
SELECT * FROM positions_history;
.output baskets.csv
SELECT * FROM baskets;
.output basket_legs.csv
SELECT * FROM basket_legs;
.output bot_kv_state.csv
SELECT * FROM bot_kv_state;
.output stdout
EOF

Open positions and resting CLOB orders are NOT recovered from the .db file in normal operation — pm-bot reconciles them via the WS user channel + GET /v1/orders on startup. So a fresh DB does not lose trading state, only journaled history.

4. Drop the DB files

rm pm-bot.sqlite pm-bot.sqlite-wal pm-bot.sqlite-shm
# (the -wal / -shm files may not exist; that's fine)

5. Start the new binary

cargo run -p pm-bot -- --strategy <your-strategy>
# or from the deployed release binary:
./pm-bot --strategy <your-strategy>

The startup log will include journal migrations V4-V6 failed only if the upgrade was incomplete (e.g. you skipped step 4); a clean install logs the standard SessionManager startup line and the trading hot path resumes.

Verification

sqlite3 pm-bot.sqlite "SELECT version FROM schema_version;"
# expected: 1

sqlite3 pm-bot.sqlite "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;"
# expected: baskets, basket_legs, basket_states, bot_kv_state, bot_sessions,
# decisions, event_counters_snapshots, event_log, fills, latency_snapshots,
# orders_history, pnl_snapshots, positions_history, price_snapshots,
# processed_trades, schema_version, signals, sqlite_sequence, strategy_stats

sqlite3 pm-bot.sqlite "SELECT name FROM sqlite_master WHERE type='view';"
# expected: v_session_kpi

Rollback

If you need to revert to the legacy ladder binary:

1. Stop the new pm-bot.
2. rm pm-bot.sqlite pm-bot.sqlite-wal pm-bot.sqlite-shm.
3. Restore the backup: cp pm-bot.sqlite.backup-${ts} pm-bot.sqlite (and the WAL/SHM siblings if you have them).
4. Run the legacy binary — it will see schema_version = 8 (or whatever your last legacy stamp was) and short-circuit to no-op.

The new binary will refuse to attach to a restored backup until you drop it again, by design — silent re-install on top of legacy _archived_* tables would produce a hybrid schema.

What this resolves

• TD-19 (station_daily_state retire) — closed by removal in init_schema.
• TD-22 (db / journal version-row conflict) — closed by collapsing both to a single schema_version = 1 stamp.

What this does NOT change

• The wire contract for BotEvent (in pm-bot-events) — schema version, payload shape, SSE replay semantics are all unchanged.
• BOT_LOCAL_API_* env vars and the /local dashboard route.
• CLOB / Gamma / WS integration paths.
• Strategy code, decision pipeline, executor adapters.

The change is bounded to persistence-layer DDL + the schema_version bookkeeping row.