mitai-jinkendo/.claude/docs/technical/DATABASE_MODEL_COMPLETE.md

# Technisches Datenmodell – Mitai Jinkendo

> **Quelle:** Development Database (dev.mitai.jinkendo.de)  
> **Generiert:** 2026-04-02  
> **PostgreSQL Version:** 16-alpine  
> **Anzahl Tabellen:** 43

---

## Inhaltsverzeichnis

1. [Kernmodule](#1-kernmodule)
2. [Tracking-Module](#2-tracking-module)
3. [KI & Analysen](#3-ki--analysen)
4. [Membership & Features](#4-membership--features)
5. [Ziele & Training](#5-ziele--training)
6. [System-Tabellen](#6-system-tabellen)
7. [Backup-Tabellen](#7-backup-tabellen)
8. [ER-Diagramm](#8-er-diagramm)

---

## 1. Kernmodule

### 1.1 profiles

**Beschreibung:** Nutzerstammdaten und Authentifizierung

| Spalte | Typ | Nullable | Default | Beschreibung |
|--------|-----|----------|---------|--------------|
| id | uuid | NO | uuid_generate_v4() | PK, eindeutige Profil-ID |
| email | varchar | YES | NULL | E-Mail-Adresse (unique) |
| pin_hash | varchar(255) | NO | - | bcrypt-gehashtes Passwort |
| role | varchar(20) | YES | 'user' | Rolle (user, admin) |
| tier | text | YES | 'free' | Membership-Tier |
| created_at | timestamp | YES | now() | Erstellungsdatum |
| email_verified | boolean | YES | false | E-Mail-Verifizierung |
| verification_token | varchar(255) | YES | NULL | Token für E-Mail-Verifizierung |
| invited_by | uuid | YES | NULL | FK zu profiles.id (Einladung) |
| quality_filter_level | varchar(20) | YES | 'disabled' | Qualitätsfilter für Aktivitäten |

**Constraints:**
- PK: `id`
- UNIQUE: `email`
- FK: `invited_by` → `profiles(id)` ON DELETE NO ACTION
- CHECK: `tier IN ('free', 'plus', 'premium', 'trial')`

**Indizes:**
- `idx_profiles_email` (email) WHERE email IS NOT NULL
- `idx_profiles_tier` (tier)
- `idx_profiles_quality_filter` (quality_filter_level)
- `idx_profiles_verification_token` (verification_token) WHERE verification_token IS NOT NULL

**Beziehungen:**
- 1:N zu allen Tracking-Tabellen (profile_id)
- 1:N zu sessions
- 1:N zu ai_insights, ai_usage
- 1:N zu goals, training_phases
- 1:1 zu user_stats
- Self-Reference (invited_by)

---

### 1.2 sessions

**Beschreibung:** Authentifizierungs-Sessions

| Spalte | Typ | Nullable | Default | Beschreibung |
|--------|-----|----------|---------|--------------|
| token | varchar(255) | NO | - | PK, Session-Token |
| profile_id | uuid | NO | - | FK zu profiles.id |
| expires_at | timestamp | NO | - | Ablaufdatum |
| created_at | timestamp | YES | now() | Erstellungsdatum |

**Constraints:**
- PK: `token`
- FK: `profile_id` → `profiles(id)` ON DELETE CASCADE

**Indizes:**
- `idx_sessions_profile_id` (profile_id)
- `idx_sessions_expires_at` (expires_at)

---

### 1.3 user_stats

**Beschreibung:** Aggregierte Nutzerstatistiken

| Spalte | Typ | Nullable | Default | Beschreibung |
|--------|-----|----------|---------|--------------|
| profile_id | uuid | NO | - | PK, FK zu profiles.id |
| total_weight_entries | integer | YES | 0 | Anzahl Gewichtseinträge |
| total_circumference_entries | integer | YES | 0 | Anzahl Umfangseinträge |
| total_caliper_entries | integer | YES | 0 | Anzahl Caliper-Einträge |
| total_activity_entries | integer | YES | 0 | Anzahl Aktivitäten |
| total_nutrition_entries | integer | YES | 0 | Anzahl Ernährungseinträge |
| total_photos | integer | YES | 0 | Anzahl Fotos |
| total_ai_insights | integer | YES | 0 | Anzahl KI-Analysen |
| last_weight_date | date | YES | NULL | Letztes Gewichtsdatum |
| last_activity_date | date | YES | NULL | Letzte Aktivität |
| last_insight_date | timestamp | YES | NULL | Letzte KI-Analyse |
| updated_at | timestamp | YES | now() | Letztes Update |

**Constraints:**
- PK: `profile_id`
- FK: `profile_id` → `profiles(id)` ON DELETE CASCADE

---

## 2. Tracking-Module

### 2.1 weight_log

**Beschreibung:** Gewichtsverlauf

| Spalte | Typ | Nullable | Default | Beschreibung |
|--------|-----|----------|---------|--------------|
| id | uuid | NO | uuid_generate_v4() | PK |
| profile_id | uuid | NO | - | FK zu profiles.id |
| date | date | NO | - | Messdatum |
| weight | numeric(5,2) | NO | - | Gewicht in kg |
| source | varchar(50) | YES | 'manual' | Datenquelle |
| notes | text | YES | NULL | Notizen |
| created_at | timestamp | YES | now() | Erstellungsdatum |

**Constraints:**
- PK: `id`
- UNIQUE: `profile_id, date`
- FK: `profile_id` → `profiles(id)` ON DELETE CASCADE

**Indizes:**
- `idx_weight_log_profile_date` (profile_id, date DESC)
- `idx_weight_log_profile_date_unique` UNIQUE (profile_id, date)

---

### 2.2 circumference_log

**Beschreibung:** Umfangsmessungen (8 Messpunkte)

| Spalte | Typ | Nullable | Default | Beschreibung |
|--------|-----|----------|---------|--------------|
| id | uuid | NO | uuid_generate_v4() | PK |
| profile_id | uuid | NO | - | FK zu profiles.id |
| date | date | NO | - | Messdatum |
| c_neck | numeric(5,2) | YES | NULL | Hals (cm) |
| c_chest | numeric(5,2) | YES | NULL | Brust (cm) |
| c_waist | numeric(5,2) | YES | NULL | Taille (cm) |
| c_hips | numeric(5,2) | YES | NULL | Hüfte (cm) |
| c_thigh | numeric(5,2) | YES | NULL | Oberschenkel (cm) |
| c_calf | numeric(5,2) | YES | NULL | Wade (cm) |
| c_biceps | numeric(5,2) | YES | NULL | Bizeps (cm) |
| c_forearm | numeric(5,2) | YES | NULL | Unterarm (cm) |
| source | varchar(50) | YES | 'manual' | Datenquelle |
| notes | text | YES | NULL | Notizen |
| created_at | timestamp | YES | now() | Erstellungsdatum |

**Constraints:**
- PK: `id`
- FK: `profile_id` → `profiles(id)` ON DELETE CASCADE

**Indizes:**
- `idx_circumference_profile_date` (profile_id, date DESC)

---

### 2.3 caliper_log

**Beschreibung:** Hautfaltenmessungen (Körperfettanalyse)

| Spalte | Typ | Nullable | Default | Beschreibung |
|--------|-----|----------|---------|--------------|
| id | uuid | NO | uuid_generate_v4() | PK |
| profile_id | uuid | NO | - | FK zu profiles.id |
| date | date | NO | - | Messdatum |
| chest | numeric(5,2) | YES | NULL | Brust (mm) |
| abdominal | numeric(5,2) | YES | NULL | Bauch (mm) |
| thigh | numeric(5,2) | YES | NULL | Oberschenkel (mm) |
| triceps | numeric(5,2) | YES | NULL | Trizeps (mm) |
| subscapular | numeric(5,2) | YES | NULL | Schulterblatt (mm) |
| suprailiac | numeric(5,2) | YES | NULL | Hüfte (mm) |
| midaxillary | numeric(5,2) | YES | NULL | Mittelachsel (mm) |
| body_fat_pct | numeric(5,2) | YES | NULL | Körperfett % (berechnet) |
| formula | varchar(20) | YES | NULL | Berechnungsformel |
| source | varchar(50) | YES | 'manual' | Datenquelle |
| notes | text | YES | NULL | Notizen |
| created_at | timestamp | YES | now() | Erstellungsdatum |

**Constraints:**
- PK: `id`
- FK: `profile_id` → `profiles(id)` ON DELETE CASCADE

**Indizes:**
- `idx_caliper_profile_date` (profile_id, date DESC)

---

### 2.4 activity_log

**Beschreibung:** Trainings- und Aktivitätslog

| Spalte | Typ | Nullable | Default | Beschreibung |
|--------|-----|----------|---------|--------------|
| id | uuid | NO | uuid_generate_v4() | PK |
| profile_id | uuid | NO | - | FK zu profiles.id |
| date | date | NO | - | Aktivitätsdatum |
| start_time | time | YES | NULL | Startzeit |
| duration_minutes | integer | YES | NULL | Dauer (Minuten) |
| training_type_id | integer | YES | NULL | FK zu training_types.id |
| training_category | varchar(50) | YES | NULL | Kategorie |
| kcal | integer | YES | NULL | Kalorienverbrauch |
| avg_hr | integer | YES | NULL | Durchschnittliche HF |
| max_hr | integer | YES | NULL | Maximale HF |
| distance_km | numeric(6,2) | YES | NULL | Distanz (km) |
| description | text | YES | NULL | Beschreibung |
| source | varchar(50) | YES | 'manual' | Datenquelle |
| evaluation | jsonb | YES | NULL | Qualitätsbewertung |
| overall_score | integer | YES | NULL | Gesamtbewertung (0-100) |
| quality_label | varchar(20) | YES | NULL | Qualitätslabel |
| created_at | timestamp | YES | now() | Erstellungsdatum |

**Constraints:**
- PK: `id`
- FK: `profile_id` → `profiles(id)` ON DELETE CASCADE
- FK: `training_type_id` → `training_types(id)` ON DELETE NO ACTION

**Indizes:**
- `idx_activity_profile_date` (profile_id, date DESC)
- `idx_activity_training_type` (training_type_id)
- `idx_activity_training_category` (training_category)
- `idx_activity_quality_label` (quality_label) WHERE quality_label IS NOT NULL
- `idx_activity_overall_score` (overall_score DESC) WHERE overall_score IS NOT NULL
- `idx_activity_evaluation_passed` (evaluation->'rule_set_results'->'minimum_requirements'->>'passed') WHERE evaluation IS NOT NULL

---

### 2.5 nutrition_log

**Beschreibung:** Ernährungslog (täglich)

| Spalte | Typ | Nullable | Default | Beschreibung |
|--------|-----|----------|---------|--------------|
| id | uuid | NO | uuid_generate_v4() | PK |
| profile_id | uuid | NO | - | FK zu profiles.id |
| date | date | NO | - | Datum |
| kcal | integer | YES | NULL | Kalorien |
| protein_g | numeric(6,2) | YES | NULL | Protein (g) |
| carbs_g | numeric(6,2) | YES | NULL | Kohlenhydrate (g) |
| fat_g | numeric(6,2) | YES | NULL | Fett (g) |
| fiber_g | numeric(6,2) | YES | NULL | Ballaststoffe (g) |
| water_l | numeric(4,2) | YES | NULL | Wasser (Liter) |
| source | varchar(50) | YES | 'manual' | Datenquelle |
| notes | text | YES | NULL | Notizen |
| created_at | timestamp | YES | now() | Erstellungsdatum |

**Constraints:**
- PK: `id`
- FK: `profile_id` → `profiles(id)` ON DELETE CASCADE

**Indizes:**
- `idx_nutrition_profile_date` (profile_id, date DESC)

---

### 2.6 sleep_log

**Beschreibung:** Schlaf-Tracking mit JSONB-Segmenten (Deep, REM, Light, Awake)

| Spalte | Typ | Nullable | Default | Beschreibung |
|--------|-----|----------|---------|--------------|
| id | uuid | NO | uuid_generate_v4() | PK |
| profile_id | uuid | NO | - | FK zu profiles.id |
| date | date | NO | - | Schlafdatum |
| sleep_start | timestamp | YES | NULL | Schlafbeginn |
| sleep_end | timestamp | YES | NULL | Schlafende |
| total_duration_minutes | integer | YES | NULL | Gesamtdauer (Minuten) |
| sleep_segments | jsonb | YES | NULL | Schlafphasen-Daten |
| quality_score | integer | YES | NULL | Schlafqualität (0-100) |
| source | varchar(50) | YES | 'manual' | Datenquelle |
| notes | text | YES | NULL | Notizen |
| created_at | timestamp | YES | now() | Erstellungsdatum |

**JSONB-Struktur `sleep_segments`:**
```json
{
  "deep": 120,     // Minuten
  "rem": 90,       // Minuten
  "light": 180,    // Minuten
  "awake": 15      // Minuten
}
```

**Constraints:**
- PK: `id`
- UNIQUE: `profile_id, date`
- FK: `profile_id` → `profiles(id)` ON DELETE CASCADE

**Indizes:**
- `idx_sleep_profile_date` (profile_id, date DESC)
- `unique_sleep_per_day` UNIQUE (profile_id, date)

---

### 2.7 vitals_baseline

**Beschreibung:** Morgenmessung Vitalwerte (RHR, HRV, VO2 Max, SpO2, Atemfrequenz)

| Spalte | Typ | Nullable | Default | Beschreibung |
|--------|-----|----------|---------|--------------|
| id | uuid | NO | uuid_generate_v4() | PK |
| profile_id | uuid | NO | - | FK zu profiles.id |
| date | date | NO | - | Messdatum |
| resting_hr | integer | YES | NULL | Ruhepuls (bpm) |
| hrv | integer | YES | NULL | HRV (ms) |
| vo2_max | numeric(5,2) | YES | NULL | VO2 Max (ml/kg/min) |
| spo2 | integer | YES | NULL | Sauerstoffsättigung (%) |
| respiratory_rate | integer | YES | NULL | Atemfrequenz (bpm) |
| source | varchar(50) | YES | 'manual' | Datenquelle |
| notes | text | YES | NULL | Notizen |
| created_at | timestamp | YES | now() | Erstellungsdatum |

**Constraints:**
- PK: `id`
- UNIQUE: `profile_id, date`
- FK: `profile_id` → `profiles(id)` ON DELETE CASCADE

**Indizes:**
- `idx_vitals_baseline_profile_date` (profile_id, date DESC)
- `unique_baseline_per_day` UNIQUE (profile_id, date)

---

### 2.8 blood_pressure_log

**Beschreibung:** Blutdruckmessungen (mehrfach täglich mit Context-Tagging)

| Spalte | Typ | Nullable | Default | Beschreibung |
|--------|-----|----------|---------|--------------|
| id | uuid | NO | uuid_generate_v4() | PK |
| profile_id | uuid | NO | - | FK zu profiles.id |
| measured_at | timestamp | NO | - | Messzeitpunkt |
| systolic | integer | NO | - | Systolisch (mmHg) |
| diastolic | integer | NO | - | Diastolisch (mmHg) |
| pulse | integer | YES | NULL | Puls (bpm) |
| context | varchar(50) | YES | NULL | Kontext (nüchtern, nach Essen, etc.) |
| irregular_heartbeat | boolean | YES | false | Unregelmäßiger Herzschlag |
| afib_detected | boolean | YES | false | Vorhofflimmern erkannt |
| source | varchar(50) | YES | 'manual' | Datenquelle |
| notes | text | YES | NULL | Notizen |
| created_at | timestamp | YES | now() | Erstellungsdatum |

**Constraints:**
- PK: `id`
- UNIQUE: `profile_id, measured_at`
- FK: `profile_id` → `profiles(id)` ON DELETE CASCADE

**Indizes:**
- `idx_blood_pressure_profile_datetime` (profile_id, measured_at DESC)
- `idx_blood_pressure_context` (context) WHERE context IS NOT NULL
- `unique_bp_measurement` UNIQUE (profile_id, measured_at)

---

### 2.9 rest_days

**Beschreibung:** Multi-dimensionale Ruhetage (Kraft, Cardio, Entspannung)

| Spalte | Typ | Nullable | Default | Beschreibung |
|--------|-----|----------|---------|--------------|
| id | uuid | NO | uuid_generate_v4() | PK |
| profile_id | uuid | NO | - | FK zu profiles.id |
| date | date | NO | - | Ruhetag-Datum |
| focus | varchar(50) | NO | - | Fokus (strength, cardio, relaxation) |
| rest_config | jsonb | YES | NULL | Detailkonfiguration |
| notes | text | YES | NULL | Notizen |
| created_at | timestamp | YES | now() | Erstellungsdatum |

**JSONB-Struktur `rest_config`:**
```json
{
  "strength_rest": true,
  "cardio_rest": false,
  "relaxation": true,
  "preset": "full_rest"
}
```

**Constraints:**
- PK: `id`
- UNIQUE: `profile_id, date, focus`
- FK: `profile_id` → `profiles(id)` ON DELETE CASCADE

**Indizes:**
- `idx_rest_days_profile_date` (profile_id, date DESC)
- `idx_rest_days_focus` (focus)
- `idx_rest_days_config` GIN (rest_config)
- `unique_rest_day_per_focus` UNIQUE (profile_id, date, focus)

---

### 2.10 photos

**Beschreibung:** Progress-Fotos

| Spalte | Typ | Nullable | Default | Beschreibung |
|--------|-----|----------|---------|--------------|
| id | uuid | NO | uuid_generate_v4() | PK |
| profile_id | uuid | NO | - | FK zu profiles.id |
| date | date | NO | - | Fotodatum |
| filename | varchar(255) | NO | - | Dateiname |
| pose | varchar(50) | YES | NULL | Pose (front, side, back) |
| notes | text | YES | NULL | Notizen |
| created_at | timestamp | YES | now() | Erstellungsdatum |

**Constraints:**
- PK: `id`
- FK: `profile_id` → `profiles(id)` ON DELETE CASCADE

**Indizes:**
- `idx_photos_profile_date` (profile_id, date DESC)

---

## 3. KI & Analysen

### 3.1 ai_prompts

**Beschreibung:** KI-Prompt-Definitionen (Unified Prompt System)

| Spalte | Typ | Nullable | Default | Beschreibung |
|--------|-----|----------|---------|--------------|
| id | uuid | NO | uuid_generate_v4() | PK |
| slug | varchar(100) | NO | - | Eindeutiger Slug (unique) |
| title | varchar(255) | NO | - | Titel |
| type | varchar(20) | YES | 'base' | Typ (base, pipeline) |
| category | varchar(50) | YES | NULL | Kategorie |
| stages | jsonb | YES | NULL | Pipeline-Stages |
| output_format | varchar(20) | YES | NULL | Output-Format (json, text) |
| output_schema | jsonb | YES | NULL | JSON-Schema für Output |
| active | boolean | YES | true | Aktiv |
| sort_order | integer | YES | 0 | Sortierung |
| created_at | timestamp | YES | now() | Erstellungsdatum |
| updated_at | timestamp | YES | now() | Letztes Update |

**JSONB-Struktur `stages`:**
```json
[
  {
    "name": "Stage 1",
    "prompts": [
      {
        "type": "reference",
        "slug": "body_summary"
      },
      {
        "type": "inline",
        "template": "Analyse {{weight_latest}}...",
        "output_format": "json",
        "output_schema": {...}
      }
    ]
  }
]
```

**Constraints:**
- PK: `id`
- UNIQUE: `slug`

**Indizes:**
- `idx_ai_prompts_slug` (slug)
- `ai_prompts_slug_key` UNIQUE (slug)
- `idx_ai_prompts_type` (type)
- `idx_ai_prompts_category` (category)
- `idx_ai_prompts_active_sort` (active, sort_order)
- `idx_ai_prompts_stages` GIN (stages)

---

### 3.2 ai_insights

**Beschreibung:** Gespeicherte KI-Analysen

| Spalte | Typ | Nullable | Default | Beschreibung |
|--------|-----|----------|---------|--------------|
| id | uuid | NO | uuid_generate_v4() | PK |
| profile_id | uuid | NO | - | FK zu profiles.id |
| scope | varchar(100) | YES | NULL | Prompt-Slug |
| content | text | NO | - | KI-Antwort |
| metadata | jsonb | YES | NULL | Platzhalter-Werte |
| created | timestamp | YES | now() | Erstellungsdatum |

**JSONB-Struktur `metadata`:**
```json
{
  "placeholders": {
    "weight_latest": 85.2,
    "goal_weight": 80.0,
    "activity_days": 28
  },
  "categories": {
    "PROFIL": [...],
    "KÖRPER": [...]
  }
}
```

**Constraints:**
- PK: `id`
- FK: `profile_id` → `profiles(id)` ON DELETE CASCADE

**Indizes:**
- `idx_ai_insights_profile_scope` (profile_id, scope, created DESC)

---

### 3.3 ai_usage

**Beschreibung:** Tägliche KI-Nutzung (Rate Limiting)

| Spalte | Typ | Nullable | Default | Beschreibung |
|--------|-----|----------|---------|--------------|
| id | uuid | NO | uuid_generate_v4() | PK |
| profile_id | uuid | NO | - | FK zu profiles.id |
| date | date | NO | - | Nutzungsdatum |
| count | integer | YES | 0 | Anzahl Calls |

**Constraints:**
- PK: `id`
- UNIQUE: `profile_id, date`
- FK: `profile_id` → `profiles(id)` ON DELETE CASCADE

**Indizes:**
- `idx_ai_usage_profile_date` (profile_id, date)
- `ai_usage_profile_id_date_key` UNIQUE (profile_id, date)

---

### 3.4 pipeline_configs

**Beschreibung:** Pipeline-Konfigurationen (deprecated, ersetzt durch ai_prompts)

| Spalte | Typ | Nullable | Default | Beschreibung |
|--------|-----|----------|---------|--------------|
| id | uuid | NO | uuid_generate_v4() | PK |
| name | varchar(100) | NO | - | Name (unique) |
| description | text | YES | NULL | Beschreibung |
| stages | jsonb | NO | - | Stage-Konfiguration |
| active | boolean | YES | true | Aktiv |
| is_default | boolean | YES | false | Standard-Pipeline |
| created_at | timestamp | YES | now() | Erstellungsdatum |

**Constraints:**
- PK: `id`
- UNIQUE: `name`

**Indizes:**
- `idx_pipeline_configs_active` (active)
- `idx_pipeline_configs_default` (is_default) WHERE is_default = true
- `idx_pipeline_configs_single_default` UNIQUE (is_default) WHERE is_default = true
- `pipeline_configs_name_key` UNIQUE (name)

---

## 4. Membership & Features

### 4.1 tiers

**Beschreibung:** Membership-Tiers (free, plus, premium, trial)

| Spalte | Typ | Nullable | Default | Beschreibung |
|--------|-----|----------|---------|--------------|
| id | text | NO | - | PK (free, plus, premium, trial) |
| name | text | NO | - | Display-Name |
| description | text | YES | NULL | Beschreibung |
| price_monthly | numeric(10,2) | YES | NULL | Preis/Monat |
| price_yearly | numeric(10,2) | YES | NULL | Preis/Jahr |
| trial_days | integer | YES | NULL | Trial-Dauer (Tage) |
| is_active | boolean | YES | true | Aktiv |
| sort_order | integer | YES | 0 | Sortierung |

**Constraints:**
- PK: `id`

---

### 4.2 features

**Beschreibung:** Feature-Definitionen

| Spalte | Typ | Nullable | Default | Beschreibung |
|--------|-----|----------|---------|--------------|
| id | text | NO | - | PK (feature_key) |
| name | text | NO | - | Display-Name |
| description | text | YES | NULL | Beschreibung |
| category | text | YES | NULL | Kategorie |

**Constraints:**
- PK: `id`

---

### 4.3 tier_limits

**Beschreibung:** Feature-Limits pro Tier

| Spalte | Typ | Nullable | Default | Beschreibung |
|--------|-----|----------|---------|--------------|
| id | uuid | NO | uuid_generate_v4() | PK |
| tier_id | text | NO | - | FK zu tiers.id |
| feature_id | text | NO | - | FK zu features.id |
| limit_value | integer | YES | NULL | Limit-Wert |
| limit_type | text | YES | NULL | Limit-Typ (daily, monthly, total) |

**Constraints:**
- PK: `id`
- UNIQUE: `tier_id, feature_id`
- FK: `tier_id` → `tiers(id)` ON DELETE CASCADE
- FK: `feature_id` → `features(id)` ON DELETE CASCADE

**Indizes:**
- `idx_tier_limits_tier` (tier_id)
- `idx_tier_limits_feature` (feature_id)
- `tier_limits_tier_id_feature_id_key` UNIQUE (tier_id, feature_id)

---

### 4.4 coupons

**Beschreibung:** Gutschein-Codes

| Spalte | Typ | Nullable | Default | Beschreibung |
|--------|-----|----------|---------|--------------|
| id | uuid | NO | uuid_generate_v4() | PK |
| code | varchar(50) | NO | - | Gutschein-Code (unique) |
| tier_id | text | YES | NULL | FK zu tiers.id |
| duration_days | integer | YES | NULL | Laufzeit (Tage) |
| max_uses | integer | YES | NULL | Max. Einlösungen |
| current_uses | integer | YES | 0 | Aktuelle Einlösungen |
| valid_from | timestamp | YES | NULL | Gültig ab |
| valid_until | timestamp | YES | NULL | Gültig bis |
| is_active | boolean | YES | true | Aktiv |
| created_at | timestamp | YES | now() | Erstellungsdatum |

**Constraints:**
- PK: `id`
- UNIQUE: `code`
- FK: `tier_id` → `tiers(id)` ON DELETE SET NULL

**Indizes:**
- `idx_coupons_code` (code)
- `coupons_code_key` UNIQUE (code)

---

### 4.5 coupon_redemptions

**Beschreibung:** Gutschein-Einlösungen

| Spalte | Typ | Nullable | Default | Beschreibung |
|--------|-----|----------|---------|--------------|
| id | uuid | NO | uuid_generate_v4() | PK |
| coupon_id | uuid | NO | - | FK zu coupons.id |
| profile_id | uuid | NO | - | FK zu profiles.id |
| redeemed_at | timestamp | YES | now() | Einlösungsdatum |

**Constraints:**
- PK: `id`
- UNIQUE: `coupon_id, profile_id`
- FK: `coupon_id` → `coupons(id)` ON DELETE CASCADE
- FK: `profile_id` → `profiles(id)` ON DELETE CASCADE

**Indizes:**
- `idx_coupon_redemptions_profile` (profile_id)
- `coupon_redemptions_coupon_id_profile_id_key` UNIQUE (coupon_id, profile_id)

---

### 4.6 access_grants

**Beschreibung:** Zeitgebundene Tier-Zugriffe

| Spalte | Typ | Nullable | Default | Beschreibung |
|--------|-----|----------|---------|--------------|
| id | uuid | NO | uuid_generate_v4() | PK |
| profile_id | uuid | NO | - | FK zu profiles.id |
| tier_id | text | NO | - | FK zu tiers.id |
| granted_by | text | YES | NULL | Vergeben durch |
| coupon_id | uuid | YES | NULL | FK zu coupons.id |
| valid_from | timestamp | NO | - | Gültig ab |
| valid_until | timestamp | NO | - | Gültig bis |
| is_active | boolean | YES | true | Aktiv |
| paused_by | uuid | YES | NULL | Pausiert von |
| paused_at | timestamp | YES | NULL | Pausiert am |
| remaining_days | integer | YES | NULL | Verbleibende Tage |

**Constraints:**
- PK: `id`
- FK: `profile_id` → `profiles(id)` ON DELETE CASCADE
- FK: `tier_id` → `tiers(id)` ON DELETE CASCADE
- FK: `coupon_id` → `coupons(id)` ON DELETE SET NULL

**Indizes:**
- `idx_access_grants_profile` (profile_id, valid_until DESC)
- `idx_access_grants_active` (profile_id, is_active, valid_until DESC)

---

### 4.7 user_feature_restrictions

**Beschreibung:** Nutzer-spezifische Feature-Restriktionen

| Spalte | Typ | Nullable | Default | Beschreibung |
|--------|-----|----------|---------|--------------|
| id | uuid | NO | uuid_generate_v4() | PK |
| profile_id | uuid | NO | - | FK zu profiles.id |
| feature_id | text | NO | - | FK zu features.id |
| custom_limit | integer | YES | NULL | Custom Limit |
| is_disabled | boolean | YES | false | Feature deaktiviert |

**Constraints:**
- PK: `id`
- UNIQUE: `profile_id, feature_id`
- FK: `profile_id` → `profiles(id)` ON DELETE CASCADE
- FK: `feature_id` → `features(id)` ON DELETE CASCADE

**Indizes:**
- `idx_user_restrictions_profile` (profile_id)
- `user_feature_restrictions_profile_id_feature_id_key` UNIQUE (profile_id, feature_id)

---

### 4.8 user_feature_usage

**Beschreibung:** Feature-Nutzungszähler

| Spalte | Typ | Nullable | Default | Beschreibung |
|--------|-----|----------|---------|--------------|
| id | uuid | NO | uuid_generate_v4() | PK |
| profile_id | uuid | NO | - | FK zu profiles.id |
| feature_id | text | NO | - | FK zu features.id |
| usage_count | integer | YES | 0 | Nutzungszähler |
| last_used_at | timestamp | YES | NULL | Letzte Nutzung |
| reset_at | timestamp | YES | NULL | Reset-Zeitpunkt |

**Constraints:**
- PK: `id`
- UNIQUE: `profile_id, feature_id`
- FK: `profile_id` → `profiles(id)` ON DELETE CASCADE
- FK: `feature_id` → `features(id)` ON DELETE CASCADE

**Indizes:**
- `idx_user_usage_profile` (profile_id)
- `user_feature_usage_profile_id_feature_id_key` UNIQUE (profile_id, feature_id)

---

### 4.9 user_activity_log

**Beschreibung:** Audit-Log für Nutzeraktionen

| Spalte | Typ | Nullable | Default | Beschreibung |
|--------|-----|----------|---------|--------------|
| id | uuid | NO | uuid_generate_v4() | PK |
| profile_id | uuid | NO | - | FK zu profiles.id |
| action | text | NO | - | Aktion |
| details | jsonb | YES | NULL | Details |
| created | timestamp | YES | now() | Erstellungsdatum |

**Constraints:**
- PK: `id`
- FK: `profile_id` → `profiles(id)` ON DELETE CASCADE

**Indizes:**
- `idx_activity_log_profile` (profile_id, created DESC)
- `idx_activity_log_action` (action, created DESC)

---

## 5. Ziele & Training

### 5.1 goals

**Beschreibung:** Nutzer-definierte Ziele

| Spalte | Typ | Nullable | Default | Beschreibung |
|--------|-----|----------|---------|--------------|
| id | uuid | NO | uuid_generate_v4() | PK |
| profile_id | uuid | NO | - | FK zu profiles.id |
| title | varchar(255) | NO | - | Zieltitel |
| goal_type | varchar(50) | NO | - | Zieltyp (weight, body_fat, etc.) |
| category | varchar(50) | YES | NULL | Kategorie |
| start_date | date | YES | NULL | Startdatum |
| target_date | date | YES | NULL | Zieldatum |
| start_value | numeric(10,2) | YES | NULL | Startwert |
| target_value | numeric(10,2) | NO | - | Zielwert |
| current_value | numeric(10,2) | YES | NULL | Aktueller Wert |
| source_table | varchar(50) | YES | NULL | Datenquelle-Tabelle |
| source_column | varchar(50) | YES | NULL | Datenquelle-Spalte |
| status | varchar(20) | YES | 'active' | Status (active, completed, paused) |
| is_primary | boolean | YES | false | Primäres Ziel |
| priority | integer | YES | 0 | Priorität |
| notes | text | YES | NULL | Notizen |
| created_at | timestamp | YES | now() | Erstellungsdatum |
| updated_at | timestamp | YES | now() | Letztes Update |

**Constraints:**
- PK: `id`
- FK: `profile_id` → `profiles(id)` ON DELETE CASCADE

**Indizes:**
- `idx_goals_profile` (profile_id)
- `idx_goals_status` (profile_id, status)
- `idx_goals_primary` (profile_id, is_primary) WHERE is_primary = true
- `idx_goals_category_priority` (profile_id, category, priority)

---

### 5.2 goal_type_definitions

**Beschreibung:** Zieltyp-Definitionen (weight, body_fat, vo2max, etc.)

| Spalte | Typ | Nullable | Default | Beschreibung |
|--------|-----|----------|---------|--------------|
| id | uuid | NO | uuid_generate_v4() | PK |
| type_key | varchar(50) | NO | - | Zieltyp-Key (unique) |
| display_name | varchar(100) | NO | - | Display-Name |
| category | varchar(50) | NO | - | Kategorie |
| unit | varchar(20) | YES | NULL | Einheit |
| description | text | YES | NULL | Beschreibung |
| is_active | boolean | YES | true | Aktiv |
| sort_order | integer | YES | 0 | Sortierung |
| created_at | timestamp | YES | now() | Erstellungsdatum |

**Constraints:**
- PK: `id`
- UNIQUE: `type_key`

**Indizes:**
- `idx_goal_type_definitions_category` (category)
- `idx_goal_type_definitions_active` (is_active) WHERE is_active = true
- `goal_type_definitions_type_key_key` UNIQUE (type_key)

---

### 5.3 goal_focus_contributions

**Beschreibung:** M:N Relationship Goals ↔ Focus Areas

| Spalte | Typ | Nullable | Default | Beschreibung |
|--------|-----|----------|---------|--------------|
| goal_id | uuid | NO | - | PK, FK zu goals.id |
| focus_area_id | integer | NO | - | PK, FK zu focus_area_definitions.id |
| contribution_weight | numeric(5,2) | YES | 100 | Gewichtung (%) |

**Constraints:**
- PK: `goal_id, focus_area_id`
- FK: `goal_id` → `goals(id)` ON DELETE CASCADE
- FK: `focus_area_id` → `focus_area_definitions(id)` ON DELETE CASCADE

**Indizes:**
- `idx_gfc_goal` (goal_id)
- `idx_gfc_focus_area` (focus_area_id)

---

### 5.4 goal_progress_log

**Beschreibung:** Tägliche Fortschrittswerte für Custom Goals

| Spalte | Typ | Nullable | Default | Beschreibung |
|--------|-----|----------|---------|--------------|
| id | uuid | NO | uuid_generate_v4() | PK |
| goal_id | uuid | NO | - | FK zu goals.id |
| profile_id | uuid | NO | - | FK zu profiles.id |
| date | date | NO | - | Datum |
| value | numeric(10,2) | NO | - | Wert |
| notes | text | YES | NULL | Notizen |
| created_at | timestamp | YES | now() | Erstellungsdatum |

**Constraints:**
- PK: `id`
- UNIQUE: `goal_id, date`
- FK: `goal_id` → `goals(id)` ON DELETE CASCADE
- FK: `profile_id` → `profiles(id)` ON DELETE CASCADE

**Indizes:**
- `idx_goal_progress_goal_date` (goal_id, date DESC)
- `idx_goal_progress_profile` (profile_id)
- `unique_progress_per_day` UNIQUE (goal_id, date)

---

### 5.5 focus_area_definitions

**Beschreibung:** Focus Area Definitionen (26 Bereiche in 7 Kategorien)

| Spalte | Typ | Nullable | Default | Beschreibung |
|--------|-----|----------|---------|--------------|
| id | serial | NO | - | PK |
| key | varchar(50) | NO | - | Focus Area Key (unique) |
| name | varchar(100) | NO | - | Display-Name |
| category | varchar(50) | NO | - | Kategorie |
| description | text | YES | NULL | Beschreibung |
| sort_order | integer | YES | 0 | Sortierung |
| is_active | boolean | YES | true | Aktiv |

**Constraints:**
- PK: `id`
- UNIQUE: `key`

**Indizes:**
- `idx_focus_area_key` (key)
- `idx_focus_area_category` (category)
- `focus_area_definitions_key_key` UNIQUE (key)

---

### 5.6 user_focus_area_weights

**Beschreibung:** User-spezifische Focus Area Gewichtungen

| Spalte | Typ | Nullable | Default | Beschreibung |
|--------|-----|----------|---------|--------------|
| profile_id | uuid | NO | - | PK, FK zu profiles.id |
| focus_area_id | integer | NO | - | PK, FK zu focus_area_definitions.id |
| weight | numeric(5,2) | YES | 0 | Gewichtung (0-100) |
| updated_at | timestamp | YES | now() | Letztes Update |

**Constraints:**
- PK: `profile_id, focus_area_id`
- FK: `profile_id` → `profiles(id)` ON DELETE CASCADE
- FK: `focus_area_id` → `focus_area_definitions(id)` ON DELETE CASCADE

**Indizes:**
- `idx_user_focus_weights_profile` (profile_id)
- `idx_user_focus_weights_area` (focus_area_id)

---

### 5.7 user_focus_preferences

**Beschreibung:** User Focus Preferences (deprecated/legacy)

| Spalte | Typ | Nullable | Default | Beschreibung |
|--------|-----|----------|---------|--------------|
| id | uuid | NO | uuid_generate_v4() | PK |
| profile_id | uuid | NO | - | FK zu profiles.id |
| focus_areas | jsonb | YES | NULL | Focus Areas (legacy) |
| active | boolean | YES | true | Aktiv |
| created_at | timestamp | YES | now() | Erstellungsdatum |
| updated_at | timestamp | YES | now() | Letztes Update |

**Constraints:**
- PK: `id`
- FK: `profile_id` → `profiles(id)` ON DELETE CASCADE

**Indizes:**
- `idx_focus_areas_profile_active` UNIQUE (profile_id) WHERE active = true

---

### 5.8 training_types

**Beschreibung:** Trainingstypen (29 Typen in 7 Kategorien)

| Spalte | Typ | Nullable | Default | Beschreibung |
|--------|-----|----------|---------|--------------|
| id | serial | NO | - | PK |
| name | varchar(100) | NO | - | Typname |
| category | varchar(50) | NO | - | Kategorie |
| color | varchar(7) | YES | NULL | Farbe (Hex) |
| icon | varchar(50) | YES | NULL | Icon-Name |
| description | text | YES | NULL | Beschreibung |
| abilities | jsonb | YES | NULL | Abilities-Matrix |
| profile | jsonb | YES | NULL | Profil-Einstellungen |

**JSONB-Struktur `abilities`:**
```json
{
  "kraft": 80,
  "ausdauer": 20,
  "beweglichkeit": 40,
  "schnelligkeit": 10,
  "koordination": 30,
  "balance": 20,
  "geist": 10
}
```

**Constraints:**
- PK: `id`

**Indizes:**
- `idx_training_types_category` (category)
- `idx_training_types_abilities` GIN (abilities)
- `idx_training_types_profile_enabled` (profile->'rule_sets'->'minimum_requirements'->>'enabled') WHERE profile IS NOT NULL

---

### 5.9 activity_type_mappings

**Beschreibung:** Lernendes Mapping-System (Workout-Name → Training Type)

| Spalte | Typ | Nullable | Default | Beschreibung |
|--------|-----|----------|---------|--------------|
| id | uuid | NO | uuid_generate_v4() | PK |
| activity_type | varchar(255) | NO | - | Workout-Name (unique per profile) |
| training_type_id | integer | NO | - | FK zu training_types.id |
| profile_id | uuid | YES | NULL | NULL = global, sonst user-spezifisch |
| is_global | boolean | YES | true | Global oder user-spezifisch |
| created_at | timestamp | YES | now() | Erstellungsdatum |

**Constraints:**
- PK: `id`
- UNIQUE: `activity_type, profile_id`
- FK: `training_type_id` → `training_types(id)` ON DELETE CASCADE

**Indizes:**
- `idx_activity_type_mappings_type` (activity_type)
- `idx_activity_type_mappings_profile` (profile_id)
- `unique_activity_type_per_profile` UNIQUE (activity_type, profile_id)

---

### 5.10 training_phases

**Beschreibung:** Trainingsphasen (Auto-Detection Framework)

| Spalte | Typ | Nullable | Default | Beschreibung |
|--------|-----|----------|---------|--------------|
| id | uuid | NO | uuid_generate_v4() | PK |
| profile_id | uuid | NO | - | FK zu profiles.id |
| phase_type | varchar(50) | NO | - | Phasentyp |
| start_date | date | NO | - | Startdatum |
| end_date | date | YES | NULL | Enddatum |
| status | varchar(20) | YES | 'suggested' | Status |
| confidence | numeric(5,2) | YES | NULL | Confidence (0-100) |
| description | text | YES | NULL | Beschreibung |
| created_at | timestamp | YES | now() | Erstellungsdatum |

**Constraints:**
- PK: `id`
- FK: `profile_id` → `profiles(id)` ON DELETE CASCADE

**Indizes:**
- `idx_training_phases_profile` (profile_id)
- `idx_training_phases_status` (profile_id, status)
- `idx_training_phases_dates` (profile_id, start_date, end_date)

---

### 5.11 fitness_tests

**Beschreibung:** Standardisierte Fitness-Tests

| Spalte | Typ | Nullable | Default | Beschreibung |
|--------|-----|----------|---------|--------------|
| id | uuid | NO | uuid_generate_v4() | PK |
| profile_id | uuid | NO | - | FK zu profiles.id |
| test_date | date | NO | - | Testdatum |
| test_type | varchar(50) | NO | - | Testtyp |
| result_value | numeric(10,2) | NO | - | Ergebnis |
| result_unit | varchar(20) | YES | NULL | Einheit |
| norm_category | varchar(50) | YES | NULL | Norm-Kategorie |
| notes | text | YES | NULL | Notizen |
| created_at | timestamp | YES | now() | Erstellungsdatum |

**Constraints:**
- PK: `id`
- FK: `profile_id` → `profiles(id)` ON DELETE CASCADE

**Indizes:**
- `idx_fitness_tests_profile` (profile_id)
- `idx_fitness_tests_type` (profile_id, test_type)
- `idx_fitness_tests_date` (profile_id, test_date)

---

### 5.12 training_parameters

**Beschreibung:** Globale Trainings-Parameter (Quality-Filter Settings)

| Spalte | Typ | Nullable | Default | Beschreibung |
|--------|-----|----------|---------|--------------|
| id | uuid | NO | uuid_generate_v4() | PK |
| key | varchar(100) | NO | - | Parameter-Key (unique) |
| category | varchar(50) | NO | - | Kategorie |
| value | text | NO | - | Wert |
| description | text | YES | NULL | Beschreibung |
| is_active | boolean | YES | true | Aktiv |
| created_at | timestamp | YES | now() | Erstellungsdatum |
| updated_at | timestamp | YES | now() | Letztes Update |

**Constraints:**
- PK: `id`
- UNIQUE: `key`

**Indizes:**
- `idx_training_parameters_key` (key) WHERE is_active = true
- `idx_training_parameters_category` (category) WHERE is_active = true
- `training_parameters_key_key` UNIQUE (key)

---

### 5.13 weekly_goals

**Beschreibung:** Wöchentliche Ziele (legacy)

| Spalte | Typ | Nullable | Default | Beschreibung |
|--------|-----|----------|---------|--------------|
| id | uuid | NO | uuid_generate_v4() | PK |
| profile_id | uuid | NO | - | FK zu profiles.id |
| week_start | date | NO | - | Wochenbeginn |
| goals | jsonb | YES | NULL | Ziele |
| created_at | timestamp | YES | now() | Erstellungsdatum |

**Constraints:**
- PK: `id`
- UNIQUE: `profile_id, week_start`
- FK: `profile_id` → `profiles(id)` ON DELETE CASCADE

**Indizes:**
- `idx_weekly_goals_profile_week` (profile_id, week_start DESC)
- `unique_weekly_goal_per_profile` UNIQUE (profile_id, week_start)

---

## 6. System-Tabellen

### 6.1 schema_migrations

**Beschreibung:** Migrations-Tracking

| Spalte | Typ | Nullable | Default | Beschreibung |
|--------|-----|----------|---------|--------------|
| id | serial | NO | - | PK |
| filename | varchar(255) | NO | - | Migrations-Dateiname (unique) |
| applied_at | timestamp | YES | now() | Anwendungsdatum |

**Constraints:**
- PK: `id`
- UNIQUE: `filename`

**Indizes:**
- `schema_migrations_filename_key` UNIQUE (filename)

---

### 6.2 app_settings

**Beschreibung:** Globale App-Einstellungen

| Spalte | Typ | Nullable | Default | Beschreibung |
|--------|-----|----------|---------|--------------|
| key | varchar(100) | NO | - | PK, Setting-Key |
| value | text | YES | NULL | Setting-Value |
| updated_at | timestamp | YES | now() | Letztes Update |

**Constraints:**
- PK: `key`

---

## 7. Backup-Tabellen

### 7.1 pipeline_configs_backup_pre_020

**Beschreibung:** Backup vor Migration 020 (Unified Prompt System)

| Spalte | Typ | Nullable | Default | Beschreibung |
|--------|-----|----------|---------|--------------|
| id | uuid | YES | - | Original ID |
| name | varchar(100) | YES | - | Name |
| description | text | YES | NULL | Beschreibung |
| stages | jsonb | YES | - | Stages |
| active | boolean | YES | true | Aktiv |
| is_default | boolean | YES | false | Default |
| created_at | timestamp | YES | now() | Erstellungsdatum |

**Keine Constraints oder Indizes.**

---

### 7.2 vitals_log_backup_pre_015

**Beschreibung:** Backup vor Vitals-Refactoring (Migration 015)

| Spalte | Typ | Nullable | Default | Beschreibung |
|--------|-----|----------|---------|--------------|
| id | uuid | NO | uuid_generate_v4() | PK |
| profile_id | uuid | NO | - | FK zu profiles.id |
| date | date | NO | - | Messdatum |
| resting_hr | integer | YES | NULL | Ruhepuls |
| hrv | integer | YES | NULL | HRV |
| systolic | integer | YES | NULL | Systolisch |
| diastolic | integer | YES | NULL | Diastolisch |
| pulse | integer | YES | NULL | Puls |
| vo2_max | numeric(5,2) | YES | NULL | VO2 Max |
| spo2 | integer | YES | NULL | SpO2 |
| respiratory_rate | integer | YES | NULL | Atemfrequenz |
| source | varchar(50) | YES | 'manual' | Datenquelle |
| notes | text | YES | NULL | Notizen |
| created_at | timestamp | YES | now() | Erstellungsdatum |

**Constraints:**
- PK: `id`
- UNIQUE: `profile_id, date`
- FK: `profile_id` → `profiles(id)` ON DELETE CASCADE

**Indizes:**
- `unique_vitals_per_day` UNIQUE (profile_id, date)

---

## 8. ER-Diagramm

### 8.1 Zentrale Entität: profiles

```
profiles (1) → (N) weight_log
profiles (1) → (N) circumference_log
profiles (1) → (N) caliper_log
profiles (1) → (N) activity_log
profiles (1) → (N) nutrition_log
profiles (1) → (N) sleep_log
profiles (1) → (N) vitals_baseline
profiles (1) → (N) blood_pressure_log
profiles (1) → (N) rest_days
profiles (1) → (N) photos
profiles (1) → (N) sessions
profiles (1) → (N) ai_insights
profiles (1) → (N) ai_usage
profiles (1) → (N) goals
profiles (1) → (N) goal_progress_log
profiles (1) → (N) training_phases
profiles (1) → (N) fitness_tests
profiles (1) → (N) access_grants
profiles (1) → (N) coupon_redemptions
profiles (1) → (N) user_feature_restrictions
profiles (1) → (N) user_feature_usage
profiles (1) → (N) user_activity_log
profiles (1) → (1) user_stats
profiles (1) → (N) user_focus_area_weights
profiles (1) → (N) user_focus_preferences
profiles (1) → (N) activity_type_mappings
profiles (1) → (N) weekly_goals
profiles (self) → (N) profiles (invited_by)
```

### 8.2 Trainings-Typen-Beziehungen

```
training_types (1) → (N) activity_log
training_types (1) → (N) activity_type_mappings
```

### 8.3 Membership-Beziehungen

```
tiers (1) → (N) tier_limits
tiers (1) → (N) coupons
tiers (1) → (N) access_grants

features (1) → (N) tier_limits
features (1) → (N) user_feature_restrictions
features (1) → (N) user_feature_usage

coupons (1) → (N) coupon_redemptions
coupons (1) → (N) access_grants
```

### 8.4 Ziele-Beziehungen

```
goals (1) → (N) goal_progress_log
goals (N) → (M) focus_area_definitions (via goal_focus_contributions)

focus_area_definitions (N) → (M) profiles (via user_focus_area_weights)
```

### 8.5 Kardinalitäten

| Beziehung | Typ | ON DELETE |
|-----------|-----|-----------|
| profiles → sessions | 1:N | CASCADE |
| profiles → weight_log | 1:N | CASCADE |
| profiles → activity_log | 1:N | CASCADE |
| profiles → goals | 1:N | CASCADE |
| profiles → access_grants | 1:N | CASCADE |
| training_types → activity_log | 1:N | NO ACTION |
| tiers → tier_limits | 1:N | CASCADE |
| goals → goal_focus_contributions | 1:N | CASCADE |
| focus_area_definitions → goal_focus_contributions | 1:N | CASCADE |

---

## 9. Datenbank-Statistiken

### 9.1 Tabellen-Übersicht

| Kategorie | Anzahl Tabellen |
|-----------|-----------------|
| Kernmodule | 3 (profiles, sessions, user_stats) |
| Tracking | 10 (weight, circumference, caliper, activity, nutrition, sleep, vitals_baseline, blood_pressure, rest_days, photos) |
| KI & Analysen | 4 (ai_prompts, ai_insights, ai_usage, pipeline_configs) |
| Membership | 9 (tiers, features, tier_limits, coupons, coupon_redemptions, access_grants, user_feature_restrictions, user_feature_usage, user_activity_log) |
| Ziele & Training | 13 (goals, goal_type_definitions, goal_progress_log, goal_focus_contributions, focus_area_definitions, user_focus_area_weights, user_focus_preferences, training_types, activity_type_mappings, training_phases, fitness_tests, training_parameters, weekly_goals) |
| System | 2 (schema_migrations, app_settings) |
| Backup | 2 (pipeline_configs_backup_pre_020, vitals_log_backup_pre_015) |
| **Gesamt** | **43** |

### 9.2 Foreign Keys

| Tabelle | Anzahl FKs |
|---------|------------|
| access_grants | 3 |
| activity_log | 2 |
| activity_type_mappings | 1 |
| ai_insights | 1 |
| ai_usage | 1 |
| blood_pressure_log | 1 |
| caliper_log | 1 |
| circumference_log | 1 |
| coupon_redemptions | 2 |
| coupons | 1 |
| fitness_tests | 1 |
| goal_focus_contributions | 2 |
| goal_progress_log | 2 |
| goals | 1 |
| nutrition_log | 1 |
| photos | 1 |
| profiles | 1 (self-reference) |
| rest_days | 1 |
| sessions | 1 |
| sleep_log | 1 |
| tier_limits | 2 |
| training_phases | 1 |
| user_activity_log | 1 |
| user_feature_restrictions | 2 |
| user_feature_usage | 2 |
| user_focus_area_weights | 2 |
| user_focus_preferences | 1 |
| user_stats | 1 |
| vitals_baseline | 1 |
| vitals_log_backup_pre_015 | 1 |
| weekly_goals | 1 |
| weight_log | 1 |
| **Gesamt** | **42** |

### 9.3 Indizes

| Typ | Anzahl |
|-----|--------|
| Primary Keys | 43 |
| Unique Constraints | 36 |
| B-Tree Indizes | 86 |
| GIN Indizes (JSONB) | 7 |
| Partial Indizes | 12 |
| **Gesamt** | **184** |

---

## 10. Wichtige Design-Patterns

### 10.1 Profile-ID Isolation

**Regel:** Jede Tabelle mit Nutzerdaten hat `profile_id` als Foreign Key.

**Vorteile:**
- Strikte Datenisolation pro Nutzer
- Kein Zugriff auf fremde Daten möglich
- Performante Queries durch Index auf profile_id

**Beispiel Query:**
```sql
SELECT * FROM weight_log
WHERE profile_id = '...' AND date >= '2026-01-01'
ORDER BY date DESC;
```

### 10.2 Source-Tracking

**Regel:** Tabellen mit Import-Daten haben `source` Spalte.

**Werte:**
- `'manual'` – manuell erfasst
- `'apple_health'` – Apple Health Import
- `'garmin'` – Garmin Import
- `'withings'` – Withings Import

**Regel bei Reimport:**
```sql
INSERT INTO sleep_log (...)
ON CONFLICT (profile_id, date)
DO UPDATE SET ... WHERE sleep_log.source != 'manual';
```

**Schutz:** Manuelle Einträge werden niemals überschrieben.

### 10.3 JSONB für flexible Daten

**Verwendung:**
- `ai_prompts.stages` – dynamische Pipeline-Stages
- `ai_insights.metadata` – Platzhalter-Werte
- `activity_log.evaluation` – Qualitätsbewertungen
- `sleep_log.sleep_segments` – Schlafphasen
- `rest_days.rest_config` – Ruhetag-Konfiguration
- `training_types.abilities` – Abilities-Matrix
- `training_parameters.value` – Parameter-Werte

**Indizes:**
- GIN-Indizes für JSONB-Suchen
- Partial-Indizes mit JSONB-Extraktion

**Beispiel:**
```sql
-- GIN Index für vollständige JSONB-Suche
CREATE INDEX idx_ai_prompts_stages ON ai_prompts USING gin (stages);

-- Partial Index für spezifischen JSONB-Wert
CREATE INDEX idx_training_types_profile_enabled
ON training_types (((profile->'rule_sets'->'minimum_requirements'->>'enabled')))
WHERE profile IS NOT NULL;
```

### 10.4 Composite Primary Keys

**M:N Beziehungen:**
- `goal_focus_contributions` (goal_id, focus_area_id)
- `user_focus_area_weights` (profile_id, focus_area_id)

**Zeitbasierte Uniqueness:**
- `weight_log` (profile_id, date)
- `sleep_log` (profile_id, date)
- `vitals_baseline` (profile_id, date)
- `blood_pressure_log` (profile_id, measured_at)

### 10.5 Soft Deletes & Status-Felder

**Status-Felder:**
- `goals.status` (active, completed, paused)
- `training_phases.status` (suggested, accepted, active, completed)
- `ai_prompts.active` (true/false)
- `coupons.is_active` (true/false)

**Keine Hard Deletes bei:**
- ai_insights (Historische Analysen behalten)
- goals (Abgeschlossene Ziele für Statistiken)
- training_phases (Phasen-Historie)

### 10.6 Auto-Updated Timestamps

**Pattern:**
```sql
created_at TIMESTAMP DEFAULT NOW()
updated_at TIMESTAMP DEFAULT NOW()
```

**Trigger für auto-update:**
```sql
CREATE TRIGGER update_timestamp
BEFORE UPDATE ON tabelle
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();
```

---

## 11. Performance-Optimierungen

### 11.1 Index-Strategie

**Profile-ID + Date Indizes:**
```sql
idx_weight_log_profile_date (profile_id, date DESC)
idx_activity_profile_date (profile_id, date DESC)
idx_sleep_profile_date (profile_id, date DESC)
```

**Grund:** Häufigste Query-Pattern = "alle Einträge für User X, neuste zuerst"

### 11.2 Partial Indizes

**Beispiele:**
```sql
-- Nur aktive Prompts indizieren
CREATE INDEX idx_ai_prompts_active_sort
ON ai_prompts (active, sort_order);

-- Nur Einträge mit Quality-Label
CREATE INDEX idx_activity_quality_label
ON activity_log (quality_label)
WHERE quality_label IS NOT NULL;

-- Nur verifizierte E-Mails
CREATE INDEX idx_profiles_verification_token
ON profiles (verification_token)
WHERE verification_token IS NOT NULL;
```

**Vorteil:** Kleinere Index-Größe, schnellere Updates

### 11.3 Composite Indizes

**Multi-Column Indizes:**
```sql
idx_access_grants_active (profile_id, is_active, valid_until DESC)
idx_goals_category_priority (profile_id, category, priority)
idx_tier_limits_tier (tier_id, feature_id)
```

**Regel:** Häufigste WHERE-Klauseln in Index-Spalten-Reihenfolge

### 11.4 GIN-Indizes für JSONB

**JSONB-Suchen beschleunigen:**
```sql
CREATE INDEX idx_ai_prompts_stages ON ai_prompts USING gin (stages);
CREATE INDEX idx_training_types_abilities ON training_types USING gin (abilities);
CREATE INDEX idx_rest_days_config ON rest_days USING gin (rest_config);
```

**Verwendung:**
```sql
SELECT * FROM ai_prompts
WHERE stages @> '[{"name": "Stage 1"}]'::jsonb;
```

---

## 12. Migration-Historie

### Migrations-Log

| Migration | Datum | Beschreibung |
|-----------|-------|--------------|
| 001-010 | 2025-2026 | Basis-Tabellen (weight, circumference, caliper, nutrition, activity, photos, ai) |
| 011-012 | 2026-02 | Membership-System (tiers, features, access_grants) |
| 013-014 | 2026-03 | Sleep + Rest Days |
| 015 | 2026-03 | Vitals-Refactoring (vitals_baseline + blood_pressure_log) |
| 016-021 | 2026-03 | Training Types + Activity Mappings |
| 022 | 2026-03 | Goal System (goals, training_phases, fitness_tests) |
| 023-028 | 2026-03 | Goal System Extensions (auto-population, time-based tracking) |
| 029-030 | 2026-03 | Dynamic Focus Areas v2.0 (focus_area_definitions, user_focus_area_weights) |
| 031-032 | 2026-03 | Goal Focus Contributions (M:N Relationship) |

**Aktueller Stand:** Migration 032  
**Tracking-Tabelle:** `schema_migrations`

---

## 13. Bekannte Deprecated-Elemente

### 13.1 Deprecated Tabellen

| Tabelle | Ersetzt durch | Migration |
|---------|---------------|-----------|
| vitals_log | vitals_baseline + blood_pressure_log | 015 |
| pipeline_configs | ai_prompts (Unified System) | 020 |

**Backup-Tabellen behalten für Rollback-Szenarien.**

### 13.2 Deprecated Felder

| Tabelle | Feld | Grund |
|---------|------|-------|
| profiles | goal_mode | Ersetzt durch Dynamic Focus Areas |
| user_focus_preferences | focus_areas | Ersetzt durch user_focus_area_weights |

---

## 14. Datenintegrität

### 14.1 Foreign Key Constraints

**ON DELETE CASCADE:**
- Alle Tracking-Daten → profiles
- sessions → profiles
- goals → profiles
- access_grants → profiles

**ON DELETE SET NULL:**
- access_grants → coupons
- coupons → tiers

**ON DELETE NO ACTION:**
- activity_log → training_types
- profiles → profiles (invited_by)

### 14.2 Check Constraints

**Beispiele:**
```sql
-- profiles.tier
CHECK (tier IN ('free', 'plus', 'premium', 'trial'))

-- goals.status
CHECK (status IN ('active', 'completed', 'paused'))

-- training_phases.phase_type
CHECK (phase_type IN ('calorie_deficit', 'calorie_surplus', 'deload', 'maintenance', 'periodization'))
```

### 14.3 Unique Constraints

**Pro User + Datum:**
- `weight_log` (profile_id, date)
- `sleep_log` (profile_id, date)
- `vitals_baseline` (profile_id, date)
- `blood_pressure_log` (profile_id, measured_at)
- `rest_days` (profile_id, date, focus)

**Pro User + Feature:**
- `user_feature_restrictions` (profile_id, feature_id)
- `user_feature_usage` (profile_id, feature_id)
- `ai_usage` (profile_id, date)

**Global:**
- `profiles` (email)
- `ai_prompts` (slug)
- `coupons` (code)
- `focus_area_definitions` (key)

---

## 15. Nächste Schritte (Roadmap)

### Phase 0c: Data Layer & Charts ✅ COMPLETED
- ✅ Data Layer Migration (97 Funktionen)
- ✅ Chart Endpoints (20 Endpoints E1-E5, A1-A8, R1-R5, C1-C4)
- 🔲 Frontend Chart Integration (IN PROGRESS)

### Phase 1: Advanced Goals & Tracking
- 🔲 Goal Templates System
- 🔲 Automated Training Phase Detection
- 🔲 Goal-Aware Placeholders (120+)

### Phase 2: Integrations
- 🔲 Apple Health Connector (persistent sync)
- 🔲 Garmin Connect Integration
- 🔲 Withings API Integration
- 🔲 Oura Ring Support

### Phase 3: Advanced Features
- 🔲 Multi-Profile Support (Trainer → Clients)
- 🔲 Team/Group Features
- 🔲 Stripe Payment Integration
- 🔲 Advanced Correlations (Lag-Analysis)

---

## Anhang

### A. Tabellen-Index (alphabetisch)

```
access_grants
activity_log
activity_type_mappings
ai_insights
ai_prompts
ai_usage
app_settings
blood_pressure_log
caliper_log
circumference_log
coupon_redemptions
coupons
features
fitness_tests
focus_area_definitions
goal_focus_contributions
goal_progress_log
goal_type_definitions
goals
nutrition_log
photos
pipeline_configs
pipeline_configs_backup_pre_020
profiles
rest_days
schema_migrations
sessions
sleep_log
tier_limits
tiers
training_parameters
training_phases
training_types
user_activity_log
user_feature_restrictions
user_feature_usage
user_focus_area_weights
user_focus_preferences
user_stats
vitals_baseline
vitals_log_backup_pre_015
weekly_goals
weight_log
```

### B. Query-Beispiele

**Gewichtsverlauf (letzte 90 Tage):**
```sql
SELECT date, weight
FROM weight_log
WHERE profile_id = '...'
  AND date >= CURRENT_DATE - INTERVAL '90 days'
ORDER BY date DESC;
```

**Aktivitäten mit Quality-Label "high":**
```sql
SELECT a.date, a.description, t.name AS training_type, a.overall_score
FROM activity_log a
JOIN training_types t ON a.training_type_id = t.id
WHERE a.profile_id = '...'
  AND a.quality_label = 'high'
ORDER BY a.date DESC;
```

**Aktuelle Tier-Limits für User:**
```sql
SELECT f.name, tl.limit_value, tl.limit_type
FROM profiles p
JOIN tier_limits tl ON p.tier = tl.tier_id
JOIN features f ON tl.feature_id = f.id
WHERE p.id = '...';
```

**Schlafqualität (7 Tage):**
```sql
SELECT date,
       total_duration_minutes,
       (sleep_segments->>'deep')::int AS deep_minutes,
       (sleep_segments->>'rem')::int AS rem_minutes,
       quality_score
FROM sleep_log
WHERE profile_id = '...'
  AND date >= CURRENT_DATE - INTERVAL '7 days'
ORDER BY date DESC;
```

**Aktive Ziele mit Progress:**
```sql
SELECT title, goal_type, target_value, current_value,
       ROUND((current_value / target_value) * 100, 1) AS progress_pct
FROM goals
WHERE profile_id = '...'
  AND status = 'active'
ORDER BY priority DESC, created_at ASC;
```

---

**Ende des Datenmodells**