[FR] Cache part requirements

[SchrodingersGat]

Problem statement

Part "requirement" data is expensive to compute dynamically - and is only becoming more so.

Functionally this data does not change frequently but it used in many "on the fly" database annotations.

Requirement data includes:

• Number required for sales order
• Number required for build orders
• Number allocated to sales orders
• Number allocated to build orders
• Number incoming from purchase orders

These quantities should include:

• Variant quantities (for template parts)
• Substitute options (for BOM lines)

Suggested solution

• Add a new table with a 1:1 relationship to the "part" model which stores this requirements information for rapid access.
• Significantly simplify API calls which currently compute this on the fly
• De-couple business logic from database annotations in the queryset
• Simplify implementation of new features which adjust the requirements for a part

Such a feature would also require that we add a number of hooks to re-calculate the requirements data when the underlying instances change. However we can at least point to a single function which provides a single "source of truth" for each part.

[matmair]

I like the concept of "single function machines" (translated from German) for problems like this.
The idea is: You define a clear set of inputs and outputs. Every time you want to get the result of the machines work you pass all the inputs to a simple helper that checks if there is a calculation for the exact inputs and fetches it from a cache/db. If there is not, the "machine" is fed with all the inputs, the "machine" calculates the results and returns them; keeping the API call sync; after returning the result it is written to the cache/db and all invalid entries in the cache are deleted.

Depending on how far you want to scope/feature creep this we could use the opportunity to rip out a lot of "inherent" BOM/assembly logic into a separate class/module and add support for more complex BOM and build logic like we would need for #10274 / made-to-order things

[SchrodingersGat]

I have been performing some more API profiling as part of #11073

The primary bottleneck for large API queries (especially around the "build" endpoints) is now the huge number of annotations which are required to provide requirements / ordering data for each row in a table:

example from `/api/build/line/

Due to this, I think that we should be giving some serious thought to caching the pricing data - it will result in a massive speed up to a bunch of the API endpoints.