sqlx-migration-safety

name	sqlx-migration-safety
description	Use when changing Sparcho SQLite schema, SQLx migrations, database startup, or persistence models. Prevents SQLx migration checksum mismatches and Android startup crashes caused by editing already-applied migration files.

SQLx Migration Safety

Use this workflow before touching src-tauri/migrations/, src-tauri/src/db/, or SQLite-backed Rust/TypeScript models.

Rules

• Never edit an existing migration file once it may have shipped, run locally, or been installed on a device.
• Never fix a migration crash by changing an old up.sql or down.sql.
• Always add a new migration with a version greater than every existing migration.
• Treat SQLx migration checksums as immutable release artifacts.
• If a checksum mismatch already exists in user/device databases, add a version-specific startup repair path. Do not ask users to clear app data as the normal fix.
• Migration failures must be surfaced in the UI as a startup diagnostic dialog. They must not make the app abort during Tauri setup.

Workflow

1. List existing migrations:
   • Get-ChildItem src-tauri\migrations | Sort-Object Name
2. Create new files only:
   • <version>_<name>.up.sql
   • <version>_<name>.down.sql
3. Prefer idempotent SQL where SQLite supports it:
   • CREATE TABLE IF NOT EXISTS
   • CREATE INDEX IF NOT EXISTS
4. For ALTER TABLE ADD COLUMN, use app startup repair logic for compatibility because SQLite support for IF NOT EXISTS varies.
5. If logcat shows migration <version> was previously applied but has been modified, add that exact version to repair_known_modified_migration in src-tauri/src/db/mod.rs.
6. Verify the repair checks schema state before updating _sqlx_migrations.checksum.

Review Checklist

• No existing src-tauri/migrations/*.sql files were modified.
• The new schema change has a new migration version.
• Rust models, SQL SELECT, INSERT, and UPDATE statements match the schema.
• Existing installed apps can start without clearing app data.
• If migration repair is needed, startup continues and the UI shows a diagnostic dialog instead of crashing.
• Run:
  • cargo fmt --manifest-path src-tauri\Cargo.toml --check
  • cargo check --manifest-path src-tauri\Cargo.toml --target aarch64-linux-android -j 1