Database Migration

1. Prepare Environment

docker compose up -d db mailcatcher

If models changed, run prestart after applying migrations to validate startup flow.

2. Update SQLModel Definitions

Modify models in backend/app/models.py.

Check field constraints, indexes, and relationship behavior against existing conventions.

3. Generate Migration

Use uv from backend directory:

cd backend && uv run alembic revision --autogenerate -m "describe change" && cd ..

4. Audit Generated Migration Manually

Never trust autogenerated output blindly. Verify:

• rename detection:
  • column/table renames often appear as drop + add and must be refactored to rename operations
• nullable -> non-nullable transitions:
  • backfill existing rows before enforcing NOT NULL
• foreign keys and ondelete behavior:
  • especially for owner/job relationships
• index coverage:
  • timestamps, foreign keys, and frequently filtered columns
• downgrade correctness:
  • downgrade should be a real reverse path where possible

5. Apply Migration

Either local uv path or container path is acceptable in this repo. Prefer consistency with your current flow.

cd backend && uv run alembic upgrade head && cd ..

or

docker compose exec backend alembic upgrade head

6. Verify Application Integrity

cd backend && uv run bash scripts/prestart.sh && cd ..
cd backend && uv run bash scripts/tests-start.sh && cd ..
cd backend && uv run coverage report --fail-under=90 && cd ..
cd backend && uv run bash scripts/lint.sh && cd ..

7. Contract Synchronization

If API schemas changed (request/response models, OpenAPI output), regenerate frontend client:

bash ./scripts/generate-client.sh

8. Documentation Synchronization

Update docs based on what changed:

• docs/project.md for scope-level data model or workflow contract changes
• docs/pacemaker-telemetry.md for telemetry field contract changes
• docs/training-sync-endpoints.md for training endpoint behavior tied to schema changes
• any additional feature docs touched by the change

If no docs changed, explicitly document why.

Safe Patterns

Nullable to Non-Nullable

1. add/backfill data first
2. then enforce NOT NULL

Rename Without Data Loss

Use alter/rename operations, not drop+add, when intent is a rename.

High-Volume Tables

Telemetry-style tables can be large. Favor migration steps that avoid unnecessary full rewrites or destructive rebuilds.

Rollback Validation

Before concluding complex migrations, validate downgrade path:

docker compose exec backend alembic downgrade -1
docker compose exec backend alembic upgrade head

Migration Completion Checklist

• SQLModel changes made in backend/app/models.py
• Alembic revision generated under backend/app/alembic/versions/
• Autogenerated migration manually audited and corrected
• Upgrade applied successfully
• Backend prestart/tests/lint passed
• Coverage remains >=90%
• Frontend client regenerated if API contract changed
• Relevant docs updated or explicit no-doc rationale captured

Common Pitfalls

• Treating rename changes as drop+add and losing data
• Enforcing NOT NULL without backfilling existing null rows
• Forgetting to regenerate frontend client after schema/API changes
• Updating migration only, but not tests and endpoint documentation
• Skipping rollback checks for complex stateful schema transitions