Automatically delete the EntityEvents corresponding to deleted entities

[marien-probesys]

Problem

For now, the EntityEvents are kept in the database even if their corresponding Entities are deleted. In the context of #766, it becomes important to delete these information as well.

The EntityEvents are not deleted in cascade by the database because the EntityEvent::entity association is polymorphic. Thus, there is no foreign key.

Evaluated solutions

Solution 1: remove EntityEvents at the same time as the Entity

When removing an Entity, we could remove the related EntityEvents as well with something like:

$someRepository->remove($someEntity);
$entityEventRepository->removeRelatedToEntity($someEntity, flush: true);

The problem is that the entity's associations may be deleted in cascade directly by the database. So this solution is necessarily insufficient as we need to remove the EntityEvents related to the associations as well.

An alternative would be to create some service that would make sure to delete the entity, its associations and the related EntityEvents. However, it seems quite difficult (if not impossible) to maintain this solution correctly on the long term.

Also, we don't want to delete the EntityEvents immediately as they serve for traceability. Indeed, by keeping the EntityEvents for some time, we keep the trace of any attack that would delete data.

In other words, this solution is not suitable for us.

Solution 2: remove EntityEvents in background after some time

In App\MessageHandler\CleanDataHandler, load the EntityEvents older than 1 month and check for each of them if its related entity still exists. If it doesn't, delete the EntityEvent.

The drawback of this solution is it will be more and more intensive for the database over time as the EntityEvent table expands.

This could be optimized by processing the EntityEvents by type and by comparing the ids with a LEFT JOIN over the corresponding tables.

Specifications

In App\MessageHandler\CleanDataHandler, load the different EntityEvent::$entityType saved in the database.

Then, iterate over these types and perform a SQL query in the form of:

DELETE entity_event FROM entity_event
LEFT JOIN [entityTable]
WHERE entity_event.entity_type = [entityType]
AND entity_event.created_at < [1 month ago]
AND [entityTable].id IS NULL

However, Doctrine DQL doesn't seem to allow this kind of query. The SQL syntax for PostgreSQL and MariaDB are quite different: https://stackoverflow.com/questions/21662726/delete-using-left-outer-join-in-postgres vs. https://stackoverflow.com/questions/2763206/deleting-rows-with-mysql-left-join

An alternative would be to load with a SELECT the EntityEvent ids that are not attached to an entity, and then delete them with a IN condition. This is less performant, but it could be enough for Bileto.

Estimated time

1 day