When a Specification's isSatisfiedBy logic needs to run as a database query instead of filtering an in-memory list, what's the core translation challenge, and how do teams typically solve it without duplicating the rule?

## Two different execution models The core tension is that `isSatisfiedBy(candidate)` is fundamentally an imperative function: give it one fully-loaded object, and it walks through some logic and returns true or false. A repository backed by a relational (or document) database, on the other hand, needs something declarative it can hand to a query planner — a `WHERE` clause, a filter document, an index scan predicate — that describes the *shape* of matching rows without ever materializing the non-matching ones. These are fundamentally different execution models: - one runs candidate-by-candidate in application memory after the object already exists; - the other describes selection criteria the storage engine applies before rows are even fetched. If you naively call a repository's `findAll()`, load every row into memory, and then run `isSatisfiedBy` over each one, you get a correct but often catastrophic-at-scale implementation — a "selection" specification with an efficient-looking `isSatisfiedBy` can silently turn into an O(n) full-table load on a million-row table, the classic pitfall of specifications leaking their in-memory mental model into a persistence boundary that was never designed for row-by-row scanning in application code. ## The typical solution: emit a query fragment The typical solution is to give the specification a second capability alongside `isSatisfiedBy`: a method that emits a query fragment in whatever form the persistence technology understands, built from the *same* underlying rule expression so there's exactly one source of truth. - **In the Java/JPA world**, Spring Data JPA's `Specification<T>` interface is the canonical example: alongside (or instead of) an in-memory `isSatisfiedBy`, it defines `toPredicate(root, query, criteriaBuilder): Predicate`, which builds a `WHERE`-clause fragment using the JPA Criteria API rather than evaluating a candidate directly. The `and`/`or`/`not` combinators then operate at the query-fragment level too, combining `Predicate` objects rather than boolean results, so `spec1.and(spec2)` produces a combined SQL condition, not a combined in-memory check. - **In .NET**, the analogous mechanism is an `Expression<Func<T, bool>>` — a compilable lambda tree that LINQ providers (like Entity Framework) can walk and translate into SQL, versus a plain `Func<T, bool>` which can only ever run in memory. The unifying idea in both cases: instead of writing the rule as ordinary imperative code, you write it as an *expression tree* or *builder call sequence* that a translator can walk and convert to a native query, which means the same specification object can, in principle, serve both selection-by-query and in-memory validation, because the expression tree can also just be evaluated directly against one object. ## What cannot be translated The trade-off is real and shows up constantly in practice: not every predicate you can express as ordinary code can be mechanically translated into a query. A specification whose `isSatisfiedBy` calls a custom Kotlin/Java method, loops over a collection, or invokes an external service is trivially expressible in memory but has no SQL equivalent — the ORM's expression-tree walker will either throw at runtime ("could not translate expression") or, worse, silently fall back to loading everything into memory and filtering there, quietly reintroducing the O(n) load problem the query-based approach was supposed to avoid. This means specifications meant for query translation are constrained to a subset of expressions the query builder actually understands — simple comparisons, joins, and boolean combinators — and teams have to be disciplined about not smuggling arbitrary business logic into a `toPredicate` implementation that looks fine in a unit test (run in memory against a mocked list) but breaks or silently degrades against the real database. ## The semantic mismatch: SQL's three-valued logic A second concrete failure mode is semantic mismatch, not just capability mismatch: boolean logic in Java/Kotlin and boolean logic in SQL don't fully agree, primarily because SQL uses three-valued logic (`TRUE`/`FALSE`/`UNKNOWN`) due to `NULL`. - A specification like `not(hasDiscountCode)` might translate naively to `NOT (discount_code IS NOT NULL)`, which behaves correctly. - But a specification like `discountAmount > 0` translated to `WHERE discount_amount > 0` will simply exclude rows where `discount_amount IS NULL` rather than including them the way an in-memory `candidate.discountAmount > 0` check would if the in-memory model defaulted null to zero. The two "same" specifications quietly diverge on edge-case data, and this divergence usually surfaces as a support ticket ("this order shows up in the in-app filter but not in the admin report") rather than a failed test, because unit tests against in-memory fixtures rarely include the NULL-heavy edge cases production data actually has. ## The pragmatic mitigation The pragmatic mitigation teams converge on: 1. Keep specifications intended for repository-level selection restricted to a genuinely declarative subset (simple field comparisons, AND/OR/NOT over those, joins expressible via the ORM's own navigation). 2. Write integration tests that exercise `toPredicate` against a real (or realistic, e.g., Testcontainers-backed) database rather than only unit-testing `isSatisfiedBy` against in-memory fixtures. 3. Explicitly document which specifications are "query-safe" versus "validation-only, must not be used for repository filtering", so the two use cases don't get silently conflated by a future maintainer who assumes every specification in the codebase is interchangeable across both.