AGENTS.md

1# Migration Package Guidelines
2 
3**Last Updated:** June 1, 2026
4 
5This file applies to `internal/entity/migrate/`. Read [`README.md`](README.md) here for the runtime flow, retry behavior, and CLI troubleshooting commands.
6 
7## Editing Migrations
8 
9- Add or change SQL in [`mysql/`](mysql/) and [`sqlite3/`](sqlite3/); do not hand-edit the generated [`dialect_mysql.go`](dialect_mysql.go) or [`dialect_sqlite3.go`](dialect_sqlite3.go) files.
10- Reuse the same timestamp-based migration ID across dialects for the same logical change.
11- Use `.pre.sql` only when the SQL must run before GORM `AutoMigrate(...)`, typically for renames or shape changes that the ORM must see afterward.
12- Keep migrations idempotent when possible with `IF EXISTS`, `IF NOT EXISTS`, or safe update conditions. Failed migrations are recorded once and then skipped on normal startup, so noisy or brittle SQL creates persistent operator friction until someone retries it manually.
13- When (re-)creating a prefix index on a `VARBINARY` column, keep the prefix at **≤ 767 bytes** (the convention is `512`). InnoDB caps key prefixes at 767 bytes on `COMPACT`/`REDUNDANT` row formats, and `VARBINARY` prefixes are counted in bytes, so a longer prefix fails on older or non-`DYNAMIC` installs. See [`README.md`](README.md) → "Index Prefix Limits".
14- If a migration requires custom Go logic instead of plain SQL, keep the reason narrow and obvious. Package-local helpers should still preserve the same retry semantics described in [`README.md`](README.md).
15 
16## Generation & Verification
17 
18- After changing migration SQL, run `go generate ./internal/entity/migrate`.
19- After Go edits in this package, run `go fmt ./internal/entity/migrate`.
20- Verify the package with `go test ./internal/entity/migrate -count=1`.
21- When a change affects operator workflows, also verify the CLI help and status paths with `./photoprism migrations --help`, `./photoprism migrations ls --help`, and `./photoprism migrations run --help`.
22 
23## Test Fixtures
24 
25- Keep [`testdata/migrate_sqlite3`](testdata/migrate_sqlite3) and [`testdata/migrate_mysql.sql`](testdata/migrate_mysql.sql) aligned with the pre-migration schema shape expected by the regression tests.
26- `TestDialectSQLite3` runs against a copied SQLite fixture. `TestDialectMysql` expects a MariaDB service reachable as `mariadb:${MARIADB_PORT:-4001}` with the `migrate` database and user from [`testdata/migrate_mysql.sql`](testdata/migrate_mysql.sql).
27 
28## Runtime Model
29 
30- The `versions` table gates the once-per-release schema initialization path.
31- The `migrations` table stores per-migration `started_at`, `finished_at`, and `error` state.
32- Normal startup does not rerun rows that already failed. Retrying failed rows requires `photoprism migrations run --failed` or explicitly naming migration IDs.
33- Unfinished rows without an error are treated as stale and repeatable only after 60 minutes. More recent unfinished rows are assumed to still be running.
34