Deletion

Learn which eight read operations the global Prisma extension filters, how deletedAt: undefined opts a read back in, and how check-nested-soft-delete.ts enforces explicit filters on nested to-many reads.

A model with a deletedAt column is archivable: setting the timestamp hides the row instead of removing it.

That choice moves the problem. Nothing is destroyed, but every read in the application now has to remember to exclude archived rows, and the one read that forgets is the one that leaks a deleted product into a storefront listing.

lib/soft-delete-extension.ts answers that by injecting deletedAt: null into top-level reads for those models, so a forgotten manual filter cannot leak archived rows. services/db.ts attaches it to the client at construction.

The extension cannot see nested relation reads, so a separate AST check enforces those by hand.

Where it is attached

services/db.ts

return new PrismaClient({
  adapter,
  transactionOptions: { maxWait: 15_000, timeout: 30_000 },
}).$extends(createSoftDeleteExtension())

Every module that imports db from ~/services/db gets the filtered client.

Two files build their own PrismaClient and are therefore unfiltered:

prisma/seed.ts
scripts/check-db-guarantees.ts

The model set is derived, not written down.

MODELS_WITH_SOFT_DELETE in the extension filters Prisma.dmmf.datamodel.models for a field named deletedAt. lib/soft-delete-models.ts exports the same derivation as SOFT_DELETE_MODELS for the linter.

Adding deletedAt to a model enrols it with no registration step. There is no list to update, so there is no list to forget.

What it intercepts

Eight read operations, on $allModels:

findMany
findFirst
findFirstOrThrow
findUnique
findUniqueOrThrow
count
aggregate
groupBy

For findUnique and findUniqueOrThrow the filter goes directly into the unique where.

Non-unique fields alongside a unique filter have been GA since Prisma 5 ("extendedWhereUnique"), so the call keeps its exact delegate return type with no rewrite to findFirst, and a soft-deleted row reads as not found.

What it deliberately does not intercept

Three categories are excluded, each for a stated reason.

Nested relation reads

Prisma's query hooks only receive the top-level operation, so an include/select where is invisible to the extension.

The manual deletedAt: null filters on nested relations have to stay.

Writes

update, updateMany, upsert, delete and deleteMany are untouched.

Injecting into a write guard would change the semantics of conditional compare-and-set claims — such as the payment webhook's conditional order claim, an updateMany on id, financialStatus: { notIn: settled } and deletedAt: null.

It would also break archive restore, which legitimately targets soft-deleted rows.

Raw SQL

$queryRaw and friends are untouched.

lib/utils/slug.ts checks slug and SKU uniqueness across deleted rows on purpose, because the database unique constraints ignore deletedAt. A slug freed by archiving a row is not actually free.

Opting out

mentionsDeletedAt skips injection when the top-level where already names deletedAt.

It recurses only through the AND, OR and NOT combinators, never into relation filters, and it tests with in rather than a truthiness check — so an explicit deletedAt: undefined counts as a mention.

Prisma ignores an undefined value, the extension sees the key, and the read returns deleted and live rows alike.

lib/inventory.ts

// `deletedAt: undefined` opts out of the soft-delete extension's injected
// filter so a deleted variant reports VARIANT_DELETED instead of the
// misleading VARIANT_NOT_FOUND.
const variant = await db.productVariant.findUnique({
  where: { id: variantId, deletedAt: undefined },
  select: { id: true, deletedAt: true, sku: true },
})

The other opt-outs in the repository are:

lib/payments/payment-webhook.ts — an externalPaymentId lookup, where the payment id itself is the proof of processing
lib/privacy/anonymize.ts
server/customers/referral-links.ts

The archived queue takes the other route — an explicit non-null filter. Admin list queries spread one canonical predicate:

lib/soft-delete-models.ts

export function archivedWhere(archived: string | undefined): { deletedAt?: { not: null } } {
  return archived === "true" ? { deletedAt: { not: null } } : {}
}

The nested-read check

scripts/check-nested-soft-delete.ts closes the gap the extension cannot reach.

A nested include/select of a soft-deletable relation with no where of its own returns archived rows silently — wrong counts, leaked deleted data, skewed averages — and tsc is happy with it.

top-level read
    ↓
extension injects deletedAt: null

nested include, no where
    ↓
nothing injected
    ↓
archived rows in the result
    ↓
no error anywhere

The script parses the TypeScript AST rather than scanning text. Regexes and template literals cannot desync it, reported positions are exact, and type annotations such as _count?: { select: { products: true } } are skipped because only runtime object literals are read.

It walks .ts and .tsx under:

app
components
lib
server
services
config
events
emails
hooks
functions

Declaration files and tests are excluded.

It flags any include: or select: property that names a soft-deletable relation and is either true or an object with no qualifying where.

Three rules narrow what it flags

To-many relations only. Prisma cannot attach a where to a to-one relation read — include: { product: { where: … } } is a type error — so the prescribed fix does not exist there. Only list (X[]) relations whose target is a soft-delete model are candidates.

Unambiguous names only. Relation names come from parsing prisma/schema.prisma at run time. A name reused elsewhere for a to-one relation or for a non-soft child row (media, items) cannot be resolved by a name-only scan, so it is skipped rather than reported as a false positive.

deletedAt anywhere in the where subtree counts. A relation that targets a join model — Collection.products → CollectionProduct[] — has no deletedAt of its own and filters through the join instead, with where: { product: { deletedAt: null } }. publishedProductWhere() from lib/products/visibility.ts is also accepted, because it provably returns deletedAt: null.

The escape hatch

A line tagged with a trailing comment is skipped:

// soft-delete-ok

That exists for an archived view that intentionally wants deleted rows. No line in the repository currently uses it.

Running it

bun run lint:nested-soft-delete

Runs scripts/check-nested-soft-delete.ts.

It also runs inside bun run verify, so a nested read without a filter fails the gate. The failure output lists each file:line → relation and prints the fix.

A to-one leak is not reported

The check skips to-one relations because Prisma offers no nested where to fix them with. Including a soft-deletable to-one relation can still surface an archived row, and closing that leak means filtering at the top level or checking deletedAt on the loaded record yourself.

Which route does a given read need

Most reads need nothing. A top-level findMany, findFirst, findUnique, count, aggregate or groupBy on the shared db is already filtered, and adding deletedAt: null by hand changes nothing.

A nested include/select of a to-many soft-deletable relation needs its own where: { deletedAt: null }, or a filter through the join model where the relation targets one. The check will tell you, but it is cheaper to write it than to be told.

A read that must see archived rows takes deletedAt: undefined — a deliberate opt-out, and the four in the repository each have a reason worth stating in a comment.

A read that must see only archived rows uses archivedWhere, not a hand-written { not: null }.

And a nested to-one relation gets no help from either mechanism, so the check on it is yours to make.

The question the whole arrangement is built around is:

If someone forgets, does the archived row leak?

Where the answer is yes, the filter is injected or the build fails. Where neither is possible — to-one relations, raw SQL, writes — the responsibility is explicitly yours, and that is why those three exclusions are documented rather than quietly assumed.