kobotoolbox / kpi / 18562672376

81%

master: 76%

push

github

feat(userReports): add `/api/v2/user-reports/` endpoint for superusers DEV-232 DEV-899 (#6243)

### 📣 Summary
Add a new superuser-only endpoint, `/api/v2/user-reports/`, to access
and filter all user usage data.

### 💭 Notes
- Created a new Django app `user_reports` 
- Used a SQL materialized view along with some Django tables to gather
and calculate all the data for the endpoint
- Our celery task `refresh_user_report_snapshots` runs every 30 minutes
and works to update the data in batches
- The endpoint is only accessible if Stripe is enabled 
- Renamed api tag "Audit logs (superusers)" to "Server logs
(superusers)" ([Zulip
thread](https://chat.kobotoolbox.org/#narrow/stream/4-Kobo-Dev/topic/API.20Documentation/near/713167))


### 📖 Description

This PR introduces a new endpoint `/api/v2/user-reports` that provides
detailed information about users, including their personal details,
metadata, organization info, subscriptions, usage statistics, and
computed balances. It supports flexible nested filtering, allowing
queries like:
```
/api/v2/user-reports/?q=username__icontains:demo
/api/v2/user-reports/?q=service_usage__total_storage_bytes__gte:1
/api/v2/user-reports/?q=service_usage__balances__submission__balance_value__gte:5000
```
To handle a large user base, the endpoint leverages a PostgreSQL
materialized view that aggregates user-related data for fast retrieval.
Since some data (like usage and submissions) lives in another database,
a periodic Celery task `refresh_user_report_snapshots` was added to
pre-compute and store this data in a new `BillingAndUsageSnapshot`
table. The materialized view references this snapshot for further
computations. A `BillingAndUsageSnapshotRun` table was also added to
track each run and safely resume interrupted jobs.


### 👀 Preview steps

1. ℹ️ have a superuser account and multiple regular users in multiple
organizations
2. create projects, make submissions, and buy stripe subscriptions
3. checkout this branch and ru... (continued)

7057 of 11105 branches covered (63.55%)

303 of 330 new or added lines in 15 files covered. (91.82%)

27262 of 33540 relevant lines covered (81.28%)

1.6 hits per line