Soft Deletes with SQLDelete and SQLRestriction

Why Soft Delete

A seller "deletes" a product, but old orders still point to it, finance needs it for GST reports, and support wants to restore it. So instead of DELETE we set a flag and hide the row from normal queries.

import org.hibernate.annotations.SQLDelete;
import org.hibernate.annotations.SQLRestriction;

@Entity
@Table(name = "products")
@SQLDelete(sql = "update products set deleted = true where id = ? and version = ?")
@SQLRestriction("deleted = false")          // Hibernate 6.3+ (replaces the deprecated @Where)
public class Product {
    @Id @GeneratedValue(strategy = GenerationType.SEQUENCE)
    private Long id;
    private String name;
    private boolean deleted;

    @Version
    private Long version;                   // with @Version, the custom SQL must take it as the 2nd parameter
}

Note: @SQLDelete parameters are bound in the same order Hibernate uses for its own delete: the id, then the version (if the entity is versioned). Forgetting and version = ? on a versioned entity causes a parameter error.

What the SQL Looks Like

SQL Log

productRepository.deleteById(7L);   productRepository.findAll();

Example output

update products set deleted = true where id = ? and version = ?
select p1_0.id,p1_0.deleted,p1_0.name,p1_0.version from products p1_0 where (p1_0.deleted = false)

@SQLRestriction is added to every entity load: find, JPQL, Criteria, and lazy loads of collections of that entity.
It is not added to native SQL queries. You must write the condition yourself there.
It is fixed at compile time: you cannot switch it off for an admin "show deleted" screen (use a filter or a native query for that).
Put @SQLRestriction on a collection field (e.g. Order.items) to filter only that association.

Simulation

Example

Plain Java version of the behaviour: "delete" sets a flag, normal queries apply the restriction, a native "admin" query does not:

import java.util.*;
import java.util.stream.*;

public class Main {
    record Product(long id, String name, boolean deleted) {}

    static List<Product> table = new ArrayList<>(List.of(
        new Product(1, "Steel Bottle", false), new Product(2, "Yoga Mat", false), new Product(3, "Desk Lamp", false)));

    static void deleteById(long id) {               // @SQLDelete
        table.replaceAll(p -> p.id() == id ? new Product(p.id(), p.name(), true) : p);
        System.out.println("update products set deleted=true where id=" + id);
    }

    static List<String> findAll() {                 // @SQLRestriction("deleted = false")
        return table.stream().filter(p -> !p.deleted()).map(Product::name).collect(Collectors.toList());
    }

    static List<String> nativeAdminQuery() {         // select * from products
        return table.stream().map(p -> p.name() + (p.deleted() ? " (deleted)" : "")).collect(Collectors.toList());
    }

    public static void main(String[] args) {
        deleteById(2);
        System.out.println("findAll()        -> " + findAll());
        System.out.println("rows still in DB -> " + table.size());
        System.out.println("admin native SQL -> " + nativeAdminQuery());
    }
}

Output

update products set deleted=true where id=2
findAll()        -> [Steel Bottle, Desk Lamp]
rows still in DB -> 3
admin native SQL -> [Steel Bottle, Yoga Mat (deleted), Desk Lamp]

Unique Constraints and Soft Deletes

A unique SKU breaks when a seller deletes a product and re-creates it with the same SKU: the hidden row still holds it. Make the constraint cover only live rows:

-- PostgreSQL: partial unique index
CREATE UNIQUE INDEX uk_products_sku_live ON products (sku) WHERE deleted = false;

-- MySQL (no partial indexes): use a nullable deleted_at and a unique (sku, deleted_at)...
-- but NULLs are not equal in MySQL unique indexes, so use a generated column instead:
ALTER TABLE products ADD COLUMN live_sku VARCHAR(40)
  GENERATED ALWAYS AS (CASE WHEN deleted = false THEN sku END) STORED,
  ADD UNIQUE KEY uk_products_live_sku (live_sku);