Phase 3: Enforce transactional correctness and concurrency safety on PostgreSQL

Type: Reliability / Concurrency
Priority: Critical
Parent: PostgreSQL & Vault Migration
Depends on: Phase 2

Objective

Ensure that database migration preserves PayGuard's transactional and concurrency guarantees across multiple application instances.

Coordinate with OTP Domain Issue #25 without duplicating its domain-invariant work.

Tasks

OTP Verification

• Choose optimistic locking, conditional updates, or appropriate transactional locking.
• Prevent concurrent double consumption of the same OTP.
• Prevent lost updates to verification attempts.
• Persist failed-attempt counters correctly.
• Preserve final-attempt success and lock behavior.
• Restore historical OTP states correctly.
• Handle concurrent verification conflicts explicitly.
• Define retry behavior after transaction conflicts.

OTP Issuance

• Define the active-challenge replacement invariant.
• Prevent invalid concurrent issuance for the same customer and purpose.
• Handle issuance when no previous ACTIVE challenge exists.
• Preserve independence between unrelated customers and purposes.

Transaction Boundaries

• Review Spring transaction rollback behavior.
• Ensure required failed-attempt and expiry updates are committed.
• Avoid holding database locks during remote service calls.
• Define database versus external event/delivery guarantees.
• Verify financial workflows affected by PostgreSQL isolation.
• Preserve business idempotency and customer ownership.

Acceptance Criteria

• Two concurrent requests cannot both commit fresh successful verification of one OTP.
• Failed-attempt increments are not lost.
• Final-attempt transitions remain valid.
• Issuance races cannot violate the selected active-challenge invariant.
• Rejected attempts persist when required.
• Multi-instance safety is demonstrated using independent PostgreSQL transactions.
• No cross-system exactly-once guarantee is claimed without appropriate evidence.

Definition of Done

Concurrency tests pass against real PostgreSQL, and the selected transaction strategy is documented.