I'm not recommend use
deleted_at flag, they don't work when the table has unique index.
My solution is use
version column and snapshot(archive) table, the
version column can also be used for avoiding concurrent updates (Get-then-Update problem / optimistic lock).
The snapshot(archive) table has same schema as source table, but with only
id + version index.
When update row in the source table, increase the version field, eg:
update example set version=1 where id=1 and version=0. You can also copy(upsert) the row to snapshot table before update if you want keep all histories,
When delete row in the source table, copy(upsert) the row to snapshot table then delete in source table, do the two steps in one transaction.
Since all snapshot tables have id and version column, I also implement a general snapshot logic, and it's even simpler if use ORM library to auto generate sql migrations.