Payment gateway integration series: 1. Redirect, signatures and IPN · 2. Late IPNs, reconciliation and failure modes
An integration that “works” still meets IPNs that arrive late, IPNs that never arrive, and numbers that disagree with the gateway. After this post you can handle a late IPN, design reconciliation, and test every failure mode. Read first: part 1, and the booking and ledger post for the ledger.
At a glance:
flowchart TD
A["IPN from gateway"] --> C["Idempotent apply"]
B["Periodic querydr job"] --> C
C --> D["Record SUCCEEDED"]
D --> E{"Seat still held?"}
E -->|"Yes"| F["Booking PAID"]
E -->|"Released"| G{"Same-class seat left?"}
G -->|"Yes"| F
G -->|"No"| H["REFUND_PENDING, refund"]
I["Daily reconciliation"] --> J["FULL JOIN statement, payments"]
J --> K["5 buckets, OURS_ONLY worst"]
1. Case study: the IPN that arrives after inventory was released
Key idea: never ignore a late successful IPN. Record the money, then keep the booking or refund; and set the time windows so this case is rare.
Recap of part 1: payment attempts and the apply function
The backend creates a PENDING_PAYMENT booking and a PENDING payment attempt. Each attempt has one txn_ref, and a booking has at most one open attempt. The URL is signed with HMAC-SHA512, and the IPN is received by an idempotent handler. The apply(result) function runs in one transaction and uses conditional updates (an UPDATE guarded by the expected old status): the payment goes from PENDING/EXPIRED to SUCCEEDED/FAILED, the booking from PENDING_PAYMENT to PAID. A statement that updates 0 rows means it was already processed or the state is no longer valid.
Context. A booking holds seats for 15 minutes. The customer opens the gateway page at minute 13, is slow with the OTP, and is charged at minute 16. The expiry job released the seats at minute 15, and someone else has just bought them. The successful IPN arrives at minute 16.
Constraints. Real money was taken, so the IPN can’t be ignored. The customer must not lose money, and no seat may go to two people.
Chosen design: combine all three options.
- Prevent it upfront: make
vnp_ExpireDateshorter than the hold (URL 15 minutes, seats 20 minutes). The expiry job does not release bookings with aPENDINGpayment still within its deadline plus a buffer. This cuts the probability sharply but not to zero (a slow gateway, clock drift). - Ask before releasing: for a booking with an open payment, the job calls the transaction-query API and releases only when the gateway confirms nothing was paid.
- When a late IPN still arrives: record the payment as
SUCCEEDEDand try to re-acquire inventory (if a seat in the same class is left, hold it and move the booking toPAID). Otherwise move the payment toREFUND_PENDING, refund automatically and notify the customer.
(1) and (2) make it rare; (3) makes the rare case come out right. The expiry job has a “no open payment” condition:
UPDATE booking b
SET status = 'EXPIRED', version = version + 1, updated_at = now()
WHERE b.status = 'PENDING_PAYMENT' AND b.expires_at < now()
AND NOT EXISTS (
SELECT 1 FROM payment p
WHERE p.booking_id = b.booking_id AND p.status = 'PENDING'
AND p.expires_at > now() - interval '5 minutes') -- buffer for a slow gateway
RETURNING booking_id;
The refund branch of apply and test results on PostgreSQL 16
The branch of apply for a booking that is no longer PENDING_PAYMENT (the booking UPDATE touched 0 rows):
UPDATE payment SET status = 'REFUND_PENDING', updated_at = now()
WHERE txn_ref = :txnRef AND status = 'SUCCEEDED';
-- + insert a "refund" outbox row with idempotency key = txn_ref, and notify the customer
The SQL in this post was run on PostgreSQL 16:
- An overdue booking whose
PENDINGpayment is valid for another 2 minutes: not expired (0 rows). - A successful IPN for an
EXPIREDbooking: the payment goesEXPIRED → SUCCEEDED → REFUND_PENDINGin one transaction, and the booking staysEXPIRED.
2. Reconciliation
Key idea: reconciliation makes three sources agree: the status in our database, the ledger, and the gateway’s data. It runs at two rhythms: a transaction query every few minutes, and a statement match every day.
2.1. Near real time: querying transactions
A job runs every few minutes:
- Pick
PENDINGpayments pastexpires_at, or where the customer came back but the IPN hasn’t. - Call the gateway’s query API (VNPay:
querydr, server to server, its own signature over a fixed field order) in small batches, respecting rate limits. - Pass the result through the same
applyfunction as the IPN (part 1). If the gateway can’t find it or reports it unpaid, the payment moves toEXPIRED; network errors wait for the next run.
2.2. Daily: matching the statement against the database and ledger
Load the gateway’s daily statement (a file or an API) into a staging table gateway_statement, then FULL JOIN it with the successful payments of the same Vietnam-time day to see gaps on both sides:
WITH g AS (
SELECT * FROM gateway_statement WHERE statement_date = DATE '2026-11-18'
), ours AS (
SELECT * FROM payment
WHERE status IN ('SUCCEEDED', 'REFUND_PENDING', 'REFUNDED')
AND paid_at >= TIMESTAMPTZ '2026-11-18 00:00+07' AND paid_at < TIMESTAMPTZ '2026-11-19 00:00+07'
)
SELECT coalesce(g.txn_ref, ours.txn_ref) AS txn_ref,
CASE
WHEN ours.txn_ref IS NULL AND p.payment_id IS NULL THEN 'UNKNOWN_TO_US'
WHEN ours.txn_ref IS NULL THEN 'NOT_PAID_HERE'
WHEN g.txn_ref IS NULL THEN 'OURS_ONLY'
WHEN ours.amount <> g.amount THEN 'AMOUNT_MISMATCH'
ELSE 'MATCHED'
END AS result
FROM g
FULL JOIN ours ON ours.txn_ref = g.txn_ref
LEFT JOIN payment p ON p.txn_ref = g.txn_ref;
Run against sample data, it produces all five categories:
| Result | Meaning | Action |
|---|---|---|
MATCHED |
Agrees | Nothing |
NOT_PAID_HERE |
The gateway collected; we still have PENDING/EXPIRED |
Both the IPN and the query missed it: run apply from the statement data, investigate |
UNKNOWN_TO_US |
The gateway has a transaction we never created | Serious: wrong merchant, leaked key, or a test environment pointing at production; raise an alarm |
OURS_ONLY |
We recorded success; the gateway has nothing | The most serious: tickets may have been issued without payment; check with the gateway immediately |
AMOUNT_MISMATCH |
Different amounts | A unit bug or another bug; freeze the order, investigate |
Payout reconciliation (settlement). The gateway pays into the bank after deducting fees, typically T+1. Record a SETTLEMENT in the ledger (debit bank, debit fees, credit the gateway clearing account), then check that the clearing account comes back to exactly what hasn’t been paid out. How to write the entries is in the booking and ledger post.
3. Testing in the sandbox
Key idea: the sandbox is for manual runs only; CI signs IPNs itself with the test key and calls the endpoint directly, one BDD scenario per failure mode, on Testcontainers PostgreSQL.
- The IPN needs a public URL: a dev machine can’t receive it. Use a tunnel (cloudflared, ngrok) or a staging environment with a real domain.
- Don’t depend on the sandbox in CI: it’s slow and sometimes down for maintenance.
- Test account: the gateway issues its own
vnp_TmnCodeand hash secret, plus test cards (VNPay: a domestic NCB card with a fixed OTP; take the current details from the sandbox site). - Testcontainers PostgreSQL (a real PostgreSQL in Docker during tests) for the scenarios, because they depend on the real behaviour of conditional updates and unique constraints.
Six BDD scenarios for the IPN
- Given a
PENDINGpayment of 3,000,000 VND, When a valid successful IPN arrives, Then the payment isSUCCEEDED, the booking isPAIDat version 2, the ledger balances, and the reply is00. - Given that IPN was processed, When the same IPN arrives again, Then no row changes and the reply is
02. - Given anything, When an IPN has a wrong signature, Then no row changes, the reply is
97, and the callback is stored withsignature_ok = false. - Given a 3,000,000 VND payment, When the IPN says
vnp_Amount = 290000000, Then the reply is04and an alert fires. - Given an
EXPIREDbooking, When a successful IPN arrives, Then the payment isREFUND_PENDINGand a refund outbox row exists. - Given two identical IPNs arriving at once, When they’re processed in parallel, Then there’s exactly one state transition and one set of entries.
4. Failure-mode table
Key idea: three mechanisms handle almost every failure: the conditional update, the transaction-query job, and the REFUND_PENDING status.
| Failure | Consequence if unhandled | Handling |
|---|---|---|
| The IPN never arrives | The customer paid, the booking waits and then expires | Transaction-query job; daily statement reconciliation |
| The IPN arrives several times | Double ledger entries, tickets issued twice | Conditional update on status, unique gateway_txn_no, reply 02 |
| IPNs arrive concurrently | Two threads both pass the checks | The conditional update arbitrates, the unique constraint is the last net |
| The IPN arrives after expiry | The customer loses money or a seat is sold twice | Section 1: URL deadline shorter than the hold, ask before releasing, REFUND_PENDING |
| The return URL beats the IPN | The page wrongly shows failure | The frontend asks the backend and accepts “confirming” |
| The customer clicks pay twice, two tabs | Two attempts, both may succeed | One PENDING payment per booking (return the old URL); a second success → REFUND_PENDING |
Six more failures: browser, signature, amount, DB, gateway, refund
| Failure | Consequence if unhandled | Handling |
|---|---|---|
| The customer closes the browser | No return URL | Fine: the IPN and queries still update; the confirmation email goes through the outbox |
| Wrong signature | Fraud or an encoding bug | Reply 97, store the callback, alert when the rate rises |
| Amount mismatch | Under- or over-charging | Reply 04, freeze the order, one single place for unit conversion |
| DB down when the IPN arrives | The result is lost | Reply 99 so the gateway resends; the query job fills the gap |
| Gateway down when creating the URL | The customer can’t pay | Short timeouts, a retry message, don’t extend holds because of it |
| A refund fails | The customer doesn’t get their money back | REFUND_PENDING with retries and an idempotency key, alert after N hours |
Exercises
Exercise 1: Write the reconciliation query
Task: given a gateway_statement(statement_date, txn_ref, gateway_txn_no, amount, status) table loaded from the gateway’s file, write a query that classifies every transaction of the day as matched, missing on our side, missing on the gateway’s side, amount mismatch, or unknown.
Solution to exercise 1
The SQL in section 2.2. Points to think about:
- Filter the day in Vietnam time:
paid_atis atimestamptz, and the day boundary is 00:00+07. - Use a
FULL JOINto see gaps on both sides. - Separate “we have a payment but it didn’t succeed” from “we have no payment at all”, because they’re handled completely differently.
- Transactions around midnight can land in two files; check the next day’s file before concluding
OURS_ONLY.
Exercise 2: Design the transaction-query job
Task: describe a job that “patches” PENDING payments that never got an IPN, safe with several instances running and staying under the gateway’s rate limit.
Solution to exercise 2
- Every minute, take a batch of 50
PENDINGpayments withexpires_at < now() - interval '2 minutes'usingFOR UPDATE SKIP LOCKED(skip rows another instance has locked, so instances share batches; see the seat-holding post). - Add a
last_queried_atcolumn, updated in the selection transaction, so the next run doesn’t pick the same rows right away. - Call the query API after committing the selection transaction: never hold locks during a network call.
- Feed results into the shared
applyfunction. - Use a rate limiter on our side (a token bucket) matching the gateway’s quota.
- After N attempts with no result, alert a human.
Remember
- Explain why a late IPN must still be recorded as
SUCCEEDED, and the two ways to handle it afterwards. - Know the three ways to make late IPNs rare: URL deadline shorter than the hold, a buffer in the expiry job, asking the gateway before releasing.
- Rewrite the
FULL JOINreconciliation query and name the five result categories, and which is the most serious. - Know why the day filter must be a
+07range, not::date. - Describe how to test IPNs in CI without the sandbox.
Interview questions
Junior
What is payment reconciliation, and why do you need it when you already have IPNs?
Reconciliation compares our data (payments, the ledger) with the gateway’s data (statements, payouts) to find discrepancies. You need it because IPNs can be lost, late, or mishandled by a bug.
Only reconciliation catches “the gateway collected but we never recorded it” or “we recorded success but the gateway has nothing”. Without it, discrepancies only surface when a customer complains or when accounting closes the books.
Mid
Why must the payment URL expire before the seat hold does?
So the gateway refuses payment before the seat can be released, which makes late IPNs rare.
If they’re equal or the URL lasts longer, a customer can pay at the last moment while the job has already released the seat to someone else. Make vnp_ExpireDate shorter (URL 15 minutes, seats 20 minutes) and give the expiry job a buffer for open payments.
How do you run the transaction-query job safely on several instances?
Split batches with FOR UPDATE SKIP LOCKED, commit before any network call, and feed results to the idempotent apply function.
Each run takes a batch of overdue PENDING payments, updates last_queried_at and commits (no locks held while waiting on the gateway). It calls the query API through a rate limiter sized to the gateway’s quota. If two instances ever process the same payment, the conditional update still allows only one state transition.
What is settlement and how do you record it in the ledger?
It’s when the gateway pays what it collected into the merchant’s bank account, typically T+1, after deducting fees and refunds.
Record it as one multi-legged transaction: debit the bank for the net amount, debit a fee expense account, credit the gateway clearing account. After settlement, the clearing account’s balance should equal what hasn’t been paid out yet; a gap is a signal to reconcile.
Senior
A successful IPN arrives after the booking expired and the seat was sold to someone else. What do you do?
Record the payment as successful because the money is real, try to hold another seat in the same class at the locked-in price; if that fails, move to REFUND_PENDING and refund automatically.
Notify the customer either way. More importantly, make it rare: a URL deadline shorter than the hold, an expiry job that doesn’t release bookings with open payments, and a gateway query before releasing. Keep a metric and an alert on REFUND_PENDING counts.
How would you design reconciliation with a payment gateway?
Two rhythms: a near-real-time transaction-query job and a daily statement match; then payout reconciliation.
- Near real time: query overdue payments with no result, sharing the idempotent function with the IPN.
- Daily: load the statement, FULL JOIN it with successful payments for the Vietnam-time day, classify rows as matched, missing on our side, missing on the gateway’s side, amount mismatch, or unknown; each category has its own procedure.
- Payouts: record settlement in the ledger and check the clearing account balance.
How do you test a payment integration without depending on the gateway’s sandbox in CI?
Sign IPN parameter sets yourself with the test key and call the endpoint directly; put the gateway’s APIs behind an interface and stub them.
- Test the signature against a fixed parameter set and expected signature.
- Integration tests with Testcontainers, one BDD scenario per failure mode (success, resend, concurrency, bad signature, wrong amount, late arrival).
- Stub URL creation and queries with WireMock.
- Use the real sandbox only for manual smoke tests or a nightly run through a tunnel.
Useful phrases for interviews
- “A late IPN is still real money, so we always record it, then either re-acquire inventory or refund.”
- “The payment URL expires before the seat hold, which makes late arrivals rare instead of routine.”
- “The expiry job never releases a booking that still has a payment in flight; it asks the gateway first.”
- “The IPN is the main path and the transaction-query job is the safety net, and both feed the same idempotent function.”
- “Daily reconciliation does a full outer join between the gateway statement and our payments, cut by Vietnam-time day boundaries.”
- “‘Ours only’ is the scariest category, because it may mean we issued tickets without collecting money.”
- “We never depend on the sandbox in CI; tests sign their own IPN fixtures and hit the endpoint directly.”
- “Every row in our failure-mode table has a test scenario written in Given/When/Then.”
Notes based on the Advanced Backend course (Ronin Engineer), rewritten in my own words.