Manage database migrations

Run direct Alembic commands from api/ so the repository configuration and migration directory resolve correctly.

cd api
uv run python -m alembic current

cd api
uv run python -m alembic heads

cd api
uv run python -m alembic history --verbose

The repository expects exactly one head. Run the canonical check from its root:

make check-migrations

If the database is behind the repository head, determine why before generating another revision. Autogeneration against an outdated or incorrect database can produce misleading operations.

Update the relevant SQLModel models first. When adding a model module, ensure the migration environment imports it before SQLModel.metadata is evaluated.

make makemigrations

Enter a short message when prompted, such as add agent retention policy. Alembic creates an autogenerated revision under api/migrations/versions/ with a filename resembling <revision>_add_agent_retention_policy.py.

The revision defines revision, down_revision, branch_labels, depends_on, upgrade(), and downgrade().

Read the entire generated revision before applying it. Confirm down_revision targets the intended current head, the graph remains continuous, and make check-migrations reports one head.

Review generated operations

• Unexpected table or column deletion, or a rename represented as drop and add
• Nullability changes without a backfill and unique constraints existing data may violate
• Foreign keys, indexes, and table rewrites that may conflict with rows or hold locks
• Server defaults, PostgreSQL enums, and type changes that require an explicit USING expression
• Missing application fields or operations caused by connecting to the wrong database

Review data transformations

Make transformations deterministic and safe for the expected volume. Account for malformed values, intermediate uniqueness, stable ordering, foreign keys, lock duration, and transaction failure. A uniqueness migration may require a collision-free temporary state.

Review the downgrade

Reverse the revision intentionally when reversal is safe. If a downgrade destroys data or cannot reconstruct the old representation, document that limit, test the real recovery strategy, require a backup, and prefer a forward corrective migration after production data changes.

Multiple heads usually appear when branches independently add revisions from the same parent. Inspect both branches before merging:

make check-migrations

cd api
uv run python -m alembic heads
uv run python -m alembic history --verbose

Confirm that both branches are compatible, especially when they touch the same table, constraint, column, enum, or data set. Then create the merge revision from the repository root:

make merge-heads

The command fails when no heads exist, does nothing with exactly one head, and creates a merge revision only for multiple heads. A merge revision normally has multiple down_revision values and empty upgrade and downgrade functions.

Test against PostgreSQL, not an approximation. The API suite starts a temporary PostgreSQL database and upgrades it to Alembic head before tests:

make check-api
make check-migrations
make test-api

API CI runs make check-migrations. The test path proves the complete migration chain can build a clean database.

Test existing data

Use a staging-sized or sanitized realistic copy for backfills, constraints, enum or type changes, identifier rewrites, destructive operations, and work that may hold long locks. Verify both schema and affected rows.

Test downgrade and reapplication

make rollback
make migrate

Confirm the prior revision, old-application compatibility, data preservation, successful reapplication, and the final upgraded result. make rollback runs alembic downgrade -1; do not use it casually on a shared database.

Use an isolated database override

cd api
ALEMBIC_DB_URL='postgresql+psycopg2://USER:PASSWORD@HOST:PORT/DATABASE' \
  uv run python -m alembic upgrade head

ALEMBIC_DB_URL overrides the normal connection for isolated migration tests. Use secret injection rather than placing production credentials in shell history.

Full local stack

./run.sh

The script starts PostgreSQL and Redis, builds the API image, runs alembic upgrade head in a temporary API container, then starts the API, worker, and UI. Application services do not start when migration fails.

Host-managed development

make migrate

cd api
uv run python -m alembic current

Do not mix host-managed services and the full Docker application stack against the same development workflow unless you deliberately understand the process and database used by each command.

Pass every schema-changing release through agent-farm-staging. Confirm it has independent application data and credentials, a current backup, one Alembic head, recorded start and target revisions, and an explicit recovery choice.

The API chart creates an agentbarn-api-migrate pre-install/pre-upgrade Job. It uses the target API image and runs:

cd /app/api && alembic upgrade head

The Job must complete before Helm rolls out the new API. After deployment, verify API health, administrator login, the changed behavior, workers, Event Deliveries, Activity, Tool Calls, logs, costs, restart recovery, migration duration, and observed locking.

Confirm the exact commit and API image, one Alembic head, representative staging results, backward compatibility with the old API, a verified application database backup, the matching AGENT_TOKEN_ENCRYPTION_KEY, and named verification and recovery owners. Prevent overlapping deployments and communicate any maintenance window.

The migration Job has backoffLimit: 2. Successful hook Jobs are deleted; failed Jobs remain for diagnosis.

kubectl get job agentbarn-api-migrate \
  --namespace agent-farm

kubectl describe job agentbarn-api-migrate \
  --namespace agent-farm

kubectl logs job/agentbarn-api-migrate \
  --namespace agent-farm

kubectl get pods \
  --namespace agent-farm \
  --selector app.kubernetes.io/component=migration-job

Do not repeatedly redeploy until you know whether the failed attempt committed schema or data changes.

Check the database revision from the deployed production API environment:

kubectl exec \
  --namespace agent-farm \
  deployment/agentbarn-api \
  -- sh -c 'cd /app/api && alembic current'

Confirm it matches the release head, then check the public API using the actual production hostname:

curl --fail https://api.agentbarn.example.com/api/v1/health

Verify API and UI health, changed behavior against old and new records, Organization isolation, authorization, workers, reconciliation, affected Agent lifecycle operations, telemetry and cost ingestion, database connections, error rates, schema-related logs, migration duration, and unexpected locks.

Record the starting and target revisions, deployment commit and image, start and finish time, backup identifier, verification results, and corrective actions.

Application rollback

A Helm rollback changes Kubernetes manifests and images. It does not run alembic downgrade. Confirm the older image can use the migrated schema before restoring it.

Alembic downgrade

make rollback

This downgrades one revision using the locally configured database. A production downgrade needs a reviewed procedure, a precise target, representative testing, understood data-loss behavior, controlled writes, application compatibility, and a current backup.

Database restoration

1. Stop or isolate writers.
2. Preserve logs and the failed database for investigation.
3. Restore the application database backup.
4. Restore the matching stable encryption key when rebuilding the environment.
5. Deploy an image compatible with the restored revision.
6. Verify the Alembic revision and run application and data-integrity checks.
7. Reopen traffic only after verification succeeds.