KuberCoin Docs

Database Migrations

Conventions for schema migrations: numbering, rollback, and the irreversible escape hatch.

Database Migrations

Each transactional surface owns its schema. Migrations are plain SQL files under domains/<surface>/db/migrations/ and are applied by db/migrate.php, which records every applied version in a schema_migrations table.

File conventions

  • Naming. NNN_short_name.sql where NNN is a zero-padded version ≥ 3 digits. Versions are unique per surface and contiguous.
  • Sections. Every file declares -- UP at the top. Reversible migrations also declare -- DOWN with the inverse statements.
  • Irreversible migrations. When a rollback is impossible (for example, a destructive data migration or a vendor schema change), the file MUST include a single line of the form -- IRREVERSIBLE: <reason>. The reason is required so reviewers can confirm the trade-off.

CI gate

scripts/check-migrations.mjs runs in the SAST suite and enforces:

  1. Filename matches NNN_name.sql.
  2. Versions are unique within a surface.
  3. No gaps in the version sequence.
  4. Each migration has a -- DOWN section or an -- IRREVERSIBLE: <reason> marker.

Migrations that pre-date the rollback gate are grandfathered in ops/migrations/legacy-allowlist.json. Do not extend the allowlist — fix the migration instead.

Dry-running a migration

Before merging a new migration, replay it against a scratch copy of the production schema: 

mariadb -u root scratch < db/migrations/<new>.sql
mariadb -u root scratch -e "SHOW WARNINGS"

Then verify the rollback path: 

php db/migrate.php --rollback=1 --config /tmp/scratch-config.php

Production rollout

  1. Take a fresh dump of the affected schema (see backup runbook).
  2. Run php db/migrate.php --status to confirm the pending list matches expectations.
  3. Apply: php db/migrate.php --up. Watch for non-zero exit.
  4. Confirm the surface's /readyz returns 200 and the SLO dashboard stays green for 15 minutes.
  5. If a rollback is needed: php db/migrate.php --rollback=1 — this only works when the migration declared a -- DOWN block.