API & Scoring Logic Documentation

Participant Portal (app.html) Tabs Details

The participant portal features five core views controlled dynamically by the user's tab navigation bar. Below is the technical breakdown of each tab, detailing where the data comes from and which configuration keys influence it.

1. Dashboard / Stats Tab (#tab-dashboard)

• Content: Renders the logged-in user's personalized stats, including cumulative distance, daily progress bars, streaks, base/challenge/bonus points, and their personal activity history list.
• Data Source:
  • Profile details (Name, Shift, Team) fetched from the registration table.
  • Cached aggregated values (Total distance, total points, splits points) read from athlete_points_summary.
  • List of recent activities fetched from activities?strava_athlete_id=eq.{athlete_id}&is_deleted=eq.false.
• Required Admin Configurations:
  • Event Configuration (event_config): Sets start and end dates of the event, regulating which activities are displayed.
  • Scoring Base Metrics (milestone_config): Determines the threshold goals (5km, 8km, 10km, 15km, 21km) displayed in the progress tracker.

2. Leaderboard Tab (#tab-leaderboard)

• Content: Dynamic rankings split into three categories: Shift Category, Team Standing, and global Hall of Fame (HOF).
• Data Source:
  • Queries the cached athlete_points_summary table. The rankings are sorted in descending order of total_points (falling back to total_distance_km in case of ties).
• Required Admin Configurations:
  • Team Configuration: The lists of valid teams (e.g. "Team San Francisco", "Team Mumbai") are configured globally. Standings aggregate summaries group on the athlete's leaderboard_team.

3. Challenges Tab (#tab-events)

• Content: A grid of active challenges (e.g., Walk 10km on Sunday) showing completions, progress percentages, and buttons for submitting manual/special entries.
• Data Source:
  • Active items are loaded from the challenges table where the current date falls between start_date and end_date.
  • Completions are computed by analyzing whether the user has one or more matching activities in the activities table that satisfy the distance target.
• Required Admin Configurations:
  • Events Configuration (events.html): Organizers create challenges specifying whether they are fixed or range types, setting targets and points values.

4. Celebrate Tab (#tab-celebrate)

• Content: A live timeline highlighting notable achievements (e.g., "John Doe achieved a 7-day walk streak" or "Sarah joined the 200km club!").
• Data Source:
  • Fetched from the celebrations table.
• Required Admin Configurations:
  • Celebrate Config (celebrate_config): Organizers set background theme assets, confetti particles, and custom sound clip paths.

Admin Panel (admin.html) Page Breakdown

Organizers manage the Walkathon using a collection of specific administrative dashboard pages. Below is the functionality and database operations playbook for each page.

1. Dashboard (admin.html)

• Purpose: Provides a high-level summary of walkathon progress, showing registrations status, total cumulative distance, and logs of recent activities.
• DB Operations:
  • Counts rows in `registration` where status = 'pending'.
  • Sums total_distance_km and total_points in `athlete_points_summary`.
  • Fetches the 10 most recent entries in `activities` (joining `registration` on `strava_athlete_id` to get names).

2. Events Management (events.html)

• Purpose: Interface for organizers to create, edit, or delete challenge events.
• DB Operations:
  • Reads, inserts, patches, and deletes rows from the challenges table.
  • Fields updated: name, distance_type, distance, points, start_date, end_date, event_id.

3. Participant Accounts (athletedtl.html)

• Purpose: Manage participant lists, edit user details (Shifts, Teams, Roles, Project Leads), review whitelist statuses, and approve/reject pending registrations.
• DB Operations:
  • Reads and updates the registration table.
  • Fields updated: shift, leaderboard_team, role, project_lead, status.

4. Reports (reports.html)

• Purpose: Generates detailed reports (Shift averages, team standings, and compliance statistics) for export.
• DB Operations:
  • Runs aggregate sum queries on athlete_points_summary and activities grouped by shifts and teams.

5. System Configuration (config.html)

• Purpose: Set up general walkathon settings (base milestones, double point dates, and event timezone bounds).
• DB Operations:
  • Patches config_value in leaderboard_config where config_key = 'event_config'.
  • Inserts and deletes rows in the special_days table.

6. Activities Auditor (activities.html)

• Purpose: Audit incoming activities, flag suspicious walks (excessive distance, speed limits violated), and restore deleted walks.
• DB Operations:
  • Updates `activities` setting is_flagged, flag_reason, and is_deleted.

7. Data & Sync Actions (data-upload.html)

• Purpose: Manually trigger sync actions for a participant, force token refresh operations, or trigger points recalculations.
• DB Operations:
  • Triggers backend endpoints (/admin/backfill-points, /admin/sync-athlete) which compile statistics in the database.

8. Audit logs viewer (audit-log.html)

• Purpose: View history logs of administrator operations to maintain system tracking.
• DB Operations:
  • Reads records from the audit_logs table.

Participant Registration & Approval Flow

The onboarding pipeline secures participant entries by verifying profiles against an active employee allowlist before authorizing Strava connectivity.

Detailed Onboarding Lifecycle

1. Initial Registration Form Submission (POST /register-request):
   An athlete fills in details on the registration form. The backend verifies the email and employee code against the employees_master table to ensure the user is an active employee.
   If verified, an entry is created in the registration table with status = 'pending', role = 'user', and strava_connected = false.
2. OAuth Handshake with Strava:
   Once the employee registration entry is saved, the user is redirected to Strava to authenticate. Upon consent, Strava redirects back to connect-strava.html with an authorization code.
3. Token Exchange and Connection (POST /register):
   The frontend sends the authorization code to the backend. The backend calls the Strava OAuth endpoint (https://www.strava.com/oauth/token) to swap it for a refresh_token, access_token, and the athlete profile.
   The backend updates the registration entry with:
   • strava_athlete_id (Strava athlete profile ID)
   • access_token & refresh_token (encrypted tokens)
   • profile_photo (URL to Strava profile image)
   • strava_connected = true
   • strava_connected_at = NOW()
4. Organizer Review & Approval (athletedtl.html):
   Organizers inspect the pending queue. Approving a user triggers a database PATCH setting status = 'approved'. This activates the user on the leaderboard and schedules history synchronization.

Scoring Rules & Engine

Points are calculated per athlete per calendar day using their local activity_date_time_ist date.

1. Base Points

• Calculated as 1 Point per 1 km walked.
• Distances are not rounded down; e.g. walking 5.45 km grants 5.45 base points.

2. Milestone Bonuses (Base Tier)

Athletes earn extra points based on their cumulative distance on any single calendar day:

3. Daily Cap (21 km / 26 Points Limit)

To ensure fair competition, active walking distance is capped at 21 km per calendar day (maximum 26.0 total points including the 5.0 milestone bonus).

4. Range-Split Active Challenges Logic

If a challenge on a day is a range-split type (e.g. walk 5km for a challenge):

1. We identify the activities matching the challenge.
2. We deduct the challenge distance (e.g. 5km) from the day's total walking pool.
3. The deducted distance is scored separately as Challenge Points.
4. The remaining distance is evaluated against the milestone tiers to compute Base Points + Milestone Bonuses.

5. Multi-Challenge Days

If an athlete enters two challenges on a single day, the engine resolves them in order of priority, separating challenge pools to prevent double-dipping, and scores the remaining remainder towards daily base standings.

Full Strava API Field Mappings

This table lists all fields mapped from the incoming Strava activity payload (via webhooks or history fetch API) to the Supabase activities database schema.

Steps Resolution Rules

Because some devices do not write steps directly to Strava, the steps count is resolved via the following prioritization pipeline in resolveSteps(a):

function resolveSteps(activity) {
  // Tier 1: Strava native steps field
  if (activity.steps && activity.steps > 0) {
    return activity.steps;
  }
  // Tier 2: Stride cadence calculation
  if (activity.average_cadence && activity.moving_time) {
    return Math.round(activity.average_cadence * 2 * (activity.moving_time / 60));
  }
  // Tier 3: Distance estimates based on sport type
  const distKm = (activity.distance || 0) / 1000;
  if (activity.sport_type === 'Walk' || activity.sport_type === 'Hike') {
    return Math.round(distKm * 1350); // 1,350 steps/km
  }
  if (activity.sport_type === 'Run' || activity.sport_type === 'VirtualRun') {
    return Math.round(distKm * 1150); // 1,150 steps/km
  }
  return null;
}

Celebrate Settings

The celebrate feed pushes achievements to participants. Configuration is stored in the leaderboard_config table.

1. feed_config JSON Schema

The configuration row matches config_key = 'feed_config' in `leaderboard_config`. The activity feed it originally drove was removed on 2026-08-17; the only key still read by the app is allow_private_profiles, which controls whether the private-profile toggle appears on the You tab.

{
  "rules": {
    "allow_private_profiles": true      // Show the private-profile toggle on the You tab
  }
}

2. Celebrate Page configuration

Controls event celebration styling (e.g. background effects, sound highlights, visual layouts, and color themes). Settings are saved under config_key = 'celebrate_config' in `leaderboard_config`.

Push Notification Engine

Push notifications allow organizers to alert users of progress milestone achievements (clubs, streaks, long walks, and daily updates) using W3C WebPush subscriptions.

1. WebPush Subscriptions Flow

1. Subscription Registration (POST /push/subscribe):
   The frontend portal requests push credentials from the browser. The resulting subscription object is posted to the backend along with the athlete_id and device_name.
   The backend inserts/patches the subscription JSON directly into the push_subscriptions table.
2. Removing Inactive Keys:
   If the WebPush dispatch (via webpush.sendNotification) fails with status 410 (Gone), the backend automatically deletes the inactive subscription record.

2. Trigger Keys and Default Templates

Rules are stored in `push_notification_rules`. If a rule lacks a custom override, the system falls back to default notification templates:

Database Schema & Fields

Below are the primary Supabase tables used for tracking and scoring activities.

1. registration (Athlete Details)

Stores the registered user profiles, shift details, and OAuth tokens.

• strava_athlete_id: Unique numeric id returned by Strava.
• full_name: Full name of the participant.
• emp_code: Unique employee code.
• shift: Athlete's work shift (e.g. Dayshift, Nightshift, etc.).
• access_token & refresh_token: API credentials used to pull data.
• strava_connected: Boolean flag showing active integration.

2. activities (Raw Activities)

Stores individual activity logs fetched from Strava API.

• activity_date_time_ist: Stores the local calendar date and time. **The primary field used for daily groupings on leaderboards.**
• activity_date: The raw UTC time stored for safety.
• start_time: Time portion parsed from the local date (e.g., 05:03:42).
• distance_meters: Float distance.
• moving_time_seconds: Float time duration.
• is_flagged: Indicates if the activity violates validation rules (e.g., pace too fast, overlapping times).

3. athlete_points_summary (Cached Standings)

Holds pre-calculated aggregated statistics for high-performance frontend loads.

• total_distance_km: Cumulative distance across the event.
• base_points: Base score awarded from active walking.
• challenge_points: Points earned from participating in specific challenges.
• bonus_points: Cumulative milestone/special day bonuses.
• total_points: base_points + challenge_points + bonus_points. **This cached value is queried by leaderboards.**

4. challenges (Challenge Targets)

Maintains dynamic active challenges.

• distance_type: Can be fixed (e.g., complete exactly 5km) or range (split-based logic).
• distance: The threshold target in km.
• points: The points rewarded if achieved.

API Endpoints

The backend server hosts the following crucial endpoints for syncing and administration:

Sync Operations

• POST /admin/refresh-strava-tokens: Iterates through registered athletes, checks oauth expiry, and retrieves fresh access tokens from Strava.
• POST /admin/backfill-points: Recalculates and caches points summaries for all registered participants. Runs when adjustments to milestones or past errors are made.
• POST /register: Webhook endpoint called by Strava's servers during athlete authorization.

System Settings

• GET /maintenance-status: Light API checking if the portal is in maintenance mode. Allows bypassing for administrators.
• PATCH /admin/maintenance: Admin endpoint to toggle maintenance mode, add whitelist IDs, and write system announcement text.

Common Fixes & Playbook

1. Recalculating Leaderboard Points

If points are showing incorrectly for a participant due to a configuration change or timezone update, run a full recalculation of points summaries:
Navigate to the Data Upload page and hit the Recalculate Summary button. This updates the cached table in Supabase.

2. Clear Cache Button

If frontend points are desynced from raw database values, click the Clear App Cache button in the admin banner. This clears cookies, sessionStorage, and deletes browser service-worker cache keys forcing a hard refetch of the stats API.

3. Repairing a Single Incorrect Activity Date

If an activity is placed under the wrong date (e.g. UTC offset desync), you can manually update it in Supabase:

UPDATE activities 
SET activity_date_time_ist = '2026-06-25T05:03:42' 
WHERE strava_activity_id = 19055574519;

Event Configuration & Settings (events.html)

The event manager (events.html) is the central dashboard where organizers configure event metadata, scoring systems, rules, layout settings, and challenges. These settings dictate how the frontend Participant Portal behaves and how activities are scored.

1. Structure & DB Mappings

When an organizer creates or saves an event, two main tables are updated in Supabase:
• events: Contains metadata like name, slug, event_type, status (draft, upcoming, live, ended, archived), start_date, and end_date.
• leaderboard_config: Stores detailed setting rules as JSON configurations associated with the event using config_key and event_id keys.

2. Tab Config Section Breakdown

3. Key Event Operations

• Cloning Events: Clicking the clone button copies the event details, challenge list, and custom configurations, creating a brand new draft event to reuse templates.
• Status Cycling: Clicking the status badge cycles the event through draft → upcoming → live → ended → archived sequentially (with confirmation).
• Asynchronous Points Backfill: Saving changes automatically fires a background recalculation request to the Backend Server at /admin/backfill-points. This backfill operation is triggered asynchronously in the background so that Render's startup latency does not freeze the UI or disable buttons.
• Save Button Fail-safes: Includes top and bottom Save buttons. Save actions use a finally block to guarantee that buttons are immediately re-enabled even if database operations fail.
• Manual Bonus Points Tab: Centralizes manual bonus points management per event. Organizers can directly award custom points to specific activities and view a log of the 50 most recent manual adjustments for that event.

Recent Changes & Updates

Below is the timeline of notable technical updates, performance improvements, and feature rollouts implemented in the application.

July 2026

• Splash Screen Reductions & Branding (July 16, 2026):
  • Reduced Walkathon chevron logo size for a cleaner native feel, and added "By UCC Team ❤️" brand footer at the bottom of the splash screens in both app.html and index.html.
• Arc Medal Prediction Fixes (July 16, 2026):
  • Corrected calculation logic inside renderMedalInsights so that ended events show 0 days remaining, preventing infinite/NaN pace targets, and properly displaying "Ended ❌" status if not achieved.
• Activity Streak Details Fallback (July 16, 2026):
  • Implemented direct database fetch fallback inside renderStreakDrawerDetails to dynamically load activities if global cache is empty, alongside timezone-aligned streak check logic.
• Flat Challenge Badges & Icons (July 16, 2026):
  • Replaced dynamic emoji challenge tags with modern, flat grey background pills (rgba(255,255,255,0.06)) and crisp white Tabler icons mapping sport types (walk, run, cycle, etc.) on the Dashboard and Challenges tabs.
• Unique Avatar Gradients (July 16, 2026):
  • Replaced hardcoded static orange gradients on initials-only avatars with unique, hash-generated gradient colors so that every participant profile is visually distinct.
• Color-Reduced Dashboard Cards (July 16, 2026):
  • Redesigned the "My Points" and "My Stats" dashboard cards, styling values in clean white with tiny colored indicator dots to limit the interface to a maximum of 3-4 primary colors.
• Dynamic Activity Metrics (Pace vs. Speed) (July 15, 2026):
  • Added smart activity classification to calculate and display speed in km/h for cycling/rides, and pace in minutes per kilometer (/km) for walking/running.
  • Dynamically adjusts the metrics headers and averages depending on the participant's primary sport type.
• Premium Time-Period Filtering (July 15, 2026):
  • Introduced sub-tab filters (Week, Month, 3 Months, Yearly) to the participant profile details.
  • Re-calculates and renders historical stats, dynamic SVG bar graphs (with custom group boundaries), and activity listings in real time across the selected duration.
• Native Swipe-to-Go-Back Gesture (July 15, 2026):
  • Implemented horizontal swipe touch gesture recognition (duration < 400ms, vertical displacement < 65px) across all detail modals.
  • Automatically binds swipe actions to the corresponding modal's back button, enabling frictionless navigation on mobile.
• Interactive Drawer Navigation Hooks (July 15, 2026):
  • Made the medals progress arc, active days chip, day streak badge, and 7-day mini-ring date cells clickable to open details directly.
• Whoop-Inspired Matte UI Theme:
  • Replaced transparent styling and backdrop blurs with a solid, high-contrast matte color scheme.
  • Updated root variables: Main background (#12161A), Primary Card (#1E2328), Secondary Surface (#272D34).
  • Eliminated borders and backdrop blurs on all participant portal cards (.card, .you-card, .date-group, .lb-row, .pb-card, etc.) for a clean, sharp look.
• Leaderboard Excel Export:
  • Added a dedicated export utility to download any leaderboard view directly into an Excel (.xls) spreadsheet with a single click.
• Events Manager Save & UI Fixes:
  • Refactored the events editor save handler to run the points backfill endpoint asynchronously.
  • Added a top Save button next to the editor title, converted all save buttons to type="button", and wrapped handlers in a try-catch-finally structure.
  • Integrated the **Manual Bonus Point by Activity** feature directly inside the event editor as a dedicated tab panel for clean event-scoped points management.
• Branding Selective Logo Inversion Filter:
  • Replaced brightness filters with filter: invert(1) hue-rotate(180deg), allowing black elements in logos to invert to white while preserving the orange brand color.
• JS/CSS Cache Busting:
  • Implemented cache-busting version rules for scripts and stylesheets loaded in app.html (e.g. app-dashboard.js?v=20260904_2100) to ensure updates are served instantly.
• Dynamic Score Units and Labels (July 13, 2026):
  • Added dynamic metric translation so that if an event has a raw scoring mode, all point trackers (arc chart title, next-milestone tracker, stat chips, You-tab medals) dynamically adjust to the metric unit (e.g. km for distance events) and label (e.g. Total Distance) instead of using hardcoded points/pts terminology.
• Mini Date Strip Event Ended Rollback (July 13, 2026):
  • If an event has ended, the 7-day mini-ring date strip rolls back to show the final 7 days of the completed event instead of the current system calendar dates.
• Uncompromising Browser Cache-Busting Redirects (July 13, 2026):
  • Appended dynamic timestamp cache-busters (_cb=Date.now()) to all redirects targeting app.html to completely bypass local browser disk/memory cache.
• Asynchronous Service Worker Purging on Boot (July 13, 2026):
  • Coordinated PWA cache cleaning script execution in index.html so it blocks login redirects until all Service Workers are fully unregistered and Cache Storage is deleted.

Prior Updates

• Progressive Web App (PWA) Support: Added full manifest, splash-screen icons, offline pages, and Service Worker integration to allow mobile install as a standalone app.
• Community Odometer & Pulse Feed: Realtime cumulative odometer and custom feeds showing participant milestones.