Unique constraints and soft delete

######## H2 ########
database: H2Dialect / H2 2.4.240 (2025-09-22)

== 1. boolean strategy, unique (email)
registered and soft-deleted ann@example.com; row is still there: [1 | ann@example.com | true]
registering it again: ConstraintViolationException -> JdbcSQLIntegrityConstraintViolationException: SQLState 23505, Unique index or primary key violation: "PUBLIC.UX_ACCOUNT_EMAIL ON PUBLIC.ACCOUNT(EMAIL NULLS FIRST) VALUES ( /* 1 */ 'ann@example.com' )"; SQL statement:

== 2. boolean strategy, unique (email, deleted)
register, delete, register again:
   second registration OK, rows: [1 | bob@example.com | true, 2 | bob@example.com | false]
   deleting the second registration: FAILED ConstraintViolationException -> JdbcSQLIntegrityConstraintViolationException: SQLState 23505, Unique index or primary key violation: "PUBLIC.UX_ACCOUNT_EMAIL_DELETED ON PUBLIC.ACCOUNT(EMAIL NULLS FIRST, DELETED NULLS FIRST) VALUES ( /* key:1 */ 'bob@example.com', TRUE)"; SQL statement:
   rows now: [1 | bob@example.com | true, 2 | bob@example.com | false]

== 3. partial unique index: create unique index ... (email) where deleted = false
H2 rejects it: SQLGrammarException -> JdbcSQLSyntaxErrorException: SQLState 42000, Syntax error in SQL statement "create unique index ux_account_live_email on account (email) [*]where deleted = false"; SQL statement:

== 4. timestamp strategy, unique (email, deleted_at) -- NULLs are distinct
two LIVE rows with the same email were accepted (columns: id | email | deleted_at is null): [1 | dee@example.com | true, 2 | dee@example.com | true]

== 5. timestamp strategy, unique nulls not distinct (email, deleted_at)
H2: SQLGrammarException -> JdbcSQLSyntaxErrorException: SQLState 42000, Syntax error in SQL statement "create unique index ux_stamped_nnd on account_stamped (email, deleted_at) [*]nulls not distinct"; SQL statement:

== 6. timestamp strategy, partial unique index (email) where deleted_at is null
H2 rejects it: SQLGrammarException -> JdbcSQLSyntaxErrorException: SQLState 42000, Syntax error in SQL statement "create unique index ux_stamped_live on account_stamped (email) [*]where deleted_at is null"; SQL statement:

######## POSTGRES ########
database: PostgreSQLDialect / PostgreSQL 17.11

== 1. boolean strategy, unique (email)
registered and soft-deleted ann@example.com; row is still there: [1 | ann@example.com | true]
registering it again: ConstraintViolationException -> PSQLException: SQLState 23505, ERROR: duplicate key value violates unique constraint "ux_account_email"

== 2. boolean strategy, unique (email, deleted)
register, delete, register again:
   second registration OK, rows: [1 | bob@example.com | true, 2 | bob@example.com | false]
   deleting the second registration: FAILED ConstraintViolationException -> PSQLException: SQLState 23505, ERROR: duplicate key value violates unique constraint "ux_account_email_deleted"
   rows now: [1 | bob@example.com | true, 2 | bob@example.com | false]

== 3. partial unique index: create unique index ... (email) where deleted = false
index created
register/delete x2, then register a third time: OK, rows: [1 | cy@example.com | true, 2 | cy@example.com | true, 3 | cy@example.com | false]
a fourth LIVE registration: ConstraintViolationException -> PSQLException: SQLState 23505, ERROR: duplicate key value violates unique constraint "ux_account_live_email"

== 4. timestamp strategy, unique (email, deleted_at) -- NULLs are distinct
two LIVE rows with the same email were accepted (columns: id | email | deleted_at is null): [1 | dee@example.com | true, 2 | dee@example.com | true]

== 5. timestamp strategy, unique nulls not distinct (email, deleted_at)
second LIVE registration: ConstraintViolationException -> PSQLException: SQLState 23505, ERROR: duplicate key value violates unique constraint "ux_stamped_nnd"
delete / re-register / delete / re-register: OK (columns: id | email | deleted_at is null), rows: [1 | eve@example.com | false, 3 | eve@example.com | false, 4 | eve@example.com | true]

== 6. timestamp strategy, partial unique index (email) where deleted_at is null
index created