[Advanced Spring Boot] Multi-tenancy và soft delete với Spring Boot và Hibernate

curl -s "https://start.spring.io/starter.zip?type=gradle-project&language=java&bootVersion=4.1.1&javaVersion=21&groupId=com.example&artifactId=demo&name=demo&packageName=com.example.demo&dependencies=web,data-jpa,postgresql,flyway,validation" -o demo.zip

docker run -d --name sba-a12-pg --memory 512m -e POSTGRES_USER=demo -e POSTGRES_PASSWORD=demo -e POSTGRES_DB=demo -p 5512:5432 postgres:18

create table tenants (
    id   varchar(30)  primary key,
    name varchar(100) not null
);
 
create table categories (
    id        bigint generated by default as identity primary key,
    tenant_id varchar(30) not null references tenants (id),
    name      varchar(60) not null,
    unique (tenant_id, name)
);
 
create table products (
    id          bigint generated by default as identity primary key,
    tenant_id   varchar(30)    not null references tenants (id),
    name        varchar(120)   not null,
    sku         varchar(40)    not null,
    price       numeric(10, 2) not null,
    category_id bigint         not null references categories (id),
    unique (tenant_id, sku)
);
 
create index products_category_id_idx on products (category_id);

spring.application.name=demo
server.port=8212
spring.datasource.url=jdbc:postgresql://localhost:5512/demo
spring.datasource.username=demo
spring.datasource.password=demo
spring.jpa.open-in-view=false
spring.jpa.hibernate.ddl-auto=validate
logging.level.org.hibernate.SQL=DEBUG
spring.mvc.problemdetails.enabled=true

demo/
├── shared/src/main/java/com/example/demo/   tenant filter and resolver, repository, service, controller
├── discriminator/   @TenantId, row-level security
├── softdelete/      @TenantId + @SoftDelete
├── sqldelete/       @TenantId + @SQLDelete and @SQLRestriction
├── schema/          a schema per tenant
└── database/        a database per tenant

package com.example.demo.tenant;
 
public final class TenantContext {
 
    private static final ThreadLocal<String> CURRENT = new ThreadLocal<>();
 
    private TenantContext() {
    }
 
    public static void set(String tenantId) {
        CURRENT.set(tenantId);
    }
 
    public static String get() {
        return CURRENT.get();
    }
 
    public static void clear() {
        CURRENT.remove();
    }
}

package com.example.demo.tenant;
 
import java.io.IOException;
import java.net.URI;
 
import jakarta.servlet.FilterChain;
import jakarta.servlet.ServletException;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;
 
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ProblemDetail;
import org.springframework.stereotype.Component;
import org.springframework.web.filter.OncePerRequestFilter;
 
import tools.jackson.databind.json.JsonMapper;
 
@Component
public class TenantFilter extends OncePerRequestFilter {
 
    public static final String HEADER = "X-Tenant-Id";
 
    private final TenantRegistry tenants;
    private final JsonMapper jsonMapper;
 
    public TenantFilter(TenantRegistry tenants, JsonMapper jsonMapper) {
        this.tenants = tenants;
        this.jsonMapper = jsonMapper;
    }
 
    @Override
    protected boolean shouldNotFilter(HttpServletRequest request) {
        return !request.getRequestURI().startsWith("/api/")
                || request.getRequestURI().startsWith("/api/admin/");
    }
 
    @Override
    protected void doFilterInternal(HttpServletRequest request, HttpServletResponse response,
                                    FilterChain chain) throws ServletException, IOException {
        String tenantId = request.getHeader(HEADER);
        if (tenantId == null || tenantId.isBlank()) {
            reject(request, response, "Missing " + HEADER + " header.");
            return;
        }
        if (!tenants.exists(tenantId)) {
            reject(request, response, "Unknown tenant '" + tenantId + "'.");
            return;
        }
        TenantContext.set(tenantId);
        try {
            chain.doFilter(request, response);
        } finally {
            TenantContext.clear();
        }
    }
 
    private void reject(HttpServletRequest request, HttpServletResponse response, String detail)
            throws IOException {
        ProblemDetail problem = ProblemDetail.forStatusAndDetail(HttpStatus.BAD_REQUEST, detail);
        problem.setTitle("Invalid tenant");
        problem.setInstance(URI.create(request.getRequestURI()));
        response.setStatus(HttpStatus.BAD_REQUEST.value());
        response.setContentType(MediaType.APPLICATION_PROBLEM_JSON_VALUE);
        jsonMapper.writeValue(response.getOutputStream(), problem);
    }
}

@Component
public class TenantRegistry {
 
    private final JdbcClient jdbc;
    private final Set<String> tenantIds = ConcurrentHashMap.newKeySet();
 
    public TenantRegistry(JdbcClient jdbc) {
        this.jdbc = jdbc;
    }
 
    public boolean exists(String tenantId) {
        return tenantIds.contains(tenantId);
    }
 
    public Set<String> all() {
        return Set.copyOf(tenantIds);
    }
 
    @EventListener(ApplicationStartedEvent.class)
    public void refresh() {
        tenantIds.addAll(jdbc.sql("select id from public.tenants").query(String.class).list());
    }
}

curl -i -s -H 'X-Tenant-Id: acme' http://localhost:8212/api/products
curl -s -H 'X-Tenant-Id: globex' http://localhost:8212/api/products
curl -i -s http://localhost:8212/api/products
curl -i -s -H 'X-Tenant-Id: initech' http://localhost:8212/api/products

HTTP/1.1 200 
Content-Type: application/json
Content-Length: 332
Date: Fri, 18 Sep 2026 07:18:17 GMT
 
[{"id":1,"sku":"KB-01","name":"Mechanical keyboard","price":89.00,"category":"Keyboards"},{"id":2,"sku":"KB-02","name":"Compact keyboard","price":59.00,"category":"Keyboards"},{"id":3,"sku":"MS-01","name":"Wireless mouse","price":24.50,"category":"Mice"},{"id":4,"sku":"MS-02","name":"Gaming mouse","price":49.90,"category":"Mice"}]

[{"id":5,"sku":"KB-01","name":"Office keyboard","price":35.00,"category":"Keyboards"},{"id":6,"sku":"MN-01","name":"27-inch monitor","price":229.00,"category":"Monitors"},{"id":7,"sku":"MN-02","name":"24-inch monitor","price":159.00,"category":"Monitors"}]

HTTP/1.1 400 
Content-Type: application/problem+json
Content-Length: 105
Date: Fri, 18 Sep 2026 07:18:17 GMT
Connection: close
 
{"detail":"Missing X-Tenant-Id header.","instance":"/api/products","status":400,"title":"Invalid tenant"}

HTTP/1.1 400 
Content-Type: application/problem+json
Content-Length: 103
Date: Fri, 18 Sep 2026 07:18:17 GMT
Connection: close
 
{"detail":"Unknown tenant 'initech'.","instance":"/api/products","status":400,"title":"Invalid tenant"}

    @Override
    protected void doFilterInternal(HttpServletRequest request, HttpServletResponse response,
                                    FilterChain chain) throws ServletException, IOException {
        String tenantId = request.getHeader("X-Tenant-Id");
        if (tenantId != null) {
            TenantContext.set(tenantId);
        }
        chain.doFilter(request, response);
    }

curl -s -H 'X-Tenant-Id: globex' http://localhost:8212/api/products
curl -i -s http://localhost:8212/api/products

HTTP/1.1 200 
Content-Type: application/json
Content-Length: 256
Date: Fri, 18 Sep 2026 07:18:44 GMT
 
[{"id":5,"sku":"KB-01","name":"Office keyboard","price":35.00,"category":"Keyboards"},{"id":6,"sku":"MN-01","name":"27-inch monitor","price":229.00,"category":"Monitors"},{"id":7,"sku":"MN-02","name":"24-inch monitor","price":159.00,"category":"Monitors"}]

2026-09-18T14:18:44.074+07:00  INFO 12703 --- [demo] [nio-8212-exec-1] c.example.demo.product.ProductService    : findAll for tenant globex
2026-09-18T14:18:44.097+07:00 DEBUG 12703 --- [demo] [nio-8212-exec-1] org.hibernate.SQL                        : select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku,p1_0.tenant_id from products p1_0 where p1_0.tenant_id = ?
2026-09-18T14:18:44.098+07:00 TRACE 12703 --- [demo] [nio-8212-exec-1] org.hibernate.orm.jdbc.bind              : binding parameter (1:VARCHAR) <- [globex]
2026-09-18T14:18:44.134+07:00  INFO 12703 --- [demo] [nio-8212-exec-1] c.example.demo.product.ProductService    : findAll for tenant globex
2026-09-18T14:18:44.135+07:00 DEBUG 12703 --- [demo] [nio-8212-exec-1] org.hibernate.SQL                        : select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku,p1_0.tenant_id from products p1_0 where p1_0.tenant_id = ?
2026-09-18T14:18:44.135+07:00 TRACE 12703 --- [demo] [nio-8212-exec-1] org.hibernate.orm.jdbc.bind              : binding parameter (1:VARCHAR) <- [globex]

    @Async
    public CompletableFuture<Long> countAsync() {
        log.info("countAsync for tenant {}", TenantContext.get());
        return CompletableFuture.completedFuture(products.count());
    }

curl -s -H 'X-Tenant-Id: acme' http://localhost:8212/api/products/count-async

0

2026-09-18T14:19:00.266+07:00  INFO 13379 --- [demo] [         task-1] c.example.demo.product.ProductService    : countAsync for tenant null
2026-09-18T14:19:00.278+07:00 DEBUG 13379 --- [demo] [         task-1] org.hibernate.SQL                        : select count(*) from products p1_0 where p1_0.tenant_id = ?
2026-09-18T14:19:00.278+07:00 TRACE 13379 --- [demo] [         task-1] org.hibernate.orm.jdbc.bind              : binding parameter (1:VARCHAR) <- [_none_]

package com.example.demo.product;
 
import java.math.BigDecimal;
 
import org.hibernate.annotations.TenantId; 
 
import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.FetchType;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.JoinColumn;
import jakarta.persistence.ManyToOne;
import jakarta.persistence.Table;
 
@Entity
@Table(name = "products")
public class Product {
 
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
 
    @TenantId
    @Column(name = "tenant_id", nullable = false, length = 30) 
    private String tenantId; 
 
    @Column(nullable = false, length = 120)
    private String name;
 
    @Column(nullable = false, length = 40)
    private String sku;
 
    @Column(nullable = false, precision = 10, scale = 2)
    private BigDecimal price;
 
    @ManyToOne(fetch = FetchType.LAZY, optional = false)
    @JoinColumn(name = "category_id", nullable = false)
    private Category category;
 
    protected Product() {
    }
 
    public Product(String name, String sku, BigDecimal price, Category category) {
        this.name = name;
        this.sku = sku;
        this.price = price;
        this.category = category;
    }
 
    // getters and setPrice; no setter for tenantId
}

package com.example.demo.tenant;
 
import java.util.Map;
 
import org.hibernate.cfg.MultiTenancySettings;
import org.hibernate.context.spi.CurrentTenantIdentifierResolver;
 
import org.springframework.boot.hibernate.autoconfigure.HibernatePropertiesCustomizer;
import org.springframework.stereotype.Component;
 
@Component
public class TenantIdentifierResolver
        implements CurrentTenantIdentifierResolver<String>, HibernatePropertiesCustomizer {
 
    public static final String NO_TENANT = "_none_";
 
    @Override
    public String resolveCurrentTenantIdentifier() {
        String tenantId = TenantContext.get();
        return tenantId != null ? tenantId : NO_TENANT;
    }
 
    @Override
    public boolean validateExistingCurrentSessions() {
        return false;
    }
 
    @Override
    public void customize(Map<String, Object> hibernateProperties) {
        hibernateProperties.put(MultiTenancySettings.MULTI_TENANT_IDENTIFIER_RESOLVER, this);
    }
}

Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'productRepository' defined in com.example.demo.product.ProductRepository defined in @EnableJpaRepositories declared on DataJpaRepositoriesRegistrar.EnableJpaRepositoriesConfiguration: SessionFactory configured for multi-tenancy, but no tenant identifier specified
Caused by: org.springframework.data.repository.query.QueryCreationException: Cannot create query for method [ProductRepository.findByPriceLessThan(java.math.BigDecimal)]; SessionFactory configured for multi-tenancy, but no tenant identifier specified
Caused by: org.hibernate.HibernateException: SessionFactory configured for multi-tenancy, but no tenant identifier specified

>>> findById(1) as acme
select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku,p1_0.tenant_id from products p1_0 where p1_0.id=? and p1_0.tenant_id = ?
binding parameter (1:BIGINT) <- [1]
binding parameter (2:VARCHAR) <- [acme]
    Optional[1:acme:KB-01 89.00]
>>> findById(5) as acme, 5 belongs to globex
select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku,p1_0.tenant_id from products p1_0 where p1_0.id=? and p1_0.tenant_id = ?
binding parameter (1:BIGINT) <- [5]
binding parameter (2:VARCHAR) <- [acme]
    Optional.empty
>>> findAll() as acme
select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku,p1_0.tenant_id from products p1_0 where p1_0.tenant_id = ? order by p1_0.id
binding parameter (1:VARCHAR) <- [acme]
    [1:acme:KB-01 89.00, 2:acme:KB-02 59.00, 3:acme:MS-01 24.50, 4:acme:MS-02 49.90] (4 rows)
>>> save(new Product("Vertical mouse", "MS-03", 39.00, Mice)) as acme
insert into products (category_id,name,price,sku,tenant_id) values (?,?,?,?,?)
binding parameter (1:BIGINT) <- [2]
binding parameter (2:VARCHAR) <- [Vertical mouse]
binding parameter (3:NUMERIC) <- [39.00]
binding parameter (4:VARCHAR) <- [MS-03]
binding parameter (5:VARCHAR) <- [acme]
    8:acme:MS-03 39.00
>>> update price of 1 to 95.00 as acme
select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku,p1_0.tenant_id from products p1_0 where p1_0.id=? and p1_0.tenant_id = ?
binding parameter (1:BIGINT) <- [1]
binding parameter (2:VARCHAR) <- [acme]
update products set category_id=?,name=?,price=?,sku=? where id=?
binding parameter (1:BIGINT) <- [1]
binding parameter (2:VARCHAR) <- [Mechanical keyboard]
binding parameter (3:NUMERIC) <- [95.00]
binding parameter (4:VARCHAR) <- [KB-01]
binding parameter (5:BIGINT) <- [1]
    1:acme:KB-01 95.00
>>> deleteById(8) as acme
select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku,p1_0.tenant_id from products p1_0 where p1_0.id=? and p1_0.tenant_id = ?
binding parameter (1:BIGINT) <- [8]
binding parameter (2:VARCHAR) <- [acme]
delete from products where id=?
binding parameter (1:BIGINT) <- [8]

>>> save a product whose tenantId is set to globex, as acme
!!! org.springframework.dao.DataIntegrityViolationException: assigned tenant id differs from current tenant id [globex != acme] for entity com.example.demo.product.Product.tenantId
!!! org.hibernate.PropertyValueException: assigned tenant id differs from current tenant id [globex != acme] for entity com.example.demo.product.Product.tenantId

public interface ProductRepository extends JpaRepository<Product, Long>, JpaSpecificationExecutor<Product> {
 
    List<Product> findByPriceLessThan(BigDecimal price);
 
    @Query("select p from Product p where p.category.name = :category order by p.price")
    List<Product> findInCategory(String category);
 
    @Modifying
    @Query("update Product p set p.price = p.price * :factor where p.sku like :skuPattern")
    int reprice(String skuPattern, BigDecimal factor);
 
    @Modifying
    @Query("delete from Product p where p.sku = :sku")
    int deleteInBulkBySku(String sku);
 
    @Query(value = "select * from products where price >= :min order by id", nativeQuery = true)
    List<Product> findPricedAtLeastNative(BigDecimal min);
 
    @Query(value = "select * from products where tenant_id = :tenantId order by id", nativeQuery = true)
    List<Product> findAllNative(String tenantId);
}

>>> findInCategory("Keyboards") as acme (JPQL)
select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku,p1_0.tenant_id from products p1_0 join categories c1_0 on c1_0.id=p1_0.category_id and c1_0.tenant_id = ? where p1_0.tenant_id = ? and c1_0.name=? order by p1_0.price
binding parameter (1:VARCHAR) <- [acme]
binding parameter (2:VARCHAR) <- [acme]
binding parameter (3:VARCHAR) <- [Keyboards]
    [2:acme:KB-02 59.00, 1:acme:KB-01 89.00] (2 rows)
>>> findByPriceLessThan(50) as acme (derived)
select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku,p1_0.tenant_id from products p1_0 where p1_0.tenant_id = ? and p1_0.price<?
binding parameter (1:VARCHAR) <- [acme]
binding parameter (2:NUMERIC) <- [50]
    [3:acme:MS-01 24.50, 4:acme:MS-02 49.90] (2 rows)
>>> findAll(priceAtLeast(50)) as acme (Specification)
select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku,p1_0.tenant_id from products p1_0 where p1_0.tenant_id = ? and p1_0.price>=?
binding parameter (1:VARCHAR) <- [acme]
binding parameter (2:NUMERIC) <- [50]
    [1:acme:KB-01 89.00, 2:acme:KB-02 59.00] (2 rows)
>>> findAll(PageRequest.of(0, 2)) as acme
select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku,p1_0.tenant_id from products p1_0 where p1_0.tenant_id = ? order by p1_0.id offset ? rows fetch first ? rows only
binding parameter (1:VARCHAR) <- [acme]
binding parameter (2:INTEGER) <- [0]
binding parameter (3:INTEGER) <- [2]
select count(p1_0.id) from products p1_0 where p1_0.tenant_id = ?
binding parameter (1:VARCHAR) <- [acme]
    [1:acme:KB-01 89.00, 2:acme:KB-02 59.00] totalElements=4
>>> count() as acme
select count(*) from products p1_0 where p1_0.tenant_id = ?
binding parameter (1:VARCHAR) <- [acme]
    4
>>> reprice("KB-%", 1.10) as acme (bulk @Modifying)
update products p1_0 set price=(p1_0.price*?) where p1_0.sku like ? escape '' and p1_0.tenant_id = ?
binding parameter (1:NUMERIC) <- [1.10]
binding parameter (2:VARCHAR) <- [KB-%]
binding parameter (3:VARCHAR) <- [acme]
    2
>>> prices of KB-01 per tenant, read with JdbcClient
    [acme:KB-01 97.90, globex:KB-01 35.00] (2 rows)

>>> findPricedAtLeastNative(0) as acme (native @Query)
select * from products where price >= ? order by id
binding parameter (1:NUMERIC) <- [0]
    [1:acme:KB-01 89.00, 2:acme:KB-02 59.00, 3:acme:MS-01 24.50, 4:acme:MS-02 49.90, 5:globex:KB-01 35.00, 6:globex:MN-01 229.00, 7:globex:MN-02 159.00] (7 rows)
>>> JdbcClient select as acme
    [1:acme:KB-01 89.00, 2:acme:KB-02 59.00, 3:acme:MS-01 24.50, 4:acme:MS-02 49.90, 5:globex:KB-01 35.00, 6:globex:MN-01 229.00, 7:globex:MN-02 159.00] (7 rows)

>>> save with category 3, which belongs to globex, as acme
insert into products (category_id,name,price,sku,tenant_id) values (?,?,?,?,?)
binding parameter (1:BIGINT) <- [3]
binding parameter (2:VARCHAR) <- [Cross-tenant keyboard]
binding parameter (3:NUMERIC) <- [1.00]
binding parameter (4:VARCHAR) <- [KB-98]
binding parameter (5:VARCHAR) <- [acme]
    8:acme:KB-98 1.00

alter table categories add constraint categories_id_tenant_uk unique (id, tenant_id);
 
alter table products drop constraint products_category_id_fkey;
alter table products add constraint products_category_tenant_fk
    foreign key (category_id, tenant_id) references categories (id, tenant_id);

>>> save with category 3, which belongs to globex, as acme
insert into products (category_id,name,price,sku,tenant_id) values (?,?,?,?,?)
binding parameter (1:BIGINT) <- [3]
binding parameter (2:VARCHAR) <- [Cross-tenant keyboard]
binding parameter (3:NUMERIC) <- [1.00]
binding parameter (4:VARCHAR) <- [KB-98]
binding parameter (5:VARCHAR) <- [acme]
HHH000247: ErrorCode: 0, SQLState: 23503
ERROR: insert or update on table "products" violates foreign key constraint "products_category_tenant_fk"
  Detail: Key (category_id, tenant_id)=(3, acme) is not present in table "categories".
!!! org.springframework.dao.DataIntegrityViolationException: could not execute statement [ERROR: insert or update on table "products" violates foreign key constraint "products_category_tenant_fk" ...
!!! org.hibernate.exception.ConstraintViolationException: could not execute statement [ERROR: insert or update on table "products" violates foreign key constraint "products_category_tenant_fk" ...
!!! org.postgresql.util.PSQLException: ERROR: insert or update on table "products" violates foreign key constraint "products_category_tenant_fk" ...

    @TenantId
    @Column(name = "tenant_id", nullable = false, length = 30) 
    private String tenantId; 

create table categories (
    id   bigint generated by default as identity primary key,
    name varchar(60) not null unique
);
 
create table products (
    id          bigint generated by default as identity primary key,
    name        varchar(120)   not null,
    sku         varchar(40)    not null unique,
    price       numeric(10, 2) not null,
    category_id bigint         not null references categories (id)
);
 
create index products_category_id_idx on products (category_id);

package com.example.demo.tenant;
 
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Map;
 
import javax.sql.DataSource;
 
import org.hibernate.cfg.MultiTenancySettings;
import org.hibernate.engine.jdbc.connections.spi.MultiTenantConnectionProvider;
import org.hibernate.service.UnknownUnwrapTypeException;
 
import org.springframework.boot.hibernate.autoconfigure.HibernatePropertiesCustomizer;
import org.springframework.stereotype.Component;
 
@Component
public class SchemaPerTenantConnectionProvider
        implements MultiTenantConnectionProvider<String>, HibernatePropertiesCustomizer {
 
    private final DataSource dataSource;
 
    public SchemaPerTenantConnectionProvider(DataSource dataSource) {
        this.dataSource = dataSource;
    }
 
    @Override
    public Connection getAnyConnection() throws SQLException {
        return dataSource.getConnection();
    }
 
    @Override
    public void releaseAnyConnection(Connection connection) throws SQLException {
        connection.close();
    }
 
    @Override
    public Connection getConnection(String tenantId) throws SQLException {
        Connection connection = getAnyConnection();
        connection.setSchema(tenantId);
        return connection;
    }
 
    @Override
    public void releaseConnection(String tenantId, Connection connection) throws SQLException {
        connection.setSchema("public");
        releaseAnyConnection(connection);
    }
 
    @Override
    public boolean supportsAggressiveRelease() {
        return false;
    }
 
    @Override
    public boolean isUnwrappableAs(Class<?> unwrapType) {
        return false;
    }
 
    @Override
    public <T> T unwrap(Class<T> unwrapType) {
        throw new UnknownUnwrapTypeException(unwrapType);
    }
 
    @Override
    public void customize(Map<String, Object> hibernateProperties) {
        hibernateProperties.put(MultiTenancySettings.MULTI_TENANT_CONNECTION_PROVIDER, this);
    }
}

2026-09-18 07:25:02.274 UTC [383] LOG:  execute <unnamed>: SET SESSION search_path TO 'globex'
2026-09-18 07:25:02.275 UTC [383] LOG:  execute <unnamed>: select current_schema()

Caused by: org.hibernate.tool.schema.spi.SchemaManagementException: Schema validation: missing table [categories]

spring.jpa.hibernate.ddl-auto=validate 
spring.jpa.hibernate.ddl-auto=none 

>>> findAll() as acme
select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku from products p1_0 order by p1_0.id
    [1:KB-01 89.00, 2:KB-02 59.00, 3:MS-01 24.50, 4:MS-02 49.90] (4 rows)
>>> findAll() as globex
select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku from products p1_0 order by p1_0.id
    [1:KB-01 35.00, 2:MN-01 229.00, 3:MN-02 159.00] (3 rows)
>>> findById(1) as acme
select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku from products p1_0 where p1_0.id=?
binding parameter (1:BIGINT) <- [1]
    Optional[1:KB-01 89.00]
>>> findById(1) as globex
select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku from products p1_0 where p1_0.id=?
binding parameter (1:BIGINT) <- [1]
    Optional[1:KB-01 35.00]
>>> findPricedAtLeastNative(0) inside a transaction as acme (native @Query)
select * from products where price >= ? order by id
binding parameter (1:NUMERIC) <- [0]
    [1:KB-01 89.00, 2:KB-02 59.00, 3:MS-01 24.50, 4:MS-02 49.90] (4 rows)
>>> JdbcClient select inside a transaction as acme
    acme: 4
>>> count() with no tenant
select count(*) from products p1_0
HHH000247: ErrorCode: 0, SQLState: 42P01
ERROR: relation "products" does not exist
  Position: 22
!!! org.springframework.dao.InvalidDataAccessResourceUsageException: JDBC exception executing SQL [ERROR: relation "products" does not exist ...
!!! org.hibernate.exception.SQLGrammarException: JDBC exception executing SQL [ERROR: relation "products" does not exist ...
!!! org.postgresql.util.PSQLException: ERROR: relation "products" does not exist ...

    @Override
    public void releaseConnection(String tenantId, Connection connection) throws SQLException {
        connection.setSchema("public"); 
        releaseAnyConnection(connection);
    }

>>> JdbcClient, before any tenant: select current_schema()
    backend 353, schema public
>>> findAll() as globex, then the backend it ran on
select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku from products p1_0 order by p1_0.id
    [1:KB-01 35.00, 2:MN-01 229.00, 3:MN-02 159.00] on backend 353, schema globex
>>> JdbcClient, no tenant: select current_schema()
    backend 353, schema globex
>>> JdbcClient, no tenant: select count(*) from tenants
!!! org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [select count(*) from tenants]
!!! org.postgresql.util.PSQLException: ERROR: relation "tenants" does not exist ...

@Component
public class TenantSchemaMigrator {
 
    private static final Logger log = LoggerFactory.getLogger(TenantSchemaMigrator.class);
 
    private final DataSource dataSource;
    private final JdbcClient jdbc;
 
    public TenantSchemaMigrator(DataSource dataSource) {
        this.dataSource = dataSource;
        // not the JdbcClient bean: that one waits for Flyway, and this class runs inside Flyway's migration
        this.jdbc = JdbcClient.create(dataSource);
    }
 
    public void migrateAll() {
        jdbc.sql("select id from public.tenants order by id").query(String.class).list()
                .forEach(this::migrate);
    }
 
    public void migrate(String tenantId) {
        MigrateResult result = Flyway.configure()
                .dataSource(dataSource)
                .schemas(tenantId)
                .locations("classpath:db/tenant")
                .load()
                .migrate();
        log.info("Tenant {}: {} migration(s) applied", tenantId, result.migrationsExecuted);
    }
}

@Configuration
class TenantFlywayConfig {
 
    @Bean
    FlywayMigrationStrategy migratePublicThenEveryTenant(TenantSchemaMigrator tenants) {
        return flyway -> {
            flyway.migrate();
            tenants.migrateAll();
        };
    }
}

Migrating schema "public" to version "1 - create tenants"
Successfully applied 1 migration to schema "public", now at version v1 (execution time 00:00.003s)
Creating schema "acme" ...
Creating Schema History table "acme"."flyway_schema_history" ...
Current version of schema "acme": null
Migrating schema "acme" to version "1 - create catalog"
Successfully applied 1 migration to schema "acme", now at version v1 (execution time 00:00.003s)
Tenant acme: 1 migration(s) applied
Creating schema "globex" ...
Creating Schema History table "globex"."flyway_schema_history" ...
Current version of schema "globex": null
Migrating schema "globex" to version "1 - create catalog"
Successfully applied 1 migration to schema "globex", now at version v1 (execution time 00:00.003s)
Tenant globex: 1 migration(s) applied

@RestController
@RequestMapping("/api/admin/tenants")
class TenantAdminController {
 
    record CreateTenantRequest(@NotBlank @Pattern(regexp = "[a-z][a-z0-9_]{1,29}") String id,
                               @NotBlank String name) {
    }
 
    private final JdbcClient jdbc;
    private final TenantSchemaMigrator migrator;
    private final TenantRegistry registry;
 
    TenantAdminController(JdbcClient jdbc, TenantSchemaMigrator migrator, TenantRegistry registry) {
        this.jdbc = jdbc;
        this.migrator = migrator;
        this.registry = registry;
    }
 
    @PostMapping
    ResponseEntity<CreateTenantRequest> create(@Valid @RequestBody CreateTenantRequest request) {
        jdbc.sql("insert into public.tenants (id, name) values (?, ?)")
                .params(request.id(), request.name())
                .update();
        migrator.migrate(request.id());
        registry.refresh();
        return ResponseEntity.created(URI.create("/api/admin/tenants/" + request.id())).body(request);
    }
}

curl -s -H 'X-Tenant-Id: initech' http://localhost:8212/api/products
curl -i -s -H 'Content-Type: application/json' -d '{"id":"initech","name":"Initech"}' http://localhost:8212/api/admin/tenants
curl -i -s -H 'X-Tenant-Id: initech' http://localhost:8212/api/products

{"detail":"Unknown tenant 'initech'.","instance":"/api/products","status":400,"title":"Invalid tenant"}

HTTP/1.1 201 
Location: /api/admin/tenants/initech
Content-Type: application/json
Transfer-Encoding: chunked
Date: Fri, 18 Sep 2026 07:26:00 GMT
 
{"id":"initech","name":"Initech"}

HTTP/1.1 200 
Content-Type: application/json
Content-Length: 2
Date: Fri, 18 Sep 2026 07:26:00 GMT
 
[]

2026-09-18T14:26:00.043+07:00  INFO 19683 --- [demo] [nio-8212-exec-2] o.f.core.internal.database.base.Schema   : Creating schema "initech" ...
2026-09-18T14:26:00.065+07:00  INFO 19683 --- [demo] [nio-8212-exec-2] o.f.core.internal.command.DbMigrate      : Migrating schema "initech" to version "1 - create catalog"
2026-09-18T14:26:00.073+07:00  INFO 19683 --- [demo] [nio-8212-exec-2] c.e.demo.tenant.TenantSchemaMigrator     : Tenant initech: 1 migration(s) applied

@Component
public class TenantSchemas implements TenantSchemaMapper<String>, HibernatePropertiesCustomizer {
 
    @Override
    public String schemaName(String tenantId) {
        return tenantId;
    }
 
    @Override
    public void customize(Map<String, Object> hibernateProperties) {
        hibernateProperties.put(MultiTenancySettings.MULTI_TENANT_SCHEMA_MAPPER, this);
    }
}

>>> JdbcClient, before any tenant: select current_schema()
    backend 426, schema public
>>> findAll() as globex, then the backend it ran on
select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku from products p1_0 order by p1_0.id
    [1:KB-01 35.00, 2:MN-01 229.00, 3:MN-02 159.00] on backend 426, schema globex
>>> JdbcClient, no tenant: select current_schema()
    backend 426, schema public
>>> JdbcClient, no tenant: select count(*) from tenants
    2

@Component
public class DatabasePerTenantConnectionProvider
        extends AbstractDataSourceBasedMultiTenantConnectionProviderImpl<String>
        implements HibernatePropertiesCustomizer, DisposableBean {
 
    private final DataSource controlDataSource;
    private final TenantRegistry registry;
    private final String urlTemplate;
    private final String username;
    private final String password;
    private final Map<String, HikariDataSource> pools = new ConcurrentHashMap<>();
 
    public DatabasePerTenantConnectionProvider(DataSource controlDataSource, TenantRegistry registry,
                                               @Value("${app.tenants.url-template}") String urlTemplate,
                                               @Value("${spring.datasource.username}") String username,
                                               @Value("${spring.datasource.password}") String password) {
        this.controlDataSource = controlDataSource;
        this.registry = registry;
        this.urlTemplate = urlTemplate;
        this.username = username;
        this.password = password;
    }
 
    @Override
    protected DataSource selectAnyDataSource() {
        return controlDataSource;
    }
 
    @Override
    protected DataSource selectDataSource(String tenantId) {
        if (!registry.exists(tenantId)) {
            throw new IllegalStateException("No database for tenant '" + tenantId + "'");
        }
        return pools.computeIfAbsent(tenantId, this::createPool);
    }
 
    private HikariDataSource createPool(String tenantId) {
        HikariDataSource pool = new HikariDataSource();
        pool.setPoolName("tenant-" + tenantId);
        pool.setJdbcUrl(urlTemplate.formatted(tenantId));
        pool.setUsername(username);
        pool.setPassword(password);
        Flyway.configure().dataSource(pool).locations("classpath:db/tenant").load().migrate();
        return pool;
    }
 
    @Override
    public void customize(Map<String, Object> hibernateProperties) {
        hibernateProperties.put(MultiTenancySettings.MULTI_TENANT_CONNECTION_PROVIDER, this);
    }
 
    @Override
    public void destroy() {
        pools.values().forEach(HikariDataSource::close);
    }
}

>>> findAll() as acme
tenant-acme - Starting...
tenant-acme - Added connection org.postgresql.jdbc.PgConnection@6eb06667
tenant-acme - Start completed.
Database: jdbc:postgresql://localhost:5512/tenant_acme (PostgreSQL 18.6)
Successfully validated 1 migration (execution time 00:00.002s)
Current version of schema "public": 1
Schema "public" is up to date. No migration necessary.
select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku from products p1_0 order by p1_0.id
    [1:KB-01 89.00, 2:KB-02 59.00, 3:MS-01 24.50, 4:MS-02 49.90] (4 rows)
>>> connections per database, from pg_stat_activity
    [dbcontrol=10, tenant_acme=10, tenant_globex=10] (3 rows)
>>> total client connections
    30

>>> connections per database, from pg_stat_activity
    [dbcontrol=10, tenant_acme=10, tenant_globex=10, tenant_t01=10, tenant_t02=10, tenant_t03=10, tenant_t04=10, tenant_t05=10, tenant_t06=8, tenant_t07=7, tenant_t08=5] (11 rows)
>>> total client connections
    100

2026-09-18 07:27:56.408 UTC [697] FATAL:  sorry, too many clients already

docker exec sba-a12-pg psql -U demo -d demo -c "create role catalog_app login password 'catalog_app'"

grant select, insert, update, delete on tenants, categories, products to catalog_app;
 
alter table categories enable row level security;
alter table products enable row level security;
 
create policy tenant_isolation on categories
    using (tenant_id = current_setting('app.tenant_id', true));
 
create policy tenant_isolation on products
    using (tenant_id = current_setting('app.tenant_id', true));

before any set: NULL
BEGIN
inside, is_local=true: acme
COMMIT
after commit: ''
is_local=false: acme
next statement: acme

package com.example.demo.tenant;
 
import java.sql.PreparedStatement;
import java.util.Objects;
 
import jakarta.persistence.EntityManagerFactory;
 
import org.hibernate.Session;
 
import org.springframework.orm.jpa.EntityManagerHolder;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.support.TransactionSynchronizationManager;
 
public class TenantAwareTransactionManager extends JpaTransactionManager {
 
    public TenantAwareTransactionManager(EntityManagerFactory emf) {
        super(emf);
    }
 
    @Override
    protected void doBegin(Object transaction, TransactionDefinition definition) {
        super.doBegin(transaction, definition);
        String tenantId = Objects.requireNonNullElse(TenantContext.get(), "");
        EntityManagerHolder holder =
                (EntityManagerHolder) TransactionSynchronizationManager.getResource(obtainEntityManagerFactory());
        holder.getEntityManager().unwrap(Session.class).doWork(connection -> {
            try (PreparedStatement statement =
                         connection.prepareStatement("select set_config('app.tenant_id', ?, true)")) {
                statement.setString(1, tenantId);
                statement.execute();
            }
        });
    }
}

@Configuration
@Profile("rls")
class RowLevelSecurityConfig {
 
    @Bean
    JpaTransactionManager transactionManager(EntityManagerFactory emf) {
        return new TenantAwareTransactionManager(emf);
    }
}

spring.datasource.username=catalog_app
spring.datasource.password=catalog_app
spring.flyway.user=demo
spring.flyway.password=demo

>>> current_user
    catalog_app
>>> findPricedAtLeastNative(0) as acme (native @Query)
select * from products where price >= ? order by id
binding parameter (1:NUMERIC) <- [0]
    [] (0 rows)
>>> findPricedAtLeastNative(0) inside a transaction as acme
select * from products where price >= ? order by id
binding parameter (1:NUMERIC) <- [0]
    [1:acme:KB-01 89.00, 2:acme:KB-02 59.00, 3:acme:MS-01 24.50, 4:acme:MS-02 49.90] (4 rows)
>>> findByPriceLessThan(50) as acme (derived)
select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku,p1_0.tenant_id from products p1_0 where p1_0.tenant_id = ? and p1_0.price<?
binding parameter (1:VARCHAR) <- [acme]
binding parameter (2:NUMERIC) <- [50]
    [] (0 rows)
>>> findAll() as acme
select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku,p1_0.tenant_id from products p1_0 where p1_0.tenant_id = ? order by p1_0.id
binding parameter (1:VARCHAR) <- [acme]
    [1:acme:KB-01 89.00, 2:acme:KB-02 59.00, 3:acme:MS-01 24.50, 4:acme:MS-02 49.90] (4 rows)
>>> JdbcClient select inside a transaction as acme
    [1:acme:KB-01, 2:acme:KB-02, 3:acme:MS-01, 4:acme:MS-02] (4 rows)
>>> JdbcClient select outside a transaction as acme
    [] (0 rows)
>>> current_setting('app.tenant_id', true) outside a transaction
    ''
>>> JdbcClient insert of a globex row inside a transaction as acme
!!! org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [insert into products (tenant_id, name, sku, price, category_id) values ('globex', 'Planted keyboard', 'KB-97', 1.00, 3)]
!!! org.postgresql.util.PSQLException: ERROR: new row violates row-level security policy for table "products"

>>> current_user
    demo
>>> findPricedAtLeastNative(0) inside a transaction as acme
    [1:acme:KB-01 89.00, 2:acme:KB-02 59.00, 3:acme:MS-01 24.50, 4:acme:MS-02 49.90, 5:globex:KB-01 35.00, 6:globex:MN-01 229.00, 7:globex:MN-02 159.00] (7 rows)
>>> JdbcClient select outside a transaction as acme
    [1:acme:KB-01, 2:acme:KB-02, 3:acme:MS-01, 4:acme:MS-02, 5:globex:KB-01, 6:globex:MN-01, 7:globex:MN-02] (7 rows)
>>> JdbcClient insert of a globex row inside a transaction as acme
    1

select rolname, rolsuper, rolbypassrls from pg_roles where rolname in ('demo', 'catalog_app') order by rolname;
begin;
select set_config('app.tenant_id', 'acme', true);
select current_user, count(*) from products;
alter table products force row level security;
select current_user, count(*) from products;
create role catalog_owner;
alter table products owner to catalog_owner;
set local role catalog_owner;
select current_user, count(*) from products;
reset role;
alter table products no force row level security;
set local role catalog_owner;
select current_user, count(*) from products;
rollback;

   rolname   | rolsuper | rolbypassrls 
-------------+----------+--------------
 catalog_app | f        | f
 demo        | t        | t
 
 current_user | count 
--------------+-------
 demo         |     7
 
 current_user | count 
--------------+-------
 demo         |     7
 
 current_user  | count 
---------------+-------
 catalog_owner |     4
 
 current_user  | count 
---------------+-------
 catalog_owner |     7

import org.hibernate.annotations.SoftDelete; 
import org.hibernate.annotations.TenantId;
 
@Entity
@Table(name = "products")
@SoftDelete
public class Product {
 
    // id, tenantId, name, sku and price unchanged
 
    @ManyToOne(fetch = FetchType.LAZY, optional = false) 
    @ManyToOne(optional = false) 
    @JoinColumn(name = "category_id", nullable = false)
    private Category category;

@Entity
@Table(name = "categories")
@SoftDelete
public class Category {
 
    // id, tenantId and name unchanged
 
    @OneToMany(mappedBy = "category") 
    @OrderBy("id") 
    private List<Product> products = new ArrayList<>(); 

Caused by: org.hibernate.metamodel.UnsupportedMappingException: To-one attribute (com.example.demo.product.Product.category) cannot be mapped as LAZY as its associated entity is defined with @SoftDelete

>>> findById(3) as acme
select p1_0.id,p1_0.category_id,c1_0.id,c1_0.name,c1_0.tenant_id,p1_0.name,p1_0.price,p1_0.sku,p1_0.tenant_id from products p1_0 join categories c1_0 on c1_0.id=p1_0.category_id and c1_0.tenant_id = ? and c1_0.deleted=false where p1_0.deleted=false and p1_0.id=? and p1_0.tenant_id = ?
binding parameter (1:VARCHAR) <- [acme]
binding parameter (2:BIGINT) <- [3]
binding parameter (3:VARCHAR) <- [acme]
    Optional[3:acme:MS-01 24.50]
>>> delete product 3 (MS-01) as acme
select p1_0.id,p1_0.category_id,c1_0.id,c1_0.name,c1_0.tenant_id,p1_0.name,p1_0.price,p1_0.sku,p1_0.tenant_id from products p1_0 join categories c1_0 on c1_0.id=p1_0.category_id and c1_0.tenant_id = ? and c1_0.deleted=false where p1_0.deleted=false and p1_0.id=? and p1_0.tenant_id = ?
binding parameter (1:VARCHAR) <- [acme]
binding parameter (2:BIGINT) <- [3]
binding parameter (3:VARCHAR) <- [acme]
update products set deleted=true where id=? and deleted=false
binding parameter (1:BIGINT) <- [3]
    deleted
>>> findById(3) as acme
select p1_0.id,p1_0.category_id,c1_0.id,c1_0.name,c1_0.tenant_id,p1_0.name,p1_0.price,p1_0.sku,p1_0.tenant_id from products p1_0 join categories c1_0 on c1_0.id=p1_0.category_id and c1_0.tenant_id = ? and c1_0.deleted=false where p1_0.deleted=false and p1_0.id=? and p1_0.tenant_id = ?
binding parameter (1:VARCHAR) <- [acme]
binding parameter (2:BIGINT) <- [3]
binding parameter (3:VARCHAR) <- [acme]
    Optional.empty
>>> findAll() as acme
select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku,p1_0.tenant_id from products p1_0 where p1_0.tenant_id = ? and p1_0.deleted=false order by p1_0.id
binding parameter (1:VARCHAR) <- [acme]
select c1_0.id,c1_0.name,c1_0.tenant_id from categories c1_0 where c1_0.deleted=false and c1_0.id=? and c1_0.tenant_id = ?
binding parameter (1:BIGINT) <- [1]
binding parameter (2:VARCHAR) <- [acme]
select c1_0.id,c1_0.name,c1_0.tenant_id from categories c1_0 where c1_0.deleted=false and c1_0.id=? and c1_0.tenant_id = ?
binding parameter (1:BIGINT) <- [2]
binding parameter (2:VARCHAR) <- [acme]
    [1:acme:KB-01 89.00, 2:acme:KB-02 59.00, 4:acme:MS-02 49.90] (3 rows)
>>> findInCategory("Mice") as acme (JPQL)
select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku,p1_0.tenant_id from products p1_0 join categories c1_0 on c1_0.id=p1_0.category_id and c1_0.tenant_id = ? and c1_0.deleted=false where p1_0.tenant_id = ? and c1_0.name=? and p1_0.deleted=false order by p1_0.price
binding parameter (1:VARCHAR) <- [acme]
binding parameter (2:VARCHAR) <- [acme]
binding parameter (3:VARCHAR) <- [Mice]
select c1_0.id,c1_0.name,c1_0.tenant_id from categories c1_0 where c1_0.deleted=false and c1_0.id=? and c1_0.tenant_id = ?
binding parameter (1:BIGINT) <- [2]
binding parameter (2:VARCHAR) <- [acme]
    [4:acme:MS-02 49.90] (1 rows)
>>> category 2 and its products collection as acme
select c1_0.id,c1_0.name,c1_0.tenant_id from categories c1_0 where c1_0.deleted=false and c1_0.id=? and c1_0.tenant_id = ?
binding parameter (1:BIGINT) <- [2]
binding parameter (2:VARCHAR) <- [acme]
select p1_0.category_id,p1_0.id,p1_0.name,p1_0.price,p1_0.sku,p1_0.tenant_id from products p1_0 where p1_0.category_id=? and p1_0.deleted=false order by p1_0.id
binding parameter (1:BIGINT) <- [2]
    2:acme:Mice -> [4:acme:MS-02 49.90]
>>> deleteInBulkBySku("MS-02") as acme (bulk JPQL delete)
update products p1_0 set deleted=true where p1_0.sku=? and p1_0.tenant_id = ? and p1_0.deleted=false
binding parameter (1:VARCHAR) <- [MS-02]
binding parameter (2:VARCHAR) <- [acme]
    1

select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku,p1_0.tenant_id
from products p1_0
join categories c1_0 on c1_0.id=p1_0.category_id and c1_0.tenant_id = ? and c1_0.deleted=false
where p1_0.tenant_id = ? and c1_0.name=? and p1_0.deleted=false
order by p1_0.price

>>> delete category 1 (Keyboards) as acme
select c1_0.id,c1_0.name,c1_0.tenant_id from categories c1_0 where c1_0.deleted=false and c1_0.id=? and c1_0.tenant_id = ?
binding parameter (1:BIGINT) <- [1]
binding parameter (2:VARCHAR) <- [acme]
update categories set deleted=true where id=? and deleted=false
binding parameter (1:BIGINT) <- [1]
    deleted
>>> findById(1) as acme, a product in the deleted category
select p1_0.id,p1_0.category_id,c1_0.id,c1_0.name,c1_0.tenant_id,p1_0.name,p1_0.price,p1_0.sku,p1_0.tenant_id from products p1_0 join categories c1_0 on c1_0.id=p1_0.category_id and c1_0.tenant_id = ? and c1_0.deleted=false where p1_0.deleted=false and p1_0.id=? and p1_0.tenant_id = ?
binding parameter (1:VARCHAR) <- [acme]
binding parameter (2:BIGINT) <- [1]
binding parameter (3:VARCHAR) <- [acme]
    Optional.empty
>>> findAll() as acme
select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku,p1_0.tenant_id from products p1_0 where p1_0.tenant_id = ? and p1_0.deleted=false order by p1_0.id
binding parameter (1:VARCHAR) <- [acme]
select c1_0.id,c1_0.name,c1_0.tenant_id from categories c1_0 where c1_0.deleted=false and c1_0.id=? and c1_0.tenant_id = ?
binding parameter (1:BIGINT) <- [1]
binding parameter (2:VARCHAR) <- [acme]
!!! org.springframework.orm.ObjectRetrievalFailureException: No row with the given identifier exists for entity [com.example.demo.product.Category with id '1']
!!! org.hibernate.ObjectNotFoundException: No row with the given identifier exists for entity [com.example.demo.product.Category with id '1']
>>> count() as acme
select count(*) from products p1_0 where p1_0.tenant_id = ? and p1_0.deleted=false
binding parameter (1:VARCHAR) <- [acme]
    4

>>> findAllNative("acme") as acme (native @Query)
select * from products where tenant_id = ? order by id
binding parameter (1:VARCHAR) <- [acme]
select c1_0.id,c1_0.name,c1_0.tenant_id from categories c1_0 where c1_0.deleted=false and c1_0.id=? and c1_0.tenant_id = ?
binding parameter (1:BIGINT) <- [1]
binding parameter (2:VARCHAR) <- [acme]
select c1_0.id,c1_0.name,c1_0.tenant_id from categories c1_0 where c1_0.deleted=false and c1_0.id=? and c1_0.tenant_id = ?
binding parameter (1:BIGINT) <- [2]
binding parameter (2:VARCHAR) <- [acme]
    [1:acme:KB-01 89.00, 2:acme:KB-02 59.00, 3:acme:MS-01 24.50, 4:acme:MS-02 49.90] (4 rows)
>>> findPricedAtLeastNative(0) as acme (native @Query, every tenant)
select * from products where price >= ? order by id
binding parameter (1:NUMERIC) <- [0]
select c1_0.id,c1_0.name,c1_0.tenant_id from categories c1_0 where c1_0.deleted=false and c1_0.id=? and c1_0.tenant_id = ?
binding parameter (1:BIGINT) <- [1]
binding parameter (2:VARCHAR) <- [acme]
select c1_0.id,c1_0.name,c1_0.tenant_id from categories c1_0 where c1_0.deleted=false and c1_0.id=? and c1_0.tenant_id = ?
binding parameter (1:BIGINT) <- [2]
binding parameter (2:VARCHAR) <- [acme]
select c1_0.id,c1_0.name,c1_0.tenant_id from categories c1_0 where c1_0.deleted=false and c1_0.id=? and c1_0.tenant_id = ?
binding parameter (1:BIGINT) <- [3]
binding parameter (2:VARCHAR) <- [acme]
!!! org.springframework.orm.ObjectRetrievalFailureException: No row with the given identifier exists for entity [com.example.demo.product.Category with id '3']
!!! org.hibernate.ObjectNotFoundException: No row with the given identifier exists for entity [com.example.demo.product.Category with id '3']
>>> JdbcClient: acme rows with their deleted flag
    [1:KB-01 deleted=false, 2:KB-02 deleted=false, 3:MS-01 deleted=true, 4:MS-02 deleted=true] (4 rows)

@Entity
@Table(name = "products")
@SQLDelete(sql = "update products set deleted = true where id = ?")
@SQLRestriction("deleted = false")
public class Product {
 
    // id, tenantId, name, sku and price as before
 
    @ManyToOne(fetch = FetchType.LAZY, optional = false)
    @JoinColumn(name = "category_id", nullable = false)
    private Category category;
 
    private boolean deleted;

>>> findById(3) as acme
select p1_0.id,p1_0.category_id,p1_0.deleted,p1_0.name,p1_0.price,p1_0.sku,p1_0.tenant_id from products p1_0 where p1_0.id=? and p1_0.tenant_id = ? and (p1_0.deleted = false)
binding parameter (1:BIGINT) <- [3]
binding parameter (2:VARCHAR) <- [acme]
    Optional[3:acme:MS-01 24.50]
>>> delete product 3 (MS-01) as acme
select p1_0.id,p1_0.category_id,p1_0.deleted,p1_0.name,p1_0.price,p1_0.sku,p1_0.tenant_id from products p1_0 where p1_0.id=? and p1_0.tenant_id = ? and (p1_0.deleted = false)
binding parameter (1:BIGINT) <- [3]
binding parameter (2:VARCHAR) <- [acme]
update products set deleted = true where id = ?
binding parameter (1:BIGINT) <- [3]
    deleted
>>> findAll() as acme
select p1_0.id,p1_0.category_id,p1_0.deleted,p1_0.name,p1_0.price,p1_0.sku,p1_0.tenant_id from products p1_0 where p1_0.tenant_id = ? and (p1_0.deleted = false) order by p1_0.id
binding parameter (1:VARCHAR) <- [acme]
    [1:acme:KB-01 89.00, 2:acme:KB-02 59.00, 4:acme:MS-02 49.90] (3 rows)
>>> findInCategory("Mice") as acme (JPQL)
select p1_0.id,p1_0.category_id,p1_0.deleted,p1_0.name,p1_0.price,p1_0.sku,p1_0.tenant_id from products p1_0 join categories c1_0 on c1_0.id=p1_0.category_id and c1_0.tenant_id = ? and (c1_0.deleted = false) and (c1_0.deleted = false) where p1_0.tenant_id = ? and (p1_0.deleted = false) and c1_0.name=? order by p1_0.price
binding parameter (1:VARCHAR) <- [acme]
binding parameter (2:VARCHAR) <- [acme]
binding parameter (3:VARCHAR) <- [Mice]
    [4:acme:MS-02 49.90] (1 rows)
>>> category 2 and its products collection as acme
select c1_0.id,c1_0.name,c1_0.tenant_id from categories c1_0 where c1_0.id=? and c1_0.tenant_id = ? and (c1_0.deleted = false)
binding parameter (1:BIGINT) <- [2]
binding parameter (2:VARCHAR) <- [acme]
select p1_0.category_id,p1_0.id,p1_0.deleted,p1_0.name,p1_0.price,p1_0.sku,p1_0.tenant_id from products p1_0 where p1_0.category_id=? and (p1_0.deleted = false) order by p1_0.id
binding parameter (1:BIGINT) <- [2]
    2:acme:Mice -> [4:acme:MS-02 49.90]
>>> JPQL: select p from Product p where p.deleted = true
select p1_0.id,p1_0.category_id,p1_0.deleted,p1_0.name,p1_0.price,p1_0.sku,p1_0.tenant_id from products p1_0 where p1_0.tenant_id = ? and (p1_0.deleted = false) and p1_0.deleted=true
binding parameter (1:VARCHAR) <- [acme]
    [] (0 rows)
>>> deleteInBulkBySku("MS-02") as acme (bulk JPQL delete)
delete from products p1_0 where p1_0.sku=? and p1_0.tenant_id = ? and (p1_0.deleted = false)
binding parameter (1:VARCHAR) <- [MS-02]
binding parameter (2:VARCHAR) <- [acme]
    1
>>> JdbcClient: acme rows with their deleted flag
    [1:KB-01 deleted=false, 2:KB-02 deleted=false, 3:MS-01 deleted=true] (3 rows)

>>> findById(1) as acme, then its category name
select p1_0.id,p1_0.category_id,p1_0.deleted,p1_0.name,p1_0.price,p1_0.sku,p1_0.tenant_id from products p1_0 where p1_0.id=? and p1_0.tenant_id = ? and (p1_0.deleted = false)
binding parameter (1:BIGINT) <- [1]
binding parameter (2:VARCHAR) <- [acme]
select c1_0.id,c1_0.name,c1_0.tenant_id from categories c1_0 where c1_0.id=? and c1_0.tenant_id = ? and (c1_0.deleted = false)
binding parameter (1:BIGINT) <- [1]
binding parameter (2:VARCHAR) <- [acme]
!!! jakarta.persistence.EntityNotFoundException: No row with the given identifier exists for entity [com.example.demo.product.Category with id '1']
!!! org.hibernate.ObjectNotFoundException: No row with the given identifier exists for entity [com.example.demo.product.Category with id '1']
>>> findAll() as acme
select p1_0.id,p1_0.category_id,p1_0.deleted,p1_0.name,p1_0.price,p1_0.sku,p1_0.tenant_id from products p1_0 where p1_0.tenant_id = ? and (p1_0.deleted = false) order by p1_0.id
binding parameter (1:VARCHAR) <- [acme]
    [1:acme:KB-01 89.00, 2:acme:KB-02 59.00, 3:acme:MS-01 24.50, 4:acme:MS-02 49.90] (4 rows)

>>> delete product 2 (KB-02) as acme
update products set deleted=true where id=? and deleted=false
    deleted
>>> save(new Product("Compact keyboard v2", "KB-02", 64.00, Keyboards)) as acme
insert into products (category_id,name,price,sku,tenant_id,deleted) values (?,?,?,?,?,false)
HHH000247: ErrorCode: 0, SQLState: 23505
ERROR: duplicate key value violates unique constraint "products_tenant_sku_uk"
  Detail: Key (tenant_id, sku)=(acme, KB-02) already exists.
!!! org.springframework.dao.DataIntegrityViolationException: could not execute statement [ERROR: duplicate key value violates unique constraint "products_tenant_sku_uk" ...
!!! org.hibernate.exception.ConstraintViolationException: could not execute statement [ERROR: duplicate key value violates unique constraint "products_tenant_sku_uk" ...
!!! org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "products_tenant_sku_uk" ...
>>> JdbcClient: acme rows with sku KB-02
    [2:KB-02 59.00 deleted=true] (1 rows)

alter table products drop constraint products_tenant_sku_uk;
 
create unique index products_tenant_sku_live_uk on products (tenant_id, sku) where not deleted;

>>> save(new Product("Compact keyboard v2", "KB-02", 64.00, Keyboards)) as acme
insert into products (category_id,name,price,sku,tenant_id,deleted) values (?,?,?,?,?,false)
binding parameter (1:BIGINT) <- [1]
binding parameter (2:VARCHAR) <- [Compact keyboard v2]
binding parameter (3:NUMERIC) <- [64.00]
binding parameter (4:VARCHAR) <- [KB-02]
binding parameter (5:VARCHAR) <- [acme]
    8:acme:KB-02 64.00
>>> JdbcClient: acme rows with sku KB-02
    [2:KB-02 59.00 deleted=true, 8:KB-02 64.00 deleted=false] (2 rows)

create temp table skus_plain (tenant_id text, sku text, deleted timestamp(6), unique (tenant_id, sku, deleted));
insert into skus_plain values ('acme', 'KB-02', null);
insert into skus_plain values ('acme', 'KB-02', null);
select count(*) as live_kb02_rows from skus_plain where deleted is null;
create temp table skus_nnd (tenant_id text, sku text, deleted timestamp(6), unique nulls not distinct (tenant_id, sku, deleted));
insert into skus_nnd values ('acme', 'KB-02', localtimestamp - interval '1 day');
insert into skus_nnd values ('acme', 'KB-02', localtimestamp);
insert into skus_nnd values ('acme', 'KB-02', null);
insert into skus_nnd values ('acme', 'KB-02', null);
create temp table skus_partial (tenant_id text, sku text, deleted timestamp(6));
create unique index skus_partial_live_uk on skus_partial (tenant_id, sku) where deleted is null;
insert into skus_partial values ('acme', 'KB-02', localtimestamp - interval '1 day');
insert into skus_partial values ('acme', 'KB-02', localtimestamp);
insert into skus_partial values ('acme', 'KB-02', null);
insert into skus_partial values ('acme', 'KB-02', null);

CREATE TABLE
INSERT 0 1
INSERT 0 1
 live_kb02_rows 
----------------
              2
(1 row)
 
CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1
ERROR:  duplicate key value violates unique constraint "skus_nnd_tenant_id_sku_deleted_key"
DETAIL:  Key (tenant_id, sku, deleted)=(acme, KB-02, null) already exists.
CREATE TABLE
CREATE INDEX
INSERT 0 1
INSERT 0 1
INSERT 0 1
ERROR:  duplicate key value violates unique constraint "skus_partial_live_uk"
DETAIL:  Key (tenant_id, sku)=(acme, KB-02) already exists.

>>> JPQL: select p from Product p where p.deleted = true
!!! java.lang.IllegalArgumentException: org.hibernate.query.sqm.UnknownPathException: Could not resolve attribute 'deleted' of 'com.example.demo.product.Product' [select p from Product p where p.deleted = true]
!!! org.hibernate.query.sqm.UnknownPathException: Could not resolve attribute 'deleted' of 'com.example.demo.product.Product'
!!! org.hibernate.query.sqm.PathElementException: Could not resolve attribute 'deleted' of 'com.example.demo.product.Product'

// read-only view of every row, deleted or not, still scoped to the current tenant
@Entity
@Immutable
@Table(name = "products")
public class ProductRecord {
 
    @Id
    private Long id;
 
    @TenantId
    @Column(name = "tenant_id")
    private String tenantId;
 
    private String sku;
 
    private BigDecimal price;
 
    private boolean deleted;
 
    // getters
}

public interface ProductRecordRepository extends JpaRepository<ProductRecord, Long> {
 
    List<ProductRecord> findByDeletedTrueOrderById();
 
    @Modifying
    @Query(value = "update products set deleted = false where id = :id and tenant_id = :tenantId", nativeQuery = true)
    int restore(Long id, String tenantId);
}

>>> findByDeletedTrueOrderById() on ProductRecord as acme
select pr1_0.id,pr1_0.deleted,pr1_0.price,pr1_0.sku,pr1_0.tenant_id from products pr1_0 where pr1_0.tenant_id = ? and pr1_0.deleted=true order by pr1_0.id
binding parameter (1:VARCHAR) <- [acme]
    [3:acme:MS-01 (deleted)] (1 rows)
>>> restore(3, "acme") as acme (native update)
update products set deleted = false where id = ? and tenant_id = ?
binding parameter (1:BIGINT) <- [3]
binding parameter (2:VARCHAR) <- [acme]
    1
>>> findById(3) as acme
select p1_0.id,p1_0.category_id,c1_0.id,c1_0.name,c1_0.tenant_id,p1_0.name,p1_0.price,p1_0.sku,p1_0.tenant_id from products p1_0 join categories c1_0 on c1_0.id=p1_0.category_id and c1_0.tenant_id = ? and c1_0.deleted=false where p1_0.deleted=false and p1_0.id=? and p1_0.tenant_id = ?
binding parameter (1:VARCHAR) <- [acme]
binding parameter (2:BIGINT) <- [3]
binding parameter (3:VARCHAR) <- [acme]
    Optional[3:acme:MS-01 24.50]

>>> restore(2, "acme") while a live KB-02 exists
update products set deleted = false where id = ? and tenant_id = ?
binding parameter (1:BIGINT) <- [2]
binding parameter (2:VARCHAR) <- [acme]
HHH000247: ErrorCode: 0, SQLState: 23505
ERROR: duplicate key value violates unique constraint "products_tenant_sku_live_uk"
  Detail: Key (tenant_id, sku)=(acme, KB-02) already exists.