Database configuration and lifecycle

The platform database holds users, roles, sessions, settings, and audit state — isolated from game-world backups. Select a supported topology, secure its connection, control schema migrations, and practice recovery steps independently of your game servers.

Select and provision

Document engine, version, availability model, storage, ownership, connection limits, and TLS.

SQLite

SQLite is embedded, requires no external service, and is the default for single-node clusters. It stores data in a single file on a PersistentVolume mounted to the API pod.

# Example: SQLite configuration in Helm values
api:
  storage:
    size: 2Gi
    storageClassName: ""  # Uses cluster default; override to a specific StorageClass
    existingClaim: ""     # Leave empty to auto-create, or set to a pre-provisioned PVC name

Constraints:

• Single connection total (enforced by the driver, SetMaxOpenConns(1)): all reads and writes are serialized, not just writes.
• RWO (read-write-once) volume only: the PersistentVolume can be mounted by one pod at a time.
• Set --db-driver=sqlite (the default); no DSN override needed for the standard location.

PostgreSQL

PostgreSQL is an external relational database engine. Use it for:

• An external, separately administered database. Keep api.replicas: 1 even with PostgreSQL: the user-management lock and audit hash chain are still per-process, so extra replicas are not safe.
• Longer data retention and advanced backup/recovery workflows.
• Regulated environments requiring separate database administration.

Required configuration:

# Example: PostgreSQL connection parameters
api:
  db:
    driver: postgres
    dsn: "postgres://user:password@host:5432/gameplane?sslmode=require"
  # Pass as environment variable or Secret; never in the pod spec.

Best practices:

• Use TLS (sslmode=require) for all connections.
• Create a dedicated database user with minimal necessary permissions (CREATE TABLE, INSERT, UPDATE, DELETE on gameplane schema only).
• Set connection pool limits in your PostgreSQL configuration to prevent resource exhaustion.
• Document the backup schedule and procedure for your PostgreSQL instance.

Migrate and operate

Tie schema migrations to a pinned release and observe connectivity, locks, latency, growth, and failures.

Automatic migrations

By default, the API reconciles schema on every startup:

# Migrations run automatically when the API container starts.
# Monitor the startup logs for migration success or errors.
kubectl -n gameplane-system logs -l app.kubernetes.io/name=gameplane-api --tail=50

Migration behavior:

• Migrations are idempotent; re-running the same migration set is safe.
• Schema version is tracked internally; migrations are executed in order from the point where the previous run stopped.
• Failed migrations block API startup (fail-closed). Address the error, resolve the database state, and retry.

Monitoring and observability

Check database connectivity:

# For SQLite, verify the PVC is mounted and writable.
kubectl -n gameplane-system exec -it deploy/gameplane-api -- ls -lh /data/gameplane.db

# For PostgreSQL, check connection logs.
kubectl -n gameplane-system logs -l app.kubernetes.io/name=gameplane-api | grep -i "database\|connection"

Metrics:

• Database file size (for SQLite) or table size (for PostgreSQL).
• Audit log table growth (may require retention policy if table grows unbounded).
• API startup time (migrations add latency on upgrades).

Audit state size:

-- PostgreSQL example: check audit table size
SELECT schemaname, tablename, pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
WHERE tablename LIKE 'audit%'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

Credential rotation

When rotating database credentials:

1. Update the Secret with the new password.
2. Restart the API deployment to pick up the new credentials.
3. Monitor startup logs to confirm successful reconnection.
4. Revoke the old password at the database level only after confirming the new one works.

For PostgreSQL, most drivers support connection pooling with retry; the API will reconnect on the next pod restart.

Back up and recover

Restore database, values, identity configuration, CRDs, and Secrets as one version-compatible recovery set.

Platform database recovery is a three-step process:

1. Back up the database file (SQLite) or export a logical backup (PostgreSQL).
2. Stop the API pods to prevent writes during restoration.
3. Restore the database from your backup and verify that the API restarts cleanly.

SQLite backup and restore

Backup:

# 1. Create a snapshot of the SQLite file PVC.
kubectl -n gameplane-system exec deploy/gameplane-api -- \
  cp /data/gameplane.db /data/gameplane.db.backup

# 2. Or use your storage system's native snapshot (recommended for production).
# Example: kubectl exec to a storage admin pod, snapshot the PVC.

Restore:

# 1. Stop the API to prevent concurrent writes.
kubectl -n gameplane-system scale deploy gameplane-api --replicas=0

# 2. Delete the corrupted PVC and recreate it from a snapshot (or manually restore the file).
kubectl -n gameplane-system delete pvc gameplane-api-data

# 3. Restart the API; it will re-mount the recovered PVC.
kubectl -n gameplane-system scale deploy gameplane-api --replicas=1

# 4. Monitor startup logs for migration success.
kubectl -n gameplane-system logs -l app.kubernetes.io/name=gameplane-api --tail=50

PostgreSQL backup and restore

Backup:

# Use pg_dump for a logical backup (recommended).
pg_dump -h <postgres-host> -U <user> -d gameplane > gameplane.sql

# Or use pgBackRest / WAL archiving for continuous backups (production-grade).

Restore:

# 1. Stop the API pods.
kubectl -n gameplane-system scale deploy gameplane-api --replicas=0

# 2. Drop and recreate the schema (or restore from a snapshot).
psql -h <postgres-host> -U <admin-user> -d postgres -c "DROP DATABASE gameplane;"
psql -h <postgres-host> -U <admin-user> -d postgres -c "CREATE DATABASE gameplane OWNER <gameplane-user>;"

# 3. Restore the backup.
psql -h <postgres-host> -U <gameplane-user> -d gameplane < gameplane.sql

# 4. Restart the API.
kubectl -n gameplane-system scale deploy gameplane-api --replicas=1

# 5. Monitor startup logs.
kubectl -n gameplane-system logs -l app.kubernetes.io/name=gameplane-api --tail=50

Recovery validation

After restoring the database, verify:

• API startup: logs show no migration errors.
• Login works: dashboard login succeeds with a known account.
• Audit trail intact: System Logs page displays expected audit events.
• Settings persist: Admin settings (OIDC, notification sinks, etc.) are readable.