Idempotent Settlement Engine with Redis and Oracle Partitioning

The Context: "The 3 AM Trigger"

It was January 2024. A routine marketing push at "NexusPay" (name changed) led to a massive influx of recurring subscription renewals hitting the main payment gateway right around midnight UTC. The systems handled the traffic fine—until the network backbone blipped for three entire seconds. Upstream API clients, not getting their fast HTTP 200s, started triggering aggressive, poorly configured exponential backoffs that were basically just while(true) loops. Within two minutes, our settlement engine was bombarded with the exact same three million transactions five times over. Duplicate ledgers, angry merchants, and a 48-hour manual rollback nightmare. A complete baigan ka architecture failure in distributed resiliency. We needed unbreakable idempotency.

The Problem Statement

Our constraints going forward:

• Throughput: Support spikes up to 40,000 settlement requests per second.
• Idempotency Window: Guarantee 100% duplicate rejection within a 72-hour sliding window.
• Database Overhead: Offload the duplicate rejection from the primary Oracle Database. Oracle CPU should only be used for actual business logic, not for discarding API noise.
• Storage Profile: Ensure LOBs and ledger histories don't bloat the primary indexes.

Visual Architecture

+-RESETFULLSCREEN

The Core Logic / Process

Data Flow

Every request into the settlement engine now requires a UUID Idempotency-Key header. The Spring WebFlux application first queries the Redis Cluster. If the key exists and holds a completed state, it immediately returns the cached HTTP response, entirely bypassing the JVM business logic and Oracle database.

If the key is new, we issue a SETNX distributed lock in Redis with a 30-second TTL to prevent parallel race conditions on the same key. The request proceeds to the Oracle database.

Concurrency & Locking

In Oracle, we partitioned the SETTLEMENT_LEDGER table by SETTLEMENT_MONTH. This ensures that active inserts only write to the hot partition, whereas historical lookups traverse older segments without contention. For storing the massive JSON receipt payloads, we migrated from basic BLOB fields to Oracle SecureFiles, reducing redo-log bloat and enabling deduplication at the DB layer.

Failure Modes

If Redis goes down, we degrade gracefully but aggressively: the application falls back to querying the Oracle database for the Idempotency-Key (which is indexed with a specific B-tree). Latency increases from 5ms to 40ms, but consistency remains intact. If the settlement fails midway, the Redis lock expires, and the next retry cleans up the state before re-attempting.

The "Baigan" Take (Expert Insights)

Look, everyone knows you should use Redis for idempotency. But here is the reality: what happens if the application crashes after Oracle commits the transaction but before it updates the Redis cache? The next retry will think the transaction hasn't happened.

To fix this, we implemented a two-phase check. The Idempotency-Key is persisted as a unique constraint column directly in the Oracle ledger table. If Redis fails us (cache eviction or crash), the Oracle DB UNIQUE CONSTRAINT violation rescues the transaction, forcing the app to re-sync the cache and return the successful state. Defense in depth.

Performance Checklist

• Configure Redis SET command with NX and EX flags to prevent race conditions.
• Ensure the Idempotency-Key in Oracle is a VARCHAR2(36) with a UNIQUE index.
• Implement Oracle Table Partitioning by date to optimize I/O and index depth.
• Implement Spring's @Transactional carefully to ensure Redis updates only happen post-DB commit.