A payment notification that must count once
A payments provider sends our service a notification each time a credit union member’s card payment settles. Each notification has an event_id, an account_id, an amount_cents and a settled_at time. The provider promises to deliver every notification at least once. In practice it sometimes delivers the same notification two or three times, and occasionally delivers notifications out of order. If our service does not answer with a success within 10 seconds, the provider sends it again.
Today the service adds amount_cents to the account balance each time a notification arrives. Design a version that never credits the same payment twice, never loses one, and stays correct if two copies arrive at the same moment on different servers.
Hint
The database can enforce uniqueness for you. Think about what has to happen in the same transaction, and about when you tell the provider “success”.
Worked solution
Keep a table of processed events with event_id as its primary key. For each notification, in one database transaction:
- Insert the
event_idinto the processed-events table. - If the insert fails because the key already exists, this is a duplicate: do nothing else and answer success.
- Otherwise, write the payment to a ledger table and update the balance.
- Commit, then answer success.
Because the uniqueness check and the balance change commit together, two copies arriving at once on different servers cannot both succeed: one insert wins and the other fails on the key. Answering success only after the commit means a crash before the commit leads to a retry, not a lost payment.
Out-of-order delivery does not matter for a running total, because addition does not depend on order. It would matter if notifications could change the same payment (for example, settled and then reversed). Then you store each event against the payment and apply a state only if it is later than the one you have, using settled_at or a sequence number from the provider.
Two more points a strong answer includes. Keep the work inside the 10-second window small, and if it cannot be, record the event first and process it from a queue. And keep the ledger of individual payments rather than only a balance, so the balance can always be rebuilt and audited.