Skip to content

DATABASE.md

Latest commit

 

History

History
297 lines (206 loc) · 5.86 KB

DATABASE.md

File metadata and controls

297 lines (206 loc) · 5.86 KB

Database Management

ContentEngine uses SQLite with Alembic for schema migrations.

Quick Start

First Time Setup

# Initialize database with latest schema
uv run alembic upgrade head

Daily Development

# Check current migration version
uv run alembic current

# View migration history
uv run alembic history

# Upgrade to latest
uv run alembic upgrade head

Common Operations

Creating a New Migration

After modifying models in lib/database.py:

# Autogenerate migration from model changes
uv run alembic revision --autogenerate -m "Add user preferences table"

# Review generated file in alembic/versions/
# Edit if needed (Alembic sometimes misses things)

# Apply migration
uv run alembic upgrade head

# Commit migration file to git
git add alembic/versions/xxx_add_user_preferences_table.py
git commit -m "db: add user preferences table"

Upgrading Database

# Upgrade to latest version
uv run alembic upgrade head

# Upgrade to specific version
uv run alembic upgrade abc123

# Upgrade by +2 versions
uv run alembic upgrade +2

Downgrading Database

# Downgrade by one version
uv run alembic downgrade -1

# Downgrade to specific version
uv run alembic downgrade abc123

# Downgrade to base (empty database)
uv run alembic downgrade base

Checking Status

# Show current version
uv run alembic current

# Show migration history
uv run alembic history

# Show detailed history with file paths
uv run alembic history --verbose

Resetting Database (DESTRUCTIVE)

# Backup first!
cp content.db content.db.backup-$(date +%Y%m%d-%H%M%S)

# Delete database
rm content.db

# Recreate with latest schema
uv run alembic upgrade head

Helper Script

Use the helper script for common operations:

# Initialize database
python scripts/db_migrate.py init

# Upgrade to latest
python scripts/db_migrate.py upgrade

# Check current version
python scripts/db_migrate.py current

# Create new migration
python scripts/db_migrate.py create "Add feature X"

# Reset database (DESTRUCTIVE)
python scripts/db_migrate.py reset

Migration Best Practices

1. Always Review Autogenerated Migrations

Alembic's autogenerate is smart but not perfect:

  • Check for missing indexes
  • Verify foreign key constraints
  • Check for data loss in downgrades
  • Add data migrations if needed

2. Test Migrations Locally First

# Test upgrade
uv run alembic upgrade head

# Test downgrade
uv run alembic downgrade -1

# Test re-upgrade
uv run alembic upgrade head

3. Data Migrations

For migrations that need to transform data:

def upgrade() -> None:
    # Add new column with default
    op.add_column('users', sa.Column('full_name', sa.String(500), nullable=True))

    # Migrate data
    connection = op.get_bind()
    connection.execute(
        "UPDATE users SET full_name = name WHERE full_name IS NULL"
    )

    # Make column non-nullable
    op.alter_column('users', 'full_name', nullable=False)

4. Handling Enums

SQLite doesn't support ALTER for enums. Use this pattern:

def upgrade() -> None:
    # Create new table with updated enum
    op.create_table('posts_new', ...)

    # Copy data
    op.execute("INSERT INTO posts_new SELECT * FROM posts")

    # Swap tables
    op.drop_table('posts')
    op.rename_table('posts_new', 'posts')

Moving Database Between Environments

Export Schema + Data (Dev → Staging)

# On dev machine
cp content.db staging-import.db
scp staging-import.db user@staging-server:/path/to/ContentEngine/content.db

# On staging server
cd /path/to/ContentEngine
uv run alembic current  # Verify migration version matches

Export Schema Only (Fresh Environment)

# On new machine
git clone <repo>
cd ContentEngine
uv sync

# Apply all migrations
uv run alembic upgrade head

# Database is now at latest schema (empty tables)

Production Deployment

# Pull latest code
git pull origin main

# Backup database
cp content.db content.db.backup-$(date +%Y%m%d-%H%M%S)

# Apply migrations
uv run alembic upgrade head

# Restart services
systemctl --user restart content-engine.service

Troubleshooting

"Can't locate revision identified by 'abc123'"

Migration file is missing. Check:

ls alembic/versions/
git log -- alembic/versions/

Solution: Pull missing migration files from git.

"Target database is not up to date"

Database is behind. Run:

uv run alembic upgrade head

"Multiple head revisions are present"

Conflicting migrations from different branches. Merge them:

uv run alembic merge heads -m "Merge migrations"

Reset Alembic Version Table

# Backup first!
cp content.db content.db.backup

# Remove version tracking
sqlite3 content.db "DROP TABLE IF EXISTS alembic_version"

# Re-stamp to current version
uv run alembic stamp head

Database Schema

Current Tables

  • users - User accounts (LinkedIn OAuth)

    • id, linkedin_sub, email, name, profile_picture_url
    • access_token, refresh_token, token_expires_at
    • created_at, last_login_at

  • sessions - User session management

    • id (UUID), user_id, created_at, expires_at

  • posts - Content posts (all platforms)

    • id, content, platform, status
    • user_id, is_demo
    • created_at, updated_at, scheduled_at, posted_at
    • external_id, error_message

  • chat_messages - AI chat history

    • id, user_id, role, content, created_at

  • oauth_tokens - Platform OAuth tokens

    • id, platform, access_token, refresh_token, expires_at
    • user_sub, user_email
    • created_at, updated_at

Schema Evolution

See alembic/versions/ for complete migration history.

Additional Resources