Database Migration Guide

This guide explains how to work with database migrations in the Meta Agent Platform using Alembic and SQLAlchemy.

Prerequisites

• PostgreSQL database running (via Docker or locally)
• Project dependencies installed
• .env file configured with database connection details

Database Connection Configuration

Ensure your .env file contains the following database configuration:

# Database settings
POSTGRES_SERVER=localhost
POSTGRES_USER=user
POSTGRES_PASSWORD=password
POSTGRES_DB=appdb
POSTGRES_PORT=5432
DATABASE_URL=postgresql://user:password@localhost:5432/appdb

Starting the Database

If you're using Docker, start the PostgreSQL database container:

cd backend
docker-compose up -d db

Working with Migrations

Viewing Current Migration Status

To check the current state of migrations:

cd backend
uv run alembic current

Creating a New Migration

To create a new migration script:

cd backend
uv run alembic revision --autogenerate -m "description of changes"

For example:

uv run alembic revision --autogenerate -m "add user table"

Applying Migrations

To apply all pending migrations:

cd backend
uv run alembic upgrade head

To apply a specific number of migrations:

uv run alembic upgrade +1

Reverting Migrations

To revert the most recent migration:

cd backend
uv run alembic downgrade -1

To revert to a specific migration version:

uv run alembic downgrade <revision_id>

To revert all migrations:

uv run alembic downgrade base

Database Schema

Current Models

User Model

The User model is defined in backend/app/models/user.py and includes the following fields:

• id: UUID primary key
• keycloak_id: String, unique identifier from Keycloak
• email: String, unique email address
• username: String, unique username
• first_name: String, optional
• last_name: String, optional
• is_active: Boolean, default True
• is_superuser: Boolean, default False
• profile_data: JSON, optional additional user data
• created_at: DateTime, automatically set on creation
• updated_at: DateTime, automatically updated

Adding New Models

To add a new model to the database:

1. Create a new model file in backend/app/models/ or add to an existing file
2. Define your model class inheriting from Base
3. Import the model in backend/app/db/init_db.py
4. Generate a new migration with uv run alembic revision --autogenerate -m "add new model"
5. Apply the migration with uv run alembic upgrade head

Example model definition:

from sqlalchemy import Column, String, Boolean, DateTime, ForeignKey
from sqlalchemy.dialects.postgresql import UUID
from sqlalchemy.sql import func
import uuid

from app.db.base_class import Base

class NewModel(Base):
    __tablename__ = "new_model"

    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    name = Column(String, nullable=False)
    description = Column(String, nullable=True)
    user_id = Column(UUID(as_uuid=True), ForeignKey("user.id"), nullable=False)
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    updated_at = Column(DateTime(timezone=True), onupdate=func.now())

Troubleshooting

Migration Conflicts

If you encounter conflicts during migration:

1. Revert to a stable migration: uv run alembic downgrade <stable_revision_id>
2. Fix the model definitions
3. Create a new migration: uv run alembic revision --autogenerate -m "fix conflicts"
4. Apply the new migration: uv run alembic upgrade head

Database Connection Issues

If you encounter database connection issues:

1. Verify the database is running: docker ps (if using Docker)
2. Check your .env file for correct connection details
3. Try connecting directly to the database: docker-compose exec db psql -U user -d appdb

Inspecting the Database

To inspect the current database schema:

docker-compose exec db psql -U user -d appdb -c "\dt"

To view a specific table:

docker-compose exec db psql -U user -d appdb -c "SELECT * FROM \"user\";"