Exercise identity hard cutover

이 문서는 UUID hard cutover의 이력과 이식 계약을 기록한 ADR이다. 현재 종목의 의미·정식 결합 종목·연속 동작 세트·외부 provider 해석 정책은 lift-guild.exercise-model을 정본으로 삼는다.

Decision

Barbelic has one internal identity for one exercise: public.exercises.id, a database-issued UUID-formatted value. The same value is used by workout facts, plans, historical 1RM input, favorites, external imports, queues, projections, and screen read models.

There is no long-lived canonical-ID resolver. Legacy slugs and UI shorthand are accepted only by the one-time cutover migration. After cutover they are not a valid persistence or RPC boundary.

Identity model

public.exercises is the entity table.

• id uuid primary key is the only internal exercise identifier (#1175, 2026-09-03: the column and every referencing column moved from text to uuid; the type now guarantees the shape that a regex check used to). The original cutover kept the SQL carrier as text, which made the one-transaction hard cutover feasible without temporarily maintaining two ID columns; this is not permission to store slugs or aliases.
• System catalog IDs are deterministically generated from the reviewed slug during the one-time cutover so separate environments converge on the same opaque value. Runtime code still receives and stores the ID; it does not regenerate it from the slug.
• slug text unique not null is a stable URL and administration label, not a foreign key.
• Ownership and provenance are two columns (#1175, 2026-09-03 — the former origin column was derived from them and retired): owner_user_id is null for the official catalog and set for every user-owned row (in-app customs and imported exercises alike); source says where the row came from (barbelic, wodup, motra) and only non-barbelic rows carry source_ref (the provider's own key, unique per (owner_user_id, source, source_ref)). RPC responses still carry an origin key, derived as system (no owner) or user (owner); client adapters normalize a cached external value with the same rule.
• Renaming, translating, hiding, or changing a slug never changes id.
• An exercise with historical references is retired with is_active = false, not deleted. Retirement removes it from new-selection surfaces but does not invalidate or prevent reconstruction of existing facts.

Search aliases and taxonomy are descriptive metadata. An archetype such as row or a search term such as deadlift is not an exercise identity and must never be written to an exercise_id column.

Every persistent internal exercise reference is either:

• canonical UUID text with a foreign key to public.exercises(id); or
• a UUID-text array used only as a bounded refresh/query filter and validated against public.exercises before use.

Provider identifiers remain strings only at the external boundary:

(provider, provider_exercise_id) -- unique source identity
                  |
                  v
       exercise_id UUID text FK  -- internal identity

Several provider exercises may deliberately map to one internal exercise. The reverse is not inferred from names.

Provider datasets such as strength standards also store the resolved exercise_id. Their source_slug remains lineage only; the frontend never matches a standard to an exercise by slug, name, or alias.

User-created exercises

Custom exercises are first-class exercise entities, not local catalog patches.

1. The client calls create_custom_exercise(jsonb) through CatalogDomain.
2. The database authenticates the owner, validates measurement metadata, and inserts an owner-scoped exercise (owner_user_id = the caller). The client issues the exercise id (payload id, #1175 D3); resending the same id returns the existing row, a different payload under the same id is refused.
3. The RPC returns the persisted row and its UUID.
4. The client adds that returned row to its catalog cache.
5. Workouts, plans, PRs, and favorites may use the UUID only after step 3.

The client must not synthesize custom-*, slug-derived, timestamp-derived, or name-derived exercise IDs. A retry uses a client-supplied idempotency key at the DB RPC boundary; it does not mint a second identity. A custom exercise can be merged only by an explicit maintenance migration that rewrites every source reference and replays all derived models; there is no partial runtime merge RPC.

Two users may create exercises with the same displayed name; they remain different owner-scoped UUID entities and are never merged by that name. An owner-scoped UUID cannot be transferred to another account. Deleting an account removes its custom entities only after the same transaction has cascaded its sessions, plans, historical RM inputs, and favorites; deleting an exercise by itself remains blocked while any source fact still references it.

Write boundaries

The workout, completed-session update, plan, historical 1RM, favorite, admin, and import write paths all receive UUID exercise IDs. Database foreign keys are the final enforcement boundary. Name lookup is a picker concern and never a write fallback.

The browser treats IDs returned by trusted read models as opaque values. At an untrusted write boundary it additionally rejects values that are not canonical UUID text before making a request; the database check and foreign key remain authoritative.

Wodup and future providers resolve (provider, provider_exercise_id) through exercise_external_mappings before inserting workout facts. An unresolved provider exercise becomes a persisted, owner-scoped exercise (source = provider, source_ref = provider key) or stays in an import review queue; its provider string is never put into a fact table's exercise_id. Raw table RLS hides these staging entities. A sanitized catalog/detail row is exposed only after that account has a provider-verified workout association, and only a verified import can create the first association.

Changing an external mapping affects future imports only. It must not rewrite a subset of historical facts during an admin button action. Consolidating an already-used external entity into another exercise is an offline maintenance migration: fence writers, rewrite every source reference, discard projections, and replay them under one generation.

One-time cutover

The cutover is intentionally not a rolling dual-ID migration.

1. Stop or fence workout writers, import workers, and stats workers.
2. Create and manually review the complete old-ID-to-UUID map. Similar names do not imply equivalence; examples such as pull-up/strict-pull-up and the row archetype/barbell-row exercise require an explicit decision.
3. Create UUID catalog entities for system, user, external-placeholder, and orphan records.
4. Rewrite source-of-truth references first: completed workout exercises, planned sets, historical 1RM records, favorites, archetype representative exercises, and external mappings.
5. Add canonical UUID-format checks and exact foreign keys to their columns and all remaining internal references. Validate refresh/query ID arrays as UUID text before use.
6. Clear pending jobs whose payloads were produced under the old identity contract.
7. Delete derived exercise data and rebuild it from source facts: session rollups, period/month/all-time stats, measured PR events and states, PR summaries and snapshots, Home/Volume projections, and calendar summaries. PR event previous_value and delta are recomputed chronologically; derived IDs are never updated in place.
8. Validate that no raw row is orphaned, no logical source row was lost, all derived generations are current, and every read model has at most one row per (user_id, exercise_id, read-model key).
9. Remove the one-time map, old catalog rows, alias-array RPC parameters, and frontend canonicalization code before reopening writers.

The migration runs transactionally where PostgreSQL permits it. Worker fencing and replay publication use a new generation so readers never observe a mixture of old and new projections.

Read-model contract

Overview, detail, log table, volume, Home, calendar, and favorites expose the same UUID as exercise_id. They do not return alias rows and the frontend does not merge rows by slug or name.

• PR targets belonging to one exercise are fields or child rows of that UUID.
• Distinct exercises remain distinct even if a benchmark presentation groups them.
• Calendar exercise counts use distinct UUIDs.
• Log and volume filters accept UUIDs and compare them directly.
• A detail request uses one UUID, not a canonical UUID plus an alias array.

Home benchmark, PR summary, and onboarding membership are also data, not slug conventions embedded in the frontend. exercise_business_role_memberships stores each stable presentation/configuration key together with one exact exercise_id and an explicit order. Runtime queries join that UUID directly; the returned benchmark_key or onboarding_key selects presentation only and must never be used to recover identity from a slug.

Forbidden steady-state mechanisms

The following are cutover regressions:

• canonicalExerciseIdFromUiAlias, exerciseIdVariantsForUiAlias, or an equivalent runtime redirect table;
• using slug, exercise name, archetype ID, or provider ID as an internal exercise_id;
• localStorage as the source of truth for custom exercises;
• generating custom-${Date.now()} or slug-derived IDs in the client;
• last-row-wins or sum-by-alias merging in a screen mapper;
• rewriting IDs inside derived PR/stat rows without a full replay.

Search synonyms, old URL redirects, and provider mappings may remain, but they resolve outside persistence and must end at one UUID entity before a write.

Acceptance gates

The cutover is complete only when all of the following hold:

• every internal persistent exercise_id is UUID-backed and referentially valid;
• all public write RPCs reject names, slugs, archetypes, and provider strings in place of an exercise UUID;
• custom-exercise creation is DB-first and idempotent;
• external mappings are many-to-one and target UUIDs;
• source counts reconcile before and after migration;
• derived data was fully replayed and projection generations are current;
• overview, log, volume, and calendar contain no duplicate/alias exercise rows;
• Home benchmark, PR summary, and onboarding membership resolve through exact database role membership rather than slug or name matching;
• the frontend contains no runtime exercise-ID canonicalizer or alias fan-out.