Spike: Guarantee of the PO Line status up-to-date

Requirements  MODORDERS-106 - Getting issue details... STATUS

The last step in receiving/check-in flow is to update the status of PO Line to "Partially Received" or "Fully Received". It's possible that something may go wrong and status won't be updated even though the items were actually received.
So the mechanism to guarantee the status is up-to-date needs to be worked out.

High Level Investigation

From the technical side this means that there may be a discrepancy between the status of the PO line and the statuses of its associated pieces in the database. The following connections are possible between PO line with PARTIALLY_RECEIVED and FULLY_RECEIVED statuses and state of pieces:

• there are pieces related to PO line that are in the RECEIVED and EXPECTED statuses - the PO line must be in the PARTIALLY_RECEIVED status;
• all pieces are in RECEIVED status - the PO line must be in FULLY_RECEIVED status.

Thus, the task is to create a mechanism that would allow changing the status of the PO line when the status of its associated pieces changes. 

Two types of approaches can be offered - "scheduled-style" mechanism that check the status of pieces and change the status of the PO line if necessary; “injection-style” mechanism that monitor changes in the status of pieces and change the status of the PO line if necessary.

1. Scheduled-style mechanism

   This group of approaches is based on periodic scanning of all saved pieces and determining the status of PO line on this basis. A typical algorithm for such mechanism might look as shown on Figure 1.

   

   Figure 1 - A general algorithm of mechanisms based on periodic scanning of the entire set of pieces.

   Particular implementations of this approach are presented below.

   Vert.X Periodic Timer

   Within the Vert.X features #_executing_periodic_and_delayed_actions one can implement a mechanism that periodically retries the pieces and checks the status of the PO line, changing it if necessary. There is a need to develop a mechanism that monitors pieces status in cluster mode, when each mod-orders / mod-orders-storage instance will start the scheduler entity. Thus, it is necessary to provide a solution so that the instances of the scheduler from each mod-orders / mod-orders-storage do not compete and do extra pieces checking.

   Separate Scheduler Application

   Since this will be a separate application it will solve the problem of multiple instance of scheduler noted in the previous option.

   PostgreSQL cron-job

   Task scheduler can be installed as PostgreSQL DB extension that executes scheduled tasks directly from the database, for example, pg_cron. This will allow to run PO line status verification task following the algorithm from Figure 1 and implemented as a sequence of PostgreSQL commands.

   
   

2. Interceptor-style mechanism

   This group of mechanisms is based on the injection of logic into the process, which checks the status of PO line and its associated pieces. This logic checks the consistency of statuses and provides PO line status changes, if necessary. The algorithm looks as shown on Figure 2.

   

   Figure 2 - A general algorithm of mechanisms based on consistency checking as part of retrieving flow.

   Checking and updating the PO line status upon retrieving

   During PO line retrieving one can check whether its status corresponds to the state of the associated pieces according to algorithm from Figure 2 and on this basis to decide on updating the status of PO line. However, within the framework of this approach, there is a need for many additional steps associated with retrieving pieces, analysis of their status, changing and updating the PO line status every GET PO line request. As a result, this will lead to poor performance due to a potential UPDATE request at time of GET PO line request.

   PostgreSQL Trigger Functions

   A similar scenario can be made using the trigger function plpgsql-trigger that will be called for each state changing of pieces and make update the status of PO line if necessary - PO line data is updated when the underlying piece records are updated.

Solution Proposal (preliminary)

The group of mechanisms for intercepting the change of state of pieces (injection-style) is associated with the lack of the need to perform additional operations, which will greatly affect performance.
A group of scheduled mechanisms seems to be the most appropriate, as it has less impact on performance.

Namely, creating cron-jobs can be effective, but it is difficult to implement and maintain, since it is necessary to install an extension on each database. Vert.X Periodic Timer seems to be the most efficient and easily implemented way, taking into account, however, the need to implement a way to coordinate instances to prevent unnecessary work in the case of a cluster. Separate Scheduler Application can be an efficient way without the lack of consistency in cluster mode (mentioned for Vert.X Periodic Timer) but it should technically be implemented probably as a separate module.

So, as a result I can propose solution based on Vert.X Periodic Timer as first approach.