Add tests for Alembic migrations

[kbighorse]

Problem

Currently, the test suite uses erase_and_rebuild_db() in tests/__init__.py which creates tables directly from SQLAlchemy models using Base.metadata.create_all(). This means alembic migrations are never tested.

This creates a gap where:

• Model changes pass tests but migrations may fail
• Migration scripts may have errors not caught until deployed
• Schema drift between models and migrations goes undetected

Proposed Solution

Add integration tests that:

1. Start from a clean database
2. Run alembic upgrade head
3. Verify expected tables and columns exist
4. Optionally test alembic downgrade paths

Acceptance Criteria

• Tests run alembic migrations instead of (or in addition to) direct model-based table creation
• CI catches migration errors before merge
• Migration rollback (downgrade) is tested

Related

Discovered while implementing BDMS-434 (MinorTraceChemistry migration)