Soft Deletes

Soft deletes mark records as deleted (a timestamp) instead of removing them, with restore, purge, cascade-to-children, and batch (cascade-group) restore/purge.

Enabling it

Use the softDelete() schema preset — one import turns the whole pipeline on:

import { sqliteTable, text } from 'drizzle-orm/sqlite-core'
import { id, timestamps, softDelete } from '@websideproject/nuxt-auto-api/schema/sqlite'

const sd = softDelete()
export const posts = sqliteTable('posts', {
  ...id(),
  title: text('title').notNull(),
  ...timestamps(),
  ...sd.columns, // deletedAt, deletedBy, deletionId, deletedReason
}, t => [...sd.indexes(t)]) // posts_trash_idx + posts_batch_idx

Detection (and the deleted_at collision)

A table is soft-deletable when it has a deletedAt / deleted_at / deletedDate marker that is nullable, or carries a preset companion column (deletionId / deletedReason / deletedBy). A NOT NULLdeleted_at with no companions is treated as a domain column, not soft delete — so a table like a GDPR erasure log that records "when the subject was erased" is not mistaken for soft-deletable.

Lifecycle columns

These columns are server-owned: a request body can never set them (an attempt is silently dropped), so a row cannot be trashed, un-trashed or moved into another batch through PATCH.

Behaviour

Trashed rows are also invisible when reached through ?include= and on both sides of M2M routes.

Permissions

restore, purge and viewDeleted are permission keys like any other — true, a permission string, an array, a function or a descriptor object:

export const postsAuth: ResourceAuthConfig = {
  permissions: {
    read: true,
    update: 'editor',
    delete: 'editor',
    restore: 'editor', // who can restore
    purge: 'admin', // who can permanently delete
    viewDeleted: 'editor', // who can see trash
  },
  // Soft-delete knobs (these keys can also hold restore / purge / viewDeleted; permissions.* wins):
  softDelete: { cascade: 'auto', retentionDays: 30 },
}

When a key is not declared it falls back:

• restore → softDelete.restore → update
• purge → softDelete.purge → delete
• viewDeleted → softDelete.viewDeleted → the restore permission

With nothing declared on the chain the operation is denied (a * super-admin still passes). Restore checks its permission before looking the row up, so a caller who may not restore learns nothing about which rows exist.

Cascade & batch

Soft-deleting a parent mirrors each foreign key's onDelete, reversibly:

• cascade → soft-delete soft-deletable children (recursively), stamped with the same deletionId; children that are not soft-deletable are left for the database's ON DELETE CASCADE at purge time.
• set null → null the child's foreign key.
• restrict / no action → the delete is refused with 409 before any row is changed.

Disable cascading with softDelete.cascade: 'off'.

A whole batch is restored or purged with restoreSoftDeletedBatch(ctx, deletionId) / purgeSoftDeletedBatch(ctx, deletionId) from @websideproject/nuxt-auto-api/utils (the single-row restore route uses the first one when the row has a batch). They are all-or-nothing: every row of the batch, in every resource, must be visible to the caller (tenant, listFilter, objectLevel) and pass that resource's restore / purge permission — otherwise nothing changes and the call is a 403. A caller can never pull another tenant's rows back by naming their batch.

Retention

Trash retention is a per-resource setting (softDelete.retentionDays, 0 = keep forever). Purging expired trash is the app's job — e.g. a scheduled Nitro task that deletes rows whose deletedAt is older than the window.

GDPR

Privacy erasure should write with Drizzle directly (not through the soft-delete route), so it also hard-deletes or anonymizes trashed rows — soft delete must never shield personal data from erasure.