Alembic Migration Setup for SQLite in VoiceStudio's `backend/core/` and Core Tables for Voices & Projects

VoiceStudio uses Alembic and SQLAlchemy to manage its SQLite database schema, with migrations configured in alembic.ini and core tables including voice_profiles, voices, projects, and project_voices for voice and project management.

This guide explains the complete Alembic migration setup for SQLite in VoiceStudio's backend/core/ architecture. The debpalash/VoiceStudio repository implements a robust database migration system that handles schema initialization, version control, and runtime configuration for voice AI applications.

How Alembic Is Configured for SQLite

VoiceStudio's migration system separates configuration from runtime behavior to support flexible deployment environments.

The alembic.ini Configuration File

The root-level alembic.ini file contains the foundational Alembic settings:

# alembic.ini

[alembic]
script_location = %(here)s/backend/migrations
sqlalchemy.url = 

Two critical design decisions appear here:

• script_location — Points to backend/migrations where revision scripts reside
• sqlalchemy.url — Left empty intentionally; populated at runtime via backend/migrations/env.py

This pattern allows the same codebase to target different SQLite files across development, testing, and production environments.

Runtime Database URL Resolution in env.py

The backend/migrations/env.py file bridges Alembic with VoiceStudio's configuration system:

# backend/migrations/env.py

from backend.core.config import get_db_url
from backend.core.db import engine

# ... inside run_migrations_online()

with engine.connect() as connection:
    context.configure(
        connection=connection,
        target_metadata=target_metadata,
        # ...

    )

This approach ensures Alembic operates against the identical engine that the application uses, preventing schema drift between migration tools and runtime code.

Database Path Configuration

VoiceStudio determines the SQLite file location through backend/core/config.py:

Configuration Source	Priority	Example Value
VOICESTUDIO_DB_PATH environment variable	Highest	/data/voice_studio.db
Default fallback	Lowest	voice_studio.db (relative)

The configuration module exposes get_db_url() which returns a SQLAlchemy-compatible connection string:

from backend.core.config import get_db_url

db_url = get_db_url()  # Returns: sqlite:///voice_studio.db

Schema Initialization: Base Schema vs. Alembic Migrations

VoiceStudio uses a two-phase database setup strategy defined in backend/core/db.py.

Phase 1: _BASE_SCHEMA for Initial Table Creation

When init_db() detects a missing database file, it executes the _BASE_SCHEMA SQL string:

# backend/core/db.py

_BASE_SCHEMA = """
CREATE TABLE IF NOT EXISTS voice_profiles (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    language TEXT,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS voices (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    profile_id INTEGER NOT NULL,
    file_path TEXT NOT NULL,
    duration REAL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (profile_id) REFERENCES voice_profiles(id)
);

CREATE TABLE IF NOT EXISTS projects (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    owner_id INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS project_voices (
    project_id INTEGER NOT NULL,
    voice_id INTEGER NOT NULL,
    PRIMARY KEY (project_id, voice_id),
    FOREIGN KEY (project_id) REFERENCES projects(id),
    FOREIGN KEY (voice_id) REFERENCES voices(id)
);
"""

def init_db():
    """Create database file and apply base schema if not exists."""
    # Implementation creates engine, executes _BASE_SCHEMA

    pass

Phase 2: Alembic Upgrades for Schema Evolution

After initial creation, backend/core/db.py provides _run_alembic_upgrade() to apply pending migrations:

# backend/core/db.py

def _run_alembic_upgrade():
    """Apply Alembic migrations to reach latest schema version."""
    from alembic import command
    from alembic.config import Config
    import os
    
    repo_root = os.path.dirname(os.path.dirname(__file__))
    cfg = Config(os.path.join(repo_root, "..", "alembic.ini"))
    command.upgrade(cfg, "head")

Migration scripts in backend/migrations/versions/ use SQLAlchemy's operation helpers:

# Example revision file in backend/migrations/versions/

from alembic import op
import sqlalchemy as sa

def upgrade():
    op.add_column('voice_profiles', sa.Column('sample_rate', sa.Integer()))
    op.create_index('ix_voices_created_at', 'voices', ['created_at'])

def downgrade():
    op.drop_column('voice_profiles', 'sample_rate')
    op.drop_index('ix_voices_created_at', table_name='voices')

Crucial Tables for Voices and Projects

Four tables form the core data model for VoiceStudio's voice and project functionality.

voice_profiles Table

Stores metadata for reusable voice configurations:

Column	Type	Purpose
id	INTEGER PRIMARY KEY	Unique identifier
name	TEXT NOT NULL	Display name for the profile
language	TEXT	Language/locale code
description	TEXT	User-provided notes
created_at	TIMESTAMP	Creation timestamp
updated_at	TIMESTAMP	Last modification timestamp

# Query voice profiles

from backend.core.db import engine
from sqlalchemy import select, Table, MetaData

meta = MetaData()
meta.reflect(bind=engine)
profiles = Table("voice_profiles", meta)

with engine.connect() as conn:
    result = conn.execute(select(profiles).where(profiles.c.language == "en-US"))
    for row in result:
        print(f"Profile: {row.name} (created {row.created_at})")

voices Table

Represents individual generated voice recordings:

Column	Type	Purpose
id	INTEGER PRIMARY KEY	Unique identifier
profile_id	INTEGER FOREIGN KEY	Links to voice_profiles.id
file_path	TEXT NOT NULL	Filesystem path to audio file
duration	REAL	Audio length in seconds
created_at	TIMESTAMP	Generation timestamp

The profile_id foreign key enforces referential integrity: deleting a profile requires explicit handling of associated voices.

projects Table

Groups related voice assets under user-defined collections:

Column	Type	Purpose
id	INTEGER PRIMARY KEY	Unique identifier
title	TEXT NOT NULL	Project name
owner_id	INTEGER	User or system owner reference
created_at	TIMESTAMP	Creation timestamp
updated_at	TIMESTAMP	Last modification timestamp

project_voices Junction Table

Enables many-to-many relationships between projects and voices:

Column	Type	Purpose
project_id	INTEGER FOREIGN KEY	Links to projects.id (composite PK)
voice_id	INTEGER FOREIGN KEY	Links to voices.id (composite PK)

This design allows:

• A single voice to belong to multiple projects
• A project to contain numerous voices
• Efficient querying of project contents without data duplication

Running Migrations Programmatically

VoiceStudio's test suite demonstrates production-ready migration handling:

# From tests/test_worker_remote_migration.py pattern

import os
from alembic import command
from alembic.config import Config

def migrate_to_latest():
    """Upgrade database to head revision."""
    repo_root = os.path.abspath(
        os.path.join(__file__, "..", "..", "..")
    )
    cfg = Config(os.path.join(repo_root, "alembic.ini"))
    command.upgrade(cfg, "head")

def get_current_revision():
    """Check current database version."""
    from alembic.migration import MigrationContext
    from backend.core.db import engine
    
    with engine.connect() as conn:
        context = MigrationContext.configure(conn)
        return context.get_current_revision()

Complete Database Initialization Workflow

The canonical sequence for preparing a fresh VoiceStudio installation:

from backend.core.db import init_db, _run_alembic_upgrade

# Step 1: Create SQLite file and base tables

init_db()

# Step 2: Apply any Alembic revisions beyond base schema

_run_alembic_upgrade()

# Database is now ready for application use

Summary

• Alembic configuration lives in alembic.ini with dynamic URL resolution via backend/migrations/env.py importing backend.core.config
• Two-phase initialization uses _BASE_SCHEMA SQL in backend/core/db.py for table creation, followed by Alembic upgrades for modifications
• voice_profiles stores reusable voice configurations with language and description metadata
• voices contains generated audio file references with duration tracking and foreign key links to profiles
• projects provides grouping containers with title and ownership fields
• project_voices implements many-to-many relationships linking voices to projects

Frequently Asked Questions

How does VoiceStudio handle different database paths across environments?

VoiceStudio reads the VOICESTUDIO_DB_PATH environment variable through backend/core/config.py. If unset, it defaults to voice_studio.db in the working directory. The backend/migrations/env.py script imports this configuration at runtime, ensuring migrations target the same database file as the application.

What happens if I run init_db() on an existing database?

The init_db() function checks for database existence before executing _BASE_SCHEMA. On existing databases, it skips schema creation entirely. Subsequent calls to _run_alembic_upgrade() safely apply only pending migrations through Alembic's version tracking system.

Can I use VoiceStudio's migration system with PostgreSQL instead of SQLite?

The current backend/core/config.py and backend/core/db.py implementation hard-codes SQLite-specific patterns including the _BASE_SCHEMA SQL syntax and file-based path handling. Adapting for PostgreSQL would require modifying get_db_url() to return PostgreSQL connection strings and adjusting _BASE_SCHEMA for dialect differences in auto-increment and timestamp handling.

Why does VoiceStudio use both SQL schema strings and Alembic migrations?

The hybrid approach solves the cold-start problem: _BASE_SCHEMA creates a functional database immediately without requiring Alembic installation or migration history. Alembic then manages incremental changes without requiring full schema regeneration. This ensures new deployments work instantly while preserving upgrade paths for existing installations.

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