kobotoolbox / kpi / 18565438923

81%

master: 76%

push

github

feat(api): add filtering and ordering support for `/api/v2/user-reports` endpoint DEV-287 DEV-233 (#6342)

### 📣 Summary
Adds robust filtering and ordering capabilities to the
`/api/v2/user-reports` API endpoint, enabling users and admins to query
large user datasets efficiently by date, usage metrics, and subscription
attributes, all backed by the optimized materialized view for scalable
performance on millions of records.

### 📖 Description
This PR introduces the filtering and ordering layer for the
`/api/v2/user-reports` endpoint, built on top of the existing
materialized view (`user_reports_userreportsmv`) that aggregates
user-level billing and usage data.

Also, added targeted indexes in `0003_add_user_reports_mv_indexes.py` on
high-cardinality numeric and timestamp columns to support range queries
without full table scans.

  Examples of usage:
  - Filter by username (case-insensitive, starts with):
    `/api/v2/user-reports/?q=username__icontains:raj`
  - Filter by email (case-insensitive, starts with):
      `/api/v2/user-reports/?q=email:kobo@example.com`

  - Filter by total storage bytes (greater than or equal to):
`/api/v2/user-reports/?q=service_usage__total_storage_bytes__gte:1`
  - Filter by total storage bytes (less than or equal to):
`/api/v2/user-reports/?q=service_usage__total_storage_bytes__lte:1`

---

Part of https://github.com/kobotoolbox/kpi/pull/6243

---------

Co-authored-by: RuthShryock <ruthieshryock@gmail.com>
Co-authored-by: Olivier Léger <olivierleger@gmail.com>

7057 of 11105 branches covered (63.55%)

27261 of 33540 relevant lines covered (81.28%)

1.6 hits per line