Soft delete

softDelete and restore give you reversible deletes — a deletedAt timestamp instead of a destructive DELETE. forge-orm wires partial-filter indexes, query-time defaults, and per-dialect emit to make soft-delete safe across all six adapters.

The Soft delete section of the README is the short tour. This page is the full reference for how the column declaration plumbs through to reads, writes, indexes, events and the compile API, plus the operational patterns that keep soft-delete from rotting over time.

What soft delete is
Declaring the column
softDelete and restore
Query-time defaults
include is scoped at every depth
The four things that used to disagree with include
Partial-filter indexes
Cascading soft deletes
Restore semantics
Hard delete after retention
Per-dialect emit
Events and audit interaction
GDPR and right-to-be-forgotten
Search and vector index interaction
Browser sqlite-wasm
Worked patterns
Anti-patterns
See also

What soft delete is

A soft delete is a write that marks a row as deleted rather than removing it. Instead of DELETE FROM posts WHERE id = $1, the row stays on disk with a timestamp stamped into one column — usually called deleted_at — and every subsequent read filters that column out.

The properties that fall out:

Recoverable. A soft-deleted row can be restored by clearing the timestamp. A hard-deleted row is gone (modulo PITR / backups).
Auditable. The row is still in the table; you can see what was deleted and when, without joining to an audit log.
Cascade-aware in app code. Children of a soft-deleted parent can be soft-deleted in the same call, so the graph stays consistent.
Not free. Indexes still cover the tombstones, uniqueness constraints still match against them, and every read pays a WHERE deleted_at IS NULL predicate. The fixes — partial-filter uniques and an index on deleted_at itself — are below.
Not erasure. A row with deleted_at set is still personal data. GDPR's right-to-be-forgotten requires hard delete or anonymisation — see GDPR and right-to-be-forgotten.

forge keeps the model surface explicit: a delete() call always hard deletes; the recoverable verb is named softDelete(). The two are different statements at the wire — there is no "is this soft-delete or hard-delete?" depending on the model shape.

Declaring the column

One field on the model, marked with .softDeleteAt():

const Post = model('posts', {
  id:         f.id(),
  title:      f.string(),
  body:       f.string(),
  deleted_at: f.dateTime().softDeleteAt(),
});

.softDeleteAt() does three things:

Forces the column optional (it is null until the row is soft-deleted — even if you wrote .dateTime() without .optional(), the chainable modifier flips the flag for you).
Marks the field on the IR so the wrapper can find it ((field as any) .softDeleteAt === true).
Tags the model as participating in soft-delete: reads filter, the softDelete / restore verbs are valid, and the runtime emits semanticOp on the resulting QueryEvent.

Exactly one per model. The wrapper finds it by iterating Object.entries(model.fields) and picking the first column whose softDeleteAt flag is set — declare it twice and only the first wins, which is a footgun worth avoiding.

The column name itself is free. deleted_at, deletedAt, removed_at, archived_at, tombstoned_at — all valid. The wrapper doesn't pattern-match the name; it reads the flag.

Type

Use f.dateTime() (timestamp with millisecond precision). A boolean — is_active, is_deleted — works for the filter but loses when, which matters for cascade decisions, retention policies, and audit reads. See Anti-patterns.

`softDelete` and `restore`

Four verbs on every collection wrapper:

Verb	`where`	Returns	Notes
`softDelete`	any filter	the updated row	affects ONE matching row; throws if none matched
`softDeleteMany`	any filter	`{ count }`	zero matches is a no-op
`restore`	any filter	the updated row	affects ONE matching row; throws if none matched
`restoreMany`	any filter	`{ count }`	zero matches is a no-op

Same asymmetry as update vs updateMany — see docs/MUTATIONS.md.

softDelete and restore do not narrow where to unique columns. They take the same full filter type as everything else, and nothing checks it — not the compiler, not the wrapper. Hand softDelete a filter that matches forty rows and exactly one of them gets stamped; which one is unspecified. On Postgres the emitted SQL is WHERE ctid = (SELECT ctid FROM … WHERE … LIMIT 1) with no ORDER BY; on Mongo it is findOneAndUpdate. Either way the call succeeds, reports one row, and leaves the other thirty-nine live — a "we deleted that" bug that no error ever surfaces.

Use the plural verbs (softDeleteMany, restoreMany, and likewise updateMany / deleteMany) when you mean "however many rows match".

await db.post.softDelete({ where: { id: 'p1' } });
await db.post.softDeleteMany({ where: { author_id: 'u9' } });

await db.post.restore({ where: { id: 'p1' } });
await db.post.restoreMany({ where: { author_id: 'u9' } });

Each verb compiles to a regular update / updateMany against the same table, with the soft-delete column as the only changed field:

softDelete → data: { [sd]: new Date() }
restore → data: { [sd]: null }

The wrapper goes through its own update path so select, include, omit, and the QueryEvent plumbing all behave identically. The one difference is the semanticOp field on the event — see Events and audit interaction.

What gets thrown

Calling any of the four soft-delete verbs on a model with no.softDeleteAt() column throws synchronously:

[forge] softDelete() requires a field declared with .softDeleteAt() on
'audit_logs'. Either add one, or use delete() for a hard delete.

This is the gate that keeps you from accidentally writing a soft-delete to a model that doesn't support it. It triggers before the wire call — no half-applied update.

`softDelete` reaches across the read filter

softDelete (and restore) skip the auto-filter that hides soft-deleted rows from reads. That's deliberate: restoring a row whose deleted_at is set requires finding it, and a strict filter would make that impossible. So:

softDelete({ where: { id } }) works whether or not the row is already soft-deleted (idempotent at the row level; re-stamps deleted_at).
restore({ where: { id } }) finds the soft-deleted row directly — the read filter is bypassed for these verbs.

If you need to know "was this row already soft-deleted?", do a findFirst({ where: { id, _withDeleted: true } }) first.

The same reach applies to update and to updateFirst (2.20.0), and it is what makes a soft-deleting patch returnable in one round trip. A read is filtered, so updateMany followed by findFirst of the same id returns null whenever the patch is what set the soft-delete column — the row it just wrote is now hidden from the read. updateFirst is not filtered and returns the post-update row from the write statement itself, so there is nothing to re-read and nothing to reconstruct:

// null. Every time — the read cannot see the row it just soft-deleted.
await db.post.updateMany({ where: { id }, data: { deleted_at: new Date() } });
const gone = await db.post.findFirst({ where: { id } });

// the row, deleted_at set, one round trip
const post = await db.post.updateFirst({ where: { id }, data: { deleted_at: new Date() } });

See docs/MUTATIONS.md.

Query-time defaults

Every read against a model with a .softDeleteAt() field has WHERE <sd> IS NULL appended to its where clause — by default, you never see soft-deleted rows.

Affected verbs:

findFirst, findFirstOrThrow
findUnique, findUniqueOrThrow
findMany
findManyStream (see docs/QUERIES.md)
count
groupBy, and aggregate({ _sum, _avg, _min, _max, _count }) with it — the typed whole-table form routes through groupBy (since 2.19.0; before that a revenue total quietly included deleted orders)
every relation sub-select reached through include or select, at any depth — see include is scoped at every depth
every relation filter — where: { posts: { some: … } } and its four siblings only consider live children
relation _count

aggregate({ pipeline }) / aggregate([...]) — the Mongo escape hatch — is not on that list: it hands your pipeline straight to the driver, so a $match you did not write yourself is never added. Filter the soft-delete column in the pipeline yourself. Same for $queryRaw / $executeRaw.

The augmentation is suppressed in three cases:

_withDeleted: true — the explicit opt-in:
ts
```
await db.post.findMany({ where: { _withDeleted: true } });
```
The wrapper strips _withDeleted from the where before building the IR; the rest of the filter passes through.
You filtered the column yourself. If the where already mentions the soft-delete column — e.g. where: { deleted_at: { not: null } } for an admin "trash" view — the auto-filter is skipped. Your filter wins.
The verb is a write that bypasses the read filter. softDelete, restore, update, delete, and all the other write verbs do not apply the read filter — by design. update({ where: { id } }) on a soft-deleted row still hits the row.

_withDeleted is a real key on WhereInput (declared in the strict-mode allow-list alongside AND, OR, NOT). It works at the top level of any read, inside an include's where, and inside a relation filter — at each level it opts that level out, and it is stripped before the where-builder sees it either way. It is a directive, not a column.

Strict mode and `_withDeleted`

When the database is created with createDb({ strict: true }), unknown where keys throw. _withDeleted is on the strict allow-list, so it passes — you can use it on any model. (On a model with no .softDeleteAt() column it's a no-op — the wrapper has nothing to filter.)

`include` is scoped at every depth

Until 2.19.0 the auto-filter rewrote the top-level where and nothing else. Relation sub-selects are built in the IR and run by the adapters, and neither had any soft-delete awareness, so soft-deleted children came back through every include — a post the user had trashed was hidden from db.post.findMany() and visible under their account. Nothing errored; the rows simply appeared. This page's worked example claimed the opposite.

That is fixed. A relation sub-select is now scoped with the target model's own soft-delete column, at every level of nesting:

// Only u1's live posts, and only each post's live comments.
const user = await db.user.findUnique({
  where:   { id: 'u1' },
  include: { posts: { include: { comments: true } } },
});

sql

-- the posts sub-select
SELECT … FROM "posts"    WHERE "deleted_at" IS NULL AND "author_id" IN ($1);
-- the comments sub-select, one level down
SELECT … FROM "comments" WHERE "removed_at" IS NULL AND "post_id"   IN (…);

(The scope comes from the IR node and the key filter is ANDed onto it by the shared loader, which is why it reads in that order.)

Four details worth being precise about.

The target's column, not the parent's. A User include of posts filters on Post's soft-delete column. The names need not match — archived_at on the user, deleted_at on the post, removed_at on the comment all work, because the scope is resolved per model from the .softDeleteAt() flag rather than from the column name.

select too, not just include. select: { id: true, posts: true } pulls the relation through the same IR path and gets the same scope.

A bare include: { posts: true } is scoped. That shape carries no nested arguments at all, and it was exactly the one that leaked: the old code skipped building a sub-node when there was nothing to build. A node is now built for it whenever the target has a soft-delete column.

Your own nested filter survives. The scope is ANDed with whatever you wrote, so include: { posts: { where: { title: 'x' } } } filters on both.

`_withDeleted: true` opts out, at any level

// Live posts, but every comment including the deleted ones.
include: {
  posts: { include: { comments: { where: { _withDeleted: true } } } },
}

This is the other half of the fix: the documented opt-out used to be actively broken. _withDeleted was only recognised at the top level of a where, so inside an include it was treated as an ordinary column name and compiled to a filter on a column called _withDeleted, which no table has — the database errored out. The flag is now recognised and stripped wherever it appears, whether or not it opts anything out, so it can never reach the where-builder as a column.

An explicit filter on the column wins

If your nested where already mentions the target's soft-delete column, forge leaves it exactly as written and adds nothing:

// "What did this user delete since Friday?" — untouched by the auto-filter.
include: { posts: { where: { deleted_at: { gte: friday } } } },

That is the same rule the top level has always had, and it is how you build a per-parent trash view. { deleted_at: null } written by hand is therefore harmless, just redundant.

What is not covered

IndexedDB relation filters. A relation predicate (where: { posts: { some: … } }) still matches everything on the IndexedDB adapter — there is no cross-store join at that layer. Relation sub-selects from includeare scoped there; see below.
Mongo relation filters throw (2.18.0), so there is nothing to scope. The two-step rewrite in the error message reads the ids with a normal findMany, which is scoped.
Raw statements. $queryRaw / $executeRaw / aggregate({ pipeline }) never see the auto-filter. Write deleted_at IS NULL yourself.

The four things that used to disagree with `include`

Scoping include correctly exposed four places that answered a different question about the same rows. All four are fixed in 2.19.0, and they are worth knowing individually because each had its own symptom.

`groupBy` — and therefore `aggregate`

groupBy applied neither the soft-delete filter nor the strict-mode where check, unlike _find, findManyStream and count. Since the typed aggregate({ _sum: … }) routes through groupBy, a revenue total silently included deleted orders:

// Before 2.19.0: summed the cancelled-and-soft-deleted orders too.
const { _sum } = await db.order.aggregate({ _sum: { total: true } });

A typo'd where key was not caught there either — which under strict: true means the filter matched nothing and the aggregate came back as though the rows did not exist. Both checks now run on groupBy.

Relation `_count` disagreed with the rows

_count: { posts: true } said 7 where include: { posts: true } returned 5. The relation counts are hand-written per adapter, outside the IR, so they did not inherit the new scope; all seven adapters now exclude soft-deleted children:

const u = await db.user.findUnique({
  where:   { id: 'u1' },
  include: { posts: true, _count: { select: { posts: true } } },
});
u.posts.length === u._count.posts;   // true

There is no _withDeleted opt-out on _count, and no filter of any kind (see RELATIONS.md). If you need the count including deleted children, read them and take the length — include: { posts: { where: { _withDeleted: true } } } — or groupBy the child table with _withDeleted: true in its where.

Relation filters matched deleted children

where: { posts: { some: { … } } } returned a parent whose only matching post was soft-deleted — with an empty posts array, from a filter that said it had some. The five modes (is, isNot, some, every, none) are all scoped now, and _withDeleted: true inside the relation filter opts out:

await db.author.findMany({ where: { posts: { some: { title: 'x' } } } });

sql

SELECT … FROM "authors" WHERE EXISTS (
  SELECT 1 FROM "posts" "t1"
   WHERE "t1"."author_id" = "authors"."id" AND "t1"."deleted_at" IS NULL
     AND "t1"."title" = $1
)

Where the scope sits is the non-obvious part, and it is why every is correct. The predicate goes on the subquery's join condition — next to t1.author_id = authors.id — and not into the nested condition the caller wrote. The reason is every: it compiles to NOT EXISTS of the negated inner condition, because "every child matches" is "no child violates".

sql

-- every: { title: 'x' }
NOT EXISTS (
  SELECT 1 FROM "posts" "t1"
   WHERE "t1"."author_id" = "authors"."id" AND "t1"."deleted_at" IS NULL
     AND NOT ("t1"."title" = $1)
)

Fold the scope into the nested condition instead and it gets negated along with it: NOT (deleted_at IS NULL AND title = 'x') is true for every deleted row, so the NOT EXISTS fails and every comes to mean "every child is live and matches" — which is false for any parent that has ever deleted a child. On the join condition the scope restricts which rows the subquery considers at all, which is the right meaning for all five modes: some/is look for a live match, none/isNot ignore deleted children rather than being blocked by them, and every quantifies over the live ones.

This applies to the five SQL dialects (Postgres, MySQL, SQLite, DuckDB and MSSQL all share that compiler). Mongo throws on a relation filter; IndexedDB matches all.

IndexedDB ignored a nested `where` entirely

On IndexedDB, relation rows were fetched by index and handed back unfiltered, so include: { posts: { where: { … } } } returned everything the parent had — and the new soft-delete predicate would have been ignored right along with it. There is no predicate to push down into an index scan, so the nested where is now evaluated in JS with the same predicate compiler the top-level residual filter uses. Relation _count on IndexedDB filters the fetched rows the same way.

Partial-filter indexes

A UNIQUE constraint that ignores soft-deleted rows is the canonical companion to a soft-delete column. Without it, a soft-deleted row's slug permanently blocks any other row from claiming the same slug — the tombstone holds the key.

The fix is a partial-filter unique index: unique on slug, but only where deleted_at IS NULL. Forge supports the cross-dialect form on the model:

const Post = model('posts', {
  id:         f.id(),
  slug:       f.string(),
  deleted_at: f.dateTime().softDeleteAt(),
}, {
  indexes: [{
    keys: { slug: 1 }, unique: true,
    where: 'deleted_at IS NULL',                        // SQL dialects
    partialFilterExpression: { deleted_at: null },      // Mongo
  }],
});

If both where: and partialFilterExpression: are set, each dialect picks the one it understands. If only one is set, forge translates where it can; see docs/INDEXES.md for the operator coverage of the object-form-to-SQL translator.

Per-dialect emit

Dialect	DDL
Postgres	`CREATE UNIQUE INDEX … ON "posts" ("slug" ASC) WHERE deleted_at IS NULL`
SQLite	same as Postgres
DuckDB	same as Postgres
MSSQL	`CREATE UNIQUE INDEX … ON [posts] ([slug] ASC) WHERE [deleted_at] IS NULL`
MySQL	`CREATE UNIQUE INDEX … ON posts ((CASE WHEN (deleted_at IS NULL) THEN slug ELSE NULL END))`
Mongo	`createIndex({ slug: 1 }, { unique: true, partialFilterExpression: { deleted_at: null } })`

MySQL has no native partial index, so the unique is rewritten as a functional unique over a CASE expression — rows whose deleted_at is not null collapse to NULL in the indexed expression, and MySQL treats NULLs as non-duplicates. Composite keys use JSON_ARRAY(col1, col2) inside the THEN for portability.

Non-unique partials on MySQL warn and ship without the filter (no clean workaround). See docs/INDEXES.md for the full caveat.

Indexing `deleted_at` itself

The WHERE deleted_at IS NULL predicate that the wrapper appends to every read benefits from a plain index on the soft-delete column when the table is large and the fraction of soft-deleted rows is small enough to matter to the optimiser:

indexes: [
  { keys: { deleted_at: 1 } },  // covers the auto-filter
],

On Postgres / SQLite / DuckDB you can go further: an index whose key is the column you actually filter on, plus a partial filter, often beats a plain index on deleted_at:

// Index posts you'll actually read — active ones.
indexes: [
  { keys: { author_id: 1, created_at: -1 }, where: 'deleted_at IS NULL' },
],

The tombstones don't enter the index at all, so it stays compact even as deletes accumulate.

Cascading soft deletes

onDelete: 'Cascade' on a rel.one declaration cascades a hard delete through the foreign key (see docs/RELATIONS.md). It does not cascade a soft delete — the database has no idea the parent row was soft-deleted; it just sees an UPDATE.

So cascading soft-delete is application-side. Three shapes:

1. Manual cascade

The straightforward version — soft-delete the children explicitly in the same call:

async function softDeleteUser(userId: string) {
  await db.$transaction(async (tx) => {
    await tx.post.softDeleteMany({ where: { author_id: userId } });
    await tx.comment.softDeleteMany({ where: { author_id: userId } });
    await tx.user.softDelete({ where: { id: userId } });
  });
}

All three writes are inside one transaction, so either everything soft-deletes or nothing does. Child rows are stamped first, parent last — the order doesn't matter for soft-delete (no FK is being broken), but parent-last reads cleanly when you scan back.

2. Mongo cascade walker

On Mongo, the same shape, but the $transaction requires a replica-set session. Outside one, the writes are best-effort and forge surfaces a warning. See docs/MUTATIONS.md.

3. Trigger-based (Postgres / MSSQL only)

For schemas where the cascade graph is fixed and large, a database trigger on the parent's deleted_at keeps the cascade out of application code. Pros: enforced at the database, not subject to forgotten call-sites. Cons: hidden from the application; ORM events don't fire for the cascaded writes; harder to test. Lift to a trigger only when the cascade is hot enough that the application-side overhead matters.

Restore semantics

restore sets the column to null. The row is active again, indexes recover it, reads see it.

The semantics worth being deliberate about:

Restore does not auto-cascade. A user soft-deleted with the cascade above is restored by a restore({ where: { id } }) on the user — but the children stay soft-deleted unless you restore them too. Same shape as the cascade:

await db.$transaction(async (tx) => {
  await tx.user.restore({ where: { id: userId } });
  await tx.post.restoreMany({ where: { author_id: userId } });
  await tx.comment.restoreMany({ where: { author_id: userId } });
});

Restore is not idempotent at the value level. Restoring an already-active row sets deleted_at from null to null — a no-op in effect, but the update still runs, the updated_at (if any) is bumped, and the event still fires. If "restore only if currently soft-deleted" matters, use restoreMany({ where: { id, deleted_at: { not: null } } }) and inspect count.
Restore on a unique-violation column can fail. If you restore a row whose slug now collides with an active row that took its place — because the partial-unique only blocked the active one — the underlying UPDATE fails with the dialect's unique-violation error. Handle it the same way you'd handle any unique-violation: catch, prompt the user for a new slug, retry.
Restoring a child without its parent. If the parent was hard-deleted, restoring the child leaves a dangling FK on SQL (which the FK constraint will reject) or an orphaned reference on Mongo. Lift the parent check above the restore, or refuse the restore.

Hard delete after retention

Soft-deleted rows accumulate. Without a purge, the table grows forever and the partial-filter indexes carry an ever-bigger tombstone set. Retention turns soft-delete into a fixed-cost feature.

The pattern is a periodic hard-delete keyed on deleted_at < cutoff:

// Daily cron — purge soft-deleted rows older than 90 days.
async function purgeOldSoftDeletes() {
  const cutoff = new Date(Date.now() - 90 * 24 * 60 * 60 * 1000);
  const { count } = await db.post.deleteMany({
    where: {
      deleted_at: { lt: cutoff },
      _withDeleted: true,                 // bypass the read filter
    },
  });
  log.info('soft-delete purge', { model: 'post', purged: count, cutoff });
}

_withDeleted: true is required — without it, the auto-filter rewrites where to deleted_at IS NULL AND deleted_at < cutoff, which matches nothing.

Chunked purge for large tables

For tables with millions of tombstones, a single deleteMany holds the write lock for the duration of the scan. Page by id in chunks of 1,000 — findMany({ select: { id: true }, take: 1000 }) followed by a deleteMany({ where: { id: { in: ids }, _withDeleted: true } }), looped until the page returns short. The retention window is your call: 30 / 60 / 90 days are common; longer when regulatory recovery is in play, shorter for high-churn tables.

Purge fires cascades

deleteMany is a hard delete and runs onDelete: 'Cascade' — children of the purged parent are gone too. If the parent's cascade targets are also soft-delete tables, this hard-deletes them across their soft-delete column without going through softDelete. That's usually what you want (they were already soft-deleted) but worth checking against your retention policy per table.

Per-dialect emit

Every soft-delete verb compiles to a regular update statement, so the dialect emit is identical to the regular update table in docs/MUTATIONS.md — with the soft-delete column as the only field in SET ….

Adapter	`softDelete({ where: { id: 'p1' } })` compiles to
postgres	`UPDATE "posts" SET "deleted_at" = $1 WHERE "id" = $2 RETURNING *`
mysql	UPDATE `posts` SET `deleted_at` = ? WHERE `id` = ? + `SELECT`
sqlite	`UPDATE "posts" SET "deleted_at" = ? WHERE "id" = ? RETURNING *`
duckdb	`UPDATE "posts" SET "deleted_at" = ? WHERE "id" = ? RETURNING *`
mssql	`UPDATE [posts] SET [deleted_at] = @p1 OUTPUT INSERTED.* WHERE [id] = @p2`
mongo	`findOneAndUpdate({ _id: 'p1' }, { $set: { deleted_at: <Date> } })`

softDeleteMany and restoreMany follow the same shape as updateMany on every dialect — count-returning, no RETURNING / OUTPUT.

Via the compile API

The compile API exposes the same four verbs:

const c = db.post.compile.softDelete({ where: { id: 'p1' } });
// c.kind === 'sql', c.sql === 'UPDATE "posts" SET "deleted_at" = $1 WHERE "id" = $2 RETURNING *'
// c.params === [<Date>, 'p1']
// c.semanticOp === 'softDelete'

semanticOp lands on both SQLArtifact and MongoArtifact for the four verbs, so downstream tooling (replay, audit, dry-run) can branch on intent without parsing the SQL or the update document. Plain update artifacts don't have semanticOp. See docs/QUERIES.md for the full compile-API reference.

The compile variants throw synchronously when the model has no .softDeleteAt() field — same gate as the runtime wrapper, just at compile time instead of dispatch time.

Events and audit interaction

Every soft-delete write emits a regular 'update' QueryEvent — the underlying statement is an UPDATE. What sets it apart is the semanticOp field:

db.$on('query', (e) => {
  if (e.semanticOp === 'softDelete' || e.semanticOp === 'softDeleteMany') {
    auditSoftDelete(e);
    return;
  }
  if (e.semanticOp === 'restore' || e.semanticOp === 'restoreMany') {
    auditRestore(e);
    return;
  }
  // ... regular update logging
});

semanticOp carries 'softDelete' | 'softDeleteMany' | 'restore' | 'restoreMany' for the four verbs and is absent on plain update / upsert. Listeners that don't care about the intent ignore the field; listeners that audit deletes branch on it.

The full QueryEvent shape is documented in docs/MUTATIONS.md — semanticOp is one field on it.

Audit trail

The audit-trail pattern from docs/MUTATIONS.md — tee writes to an audit table from a single $on('query') listener — handles soft-delete naturally: the semanticOp field is what lets the audit row distinguish a soft-delete from a regular update, without parsing the SQL or the update document.

db.$on('query', async (e) => {
  if (e.op !== 'update') return;
  if (e.model === 'audit_log') return;
  await db.auditLog.create({
    data: {
      at:       e.startedAt,
      model:    e.model,
      op:       e.semanticOp ?? e.op,    // 'softDelete' / 'restore' / 'update'
      rows:     e.rowCount,
      sql:      e.sql,
      duration: e.duration_ms,
    },
  });
});

For compliance use-cases that require the audit row to be in the same transaction as the write, the outbox pattern from docs/MUTATIONS.md is the shape — write the audit row inside the same $transaction as the soft-delete verb.

See docs/EVENTS.md for the full event-system reference.

Soft delete is not erasure. A row with deleted_at set still contains personal data; it's just hidden from default reads. GDPR Article 17 (and similar regulations) require actual removal — the data must no longer exist in identifiable form.

Two paths:

Hard delete

The simplest fulfilment — hard-delete the row, fire the cascade, drop any associated rows in joined tables:

async function forgetUser(userId: string) {
  await db.$transaction(async (tx) => {
    await tx.user.delete({ where: { id: userId } });
    // onDelete: 'Cascade' on the FKs purges posts, comments, sessions, etc.
  });
}

The cascade graph is your responsibility — every model that holds identifiable data for the user must either cascade-delete or be explicitly purged in the same transaction.

Anonymise in place

When hard delete would break analytics or aggregate counts, replace the identifiable fields with anonymous placeholders and keep the row:

async function anonymiseUser(userId: string) {
  await db.user.update({
    where: { id: userId },
    data: {
      email:       `redacted-${userId}@example.invalid`,
      name:        'Redacted User',
      avatar_url:  null,
      phone:       null,
      address:     null,
      deleted_at:  new Date(),
      anonymised:  true,
    },
  });
}

The row remains for foreign-key integrity and historical aggregates, but the personal data is gone. Pair with a flag (anonymised: true) so downstream code can refuse to re-display the row's name / email.

Soft-delete is the pre-condition, not the answer

A typical flow: user requests deletion → soft-delete immediately (UI stops showing the account) → after the cooling-off period (e.g. 30 days, lets the user reverse the decision) → anonymise or hard-delete to fulfil the GDPR obligation. The retention purge from Hard delete after retention is the trigger for the second step.

Search and vector index interaction

Full-text and vector indexes do not know about the auto-filter. Without guarding, FTS / vector searches return soft-deleted rows.

FTS

The filter is the same deleted_at IS NULL predicate, applied alongside the FTS match:

const rows = await db.post.findMany({
  where: {
    AND: [
      { body: { search: 'soft delete' } },   // FTS predicate
      // _withDeleted defaults to false; auto-filter excludes tombstones.
    ],
  },
});

The auto-filter handles this for you — every read goes through it, including FTS. The pitfall is when you drop to $queryRaw for a hand-written tsquery / MATCH / $text; the raw path doesn't auto-filter:

const rows = await db.$queryRaw<Post[]>`
  SELECT * FROM posts
  WHERE to_tsvector('english', body) @@ plainto_tsquery($1)
    AND deleted_at IS NULL                            -- add manually
`;

See docs/FTS.md for the FTS surface.

Vector

Same shape — db.post.findMany({ where: { embedding: { nearestTo: vec, k: 10 } } }) runs through the auto-filter, so soft-deleted rows are excluded.

Vector indexes themselves don't have a WHERE clause on most engines (Postgres pgvector does; sqlite-vec doesn't), so the index still covers tombstones. The runtime filter is what gates the results. If soft-deleted rows dominate the vector space, the recall:precision trade-off shifts — consider a periodic purge as in Hard delete after retention.

See docs/VECTOR.md for the vector-search surface.

Browser sqlite-wasm

The browser adapter (sqlite-wasm via OPFS, see docs/BROWSER.md) supports soft-delete with identical semantics: .softDeleteAt() on the column, the four wrapper verbs, auto-filter on reads, partial-filter indexes via the SQLite syntax, _withDeleted opt-out. The scale differs — a browser SQLite database is rarely the right home for tables that accumulate millions of tombstones — but the API is the same. The retention purge from Hard delete after retention applies the same way; the cron is just setInterval instead of a backend job.

Worked patterns

(a) Blog post: soft-delete, restore, purge

The full lifecycle on one model:

const Post = model('posts', {
  id:         f.id(),
  author_id:  f.objectId(),
  slug:       f.string(),
  title:      f.string(),
  body:       f.string(),
  created_at: f.dateTime().default('now'),
  deleted_at: f.dateTime().softDeleteAt(),
}, {
  indexes: [
    // Unique slug per active post — recycled after soft-delete.
    {
      keys: { slug: 1 }, unique: true,
      where: 'deleted_at IS NULL',
      partialFilterExpression: { deleted_at: null },
    },
    // Covers the auto-filter on large feeds.
    { keys: { author_id: 1, created_at: -1 }, where: 'deleted_at IS NULL' },
  ],
});

// User trashes a post:
await db.post.softDelete({ where: { id: 'p1' } });
// → row stays; deleted_at set; partial-unique releases the slug.

// User restores within the cooling-off window:
await db.post.restore({ where: { id: 'p1' } });
// → only succeeds if the slug is still available (no one took it).

// Retention cron, daily — purge anything soft-deleted > 90 days ago:
await db.post.deleteMany({
  where: { deleted_at: { lt: cutoff90d }, _withDeleted: true },
});

The partial-unique on slug is the load-bearing piece. Without it, a soft-deleted slug holds the key forever and the next post can't reuse it.

(b) User account: soft-delete with cascade to posts and comments

A user soft-delete that cascades to their content, all in one transaction:

async function deactivateUser(userId: string) {
  await db.$transaction(async (tx) => {
    await tx.post.softDeleteMany({ where: { author_id: userId } });
    await tx.comment.softDeleteMany({ where: { author_id: userId } });
    await tx.session.deleteMany({ where: { user_id: userId } });  // hard — sessions are not recoverable
    await tx.user.softDelete({ where: { id: userId } });
  });
}

async function reactivateUser(userId: string) {
  await db.$transaction(async (tx) => {
    await tx.user.restore({ where: { id: userId } });
    await tx.post.restoreMany({ where: { author_id: userId } });
    await tx.comment.restoreMany({ where: { author_id: userId } });
    // sessions stay gone — user re-logs in.
  });
}

Sessions are not a recoverable resource (you don't restore a logged-out session), so they're hard-deleted. Posts and comments are; they're soft-deleted with the user and restored on reactivation.

The transaction guarantees consistency: if the user soft-delete fails, nothing soft-deletes; if any cascade fails, the user stays active.

(c) Admin "trash" view — soft-deleted rows only

Inverting the auto-filter for an admin UI that lists what's pending purge:

async function listTrash() {
  return db.post.findMany({
    where: {
      deleted_at: { not: null },   // explicit filter — auto-filter is skipped
    },
    orderBy: { deleted_at: 'desc' },
    take: 100,
  });
}

The auto-filter only applies when you don't filter the soft-delete column yourself. Filtering with { not: null } is the explicit "give me only soft-deleted rows" form.

Pair with a restore button that calls restore({ where: { id } }), and a "purge now" button that calls delete({ where: { id } }) with the _withDeleted: true opt-out so the unique read hits the soft-deleted row:

async function purgeOne(id: string) {
  await db.post.delete({ where: { id, _withDeleted: true } });
}

delete is a hard delete; the _withDeleted: true is only needed because the read filter would otherwise hide the row when the wrapper loads it for the RETURNING clause.

Anti-patterns

`is_active: bool` instead of a timestamp

The boolean variant — is_active: false for soft-deleted, is_active: true for live — appears at first to be the same shape. It isn't:

No when. You can't write the retention purge from Hard delete after retention without the timestamp.
No audit signal. "This row was deactivated" doesn't tell you when, by whom, in what release.
Two-state collapse. A row that's been deactivated, reactivated, and deactivated again looks identical to one deactivated once. With a timestamp + audit log, the history is recoverable.

Use f.dateTime().softDeleteAt(). The flag falls out: "soft-deleted iff the timestamp is non-null".

Soft-deleting embedded children

If a model embeds children as a JSON array — posts: [{ ... }] on Mongo, or a jsonb array on Postgres — there is no "child row" to soft-delete. Soft-delete the parent instead, or model the children as a separate collection with their own deleted_at.

The same applies to fields on a row: soft-deleting a column (setting it to null while keeping the row) isn't soft-delete; it's just a write. Soft-delete operates on the row.

Hard-deleting from a soft-delete table without `_withDeleted`

// Wrong — auto-filter hides the row from the delete's RETURNING read.
await db.post.delete({ where: { id: 'p1' } });
// If post p1 has deleted_at set, this throws "no row matched" because the
// internal read for RETURNING applies the auto-filter and doesn't see it.

// Right — opt out of the filter when hard-deleting a soft-deleted row.
await db.post.delete({ where: { id: 'p1', _withDeleted: true } });

This bites during the purge step of the retention pattern if you use single-row delete instead of deleteMany. deleteMany doesn't have this problem (no RETURNING) — the _withDeleted: true there is to bypass the filter on the match, not the read-back.

Forgetting the partial-unique

If you put a plain f.string().unique() on a column in a soft-delete model, the unique constraint covers tombstones. The first user who soft-deletes a row with slug = 'hello' permanently blocks any other row from claiming 'hello'. The user-visible symptom is a unique-violation error on what looks like an empty slug space.

Fix: replace the column-level .unique() with a partial-filter unique index on the model:

// Wrong:
slug: f.string().unique(),

// Right:
slug: f.string(),
// + on the model:
indexes: [{
  keys: { slug: 1 }, unique: true,
  where: 'deleted_at IS NULL',
  partialFilterExpression: { deleted_at: null },
}],

Soft-deleting infrastructure tables

Audit logs, outbox events, sessions, rate-limit counters — these tables should not be soft-delete. They have no business logic that benefits from "recoverable", and the tombstone accumulation hurts the only thing the tables exist for: throughput.

The model-level question is "is restoring this row a real product behavior?". If no, hard-delete. Soft-delete is a feature, not a default.

Soft delete ​

Contents ​

What soft delete is ​

Declaring the column ​

Type ​

softDelete and restore ​

What gets thrown ​

softDelete reaches across the read filter ​

Query-time defaults ​

Strict mode and _withDeleted ​

include is scoped at every depth ​

_withDeleted: true opts out, at any level ​

An explicit filter on the column wins ​

What is not covered ​

The four things that used to disagree with include ​

groupBy — and therefore aggregate ​

Relation _count disagreed with the rows ​

Relation filters matched deleted children ​

IndexedDB ignored a nested where entirely ​

Partial-filter indexes ​

Per-dialect emit ​

Indexing deleted_at itself ​

Cascading soft deletes ​

1. Manual cascade ​

2. Mongo cascade walker ​

3. Trigger-based (Postgres / MSSQL only) ​

Restore semantics ​

Hard delete after retention ​

Chunked purge for large tables ​

Purge fires cascades ​

Per-dialect emit ​

Via the compile API ​

Events and audit interaction ​

Audit trail ​

GDPR and right-to-be-forgotten ​

Hard delete ​

Anonymise in place ​

Soft-delete is the pre-condition, not the answer ​

Search and vector index interaction ​

FTS ​

Vector ​

Browser sqlite-wasm ​

Worked patterns ​

(a) Blog post: soft-delete, restore, purge ​

(b) User account: soft-delete with cascade to posts and comments ​

(c) Admin "trash" view — soft-deleted rows only ​

Anti-patterns ​

is_active: bool instead of a timestamp ​

Soft-deleting embedded children ​

Hard-deleting from a soft-delete table without _withDeleted ​

Forgetting the partial-unique ​

Soft-deleting infrastructure tables ​

See also ​

Soft delete

Contents

What soft delete is

Declaring the column

Type

`softDelete` and `restore`

What gets thrown

`softDelete` reaches across the read filter

Query-time defaults

Strict mode and `_withDeleted`

`include` is scoped at every depth

`_withDeleted: true` opts out, at any level

An explicit filter on the column wins

What is not covered

The four things that used to disagree with `include`

`groupBy` — and therefore `aggregate`

Relation `_count` disagreed with the rows

Relation filters matched deleted children

IndexedDB ignored a nested `where` entirely

Partial-filter indexes

Per-dialect emit

Indexing `deleted_at` itself

Cascading soft deletes

1. Manual cascade

2. Mongo cascade walker

3. Trigger-based (Postgres / MSSQL only)

Restore semantics

Hard delete after retention

Chunked purge for large tables

Purge fires cascades

Per-dialect emit

Via the compile API

Events and audit interaction

Audit trail

GDPR and right-to-be-forgotten

Hard delete

Anonymise in place

Soft-delete is the pre-condition, not the answer

Search and vector index interaction

FTS

Vector

Browser sqlite-wasm

Worked patterns

(a) Blog post: soft-delete, restore, purge

(b) User account: soft-delete with cascade to posts and comments

(c) Admin "trash" view — soft-deleted rows only

Anti-patterns

`is_active: bool` instead of a timestamp

Soft-deleting embedded children

Hard-deleting from a soft-delete table without `_withDeleted`

Forgetting the partial-unique

Soft-deleting infrastructure tables

See also