For most of our systems we never ever delete any data, all data that has ever been put in the system is still in the system - that includes any modifications. The reason we do this, is that it provides a very good audit trail, and we can view the state of the system at any point in time.
The way we handle this is with two timestamps:
and a trigger that actually causes the update to run as an INSERT whilst setting the deleted_on field on the "old" row. A similar trigger for DELETE updates the timestamp but doesn't delete the row.
The most current record is the newest created_on without a deleted_on set - if deleted_on is set then the record has been "deleted".
Does anyone have an elegant way of doing this in Phalcon? Phalcon supports soft delete, so we can use that to fake the delete part along with a beforeDelete behaviour - The problem at the moment is the UPDATE side of things, quite possibly because I don't know Phalcon that well at the moment. Of course we could stick to the trigger based solution, but that would mess with the ORM as that objects ID would then change.