Repository navigation
Transition API urls to require a project prefix #978
Description
Activity
Claude says: Adding a related finding from #1390 here, since this ticket is where the project-scoping approach across the API is being decided.
Detectionhas noprojectcolumn; it reaches a project only throughsource_image__project. When #1390 scoped the detections list to the requested project, the pagination COUNT got 10 to 40 times faster on a local copy of production (22 s to 1.8 s on the largest project, 0.5 to 0.6 s on smaller ones), butEXPLAIN (ANALYZE)shows the plan is aParallel Seq Scanover the whole detection table followed by index probes into source images, on every project size tried. The cost is therefore bounded by the total size of the detection table rather than by the project, and it will grow platform-wide as data accumulates.Classification(scoped viadetection__source_image__project) most likely has the same shape, though that one has not been measured.Directions to discuss, roughly by effort:
- Denormalise
projectontoDetection(and possiblyClassification) with a composite index leading with it, matchingOccurrenceandSourceImage. The hard part is not the column but keeping it in sync: it has to be set on every write path (pipeline results, imports, crop regeneration) and updated whenever a capture or occurrence moves between projects, otherwise detections end up orphaned or attributed to the wrong project. A data-integrity check in the framework from Framework to check and report data integrity issues #1188 could pin the invariant (detection.project_id == detection.source_image.project_id). - Estimated counts for large filtered lists (Estimated-count paginator for fast pagination on large filtered lists #1328), which sidesteps the COUNT without a schema change but does not help the list query itself.
- Leave it and accept the table-bound cost until the tables are partitioned by project, which the URL structure proposed in this ticket would make natural.
Measurements are from a local copy of the production database with the query cache disabled; the plans and the table are in #1390's description.
- Denormalise
Summary
We are relying more on projects to check object permissions, add nested data based on project settings (even to single-object detail views), and increase performance of large requests (partitioning based on project). However each view currently retrieves the "current" project in a few different ways and we are requiring the
project_idurl param on more and more views.One standard practice is to add a parent object to the URL structure. So instead of
/api/occurrences/?project_id=5We use
/api/project/6/occurrences/Related to #734 & #928
Another option is to continue to use the
project_idparameter, but make it required for all endpoints. But it's not as elegant!