Database Migrations Guide

This project uses Alembic for database schema migrations.

Overview

Alembic provides a way to version control database schema changes, making it easier to:

  • Track schema evolution over time
  • Apply migrations consistently across environments
  • Rollback changes if needed
  • Collaborate on schema changes

Setup

  1. Install dependencies: 
    pip install -r requirements.txt

    This will install Alembic and SQLAlchemy (required for Alembic).

  2. Configure database URL: The migration system uses DATABASE_URL from config.py (loaded from environment variables).

    Ensure your .env file contains:

    DATABASE_URL=postgresql://user:password@host:port/database

  3. Important for existing databases:

    If your database already has tables (production), you need to mark the baseline migration as applied:

    alembic stamp head

    This tells Alembic that the current database state matches the baseline migration, without actually running it.

    If your database is empty, you can run:

    alembic upgrade head

Running Migrations

Check Migration Status

alembic current

Shows the current revision of the database.

alembic history

Shows all available migrations and their revision chain.

Apply Migrations

alembic upgrade head

Applies all pending migrations up to the latest version.

alembic upgrade +1

Applies the next migration only.

Rollback Migrations

alembic downgrade -1

Rolls back the last migration.

alembic downgrade base

Rolls back all migrations (⚠️ DESTRUCTIVE - use with caution).

Creating New Migrations

Auto-generate Migration

alembic revision --autogenerate -m "Description of changes"

This will analyze the current database state and generate a migration file. Always review the generated migration before applying it.

Manual Migration

alembic revision -m "Description of changes"

Creates an empty migration file that you can fill in manually.

Migration Files

Migrations are stored in alembic/versions/ with the format:

  • 001_initial_schema.py - Baseline migration
  • 002_add_feature_x.py - Feature-specific migrations
  • etc.

Each migration file contains:

  • revision: Unique identifier for this migration
  • down_revision: The previous migration (forms a chain)
  • upgrade(): Function that applies the migration
  • downgrade(): Function that rolls back the migration

Best Practices

  1. Always test migrations on a development/staging database first
  2. Review auto-generated migrations - Alembic may not detect all changes correctly
  3. Keep migrations small - One logical change per migration
  4. Never edit applied migrations - Create a new migration to fix issues
  5. Backup before major migrations - Especially when dropping tables or columns
  6. Use transactions - Alembic wraps migrations in transactions by default

Integration with Bot

The bot can check migration status on startup. To enable automatic migration checks:

  1. Add migration check to bot.py startup hook
  2. Use /migrate status command to check status manually
  3. Use /migrate command (admin only) to apply migrations

Troubleshooting

Migration conflicts

If migrations are out of sync:

# Check current state
alembic current

# See what's pending
alembic heads

# If needed, mark current state manually
alembic stamp head

Database connection issues

Ensure DATABASE_URL is set correctly:

export DATABASE_URL="postgresql://user:pass@localhost/dbname"

Migration fails mid-way

If a migration fails partway through:

  1. Check the error message
  2. Fix the migration file if needed
  3. Manually fix the database state if necessary
  4. Use alembic stamp to mark the correct revision

Migration Workflow

  1. Development: Create migration locally 
    alembic revision --autogenerate -m "Add new feature"

  2. Review: Check the generated migration file

  3. Test: Apply migration on dev database 
    alembic upgrade head

  4. Commit: Add migration file to git

  5. Deploy: Apply migrations on production 
    alembic upgrade head

Current Schema

The baseline migration (001_initial_schema.py) includes:

  • bot_settings - Guild-specific bot configuration
  • settings_history - Audit trail for settings changes
  • reminders - Reminder system data
  • onboarding - User onboarding responses
  • guild_onboarding_questions - Onboarding question definitions
  • guild_rules - Guild rules
  • support_tickets - Support ticket system
  • faq_entries - FAQ entries
  • faq_search_logs - FAQ search analytics
  • audit_logs - Command usage analytics
  • health_check_history - Health check trend data

References