From e3d0beee5be3dd2854b0f4fc10905a4fcdc08f47 Mon Sep 17 00:00:00 2001
From: Martin Kagamino Lehoux <martin@lehoux.net>
Date: Sun, 13 Sep 2026 21:55:59 +0200
Subject: Persist official climb attempts with climbs pages

Record one attempt row per (ride, climb, occurrence) when a ride detail
loads, snapshotting distance, elevation gain, slope, cotacol and attempt
timestamps; a climbs-backfill CLI command seeds all existing rides.

New /climbs list page aggregates attempts (medians computed in Go, best
time as min duration) with sortable columns, and /climbs/{id} shows the
median scalar stats, a median normalized elevation profile (50 steps
over each attempt's own length), the full-precision recorded route of
the median-distance attempt on an OSM map, and every attempt with time
at second precision.
---
 .../20260913000000_ride_official_climb_attempts.sql    | 18 ++++++++++++++++++
 db/schema.sql                                          | 17 ++++++++++++++++-
 2 files changed, 34 insertions(+), 1 deletion(-)
 create mode 100644 db/migrations/20260913000000_ride_official_climb_attempts.sql

(limited to 'db')

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');
-- 
cgit v1.3