Workout results — behavior spec

Canonical numeric encoding

scoreNumeric is numeric(14,4) in the DB. Parsing happens in parseScoreInput(scoring, raw) — non-parseable strings are rejected with 400 rather than silently stored as null.

Per-set storage is fully canonical: weight_kg numeric(8,3), distance_m numeric(10,3), duration_seconds integer. The athlete-entered unit is preserved in weight_display_unit/distance_display_unit so the UI can format back to lb/mi/ft without re-converting and accumulating drift on subsequent edits.

Conversions (workout-results.service.ts:84+):

• toKilograms(value, unit) — lb/lbs → kg via * 0.453592.
• toMetres(value, unit) — km → 1000, mi → 1609.344, ft → 0.3048.
• toSeconds(input) — mm:ss, hh:mm:ss, or raw integer seconds.

PR detection algorithm (checkIsPR)

Runs on every new result whose workout has scoring !== 'none'.

prev_results = all this user's results for libraryWorkoutId, not deleted
current = the new result
others = prev_results minus current, with scoreNumeric != null

if others.isEmpty:
    persist PR (first time = PR)
    return true

lower_is_better = (workout.scoring == 'time')
is_best = for each r in others:
    current <= r.score  if lower_is_better
    current >= r.score  otherwise

if is_best:
    persist PR
return is_best

Persisted PR rows go into personal_records. Workout-level PR: keyed by (user_id, library_workout_id). Exercise-level PR: keyed by (user_id, exercise_id), written only when the workout has exactly one non-deleted movement AND scoring='weight'.

PR upsert is idempotent — upsertPR reads the existing row by partial unique index, updates value_numeric + achieved_at + workout_result_id if better, or inserts new.

Invariants

• Snapshot anchor. workout_results.snapshot_workout_id always references a workout row (library or snapshot). Library row used when no assignmentId; snapshot id used when assignmentId triggers a fork.
• Library denormalization. library_workout_id = snapshot.forked_from_id ?? snapshot.id. Lets per-canonical leaderboards run as a single indexed scan instead of walking the fork chain.
• Exactly one of personalRecords.exerciseId / libraryWorkoutId. DB CHECK personal_records_target_exclusive_chk.
• Soft delete only on results. deleted_at-filtered everywhere.
• Owner of the result. update / remove reject when existing.userId !== requester.id (403).
• Org scope. update / remove reject if existing.organizationId !== orgId (403).
• personalRecords upsert via partial unique indexes — personal_records_user_exercise_unique (WHERE exerciseId IS NOT NULL AND deleted_at IS NULL) and personal_records_user_workout_unique.

Golden path — log a result with sets

1. Member finishes Fran.
2. UI calls POST /organizations/:orgId/workouts/<snapshotOrLibraryWorkoutId>/results:

   {
     "assignmentId": "<assignmentId>",
     "scoreValue": "5:42",
     "rx": true,
     "scaled": false,
     "setResults": [
       { "exerciseId": "<thruster>", "setNumber": 1, "reps": 21, "weight": "42.5", "weightUnit": "kg" },
       { "exerciseId": "<thruster>", "setNumber": 2, "reps": 15, "weight": "42.5", "weightUnit": "kg" }
     ]
   }

3. Service resolves the workout in the URL, parses score (5:42 → 342 seconds for scoring='time').
4. If assignmentId: forkSnapshotIfNeeded returns the snapshot id (creating one on first edit/completion). snapshotWorkoutId and libraryWorkoutId set accordingly.
5. Inserts the workout_results row + workout_set_results rows (one per set).
6. UPDATE on the assignment (matched by assignmentId AND status='assigned'): status→completed.
7. Runs checkIsPR. If yes, upserts personal_records (workout-level; exercise-level if single-movement weight). Returns { ...result, isPR: true|false }.

Golden path — manual PR

1. Member visits profile → PRs → “Log PR”.
2. UI calls POST /personal-records/me { exerciseId: <backSquatId>, value: "120", unit: "kg", achievedAt }.
3. Service validates: exactly one of exerciseId / workoutId set, exercise resolves to canonical or this org’s row, value parses to number.
4. Upserts by partial unique index. workoutResultId set to null (manual entry).

Edge cases & error states

Side effects

• Lazy-fork of the snapshot (via forkSnapshotIfNeeded).
• Assignment auto-completion — UPDATE status→completed, completed_at set.
• PR upsert — insert or update personal_records.
• No push notification on result log or PR achievement at present.

Permissions