[FEATURE] Add database migrations to ensure smooth version upgrades

[edenreich]

Summary

Currently if I switch to a newer version of the CLI where there are schema changes, the CLI breaks.

The migration should probably be as a part of the infer init --overwrite command to also run the database migrations. Focus on sqlite but also ensure the system support multiple database migrations like postgres etc..

Acceptance Criteria

• There is a smooth upgrading from one version of the CLI to the other
• When upgrading to a newer version of the CLI schema changes are included
• It's documented
• It's tested

[edenreich]

@claude Can you implement it?

[claude]

Claude finished @edenreich's task in 9m 36s —— View job • claude/issue-340-20251218-1649 • Create PR ➔

---

Database Migrations Implementation Complete ✅

I've successfully implemented a comprehensive database migration system for smooth CLI version upgrades.

What Was Implemented

Migration Infrastructure (internal/infra/storage/migrations/):

• ✅ Migration runner with version tracking (migration.go)
• ✅ Transaction-based migrations (atomic all-or-nothing)
• ✅ schema_migrations table for tracking applied migrations
• ✅ Support for SQLite and PostgreSQL backends

Database-Specific Migrations:

• ✅ SQLite migration files (sqlite_migrations.go)
• ✅ PostgreSQL migration files (postgres_migrations.go)
• ✅ Initial migration (001) for current schemas
• ✅ Automatic migration on database initialization

CLI Commands:

• ✅ infer migrate - Run pending migrations
• ✅ infer migrate --status - Show migration status
• ✅ infer init --overwrite - Automatically runs migrations
• ✅ infer init --overwrite --skip-migrations - Opt-out flag

Integration with Storage:

• ✅ Migrations auto-run in NewSQLiteStorage()
• ✅ Migrations auto-run in NewPostgresStorage()
• ✅ Exposed DB() method for migration status checks

Testing & Documentation:

• ✅ Comprehensive unit tests (6 test cases, all passing)
• ✅ Complete documentation in docs/database-migrations.md
• ✅ Upgrade guide, troubleshooting, and best practices

How It Works

Automatic Migrations:

• Migrations run automatically when connecting to the database
• Version tracking prevents duplicate applications
• Transactional approach ensures no partial states

Migration Status:

$ infer migrate --status
SQLite Migration Status:

  ✅ Version 001: Initial schema - conversations table (Applied)

Manual Migration:

$ infer migrate
✓ SQLite database migrations are up to date
   All migrations have been applied automatically

Acceptance Criteria Met

• ✅ Smooth upgrading: Migrations apply automatically on version upgrade
• ✅ Schema changes included: Current schema captured in migration 001
• ✅ Multi-database support: SQLite and PostgreSQL both supported
• ✅ Documented: Complete documentation in docs/database-migrations.md
• ✅ Tested: 6 test cases covering all migration scenarios

Files Changed

• internal/infra/storage/migrations/migration.go - Core migration runner
• internal/infra/storage/migrations/migration_test.go - Unit tests
• internal/infra/storage/migrations/sqlite_migrations.go - SQLite migrations
• internal/infra/storage/migrations/postgres_migrations.go - PostgreSQL migrations
• internal/infra/storage/sqlite.go - Integrated migration runner
• internal/infra/storage/postgres.go - Integrated migration runner
• cmd/migrate.go - Migration CLI command
• cmd/init.go - Added migration integration to init command
• docs/database-migrations.md - Comprehensive documentation

Next Steps

---

| Branch: claude/issue-340-20251218-1649

[ig-semantic-release-bot]

🎉 This issue has been resolved in version 0.93.0 🎉

The release is available on GitHub release

Your semantic-release bot 📦🚀