Case lifecycle changes.
Assigned user changes.
Evidence is attached.
Status transition emits a domain event.
The event must not be lost.
Concurrent reviewers must not overwrite each other.
Read screens must not trigger N+1.
Soft-deleted records must not leak into active views.
Audit trail must be defensible.
Tests must use a real database.

1. Implement the module once naïvely.
2. Observe what breaks.
3. Add constraints.
4. Add transaction boundaries.
5. Add query boundaries.
6. Add locking.
7. Add outbox.
8. Add tests that fail without those protections.
9. Add metrics and review checklists.

com.example.enforcement.casepersistence
├── api
│   ├── CaseCommandController.java
│   └── CaseQueryController.java
├── application
│   ├── AssignCaseHandler.java
│   ├── ChangeCaseStatusHandler.java
│   ├── AddEvidenceHandler.java
│   └── CaseApplicationEvents.java
├── domain
│   ├── EnforcementCase.java
│   ├── CaseStatus.java
│   ├── CasePriority.java
│   ├── CaseAssignment.java
│   ├── Evidence.java
│   ├── EvidenceType.java
│   ├── CaseNote.java
│   ├── CaseTransition.java
│   └── DomainViolation.java
├── persistence
│   ├── CaseRepository.java
│   ├── CaseJpaRepository.java
│   ├── CaseReadRepository.java
│   ├── OutboxJpaRepository.java
│   └── mappings.md
├── outbox
│   ├── OutboxEvent.java
│   ├── OutboxEventRepository.java
│   ├── OutboxRelay.java
│   └── OutboxEventType.java
├── readmodel
│   ├── CaseSummaryView.java
│   ├── CaseDetailView.java
│   └── AssignedCaseRow.java
└── support
    ├── TenantContext.java
    ├── CurrentActor.java
    ├── PersistenceDiagnostics.java
    └── TransactionalGuards.java

entity/
repository/
service/
controller/

What business boundary does this code protect?

DRAFT -> OPEN -> IN_REVIEW -> ESCALATED -> RESOLVED -> CLOSED

CREATE TABLE enforcement_case (
    id                  UUID PRIMARY KEY,
    tenant_id           VARCHAR(64) NOT NULL,
    case_number         VARCHAR(64) NOT NULL,
    title               VARCHAR(300) NOT NULL,
    status              VARCHAR(32) NOT NULL,
    priority            VARCHAR(32) NOT NULL,
    assigned_actor_id   VARCHAR(128),
    opened_at           TIMESTAMPTZ,
    resolved_at         TIMESTAMPTZ,
    closed_at           TIMESTAMPTZ,
    created_at          TIMESTAMPTZ NOT NULL,
    created_by          VARCHAR(128) NOT NULL,
    updated_at          TIMESTAMPTZ NOT NULL,
    updated_by          VARCHAR(128) NOT NULL,
    version             BIGINT NOT NULL,
    CONSTRAINT uq_case_tenant_case_number UNIQUE (tenant_id, case_number),
    CONSTRAINT chk_case_status CHECK (status IN ('DRAFT', 'OPEN', 'IN_REVIEW', 'ESCALATED', 'RESOLVED', 'CLOSED')),
    CONSTRAINT chk_case_priority CHECK (priority IN ('LOW', 'MEDIUM', 'HIGH', 'CRITICAL'))
);

CREATE INDEX idx_case_tenant_status_priority
    ON enforcement_case (tenant_id, status, priority, updated_at DESC);

CREATE INDEX idx_case_tenant_assigned_status
    ON enforcement_case (tenant_id, assigned_actor_id, status, updated_at DESC);

CREATE TABLE case_evidence (
    id                  UUID PRIMARY KEY,
    tenant_id           VARCHAR(64) NOT NULL,
    case_id             UUID NOT NULL,
    evidence_type       VARCHAR(32) NOT NULL,
    external_reference  VARCHAR(256) NOT NULL,
    display_name        VARCHAR(300) NOT NULL,
    content_hash        VARCHAR(128),
    deleted             BOOLEAN NOT NULL DEFAULT FALSE,
    deleted_at          TIMESTAMPTZ,
    deleted_by          VARCHAR(128),
    created_at          TIMESTAMPTZ NOT NULL,
    created_by          VARCHAR(128) NOT NULL,
    version             BIGINT NOT NULL,
    CONSTRAINT fk_evidence_case FOREIGN KEY (case_id) REFERENCES enforcement_case(id),
    CONSTRAINT uq_evidence_case_ref UNIQUE (tenant_id, case_id, external_reference),
    CONSTRAINT chk_evidence_type CHECK (evidence_type IN ('DOCUMENT', 'IMAGE', 'VIDEO', 'SYSTEM_RECORD'))
);

CREATE INDEX idx_evidence_case_active
    ON case_evidence (tenant_id, case_id, deleted);

CREATE TABLE case_transition (
    id                  UUID PRIMARY KEY,
    tenant_id           VARCHAR(64) NOT NULL,
    case_id             UUID NOT NULL,
    from_status         VARCHAR(32),
    to_status           VARCHAR(32) NOT NULL,
    reason              VARCHAR(1000),
    actor_id            VARCHAR(128) NOT NULL,
    occurred_at         TIMESTAMPTZ NOT NULL,
    CONSTRAINT fk_transition_case FOREIGN KEY (case_id) REFERENCES enforcement_case(id)
);

CREATE INDEX idx_transition_case_time
    ON case_transition (tenant_id, case_id, occurred_at DESC);

CREATE TABLE outbox_event (
    id                  UUID PRIMARY KEY,
    tenant_id           VARCHAR(64) NOT NULL,
    aggregate_type      VARCHAR(100) NOT NULL,
    aggregate_id        UUID NOT NULL,
    event_type          VARCHAR(200) NOT NULL,
    event_version       INTEGER NOT NULL,
    payload_json        TEXT NOT NULL,
    idempotency_key     VARCHAR(200) NOT NULL,
    status              VARCHAR(32) NOT NULL,
    attempts            INTEGER NOT NULL,
    next_attempt_at     TIMESTAMPTZ NOT NULL,
    created_at          TIMESTAMPTZ NOT NULL,
    published_at        TIMESTAMPTZ,
    last_error          TEXT,
    CONSTRAINT uq_outbox_idempotency UNIQUE (tenant_id, idempotency_key),
    CONSTRAINT chk_outbox_status CHECK (status IN ('PENDING', 'PUBLISHING', 'PUBLISHED', 'FAILED'))
);

CREATE INDEX idx_outbox_pending
    ON outbox_event (status, next_attempt_at, created_at);

@Entity
@Table(
    name = "enforcement_case",
    uniqueConstraints = @UniqueConstraint(
        name = "uq_case_tenant_case_number",
        columnNames = {"tenant_id", "case_number"}
    )
)
public class EnforcementCase {

    @Id
    private UUID id;

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

    @Column(name = "case_number", nullable = false, updatable = false, length = 64)
    private String caseNumber;

    @Column(nullable = false, length = 300)
    private String title;

    @Enumerated(EnumType.STRING)
    @Column(nullable = false, length = 32)
    private CaseStatus status;

    @Enumerated(EnumType.STRING)
    @Column(nullable = false, length = 32)
    private CasePriority priority;

    @Column(name = "assigned_actor_id", length = 128)
    private String assignedActorId;

    @Column(name = "opened_at")
    private Instant openedAt;

    @Column(name = "resolved_at")
    private Instant resolvedAt;

    @Column(name = "closed_at")
    private Instant closedAt;

    @Embedded
    private AuditMetadata audit;

    @Version
    @Column(nullable = false)
    private long version;

    @OneToMany(mappedBy = "enforcementCase", cascade = CascadeType.PERSIST, orphanRemoval = false)
    private final List<CaseTransition> transitions = new ArrayList<>();

    @OneToMany(mappedBy = "enforcementCase", cascade = CascadeType.PERSIST, orphanRemoval = false)
    private final List<Evidence> evidence = new ArrayList<>();

    protected EnforcementCase() {
        // JPA
    }

    private EnforcementCase(
            UUID id,
            String tenantId,
            String caseNumber,
            String title,
            CasePriority priority,
            Actor actor,
            Clock clock
    ) {
        this.id = Objects.requireNonNull(id);
        this.tenantId = requireText(tenantId, "tenantId");
        this.caseNumber = requireText(caseNumber, "caseNumber");
        this.title = requireText(title, "title");
        this.priority = Objects.requireNonNull(priority);
        this.status = CaseStatus.DRAFT;
        this.audit = AuditMetadata.created(actor.id(), clock.instant());
    }

    public static EnforcementCase draft(
            String tenantId,
            String caseNumber,
            String title,
            CasePriority priority,
            Actor actor,
            Clock clock
    ) {
        return new EnforcementCase(UUID.randomUUID(), tenantId, caseNumber, title, priority, actor, clock);
    }
}

The entity should not allow invalid business transitions through ordinary object mutation.

public void open(Actor actor, Clock clock, String reason) {
    requireNotClosed();

    if (status != CaseStatus.DRAFT) {
        throw DomainViolation.invalidTransition(status, CaseStatus.OPEN);
    }

    CaseStatus previous = status;
    status = CaseStatus.OPEN;
    openedAt = clock.instant();
    audit = audit.updated(actor.id(), clock.instant());

    transitions.add(CaseTransition.record(
        tenantId,
        this,
        previous,
        status,
        actor,
        reason,
        clock
    ));
}

public void assignTo(Actor assignee, Actor actor, Clock clock) {
    requireActive();

    String newAssignee = requireText(assignee.id(), "assignee");
    if (Objects.equals(assignedActorId, newAssignee)) {
        return;
    }

    assignedActorId = newAssignee;
    audit = audit.updated(actor.id(), clock.instant());
}

public Evidence addEvidence(
        EvidenceType type,
        String externalReference,
        String displayName,
        String contentHash,
        Actor actor,
        Clock clock
) {
    requireActive();

    boolean duplicateInMemory = evidence.stream()
        .filter(e -> !e.isDeleted())
        .anyMatch(e -> e.sameExternalReference(externalReference));

    if (duplicateInMemory) {
        throw DomainViolation.duplicateEvidence(externalReference);
    }

    Evidence item = Evidence.attach(
        tenantId,
        this,
        type,
        externalReference,
        displayName,
        contentHash,
        actor,
        clock
    );

    evidence.add(item);
    audit = audit.updated(actor.id(), clock.instant());
    return item;
}

private void requireActive() {
    if (status == CaseStatus.CLOSED || status == CaseStatus.RESOLVED) {
        throw DomainViolation.caseNotActive(id, status);
    }
}

private void requireNotClosed() {
    if (status == CaseStatus.CLOSED) {
        throw DomainViolation.caseClosed(id);
    }
}

Domain method protects local object consistency.
Database constraint protects global concurrent consistency.

@Embeddable
public class AuditMetadata {

    @Column(name = "created_at", nullable = false, updatable = false)
    private Instant createdAt;

    @Column(name = "created_by", nullable = false, updatable = false, length = 128)
    private String createdBy;

    @Column(name = "updated_at", nullable = false)
    private Instant updatedAt;

    @Column(name = "updated_by", nullable = false, length = 128)
    private String updatedBy;

    protected AuditMetadata() {
    }

    private AuditMetadata(Instant createdAt, String createdBy, Instant updatedAt, String updatedBy) {
        this.createdAt = Objects.requireNonNull(createdAt);
        this.createdBy = requireText(createdBy, "createdBy");
        this.updatedAt = Objects.requireNonNull(updatedAt);
        this.updatedBy = requireText(updatedBy, "updatedBy");
    }

    public static AuditMetadata created(String actorId, Instant now) {
        return new AuditMetadata(now, actorId, now, actorId);
    }

    public AuditMetadata updated(String actorId, Instant now) {
        return new AuditMetadata(createdAt, createdBy, now, actorId);
    }
}

@Entity
@Table(
    name = "case_evidence",
    uniqueConstraints = @UniqueConstraint(
        name = "uq_evidence_case_ref",
        columnNames = {"tenant_id", "case_id", "external_reference"}
    )
)
public class Evidence {

    @Id
    private UUID id;

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

    @ManyToOne(fetch = FetchType.LAZY, optional = false)
    @JoinColumn(name = "case_id", nullable = false, updatable = false)
    private EnforcementCase enforcementCase;

    @Enumerated(EnumType.STRING)
    @Column(name = "evidence_type", nullable = false, length = 32)
    private EvidenceType type;

    @Column(name = "external_reference", nullable = false, updatable = false, length = 256)
    private String externalReference;

    @Column(name = "display_name", nullable = false, length = 300)
    private String displayName;

    @Column(name = "content_hash", length = 128)
    private String contentHash;

    @Column(nullable = false)
    private boolean deleted;

    @Column(name = "deleted_at")
    private Instant deletedAt;

    @Column(name = "deleted_by", length = 128)
    private String deletedBy;

    @Embedded
    private AuditMetadata audit;

    @Version
    @Column(nullable = false)
    private long version;

    protected Evidence() {
    }

    static Evidence attach(
            String tenantId,
            EnforcementCase enforcementCase,
            EvidenceType type,
            String externalReference,
            String displayName,
            String contentHash,
            Actor actor,
            Clock clock
    ) {
        Evidence evidence = new Evidence();
        evidence.id = UUID.randomUUID();
        evidence.tenantId = requireText(tenantId, "tenantId");
        evidence.enforcementCase = Objects.requireNonNull(enforcementCase);
        evidence.type = Objects.requireNonNull(type);
        evidence.externalReference = requireText(externalReference, "externalReference");
        evidence.displayName = requireText(displayName, "displayName");
        evidence.contentHash = contentHash;
        evidence.deleted = false;
        evidence.audit = AuditMetadata.created(actor.id(), clock.instant());
        return evidence;
    }

    public void softDelete(Actor actor, Clock clock) {
        if (deleted) {
            return;
        }
        deleted = true;
        deletedAt = clock.instant();
        deletedBy = actor.id();
    }
}

@Entity
@Table(name = "case_transition")
public class CaseTransition {

    @Id
    private UUID id;

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

    @ManyToOne(fetch = FetchType.LAZY, optional = false)
    @JoinColumn(name = "case_id", nullable = false, updatable = false)
    private EnforcementCase enforcementCase;

    @Enumerated(EnumType.STRING)
    @Column(name = "from_status", length = 32)
    private CaseStatus fromStatus;

    @Enumerated(EnumType.STRING)
    @Column(name = "to_status", nullable = false, length = 32)
    private CaseStatus toStatus;

    @Column(length = 1000)
    private String reason;

    @Column(name = "actor_id", nullable = false, updatable = false, length = 128)
    private String actorId;

    @Column(name = "occurred_at", nullable = false, updatable = false)
    private Instant occurredAt;

    protected CaseTransition() {
    }

    static CaseTransition record(
            String tenantId,
            EnforcementCase enforcementCase,
            CaseStatus fromStatus,
            CaseStatus toStatus,
            Actor actor,
            String reason,
            Clock clock
    ) {
        CaseTransition transition = new CaseTransition();
        transition.id = UUID.randomUUID();
        transition.tenantId = tenantId;
        transition.enforcementCase = enforcementCase;
        transition.fromStatus = fromStatus;
        transition.toStatus = Objects.requireNonNull(toStatus);
        transition.reason = reason;
        transition.actorId = actor.id();
        transition.occurredAt = clock.instant();
        return transition;
    }
}

public interface CaseRepository {

    Optional<EnforcementCase> findForCommand(String tenantId, UUID caseId);

    Optional<EnforcementCase> findForUpdate(String tenantId, UUID caseId);

    boolean existsCaseNumber(String tenantId, String caseNumber);

    EnforcementCase save(EnforcementCase enforcementCase);
}

@Repository
class JpaCaseRepositoryAdapter implements CaseRepository {

    private final CaseJpaRepository delegate;

    JpaCaseRepositoryAdapter(CaseJpaRepository delegate) {
        this.delegate = delegate;
    }

    @Override
    public Optional<EnforcementCase> findForCommand(String tenantId, UUID caseId) {
        return delegate.findByTenantIdAndId(tenantId, caseId);
    }

    @Override
    public Optional<EnforcementCase> findForUpdate(String tenantId, UUID caseId) {
        return delegate.findLockedByTenantIdAndId(tenantId, caseId);
    }

    @Override
    public boolean existsCaseNumber(String tenantId, String caseNumber) {
        return delegate.existsByTenantIdAndCaseNumber(tenantId, caseNumber);
    }

    @Override
    public EnforcementCase save(EnforcementCase enforcementCase) {
        return delegate.save(enforcementCase);
    }
}

interface CaseJpaRepository extends JpaRepository<EnforcementCase, UUID> {

    Optional<EnforcementCase> findByTenantIdAndId(String tenantId, UUID id);

    boolean existsByTenantIdAndCaseNumber(String tenantId, String caseNumber);

    @Lock(LockModeType.OPTIMISTIC_FORCE_INCREMENT)
    @Query("""
        select c
        from EnforcementCase c
        where c.tenantId = :tenantId
          and c.id = :id
        """)
    Optional<EnforcementCase> findLockedByTenantIdAndId(
        @Param("tenantId") String tenantId,
        @Param("id") UUID id
    );
}

@Service
public class ChangeCaseStatusHandler {

    private final CaseRepository cases;
    private final OutboxEventRepository outbox;
    private final CurrentActor currentActor;
    private final Clock clock;

    public ChangeCaseStatusHandler(
            CaseRepository cases,
            OutboxEventRepository outbox,
            CurrentActor currentActor,
            Clock clock
    ) {
        this.cases = cases;
        this.outbox = outbox;
        this.currentActor = currentActor;
        this.clock = clock;
    }

    @Transactional
    public ChangeCaseStatusResult handle(ChangeCaseStatusCommand command) {
        Actor actor = currentActor.required();

        EnforcementCase enforcementCase = cases.findForCommand(command.tenantId(), command.caseId())
            .orElseThrow(() -> CaseNotFoundException.forId(command.caseId()));

        CaseStatus previous = enforcementCase.status();
        enforcementCase.changeStatus(command.targetStatus(), actor, clock, command.reason());

        outbox.save(OutboxEvent.caseStatusChanged(
            command.tenantId(),
            enforcementCase.id(),
            previous,
            enforcementCase.status(),
            actor.id(),
            clock.instant()
        ));

        return ChangeCaseStatusResult.accepted(enforcementCase.id(), enforcementCase.status());
    }
}

Load case.
Validate lifecycle transition.
Mutate case.
Append transition history.
Store outbox event.
Commit all or none.

public void changeStatus(CaseStatus target, Actor actor, Clock clock, String reason) {
    requireNotClosedUnlessClosing(target);

    if (!status.canMoveTo(target)) {
        throw DomainViolation.invalidTransition(status, target);
    }

    CaseStatus previous = status;
    Instant now = clock.instant();

    status = target;

    switch (target) {
        case OPEN -> openedAt = firstNonNull(openedAt, now);
        case RESOLVED -> resolvedAt = now;
        case CLOSED -> closedAt = now;
        default -> {
            // no timestamp side effect
        }
    }

    transitions.add(CaseTransition.record(
        tenantId,
        this,
        previous,
        target,
        actor,
        reason,
        clock
    ));

    audit = audit.updated(actor.id(), now);
}

public enum CaseStatus {
    DRAFT,
    OPEN,
    IN_REVIEW,
    ESCALATED,
    RESOLVED,
    CLOSED;

    public boolean canMoveTo(CaseStatus target) {
        return switch (this) {
            case DRAFT -> target == OPEN;
            case OPEN -> target == IN_REVIEW || target == ESCALATED;
            case IN_REVIEW -> target == ESCALATED || target == RESOLVED;
            case ESCALATED -> target == RESOLVED;
            case RESOLVED -> target == CLOSED;
            case CLOSED -> false;
        };
    }
}

public interface CaseTransitionPolicy {
    TransitionDecision evaluate(EnforcementCase enforcementCase, CaseStatus target, Actor actor);
}

@Entity
@Table(
    name = "outbox_event",
    uniqueConstraints = @UniqueConstraint(
        name = "uq_outbox_idempotency",
        columnNames = {"tenant_id", "idempotency_key"}
    )
)
public class OutboxEvent {

    @Id
    private UUID id;

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

    @Column(name = "aggregate_type", nullable = false, updatable = false, length = 100)
    private String aggregateType;

    @Column(name = "aggregate_id", nullable = false, updatable = false)
    private UUID aggregateId;

    @Column(name = "event_type", nullable = false, updatable = false, length = 200)
    private String eventType;

    @Column(name = "event_version", nullable = false, updatable = false)
    private int eventVersion;

    @Lob
    @Column(name = "payload_json", nullable = false, updatable = false)
    private String payloadJson;

    @Column(name = "idempotency_key", nullable = false, updatable = false, length = 200)
    private String idempotencyKey;

    @Enumerated(EnumType.STRING)
    @Column(nullable = false, length = 32)
    private OutboxStatus status;

    @Column(nullable = false)
    private int attempts;

    @Column(name = "next_attempt_at", nullable = false)
    private Instant nextAttemptAt;

    @Column(name = "created_at", nullable = false, updatable = false)
    private Instant createdAt;

    @Column(name = "published_at")
    private Instant publishedAt;

    @Column(name = "last_error")
    private String lastError;

    @Version
    private long version;

    protected OutboxEvent() {
    }
}

public static OutboxEvent caseStatusChanged(
        String tenantId,
        UUID caseId,
        CaseStatus from,
        CaseStatus to,
        String actorId,
        Instant occurredAt
) {
    String eventType = "case.status-changed";
    int eventVersion = 1;
    String idempotencyKey = "case:%s:status:%s:%s".formatted(caseId, from, to);

    String payload = """
        {
          "caseId": "%s",
          "fromStatus": "%s",
          "toStatus": "%s",
          "actorId": "%s",
          "occurredAt": "%s"
        }
        """.formatted(caseId, from, to, actorId, occurredAt);

    return new OutboxEvent(
        UUID.randomUUID(),
        tenantId,
        "EnforcementCase",
        caseId,
        eventType,
        eventVersion,
        payload,
        idempotencyKey,
        occurredAt
    );
}

random UUID generated per retry

tenant + aggregate + transition identity + command id

@Component
public class OutboxRelay {

    private final OutboxEventRepository events;
    private final EventPublisher publisher;
    private final Clock clock;

    @Scheduled(fixedDelayString = "${outbox.relay.delay-ms:1000}")
    public void publishBatch() {
        List<OutboxEvent> batch = events.claimPendingBatch(clock.instant(), 100);

        for (OutboxEvent event : batch) {
            publishOne(event.id());
        }
    }

    @Transactional(propagation = Propagation.REQUIRES_NEW)
    public void publishOne(UUID eventId) {
        OutboxEvent event = events.findByIdForUpdate(eventId)
            .orElseThrow();

        if (!event.isPublishable(clock.instant())) {
            return;
        }

        try {
            publisher.publish(event.topic(), event.key(), event.payloadJson());
            event.markPublished(clock.instant());
        } catch (Exception ex) {
            event.markFailed(ex, clock.instant());
        }
    }
}

@Query(value = """
    select *
    from outbox_event
    where status in ('PENDING', 'FAILED')
      and next_attempt_at <= :now
    order by created_at
    limit :limit
    for update skip locked
    """, nativeQuery = true)
List<OutboxEvent> claimPendingBatch(@Param("now") Instant now, @Param("limit") int limit);

public record AssignedCaseRow(
    UUID id,
    String caseNumber,
    String title,
    CaseStatus status,
    CasePriority priority,
    String assignedActorId,
    Instant updatedAt,
    long version
) {
}

public interface CaseReadRepository {

    Page<AssignedCaseRow> findAssignedCases(
        String tenantId,
        String actorId,
        Set<CaseStatus> statuses,
        Pageable pageable
    );

    CaseDetailView findCaseDetail(String tenantId, UUID caseId);

    List<CaseStatusCountRow> countByStatus(String tenantId);
}

@Repository
class JpaCaseReadRepository implements CaseReadRepository {

    @PersistenceContext
    private EntityManager entityManager;

    @Override
    public Page<AssignedCaseRow> findAssignedCases(
            String tenantId,
            String actorId,
            Set<CaseStatus> statuses,
            Pageable pageable
    ) {
        List<AssignedCaseRow> rows = entityManager.createQuery("""
            select new com.example.enforcement.casepersistence.readmodel.AssignedCaseRow(
                c.id,
                c.caseNumber,
                c.title,
                c.status,
                c.priority,
                c.assignedActorId,
                c.audit.updatedAt,
                c.version
            )
            from EnforcementCase c
            where c.tenantId = :tenantId
              and c.assignedActorId = :actorId
              and c.status in :statuses
            order by c.audit.updatedAt desc, c.id desc
            """, AssignedCaseRow.class)
            .setParameter("tenantId", tenantId)
            .setParameter("actorId", actorId)
            .setParameter("statuses", statuses)
            .setFirstResult((int) pageable.getOffset())
            .setMaxResults(pageable.getPageSize())
            .getResultList();

        Long total = entityManager.createQuery("""
            select count(c)
            from EnforcementCase c
            where c.tenantId = :tenantId
              and c.assignedActorId = :actorId
              and c.status in :statuses
            """, Long.class)
            .setParameter("tenantId", tenantId)
            .setParameter("actorId", actorId)
            .setParameter("statuses", statuses)
            .getSingleResult();

        return new PageImpl<>(rows, pageable, total);
    }
}

CaseHeaderRow header = readRepository.findHeader(tenantId, caseId);
List<EvidenceRow> evidence = readRepository.findActiveEvidence(tenantId, caseId);
List<TransitionRow> transitions = readRepository.findTransitions(tenantId, caseId);

@EntityGraph(attributePaths = {"evidence"})
@Query("""
    select c
    from EnforcementCase c
    where c.tenantId = :tenantId
      and c.id = :id
    """)
Optional<EnforcementCase> findWithEvidence(String tenantId, UUID id);

@Query("""
    select distinct c
    from EnforcementCase c
    left join fetch c.evidence e
    where c.tenantId = :tenantId
      and c.id = :id
      and (e.deleted = false or e is null)
    """)
Optional<EnforcementCase> findDetailAggregate(String tenantId, UUID id);

Optional<EnforcementCase> findById(UUID id);

Optional<EnforcementCase> findByTenantIdAndId(String tenantId, UUID id);

Application tenant predicate + database constraint + tenant-aware tests + observability.

@Test
void assignedCaseQueryDoesNotReturnOtherTenantRows() {
    String tenantA = "tenant-a";
    String tenantB = "tenant-b";

    UUID sameLookingCaseA = createCase(tenantA, "CASE-001", "actor-1");
    UUID sameLookingCaseB = createCase(tenantB, "CASE-001", "actor-1");

    Page<AssignedCaseRow> rows = readRepository.findAssignedCases(
        tenantA,
        "actor-1",
        Set.of(CaseStatus.OPEN),
        PageRequest.of(0, 20)
    );

    assertThat(rows.getContent())
        .extracting(AssignedCaseRow::id)
        .contains(sameLookingCaseA)
        .doesNotContain(sameLookingCaseB);
}

@Query("""
    select new com.example.CaseHeaderRow(
        c.id, c.caseNumber, c.title, c.status, c.priority
    )
    from EnforcementCase c
    where c.tenantId = :tenantId
      and c.id = :caseId
      and (
          c.assignedActorId = :actorId
          or exists (
              select 1
              from CaseTeamMembership m
              where m.caseId = c.id
                and m.actorId = :actorId
          )
      )
    """)
Optional<CaseHeaderRow> findVisibleCaseHeader(String tenantId, UUID caseId, String actorId);

@RestControllerAdvice
class PersistenceExceptionHandler {

    @ExceptionHandler(ObjectOptimisticLockingFailureException.class)
    ResponseEntity<ApiError> optimisticLock(ObjectOptimisticLockingFailureException ex) {
        return ResponseEntity.status(HttpStatus.CONFLICT)
            .body(ApiError.conflict("The case was modified by another transaction."));
    }

    @ExceptionHandler(DataIntegrityViolationException.class)
    ResponseEntity<ApiError> integrity(DataIntegrityViolationException ex) {
        ConstraintKind kind = ConstraintClassifier.classify(ex);

        return switch (kind) {
            case DUPLICATE_CASE_NUMBER -> conflict("Case number already exists.");
            case DUPLICATE_EVIDENCE_REFERENCE -> conflict("Evidence reference already exists for this case.");
            default -> conflict("The requested change violates data constraints.");
        };
    }
}

public record ChangeCaseStatusCommand(
    String tenantId,
    UUID caseId,
    CaseStatus targetStatus,
    String reason,
    Long expectedVersion
) {
}

@Transactional
public ChangeCaseStatusResult handle(ChangeCaseStatusCommand command) {
    EnforcementCase enforcementCase = cases.findForCommand(command.tenantId(), command.caseId())
        .orElseThrow(() -> CaseNotFoundException.forId(command.caseId()));

    if (command.expectedVersion() != null && enforcementCase.version() != command.expectedVersion()) {
        throw new StaleCaseCommandException(enforcementCase.id(), enforcementCase.version());
    }

    enforcementCase.changeStatus(command.targetStatus(), currentActor.required(), clock, command.reason());
    outbox.save(OutboxEvent.caseStatusChanged(...));

    return ChangeCaseStatusResult.accepted(enforcementCase.id(), enforcementCase.status(), enforcementCase.version());
}

Expected version check gives better user-facing conflict semantics.
@Version gives database-level lost-update protection at flush/commit.

Transaction boundary:
- starts in ChangeCaseStatusHandler.handle
- loads one EnforcementCase by tenant + id
- validates lifecycle transition
- mutates case status/timestamps/audit
- appends CaseTransition
- stores OutboxEvent
- commits

Inside transaction:
- database reads/writes only
- no broker publish
- no email send
- no remote file download
- no long CPU processing

Expected conflicts:
- stale version
- invalid transition
- duplicate outbox idempotency key
- database timeout/deadlock

Retry policy:
- optimistic conflict: no automatic retry for user-driven command
- deadlock/lock timeout: bounded retry if command idempotent
- duplicate command id: return previous accepted result if available

Goal: replace assigned_actor_id with assignment table.

Release 1:
- create case_assignment table
- write both enforcement_case.assigned_actor_id and case_assignment
- read old column
- backfill case_assignment

Release 2:
- read case_assignment
- keep dual write
- verify metrics: old/new assignment consistency

Release 3:
- stop writing old column
- drop old column later after retention window

class EnforcementCaseTest {

    private final Clock clock = Clock.fixed(Instant.parse("2026-06-30T10:00:00Z"), ZoneOffset.UTC);
    private final Actor actor = new Actor("reviewer-1");

    @Test
    void draftCanBeOpened() {
        EnforcementCase c = EnforcementCase.draft(
            "tenant-a",
            "CASE-001",
            "Suspicious activity",
            CasePriority.HIGH,
            actor,
            clock
        );

        c.open(actor, clock, "Initial review started");

        assertThat(c.status()).isEqualTo(CaseStatus.OPEN);
        assertThat(c.transitions()).hasSize(1);
    }

    @Test
    void closedCaseCannotBeReopened() {
        EnforcementCase c = resolvedCase();
        c.close(actor, clock, "Complete");

        assertThatThrownBy(() -> c.open(actor, clock, "Reopen"))
            .isInstanceOf(DomainViolation.class);
    }
}

@DataJpaTest
@Testcontainers
class CaseRepositoryIT {

    @Container
    static PostgreSQLContainer<?> postgres = new PostgreSQLContainer<>("postgres:16-alpine");

    @Autowired
    CaseRepository cases;

    @Autowired
    EntityManager entityManager;

    @Test
    void persistsCaseWithTransitionHistory() {
        EnforcementCase c = EnforcementCase.draft(...);
        c.open(actor, clock, "Start");

        cases.save(c);
        entityManager.flush();
        entityManager.clear();

        EnforcementCase reloaded = cases.findForCommand("tenant-a", c.id()).orElseThrow();

        assertThat(reloaded.status()).isEqualTo(CaseStatus.OPEN);
        assertThat(reloaded.transitions()).hasSize(1);
    }
}

save -> flush -> clear -> reload -> assert database state

@Test
void duplicateCaseNumberInSameTenantIsRejected() {
    EnforcementCase first = EnforcementCase.draft("tenant-a", "CASE-001", "One", HIGH, actor, clock);
    EnforcementCase second = EnforcementCase.draft("tenant-a", "CASE-001", "Two", HIGH, actor, clock);

    cases.save(first);
    entityManager.flush();

    cases.save(second);

    assertThatThrownBy(() -> entityManager.flush())
        .isInstanceOf(PersistenceException.class);
}

@Test
void sameCaseNumberAcrossTenantsIsAllowed() {
    cases.save(EnforcementCase.draft("tenant-a", "CASE-001", "One", HIGH, actor, clock));
    cases.save(EnforcementCase.draft("tenant-b", "CASE-001", "Two", HIGH, actor, clock));

    entityManager.flush();
}

@Test
void concurrentStatusChangesCauseOptimisticConflict() {
    UUID caseId = transactionTemplate.execute(tx -> {
        EnforcementCase c = EnforcementCase.draft("tenant-a", "CASE-001", "Case", HIGH, actor, clock);
        c.open(actor, clock, "Start");
        cases.save(c);
        return c.id();
    });

    EnforcementCase tx1Case = loadInNewEntityManager(caseId);
    EnforcementCase tx2Case = loadInNewEntityManager(caseId);

    tx1Case.changeStatus(CaseStatus.IN_REVIEW, actor, clock, "Review");
    tx2Case.changeStatus(CaseStatus.ESCALATED, actor, clock, "Escalate");

    commit(tx1Case);

    assertThatThrownBy(() -> commit(tx2Case))
        .isInstanceOf(OptimisticLockException.class);
}

@Test
void statusChangePersistsCaseTransitionAndOutboxAtomically() {
    UUID caseId = givenOpenCase();

    handler.handle(new ChangeCaseStatusCommand(
        "tenant-a",
        caseId,
        CaseStatus.IN_REVIEW,
        "Begin review",
        null
    ));

    entityManager.flush();
    entityManager.clear();

    EnforcementCase reloaded = cases.findForCommand("tenant-a", caseId).orElseThrow();
    List<OutboxEvent> events = outbox.findByAggregateId(caseId);

    assertThat(reloaded.status()).isEqualTo(CaseStatus.IN_REVIEW);
    assertThat(reloaded.transitions()).hasSize(2); // OPEN plus IN_REVIEW
    assertThat(events)
        .extracting(OutboxEvent::eventType)
        .contains("case.status-changed");
}

@Test
void invalidTransitionDoesNotPersistOutboxEvent() {
    UUID caseId = givenClosedCase();

    assertThatThrownBy(() -> handler.handle(new ChangeCaseStatusCommand(
        "tenant-a",
        caseId,
        CaseStatus.OPEN,
        "Invalid reopen",
        null
    ))).isInstanceOf(DomainViolation.class);

    assertThat(outbox.findByAggregateId(caseId)).isEmpty();
}

@Test
void assignedCaseListUsesBoundedQueryCount() {
    givenCasesWithEvidenceAndTransitions(30);

    sqlCounter.reset();

    Page<AssignedCaseRow> page = readRepository.findAssignedCases(
        "tenant-a",
        "actor-1",
        Set.of(CaseStatus.OPEN, CaseStatus.IN_REVIEW),
        PageRequest.of(0, 20)
    );

    assertThat(page.getContent()).hasSize(20);
    assertThat(sqlCounter.selectCount()).isLessThanOrEqualTo(2);
}

No query budget -> no regression guardrail.

spring.jpa.properties.hibernate.generate_statistics=true
logging.level.org.hibernate.SQL=DEBUG
logging.level.org.hibernate.orm.jdbc.bind=TRACE
logging.level.org.hibernate.stat=DEBUG

"It seems fine."

"This endpoint regressed from 2 queries to 42 queries after adding evidence preview."

1. What endpoint/use case is affected?
2. Is it command or read-only path?
3. Does it load entities or projections?
4. How many SQL statements are expected?
5. Are collections fetched intentionally?
6. Does pagination interact with fetch joins?
7. Are predicates covered by indexes?
8. Is sort order deterministic?
9. Does tenant predicate appear?
10. Does test assert query count or result shape?
11. Does production metric expose latency/regression?

"Does the repository method work?"

Command path:
- loads aggregate/entity
- enforces invariant
- uses transaction
- writes outbox
- cares about concurrency

Read path:
- uses projection
- may be read-only transaction
- cares about query plan
- cares about pagination and visibility
- should not mutate managed state

public void deleteEvidence(UUID evidenceId, Actor actor, Clock clock) {
    requireActive();

    Evidence item = evidence.stream()
        .filter(e -> e.id().equals(evidenceId))
        .findFirst()
        .orElseThrow(() -> DomainViolation.evidenceNotFound(evidenceId));

    item.softDelete(actor, clock);
    audit = audit.updated(actor.id(), clock.instant());
}

@Modifying(clearAutomatically = true, flushAutomatically = true)
@Query("""
    update EnforcementCase c
    set c.status = 'CLOSED',
        c.closedAt = :now,
        c.audit.updatedAt = :now,
        c.audit.updatedBy = :actorId
    where c.tenantId = :tenantId
      and c.status = 'DRAFT'
      and c.audit.createdAt < :cutoff
    """)
int closeStaleDrafts(
    String tenantId,
    Instant cutoff,
    Instant now,
    String actorId
);

Do not add second-level cache until query and transaction design are already correct.

Cache key = tenant + actor/role scope + query parameters + visibility mode + page cursor/version

Persist only what you need.
Publish only what consumers need.
Log only what operations need.
Expose only what actor is allowed to see.

# Case Persistence Mapping Notes

## Aggregate

`EnforcementCase` is the lifecycle aggregate root.
`Evidence` has identity and audit significance, so it is a child entity, not an embeddable.
`CaseTransition` is append-only history.

## Transaction Boundary

Command handlers own transactions.
Repositories do not start independent transactions.
Outbox events are written in the same transaction as case updates.

## Concurrency

`EnforcementCase.version` protects lifecycle and assignment updates.
User-facing commands may include expected version.
Outbox relay uses row-level locking for claim/publish.

## Read Models

List and dashboard endpoints use DTO projections.
Entities are not returned by API.
Fetch joins are allowed only for bounded command preparation.

## Soft Delete

Evidence uses soft delete.
Active reads exclude deleted rows.
Audit reads include deleted rows.
Duplicate external reference remains reserved after deletion.

## Migration

Production uses Flyway/Liquibase migrations.
DDL auto-update is disabled outside local development.

[ ] Entity identity is clear.
[ ] equals/hashCode does not break managed/detached behavior.
[ ] No public setters for invariant-sensitive fields.
[ ] Associations have correct owning side.
[ ] Cascades represent lifecycle ownership, not convenience.
[ ] Large collections are not loaded accidentally.
[ ] Enum mapping is stable and migration-aware.
[ ] Soft-delete semantics are explicit.

[ ] Transaction boundary is at application service/command handler.
[ ] No remote calls inside transaction.
[ ] Rollback behavior is clear.
[ ] Side effects use outbox or after-commit strategy.
[ ] Locking strategy matches conflict model.
[ ] Retry policy is idempotency-aware.

[ ] Read path uses projection when entity behavior is not needed.
[ ] Pagination is deterministic.
[ ] Fetch plan is explicit.
[ ] Query count is bounded.
[ ] Tenant/authorization predicates are included.
[ ] Index supports predicate and sort order.
[ ] Count query is correct and not accidentally expensive.

[ ] Migration is versioned.
[ ] Constraints encode important invariants.
[ ] New non-null columns have backfill strategy.
[ ] Index creation is operationally safe.
[ ] No production runtime schema mutation.

[ ] Repository test uses real database.
[ ] Tests flush/clear/reload before asserting persistence state.
[ ] Constraint tests cover duplicates and tenant boundary.
[ ] Query count or query shape is guarded.
[ ] Concurrency behavior is tested where relevant.
[ ] Outbox atomicity is tested.

[ ] SQL latency is observable.
[ ] Connection pool metrics are observable.
[ ] Outbox lag is observable.
[ ] Lock failures are observable.
[ ] Cache behavior is observable if cache is used.
[ ] Logs avoid sensitive values.

The user-visible state change and the outbox event are committed together.

flush -> clear -> reload passes.
Schema constraints exist.
No controller returns entity.

Invalid transitions fail.
Concurrent updates conflict.
Transition history persists.

List endpoint uses bounded query count.
No lazy loading happens during serialization.
Pagination is deterministic.

Business update and event commit atomically.
Relay retry works.
Duplicate publish is tolerated.
Outbox lag metric exists.

You can answer: what SQL is slow, which endpoint caused it, how many connections are busy, whether outbox is delayed, and whether lock conflicts increased.

A field in Java becomes a column.
A column participates in constraints.
Constraints interact with concurrent transactions.
Transactions interact with locks.
Locks interact with latency.
Latency interacts with connection pool pressure.
Connection pressure interacts with system availability.
Entity graphs interact with query plans.
Query plans interact with indexes.
Indexes interact with migrations.
Migrations interact with deployment order.
Deployment order interacts with backward compatibility.
Outbox events interact with consumers and idempotency.
Idempotency interacts with business semantics.

Entity = lifecycle and invariant boundary.
Persistence context = identity map and unit-of-work boundary.
Transaction = atomic consistency boundary.
Repository = persistence intention boundary.
Projection = read shape boundary.
Migration = schema evolution boundary.
Constraint = database truth boundary.
Lock = concurrency boundary.
Outbox = side-effect reliability boundary.
Test = proof boundary.
Observability = production feedback boundary.
Review checklist = organizational memory boundary.

Entity returned directly from REST API.
CascadeType.ALL everywhere.
@ManyToMany for business relationship with lifecycle metadata.
EAGER used as N+1 fix.
@Transactional on private methods or random repository calls.
Remote API call inside transaction.
Message broker publish before database commit.
Repository method missing tenant predicate.
Read endpoint loads aggregate graph for a simple table.
Bulk update bypasses audit/outbox without explicit compensation.
Soft delete without active/audit visibility policy.
No version column on high-contention aggregate.
No migration file for schema change.
No flush/clear/reload in persistence tests.
No query count guard for important endpoints.
No outbox lag metric.
No plan for duplicate event consumption.

What is the consistency boundary?
What is the lifecycle owner?
What does the database guarantee?
What does the transaction guarantee?
What does the test actually prove?
What does production telemetry reveal?
What can fail under concurrency?
What will break during schema evolution?
What data can leak across tenant, role, or visibility boundary?