Payment System Design: Idempotency, Reconciliation, and Refunds

1. Overview

The payment problem differs from typical CRUD tasks in one key aspect: a single mistake means real money is lost. Client retries can cause customers to be charged twice. A payment gateway (PSP) may timeout even though the transaction already succeeded. Two concurrent refund requests can refund more than what was collected.

This article walks through a specific design comprising requirements, data model, API, and processing flows. It then addresses three critical challenges: idempotency, reconciliation, and refunds. After reading, you’ll be able to build the core yourself using Spring Boot 3 and PostgreSQL, complete with tests proving the system never over-refunds during concurrent disputes.

2. Requirements

2.1. Functional Requirements

• Merchant creates a payment for an Order. The system calls the PSP to deduct money from card or wallet.
• Merchant refunds in full or in part. Total refunded must not exceed total collected.
• Daily, the system reconciles internal data with the PSP’s settlement file.
• The system emits events (PaymentCaptured, RefundSucceeded, etc.) to other services like Order and Accounting.

2.2. Scale and Constraints

• Average 200 transactions/second. Flash sale peak around 5,000 transactions/second for a few minutes.
• Never charge twice for the same payment intent. This is a hard constraint.
• Prioritize consistency over availability: better to error to the client than record incorrect amounts.
• The PSP is an external system. It may be slow, timeout, or send late or duplicate webhooks.

3. Data Model

Money is always stored in the smallest unit (VND as VND, USD as cents) as BIGINT. Never use double.

CREATE TABLE payments (
    id              UUID PRIMARY KEY,
    merchant_id     TEXT        NOT NULL,
    order_id        TEXT        NOT NULL,
    amount          BIGINT      NOT NULL CHECK (amount > 0),
    currency        CHAR(3)     NOT NULL,
    status          TEXT        NOT NULL, -- CREATED, PROCESSING, CAPTURED, FAILED, UNKNOWN
    refunded_amount BIGINT      NOT NULL DEFAULT 0,
    psp_reference   TEXT,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    CHECK (refunded_amount <= amount)
);

CREATE TABLE refunds (
    id          UUID PRIMARY KEY,
    payment_id  UUID   NOT NULL REFERENCES payments(id),
    amount      BIGINT NOT NULL CHECK (amount > 0),
    status      TEXT   NOT NULL, -- PENDING, SUCCEEDED, FAILED
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE idempotency_keys (
    merchant_id   TEXT NOT NULL,
    idem_key      TEXT NOT NULL,
    request_hash  TEXT NOT NULL,
    status        TEXT NOT NULL, -- IN_PROGRESS, COMPLETED
    response_code INT,
    response_body TEXT,
    created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
    PRIMARY KEY (merchant_id, idem_key)
);

CREATE TABLE outbox (
    id         BIGSERIAL PRIMARY KEY,
    topic      TEXT  NOT NULL,
    payload    JSONB NOT NULL,
    sent_at    TIMESTAMPTZ
);

A few noteworthy points:

• CHECK (refunded_amount <= amount) is the final safety net at the database layer. If code has a bug, the database still blocks it.
• The primary key (merchant_id, idem_key) on the idempotency_keys table is the core of the deduplication mechanism.
• The outbox table enables emitting events to Kafka without needing distributed transactions (see section 6).

4. API Design

POST /v1/payments
Headers: Idempotency-Key: 7f3c1e2a-...
Body:    { "orderId": "ORD-1001", "amount": 250000, "currency": "VND", "source": "tok_visa" }
→ 201  { "id": "...", "status": "CAPTURED" }

POST /v1/payments/{id}/refunds
Headers: Idempotency-Key: 9a8b...
Body:    { "amount": 100000 }
→ 202  { "refundId": "...", "status": "PENDING" }

GET  /v1/payments/{id}

Every state-changing API must include an Idempotency-Key generated by the client. The client resends the same key on retry. Refunds return 202 Accepted because the PSP handles refunds asynchronously.

5. Idempotency: Preventing Double-Charging

5.1. The Idea

When receiving a request, we write the key to the database before doing anything else. Three outcomes are possible:

1. Key doesn’t exist yet: write succeeds, proceed with processing.
2. Key is already COMPLETED: return the stored response, don’t call PSP again.
3. Key is currently IN_PROGRESS: another request with the same key is running. Return 409 Conflict so the client retries later.

Additionally, we store a hash of the request body. If the client reuses a key with a different body (e.g., different amount), that’s a client error and we return 422.

5.2. Implementation

Dependencies needed:

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <scope>runtime</scope>
</dependency>

@Service
public class IdempotencyService {

    private final JdbcTemplate jdbc;

    public IdempotencyService(JdbcTemplate jdbc) {
        this.jdbc = jdbc;
    }

    public record StoredResponse(int code, String body) {}

    public Optional<StoredResponse> begin(String merchantId, String key, String requestHash) {
        int inserted = jdbc.update("""
            INSERT INTO idempotency_keys (merchant_id, idem_key, request_hash, status)
            VALUES (?, ?, ?, 'IN_PROGRESS')
            ON CONFLICT (merchant_id, idem_key) DO NOTHING
            """, merchantId, key, requestHash);
        if (inserted == 1) {
            return Optional.empty(); // new request, allowed to proceed
        }
        Map<String, Object> row = jdbc.queryForMap("""
            SELECT request_hash, status, response_code, response_body
            FROM idempotency_keys WHERE merchant_id = ? AND idem_key = ?
            """, merchantId, key);
        if (!requestHash.equals(row.get("request_hash"))) {
            throw new ResponseStatusException(HttpStatus.UNPROCESSABLE_ENTITY, "Key reused with different body");
        }
        if ("IN_PROGRESS".equals(row.get("status"))) {
            throw new ResponseStatusException(HttpStatus.CONFLICT, "Request is being processed");
        }
        return Optional.of(new StoredResponse((Integer) row.get("response_code"), (String) row.get("response_body")));
    }

    public void complete(String merchantId, String key, int code, String body) {
        jdbc.update("""
            UPDATE idempotency_keys SET status = 'COMPLETED', response_code = ?, response_body = ?
            WHERE merchant_id = ? AND idem_key = ?
            """, code, body, merchantId, key);
    }
}

ON CONFLICT DO NOTHING turns the “check-then-write” operation into a single atomic statement. When two requests with the same key arrive simultaneously, only one gets inserted == 1. The other falls through to the read branch and gets 409. There’s no race condition where both see the key as non-existent.

Note: Don’t use Redis SETNX alone as the source of truth for payment idempotency. A Redis failover or key eviction means you lose track, and money gets charged twice. Redis should only be a caching layer in front; the database is the arbiter.

5.3. Idempotency on the PSP Side Too

Your requests are protected, but calls from you to the PSP may also be retried. Most PSPs support their own idempotency key. Pass the payments.id itself as that key:

PspResult result = pspClient.charge(new ChargeRequest(
        payment.id().toString(), // idempotency key sent to PSP
        payment.amount(),
        payment.currency(),
        sourceToken));

When a call to the PSP times out, you don’t know whether money was deducted. Don’t mark it FAILED. Set the status to UNKNOWN, then have a job query the PSP again using payments.id. Marking FAILED and letting the client retry with a new key is the classic path to double-charging.

6. Database-Kafka Consistency: Transactional Outbox

After collecting payment, we need to update payments and emit a PaymentCaptured event. If the database write succeeds but Kafka send fails (or vice versa), the Order service state drifts. The solution is to write the event to the outbox table in the same transaction:

@Transactional
public void markCaptured(UUID paymentId, String pspReference) {
    jdbc.update("UPDATE payments SET status = 'CAPTURED', psp_reference = ? WHERE id = ? AND status IN ('PROCESSING','UNKNOWN')",
            pspReference, paymentId);
    jdbc.update("INSERT INTO outbox (topic, payload) VALUES ('payment-events', ?::jsonb)",
            """
            {"type":"PaymentCaptured","paymentId":"%s"}""".formatted(paymentId));
}

A relay (periodic job or Debezium CDC) reads rows with sent_at IS NULL, sends them to Kafka, then updates sent_at. The relay may send duplicates, so consumers must be idempotent, e.g., tracking processed paymentId. The status IN (...) condition in the UPDATE prevents duplicate webhooks from the PSP from overwriting a different terminal state.

7. Refunds and Concurrent Disputes

7.1. The Problem

Payment is 100,000. A support agent and an automated job both submit a 60,000 refund request. If the code reads refunded_amount, checks, then writes, both see 0 + 60,000 <= 100,000 and both pass. Result: you refund 120,000.

7.2. Solution: Conditional Update

Combine the check and update into a single conditional UPDATE statement:

@Service
public class RefundService {

    private final JdbcTemplate jdbc;

    public RefundService(JdbcTemplate jdbc) {
        this.jdbc = jdbc;
    }

    @Transactional
    public UUID requestRefund(UUID paymentId, long amount) {
        int reserved = jdbc.update("""
            UPDATE payments SET refunded_amount = refunded_amount + ?
            WHERE id = ? AND status = 'CAPTURED' AND refunded_amount + ? <= amount
            """, amount, paymentId, amount);
        if (reserved == 0) {
            throw new IllegalStateException("Refund exceeds refundable amount or payment not captured");
        }
        UUID refundId = UUID.randomUUID();
        jdbc.update("INSERT INTO refunds (id, payment_id, amount, status) VALUES (?, ?, ?, 'PENDING')",
                refundId, paymentId, amount);
        jdbc.update("INSERT INTO outbox (topic, payload) VALUES ('refund-commands', ?::jsonb)",
                """
                {"refundId":"%s","paymentId":"%s","amount":%d}""".formatted(refundId, paymentId, amount));
        return refundId;
    }
}

PostgreSQL locks the payments row during UPDATE. The second transaction must wait for the first to commit. Then it re-evaluates the WHERE condition on the new data, sees 60,000 + 60,000 > 100,000, and gets reserved == 0. This is default behavior at READ COMMITTED isolation; no need for SERIALIZABLE.

We “reserve” the amount first, then call the PSP asynchronously via the outbox. If the PSP rejects the refund, the consumer returns the reserved amount:

UPDATE payments SET refunded_amount = refunded_amount - :amount WHERE id = :paymentId;
UPDATE refunds  SET status = 'FAILED' WHERE id = :refundId AND status = 'PENDING';

Both must be in the same transaction. The status = 'PENDING' condition ensures a duplicate failed webhook doesn’t return the reserved amount twice. For extra safety, only run the first if the second updated exactly one row.

7.3. Concurrency Test

Test dependencies:

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-testcontainers</artifactId>
    <scope>test</scope>
</dependency>
<dependency>
    <groupId>org.testcontainers</groupId>
    <artifactId>postgresql</artifactId>
    <scope>test</scope>
</dependency>

@SpringBootTest
@Testcontainers
class RefundServiceTest {

    @Container
    @ServiceConnection
    static PostgreSQLContainer<?> postgres = new PostgreSQLContainer<>("postgres:16-alpine");

    @Autowired RefundService refundService;
    @Autowired JdbcTemplate jdbc;

    @Test
    void concurrentRefunds_neverExceedCapturedAmount() throws Exception {
        UUID paymentId = UUID.randomUUID();
        jdbc.update("""
            INSERT INTO payments (id, merchant_id, order_id, amount, currency, status)
            VALUES (?, 'shop-1', 'ORD-1001', 100000, 'VND', 'CAPTURED')
            """, paymentId);

        Callable<Boolean> refund60k = () -> {
            try {
                refundService.requestRefund(paymentId, 60_000);
                return true;
            } catch (IllegalStateException e) {
                return false;
            }
        };

        List<Boolean> results;
        try (var executor = Executors.newVirtualThreadPerTaskExecutor()) {
            results = executor.invokeAll(List.of(refund60k, refund60k))
                    .stream().map(Future::resultNow).toList();
        }

        assertThat(results).containsExactlyInAnyOrder(true, false);
        Long refunded = jdbc.queryForObject(
                "SELECT refunded_amount FROM payments WHERE id = ?", Long.class, paymentId);
        assertThat(refunded).isEqualTo(60_000L);
    }
}

The test assumes the schema from section 3 is loaded via src/test/resources/schema.sql (with spring.sql.init.mode=always) or Flyway. Two virtual threads submit refunds simultaneously. Expected result is always stable:

results          = [true, false]   (any order)
refunded_amount  = 60000

Try replacing the UPDATE with a read-then-compare in Java, and you’ll see the test flake. That’s the race condition this design eliminates.

8. Reconciliation

No matter how careful, internal data and the PSP’s can diverge. Webhooks can be lost, transactions may remain UNKNOWN, or the PSP may issue refunds due to chargebacks. Reconciliation is the final line of defense.

Daily flow:

1. Download the PSP’s settlement file and load it into a temporary table psp_settlements(psp_reference, amount, type).
2. Match it against internal data using FULL OUTER JOIN.
3. Write discrepancies to a recon_breaks table for auto or manual handling.

INSERT INTO recon_breaks (psp_reference, internal_amount, psp_amount, reason)
SELECT COALESCE(p.psp_reference, s.psp_reference),
       p.amount,
       s.amount,
       CASE
           WHEN p.psp_reference IS NULL THEN 'MISSING_INTERNAL'
           WHEN s.psp_reference IS NULL THEN 'MISSING_AT_PSP'
           ELSE 'AMOUNT_MISMATCH'
       END
FROM (SELECT * FROM payments
      WHERE status = 'CAPTURED' AND created_at >= :day AND created_at < :day + INTERVAL '1 day') p
FULL OUTER JOIN (SELECT * FROM psp_settlements WHERE type = 'CHARGE') s
       ON p.psp_reference = s.psp_reference
WHERE p.psp_reference IS NULL
   OR s.psp_reference IS NULL
   OR p.amount <> s.amount;

Meaning of each break type:

• MISSING_INTERNAL: PSP collected money but you have no CAPTURED record. Usually an UNKNOWN transaction or lost webhook. Handle by updating status or refunding if the order was cancelled.
• MISSING_AT_PSP: You recorded collection but the PSP has none. Serious: you may have shipped without receiving payment.
• AMOUNT_MISMATCH: Usually due to fees, exchange rates, or rounding bugs.

Note: Mind the time window. A transaction at 23:59 may appear in next day’s settlement file. Reconcile with T+1 or T+2 lag, and escalate a break to humans only after it persists across two consecutive runs.

9. Traffic Spikes

During flash sales, load spikes 25×. A few techniques help:

• Rate-limit per merchant using Redis (INCR + EXPIRE per-second window). This protects the system from one noisy merchant. Here Redis only enforces limits; it doesn’t decide correctness.
• Separate sync and async paths. The sync path only writes the idempotency key, creates the payment, and calls PSP authorize. Refunds, email, and accounting updates all go through outbox → Kafka. Partition Kafka by paymentId to preserve event order per payment.
• Circuit breaker and short timeouts when calling the PSP (e.g., Resilience4j). When the PSP is slow, fail fast and return 503 with Retry-After, rather than exhausting thread pools. Client retries with the same Idempotency-Key are safe thanks to section 5.
• Clean the idempotency_keys table periodically (keep 24–72 hours) and partition by date to avoid bloat slowing down INSERT.

10. Common Pitfalls

• Using double/float for money. Use long in the smallest unit, or BigDecimal if you need exchange rates.
• Treating timeout as failure. Timeout means unknown, so set UNKNOWN and query the PSP again.
• Generating the idempotency key on the server. The key must be client-generated and preserved across retries.
• Checking refund balance in Java then UPDATE. Let the database do it in a single statement.
• Sending to Kafka inside @Transactional hoping it rolls back with the database. It won’t. Use outbox.
• Skipping reconciliation because “the code is correct.” External systems aren’t under your control.

11. Conclusion

This article designed the core of a payment system on three principles. First, idempotency is ensured by unique constraints in the database, both in your API and when calling the PSP. Second, concurrent refund disputes are resolved via conditional updates plus CHECK constraints, backed by concurrency tests. Third, consistency with the outside world relies on the transactional outbox and daily reconciliation.

The common thread across all three: push important invariants down to the database layer, where they’re enforced atomically. And always assume the network, PSP, and your code can fail—have detection and repair mechanisms ready.