kobotoolbox / kpi / 30820374083 / 6

82%

master: 76%

push

github

fix(usage): batch usage queries by billing window DEV-2567 (#7358)

### 📣 Summary

Fixes a background job that could overload the database and disrupt
service when checking which accounts are approaching their usage limits.

### 💭 Notes

- Same root cause and fix pattern as the earlier
`BillingAndUsageCalculator` fix in #7350 (bitmap-or OOM), applied to the
two remaining call sites with the same shape:
`get_submission_counts_in_date_range_by_user_id` and
`get_nlp_usage_in_date_range_by_user_id` in
`kpi/utils/usage_calculator.py`.
- Both functions built one giant `Q()` OR-chain (`user_id=X AND date
BETWEEN start AND end` per user) and ran it as a single query. These are
reached via `get_submissions_for_current_billing_period_by_user_id` /
`get_nlp_usage_for_current_billing_period_by_user_id`, called without an
`orgs` filter from
`kobo.apps.mass_emails.user_queries.get_users_within_range_of_usage_limit`
(the periodic `generate_mass_email_user_lists` task), so the OR-chain
spanned every organization in the instance, not a bounded batch.
- Both now group users by their distinct `(start, end)` billing window
and issue one `user_id__in=...` query per window instead, matching the
existing calculator fix.
- Also fixed along the way:
`get_submission_counts_in_date_range_by_user_id` was grouping by
`.values('counter', 'user_id', 'date')` instead of `.values('user_id')`,
so the `Sum('counter')` annotation wasn't actually aggregating per user,
the returned dict kept only one arbitrary row per user rather than the
true sum. Now grouped by `user_id` only, consistent with the NLP usage
function and the calculator.
- No behavior change for callers, same return shape (`dict[int, int]` /
`dict[int, NLPUsage]`), just fixed aggregation and no more single-query
OR-chain.
- Follow-up cleanup bundled in this PR: the "group users by window"
pattern above ended up duplicated three times
(`BillingAndUsageCalculator._get_submission_usage_batch`,
`get_submission_counts_in_date_r... (continued)

4562 of 12549 branches covered (36.35%)

18925 of 37205 relevant lines covered (50.87%)

0.51 hits per line