Rubik's TrainerCFOP practice for sub-20 solvers

Data model#

All models live in app/models.py. SQLAlchemy 2 declarative with typed Mapped[...] columns, one db.Model per table, no inheritance tricks.

The tables fall into three groups:

Library — shared by everyone, rebuilt from app/data/algorithms.json: case_group, case, algorithm, media.

Student data — owned by a profile, the thing you back up: profile, assessment, attempt, solve, star, focus.

Plans and sessions — training_plan, drill_session, drill_item, plan_progress, plus the case_score_cache denormalisation.

The diagram#

LIBRARY — SHARED case_group phase + key unique case key unique · setup_moves · mask algorithm moves · is_primary media image · video · youtube STUDENT DATA — PER PROFILE profile slug unique · goal_key · is_default assessment score 0–5 attempt ms · is_dnf solve total + 4 splits star the todo list focus case | group | phase plan_progress case_score_cache case_id PLANS AND SESSIONS training_plan config_json = block list drill_session kind · status drill_item one planned rep session_id on attempt solid = owning FK (cascade) dashed = reference across groups
Three groups of tables. Everything a student owns hangs off profile; everything shared hangs off case.

The diagram is hand-written inline SVG rather than a rendered Mermaid graph: the app must work with no network and vendoring a diagram library for four pictures is not a trade worth making. If you would rather have the graph in a tool, this is the same model in Mermaid ER syntax:

CASE_GROUP    ||--o{ CASE             : groups
CASE          ||--o{ ALGORITHM        : "solved by"
CASE          ||--o{ MEDIA            : "illustrated by"
ALGORITHM     ||--o{ MEDIA            : "illustrated by"
PROFILE       ||--o{ ASSESSMENT       : rates
PROFILE       ||--o{ ATTEMPT          : times
PROFILE       ||--o{ SOLVE            : records
PROFILE       ||--o{ STAR             : stars
PROFILE       ||--o{ FOCUS            : "focuses on"
PROFILE       ||--o{ DRILL_SESSION    : runs
PROFILE       ||--o{ PLAN_PROGRESS    : progresses
PROFILE       ||--o{ CASE_SCORE_CACHE : "scored for"
CASE          ||--o{ ASSESSMENT       : "rated in"
CASE          ||--o{ ATTEMPT          : "attempted in"
CASE          ||--o{ STAR             : "starred by"
CASE          ||--o{ DRILL_ITEM       : "practised as"
CASE          ||--o{ CASE_SCORE_CACHE : "cached for"
ALGORITHM     ||--o{ ATTEMPT          : "used in"
ALGORITHM     ||--o{ DRILL_ITEM       : "used in"
TRAINING_PLAN ||--o{ DRILL_SESSION    : drives
TRAINING_PLAN ||--o{ PLAN_PROGRESS    : "tracked by"
DRILL_SESSION ||--o{ DRILL_ITEM       : contains
DRILL_SESSION ||--o{ ATTEMPT          : produces

Vocabulary#

The docstring at the top of models.py is the canonical glossary:

phase
One of cross, f2l, oll, pll (PHASES). Only f2l, oll and pll own a case library (TRAINABLE_PHASES); cross is timed but solved intuitively.
case
A recognisable cube situation to be solved — OLL 21, the T-perm, an F2L pair in a given relative position.
algorithm
One move sequence that solves a case. A case has one or more.
assessment
A student's self rating, 0–5.
attempt
One timed execution of a single case.
solve
One full timed solve, optionally with phase splits.

Library tables#

case_group#

A named grouping inside a phase — OLL "T-Shapes", PLL "Adjacent corner swap".

Column Type Notes
id int PK
phase str(16) Indexed
key str(64) Unique within a phase (uq_case_group_phase_key)
name str(128) Display name
description text Optional
sort_order int Display order within the phase

cases is a selectin relationship ordered by Case.sort_order, so listing a group's cases costs one extra query rather than N.

case#

Column Type Notes
key str(64) Globally unique, indexed — oll-21, pll-t. This is the stable identifier used by the data file, the API and permalinks
phase str(16) Indexed
name str(128) OCLL7 — Double Cross
number int? The number on the source sheet
group_id FK → case_group ON DELETE SET NULL — losing a group must not lose cases
sort_order int Within the phase
probability str(32) As printed on the sheet, e.g. 1/108. A string because the sheets give fractions
setup_moves text Solved cube → this case. Validated at import
mask_json text Sticker mask for the thumbnail; see rendering
notes, source text/str Provenance

Two helpers worth knowing:

  • Case.mask — property wrapping mask_json through JsonMixin, so callers never touch JSON strings.
  • Case.primary_algorithm — the algorithm flagged is_primary, falling back to the first one. Never returns a surprise when the data is imperfect.

algorithm#

Column Type Notes
case_id FK → case ON DELETE CASCADE, indexed
moves text Singmaster notation
is_primary bool At most one per case, by convention
move_count int Denormalised for sorting and display
sort_order int Display order
label, notes, source Provenance and "good for a left-hand grip" style notes

media#

Images or videos attached to a case, and optionally to one specific algorithm (finger tricks differ per algorithm). kind is image, video or youtube.

Media can break the offline guarantee

media.url is free text. A youtube row points at the network by definition. The library ships with none, and the app degrades to a link rather than an embed when a URL is external.

Student tables#

profile#

Column Notes
slug Unique, indexed. Derived from the name and de-duplicated. Used by flask reset-profile
name Display name; renaming does not change the slug
goal_key Selects the row of targets.splits — sub_20 by default
is_default Exactly one profile is the default; it cannot be deleted
settings_json Free-form per-profile settings via the settings property

There is no password and no email. See Administration for what that means.

assessment#

One row per rating, history kept, newest wins. score is 0–5.

The composite index ix_assessment_profile_case_created on (profile_id, case_id, created_at) is what makes "the newest rating for this case" cheap, which the scoring model asks for constantly.

attempt#

One timed execution of one case.

Column Notes
case_id, algorithm_id Which case, and which algorithm you actually used
session_id FK → drill_session, nullable — attempts can happen outside a session
ms Milliseconds. Nullable, because a DNF has no meaningful time
is_dnf Counts as an attempt, excluded from every average
source drill, case, solve … where the attempt came from
scramble The scramble that produced the case, so the rep is reproducible

Indexed on (profile_id, case_id, created_at), the query the score model runs.

solve#

A full solve with optional CFOP splits, all in milliseconds: total_ms, cross_ms, f2l_ms, oll_ms, pll_ms. penalty is none, plus2 or dnf — stored as a flag alongside the raw time so the original is never lost.

Splits are nullable individually. A solve timed without splits is a perfectly good solve; it just does not contribute to the phase averages.

star and focus#

star is a unique (profile_id, case_id) pair — the todo list.

focus is deliberately looser: it holds case_id, group_id or phase, any of which may be null, so a focus can be "this case", "this group" or "all of OLL". Rows are additive; the drill generator unions them.

Plans and sessions#

training_plan#

config_json holds the ordered block list documented in Training plans. is_builtin marks the shipped plans, which cannot be edited but can be duplicated. author_profile_id is ON DELETE SET NULL, so deleting a coach's profile leaves their plans standing.

drill_session and drill_item#

A session is a header (kind, status, config_json, timestamps) with an ordered list of items. Each drill_item is one planned rep: case, algorithm, position, scramble, and — once done — ms, is_dnf, rating and completed_at.

Why both drill_item and attempt

They answer different questions. drill_item is the plan: fifteen reps, in this order, with these scrambles — it exists before you have done anything, and a skipped rep is a row with no completed_at. attempt is the record: every timed execution ever, whatever produced it, which is what the score model reads. Attempts made inside a session carry session_id; attempts from a case page do not.

plan_progress#

Unique on (profile_id, plan_id). state_json holds the position in the block list, so two students share a plan and keep separate progress.

case_score_cache#

Unique on (profile_id, case_id), indexed on profile_id. Holds score, self_score, best_ms, avg_ms, attempts, is_assessed and updated_at.

This is a cache, not a source of truth — every value in it is derivable from assessment and attempt. It exists so that sorting 119 cases by score is one indexed query. scoring.refresh_case_score(profile_id, case_id) is the only thing that writes it, and the test suite asserts that a rebuilt cache matches a freshly computed one.

Conventions#

Times are integer milliseconds everywhere in the database. Seconds and formatting are a presentation concern (RT.formatMs on the client). Only the config's split targets are floats in seconds, because a human types them.

Timestamps are timezone-aware UTC, via models.utcnow(). Never datetime.now().

JSON columns go through JsonMixin. It gives every model a _loads/ _dumps pair with a safe default, so a corrupt blob degrades to {} instead of raising in a template. Dumps are sorted and compact, which keeps diffs and tests stable.

Cascades are explicit and deliberate. Deleting a profile deletes everything it owns (CASCADE). Deleting a group or an author does not delete cases or plans (SET NULL). If you add a foreign key, decide which of those it is.

Migrations#

There are none. The schema is created with db.create_all() at startup, which adds missing tables and never alters existing ones.

Adding a table or a nullable column with a default is therefore safe: new installs get it, and existing ones pick the table up on restart (a new column on an existing table will not appear — SQLite needs the ALTER).

Anything else — renaming, dropping, changing a type, adding a NOT NULL column — needs a note in the release and a documented manual step. Say so loudly in the pull request, and see Contributing.

Source: docs/developer/data-model.md