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)

[kbighorse]

Implemented in tests/integration/test_alembic_migrations.py

Test Coverage

Migration History Tests (3 tests)

• test_migrations_have_no_multiple_heads - Ensures no branching in migration history
• test_all_migrations_have_down_revision - Validates migration chain is unbroken
• test_current_revision_matches_head - Verifies DB is at latest migration

Schema Verification Tests (7 tests)

• test_core_tables_exist - Verifies 13 core tables (location, thing, observation, etc.)
• test_legacy_nma_tables_exist - Verifies 9 NMA legacy tables
• test_thing_table_has_required_columns - Checks required columns including legacy PKs
• test_location_table_has_geometry_column - Verifies PostGIS geometry
• test_observation_table_has_required_columns - Checks observation schema
• test_alembic_version_table_exists - Verifies migration tracking
• test_postgis_extension_enabled - Verifies PostGIS extension

Foreign Key Integrity Tests (5 tests)

• Tests FK relationships: Observation→Sample→FieldActivity→FieldEvent→Thing
• Tests NMA_Chemistry_SampleInfo→Thing FK

Index Tests (1 test)

• test_location_has_spatial_index - Verifies GIST spatial index

Downgrade Tests (1 test, skipped by default)

• test_can_downgrade_one_revision - Available for manual testing

Acceptance Criteria

• ✅ Tests run alembic migrations (via conftest.py command.upgrade)
• ✅ CI catches migration errors before merge (schema/FK validation)
• ✅ Migration rollback tested (skipped by default, available for manual runs)

================== 16 passed, 1 skipped in 9.97s ==================