eagle0/docs/SQLITE_HISTORY_DESIGN.md

# SQLite Game History Design

## Why

The current persistence layer is file-based: action results are chunked into `.e0a` files, individual results spill to `.e0i` files for crash recovery, state snapshots live in `.e0s` files, and a `directory.e0i` index tracks chunks. This shape has produced three workarounds in recent history:

1. A `games.e0es` cache (PR #6706) to avoid re-reading the running-games list.
2. Dual-storage in `PersistedActionResult` (proto bytes + parsed proto) to amortize serialization across save flushes.
3. Lazy `gameState` (PR #6692) to avoid eager proto conversion on every replayed step, after PR #6691 fixed the `stateAfter` OOM that materialized ~4500 `PersistedActionResult` instances during a stale-LLM-replay path.

Each is a workaround for the same root cause: file-based random access is expensive, so we cache aggressively, and the cache layers are fragile (one misplaced `.map(_.gameState)` reintroduces the OOM).

The alternative — SQLite as a per-game container, with proto bytes stored as BLOB columns indexed by action sequence number — gives us random access by construction, removes the need for all three workarounds, and makes idle-game eviction cheap (rehydrate from the DB instead of reparsing files). The pattern is already in production here: `SqliteClientTextStore` does exactly this for the client text cache. We extend the same pattern to game history.

## Goal & non-goals

**In scope:**
- Per-game SQLite container (`game.db`) holding action history, state snapshots, and shardok results.
- A new `SqliteHistory` class implementing the existing `FullGameHistory` trait, drop-in replacement for `PersistedHistory` at the call site.

**Out of scope (deferred or rejected):**
- **Migration of existing saves.** Pre-alpha there are only two users and a handful of test games. Cheaper to nuke the existing save directories at cutover than to write and verify a migration importer. New games created post-cutover land in `game.db` directly; existing games are gone.
- The top-level `games.e0es` running-games registry. Stays as-is; its cache (PR #6706) already works.
- Idle-game eviction. Enabled by this work but lands in a follow-up phase after `SqliteHistory` is the authoritative path.
- A shared cross-game analytics DB. Per-game design supports ad-hoc cross-game queries via iterate-and-aggregate; build the centralized analytics DB only if/when those queries become hot.

## File layout

`game.db` lives in the existing per-game save directory, alongside `text_store.db`:

```
${EAGLE_SAVE_DIR}/${gameIdHex}/
├── game.db          # NEW — action history, snapshots, shardok results
└── text_store.db    # existing — client text (SqliteClientTextStore)
```

Existing `.e0a` / `.e0s` / `.e0i` / `directory.e0i` files do not coexist with `game.db`: at cutover we nuke save directories one time. There is no migration path and no legacy-file fallback.

### Why `game.db` and `text_store.db` are separate files (not tables in one DB)

It's tempting to combine them — one file per game, one connection per game, one cloud-upload story. The reason not to: **writer-lock contention.** SQLite serializes writers per-database file. `SqliteClientTextStore` writes frequently during LLM streaming (one `UPDATE` per token append on `texts.text`). `SqliteHistory` writes per-action during turn commits. Combining them means an in-flight LLM token append can block a turn commit, or vice versa. Separate DBs have separate writer locks and never contend. Subsystem ownership (each package owning its own schema, independent migration paths if the schemas evolve) is a bonus.

`Persister` integration follows the `SqliteClientTextStore` pattern: `game.db` is uploaded to / downloaded from cloud storage as a single opaque blob, keyed by the filename. On first load, if `game.db` is missing locally, try `persister.retrieveAsStream("game.db")`. If both are missing, this is a new game and we create an empty DB.

## Schema

```sql
-- Action results, one row per action_result_index.
CREATE TABLE action_results (
    action_seq           INTEGER PRIMARY KEY,        -- 0-based, dense, never sparse
    action_result_type   INTEGER NOT NULL,           -- denormalized for filtering
    round_id             INTEGER NOT NULL,           -- denormalized for recentResultsForRound
    date_year            INTEGER,                    -- denormalized for sinceDate; NULL pre-game-start
    date_month           INTEGER,                    -- denormalized for sinceDate; NULL pre-game-start
    payload              BLOB    NOT NULL            -- ActionResult proto bytes
) WITHOUT ROWID;

CREATE INDEX idx_action_results_round   ON action_results(round_id);
CREATE INDEX idx_action_results_date    ON action_results(date_year, date_month);
-- action_seq is the PK so no index needed there.

-- State snapshots at chunk boundaries (and the starting state at seq 0).
-- boundary_action_seq is the action_seq AFTER which the snapshot reflects state.
-- The starting state lives at boundary_action_seq = 0 (state before any actions).
CREATE TABLE state_snapshots (
    boundary_action_seq  INTEGER PRIMARY KEY,
    payload              BLOB    NOT NULL            -- GameState proto bytes
) WITHOUT ROWID;

-- Shardok per-battle results.
CREATE TABLE shardok_results (
    shardok_game_id      TEXT    NOT NULL,
    action_seq           INTEGER NOT NULL,           -- 0-based within this shardok game
    payload              BLOB    NOT NULL,           -- ShardokActionResult proto bytes
    PRIMARY KEY (shardok_game_id, action_seq)
) WITHOUT ROWID;

-- Shardok per-game state (one row per shardok_game_id).
CREATE TABLE shardok_state (
    shardok_game_id      TEXT    PRIMARY KEY,
    game_state           BLOB    NOT NULL,           -- ShardokGameState proto bytes
    last_eagle_round_id  INTEGER NOT NULL
) WITHOUT ROWID;

-- Shardok per-player results (filtered ActionResultView per faction).
CREATE TABLE shardok_player_results (
    shardok_game_id      TEXT    NOT NULL,
    faction_id           INTEGER NOT NULL,
    seq                  INTEGER NOT NULL,
    payload              BLOB    NOT NULL,           -- ShardokActionResultView proto bytes
    PRIMARY KEY (shardok_game_id, faction_id, seq)
) WITHOUT ROWID;

-- Shardok per-player available commands (latest only, keyed by game + faction).
CREATE TABLE shardok_player_commands (
    shardok_game_id      TEXT    NOT NULL,
    faction_id           INTEGER NOT NULL,
    payload              BLOB,                       -- ShardokAvailableCommands proto bytes; NULL = no commands
    PRIMARY KEY (shardok_game_id, faction_id)
) WITHOUT ROWID;

-- Schema version and starting state.
CREATE TABLE metadata (
    key                  TEXT    PRIMARY KEY,
    value                BLOB    NOT NULL
);
-- Seeded with: schema_version=1
```

### Column rationale

- **`action_seq`** as `INTEGER PRIMARY KEY` (the SQLite rowid alias) — densest possible storage and no separate index; range scans for `since(start)` are O(log n + result count).
- **`WITHOUT ROWID`** on tables with synthetic keys to skip the implicit rowid column.
- **Denormalized `action_result_type`, `round_id`, `date_year`, `date_month`** — these are the predicates the existing read paths use (`recentResultsForRound`, `sinceDate`). Keeping them as columns avoids parsing the proto blob to filter.
- **No `created_at` timestamp** — wall-clock time isn't queried by the History trait, and game date (year/month) is what matters semantically.
- **`payload BLOB`** — the proto bytes are the source of truth for the structured action result. Polymorphic action types make a relational schema painful for limited gain; the denormalized columns above cover the queries we need.
- **Snapshots keyed by `boundary_action_seq`** — `stateAfter(N)` finds `MAX(boundary_action_seq) WHERE boundary_action_seq <= N`, returns that snapshot, replays forward `N - boundary_action_seq` actions. Snapshot at `0` is the game's starting state.

### Snapshot strategy

Match the existing `resultsPerSaveFile = 25` boundary: write a `state_snapshots` row every 25 actions. That gives the same replay-window cost as the current chunk-file design (`stateAfter(N)` replays at most 25 actions to reach an arbitrary point), and matches the cadence developers are already calibrated to.

Snapshots are GameState proto bytes, identical in shape to today's chunk-file `startingState`. The migration importer derives them directly from the chunk files. New games write a snapshot after every 25th `withNewResults` action.

## Connection lifecycle

Mirror `SqliteClientTextStore`:

- One `Connection` per loaded game, opened when `GamesManager` loads the game, closed when the game is evicted (future eviction work) or the server shuts down.
- `Class.forName("org.sqlite.JDBC")` + `DriverManager.getConnection("jdbc:sqlite:${path}")` on open.
- `PRAGMA journal_mode = WAL` on every connection open. WAL gives us crash-safe writes and lets readers proceed concurrently with the single writer — important because gRPC stream readers (humanPlayerClientConnectionState) query history mid-turn.
- `PRAGMA synchronous = NORMAL` (the WAL-recommended setting; durability is preserved through WAL checkpoint).
- `PRAGMA foreign_keys = ON` (defensive; we have no FKs today but cheap to enable).
- Auto-commit on by default. Transactions explicitly opened for `withNewResults` (batch of action inserts + optional snapshot) and `truncateTo` (deletes across all tables).

### Threading

The current `PersistedHistory` is an immutable case class; `withNewResults` returns a new instance. `SqliteHistory` cannot be pure-immutable (the DB is mutable state) but should present the same interface: methods that "change" the history return `this` after a successful write. The underlying `Connection` is shared.

JDBC `Connection` is not thread-safe in general; SQLite's JDBC driver serializes operations per-connection. Existing call sites already serialize writes through the `EngineApplier` flow, so single-threaded write access is preserved. Reads from gRPC stream readers can use the same connection — SQLite serializes them transparently, and WAL prevents read-write blocking.

## Read paths

How each `FullGameHistory` method maps to SQL:

| Method | Query |
|---|---|
| `count` | `SELECT COALESCE(MAX(action_seq), -1) + 1 FROM action_results` (cached as a counter after the first read) |
| `last` | `SELECT payload FROM action_results ORDER BY action_seq DESC LIMIT 1` + state from `stateAfter(count)` |
| `all` | `SELECT payload FROM action_results ORDER BY action_seq` — used by `GameAdminServiceImpl.getActionDetail` to fetch one action by index. See follow-up note below. |
| `since(start)` | `SELECT payload FROM action_results WHERE action_seq >= ? ORDER BY action_seq` |
| `sinceDate(date)` | `SELECT payload FROM action_results WHERE (date_year, date_month) >= (?, ?) ORDER BY action_seq` |
| `recentResultsForRound(round, pred)` | `SELECT payload FROM action_results WHERE round_id = ? AND action_seq > ? ORDER BY action_seq` where `?` is the cutoff matching current `recentHistory` semantics (last N actions, or all actions for the current round) |
| `stateAfter(N)` | Find latest snapshot ≤ N, replay forward via `replayApplier` (same logic as `replayScalaOnlyToState`) |

For methods that return `Vector[ActionResultWithResultingState]` (with resulting state per row): we **do not** materialize per-row gameStates. Instead, fold the actions through `replayApplier` starting from the latest snapshot ≤ start, producing the states on the fly. This matches what `formAwrs` does today, with one critical difference: no `PersistedActionResult` wrapper is allocated, and no proto-conversion is performed per step. This is the same shape as `replayScalaOnlyToState`, generalized to produce intermediate states.

### `all()` follow-up

`GameAdminServiceImpl.getActionDetail` is the one production caller. It fetches a single action by index from the full vector. Either of these is cheaper than materializing the whole history:

- Replace the call site with `history.since(index).headOption`.
- Add a new `actionAt(index): Option[ActionResultWithResultingState]` method and drop `all()` from the trait entirely.

`SqliteHistory.all` will work — it's just `SELECT * ORDER BY action_seq` — but it's expensive (materializes the full history into memory), so we should switch the admin caller in a small follow-up PR. Not blocking the SQLite work.

### `recentResultsForRound` semantics

Today this filters `recentHistory` (the in-memory tail) by `roundId`. In SQLite there's no in-memory/persisted split — all results live in the DB. The semantics shift slightly: return all results matching `round_id` after a configurable cutoff. The cutoff should match today's behavior (results since the start of the current round, or some bounded recent window). Default to "all results with the given `round_id`," which is correct as long as `round_id` uniquely identifies a round across the game's history (it does, per the current `RoundId` model).

## Write paths

### `withNewResults(newResults)`

In a transaction:
1. `INSERT INTO action_results (action_seq, action_result_type, round_id, date_year, date_month, payload) VALUES ...` — one row per new result. Use `addBatch()` for multiple.
2. For each new result whose `action_seq % 25 == 0` (snapshot boundary), `INSERT INTO state_snapshots(boundary_action_seq, payload) VALUES (?, ?)` with the GameState proto bytes.
3. Commit.

The denormalized columns (`action_result_type`, `round_id`, `date_year`, `date_month`) are extracted from the action's resulting state at insert time. They are immutable once written; if the schema interpretation changes, a migration is required.

The "individual result for crash recovery" pattern (`.e0i` files) is replaced by: the action result is durably written when the transaction commits. WAL gives us atomicity per transaction. No separate crash-recovery file is needed.

### `saveNow`

Becomes a no-op in normal operation — writes are already durable per `withNewResults` commit. We keep the method on the trait for API compatibility but the implementation just returns `this`. (We could call `PRAGMA wal_checkpoint(TRUNCATE)` here to roll the WAL into the main DB file, useful before cloud upload; defer this until we measure WAL growth.)

### `truncateTo(targetActionCount)`

In a transaction:
1. `DELETE FROM action_results WHERE action_seq >= ?`
2. `DELETE FROM state_snapshots WHERE boundary_action_seq > ?`
3. Shardok cleanup: `DELETE FROM shardok_results / shardok_state / shardok_player_results / shardok_player_commands WHERE shardok_game_id NOT IN (...)` (the set of battles still outstanding at the truncate point; mirrors `deleteOrphanedShardokFiles`).
4. Commit.

`truncateTo(0)` resets the game; everything after the starting-state snapshot is deleted.

## Shardok results

The shardok subsystem currently lives in `.e0s` files (one per battle, full per-battle state). It's loaded selectively at game-load time, only for outstanding battles (see `PersistedHistory.apply` line 248-256).

Moving to SQLite, the four shardok tables above capture:
- `shardok_results` — the per-battle result stream
- `shardok_state` — current per-battle state (one row per battle)
- `shardok_player_results` — per-faction filtered views
- `shardok_player_commands` — current available commands per faction

`withNewShardokResults` writes to all four in a transaction. `shardokCount`, `shardokGameState`, etc., become single-row indexed lookups.

The selective-load optimization disappears with SQLite: we don't proactively read anything; queries hit the DB on demand. The "load only outstanding battles" logic is replaced by "query by `shardok_game_id` when needed."

## Crash recovery

WAL replaces the `.e0i` individual-result-file mechanism. On startup:
- WAL is automatically replayed by SQLite if the previous shutdown was unclean. No application code needed.
- A successful commit means durable; an interrupted commit means rolled back. No half-written state visible.

The current "orphaned individual results on load" path (`loadIndividualResults` in `PersistedHistory.apply`) is gone.

## Cutover

No migration path. At cutover:

1. Stop the server.
2. Nuke the contents of `${EAGLE_SAVE_DIR}` (and `${EAGLE_ARCHIVE_DIR}` if there's anything there).
3. Deploy.
4. New games created post-deploy land in `game.db` directly.

Pre-alpha there are only two users and a handful of test games; a one-time nuke is cheaper than a verified migration importer. Trade-off accepted by the user explicitly.

### Testing strategy (no migration)

Without migration, we don't need equivalence-vs-`PersistedHistory` testing. The correctness gate becomes:

1. **Adapt `PersistedHistoryTest`** to run against `SqliteHistory` instead. The 890-line existing test suite covers the trait surface exhaustively. Same assertions, new implementation under test.
2. **Property-style end-to-end test**: create a synthetic game, run N batches of `withNewResults`, verify that `since(start)`, `stateAfter(N)`, `sinceDate(date)`, `recentResultsForRound`, `truncateTo`, etc., all produce expected values. Run with a deterministic random seed.
3. **Manual smoke test in dev**: start a fresh game, play several turns, restart the server, verify saves persist and replay correctly.

## Cloud-storage integration

`Persister.save(key, bytes)` and `persister.retrieveAsStream(key)` already handle local-vs-S3 transparently. `SqliteClientTextStore` integrates by treating `text_store.db` as a single binary blob: upload after writes, download before reads.

`SqliteHistory` will do the same with `game.db`:
- **On load:** if local `game.db` missing, try to download via `persister.retrieveAsStream("game.db")`. Fall back to migration from `.e0a` if both are missing.
- **On save:** after a meaningful change (turn boundary? configurable cadence?), upload the current `game.db` file to S3. Need to decide the upload trigger — too frequent and we burn S3 PUTs; too rare and crash recovery is lossy.

**Open question:** the right upload cadence. Today, chunk saves trigger cloud upload at chunk boundaries (every 25 actions). The simplest match: keep that cadence, upload `game.db` once per chunk boundary. We could be smarter (only upload if WAL has been checkpointed; only upload deltas) but those are optimizations.

## What the trait-level cutover looks like

`GamesManager` currently has roughly:

```scala
val history: FullGameHistory = PersistedHistory(gameId, persister).getOrElse(...)
```

Becomes:

```scala
val history: FullGameHistory = SqliteHistory.loaded(gameId, persister)
```

Every other consumer of `FullGameHistory` is unchanged.

## Decisions locked

1. **Per-game `game.db`** (not shared across games).
2. **`game.db` and `text_store.db` stay separate files** (not combined into one DB). Writer-lock contention is the deciding factor.
3. **No migration** of existing saves. Nuke save directories at cutover. Pre-alpha trade-off accepted by the user.
4. **No feature flag.** Cutover is unconditional. Failures are loud.
5. **Cloud upload cadence**: match the existing 25-action chunk cadence. Upload `game.db` once per snapshot boundary.
6. **`recentResultsForRound`**: return all results for the given round (no recent-window cap). The `round_id` predicate bounds the scan naturally.
7. **WAL checkpointing**: rely on SQLite's auto-checkpoint. Add explicit `PRAGMA wal_checkpoint` only if WAL file growth becomes a problem.
8. **Schema versioning**: seed `metadata` with `schema_version = 1`. Schema migration mechanism deferred until we need a v2.
9. **`history.all`**: keep on the trait for now; admin call site moved to `since(i).headOption` (or a new `actionAt(i)` method) in a small follow-up PR. Not blocking.

## Phase plan (revised)

| Phase | Work | Estimate |
|---|---|---|
| 0 | This design doc | 2-3 days (in flight) |
| 1 | `SqliteHistory` schema + write paths + tests adapted from `PersistedHistoryTest` | ~1 week |
| 2 | Read paths + full test parity | ~1 week |
| 3 | Shardok results integration | 3-5 days |
| 4 | Cutover in `GamesManager` (nuke `${EAGLE_SAVE_DIR}` as part of deploy) | 2-3 days |
| 5 | Idle-game eviction | 3-5 days |
| 6 | Post-alpha cleanup (delete `PersistedHistory`, `PersistedActionResult`, `PartialGameUtils`, the chunk-file save code, `games.e0es` cache if no longer needed) | 1-2 days |

**Total: ~3-4 weeks** (down from 5-6 — migration and equivalence-testing dropped).

The follow-up to migrate `GameAdminServiceImpl.getActionDetail` off `history.all` is a small, independent PR that can land any time.