Soft delete mechanisms for the catalog

1. Principles for choosing a mechanism

In soft delete there is one tricky detail: technically, it looks very simple. Add a deleted column, start writing where deleted = false everywhere, and life seems good. The problem is that this kind of “simplicity” quickly turns into a swamp. In a week you will have queries where the condition was forgotten, in two weeks — queries where it was written incorrectly, and in a month you will be chasing a bug that depends on which repository a particular developer wrote and how awake they were.

In a Hibernate-heavy project, we need a different feeling: when I see any read case, I should be able to predict in advance whether a soft-deleted row will appear in the result. And I do not want to play “find 15 places where the condition was forgotten.” So our goal in this course is to lock visibility rules either in the mapping layer, when they are almost always the same, or in an explicit scenario mechanism, when they need to switch, rather than scattering conditions across all queries like confetti at a company party.

Three questions for choosing a mechanism

Before choosing an annotation, or even deciding whether annotations are the right approach at all, it is useful to stop and ask yourself three questions. And yes, this is one of those rare cases where “let’s talk about meaning” genuinely saves you code and nerves.

The first question is: what exactly counts as “deleted” in business terms? This matters more than it seems. Sometimes “delete a product” means “it disappears from the catalog forever.” Sometimes it means “it disappears for customers, but remains in order history.” Sometimes it means “it disappears from normal lists, but an admin can bring up the archive.” The second question: where does the deletion indicator physically live? In the entity table itself (product.deleted), in a join table (if you use ManyToMany), or in a separate link entity (as with our product_category_assignment.deleted). And the third question: should visibility switch within a scenario? That is, “usually hidden, but sometimes shown” is a completely different mechanism from “hidden all the time.”

These three questions give you not one “correct annotation,” but the correct class of solution. And then things become easier: Hibernate is a tool you choose for the task, not a casino where you bet on @SoftDelete and hope life improves.

Mechanism map and constraints

To avoid holding all this in your head as a mess, it is convenient to summarize the mechanisms in a small table. A table is not magic, but it saves you from “wait, did we already discuss this?”

And here is an important practical annoyance: @SoftDelete creates a very strong read rule. If you think “I’ll just write where deleted = true in JPQL later and find deleted rows,” Hibernate may disappoint you. It will add its own visibility condition, and you will get a query like deleted=true AND deleted=false. So sometimes @SoftDelete perfectly solves default visibility, but makes “archive reading” more difficult, usually through a native query or separate modeling.

2. The project: where the deletion indicator lives

Now let us ground everything in our project. A key fact: in Commerce Persistence Lab, the Product ↔ Category relationship is not implemented as a naive ManyToMany. We use a link entity, ProductCategoryAssignment. That means the question “where should we store the deleted flag for the relationship?” is solved at the entity level, not at the join table annotation level.

In simplified form, our tables look like this:

It is important to establish the baseline: deleted exists not only on product and category, but also on product_category_assignment. For the relationship, this immediately gives a simple rule: assigning a category again is not a new INSERT of the same pair, but a restore of an existing row.

erDiagram
  PRODUCT ||--o{ PRODUCT_CATEGORY_ASSIGNMENT : "has"
  CATEGORY ||--o{ PRODUCT_CATEGORY_ASSIGNMENT : "has"

  PRODUCT {
    bigint id
    varchar sku
    varchar name
    boolean deleted
  }

  CATEGORY {
    bigint id
    varchar code
    varchar name
    boolean deleted
  }

  PRODUCT_CATEGORY_ASSIGNMENT {
    bigint id
    bigint product_id
    bigint category_id
    int sort_order
    timestamp assigned_at
    boolean deleted
  }

The diagram matters not in itself, but as an answer to the second question from the “three questions” list: where does the visibility indicator live. In our case, it lives in the entity tables, and also in the link entity table.

3. The choice for Product: @SoftDelete and sku

For a product, soft delete is a classic. A product may disappear from the active catalog while still remaining in order history, audit trails, links, and everywhere else where the fact of its existence matters. That is why for Product we choose a strong default: entity-level @SoftDelete(strategy = SoftDeleteType.DELETED, columnName = "deleted"). The mechanism is simple: remove() means logical hiding, not physical DELETE. What matters next is not repeating the mapping, but understanding its consequences for sku.

Nuance: sku uniqueness

Now we come to the most important place where students usually step on a rake, and rakes are patient — they do not run away.

Our project has a rule: sku is not reused after soft delete. That means if a product once existed with SKU-123, we do not want to create a new product with the same sku a year later, even if the old one is “deleted.” For that, the rule must be expressed not only in Java code, but also in the database schema. Therefore the unique index/constraint on sku remains ordinary, “across all rows,” with no exceptions for deleted.

And now the trap: many checks like existsBySku() will start acting “as if sku is free,” because soft-deleted rows are often not visible through ORM reads. So you can end up with a logically incorrect service check: “sku is free,” and then a failure on unique constraint in PostgreSQL at insert time. That is not a PostgreSQL bug; it is a bug in our expectation.

The most practical way to make the behavior honest is to add a separate validation method that looks directly at the table, via a native query, that is, “including deleted”:

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

public interface ProductRepository extends JpaRepository<Product, Long> {

    // Special method: check sku "occupancy" in the table, including archived rows
    @Query(value = "select exists (select 1 from product where sku = :sku)", nativeQuery = true)
    boolean existsBySkuIncludingDeleted(String sku);
}

Yes, this looks a bit “dirtier” than a derived query. But it honestly expresses the business rule: sku is taken, even if the product is archived.

4. The choice for Category: @SoftDelete and archive reads

Categories are almost the same case, just with a more “reference data” nature. For Category, we use the same entity-level soft delete with a deleted column: the category disappears from ordinary reads, but remains in links and history. So here the main question is no longer how remove() works, but how to read the archive honestly.

Important note: “archived” queries and @SoftDelete

If you suddenly want to do this:

// DO NOT DO THIS (at least not if you expect it to work)
List<Category> deleted = entityManager.createQuery(
        "select c from Category c where c.deleted = true",
        Category.class
).getResultList();

...then Hibernate may generate SQL where your condition is combined with its default visibility condition. And you will get deleted=true AND deleted=false. This is not “Hibernate being difficult”; it is the logic of @SoftDelete: by definition, it considers deleted rows invisible to HQL/JPQL.

If you really need an “archive list,” it is easier and more honest to follow the route we already covered in the read-model module: use a separate read via native query in a projection/DTO, and do not try to force a managed entity to temporarily “see what it should not see.”

For example, a very compact archive projection:

// Projection for archive reading: we do not need a managed entity, only a few fields
public interface ArchivedCategoryRow {
    Long getId();
    String getCode();
}

And the query:

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.Repository;

public interface CategoryArchiveQuery extends Repository<Category, Long> {

    // Separate read case: read archived categories directly with SQL
    @Query(value = "select id, code from category where deleted = true order by code", nativeQuery = true)
    java.util.List<ArchivedCategoryRow> findArchived();
}

From the course perspective, this is nice: the write model (entity) lives with strong rules, and the read model for special scenarios is built separately and transparently.

5. The Product—Category relationship: choosing a mechanism

When relationships come up, beginners often have a simple thought in their head: “Well, it is just a table that connects two IDs... what is there to choose?” And that is where ORM reality starts smiling politely and pulling up a second chair.

In the ManyToMany world, you really do have a join table, and if you want to hide relationship rows, you need join-table-level tools: @SQLJoinTableRestriction (static) or @FilterJoinTable (dynamic). There, the visibility indicator lives exactly in the relationship table, and you filter exactly the relationship rows.

But in our case, the relationship is modeled as the ProductCategoryAssignment entity. That means lifecycle and visibility are designed at the entity level itself, not at the level of a hidden join table.

Soft delete for ProductCategoryAssignment: our default

In the catalog we choose exactly this path: ProductCategoryAssignment also has deleted. The relationship has its own fields, we want to preserve the fact of past assignments, and we treat reassigning a category as restoring the same row rather than as a second INSERT.

import jakarta.persistence.Entity;
import jakarta.persistence.Id;
import jakarta.persistence.ManyToOne;
import jakarta.persistence.FetchType;
import org.hibernate.annotations.SoftDelete;
import org.hibernate.annotations.SoftDeleteType;

@Entity
@SoftDelete(strategy = SoftDeleteType.DELETED, columnName = "deleted") // softly hide the category assignment
public class ProductCategoryAssignment {

    @Id
    private Long id; // PK of the assignment row

    @ManyToOne(fetch = FetchType.LAZY)
    private Product product; // which product the assignment belongs to

    @ManyToOne(fetch = FetchType.LAZY)
    private Category category; // which category is assigned
}

And then remove() becomes “archiving” the relationship.

Physical delete is useful to keep in mind as a contrasting option, but not as the chosen default. Once the relationship lives as its own entity, soft delete gives it the same predictability as for Product / Category.

As soon as you choose soft delete for a link entity, you get extra responsibility: what to do with the uniqueness of the product_id + category_id pair. If the database has a unique constraint and the relationship row remains, you cannot simply “create the relationship again.” That means “assign the category again” is not an insert of a new pair, but a restore of the existing record.

A mini-pattern: restore instead of insert when the relationship was deleted

One of the simplest approaches is to give the repository a native UPDATE that “revives” the relationship if it already exists:

import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.Repository;

public interface ProductCategoryAssignmentRepository extends Repository<ProductCategoryAssignment, Long> {

    // Restore: if the assignment row already exists and is marked deleted=true, just switch it back
    @Modifying
    @Query(value = """
        update product_category_assignment
           set deleted = false
         where product_id = :productId
           and category_id = :categoryId
           and deleted = true
        """, nativeQuery = true)
    int restore(long productId, long categoryId);
}

And in the service, the logic may look like this, simplified:

import jakarta.transaction.Transactional;

@Transactional
public void assignCategory(long productId, long categoryId) {
    // First try to restore the archived relationship (if it exists)
    int restored = assignmentRepository.restore(productId, categoryId);
    if (restored > 0) return; // the relationship already existed, we just restored it

    // Otherwise create a new relationship in the usual way (insert a new row)
}

Notice the style: here we are not trying to “trick” Hibernate; we are acknowledging that soft delete is a visibility rule, not row disappearance. So restore is a conscious operation.

6. One default and separate scenarios

The most common architectural mistake in soft delete looks like this: a developer first adds @SoftDelete, then adds @SQLRestriction, then adds @Filter just in case, and then writes where deleted = false in the repository “for reliability.” This is one of those rare cases where “backup protection” makes the system less safe. You no longer understand what filtered the row: the annotation? the filter? the query condition? or just “luck.”

So the practical rule for our course sounds boring, but it saves projects: each entity should have one main default visibility mechanism, and scenario-specific exceptions should be implemented as separate read cases. For Product, Category, and ProductCategoryAssignment, our default is entity-level @SoftDelete. That means the ordinary service and ordinary repository read only “live” rows, without manual conditions. @SQLRestriction and @Filter are useful here not as a second mandatory layer “for reliability,” but as separate mechanisms for cases where the read-side task is the real problem.

If you need scenario-specific reading, for example an “archive list,” you have two honest options. The first is to use dynamic filters (@Filter), but then you must remember that @SoftDelete is not intended to be “temporarily turned off” for HQL/JPQL queries. In other words, a visibility filter for deleted rows usually makes sense when you model deletion yourself (for example, through a manual deleted flag and @Filter), not through @SoftDelete.

The second option is what we already showed with ArchivedCategoryRow: make a separate read case through a native query and projection. In the course, this option is often simpler because it clearly shows what you are doing and does not create hidden state in the Session. Yes, you write SQL. But you already know how to read SQL logs, and that is exactly why we are here.

7. Typical mistakes with soft delete

Mistake #1: trying to fetch deleted rows through JPQL “straight up” with @SoftDelete.
Intuition says: “there is a deleted field, so I’ll write where deleted=true.” But @SoftDelete adds its own visibility rule, and you risk getting a nonsensical condition like deleted=true AND deleted=false. If you need to read the archive, make a separate native read case in a projection/DTO, or model soft delete manually without @SoftDelete.

Mistake #2: checking business-key uniqueness (sku, code) through ordinary ORM methods.
It is very easy to write existsBySku() and assume that if the method returned false, then sku is free. With soft delete, that may be untrue: deleted rows are often invisible to ORM reads. In a project where keys are not reused, either check uniqueness with a native query “including deleted” or honestly rely on the unique constraint in the database and treat violations as a business error.

Mistake #3: mixing several filtering mechanisms “just in case.”
The combination of @SoftDelete + @SQLRestriction + @Filter + manual query conditions quickly turns data reading into unpredictable magic. From the outside it may look like “we are very reliable,” but in practice you have only made it harder to explain why a row did not appear in the result. Choose one main default mechanism and handle separate scenario-specific reads separately.

Mistake #4: ignoring the consequences of soft delete for relationships.
If you soft-delete Category but leave the relationships and continue lazily walking the graph, you may get unexpected effects: a category fails to load somewhere, a query returns fewer rows somewhere else, and the relationship “disappears” somewhere else. That is not a reason to enable EAGER; it is a reason to design reads around the use case and remember that soft delete changes visibility, not “as if the row is gone.”

Mistake #5: trying to reuse a relationship or key as if soft delete were physical deletion.
If the row remains in the database, it also remains in unique constraints. For ProductCategoryAssignment this is especially noticeable: the unique pair product_id + category_id does not disappear after soft delete. So either you restore the existing relationship, or you change the uniqueness rule, which is a separate engineering story, or you consciously choose physical delete for relationships.