Journal Retention — Operator Runbook (TD-11)

Status: Shipped 2026-05-03 — closes TD-11. Audience: Operators running pm-bot in production or against a long-lived local DB. Cross-references:local-dashboard-runbook.md §6.4 · tech-debt-backlog.md TD-11 · db-clean-rewrite-2026-05-03.md

---

1. Why this exists

The pm-bot SQLite file (BOT_STATE_DB_PATH, default pm-bot-state.db) holds three tables that grow continuously:

Table	Source	Steady-state growth
price_snapshots	Live orderbook + position fair-value sampler	~1 GB/day at 1 Hz × dozens of positions
event_log	ADR-005 journal — every BotEvent (Fill, OrderPlaced, OrderCancelled, MetricsTick × 1 Hz, RTDS pressure, …)	~100–500 MB/day depending on strategy
*_snapshots rollups (pnl_snapshots, latency_snapshots, event_counters_snapshots)	metrics_loop @ 1 Hz	~50 MB/day

Without retention the file becomes unmanageable:

• WAL checkpoints take longer.
• VACUUM (manual or auto) takes minutes.
• Backups blow out.
• Operators end up dropping the file entirely, losing recent journal.

TD-11 ships two coordinated paths so retention is automatic when the operator opts in, and manually triggerable when they don't.

2. Retention policy (defaults)

Knob	Default	Notes
Retention window	7 days	bot_sessions.ended_at older than now - 7d is pruned.
Active session safety	always retained	ended_at IS NULL is NEVER deleted, regardless of started_at.
GC cadence	24 h (scheduler only)	First tick fires interval after spawn — never on startup.
VACUUM after delete	true	Skip with --no-vacuum (CLI) or BOT_JOURNAL_AUTO_PRUNE_VACUUM=false (scheduler) on huge DBs.
Dry-run	available on CLI	--dry-run reports what would be deleted without committing.

3. Manual operator path — pm-cli journal gc

Shipped 2026-05-03. Lives at crates/pm-cli/src/journal.rs and routes through the canonical pm_bot::persistence::journal::gc::run_gc primitive (single source of truth).

# Default: 7d retention, VACUUM enabled.
pm-cli journal gc --older-than 7d

# Audit first (no commits).
pm-cli journal gc --older-than 7d --dry-run

# Custom DB path (otherwise reads BOT_STATE_DB_PATH or default).
pm-cli journal gc --db-path /var/data/pm-bot.sqlite --older-than 14d

# Skip VACUUM on a >100 GB journal where reclaim would stall for minutes.
pm-cli journal gc --older-than 7d --no-vacuum

Duration parser accepts <N>d / <N>h / <N>m / <N>s. Negative or zero values are rejected — the CLI refuses configurations that would purge live data.

The CLI prints a structured report:

== pm-cli journal --gc report ==
DB:              /var/data/pm-bot.sqlite
Cutoff:          2026-04-26T12:00:00+00:00
Sessions:        DELETED 14 session(s)
Child rows:      DELETED 8,234,901 row(s) total
Per table:
  event_log                              4,512,003
  decisions                                 12,901
  signals                                    8,234
  pnl_snapshots                             86,400
  latency_snapshots                         86,400
  event_counters_snapshots                  86,400
  basket_states                                 78
  orders_history                             1,245
  fills                                      4,012
  positions_history                            234
  price_snapshots                        3,437,000
  bot_sessions                                  14
Bytes before:    52.34 GiB
Bytes after:     3.21 GiB
Bytes reclaimed: 49.13 GiB
Duration:        12,431 ms

4. Scheduler path — opt-in BOT_JOURNAL_AUTO_PRUNE_*

For unattended deployments, enable the in-process scheduler so retention runs without operator intervention. Lives at crates/pm-bot/src/application/auto_prune.rs.

4.1 Activation

Add to .env:

# Master switch. Default false ⇒ scheduler never spawns.
BOT_JOURNAL_AUTO_PRUNE_ENABLED=true

# Hours between GC ticks. Default 24. The first tick fires `interval`
# after pm-bot startup — never immediately, so session bootstrap is
# never racing the writer lock.
BOT_JOURNAL_AUTO_PRUNE_INTERVAL_HOURS=24

# Retention window in days. Sessions with `ended_at` older than this
# are pruned. Default 7.
BOT_JOURNAL_AUTO_PRUNE_OLDER_THAN_DAYS=7

# Run VACUUM after the delete tx commits. Default true.
BOT_JOURNAL_AUTO_PRUNE_VACUUM=true

All four knobs are semantic-Optional — they are NOT in the fail-fast list (BotConfig::validate_required_env_or_panic). Missing the lot is a valid configuration: ENABLED defaults to false, the other three default per the table above.

4.2 Independence from local-bot dashboard

The scheduler is independent of BOT_LOCAL_API_ENABLED. Operators running headless pm-bot still benefit — base-table retention (price_snapshots, orders_history, fills, positions_history) is the dominant disk pressure source even without the dashboard journal tables installed. The GC primitive tolerates DBs without the journal schema (it skips session-keyed tables and only prunes price_snapshots in that mode).

4.3 Observability

Each tick emits one info! line:

TD-11 auto-prune tick complete sessions_pruned=14 child_rows=8234901
  bytes_before=56186900480 bytes_after=3445788672 bytes_reclaimed=52741111808
  duration_ms=12431 cutoff=2026-04-26T12:00:00+00:00

Failed ticks (DB locked, disk full, schema mismatch) emit a warn! and are retried on the next interval. The scheduler never panics.

5. Safety invariants

The retention primitive (journal::gc::run_gc) guarantees:

• Active sessions are never deleted. The DELETE on bot_sessions has WHERE ended_at IS NOT NULL AND ended_at < cutoff_secs.
• Schema is never mutated. Only DELETE + VACUUM. No DDL.
• Atomic delete batch. Every child-table DELETE plus the bot_sessions DELETE runs inside a single BEGIN IMMEDIATE transaction. A partial failure leaves the DB consistent.
• VACUUM is best-effort. It runs after the delete tx commits, so a VACUUM failure does NOT roll back the deletes — the operator gets a warning and can re-run VACUUM separately.
• WAL checkpoint. When VACUUM succeeds, the GC also runs PRAGMA wal_checkpoint(TRUNCATE) so the -wal/-shm files don't keep just- reclaimed pages pinned.

6. Activation playbook

Recommended cutover for production:

1. Audit first. Run a dry-run from the operator shell:

   pm-cli journal gc --older-than 7d --dry-run --db-path $BOT_STATE_DB_PATH

   Confirm the per-table counts match expectations and bytes_reclaimed is meaningful (>1 GiB on a long-lived DB).

2. Manual run. Take a backup, then run the full GC:

   cp $BOT_STATE_DB_PATH ${BOT_STATE_DB_PATH}.pre-gc.bak
   pm-cli journal gc --older-than 7d --db-path $BOT_STATE_DB_PATH

   Verify the bot still starts cleanly and the dashboard /local view loads (if BOT_LOCAL_API_ENABLED=true).

3. Enable the scheduler. Add the four BOT_JOURNAL_AUTO_PRUNE_* knobs to .env, set ENABLED=true, restart pm-bot. Watch for the TD-11 auto-prune scheduler spawned info line in the bot log.

4. Verify after first interval. Wait one INTERVAL_HOURS, confirm the TD-11 auto-prune tick complete line appears and bytes_after is steady-state.

7. Disabling

To roll back: set BOT_JOURNAL_AUTO_PRUNE_ENABLED=false (or remove the knob entirely) and restart pm-bot. The scheduler will not spawn on the next start. The CLI path remains available regardless.

8. Tests + assurance

Lib tests in crates/pm-bot/src/persistence/journal/gc.rs::tests (10):

• parse_duration_accepts_dhms / parse_duration_rejects_bad_input
• dry_run_counts_without_deleting
• live_run_deletes_old_session_keeps_active
• never_deletes_active_session_even_if_old
• price_snapshots_pruned_by_iso_timestamp
• cascade_deletes_journal_children_before_session
• vacuum_does_not_corrupt_db
• tolerates_db_without_journal_tables
• run_gc_from_str_wraps_parser

Lib tests in crates/pm-bot/src/application/auto_prune.rs::tests (6):

• config_defaults_are_disabled_safe
• spawn_returns_none_when_disabled
• run_one_tick_deletes_old_session
• run_one_tick_is_failsoft_on_bad_path
• run_loop_respects_shutdown_signal
• run_loop_fires_after_interval_not_immediately

Integration: existing pm-cli tests cover the CLI parser surface (17 tests). Total +16 lib tests on top of the 899 baseline ⇒ 915 lib tests passing as of 2026-05-03.