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_URLmust be configured in your.envfile 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
PgVectorfor 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_asyncwrapper, allowing Alembic to communicate with the database via theasyncpgdriver.
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.pywithout first ensuring that existing data inevaluation_eventsoraction_eventsis either backed up or no longer needed.