2 files changed, 34 insertions, 1 deletions
diff --git a/db/migrations/20260913000000_ride_official_climb_attempts.sql b/db/migrations/20260913000000_ride_official_climb_attempts.sql
new file mode 100644
index 0000000..880f81b
--- /dev/null
+++ b/db/migrations/20260913000000_ride_official_climb_attempts.sql
@@ -0,0 +1,18 @@
+-- migrate:up
+CREATE TABLE ride_official_climb_attempts (
+    ride_id integer not null references rides(id),
+    climb_id integer not null references official_climbs(id),
+    occurrence integer not null,
+    distance_m real not null,
+    elevation_gain_m real not null,
+    slope_percent real not null,
+    cotacol real not null,
+    cotacol_algo_version text not null,
+    started_at text not null,
+    ended_at text not null,
+    created_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
+    primary key (ride_id, climb_id, occurrence)
+);
+
+-- migrate:down
+DROP TABLE ride_official_climb_attempts;
diff --git a/db/schema.sql b/db/schema.sql
index a97b7f1..e149762 100644
--- a/db/schema.sql
+++ b/db/schema.sql
@@ -24,6 +24,20 @@ CREATE TABLE official_climbs (
     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'))
 );
+CREATE TABLE ride_official_climb_attempts (
+    ride_id integer not null references rides(id),
+    climb_id integer not null references official_climbs(id),
+    occurrence integer not null,
+    distance_m real not null,
+    elevation_gain_m real not null,
+    slope_percent real not null,
+    cotacol real not null,
+    cotacol_algo_version text not null,
+    started_at text not null,
+    ended_at text not null,
+    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" (
     id integer primary key,
     osm_id integer unique not null,
@@ -45,4 +59,5 @@ INSERT INTO "schema_migrations" (version) VALUES
   ('20260827000000'),
   ('20260828000000'),
   ('20260829000000'),
-  ('20260831000000');
+  ('20260831000000'),
+  ('20260913000000');