fix: align migrations with models for bigint ids and required deck_id - #91

alembic check against a fully upgraded database reported drift that existed before the idempotency key work. The init migration created decks.id and cards.id as INTEGER and left cards.deck_id nullable, while the models say otherwise. This adds a migration that brings the schema in line with the models, and a pytest-alembic suite that fails whenever the two drift apart again.

Which side is right

• decks.id and cards.id: the model is right. BigIntAuditBase maps them as BIGINT, and the id columns are the only key the API exposes, so a 32-bit cap would be a hard limit at about 2.1 billion rows. Moving the model to Integer would mean dropping advanced-alchemy's base class for a hand-rolled one. The migration also runs ALTER SEQUENCE ... AS bigint. Postgres 10 and later create SERIAL sequences AS integer, and changing the column type leaves the sequence alone, so without it the ids would still stop at 2^31 - 1.
• cards.deck_id nullability: the model is right. orm.Mapped[int] makes it NOT NULL, and the API only creates cards under a deck (CardsRepository.add_cards always sets deck_id).
• cards.deck_id type: both change. The model mapped it as sa.Integer while it references decks.id. Autogenerate didn't flag this because model and database agreed, but once decks.id is BIGINT a foreign key stuck at INTEGER can't hold every deck id. The model now says sa.BigInteger and the migration converts the column.

The init migration is unchanged.

Notes for existing databases

• Changing INTEGER to BIGINT rewrites both tables under an ACCESS EXCLUSIVE lock. That is fine for a template, but a large deployment should plan for it.
• Setting cards.deck_id NOT NULL fails if a card with a NULL deck_id exists. The migration doesn't delete such rows on its own; that call belongs to whoever owns the data.
• Downgrade reverses everything. It fails if an id or the sequence has already gone past the INTEGER range.

Migration tests

tests/migrations/ runs pytest-alembic's four built-in tests against an empty database, the same way chat-app does: single head, upgrade, up/down consistency for every revision, and model definitions matching the DDL, which is alembic check as a test. The default pytest run skips the directory (--ignore in addopts, omit in coverage) because migrating the schema would break the transaction-rollback fixture. It runs with just test-migrations, and in CI as its own matrix leg next to the app suite.

These tests don't check sequence types, so nothing catches a SERIAL sequence left AS integer.

Testing

On Postgres 14, test_model_definitions_match_ddl fails with this migration removed and the old model, and all four tests pass with it. ruff, ty and pytest (100% coverage) pass.