Handling Soft Deletes: Best Practices for SaaS Data Retention

Previously in this course, we discussed the importance of finalizing the SaaS database architecture to ensure your schema is ready for production. In this lesson, we add a crucial safety layer: soft deletes.

In a production environment, a hard DELETE is often an irreversible mistake. If a user accidentally deletes a project or a team member removes a critical configuration, the data is gone forever unless you rely on backups—which are slow and disruptive. Soft deletes allow us to simulate deletion by flagging rows as "inactive" while keeping the actual data intact for restoration or compliance.

The Concept of Soft Deletes

A soft delete (or "logical delete") involves adding a column—typically deleted_at—to your tables. Instead of executing a DELETE FROM statement, your application performs an UPDATE that sets the deleted_at timestamp to the current time.

This approach aligns with database schema design strategies where we prioritize data durability. By keeping rows in the table, you maintain referential integrity (foreign keys still point to valid rows) and provide a "trash bin" feature for your end users.

Implementing the Soft Delete Pattern

To implement this, you need to modify your existing tables. Let’s take our users table from our ongoing SaaS project.

1. Schema Modification

Add the deleted_at column. It should be a nullable TIMESTAMP or DATETIME. It must be nullable because active records have no deletion time.

SQL
ALTER TABLE users ADD COLUMN deleted_at TIMESTAMP NULL DEFAULT NULL;

-- Indexing is crucial for performance
CREATE INDEX idx_users_deleted_at ON users(deleted_at);

2. Updating Queries

The core challenge with soft deletes is ensuring that every standard query ignores deleted rows. You must append a WHERE clause to your SELECT statements.

The "Standard" Fetch:

SQL
-- Find active users only
SELECT * FROM users 
WHERE deleted_at IS NULL;

The "Soft Delete" Operation:

SQL
-- Instead of DELETE FROM users WHERE id = 123;
UPDATE users 
SET deleted_at = CURRENT_TIMESTAMP 
WHERE id = 123;

Practice Exercise

Imagine our SaaS project includes a projects table. Write the SQL to:

1. Add a deleted_at column to the projects table.
2. Write a query to retrieve all projects that are not deleted.
3. Write the update statement to "soft delete" a project with ID 5.

Common Pitfalls

• Unique Constraint Conflicts: If your users table has a unique constraint on email, you cannot simply create a new account with the same email as a "deleted" user. You will likely need a composite unique index: UNIQUE(email, deleted_at). This allows multiple "deleted" rows to share an email, provided only one active row exists.
• The "Forgotten" Filter: The most common bug is forgetting to add WHERE deleted_at IS NULL to a join or a count query. This often leads to "ghost data" appearing in reports.
• Index Bloat: If your table is massive, keeping thousands of deleted rows can impact scan performance. In a later lesson, we will cover database migrations and archival strategies to move these rows to a dedicated history table.

FAQ

Q: Should I use a boolean is_deleted or a timestamp deleted_at? A: Always use deleted_at. It provides an audit trail showing when the data was removed, which is invaluable for debugging and customer support.

Q: Do I need to update foreign keys? A: Usually, no. If you delete a user, you typically want their related tasks to stay in the database (linked to the "deleted" user) rather than being wiped out by a CASCADE DELETE.

Q: How do I handle restoring a record? A: Simply set deleted_at = NULL for that record.

Recap

Soft deletes transform the destructive DELETE operation into a safe UPDATE. By adding a nullable deleted_at timestamp and consistently filtering with WHERE deleted_at IS NULL, you protect your SaaS product against accidental data loss and empower your users with restoration capabilities.

Up next: We will discuss Data Archiving Strategies to manage the growth of tables that contain many soft-deleted records.