Exercise Database Architecture

Schema design and exercise selection logic for the MoPlan system. Status: Design Complete - Implementation pending

---

Proposed Schema

-- Core exercise definition
exercises
|- id (uuid)
|- name (text) -- "Barbell Bench Press"
|- slug (text) -- "barbell-bench-press"
|- category (enum: compound, isolation, cardio, mobility)
|- movement_pattern (enum: push, pull, hinge, squat, lunge, carry, rotation)
|- primary_muscles (jsonb) -- ["chest", "front_delts", "triceps"]
|- secondary_muscles (jsonb) -- ["core"]
|- equipment_required (jsonb) -- ["barbell", "bench"]
|- equipment_optional (jsonb) -- ["rack"]
|- difficulty (enum: beginner, intermediate, advanced)
|- injury_considerations (jsonb) -- ["shoulder", "wrist"]
|- description (text)
|- created_at, updated_at

-- How to perform for different goals (primary modes)
exercise_execution_modes
|- id (uuid)
|- exercise_id (uuid, FK)
|- mode (enum: hypertrophy, strength, power, metabolic, endurance, eccentric, isometric)
|- tempo (text) -- "3-1-2-0"
|- tempo_description (text)
|- rep_range_low (int)
|- rep_range_high (int)
|- rest_seconds_low (int)
|- rest_seconds_high (int)
|- rpe_target (decimal)
|- intensity_percent_low (int) -- % of 1RM
|- intensity_percent_high (int)
|- focus_cues (jsonb)
|- common_mistakes (jsonb)
|- why_this_mode (text)

-- Intensity techniques that can be applied to exercises
intensity_techniques
|- id (uuid)
|- name (text) -- "Drop Sets", "Rest-Pause", etc.
|- slug (text) -- "drop-sets"
|- category (enum: set_extension, time_efficiency, metabolic, neurological)
|- protocol (text) -- How to perform
|- recovery_impact (enum: low, moderate, high)
|- experience_required (enum: beginner, intermediate, advanced)
|- compatible_modes (jsonb) -- ["hypertrophy", "strength"]
|- contraindications (jsonb) -- ["beginners", "fatigue"]
|- description (text)

-- Which techniques work with which exercises
exercise_technique_compatibility
|- id (uuid)
|- exercise_id (uuid, FK)
|- technique_id (uuid, FK)
|- effectiveness (enum: excellent, good, fair, poor)
|- notes (text)

-- Step-by-step instructions
exercise_instructions
|- id (uuid)
|- exercise_id (uuid, FK)
|- step_order (int)
|- phase (enum: setup, execution, return, breathing)
|- instruction (text)
|- cue (text) -- Short coaching cue
|- visual_url (text) -- Optional image/video

-- Exercise variations/alternatives
exercise_variations
|- id (uuid)
|- base_exercise_id (uuid, FK)
|- variation_exercise_id (uuid, FK)
|- variation_type (enum: easier, harder, equipment_swap, injury_friendly)
|- notes (text)

-- Goal-specific exercise rankings
exercise_goal_alignment
|- id (uuid)
|- exercise_id (uuid, FK)
|- goal (enum: body_recomposition, hypertrophy, strength, fat_loss, ...)
|- effectiveness_score (int) -- 1-10
|- priority (enum: essential, recommended, optional, avoid)
|- rationale (text)

---

Drizzle Schema (TypeScript)

import { pgTable, uuid, text, integer, decimal, jsonb, pgEnum, timestamp } from 'drizzle-orm/pg-core';

// Enums
export const exerciseCategoryEnum = pgEnum('exercise_category', [
  'compound', 'isolation', 'cardio', 'mobility'
]);

export const movementPatternEnum = pgEnum('movement_pattern', [
  'push', 'pull', 'hinge', 'squat', 'lunge', 'carry', 'rotation'
]);

export const difficultyEnum = pgEnum('difficulty', [
  'beginner', 'intermediate', 'advanced'
]);

export const executionModeEnum = pgEnum('execution_mode', [
  'hypertrophy', 'strength', 'power', 'metabolic', 'endurance', 'eccentric', 'isometric'
]);

export const techniqueCategryEnum = pgEnum('technique_category', [
  'set_extension', 'time_efficiency', 'metabolic', 'neurological'
]);

export const recoveryImpactEnum = pgEnum('recovery_impact', [
  'low', 'moderate', 'high'
]);

export const instructionPhaseEnum = pgEnum('instruction_phase', [
  'setup', 'execution', 'return', 'breathing'
]);

export const variationTypeEnum = pgEnum('variation_type', [
  'easier', 'harder', 'equipment_swap', 'injury_friendly'
]);

export const effectivenessEnum = pgEnum('effectiveness', [
  'excellent', 'good', 'fair', 'poor'
]);

export const priorityEnum = pgEnum('priority', [
  'essential', 'recommended', 'optional', 'avoid'
]);

export const goalEnum = pgEnum('goal', [
  'body_recomposition', 'hypertrophy', 'strength', 'fat_loss',
  'athletic_performance', 'endurance', 'general_fitness'
]);

// Tables
export const exercises = pgTable('exercises', {
  id: uuid('id').primaryKey().defaultRandom(),
  name: text('name').notNull(),
  slug: text('slug').notNull().unique(),
  category: exerciseCategoryEnum('category').notNull(),
  movementPattern: movementPatternEnum('movement_pattern').notNull(),
  primaryMuscles: jsonb('primary_muscles').$type<string[]>().notNull(),
  secondaryMuscles: jsonb('secondary_muscles').$type<string[]>().default([]),
  equipmentRequired: jsonb('equipment_required').$type<string[]>().notNull(),
  equipmentOptional: jsonb('equipment_optional').$type<string[]>().default([]),
  difficulty: difficultyEnum('difficulty').notNull(),
  injuryConsiderations: jsonb('injury_considerations').$type<string[]>().default([]),
  description: text('description'),
  createdAt: timestamp('created_at').defaultNow(),
  updatedAt: timestamp('updated_at').defaultNow(),
});

export const exerciseExecutionModes = pgTable('exercise_execution_modes', {
  id: uuid('id').primaryKey().defaultRandom(),
  exerciseId: uuid('exercise_id').notNull().references(() => exercises.id),
  mode: executionModeEnum('mode').notNull(),
  tempo: text('tempo'), // "3-1-2-0"
  tempoDescription: text('tempo_description'),
  repRangeLow: integer('rep_range_low'),
  repRangeHigh: integer('rep_range_high'),
  restSecondsLow: integer('rest_seconds_low'),
  restSecondsHigh: integer('rest_seconds_high'),
  rpeTarget: decimal('rpe_target', { precision: 3, scale: 1 }),
  intensityPercentLow: integer('intensity_percent_low'),
  intensityPercentHigh: integer('intensity_percent_high'),
  focusCues: jsonb('focus_cues').$type<string[]>().default([]),
  commonMistakes: jsonb('common_mistakes').$type<string[]>().default([]),
  whyThisMode: text('why_this_mode'),
});

export const intensityTechniques = pgTable('intensity_techniques', {
  id: uuid('id').primaryKey().defaultRandom(),
  name: text('name').notNull(),
  slug: text('slug').notNull().unique(),
  category: techniqueCategryEnum('category').notNull(),
  protocol: text('protocol').notNull(),
  recoveryImpact: recoveryImpactEnum('recovery_impact').notNull(),
  experienceRequired: difficultyEnum('experience_required').notNull(),
  compatibleModes: jsonb('compatible_modes').$type<string[]>().default([]),
  contraindications: jsonb('contraindications').$type<string[]>().default([]),
  description: text('description'),
});

export const exerciseTechniqueCompatibility = pgTable('exercise_technique_compatibility', {
  id: uuid('id').primaryKey().defaultRandom(),
  exerciseId: uuid('exercise_id').notNull().references(() => exercises.id),
  techniqueId: uuid('technique_id').notNull().references(() => intensityTechniques.id),
  effectiveness: effectivenessEnum('effectiveness').notNull(),
  notes: text('notes'),
});

export const exerciseInstructions = pgTable('exercise_instructions', {
  id: uuid('id').primaryKey().defaultRandom(),
  exerciseId: uuid('exercise_id').notNull().references(() => exercises.id),
  stepOrder: integer('step_order').notNull(),
  phase: instructionPhaseEnum('phase').notNull(),
  instruction: text('instruction').notNull(),
  cue: text('cue'),
  visualUrl: text('visual_url'),
});

export const exerciseVariations = pgTable('exercise_variations', {
  id: uuid('id').primaryKey().defaultRandom(),
  baseExerciseId: uuid('base_exercise_id').notNull().references(() => exercises.id),
  variationExerciseId: uuid('variation_exercise_id').notNull().references(() => exercises.id),
  variationType: variationTypeEnum('variation_type').notNull(),
  notes: text('notes'),
});

export const exerciseGoalAlignment = pgTable('exercise_goal_alignment', {
  id: uuid('id').primaryKey().defaultRandom(),
  exerciseId: uuid('exercise_id').notNull().references(() => exercises.id),
  goal: goalEnum('goal').notNull(),
  effectivenessScore: integer('effectiveness_score').notNull(), // 1-10
  priority: priorityEnum('priority').notNull(),
  rationale: text('rationale'),
});

---

Exercise Selection Logic

interface ExerciseSelectionInput {
  goal: Goal;
  equipment: string[];
  experience: ExperienceLevel;
  constraints: {
    excluded: string[];
    injuries: string[];
  };
  muscleGroup: string;
  sessionPosition: 'primary' | 'secondary' | 'isolation';
}

function selectExercises(input: ExerciseSelectionInput): Exercise[] {
  // 1. Filter by equipment available
  const available = exercises.filter(e =>
    e.equipmentRequired.every(eq => input.equipment.includes(eq))
  );

  // 2. Filter by excluded exercises/injuries
  const safe = available.filter(e =>
    !input.constraints.excluded.includes(e.id) &&
    !e.injuryConsiderations.some(injury =>
      input.constraints.injuries.includes(injury)
    )
  );

  // 3. Filter by difficulty (experience level)
  const appropriate = safe.filter(e => {
    if (input.experience === 'beginner') {
      return e.difficulty === 'beginner';
    }
    if (input.experience === 'intermediate') {
      return e.difficulty !== 'advanced';
    }
    return true; // Advanced can do all
  });

  // 4. Filter by muscle group
  const targeted = appropriate.filter(e =>
    e.primaryMuscles.includes(input.muscleGroup) ||
    e.secondaryMuscles.includes(input.muscleGroup)
  );

  // 5. Prioritize by goal alignment
  const ranked = targeted.sort((a, b) => {
    const aAlignment = getGoalAlignment(a.id, input.goal);
    const bAlignment = getGoalAlignment(b.id, input.goal);
    return (bAlignment?.effectivenessScore || 0) - (aAlignment?.effectivenessScore || 0);
  });

  // 6. Filter by session position
  if (input.sessionPosition === 'primary') {
    return ranked.filter(e => e.category === 'compound');
  }
  if (input.sessionPosition === 'isolation') {
    return ranked.filter(e => e.category === 'isolation');
  }
  return ranked;
}

---

Session Builder Logic

interface SessionTemplate {
  name: string;
  muscleGroups: string[];
  structure: {
    warmup: number; // minutes
    mainLifts: number; // number of exercises
    accessories: number;
    isolation: number;
    cooldown: number;
  };
}

function buildSession(
  template: SessionTemplate,
  input: ExerciseSelectionInput
): WorkoutSession {
  const session: WorkoutSession = {
    name: template.name,
    exercises: [],
    estimatedDuration: 0,
  };

  // Select main compound lifts
  for (let i = 0; i < template.structure.mainLifts; i++) {
    const muscleGroup = template.muscleGroups[i % template.muscleGroups.length];
    const options = selectExercises({
      ...input,
      muscleGroup,
      sessionPosition: 'primary',
    });

    if (options.length > 0) {
      session.exercises.push({
        exercise: options[0],
        sets: 3-4,
        reps: '6-10',
        rest: '2-3 min',
        order: session.exercises.length + 1,
      });
    }
  }

  // Select accessory compounds
  for (let i = 0; i < template.structure.accessories; i++) {
    const muscleGroup = template.muscleGroups[i % template.muscleGroups.length];
    const options = selectExercises({
      ...input,
      muscleGroup,
      sessionPosition: 'secondary',
    });

    // Avoid duplicates
    const unused = options.filter(e =>
      !session.exercises.some(se => se.exercise.id === e.id)
    );

    if (unused.length > 0) {
      session.exercises.push({
        exercise: unused[0],
        sets: 3,
        reps: '8-12',
        rest: '90 sec',
        order: session.exercises.length + 1,
      });
    }
  }

  // Select isolation work
  for (let i = 0; i < template.structure.isolation; i++) {
    const muscleGroup = template.muscleGroups[i % template.muscleGroups.length];
    const options = selectExercises({
      ...input,
      muscleGroup,
      sessionPosition: 'isolation',
    });

    const unused = options.filter(e =>
      !session.exercises.some(se => se.exercise.id === e.id)
    );

    if (unused.length > 0) {
      session.exercises.push({
        exercise: unused[0],
        sets: 2-3,
        reps: '10-15',
        rest: '60 sec',
        order: session.exercises.length + 1,
      });
    }
  }

  return session;
}

---

Seed Data Structure

Sample Exercise Entry

{
  "name": "Barbell Bench Press",
  "slug": "barbell-bench-press",
  "category": "compound",
  "movementPattern": "push",
  "primaryMuscles": ["chest", "front_delts", "triceps"],
  "secondaryMuscles": ["core"],
  "equipmentRequired": ["barbell", "bench"],
  "equipmentOptional": ["rack"],
  "difficulty": "intermediate",
  "injuryConsiderations": ["shoulder", "wrist"],
  "description": "The barbell bench press is a compound pushing exercise that primarily targets the chest, front deltoids, and triceps.",
  "executionModes": [
    {
      "mode": "hypertrophy",
      "tempo": "3-1-2-0",
      "repRangeLow": 8,
      "repRangeHigh": 12,
      "restSecondsLow": 60,
      "restSecondsHigh": 90,
      "rpeTarget": 7.5,
      "intensityPercentLow": 65,
      "intensityPercentHigh": 75,
      "focusCues": ["Squeeze at top", "Control descent", "Feel chest stretch"],
      "commonMistakes": ["Flaring elbows", "Bouncing off chest", "Lifting hips"],
      "whyThisMode": "Moderate weight with controlled tempo maximizes time under tension for muscle growth"
    },
    {
      "mode": "strength",
      "tempo": "2-1-X-0",
      "repRangeLow": 3,
      "repRangeHigh": 6,
      "restSecondsLow": 180,
      "restSecondsHigh": 300,
      "rpeTarget": 8.5,
      "intensityPercentLow": 80,
      "intensityPercentHigh": 90,
      "focusCues": ["Drive through heels", "Leg drive", "Explosive press"],
      "commonMistakes": ["Losing tightness", "Rushing between reps"],
      "whyThisMode": "Heavy loads with explosive concentric develop maximal strength"
    }
  ],
  "instructions": [
    { "stepOrder": 1, "phase": "setup", "instruction": "Lie on bench with eyes under bar", "cue": "Eyes under bar" },
    { "stepOrder": 2, "phase": "setup", "instruction": "Grip slightly wider than shoulder width", "cue": "Pinky on ring" },
    { "stepOrder": 3, "phase": "setup", "instruction": "Plant feet flat, slight back arch", "cue": "Big chest" },
    { "stepOrder": 4, "phase": "execution", "instruction": "Unrack with straight arms", "cue": "Lock elbows" },
    { "stepOrder": 5, "phase": "execution", "instruction": "Lower to mid-chest", "cue": "Touch nipple line" },
    { "stepOrder": 6, "phase": "execution", "instruction": "Press up in slight arc", "cue": "Back to start" },
    { "stepOrder": 7, "phase": "breathing", "instruction": "Breathe in on descent, out on ascent", "cue": "Down inhale, up exhale" }
  ],
  "goalAlignment": [
    { "goal": "body_recomposition", "effectivenessScore": 9, "priority": "essential" },
    { "goal": "hypertrophy", "effectivenessScore": 10, "priority": "essential" },
    { "goal": "strength", "effectivenessScore": 10, "priority": "essential" }
  ]
}

---

Remaining Tasks

• Finalize complete exercise list (~100 exercises)
• Determine video/image sources for exercise demos
• Define intensity technique compatibility matrix
• Create seed data for all tables
• Create Drizzle migration
• Build exercise selection API endpoints

---

Related Documents

• 02-ten-pillars.md - Science-based plan design
• 04-execution-modes.md - Training modes and techniques
• 06-implementation.md - Algorithm design

---

Last Updated: 2025-12-08