Metric Sets — Data Model

Schema: libs/db/src/lib/schema/metric-sets.ts.

`metric_definitions` (global catalogue)

Column	Type	Notes
`id`	`uuid` PK
`slug`	`varchar(100)` UNIQUE	Machine identifier (e.g. `back_squat_1rm`)
`name_i18n`	`jsonb` NOT NULL	`{ en: "1RM Back Squat", he: "1RM סקוואט", ru: "1ПМ присед" }`
`unit`	`metric_unit` NOT NULL	kg, lb, %, seconds, …
`kind`	`metric_kind` NOT NULL	one_rep_max, time, distance, body_comp, …
`description`	`text`

Global (not org-scoped) — same vocabulary across the platform.

Column	Type	Notes
`id`	`uuid` PK
`name`	`varchar(255)` NOT NULL
`member_user_id`	`uuid`	FK `users(id)` CASCADE — set if owned by a member
`workout_id`	`uuid`	FK `workouts(id)` CASCADE — set if owned by a workout
`organization_id`	`uuid`	FK `organizations(id)` CASCADE — set if org-owned

Constraints:

metric_sets_owner_exclusive_chk — exactly one of the three owner FKs is non-null.

Indexes (all partial WHERE owner field IS NOT NULL):

Column	Type	Notes
`set_id`	`uuid` NOT NULL	FK CASCADE
`definition_id`	`uuid` NOT NULL	FK RESTRICT (can’t delete a definition while still referenced)
`sort_order`	`int` NOT NULL DEFAULT 0

PK: (set_id, definition_id).

Column	Type	Notes
`id`	`uuid` PK
`member_id`	`uuid` NOT NULL	FK `users(id)`. NB: column is on `users.id`, not `memberships.id` — values follow the person, not their gym tenure
`definition_id`	`uuid` NOT NULL	FK `metric_definitions(id)`
`value`	`numeric(14,4)` NOT NULL
`unit`	`metric_unit` NOT NULL
`recorded_at`	`timestamptz` NOT NULL DEFAULT now
`source`	`varchar(50)`	e.g. `coach_input`, `self_report`, `workout_result`

Indexes:

member_metrics_member_def_recorded_idx on (member_id, definition_id, recorded_at DESC) — hot path for “latest value”.
member_metrics_definition_idx on (definition_id, recorded_at DESC) — “everyone’s most recent 1RM”.

metric_definitions is global — same back_squat_1rm slug across all gyms.
metric_sets is owner-scoped (org / member / workout). Member-owned and member metrics follow the user across orgs (data lives at user-id, not membership-id).
Resolution endpoint: org-scoped by route, but member_metrics is fetched by user-id — staff in org A could in principle see a member’s metrics they recorded in org B. Cross-org leak risk worth a security review.