3 files changed, 76 insertions, 20 deletions
diff --git a/db/migrations/20260924000000_official_climb_sample.sql b/db/migrations/20260924000000_official_climb_sample.sql
new file mode 100644
index 0000000..6a3990f
--- /dev/null
+++ b/db/migrations/20260924000000_official_climb_sample.sql
@@ -0,0 +1,7 @@
+-- migrate:up
+ALTER TABLE official_climbs ADD COLUMN sample_distance_m real;
+ALTER TABLE official_climbs ADD COLUMN sample_elevation_gain_m real;
+
+-- migrate:down
+ALTER TABLE official_climbs DROP COLUMN sample_distance_m;
+ALTER TABLE official_climbs DROP COLUMN sample_elevation_gain_m;
diff --git a/db/migrations/20260925000000_official_climb_sample_not_null.sql b/db/migrations/20260925000000_official_climb_sample_not_null.sql
new file mode 100644
index 0000000..a3f80c2
--- /dev/null
+++ b/db/migrations/20260925000000_official_climb_sample_not_null.sql
@@ -0,0 +1,38 @@
+-- Every climb carries its segment sample, so the columns stop being optional; SQLite cannot alter nullability, hence the rebuild (it fails loudly on a missing sample).
+-- migrate:up transaction:false
+PRAGMA foreign_keys=OFF;
+CREATE TABLE official_climbs_rebuild (
+    id integer primary key,
+    name text not null,
+    start_latitude real not null,
+    start_longitude real not null,
+    end_latitude real not null,
+    end_longitude real not null,
+    created_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
+    updated_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
+    sample_distance_m real not null,
+    sample_elevation_gain_m real not null
+);
+INSERT INTO official_climbs_rebuild (id, name, start_latitude, start_longitude, end_latitude, end_longitude, created_at, updated_at, sample_distance_m, sample_elevation_gain_m)
+SELECT id, name, start_latitude, start_longitude, end_latitude, end_longitude, created_at, updated_at, sample_distance_m, sample_elevation_gain_m FROM official_climbs;
+DROP TABLE official_climbs;
+ALTER TABLE official_climbs_rebuild RENAME TO official_climbs;
+
+-- migrate:down transaction:false
+PRAGMA foreign_keys=OFF;
+CREATE TABLE official_climbs_rebuild (
+    id integer primary key,
+    name text not null,
+    start_latitude real not null,
+    start_longitude real not null,
+    end_latitude real not null,
+    end_longitude real not null,
+    created_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
+    updated_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
+    sample_distance_m real,
+    sample_elevation_gain_m real
+);
+INSERT INTO official_climbs_rebuild (id, name, start_latitude, start_longitude, end_latitude, end_longitude, created_at, updated_at, sample_distance_m, sample_elevation_gain_m)
+SELECT id, name, start_latitude, start_longitude, end_latitude, end_longitude, created_at, updated_at, sample_distance_m, sample_elevation_gain_m FROM official_climbs;
+DROP TABLE official_climbs;
+ALTER TABLE official_climbs_rebuild RENAME TO official_climbs;
diff --git a/db/schema.sql b/db/schema.sql
index e149762..48bd02f 100644
--- a/db/schema.sql
+++ b/db/schema.sql
@@ -1,5 +1,13 @@
 CREATE TABLE IF NOT EXISTS "schema_migrations" (version varchar(255) primary key);
-CREATE TABLE rides (
+CREATE TABLE IF NOT EXISTS "mountain_passes" (
+    id integer primary key,
+    osm_id integer unique not null,
+    name text,
+    elevation integer,
+    latitude real not null,
+    longitude real not null
+, category TEXT NOT NULL DEFAULT 'unknown' CHECK (category IN ('road', 'track', 'path', 'hiking', 'unknown')));
+CREATE TABLE IF NOT EXISTS "rides" (
     id integer primary key,
     external_id text unique not null,
     gpx_path text not null,
@@ -12,17 +20,14 @@ CREATE TABLE rides (
     total_elevation_gain_m real not null,
     average_speed_mps real not null,
     created_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
-    updated_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now'))
-, cotacol_score real, cotacol_algo_version text, moving_time_s integer, moving_time_algo_version text, bike_type TEXT NOT NULL DEFAULT '' CHECK (bike_type IN ('', 'road', 'mtb')), ride_type TEXT NOT NULL DEFAULT '' CHECK (ride_type IN ('', 'training', 'indoor', 'race', 'transport', 'touring')), fit_path TEXT NOT NULL DEFAULT '');
-CREATE TABLE official_climbs (
-    id integer primary key,
-    name text not null,
-    start_latitude real not null,
-    start_longitude real not null,
-    end_latitude real not null,
-    end_longitude real not null,
-    created_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
-    updated_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now'))
+    updated_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
+    cotacol_score real,
+    cotacol_algo_version text,
+    moving_time_s integer,
+    moving_time_algo_version text,
+    bike_type TEXT NOT NULL DEFAULT '' CHECK (bike_type IN ('', 'road', 'mtb')),
+    ride_type TEXT NOT NULL DEFAULT '' CHECK (ride_type IN ('', 'training', 'indoor', 'race', 'transport', 'touring')),
+    fit_path TEXT NOT NULL DEFAULT ''
 );
 CREATE TABLE ride_official_climb_attempts (
     ride_id integer not null references rides(id),
@@ -38,14 +43,18 @@ CREATE TABLE ride_official_climb_attempts (
     created_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
     primary key (ride_id, climb_id, occurrence)
 );
-CREATE TABLE IF NOT EXISTS "mountain_passes" (
+CREATE TABLE IF NOT EXISTS "official_climbs" (
     id integer primary key,
-    osm_id integer unique not null,
-    name text,
-    elevation integer,
-    latitude real not null,
-    longitude real not null
-, category TEXT NOT NULL DEFAULT 'unknown' CHECK (category IN ('road', 'track', 'path', 'hiking', 'unknown')));
+    name text not null,
+    start_latitude real not null,
+    start_longitude real not null,
+    end_latitude real not null,
+    end_longitude real not null,
+    created_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
+    updated_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
+    sample_distance_m real not null,
+    sample_elevation_gain_m real not null
+);
 -- Dbmate schema migrations
 INSERT INTO "schema_migrations" (version) VALUES
   ('20250802140659'),
@@ -60,4 +69,6 @@ INSERT INTO "schema_migrations" (version) VALUES
   ('20260828000000'),
   ('20260829000000'),
   ('20260831000000'),
-  ('20260913000000');
+  ('20260913000000'),
+  ('20260924000000'),
+  ('20260925000000');