Designing for Soft Deletes: Patterns, Performance, and Queries

I still remember the exact moment I realized soft deletes weren't just a feature—they were a confession. We were seven months into a CRM project. The product manager came to my desk with a panicked look. "A user accidentally deleted 3,000 contact records. Can we get them back?" My stomach turned. I had designed the database with hard deletes—clean, efficient, and unforgiving. DELETE FROM contacts WHERE id = ? and it was gone. Forever. The backup restoration would take hours, and we'd lose a day'