# Soft delete (/docs/schema/soft-delete)

---
title: Soft delete
description: One option turns delete into a stamp on the row. The row drops out of every query, restoreById brings it back, and a second authority is what finally removes it.
kind: guide
package: questpie
---

Someone deleted the wrong row a week ago. Can you get it back? This page turns
the option on, deletes a row, finds it again and restores it, then hands off to
the separate authority that removes one for good.

## Turn it on

```ts title="src/questpie/server/collections/posts.ts"
import { collection } from "#questpie/factories";

export const posts = collection("posts")
	.fields(({ f }) => ({
		title: f.text(255).required(),
		body: f.textarea(),
	}))
	.title(({ f }) => f.title)
	.access({ read: true })
	.options({ softDelete: true });
```

Run `questpie generate` and then `questpie push`, and the table gains a
nullable `deletedAt` column.

## Delete it, look for it, bring it back

```ts
const { data } = await app.collections.posts.deleteById({ id });
data.deletedAt; // a Date. The row is still in the table.

const { docs } = await app.collections.posts.find();
// The row is not in here.

const { docs: withDeleted } = await app.collections.posts.find({
	includeDeleted: true,
});
withDeleted.some((post) => post.id === id); // true

const restored = await app.collections.posts.restoreById({ id });
restored.deletedAt; // null, and the row is back in the list
```

That is the whole feature. `deleteById` resolves to `{ success, data }` where
`data` is the stamped row, and `restoreById` resolves to the row itself.

## What the option changes

| Call                       | Off                   | On                         |
| -------------------------- | --------------------- | -------------------------- |
| `deleteById`, `deleteMany` | The row is gone       | `deletedAt` is stamped     |
| `find`, `findOne`, `count` | Every row             | `WHERE deleted_at IS NULL` |
| `restoreById`              | `501 NOT_IMPLEMENTED` | Clears `deletedAt`         |
| `purgeById`                | `501 NOT_IMPLEMENTED` | Removes the row for good   |

`includeDeleted: true` lifts that filter on `find`, `findOne` and `count`, and
it works as a query parameter over REST. Neither `restoreById` nor `purgeById`
has a bulk form. Both take one id, and anything bulk is a loop you write.

## The columns and indexes it adds

`deletedAt` is a nullable timestamp. The collection also gets two indexes: one
on `deletedAt` for the read filter, and `<table>_deleted_at_retention_idx` on
`(deletedAt, id)` limited to `WHERE deleted_at IS NOT NULL`. The second one is
the keyset a retention job pages through, so it stays small as live rows grow.

## A unique value the deleted row still holds

A tombstone keeps its columns, so a plain unique index still rejects a value
nobody can see. Scope the constraint to live rows instead:

```ts
import { softDeleteUniqueIndex } from "questpie";

collection("users")
	.fields(({ f }) => ({ email: f.text(255).required() }))
	.options({ softDelete: true })
	.indexes(({ table }) => [
		softDeleteUniqueIndex("users_email_unique", table.deletedAt, table.email),
	]);
```

It builds a unique index `WHERE deleted_at IS NULL`, so a new user may reclaim
the address of a deleted one.

## Who may restore

Restore is an update, and it runs under the `update` access rule rather than
one of its own. Restoring a row that is not deleted returns conflict, and an id
that was never there returns not found.

## A cascade leaves tombstones behind

`onDelete: "cascade"` calls `deleteById` on each child, so a child with soft
delete on gets stamped rather than removed. Parent and children are all still
rows, and a restored parent does not restore them. That also matters at purge
time, where any retained child blocks the parent.

## In the admin

A soft-delete collection gets a **Show deleted** switch in its view options,
and a deleted row offers restore, one at a time or over a selection.
`@questpie/admin` ships no purge action, so irreversible removal stays in code
you write on purpose.

<Callout type="warn" title="A tombstone is still readable data">
	Soft delete hides a row from queries. It does not erase it. Anything that
	reads with `includeDeleted`, or reaches the table directly, still sees every
	field.
</Callout>

A row in an upload collection keeps its file as well. Soft delete leaves the
object in storage, so a restore still has something to point at, and only purge
schedules the object for removal.

## Removing a row for good

`purgeById` is a separate operation with its own access rule, its own hooks and
its own failure modes. It refuses an active row, and delete permission never
grants it.

| Topic                                      | Page                                                   |
| ------------------------------------------ | ------------------------------------------------------ |
| The `purge` rule, its errors and its hooks | [Purge](/docs/schema/soft-delete/purge)                |
| Clearing old tombstones on a schedule      | [Retention](/docs/schema/soft-delete/retention)        |
| Every switch on `.options()`               | [Options](/docs/schema/collections/options)            |
| Where `purge` sits among the access rules  | [Beyond CRUD](/docs/schema/access-control/beyond-crud) |

## Next

**[Seeds](/docs/schema/seeds)** covers the other end of the lifecycle: the rows
a fresh database starts with.