Day 2: Persist Transaction Data to Postgres — with Idempotent Writes

Day 2: Persist Transaction Data to Postgres — with Idempotent Writes

Welcome back, engineer. Yesterday, we bootstrapped our Node.js Express API, giving it a /transactions endpoint that could process incoming requests. It was a good start, but our transaction data was ephemeral, living only as long as our server process. Today, we fix that. We'll connect our API to a real database, PostgreSQL, to ensure our transaction data persists.

But persistence alone isn't enough. In distributed systems, network failures, server restarts, and client-side timeouts are not "edge cases" — they are the everyday reality. This chaos often leads clients to retry requests. If your server isn't prepared, a retried payment request can turn into a double charge, a retried order into duplicate items, or a retried data write into inconsistent analytics. This is how systems lose trust and, sometimes, millions of dollars.

Today, you'll learn to handle this chaos head-on by implementing idempotent writes. By the end of this lesson, your transaction API will not only persist data to PostgreSQL but will also gracefully handle retried requests without creating duplicate records, a crucial step toward building a truly robust service.

The Problem: When "At-Least-Once" Becomes "At-Least-Twice"

Many fundamental building blocks of distributed systems — message queues like Kafka, network protocols like TCP, and even HTTP clients — offer "at-least-once" delivery semantics. This means a message or request is guaranteed to be delivered at least once, but may be delivered multiple times.

Imagine a user attempts to make a payment. Their client sends a POST /transactions request.

1. 1.The server receives the request, processes the payment logic, and successfully writes the transaction to the database.

2. 2.Just as the server is about to send back the success response, a transient network glitch occurs, or the server process crashes.

3. 3.The client never receives a response and, naturally, retries the request.

Without a mechanism to detect and handle this retry, the server will process the request a second time, creating a duplicate transaction in the database. This isn't theoretical; this exact scenario has led to costly errors in payment gateways and financial systems globally. For instance, early versions of some payment processors struggled with this, leading to customer complaints and complex reconciliation processes. The core issue is that the effect of the operation (creating a transaction) is not idempotent by default.

The Solution: Idempotency with a Key

An operation is idempotent if applying it multiple times produces the same result as applying it once. For our transaction API, this means: if you send the exact same POST /transactions request twice, only one transaction record should be created.

How do we achieve this? We introduce an Idempotency Key. This is a unique identifier generated by the client for each logical operation. The client sends this key as a header (e.g., Idempotency-Key: <unique-uuid>) with its request.

When our server receives a request:

1. 1.It extracts the Idempotency-Key from the header.

2. 2.It checks if a transaction with that Idempotency-Key has already been processed and successfully recorded in the database.

3. 3.If it has, the server simply returns the result of the original successful operation. It does not re-process or re-create the transaction.

4. 4.If it hasn't, the server processes the request, records the transaction, and crucially, associates the Idempotency-Key with the newly created transaction before sending a success response.

This ensures that even if the client retries, the system "remembers" it already handled that specific logical operation and avoids duplication.

Architecture: Adding Postgres to the Mix

Today, we're adding PostgreSQL as our persistent storage layer. Our Node.js Express application will connect to it using the pg client library.

Diagram 1: Component Architecture. The client sends requests with an Idempotency-Key to the Node.js API, which then interacts with PostgreSQL for persistent storage and idempotent checks.

The Core: INSERT ... ON CONFLICT

PostgreSQL offers a powerful INSERT ... ON CONFLICT statement (often called "UPSERT"). This allows us to attempt an insert, and if a conflict arises on a unique constraint (like our idempotency_key), we can specify an alternative action, such as DO NOTHING or DO UPDATE. For idempotency, we'll DO NOTHING if the key already exists, then SELECT the existing record.

First, we need a table that supports this. Our transactions table will have an idempotency_key column with a UNIQUE constraint.

CREATE TABLE transactions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    idempotency_key VARCHAR(255) UNIQUE NOT NULL,
    amount NUMERIC(10, 2) NOT NULL,
    currency VARCHAR(3) NOT NULL,
    status VARCHAR(50) DEFAULT 'pending',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

This schema is critical. The idempotency_key column is UNIQUE NOT NULL, making it impossible to store two transactions with the same key. The id is still a UUID, serving as the primary identifier for the transaction itself.

Next, in our API endpoint, the logic will look something like this:

// Inside your POST /transactions handler
const idempotencyKey = req.headers['idempotency-key'];
if (!idempotencyKey) {
    return res.status(400).json({ error: 'Idempotency-Key header is required.' });
}

try {
    // Attempt to insert the transaction
    const result = await pool.query(
        `INSERT INTO transactions (idempotency_key, amount, currency)
         VALUES ($1, $2, $3)
         ON CONFLICT (idempotency_key) DO NOTHING
         RETURNING id, idempotency_key, amount, currency, status, created_at`,
        [idempotencyKey, amount, currency]
    );

    if (result.rows.length > 0) {
        // New transaction created successfully
        return res.status(201).json(result.rows[0]);
    } else {
        // Conflict occurred (key already exists), retrieve the existing transaction
        const existingTx = await pool.query(
            `SELECT id, idempotency_key, amount, currency, status, created_at
             FROM transactions
             WHERE idempotency_key = $1`,
            [idempotencyKey]
        );
        // Return the existing transaction, indicating successful idempotent processing
        return res.status(200).json(existingTx.rows[0]);
    }
} catch (error) {
    console.error('Error processing transaction:', error);
    res.status(500).json({ error: 'Failed to process transaction.' });
}

This snippet is the heart of our idempotency. If the INSERT succeeds, we return 201 Created with the new transaction. If ON CONFLICT triggers DO NOTHING, result.rows will be empty. In that case, we explicitly SELECT the existing transaction and return it with a 200 OK status, signaling that the operation was already completed.

Control Flow for an Idempotent Write

Diagram 2: Flowchart for an Idempotent Write. The API checks for the Idempotency-Key in the database. If found, it returns the existing result; otherwise, it creates a new record.

State Changes for a Request

Diagram 3: State Machine for a single request with an Idempotency-Key. A new request transitions to processing. If successful, it completes. Subsequent requests with the same key are directly mapped to the Completed state, ensuring no duplicate processing.

The Failure Demo: Retries Without Duplicates

Now for the fun part: deliberately breaking things to prove our design. We'll simulate a client retry by sending a request, killing our server after the database write but before the response, and then sending the same request again. Your system should gracefully handle this, creating only one transaction record.

Before today, the system could not reliably persist transaction data to a database without risking duplicates on retries, and its data was ephemeral (in-memory). After today it can persist transaction data to PostgreSQL, and critically, it handles retried requests for the same transaction <em style="color: #475569; font-style: italic;">without</em> creating duplicate records, ensuring data integrity.

Here's how we'll prove it:

1. 1.Start the service and database.

2. 2.Send the first request:

   bashCopy

   curl -X POST -H 'Content-Type: application/json' -H 'Idempotency-Key: my-unique-tx-001' -d '{"amount": 100.50, "currency": "USD"}' http://localhost:3000/transactions
   

   *Expected output:* A 201 Created response with the transaction details.

3. 3.Immediately kill the Node.js server. (Simulates a crash after DB write, before response).

4. 4.Send the exact same request again:

   bashCopy

   curl -X POST -H 'Content-Type: application/json' -H 'Idempotency-Key: my-unique-tx-001' -d '{"amount": 100.50, "currency": "USD"}' http://localhost:3000/transactions
   

   *Expected output:* A 200 OK response with the *same* transaction details. The 200 OK indicates that the operation was already completed.

5. 5.Verify in PostgreSQL:

   bashCopy

   psql -U user -d transactions_db -h localhost -p 5432 -c "SELECT idempotency_key, amount, currency, created_at FROM transactions;"
   

   *Expected output:* You should see *only one row* with idempotency_key: my-unique-tx-001.
   

This demonstrates that even with a simulated failure and retry, our database remains consistent, and no duplicate transaction is created.

Production Considerations and Trade-offs

While our laptop setup works, real-world systems deal with higher stakes and scale.

1. 1.Idempotency Key Storage: We're currently storing idempotency keys directly in the transactions table. This is simple and highly consistent. For extremely high-throughput systems, checking a database for every request can add latency. Alternatives include:

• Dedicated Idempotency Store (e.g., Redis): A fast in-memory cache like Redis can store idempotency_key -> response_payload mappings. This is faster but introduces another dependency and consistency challenges (what if Redis fails or the DB write succeeds but Redis update fails?). For most services, the database approach is perfectly adequate and more robust.

• Trade-off: Simplicity and strong consistency (DB) vs. lower latency at very high scale (Redis + complex consistency).

1. 1.Idempotency Key Expiration: Idempotency keys should not live forever. Eventually, you'll want to clean up old keys to prevent your database (or Redis) from growing indefinitely. How long should they live?

• Consider client retry windows: If clients typically retry for 60 seconds, your keys should persist for at least that long. A common practice is to keep them for a few hours or even a few days, balancing storage cost with the longest expected retry/reconciliation window.

• Implementation: Add a TTL (Time-To-Live) or expires_at column to your table and run a background job to periodically delete expired keys.

1. 1.Returning the Cached Response: Our current implementation returns the existing transaction details. For a truly robust idempotent API, you ideally want to return the exact same HTTP response (status code, headers, body) as the original successful request. This would require storing the full HTTP response alongside the idempotency key. This is more complex but ensures clients receive a consistent experience on retries. For this lesson, returning the existing transaction details is sufficient and simpler.

Assignment: Extend Idempotency to Status Updates

Our current idempotency protects against creating duplicate new transactions. But what if we want to idempotently update a transaction's status (e.g., from pending to completed)?

Your task:
Modify the API to support an idempotent PUT /transactions/:id/status endpoint. This endpoint should allow clients to update the status of a transaction (e.g., from pending to completed). If the client retries the same status update request (with an Idempotency-Key), the system should ensure the status is updated only once, and subsequent identical requests should return the currently known status without re-processing.

Success Criteria:

1. 1.You can successfully update a transaction's status via PUT /transactions/:id/status with an Idempotency-Key.

2. 2.If you retry the exact same PUT request with the same Idempotency-Key, the transaction's status in the database does not change again (if it was already updated), and the API returns a 200 OK with the current status.

3. 3.If you try to update the status with a different Idempotency-Key (even if the target status is the same), it should be treated as a new, distinct update operation.

Solution Hints

• You'll need a new table, or modify your existing transactions table, to store idempotency_key specifically for update operations, or perhaps a separate idempotent_requests table that records the idempotency_key along with the resource_id (transaction ID) and the operation_type (e.g., 'update_status').

• The INSERT ... ON CONFLICT pattern still applies. You'd use it to record the fact that a specific update operation (identified by its Idempotency-Key) has been attempted or completed for a given transaction.

• Consider the state machine: A transaction's status can only move forward (e.g., pending -> completed, not completed -> pending). Your idempotent update logic should respect these transitions. If a client tries to move completed to pending, that's an invalid state transition, not an idempotent success.

• The ON CONFLICT clause can be used with DO UPDATE SET for this scenario, specifically to update a record in an idempotent_requests table that tracks the status of the idempotent operation itself.

Today, you've built a foundational piece of a robust distributed system: persistent storage with idempotent writes. This mechanism is critical for preventing data corruption and ensuring reliability in the face of inevitable failures. Tomorrow, we'll layer another essential defense: authentication. We'll secure our API using JSON Web Tokens (JWTs), ensuring that only authorized clients can interact with our hardened service.