Skip to main content

Automated Garmin Connect Telemetry Ingestion Pipeline Documentation

📄 Overview​

This document details the architecture and operational workflow of the automated pipeline responsible for ingesting daily biometric telemetry data from Garmin Connect into the central sleeper.biometrics table within Supabase Postgres.

The primary goal is to ensure reliable, scheduled synchronization of user health metrics (e.g., sleep stages, heart rate variability, activity logs) while maintaining data integrity through robust deduplication and upsert logic.

🚀 Architecture Diagram (Conceptual Flow)​

graph TD
    A[Garmin Connect API] -->|OAuth Session Token| B(Local Execution Environment);
    B --> C{garmin_sync.py};
    C --> D{sync_health_supabase.py};
    D --> E[Supabase Postgres (sleeper.biometrics)];
    F[Hermes Agent Cron Job] --> B;

    subgraph Pipeline Components
        C
        D
    end

⚙️ Workflow Details: The Two-Step Process​

The ingestion process is split into two distinct, sequential scripts to ensure separation of concerns: data retrieval and data persistence.

Step 1: Data Retrieval (garmin_sync.py)​

This script is responsible for authenticating with the Garmin Connect API, fetching raw telemetry data, and structuring it into a standardized format (e.g., JSON or Pandas DataFrame) ready for database insertion.

Key Functions:

  1. Authentication Check: Verifies the existence and validity of the session token (~/.garminconnect).
  2. API Call: Executes necessary API calls to retrieve data based on defined endpoints (e.g., sleep, activity).
  3. Data Structuring: Normalizes disparate Garmin data fields into a consistent schema required by sleeper.biometrics.
  4. Output: Writes the structured dataset to a temporary staging file or passes it directly as an object/list to the next script (sync_health_supabase.py).

Step 2: Data Persistence (sync_health_supabase.py)​

This script handles the secure connection to Supabase, processes the staged data, and executes the final database write operation.

Key Functions:

  1. Input Reading: Reads the structured dataset generated by garmin_sync.py.
  2. Data Validation: Performs final checks on required fields (e.g., presence of a unique identifier like user_id and timestamp).
  3. Database Connection: Establishes a secure connection to the Supabase Postgres instance.
  4. Upsert Logic: Executes the bulk insertion using an UPSERT mechanism, ensuring that existing records are updated rather than duplicated.

🔑 Authentication Mechanism: Session Tokens​

The pipeline relies on OAuth session tokens for seamless and automated API access without requiring manual credential input during runtime.

Token Storage Location​

  • File Path: ~/.garminconnect (or specified environment variable path).
  • Format: The file stores the necessary credentials or a refresh token/session key required by the Garmin Connect SDK to generate an active API session.

Security and Refresh Cycle​

  1. Initial Setup: Tokens are acquired manually or via a dedicated setup script, following standard OAuth 2.0 flows.
  2. Token Expiration: The garmin_sync.py script includes logic to detect token expiration (e.g., HTTP 401 Unauthorized).
  3. Re-authentication: Upon detection of an expired token, the script attempts a refresh using stored refresh tokens or triggers an alert for manual re-authorization if necessary.

⏰ Scheduling and Execution: Hermes Agent Cron Job​

The entire pipeline is orchestrated by the Hermes Agent, which manages scheduled execution via a cron job. This ensures that data ingestion occurs reliably at a predictable interval, minimizing latency between data generation and database availability.

Schedule Details​

  • Service: Hermes Agent Scheduler
  • Job Name: garmin_telemetry_sync
  • Frequency: Daily
  • Time: 03:00 AM UTC (Recommended time to minimize impact on active user usage or API rate limits).

Execution Flow in Hermes Agent​

  1. The cron job triggers the execution of a wrapper script (run_garmin_pipeline.sh).
  2. This wrapper script sets necessary environment variables (e.g., SUPABASE_KEY, GARMIN_TOKEN_PATH).
  3. It executes the pipeline sequentially: 
    # 1. Retrieve data and stage it
    python /path/to/garmin_sync.py

    # 2. Process staged data and upsert to Supabase
    python /path/to/sync_health_supabase.py
  4. Logging: All execution steps, successes, failures, and API rate limit warnings are logged to the Hermes Agent logging system for auditing and debugging.

💾 Data Integrity: Deduplication and Upsert Logic​

The most critical aspect of the pipeline is maintaining data integrity within the sleeper.biometrics table. We utilize a robust UPSERT mechanism to handle potential re-runs or partial syncs without corrupting historical records.

Target Table​

  • Database: Supabase Postgres
  • Table: sleeper.biometrics

Primary Key / Unique Constraint​

The deduplication logic relies on a composite unique constraint defined on the following fields:

Field NameData TypeDescriptionRole in Deduplication
user_idUUID/TextIdentifier for the user.Required
metric_typeTexte.g., 'sleep', 'hrv', 'activity'.Required
start_timestampTimestampTZThe start time of the recorded metric period.Required

Upsert Mechanism (ON CONFLICT)​

The sync_health_supabase.py script executes an SQL statement utilizing PostgreSQL's INSERT ... ON CONFLICT DO UPDATE.

Logic Flow:

  1. Attempt to insert a new record using the unique combination of (user_id, metric_type, start_timestamp).
  2. **If Conflict