Migration

Migrate gocron data

Treat migrations as compatibility code. Preserve existing installations and all three supported databases; do not optimize only for a fresh SQLite database.

Compatibility invariants

• Upgrade existing installations in place; never require recreating the database or discarding tasks, configuration, users, or logs.
• Validate from an N-1 schema/data fixture and every older schema directly affected by the change. A fresh database alone is not evidence.
• Prefer additive, idempotent changes and compatible defaults on SQLite, MySQL, and PostgreSQL. Do not drop or reinterpret persisted data in a minor or patch release.
• Do not advance the migration version until all work succeeds. A failed migration must remain diagnosable and safely retryable.
• Document downgrade safety and backup/recovery steps. If downgrade can lose or corrupt data, stop and obtain explicit approval before implementation.

Performance invariants

• Test against a representative populated database, not only a tiny fixture. Record row counts and migration duration for performance-sensitive changes.
• For populated-table changes, assess full scans, lock/transaction duration, temporary disk, and write amplification on SQLite, MySQL, and PostgreSQL.
• On SQLite, keep write transactions short and avoid per-row commits. Use safe bounded batches for large backfills and test busy/lock behavior with readers.
• Add indexes only for demonstrated query patterns; inspect the query plan and account for index build time, disk growth, and write overhead.
• Prefer resumable/idempotent batches when a single transaction could block startup or exceed reasonable memory or disk. State the interruption behavior.

Establish the change

• Inspect cmd/gocron/gocron.go, internal/models/migration.go, the affected models, and nearby migration tests before editing.
• Determine whether the change affects an existing table, creates a new table, or transforms existing data. Do not create an empty migration for a release with no schema or data change.
• Derive the migration id from the target AppVersion using the repository's established conversion. Never reuse an id. If the release version is not known and a new id is required, stop and ask for it.

Implement atomically

For a new persisted model or schema change:

1. Add a new unique id to versionIds in chronological release order.
2. Add the matching migration.upgradeForNNN entry at the same index.
3. Implement upgradeForNNN with the transaction passed to it; return every error instead of logging and continuing.
4. Add new install-time models to the Install table slice. Do not add an existing-table column there as a substitute for an upgrade.
5. Make the upgrade idempotent where practical. Guard data rewrites that would corrupt values on a second run.
6. Avoid database-specific SQL. When unavoidable, branch on the GORM dialect and implement SQLite, MySQL, and PostgreSQL behavior explicitly.
7. Add a focused migration test that builds the N-1 pre-upgrade schema (and older affected schemas), runs the upgrade, proves existing rows and values survive, and exercises a second run when idempotency is expected.

Do not rely only on GORM AutoMigrate when data must be renamed, remapped, backfilled, deduplicated, or constrained.

Verify

Run:

python3 .agents/skills/migration/scripts/check_migration.py
go test -race ./internal/models/...
go test -race ./cmd/gocron/...

If a specific migration was added, pass its id to the structural checker:

python3 .agents/skills/migration/scripts/check_migration.py 191

Then invoke $verify before committing. Report the migration id, affected tables, upgrade path, downgrade/backup implications, database-specific risks, exact tests run, and performance evidence when the change has material risk.