Skip to content

0024 device tables

github-actions[bot] edited this page Nov 18, 2026 · 5 revisions

The first tables

Status: accepted · Implementation: done · Date: 2026-11-18

The place LibreLifts ADR 0030 holds there; the tables are new. LibreLifts has workouts, sets, lift_state and settings. As built so far, 2026-11-18: the rows of the seven tables are in packages/contracts. That a load comes with a unit is not something the JSON schema says; the server checks it when it saves a row. On the device, 2026-11-18: the seven tables, the outbox and device_state are built, in one migration, and tested against the rows in the contract. The check on a load reads load is not null and unit is not null and unit in (kg, lb): without the middle part a load with no unit was accepted, because a check only fails on false and a comparison with null is null. The server needs the same. Finishing a workout keeps sets that were not ticked off, not done. The server tables are still to come, so this stays planned. Changed on 2026-11-18 with the screens: finishing a workout now drops the sets that were not ticked off (marks them deleted), so history holds what was done. The server tables were built on 2026-11-18, with the same check on a load, and are tested against the contract as the device tables are.

Context

The first version is routines, workouts, sets and history (0006), kept in SQLite on the device (0018) and synced by push and pull (0019). The shape of these tables is hard to change once apps are in use, because a synced column can be added but not renamed or removed.

LibreLifts put an exercise's place in the workout on each set. That cannot say "this exercise is in the workout and has no sets yet", which is the state right after adding one, and it cannot hold the same exercise twice.

Decision

Seven tables sync, and each row belongs to one person:

Table What a row is Its own columns
routines A routine the person made. name, position
routine_exercises An exercise in a routine. routine_id, exercise_id, position
routine_sets A planned set. routine_exercise_id, set_number, target_reps, target_seconds, load, unit
workouts One training session. routine_id, name, started_at, finished_at
workout_exercises An exercise in a workout. workout_id, exercise_id, position
sets A set, planned or done. workout_exercise_id, set_number, reps, seconds, load, unit, done_at
settings One setting, as a name and a value. key, value
  • Every synced row has the same five columns: id (a UUIDv7 made on the device, as text), created_at and updated_at by the device's clock, deleted_at, set instead of deleting, and server_version, empty until the server has seen the row.
  • Two tables never sync: outbox, one note per changed row, in order; and device_state, a few values about this device, such as the last version seen and whether backup is on.
  • A set has reps or seconds, according to how its exercise is counted. Both columns may be empty until the set is done. done_at is when it was ticked off.
  • A load is the number the person entered, with its unit (kg or lb) beside it. It may be negative, for help. load and unit are both set or both empty, and a stored load is never converted.
  • A time is a whole number of milliseconds.
  • finished_at is empty while a workout is in progress.
  • Exercises are referred to by their ids in packages/data (0023). The device has no table of exercises, and nothing in the database checks those ids.
  • A workout remembers the routine it was started from, but the routine may be deleted later, so nothing in the database ties the two.
  • Each child has a foreign key to its parent: a routine's exercises and their sets, a workout's exercises and their sets.
  • Deleting a routine or a workout marks its children deleted too, in the same transaction, each with its own outbox note.
  • A settings row gets its id from the setting's name (0019).
  • The server's tables mirror these in a Postgres schema of their own, with the account beside each row.
  • Until the first release these tables may still change. From then on a column can be added, never renamed or removed.

Consequences

  • An empty exercise and a repeated exercise are both possible.
  • 20 lb stays 20 lb. A total across sets is given per unit (0012).
  • A deleted row stays in the table with a mark. Every query that lists rows leaves marked rows out.
  • Reordering exercises changes one row for each exercise that moved.
  • "Last time" is not stored. It is read from the last finished workout that has the exercise, which costs a query each time an exercise is shown.
  • Seven tables is more to keep alike across the device, the contract and the server than LibreLifts' four.
  • An exercise a person adds themself has no home yet. It gets a table of its own, and its rows' ids can stand where a catalog id stands.

LibreBars

Clone this wiki locally