shinkan-jinkendo/.claude/docs/technical/EXERCISES_DATABASE_FINAL.md

Database Schema Final - Exercises System

Version: 1.2 Datum: 2026-04-24 Status: REVIEWED - Pending Implementation Autor: Claude Code Änderungen v1.2: Skill-Level auf benannte Stufen (einsteiger–experte), intensity auf niedrig/mittel/hoch, ai_suggested Felder, summary_ai_generated Änderungen v1.1: age_groups JSONB entfernt, Legacy-DROP ergänzt, Migration 017 (Exercise Blocks)

---

1. Übersicht

Neue Migrationen:

• Migration 014: Variant Progression System + Search Vector + Legacy-Cleanup
• Migration 015: Semantic Matching (OPTIONAL - Phase 2)
• Migration 016: Saved Searches
• Migration 017: Exercise Blocks + Template Blocks

Basis: Migrationen 001-013 (bereits deployed)

Progressionsgraph zwischen Übungen: Migrationen 032–034 — nicht Bestandteil dieses „Exercise Catalog“-Schemas-Dokuments; siehe TRAINING_FRAMEWORK_SPEC.md §3 und DATABASE_SCHEMA.md (Migrationshistorie).

---

2. Migration 014: Variant Progression + Search

2.1 Vollständige Migration

-- Migration 014: Variant Progression System + Search Vector
-- Autor: Claude Code
-- Datum: 2026-04-24

DO $$
BEGIN

-- ============================================================================
-- VARIANT PROGRESSION
-- ============================================================================

-- Erweitere exercise_variants Tabelle
ALTER TABLE exercise_variants
ADD COLUMN IF NOT EXISTS progression_level INT DEFAULT 1 CHECK (progression_level BETWEEN 1 AND 10),
ADD COLUMN IF NOT EXISTS sequence_order INT,
ADD COLUMN IF NOT EXISTS prerequisite_variant_id INT REFERENCES exercise_variants(id) ON DELETE SET NULL;

-- Index für Prerequisites
CREATE INDEX IF NOT EXISTS idx_exercise_variants_prerequisite
ON exercise_variants(prerequisite_variant_id);

-- ============================================================================
-- SEARCH VECTOR (Volltext-Suche)
-- ============================================================================

-- Füge search_vector zu exercises hinzu
ALTER TABLE exercises
ADD COLUMN IF NOT EXISTS search_vector tsvector;

-- Index für Volltext-Suche
CREATE INDEX IF NOT EXISTS idx_exercises_search
ON exercises USING gin(search_vector);

-- Funktion für automatisches Update
CREATE OR REPLACE FUNCTION update_exercises_search_vector()
RETURNS trigger AS $func$
BEGIN
    NEW.search_vector :=
        setweight(to_tsvector('german', COALESCE(NEW.title, '')), 'A') ||
        setweight(to_tsvector('german', COALESCE(NEW.summary, '')), 'B') ||
        setweight(to_tsvector('german', COALESCE(NEW.execution, '')), 'C') ||
        setweight(to_tsvector('german', COALESCE(NEW.trainer_notes, '')), 'D');
    RETURN NEW;
END;
$func$ LANGUAGE plpgsql;

-- Trigger
DROP TRIGGER IF EXISTS exercises_search_update ON exercises;
CREATE TRIGGER exercises_search_update
BEFORE INSERT OR UPDATE ON exercises
FOR EACH ROW EXECUTE FUNCTION update_exercises_search_vector();

-- Initiales Befüllen (für existierende Zeilen)
UPDATE exercises SET search_vector = (
    setweight(to_tsvector('german', COALESCE(title, '')), 'A') ||
    setweight(to_tsvector('german', COALESCE(summary, '')), 'B') ||
    setweight(to_tsvector('german', COALESCE(execution, '')), 'C') ||
    setweight(to_tsvector('german', COALESCE(trainer_notes, '')), 'D')
) WHERE search_vector IS NULL;

-- ============================================================================
-- LEGACY COLUMN CLEANUP
-- Deprecated Felder aus exercises (ersetzt durch M:N Tabellen in Migration 008+)
-- ============================================================================

-- age_groups JSONB → ersetzt durch exercise_age_groups M:N (seit Migration 008)
ALTER TABLE exercises DROP COLUMN IF EXISTS age_groups;

-- focus_area VARCHAR → ersetzt durch exercise_focus_areas M:N (seit Migration 008)
ALTER TABLE exercises DROP COLUMN IF EXISTS focus_area;

-- secondary_areas JSONB → ersetzt durch exercise_focus_areas M:N
ALTER TABLE exercises DROP COLUMN IF EXISTS secondary_areas;

-- training_character VARCHAR → ersetzt durch exercise_training_characters M:N (seit Migration 012)
ALTER TABLE exercises DROP COLUMN IF EXISTS training_character;

-- ============================================================================
-- ADDITIONAL INDEXES (Performance)
-- ============================================================================

-- Häufige Filter
CREATE INDEX IF NOT EXISTS idx_exercises_visibility ON exercises(visibility);
CREATE INDEX IF NOT EXISTS idx_exercises_status ON exercises(status);
CREATE INDEX IF NOT EXISTS idx_exercises_created_at ON exercises(created_at DESC);

-- M:N Relations (falls noch nicht vorhanden)
CREATE INDEX IF NOT EXISTS idx_exercise_focus_areas_focus
ON exercise_focus_areas(focus_area_id);

CREATE INDEX IF NOT EXISTS idx_exercise_styles_style
ON exercise_training_styles(training_style_id);

CREATE INDEX IF NOT EXISTS idx_exercise_target_groups_group
ON exercise_target_groups(target_group_id);

CREATE INDEX IF NOT EXISTS idx_exercise_skills_skill
ON exercise_skills(skill_id);

RAISE NOTICE 'Migration 014 completed successfully';

END $$;

---

3. Migration 015: Semantic Matching (Optional Phase 2)

Diese Migration ist optional für MVP Phase 1

-- Migration 015: Semantic Exercise Matching (OPTIONAL - Phase 2)
-- Nutzt pgvector extension für Similarity-Matching
-- Autor: Claude Code
-- Datum: 2026-04-24

DO $$
BEGIN

-- ============================================================================
-- SEMANTIC EMBEDDINGS (Optional)
-- ============================================================================

-- Benötigt pgvector extension
CREATE EXTENSION IF NOT EXISTS vector;

-- Erweitere exercises Tabelle
ALTER TABLE exercises
ADD COLUMN IF NOT EXISTS embedding vector(1536); -- OpenAI ada-002 embedding size

-- Index für Ähnlichkeitssuche
CREATE INDEX IF NOT EXISTS idx_exercises_embedding
ON exercises USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100);

-- Funktion für Similarity-Search
CREATE OR REPLACE FUNCTION find_similar_exercises(
    query_embedding vector(1536),
    limit_count INT DEFAULT 10
)
RETURNS TABLE (
    exercise_id INT,
    title VARCHAR,
    similarity FLOAT
) AS $func$
BEGIN
    RETURN QUERY
    SELECT
        id,
        title,
        1 - (embedding <=> query_embedding) AS similarity
    FROM exercises
    WHERE embedding IS NOT NULL
    ORDER BY embedding <=> query_embedding
    LIMIT limit_count;
END;
$func$ LANGUAGE plpgsql;

RAISE NOTICE 'Migration 015 completed successfully (Semantic Matching - OPTIONAL)';

END $$;

Hinweis: Embeddings werden über separaten Background-Job befüllt, nicht beim INSERT.

---

4. Migration 016: Saved Searches

-- Migration 016: Saved Exercise Searches
-- Autor: Claude Code
-- Datum: 2026-04-24

DO $$
BEGIN

-- ============================================================================
-- SAVED SEARCHES
-- ============================================================================

CREATE TABLE IF NOT EXISTS saved_exercise_searches (
    id SERIAL PRIMARY KEY,
    profile_id INT NOT NULL REFERENCES profiles(id) ON DELETE CASCADE,
    name VARCHAR(100) NOT NULL,
    filters JSONB NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- Index für User-Zugriff
CREATE INDEX IF NOT EXISTS idx_saved_searches_profile
ON saved_exercise_searches(profile_id);

-- Trigger für updated_at
CREATE OR REPLACE FUNCTION update_saved_searches_timestamp()
RETURNS trigger AS $func$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$func$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS saved_searches_update ON saved_exercise_searches;
CREATE TRIGGER saved_searches_update
BEFORE UPDATE ON saved_exercise_searches
FOR EACH ROW EXECUTE FUNCTION update_saved_searches_timestamp();

RAISE NOTICE 'Migration 016 completed successfully';

END $$;

---

5. Migration 017: Exercise Blocks + Template Blocks

-- Migration 017: Exercise Blocks + Template Blocks
-- Autor: Claude Code
-- Datum: 2026-04-24
-- Zweck: Gruppierung verschiedener Übungen in Blöcken (User-Anforderung #4)
--        Series = Varianten-Progression (via exercise_variants, KEINE eigene Tabelle)
--        Blocks = Verschiedene Übungen in Reihenfolge (DIESE Migration)

DO $$
BEGIN

-- ============================================================================
-- EXERCISE BLOCKS
-- Eine Sammlung verschiedener Übungen in definierter Reihenfolge
-- ============================================================================

CREATE TABLE IF NOT EXISTS exercise_blocks (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    goal TEXT,

    -- Template-Modus: Block mit Platzhaltern für flexible Planung
    is_template BOOLEAN DEFAULT false,

    -- Ownership & Sichtbarkeit
    club_id INT REFERENCES clubs(id) ON DELETE SET NULL,
    created_by INT REFERENCES profiles(id) ON DELETE SET NULL,
    visibility VARCHAR(20) DEFAULT 'private' CHECK (visibility IN ('private', 'club', 'official')),

    -- Timestamps
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- ============================================================================
-- EXERCISE BLOCK ITEMS
-- Einzelne Positionen innerhalb eines Blocks
-- ============================================================================

CREATE TABLE IF NOT EXISTS exercise_block_items (
    id SERIAL PRIMARY KEY,
    block_id INT NOT NULL REFERENCES exercise_blocks(id) ON DELETE CASCADE,

    -- Konkrete Übung (NULL wenn is_placeholder = true)
    exercise_id INT REFERENCES exercises(id) ON DELETE RESTRICT,

    -- Optionale Variante (kann NULL sein → Haupt-Übung wird genutzt)
    variant_id INT REFERENCES exercise_variants(id) ON DELETE SET NULL,

    -- Reihenfolge innerhalb des Blocks
    sequence_order INT NOT NULL,

    -- Template-Modus: Platzhalter statt konkreter Übung
    is_placeholder BOOLEAN DEFAULT false,

    -- Kriterien für Platzhalter-Auflösung (nur relevant wenn is_placeholder = true)
    -- Schema: {"focus_area_id": 1, "max_duration": 10, "skill_ids": [3, 7], "difficulty": "easier"}
    -- Alle Felder optional, werden als AND-Filter bei der Übungssuche genutzt
    placeholder_criteria JSONB,

    -- Platzhalter-Beschriftung (für UX im Template-Modus)
    placeholder_label VARCHAR(100), -- z.B. "Aufwärmübung Schlag", "Hauptübung Kumite"

    -- Zusätzliche Notizen für diese Position
    notes TEXT,

    -- Timestamps
    created_at TIMESTAMP DEFAULT NOW(),

    -- Constraints
    UNIQUE(block_id, sequence_order),
    -- Entweder exercise_id ODER is_placeholder=true
    CHECK (
        (is_placeholder = false AND exercise_id IS NOT NULL) OR
        (is_placeholder = true AND exercise_id IS NULL)
    )
);

-- ============================================================================
-- INDEXES
-- ============================================================================

CREATE INDEX IF NOT EXISTS idx_exercise_blocks_club ON exercise_blocks(club_id);
CREATE INDEX IF NOT EXISTS idx_exercise_blocks_creator ON exercise_blocks(created_by);
CREATE INDEX IF NOT EXISTS idx_exercise_blocks_visibility ON exercise_blocks(visibility);
CREATE INDEX IF NOT EXISTS idx_exercise_blocks_template ON exercise_blocks(is_template) WHERE is_template = true;

CREATE INDEX IF NOT EXISTS idx_exercise_block_items_block ON exercise_block_items(block_id);
CREATE INDEX IF NOT EXISTS idx_exercise_block_items_exercise ON exercise_block_items(exercise_id);
CREATE INDEX IF NOT EXISTS idx_exercise_block_items_placeholder ON exercise_block_items(is_placeholder) WHERE is_placeholder = true;

-- ============================================================================
-- UPDATED_AT TRIGGER
-- ============================================================================

DROP TRIGGER IF EXISTS exercise_blocks_update ON exercise_blocks;
CREATE TRIGGER exercise_blocks_update
BEFORE UPDATE ON exercise_blocks
FOR EACH ROW EXECUTE FUNCTION update_timestamp();

RAISE NOTICE 'Migration 017 completed successfully (Exercise Blocks)';

END $$;

5.0 Placeholder Criteria Schema

Das placeholder_criteria JSONB-Feld in exercise_block_items erlaubt folgende Schlüssel (alle optional, werden als AND-Filter kombiniert):

{
  "focus_area_id": 1,          // INT: Fokusbereich-ID
  "training_style_id": 2,      // INT: Stil-ID
  "target_group_id": 5,        // INT: Zielgruppen-ID
  "skill_ids": [3, 7],         // INT[]: Mindestens eine dieser Fähigkeiten
  "max_duration": 15,          // INT: Maximale Dauer in Minuten
  "min_duration": 5,           // INT: Minimale Dauer in Minuten
  "difficulty": "easier",      // "easier" | "same" | "harder"
  "visibility": "club"         // "private" | "club" | "official"
}

Validierung: Backend prüft, dass alle vorhandenen Schlüssel bekannte Felder sind und die Werte dem erwarteten Typ entsprechen.

---

6. Vollständige Tabellenstruktur (Final State)

5.1 exercises (Kern-Tabelle)

CREATE TABLE exercises (
    id SERIAL PRIMARY KEY,
    
    -- Basis-Info
    title VARCHAR(300) NOT NULL,
    summary TEXT,
    goal TEXT NOT NULL,
    execution TEXT NOT NULL,
    preparation TEXT,
    trainer_notes TEXT,
    
    -- Dauer & Gruppengröße
    duration_min INT,
    duration_max INT,
    group_size_min INT,
    group_size_max INT,
    
    -- JSONB-Felder
    equipment JSONB DEFAULT '[]'::jsonb,
    -- HINWEIS: age_groups werden via exercise_age_groups M:N Tabelle verwaltet (nicht JSONB)

    -- Suche
    search_vector tsvector,  -- NEU in Migration 014
    
    -- Semantic Matching (optional)
    embedding vector(1536),  -- NEU in Migration 015 (optional)
    
    -- Sichtbarkeit & Status
    visibility VARCHAR(20) DEFAULT 'private' CHECK (visibility IN ('private', 'club', 'official')),
    status VARCHAR(20) DEFAULT 'draft' CHECK (status IN ('draft', 'in_review', 'approved', 'archived')),
    
    -- Ownership
    created_by INT REFERENCES profiles(id) ON DELETE SET NULL,
    club_id INT REFERENCES clubs(id) ON DELETE SET NULL,
    
    -- Import-Tracking
    import_source VARCHAR(50), -- 'mediawiki', 'csv', etc.
    import_id VARCHAR(100),    -- Original-ID aus Quellsystem
    
    -- Timestamps
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

5.2 exercise_variants

CREATE TABLE exercise_variants (
    id SERIAL PRIMARY KEY,
    exercise_id INT NOT NULL REFERENCES exercises(id) ON DELETE CASCADE,
    
    -- Variant-Details
    variant_name VARCHAR(200) NOT NULL,
    description TEXT,
    execution_changes TEXT,
    
    -- Dauer (Override)
    duration_min INT,
    duration_max INT,
    
    -- Equipment-Änderungen
    equipment_changes JSONB DEFAULT '[]'::jsonb,
    
    -- Schwierigkeit
    difficulty_adjustment VARCHAR(20) CHECK (difficulty_adjustment IN ('easier', 'same', 'harder')),
    
    -- Progression (NEU in Migration 014)
    progression_level INT DEFAULT 1 CHECK (progression_level BETWEEN 1 AND 10),
    sequence_order INT,
    prerequisite_variant_id INT REFERENCES exercise_variants(id) ON DELETE SET NULL,
    
    -- Timestamps
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

5.3 exercise_media

CREATE TABLE exercise_media (
    id SERIAL PRIMARY KEY,
    exercise_id INT NOT NULL REFERENCES exercises(id) ON DELETE CASCADE,
    
    -- Media-Type
    media_type VARCHAR(20) CHECK (media_type IN ('image', 'video', 'document', 'sketch')),
    
    -- Lokale Datei (exklusiv mit embed_url)
    file_path VARCHAR(500),
    file_size INT,
    mime_type VARCHAR(100),
    original_filename VARCHAR(300),
    
    -- Embed (exklusiv mit file_path)
    embed_url TEXT,
    embed_platform VARCHAR(50), -- 'youtube', 'vimeo', 'instagram', 'tiktok'
    
    -- Metadata
    title VARCHAR(200),
    description TEXT,
    sort_order INT DEFAULT 1,
    is_primary BOOLEAN DEFAULT FALSE,
    context VARCHAR(50) DEFAULT 'ablauf' CHECK (context IN ('ablauf', 'detail', 'trainer_hint')),
    
    -- Timestamps
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),
    
    -- Constraint: Entweder file_path ODER embed_url
    CHECK (
        (file_path IS NOT NULL AND embed_url IS NULL) OR
        (file_path IS NULL AND embed_url IS NOT NULL)
    )
);

5.4 M:N Relation Tables

exercise_focus_areas:

CREATE TABLE exercise_focus_areas (
    id SERIAL PRIMARY KEY,
    exercise_id INT NOT NULL REFERENCES exercises(id) ON DELETE CASCADE,
    focus_area_id INT NOT NULL REFERENCES focus_areas(id) ON DELETE RESTRICT,
    is_primary BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT NOW(),
    UNIQUE(exercise_id, focus_area_id)
);

exercise_training_styles:

CREATE TABLE exercise_training_styles (
    id SERIAL PRIMARY KEY,
    exercise_id INT NOT NULL REFERENCES exercises(id) ON DELETE CASCADE,
    training_style_id INT NOT NULL REFERENCES training_styles(id) ON DELETE RESTRICT,
    is_primary BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT NOW(),
    UNIQUE(exercise_id, training_style_id)
);

exercise_target_groups:

CREATE TABLE exercise_target_groups (
    id SERIAL PRIMARY KEY,
    exercise_id INT NOT NULL REFERENCES exercises(id) ON DELETE CASCADE,
    target_group_id INT NOT NULL REFERENCES target_groups(id) ON DELETE RESTRICT,
    is_primary BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT NOW(),
    UNIQUE(exercise_id, target_group_id)
);

exercise_age_groups:

CREATE TABLE exercise_age_groups (
    id SERIAL PRIMARY KEY,
    exercise_id INT NOT NULL REFERENCES exercises(id) ON DELETE CASCADE,
    age_group_name VARCHAR(50) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    UNIQUE(exercise_id, age_group_name)
);

exercise_skills:

CREATE TABLE exercise_skills (
    id SERIAL PRIMARY KEY,
    exercise_id INT NOT NULL REFERENCES exercises(id) ON DELETE CASCADE,
    skill_id INT NOT NULL REFERENCES skills(id) ON DELETE RESTRICT,
    is_primary BOOLEAN DEFAULT FALSE,

    -- Kompetenzmodell: benannte Stufen (nicht numerisch 1-10)
    -- NULL = nicht definiert / nicht relevant
    required_level VARCHAR(20) CHECK (required_level IN ('einsteiger', 'grundlagen', 'aufbau', 'fortgeschritten', 'experte')),
    target_level   VARCHAR(20) CHECK (target_level   IN ('einsteiger', 'grundlagen', 'aufbau', 'fortgeschritten', 'experte')),

    -- Trainingsintensität: Wie stark wird diese Fähigkeit in der Übung trainiert
    intensity VARCHAR(10) CHECK (intensity IN ('niedrig', 'mittel', 'hoch')),

    -- KI-generierte Zuordnung (false = manuell bestätigt)
    ai_suggested BOOLEAN DEFAULT false,

    created_at TIMESTAMP DEFAULT NOW(),
    UNIQUE(exercise_id, skill_id)
);

Skill-Level-Definitionen (Kompetenzmodell):

Stufe	Wert	Beschreibung	Beispiel Distanzgefühl
1	einsteiger	Erste Berührung mit der Fähigkeit, kein Vorwissen nötig	Versteht das Konzept Distanz
2	grundlagen	Grundprinzipien bekannt, in einfachen Situationen anwendbar	Hält in ruhigen Übungen Distanz
3	aufbau	Sicher in Standard-Situationen, braucht noch Korrektur	Distanzkontrolle in Partnerübungen
4	fortgeschritten	Zuverlässig auch unter Druck, wenig Fehler	Stabile Distanz im Sparring
5	experte	Automatisiert, intuitiv, auch in komplexen Situationen	Feines Distanzgefühl im Wettkampf

Für Migration 014 ergänzen:

-- In Migration 014 nach den Variant-Columns ergänzen:
-- Skill-Level auf VARCHAR umstellen (bestehende INT-Werte migrieren)
ALTER TABLE exercise_skills
    ALTER COLUMN required_level TYPE VARCHAR(20) USING
        CASE required_level
            WHEN 0 THEN NULL
            WHEN 1 THEN 'einsteiger'
            WHEN 2 THEN 'grundlagen'
            WHEN 3 THEN 'aufbau'
            WHEN 4 THEN 'fortgeschritten'
            WHEN 5 THEN 'experte'
            ELSE NULL
        END,
    ALTER COLUMN target_level TYPE VARCHAR(20) USING
        CASE target_level
            WHEN 0 THEN NULL
            WHEN 1 THEN 'einsteiger'
            WHEN 2 THEN 'grundlagen'
            WHEN 3 THEN 'aufbau'
            WHEN 4 THEN 'fortgeschritten'
            WHEN 5 THEN 'experte'
            ELSE NULL
        END,
    ALTER COLUMN intensity TYPE VARCHAR(10) USING
        CASE
            WHEN intensity <= 3 THEN 'niedrig'
            WHEN intensity <= 7 THEN 'mittel'
            ELSE 'hoch'
        END;

-- Neue Constraints hinzufügen
ALTER TABLE exercise_skills
    ADD CONSTRAINT ck_required_level CHECK (required_level IN ('einsteiger', 'grundlagen', 'aufbau', 'fortgeschritten', 'experte')),
    ADD CONSTRAINT ck_target_level   CHECK (target_level   IN ('einsteiger', 'grundlagen', 'aufbau', 'fortgeschritten', 'experte')),
    ADD CONSTRAINT ck_intensity      CHECK (intensity      IN ('niedrig', 'mittel', 'hoch'));

-- KI-Tracking Feld
ALTER TABLE exercises
ADD COLUMN IF NOT EXISTS summary_ai_generated BOOLEAN DEFAULT false;

ALTER TABLE exercise_skills
ADD COLUMN IF NOT EXISTS ai_suggested BOOLEAN DEFAULT false;

exercise_training_characters:

CREATE TABLE exercise_training_characters (
    id SERIAL PRIMARY KEY,
    exercise_id INT NOT NULL REFERENCES exercises(id) ON DELETE CASCADE,
    training_character_id INT NOT NULL REFERENCES training_characters(id) ON DELETE RESTRICT,
    is_primary BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT NOW(),
    UNIQUE(exercise_id, training_character_id)
);

6.5 Exercise Blocks (Migration 017)

CREATE TABLE exercise_blocks (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    goal TEXT,
    is_template BOOLEAN DEFAULT false,
    club_id INT REFERENCES clubs(id) ON DELETE SET NULL,
    created_by INT REFERENCES profiles(id) ON DELETE SET NULL,
    visibility VARCHAR(20) DEFAULT 'private' CHECK (visibility IN ('private', 'club', 'official')),
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE exercise_block_items (
    id SERIAL PRIMARY KEY,
    block_id INT NOT NULL REFERENCES exercise_blocks(id) ON DELETE CASCADE,
    exercise_id INT REFERENCES exercises(id) ON DELETE RESTRICT,
    variant_id INT REFERENCES exercise_variants(id) ON DELETE SET NULL,
    sequence_order INT NOT NULL,
    is_placeholder BOOLEAN DEFAULT false,
    placeholder_criteria JSONB,
    placeholder_label VARCHAR(100),
    notes TEXT,
    created_at TIMESTAMP DEFAULT NOW(),
    UNIQUE(block_id, sequence_order),
    CHECK (
        (is_placeholder = false AND exercise_id IS NOT NULL) OR
        (is_placeholder = true AND exercise_id IS NULL)
    )
);

6.6 Saved Searches (Migration 016)

CREATE TABLE saved_exercise_searches (
    id SERIAL PRIMARY KEY,
    profile_id INT NOT NULL REFERENCES profiles(id) ON DELETE CASCADE,
    name VARCHAR(100) NOT NULL,
    filters JSONB NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

---

6. Indizes (Komplett-Übersicht)

-- Volltext-Suche
CREATE INDEX idx_exercises_search ON exercises USING gin(search_vector);

-- Semantic Matching (optional)
CREATE INDEX idx_exercises_embedding ON exercises USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);

-- Häufige Filter
CREATE INDEX idx_exercises_visibility ON exercises(visibility);
CREATE INDEX idx_exercises_status ON exercises(status);
CREATE INDEX idx_exercises_created_at ON exercises(created_at DESC);
CREATE INDEX idx_exercises_club ON exercises(club_id);
CREATE INDEX idx_exercises_creator ON exercises(created_by);

-- M:N Relations
CREATE INDEX idx_exercise_focus_areas_exercise ON exercise_focus_areas(exercise_id);
CREATE INDEX idx_exercise_focus_areas_focus ON exercise_focus_areas(focus_area_id);

CREATE INDEX idx_exercise_styles_exercise ON exercise_training_styles(exercise_id);
CREATE INDEX idx_exercise_styles_style ON exercise_training_styles(training_style_id);

CREATE INDEX idx_exercise_target_groups_exercise ON exercise_target_groups(exercise_id);
CREATE INDEX idx_exercise_target_groups_group ON exercise_target_groups(target_group_id);

CREATE INDEX idx_exercise_skills_exercise ON exercise_skills(exercise_id);
CREATE INDEX idx_exercise_skills_skill ON exercise_skills(skill_id);

CREATE INDEX idx_exercise_characters_exercise ON exercise_training_characters(exercise_id);
CREATE INDEX idx_exercise_characters_character ON exercise_training_characters(training_character_id);

-- Variants
CREATE INDEX idx_exercise_variants_exercise ON exercise_variants(exercise_id);
CREATE INDEX idx_exercise_variants_prerequisite ON exercise_variants(prerequisite_variant_id);

-- Media
CREATE INDEX idx_exercise_media_exercise ON exercise_media(exercise_id);
CREATE INDEX idx_exercise_media_primary ON exercise_media(is_primary) WHERE is_primary = true;
CREATE INDEX idx_exercise_media_context ON exercise_media(context);

-- Saved Searches
CREATE INDEX idx_saved_searches_profile ON saved_exercise_searches(profile_id);

-- Exercise Blocks
CREATE INDEX idx_exercise_blocks_club ON exercise_blocks(club_id);
CREATE INDEX idx_exercise_blocks_creator ON exercise_blocks(created_by);
CREATE INDEX idx_exercise_blocks_visibility ON exercise_blocks(visibility);
CREATE INDEX idx_exercise_blocks_template ON exercise_blocks(is_template) WHERE is_template = true;

CREATE INDEX idx_exercise_block_items_block ON exercise_block_items(block_id);
CREATE INDEX idx_exercise_block_items_exercise ON exercise_block_items(exercise_id);
CREATE INDEX idx_exercise_block_items_placeholder ON exercise_block_items(is_placeholder) WHERE is_placeholder = true;

---

7. Constraints & Business Rules

7.1 Data Integrity

Exercises:

• title mindestens 3 Zeichen
• goal mindestens 10 Zeichen
• execution mindestens 10 Zeichen
• duration_min <= duration_max
• group_size_min <= group_size_max

Variants:

• variant_name mindestens 3 Zeichen
• prerequisite_variant_id muss zum gleichen Exercise gehören

Media:

• Max. 50 MB per file (enforced in Backend)
• Max. 10 media items per exercise (enforced in Backend)
• Entweder file_path ODER embed_url (enforced in CHECK)

7.2 Cascading Delete

ON DELETE CASCADE:

• exercise → variants
• exercise → media
• exercise → M:N relations (focus_areas, styles, etc.)
• profile → saved searches

ON DELETE RESTRICT:

• focus_area → exercise_focus_areas (verhindert Löschen genutzter Katalog-Einträge)
• skill → exercise_skills
• target_group → exercise_target_groups

ON DELETE SET NULL:

• profile (creator) → exercises
• club → exercises
• variant (prerequisite) → variants

---

8. Trigger-Funktionen

8.1 Search Vector Auto-Update

CREATE OR REPLACE FUNCTION update_exercises_search_vector()
RETURNS trigger AS $$
BEGIN
    NEW.search_vector :=
        setweight(to_tsvector('german', COALESCE(NEW.title, '')), 'A') ||
        setweight(to_tsvector('german', COALESCE(NEW.summary, '')), 'B') ||
        setweight(to_tsvector('german', COALESCE(NEW.execution, '')), 'C') ||
        setweight(to_tsvector('german', COALESCE(NEW.trainer_notes, '')), 'D');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER exercises_search_update
BEFORE INSERT OR UPDATE ON exercises
FOR EACH ROW EXECUTE FUNCTION update_exercises_search_vector();

8.2 Updated_At Timestamp

CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS trigger AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigger für alle relevanten Tabellen
CREATE TRIGGER exercises_update_timestamp
BEFORE UPDATE ON exercises
FOR EACH ROW EXECUTE FUNCTION update_timestamp();

CREATE TRIGGER exercise_variants_update_timestamp
BEFORE UPDATE ON exercise_variants
FOR EACH ROW EXECUTE FUNCTION update_timestamp();

CREATE TRIGGER exercise_media_update_timestamp
BEFORE UPDATE ON exercise_media
FOR EACH ROW EXECUTE FUNCTION update_timestamp();

CREATE TRIGGER saved_searches_update_timestamp
BEFORE UPDATE ON saved_exercise_searches
FOR EACH ROW EXECUTE FUNCTION update_timestamp();

---

9. Migrations-Tracking

Tabelle: schema_migrations

CREATE TABLE IF NOT EXISTS schema_migrations (
    migration_id VARCHAR(50) PRIMARY KEY,
    description TEXT,
    applied_at TIMESTAMP DEFAULT NOW()
);

Nach jeder Migration:

INSERT INTO schema_migrations (migration_id, description) VALUES
('014', 'Variant Progression System + Search Vector + Legacy-Cleanup'),
('015', 'Semantic Exercise Matching (OPTIONAL)'),
('016', 'Saved Exercise Searches'),
('017', 'Exercise Blocks + Template Blocks');

---

10. Datenbank-Größenabschätzung

Annahmen:

• 5.000 Übungen
• Durchschnittlich 2 Varianten pro Übung
• Durchschnittlich 3 Media-Items pro Übung
• Durchschnittlich 5 M:N Zuordnungen pro Übung

Geschätzte Zeilen:

exercises:                     5.000
exercise_variants:            10.000
exercise_media:               15.000
exercise_focus_areas:         10.000
exercise_training_styles:     10.000
exercise_target_groups:       10.000
exercise_skills:              25.000
exercise_blocks:               1.000
exercise_block_items:          5.000
─────────────────────────────────────
TOTAL:                       101.000 Zeilen

Speicherbedarf (ohne Media-Files):

• exercises Tabelle: ~50 MB (mit search_vector + embedding)
• Alle M:N Tabellen: ~10 MB
• Variants + Media: ~5 MB
• Total DB: ~65 MB

Media-Files (lokal gespeichert):

• 15.000 Items × 5 MB Durchschnitt = ~75 GB

---

11. Performance-Benchmarks

Target Query Performance:

• List Exercises (ohne Filter): < 50ms
• List Exercises (mit 3 Filtern): < 100ms
• Volltext-Suche: < 150ms
• Exercise Detail (enriched): < 80ms
• Semantic Similarity: < 200ms (optional)

Optimierungen:

• GIN-Index für tsvector → 10x schnellere Suche
• IVFFlat-Index für Embeddings → 50x schnellere Similarity-Search
• Partial Index für is_primary Media → schnellere Primary-Lookups

---

Version: 1.0
Letzte Änderung: 2026-04-24
Status: DRAFT - Awaiting Review