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
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`	unique selector	the updated row	throws if no row matched
`softDeleteMany`	any filter	`{ count }`	zero matches is a no-op
`restore`	unique selector	the updated row	throws if no row matched
`restoreMany`	any filter	`{ count }`	zero matches is a no-op

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

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.

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
aggregate (Mongo only — pipeline $match is augmented)

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). Pass it on any read; the wrapper strips it before IR build.

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.)

Including soft-deleted via relations

include and nested select traverse relations, and the soft-delete filter applies recursively:

// User's *active* posts only — soft-deleted posts are hidden.
const user = await db.user.findUnique({
  where:   { id: 'u1' },
  include: { posts: true },
});

// User's *all* posts, including soft-deleted.
const user = await db.user.findUnique({
  where:   { id: 'u1' },
  include: {
    posts: { where: { _withDeleted: true } },
  },
});

Each include level is treated as its own read for the purpose of auto-filtering.

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 ​

Including soft-deleted via relations ​

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`

Including soft-deleted via relations

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