---
name: specx-sqlalchemy-migrations
description: Add or repair Alembic migrations for specx SQLAlchemy services. Use when adding SQLAlchemy models or repositories, replacing metadata.create_all schema bootstraps, creating async Alembic env.py, adding migration Makefile targets, generating initial revisions, or testing migration drift.
---

# specx SQLAlchemy Migrations

Use this skill whenever a specx project has SQLAlchemy models or persistence
adapters. Read `references/alembic.md` before editing migration files.

## Workflow

1. Add `alembic>=1.18.5` as a runtime dependency when SQLAlchemy adapters
   exist, together with SQLAlchemy's asyncio extra and the selected driver.
2. Add `alembic.ini`, `migrations/env.py`, `migrations/script.py.mako`, and
   `migrations/versions/`.
3. Use Alembic's async pattern for async SQLAlchemy engines.
4. Put app-wide SQLAlchemy settings/session factory under top-level
   `infrastructure/sqlalchemy/`.
5. Keep scope-owned ORM models and repositories under
   `core/<scope>/infrastructure/sqlalchemy/`.
6. Add a project-local SQLAlchemy declarative base under
   `src/<package>/foundation/sqlalchemy_model.py`; do not use shared packaged
   metadata for generated services.
7. Put reusable model discovery under top-level
   `infrastructure/sqlalchemy/model_discovery.py`. Use that same function from
   `migrations/env.py` and its guardrail test before assigning
   `target_metadata`; do not duplicate discovery or maintain hard-coded model
   module names.
8. Set `target_metadata` to the project-local `BaseSQLAlchemyModel.metadata`.
9. Generate or hand-review an initial migration for current models.
10. Add `make migrate` and `make makemigrations`.
11. Add tests that run `alembic upgrade head` against an isolated database,
    check for pending autogenerate changes, and prove every core SQLAlchemy
    model file is included by the exact discovery function Alembic uses. Use
    the production database family when dialect behavior matters.

## Guardrails

- Do not call `Base.metadata.create_all`, `metadata.create_all`, or
  `drop_all` from `src/`.
- Do not run migrations from FastAPI startup by default. Run migrations as an
  operational command before app startup.
- Do not put app-wide engine/session factory code inside one core scope.
- Do not let delivery controllers import ORM models, repositories, sessions, or
  migration helpers.
- Do not let Alembic drift checks depend on incomplete metadata. Model discovery
  must include every `core/*/infrastructure/sqlalchemy/models/*.py` file.
- Do not trust autogenerated migrations without review.
- Do not edit, delete, or reorder a revision that may already have been applied;
  add a new corrective revision.
- Do not pass SQLite's `autocommit` connect argument on Python 3.11. For a
  savepoint-based SQLite test harness spanning Python versions, use
  SQLAlchemy's `connect` and `begin` event-hook recipe.

## Code Style

Use blank lines as logical separators in all code. Keep related statements
together, but separate independent setup, action, assertion, response, branch,
and transformation groups so long blocks stay readable.

## References

- `references/alembic.md` - async Alembic layout, env.py, Makefile targets,
  initial migration, and migration tests.