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
2. Tracking-Module
3. KI & Analysen
4. Membership & Features
5. Ziele & Training
6. System-Tabellen
7. Backup-Tabellen
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:

{
  "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:

{
  "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:

[
  {
    "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:

{
  "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:

{
  "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:

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:

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:

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

created_at TIMESTAMP DEFAULT NOW()
updated_at TIMESTAMP DEFAULT NOW()

Trigger für auto-update:

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:

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:

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

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:

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:

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:

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

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

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:

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

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:

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