Mastery status lookup + learner progress models

[jesperhodge]

Description

Parent: #613. This ticket implements the "Lookup data" and "Learner progress models" sections of #613: tracking each learner's actual progress toward demonstrating a competency. See #613 for full background.

Read ADR-0004 alongside ADR-0002 and ADR-0003. It is the reason the status rows are updated in place rather than appended, the reason the three learner status indexes are unique, and the reason the mastery status values need an ordering the database can compare. ADR-0003 Decision 5 was amended on 2026-07-27 to match it.

Every criterion below is one of #613's, or one of #613's narrowed to this ticket's models. Nothing here is additional to the parent.

What to build

One lookup table, three learner status tables, and two migrations.

CompetencyMasteryStatuses

A small lookup table of possible status values. Seed exactly three rows, and seed them in rank order
from lowest to highest: AttemptedNotDemonstrated, PartiallyAttempted, Demonstrated. Seed via a
dedicated data migration, not via fixtures or application code, so the seeding is auditable and
repeatable.

The rank order has to be visible to the database, not only to Python. That is a requirement, not a nicety,
and it comes from ADR-0004 Decision 4: an automatic status update stores whichever is higher, the value
already stored or the newly computed one. For that to be safe under concurrency it has to happen in a
single statement, roughly UPDATE ... WHERE current_status < new_status. Written instead as read, then
compare in Python, then write, two concurrent tasks can both read the old value and the later write
lowers what the earlier one raised, which is exactly the race ADR-0004 exists to prevent. A database
cannot compare two statuses if the ordering lives in a Python constant. Whether you express that as a
rank column or as deliberately ordered primary keys is your call; what matters is that the comparison is
expressible in SQL.

The three learner status tables

StudentCompetencyCriteriaStatus, StudentCompetencyCriteriaGroupStatus, and
StudentCompetencyStatus, tracking a learner's mastery at the leaf, group, and competency levels.

One row per learner and node, updated in place. Each table carries a unique constraint on
(user_id, node_id), plus both a created (auto_now_add=True) and a modified (auto_now=True)
timestamp. Finding a learner's current status is a lookup of the single row, not a query for the most
recent of several.

Earlier drafts of this ticket described these tables as append-only, one row per status change, with
current status resolved as the latest row. That was ADR-0003 Decision 5's original text, amended on
2026-07-27 for ADR-0004. Append-only was dropped because "the current status" then becomes a query for
the newest row, which cannot be made safe against concurrent writers cheaply, whereas a single row per
learner and node can be raised with one conditional UPDATE. The unique constraint is what makes that
single row true, so it is load-bearing rather than a lookup optimization.

No history package. django-simple-history goes on the criteria definition models in #641 only.

The user_id foreign key points at settings.AUTH_USER_MODEL, not auth.User, and the migration
declares migrations.swappable_dependency(settings.AUTH_USER_MODEL). ADR-0002 Decision 6 says
"auth_user table", but that wording is loose: Django lets a deployment swap its user model, and
hard-coding auth.User breaks on any deployment that has. This is the existing pattern in this repo, see
src/openedx_content/migrations/0001_initial.py.

The status restriction on StudentCompetencyStatus

A learner's overall competency status should never be AttemptedNotDemonstrated; only Demonstrated
and PartiallyAttempted make sense at that level. The foreign key to CompetencyMasteryStatuses does
not restrict which values are allowed, since any status id is a valid target.

Enforce it with a database check constraint, not in clean(). clean() is not enough here: Django only
calls it via full_clean(), which ModelForm and the admin do, but QuerySet.update() and
bulk_create() do not, and neither does a DRF serializer. Rollup code writes many learners' statuses at
once through exactly those bulk paths, so the one place this rule most needs to hold is the one place
clean() never runs. Tests should cover both a direct save and a bulk write.

Foreign keys and deletion

Every foreign key from these three status models to one of #641's definition tables is PROTECT, and
that value is final. It is also load-bearing rather than defensive, which is the part worth
understanding before you touch it.

#641's four definition-to-definition foreign keys are CASCADE, decided on #655 on 2026-09-02. So
deleting a Tag walks down into its criteria groups and then into their criteria, and deleting a group
walks down into its descendant groups and their criteria. The PROTECT on these status foreign keys is
the only thing that stops those walks. It is what turns ADR-0002 Decision 7's guarantee into behavior:
the delete succeeds when no learner holds a status beneath the row being deleted, and raises
ProtectedError when one does. Two of these three foreign keys point at rows one and two levels below
the tag, so they are reached transitively rather than directly. #675 re-implements the same predicate at
the application layer to return a clean status code instead of a 500; this is the backstop for the paths
that never reach #675.

PROTECT is evaluated on every row the collector reaches, not only on the row passed to delete().
That is why a transitive case works at all.

The user foreign key is CASCADE, not PROTECT. PROTECT there would let this library veto
User.delete() platform-wide, from code in openedx-platform that has no reason to know CBE rows
exist. A learner status row is a derived fact about that user, so removing it along with the user is the
right behavior. SET_NULL was never a candidate: a null user_id would break the (user_id, node_id)
uniqueness that the whole in-place-update design rests on. Draft PR #802 already ships CASCADE here.

The status foreign key to the mastery status lookup table stays PROTECT. That table holds
system-owned immutable data, seeded by migration and never deleted, so PROTECT stops a later migration
or an admin from removing a status value that live rows still reference.

StudentCompetencyStatus.tag, pointing at oel_tagging_tag, is PROTECT and is reached directly
rather than transitively. Once #641 lands, a Tag delete is guarded by three PROTECT values along the
new CASCADE chain: this one directly, plus the group and leaf status tables one and two levels down.

Everything else about deletion is out of scope. Do not override delete(), do not add an
archive-versus-delete branch, and do not add a deletion-lock field. #655's approved design enforces
archive-versus-delete entirely at the application layer, driven by a persisted lock flag on
oel_tagging_objecttag, which makes it an openedx_tagging change rather than a CBE one.

What is not in this ticket

The rules that govern which status writes are allowed live in the API layer, not in these models. The
models accept any status value the caller writes. Specifically, do not implement:

• The monotone rule, that an automatic update may raise a status but never lower it (ADR-0004 Decision
  4). A model-level check cannot enforce it, because by the time a write reaches save() there is no
  caller context left to tell an automatic write apart from a staff correction, which is explicitly
  allowed to lower a status (ADR-0003 Decision 5, ADR-0004 Decision 6). This ticket's job is only to make
  the comparison expressible in one SQL statement.
• The rollup, meaning the celery task that recomputes ancestor statuses after a leaf changes (ADR-0004
  Decisions 2 and 3), and the manual recovery command (Decision 5).

Migrations

Two, in order: a schema migration creating the four new tables (CompetencyMasteryStatuses plus the
three status models), then a separate data migration seeding the three CompetencyMasteryStatuses rows.
The seed must run after the tables exist; do not fold it into the schema migration.

Acceptance criteria (from #613)

Copied from #613. Only the criteria this ticket is responsible for are listed. As the last ticket to merge, this ticket also owns the gates that span the whole feature: all ten indexes present, make pii_check at 100%, and migrations applying cleanly from scratch. The archive-versus-delete enforcement criteria are not this ticket's; see the Deletions criteria below for the on_delete values it does set, all of which are final.

• The three mastery status values, AttemptedNotDemonstrated, PartiallyAttempted and Demonstrated, exist and their order is available to the database, so that raising a status can be written as one conditional UPDATE rather than a read followed by a write. A test asserts that a write of a lower value against a higher stored value changes no row, using a single statement.
• StudentCompetencyStatus rejects AttemptedNotDemonstrated and accepts only Demonstrated and PartiallyAttempted. The rejection holds on every write path, including QuerySet.update() and bulk_create(), which never call clean(). Tests cover a direct save and a bulk write.
• Learner status rows are updated in place, one row per learner and node under a unique constraint, per ADR-0003 Decision 5 as amended on 2026-07-27. Each table carries both created (auto_now_add=True) and modified (auto_now=True). No history package is applied.
• No monotone-write logic and no staff-edit path land here. The models accept any status value the caller writes; the rules that decide which writes are allowed, that an automatic write may raise a status but never lower it and that a staff correction may lower one, are enforced in the API layer.
• The indexes from ADR-0002 Decision 5 that belong to these models are present and unique: 6 (StudentCompetencyCriteriaStatus(user_id, competency_criteria_id)), 7 (StudentCompetencyCriteriaGroupStatus(user_id, competency_criteria_group_id)), 8 (StudentCompetencyStatus(user_id, oel_tagging_tag_id)) and 10 (CompetencyMasteryStatuses(status)). All four are unique; a plain index in any of those positions fails this criterion. For 6, 7 and 8 the uniqueness is not a performance detail: it is what makes "one row per learner and node" true, which is the precondition for the in-place updates above.
• The models added here are registered in .annotation_safe_list.yml (or annotated inline) as .. no_pii:. Each of the three StudentCompetency*Status models stores a user foreign key and a status value and no personal data of its own, which is how every existing openedx-core model with a user foreign key is annotated, openedx_content.PublishableEntity and Collection among them. pii_retirement: consumer_api is not used, because it asserts a consumer-facing retirement API that openedx-core does not have.
• No column exists on the models added here beyond those in ADR-0002 Decision 6, plus the constraints and the created and modified timestamps this ticket lists.
• All FK relationships match the ADR definitions exactly, targets included: the learner user_id points at settings.AUTH_USER_MODEL rather than auth.User, with migrations.swappable_dependency declared in the migration, so that deployments with a swapped user model still work.

Deletions

All archive-versus-delete enforcement logic from ADR-0002 Decision 7 is out of scope here. #655 governs
it, and it lands in whichever future issue implements it: #674 and #675 for the criterion and group
removal endpoints, #716 for the archived column on CompetencyCriteriaGroup and CompetencyCriterion,
and a not-yet-filed openedx_tagging ticket for the archived and deletion_locked columns on the
tagging models. #799, which previously held these criteria, is closed as superseded.

• Every foreign key from these three status models to one of Competency criteria models (authoring/definition layer) #641's definition tables is PROTECT, with no TODO comment attached. This is the mechanism that stops Competency criteria models (authoring/definition layer) #641's CASCADE chain, so it is load-bearing rather than defensive.
• The user foreign key on all three models is CASCADE. StudentCompetencyStatus.tag and the status foreign key to the mastery status lookup table are PROTECT.
• The transitive cases are tested, not only the direct ones. Deleting an oel_tagging_tag with a status row anywhere beneath it raises ProtectedError, and so does deleting a CompetencyCriteriaGroup at depth with a leaf status row beneath it. Deleting either with no status rows beneath it succeeds. These tests can only run once Competency criteria models (authoring/definition layer) #641's tables exist, so they belong in the slice that follows Competency criteria models (authoring/definition layer) #641.
• Deleting a user row removes that user's status rows across all three models, and a test covers it.
• No delete() override, no archive-versus-delete branch, and no deletion-lock field lands in this ticket. Nothing here implements deletion behavior in code. [Arch] Implementation approach for competency data delete/edit guardrails #655's approved design enforces archive-versus-delete entirely at the application layer, driven by a persisted lock flag on oel_tagging_objecttag, which changes openedx_tagging as well as CBE.

Whole-feature gates

This ticket merges last, so it also carries the three criteria from #613 that span more than one ticket.

• Migrations apply cleanly on top of Competency criteria models (authoring/definition layer) #641's migrations, and [BE] Implement CBE core data models (CompetencyTaxonomy, criteria, learner status) #613's "apply cleanly from scratch" is verified end to end across the three merged tickets.
• All ten indexes from ADR-0002 Decision 5 are present across the merged tickets: 1, 2, 4, 5 and 9 from Competency criteria models (authoring/definition layer) #641, 3 already satisfied by the existing db_index=True on ObjectTag.object_id, and 6, 7, 8 and 10 from this ticket.
• make pii_check passes with 100% coverage across every model [BE] Implement CBE core data models (CompetencyTaxonomy, criteria, learner status) #613 adds, not only the ones in this ticket.

Out of scope

The criteria models themselves, from #641. The taxonomy model, from #640, delivered by PR #712.

All archive-versus-delete enforcement logic, which #655 governs and which lands in whichever future
issue implements it: #674, #675, #716, and a not-yet-filed openedx_tagging ticket. #799, which
previously held it, is closed as superseded. See "Foreign keys and deletion" above for the on_delete
values this ticket does set, all of which are final.

Any REST API or UI work, including the monotone-write rule, staff corrections, the rollup task, and the
manual recovery command. See "What is not in this ticket" above.

Depends on

#641, because this ticket's foreign keys point into #641's tables. And #640, delivered by PR #712, for the app itself. This is the last of the three model tickets to merge, so it carries the end-to-end gates.