Sync Engine v2 — Database Design (built to absorb every future case)

Companion to sync-engine-v2-plan.md. This is the schema of record. Design goal: the DB structure is the durable part — new activity types, new sport groups, new ranking metrics, new leaderboard dimensions (team / company / region / anything), and periodic boards must all be config or query changes, never migrations.

Three design principles (why the schema survives the future)

1. Immutable log + materialized aggregate + derived periods. sync_activities is the append-only source of truth (one row per Strava activity, full raw + promoted columns). Every total is derived from it. sync_entity_totals is a materialized all-time cache for fast reads. Any weekly/daily/custom-window board is a query over sync_activities — no new storage needed. ⇒ Consequence: totals can always be rebuilt from the log; a config change can never corrupt history, and re-grouping is just a recompute.

2. Entity polymorphism — ONE totals table for every dimension. Legacy uses a separate table per dimension (individual / team / company). We use a single sync_entity_totals keyed by (entity_type, entity_ref). Adding "region", "age band", "gender", or any future leaderboard dimension is a new enum value + tally fan-out — zero new tables.

3. Metric-agnostic ranking. Totals carry the handful of real metrics as columns (distance, moving_time, elevation, calories, count); each group declares which metric it ranks by (event_activities.metric). Hours-mode, elevation challenges, activity-count challenges all fall out of one column choice — no schema fork per tracking mode.

---

CONFIG LAYER — per-event definition (source of truth for grouping)

strava_activity — global master catalogue (wire up: seed from 6 → full list)

col	type	note / future case
id	bigint PK
slug	varchar(64) UNIQUE	Strava sport_type string (Ride, VirtualRun, EBikeRide)
name	varchar(128)	friendly label ("Outdoor Ride")
default_context	enum('outdoor','indoor')	default indoor/outdoor hint
is_active	tinyint(1)	selectable in the editor
timestamps

Future: a brand-new Strava type Strava invents = one row here. Nothing else changes.

event_activities — named groups per event (= ranking dimension = frontend chip)

col	type	note / future case
id	bigint PK	referenced by totals as group_id
event_id	bigint
event_leaderboard_tab_id	bigint NULL	which tab the group shows under
name	varchar(128)	"Cycling", "Run/Walk"
slug	varchar(64)	stable chip key the frontend/URL uses
metric	enum('distance','moving_time','elevation','calories','activities') default 'distance'	what this group ranks by
is_overall	tinyint(1)	marks the whole-event "Overall" group (union of all counted)
enable	tinyint(1)
sort_order	int	chip order
timestamps
		UNIQUE(event_id, slug)

Future: any number of groups; hours-mode = metric='moving_time'; an elevation challenge = metric='elevation'. Overall is just a group with is_overall=1.

event_activity_map_strava — group membership (which Strava types feed a group)

col	type	note / future case
id	bigint PK
event_activity_id	bigint FK
strava_activity_id	bigint FK → strava_activity
context	enum('outdoor','indoor','both') default 'both'	count only when the activity matches
manual_allow	tinyint(1)	allow manual entries of this type into this group
timestamps
		UNIQUE(event_activity_id, strava_activity_id, context)

Future / overlap: the SAME strava type maps into many groups (many rows) — one activity then contributes to every group it belongs to. E-bike case: put EBikeRide in the Overall group but NOT in Cycling → counts to total, not to cycle, no code. Swim pool/open-water: two rows, context indoor vs outdoor. Allow-list = union of all enabled groups' memberships.

(Reused config, unchanged: event_leaderboard_tab (tabs + table_column JSON), sync_event_settings (engine/shadow/rules/connection), leaderboard_setting.)

---

RUNTIME LAYER — engine output

sync_activities — immutable per-activity log (SOURCE OF TRUTH) (exists; add one index)

Keep as-is (event_id, activity_id, tgp_userid, sport_type, distance, moving_time, total_elevation_gain, calories, start_date_local, status, raw JSON, evaluation cols…). Add:

INDEX sync_act_period (event_id, tgp_userid, start_date_local)   -- powers derived periodic boards

Future: every all-time total AND every weekly/daily/custom board derives from this table. Change a group definition mid-event → sync:rebuild recomputes everything from here. No data loss.

sync_entity_totals — THE unified materialized aggregate (NEW — replaces sync_totals/team/group)

col	type	note / future case
id	bigint PK
event_id	bigint
group_id	bigint NULL	FK event_activities; NULL = overall whole-event total
entity_type	enum('user','team','group','region')	the leaderboard dimension
entity_ref	varchar(191)	natural key: tgp_userid | tga_team_id | company name | region name
distance_km	double
moving_time_s	int
elevation_m	double
calories	double
activities_count	int
members_count	int NULL	teams/companies/regions (null for user)
last_activity_at	datetime NULL
rank	int	computed per the group's metric within (event, group, entity_type)
dirty	tinyint(1)	RankStep recompute flag
timestamps
		UNIQUE(event_id, group_id, entity_type, entity_ref)
		INDEX(event_id, group_id, entity_type, rank) ← the leaderboard read

Future — this one table covers ALL of: {overall, per-group} × {user, team, company, region}. A new dimension (age band, gender, corporate tier) = a new entity_type enum value + a tally fan-out line. No new table, ever. Any metric shown per row is already a column.

sync_period_totals — periodic cache (DEFERRED — build only if derivation is too slow)

Same shape + period_type enum('all','weekly','daily','custom'), period_key varchar(24) (2026-W32). Not built now; weekly boards derive from sync_activities on demand first.

(Ops tables, unchanged: sync_runs, sync_quota_samples, sync_alerts, sync_dead_letters, sync_activity_log, sync_settings_audit, sync_engine_state.)

---

Algorithms

Tally (fan-out). For each accepted activity:

1. Resolve entities of the actor: user=tgp_userid; team=team_users membership(s); group= event_users.group; region=event_users.subdistrict (each only if that dimension's tab is on).
2. Resolve groups: all event_activities whose membership contains the activity's sport_type honoring context/manual_allow, plus the is_overall group (NULL group_id).
3. For each (group × entity) upsert the sync_entity_totals row: add distance/time/elevation/calories, bump count, advance last_activity_at, set dirty=1. (metres→km once, here.)

Rank. For each (event, group_id, entity_type) with dirty rows: ROW_NUMBER() OVER (ORDER BY <metric-column> DESC) → write rank, clear dirty. Metric column chosen from the group's metric. Debounced (skip if ranked <60s ago).

Rebuild (sync:rebuild --event=). Truncate sync_entity_totals for the event, replay all counted rows in sync_activities through Tally, then Rank. Used after any group-definition change. This is the safety net that makes config edits fearless.

Periodic (derived). SELECT entity, SUM(distance) FROM sync_activities WHERE event_id=? AND status='counted' AND start_date_local BETWEEN ? AND ? GROUP BY entity ORDER BY 2 DESC. Cache in sync_period_totals only if measured cost demands it.

---

Every-future-case matrix

Future case	Handled by	Migration?
Strava adds a new activity type	strava_activity row	no
New sport group for an event	event_activities row + memberships	no
A type in several groups (overlap)	multiple event_activity_map_strava rows	no
Same type counted diff indoor/outdoor	context on membership	no
Manual entries per type/group	manual_allow + sync_activities.manual	no
E-bike counts to total but not cycle	in Overall group, not in Cycling group	no
Rank by time (hours events)	event_activities.metric='moving_time'	no
Rank by elevation / calories / activity count	metric value	no
Team leaderboard	entity_type='team'	no
Company / Impact-Partner board	entity_type='group'	no
Region / district board	entity_type='region'	no
A NEW dimension (age band, gender, tier)	new entity_type enum value + 1 tally line	no (enum alter only)
Weekly / daily / custom-window board	derive from sync_activities (opt. sync_period_totals)	no
Change group definition mid-event	sync:rebuild from the log	no
Multiple metrics displayed per row	already columns on sync_entity_totals	no
Qualification gate (donation)	donation-side qualified, merged at read	no
Audit / history / config change trail	sync_activity_log, sync_settings_audit	no

If a requested future case is NOT in this table, that is the signal to revisit the schema — the intent is that everything above is config/query, and only a genuinely new kind of data is a migration.

Migration from the current shadow schema

The shadow engine currently writes sync_totals + sync_team_totals with a binary cycling/running split. Because the engine is off / shadow and event 52 has no data, now is the zero-cost moment to switch:

1. Add sync_entity_totals (guarded migration). 2. Repoint TallyStep/RankStep to it (fan-out + metric rank). 3. Retire sync_totals/sync_team_totals writes (no live readers — sync-ops admin repoints to the new table). 4. Optional: expose a sync_totals-shaped VIEW over sync_entity_totals WHERE group_id IS NULL AND entity_type='user' if any transitional reader wants it.

Build order (schema-first)

• A1 seed strava_activity; A2 create event_activities + event_activity_map_strava + models; A3 admin group editor; A4 seed event 52 groups.
• B1 sync_entity_totals migration + index on sync_activities; B2 fan-out TallyStep; B3 metric-aware RankStep; B4 sync:rebuild; B5 shadow on 52.
• C WL read adapter reads sync_entity_totals (group_id + entity_type) → merge with donation.
• D go live on 52.