Module 2: Doing Soft Deletes Right

Correct Soft Deletes — Module introduction

Capsule overview

Soft delete is probably the most widely known and worst implemented pattern in the industry. Almost every multi-tenant app needs it for compliance, recovery, or traceability. Almost every developer knows it ("you add a deleted_at column and you're done"). And almost nobody gets it right.

Mistake #1 is adding WHERE deleted_at IS NULL manually to every query, without a partial index backing the filter. That turns a control column into a silent performance killer, equivalent to the N+1 you already saw in guide #12. Mistake #2 is semantic, not about performance: a teammate forgets the filter in a new query and your API starts showing deleted tasks to customers. Both mistakes are discovered late, in production, and they're incredibly expensive to reverse.

This module kicks off the guide's "Defensive Modeling" block. Here you don't learn to delete data: you learn not to lose it without paying the typical operational price. By the end, you'll have a solid soft delete pattern with SQLAlchemy 2.0 + PostgreSQL 16+, partial indexes that leave your queries in the millisecond range, an automatic mechanism that keeps any developer from forgetting the filter, and clear criteria for deciding when soft delete is NOT the right answer and you should use archive tables or partitioning.

Where are we? Where are we going?

In module 1 you learned to paginate task results with cursor pagination. You solved the "page 50,000 that takes 8 seconds" problem. But there's a question you left open: what happens to deleted tasks? If you hard delete, you lose history and break the audit trail. If you soft delete badly, cursor pagination returns deleted records to your customers and the speedup that cost so much effort gets diluted in queries that scan rows you don't need.

This module closes that loop. You're going to understand when soft delete is the right tool, how to implement it in a way that doesn't turn into technical debt, and how to combine it with the partial indexes you saw mentioned in guide #12 (module 3) without re-explaining them. The new piece is the integration: PostgreSQL + SQLAlchemy + FastAPI doing soft delete idiomatically and safely.

After this module comes module 3 (Audit Logs and History Tables), which closes the defensive modeling block. The transition is natural: you'll already know how to preserve the data when it's deleted; now it's time to learn to preserve the change when it's modified. Soft delete and audit log aren't alternatives, they're complements.

Professional objective

By the end of this module you'll be able to:

Design the soft delete schema with deleted_at TIMESTAMP NULL in PostgreSQL 16+, including the partial index that holds the pattern up on large tables.
Implement and compare the three soft delete mechanisms in SQLAlchemy 2.0 (a before_compile event listener, a custom query class, a mixin with @declared_attr) and pick one as the default for your team with a well-founded rationale.
Refactor the four most common anti-patterns: the forgotten filter, the missing partial index, a soft delete that triggers a cascade, and stats queries that don't exclude deleted rows.
Decide when soft delete stops being viable and you should migrate to archive tables or partitioning by state, with a quantitative decision matrix.
Anticipate the compliance impact: when soft delete does NOT satisfy GDPR's right-to-be-forgotten and you need to combine soft delete + anonymization + deferred hard delete.

Why does this module matter?

1. It's the pattern that generates the most production bugs in SaaS. "I saw a deleted task in my list" is one of the most expensive support tickets: the customer loses trust, debugging is slow because the bug is semantic (the data is where it should be, the query just doesn't filter), and the fix usually requires auditing every query in the codebase.

2. It's the first thing any serious multi-tenant design asks about. In a multi-tenant SaaS, hard delete is usually prohibited by contract (customers ask for recovery, audit, undo). Knowing how to implement soft delete correctly is a prerequisite for architectures that scale beyond a handful of customers.

3. It's where the "automate what can be forgotten" principle pays off most. A team of 10 developers can't remember to add WHERE deleted_at IS NULL to every new query. The automatic mechanism with SQLAlchemy is what separates a professional solution from one that survives only while the original author is still on the team.

4. It's a measurable performance lever. The before-and-after with a partial index is one of the few changes that produce 50x-100x improvements in production without touching the business logic. Knowing how to execute this change without downtime is a senior skill.

A scenario that illustrates the module

Imagine you join as a senior backend developer at a startup with a task management API (not too different from TaskFlow, the guide's final project). The team shows you a GET /tasks endpoint that takes 800ms at p95. The query is simple: SELECT * FROM tasks WHERE author_id = $1 AND deleted_at IS NULL ORDER BY created_at DESC LIMIT 50. The table has 4 million rows. Roughly 60% of the historical tasks are "soft-deleted."

Your first reflex from guide #12 is to check the plan: EXPLAIN ANALYZE shows an Index Scan over (author_id, created_at). But that index also includes the rows with deleted_at NOT NULL, so PostgreSQL walks thousands of deleted rows and discards them one by one. You swap the index for a partial one: CREATE INDEX CONCURRENTLY idx_tasks_active ON tasks (author_id, created_at DESC) WHERE deleted_at IS NULL. The query drops to 12ms.

A week later, support reports that a customer saw a deleted task in their list. You investigate: someone on the team wrote a new endpoint for "assigned tasks" and forgot the AND deleted_at IS NULL filter. The bug is semantic: the endpoint works, the data is consistent, it just returns records it shouldn't. You trace every query in the app and find three more with the same problem.

You decide to automate. You implement a SQLAlchemy mixin with @declared_attr that adds deleted_at to every model, plus a before_compile event listener that injects the WHERE deleted_at IS NULL filter automatically into every query touching models with soft delete. You document the escape hatch (execution_options(include_deleted=True)) for the few legitimate cases where you do need to see deleted rows (auditing, analytics, recovery).

Three months later, one specific table (audit_events) grows to 200 million rows, with 95% of the old events "soft-deleted." The partial index stops being enough: inserts slow down, VACUUM takes hours. You decide to migrate that table to a separate archive table scheme with a nightly job that moves the deleted records. Module 7 will teach you to do that migration without downtime; this module teaches you when to decide it.

That complete cycle (design → automation → scale) is exactly what the module covers.

Module map

Capsule	Topic	What you'll learn
01	Module introduction	Context, objectives, connection with #12 and module 1
02	Soft delete vs hard delete: trade-offs	When each is the right answer, invisible costs, GDPR impact
03	Implementing `deleted_at` in PostgreSQL	Schema, partial index, a measurable before and after
04	Soft delete in SQLAlchemy: mixins and events	Three mechanisms compared; mixin + `before_compile` as the recommended default
05	Partial indexes for soft deletes	The canonical partial index case; a bridge to guide #12
06	Anti-patterns: `WHERE deleted_at IS NULL` everywhere	The forgotten filter, silent cascade, stats queries, broken recovery
07	Alternatives: archive tables and partitioning	When soft delete stops scaling; a bridge to guide #14
08	Project: soft delete in TaskFlow	Refactoring a table with soft deletes that goes from 800ms to 12ms

The module's narrative is: you understand the trade-off (02), you implement the base pattern (03), you automate to avoid human error (04-05), you learn to detect the errors that happen anyway (06), you recognize when the pattern is no longer enough (07), and you apply it all in a measurable project (08).

Connection with the integrative project (TaskFlow)

The module 8 final project (TaskFlow) uses soft delete on tasks.deleted_at with a partial index WHERE deleted_at IS NULL. Specifically, it uses the mixin + event listener pattern we'll recommend in capsule 04 as the default. You'll arrive at module 8 with that pattern already internalized, including:

The reusable SoftDeleteMixin with deleted_at, soft_delete(), and restore().
The globally registered before_compile event listener that injects the filter automatically.
The escape hatch for audit queries that do need to see deleted rows.
Tests that verify the filter is applied only where it should be and is omitted when explicitly requested.

Capsule 08 of this module (the module project) is a mini-TaskFlow: refactoring a real tasks table with 1M rows, 60% deleted, measuring the before and after. The module 8 project extends it with multi-tenancy and audit logs.

What is NOT covered in this module

Audit logs and history tables — covered in module 3 (the next module). They're the natural complement of soft delete: this one preserves the data, the other preserves the change. Seeing them separately makes it clear they're distinct patterns.
Multi-tenancy with RLS — covered in module 4. Soft delete and multi-tenancy combine in the final project, but conceptually they're orthogonal.
Zero-downtime migrations — covered in module 5. When you learn to apply soft delete to a live table without downtime, you'll use CREATE INDEX CONCURRENTLY and a stepped backfill. We mention it here but don't go deep.
Complete right-to-be-forgotten compliance — we only mention it in capsule 02 as a disclaimer. Full compliance (per-column anonymization, deferred hard delete with cron, deletion logs) deserves its own guide.
PostgreSQL declarative partitioning — capsule 07 introduces it as an alternative to soft delete when the table passes 100M rows, but the detailed implementation (PARTITION BY RANGE, pg_partman, hash approaches) is in guide #14 (Advanced PostgreSQL for Backend).

Traps to avoid while taking the module

1. "I already know soft delete, this is review." The base pattern is trivial. What separates a good course from a blog post is what comes after: the automatic mechanism in SQLAlchemy, the decision among the three approaches, the quantitative decision matrix for when to migrate to archive tables, handling the filter in JOINs. Don't skip capsule 04 even if you know what a mixin is.

2. Confusing soft delete with an audit log. They're different. Soft delete says "this record exists but is marked as deleted." An audit log says "this record was modified by X at time Y." Soft delete preserves current state; an audit log preserves the history of changes. If you need complete traceability of changes, soft delete isn't enough, and you'll see that in module 3.

3. Assuming soft delete satisfies GDPR. It doesn't. Right-to-be-forgotten requires that personal data disappear, not that it be marked. Capsule 02 discusses it. If you work with regulated data, soft delete is only one piece of the puzzle.

4. Treating the partial index as an "optional optimization." It isn't. Without a partial index, soft delete at scale is a disguised N+1: every query scans deleted rows it then discards. Capsule 05 demonstrates it with numbers. If you're going to use soft delete, the partial index is part of the pattern, not an extra.

5. Thinking the automatic mechanism is magic that fixes everything. The event listener injects the filter, but it doesn't inject the JOIN ... ON other.deleted_at IS NULL. The automatic filter has limits; you have to know them. Capsule 06 enumerates them explicitly.

Self-assessment questions

Before moving on to the module, can you answer?

What's the difference between DELETE FROM tasks WHERE id = $1 and UPDATE tasks SET deleted_at = NOW() WHERE id = $1 from the point of view of PostgreSQL's MVCC? If you're unsure, review module 4 of guide #8 (PostgreSQL & SQLAlchemy) on row visibility.
What is a partial index and when is it used? If you need a refresher, review module 3 of guide #12 (Database Performance & Query Tuning) on partial and covering indexes.
What is an event listener in SQLAlchemy 2.0? If you've never used @event.listens_for, it isn't a blocker; capsule 04 explains it from scratch.
Does your current app use soft delete? If so, with what mechanism? If not, what would happen if you had to delete a task today and tomorrow the customer asked to recover it?

Evidence of success

By the end of the module, you'll know you succeeded if you can:

Implement soft delete on a new table (mixin + column + partial index + test) in under 20 minutes.
Diagnose a slow endpoint that filters by deleted_at IS NULL by looking at the execution plan and propose a fix with a partial index.
Justify to a teammate why you chose mixin + event listener instead of a custom query class, citing concrete trade-offs.
Identify in a code review queries that forget the filter or that can break from overusing the escape hatch.
Argue when a specific table in the project should migrate from soft delete to an archive table or partitioning, with numbers (volume, delete ratio, access patterns).

We start in the next capsule

Capsule 02 (Soft delete vs hard delete: trade-offs) opens with a deceptively simple question: when do you really delete? You're going to see the invisible costs of each option (latency, space, compliance, recovery) with concrete cases. You'll come away with a decision tree you can apply in code review.

Before moving on, make sure you have a PostgreSQL 16+ running locally or in Docker, a psql session you know how to use, and a FastAPI + SQLAlchemy 2.0 async project to practice in (if you don't have one, the module 1 setup works). The module assumes the whole guide #8 stack and the advanced indexes from guide #12.

Resources for the module

Cultured Systems — "Avoiding the soft delete anti-pattern" — the critical manifesto against the pattern. Worth reading at the start of the module: it trains you to think in trade-offs instead of applying the pattern out of inertia.
Brandur Leach — "Soft deletion probably isn't worth it" — a real case from Stripe. Why soft delete stops being sustainable at a certain scale and how to decide that.
Milan Jovanovic — "Implementing the Soft Delete Pattern" — although it's Entity Framework (.NET), the discussion of automatic filtering mechanisms applies conceptually to SQLAlchemy.
PostgreSQL Documentation — Partial Indexes — the official reference. Soft delete is one of the canonical cases the docs mention explicitly.
SQLAlchemy 2.0 — Mapping Class Inheritance Hierarchies — the basis for understanding mixins and @declared_attr.
SQLAlchemy 2.0 — Events: ORM Events — the reference for the event system we use for before_compile.

Module 2 — SQL Patterns for Production APIs Guide

Next capsule: Soft delete vs hard delete — trade-offs.

← PreviousModule 1 deliverable: Cursor Pagination in TaskFlow Next →Soft delete vs hard delete: trade-offs