Apple Health Data Schema: What's in Your Database

Understanding the health4ai database schema lets you write custom SQL queries, build dashboards directly on the database, and debug sync issues. The published schema is the authoritative definition for a new Supabase project.

Two Tables

health4ai uses a two-tier storage architecture:

• healthkit_metrics — raw HealthKit samples, including recent and selected older samples
• healthkit_daily_summaries — daily aggregates for older quantity samples where summarization has run

The summarizer can aggregate older quantity samples into daily rows. Some older samples remain raw: category and workout records retain detail, merged activity types retain raw rows for safe recomputation, and other quantity types remain raw until their history can be compacted without double counting. Historical queries use a daily summary where available and aggregate remaining raw rows in Postgres.

healthkit_metrics

The raw table stores a UUID user_id linked to Supabase Auth. Its unique sample key is (user_id, metric_type, source_device, started_at), so an upload of the same sample does not create another row. synced_at records when the row reached the database. See the published schema for the current columns, constraints, and indexes.

Columns:

Workout records store their detail in the metadata JSONB column:

{
  "workout_type": "Running",
  "duration_seconds": 2340.0,
  "total_distance_meters": 5820.0,
  "total_energy_burned_cal": 387.0
}

Sleep records use integer values for stage codes:

• 0 = InBed
• 2 = Awake
• 3 = AsleepCore
• 4 = AsleepDeep
• 5 = AsleepREM

healthkit_daily_summaries

The daily table holds averages, minimums, maximums, sums, and sample counts by user, metric type, calendar date, and unit. The new-project schema calls the date column summary_date; a legacy deployment may use date. Day boundaries follow the project’s configured time zone. A fresh self-hosted project does not run the optional historical summarizer, so this table can be empty while historical queries still work from raw samples.

Common HealthKit Type Identifiers

Activity

Vitals

Body

Sleep and Workouts

Example Queries

All metric types in your database:

SELECT metric_type, COUNT(*) as rows,
       MIN(started_at)::date as earliest,
       MAX(started_at)::date as latest
FROM healthkit_metrics
WHERE user_id = 'your_user_uuid'
GROUP BY metric_type
ORDER BY rows DESC;

Monthly HRV averages (works on a fresh install with raw history):

-- Replace UTC with your HEALTH4AI_TZ setting if you changed it.
SELECT DATE_TRUNC('month', started_at AT TIME ZONE 'UTC') AS month,
       ROUND(AVG(value)::numeric, 1) AS avg_hrv_ms,
       COUNT(*) AS samples
FROM healthkit_metrics
WHERE user_id = 'your_user_uuid'
  AND metric_type = 'HKQuantityTypeIdentifierHeartRateVariabilitySDNN'
GROUP BY 1
ORDER BY 1 DESC;

Recent sleep stage records:

SELECT started_at, ended_at, value,
       ROUND((EXTRACT(EPOCH FROM (ended_at - started_at)) / 60)::numeric, 1) as duration_min,
       CASE value::int
         WHEN 3 THEN 'Core'
         WHEN 4 THEN 'Deep'
         WHEN 5 THEN 'REM'
         WHEN 2 THEN 'Awake'
       END as stage
FROM healthkit_metrics
WHERE user_id = 'your_user_uuid'
  AND metric_type = 'HKCategoryTypeIdentifierSleepAnalysis'
  AND value IN (2, 3, 4, 5)
  AND started_at >= NOW() - INTERVAL '7 days'
ORDER BY started_at DESC;

Workout list with metadata:

SELECT started_at::date as date,
       metadata->>'workout_type' as type,
       ROUND((metadata->>'duration_seconds')::numeric / 60, 0) as duration_min,
       ROUND((metadata->>'total_distance_meters')::numeric / 1000, 2) as distance_km,
       ROUND((metadata->>'total_energy_burned_cal')::numeric) as calories
FROM healthkit_metrics
WHERE user_id = 'your_user_uuid'
  AND metric_type = 'HKWorkoutTypeIdentifier'
  AND started_at >= NOW() - INTERVAL '30 days'
ORDER BY started_at DESC;

Get health4ai on the App Store →