A Ship-It Guide to Idempotency for SaaS APIs Using .NET and SQL

Hossein Esmati provides a complete, code-level playbook for preventing duplicate API requests, including the exact SQL schema and middleware logic for .NET applications handling critical transactions.

A user clicks “Pay” and, seeing no immediate response, clicks again. This action can result in a double charge, a duplicate database record, and a support ticket. This is the class of problem solved by idempotency, a core reliability feature for any API handling payments or state-changing operations. It ensures that making the same request multiple times has the same effect as making it once.

Hossein Esmati published a detailed, practical guide for implementing this pattern in a .NET and Azure environment. The playbook provides specific code artifacts, from the database schema to the API contract, for building a robust system that prevents duplicate processing.

The API contract is the entry point

The implementation begins at the HTTP layer. The contract requires clients to send a unique Idempotency-Key in the header for all POST requests that create resources. For PUT and PATCH requests, which update existing resources, the guide recommends using natural keys like the resource URI combined with concurrency controls like ETags, rather than a custom idempotency key.

When the server detects a replayed request using a key it has already processed, it does not re-execute the business logic. Instead, it returns the stored original response and adds an Idempotency-Replayed: true header. This signals to the client that the request was a duplicate and the returned response is from the original execution.

A 7-column table for state

The core of the system is a persistence layer that tracks the state of incoming requests. Esmati provides a specific schema for an Azure SQL table named Idempotency. The core mechanism is a unique constraint on the tenant ID and the client-provided idempotency key.

The proposed table includes seven columns:

• TenantId: To scope keys within a multi-tenant system.
• IdempotencyKey: The client-provided unique identifier.
• RequestHash: A SHA-256 hash of the request to prevent a key from being reused with a different payload.
• StatusCode: The HTTP status code of the original response.
• ResponseBody: The full body of the original response.
• CreatedAtUtc: Timestamp for when the key was first seen.
• CompletedAtUtc: Timestamp for when processing finished.

A composite primary key on (TenantId, IdempotencyKey) enforces uniqueness at the database level, which is the foundation of the entire de-duplication strategy.

Middleware ties it all together

The logic is implemented in an ASP.NET Core middleware component that intercepts incoming requests. Before passing a request to the application's business logic, the middleware attempts to insert a record into the Idempotency table with the key from the request header.

If the database insert succeeds, the request is new. The middleware allows the request to proceed, and upon completion, it updates the corresponding table row with the response status code and body. If the insert fails because of the unique key constraint, the request is a duplicate. The middleware then retrieves the stored response from the table and returns it directly, bypassing the business logic entirely.

What We'd Change

This playbook is a strong foundation for a production system. However, founders implementing it should consider several operational factors not fully detailed in the original post.

Define your TTL explicitly

The guide mentions a short Time To Live (TTL) for the stored responses but does not specify a duration. This is a critical business decision. Stripe, for example, maintains idempotency keys for 24 hours. A shorter TTL reduces storage costs, but a longer one provides a better experience for clients that may retry requests over a longer period. This value should be chosen deliberately and documented for API consumers.

This pattern is portable, not proprietary

The implementation uses .NET and Azure SQL, but the underlying pattern is portable. The essential database features are a unique constraint and, ideally, a TTL mechanism. A team using PostgreSQL could implement this with an ON CONFLICT clause. An AWS-based team could use DynamoDB with conditional writes and its built-in TTL attribute on items. The specific code changes, but the architectural principle remains the same.

Mind the response body storage cost

Storing the full ResponseBody for every POST request, even for 24 hours, can become a significant cost and performance factor, especially for APIs with large response payloads. The provided schema uses VARBINARY(MAX). Teams should evaluate whether storing the full response is necessary for all endpoints. Alternatives include storing responses only for specific critical endpoints or storing a pointer to the response in cheaper object storage like S3 or Azure Blob Storage.

Landing

Idempotency is not an edge case or a technical luxury. It is a fundamental component of a reliable, trustworthy API. For any SaaS product that handles financial transactions, critical infrastructure, or any operation where duplication would be harmful, implementing a robust idempotency layer is a requirement for production readiness. This playbook provides a clear, actionable path to building that trust with both developers and end-users.

The investor read

Idempotency is a key non-functional requirement that signals engineering discipline. For investors evaluating early-stage SaaS, particularly in fintech, logistics, or infrastructure, its presence is a strong positive indicator. It suggests the team understands how to build robust, fault-tolerant systems. Its absence in a transactional API is a significant red flag, implying future scalability problems and a high customer support burden. While not a growth feature, it is a critical component of technical due diligence and de-risks the investment by demonstrating a mature approach to product development.

Pull quote: “The core mechanism is a unique constraint on the tenant ID and the client-provided idempotency key.”