garmin_cli/db/
schema.rs

1//! Database schema and migrations
2
3use duckdb::Connection;
4
5/// Current schema version
6#[allow(dead_code)]
7const SCHEMA_VERSION: i32 = 1;
8
9/// Run all pending migrations
10pub fn migrate(conn: &Connection) -> crate::Result<()> {
11    // Create migrations table if not exists
12    conn.execute(
13        "CREATE TABLE IF NOT EXISTS schema_migrations (
14            version INTEGER PRIMARY KEY,
15            applied_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
16        )",
17        [],
18    )
19    .map_err(|e| crate::error::GarminError::Database(e.to_string()))?;
20
21    // Get current version
22    let current_version: i32 = conn
23        .query_row(
24            "SELECT COALESCE(MAX(version), 0) FROM schema_migrations",
25            [],
26            |row| row.get(0),
27        )
28        .unwrap_or(0);
29
30    // Apply pending migrations
31    if current_version < 1 {
32        migration_v1(conn)?;
33    }
34
35    Ok(())
36}
37
38/// Migration v1: Initial schema
39fn migration_v1(conn: &Connection) -> crate::Result<()> {
40    let statements = [
41        // Profiles for multi-account support
42        "CREATE SEQUENCE IF NOT EXISTS profiles_seq",
43        "CREATE TABLE IF NOT EXISTS profiles (
44            profile_id INTEGER PRIMARY KEY DEFAULT nextval('profiles_seq'),
45            display_name TEXT NOT NULL UNIQUE,
46            user_id BIGINT,
47            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
48            last_sync_at TIMESTAMP
49        )",
50        // Activities (summary data from API)
51        "CREATE TABLE IF NOT EXISTS activities (
52            activity_id BIGINT PRIMARY KEY,
53            profile_id INTEGER REFERENCES profiles(profile_id),
54            activity_name TEXT,
55            activity_type TEXT,
56            start_time_local TIMESTAMP,
57            start_time_gmt TIMESTAMP,
58            duration_sec DOUBLE,
59            distance_m DOUBLE,
60            calories INTEGER,
61            avg_hr INTEGER,
62            max_hr INTEGER,
63            avg_speed DOUBLE,
64            max_speed DOUBLE,
65            elevation_gain DOUBLE,
66            elevation_loss DOUBLE,
67            avg_cadence DOUBLE,
68            avg_power INTEGER,
69            normalized_power INTEGER,
70            training_effect DOUBLE,
71            training_load DOUBLE,
72            start_lat DOUBLE,
73            start_lon DOUBLE,
74            end_lat DOUBLE,
75            end_lon DOUBLE,
76            ground_contact_time DOUBLE,
77            vertical_oscillation DOUBLE,
78            stride_length DOUBLE,
79            location_name TEXT,
80            raw_json JSON,
81            synced_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
82        )",
83        // Track points (time-series GPS + sensor data)
84        "CREATE SEQUENCE IF NOT EXISTS track_points_seq",
85        "CREATE TABLE IF NOT EXISTS track_points (
86            id BIGINT PRIMARY KEY DEFAULT nextval('track_points_seq'),
87            activity_id BIGINT REFERENCES activities(activity_id),
88            timestamp TIMESTAMP NOT NULL,
89            lat DOUBLE,
90            lon DOUBLE,
91            elevation DOUBLE,
92            heart_rate INTEGER,
93            cadence INTEGER,
94            power INTEGER,
95            speed DOUBLE
96        )",
97        "CREATE INDEX IF NOT EXISTS idx_trackpoints_activity ON track_points(activity_id, timestamp)",
98        // Daily health metrics
99        "CREATE SEQUENCE IF NOT EXISTS daily_health_seq",
100        "CREATE TABLE IF NOT EXISTS daily_health (
101            id BIGINT PRIMARY KEY DEFAULT nextval('daily_health_seq'),
102            profile_id INTEGER REFERENCES profiles(profile_id),
103            date DATE NOT NULL,
104            steps INTEGER,
105            step_goal INTEGER,
106            total_calories INTEGER,
107            active_calories INTEGER,
108            bmr_calories INTEGER,
109            resting_hr INTEGER,
110            sleep_seconds INTEGER,
111            deep_sleep_seconds INTEGER,
112            light_sleep_seconds INTEGER,
113            rem_sleep_seconds INTEGER,
114            sleep_score INTEGER,
115            avg_stress INTEGER,
116            max_stress INTEGER,
117            body_battery_start INTEGER,
118            body_battery_end INTEGER,
119            hrv_weekly_avg INTEGER,
120            hrv_last_night INTEGER,
121            hrv_status TEXT,
122            avg_respiration DOUBLE,
123            avg_spo2 INTEGER,
124            lowest_spo2 INTEGER,
125            hydration_ml INTEGER,
126            moderate_intensity_min INTEGER,
127            vigorous_intensity_min INTEGER,
128            raw_json JSON,
129            synced_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
130            UNIQUE(profile_id, date)
131        )",
132        "CREATE INDEX IF NOT EXISTS idx_health_date ON daily_health(profile_id, date)",
133        // Performance metrics
134        "CREATE SEQUENCE IF NOT EXISTS performance_metrics_seq",
135        "CREATE TABLE IF NOT EXISTS performance_metrics (
136            id BIGINT PRIMARY KEY DEFAULT nextval('performance_metrics_seq'),
137            profile_id INTEGER REFERENCES profiles(profile_id),
138            date DATE NOT NULL,
139            vo2max DOUBLE,
140            fitness_age INTEGER,
141            training_readiness INTEGER,
142            training_status TEXT,
143            lactate_threshold_hr INTEGER,
144            lactate_threshold_pace DOUBLE,
145            race_5k_sec INTEGER,
146            race_10k_sec INTEGER,
147            race_half_sec INTEGER,
148            race_marathon_sec INTEGER,
149            endurance_score INTEGER,
150            hill_score INTEGER,
151            raw_json JSON,
152            synced_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
153            UNIQUE(profile_id, date)
154        )",
155        // Weight entries
156        "CREATE SEQUENCE IF NOT EXISTS weight_entries_seq",
157        "CREATE TABLE IF NOT EXISTS weight_entries (
158            id BIGINT PRIMARY KEY DEFAULT nextval('weight_entries_seq'),
159            profile_id INTEGER REFERENCES profiles(profile_id),
160            date DATE NOT NULL,
161            weight_kg DOUBLE,
162            bmi DOUBLE,
163            body_fat_pct DOUBLE,
164            muscle_mass_kg DOUBLE,
165            synced_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
166            UNIQUE(profile_id, date)
167        )",
168        // Sync state tracking
169        "CREATE TABLE IF NOT EXISTS sync_state (
170            profile_id INTEGER REFERENCES profiles(profile_id),
171            data_type TEXT NOT NULL,
172            last_sync_date DATE,
173            last_activity_id BIGINT,
174            PRIMARY KEY (profile_id, data_type)
175        )",
176        // Sync tasks queue for recovery
177        "CREATE SEQUENCE IF NOT EXISTS sync_tasks_seq",
178        "CREATE TABLE IF NOT EXISTS sync_tasks (
179            id BIGINT PRIMARY KEY DEFAULT nextval('sync_tasks_seq'),
180            profile_id INTEGER,
181            task_type TEXT NOT NULL,
182            task_data JSON,
183            status TEXT DEFAULT 'pending',
184            attempts INTEGER DEFAULT 0,
185            last_error TEXT,
186            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
187            next_retry_at TIMESTAMP,
188            completed_at TIMESTAMP
189        )",
190        "CREATE INDEX IF NOT EXISTS idx_sync_tasks_status ON sync_tasks(status, next_retry_at)",
191        // Record migration
192        "INSERT INTO schema_migrations (version) VALUES (1)",
193    ];
194
195    for sql in statements {
196        conn.execute(sql, []).map_err(|e| {
197            crate::error::GarminError::Database(format!(
198                "{}: {}",
199                sql.chars().take(50).collect::<String>(),
200                e
201            ))
202        })?;
203    }
204
205    Ok(())
206}
207
208#[cfg(test)]
209mod tests {
210    use super::*;
211
212    #[test]
213    fn test_migration_v1() {
214        let conn = Connection::open_in_memory().unwrap();
215        migrate(&conn).expect("Migration failed");
216
217        // Verify tables exist
218        let tables: Vec<String> = conn
219            .prepare("SELECT table_name FROM information_schema.tables WHERE table_schema = 'main'")
220            .unwrap()
221            .query_map([], |row| row.get(0))
222            .unwrap()
223            .filter_map(|r| r.ok())
224            .collect();
225
226        assert!(tables.contains(&"profiles".to_string()));
227        assert!(tables.contains(&"activities".to_string()));
228        assert!(tables.contains(&"track_points".to_string()));
229        assert!(tables.contains(&"daily_health".to_string()));
230        assert!(tables.contains(&"sync_tasks".to_string()));
231    }
232
233    #[test]
234    fn test_migration_idempotent() {
235        let conn = Connection::open_in_memory().unwrap();
236        migrate(&conn).expect("First migration failed");
237        migrate(&conn).expect("Second migration should be idempotent");
238    }
239}
garmin_cli/db/schema.rs

garmin_cli/db/
schema.rs
