The Anatomy of Payment Idempotency: Preventing Double Charges without Redis

Request 1 (Drop) ──> [ Charge $200 ] ──X (Network Drop)
Request 2 (Retry) ─> [ Charge $200 ] ──> Customer billed $400! 🚨

                         Incoming Request + Idempotency-Key
                                         │
                                         ▼
                            Compute SHA-256 Payload Hash
                                         │
                                         ▼
                      Atomic INSERT INTO idempotency_records
                                         │
                 ┌───────────────────────┴───────────────────────┐
                 ▼                                               ▼
        [ Lock Acquired ]                               [ Key Already Exists ]
                 │                                               │
       Execute Payment Rails                               Check Record Status
                 │                                               │
     ┌───────────┴───────────┐                         ┌─────────┴─────────┐
     ▼                       ▼                         ▼                   ▼
 [ Success ]             [ Failure ]             [ COMPLETED ]        [ PENDING ]
Save 200 + Response      Delete Record          Replay Cached JSON    Poll DB (up to 5s)

CREATE TABLE IF NOT EXISTS idempotency_records (
   id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
   merchant_id UUID NOT NULL REFERENCES merchants(id) ON DELETE CASCADE,
   idempotency_key VARCHAR(255) NOT NULL,
   request_path VARCHAR(64) NOT NULL DEFAULT '/v1/payments',
   request_method VARCHAR(10) NOT NULL DEFAULT 'POST',
   request_hash VARCHAR(64) NOT NULL, -- SHA-256 digest of the request payload
   status VARCHAR(20) NOT NULL DEFAULT 'PENDING', -- PENDING | COMPLETED
   locked_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

   response_code INT,
   response_headers JSONB,
   response_body JSONB,
   created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
   updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
   expires_at TIMESTAMPTZ NOT NULL DEFAULT NOW() + INTERVAL '24 hour',
   CONSTRAINT uq_merchant_idempotency_key UNIQUE (merchant_id, idempotency_key)
);

CREATE INDEX idx_idem_merchant_lookup ON idempotency_records(merchant_id, idempotency_key);
CREATE INDEX idx_idem_expires_at ON idempotency_records(expires_at) WHERE status = 'COMPLETED';

namespace Features.Idempotency;
public abstract record IdempotencyResult
{
   /// <summary>The lock was acquired; caller should proceed with execution.</summary>
   public sealed record Acquired(string RequestHash) : IdempotencyResult;
   /// <summary>Request was already processed; replay this cached response.</summary>
   public sealed record Replay(int StatusCode, string ResponseBody) : IdempotencyResult;
   /// <summary>Key was reused, but payload differs from original request.</summary>
   public sealed record PayloadMismatch : IdempotencyResult;
   /// <summary>Concurrent request did not complete within the polling window.</summary>
   public sealed record Timeout : IdempotencyResult;
}

private static string ComputeSha256(string rawData)
{
   byte[] bytes = SHA256.HashData(Encoding.UTF8.GetBytes(rawData));
   return Convert.ToHexString(bytes).ToLowerInvariant();
}

public async Task<IdempotencyResult> BeginOrWaitAsync(
   Guid merchantId,
   string idempotencyKey,
   string requestPayload,
   CancellationToken ct)
{
   string requestHash = ComputeSha256(requestPayload);
   await using var conn = await dataSource.OpenConnectionAsync(ct);
   const string insertSql = """
       INSERT INTO idempotency_records(
           merchant_id,
           idempotency_key,
           request_hash,
           status,
           created_at,
           expires_at
       )
       VALUES(
           $1, $2, $3, 'PENDING', NOW(), NOW() + INTERVAL '24 hours'
       )
       ON CONFLICT (merchant_id, idempotency_key) DO NOTHING;
   """;
   await using (var insertCmd = new NpgsqlCommand(insertSql, conn))
   {
       insertCmd.Parameters.AddWithValue(merchantId);
       insertCmd.Parameters.AddWithValue(idempotencyKey);
       insertCmd.Parameters.AddWithValue(requestHash);
       int rowsAffected = await insertCmd.ExecuteNonQueryAsync(ct);
       // If a row was inserted, WE OWN THE LOCK!
       if (rowsAffected > 0)
       {
           return new IdempotencyResult.Acquired(requestHash);
       }
   }

   // ... If rowsAffected == 0, another request already inserted the key!

 var deadline = DateTime.UtcNow + TimeSpan.FromSeconds(5);
   var pollingInterval = TimeSpan.FromMilliseconds(100);
   while (DateTime.UtcNow < deadline)
   {
       ct.ThrowIfCancellationRequested();
       const string selectSql = """
           SELECT request_hash, status, response_code, response_body::text
           FROM idempotency_records
           WHERE merchant_id = $1 AND idempotency_key = $2
           LIMIT 1;
       """;
       await using var selectCmd = new NpgsqlCommand(selectSql, conn);
       selectCmd.Parameters.AddWithValue(merchantId);
       selectCmd.Parameters.AddWithValue(idempotencyKey);
       await using var reader = await selectCmd.ExecuteReaderAsync(ct);
       if (await reader.ReadAsync(ct))
       {
           string existingHash = reader.GetString(0);
           string status = reader.GetString(1);
           // 1. Check for payload tampering / mismatch
           if (!string.Equals(existingHash, requestHash, StringComparison.OrdinalIgnoreCase))
           {
               return new IdempotencyResult.PayloadMismatch();
           }
           // 2. If the first request has finished, replay the cached response!
           if (status == "COMPLETED")
           {
               int code = reader.GetInt32(2);
               string body = reader.GetString(3);
               return new IdempotencyResult.Replay(code, body);
           }
       }
       // Wait 100ms before checking again
       await Task.Delay(pollingInterval, ct);
   }
   // If the active request took longer than 5 seconds, return timeout
   return new IdempotencyResult.Timeout();
}

public async Task CompleteAsync(
   Guid merchantId,
   string idempotencyKey,
   int statusCode,
   string responseBody,
   CancellationToken ct)
{
   const string updateSql = """
       UPDATE idempotency_records
       SET status = 'COMPLETED',
           response_code = $1,
           response_body = $2::jsonb,
           updated_at = NOW()
       WHERE merchant_id = $3 AND idempotency_key = $4;
   """;
   await using var conn = await dataSource.OpenConnectionAsync(ct);
   await using var cmd = new NpgsqlCommand(updateSql, conn);
   cmd.Parameters.AddWithValue(statusCode);
   cmd.Parameters.AddWithValue(responseBody);
   cmd.Parameters.AddWithValue(merchantId);
   cmd.Parameters.AddWithValue(idempotencyKey);
   await cmd.ExecuteNonQueryAsync(ct);
}

public async Task FailAsync(Guid merchantId, string idempotencyKey, CancellationToken ct)
{
   const string deleteSql = """
       DELETE FROM idempotency_records
       WHERE merchant_id = $1 AND idempotency_key = $2 AND status = 'PENDING';
   """;
   await using var conn = await dataSource.OpenConnectionAsync(ct);
   await using var cmd = new NpgsqlCommand(deleteSql, conn);
   cmd.Parameters.AddWithValue(merchantId);
   cmd.Parameters.AddWithValue(idempotencyKey);
   await cmd.ExecuteNonQueryAsync(ct);
}

app.MapPost("/v1/payments", async (
   [FromHeader(Name = "Idempotency-Key")] string? idempotencyKey,
   [FromBody] CreateChargeRequest request,
   IdempotencyService idempotencyService,
   PaymentProcessor paymentProcessor,
   CancellationToken ct) =>
{
   if (string.IsNullOrWhiteSpace(idempotencyKey))
   {
       return Results.BadRequest(new { error = "Idempotency-Key header is required." });
   }
   var merchantId = GetCurrentMerchantId();
   string payloadJson = JsonSerializer.Serialize(request);
   // 1. Begin or Wait
   var result = await idempotencyService.BeginOrWaitAsync(
       merchantId, idempotencyKey, payloadJson, ct);
   // 2. Handle Edge Cases
   if (result is IdempotencyResult.PayloadMismatch)
   {
       return Results.UnprocessableEntity(new {
           error = "Idempotency key reused with a different request payload."
       });
   }
   if (result is IdempotencyResult.Timeout)
   {
       return Results.Conflict(new {
           error = "Another request with this key is currently in progress. Please retry shortly."
       });
   }
   // 3. Replay Cached Response
   if (result is IdempotencyResult.Replay replay)
   {
       return Results.Content(replay.ResponseBody, "application/json", statusCode: replay.StatusCode);
   }
   // 4. Execute the Actual Payment
   try
   {
       var chargeResponse = await paymentProcessor.ProcessChargeAsync(merchantId, request, ct);
       // 5. Complete & Cache Response
       await idempotencyService.CompleteAsync(
           merchantId,
           idempotencyKey,
           StatusCodes.Status201Created,
           JsonSerializer.Serialize(chargeResponse),
           ct);
       return Results.Created($"/v1/payments/{chargeResponse.Id}", chargeResponse);
   }
   catch (Exception ex)
   {
       // 6. Fail & Unlock
       await idempotencyService.FailAsync(merchantId, idempotencyKey, CancellationToken.None);
       throw;
   }
});

-- Allow acquiring the lock if previous lock is abandoned for > 2 minutes
INSERT INTO idempotency_records (...)
...
ON CONFLICT (merchant_id, idempotency_key)
DO UPDATE SET locked_at = NOW(), status = 'PENDING'
WHERE idempotency_records.status = 'PENDING'
 AND idempotency_records.locked_at < NOW() - INTERVAL '2 minutes';