| name | migration |
| description | Create, review, or verify gocron database migrations across SQLite, MySQL, and PostgreSQL. Use when adding or changing GORM models, columns, indexes, constraints, persisted settings, migration version ids, Install tables, upgradeForNNN functions, or migration tests. |
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:
- Add a new unique id to
versionIds in chronological release order.
- Add the matching
migration.upgradeForNNN entry at the same index.
- Implement
upgradeForNNN with the transaction passed to it; return every
error instead of logging and continuing.
- Add new install-time models to the
Install table slice. Do not add an
existing-table column there as a substitute for an upgrade.
- Make the upgrade idempotent where practical. Guard data rewrites that would
corrupt values on a second run.
- Avoid database-specific SQL. When unavoidable, branch on the GORM dialect
and implement SQLite, MySQL, and PostgreSQL behavior explicitly.
- 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.
