Repository navigation
[ARCH] Plan for scaling Competency Criteria Group associations across recurring course runs #676
Description
Use Case
As an instance operator running Open edX with courses that rerun every term, I want a validated backend design for handling the accumulation of CompetencyCriteriaGroup records across course reruns, so that the competency authoring API, and any other consumer of the same data (Studio, Competency Management, future Libraries integration, plugin developers calling the API directly), stays performant as terms accumulate.
Description
Current behavior: No CBE code exists yet; this is greenfield work. Per docs/openedx_learning/decisions/0002-competency-criteria-model.rst (Decision 2), a competency's full criteria expression (CompetencyAchievementCriteria) is a tree of CompetencyCriteriaGroup nodes: one root group plus descendant groups and leaf CompetencyCriteria rows, nested up to a frontend-enforced depth of 3 (root, course-scope, leaf/group). A group is scoped to zero courses (course_id null) or to exactly one course run (course_id is a foreign key to openedx_catalog_courserun, a specific run, not a parent course). Because of this nesting, a single course-scoped criteria subtree is not one row: it can already contain multiple CompetencyCriteriaGroup rows.
It is intentional design, not an open question, that a course rerun gets its own new CompetencyCriteriaGroup rows and child CompetencyCriteria rows: these must associate to that specific course run. Every rerun of a course therefore multiplies the number of groups already present for a given competency. A heavily-reused competency, for example a college-wide Gen Ed competency attached to many courses that all rerun every term, can accumulate a large and continuously growing number of groups.
ADR 0002 Decision 2 already commits to: no DB-level row cap on CompetencyCriteriaGroup (growth is expected), pagination being available for authoring/list APIs, and evaluation/read paths being windowed by course run dates by default. What is not yet designed is the concrete backend mechanism needed to keep the Studio authoring panel that lists a competency's groups (see companion UX ticket) both performant and usable at this scale.
Requested change: This spike's central deliverable is a backend implementation plan that can deliver whatever the companion UX design spike decides the authoring panel needs: windowing, filtering, sorting, pagination, some combination of these, or another mechanism entirely. Concretely:
- Once the companion UX spike's decision is documented, translate it into a concrete backend design: the query shape, any index changes, and the API contract (parameters and response shape) needed to deliver it. This is the central question this spike exists to answer.
- Independent of that decision, review whether ADR 0002 Decision 5's proposed indexes (
CompetencyCriteriaGroup(oel_tagging_tag_id, course_id),(parent_id)) are sufficient for the general "list every group for a competency" access pattern, and document any gaps. - Provide a rough order-of-magnitude estimate of how large this can get in the worst case (for example, the Gen Ed scenario), so the design in (1) is checked against a realistic scale, not just the common case.
Out of scope:
- Implementing the design. This spike documents the plan; a future ticket implements it.
- Evaluation-time recomputation performance: a separate read path per ADR 0002 Decision 2, already addressed there.
- The authoring panel's own frontend implementation. The companion UX ticket documents the interaction design; building it is a separate, later frontend-app-authoring ticket.
- Load testing or benchmarking against synthetic or production-scale data. Not required by this project's contractual scope.
Acceptance Criteria
This is a design spike. The deliverable is design artifacts and documented decisions, not working code. There is no Gherkin and no QA path.
- Document a concrete backend design (query shape, index changes if any, API contract covering parameters and response shape) that delivers the mechanism decided by the companion UX ticket (windowing, filtering, sorting, pagination, or a combination).
- Review ADR 0002 Decision 5's proposed indexes against the general "list every group for a competency" access pattern; document any gaps.
- Document a rough order-of-magnitude estimate of worst-case group count (for example, the Gen Ed scenario), stating assumptions.
- Decide whether the authoring/list endpoint reuses ADR 0002's evaluation-path date-windowing default or needs its own, with rationale.
- Document the decision, and note whether an ADR 0002 amendment is warranted to record it.
- Resolve and document every entry in Open Questions below.
- Do not include load testing, benchmarking, or a proof-of-concept in this spike's scope.
Open Questions
- [BLOCKING, depends on companion UX ticket] What mechanism does the frontend need: windowing, filtering, sorting, pagination, or a combination? Most of this ticket's design work cannot proceed until that decision is documented. (owner: designer, via companion ticket)
- Is an ADR 0002 amendment warranted to record the finalized backend design? (owner: architect)
Context for the Architect
Most of this spike's substantive design work is gated on the companion UX ticket's decision (see Open Questions, above). The index review and the rough sizing estimate can proceed in parallel; the concrete query/API design cannot be finalized until the UX decision lands.
Existing precedent worth reviewing once that decision is known, not a prescribed solution: src/openedx_tagging/ already solves a structurally similar problem (a taxonomy with a large number of tags).
src/openedx_tagging/rest_api/paginators.pydefines multiple pagination classes tuned to different list sizes (TaxonomyPagination,TagsPagination,DisabledTagsPagination), andTaxonomyTagsView(src/openedx_tagging/rest_api/v1/views.py, roughly lines 659-880) switches between them at request time based on a caller-supplied size threshold.src/openedx_tagging/api.py's in-process functions return lazy, unmaterialized querysets withselect_relatedat hot paths, leaving pagination to the REST view layer.docs/openedx_tagging/decisions/0003-tagging-tree-data-arch.rstaddresses a similar tree-scaling question for the tagging model.
Which of these patterns fits, if any, depends entirely on the companion ticket's UX decision. Don't assume a specific technique (caching, for example) is available or the right fit without first confirming it against how this instance of Open edX actually handles comparable problems today.
Context
docs/openedx_learning/decisions/0002-competency-criteria-model.rst(Decision 2, Decision 5)docs/openedx_learning/decisions/0001-competency-criteria-location.rst(code location:src/openedx_learning/applets/cbe)src/openedx_tagging/rest_api/paginators.py,src/openedx_tagging/rest_api/v1/views.py(TaxonomyTagsView) — precedent referenced abovedocs/openedx_tagging/decisions/0003-tagging-tree-data-arch.rst- Companion ticket: UX design spike for the "Demonstrate Mastery For" panel's interaction design. Most of this ticket's design work depends on that ticket's decision. Link the two as a GitHub parent/child issue pair once both are created; this is the parent.
Activity
mgwozdz-unicon commentedon Aug 31, 2026
@ormsbee @kdmccormick @bradenmacdonald @jesperhodge Let me know if you have any concerns with this or if you approve.
Proposal
(Drafted together with Claude.)
Backend design for scoping the Competency Criteria Associations panel to whatever course runs are currently visible in the course-browse panel below it (#670, backed by #669, currently nearly through review). The panel gets no independent pagination or filtering of its own: it takes the course keys #669/#670 already resolved, through title search, date range, and pagination, and filters by them. Root/instance-wide groups (
course_id is null) are always included regardless of which courses are on screen.This needs an additive extension to #681's endpoint (via #682), no new indexes, and a scoped ADR 0002 amendment.
API contract
Extend #681 with an optional
course_keysquery param: a comma-separated list of course run keys, matching the already-shipped blocked-courses endpoint's convention and this codebase's.dict()-based query-param handling.course_keyshas two modes:course_keys. At zero keys (?course_keys=), that rule still holds, it just yields root plus nothing, not a separate case to special-case in the spec. This is the state you're in when the user searched for courses that don't exist or the request for courses hasn't come back from [BE] Add start-date range filter and outline delivery for accessible courses (Competency Criteria Associations) #669/[FE] Competency Management Course Search/View #670 yet.course_keysThe frontend should always send the param, never omit it, so it never has to distinguish "empty" from "absent."
Response adds two fields, both counts of
CompetencyCriterialeaf rows:criteria_count: count within the currentcourse_keysscope plus root. The numerator.total_criteria_count: unfiltered instance-wide count for the competency. The denominator.Course-scoped subtrees in the response are keyed by
course_key, not the integercourse_id, perCourseRun's own docstring against exposing its primary key.Cap
course_keysat 100, ten times the browse panel's current page size of 10, and reject over the cap with a 400. This also keeps subtree-completeness free: no page boundary can split a subtree, so #681 doesn't need result-level pagination at all, despite Decision 2's current "pagination is supported for authoring/list APIs" line (see ADR amendment, below).Edge cases:
CourseRunis silently dropped, not an error (covers the [BE] Add start-date range filter and outline delivery for accessible courses (Competency Criteria Associations) #669-then-[BE] GET Competency Criteria & Groups #681 race if a run is deleted in between).criteria_count: 0, total_criteria_count: 0(drives the empty state).criteria_count: 0with a non-zerototal_criteria_count, which is exactly what tells the admin the view is partial rather than the competency being empty.Ordering: root subtree first, then course-scoped subtrees by
course_key, siblings byordering(semantically meaningful for short-circuit evaluation). If the panel needs to visually match the course list below it, that's a client-side reorder using the key list it already has.Query shape
This is the query plan inside #681's competency-criteria endpoint, once
course_keysarrives already resolved by #669/#670. Four queries, constant in count regardless of scope size:SELECT id FROM openedx_catalog_courserun WHERE course_key IN (:keys). Needs a small bulk resolver added toopenedx_catalog.api; today it only exposesget_course_run(course_key), one key at a time (src/openedx_catalog/api_impl.py).WHERE oel_tagging_tag_id = :tag AND (course_id IS NULL OR course_id IN (:run_ids)), using the existingCompetencyCriteriaGroup(oel_tagging_tag_id, course_id)index.WHERE competency_criteria_group_id IN (:group_ids), usingCompetencyCriteria(competency_criteria_group_id).COUNT(*)joiningCompetencyCriteriatoCompetencyCriteriaGroupon that same FK, filtered tooel_tagging_tag_id = :tagonly, no course filter, viaCompetencyCriteriaGroup.oel_tagging_tag_idrather thanoel_tagging_objecttag(which allows a nulltagand can't express course scope anyway).Sequential calls (course-browse, then associations)
One extra round trip per course-browse page or filter change, not per row: resolve the course window first, then call the associations endpoint with those keys. Ten keys is roughly 350-500 bytes of query string, comfortably below any URL limit, and the 100-key cap above keeps that true even if the UI's own page size changes later.
Sizing
Once the panel is scoped to on-screen course keys, the instance-wide total number of groups a competency has accumulated across every rerun no longer bounds what's rendered. It only bounds
total_criteria_count(a single indexed count, cheap regardless of scale) and the deliberately non-UI unfiltered #681 path.total_criteria_count, a single indexed countComfortably performant even at these extremes.
ADR 0002 amendment
Amend in place with a changelog entry. Scope:
course_id: denormalized onto every group in a course-scoped subtree (course-level and bottom-tier both), immutable after creation, root always null.course_keys, don't paginate output), since output pagination would conflict with "do not return partial subtrees."Appendix: already-settled background, not new proposals
course_idcovers every course-scoped group, not just the course-level oneFor MVP the tree is exactly three
CompetencyCriteriaGrouplevels: root (course_idnull), then a course-level group, then a bottom-tier group.course_idis denormalized onto the course-level group and its bottom-tier child, both carrying the same value, immutable after creation. This keeps "all nodes wherecourse_idis null" unambiguous (root only) and makes the Query shape's group lookup a single index range scan instead of aparent_idwalk per level. Free to settle now:CompetencyCriteriaGroup/CompetencyCriteriadon't exist in code yet, onlyCompetencyTaxonomydoes (src/openedx_learning/applets/cbe/models.py), so there's no migration or backfill involved.Index review (AC: review Decision 5's indexes against this access pattern)
No index changes needed; Decision 5's existing indexes already cover it once
course_idis denormalized as above.oel_tagging_objecttag(object_id)already exists on the model (db_index=True). Decision 5 lists it as new work; it isn't.criteria_countcosts no extra query, it's the criteria rows the groups/criteria queries already loaded. Onlytotal_criteria_countneeds its own.Why the course window is resolved client-side, not server-side
openedx_catalogdoesn't do course-visibility; its own API doc says directly that it "does not yet provide any 'list courses' methods." That logic lives inHomePageCoursesViewV2inopenedx-platform, whichopenedx-corecan never import. Resolving the course window client-side first, then calling the associations endpoint with those keys, is the only shape that respects that boundary.A pre-existing, unrelated gap this design touches
openedx_catalogis missing from.importlinter's layer list (root_packages/src_layeringcurrently listopenedx_learning,openedx_content,openedx_tagging,openedx_django_lib,openedx_coreonly), even thoughCompetencyCriteriaGroup.course_idalready FKs into it per ADR 0002. Worth giving it an explicit place in the layer ordering as part of this work, not a blocker for this design.ormsbee commentedon Sep 1, 2026
If we just want the course run metadata, it seems like it should just return you the actual model objects instead of the keys? So
get_course_runs(course_keys)wherecourse_keysis an iterable thing (even a queryset for a sub-select)?Anyhow, that nit can be worked out in PR. I think it's fine to go ahead with this.
jesperhodge commentedon Sep 1, 2026
This looks great performance wise and behavior-wise.
For the API contract, I'm wondering about:
Can you explain what we need the root subtree here for when the resolved window is empty? What is meant with root subtree?
Maybe it's just me, but this seems a bit non-intuitive. Can we make the API endpoint more expressive? This "empty" state seems to need careful reading of the API docs to understand, so I'd like to replace it with something more clear.
Aside from that detail, the what should be done, and the reasoning, look good.
Just a note for follow-ups from this:
In terms of language, I have some trouble understanding the writing with how Claude phrased things, which is typical for Claude-written texts right now. For this proposal, that is perfectly fine, but I'd love if documentation or ADR changes that come out of this could become a bit more readable for humans.
mgwozdz-unicon commentedon Sep 2, 2026
@jesperhodge The root subtree is the CompetencyCriteriaGroup that corresponds to the competency itself, not to any individual course run.
course_keys comes back empty when either:
If we define the
course_keysparameter as always returning the root plus one subtree per key incourse_keys, then at zero keys that's just root plus nothing. So this is effectively just an edge case of the?course_keys=a,b,cvariant. I'll update the proposal to state it that way instead of listing empty as its own condition.Yes, I'll try to be very clear in any further documentation and ADR updates that I write.
docs: amend ADR 0002 for openedx#676's bounded competency criteria reads
docs: amend ADR 0002 for #676's bounded competency criteria reads