Match official climbs against their recorded sample

An official climb matched on endpoint coordinates alone. On a loop ridden
the other way the route meets the climb's top before its bottom, so pairing
each start with the first end inside its lap window credited a 5.9 km
passage as the 850 m Chemin de la Tour Pey Blanc, and a 49 km one on
another ride.

Climbs now keep the sample of the segment they were created from: the
recorded length and elevation gain of the selected route points, captured
by the ride detail and preview handlers and stored NOT NULL. Matching takes
the whole route and drops any candidate outside ±50% of that sample on
either axis, and attempts are refreshed by upsert and prune so re-analyzing
a ride keeps the recorded-at stamp of the passages that survive.

The backfill leaves the web package: backfill.Run recomputes ride values
then official climb attempts as a single `go run . backfill` command
(climbs-backfill is gone), rideanalysis owns the attempt snapshot, and the
mountain-pass loading margin is shared. Applied to the stored rides it
stamps 78 samples, drops the four bogus attempts and leaves ride 800 with
its three Pont Rout passages.

1 files changed, 31 insertions, 20 deletions

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');