Soft deletes vs hard deletes: the hidden complexity of 'is_deleted' flags

We've been using soft deletes for years across most of our services at work, and honestly, the hidden complexities often outweigh the perceived simplicity. What started as just adding an `is_deleted` column has led to so many headaches. Engineers constantly forget `WHERE is_deleted = false`, leading to deleted data appearing in reports or even user-facing UIs. Our unique constraints get broken because a 'deleted' user can't re-register with the same email, even though they should logically be able to. It also balloons our table sizes, making scans slower because we're iterating over millions of rows that are logically gone. Then there's the GDPR and data retention side. Eventually, you still need to *actually* delete data. Our 'soft delete' often just means 'delete after 90 days of soft deletion'. So we have cron jobs running that still do hard deletes. It feels like we've just kicked the can down the road, and sometimes even added more complexity. For audit history, we've started looking at separate audit logs or event sourcing, which provides a much clearer trail without polluting the primary data tables. Curious how others at a similar scale manage this, especially with global compliance requirements.