Soft Delete Pattern in a Monolith: deleted_at, Filters and Pitfalls

What the soft delete pattern is and how to represent it

A soft delete marks a row as deleted instead of removing it. The row stays in the table, and the application treats it as gone. This makes deletion a lifecycle state rather than an event. Once you adopt it, every query, constraint and downstream consumer has to know about that state. Adding a column is the smallest part of the work.

There are three common ways to represent it:

The deleted_at column usually wins for three reasons. NULL has one meaning, which is "live," so there is no ambiguous default. The timestamp gives you "deleted 30 days ago" directly, and a purge job can use it without extra bookkeeping. It also leaves any existing status column to describe the business process.

Choosing the column is the start. Four concerns depend on each other and have to be designed together:

• Filtering: keeping deleted rows out of reads.
• Uniqueness: stopping deleted rows from blocking new ones.
• Relationships: deciding what happens to children and how restore works.
• Purging: removing deleted rows permanently, on a schedule.

When to use soft deletes, when to avoid them, and the alternatives

Decide per table, not per codebase. Soft delete earns its cost when a specific requirement depends on the deleted row still being queryable: a user-facing undo or restore, an audit trail that must show what existed, foreign keys from historical records such as invoices pointing at a since-removed product, or a legal retention period. "We might need it someday" is not one of these. That reflex adds a filter to every read on the table without a feature that uses the deleted rows.

Two alternatives often fit better:

• Archive table: move the row to a *_archived table in the same transaction. Live queries stay unfiltered, and restore is a move back.
• Hard delete plus audit log: delete the row and write an event recording who deleted it, when, and a snapshot of its contents. History survives without keeping the live table populated.

Soft-deleted personal data is still data you hold. Setting deleted_at does not satisfy an erasure request, so soft delete is not a compliance mechanism.

Whatever you choose, be consistent within a feature. If orders are soft-deleted and line items are hard-deleted, a restore brings back an order with no lines, and users see behavior that differs from screen to screen.

How filtering works: global query filters and the queries that escape them

The filter belongs in one place: the data-access layer. Most ORMs offer a default scope or global query filter that appends deleted_at IS NULL to every query on a model. Without ORM support, a repository base class can add the predicate before any query runs. A third option is a database view over live rows, such as live_orders, which the application reads instead of the base table.

Bypasses should be explicit and easy to grep. Give them names like with_deleted() or include_deleted(), and allow them only in admin, restore and audit code. A reviewer should be able to search for that one call and see every place that reads deleted rows.

What skips the filter

The scope only applies to queries that go through the ORM model. These paths go around it:

• Raw SQL written inside the application.
• Reporting jobs and ad-hoc analytics that connect to the database directly.
• JOINs from an un-scoped table. Starting from customers and joining orders often skips the filter on orders.
• Aggregates and counts built by hand. These inflate totals and give no sign of the error.

Request paths App code → ORM / repository (filter applied) → base table: live rows only Admin, restore, audit → explicit with_deleted() → base table: all rows, visible in review Raw SQL, reports, analytics → base table directly: deleted rows leak Any reader → live_* view or row-level policy: filtered in the database

Views and row-level security policies, where your database supports them, act as a second line of defense. They also cover the clients your ORM never sees.

Treat the filter as a convenience rather than a guarantee. When it fails, you get wrong numbers instead of an error. Implicit scopes also surprise developers who did not know the filter existed, and bypasses tend to multiply over time. Audit those bypass calls regularly.

Copy-- PostgreSQL: database-level second line of defense for soft-deleted orders.
-- Assumes roles app_rw (application) and audit_ro (admin/restore/audit) exist.

-- 1. A view over live rows that any client can read instead of the base table.
--    security_invoker (PostgreSQL 15+) makes RLS below apply to the caller, not the view owner.
CREATE VIEW live_orders WITH (security_invoker = true) AS
  SELECT * FROM orders WHERE deleted_at IS NULL;

-- 2. Row-level security catches reads that go around the ORM (raw SQL, reports, JOINs).
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- Application role only ever sees live rows, whatever query shape it uses.
CREATE POLICY orders_live_only ON orders
  FOR SELECT TO app_rw
  USING (deleted_at IS NULL);

-- The explicit, greppable bypass: only the audit role sees deleted rows.
CREATE POLICY orders_audit_all ON orders
  FOR SELECT TO audit_ro
  USING (true);

-- 3. The classic leak: starting from an un-scoped table and counting by hand.
--    Without the policy this counts deleted orders and returns no error.
--    With it, app_rw gets live-only counts even though the query never mentions deleted_at.
SELECT c.id, count(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id;

-- Caveats: the table owner and roles with BYPASSRLS skip these policies
-- (use FORCE ROW LEVEL SECURITY to apply them to the owner). The policies above
-- only cover SELECT, so UPDATE and DELETE need their own policies before you rely on this.

A PostgreSQL live-rows view and row-level security policies that filter soft-deleted orders for the application role. The audit role gets an explicit bypass, and a hand-built JOIN count shows the kind of query the ORM scope would miss.

Unique constraints and indexing for live rows

A plain UNIQUE(email) constraint knows nothing about deleted_at. The soft-deleted row still holds the value, so when that user signs up again, the insert fails even though the application considers the address free. The constraint has to cover only live rows. There are three portable ways to do that.

• Partial or filtered unique index. Declare uniqueness on email with WHERE deleted_at IS NULL. Deleted rows fall outside the index entirely. This is the cleanest option, but not every database supports predicates on indexes.
• Composite key with a sentinel. UNIQUE(email, deleted_at) alone does not work. Most databases treat NULLs as distinct in unique constraints, so two live rows with the same email would both pass. Store a fixed non-NULL sentinel for live rows, such as a far-past timestamp, and the real time on deletion. The pair is then unique per live row. The tradeoff is that every query has to compare against the sentinel instead of IS NULL.
• Move deleted rows to an archive table. The live table keeps an ordinary constraint.

Restore creates a new conflict. If a new live row has taken the email, un-deleting the old row will violate the constraint. You need a policy for this case: refuse the restore, merge the two records, or restore under a modified value. Decide it before the first support ticket arrives.

Indexes should follow the live-row predicate. Use a partial index, or a composite index that leads with the columns you filter on and includes deleted_at. Without one, deleted rows pile up in every index range scan. The planner's row estimates also drift as those rows accumulate, and plans that were fast at launch slowly degrade.

Copy-- PostgreSQL syntax. Option 1: partial unique index.
-- Soft-deleted rows fall outside the index, so a deleted
-- address can be registered again.
CREATE TABLE users (
  id         bigserial PRIMARY KEY,
  tenant_id  bigint      NOT NULL,
  email      text        NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  deleted_at timestamptz NULL
);

CREATE UNIQUE INDEX users_email_live_uq
  ON users (email)
  WHERE deleted_at IS NULL;

-- Index the common live-row access path with the same predicate,
-- so range scans never visit deleted rows.
CREATE INDEX users_tenant_live_idx
  ON users (tenant_id, created_at)
  WHERE deleted_at IS NULL;

-- Option 2 (no partial indexes): use a non-NULL sentinel for live rows.
-- With NULL, two live rows with the same email would both pass,
-- because NULLs are treated as distinct in unique constraints.
--   deleted_at timestamptz NOT NULL DEFAULT '1970-01-01 00:00:00+00',
--   UNIQUE (email, deleted_at)
-- Live-row queries then filter on deleted_at = '1970-01-01 00:00:00+00'.

-- Restore with a "refuse on conflict" policy: un-delete only if no
-- live row has taken the email. Zero rows updated means a conflict to
-- report. The unique index still catches a concurrent insert that
-- slips past this check.
UPDATE users AS u
SET    deleted_at = NULL
WHERE  u.id = 42
  AND  u.deleted_at IS NOT NULL
  AND  NOT EXISTS (
         SELECT 1 FROM users AS live
         WHERE  live.email = u.email
           AND  live.deleted_at IS NULL);

A PostgreSQL schema that enforces email uniqueness only among live rows and indexes the live-row access path with partial indexes. It shows the sentinel-value alternative and a restore that refuses when a live row already holds the email.

Relationships, cascades and restore

A foreign key checks that the parent row exists. It does not check whether that row is live. An order can reference a soft-deleted customer, and the database will accept new inserts pointing at that customer without complaint. ON DELETE CASCADE never fires because nothing is actually deleted. Referential integrity for live data now lives in application code.

When a parent is soft-deleted, you have three options for its children:

• Cascade in code. Mark the children deleted in the same transaction as the parent.
• Block. Refuse the delete while live children exist.
• Leave orphans visible. This is acceptable only if every screen and job can tolerate a child whose parent the global filter hides.

For cascades, stamp the parent and every child with one shared deleted_at value inside a single transaction. Restore then reverses exactly that operation: within one transaction, clear deleted_at on the parent, and on children only where the timestamp matches. Children that were deleted earlier, on their own, stay deleted. Without the shared timestamp, restore cannot tell which children were removed by the cascade and which were removed deliberately before it.

Even with correct ordering, restore is rarely just setting deleted_at back to NULL:

• Uniqueness conflicts. A live row may now hold the restored row's email or slug.
• Side effects already ran. The cancellation email went out, billing stopped, and caches and search indexes dropped the record.

Each of these needs an explicit reversal or a decision not to reverse it. Restore should therefore be treated as a product feature with its own tests.

Copy-- PostgreSQL. now() returns the transaction start time, so every
-- statement in this transaction stamps the same deleted_at value.

-- Cascade soft delete: customer 42 and its live orders.
BEGIN;
UPDATE customers
   SET deleted_at = now()
 WHERE id = 42 AND deleted_at IS NULL;

-- Only live orders join the cascade. Orders deleted earlier
-- keep their original, different timestamp.
UPDATE orders
   SET deleted_at = now()
 WHERE customer_id = 42 AND deleted_at IS NULL;
COMMIT;

-- Restore: reverse exactly that cascade.
BEGIN;
-- Lock the parent so a concurrent delete or restore cannot interleave.
SELECT deleted_at FROM customers WHERE id = 42 FOR UPDATE;

-- Restore children first, while the parent still holds the shared
-- timestamp. Match on equality so earlier deletions stay deleted.
UPDATE orders o
   SET deleted_at = NULL
  FROM customers c
 WHERE c.id = 42
   AND o.customer_id = c.id
   AND o.deleted_at = c.deleted_at;

UPDATE customers
   SET deleted_at = NULL
 WHERE id = 42 AND deleted_at IS NOT NULL;
COMMIT;
-- Unique conflicts, emails, billing and cache or search reindexing
-- are not handled here. They need their own explicit restore steps.

A transactional cascade that stamps a customer and its live orders with one shared deleted_at, and a matching restore that brings back only the orders whose timestamp equals the parent's.

Purging, retention and the systems downstream

Purging should be designed in from the start. Without it, "deleted" becomes "kept forever," and the table carries rows nobody reads.

How fast deleted rows accumulate

Illustrative estimate. Assume a table holds a steady 1,000,000 live rows, 30% of them (300,000) are soft-deleted each year, and inserts replace them so the live count stays flat. With no purging, deleted rows reach 300,000 after year one, 900,000 after year three, and 1,200,000 after year four. At that point they outnumber live rows. Storage and backup size both more than double. Vacuum or equivalent maintenance has to scan the dead weight. Indexes without a live-row predicate bloat, and the planner's statistics describe a table that is mostly irrelevant to real queries.

The purge job

Run a scheduled job that hard-deletes rows whose deleted_at is older than the retention window. Delete in small batches ordered by primary key, commit each batch, and pause between batches. One large DELETE holds locks for a long time and produces a burst of write-ahead or binary log that replicas then lag behind.

Erasure requests skip the retention window and hard-delete immediately. Backups cannot be edited, so you need one of two approaches. Either let backups expire within a documented period, or keep a list of erased IDs and re-apply it after any restore.

Downstream copies

Caches, search indexes, event consumers and exports each hold their own copy of the data. Emit explicit delete, restore and purge events, ideally written in the same transaction as the row change. If you skip this, a record that is deleted in the database will keep reaching users through these other systems.

Copy# Nightly purge: hard-delete soft-deleted rows past retention, in small batches.
# Assumes PostgreSQL, psycopg2, and an outbox_events table read by a relay
# that fans events out to caches, search indexes and export jobs.
import os
import time
import psycopg2

RETENTION_DAYS = 90   # policy value, not a recommendation
BATCH_SIZE = 1000     # small enough to keep lock time and log bursts short
PAUSE_SECONDS = 0.5   # gives replicas time to catch up between batches

# Postgres DELETE has no LIMIT, so pick a batch of ids first.
# The outbox insert shares the transaction, so no purge goes unannounced.
PURGE_BATCH = """
WITH batch AS (
    SELECT id FROM orders
    WHERE deleted_at < now() - make_interval(days => %s)
    ORDER BY id
    LIMIT %s
), gone AS (
    DELETE FROM orders o USING batch b
    WHERE o.id = b.id
    RETURNING o.id
)
INSERT INTO outbox_events (aggregate, aggregate_id, event_type)
SELECT 'order', id, 'purged' FROM gone
"""

conn = psycopg2.connect(os.environ["PURGE_DATABASE_URL"])
try:
    while True:
        with conn:  # one transaction per batch; commits on exit
            with conn.cursor() as cur:
                cur.execute(PURGE_BATCH, (RETENTION_DAYS, BATCH_SIZE))
                purged = cur.rowcount
        if purged < BATCH_SIZE:
            break  # nothing left past the retention window
        time.sleep(PAUSE_SECONDS)
finally:
    conn.close()

A batched PostgreSQL purge job that hard-deletes soft-deleted rows older than the retention window, commits each batch separately, and writes a 'purged' outbox event in the same transaction so downstream caches and indexes can drop their copies.

Rolling it out in an existing monolith: cost and engineering time

Retrofit in an order where each step is safe to ship on its own:

1. Add deleted_at as a nullable column. Existing rows stay live, and the migration needs no backfill, so it does not block deploys.
2. Put the filter behind the data-access layer. Use one repository method, scope or global query filter, not a WHERE clause copied into every handler. Ship it before any code writes deleted_at, so reads are already correct when the first soft delete happens.
3. Find the call sites that bypass it. Grep for raw SQL, report queries and direct table names. Then enable query logging in staging and flag every statement against the table that lacks the deleted_at predicate. Grep finds the code paths. Logging finds the queries that are generated at runtime.
4. Add tests that hold the invariant. Soft-delete a fixture, then call each user-facing list, detail, search and export endpoint and assert the row is absent. New endpoints then fail CI instead of leaking rows.

Plan the cost honestly. The filter itself is the cheap part. Restore semantics, cascading to child rows and a purge job are each separate features that need their own maintenance. Retrofits often stall after step 2, which leaves a filter everyone trusts and a few paths that ignore it.

The pattern saves time in three places:

• Support "undo" requests become a single update instead of a backup restore.
• Accidental deletes stop turning into recovery incidents.
• "Who removed this, and when?" has an answer in the row itself.

If restores are rare and you mainly need a record of what happened, a hard delete plus an audit log is cheaper to build and maintain. So is moving deleted rows to an archive table, because neither approach needs a filter on every query.

How OneEnv can help

Disclosure: OneEnv is the product behind this blog.

OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.

Applied to soft delete pattern, this is where it helps:

Saves time

• Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
• One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
• Changing a route's URL or method automatically notifies the services that consume it — no hunting through repos for callers.

Saves cost

• Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
• Less duplicated configuration to maintain: one shared definition inherited by many services.
• Consumers learn about a breaking change at review time rather than from production errors.
• OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.

These are qualitative benefits, not measured results — how much you save depends on how many services share the value.

Want to try it? Start at oneenv.io.