Data model

Why time series

We use a time series table instead of daily snapshots. The Shortcut runs once a day in v1, but a single day's payload has many metrics, each with its own timestamp (a workout at 08:00, sleep at 23:42). A snapshot table would force us to flatten all of that and lose detail, which would make future components a lot harder to build.

Schema

create table if not exists wristkit_samples (
  id           bigserial primary key,
  user_id      uuid,
  metric       text not null,
  value        numeric not null,
  unit         text not null,
  recorded_at  timestamptz not null,
  source       text,
  ingested_at  timestamptz not null default now()
);

user_id is nullable in v1, so we can add multi user support later without a schema migration.

recorded_at is the Apple Health timestamp, the moment the data was recorded on the device, not when it was synced.

source is a free form string. The Shortcut sends apple_watch or iphone.

Indexes

create index if not exists idx_metric_recorded
  on wristkit_samples (metric, recorded_at desc);
 
create index if not exists idx_user_metric_recorded
  on wristkit_samples (user_id, metric, recorded_at desc);
 
create unique index if not exists uq_sample_dedupe
  on wristkit_samples (user_id, metric, recorded_at)
  nulls not distinct;

The first two back the most common query pattern: WHERE metric = $1 AND recorded_at >= $2 ORDER BY recorded_at DESC LIMIT 1.

The unique index protects against duplicate samples when the Shortcut retries. NULLS NOT DISTINCT treats two NULL user_id rows as equal, which matches v1 single user mode.

Metrics in v1

These are the three metrics TodayActivityCard needs. New components in v2 will add more metric types, and the schema does not have to change.

Query helpers

lib/wristkit/queries.ts exports typed helpers:

export async function getTodayActivity(url: string, tz: string): Promise<{
  kcal: number | null;
  exerciseMinutes: number | null;
  steps: number | null;
  lastSync: Date | null;
}>;

Components call loadTodayActivity({ tz }) (which calls getTodayActivity() for you) and never query the database directly. lastSync is the recorded_at of the most recent sample, so a backfill of yesterday's data still reads as stale.