Voicebox Database Migrations System: Automatic SQLite Schema Management for Desktop Apps

The Voicebox database migrations system automatically upgrades SQLite schemas on every application startup by inspecting existing tables, idempotently adding missing columns, and safely recreating tables when columns must be dropped, all without external tools like Alembic.

Voicebox persists data to a local SQLite file in the user-specific data directory and implements a lightweight, column-level migration pipeline that triggers before the FastAPI server begins accepting requests. Because the application ships as a bundled PyInstaller binary, it cannot rely on heavyweight migration frameworks, so developers created a self-contained system in backend/database/migrations.py that detects schema drift and repairs it automatically.

How the Migration System Initializes

The migration process begins inside init_db() in backend/database/session.py, which creates the SQLAlchemy engine and immediately invokes the migration runner before declaring table objects.

# backend/database/session.py

def init_db() -> None:
    """Initialize the database engine, run migrations, create tables, and seed data."""
    global engine, SessionLocal, _db_path

    _db_path = config.get_db_path()
    _db_path.parent.mkdir(parents=True, exist_ok=True)

    engine = create_engine(
        f"sqlite:///{_db_path}",
        connect_args={"check_same_thread": False},
    )

    SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

    # Migration step ---------------------------------------------------------

    run_migrations(engine)  # Inspects and repairs schema before table creation

    # -----------------------------------------------------------------------

    Base.metadata.create_all(bind=engine)  # Creates any missing tables

This guarantees that every user receives the latest schema regardless of which version they last ran, because the migration logic executes unconditionally on every startup.

The Migration Entry Point and Table Inspection

The run_migrations() function in backend/database/migrations.py acts as a dispatcher. It uses SQLAlchemy’s inspector to enumerate existing tables, then delegates to specialized helpers for each domain entity.

# backend/database/migrations.py

def run_migrations(engine) -> None:
    """Run all schema migrations. Safe to call on every startup."""
    inspector = inspect(engine)
    tables = set(inspector.get_table_names())

    _migrate_story_items(engine, inspector, tables)
    _migrate_profiles(engine, inspector, tables)
    _migrate_generations(engine, inspector, tables)
    _migrate_effect_presets(engine, inspector, tables)
    _migrate_generation_versions(engine, inspector, tables)
    _normalize_storage_paths(engine, tables)  # Path portability fix

Each helper receives the engine, inspector, and set of table names so it can perform existence checks before attempting modifications.

Idempotent Schema Changes: The Helper Pattern

All migrations share a common safety pattern: check if a column exists, then add it only if absent. The _add_column() helper encapsulates this logic using raw SQL ALTER TABLE statements.

def _add_column(engine, table: str, column_sql: str, label: str) -> None:
    """Add a column if it doesn't already exist."""
    with engine.connect() as conn:
        conn.execute(text(f"ALTER TABLE {table} ADD COLUMN {column_sql}"))
        conn.commit()
    logger.info("Added %s column to %s", label, table)

This pattern makes migrations idempotent—running them twice produces the same result as running them once, preventing errors during subsequent startups.

Handling Complex Migrations: The story_items Example

When a column must be removed (SQLite lacks native DROP COLUMN support), Voicebox migrates data to a new table schema. The _migrate_story_items() function demonstrates this by replacing the deprecated position column with start_time_ms.

def _migrate_story_items(engine, inspector, tables: set[str]) -> None:
    if "story_items" not in tables:
        return

    columns = _get_columns(inspector, "story_items")

    # Replace old `position` column with absolute `start_time_ms`

    if "position" in columns:
        logger.info("Migrating story_items: removing position column")
        with engine.connect() as conn:
            # 1. Add new column if missing

            if "start_time_ms" not in columns:
                conn.execute(text(
                    "ALTER TABLE story_items ADD COLUMN start_time_ms INTEGER DEFAULT 0"
                ))
                # 2. Populate from existing rows using JOIN to get durations

                result = conn.execute(text("""
                    SELECT si.id, si.story_id, si.position, g.duration
                    FROM story_items si
                    JOIN generations g ON si.generation_id = g.id
                    ORDER BY si.story_id, si.position
                """))
                # ... iteration logic to calculate cumulative time ...

                conn.commit()

            # 3. Recreate table without the `position` column

            conn.execute(text("CREATE TABLE story_items_new ( ... )"))
            conn.execute(text("INSERT INTO story_items_new SELECT ... FROM story_items"))
            conn.execute(text("DROP TABLE story_items"))
            conn.execute(text("ALTER TABLE story_items_new RENAME TO story_items"))
            conn.commit()
        
        columns = _get_columns(inspector, "story_items")

    # Add new optional columns that may be missing

    if "track" not in columns:
        _add_column(engine, "story_items", "track INTEGER DEFAULT 0", "track")

This approach preserves existing data by calculating new values from joined tables before dropping the old column, then adds subsequent columns using the safe _add_column() helper.

Normalizing Storage Paths Across Versions

Early Voicebox versions stored absolute file paths for audio assets. The _normalize_storage_paths() helper in backend/database/migrations.py rewrites these to relative paths during migration, ensuring portability when users move their data directory.

def _normalize_storage_paths(engine, tables: set[str]) -> None:
    """Normalize stored file paths to be relative to the configured data dir."""
    from pathlib import Path
    from ..config import get_data_dir, to_storage_path, resolve_storage_path

    data_dir = get_data_dir()
    path_columns = [
        ("generations", "audio_path"),
        ("generation_versions", "audio_path"),
        ("profile_samples", "audio_path"),
        ("profiles", "avatar_path"),
    ]
    # ... iterate rows and update absolute paths to relative ...

Located at lines 85-99 of migrations.py, this function runs automatically alongside schema changes to maintain data integrity across different machines.

Triggering Migrations on Application Startup

The migration pipeline hooks into FastAPI’s lifecycle events in backend/app.py. When the server starts, it calls database.init_db(), which triggers the entire migration sequence before the first API request arrives.

# backend/app.py

@application.on_event("startup")
async def startup_event():
    # ...

    database.init_db()  # Triggers run_migrations() internally

    # ...

This architecture guarantees that the database schema is always current without requiring manual CLI commands or user intervention.

Adding a New Migration to Voicebox

When evolving the schema, follow this pattern to ensure compatibility with existing user databases:

1. Append a helper function to backend/database/migrations.py
2. Register the helper in run_migrations() in dependency order
3. Check column existence with _get_columns() before adding
4. Use the table-recreation pattern only when dropping columns is unavoidable

def _migrate_new_feature(engine, inspector, tables: set[str]) -> None:
    if "new_feature" not in tables:
        # Create entire table if it never existed

        with engine.connect() as conn:
            conn.execute(text("""
                CREATE TABLE new_feature (
                    id VARCHAR PRIMARY KEY,
                    enabled BOOLEAN DEFAULT 0
                )
            """))
            conn.commit()
        return

    columns = _get_columns(inspector, "new_feature")
    if "enabled" not in columns:
        _add_column(engine, "new_feature", "enabled BOOLEAN DEFAULT 0", "enabled")

Register the new migration in the dispatcher:

def run_migrations(engine):
    # ... existing calls ...

    _migrate_new_feature(engine, inspector, tables)

When users launch the updated application, the migration applies automatically.

Summary

• Automatic execution: init_db() in backend/database/session.py calls run_migrations() on every startup, ensuring zero-downtime schema updates.
• Column-level safety: The system inspects existing columns via _get_columns() and uses _add_column() for idempotent additions, preventing duplicate column errors.
• Table recreation: For column removals, Voicebox migrates data to temporary tables and swaps them, working around SQLite’s ALTER TABLE limitations.
• Path normalization: The _normalize_storage_paths() helper converts absolute paths to relative paths during migration, enabling data directory portability.
• Startup integration: FastAPI’s startup event in backend/app.py guarantees migrations complete before the API serves requests.

Frequently Asked Questions

How do I add a new column to an existing table in Voicebox?

Create a helper function in backend/database/migrations.py that checks _get_columns() for the column name, then calls _add_column() with the SQL type definition. Register the helper in run_migrations(). When the application restarts, it will detect the missing column and add it automatically without affecting existing data.

Why doesn't Voicebox use Alembic for database migrations?

Because Voicebox ships as a PyInstaller binary to end users, it cannot rely on external CLI tools or heavyweight dependencies. The custom migration system in backend/database/migrations.py provides a lightweight, self-contained alternative that inspects the SQLite schema directly and performs safe, idempotent modifications using raw SQL executed through SQLAlchemy.

What happens if a migration fails during startup?

If a migration raises an exception, FastAPI’s startup event fails and the application will not begin serving requests. This prevents the API from running against an incompatible schema. The error logs in backend/database/migrations.py indicate which specific table or column operation failed, allowing developers to inspect the database state or adjust the migration logic.

How are file paths handled during database migrations?

The _normalize_storage_paths() helper automatically runs during every migration cycle to convert absolute file paths to relative paths stored in the database. This ensures that if a user moves their Voicebox data directory to a new location or different machine, the application can still locate audio files and avatars by resolving them against the configured data directory.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}