geniusrun/backend/internal/store/schema.sql

-- geniusrun's complete SQLite schema, applied in full on every Open() (see
-- db.go). There is no migration history: this file is regenerated in place
-- whenever the schema changes, and is the single source of truth for both
-- the app and its documentation (see docs/DATABASE.md, generated from this
-- file's live effect via cmd/dumpschema -- regenerate it after editing this
-- file). This is a pre-production app with no compatibility obligation to
-- older database files; if you need to change a column, edit it directly
-- here rather than appending an ALTER TABLE migration.

-- One geniusrun account per OIDC subject. Every other table below is scoped
-- to a user_id, directly (profiles/workout_kinds/activities/sync_state/
-- sync_runs) or transitively through a JOIN to the owning row (activity_laps/
-- activity_samples/kind_assignments/workout_type_paces, which have no
-- user_id column of their own since they're never queried except through a
-- specific activity or workout kind). name is the account's single
-- human-facing name: set at onboarding, editable from the Profile page.
CREATE TABLE users (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    oidc_sub   TEXT NOT NULL UNIQUE,
    name       TEXT NOT NULL,
    created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

-- One row per user: Garmin credentials plus every tunable analysis-engine
-- parameter (HR zones, phase-detection minutes, pace-artifact filtering,
-- chart colors). store.ProvisionUser creates this (and everything else
-- below) in one transaction when a new account signs up.
CREATE TABLE profiles (
    id                  INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id             INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    garmin_email        TEXT NOT NULL DEFAULT '',
    garmin_password     TEXT NOT NULL DEFAULT '',
    -- Set once, the first time this user successfully authenticates with
    -- Garmin (handleAuthLogin/handleAuthMFA). Null means never connected --
    -- the login gate uses this (not the in-memory auth status, which
    -- resets on restart) to decide whether a returning user must
    -- reconnect before entering the app.
    garmin_connected_at TEXT,
    rolling_window_days INTEGER NOT NULL DEFAULT 90,
    -- BackfillHorizonDays bounds how far back "Sync now" reaches when
    -- walking backward from today; read fresh on every sync (not cached at
    -- server startup), so changing it here takes effect on the next click.
    backfill_horizon_days INTEGER NOT NULL DEFAULT 90,
    max_heart_rate      REAL,
    resting_heart_rate  REAL,
    hr_zone1_min_pct REAL NOT NULL DEFAULT 50,
    hr_zone1_max_pct REAL NOT NULL DEFAULT 60,
    hr_zone2_min_pct REAL NOT NULL DEFAULT 60,
    hr_zone2_max_pct REAL NOT NULL DEFAULT 70,
    hr_zone3_min_pct REAL NOT NULL DEFAULT 70,
    hr_zone3_max_pct REAL NOT NULL DEFAULT 80,
    hr_zone4_min_pct REAL NOT NULL DEFAULT 80,
    hr_zone4_max_pct REAL NOT NULL DEFAULT 90,
    hr_zone5_min_pct REAL NOT NULL DEFAULT 90,
    hr_zone5_max_pct REAL NOT NULL DEFAULT 100,
    -- Applies uniformly to every fixed-duration workout type's phase
    -- detection (Easy, Long, Tempo, Threshold 30'/60', MAS Test). Interval
    -- workouts detect phases from lap data directly and don't use these.
    warmup_minutes   REAL NOT NULL DEFAULT 10,
    cooldown_minutes REAL NOT NULL DEFAULT 5,
    -- Review Queue pace-chart artifact filter: a stretch of samples slower
    -- than min_representative_pace_sec_per_km is dropped unless it persists
    -- for at least min_representative_time_seconds, in which case it's a
    -- real stop/walk break rather than GPS/motion noise at recording start.
    min_representative_pace_sec_per_km REAL NOT NULL DEFAULT 720,
    min_representative_time_seconds    REAL NOT NULL DEFAULT 3,
    -- Chart colors: pace_color/heart_rate_color are each chart's "main
    -- line" color; warmup/effort/recovery/cooldown_color are the "effort
    -- kind" colors the frontend derives fills from (see
    -- ExpectedVsActualChart).
    pace_color       TEXT NOT NULL DEFAULT '#3b82f6',
    heart_rate_color TEXT NOT NULL DEFAULT '#ef4444',
    warmup_color     TEXT NOT NULL DEFAULT '#c2410c',
    effort_color     TEXT NOT NULL DEFAULT '#7c3aed',
    recovery_color   TEXT NOT NULL DEFAULT '#15803d',
    cooldown_color   TEXT NOT NULL DEFAULT '#fb923c',
    main_line_tint_pct    REAL NOT NULL DEFAULT 20,
    background_darken_pct REAL NOT NULL DEFAULT 35,
    target_brighten_pct   REAL NOT NULL DEFAULT 20,
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at TEXT NOT NULL DEFAULT (datetime('now')),
    UNIQUE(user_id)
);

-- The fixed, closed 8-type taxonomy (Easy, Long, 60' Threshold,
-- 30' Threshold, Tempo, Intervals, MAS Test, Race) -- one independently
-- tunable copy per user, seeded by store.ProvisionUser. rule_json holds the
-- recursive AND/OR condition tree evaluated by internal/classify.
CREATE TABLE workout_kinds (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id     INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    name        TEXT NOT NULL,
    description TEXT NOT NULL DEFAULT '',
    color       TEXT NOT NULL DEFAULT '',
    rule_json   TEXT NOT NULL,
    priority    INTEGER NOT NULL DEFAULT 0,
    is_active   INTEGER NOT NULL DEFAULT 1,
    created_at  TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at  TEXT NOT NULL DEFAULT (datetime('now')),
    UNIQUE(user_id, name)
);

-- Each workout kind's user-declared target pace range + HR range (percent
-- of heart rate reserve). Informational only -- never read by the
-- classification rule engine. No history: overwritten in place, since the
-- synced activity log is the history. No user_id column of its own --
-- ownership is checked via a JOIN to workout_kinds.
CREATE TABLE workout_type_paces (
    workout_kind_id     INTEGER PRIMARY KEY REFERENCES workout_kinds(id) ON DELETE CASCADE,
    pace_min_sec_per_km REAL,
    pace_max_sec_per_km REAL,
    hr_min_pct_hrr      REAL,
    hr_max_pct_hrr      REAL
);

-- One row per synced Garmin activity. garmin_activity_id is the natural
-- idempotency key for UpsertActivity's ON CONFLICT, scoped per user so two
-- different users' Garmin accounts can never collide even if their
-- activity IDs coincided. raw_json/details_raw_json/workout_raw_json store
-- the full original Garmin JSON -- fields that are a pure untransformed
-- copy of something already in raw_json (activity_name, activity_type,
-- etc.) are deliberately NOT modeled as their own columns; internal/api's
-- display_fields.go decodes them fresh from raw_json at response time
-- instead of storing a redundant copy.
CREATE TABLE activities (
    id                         INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id                    INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    garmin_activity_id         INTEGER NOT NULL,
    -- Derived at sync time from Garmin's own eventType.typeKey=="race" --
    -- a hard fact, not a rule the user tunes (see internal/classify's
    -- is_race metric and handleReclassifyAll's Race special-case).
    event_type_key             TEXT NOT NULL DEFAULT '',
    -- Set when the activity was recorded from a structured Garmin workout;
    -- used to resolve each lap's expected pace/HR band (see
    -- internal/sync/mapping.go's alignWorkoutTargets).
    workout_id                 INTEGER,
    start_time_utc             TEXT NOT NULL,
    duration_seconds           REAL NOT NULL,
    distance_meters            REAL NOT NULL,
    avg_hr                     REAL,
    max_hr                     REAL,
    avg_speed_mps              REAL,
    elevation_gain_m           REAL,
    aerobic_training_effect    REAL,
    anaerobic_training_effect  REAL,
    vo2max_value               REAL,
    raw_json                   TEXT NOT NULL,
    details_fetched_at         TEXT,
    details_raw_json           TEXT,
    splits_fetched_at          TEXT,
    -- Genuine raw get_workout_by_id() response, the source used to compute
    -- alignWorkoutTargets. Null when the activity has no workout_id.
    workout_raw_json           TEXT,
    -- Set when get_workout_by_id returned a definitive HTTP 404 (the
    -- workout was deleted on Garmin's side after being linked to this
    -- activity) -- distinct from workout_raw_json staying null for "not yet
    -- fetched": this activity is excluded from ActivitiesMissingWorkout so
    -- it stops being retried forever (see
    -- docs/superpowers/specs/2026-07-27-workout-not-found-design.md).
    -- workout_raw_json itself is never fabricated; it just stays null.
    workout_not_found_at       TEXT,
    created_at                 TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at                 TEXT NOT NULL DEFAULT (datetime('now')),
    UNIQUE(user_id, garmin_activity_id)
);
CREATE INDEX idx_activities_start_time ON activities(start_time_utc);

-- One row per lap/split (from get_activity_splits), plus HR drift/recovery
-- derived from activity_samples. No user_id column -- always accessed
-- through a specific owning activity.
CREATE TABLE activity_laps (
    id                      INTEGER PRIMARY KEY AUTOINCREMENT,
    activity_id             INTEGER NOT NULL REFERENCES activities(id) ON DELETE CASCADE,
    lap_index               INTEGER NOT NULL,
    avg_speed_mps           REAL,
    intensity_type          TEXT NOT NULL DEFAULT '',
    hr_drift_bpm_per_min    REAL,
    hr_recovery_bpm_per_min REAL,
    -- Expected band for this lap, resolved from the activity's structured
    -- Garmin workout when its steps line up 1:1 with the recorded laps.
    -- Null when there's no structured workout or the counts don't match.
    target_pace_low_mps  REAL,
    target_pace_high_mps REAL,
    target_hr_low_bpm    REAL,
    target_hr_high_bpm   REAL,
    raw_json                TEXT NOT NULL,
    UNIQUE(activity_id, lap_index)
);

-- One row per ~1-second telemetry sample (from get_activity_details). No
-- user_id column -- always accessed through a specific owning activity.
CREATE TABLE activity_samples (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    activity_id     INTEGER NOT NULL REFERENCES activities(id) ON DELETE CASCADE,
    elapsed_seconds REAL NOT NULL,
    timestamp_ms    INTEGER NOT NULL,
    heart_rate      REAL,
    speed_mps       REAL,
    distance_m      REAL,
    elevation_m     REAL
);
CREATE INDEX idx_activity_samples_activity ON activity_samples(activity_id, elapsed_seconds);

-- Append-only classification history -- always INSERT, never UPDATE.
-- Re-classifying after a rule edit, or a manual override, keeps full
-- history; current_kind_assignment (below) picks the latest row per
-- activity. assignment_source distinguishes manual overrides (locked
-- against future global reclassifies) from rule-engine assignments. No
-- user_id column -- always accessed through the owning activity.
CREATE TABLE kind_assignments (
    id                   INTEGER PRIMARY KEY AUTOINCREMENT,
    activity_id          INTEGER NOT NULL REFERENCES activities(id) ON DELETE CASCADE,
    workout_kind_id      INTEGER REFERENCES workout_kinds(id),
    assignment_source    TEXT NOT NULL CHECK(assignment_source IN ('rule_engine','manual')),
    status               TEXT NOT NULL CHECK(status IN ('assigned','needs_review')),
    confidence           REAL,
    candidate_kinds_json TEXT NOT NULL DEFAULT '[]',
    created_at           TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE INDEX idx_kind_assignments_activity ON kind_assignments(activity_id);
CREATE INDEX idx_kind_assignments_status ON kind_assignments(status);

CREATE VIEW current_kind_assignment AS
SELECT a.* FROM kind_assignments a
JOIN (
    SELECT activity_id, MAX(id) AS max_id
    FROM kind_assignments GROUP BY activity_id
) latest ON a.activity_id = latest.activity_id AND a.id = latest.max_id;

-- One row per user: tracks a backfill watermark (earliest_synced_date,
-- backfill_complete). Since Garmin history is immutable once recorded,
-- Service.Backfill uses this to resume from where it left off instead of
-- re-walking years of already-known history on every call.
CREATE TABLE sync_state (
    id                   INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id              INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    earliest_synced_date TEXT,
    backfill_complete    INTEGER NOT NULL DEFAULT 0,
    UNIQUE(user_id)
);

-- One row per backfill/incremental/full sync attempt, for the "last sync"
-- status the frontend polls.
CREATE TABLE sync_runs (
    id                 INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id            INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    kind               TEXT NOT NULL CHECK(kind IN ('backfill','incremental','full')),
    started_at         TEXT NOT NULL,
    finished_at        TEXT,
    activities_fetched INTEGER NOT NULL DEFAULT 0,
    status             TEXT NOT NULL CHECK(status IN ('running','success','error')),
    error_message      TEXT
);

-- Application configuration: instance-global key/value settings, shared
-- by every user -- deliberately the one table with no user_id, because
-- application configuration is common to all users by definition. Every
-- key in internal/config's app-key registry is mandatory here: missing
-- rows are seeded with their defaults at startup (cmd/geniusrund), so
-- there are no code-side fallbacks. Cold: read once at startup, a change
-- applies on the next backend restart.
CREATE TABLE config (
    key        TEXT PRIMARY KEY,
    value      TEXT NOT NULL,
    updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);