Kratos Migration Recovery: Handling Failed Schema Updates Without Built-in Rollback

Direct Answer

Kratos CLI tools cannot safely restore a database to a known good state after a failed migration. The kratos migrate status command only reports the current version recorded in the schema_version table; it does not revert schema changes or repair data. Manual SQL intervention—specifically resetting the goose version marker and undoing partially applied DDL—is required to recover without a full database restore. A database backup remains the only guaranteed recovery path.

Likely Explanation of Failure

Kratos utilizes the goose migration tool. While migrations typically run in transactions, certain DDL statements (depending on the database engine) may not be transactional or may fail after some changes have already been committed. This creates a mismatch where the schema_version table does not reflect the actual state of the database schema. Because Kratos does not ship with down migrations (rollback scripts), the CLI has no mechanism to automatically undo these changes.

Recovery Steps for Partial Migrations

1. Create a Safety Snapshot: Before any manual intervention, perform a logical dump of the current state (e.g., pg_dump or mysqldump).
2. Inspect the Version Table: Identify the last successfully applied version.

   SELECT * FROM schema_version ORDER BY version_id DESC LIMIT 5;

3. Manual DDL Reversion: Identify the SQL statements in the failed migration file (found in the Kratos distribution's sql/ directory). Manually DROP any columns, indexes, or constraints that were created before the failure occurred.
4. Reset the Version Marker: Update the schema_version table to point to the last known good version so Kratos attempts to run the failed migration again from the start.

   DELETE FROM schema_version WHERE version_id > [last_good_version];

5. Re-run Migration: Execute kratos migrate up to apply the changes cleanly.

Verifying Data Integrity

• Schema Validation: Use \d+ [table_name] (PostgreSQL) or SHOW CREATE TABLE [table_name] (MySQL) to ensure columns, types, and constraints match the expected version.
• Constraint Check: Specifically verify that NOT NULL constraints and Foreign Keys were not dropped or left in an invalid state during the failure.
• Custom Schema Diff: For custom modifications, compare the current table structure against a pre-upgrade backup to ensure no business-logic columns were accidentally deleted.

Required Diagnostic Detail

Which exact Kratos version and database engine/version are you using? Migration table structures (e.g., version_id vs id) and the behavior of transactional DDL vary significantly between Kratos versions and between PostgreSQL and MySQL.