Soft deletes and the deleted_at trap

Soft deletes look like a one-line schema decision and behave like a distributed-systems problem. In the schemas we review, four patterns keep showing up: a nullable deleted_at, a boolean is_deleted, both at once, and a tombstone table. Two of the four break UNIQUE constraints in a way that only surfaces the first time you try to seed the table with realistic data, and by then the fix is a migration, not a config change. This post is the field guide, and the design record for the validator checks we intend to land alongside the v0.3.3 GitHub Action (roadmap).

The short version: if a table has a soft-delete column and a plain UNIQUE constraint on any human-meaningful field (email, slug, username, handle, sku), you almost certainly want the UNIQUE to be a partial index predicated on the row being live. If it isn't, your seed data will collide with itself the moment a synthetic user "signs up again", and your production users will hit the same wall the first time somebody deletes an account and comes back.

The four schemes

#	Scheme	Catalog signal	Breaks under seeding?
1	deleted_at timestamptz NULL	nullable timestamp, name suffix _at	yes, if UNIQUE is not partial
2	is_deleted boolean DEFAULT false	boolean, name prefix is_ or has_	yes, if UNIQUE is not partial
3	Both columns at once	timestamp + boolean, redundant	yes, and they drift
4	Tombstone table (deleted_<name>)	separate table populated by trigger or app	no, correct by construction

Scheme 1 is the default in most ecosystems that ship a soft-delete convention (Rails' acts_as_paranoid, the django-safedelete package, hand-rolled Prisma middleware). Scheme 2 is what people reach for when they want a cheaper predicate and don't need an audit timestamp. Scheme 3 exists because someone added is_deleted for a query, someone else added deleted_at for an audit, and no migration ever consolidated them. Scheme 4 is what you get after the second time schemes 1 or 2 caused an outage.

Why schemes 1 and 2 break under seeding

Consider the canonical users table, in what we see most often in the wild:

CREATE TABLE users (
  id         uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  email      citext NOT NULL,
  deleted_at timestamptz,
  UNIQUE (email)
);

The UNIQUE (email) constraint applies to every row in the table, whether soft-deleted or not. That is not a Postgres quirk; it is the correct behaviour of a full unique index (PostgreSQL docs: Unique Indexes). It means:

• A real user who deletes their account and signs up again with the same email cannot, because the deleted row still occupies the email slot.
• A seeder generating 10,000 synthetic users will collide on email far more often than birthday-paradox math would suggest, because it isn't seeding against 10,000 rows, it's seeding against 10,000 plus every soft-deleted row it already inserted in a prior scenario.

The most common workaround in application code is worse than the constraint: on re-signup, the app clears deleted_at, rewrites email to deleted+<timestamp>+<old-email>, or forks to a second row and points the FK graph at whichever one is "current". All three have shown up in real support tickets, all three eventually produce a row where deleted_at IS NULL and email is deleted+…@example.com, and none of them are recoverable without human review.

The correct primitive is the partial unique index (PostgreSQL docs: Partial Indexes):

CREATE TABLE users (
  id         uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  email      citext NOT NULL,
  deleted_at timestamptz
);

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

Now the constraint means what the application means: at most one live user per email. Deleted rows can coexist, resurrection is a straightforward UPDATE, and the seeder can insert as many soft-deleted duplicates as its fixture wants without touching the constraint.

Scheme 2 rewrites cleanly to the same shape:

CREATE UNIQUE INDEX users_email_live_uidx
  ON users (email)
  WHERE is_deleted = false;

Same idea, same guarantee, no drift risk—provided you never also add deleted_at and forget which is authoritative.

Why scheme 3 is the worst of both

Two soft-delete columns on the same table are a lie the schema tells about itself. There is no CHECK constraint you can write that isn't awkward, and the four states of (deleted_at, is_deleted) include two that should be unreachable:

deleted_at        is_deleted    application reads as        legal?
────────────────  ────────────  ──────────────────────────  ─────────
NULL              false         active                       yes
<timestamp>       true          soft-deleted                 yes
NULL              true          soft-deleted, no audit trail no
<timestamp>       false         "undeleted" by hand          no

Every large schema with both columns eventually produces a small population of rows in the illegal states—usually from a partial migration, occasionally from a rushed backfill, once memorably from a bulk UPDATE users SET is_deleted = false WHERE … that forgot the timestamp. satus refuses to seed the illegal combinations by default. That was easy to add and has caught the bug in three of the last dozen customer schemas we reviewed.

If you already have both columns, the honest fix is a migration that picks one and drops the other. The pragmatic fix is a CHECK that at least makes the illegal states illegal in future writes:

ALTER TABLE users
  ADD CONSTRAINT users_soft_delete_consistent
  CHECK ((deleted_at IS NULL) = (is_deleted = false));

This is one of the checks the next validator pass will flag (see below), since a schema without the constraint is asking the seeder to invent rows the app cannot correctly read.

Scheme 4: tombstone tables

The last pattern moves the deleted row into a separate table on delete, usually via a trigger:

CREATE TABLE users (
  id    uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  email citext NOT NULL UNIQUE
);

CREATE TABLE deleted_users (
  id         uuid PRIMARY KEY,
  email      citext NOT NULL,
  deleted_at timestamptz NOT NULL DEFAULT now(),
  reason     text
);

This is boring, which is the point. users.email is unique because the table only contains live users; deleted_users has no unique constraint on email because the semantics of a tombstone table do not require one. Every FK that pointed at users(id) still resolves as long as the delete flow copies the row before removing it, and the "resurrection" case becomes an explicit INSERT INTO users SELECT … FROM deleted_users that the app can policy-check.

The costs are real: joins that need "all users ever" become UNION ALL, the audit is a separate table to back up and to grant on, and the trigger has to be right. In exchange, you get a schema where soft deletion has no ambient effect on any other query in the system. In the OSS schemas we've audited so far, tombstone tables are rare in general-purpose SaaS and common in healthcare and finance, where regulators effectively require a separately controllable "deleted records" surface.

What the next validator pass will surface

Today (@passkeybridge/satus@0.3.2), the dry-run validator described in Dry-run validation catches unique-column duplicates within a batch, but it does not reason about soft-delete columns or partial indexes. The seeder will happily generate two live rows with the same email if the schema only has a plain UNIQUE (email), and the resulting INSERT fails at the database, not at plan time. That is worth fixing.

The design we intend to land alongside the v0.3.3 GitHub Action work is three catalog-level checks, all warnings, none of them fatal:

• Soft-delete + non-partial UNIQUE. Any table with a soft-delete column (matched with the same suffix/type heuristics from NULL vs NOT NULL is not the question) that also carries a plain UNIQUE on a text/citext/varchar column gets a warning with the exact partial-index migration printed inline.
• Two soft-delete columns, no CHECK. deleted_at and is_deleted on the same table with no constraint tying them together gets a warning that names both columns and the four-state table above.
• Soft-delete column with no index that mentions it. Missing partial indexes are cheap performance bugs; surfacing them at plan time is the cheapest place to catch them.

The warnings will be suppressible per column in the existing ~/.satus/config.json file, and dry-run will keep exiting 0 by default; refusing to seed a legal schema is worse than warning about it. What ships in v0.3.3 is the GitHub Action (roadmap); the validator additions are the follow-up. This post is the design record, not a changelog entry, and we will link the release notes back here when they ship.

What this does not solve

Two adjacent problems that look related and are not:

• Row-level security and soft deletes. If your RLS policy is USING (deleted_at IS NULL), then re-parenting FKs during resurrection can bypass the policy in ways that are subtle enough to warrant their own post. We touched on the general shape in Partitioned tables meet row-level security; the soft-delete-specific version is on the backlog.
• Cascading deletes across a soft-delete boundary. ON DELETE CASCADE fires on hard delete only. Soft-deleting a parent leaves the children live, which is either what you want (audit-preserving) or a bug (dangling references), and the catalog alone does not disambiguate. We will not warn on this, because we would be wrong about half the time.

The one-line takeaway

If you use deleted_at or is_deleted, treat every UNIQUE on that table as suspect until proven partial. The database will not tell you which of your unique constraints is a business rule and which is a leftover from before soft deletes existed.

References

• PostgreSQL documentation, Unique Indexes.
• PostgreSQL documentation, Partial Indexes.
• PostgreSQL documentation, CREATE INDEX.
• PostgreSQL wiki, Don't Do This.
• Earlier in this log: NULL vs NOT NULL is not the question, Check constraints that lie, Dry-run validation.

—the satus.sh team