Idempotency Situation

In this task, I understood the concept of idempotency in a wallet system. In real applications like GPay or PhonePe, sometimes the same request may be sent multiple times due to network issues or retries. The system should ensure that the same transaction is not executed more than once.

First, I created the accounts table.

CREATE TABLE accounts (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
balance INT NOT NULL CHECK (balance >= 0),
last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Then I inserted sample data.

INSERT INTO accounts (name, balance)
VALUES
('Alice', 1000),
('Bob', 500);

To check initial balances:

SELECT * FROM accounts;

Alice had 1000 and Bob had 500.

Next, I performed a transfer of 200 from Alice to Bob.

BEGIN;

UPDATE accounts
SET balance = balance - 200
WHERE id = 1;

UPDATE accounts
SET balance = balance + 200
WHERE id = 2;

COMMIT;
After this, Alice had 800 and Bob had 700.

Then I executed the same transaction again.

BEGIN;

UPDATE accounts
SET balance = balance - 200
WHERE id = 1;

UPDATE accounts
SET balance = balance + 200
WHERE id = 2;

COMMIT;

Now Alice had 600 and Bob had 900.

This shows that the same transaction was applied again, which means the system is not idempotent. The database does not automatically prevent duplicate execution.

To solve this, real systems use a unique transaction ID.

CREATE TABLE wallet_transactions (
txn_id TEXT PRIMARY KEY,
sender_id INT,
receiver_id INT,
amount INT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Insert a transaction:

INSERT INTO wallet_transactions (txn_id, sender_id, receiver_id, amount)
VALUES ('TXN1001', 1, 2, 200);

If the same request is sent again:

INSERT INTO wallet_transactions (txn_id, sender_id, receiver_id, amount)
VALUES ('TXN1001', 1, 2, 200);
PostgreSQL will give an error because the transaction ID already exists. This prevents duplicate processing.