🛡️ Database Migration Guardrails

CRITICAL: Following these rules prevents database corruption, data loss, and "checksum mismatch" errors that force wipes.

🚫 Rule 1: The Principle of immutability

• Why? sqlx stores a checksum of every applied migration in the _sqlx_migrations table. If you edit a file on disk (e.g., to fix a typo), the checksum changes. The next time anyone tries to run migrations, sqlx will panic, detecting corruption.
• The Consequence: Often the only easy fix for a checksum mismatch is sqlx database reset, which WIPES ALL DATA.
• The Correct Way: If you make a mistake in 001_initial.sql, create 002_fix_typo.sql to correct it.

⏰ Rule 2: Timestamp Versioning

• Bad: 045_add_tables.sql, 046_update.sql
• Good: 20260124120000_add_tables.sql
• Why? Integer collisions are guaranteed on a team. Timestamps allow multiple developers to create migrations concurrently without conflict (they just run in chronological order).
• How:

  # Inside /rust-engine
  sqlx migrate add description_of_change
  # Creates: 20260124123456_description_of_change.up.sql

🧪 Rule 3: The "Fresh Start" Test

• The Problem: A migration 003.sql might try to insert data into a table created in 001.sql. If you accidentally deleted that table in 002.sql or messed up dependencies, your entire chain is broken.
• The Test: Before merging any PR with DB changes, verify the chain:

  # Local Verification
  sqlx database drop -y
  sqlx database create
  sqlx migrate run

• If this fails: You have a broken migration dependency. Fix it immediately before committing.

🛠️ Recovery Procedures

Scenario: Checksum Mismatch (Local Dev)

1. Revert the file change.
2. Create a new migration for your fix.

1. sqlx migrate revert (undoes the DB change)
2. Edit the file.
3. sqlx migrate run (re-applies and calculates new hash).

Scenario: Checksum Mismatch (Remote/Shared)

1. Accept that the "bad" migration is now canon.
2. Create a new "fix-forward" migration.