Migrations

This page documents the Magikal Alembic migration workflow.

Purpose

Alembic migrations update the Postgres database schema safely and repeatably.

Use migrations when the bot database models change, such as:

• adding a new config table
• adding a new column
• changing indexes
• adding new persistent bot state
• changing recruitment, tickets, Temp VC, moderation, XP, or panel-backed storage

Important warning

Before running a real migration for a new bot change, confirm this documented process still matches the live VPS setup.

Magikal has had migration workflow issues before, so do not blindly trust old notes.

Before generating or applying migrations, re-check:

• current bot project path
• active Python virtual environment
• Alembic config path
• environment file path
• database URL variables
• current Alembic head
• latest valid database backup
• generated migration contents

Important rules

• Postgres is the source of truth.
• Alembic manages schema changes.
• Do not use SQLite.
• Do not run the system alembic command.
• Always use the project virtual environment.
• Always load the bot environment file first.
• Always review generated migrations before applying them.
• Do not apply destructive migrations without checking the generated file.
• Generate first, review second, upgrade last.

Magikal-specific rules

Run Alembic commands as the magikalbot Linux user.

Use root only for service restarts, system logs, Nginx, and server-level work.

Alembic should use the sync migration database URL from the environment, normally ALEMBIC_DATABASE_URL.

The live bot runtime may use an async database URL, but Alembic must run through the project virtual environment and migration config.

Alembic manages the cfg schema only.

It should not make changes to public.*.

If an autogenerated migration includes unrelated drops, public schema changes, unexpected index changes, or tables you did not intentionally edit, stop and review before applying.

Project location

Bot project:

/home/magikalbot/magikal-bot

Bot environment file:

/etc/magikal-bot.env

Alembic config:

/home/magikalbot/magikal-bot/migrations/alembic.ini

Standard command pattern

Run from the bot project:

cd /home/magikalbot/magikal-bot
set -a; . /etc/magikal-bot.env; set +a

Then use:

./.venv/bin/python -m alembic -c migrations/alembic.ini <command>

Do not use the system alembic binary.

Check current migration state

Run from the bot project:

cd /home/magikalbot/magikal-bot
set -a; . /etc/magikal-bot.env; set +a
./.venv/bin/python -m alembic -c migrations/alembic.ini current

Check available heads:

cd /home/magikalbot/magikal-bot
set -a; . /etc/magikal-bot.env; set +a
./.venv/bin/python -m alembic -c migrations/alembic.ini heads

Generate a migration

Only generate a migration after updating the SQLAlchemy models.

Run:

cd /home/magikalbot/magikal-bot
set -a; . /etc/magikal-bot.env; set +a
./.venv/bin/python -m alembic -c migrations/alembic.ini revision --autogenerate -m "describe change here"

Example:

./.venv/bin/python -m alembic -c migrations/alembic.ini revision --autogenerate -m "add tempvc live state table"

Review generated migration

After generating a migration, inspect the new file:

ls -lt migrations/versions | head

Then open the newest file and check:

• table names
• schema is cfg
• columns are correct
• no unexpected drops
• no accidental public schema changes
• no unrelated table/index removals
• downgrade is sensible or at least understood

Useful command:

git diff -- migrations/versions

Red flags in generated migrations

Stop before applying a migration if you see:

• drop_table
• drop_column
• unexpected drop_index
• changes outside the cfg schema
• unrelated table changes
• downgrade that deletes important data
• migration generated from stale models
• database connection errors
• missing environment variables

Backup check before upgrade

Check today's database backup:

ls -lh /home/magikalbot/backups/appdb-$(date +%F).dump

Check backup log:

tail -40 /home/magikalbot/backups/magikal-db-dump.log

Good signs:

backup written
backup complete

Do not continue if the latest backup is 0 bytes.

Git restore point before upgrade

Create a restore point before applying migration work:

cd /home/magikalbot/magikal-bot
git status
git add .
git commit -m "restore point before migration work"

Apply migration

Only apply after:

1. backup check passes
2. generated migration has been reviewed
3. Git restore point exists
4. unexpected autogenerated changes have been ruled out

Run:

cd /home/magikalbot/magikal-bot
set -a; . /etc/magikal-bot.env; set +a
./.venv/bin/python -m alembic -c migrations/alembic.ini upgrade head

Then check current state:

./.venv/bin/python -m alembic -c migrations/alembic.ini current

Restart after migration

Most schema changes require a bot restart after upgrade:

sudo systemctl restart magikal-bot
sudo systemctl status magikal-bot --no-pager

Check logs:

sudo journalctl -u magikal-bot -n 120 --no-pager

Common mistakes

Do not run:

alembic upgrade head

Use this instead:

./.venv/bin/python -m alembic -c migrations/alembic.ini upgrade head

Do not skip:

set -a; . /etc/magikal-bot.env; set +a

The environment file provides the database connection settings.

Safe migration order

Use this order:

1. Confirm this documented process still matches the live VPS setup.
2. Confirm latest database backup is valid and non-zero.
3. Update SQLAlchemy models.
4. Generate migration with autogenerate.
5. Review generated migration.
6. Stop if unexpected destructive changes appear.
7. Create Git restore point.
8. Apply migration.
9. Confirm Alembic current/head.
10. Restart bot if required.
11. Check logs.

Rule

Backup-check, generate, review, restore-point, then upgrade.

Never blindly apply an autogenerated migration.