UmbraAudio/specs/08-database.md

Spec: Database Schema

Overview

UmbraAudio uses SQLite for all project state. The database tracks the full lifecycle from parsed manuscript through generated audio. Character discovery and voice assignments are stored in JSON files for flexibility.

Current Implementation

Actual Schema (project.db)

-- Project metadata
CREATE TABLE IF NOT EXISTS project (
    id              INTEGER PRIMARY KEY,
    name            TEXT NOT NULL,
    source_path     TEXT NOT NULL,
    source_format   TEXT NOT NULL,
    created_at      TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at      TEXT NOT NULL DEFAULT (datetime('now')),
    status          TEXT NOT NULL DEFAULT 'initialized'
);

-- Chapters with source text
CREATE TABLE IF NOT EXISTS chapters (
    id              INTEGER PRIMARY KEY,
    project_id      INTEGER NOT NULL,
    chapter_index   INTEGER NOT NULL,
    title           TEXT,
    source_text     TEXT NOT NULL,
    word_count      INTEGER NOT NULL DEFAULT 0,
    status          TEXT NOT NULL DEFAULT 'parsed',
    audio_path      TEXT,
    created_at      TEXT NOT NULL DEFAULT (datetime('now'))
);

-- Utterances (narration and dialogue)
CREATE TABLE IF NOT EXISTS utterances (
    id              INTEGER PRIMARY KEY,
    chapter_id      INTEGER NOT NULL,
    character_id    INTEGER,
    utterance_index INTEGER NOT NULL,
    text            TEXT NOT NULL,
    type            TEXT NOT NULL DEFAULT 'narration',
    context_before  TEXT,
    context_after   TEXT,
    emotion         TEXT,
    pace            TEXT,
    emphasis_words  TEXT,
    pause_before_ms INTEGER,
    pause_after_ms  INTEGER,
    instruct        TEXT,
    speaker_name    TEXT,
    status          TEXT NOT NULL DEFAULT 'pending'
);

-- Generated audio files
CREATE TABLE IF NOT EXISTS generated_audio (
    id                  INTEGER PRIMARY KEY,
    utterance_id        INTEGER NOT NULL,
    character_id        INTEGER,
    audio_path          TEXT NOT NULL,
    duration_ms         INTEGER,
    mos_score           REAL,
    wer_score           REAL,
    speaker_similarity  REAL,
    artifact_score      REAL,
    generation_params   TEXT,
    status              TEXT NOT NULL DEFAULT 'generated',
    created_at          TEXT NOT NULL DEFAULT (datetime('now'))
);

JSON Data Files

speakers.json

{
  "speakers": [
    {
      "primary_name": "Kainda",
      "aliases": ["Princess Kainda", "Princess", "Captain", "XO Capek"],
      "demographics": {
        "type": "Human",
        "gender": "Female"
      },
      "vocal_traits": ["assertive", "clear", "young"],
      "communication_style": {
        "primary_register": "Formal",
        "style_shifts_or_notes": "Shifts to casual with close allies"
      },
      "is_narrator": false,
      "is_group": false,
      "description": "Princess and communication specialist",
      "sample_dialogue": "I'm on a yacht.",
      "chapters_appeared": [5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31]
    }
  ]
}

voice_assignments.json

{
  "Narrator": "en-US-GuyNeural:-10",
  "Kainda": "en-US-JennyNeural",
  "Captain Weston": "en-US-ChristopherNeural",
  "Hunter": "en-US-JasonNeural",
  "Nanny": "en-GB-SoniaNeural",
  "Aunt Vicky": "en-US-AvaNeural",
  "Admiral Wake": "en-US-AndrewNeural",
  "Princess Kainda": "en-US-JennyNeural",
  "Doctor": "en-US-GuyNeural",
  "Guards": "en-US-GuyNeural:+48"
}

Entity Relationships

project (1) ──< chapters (many)
chapters (1) ──< utterances (many)
utterances (1) ──< generated_audio (many)

speakers.json ──< (character profiles)
voice_assignments.json ──< (voice mappings)

Key Differences from Original Spec

1. No characters table: Character profiles stored in speakers.json
2. No pronunciation_guide table: Not currently implemented
3. No quality_reports table: Quality tracking via web UI
4. No review_decisions table: Review via web UI endpoints
5. speaker_name column: Added to utterances for speaker attribution
6. JSON-based configuration: speakers.json and voice_assignments.json

Database Class API

class Database:
    def __init__(self, db_path: Path) -> None
    def connect(self) -> None
    def close(self) -> None

Query Methods

Method	Returns	Description
get_chapters()	list[dict]	All chapters ordered by index
get_utterances(chapter_id)	list[dict]	Utterances for a chapter
get_audio_for_chapter(chapter_id)	list[dict]	Generated audio files

Data Flow

1. Parsing: Manuscript → chapters table (source_text)
2. Character Discovery: LLM analysis → speakers.json
3. Voice Assignment: Manual/automated → voice_assignments.json
4. Segmentation: Chapter text + speakers.json → utterances table
5. Audio Generation: utterances + voice_assignments.json → chapter_audio/

Indexes

CREATE INDEX IF NOT EXISTS idx_utterances_chapter ON utterances(chapter_id);
CREATE INDEX IF NOT EXISTS idx_generated_audio_utterance ON generated_audio(utterance_id);
CREATE INDEX IF NOT EXISTS idx_chapters_project ON chapters(project_id);