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

> Learn Alembic migration setup for SQLite in VoiceStudio's backend/core. Discover essential tables like voice_profiles, voices, projects, and project_voices for effective voice and project management.

- Repository: [Palash Debnath/VoiceStudio](https://github.com/debpalash/VoiceStudio)
- Tags: how-to-guide
- Published: 2026-09-06

---

**VoiceStudio uses Alembic and SQLAlchemy to manage its SQLite database schema, with migrations configured in [`alembic.ini`](https://github.com/debpalash/VoiceStudio/blob/main/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`](https://github.com/debpalash/VoiceStudio/blob/main/alembic.ini) Configuration File

The root-level [`alembic.ini`](https://github.com/debpalash/VoiceStudio/blob/main/alembic.ini) file contains the foundational Alembic settings:

```ini

# 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`](https://github.com/debpalash/VoiceStudio/blob/main/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`](https://github.com/debpalash/VoiceStudio/blob/main/env.py)

The [`backend/migrations/env.py`](https://github.com/debpalash/VoiceStudio/blob/main/backend/migrations/env.py) file bridges Alembic with VoiceStudio's configuration system:

```python

# 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`](https://github.com/debpalash/VoiceStudio/blob/main/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:

```python
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`](https://github.com/debpalash/VoiceStudio/blob/main/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:

```python

# 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`](https://github.com/debpalash/VoiceStudio/blob/main/backend/core/db.py) provides `_run_alembic_upgrade()` to apply pending migrations:

```python

# 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:

```python

# 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 |

```python

# 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:

```python

# 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:

```python
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`](https://github.com/debpalash/VoiceStudio/blob/main/alembic.ini) with dynamic URL resolution via [`backend/migrations/env.py`](https://github.com/debpalash/VoiceStudio/blob/main/backend/migrations/env.py) importing `backend.core.config`
- **Two-phase initialization** uses `_BASE_SCHEMA` SQL in [`backend/core/db.py`](https://github.com/debpalash/VoiceStudio/blob/main/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`](https://github.com/debpalash/VoiceStudio/blob/main/backend/core/config.py). If unset, it defaults to `voice_studio.db` in the working directory. The [`backend/migrations/env.py`](https://github.com/debpalash/VoiceStudio/blob/main/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`](https://github.com/debpalash/VoiceStudio/blob/main/backend/core/config.py) and [`backend/core/db.py`](https://github.com/debpalash/VoiceStudio/blob/main/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.