Add structured database migrations with YAML/JSON schema and idempotent tracking

[mikkeldamsgaard]

We want Initium to support structured database migrations driven by a migration spec in JSON or YAML instead of just running external commands.

Goals

Build a first-class migrate subcommand that:

• Replace the current migrate command
• Parses a migration plan defined in YAML and JSON with a strict schema.
• Connects to supported databases using credentials from a Kubernetes secret (MySQL, PostgreSQL, SQLite).
• Ensures idempotent migrations using a configurable migration tracking table (default: initium_migrate).
• Applies only migrations that have not yet been applied.

Requirements

Input schema

Migration files must accept at minimum:

• Migration name
• Target database type
• SQL statements or logical diffs
• Optional rollbacks
• Optional tags/metadata

Support both JSON and YAML formats with a well-documented schema; auto-generated IDs should be allowed and recommended for simple cases.

Behavior

The migrate engine should:

• Read the spec file (YAML/JSON)
• Validate against a JSON Schema
• Open a DB connection securely via provided secret
• Create the migration tracking table if missing
• Only apply new migrations
• Log applied migrations with name and timestamp
• Fail gracefully with structured errors
• Support MySQL, PostgreSQL, and SQLite
• This is ment to run in a kubernetes init container, so make sure the database connection params is read from a environment variable that can be specified from a secret.
  • Make an example for each of postgres, mysql and sqllite with how to specify the connection params

Deliverables

• New Go package under internal/migrate implementing above
• All inputs (except the db secret and the spec file) should have sensible defaults and have cmdline argugment
• JSON Schema for migration files, with examples in examples/migrate/*.y*ml
• Unit and integration tests covering edge cases
• Docs in docs/migrations.md
• Updated CHANGELOG.md

Acceptance criteria

• Migration plan example runs against all three supported databases
• Tests validate idempotency and error handling
• Documentation covers connection via secret and spec file format

Rationale

This moves us from ad hoc command execution to a repeatable, safe DB migration system with clear semantics and schema-driven behavior. X