Soft Delete Plugin

Mark records as deleted without permanently removing them from the database. Soft-delete filtering works automatically with both Repository and DAL patterns through the unified @kysera/executor layer.

Installation

npm install @kysera/soft-delete

Quick Start

Repository Pattern

import { createORM } from '@kysera/repository'
import { softDeletePlugin } from '@kysera/soft-delete'

const orm = await createORM(db, [softDeletePlugin({ deletedAtColumn: 'deleted_at' })])

const userRepo = orm.createRepository(createUserRepository)

// Soft delete (sets deleted_at timestamp)
await userRepo.softDelete(userId)

// Find only active users (automatic filtering)
const activeUsers = await userRepo.findAll()

// Find including deleted
const allUsers = await userRepo.findAllWithDeleted()

// Restore a soft-deleted user
await userRepo.restore(userId)

// Permanently delete
await userRepo.hardDelete(userId)

DAL Pattern

import { createQuery, createContext } from '@kysera/dal'
import { createExecutor, withPluginMetadata } from '@kysera/executor'
import { softDeletePlugin } from '@kysera/soft-delete'

// Create executor with soft-delete plugin
const executor = await createExecutor(db, [softDeletePlugin({ deletedAtColumn: 'deleted_at' })])

// DAL queries automatically filter soft-deleted records
const getActiveUsers = createQuery((ctx: DbContext<Database>) => ctx.db.selectFrom('users').selectAll().execute())

const ctx = createContext(executor)
const activeUsers = await getActiveUsers(ctx) // Excludes soft-deleted

// Per-query opt-out: derive an executor whose queries include deleted rows
const withDeleted = withPluginMetadata(executor, { includeDeleted: true })
const getAllUsers = createQuery((ctx: DbContext<Database>) => ctx.db.selectFrom('users').selectAll().execute())
const allUsers = await getAllUsers(createContext(withDeleted)) // Includes soft-deleted

Configuration

All options are optional with sensible defaults:

interface SoftDeleteOptions {
  /**
   * Column name for soft delete timestamp
   * @default 'deleted_at'
   */
  deletedAtColumn?: string

  /**
   * Include deleted records by default in queries
   * When false, soft-deleted records are automatically filtered out
   * @default false
   */
  includeDeleted?: boolean

  /**
   * List of tables that support soft delete
   * If not provided, all tables are assumed to support it
   * Takes precedence over excludeTables when both are provided
   * @example ['users', 'posts', 'comments']
   */
  tables?: string[]

  /**
   * Tables that should be excluded from soft delete
   * Ignored if the `tables` whitelist is provided
   * @example ['migrations', 'sessions']
   */
  excludeTables?: string[]

  /**
   * Primary key column name used for identifying records
   * @default 'id'
   * @example 'uuid', 'user_id', 'post_id'
   */
  primaryKeyColumn?: string

  /**
   * Logger for plugin operations
   * @default silentLogger (no output)
   */
  logger?: KyseraLogger
}

Configuration Examples

// Custom deleted column name
softDeletePlugin({ deletedAtColumn: 'removed_at' })

// UUID primary key
softDeletePlugin({ primaryKeyColumn: 'uuid' })

// Table whitelist (only these tables support soft delete)
softDeletePlugin({ tables: ['users', 'posts', 'comments'] })

// Include deleted by default (requires explicit filtering)
softDeletePlugin({ includeDeleted: true })

// Enable logging
import { consoleLogger } from '@kysera/core'
softDeletePlugin({ logger: consoleLogger })

Repository Extensions

The plugin adds the following methods to repositories created with @kysera/repository:

Single Record Operations

interface SoftDeleteMethods<T> {
  softDelete(id: number | string): Promise<T>
  restore(id: number | string): Promise<T>
  hardDelete(id: number | string): Promise<void>
  findWithDeleted(id: number | string): Promise<T | null>
}

Method	Description	Returns	Throws
`softDelete(id)`	Sets deleted_at to the current time (client-generated, dialect-formatted timestamp — not SQL `CURRENT_TIMESTAMP`)	Soft-deleted record	`NotFoundError` if record doesn't exist
`restore(id)`	Sets deleted_at to NULL	Restored record	`NotFoundError` if not found, `RecordNotDeletedError` if not deleted
`hardDelete(id)`	Permanently deletes record (real DELETE)	void	N/A
`findWithDeleted(id)`	Finds by ID including soft-deleted	Record or null	N/A

Batch Operations

interface SoftDeleteMethods<T> {
  softDeleteMany(ids: (number | string)[]): Promise<T[]>
  restoreMany(ids: (number | string)[]): Promise<T[]>
  hardDeleteMany(ids: (number | string)[]): Promise<void>
}

Method	Description	Returns	Throws
`softDeleteMany(ids)`	Soft deletes multiple records	Array of soft-deleted records	Warns if any records not found (returns partial results)
`restoreMany(ids)`	Restores multiple records	Array of restored records	N/A
`hardDeleteMany(ids)`	Permanently deletes multiple records	void	N/A

Query Methods

interface SoftDeleteMethods<T> {
  findAllWithDeleted(): Promise<T[]>
  findDeleted(): Promise<T[]>
}

Method	Description	Returns
`findAll()`	Returns only active records (automatic filtering)	Array of non-deleted records
`findById(id)`	Returns only if not deleted (automatic filtering)	Record or null
`findAllWithDeleted()`	Returns all records including soft-deleted	Array of all records
`findDeleted()`	Returns only soft-deleted records	Array of deleted records

Automatic Query Filtering

SELECT queries automatically exclude soft-deleted records in both Repository and DAL patterns:

// Repository pattern - automatic filtering
const users = await userRepo.findAll()
// SQL: SELECT * FROM users WHERE deleted_at IS NULL

// DAL pattern - automatic filtering
const getUsers = createQuery((ctx: DbContext<Database>) => ctx.db.selectFrom('users').selectAll().execute())
const users = await getUsers(ctx)
// SQL: SELECT * FROM users WHERE deleted_at IS NULL

How It Works

The plugin uses query interception from @kysera/executor:

createORM or createExecutor wraps Kysely with a plugin-aware executor
The executor intercepts .selectFrom(), .updateTable(), and .deleteFrom() calls using a Proxy
When a query is built, interceptQuery() is called for each plugin
The soft-delete plugin adds WHERE deleted_at IS NULL to the query builder — filtering SELECT results and narrowing the row scope of UPDATE/DELETE
The filtered query is executed

This works automatically in both Repository and DAL patterns.

Aliased references are handled correctly: for selectFrom('users as u') the plugin receives the base name users (so tables/excludeTables allowlists keep matching) and qualifies the filter with the alias (WHERE u.deleted_at IS NULL). Array cross-join form (selectFrom(['users as u', 'posts'])) applies the filter once per matching table entry.

Common table expressions are handled as well: the executor registers the string names passed to with() / withRecursive(), so a CTE name is never mistaken for a soft-delete table — selectFrom('active_users') on a CTE gets no filter. The CTE body is still built through the executor and is filtered normally.

Filtering Behavior

Query Type	Filtered?	Notes
SELECT	✅ Yes	Automatic `WHERE deleted_at IS NULL`
INSERT	❌ No	Inserts are not affected
UPDATE	✅ Yes	Narrowed with `deleted_at IS NULL` — soft-deleted rows are untouchable
DELETE	✅ Yes	Narrowed with `deleted_at IS NULL`; not converted to a soft delete — use `softDelete()`

Important: DELETE statements are narrowed to active rows but NOT automatically converted to soft deletes. This is by design for simplicity and explicitness. Use the softDelete() method to perform soft deletes.

Usage Examples

Repository Pattern Examples

import { createORM } from '@kysera/repository'
import { softDeletePlugin } from '@kysera/soft-delete'

const orm = await createORM(db, [softDeletePlugin()])
const userRepo = orm.createRepository(createUserRepository)

// Single operations
await userRepo.softDelete(userId)
await userRepo.restore(userId)
await userRepo.hardDelete(userId)

// Batch operations (efficient single queries)
await userRepo.softDeleteMany([1, 2, 3, 4, 5]) // Single UPDATE
await userRepo.restoreMany([1, 2, 3]) // Single UPDATE
await userRepo.hardDeleteMany([1, 2, 3]) // Single DELETE

// Query methods
const active = await userRepo.findAll() // Excludes deleted
const all = await userRepo.findAllWithDeleted() // Includes deleted
const deleted = await userRepo.findDeleted() // Only deleted
const user = await userRepo.findWithDeleted(id) // Find by ID including deleted

DAL Pattern Examples

import { createQuery, createContext, withTransaction } from '@kysera/dal'
import { createExecutor, withPluginMetadata } from '@kysera/executor'
import { softDeletePlugin } from '@kysera/soft-delete'

const executor = await createExecutor(db, [softDeletePlugin()])

// Automatic filtering
const getActiveUsers = createQuery((ctx: DbContext<Database>) => ctx.db.selectFrom('users').selectAll().execute())
const users = await getActiveUsers(executor) // Filtered automatically

// Include deleted: scoped opt-out that keeps every other plugin active
const withDeleted = withPluginMetadata(executor, { includeDeleted: true })
const getAllUsers = createQuery((ctx: DbContext<Database>) => ctx.db.selectFrom('users').selectAll().execute())
const allUsers = await getAllUsers(withDeleted) // Soft-delete filter off

// Manual soft delete in DAL (the target row is still active, so UPDATE
// narrowing does not get in the way)
const softDeleteUser = createQuery((ctx: DbContext<Database>, userId: number) =>
  ctx.db.updateTable('users').set({ deleted_at: new Date() }).where('id', '=', userId).execute()
)

// Restore in DAL: the target row IS soft-deleted, and UPDATE statements are
// narrowed with `deleted_at IS NULL` — through the plain executor this UPDATE
// matches zero rows. Run it through the metadata-scoped executor instead:
const restoreUser = createQuery((ctx: DbContext<Database>, userId: number) =>
  ctx.db.updateTable('users').set({ deleted_at: null }).where('id', '=', userId).execute()
)
await restoreUser(withDeleted, userId)

Combined Pattern (CQRS-lite)

import { createORM } from '@kysera/repository'
import { createQuery } from '@kysera/dal'
import { softDeletePlugin } from '@kysera/soft-delete'

const orm = await createORM(db, [softDeletePlugin()])

// Repository for writes
const userRepo = orm.createRepository(createUserRepository)

// DAL for reads
const getUserStats = createQuery((ctx: DbContext<Database>, userId: number) =>
  ctx.db
    .selectFrom('users')
    .leftJoin('posts', 'posts.user_id', 'users.id')
    .select([
      'users.id',
      'users.name',
      db.fn.count('posts.id').as('post_count')
    ])
    .where('users.id', '=', userId)
    .groupBy('users.id')
    .executeTakeFirst()
)

// Use in transaction
await orm.transaction(async (ctx) => {
  // Write with repository
  const user = await userRepo.create({ name: 'Alice' })

  // Read with DAL (same transaction, same plugins)
  const stats = await getUserStats(ctx, user.id)

  // Soft delete with repository
  await userRepo.softDelete(user.id)
})

Database Schema

Ensure your tables have the deleted_at column:

-- PostgreSQL
ALTER TABLE users ADD COLUMN deleted_at TIMESTAMP DEFAULT NULL;
CREATE INDEX idx_users_deleted_at ON users(deleted_at) WHERE deleted_at IS NULL;

-- MySQL
ALTER TABLE users ADD COLUMN deleted_at DATETIME DEFAULT NULL;
CREATE INDEX idx_users_deleted_at ON users(deleted_at);

-- SQLite
ALTER TABLE users ADD COLUMN deleted_at TEXT DEFAULT NULL;
CREATE INDEX idx_users_deleted_at ON users(deleted_at);

Transaction Support

Soft delete operations respect ACID properties and work correctly with transactions.

Repository Pattern

import { createORM } from '@kysera/repository'
import { softDeletePlugin } from '@kysera/soft-delete'

const orm = await createORM(db, [softDeletePlugin()])

await orm.transaction(async ctx => {
  const userRepo = orm.createRepository(createUserRepository)
  const postRepo = orm.createRepository(createPostRepository)

  // All operations in transaction
  await userRepo.softDelete(userId)
  await postRepo.softDeleteMany([1, 2, 3])

  // If transaction fails, all operations roll back
})

DAL Pattern

import { withTransaction } from '@kysera/dal'
import { createExecutor } from '@kysera/executor'
import { softDeletePlugin } from '@kysera/soft-delete'

const executor = await createExecutor(db, [softDeletePlugin()])

await withTransaction(executor, async txCtx => {
  // All queries in transaction have soft-delete filter applied
  const user = await getUser(txCtx, userId)
  const posts = await getUserPosts(txCtx, userId)

  // Manual soft delete in transaction
  await txCtx.db
    .updateTable('users')
    .set({ deleted_at: new Date() })
    .where('id', '=', userId)
    .execute()

  // If transaction fails, all operations roll back
})

Cascade Soft Delete

For related entities, manually implement cascade soft delete:

await orm.transaction(async ctx => {
  const userRepo = orm.createRepository(createUserRepository)
  const postRepo = orm.createRepository(createPostRepository)

  // 1. Find child records
  const userPosts = await postRepo.findBy({ user_id: userId })

  // 2. Soft delete children
  await postRepo.softDeleteMany(userPosts.map(p => p.id))

  // 3. Soft delete parent
  await userRepo.softDelete(userId)

  // All operations commit or roll back together
})

Plugins are automatically propagated to transactions, so soft-delete filtering works correctly in both patterns.

Per-Query Override

You can opt queries out of the soft-delete filter without disabling any other plugin by deriving a metadata-scoped executor.

Using withPluginMetadata (Recommended)

import { withPluginMetadata } from '@kysera/executor'
import { createQuery } from '@kysera/dal'

const withDeleted = withPluginMetadata(executor, { includeDeleted: true })

const getAllUsers = createQuery((ctx: DbContext<Database>) =>
  ctx.db.selectFrom('users').selectAll().execute()
)

// Soft-delete filter off; every other plugin (RLS, timestamps, ...) stays active
const allUsers = await getAllUsers(withDeleted)

The metadata channel is scoped: only plugins that explicitly read a key react to it. The soft-delete plugin skips its filter when metadata.includeDeleted === true; security plugins such as RLS deliberately ignore this channel, so the opt-out cannot widen tenant visibility.

Why not getRawDb?

getRawDb() returns the raw Kysely instance and bypasses all plugin interceptors — not just soft-delete. With an RLS plugin installed, that turns a harmless "include deleted rows" query into a cross-tenant data leak. Reserve getRawDb() for genuinely plugin-free internals and use withPluginMetadata for scoped opt-outs.

Best Practices

1. Schema Design

Always use nullable deleted_at columns:

interface UsersTable {
  id: Generated<number>
  email: string
  deleted_at: Date | null // Must be nullable
}

2. Database Indexes

Create partial indexes for active records (PostgreSQL):

-- Index only active records
CREATE INDEX idx_users_active ON users(id)
WHERE deleted_at IS NULL;

-- Composite index with deleted_at
CREATE INDEX idx_users_email_active ON users(email)
WHERE deleted_at IS NULL;

For MySQL and SQLite, use regular indexes:

CREATE INDEX idx_users_deleted_at ON users(deleted_at);

3. Periodic Cleanup

Hard delete old soft-deleted records to prevent table bloat:

import { createQuery } from '@kysera/dal'
import { withPluginMetadata } from '@kysera/executor'

const cleanupOldDeleted = createQuery(async (ctx: DbContext<Database>, daysOld: number) => {
  const cutoffDate = new Date()
  cutoffDate.setDate(cutoffDate.getDate() - daysOld)

  await ctx.db
    .deleteFrom('users')
    .where('deleted_at', '<', cutoffDate)
    .where('deleted_at', 'is not', null)
    .execute()
})

// DELETE statements are narrowed with `deleted_at IS NULL`, so run the
// cleanup through a metadata-scoped executor that can see soft-deleted rows.
// Deletes records soft-deleted more than 90 days ago:
const withDeleted = withPluginMetadata(executor, { includeDeleted: true })
await cleanupOldDeleted(withDeleted, 90)

4. Cascade Operations

Implement cascade soft delete for related entities:

await orm.transaction(async ctx => {
  const userRepo = orm.createRepository(createUserRepository)
  const postRepo = orm.createRepository(createPostRepository)
  const commentRepo = orm.createRepository(createCommentRepository)

  // Get all related records
  const posts = await postRepo.findBy({ user_id: userId })
  const comments = await commentRepo.findBy({ user_id: userId })

  // Soft delete in order: children first, then parent
  await commentRepo.softDeleteMany(comments.map(c => c.id))
  await postRepo.softDeleteMany(posts.map(p => p.id))
  await userRepo.softDelete(userId)
})

5. Unique Constraints

Handle unique constraints with soft-deleted records:

-- PostgreSQL: Partial unique index (excludes deleted)
CREATE UNIQUE INDEX idx_users_email_unique ON users(email)
WHERE deleted_at IS NULL;

-- MySQL/SQLite: Use composite unique constraint
-- This allows duplicate emails if one is soft-deleted
ALTER TABLE users ADD CONSTRAINT unique_active_email
  UNIQUE (email, deleted_at);

Schema Validation (Optional)

The soft-delete plugin optionally supports Zod schema validation for configuration:

import { SoftDeleteOptionsSchema } from '@kysera/soft-delete/schema'
import { z } from 'zod'

// Validate configuration
const config = SoftDeleteOptionsSchema.parse({
  deletedAtColumn: 'deleted_at',
  includeDeleted: false,
  tables: ['users', 'posts']
})

tip

The main @kysera/soft-delete package works without Zod installed. Only import from /schema if you need runtime validation.

Architecture

The soft-delete plugin uses the unified @kysera/executor layer:

Plugin Interface

The plugin implements the standard Plugin interface from @kysera/executor:

interface Plugin {
  name: string
  version: string
  interceptQuery?<QB>(qb: QB, context: QueryBuilderContext): QB
  extendRepository?<T>(repo: T): T
}

How It Works

Query Interception: The interceptQuery() hook adds WHERE deleted_at IS NULL to SELECT queries and narrows the row scope of UPDATE/DELETE statements
Repository Extensions: The extendRepository() hook adds soft-delete methods (softDelete, restore, etc.)
Scoped Opt-Out: Extension methods that must reach soft-deleted rows (restore(), findDeleted(), ...) use the withPluginMetadata channel internally instead of bypassing all plugins
Cross-Pattern Support: Works with both Repository and DAL patterns

restore()'s existence probe additionally reads through @kysera/core's per-operation row cache (v0.10+): when the audit plugin wraps the same call, its old-values fetch has identical visibility and the probe reuses that SELECT instead of issuing a second one. Behavior is unchanged when the plugin runs alone.

On PostgreSQL and SQLite, softDelete() folds its post-update read-back into a single UPDATE ... RETURNING statement (v0.10+); MySQL and MSSQL keep the two-statement UPDATE + SELECT path. Results and errors are identical on all dialects.

Method Override Pattern

IMPORTANT: Soft deleting itself uses the Method Override pattern — a DELETE is never silently converted:

Operation	Behavior
✅ SELECT queries	Automatically filtered (`WHERE deleted_at IS NULL`)
✅ UPDATE statements	Narrowed with `deleted_at IS NULL` — soft-deleted rows untouchable
✅ DELETE statements	Narrowed with `deleted_at IS NULL`; NOT converted to soft deletes
✅ Repository methods	Extended with `softDelete()`, `restore()`, etc.
✅ Hard delete	Use `hardDelete()` method for real DELETE

This design is intentional for simplicity and explicitness. Users must explicitly call softDelete() instead of delete() to perform soft deletes.

Performance Characteristics

SELECT overhead: Minimal (adds one WHERE clause)
Batch operations: Single query for multiple records
Index support: Partial indexes recommended (PostgreSQL)
Transaction support: Full ACID compliance

Installation​

Quick Start​

Repository Pattern​

DAL Pattern​

Configuration​

Configuration Examples​

Repository Extensions​

Single Record Operations​

Batch Operations​

Query Methods​

Automatic Query Filtering​

How It Works​

Filtering Behavior​

Usage Examples​

Repository Pattern Examples​

DAL Pattern Examples​

Combined Pattern (CQRS-lite)​

Database Schema​

Transaction Support​

Repository Pattern​

DAL Pattern​

Cascade Soft Delete​

Per-Query Override​

Using withPluginMetadata (Recommended)​

Why not getRawDb?​

Best Practices​

1. Schema Design​

2. Database Indexes​

3. Periodic Cleanup​

4. Cascade Operations​

5. Unique Constraints​

Schema Validation (Optional)​

Architecture​

Plugin Interface​

How It Works​

Method Override Pattern​

Performance Characteristics​

See Also​

Installation

Quick Start

Repository Pattern

DAL Pattern

Configuration

Configuration Examples

Repository Extensions

Single Record Operations

Batch Operations

Query Methods

Automatic Query Filtering

How It Works

Filtering Behavior

Usage Examples

Repository Pattern Examples

DAL Pattern Examples

Combined Pattern (CQRS-lite)

Database Schema

Transaction Support

Repository Pattern

DAL Pattern

Cascade Soft Delete

Per-Query Override

Using withPluginMetadata (Recommended)

Why not getRawDb?

Best Practices

1. Schema Design

2. Database Indexes

3. Periodic Cleanup

4. Cascade Operations

5. Unique Constraints

Schema Validation (Optional)

Architecture

Plugin Interface

How It Works

Method Override Pattern

Performance Characteristics

See Also