Pretty much every soft-delete implementation I've seen involves modifying a tables schema to add a deleted_at column.
Invariably, this becomes a rather annoying thorn in my side when I need to do more advanced indexing, like unique indexes for upserts. Nothing impossible, but I'm quickly swimming upstream against whatever ORM I happen to be using (I'm looking at you, Prisma).
What I haven't seen much of is a trigger-based system which moves the deleted record into an entirely new table. Is it possible to make a deletions table that always mirrors the source tables schema? I know table inheritance is a thing but I don't know if that applies to future schema changes.
Anyways, I know deleted_at quite well, but I long for something better.
Invariably, this becomes a rather annoying thorn in my side when I need to do more advanced indexing, like unique indexes for upserts. Nothing impossible, but I'm quickly swimming upstream against whatever ORM I happen to be using (I'm looking at you, Prisma).
What I haven't seen much of is a trigger-based system which moves the deleted record into an entirely new table. Is it possible to make a deletions table that always mirrors the source tables schema? I know table inheritance is a thing but I don't know if that applies to future schema changes.
Anyways, I know deleted_at quite well, but I long for something better.