Migrations

AEGIS uses Alembic for database schema migrations, managed per-service.

Approach

Each service that owns database tables manages its own Alembic migration history. The base schema is defined in SQL files mounted into the PostgreSQL container and executed at startup.

Initial Schema

The Docker PostgreSQL container automatically runs the following SQL files on first boot:

These scripts collectively:

1. Create the aegis database and user
2. Enable extensions: age, pgvector, uuid-ossp
3. Create the oilgas graph in Apache AGE
4. Create all base tables with indexes and constraints
5. Set up audit trail triggers (prevent UPDATE/DELETE on audit_logs)

Standalone Deploy Migrations

For databases that already exist (and therefore won’t re-run init.sql), standalone idempotent SQL migrations live under infrastructure/deploy/migrations/ and are applied directly with psql:

psql "$DATABASE_URL" -f infrastructure/deploy/migrations/001_users.sql

Fresh databases get the users table from init.sql, so this migration is only needed to bring existing databases up to date.

Running Migrations

For services with Alembic configured:

# Navigate to the service directory
cd services/{service-name}
 
# Create a new migration
poetry run alembic revision --autogenerate -m "description of change"
 
# Run pending migrations
poetry run alembic upgrade head
 
# Check current migration status
poetry run alembic current
 
# Roll back one migration
poetry run alembic downgrade -1
 
# View migration history
poetry run alembic history

Migration Workflow

Creating a New Migration

1. Make your model or schema changes in the service code.
2. Run the autogenerate command to create a migration file:

cd services/{service-name}
poetry run alembic revision --autogenerate -m "add compliance_status table"

3. Review the generated migration in alembic/versions/. Autogenerate is not perfect — verify that the upgrade() and downgrade() functions are correct.
4. Test the migration in both directions:

# Apply
poetry run alembic upgrade head
 
# Verify
poetry run alembic current
 
# Roll back
poetry run alembic downgrade -1
 
# Re-apply
poetry run alembic upgrade head

5. Commit the migration file alongside the code changes.

Checking Migration Status

# Show current revision
poetry run alembic current
 
# Show all pending migrations
poetry run alembic history --indicate-current
 
# Show the SQL that would be run (without executing)
poetry run alembic upgrade head --sql

Conventions

• One migration per logical change — don’t bundle unrelated schema changes into a single revision
• Descriptive revision messages — e.g., add compliance_status table not update schema
• Always test migrations — run upgrade and downgrade before committing
• Never modify existing migrations — if a migration has been applied by other developers, create a new corrective migration instead
• Schema ownership — only the owning service should create migrations for its tables
• Idempotent where possible — use IF NOT EXISTS guards in raw SQL within migrations

Adding Alembic to a New Service

If a service needs to manage its own tables, set up Alembic:

cd services/{service-name}
poetry add alembic asyncpg sqlalchemy
poetry run alembic init alembic

Configure alembic.ini with the database URL:

sqlalchemy.url = postgresql://aegis:aegis_local@localhost:5432/aegis

Update alembic/env.py to import your models for autogenerate support:

from your_service.models import Base
target_metadata = Base.metadata

Resetting the Database

During development, you may need to start fresh. The most reliable way is to remove the Docker volume and recreate:

# Stop containers and remove volumes
docker compose down -v
 
# Recreate containers (init.sql runs automatically)
docker compose up -d
 
# Re-run seed scripts
curl -X POST http://localhost:8003/seed
cd services/orchestration-engine
poetry run python -m orchestration.seed_skills
poetry run python -m orchestration.seed_checklists
poetry run python -m orchestration.seed_rules
poetry run python -m orchestration.seed_demo_data

Troubleshooting