Database Schema

PostgreSQL schema for persistent data storage.

Tables

players

Player and admin accounts (unified).

CREATE TABLE players (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    username VARCHAR(20) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    is_admin BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

    CONSTRAINT username_format CHECK (username ~ '^[a-zA-Z0-9_]{3,20}$')
);

CREATE INDEX idx_players_username ON players(username);

ships

Player-owned ships (multi-ship support, added in migration 005).

CREATE TABLE ships (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    player_id UUID NOT NULL REFERENCES players(id) ON DELETE CASCADE,
    name VARCHAR(32) NOT NULL,
    ship_class VARCHAR(32) NOT NULL DEFAULT 'fast_frigate',
    system_id VARCHAR(32) NOT NULL DEFAULT 'sol',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

    CONSTRAINT uq_ships_player_name UNIQUE (player_id, name)
);

CREATE INDEX idx_ships_player_created ON ships(player_id, created_at);
CREATE INDEX idx_ships_system_id ON ships(system_id);

The compound index (player_id, created_at) supports both single-column lookups on player_id and the common get_ships query pattern (WHERE player_id = $1 ORDER BY created_at).

Ship Data Ownership

Data Storage Owner Service
player_id → ship_id mapping PostgreSQL (ships table) players
Ship real-time state Redis physics
Ship state in snapshots PostgreSQL (snapshots.state) tick-engine

Data flow:

  1. Player registers → players service generates ship_id, stores in ships table
  2. players service calls physics to spawn ship with that ship_id
  3. physics service creates ship state in Redis
  4. Snapshots include full ship state from Redis
  5. On restart, physics restores ship state from snapshot
  6. player_id → ship_id mapping survives independently in PostgreSQL

snapshots

Periodic game state snapshots for recovery.

CREATE TABLE snapshots (
    id SERIAL PRIMARY KEY,
    tick_number BIGINT NOT NULL,
    game_time TIMESTAMP WITH TIME ZONE NOT NULL,
    state JSONB NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_snapshots_tick ON snapshots(tick_number DESC);

game_config

Server configuration stored in database.

CREATE TABLE game_config (
    key VARCHAR(50) PRIMARY KEY,
    value JSONB NOT NULL,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

Snapshot State Format

The state JSONB column in snapshots contains:

{
  "tick_number": 123456,
  "game_time": "2025-01-15T10:30:00Z",
  "total_spawns": 42,
  "paused": false,
  "tick_rate": 1.0,
  "bodies": [
    {
      "name": "Earth",
      "position": [x, y, z],
      "velocity": [vx, vy, vz]
    }
  ],
  "ships": [
    {
      "ship_id": "uuid",
      "player_id": "uuid",
      "name": "PlayerName",
      "position": [x, y, z],
      "velocity": [vx, vy, vz],
      "attitude": [qw, qx, qy, qz],
      "angular_velocity": [wx, wy, wz],
      "rotation_input": [pitch, yaw, roll],
      "fuel": 25000.0,
      "thrust_level": 0.0,
      "wheel_momentum": [mx, my, mz]
    }
  ]
}

Game Config Keys

Key Value Type Description
tick_rate number Ticks per second
start_date string Initial game date (ISO 8601)
snapshot_interval number Seconds between snapshots

Note: current_tick and paused are stored in Redis (game:tick, game:paused) for real-time access, and persisted via snapshots. They are not stored in game_config to avoid inconsistency between Redis and PostgreSQL.

Recovery Process

On server restart:

  1. Load latest snapshot: SELECT * FROM snapshots ORDER BY tick_number DESC LIMIT 1
  2. Check Redis state:
    • Read game:tick from Redis
    • If Redis is empty or game:tick doesn’t exist → use snapshot
    • If Redis game:tick > snapshot tick_number → use Redis (it’s newer)
    • If Redis game:tick ≤ snapshot tick_number → use snapshot (Redis is stale)
  3. If using snapshot: restore all state to Redis from snapshot JSON
  4. Restore game:paused from snapshot (preserves pause state across restarts)
  5. Restore game:tick_rate from snapshot (preserves tick rate across restarts)
  6. Restore game:total_spawns from snapshot (preserves spawn counter for position sequencing)
  7. If paused: tick-engine starts in paused state (no tick processing until admin resumes)
  8. If not paused: resume tick processing from the recovered game:tick value

Staleness detection:

Redis game:tick Snapshot tick_number Decision
Not present Any Use snapshot
1000 500 Use Redis (newer)
500 500 Use snapshot (authoritative)
500 1000 Use snapshot (Redis stale)

Redis is considered stale if its tick is less than or equal to the snapshot, because the snapshot represents a known-good state that was persisted to durable storage.

Snapshot Retention

Snapshots are pruned to prevent unbounded storage growth.

Retention policy:

Parameter Value Description
Max snapshots 100 Maximum snapshots to retain
Min age to prune 1 hour Never delete snapshots less than 1 hour old

Pruning process:

After each successful snapshot creation:

  1. Count total snapshots: SELECT COUNT(*) FROM snapshots
  2. If count > 100:
    • Delete oldest snapshots that are > 1 hour old
    • Keep at most 100 snapshots
    • DELETE FROM snapshots WHERE id IN (SELECT id FROM snapshots WHERE created_at < NOW() - INTERVAL '1 hour' ORDER BY tick_number ASC LIMIT (count - 100))
  3. Log: “Pruned {n} old snapshots”

Rationale:

  • 100 snapshots at 60-second intervals = ~1.7 hours of history
  • 1-hour minimum age protects recent snapshots during high activity
  • Sufficient for debugging and recovery without excessive storage

Manual snapshot retention:

Admin-created manual snapshots follow the same retention policy (no special treatment in MVP).

Migrations

Schema migrations are managed with Alembic in the db/ directory at the project root.

Why Alembic

  • Tracks migration history in an alembic_version table
  • Supports upgrade and downgrade operations
  • Integrates with Kubernetes via a Job that runs before service deployments
  • Shared across all services (no single service owns the schema)

Directory Structure 

db/
  alembic.ini              # Alembic configuration
  requirements.txt         # Python dependencies (alembic, psycopg2-binary)
  Dockerfile               # Migration runner image
  alembic/
    env.py                 # Migration environment (reads DATABASE_URL)
    script.py.mako         # Template for new migrations
    versions/
      001_initial_schema.py  # Initial schema (4 tables)
  tests/
    test_migrations.py     # Migration tests

Design Decisions

  • Raw SQL migrations: Services use asyncpg directly (no SQLAlchemy models), so migrations use op.execute() with raw SQL. No model autogenerate.
  • psycopg2-binary for Alembic: Alembic is synchronous; asyncpg cannot be used. psycopg2-binary is a separate dependency only in the migration container.
  • Idempotent initial migration: Uses IF NOT EXISTS so it is safe to run against databases initialized by the ConfigMap init script.
  • ConfigMap init script retained: Still used for bootstrapping new PostgreSQL instances. Alembic handles ongoing schema changes.

Creating a New Migration 

# From the db/ directory:
alembic -c db/alembic.ini revision -m "description_of_change"

Edit the generated file in db/alembic/versions/ — write raw SQL in upgrade() and downgrade().

Running Migrations

Locally (development):

DATABASE_URL=postgresql://galaxy:password@localhost:5432/galaxy \
  alembic -c db/alembic.ini upgrade head

Kubernetes:

kubectl apply -f k8s/base/migration-job.yaml -n galaxy-dev
kubectl wait --for=condition=complete job/db-migration -n galaxy-dev --timeout=60s

maneuver_events

Persistent maneuver lifecycle events for diagnosis. Low-volume (lifecycle events only, not per-tick), survives pod restarts.

CREATE TABLE maneuver_events (
    id BIGSERIAL PRIMARY KEY,
    ship_id UUID NOT NULL,
    maneuver_type VARCHAR(32) NOT NULL,   -- circularize, set_inclination, rendezvous
    event_type VARCHAR(32) NOT NULL,       -- maneuver_started, phase_transition, maneuver_completed, maneuver_aborted
    phase VARCHAR(32),                     -- current phase
    from_phase VARCHAR(32),                -- previous phase (for transitions)
    tick BIGINT NOT NULL,
    game_time TIMESTAMP WITH TIME ZONE NOT NULL,
    element_errors JSONB,                  -- [{label, value, ok}, ...]
    distance DOUBLE PRECISION,
    effectivity DOUBLE PRECISION,
    status_text TEXT,
    extra JSONB,                           -- arbitrary per-event data
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
CREATE INDEX idx_maneuver_events_ship ON maneuver_events(ship_id, created_at DESC);
CREATE INDEX idx_maneuver_events_tick ON maneuver_events(tick DESC);

Retention: 1000 rows per ship, pruned on insert.

Migration Naming

Alembic auto-generates revision IDs. Files are prefixed with a sequence number for readability:

001_initial_schema.py
002_maneuver_events.py
003_unified_admin_auth.py
004_drop_admins_table.py
005_multi_ship.py
006_reset_backups.py
007_unique_ship_names.py
008_compound_indexes.py
009_multi_system.py

Back to top

Galaxy — Kubernetes-based multiplayer space game

This site uses Just the Docs, a documentation theme for Jekyll.