Database Migrations

Database Migrations

Gova uses Alembic alongside SQLAlchemy to manage database schema versioning. This ensures that the PostgreSQL schema, including support for specialized types like PgVector and JSONB, remains consistent across different environments.

Requirements

Before running migrations, ensure your environment meets the following:

• PostgreSQL: A running instance with the pgvector extension installed.
• Environment Variables: Your DATABASE_URL must be configured in your .env file or environment.

Core Migration Workflow

The migration process follows the standard Alembic workflow. Because the project uses SQLAlchemy with asyncio, the migrations are executed using an asynchronous driver.

1. Generating a New Migration

When you modify models in src/db_models.py (e.g., adding a column to the Users or Moderators tables), generate a new migration script using the --autogenerate flag:

docker compose exec api alembic revision --autogenerate -m "description_of_changes"

This command compares your current database state with the definitions in db_models.py and creates a version script in the alembic/versions directory.

2. Applying Migrations

To update your database schema to the latest version, use the upgrade command:

docker compose exec api alembic upgrade head

3. Reverting Migrations

To roll back the last applied migration:

docker compose exec api alembic downgrade -1

Custom Migration Templates

Gova utilizes a custom migration template to support specific PostgreSQL features and project requirements:

• UUID v7 Support: The schema uses uuidv7() for primary keys to ensure time-ordered, unique identifiers. Migration scripts include the logic to ensure the database can handle these defaults.
• PgVector Extension: Since the project requires PgVector for content detection trends, the initial migration includes the command to enable the extension: CREATE EXTENSION IF NOT EXISTS vector;.
• Async Dispatch: The migration environment is configured to use the run_async wrapper, allowing Alembic to communicate with the database via the asyncpg driver.

Database Models Overview

When creating migrations, Alembic tracks the following core entities defined in src/db_models.py:

| Table | Description | | :--- | :--- | | users | Core user accounts, pricing tiers, and encrypted OAuth payloads. | | moderators | Bot configurations and status for specific platforms (e.g., Discord). | | evaluation_events | Logs of content evaluations including severity_score and behaviour_score. | | action_events | Record of automated or manual actions (Kicks, Timeouts, Replies) taken by moderators. |

Best Practices

• Review Autogenerated Code: Always inspect the generated migration script in alembic/versions/ before applying it. Autogeneration can occasionally miss manual constraints or specialized indexes.
• Docker Context: It is recommended to run migrations within the Docker container to ensure the environment matches the production runtime.
• Data Integrity: Avoid deleting columns in db_models.py without first ensuring that existing data in evaluation_events or action_events is either backed up or no longer needed.