Enhancing Workout Tracking: Granular Set Data with JSONB and React

For fitness applications, tracking more than just exercises is crucial for detailed performance analysis and progress. In the gymflow project, we recently implemented a significant upgrade to allow users to store and manage per-set data within their workout routines. This move from general exercise tracking to detailed set-level information enables richer analytics and more personalized workout experiences.

The Need for Granularity: Beyond Exercises

Previously, workout routines might only log an exercise, its weight, and reps. However, to truly understand performance variations, especially during a set (e.g., dropsets, supersets, RPE logging for each set), more granular data is required. This led us to enhance our data model to accommodate per-set details without overly complicating the main schema.

Database Evolution: Capturing Flexible Set Data with JSONB

To store this variable and often unstructured per-set data, we opted for a JSONB column. JSONB in PostgreSQL (and by extension, in our Supabase backend) is ideal for semi-structured data, allowing us to store diverse information for each set without rigid schema changes for every new data point. This provides immense flexibility for future feature expansions without requiring costly migrations.

We added a sets_data column to our routine_exercises table:

ALTER TABLE routine_exercises
ADD COLUMN sets_data JSONB;

-- Example of updating a routine exercise to include per-set data
UPDATE routine_exercises
SET sets_data = '[
  {"set_number": 1, "reps": 10, "weight": 100, "rpe": 7},
  {"set_number": 2, "reps": 8, "weight": 105, "rpe": 8}
]'::jsonb
WHERE exercise_id = 'some_uuid';

Type Safety with TypeScript

With the database schema updated, ensuring type safety in our application was the next critical step. We updated our TypeScript types for routine_exercises and routines to reflect the new sets_data field. This ensures that any data manipulation or display involving sets_data is type-checked, preventing common runtime errors and improving developer experience.

interface SetDataItem {
  set_number: number;
  reps: number;
  weight: number;
  rpe?: number; // Optional RPE for specific sets
  notes?: string;
}

interface RoutineExercise {
  id: string;
  exercise_name: string;
  // ... other exercise properties
  sets_data: SetDataItem[] | null; // Our new JSONB column mapped to an array of objects
}

// When fetching and using the data
const exercise: RoutineExercise = fetchExerciseData();
if (exercise.sets_data) {
  exercise.sets_data.forEach(set => {
    console.log(`Set ${set.set_number}: ${set.reps} reps @ ${set.weight}kg`);
  });
}

Building an Intuitive Per-Set Editor UI with React

The front-end challenge was to create an intuitive user interface for editing this granular sets_data. Using React, we developed a dynamic editor within Routines.tsx that provides a seamless experience across devices. Key UI elements include:

• Toggle Functionality: A simple toggle to switch between general exercise view and detailed per-set editing.
• Desktop Sub-Rows: For desktop users, per-set data is displayed in expandable sub-rows, keeping the main routine view clean while allowing deep dives.
• Mobile Collapsible Sections: On mobile, we implemented collapsible sections for each exercise, revealing the per-set editor when expanded, optimizing for smaller screens.

Finally, ensuring this new data was consistently available, we updated relevant SELECT queries in Rutina.tsx to explicitly fetch the sets_data column, preparing for future phases that will leverage this rich information.

Actionable Takeaway

When faced with evolving data requirements that might not fit a rigid relational model, consider JSONB columns in conjunction with strong typing (like TypeScript) and a flexible UI (like React). This combination allows for rapid iteration and rich data capture without sacrificing data integrity or developer productivity. It's a powerful pattern for building scalable, feature-rich applications in a dynamic environment.

Generated with Gitvlg.com