Four kinds of set, one flat table

A workout is not a list of exercises. A session is singles, supersets, trisets and dropsets, and those four things nest differently. A superset is two exercises you alternate. A dropset is one exercise where a single set collapses through three weights without rest. Get the first wrong and the routine won’t render; get the second wrong and you lose what was actually lifted.

The instinct is a table per shape: supersets, dropsets, each with its own columns and its own join. The Forge pushes the structure into columns instead, and ends up with two tables for the whole routine.

The group is an array

CREATE TABLE public.routine_group (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    routine_id uuid NOT NULL,
    group_type character varying,
    group_order integer DEFAULT 0,
    exercises character varying[] DEFAULT '{}'::character varying[] NOT NULL,
    note text,
    rest_time integer
);

exercises is a Postgres array of exercise ids. A single is a group of one, a superset a group of two, a triset three. group_type is a label for the UI; the array length is what actually carries the shape. Order within the group is the array’s own order, so reordering a superset rewrites one column instead of shuffling order_index values across rows.

The set is flat, and drop_index is nullable

CREATE TABLE public.routine_sets (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    routine_id uuid NOT NULL,
    group_id uuid,
    exercise_id character varying NOT NULL,
    set_number integer NOT NULL,
    drop_index integer,
    reps character varying,
    weight character varying
);

Every set of every exercise in every group lands here. set_number is which set it is. drop_index is the one doing the work: null for an ordinary set, and 1, 2, 3… for the rungs of a dropset.

So a normal set is one row. A dropset’s third set, dropping through three weights, is three rows sharing set_number = 3 and differing by drop_index. There is no dropsets table, and the write path is a single branch:

if (set.drops) {
  set.drops.forEach((drop, dropIdx) => {
    setRows.push({ ...base, set_number: idx + 1, drop_index: dropIdx + 1 })
  })
} else {
  setRows.push({ ...base, set_number: idx + 1, drop_index: null })
}

Reassembling it in one round trip

Flattening moves the cost to reads, so reads go through one SQL function, get_routine_full, which returns an entire routine as nested JSON. Two parts of it matter.

Expanding the group array back into rows, keeping position:

FROM routine_group rg,
    unnest(rg.exercises) WITH ORDINALITY AS ex_list(exercise_id, ordinality)
WHERE rg.routine_id = p_routine_id

WITH ORDINALITY is what makes the array pay off. It hands back each element’s position alongside the element, so the group’s ordering survives the round trip without ever being stored as a column.

Then folding set rows back into sets, bucketed by (group, exercise, set_number):

jsonb_agg(
    jsonb_build_object('drop_index', rs.drop_index, 'reps', rs.reps, 'weight', rs.weight)
    ORDER BY rs.drop_index NULLS FIRST
) AS parts,
bool_or(rs.drop_index IS NOT NULL) AS has_drops

has_drops decides which shape comes out: a set with a nested drops array, or a plain {set_number, reps, weight}. The client receives a whole routine in one call and maps it straight onto component props.

The thing that broke

There’s a comment in the middle of that function that exists because of a bug:

-- match sets by both group_id and exercise_id so sets belong to that specific occurrence
LEFT JOIN sets_grouped sg ON sg.group_id = ge.group_id AND sg.exercise_id = ge.exercise_id

Joining on exercise_id alone is the version you write first, and it’s correct for every routine where each exercise appears once. Put dumbbell curls in a warm-up superset and again as a finisher, and both occurrences claim every set row for that exercise, so the finisher silently inherits the warm-up’s weights.

The fix lives in the join, but the cause is the schema: a row in routine_sets has no idea which occurrence it belongs to except through group_id. Flattening bought simplicity on write and handed the reassembly a problem to be careful about.

What I’d do differently

Saving a routine deletes it first. The update path does what the heading says:

await supabase.from('routine_group').delete().eq('routine_id', routineId)
await supabase.from('routine_sets').delete().eq('routine_id', routineId)
// ...then re-insert every group and every set

Every save throws away every group and set and writes fresh rows with fresh UUIDs. It’s simple, and it lets the update path share all its code with create. It’s also three network calls with no transaction around them. If the insert fails after the delete lands, the routine survives with nothing in it. The read already goes through a single Postgres function; the write should too.

It also settles a question this project invites: the history isn’t there. Editing last week’s plan overwrites it rather than recording a change. Keeping the plan as a template and each performance as its own immutable row is the change I’d make first, and it’s larger than it sounds, because routines is currently doing both jobs at once.

reps and weight are strings. character varying, holding values like "135 lbs". It made the form trivial: whatever gets typed is what gets stored, units and all. The bill arrives the moment you want to ask anything numeric: total volume for a session, whether a lift is progressing, a chart of anything. None of that is arithmetic you can do in SQL on "135 lbs". A numeric column with the unit held once on the routine would have cost an afternoon.

The flat table I’d keep. drop_index is the rare nullable column that encodes a shape rather than hiding a missing value, and one function returning a whole routine has never been the slow part. The two mistakes above are the same mistake wearing different clothes: treating a routine as a document to overwrite instead of a record of something that happened.