Speed up list pages by checking user permissions once per request instead of once per row #1475

Summary

Most API responses include, for every object, the actions the current user may take on it (user_permissions). Today that list is worked out separately for each row of a response, even though on a project-scoped page the user and the project are the same for every row. As a result, list endpoints and dropdowns run several extra database queries per row, and the cost grows with the page size. Doing this work once per request would make list pages cheaper across the API and remove a trap that new serializers fall into without noticing.

What we observed

• Job choices (Record which job created each detection and classification, and filter occurrences by job #1471). The new job dropdown endpoint ran 27 queries for 3 jobs and 57 for 8, about 6 per job (measured in a test with cachalot disabled). Copilot flagged it in review; Record which job created each detection and classification, and filter occurrences by job #1471 fixed that endpoint by skipping permissions, since a dropdown does not use them.
• Occurrence list. An earlier test measurement, also with cachalot disabled, found that a 3-row occurrence page ran 50 queries and a 4-row page ran 62, about 12 per row. All of the per-row queries came from OccurrenceListSerializer.get_permissions: a project lookup and Guardian's get_perms(user, project).

Cachalot serves repeated queries from cache, so this is hard to see when testing by hand.

Where it comes from (based on reading the code)

• DefaultSerializer.to_representation (ami/base/serializers.py) calls get_permissions for every row, and almost every serializer inherits it. A new serializer pays the per-row cost unless its author knows to opt out.
• For each row, BaseModel.get_user_object_permissions resolves the project and calls get_perms. Job.get_custom_user_permissions also builds a debug message with an f-string that includes the job, which formats the job (reading status) even when debug logging is off. When the queryset used .only(), that read is an extra query per row.
• One serializer already avoids this: TaxaListSerializer.get_permissions (ami/main/api/serializers.py) caches the project and the user's project permissions on the serializer, which DRF reuses for every row of a list response, so they are resolved once per request. A few nested serializers instead skip permissions by returning an empty list.

Direction to discuss

1. Move the per-request caching from TaxaListSerializer into DefaultSerializer.get_permissions, keyed by project id, so every serializer resolves a user's project permissions once per request rather than once per row. Object-level checks that genuinely differ per row (for example, whether the user created an identification) would stay per row.
2. Switch the debug logging in the permission code to lazy %s formatting, so messages are not built when debug is off.
3. Optionally, have the test suite fail on repeated lazy relation loads (for example with django-zeal). It would not catch the permission queries themselves, which is why the first item is the main fix.

What we still need to verify

• Re-measure the occurrence list, the captures list, and the jobs list before and after, with cachalot disabled and at two page sizes, and confirm the per-row count drops to zero or near it.
• Check that user_permissions in responses is unchanged for each role (anonymous, non-member, member, project manager, superuser), using the existing permission tests.
• Confirm no serializer relies on a per-row difference in project permissions, for example a list spanning several projects. Those cases would need the cache keyed by project rather than a single value per request.

Related

• Record which job created each detection and classification, and filter occurrences by job #1471: the job choices endpoint, where this was caught and fixed for one serializer.
• The agent guide's endpoint checklist gains a line on this trap in Document attaching screenshots to pull requests, and checking new endpoints for per-row queries #1474.