Master Spring Data derived queries for soft delete, why default findAll() and findById() methods leak archived records, and clean repository interfaces.

Derived Query Methods & The `findById` Trap

In a soft-delete architecture, using default Spring Data JPA repository methods like findAll() or findById() leads to severe data leakage because physically existing archived records continue to be returned.

1. The `findAll()` Data Leakage Trap

Suppose a database contains three records:

`id`	`name`	`is_deleted`
1	Rahul	`false` (Active)
2	Priya	`true` (Soft-Deleted)
3	Aman	`false` (Active)

If your REST controller calls standard repository.findAll(), Spring Data issues:

SELECT * FROM students; -- Returns ALL 3 rows including Priya!

[!WARNING] Data Leakage Risk: Standard findAll() ignores soft-delete markers and leaks logically deleted records into your active API responses!

2. Spring Data JPA Derived Query Methods

To enforce active record filtering, declare a derived query method in your repository interface:

public interface StudentRepository extends JpaRepository<Student, Long> {

    // Spring Data parses this method name to append "WHERE is_deleted = false"
    List<Student> findByIsDeletedFalse();
}

Method Name Parsing Breakdown

findBy    ──► Generates SELECT query
IsDeleted ──► Targets entity field "isDeleted"
False     ──► Constrains value to false (WHERE is_deleted = false)

Generated SQL:

SELECT * FROM students WHERE is_deleted = false;

3. The `findById()` Leakage Trap & Solution

A subtle bug occurs when single-record lookup uses standard findById(id):

// UNSAFE: Returns archived student if id = 2 physically exists!
Optional<Student> student = studentRepository.findById(2L);

Because findById(id) checks only physical primary key existence (WHERE id = ?), a soft-deleted record is returned as present!

The Soft-Delete-Aware Solution

public interface StudentRepository extends JpaRepository<Student, Long> {

    List<Student> findByIsDeletedFalse();

    // Enforces BOTH Primary Key match AND Active State!
    Optional<Student> findByIdAndIsDeletedFalse(Long id);
}

Method Parsing Breakdown

findBy    ──► Generates SELECT query
Id        ──► Constrains WHERE id = ?
And       ──► Appends AND operator
IsDeleted ──► Targets entity field "isDeleted"
False     ──► Constrains WHERE is_deleted = false

Generated SQL:

SELECT * FROM students WHERE id = ? AND is_deleted = false;

4. Repository Method Strategy Comparison

Method	Soft-Deleted Records Returned?	Safe for Soft-Delete Active APIs?
`findAll()`	Yes (Leaked)	❌ Unsafe
`findByIsDeletedFalse()`	No (Filtered)	✅ Safe for List Endpoints
`findById(id)`	Yes (Leaked)	❌ Unsafe
`findByIdAndIsDeletedFalse(id)`	No (Filtered)	✅ Safe for Single Read Endpoints

5. Soft Delete Execution Pipeline

Executing a soft delete follows a 3-step pipeline: Fetch ──► Mutate ──► Save.

❓ Knowledge Check

Knowledge Check

Why is standard repository.findById(id) unsafe in a soft-delete architecture?