Skip to content

🗄️ Database Rules

This page documents the database rules for the Magikal bot and web panel.

Core rule

Postgres is the source of truth.

Persistent bot data, guild configuration, moderation records, tickets, recruitment state, Temp VC state, XP, banking, events, and panel-backed settings should live in Postgres.

Do not introduce SQLite unless explicitly approved.

Main database files

Area File / Path
Database models and manager /home/magikalbot/magikal-bot/bot/database.py
Postgres config adapter /home/magikalbot/magikal-bot/bot/storage/pg_config.py
Postgres state adapter /home/magikalbot/magikal-bot/bot/storage/pg_state.py
Redis adapter /home/magikalbot/magikal-bot/bot/storage/redis.py
Ephemeral fallback storage /home/magikalbot/magikal-bot/bot/storage/ephemeral.py
Alembic config /home/magikalbot/magikal-bot/migrations/alembic.ini
Alembic migrations /home/magikalbot/magikal-bot/migrations/versions/

Normal cog access pattern

Normal cogs should use:

self.config_storage

or:

self.bot.config_storage

The usual live adapter is:

PgConfigStorage

This keeps feature cogs cleaner and avoids spreading raw SQL/session logic across the project.

Avoid direct DB access in cogs

Do not normally do this in new cogs:

self.db = bot.database_manager
async with self.db.session_factory() as s:
    ...

Direct database/session access is an advanced exception.

It should only be used when explicitly approved and when the normal storage adapter is not suitable.

Current exception

The live banking.py cog currently uses:

bot.database_manager

and direct database sessions.

Treat this as an existing approved/legacy exception.

Do not copy that pattern into new cogs unless the architecture is reviewed first.

Storage adapter rule

When adding a new persistent feature, prefer this flow:

  1. Add model/table logic in bot/database.py.
  2. Add clean access methods on the database manager.
  3. Expose feature-friendly methods through PgConfigStorage.
  4. Use self.config_storage from the cog.
  5. Add an Alembic migration.
  6. Test and document the new storage behaviour.

Schema rule

Alembic should manage the cfg schema.

Do not let migrations accidentally change public.*.

If autogenerate shows unrelated changes, stop and review.

When Alembic is needed

Use Alembic when changing persistent schema, such as:

  • adding a table
  • adding a column
  • removing a column
  • changing a column type
  • adding persistent state
  • adding indexes
  • adding constraints
  • changing stored feature config

Do not manually edit the live database schema as a shortcut.

Migration safety

Before migration work:

  1. Confirm the documented migration process still matches the live VPS setup.
  2. Confirm the latest database backup is non-zero.
  3. Create or confirm a Git restore point.
  4. Generate the migration.
  5. Review the generated migration.
  6. Stop if unexpected destructive changes appear.
  7. Apply migration.
  8. Restart affected service if required.
  9. Check logs.

Backup check before database work

Check today's 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.

Redis rule

Redis is not the primary database.

Redis may be used only for approved sensitive or ephemeral helper data.

Examples of acceptable Redis-style use:

  • temporary session-style data
  • sensitive PID to identity mapping where still required
  • short-lived helper state

Do not use Redis for durable guild config, moderation records, tickets, recruitment records, XP, banking, or other long-term source-of-truth data.

Ephemeral storage rule

In-process ephemeral storage is only a fallback.

It is not reliable across bot restarts.

Do not use ephemeral storage for anything that must survive:

  • bot restart
  • VPS reboot
  • deploy
  • crash
  • cog reload

Temp VC state

Temp VC runtime state should be persistent enough to recover from bot restarts where required.

Current project direction is Postgres-backed Temp VC live state through the state storage layer.

Do not add new Redis-heavy Temp VC write loops without review.

User identifiers

User-related database records should use privacy-safe PIDs where appropriate.

Use:

make_pid(guild_id, user_id)

Do not store raw Discord user IDs in new tables unless explicitly approved for a specific technical reason.

Logging database errors

Database errors should be logged safely.

Good:

logger.warning(event="feature.persist_failed", error=str(e))

Avoid:

logger.error(event="db.failed", database_url=url)
logger.error(event="db.failed", user_id=user.id)
logger.error(event="db.failed", payload=full_payload)

Do not log:

  • database URLs
  • passwords
  • secrets
  • tokens
  • raw Discord IDs
  • full private payloads

Panel-backed config

Panel-backed settings should still flow through the same storage model.

The panel should not invent a separate source of truth.

Expected flow:

Web panel -> API -> Postgres-backed config/storage -> bot reads config

The bot should not have one version of config while the panel saves a different version somewhere else.

Red flags

Stop and review if a database change:

  • needs schema changes
  • touches bot/database.py
  • adds a new table
  • changes migrations
  • touches tickets/recruitment state
  • touches moderation records
  • touches Temp VC state
  • touches XP/banking/event persistence
  • changes Redis usage
  • introduces SQLite
  • uses raw Discord IDs
  • creates direct DB access inside a cog
  • changes backup or restore behaviour

Rule

Persistent state belongs in Postgres, accessed through clean storage layers, protected by backups, migrations, and privacy-safe IDs.