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 dev tenant’s per-tenant Apache AGE graph (tenant_<hex>_oilgas; graphs are per-tenant, not a single global oilgas)
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. Since R42a the auth-service migration runner (below) re-ships 001/002 idempotently, so the manual psql step is no longer required for auth tables.

Core Numbered Migrations (infrastructure/docker/postgres/)

Numbered migrations 002–031 live alongside init.sql. They do not auto-apply: a fresh Docker volume gets only the files docker-compose mounts into docker-entrypoint-initdb.d (init.sql, 002, 007, 015, and agent-config’s 001) — everything else, including 030/031, must be applied manually with psql, in numeric order (the box deploy flow applies new files by hand before restarting; local volumes need the same). The two newest:

Self-Applying Service Migrations (auth-service, R42a)

auth-service owns its numbered SQL migrations under services/auth-service/src/auth_service/migrations/ and self-applies them on every boot (same convention as agent-config-service: every statement catalog-guarded, no applied-tracking). The runner is auth_service/migrate.py, also runnable standalone as poetry run python -m auth_service.migrate. After the SQL it converges the RBAC seeds (4 system roles + the Appendix C permission matrix), a one-time backfill of user_roles from the legacy users.roles array, and (R42e) the system-user + identity_map seeds — then drops the users.roles shadow column once the backfill has run, so grants live solely in user_roles.

The CI lane auth-service-tests runs ci/auth_double_boot.sh: boot → snapshot → boot again → snapshot; both boots must succeed (health + bootstrap-admin login) with byte-identical schema and seed state. A non-idempotent migration fails this lane instead of boot-looping the box.

Self-Applying Service Migrations (agent-config-service)

The agent-config-service owns its numbered SQL migrations under services/agent-config-service/src/agent_config/migrations/ and self-applies them on every boot (each statement is idempotent — there is no applied-tracking table, so files must stay safe to re-run against newer state). Notable recent addition:

See the Database Schema page for column details.

Self-Applying Service Migrations (connector-service, R44a)

connector-service owns its numbered SQL migrations under services/connector-service/src/connector_service/migrations/ and self-applies them on every boot (auth-service runner pattern: every statement catalog-predicate-guarded, no applied-tracking, each file in its own transaction). The runner is connector_service/migrate.py, also runnable standalone as poetry run python -m connector_service.migrate. A failing migration boot-loops the service by design.

The CI double-boot test ci/connector_double_boot.sh guards the idempotency invariant (boot → boot again → schema must be identical).

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_checklists
poetry run python -m orchestration.seed_rules
poetry run python -m orchestration.seed_demo_data

Troubleshooting