504 lines
12 KiB
Markdown
504 lines
12 KiB
Markdown
# 📊 HabitForge - Database Schema
|
|
|
|
> Diagrammes Mermaid du schéma de base de données
|
|
|
|
---
|
|
|
|
## Vue d'Ensemble (Core + Health Connect)
|
|
|
|
```mermaid
|
|
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é)
|
|
|
|
```mermaid
|
|
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
|
|
|
|
```mermaid
|
|
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
|
|
|
|
```mermaid
|
|
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
|
|
|
|
```mermaid
|
|
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
|
|
|
|
```mermaid
|
|
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
|
|
|
|
```mermaid
|
|
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
|
|
|
|
```mermaid
|
|
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
|