Database Migrations - sgajbi/portfolio-analytics-system GitHub Wiki

Overview

The Portfolio Analytics System uses SQLAlchemy for ORM models and Alembic for database migrations. This ensures:

• Version-controlled schema changes
• Safe upgrades/downgrades
• Consistent deployment across environments

This page describes how to create, review, and apply migrations.

---

Database Stack

• ORM: SQLAlchemy (models in each service's database_models.py)
• Migration Tool: Alembic (configured per service, with a shared migration pattern)
• Database: PostgreSQL

---

Directory Structure

Each service has:

app/
  database_models.py   # SQLAlchemy models
  alembic/
    versions/          # Auto-generated migration scripts
    env.py             # Alembic environment config
    script.py.mako     # Migration script template

---

Alembic Workflow

1️⃣ Autogenerate a Migration

When schema changes are made in SQLAlchemy models:

alembic revision --autogenerate -m "feat: add correlation_id to processed_events"

This creates a migration file in alembic/versions/.

---

2️⃣ Review the Migration

Always inspect the generated script:

def upgrade():
    op.add_column('processed_events', sa.Column('correlation_id', sa.String(), nullable=True))

def downgrade():
    op.drop_column('processed_events', 'correlation_id')

✔ Ensure indexes, constraints, and default values are correct.

---

3️⃣ Apply the Migration

Apply changes to local/dev database:

alembic upgrade head

---

4️⃣ Verify the Migration

Confirm the schema matches expectations:

\d processed_events

---

5️⃣ Downgrade (if needed)

If a rollback is required:

alembic downgrade -1

⚠ Only use downgrade in dev/staging. In production, use forward-only migrations with fixes.

---

Best Practices

• Never edit old migrations — create new migration scripts for any change.
• Always test migrations on a staging environment before production.
• Use descriptive messages in -m flag (helps audit trail).
• Keep Alembic configs per service but ensure consistent naming of migrations.

---

Example: Adding Correlation ID Column

1. Modify model in database_models.py:

class ProcessedEvent(Base):
    __tablename__ = 'processed_events'
    event_id = Column(String, primary_key=True)
    service_name = Column(String, nullable=False)
    correlation_id = Column(String, nullable=True)

2. Generate migration:

alembic revision --autogenerate -m "feat: add correlation_id to processed_events"

3. Apply migration:

alembic upgrade head