Data Model

Local schema (Drift/SQLite) and Supabase schema (Postgres) mirror each other. All tables carry id UUID, created_at, and updated_at for sync. Rows never hard-delete on the client — a deleted_at timestamp is set and reconciled with the remote.

---

profiles

One row per user.

column	type	notes
id	uuid PK	matches auth.uid() when signed in
avg_cycle_length	int	default 28, range 21–45
avg_period_length	int	default 5, range 2–10
on_hormonal_contraception	bool	if true → flat programming mode
fitness_level	enum	new · regular · athlete
goals	text[]	subset of strength, endurance, general, weight_management, mobility, recovery
equipment	enum	bodyweight · dumbbells · gym
hr_max	int?	for HR-zone rendering on export
lthr	int?	lactate threshold HR
ftp	int?	functional threshold power
timezone	text	for accurate day-boundaries
notifications_enabled	bool

---

cycles

One row per menstrual cycle (period-start to next-period-start).

column	type	notes
id	uuid PK
profile_id	uuid FK
start_date	date	day 1 of the cycle
end_date	date?	null until the next cycle starts
period_length_days	int?	computed on close
cycle_length_days	int?	computed on close
notes	text?

---

cycle_days

One row per day the user logs anything.

column	type	notes
id	uuid PK
profile_id	uuid FK
date	date	unique per profile
flow	enum?	none · spot · light · medium · heavy
symptoms	text[]	e.g. cramps, bloating, headache, breast_tenderness, acne, mood_swings, fatigue
mood	int?	1–5
energy	int?	1–5 self-rating; feeds the recommender
sleep_hours	numeric?
notes	text?

Compound unique: (profile_id, date).

---

workouts

Catalog. Ships with seed rows from WORKOUT_LIBRARY.md. User-created custom workouts also live here with is_custom=true.

column	type	notes
id	uuid PK
slug	text	stable identifier like F1_full_body_strength
title	text
description	text
phase	enum	menstrual · follicular · ovulatory · luteal_early · luteal_late
min_level	enum	new · regular · athlete
equipment	enum[]
goals	text[]
duration_min	int
intensity_ceiling_rpe	int	1–10
steps	jsonb	see WorkoutStep below
exportable	bool	true iff every step has a concrete duration + target
is_custom	bool
owner_profile_id	uuid?	non-null only when is_custom

WorkoutStep (embedded jsonb)

{
  "order": 1,
  "kind": "work" | "rest" | "warmup" | "cooldown" | "repeat",
  "durationSeconds": 300,      // required for exportable=true
  "target": {                  // exactly one of the following:
    "rpe": 8,                  //   RPE 1..10
    "zone": 4,                 //   Zone 1..5
    "hr": { "min": 150, "max": 165 },
    "power": { "min": 220, "max": 260 }
  },
  "description": "5 reps goblet squat, 90s rest",
  "repeat": {                  // set only when kind == "repeat"
    "count": 6,
    "childSteps": [ /* nested WorkoutStep[] */ ]
  }
}

This shape is deliberately close to the Intervals.icu workout JSON and the Garmin FIT workout_step message so exporters are near-lossless.

---

workout_sessions

A completed instance of a workout.

column	type	notes
id	uuid PK
profile_id	uuid FK
workout_id	uuid FK
date	date
phase_at_time	enum	snapshot; the user's phase may drift as cycle data updates, but this is what we recommended based on
day_of_cycle_at_time	int
completed	bool
felt_score	int?	1–5 subjective post-workout feel; feeds the recommender
notes	text?
external_ids	jsonb?	{ "strava": "12345", "garmin": "abc", "intervals_icu": "xyz" }

---

integrations

Per-provider OAuth state.

column	type	notes
id	uuid PK
profile_id	uuid FK
provider	enum	garmin · strava · intervals_icu · apple_health · google_fit
enabled	bool
access_token_encrypted	bytea?	never plaintext at rest
refresh_token_encrypted	bytea?
expires_at	timestamptz?
scopes	text[]

Compound unique: (profile_id, provider).

---

Supabase RLS

Every table has RLS enabled. Policy on every user-owned table:

create policy "own rows only"
on <table>
for all
using (profile_id = auth.uid())
with check (profile_id = auth.uid());

The workouts table adds a public-read exception for is_custom = false seed rows.

---

Sync strategy

• Local writes are authoritative and offline-first. Each row gets updated_at = now().
• Sync loop (when online and signed in): push local rows where updated_at > last_synced_at, then pull remote rows where updated_at > last_synced_at.
• Conflict resolution: last-write-wins per row. This is fine for a single-user app synced across their own devices; if we later add sharing, we revisit.
• Deletes are soft (deleted_at) so a stale device coming online can't resurrect deleted rows.

---

Extension points (kept in mind but not built)

• basal_body_temp, cervical_mucus, lh_test_result columns on cycle_days — for fertility mode.
• wearable_readings table — HRV, sleep score, resting HR — to make the recommender adjust for objective recovery, not just phase.
• programs table — multi-week structured programs vs. day-by-day recommendations.