Healthsync

Important Constraints

• READ ONLY — You must NEVER write to the database. No INSERT, UPDATE, DELETE, DROP, ALTER, or any write operations.
• Two query methods: CLI (healthsync query) or direct SQLite (sqlite3 ~/.healthsync/healthsync.db)
• Prefer CLI for simple queries. Use direct SQLite for complex aggregations, joins, or custom SQL.

Database Location

Default: ~/.healthsync/healthsync.db

Quick Start

# Recent heart rate readings
healthsync query heart-rate --limit 10

# Steps in a date range
healthsync query steps --from 2024-01-01 --to 2024-06-30 --limit 100

# Deduplicated daily step totals
healthsync query steps --total --from 2024-01-01

# Deduplicated daily active energy totals
healthsync query active-energy --total --from 2024-01-01

# Workouts as JSON
healthsync query workouts --format json --limit 20

# Sleep data as CSV
healthsync query sleep --format csv --limit 50

# Resting heart rate trend
healthsync query resting-heart-rate --limit 30

# HRV readings
healthsync query hrv --limit 30

# Blood pressure
healthsync query blood-pressure --limit 20

# Body weight trend
healthsync query body-mass --limit 30

# Direct SQLite for aggregations
sqlite3 ~/.healthsync/healthsync.db "SELECT date(start_date) as day, SUM(value) as total_steps FROM steps GROUP BY day ORDER BY day DESC LIMIT 7"

# Average resting heart rate per week
sqlite3 ~/.healthsync/healthsync.db "SELECT strftime('%Y-W%W', start_date) as week, ROUND(AVG(value),1) as avg_rhr FROM resting_heart_rate GROUP BY week ORDER BY week DESC LIMIT 12"

CLI Reference

healthsync query <table>

Available Tables

Cardiac

Activity / Energy

Body

Mobility / Walking

Running

Other

healthsync parse <file>

Parse an Apple Health export into the database. (Informational — do not run unless the user asks.)

healthsync server

Start HTTP server for receiving uploads. (Informational — do not start unless the user asks.)

Endpoints:

• POST /api/upload — Upload .zip or .xml (multipart form, field: file). Returns 202, parses async.
• GET /api/upload/status — Poll parse progress.
• GET /api/health/{table}?from=&to=&limit= — Query data as JSON.

Database Schema

Standard quantity tables

Schema: id, source_name, start_date, end_date, value REAL, unit TEXT, created_at

Applies to all tables except blood_pressure, sleep, mindful_sessions, stand_hours, and workouts.

CREATE TABLE resting_heart_rate (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    source_name TEXT NOT NULL,
    start_date  TEXT NOT NULL,
    end_date    TEXT NOT NULL,
    value       REAL NOT NULL,
    unit        TEXT NOT NULL,
    created_at  TEXT DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(source_name, start_date, end_date, value)
);

blood_pressure (special — paired systolic + diastolic)

CREATE TABLE blood_pressure (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    source_name TEXT NOT NULL,
    start_date  TEXT NOT NULL,
    end_date    TEXT NOT NULL,
    systolic    REAL NOT NULL,   -- mmHg
    diastolic   REAL NOT NULL,   -- mmHg
    unit        TEXT NOT NULL,   -- "mmHg"
    created_at  TEXT DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(source_name, start_date, end_date, systolic, diastolic)
);

Category tables — no unit column

Applies to: sleep, mindful_sessions, stand_hours

CREATE TABLE sleep (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    source_name TEXT NOT NULL,
    start_date  TEXT NOT NULL,
    end_date    TEXT NOT NULL,
    value       TEXT NOT NULL,   -- e.g. HKCategoryValueSleepAnalysisAsleepCore
    created_at  TEXT DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(source_name, start_date, end_date, value)
);

workouts

CREATE TABLE workouts (
    id                       INTEGER PRIMARY KEY AUTOINCREMENT,
    activity_type            TEXT NOT NULL,
    source_name              TEXT NOT NULL,
    start_date               TEXT NOT NULL,
    end_date                 TEXT NOT NULL,
    duration                 REAL,
    duration_unit            TEXT,
    total_distance           REAL,
    total_distance_unit      TEXT,
    total_energy_burned      REAL,
    total_energy_burned_unit TEXT,
    created_at               TEXT DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(activity_type, start_date, end_date, source_name)
);

Date Format

All dates stored as text: 2024-01-15 08:30:00 +0530. Filter with date prefix — 2024-01-01 works via SQLite string comparison.

Sleep Stage Values

Common Query Patterns

Daily step totals (deduped)

healthsync query steps --total --from 2024-01-01

Daily active energy totals (deduped)

healthsync query active-energy --total --from 2024-01-01

Average resting heart rate per week

SELECT strftime('%Y-W%W', start_date) as week,
  ROUND(AVG(value), 1) as avg_rhr
FROM resting_heart_rate
GROUP BY week ORDER BY week DESC LIMIT 12;

HRV trend

SELECT date(start_date) as day, ROUND(AVG(value), 1) as hrv_ms
FROM hrv
GROUP BY day ORDER BY day DESC LIMIT 30;

Blood pressure history

SELECT date(start_date) as day,
  ROUND(AVG(systolic), 1) as avg_sys,
  ROUND(AVG(diastolic), 1) as avg_dia
FROM blood_pressure
GROUP BY day ORDER BY day DESC LIMIT 30;

Body weight trend

SELECT date(start_date) as day, value as kg
FROM body_mass
ORDER BY day DESC LIMIT 30;

Sleep duration per night

SELECT date(start_date) as night,
  ROUND(SUM((julianday(end_date) - julianday(start_date)) * 24), 1) as hours
FROM sleep
WHERE value LIKE '%Asleep%'
GROUP BY night ORDER BY night DESC LIMIT 14;

Average heart rate per day

SELECT date(start_date) as day,
  ROUND(AVG(value), 1) as avg_hr,
  MIN(value) as min_hr,
  MAX(value) as max_hr
FROM heart_rate
GROUP BY day ORDER BY day DESC LIMIT 30;

Workout summary

SELECT activity_type, COUNT(*) as count,
  ROUND(AVG(duration), 1) as avg_min,
  ROUND(SUM(total_energy_burned)) as total_kcal
FROM workouts
GROUP BY activity_type ORDER BY count DESC;

Weekly VO2 Max trend

SELECT strftime('%Y-W%W', start_date) as week,
  ROUND(AVG(value), 2) as avg_vo2
FROM vo2_max
GROUP BY week ORDER BY week DESC LIMIT 12;

Mindfulness minutes per week

SELECT strftime('%Y-W%W', start_date) as week,
  ROUND(SUM((julianday(end_date) - julianday(start_date)) * 1440), 0) as minutes
FROM mindful_sessions
GROUP BY week ORDER BY week DESC LIMIT 12;

Limitations

• Read-only — This skill must never write to the database
• No real-time data — Data is only as fresh as the last healthsync parse run
• Date filtering is string-based — Timezone offsets are part of the stored date string
• SpO2 values are fractions — 0.98 means 98%, not 98
• Blood pressure is paired — systolic and diastolic are stored together in one row per measurement
• Category tables have no unit column — sleep, mindful_sessions, stand_hours store text values, not numeric