HabitForge/DATABASE_SCHEMA.md

📊 HabitForge - Database Schema

Diagrammes Mermaid du schéma de base de données

---

Vue d'Ensemble (Core + Health Connect)

erDiagram
    users ||--o{ challenges : "owns"
    users ||--o{ daily_health_metrics : "tracks"
    users ||--o{ daily_context : "logs"
    users ||--o{ workout_sessions : "records"
    users ||--o{ health_connect_sync_logs : "syncs"
    challenges ||--o{ trackings : "has"

    users {
        int id PK
        string username UK
        string hashed_password
    }

    challenges {
        int id PK
        int user_id FK
        string name
        string period_type
        int daily_target
        date start_date
        date end_date
        date created_at
        int display_order
        string unit_type
        string rest_days
        string icon
        string image_url
        string description
        int frequency_count
        string selected_days
    }

    tracking {
        int id PK
        int challenge_id FK
        date date
        int reps
        boolean completed
        text notes
    }

    daily_health_metrics {
        int id PK
        int user_id FK
        date date
        int step_count
        float distance_meters
        float calories_burned
        int sleep_duration_minutes
        int deep_sleep_minutes
        int light_sleep_minutes
        int rem_sleep_minutes
        int awake_duration_minutes
        int avg_heart_rate
        int min_heart_rate
        int max_heart_rate
        float avg_spo2
        float pai_score
        float weight
        int resting_heart_rate
        float hrv
        string data_source
    }

    daily_context {
        int id PK
        int user_id FK
        date date
        int mood_score
        string tags
        string notes
    }

    workout_sessions {
        int id PK
        int user_id FK
        datetime start_time
        datetime end_time
        string activity_type
        int activity_type_id
        int duration_seconds
        float distance_meters
        float calories
        float avg_pace
        float max_pace
        float min_pace
        int avg_hr
        int max_hr
        int min_hr
        string notes
        string data_source
    }

---

Health Connect (Sync & Agrégé)

erDiagram
    users ||--o{ health_connect_sync_logs : "syncs"
    users ||--o{ health_connect_daily_metrics : "aggregates"
    users ||--o{ health_connect_exercise_sessions : "exercises"
    health_connect_sync_logs ||--o{ health_connect_daily_metrics : "produced"
    health_connect_sync_logs ||--o{ health_connect_exercise_sessions : "produced"

    health_connect_sync_logs {
        int id PK
        int user_id FK
        string device_id
        datetime sync_timestamp
        int records_pushed
        string status
        text error_message
    }

    health_connect_daily_metrics {
        int id PK
        int user_id FK
        date date
        string data_source
        string data_source_name
        int step_count
        float distance_meters
        float calories_burned
        float total_calories
        int sleep_duration_minutes
        int deep_sleep_minutes
        int light_sleep_minutes
        int rem_sleep_minutes
        int awake_duration_minutes
        int avg_heart_rate
        int min_heart_rate
        int max_heart_rate
        int resting_heart_rate
        float avg_spo2
        float min_spo2
        float max_spo2
        float weight
        datetime last_synced
        int sync_id FK
    }

    health_connect_exercise_sessions {
        int id PK
        int user_id FK
        datetime start_time
        datetime end_time
        int exercise_type
        string exercise_type_name
        int duration_seconds
        float distance_meters
        float calories_burned
        int avg_heart_rate
        int max_heart_rate
        int min_heart_rate
        int steps
        float elevation_gained
        text notes
        datetime last_synced
        int sync_id FK
    }

---

Health Connect Raw Data (15 tables)

Steps, Distance, Calories

erDiagram
    users ||--o{ hc_raw_steps : "steps"
    users ||--o{ hc_raw_distance : "distance"
    users ||--o{ hc_raw_calories : "calories"
    health_connect_sync_logs ||--o{ hc_raw_steps : "batch"
    health_connect_sync_logs ||--o{ hc_raw_distance : "batch"
    health_connect_sync_logs ||--o{ hc_raw_calories : "batch"

    hc_raw_steps {
        int id PK
        int user_id FK
        string record_id
        string data_source
        string data_source_name
        datetime start_time
        datetime end_time
        datetime synced_at
        int count
        int sync_id FK
    }

    hc_raw_distance {
        int id PK
        int user_id FK
        string record_id
        string data_source
        string data_source_name
        datetime start_time
        datetime end_time
        datetime synced_at
        float distance_meters
        int sync_id FK
    }

    hc_raw_calories {
        int id PK
        int user_id FK
        string record_id
        string data_source
        string data_source_name
        datetime start_time
        datetime end_time
        datetime synced_at
        float calories
        string record_type
        int sync_id FK
    }

Heart Rate & HRV

erDiagram
    users ||--o{ hc_raw_heart_rate : "records"
    users ||--o{ hc_raw_resting_hr : "resting"
    users ||--o{ hc_raw_hrv_rmssd : "hrv"
    hc_raw_heart_rate ||--o{ hc_raw_heart_rate_samples : "contains"

    hc_raw_heart_rate {
        int id PK
        int user_id FK
        string record_id
        string data_source
        string data_source_name
        datetime start_time
        datetime end_time
        datetime synced_at
        int sync_id FK
    }

    hc_raw_heart_rate_samples {
        int id PK
        int record_id FK
        datetime time
        int bpm
    }

    hc_raw_resting_hr {
        int id PK
        int user_id FK
        string record_id
        string data_source
        string data_source_name
        datetime time
        datetime synced_at
        int bpm
        int sync_id FK
    }

    hc_raw_hrv_rmssd {
        int id PK
        int user_id FK
        string record_id
        string data_source
        string data_source_name
        datetime time
        datetime synced_at
        float rmssd
        int sync_id FK
    }

Sleep

erDiagram
    users ||--o{ hc_raw_sleep_sessions : "sleeps"
    hc_raw_sleep_sessions ||--o{ hc_raw_sleep_stages : "stages"

    hc_raw_sleep_sessions {
        int id PK
        int user_id FK
        string record_id
        string data_source
        string data_source_name
        datetime start_time
        datetime end_time
        datetime synced_at
        string title
        text notes
        int sync_id FK
    }

    hc_raw_sleep_stages {
        int id PK
        int session_id FK
        datetime start_time
        datetime end_time
        int stage_type
        string stage_name
    }

Body & Health Metrics

erDiagram
    users ||--o{ hc_raw_weight : "weight"
    users ||--o{ hc_raw_height : "height"
    users ||--o{ hc_raw_body_fat : "bodyfat"
    users ||--o{ hc_raw_spo2 : "spo2"

    hc_raw_weight {
        int id PK
        int user_id FK
        string record_id
        string data_source
        string data_source_name
        datetime time
        datetime synced_at
        float weight_kg
        int sync_id FK
    }

    hc_raw_height {
        int id PK
        int user_id FK
        string record_id
        string data_source
        string data_source_name
        datetime time
        datetime synced_at
        float height_meters
        int sync_id FK
    }

    hc_raw_body_fat {
        int id PK
        int user_id FK
        string record_id
        string data_source
        string data_source_name
        datetime time
        datetime synced_at
        float percentage
        int sync_id FK
    }

    hc_raw_spo2 {
        int id PK
        int user_id FK
        string record_id
        string data_source
        string data_source_name
        datetime time
        datetime synced_at
        float percentage
        int sync_id FK
    }

Exercise, Nutrition & Hydration

erDiagram
    users ||--o{ hc_raw_exercise_sessions : "exercises"
    users ||--o{ hc_raw_nutrition : "nutrition"
    users ||--o{ hc_raw_hydration : "hydration"

    hc_raw_exercise_sessions {
        int id PK
        int user_id FK
        string record_id
        string data_source
        string data_source_name
        datetime start_time
        datetime end_time
        datetime synced_at
        int exercise_type
        string exercise_type_name
        string title
        text notes
        float distance_meters
        float calories
        int avg_heart_rate
        int max_heart_rate
        int min_heart_rate
        int steps
        float elevation_gained_meters
        int sync_id FK
    }

    hc_raw_nutrition {
        int id PK
        int user_id FK
        string record_id
        string data_source
        string data_source_name
        datetime start_time
        datetime end_time
        datetime synced_at
        string name
        int meal_type
        float calories
        float protein_grams
        float carbohydrates_grams
        float fat_grams
        float fiber_grams
        float sugar_grams
        int sync_id FK
    }

    hc_raw_hydration {
        int id PK
        int user_id FK
        string record_id
        string data_source
        string data_source_name
        datetime start_time
        datetime end_time
        datetime synced_at
        float volume_liters
        int sync_id FK
    }

---

Résumé des Tables

#	Table	Type	Description
1	users	Core	Comptes utilisateurs
2	challenges	Core	Défis/habitudes à suivre
3	tracking	Core	Progression quotidienne des défis
4	daily_health_metrics	Core	Métriques santé agrégées (legacy)
5	daily_context	Core	Contexte subjectif (humeur, tags)
6	workout_sessions	Core	Sessions d'entraînement manuelles
7	health_connect_sync_logs	HC	Journal de synchronisation
8	health_connect_daily_metrics	HC	Métriques quotidiennes Health Connect
9	health_connect_exercise_sessions	HC	Sessions d'exercice Health Connect
10	hc_raw_steps	Raw	Pas individuels
11	hc_raw_heart_rate	Raw	Conteneurs de fréquence cardiaque
12	hc_raw_heart_rate_samples	Raw	Échantillons de fréquence cardiaque
13	hc_raw_resting_hr	Raw	Fréquence cardiaque au repos
14	hc_raw_sleep_sessions	Raw	Sessions de sommeil
15	hc_raw_sleep_stages	Raw	Phases de sommeil
16	hc_raw_distance	Raw	Distance
17	hc_raw_calories	Raw	Calories
18	hc_raw_spo2	Raw	Saturation en oxygène
19	hc_raw_weight	Raw	Poids
20	hc_raw_height	Raw	Taille
21	hc_raw_body_fat	Raw	Masse grasse
22	hc_raw_exercise_sessions	Raw	Sessions d'exercice brutes
23	hc_raw_nutrition	Raw	Nutrition
24	hc_raw_hydration	Raw	Hydratation
25	hc_raw_hrv_rmssd	Raw	Variabilité cardiaque (HRV)

---

Flux de Données

graph TD
    A[Android Health Connect API] -->|Raw Records| B[Android Companion App]
    B -->|POST /api/health-connect/sync-raw| C[Backend FastAPI]
    C -->|Batch Insert| D[(hc_raw_* Tables)]
    D -->|AggregationService| E[health_connect_daily_metrics]
    D -->|Health Analytics API| F[/api/analytics/daily]
    D -->|Health Analytics API| G[/api/analytics/range]
    E -->|Legacy API| H[/api/health/stats]
    F --> I[Frontend Dashboard]
    G --> I
    H --> I

    J[Zepp Export CSV] -->|Script Import| C
    C --> K[(daily_health_metrics)]
    C --> L[(workout_sessions)]
    K --> H
    L --> M[/api/health/workouts]
    M --> I

---

Index & Contraintes

Toutes les tables raw incluent :

• Unique Constraint sur (user_id, record_id, data_source) pour éviter les doublons
• Index sur (user_id, start_time) ou (user_id, time) pour des requêtes rapides par période
• Foreign Key vers health_connect_sync_logs pour la traçabilité
• Foreign Key vers users pour l'isolation multi-utilisateurs