Cascading delete (remove related entities / references when deleting an entity)

[liwde]

Issue Summary

When deleting an entity that is referenced in other entities (e.g. its id is stored in Note entities, etc.), we are currently neither deleting these related records nor removing the reference. This leads to dead references and subsequently to errors (see #874). We therefore need to implement a more sophisticated delete functionality.

Requirements:

• ? id of deleted entity is removed from any other record that remains in the database (e.g. Notes referencing a deleted child are edited and that id is removed from the Note)
• ? cascading delete: related entities can be deleted automatically together with the primary entity that is deleted (e.g. all ChildSchoolRelation entities for a child are deleted when that child is deleted)
  • how to configure this or ask the user whether to do a cascading delete?

Possible approaches:

• ?

---

original comment

As discussed in #218, currently we do not look at foreign key relationships when deleting entities at all. When someone deletes e.g. a child, the notes relating to that child keep the reference to a (now deleted) Entity causing some errors. Aside from the error-handling, we should also focus on this root-issue.

I can imagine that a feasible workaround would be to extend entity.ts to have some (abstract) lifecycle-method onBeforeDelete(em: EntityMapperService), where each Entity-Subclass (like Child) can define steps to remove foreign references through the EntityMapperService passed as an argument.

This should work relatively independent of the Database layer just within our Entity system. It should be especially easy for Entities like ChildSchoolRelation that should be deleted with the Child.

What are your thoughts on this?

[Lorsbyjm]

I am not really in to the EntityMapperService, but we should definetly not have "dead" relations. The sougesstion sounds good and not to complicated

[sleidig]

This is somewhat related to the overall topic of how we manage related entities in the database (#108). But relational-pouch doesn't seem to offer this at the moment either pouchdb-community/relational-pouch#82

[sleidig]

If we implement a cascading delete of related records, we should consider restricting the rights to delete to admins only. Anyway, deletion is usually something users wouldn't want, as they loose all the context. Archiving entities (i.e. flagging them to be hidden) would often be a better option, I guess.

[sleidig]

For ChildSchoolRelations (and maybe some other types of entities) there is really no point to keep them if a child is indeed deleted. So at least for some entities this cascading delete is needed (#547), while for other entities deleting in general may not be wanted (#371).

[sleidig]

Use case scenarios & requirement specifications:

When deleting a ("primary") entity P there may be the following related scenarios:

P has a property that references another entity

• --> not problematic. The reference is lost but that can be assumed an intended consequence of the user action. There will not be any "dead references" left in the database from this.

P is referenced by another ("secondary") entity S1 (exclusively, i.e. that secondary entity does not reference any further entities)

• the secondary entity S1 should also be deleted (?) as it exclusively relates to the deleted entity
• ❓ is there any use case where we would want to just delete the referenced id but keep S1?
  • for Notes, etc. it doesn't seem to be relevant usually
  • maybe we should make this configurable by flagging the schema field as "essential" or not (see below)
• the user should be warned in the confirmation dialog that these related records are removed as well.

P is referenced by another entity S2, where P is the only reference in that property but S2 has another property that also references some other entity

• e.g. a Note referencing only P in children but another entity in schools
• for Notes I would treat this as a case of S3 (i.e. keep the secondary record)
• but for ChildSchoolRelation the schema technically looks similar but we would want to delete that record
  • maybe this requires configuration in the schema which references are "primary"? (see below)

P is referenced by another entity S3 which also references other primary entities in the same property

• e.g. a Note referencing multiple children, one of which is P, being deleted now
• --> remove the id of P from the references array and keep S3 in the database
• ❓ in case of deleting the record for data protection reasons, this might be problematic / require manual review of the user: a Note may well contain very sensitive and clearly identifiable information referring to P in the Note text that remains in the database even though the formal record and reference are removed (same is true for a possible "cascading anonymization")

---

Configurable role/importance of an entity reference property?

Should we make entity reference properties in the schema configurable to flag their importance? e.g.

• primary: reference is essential, when removed, the whole entity should be deleted as well
• secondary: reference is providing additional information, when the referenced is delete, remove the referenced id but keep this entity
• review: manual review? (see below)
• what would be default behavior? = secondary?

example schema: { dataType: "entity", role: "primary" }

ChildSchoolRelation: childId = primary & schoolId = primary
--> if either one is deleted, the ChildSchoolRelation also gets deleted

Manual review of referenced secondary entities?

Use case: A note is referencing multiple children. The text contains sensitive personal information about each of the children. The user now deletes one of the referenced Child entities.

This is a case of S3 and we very likely should not delete that note (the other referenced children are still in the database).
However, if P is deleted out of data protection concerns (e.g. a GDPR data deletion request), that note's text would need to be edited.

Is it feasible to show the user certain related entities to decide case by case to update or delete them?
UX seems difficult for that. But how to ensure sensitive data is properly removed?

Possible approaches:

• Refuse to delete the primary entity as long as such related entities exist
  • user has to go through the record and either remove the link to the entity or delete the related entity completely
  • after that, can click "delete" for P again and go ahead with the deletion
• Show each related entity one by one to the user for review
  • user can update (e.g. remove details from text and save the note), delete or keep
  • what happens if the user "aborts" that process with a long queue of secondary entities to be reviewed?
• Flag such related entities and provide a view that lists these for the user to review and clear

in case of anonymization: (how) could the user flag a note as "text is clear of personal data about P" without removing the link completely (e.g. to keep the record of someone having participated in an event)?

also see scenarios for anonymization: #1674 (comment)

[TheSlimvReal]

Good collection of the possible cases. I agree with the 3 roles even though I expect it to be very difficult to build a good review workflow that brings the user through all data.
What I am not sure now with your idea of primary and secondary: I imagine the children property of a note to be of role secondary (especially for group notes). What if all children that were referenced in a note where deleted? secondary would keep the note but remove all the IDs. While for a group note this might make sense, for notes just linked with one entity this should probably be deleted once the child is deleted.
So maybe to property would rather be primary and we define the rules of primary as delete entity if no other references are in this property array are left and otherwise behave like secondary?

[sleidig]

[sleidig]

when implementing this, revisit the current (incomplete) logic of anonymize: #2012 (comment)

[aam-digital-ci]

🎉 This issue has been resolved in version 3.26.0-master.21 🎉

The release is available on GitHub release

Your semantic-release bot 📦🚀

[aam-digital-ci]

🎉 This issue has been resolved in version 3.26.0 🎉

The release is available on GitHub release

Your semantic-release bot 📦🚀