Once a table carries a deleted_at column, every read in the system has to exclude those rows. What mechanisms keep that filter from being forgotten, and where do the leaks typically show up?

## The problem Soft delete converts a structural guarantee (the row is gone) into a convention (everyone remembers a predicate). Conventions decay: new joiners, new services, ad-hoc SQL from analysts, a quick fix at 2am. Each omission is a silent correctness bug — no error, just wrong data on a screen or in a number someone reports upward. So the engineering question is not "how do I remember the filter" but "how do I make the unfiltered form the unusual one". ## Mechanism 1: filtered views (rename-and-view) Rename the physical table to `orders_all` and create: ``` CREATE VIEW orders AS SELECT * FROM orders_all WHERE deleted_at IS NULL; ``` Every existing query that says `FROM orders` is now filtered without being touched. Reaching dead rows requires deliberately typing `orders_all`, which is greppable and reviewable. Simple views are usually updatable, so writes still work; if not, add `INSTEAD OF` triggers or write through the base table explicitly. Downsides: two names to keep in your head, migration tooling that introspects tables can get confused, and some ORMs need help mapping a view for writes. ## Mechanism 2: ORM-level default scopes Hibernate's `@SQLRestriction` (formerly `@Where`), Rails' `default_scope`, Django managers and similar features append the predicate automatically. This is the lowest-friction option and catches the majority of application reads. Its limits are real: raw SQL, native queries, reporting tools, other services sharing the database, and data-migration scripts all bypass it. It also becomes awkward the moment you legitimately need the deleted rows (admin restore screens, purge jobs), and `default_scope` in particular has a habit of leaking into inserts and associations in surprising ways. ## Mechanism 3: row-level security Engines with RLS let you attach a policy to the table so the predicate is applied by the database itself, whatever client issues the query. That covers psql sessions, BI tools and every service. You then grant a `bypassrls`-style role, or a policy keyed off a session setting, to the few jobs that must see dead rows. This is the strongest guarantee short of not storing the rows at all; the cost is an extra predicate the planner must handle on every access and a policy surface that has to be tested like any other security control. ## Mechanism 4: do not mix states The structural fix: hard delete from the live table and insert into an archive table in the same transaction. Nothing to filter, constraints keep their meaning, the hot table stays small, and restore is an explicit, auditable operation. The trade is that restore is more work and cross-state queries need a `UNION`. ## Where leaks actually show up - **Aggregates.** `COUNT(*)`, `SUM(amount)`, dashboard tiles and monthly reports are written by different people, often outside the application, and are exactly where a missing predicate becomes a visible wrong number. - **The other side of a join.** Developers filter the table they are thinking about. `JOIN customers c ON …` without `AND c.deleted_at IS NULL` resurrects deleted customers through their live orders. - **Outer joins.** Putting a parent-side predicate in `WHERE` rather than in the join condition turns a `LEFT JOIN` into an inner join and drops rows that should have appeared with NULLs — a filter that is present but placed wrong. - **Derived and cached data.** Materialized views, denormalized counters, Redis caches, Elasticsearch documents and analytics extracts were all populated before the delete. Soft delete does not notify them; you need an explicit invalidation path, exactly as with a hard delete. - **Batch and integration paths.** Nightly exports, partner feeds, billing runs and data migrations are the classic leak sites because they are written once and rarely reviewed. - **Lookup-by-identifier paths.** "Find the user by email" must decide whether a deleted user counts — for login it must not, for signup collision handling it might. ## Do not forget the plan A predicate on every query changes the physics of your indexes. If dead rows accumulate, an index on `(customer_id)` now contains mostly rows the query will throw away, so more pages are read for the same result and cardinality estimates drift. Two fixes: make the hot indexes partial (`WHERE deleted_at IS NULL`), which shrinks them to the live set and lets the engine skip the filter entirely, or lead composite indexes with the flag. Partial indexes are the better answer where the engine supports them, and they pair naturally with the partial *unique* index the same schema already needs. ## Verification Whatever mechanism you pick, add cheap tests: a repository/integration test per entity asserting a soft-deleted row is invisible, a lint or grep rule flagging `FROM orders_all` outside approved files, and a periodic reconciliation query comparing a dashboard number against a known-correct filtered query. Correctness that depends on memory should always be backed by something automated.