462 lines
16 KiB
Python
462 lines
16 KiB
Python
"""
|
|
Health Connect Raw Data Models - Storage for raw health records from Health Connect.
|
|
|
|
These models store individual health records (not aggregated) received from
|
|
the Android companion app, enabling accurate aggregation on-demand.
|
|
"""
|
|
from sqlalchemy import Column, Integer, String, Float, DateTime, ForeignKey, Text, UniqueConstraint, Index
|
|
from sqlalchemy.orm import relationship
|
|
from backend.database import Base
|
|
import datetime
|
|
|
|
|
|
# ==================== STEPS ====================
|
|
|
|
class HCRawSteps(Base):
|
|
"""Individual step records from Health Connect StepsRecord"""
|
|
__tablename__ = "hc_raw_steps"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
user_id = Column(Integer, ForeignKey("users.id"), index=True)
|
|
|
|
# Record identification (from Health Connect metadata)
|
|
record_id = Column(String(100), index=True) # metadata.id - unique per source
|
|
data_source = Column(String(200)) # Package name (e.g., com.huami.watch.hmwatchmanager)
|
|
data_source_name = Column(String(100)) # Friendly name (e.g., "Zepp")
|
|
|
|
# Timestamps
|
|
start_time = Column(DateTime, index=True)
|
|
end_time = Column(DateTime)
|
|
synced_at = Column(DateTime, default=datetime.datetime.utcnow)
|
|
|
|
# Data
|
|
count = Column(Integer)
|
|
|
|
# Sync tracking
|
|
sync_id = Column(Integer, ForeignKey("health_connect_sync_logs.id"), nullable=True)
|
|
|
|
# Unique constraint: same user + record_id + source = unique record
|
|
__table_args__ = (
|
|
UniqueConstraint('user_id', 'record_id', 'data_source', name='uq_steps_record'),
|
|
Index('ix_steps_user_date', 'user_id', 'start_time'),
|
|
)
|
|
|
|
|
|
# ==================== HEART RATE ====================
|
|
|
|
class HCRawHeartRate(Base):
|
|
"""Heart rate record containers from Health Connect HeartRateRecord"""
|
|
__tablename__ = "hc_raw_heart_rate"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
user_id = Column(Integer, ForeignKey("users.id"), index=True)
|
|
|
|
record_id = Column(String(100), index=True)
|
|
data_source = Column(String(200))
|
|
data_source_name = Column(String(100))
|
|
|
|
start_time = Column(DateTime, index=True)
|
|
end_time = Column(DateTime)
|
|
synced_at = Column(DateTime, default=datetime.datetime.utcnow)
|
|
|
|
sync_id = Column(Integer, ForeignKey("health_connect_sync_logs.id"), nullable=True)
|
|
|
|
# Relationship to samples
|
|
samples = relationship("HCRawHeartRateSample", back_populates="record", cascade="all, delete-orphan")
|
|
|
|
__table_args__ = (
|
|
UniqueConstraint('user_id', 'record_id', 'data_source', name='uq_hr_record'),
|
|
Index('ix_hr_user_date', 'user_id', 'start_time'),
|
|
)
|
|
|
|
|
|
class HCRawHeartRateSample(Base):
|
|
"""Individual heart rate samples within a HeartRateRecord"""
|
|
__tablename__ = "hc_raw_heart_rate_samples"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
record_id = Column(Integer, ForeignKey("hc_raw_heart_rate.id", ondelete="CASCADE"), index=True)
|
|
|
|
time = Column(DateTime, index=True)
|
|
bpm = Column(Integer)
|
|
|
|
record = relationship("HCRawHeartRate", back_populates="samples")
|
|
|
|
|
|
# ==================== RESTING HEART RATE ====================
|
|
|
|
class HCRawRestingHeartRate(Base):
|
|
"""Resting heart rate records from Health Connect RestingHeartRateRecord"""
|
|
__tablename__ = "hc_raw_resting_hr"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
user_id = Column(Integer, ForeignKey("users.id"), index=True)
|
|
|
|
record_id = Column(String(100), index=True)
|
|
data_source = Column(String(200))
|
|
data_source_name = Column(String(100))
|
|
|
|
time = Column(DateTime, index=True)
|
|
synced_at = Column(DateTime, default=datetime.datetime.utcnow)
|
|
|
|
bpm = Column(Integer)
|
|
|
|
sync_id = Column(Integer, ForeignKey("health_connect_sync_logs.id"), nullable=True)
|
|
|
|
__table_args__ = (
|
|
UniqueConstraint('user_id', 'record_id', 'data_source', name='uq_resting_hr_record'),
|
|
Index('ix_resting_hr_user_date', 'user_id', 'time'),
|
|
)
|
|
|
|
|
|
# ==================== SLEEP ====================
|
|
|
|
class HCRawSleepSession(Base):
|
|
"""Sleep session records from Health Connect SleepSessionRecord"""
|
|
__tablename__ = "hc_raw_sleep_sessions"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
user_id = Column(Integer, ForeignKey("users.id"), index=True)
|
|
|
|
record_id = Column(String(100), index=True)
|
|
data_source = Column(String(200))
|
|
data_source_name = Column(String(100))
|
|
|
|
start_time = Column(DateTime, index=True)
|
|
end_time = Column(DateTime)
|
|
synced_at = Column(DateTime, default=datetime.datetime.utcnow)
|
|
|
|
title = Column(String(200), nullable=True)
|
|
notes = Column(Text, nullable=True)
|
|
|
|
sync_id = Column(Integer, ForeignKey("health_connect_sync_logs.id"), nullable=True)
|
|
|
|
# Relationship to stages
|
|
stages = relationship("HCRawSleepStage", back_populates="session", cascade="all, delete-orphan")
|
|
|
|
__table_args__ = (
|
|
UniqueConstraint('user_id', 'record_id', 'data_source', name='uq_sleep_record'),
|
|
Index('ix_sleep_user_date', 'user_id', 'start_time'),
|
|
)
|
|
|
|
|
|
class HCRawSleepStage(Base):
|
|
"""Sleep stages within a sleep session"""
|
|
__tablename__ = "hc_raw_sleep_stages"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
session_id = Column(Integer, ForeignKey("hc_raw_sleep_sessions.id", ondelete="CASCADE"), index=True)
|
|
|
|
start_time = Column(DateTime)
|
|
end_time = Column(DateTime)
|
|
stage_type = Column(Integer) # Health Connect stage type enum
|
|
stage_name = Column(String(50)) # Human readable: "awake", "light", "deep", "rem", etc.
|
|
|
|
session = relationship("HCRawSleepSession", back_populates="stages")
|
|
|
|
|
|
# Sleep stage type constants (from Health Connect)
|
|
SLEEP_STAGE_TYPES = {
|
|
0: "unknown",
|
|
1: "awake",
|
|
2: "sleeping",
|
|
3: "out_of_bed",
|
|
4: "light",
|
|
5: "deep",
|
|
6: "rem",
|
|
7: "awake_in_bed",
|
|
}
|
|
|
|
|
|
# ==================== DISTANCE ====================
|
|
|
|
class HCRawDistance(Base):
|
|
"""Distance records from Health Connect DistanceRecord"""
|
|
__tablename__ = "hc_raw_distance"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
user_id = Column(Integer, ForeignKey("users.id"), index=True)
|
|
|
|
record_id = Column(String(100), index=True)
|
|
data_source = Column(String(200))
|
|
data_source_name = Column(String(100))
|
|
|
|
start_time = Column(DateTime, index=True)
|
|
end_time = Column(DateTime)
|
|
synced_at = Column(DateTime, default=datetime.datetime.utcnow)
|
|
|
|
distance_meters = Column(Float)
|
|
|
|
sync_id = Column(Integer, ForeignKey("health_connect_sync_logs.id"), nullable=True)
|
|
|
|
__table_args__ = (
|
|
UniqueConstraint('user_id', 'record_id', 'data_source', name='uq_distance_record'),
|
|
Index('ix_distance_user_date', 'user_id', 'start_time'),
|
|
)
|
|
|
|
|
|
# ==================== CALORIES ====================
|
|
|
|
class HCRawCalories(Base):
|
|
"""Calories records from Health Connect (ActiveCaloriesBurned or TotalCaloriesBurned)"""
|
|
__tablename__ = "hc_raw_calories"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
user_id = Column(Integer, ForeignKey("users.id"), index=True)
|
|
|
|
record_id = Column(String(100), index=True)
|
|
data_source = Column(String(200))
|
|
data_source_name = Column(String(100))
|
|
|
|
start_time = Column(DateTime, index=True)
|
|
end_time = Column(DateTime)
|
|
synced_at = Column(DateTime, default=datetime.datetime.utcnow)
|
|
|
|
calories = Column(Float)
|
|
record_type = Column(String(20)) # "active" or "total"
|
|
|
|
sync_id = Column(Integer, ForeignKey("health_connect_sync_logs.id"), nullable=True)
|
|
|
|
__table_args__ = (
|
|
UniqueConstraint('user_id', 'record_id', 'data_source', name='uq_calories_record'),
|
|
Index('ix_calories_user_date', 'user_id', 'start_time'),
|
|
)
|
|
|
|
|
|
# ==================== OXYGEN SATURATION (SpO2) ====================
|
|
|
|
class HCRawOxygenSaturation(Base):
|
|
"""Oxygen saturation records from Health Connect OxygenSaturationRecord"""
|
|
__tablename__ = "hc_raw_spo2"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
user_id = Column(Integer, ForeignKey("users.id"), index=True)
|
|
|
|
record_id = Column(String(100), index=True)
|
|
data_source = Column(String(200))
|
|
data_source_name = Column(String(100))
|
|
|
|
time = Column(DateTime, index=True)
|
|
synced_at = Column(DateTime, default=datetime.datetime.utcnow)
|
|
|
|
percentage = Column(Float)
|
|
|
|
sync_id = Column(Integer, ForeignKey("health_connect_sync_logs.id"), nullable=True)
|
|
|
|
__table_args__ = (
|
|
UniqueConstraint('user_id', 'record_id', 'data_source', name='uq_spo2_record'),
|
|
Index('ix_spo2_user_date', 'user_id', 'time'),
|
|
)
|
|
|
|
|
|
# ==================== WEIGHT ====================
|
|
|
|
class HCRawWeight(Base):
|
|
"""Weight records from Health Connect WeightRecord"""
|
|
__tablename__ = "hc_raw_weight"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
user_id = Column(Integer, ForeignKey("users.id"), index=True)
|
|
|
|
record_id = Column(String(100), index=True)
|
|
data_source = Column(String(200))
|
|
data_source_name = Column(String(100))
|
|
|
|
time = Column(DateTime, index=True)
|
|
synced_at = Column(DateTime, default=datetime.datetime.utcnow)
|
|
|
|
weight_kg = Column(Float)
|
|
|
|
sync_id = Column(Integer, ForeignKey("health_connect_sync_logs.id"), nullable=True)
|
|
|
|
__table_args__ = (
|
|
UniqueConstraint('user_id', 'record_id', 'data_source', name='uq_weight_record'),
|
|
Index('ix_weight_user_date', 'user_id', 'time'),
|
|
)
|
|
|
|
|
|
# ==================== HEIGHT ====================
|
|
|
|
class HCRawHeight(Base):
|
|
"""Height records from Health Connect HeightRecord"""
|
|
__tablename__ = "hc_raw_height"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
user_id = Column(Integer, ForeignKey("users.id"), index=True)
|
|
|
|
record_id = Column(String(100), index=True)
|
|
data_source = Column(String(200))
|
|
data_source_name = Column(String(100))
|
|
|
|
time = Column(DateTime, index=True)
|
|
synced_at = Column(DateTime, default=datetime.datetime.utcnow)
|
|
|
|
height_meters = Column(Float)
|
|
|
|
sync_id = Column(Integer, ForeignKey("health_connect_sync_logs.id"), nullable=True)
|
|
|
|
__table_args__ = (
|
|
UniqueConstraint('user_id', 'record_id', 'data_source', name='uq_height_record'),
|
|
Index('ix_height_user_date', 'user_id', 'time'),
|
|
)
|
|
|
|
|
|
# ==================== BODY FAT ====================
|
|
|
|
class HCRawBodyFat(Base):
|
|
"""Body fat records from Health Connect BodyFatRecord"""
|
|
__tablename__ = "hc_raw_body_fat"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
user_id = Column(Integer, ForeignKey("users.id"), index=True)
|
|
|
|
record_id = Column(String(100), index=True)
|
|
data_source = Column(String(200))
|
|
data_source_name = Column(String(100))
|
|
|
|
time = Column(DateTime, index=True)
|
|
synced_at = Column(DateTime, default=datetime.datetime.utcnow)
|
|
|
|
percentage = Column(Float)
|
|
|
|
sync_id = Column(Integer, ForeignKey("health_connect_sync_logs.id"), nullable=True)
|
|
|
|
__table_args__ = (
|
|
UniqueConstraint('user_id', 'record_id', 'data_source', name='uq_body_fat_record'),
|
|
Index('ix_body_fat_user_date', 'user_id', 'time'),
|
|
)
|
|
|
|
|
|
# ==================== EXERCISE SESSIONS ====================
|
|
|
|
class HCRawExerciseSession(Base):
|
|
"""Exercise session records from Health Connect ExerciseSessionRecord"""
|
|
__tablename__ = "hc_raw_exercise_sessions"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
user_id = Column(Integer, ForeignKey("users.id"), index=True)
|
|
|
|
record_id = Column(String(100), index=True)
|
|
data_source = Column(String(200))
|
|
data_source_name = Column(String(100))
|
|
|
|
start_time = Column(DateTime, index=True)
|
|
end_time = Column(DateTime)
|
|
synced_at = Column(DateTime, default=datetime.datetime.utcnow)
|
|
|
|
# Exercise details
|
|
exercise_type = Column(Integer) # Health Connect ExerciseType enum
|
|
exercise_type_name = Column(String(100))
|
|
title = Column(String(200), nullable=True)
|
|
notes = Column(Text, nullable=True)
|
|
|
|
# Associated metrics (if available in the session)
|
|
distance_meters = Column(Float, nullable=True)
|
|
calories = Column(Float, nullable=True)
|
|
|
|
# Heart rate during exercise
|
|
avg_heart_rate = Column(Integer, nullable=True)
|
|
max_heart_rate = Column(Integer, nullable=True)
|
|
min_heart_rate = Column(Integer, nullable=True)
|
|
|
|
# Additional
|
|
steps = Column(Integer, nullable=True)
|
|
elevation_gained_meters = Column(Float, nullable=True)
|
|
|
|
sync_id = Column(Integer, ForeignKey("health_connect_sync_logs.id"), nullable=True)
|
|
|
|
__table_args__ = (
|
|
UniqueConstraint('user_id', 'record_id', 'data_source', name='uq_exercise_record'),
|
|
Index('ix_exercise_user_date', 'user_id', 'start_time'),
|
|
)
|
|
|
|
|
|
# ==================== NUTRITION ====================
|
|
|
|
class HCRawNutrition(Base):
|
|
"""Nutrition records from Health Connect NutritionRecord"""
|
|
__tablename__ = "hc_raw_nutrition"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
user_id = Column(Integer, ForeignKey("users.id"), index=True)
|
|
|
|
record_id = Column(String(100), index=True)
|
|
data_source = Column(String(200))
|
|
data_source_name = Column(String(100))
|
|
|
|
start_time = Column(DateTime, index=True)
|
|
end_time = Column(DateTime)
|
|
synced_at = Column(DateTime, default=datetime.datetime.utcnow)
|
|
|
|
# Basic nutrition info
|
|
name = Column(String(200), nullable=True)
|
|
meal_type = Column(Integer, nullable=True) # 0=unknown, 1=breakfast, 2=lunch, 3=dinner, 4=snack
|
|
|
|
# Macros
|
|
calories = Column(Float, nullable=True)
|
|
protein_grams = Column(Float, nullable=True)
|
|
carbohydrates_grams = Column(Float, nullable=True)
|
|
fat_grams = Column(Float, nullable=True)
|
|
fiber_grams = Column(Float, nullable=True)
|
|
sugar_grams = Column(Float, nullable=True)
|
|
|
|
sync_id = Column(Integer, ForeignKey("health_connect_sync_logs.id"), nullable=True)
|
|
|
|
__table_args__ = (
|
|
UniqueConstraint('user_id', 'record_id', 'data_source', name='uq_nutrition_record'),
|
|
Index('ix_nutrition_user_date', 'user_id', 'start_time'),
|
|
)
|
|
|
|
|
|
# ==================== HYDRATION ====================
|
|
|
|
class HCRawHydration(Base):
|
|
"""Hydration records from Health Connect HydrationRecord"""
|
|
__tablename__ = "hc_raw_hydration"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
user_id = Column(Integer, ForeignKey("users.id"), index=True)
|
|
|
|
record_id = Column(String(100), index=True)
|
|
data_source = Column(String(200))
|
|
data_source_name = Column(String(100))
|
|
|
|
start_time = Column(DateTime, index=True)
|
|
end_time = Column(DateTime)
|
|
synced_at = Column(DateTime, default=datetime.datetime.utcnow)
|
|
|
|
volume_liters = Column(Float)
|
|
|
|
sync_id = Column(Integer, ForeignKey("health_connect_sync_logs.id"), nullable=True)
|
|
|
|
__table_args__ = (
|
|
UniqueConstraint('user_id', 'record_id', 'data_source', name='uq_hydration_record'),
|
|
Index('ix_hydration_user_date', 'user_id', 'start_time'),
|
|
)
|
|
|
|
|
|
# ==================== HEART RATE VARIABILITY ====================
|
|
|
|
class HCRawHeartRateVariabilityRmssd(Base):
|
|
"""HRV records from Health Connect HeartRateVariabilityRmssdRecord"""
|
|
__tablename__ = "hc_raw_hrv_rmssd"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
user_id = Column(Integer, ForeignKey("users.id"), index=True)
|
|
|
|
record_id = Column(String(100), index=True)
|
|
data_source = Column(String(200))
|
|
data_source_name = Column(String(100))
|
|
|
|
time = Column(DateTime, index=True)
|
|
synced_at = Column(DateTime, default=datetime.datetime.utcnow)
|
|
|
|
rmssd = Column(Float)
|
|
|
|
sync_id = Column(Integer, ForeignKey("health_connect_sync_logs.id"), nullable=True)
|
|
|
|
__table_args__ = (
|
|
UniqueConstraint('user_id', 'record_id', 'data_source', name='uq_hrv_rmssd_record'),
|
|
Index('ix_hrv_rmssd_user_date', 'user_id', 'time'),
|
|
)
|