Database Migrations

AuroraSOC uses Alembic for PostgreSQL schema migrations, integrated with SQLAlchemy 2.0 async models.

Setup

Alembic Configuration (alembic.ini)

[alembic]
script_location = alembic
sqlalchemy.url = postgresql+asyncpg://aurora:aurora@localhost:5432/aurorasoc

Migration Environment (alembic/env.py)

from aurorasoc.core.models import Base

target_metadata = Base.metadata

def run_migrations_online():
    connectable = create_async_engine(config.get_main_option("sqlalchemy.url"))
    
    async def do_run():
        async with connectable.connect() as connection:
            await connection.run_sync(do_run_migrations)
    
    asyncio.run(do_run())

Initial Schema (001_initial_schema.py)

The initial migration creates all 11 tables:

PostgreSQL Extensions

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";   -- UUID generation
CREATE EXTENSION IF NOT EXISTS "pg_trgm";     -- Trigram similarity for IOC search

Key Indexes

Table	Index	Type	Purpose
alerts	idx_alerts_severity	B-tree	Filter by severity
alerts	idx_alerts_status	B-tree	Filter by status
alerts	idx_alerts_created_at	B-tree	Time-range queries
alerts	idx_alerts_dedup_hash	B-tree	Deduplication lookup
cases	idx_cases_status	B-tree	Filter by status
cases	idx_cases_severity	B-tree	Filter by severity
cps_devices	idx_cps_device_id	B-tree	Device lookup
cps_devices	idx_cps_attestation_status	B-tree	Filter by attestation
iocs	idx_iocs_type_value	B-tree (unique)	Dedup + fast lookup
human_approvals	idx_approvals_status	B-tree	Pending approval filter

Auto-Update Trigger

CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ language 'plpgsql';

-- Applied to all tables with updated_at columns
CREATE TRIGGER update_alerts_updated_at
    BEFORE UPDATE ON alerts
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

Migration Commands

# Generate auto-migration from model changes
alembic revision --autogenerate -m "Description of change"

# Apply all pending migrations
alembic upgrade head

# Rollback one migration
alembic downgrade -1

# Rollback to specific revision
alembic downgrade 001

# Show current migration state
alembic current

# Show migration history
alembic history --verbose

Creating New Migrations

When you modify SQLAlchemy models in aurorasoc/core/models.py:

1. Make the model change:

class AlertModel(Base):
    # ... existing columns ...
    priority: Mapped[int] = mapped_column(Integer, default=3)  # NEW

2. Generate migration:

alembic revision --autogenerate -m "Add priority to alerts"

3. Review the generated migration (Alembic generates in alembic/versions/):

def upgrade():
    op.add_column('alerts', sa.Column('priority', sa.Integer(), nullable=True, default=3))

def downgrade():
    op.drop_column('alerts', 'priority')

4. Apply:

alembic upgrade head

Docker Integration

In Docker Compose, migrations run automatically on API startup:

# aurorasoc/core/database.py
async def init_db():
    engine = create_async_engine(settings.postgres.url)
    async with engine.begin() as conn:
        await conn.run_sync(Base.metadata.create_all)

For production deployments, use explicit Alembic commands:

# docker-compose.yml
api:
  command: >
    sh -c "alembic upgrade head && uvicorn aurorasoc.api.main:app --host 0.0.0.0"

JSONB Migration Patterns

JSONB columns don't require migrations for schema changes within the JSON structure. Adding a new key to an IOC's context field just works:

# Before: {"source": "virustotal"}
# After:  {"source": "virustotal", "confidence": 0.9}
# No migration needed — JSONB is schema-less

This is why AuroraSOC uses JSONB for flexible fields like iocs, mitre_techniques, metadata, and context.