flowsmithonline

The schema — one table per domain

Everything downstream inherits the shape you choose here. Get the tables right and the API almost writes itself; get them wrong and every query starts with an apology. This is the first Members' lesson and it is deliberately the least glamorous one.

The decision: many small tables, not one big one

The tempting design is a single events table with a type column and a JSON payload. It feels flexible. It is a trap:

The working design has a table per domain — measurements, meals, workouts, sleep sessions, notes, plus the plumbing tables for OAuth and logging. A weight and a sleep session share almost nothing, so stop pretending they do.

A worked example

CREATE TABLE measurements (
  id            INTEGER PRIMARY KEY AUTOINCREMENT,
  taken_utc     TEXT NOT NULL,          -- always UTC, always ISO-8601
  day           TEXT NOT NULL,          -- 'YYYY-MM-DD', derived, indexed
  kind          TEXT NOT NULL,          -- weight | waist | systolic | resting_hr | vo2max
  value         REAL NOT NULL,
  unit          TEXT NOT NULL,
  source        TEXT NOT NULL,          -- scale | watch | manual | import:apple
  source_key    TEXT,                   -- id from the source system, if any
  created_utc   TEXT NOT NULL
);
CREATE INDEX  idx_meas_day  ON measurements (day, kind);
CREATE UNIQUE INDEX idx_meas_dedup ON measurements (source, source_key)
  WHERE source_key IS NOT NULL;

Four decisions are hiding in there, and each one is load-bearing.

1 · day exists even though taken_utc contains it

Deliberate duplication. You will group by day constantly, and substr(taken_utc,1,10) in a WHERE clause cannot use an index. Storing the date separately costs ten bytes and buys every query you will ever write.

It also survives the timestamp-format mess from lesson 8.3: however inconsistent taken_utc becomes over the years, day is one shape forever.

2 · source is not decoration

When two devices disagree about your weight — and they will — you need to know which row came from where before you can decide anything. A dataset that cannot tell you its own provenance is a dataset you cannot debug.

3 · The partial unique index is the idempotency key

This is the fix for the trap at the end of lesson 8.3. Aggregating imports are correct and dangerous: run the same file twice and you double the day. The unique index on (source, source_key) makes a repeat impossible rather than merely unlikely:

INSERT INTO measurements (taken_utc, day, kind, value, unit, source, source_key, created_utc)
VALUES (?1, substr(?1,1,10), ?2, ?3, ?4, ?5, ?6, ?7)
ON CONFLICT(source, source_key) DO UPDATE SET
  value = excluded.value,           -- a corrected reading should win
  taken_utc = excluded.taken_utc;

The WHERE source_key IS NOT NULL clause matters: manual entries have no source key, and you genuinely may weigh yourself twice in a morning. The constraint binds imports, not you.

4 · NOT NULL wherever it is true

Every nullable column is a branch you will write in every consumer, forever. Make the database refuse the bad row instead. A schema that permits nonsense will receive nonsense, on a Tuesday, from a script you wrote and forgot.

Daily aggregates: a second table, not a view

Some things are naturally per-day rather than per-event — steps, active minutes, total sleep. Give them their own table with the day as the primary key, and apply the aggregate-not-replace rule from lesson 8.3 there:

CREATE TABLE activity_daily (
  day            TEXT PRIMARY KEY,
  steps          INTEGER NOT NULL DEFAULT 0,
  active_minutes INTEGER NOT NULL DEFAULT 0,
  updated_utc    TEXT NOT NULL
);
The rule that makes the whole thing debuggable: raw rows are the truth, aggregates are a convenience. If you can always recompute the aggregate from the raw rows, a bug in the aggregation is a bad afternoon. If you cannot, it is permanent data loss. Keep the raw import payloads somewhere — even a health_raw table with the untouched blob — until you trust the pipeline.

Migrations, on a project with one user

You do not need a migration framework. You do need to stop editing schema.sql as if it were the database. Keep numbered files, apply them in order, and never rewrite one that has run:

migrations/0001-initial.sql
migrations/0002-add-source-key.sql
migrations/0003-nutrition-days.sql

npx wrangler d1 execute health-db --remote --file=migrations/0003-nutrition-days.sql
Before any migration on real data: export first. wrangler d1 export takes seconds and is the difference between a mistake and an incident. This is also where the free Level 3 lesson on backups stops being theoretical.

Where next