Metric Sets — Behavior

State machine

Stateless tables. The lifecycle is “create or record” → “read”:


metric_definitions: seeded → (no UI to mutate today)
metric_sets:        createSet (validate exactly-one-owner, validate definitions exist) → exists
metric_set_definitions: insert per definitionId, with sortOrder
member_metrics:     recordMemberMetric → row appended (append-only; reads pick latest by recorded_at DESC)

Invariants

Invariant	Enforcement
Each `metric_set` has exactly one owner among (member, workout, org)	DB CHECK `metric_sets_owner_exclusive_chk`
`metric_definitions.slug` is globally unique	DB unique on `slug`
Set ↔ definition junction has unique `(setId, definitionId)` pair	Composite PK
Coach cannot create an org-owned set for a different org	Service check on `dto.organizationId !== orgId`
Only staff can create metric sets	`isStaffRole` check

Golden paths

Coach attaches a metric set to a workout

In the workout builder, coach picks “Use metric set” → modal opens.
UI lists this org’s org-owned + workout-owned sets.
Coach creates a new workout-owned set: POST /metric-sets with { name, workoutId, definitionIds: [...] }.
Service: validates exactly-one-owner, validates definition IDs all exist, inserts the set + N junction rows in a transaction.

Assigning a workout to a member resolves the set

When a workout is assigned and contains % 1RM cues, the UI calls GET /metric-sets/:setId/resolve?memberId=....
Service joins the set’s definitions with the member’s latest member_metrics value per definition.
The UI uses value * percent / 100 to compute the prescribed weight.

Coach records a member’s 1RM back squat

Coach navigates to member → Performance metrics tab.
Records { definitionId, value, unit } via POST /members/:memberId/metrics.
Row appended; recorded_at defaults to now.
Future resolves use this as the latest value.

Edge cases

Scenario	Behavior
Member has multiple values for the same definition	Resolver picks the row with greatest `recorded_at` (index supports this)
Set references a definition that’s deleted	Junction has `ON DELETE RESTRICT` — definition delete blocked while referenced
Cross-org workout owns the set	Possible: workouts have their own org scoping; the set inherits via FK to a workout in a specific org
Two coaches record the same metric at the same instant	Both rows persist; resolver returns whichever has the later timestamp
Set owner type changed via DB	Should be forbidden — `metric_sets_owner_exclusive_chk` enforces 1-of-3

Side effects

Action	Writes
`createSet`	`metric_sets` insert; `metric_set_definitions` insert per def
`recordMemberMetric`	`member_metrics` insert
`resolve`	reads only

No events emitted.

Permissions

Action	Auth
List definitions / list sets / resolve	Active org membership (any role)
Create set / record member metric	Staff role