micronutrientsupport / database-architecture

The Postgres database code for the MAPS tool
3 stars 0 forks source link

feat: add basic lock flag and check trigger for intervention_data updates #265

Closed bgsandan closed 2 years ago

bgsandan commented 2 years ago

Adds an is_locked flag to the interventions table.  This should be set as true at point of creation for premade interventions as they should never be editable.

When a new intervention is created (derived from a parent intervention), that parent intervention should be marked as 'locked'.  This will ensure that the parent data that fed into this intervention can be recalled if needed and won't be updated after the new intervention was forked.  The create_derived_intervention function has been updated to set this flag on the parent intervention as part of it's transaction.

The intervention_list view has a new field is_editable which reflects the union of the is_premade and is_locked flags.  The front-end can use this to establish whether to offer the user the option to edit their intervention or only to be able to fork it (front end unlikely to use 'fork' terminology but functionally that is what is happening).

There is a new function and update trigger on the intervention_data table.  This checks the state of the is_locked flag for the corresponding intervention in the intervention table.  If the intervention is_locked flag is false the update succeeds.  If it is true, then the update is rejected

_NB: implemented as a trigger and function rather than a check constraint due to the need to access the data in the intervention table when the intervention_data table is updated, rather than just being a constraint on the table being updated._

andy-bevan commented 2 years ago

I can't see any reason why that shouldn't work.