The bigint→UUID conversion already preserved data via in-place column
swaps, but operators reasonably wanted stronger guarantees. Added:
- Pre-conversion snapshot per affected table:
CREATE TABLE <name>_bak_bigint_uuid_2026_04_29 LIKE <name>;
INSERT INTO <name>_bak_bigint_uuid_2026_04_29 SELECT * FROM <name>;
Idempotent — a retry after a crash never overwrites the original
snapshot. Snapshots are intentionally not auto-dropped.
- Post-conversion verification:
- row count must match the snapshot exactly per table; mismatch
throws and instructs recovery from the bak table
- FK integrity check: zero orphans in permission_members.permission_id
/ permission_usages.permission_id / role_members.role_id /
roles.parent_id (allowing NULL on the self-FK)
- Early-return if every affected table is already UUID, so reruns
on hosts that already migrated don't even create snapshots.
Plus roles:drop-uuid-migration-backup-tables to clean up the
snapshots once the operator is confident — with --dry-run and
explicit confirmation. Verified end-to-end on a disposable bigint
fixture: 3 rows preserved, 0 orphans, snapshot intact, schema
correctly converted to char(36).
Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
Fabian @ Blax Software
7e19d4ffbe