It is possible to create two or more regular requisitions for the same program, facility and period if users initiate a requisition in the same time. It is caused by a delay between the check for already existing requisition for the given program/facility/period and creation of the requisition in the database.
One possible solution would be to raise the transaction isolation level for the requisition initiate, such that all transactions for initiate happen in order. This may however affect performance a lot. Another solution would be introduce an additional constraint on the database level, which may be a better solution, based on the fact that the described scenario is an edge case.
Acceptance criteria:
when users click initiate for the same period/facility/program at the same time only one requisition is created
appropriate repository integration tests (if DB constraint used)
if the DB changes, any built reports should be updated / notified that they might break with this change
: FYI when this is done, Malawi reports could very well break. If you could coordinate the investigation of weather this is true for Malawi, and your acceptance testing of 3.2, that'd be very helpful. Read my proposal above on how this should be fixed. From that the field you'll need to check Malawi report usage for is the one titled "emergency" in the requisitions table.
Will do. Thank you very much, , for highlighting the potential schema change.
pretty sure most mainstream databases work like that, I'm pretty sure MySQL does. If we actually want to take advantage of PostgreSQL, how about a unique index with a condition? https://stackoverflow.com/questions/16236365/postgresql-conditionally-unique-constraint
This would allows us to avoid any db changes which I think is a big plus for us and Malawi, but it's not the prettiest thing. But hey, always using null instead of false is not that pretty as well.
Note: this is estimated at 1, but if we have to change the column name and do migrations and update reports etc. then this probably goes up to a 3
Root cause: we never had this check - we should pay more attention to our db definitions. In the end, the db must force its own integrity.