Sync Engine v2 — Dynamic Sport-Category Model + Activity↔Donation Contract

Synthesis of Phase 0 discovery: legacy crons, current v2 engine, and WL frontend needs. Goal: a leaderboard model dynamic enough to absorb any Strava activity type in future with zero schema/code change, and an explicit contract for how activity couples to donation.

What each side actually is today

Legacy (old prod) — two layers, one good, one bad:

• ✅ GOOD & already dynamic: the per-event allow-list event_leaderboard_activities (event_id, name=Strava type, type=outdoor|indoor, enable) → LeaderboardCronService. "Which types count" is already DB-driven per event. Keep this pattern.
• ❌ BAD & hardcoded: Stage3 calculateUserActivitiesData() collapses ~30 types into 3 PHP arrays → 3 fixed columns ($cyclingTypes, $runningTypes, Swim). Everything else counts only into total_distance. Ranks = per-column stored procs (UpdateRanks/UpdateHoursRanks/ UpdateDonationRanks in the TGP DB) + inline @row_number passes, per user_type.

Current v2 engine — improved validation, cruder sport model:

• ✅ v2 IMPROVEMENTS over legacy: local dedup in EvaluateStep (overlap/external_id/cross_user) replaces the external filter_duplicates.php dependency; suspicious scoring is per-sport in rules JSON (legacy hardcodes cid=37/ride). Allow-list = allowed_types/indoor_types JSON.
• ❌ v2 REGRESSION: DistanceBucket is binary (ride → cycling, everything else → running). Single overall rank. Worse granularity than legacy's 3 buckets.

WL frontend — needs far LESS than the legacy 60-col schema:

• Row contract: { rank, qualified, username{id,name,img_url}, donation_rank.completed, rank_cycle.completed, team_members_count, achievements_unlocked{…}, last_activity{…} }.
• rank_cycle.completed = ONE filter-selected distance; the chip decides which.
• Filter chips: fundraising | overall | cycle | run_walk. Which Strava types feed cycle vs run_walk is resolved server-side per event — the frontend already expects server-defined sport groups.
• Member drawer only: distance.completed / .cycling / .running.
• NO walking/swimming/indoor/outdoor/speed/calories columns anywhere in the UI.

Core proposal — normalized, config-driven categories (no column-per-sport)

1. Categories are DATA, not columns. A category = { key, label, metric, member_types[] }. Seeded default catalogue, per-event overridable (extends the existing allow-list idea). Frontend chips map to category keys. Default seed (ground-truth from legacy §2):

cycle    : metric=distance, types=[Ride,GravelRide,MountainBikeRide,VirtualRide]
run_walk : metric=distance, types=[Run,Walk,Hike,TrailRun,Wheelchair,VirtualRun]
swim     : metric=distance, types=[Swim]            # optional per event
# any Strava type not in a category still counts to the OVERALL total (never dropped)

Add a new type or a new category = edit config rows. No ALTER TABLE, no code.

2. Normalized totals (replaces fixed total_*_distance / rank_* columns).

• sync_totals — keep as the OVERALL aggregate: total_distance, activities_count, moving_time_total, total_calories, last_activity_at, rank (overall). Drop the running_distance/cycling_distance columns (superseded by the child table).
• NEW sync_category_totals(event_id, tgp_userid, category_key, distance, moving_time, activities_count, rank, dirty) — one row per (user, category). Index (event_id, category_key, rank) → the leaderboard query for any chip is one indexed read.
• NEW sync_category_team_totals(event_id, tga_team_id, category_key, …, rank, dirty).

3. Ranking generalized. RankStep computes overall rank (sync_totals) + a rank per category (sync_category_totals), each parameterized by metric (distance vs moving_time for hours-mode events like the soccer event). One loop over categories — no per-sport SQL passes, no stored procs.

4. Legacy policies become per-event RULE FLAGS (kill the cid==92/96/105 hacks):

• ebike_in_cycle: false — EBikeRide/EMountainBikeRide count to overall total but NOT the cycle category (legacy behaviour).
• manual_policy (already in v2) — manual excluded unless indoor event + allowed type.
• swim_split: pool=manual / openwater=gps — carry the Swim indoor/outdoor rule as a flag.
• metric: distance|time per event (or per category) — replaces the hardcoded event-47 time rank.
• host/excluded user ids → a config list, not an inline array.

Activity ↔ Donation contract (the coupling)

The leaderboard endpoint is fundamentally a JOIN of two independently-computed leaderboards:

Half	Owned by	Source (v2 event)
Activity distance + overall/category ranks	Sync Engine v2	sync_totals + sync_category_totals
Raised amount (donation_rank.completed) + qualified	Donation pipeline (separate)	challenge_donation_leaderboard (TGP) via donation recalc

• Every row merges both, keyed by tgp_userid. Default sort chip is fundraising (donation).
• qualified (donation-sourced) gates activity-distance visibility — not qualified → distance hidden, raised still shown.
• Recommendation: v2 owns ACTIVITY ONLY. Do NOT move donations into the sync engine — it stays in its own pipeline; the read-adapter joins the two per user. This respects the coupling without entangling two independently-cadenced systems.

Open questions (blocking design finalization)

1. Donation source for v3 events (>50): does a v3 event's donation leaderboard + qualified still live in TGP challenge_donation_leaderboard (written by the WL donation-recalc pipeline)? The adapter's merge depends on this. (Strong hypothesis: yes, unchanged.)
2. Category set for near-term v3: ship just overall + cycle + run_walk (matches today's chips) but on the dynamic engine, or seed swim/others now? Config-driven either way.
3. hours-mode / e-bike / swim-split: carry all three as generic flags now, or defer until an event needs them (event 52 is distance-mode)?
4. Denormalize for speed? Fully normalized (recommended) vs keep cycle/run distance columns on sync_totals as a fast-path for the two common chips.