Mastery status lookup + learner progress models

[jesperhodge]

Mastery status lookup + learner progress models

User Story

As a learner, I want my current mastery status recorded for each competency, criteria
group, and individual criterion, in order to see which requirements I have already met and
which are still outstanding.

Acceptance Criteria

• 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 and ADR-0002 Decision 6, which lists created and modified on all three tables. Each table carries both created (auto_now_add=True) and modified (auto_now=True). No history package is applied. [BE] Implement CBE core data models (CompetencyTaxonomy, criteria, learner status) #613 cites this as "ADR-0003 Decision 5 as amended on 2026-07-27"; that changelog entry no longer exists, because ADR-0003 Decision 5 was rewritten again afterwards. The requirement is unchanged, only the citation.
• 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. Those two rules now live only in ADR-0004 Decisions 4 and 6. [BE] Implement CBE core data models (CompetencyTaxonomy, criteria, learner status) #613 also attributes them to ADR-0003 Decision 5, which no longer contains them.
• 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

• The foreign key from each of the three status models to its definition row (CompetencyCriterion, CompetencyCriteriaGroup, or oel_tagging_tag) is PROTECT, with no TODO comment attached. Two of the three point into Competency criteria models (authoring/definition layer) #641's tables and the third points into openedx_tagging. 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.
• This ticket owns every ProtectedError case in [BE] Implement CBE core data models (CompetencyTaxonomy, criteria, learner status) #613, including the ones Competency criteria models (authoring/definition layer) #641's prose describes, because asserting one needs a Student*Status row and this is the ticket that creates those three tables. Competency criteria models (authoring/definition layer) #641 tests only the cascade half of each case. The transitive cases are tested, not only the direct ones, since PROTECT is evaluated on every row the collector reaches rather than only on the row passed to delete().
• Deleting an oel_tagging_tag with a status row anywhere beneath it raises ProtectedError. Deleting one with no status rows beneath it still succeeds and removes the whole criteria tree.
• Deleting a CompetencyCriteriaGroup at depth with a leaf status row anywhere beneath it raises ProtectedError, and deleting one with no status rows beneath it succeeds.
• Deleting an oel_tagging_objecttag whose criterion has a leaf status row raises ProtectedError, and deleting one whose criterion has none succeeds and cascades that criterion away.
• Deleting an oel_tagging_taxonomy raises ProtectedError when a status row exists beneath any of its tags. Tag.taxonomy is already CASCADE in openedx_tagging, so the collector reaches every tag beneath the taxonomy and the tag case above holds transitively.
• 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

• 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. That count includes the three models django-simple-history generates for Competency criteria models (authoring/definition layer) #641 (HistoricalCompetencyCriteriaGroup, HistoricalCompetencyCriterion and HistoricalCompetencyRuleProfile), which are real Django models the annotation scan counts.

Description

This is one of three tickets implementing #613's model layer, and the last of the three to
merge, which is why it also carries the three gates that span the whole feature. Every
criterion above is one of #613's, or one of #613's narrowed to this ticket's models.
Nothing here is additional to the parent.

Technical Details

Background and a suggested approach, not the source of truth. The Acceptance Criteria
above define what must be true when the work is done; this section exists so an
implementer does not have to rediscover the surrounding context first.

In short

What these tables hold, and where the code goes. CompetencyMasteryStatuses is a small
lookup of the three possible status values. The other three tables record one learner's
mastery at the leaf, group, and competency levels of the tree #641 defines. They hold one
row per learner and node, updated in place, so finding a learner's current status is a
lookup of a single row rather than a query for the most recent of several. Earlier drafts
of this ticket described them as append-only; ADR-0003 Decision 5 has since been rewritten
and no longer does, on grounds of scale and because no pilot partner needs status history
for MVP. #641 turns src/openedx_learning/applets/cbe/models.py into a models/ package;
put these four models in a new models/learner_status.py inside it and export them from
models/__init__.py. Do not add a top-level models.py: after #641 that path is a
directory, and a branch still treating it as a file conflicts irreconcilably rather than
merging.

Why the status ordering has to be visible to the database. ADR-0004 Decision 4 says an
automatic update stores whichever is higher, the value already stored or the newly computed
one. Written 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. Written as a
single UPDATE ... WHERE current_status < new_status that race cannot happen, and a
database can only make that comparison if the ordering lives in a column it can sort rather
than in a Python constant. Whether you express that as a rank column or as deliberately
ordered primary keys is your call. This ticket only has to make the comparison expressible;
the rule about which writes are allowed is API-layer work and is out of scope.

Deletion, beyond what the criteria already state. The user foreign key is CASCADE
because 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, and because a learner status
row is a derived fact about that learner. SET_NULL was never a candidate: a null user_id
would break the (user_id, node_id) uniqueness the whole in-place-update design rests on.
Separately, the three PROTECT values into the definition tables are deliberately stricter
than the predicate the application layer uses. ADR-0002 Decision 7, as amended on
2026-09-01, names the leaf table as the single table that determines whether a record is
protected and treats the two roll-up tables as derived from it. The database makes no such
distinction. A roll-up row with no leaf row beneath it should never occur, but if one ever
does, the delete fails with ProtectedError rather than succeeding, and failing closed is
the right default for a backstop.

Implementation specifics

• Migration numbering. Migrations live in src/openedx_learning/migrations/. Competency criteria models (authoring/definition layer) #641 adds
  0002_competency_criteria and 0003_seed_default_rule_profile, so number these 0004
  (schema) and 0005 (seed) and set the first one's dependencies to Competency criteria models (authoring/definition layer) #641's last
  migration. Developing off main will naturally produce a clashing 0002 and 0003;
  renumber before merging or the app ends up with two migration leaves.
• Seed via a dedicated data migration, not fixtures or application code, and run it
  after the schema migration rather than folding it in.
• settings.AUTH_USER_MODEL precedent for both the foreign key and the
  swappable_dependency declaration: src/openedx_content/migrations/0001_initial.py.
  ADR-0002 Decision 6 says "auth_user table", but that wording is loose; a deployment can
  swap its user model.
• Companion work in openedx-platform, which no issue owns yet. That repo lists every
  openedx-core model individually in its own .annotation_safe_list.yml, because its
  .pii_annotations.yml sets source_path: ./ and the annotation scan never reads
  installed site-packages. Its only openedx_learning entry today is CompetencyTaxonomy,
  so the first openedx-core pin bump including Competency criteria models (authoring/definition layer) #641 and this ticket drops that repo's
  pii_check below its 100% target until ten entries are added: the seven models the two
  tickets create, plus the three Historical* models django-simple-history generates for
  Competency criteria models (authoring/definition layer) #641. File an issue there before the pin is bumped.
• Out of scope: the monotone-write rule and the staff-correction path (ADR-0004
  Decisions 4 and 6); the rollup celery task and the manual recovery command (ADR-0004
  Decisions 2, 3 and 5); all archive-versus-delete enforcement, which [Arch] Implementation approach for competency data delete/edit guardrails #655 governs and
  which lands in [BE] Build endpoint for removing a Competency Criterion #674, [BE] Build endpoint for removing a Competency Criteria Group #675, [BE] Add archived field to CompetencyCriteriaGroup and CompetencyCriterion #716, [BE] Add archived and deletion_locked fields to Tag, Taxonomy, and ObjectTag #776 and [BE] Exclude archived tagging records from read paths and block edits to them #778, with [Placeholder] Competency Criteria Deletions #799 closed as superseded; Competency criteria models (authoring/definition layer) #641's
  criteria models; and CBE app foundation + CompetencyTaxonomy model #640's taxonomy model, delivered by PR feat: provide openedx_learning djangoapp and CompetencyTaxonomy model #712.

Files to create and modify

New files

File	Purpose
src/openedx_learning/applets/cbe/models/learner_status.py	CompetencyMasteryStatuses and the three Student*Status models
src/openedx_learning/migrations/0004_learner_status.py	schema migration
src/openedx_learning/migrations/0005_seed_mastery_statuses.py	seeds the three status rows in rank order

Modified files

File	Nature of modification
src/openedx_learning/applets/cbe/models/init.py	export the four new models
tests/openedx_learning/applets/cbe/test_models.py	constraint, index and ProtectedError tests, including the transitive cases
.annotation_safe_list.yml	annotate the four new models as no_pii

Context

• Parent issue: [BE] Implement CBE core data models (CompetencyTaxonomy, criteria, learner status) #613. ADR-0002 Decisions 5, 6 and 7; ADR-0003 Decision 5; ADR-0004
  Decisions 4 and 6.
• Depends on Competency criteria models (authoring/definition layer) #641, which creates the tables two of these foreign keys point at, and on
  CBE app foundation + CompetencyTaxonomy model #640, delivered by PR feat: provide openedx_learning djangoapp and CompetencyTaxonomy model #712, for the app itself.
• Draft PR feat: add mastery status lookup and the three learner status models #802 already delivers part of this work: the lookup table,
  StudentCompetencyStatus, indexes 8 and 10, both migrations, read-only admin pages, and
  eleven tests run against both SQLite and MySQL 8.4. What remains is
  StudentCompetencyCriteriaStatus, StudentCompetencyCriteriaGroupStatus, indexes 6 and
  7, and the three whole-feature gates, none of which can be verified until Competency criteria models (authoring/definition layer) #641's tables
  exist.
• feat: add mastery status lookup and the three learner status models #802 diverges from three of the criteria above deliberately, each argued in that pull
  request: manual_date_time_field() in place of auto_now_add and auto_now, because
  DateTimeField.pre_save runs only on Model.save() and would leave modified stale on
  exactly the conditional-UPDATE path this ticket exists to enable; the singular class
  name CompetencyMasteryStatus; and idiomatic foreign key field names, so index 8 lands
  on (user_id, tag_id) rather than the ADR's literal (user_id, oel_tagging_tag_id).

Open Questions

• Do the three criteria feat: add mastery status lookup and the three learner status models #802 diverges from get amended to match it, or does feat: add mastery status lookup and the three learner status models #802 change to
  match them? The three are the created and modified field type, the singular
  CompetencyMasteryStatus class name, and the idiomatic foreign key field names, each
  argued in that pull request. Owner: whoever reviews feat: add mastery status lookup and the three learner status models #802.