Test a rollback migration

Run a migration up and back down, and verify the schema returns to its original shape while the migration history records the rollback.

1. 1CREATE TABLE schema_migrations (version TEXT PRIMARY KEY, applied_at TEXT NOT NULL, rolled_back_at TEXT).
2. 2Up: add customers.phone TEXT, create customer_notes (id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL REFERENCES customers(id), note TEXT NOT NULL), and insert version '2025_12_add_phone_and_notes' applied_at '2025-12-30'.
3. 3Down: drop customer_notes, drop customers.phone, and set rolled_back_at = '2025-12-31' for that version.
4. 4Verify: SELECT COUNT(*) AS leftover FROM (SELECT name FROM pragma_table_info('customers') WHERE name = 'phone' UNION ALL SELECT name FROM sqlite_master WHERE name = 'customer_notes') returns 0.

• The schema matches the original plus schema_migrations, with the version marked rolled back
• The last result set shows 0 leftover objects
• The down migration uses DROP

• Every run starts from a freshly seeded database, so your script must include its own setup statements.
• Dialect: SQLite. ALTER TABLE supports ADD COLUMN, DROP COLUMN and RENAME; other type changes need a table rebuild.
• Only the last result set of your script is compared, so finish with the verification query.