Manoj Kumar

Posted on Mar 29

Building a Safe Money Transfer System in PostgreSQL — Transactions, Rollbacks, and What Happens When Things Break

#postgres #database #sql #backend

This one hit different from all the previous problems. We are not just querying data anymore. We are building something that handles real money movement, the kind of thing that powers apps like PhonePe and GPay. And the core idea here is simple but critical — either both updates happen or none of them do. No in between.

The Setup

We have an accounts table that stores user balances. The CHECK constraint on balance makes sure no one can go below zero, which is already a safety net built into the table itself.

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

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

So Alice starts with 1000 and Bob starts with 500.

The Basic Transfer — The Right Way

The goal is to move money from Alice to Bob inside a single transaction block. A transaction is a group of SQL statements that either all succeed together or all fail together. That is the entire point of wrapping this in BEGIN and COMMIT.

BEGIN;

UPDATE accounts
SET balance = balance - 200,
    last_updated = CURRENT_TIMESTAMP
WHERE name = 'Alice';

UPDATE accounts
SET balance = balance + 200,
    last_updated = CURRENT_TIMESTAMP
WHERE name = 'Bob';

COMMIT;

After this runs Alice has 800 and Bob has 700. Clean transfer, both sides updated, transaction committed.

Now Let's Break It on Purpose

This is where things get interesting. The task was to deliberately introduce failures at different points and see what the database does.

Breaking the Credit Step

Here we deduct from Alice but then write a broken UPDATE that will fail before Bob gets credited.

BEGIN;

UPDATE accounts
SET balance = balance - 200,
    last_updated = CURRENT_TIMESTAMP
WHERE name = 'Alice';

-- This is intentionally broken
UPDATE accountsss
SET balance = balance + 200
WHERE name = 'Bob';

COMMIT;

The second UPDATE references a table that does not exist so PostgreSQL throws an error. The COMMIT never runs. The whole transaction is rolled back automatically.

Alice's balance stays at 1000. Bob's balance stays at 500. Nothing changed.

Manually Rolling Back After a Partial Step

You can also trigger a rollback yourself using ROLLBACK instead of COMMIT.

BEGIN;

UPDATE accounts
SET balance = balance - 200,
    last_updated = CURRENT_TIMESTAMP
WHERE name = 'Alice';

-- Realized something is wrong, rolling back manually
ROLLBACK;

Even though the deduct step ran, ROLLBACK undoes it completely. Alice still has 1000. This is useful when you want to cancel a transaction mid way based on some condition in your application code.

Triggering the CHECK Constraint

The balance column has CHECK (balance >= 0) which means you cannot push anyone below zero. Let us try to overdraft Alice.

BEGIN;

UPDATE accounts
SET balance = balance - 5000,
    last_updated = CURRENT_TIMESTAMP
WHERE name = 'Alice';

UPDATE accounts
SET balance = balance + 5000,
    last_updated = CURRENT_TIMESTAMP
WHERE name = 'Bob';

COMMIT;

The first UPDATE tries to set Alice's balance to negative 4000. The CHECK constraint fires immediately and rejects it. The error causes the whole transaction to roll back. Bob does not get 5000 either. Both accounts stay exactly as they were.

Checking the State Before and After

Before any transfer:

SELECT * FROM accounts;

Alice — 1000, Bob — 500.

After a successful transfer of 200:

Alice — 800, Bob — 700.

After a failed transfer where the credit step was broken:

Alice — 1000, Bob — 500. Exactly the same as before. Nothing leaked out.

That is the whole point. A failed transaction leaves zero trace. The database goes back to the state it was in before BEGIN was called.

What I Actually Learned Here

The thing that stuck with me the most is that partial updates are the scariest thing in a payment system. If Alice loses 200 but Bob never gets it, that money just vanishes. Transactions exist to make sure that can never happen.

PostgreSQL does not just protect you when things go wrong on its own. It also gives you tools like ROLLBACK to protect yourself when your own code detects something is off. Combined with constraints like CHECK, you have multiple layers making sure the data stays consistent no matter what breaks and at what point it breaks.

This is not just a database concept. This is exactly how real payment systems are built at their core.

DEV Community