Duplicate webhook delivery can lead to multiple balance updates if the system processes requests independently. A common error is using a SELECT-before-INSERT pattern, which fails to guarantee atomicity between checking for an existing event and applying the change.
The proposed solution involves using a unique composite key in the database, consisting of the event source and the event identifier. This ensures that the registration of the event and the balance update occur within a single transaction, preventing race conditions.
The implementation uses SQLite with explicit transaction management, specifically utilizing BEGIN IMMEDIATE to handle concurrent access. The system validates that the event data matches existing records to distinguish between legitimate retries and conflicting requests.
The approach emphasizes that database transactions cannot extend across network boundaries. For external side effects, such as sending notifications, a transactional outbox pattern is recommended to maintain consistency, though it does not replace the need for idempotency on the receiver's side.
