eagle0/docs/POSTGRES_HISTORY_MIGRATION_PLAN.md

Postgres History Migration Plan

Goal

Evaluate and, if the measurements support it, migrate production game history persistence from local SQLite plus S3 base and delta files to a managed Postgres database.

This is not just a mechanical port. The important product requirement is that battle commands, especially Shardok map clicks, show results to the player as quickly as possible while still keeping the chance of lost results acceptably low.

Proposed Direction

Use Postgres as the authoritative production store. Local SQLite should remain useful for development, tests, and rollback during rollout, but production should not have two long-lived authoritative stores for the same game.

Start with a small DigitalOcean Managed Postgres instance in the same region and VPC as Eagle. The $15 single-node plan is reasonable for early measurement and staging. If the design works, production can move to a more reliable plan when the availability requirements justify it.

Phase 1: Measure First

Before porting the full history implementation, add a benchmark or admin utility that runs from the Eagle deployment environment against the candidate Postgres database.

Measure at least:

• single action-result append transaction latency
• batch append latency for 25 and 100 action results
• latest snapshot lookup
• replay from a snapshot to a target index
• since and stream-update query latency
• Shardok battle result append latency
• p50, p95, p99, and max latency
• async queue lag, if an async persistence prototype is included

Useful initial exit criteria:

• Strategic action-result writes should be comfortably below 50 ms p95.
• Shardok synchronous persistence should be below roughly 20-30 ms p95 to stay on the click-to-animation path.
• If Shardok sync writes are above that, use an async Shardok persistence design rather than blocking player feedback.

Phase 2: Schema Prototype

Add Postgres configuration and a prototype schema that maps the current SQLite history model.

Expected tables:

• games, for game-level metadata and backend bookkeeping
• action_results, for ordered strategic action results
• per-entity decomposition tables currently written by the SQLite dispatchers
• state_snapshots, for replay anchors
• Shardok battle result tables, including battle id, sequence number, command/result payloads, and player metadata
• schema_migrations, unless the migration mechanism is handled outside the app

Prefer batched inserts and batched reads. The current SQLite implementation can afford some local-file patterns that will be too chatty over a network.

Phase 3: Implement Postgres History

Implement a FullGameHistory backend backed by Postgres. Keep the existing SQLite backend available behind a config flag during rollout.

Core methods to support:

• count
• last
• withNewResults
• stateAfter
• since
• sinceDate
• round-local recent-result queries
• Shardok result writes and reads

The implementation should preserve action ordering and idempotency. If retries are possible, writes should include stable keys or sequence ranges so replaying a write request cannot duplicate results.

Phase 4: Decide Shardok Durability Mode

Shardok latency is the place where this migration can most visibly hurt the player. There are two viable modes.

Option A: Synchronous Shardok Writes

Use this if measured Postgres latency is low enough. The command path writes results to Postgres before telling the client about them.

This is the simplest durability model, but only acceptable if it keeps click-to-animation latency low.

Option B: Async Shardok Writes

Use this if synchronous Postgres writes add noticeable latency.

The model:

• apply battle results to authoritative in-memory game state immediately
• send client updates immediately after the ordered in-memory apply
• enqueue ordered persistence batches in the same order they were applied
• persist batches with battle_id, sequence range, payload hash, and payload
• track lastAppliedSeq and lastPersistedSeq
• retry failed writes until they succeed
• alert when persistence lag exceeds a small threshold
• block or degrade new commands if the queue grows too large
• force a synchronous checkpoint before battle end reconciliation or strategic-layer handoff
• drain the queue during graceful shutdown

This creates a small crash-loss window, but only for rare crashes that happen after the player sees a result and before the async write completes. That tradeoff may be better than adding network persistence latency to every tactical command.

Phase 5: Rollout

Add a backend flag such as:

EAGLE_HISTORY_BACKEND=sqlite|postgres

Suggested rollout:

1. Run benchmarks from the deployed environment.
2. Enable Postgres in staging for new games.
3. Run warmup games and Shardok battle smoke tests.
4. Enable Postgres for new production games.
5. Migrate old games either lazily on first open or through an admin-triggered migration job.
6. Keep SQLite/S3 rollback available until enough new-game production time has passed.

If Postgres is unavailable, commands should not be acknowledged as durable. Either hold/retry them before applying, or reject them clearly. Do not show a result to the client while pretending it has already been durably stored unless that is an explicit async-durability path with lag monitoring.

Phase 6: Cleanup

After production confidence:

• remove production dependence on S3 base/delta history files
• keep object storage for backups, exports, or emergency archives
• keep SQLite for local development and tests if it remains useful
• document restore, migration, and rollback procedures

Open Questions

• What exact durability guarantee do we want for "the client already saw this result"?
• What Shardok persistence latency is acceptable in real play?
• Should old games migrate lazily or through a bulk/admin process?
• When should the production database move from single-node to HA?
• What should the player experience be if Postgres is reachable but slow?

Recommended Next PR

The next implementation PR should add a Postgres benchmark/admin utility and configuration plumbing, not the full port. That gives us real latency numbers from the Eagle deployment environment before committing to the larger migration.