🔗 Deleted record audit log with Ecto and PostgreSQL

José Valim wrote about how to implement soft delete using a PostgreSQL trigger. The best reason for soft delete is when you need the ability to undelete records, e.g., you give a grace period of 30 days before the record is deleted permanently.

If the deletion of records is meant to be irreversible, this would be an anti-pattern. Soft delete for audit logging is problematic because you must be defensive in how you access data, and you must ensure you always use the right view or table partition.

For an audit log, we want to allow the record to be deleted from the table and add a log entry to another table. This could be done entirely in your application code, but this will not catch any deletions by other applications or clients.

As José writes in his blog post, the simple solution is to store a JSON blob of the data.