User Story
- As a B2B organization manager, I want the learner directory's summary tiles to load without five round-trips, so the page does not fall over when a few managers open it at once.
Description/Context
MIT Learn's learner directory (mitodl/mit-learn#3958) renders four summary tiles above the table: Enrollments, Not started, In progress, Completed. LearnerProgressResponse carries total_count and outcomes_withheld_count but no per-status breakdown, so the page gets the other three by issuing three more requests to /learner-progress with limit=1 and a completion_status filter, reading only total_count off each and discarding the row.
That is five requests per page load, and each one is more expensive than the limit=1 suggests:
| Per request |
Cost |
require_org_manager |
mitxonline_client.is_org_manager — TTLCache, 60s, no per-key lock, so concurrent cold-cache misses all round-trip |
require_contract_in_org |
one existence check against mv_b2b_contract_utilization |
latest_refresh_timestamp |
cached 300s per (schema, MV), so effectively free |
| page query |
SELECT ... LIMIT 1 over mv_b2b_learner_enrollment |
| count query |
COUNT(*) plus the withheld SUM over the same set — full scan regardless of limit |
So one page load is roughly 15 StarRocks queries and, on a cold manager cache, five concurrent authenticated round-trips to MITx Online. starrocks_pool_max_size is 10 per pod and starrocks_pool_acquire_timeout_seconds is 5, and a saturated pool is a 503 through add_shared_error_handlers — which the dashboard renders as "Something went wrong loading learner data." Two managers opening the page at the same moment is enough to reach that, and the four extra requests buy three integers.
outcomes_withheld_count already exists on this envelope for exactly this reason: so a client can characterize the result set without paging through it. The status breakdown belongs in the same place.
The count query already aggregates over the filtered set, so conditional SUMs per status add no scan. The shape is the cheap part; the question below is not.
Open question, and it is a product decision rather than an implementation one. MIT Learn's tiles today are deliberately unfiltered — a fixed summary of the contract while the table narrows under search and the status filter. Counts computed in the existing count query share that query's WHERE clause, so they would narrow with it instead. Either:
- the counts track the response's own filters (free, consistent with
total_count and outcomes_withheld_count, and the tiles come to mean "of the rows matching your filters"), or
- the tiles stay a fixed contract summary, which needs the counts computed over scope-only predicates (organization, contract,
include_inactive) and therefore a second aggregate in the same request.
I lean toward the first: it costs nothing, it keeps every number in the envelope describing one result set, and a tile that disagrees with the table underneath it is the confusing case. Either way it collapses to one request. This needs a call from whoever owns the dashboard's UX before implementation.
Related, not in scope: mitxonline_client's _cache has no per-key lock, unlike refresh_metadata, so N concurrent cold-cache requests for one (sub, org) all issue the manager check. Fixing the fan-out here makes that much less likely to bite, but it is worth its own issue.
Acceptance Criteria
Plan/Design
Extend the count query in learner_queries.learner_progress with one conditional SUM per status, alongside the outcomes_withheld_count SUM already there, and add the field to LearnerProgressResponse in learner_models.py. passed and certified are mutually exclusive in _COMPLETION_STATUS (an unrevoked certificate is checked first), so the buckets do not overlap.
A nested object rather than four sibling fields, so the set stays closed when a status is added:
{
"organization_id": "...",
"as_of": "...",
"total_count": 168,
"outcomes_withheld_count": 0,
"completion_status_counts": {
"not_started": 14,
"in_progress": 125,
"passed": 8,
"certified": 21
},
"data": [...]
}Note for the consumer: passed and certified are separate buckets here, and a client that wants one "Completed" number sums them. mitodl/mit-learn#3958 currently counts ["passed", "certified"] for its Completed tile while its Completed filter sends only passed, which is the kind of mismatch a single explicit breakdown makes hard to write.
User Story
Description/Context
MIT Learn's learner directory (mitodl/mit-learn#3958) renders four summary tiles above the table: Enrollments, Not started, In progress, Completed.
LearnerProgressResponsecarriestotal_countandoutcomes_withheld_countbut no per-status breakdown, so the page gets the other three by issuing three more requests to/learner-progresswithlimit=1and acompletion_statusfilter, reading onlytotal_countoff each and discarding the row.That is five requests per page load, and each one is more expensive than the
limit=1suggests:require_org_managermitxonline_client.is_org_manager— TTLCache, 60s, no per-key lock, so concurrent cold-cache misses all round-triprequire_contract_in_orgmv_b2b_contract_utilizationlatest_refresh_timestampSELECT ... LIMIT 1overmv_b2b_learner_enrollmentCOUNT(*)plus the withheldSUMover the same set — full scan regardless oflimitSo one page load is roughly 15 StarRocks queries and, on a cold manager cache, five concurrent authenticated round-trips to MITx Online.
starrocks_pool_max_sizeis 10 per pod andstarrocks_pool_acquire_timeout_secondsis 5, and a saturated pool is a 503 throughadd_shared_error_handlers— which the dashboard renders as "Something went wrong loading learner data." Two managers opening the page at the same moment is enough to reach that, and the four extra requests buy three integers.outcomes_withheld_countalready exists on this envelope for exactly this reason: so a client can characterize the result set without paging through it. The status breakdown belongs in the same place.The count query already aggregates over the filtered set, so conditional
SUMs per status add no scan. The shape is the cheap part; the question below is not.Open question, and it is a product decision rather than an implementation one. MIT Learn's tiles today are deliberately unfiltered — a fixed summary of the contract while the table narrows under search and the status filter. Counts computed in the existing count query share that query's WHERE clause, so they would narrow with it instead. Either:
total_countandoutcomes_withheld_count, and the tiles come to mean "of the rows matching your filters"), orinclude_inactive) and therefore a second aggregate in the same request.I lean toward the first: it costs nothing, it keeps every number in the envelope describing one result set, and a tile that disagrees with the table underneath it is the confusing case. Either way it collapses to one request. This needs a call from whoever owns the dashboard's UX before implementation.
Related, not in scope:
mitxonline_client's_cachehas no per-key lock, unlikerefresh_metadata, so N concurrent cold-cache requests for one (sub, org) all issue the manager check. Fixing the fan-out here makes that much less likely to bite, but it is worth its own issue.Acceptance Criteria
LearnerProgressResponsecarries a per-status count for eachCompletionStatusvaluecompletion_status; the buckets plusoutcomes_withheld_countsum tototal_countmv_b2b_learner_enrollmentbeyond the existing count queryPlan/Design
Extend the count query in
learner_queries.learner_progresswith one conditionalSUMper status, alongside theoutcomes_withheld_countSUMalready there, and add the field toLearnerProgressResponseinlearner_models.py.passedandcertifiedare mutually exclusive in_COMPLETION_STATUS(an unrevoked certificate is checked first), so the buckets do not overlap.A nested object rather than four sibling fields, so the set stays closed when a status is added:
{ "organization_id": "...", "as_of": "...", "total_count": 168, "outcomes_withheld_count": 0, "completion_status_counts": { "not_started": 14, "in_progress": 125, "passed": 8, "certified": 21 }, "data": [...] }Note for the consumer:
passedandcertifiedare separate buckets here, and a client that wants one "Completed" number sums them. mitodl/mit-learn#3958 currently counts["passed", "certified"]for its Completed tile while its Completed filter sends onlypassed, which is the kind of mismatch a single explicit breakdown makes hard to write.