Database Schema
Health Observability uses Cloudflare D1 — a serverless SQLite-compatible database. The schema is managed through eight ordered migration files in migrations/.
Entity Relationship Diagram
Tables
metric_types
Seed table defining the five supported metric types. Populated once at migration time.
| Column | Type | Notes |
|---|---|---|
id | INTEGER | Auto-increment primary key |
name | TEXT | Unique; one of systolic, diastolic, pulse, pulse_pressure, mean_arterial_pressure |
unit | TEXT | e.g. mmHg |
category | TEXT | Grouping label |
description | TEXT | Human-readable description |
measurement_sessions
One row per blood pressure measurement event. A single session captures three readings taken in sequence.
| Column | Type | Notes |
|---|---|---|
id | TEXT | UUID v4 primary key |
recorded_at | DATETIME | Auto-set to CURRENT_TIMESTAMP on insert |
context | TEXT | Optional free-text context (e.g. “morning”, “post-exercise”) |
notes | TEXT | Optional additional notes |
observations
One row per individual metric value per reading per session. A typical session with 3 readings produces 9 observation rows (3 readings × 3 metrics: systolic, diastolic, pulse) plus 2 more for the best reading’s derived metrics (pulse_pressure, MAP) = 11 rows total.
| Column | Type | Notes |
|---|---|---|
id | TEXT | UUID v4 primary key |
session_id | TEXT | FK → measurement_sessions.id |
metric_id | INTEGER | FK → metric_types.id |
value | REAL | Measured or computed value |
reading_number | INTEGER | 1, 2, or 3 — which reading within the session |
is_best | BOOLEAN | 1 for the best-selected reading; 0 otherwise |
created_at | DATETIME | Auto-set on insert |
tags
Lookup table of user-defined session labels.
| Column | Type | Notes |
|---|---|---|
id | INTEGER | Auto-increment primary key |
name | TEXT | Unique tag name (e.g. “post-exercise”, “stressed”) |
category | TEXT | Optional grouping |
session_tags
Many-to-many junction between sessions and tags.
| Column | Type | Notes |
|---|---|---|
session_id | TEXT | FK → measurement_sessions.id, part of composite PK |
tag_id | INTEGER | FK → tags.id, part of composite PK |
profile
Singleton user record. Enforced as a single row via CHECK (id = 1) and seeded by a migration.
| Column | Type | Notes |
|---|---|---|
id | INTEGER | Always 1; constraint CHECK (id = 1) |
height_cm | REAL | User’s height in centimetres; nullable |
updated_at | DATETIME | Updated on every PUT /api/profile |
weight_entries
Date-stamped weight measurements.
| Column | Type | Notes |
|---|---|---|
id | TEXT | UUID v4 primary key |
weight_kg | REAL | Weight in kilograms (validated: 20–300) |
notes | TEXT | Optional free-text |
recorded_at | DATETIME | Auto-set on insert |
cpap_data
One row per sleep night, sourced from ResMed MyAir via the external sync worker.
| Column | Type | Notes |
|---|---|---|
log_date | TEXT | YYYY-MM-DD primary key |
ahi | REAL | Apnea-Hypopnea Index (events/hour) |
usage_minutes | INTEGER | Total mask-on time in minutes |
mask_leak_pct | REAL | Large leak percentage |
myair_score | INTEGER | ResMed proprietary score 0–100 |
pressure_min | REAL | Minimum CPAP pressure (cmH₂O) |
pressure_max | REAL | Maximum CPAP pressure (cmH₂O) |
synced_at | DATETIME | Timestamp of last sync from MyAir |
Indexes
| Index | Table | Columns | Purpose |
|---|---|---|---|
idx_sessions_recorded_at | measurement_sessions | recorded_at DESC | Chronological paging of sessions |
idx_obs_session_id | observations | session_id | Observation lookup by session |
idx_obs_metric_id | observations | metric_id | Metric-type filtering |
idx_obs_is_best | observations | is_best | Best-reading lookup |
idx_weight_recorded_at | weight_entries | recorded_at DESC | Chronological paging of weight |
idx_cpap_log_date | cpap_data | log_date DESC | Chronological paging of CPAP |
Migration History
Migrations are applied via wrangler d1 migrations apply health_observability. They must be run in order; Wrangler tracks applied migrations automatically.
Migration 01 — metric_types
Creates the metric type registry. Seed data for the five metrics (systolic, diastolic, pulse, pulse_pressure, mean_arterial_pressure) is inserted separately.
Migration 02 — measurement_sessions
Creates the blood pressure session container table.
Migration 03 — observations
Creates the per-metric per-reading value store with foreign keys to sessions and metric types.
Migration 04 — tags
Creates the tag label registry.
Migration 05 — session_tags
Creates the session–tag junction table with a composite primary key.
Migration 06 — Indexes
Adds performance indexes on measurement_sessions(recorded_at DESC), observations(session_id), observations(metric_id), and observations(is_best).
Migration 07 — Profile & Weight
Adds the singleton profile table and the weight_entries table, including the idx_weight_recorded_at index and a seed INSERT OR IGNORE for the profile row.
Migration 08 — CPAP Data
Adds the cpap_data table and idx_cpap_log_date index to support the ResMed MyAir integration.
Key Query Patterns
Fetch paginated blood pressure sessions
SELECT s.id, s.recorded_at, s.context, s.notes,
mt.name AS metric, o.value, o.reading_number, o.is_best
FROM measurement_sessions s
JOIN observations o ON o.session_id = s.id
JOIN metric_types mt ON mt.id = o.metric_id
ORDER BY s.recorded_at DESC
LIMIT ? OFFSET ?
The Worker fetches limit * 10 rows to account for the multiple observation rows per session, then slices to limit sessions in application code.
Delete a blood pressure session (atomic batch)
DELETE FROM session_tags WHERE session_id = ?;
DELETE FROM observations WHERE session_id = ?;
DELETE FROM measurement_sessions WHERE id = ?;
All three statements are executed as a single D1.batch([]) call to maintain referential integrity.
Insert a blood pressure session (batch)
The Worker builds an array of prepared statements:
INSERT INTO measurement_sessions- For each of the 3 readings × 3 metrics →
INSERT INTO observations - For the best reading’s 2 derived metrics →
INSERT INTO observations - For each tag →
INSERT INTO session_tags
The entire array is committed atomically via env.DB.batch(stmts).