FitTrack_GarminSync/data-model.md

Data Model for GarminSync Service

This document outlines the key entities, their attributes, relationships, and validation rules for the GarminSync service, primarily focusing on data stored in CentralDB.

1. User

Represents the single authenticated individual utilizing the GarminSync service.

• Attributes:
  • id: Unique identifier (Primary Key, UUID)
  • username: User's chosen username (String, Unique, Required)
  • password_hash: Hashed password for authentication (String, Required)
  • created_at: Timestamp of user creation (Datetime, Auto-generated)
  • updated_at: Timestamp of last update (Datetime, Auto-generated)
• Relationships:
  • One-to-One with Garmin Connect Account
  • One-to-Many with Activity (indirectly through Garmin Connect Account)
  • One-to-Many with Health Metric (indirectly through Garmin Connect Account)
  • One-to-Many with Workout
  • One-to-Many with Sync Job
• Validation Rules:
  • username must be unique.
  • password_hash must be present.

2. Garmin Connect Account

Represents the user's linked Garmin account, storing authentication credentials.

• Attributes:
  • id: Unique identifier (Primary Key, UUID)
  • user_id: Foreign Key to User.id (UUID, Required)
  • garmin_user_id: Unique identifier from Garmin Connect (String, Required)
  • oauth_token: OAuth2 access token (String, Encrypted, Required)
  • oauth_token_secret: OAuth2 token secret (String, Encrypted, Required)
  • refresh_token: OAuth2 refresh token (String, Encrypted)
  • token_expires_at: Timestamp of token expiration (Datetime)
  • linked_at: Timestamp of account linking (Datetime, Auto-generated)
  • updated_at: Timestamp of last update (Datetime, Auto-generated)
• Relationships:
  • Many-to-One with User
  • One-to-Many with Activity
  • One-to-Many with Health Metric
• Validation Rules:
  • user_id must reference an existing User.
  • oauth_token and oauth_token_secret must be present for active accounts.

3. Activity

A record of a fitness activity obtained from Garmin Connect, encompassing raw data files and metadata.

• Attributes:
  • id: Unique identifier (Primary Key, UUID)
  • garmin_connect_account_id: Foreign Key to Garmin Connect Account.id (UUID, Required)
  • garmin_activity_id: Unique identifier from Garmin Connect (String, Unique, Required)
  • activity_type: Type of activity (e.g., 'running', 'cycling') (String, Required)
  • start_time: Activity start time (Datetime, Required)
  • duration: Activity duration in seconds (Integer, Required)
  • distance: Activity distance in meters (Float)
  • calories: Calories burned (Float)
  • file_type: Original file type (FIT, GPX, TCX) (String, Required)
  • file_path: Path to the stored activity file in CentralDB (String, Required)
  • metadata: JSON blob for additional metadata (JSONB)
  • synced_at: Timestamp of last successful sync (Datetime, Auto-generated)
  • created_at: Timestamp of record creation (Datetime, Auto-generated)
• Relationships:
  • Many-to-One with Garmin Connect Account
• Validation Rules:
  • garmin_activity_id must be unique per Garmin Connect Account.
  • file_type must be one of FIT, GPX, or TCX.
  • file_path must be a valid path.

4. Health Metric

A data point representing a user's health measurement synchronized from Garmin Connect.

• Attributes:
  • id: Unique identifier (Primary Key, UUID)
  • garmin_connect_account_id: Foreign Key to Garmin Connect Account.id (UUID, Required)
  • metric_type: Type of health metric (e.g., 'heart_rate', 'sleep', 'body_composition') (String, Required)
  • timestamp: Timestamp of the metric reading (Datetime, Required)
  • value: The measured value (Float, Required)
  • unit: Unit of measurement (String)
  • metadata: JSON blob for additional metadata (JSONB)
  • synced_at: Timestamp of last successful sync (Datetime, Auto-generated)
  • created_at: Timestamp of record creation (Datetime, Auto-generated)
• Relationships:
  • Many-to-One with Garmin Connect Account
• Validation Rules:
  • Combination of garmin_connect_account_id, metric_type, and timestamp should be unique.

5. Workout

A structured training plan or session that can be uploaded to Garmin Connect.

• Attributes:
  • id: Unique identifier (Primary Key, UUID)
  • user_id: Foreign Key to User.id (UUID, Required)
  • name: Name of the workout (String, Required)
  • description: Description of the workout (String)
  • workout_definition: JSON representation of the workout structure (JSONB, Required)
  • created_at: Timestamp of workout creation (Datetime, Auto-generated)
  • updated_at: Timestamp of last update (Datetime, Auto-generated)
  • uploaded_to_garmin_at: Timestamp of last successful upload to Garmin (Datetime)
• Relationships:
  • Many-to-One with User
• Validation Rules:
  • workout_definition must conform to a predefined schema for Garmin Connect workouts.

6. Sync Job

An instance of a synchronization operation, tracking its status, progress, and any associated errors.

• Attributes:
  • id: Unique identifier (Primary Key, UUID)
  • user_id: Foreign Key to User.id (UUID, Required)
  • job_type: Type of synchronization (e.g., 'activity_sync', 'health_metrics_sync', 'workout_upload') (String, Required)
  • status: Current status ('pending', 'in_progress', 'completed', 'failed') (String, Required)
  • started_at: Timestamp when the job started (Datetime)
  • completed_at: Timestamp when the job completed (Datetime)
  • progress: Current progress (Float, 0.0-1.0)
  • error_message: Details of any error encountered (Text)
  • metadata: JSON blob for job-specific data (JSONB)
  • created_at: Timestamp of job creation (Datetime, Auto-generated)
  • updated_at: Timestamp of last update (Datetime, Auto-generated)
• Relationships:
  • Many-to-One with User
• Validation Rules:
  • job_type must be one of the defined synchronization types.
  • status must be one of the defined statuses.
  • progress must be between 0.0 and 1.0.