Chart power vs time for official climb attempts

Snapshot average power per attempt in a nullable average_power_w column (backfilled from stored rides), plot each powered attempt on the climb page as duration vs average watts with ride hover and click-through, and show avg power in the attempts table.

2 files changed, 9 insertions, 2 deletions

diff --git a/db/migrations/20260925061236_ride_official_climb_attempts_average_power.sql b/db/migrations/20260925061236_ride_official_climb_attempts_average_power.sql
new file mode 100644
index 0000000..e0c4bf1
--- /dev/null
+++ b/db/migrations/20260925061236_ride_official_climb_attempts_average_power.sql
@@ -0,0 +1,6 @@
+-- Average power over the attempt interval; null for rides without a power meter.
+-- migrate:up
+ALTER TABLE ride_official_climb_attempts ADD COLUMN average_power_w real;
+
+-- migrate:down
+ALTER TABLE ride_official_climb_attempts DROP COLUMN average_power_w;
diff --git a/db/schema.sql b/db/schema.sql
index 48bd02f..8995098 100644
--- a/db/schema.sql
+++ b/db/schema.sql
@@ -40,7 +40,7 @@ CREATE TABLE ride_official_climb_attempts (
     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')),
+    created_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')), average_power_w real,
     primary key (ride_id, climb_id, occurrence)
 );
 CREATE TABLE IF NOT EXISTS "official_climbs" (
@@ -71,4 +71,5 @@ INSERT INTO "schema_migrations" (version) VALUES
   ('20260831000000'),
   ('20260913000000'),
   ('20260924000000'),
-  ('20260925000000');
+  ('20260925000000'),
+  ('20260925061236');