"status" attribute

[dkastl]

Background

So far the API allows to create, update and delete items, which is fine.
However, deleting a record will remove it definitely from the database, and there are good arguments that this is good, but there might be also cases where it would be better to not delete a record.

Proposal

We could introduce a status attribute for all tables, and the status could indicate for example (just ideas):

• draft
• deleted
• dirty (which could indicate rescheduling is needed)
• archived (i.e. for projects)
• read-only
• others

Alternatives

We could even keep track of changes as "history", for example implemented in this way https://postgis.net/workshops/postgis-intro/history_tracking.html

[krashish8]

This is definitely a good idea.
As of now, there is a deleted (TRUE/FALSE) field in each table (e.g.), which is set to FALSE on a DELETE request. Anyone directly using the web API will have no difference, it would feel that the record is deleted. But the record will be still present in the database (and can be restored from the database).

The deleted field is present in all the tables except the *_time_windows table (because I feel that time windows are more like an attribute of some entity and can be actually added as an attribute in some later version by creating custom types, however, we haven't worked with custom types on an extension, so leaving it for the end), and the matrix/schedules table (because these don't support a direct delete operation by an API user as the records are created using the web API or triggers, etc).

Keeping track of history can be left for the end because normally this does add much functional value to the API. Later, we could introduce a status field instead of the current deleted field:

• deleted: set using DELETE request, for an API user, this would be the same as actually deleting the record.
• Other fields can be set using PATCH request:
  • read-only would make the record non-editable.

Not sure about what functional value is added by the status fields apart from deleted or read-only. They could be used as additional information for the entity, e.g. dirty field would just be a reminder for the user to defragment/reschedule, but whether to actually do that might depend entirely on the user (there could be an API endpoint to defragment/reschedule).

[dkastl]

Oh, I didn't see that there is already a deleted field, which is good.

"Deleting" can be a bit more complicated, if you take into account legislation such as GDPR, which for example gives everyone "the right to delete" data. So maybe a "delete" flag, which can be true or false is more a convenience to be able to recover accidentally deleted data.

I agree that "history" is a new feature request and should be a different issue. I think keeping track of changes can be helpful if things don't go as expected and there is a need to figure out if the problem was wrong use of the API, for example. I think the linked approach is quite smart and runs just via triggers within the database.

Overall I just created this issue to discuss the proposal.