Soft delete

Keep deleted rows as tombstones, hide them from reads, restore or purge them

softDelete() is the official soft-delete extension. On the models you name, a delete stops removing the row: it writes the time (and, optionally, who did it) into the row instead. Reads then skip those rows, everywhere a query reaches them.

The extension is one file of about 100 lines, built only from public extension capabilities (controls, rows and deletion, see Create an extension). It lives in its own entry point, so an application that does not import it pays nothing for it.

import { s } from "viborm";
import { softDelete } from "viborm/soft-delete";

const post = s.model({
  id: s.string().id(),
  title: s.string(),
  createdAt: s.dateTime(),
  authorId: s.string(),
  author: s.toOne(() => user).fields("authorId").references("id"),
  deletedAt: s.dateTime().nullable(), // the marker: null while the post is live
  deletedById: s.string().nullable(), // optional: who deleted it
});

const db = softDelete({
  models: { post: { deletedAt: "deletedAt", deletedBy: "deletedById" } },
  actor: session.userId,
})(base);

await db.user.findMany({ include: { posts: true } }); // each user's live posts
await db.post.findMany({ deleted: "only" });          // recycle bin
await db.post.delete({ where: { id: "p1" } });        // tombstones p1, returns it
await db.post.restore({ where: { id: "p1" }, select: { id: true } }); // { id }

softDelete(config) returns a function that takes a client and returns a new client. The client you pass in is unchanged: keep it for maintenance work that must see every row.

Apply defaultOmit() and any other extension that declares rows before softDelete(): the restore methods it adds are model methods, and neither may follow those. The cache can go on either side.

Configuration

A definition is not checked at runtime. TypeScript flags a misspelt member in your editor; an unknown model name, a field of the wrong type or a removeWhen that names no control is not caught and misbehaves at the first call. In softDelete, a model name the schema lacks is still refused when the extension is applied, through its restore methods.

One actor per client, one actor per transaction

actor is fixed for the client softDelete() returns. For a per-request actor, derive one client per request:

function clientFor(session: Session) {
  return softDelete({ models, actor: session.userId })(base);
}

Deriving costs two $extends calls, about 11 µs (an estimate: two derived clients at about 5.5 µs each, from one measurement). A transaction client has no $extends, so one transaction has one actor: derive the client with the right actor before calling $transaction, and use a separate transaction for a different actor.

Reading: deleted

Every read, count, aggregate, groupBy, update and delete of every model accepts deleted:

The domain applies wherever a query reaches a managed model through a to-many relation: include and select of related rows, some/none/every, _count, ordering by a relation count, nested pages, and recursive reads (a tombstoned node cuts its branch). A cursor that points at a hidden row is a missing cursor: the page is empty.

await db.post.count();                        // live posts
await db.post.count({ deleted: "only" });     // tombstones
await db.user.findMany({ where: { posts: { none: {} } } }); // users with no live post

On a model you did not configure, deleted changes nothing about that model’s own rows; it only chooses what its relations to managed models show. only on such a model reads its related posts live, like without.

Recipes: with and only

• Recycle bin: findMany({ deleted: "only" }). The bin’s posts show their live related rows.
• Deleted posts with everything related, deleted or not: findMany({ deleted: "with", where: { deletedAt: { not: null } } }).
• Filtering on the marker needs deleted. Without it, where: { deletedAt: { not: null } } returns []: the live default is added to your filter.

To-one relations read null for a tombstone

A to-one relation to a configured model (say a comment model with a required post, or a polymorphic subject one of whose targets is post) reads null while its target is a tombstone, as if the target were missing, and a nested connect or update through it treats the tombstone as missing too. The example’s post.author is unchanged: user is not configured. is/isNot filters and ordering by a to-one field read the same way, and an upward recursive read (parent with recurse) stops at a tombstoned node.

const comment = await db.comment.findUniqueOrThrow({
  where: { id: "c1" },
  include: { post: true },
});
comment.post; // Post | null: null while the post is a tombstone

So on a client with the extension, TypeScript types every to-one relation to a configured model as | null, even one that is required in the schema and even under deleted: "with" (the type does not read the call’s mode). A to-one relation to a model you did not configure keeps its type, unless that model has exactly the same field and relation names as a configured one: TypeScript compares models by those names and cannot tell the two apart, so both read | null.

A reference that points at a row that does not exist at all is still an error, as it is without the extension: a polymorphic relation whose row is gone throws “references a missing record”, while one whose row is a tombstone reads null.

OperationResult, InferDatabase and renderOperationResultType describe the schema alone and do not know about the extension: on a soft-delete client they type a to-one relation as present where it can read null. Use ExtendedOperationResult<typeof db, "comment", "findMany", Args> for the client’s own result types. A model-mapped query handler applied to the soft-delete client reads its proceed() result the same way.

Deleting

delete, deleteMany, and nested delete/deleteMany through a relation write the tombstone instead of removing the row:

• delete returns the row as it is after the tombstone (its “post-image”), including deletedAt and deletedBy. deleteMany returns { count } of the live rows it tombstoned.
• A delete only takes live rows, whatever deleted says. Deleting a tombstone again is NotFoundError, as deleting a missing row is.
• deleteMany with limit takes the first live rows by id: limit: 10 tombstones the ten first rows not deleted already, ordered by primary key, the rows mode: "hard" would delete on the same data. The live-child check looks only at those rows.
• Nothing cascades and no link is removed: foreign keys, join-table rows and memberships stay, so a restore brings the row back with its relations.
• updatedAt fields are refreshed, as by any update.
• Query and observe handlers, errors and logs still see a delete.

One timestamp per call

Every row one call tombstones gets the same deletedAt, nested rows included. Separate calls get separate times, even inside one transaction. That lets you undo one call:

await db.user.update({
  where: { id: "u1" },
  data: { posts: { deleteMany: { title: { startsWith: "draft" } } } },
});
// All of those posts share one deletedAt, t:
await db.post.restoreMany({ where: { authorId: "u1", deletedAt: t } });

To delete or restore a parent together with its children under one shared time, write the marker yourself in one transaction: updateMany({ data: { deletedAt: at } }) per model. That bypasses the extension: no actor, no live-rows rule and no referential check.

What happens to related rows

What the database would do on a physical delete, and what a soft delete does instead:

The live-child check reads the child model’s default (live) rows whatever the call’s deleted says. A child model you did not configure has no tombstones, so any child blocks, as the database does. A nested delete does not count the parent it runs under: the parent’s own link never blocks it.

On PostgreSQL and MySQL the check holds under concurrency too: a soft delete locks the rows it is about to tombstone before it looks for live children. A create that connects one of them at the same moment either lands first, and the delete is refused, or waits and then finds its target deleted.

disconnect and set act on the rows the call sees, like every nested write: disconnecting a tombstoned row is a NestedWriteError, and set keeps a tombstoned member linked, so a restore brings it back with its links. Pass deleted: "with" to unlink a tombstone. set over a required foreign key keeps today’s refusal (“rows removed from the set cannot be disconnected. Delete them instead.”), and counts tombstoned children too. Purge the tombstoned child first.

Restoring

restore and restoreMany exist on the configured models. They take the model’s own update/updateMany arguments without data, select only tombstones, and clear deletedAt and deletedBy:

const { id } = await db.post.restore({ where: { id: "p1" }, select: { id: true } });
await db.post.restoreMany({ where: { deletedAt: { gte: since } } });

• The result is narrowed like update’s: select and include shape it.
• Restoring a live row is NotFoundError.
• TypeScript flags data and a misspelt key in your editor. It also flags another extension’s argument on restore, such as the cache’s cache option: pass it on an ordinary update instead.
• Restoring a row whose unique key a live row now holds fails with UniqueConstraintError; inside a transaction the transaction rolls back.

Purging: mode: "hard"

mode: "hard" on delete or deleteMany of a configured model deletes physically, with the database’s own referential actions. TypeScript flags mode on any other model or operation.

await db.post.deleteMany({
  where: { deletedAt: { lt: cutoff } },
  deleted: "only",
  mode: "hard",
});

Unique keys

A tombstone keeps its values, so a total unique index still counts it: re-creating a row with a soft-deleted row’s email fails with UniqueConstraintError. To make a key unique among live rows only, use a partial unique index:

const user = s
  .model({
    id: s.string().id(),
    email: s.string(),
    deletedAt: s.dateTime().nullable(),
  })
  .index(["email"], { unique: true, where: '"deletedAt" IS NULL' });

• where is raw SQL naming the physical column. PostgreSQL and SQLite build it; MySQL has no partial index and refuses the declaration (use a generated column there).
• The cost: a partial index is not a unique selector. findUnique, upsert, connect and connectOrCreate by email become errors, flagged by TypeScript in your editor; use findFirst({ where: { email } }).

upsert and connectOrCreate never restore: a tombstone holding the key is a conflict. Restore first, or pass deleted: "with" to upsert to update the tombstone as it is.

Caching

With the official cache, a cached read is keyed on its resolved deleted value: an absent deleted and deleted: "without" share one entry, "with" and "only" have their own. A soft-delete client and the client it was built from never share entries. A delete with cache: { autoInvalidate: true } clears the complete bound cache scope after a durable write, including entries for other models that include the deleted row, as after a hard delete. Automatic invalidation is enabled by default; opting out requires manual invalidation. The cache works in either order: softDelete(config)(client.$extends(cache(…))) or softDelete(config)(client).$extends(cache(…)).

Adopting soft delete

1. Add deletedAt: s.dateTime().nullable() (and the actor field, if any) and migrate before deploying the extension.
2. Replace where: { deletedAt: … } filters with deleted.
3. Backfill a boolean isDeleted into a nullable DateTime marker.
4. List the cascade relations that stop firing and the restrict relations that now refuse a delete while live children exist.
5. Decide your unique keys (partial unique index or not) and review raw SQL.
6. Removing the extension is not a rollback: every tombstone reappears in reads.

Not covered

Soft delete controls which rows reads and deletes see. It does not apply to:

• create and createMany, or the data of an update;
• linking and unlinking (connect, disconnect, set) beyond the rules above;
• which fields a query may use;
• raw SQL ($queryRaw, $executeRaw) and statement transforms, which see every row.

deleted: "with" and raw SQL reach every tombstone: soft delete is not erasure. A marker is not a version: deleting, restoring and deleting again leaves no trace of the first deletion.

Never use soft delete (or the rows capability it is built on) for tenant isolation or authorization. Any caller can pass deleted: "with", and raw SQL ignores it.