Alembic Migration Pitfalls: Silent DB Mismatches, Hex IDs, and SQLite Traps

Alembic, the migration tool for SQLAlchemy, uses hex-prefixed revision filenames derived from UUIDs to avoid numbering collisions when multiple developers work in parallel. Each migration file contains revision and down_revision fields that form a linked list, meaning file order is determined by pointers rather than filenames or alphabetical sorting. A subtle but serious bug can occur when Alembic's env.py reads database URLs via os.getenv while the main application resolves them through pydantic-settings, causing migrations to silently apply to a different database file for months without any error. In the example project FinOps Sentinel, this discrepancy went undetected because most tables were also created by SQLAlchemy's create_all, until a migration introduced a table that nothing else created. The article also covers how to configure date-sortable filenames and how to safely handle CHECK constraint changes in SQLite, which lacks native ALTER TABLE support.