Soft delete

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

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

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

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:

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.

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.

Next

Seeds covers the other end of the lifecycle: the rows a fresh database starts with.