Soft Deletion Architecture

Overview

Soft-deleted rows stay in the database (deleted_at set) but are hidden from normal queries. That preserves history and FK integrity while still supporting restore and admin visibility.

Design goals

Base model — deleted_at, soft_delete() / restore() on shared Base so new entities get the same behavior without one-off flags.
Automatic filters — A compile-time listener adds deleted_at IS NULL so callers do not manually append the predicate everywhere.
Correct pagination — Filters apply before LIMIT/OFFSET where SQLAlchemy requires it (see event listener).
Escape hatches — without_soft_delete_filter(), QueryBuilder helpers, and CRUD flags for recycle-bin and restore flows.

Architecture

The implementation uses a layered approach with four complementary mechanisms:

1. Base Model Enhancement

All models inherit from Base which now includes:

base-model.py
# Soft delete support
deleted_at = Column(DateTime, nullable=True, index=True)

@hybrid_property
def is_deleted(self):
    """Check if this record is soft-deleted"""
    return self.deleted_at is not None

@is_deleted.expression
def is_deleted(cls):
    """SQL expression for is_deleted filter"""
    return cls.deleted_at.isnot(None)

def soft_delete(self):
    """Mark this record as deleted"""
    from datetime import datetime, timezone
    self.deleted_at = datetime.now(timezone.utc)

def restore(self):
    """Restore a soft-deleted record"""
    self.deleted_at = None

Location: apps/backend/src/rhesis/backend/app/models/base.py

2. SQLAlchemy Event Listener (Automatic Filtering)

A before_compile event listener automatically adds deleted_at IS NULL filter to ALL queries. This is the core mechanism that enables automatic filtering without code changes.

Key features:

Intercepts queries at compilation time
Catches InvalidRequestError for queries with LIMIT/OFFSET
Modifies _where_criteria tuple directly when .filter() fails
Respects _include_soft_deleted flag for explicit control

Location: apps/backend/src/rhesis/backend/app/models/soft_delete_events.py

3. QueryBuilder Enhancements

New methods for explicit control over soft delete behavior:

querybuilder-methods.py
# Include both active and deleted records
QueryBuilder(db, User).with_deleted().all()

# Retrieve only deleted records (recycle bin)
QueryBuilder(db, User).only_deleted().all()

Location: apps/backend/src/rhesis/backend/app/utils/model_utils.py

4. Context Manager (Global Control)

Use the without_soft_delete_filter() context manager to temporarily disable filtering:

context-manager.py
from rhesis.backend.app.database import without_soft_delete_filter

# Normal query (excludes deleted)
active_users = db.query(User).all()

# With context manager (includes deleted)
with without_soft_delete_filter():
    all_users = db.query(User).all()

Location: apps/backend.src/rhesis/backend/app/database.py

CRUD Operations

The CRUD utilities have been enhanced to support soft deletion:

crud-operations.py
from rhesis.backend.app.utils import crud_utils

# Soft delete (default behavior)
deleted_item = crud_utils.delete_item(db, Model, item_id, organization_id=org_id)

# Restore a soft-deleted item
restored_item = crud_utils.restore_item(db, Model, item_id, organization_id=org_id)

# Get only soft-deleted items
deleted_items = crud_utils.get_deleted_items(db, Model, organization_id=org_id)

# Permanent deletion (WARNING: Cannot be undone)
success = crud_utils.hard_delete_item(db, Model, item_id, organization_id=org_id)

# Get item including deleted records
item = crud_utils.get_item(db, Model, item_id, include_deleted=True)

Location: apps/backend/src/rhesis/backend/app/utils/crud_utils.py

Recycle Bin API (Superuser Only)

A complete REST API for managing deleted records is available at /recycle:

List Available Models

recycle-list-models.txt
GET /recycle/models

Get Deleted Records

recycle-get-deleted.txt
GET /recycle/{model_name}?skip=0&limit=100&organization_id={org_id}

Restore a Record

recycle-restore.txt
POST /recycle/{model_name}/{item_id}/restore

Permanently Delete a Record

recycle-delete.txt
DELETE /recycle/{model_name}/{item_id}?confirm=true

Get Recycle Bin Statistics

recycle-stats.txt
GET /recycle/stats/counts

Bulk Restore

recycle-bulk-restore.txt
POST /recycle/bulk-restore/{model_name}
Body: { "item_ids": ["uuid1", "uuid2", ...] }

Empty Recycle Bin for a Model

recycle-empty.txt
DELETE /recycle/empty/{model_name}?confirm=true

Location: apps/backend/src/rhesis/backend/app/routers/recycle.py

How Pagination Works with Soft Deletion

This is a critical aspect of the implementation:

The event listener ensures soft delete filters are applied BEFORE LIMIT/OFFSET in the SQL query. Here’s how:

When .first() or .limit() are called, SQLAlchemy adds LIMIT before query compilation
The event listener attempts to use .filter() to add the soft delete condition
If InvalidRequestError is raised (because LIMIT/OFFSET already applied), the listener catches it
It then modifies the _where_criteria tuple directly, ensuring the filter becomes part of the WHERE clause

Result: Both count queries and paginated results correctly exclude deleted records, providing accurate pagination metadata.

Example Query Behavior

query-behavior.py
# This query correctly filters soft-deleted records BEFORE pagination
users = db.query(User).limit(10).all()
# SQL: SELECT * FROM user WHERE deleted_at IS NULL LIMIT 10

# Count queries also work correctly
count = db.query(User).count()
# SQL: SELECT COUNT(*) FROM user WHERE deleted_at IS NULL

Database Migration

The migration adds deleted_at column and index to all tables:

run-migration.sh
# Run migration
cd apps/backend
alembic upgrade head

Migration file: apps/backend/src/rhesis/backend/alembic/versions/e364aaec703f_add_soft_delete_support.py

Testing

Coverage includes CRUD, QueryBuilder, event listener, context manager, recycle routes, org scoping, and edge cases (e.g. .first()).

Test files:

tests/backend/utils/test_soft_delete_crud.py
tests/backend/utils/test_soft_delete_querybuilder.py
tests/backend/routes/test_recycle.py

Usage Examples

Basic Soft Delete and Restore

basic-soft-delete-restore.py
from rhesis.backend.app.utils import crud_utils
from rhesis.backend.app import models

# Soft delete a test
deleted_test = crud_utils.delete_item(
    db, models.Test, test_id,
    organization_id=org_id
)

# The test is now hidden from normal queries
test = crud_utils.get_item(db, models.Test, test_id)  # Returns None

# But can be retrieved with include_deleted=True
test = crud_utils.get_item(
    db, models.Test, test_id,
    include_deleted=True
)  # Returns the deleted test

# Restore the test
restored_test = crud_utils.restore_item(
    db, models.Test, test_id,
    organization_id=org_id
)

Using QueryBuilder

using-querybuilder.py
from rhesis.backend.app.utils.model_utils import QueryBuilder

# Default: excludes deleted records
active_tests = QueryBuilder(db, models.Test)\
    .with_organization_filter(org_id)\
    .all()

# Include deleted records
all_tests = QueryBuilder(db, models.Test)\
    .with_deleted()\
    .with_organization_filter(org_id)\
    .all()

# Only deleted records (recycle bin view)
deleted_tests = QueryBuilder(db, models.Test)\
    .only_deleted()\
    .with_organization_filter(org_id)\
    .with_sorting('deleted_at', 'desc')\
    .all()

Using Context Manager

using-context-manager.py
from rhesis.backend.app.database import without_soft_delete_filter

# For admin operations that need to see everything
with without_soft_delete_filter():
    all_users = db.query(models.User).all()
    deleted_count = db.query(models.User)\
        .filter(models.User.deleted_at.isnot(None))\
        .count()

Key Implementation Files

File	Purpose
`app/models/base.py`	Base model with soft deletion columns and methods
`app/models/soft_delete_events.py`	SQLAlchemy event listener for automatic filtering
`app/database.py`	Context manager for global control
`app/utils/crud_utils.py`	Enhanced CRUD operations
`app/utils/model_utils.py`	QueryBuilder with soft delete methods
`app/routers/recycle.py`	REST API for recycle bin management
`alembic/versions/e364aaec703f_add_soft_delete_support.py`	Database migration