Database migrations

Storage model

Platform identity and settings live in the database while desired infrastructure remains in CRDs.

Write a migration

Add one portable, immutable, zero-padded SQL file and use a transaction that records success only at completion.

Migration file format

Create a file named api/internal/db/migrations/<NNN>_<description>.sql where <NNN> is the next three-digit zero-padded number. For example, if the latest migration is 011_user_theme_preferences.sql, the next file is 012_your_change.sql.

Each migration is automatically wrapped in a database transaction by the framework (the api/internal/db package calls BeginTx before executing your migration and Commit when all statements succeed). Do not include explicit BEGIN TRANSACTION; or COMMIT; in your SQL file.

Separate statements with a semicolon and newline (;\n). For example:

-- Describe the change
CREATE TABLE example_table (
  id TEXT PRIMARY KEY,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX idx_example_table_created_at ON example_table(created_at);

If the migration fails, the entire transaction automatically rolls back.

Portable SQL patterns

Create tables using standard DDL:

CREATE TABLE users (
  id TEXT PRIMARY KEY,
  email TEXT UNIQUE NOT NULL,
  created_at TEXT NOT NULL,
  updated_at TEXT
);

Use only standard constraints:

• PRIMARY KEY, UNIQUE, NOT NULL, DEFAULT, CHECK
• Both SQLite and PostgreSQL support these.

Avoid dialect-specific features:

• No SERIAL (SQLite), BIGSERIAL (PostgreSQL), or GENERATED columns — use TEXT + application logic.
• No JSON or JSONB — use TEXT columns.
• No database-specific functions in schemas — handle logic in application code.

If you must alter a table in SQLite (e.g., add a column):

SQLite supports ALTER TABLE ... ADD COLUMN with constraints, but not DROP COLUMN or constraint modifications without rebuilding. If your change requires dropping a column or modifying a constraint, create a new table, copy data, drop the old table, and rename:

-- Create new table with desired schema
CREATE TABLE users_new (
  id TEXT PRIMARY KEY,
  email TEXT UNIQUE NOT NULL,
  status TEXT DEFAULT 'active'
);

-- Copy data, transforming if needed
INSERT INTO users_new (id, email, status)
SELECT id, email, 'active' FROM users;

-- Drop old table and rename
DROP TABLE users;

ALTER TABLE users_new RENAME TO users;

-- Recreate indexes
CREATE INDEX idx_users_email ON users(email);

The framework automatically wraps this entire sequence in a transaction, so if any statement fails, all changes roll back.

Validate and operate

Test fresh schema, prior-version upgrade, reopen, transaction rollback, and both database build modes.

Testing migrations

Run the following after every schema change:

1. Fresh schema test — start with an empty database and run all migrations:

   rm -f gameplane.db  # Fresh SQLite
   cd api && go test ./internal/db

2. Prior-version upgrade — ensure migrations from the previous release apply cleanly:

   cd test/e2e && go test -tags e2e -run TestUpgrade_FromPreviousRelease ./...

   Note: this requires the e2e build tag and the Kind upgrade harness (make test-e2e-bucket BUCKET=upgrade), not a bare go test.

3. PostgreSQL compatibility — build and test with the PostgreSQL driver enabled:

   cd api && go test -tags postgres ./internal/db

4. Reopen and idempotency — already covered by TestOpen_SQLiteAndMigrate (api/internal/db/db_test.go), which calls Migrate() twice against the same open store and asserts the second call is a no-op; run it directly with:

   cd api && go test ./internal/db -run TestOpen_SQLiteAndMigrate

Schema review checklist

Before committing a migration file:

• File is named with the next zero-padded number (e.g., 012_feature.sql)
• SQL does NOT include explicit BEGIN TRANSACTION;/COMMIT; (the framework wraps each migration in a transaction automatically)
• All SQL is portable across SQLite and PostgreSQL (no dialect-specific syntax)
• New tables use only standard constraints and TEXT primary keys
• No application logic is embedded in schema (stored procedures, triggers)
• Tests pass: go test ./internal/db and go test -tags postgres ./internal/db
• Migration is immutable — no edits after initial commit

Related guides

• Database Configuration & Lifecycle — connection pooling, disaster recovery, PostgreSQL setup
• Repository & Components — schema layout and where migrations live
• API & CRDs — REST endpoints that read/write to the database