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. Here’s a complete reference.

Two Tables

health4ai uses a two-tier storage architecture:

• healthkit_metrics — raw HealthKit samples, retained for 30 days
• healthkit_daily_summaries — pre-aggregated daily data for all historical data

Beyond 30 days, raw samples are no longer individually stored — the daily summary captures the aggregate. This keeps the database size manageable while preserving the analytical signal.

healthkit_metrics

CREATE TABLE healthkit_metrics (
    id            BIGSERIAL PRIMARY KEY,
    user_id       TEXT NOT NULL,
    metric_type   TEXT NOT NULL,
    value         DOUBLE PRECISION,
    unit          TEXT,
    started_at    TIMESTAMPTZ NOT NULL,
    ended_at      TIMESTAMPTZ,
    source_name   TEXT,
    source_device TEXT,
    metadata      JSONB,
    created_at    TIMESTAMPTZ DEFAULT NOW()
);

-- Primary query index
CREATE INDEX idx_healthkit_metrics_user_type_time 
  ON healthkit_metrics (user_id, metric_type, started_at DESC);

-- Source filtering (used for sleep deduplication)
CREATE INDEX idx_healthkit_metrics_source 
  ON healthkit_metrics (user_id, metric_type, source_device);

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

CREATE TABLE healthkit_daily_summaries (
    id            BIGSERIAL PRIMARY KEY,
    user_id       TEXT NOT NULL,
    metric_type   TEXT NOT NULL,
    date          DATE NOT NULL,
    avg_value     DOUBLE PRECISION,
    min_value     DOUBLE PRECISION,
    max_value     DOUBLE PRECISION,
    sum_value     DOUBLE PRECISION,
    sample_count  INTEGER,
    UNIQUE (user_id, metric_type, date)
);

CREATE INDEX idx_healthkit_daily_summaries_user_type_date
  ON healthkit_daily_summaries (user_id, metric_type, date DESC);

Columns:

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_id'
GROUP BY metric_type
ORDER BY rows DESC;

Monthly HRV averages (from daily summaries):

SELECT DATE_TRUNC('month', date) as month,
       ROUND(AVG(avg_value)::numeric, 1) as avg_hrv_ms,
       COUNT(*) as days_with_data
FROM healthkit_daily_summaries
WHERE user_id = 'your_user_id'
  AND metric_type = 'HKQuantityTypeIdentifierHeartRateVariabilitySDNN'
GROUP BY month
ORDER BY month 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_id'
  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_id'
  AND metric_type = 'HKWorkoutTypeIdentifier'
  AND started_at >= NOW() - INTERVAL '30 days'
ORDER BY started_at DESC;

health4ai: Free while in early access.
Download on the App Store →