Airline booking database series: 1. Flights, seats and overbooking · 2. Holding seats under concurrency · 3. Bookings, ledger and history
The previous two parts handled the “goods” (flights, seats); this one handles “the order and the money”. By the end you can design a multi-passenger round-trip booking you can price, partially refund and audit, a double-entry ledger that checks its own balance, and v2’s version + booking_history pattern that prevents lost writes and keeps history. Every DDL statement and scenario was run on PostgreSQL 16, including the cases that must fail.
Read first: part 1 for the flight and seat_availability tables; BigDecimal and money if you’re not used to keeping double away from money.
1. A booking is not one row
Key idea: v1 crammed outbound flight, return flight, passenger count and amount into one booking table. v2 splits it into three levels so each direction has its own money, tickets and status.
erDiagram
BOOKING ||--|{ BOOKING_DIRECTION : "1 or 2 directions"
BOOKING_DIRECTION ||--|{ BOOKING_LINE : "fare, fees, VAT"
BOOKING_DIRECTION }o--|| FLIGHT : "flies on"
BOOKING_DIRECTION ||--o{ TICKET : "one ticket per passenger"
BOOKING ||--|{ BOOKING_PASSENGER : ""
PASSENGER ||--o{ BOOKING_PASSENGER : ""
PASSENGER ||--o{ TICKET : ""
- booking (the header): who booked, status, the total snapshotted at checkout, payment deadline, version.
- booking_direction: one row per direction (outbound =
AWAY, return =RETURN), pointing at a flight, with that direction’s subtotal. - booking_line: a direction’s money lines: the fare (unit price × passengers), fees, VAT.
So refunding the return leg means finding the RETURN direction; an airport-fee report sums the FEE lines; the VAT invoice reads the VAT line.
DDL for booking, booking_direction, booking_line and fixes to the original notes
CREATE TABLE booking (
booking_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
booking_code char(6) NOT NULL UNIQUE, -- booking reference (PNR) given to the customer
user_id bigint NOT NULL,
request_id text NOT NULL, -- idempotency key generated by the client
status text NOT NULL DEFAULT 'PENDING_PAYMENT'
CHECK (status IN ('PENDING_PAYMENT', 'PAID', 'TICKETED', 'CANCELLED', 'EXPIRED')),
voucher_code text,
ticket_amount bigint NOT NULL CHECK (ticket_amount >= 0),
fee_amount bigint NOT NULL DEFAULT 0 CHECK (fee_amount >= 0),
vat_amount bigint NOT NULL DEFAULT 0 CHECK (vat_amount >= 0),
discount_amount bigint NOT NULL DEFAULT 0 CHECK (discount_amount >= 0),
total_amount bigint NOT NULL,
currency char(3) NOT NULL DEFAULT 'VND',
checkout_at timestamptz NOT NULL DEFAULT now(),
expires_at timestamptz NOT NULL, -- payment deadline
paid_at timestamptz,
extra_data jsonb NOT NULL DEFAULT '{}',
version int NOT NULL DEFAULT 1,
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now(),
UNIQUE (user_id, request_id),
CHECK (total_amount = ticket_amount + fee_amount + vat_amount - discount_amount),
CHECK ((status IN ('PAID', 'TICKETED')) = (paid_at IS NOT NULL))
);
CREATE TABLE booking_direction (
bd_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
booking_id bigint NOT NULL REFERENCES booking,
direction smallint NOT NULL CHECK (direction IN (0, 1)), -- 0 = AWAY, 1 = RETURN
flight_id bigint NOT NULL REFERENCES flight,
subtotal_amount bigint NOT NULL,
UNIQUE (booking_id, direction)
);
CREATE TABLE booking_line (
bl_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
bd_id bigint NOT NULL REFERENCES booking_direction,
type text NOT NULL CHECK (type IN ('TICKET', 'FEE', 'VAT')),
description text NOT NULL,
unit_amount bigint NOT NULL,
quantity int NOT NULL CHECK (quantity > 0),
subtotal_amount bigint NOT NULL,
CHECK (subtotal_amount = unit_amount * quantity)
);
Differences from the original notes:
UNIQUE (user_id, request_id): the course’s homestay project has arequest_idbut no unique constraint, so retries create duplicate bookings (see Idempotency part 1).- The v2 notes have a
discount_countcolumn, almost certainly meant as the discount amount; here it’sdiscount_amount. - v2 has both a
bd_idcolumn onticketand abd_ticketjoin table. A ticket belongs to exactly one direction, so the foreign key is enough; the join table is a second source of truth for the same relationship.
1.1. Storing money
Money is a bigint in the currency’s minor unit plus a currency column. VND has 0 decimal places under ISO 4217, so 1,500,000 VND is 1500000; for USD you’d store cents. Never float/double; numeric works too, but integers are simpler. In Java that’s long, or BigDecimal when you multiply by rates.
1.2. Storing the total on the header: deliberate denormalization
booking.total_amount can be computed from booking_line, so storing it breaks normalization. Store it anyway because:
- It’s the figure locked in at checkout, exactly the amount sent to the payment gateway; it must not change when the price list changes.
- Booking lists and reports read the header without joining and summing.
The cost is two sources that can drift. Reduce the risk with a single code path that creates bookings (lines, then header, in one transaction), the header’s internal CHECK, and a periodic reconciliation query:
SELECT b.booking_id, b.total_amount, sum(bl.subtotal_amount) AS lines_total
FROM booking b
JOIN booking_direction bd USING (booking_id)
JOIN booking_line bl USING (bd_id)
GROUP BY b.booking_id, b.total_amount
HAVING b.total_amount <> sum(bl.subtotal_amount);
-- expected: 0 rows
When to break normalization in general: Database normalization.
2. Passengers and tickets
Key idea: passenger is the person travelling, not an account; a ticket is one passenger on one direction. The ticket repeats flight_id on purpose so a unique index can block selling one seat twice.
passengeris the person travelling, not a user account: one person can book for the whole family. It links to bookings throughbooking_passenger.tickethasUNIQUE (bd_id, passenger_id): one ticket per passenger per direction. Thechar(13)ticket number is a 3-digit airline code + a 10-digit serial.ticket.flight_idcould be derived viabd_id → booking_direction.flight_id. It’s repeated so you can put a partial unique index (one that only holds rows matching a condition) on(flight_id, seat_code)for active tickets. That’s the last line of defence, even if the seat-holding code has a bug.- Tested: a second ticket for the same seat is rejected; once the first becomes
VOID, the seat can be issued again normally.
DDL for passenger, booking_passenger, ticket
CREATE TABLE passenger (
passenger_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
full_name text NOT NULL,
nationality char(2) NOT NULL, -- ISO 3166-1 alpha-2
document_no text NOT NULL, -- national ID / passport: sensitive personal data
date_of_birth date NOT NULL,
gender smallint NOT NULL CHECK (gender IN (0, 1, 2, 9)), -- ISO 5218
phone text,
email text
);
CREATE TABLE booking_passenger (
booking_id bigint NOT NULL REFERENCES booking,
passenger_id bigint NOT NULL REFERENCES passenger,
PRIMARY KEY (booking_id, passenger_id)
);
CREATE INDEX booking_passenger_pax_idx ON booking_passenger (passenger_id);
CREATE TABLE ticket (
ticket_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
ticket_no char(13) NOT NULL UNIQUE, -- 3-digit airline code + 10-digit serial
status text NOT NULL DEFAULT 'ISSUED' CHECK (status IN ('ISSUED', 'USED', 'VOID', 'REFUNDED')),
booking_id bigint NOT NULL REFERENCES booking,
bd_id bigint NOT NULL REFERENCES booking_direction,
flight_id bigint NOT NULL REFERENCES flight,
passenger_id bigint NOT NULL REFERENCES passenger,
seat_code text,
issued_at timestamptz NOT NULL DEFAULT now(),
UNIQUE (bd_id, passenger_id) -- one ticket per passenger per direction
);
CREATE UNIQUE INDEX ticket_one_seat_idx ON ticket (flight_id, seat_code)
WHERE seat_code IS NOT NULL AND status IN ('ISSUED', 'USED');
gender uses ISO 5218 codes (0 unknown, 1 male, 2 female, 9 not applicable), as the v2 notes’ SMALLINT suggests; numeric codes are more stable than strings when display languages change.
3. Money: transactions and a double-entry ledger
Key idea: v1 only had a transaction table with amount, method and fee. v2 adds debit/credit accounts and an entry table following double-entry rules; this is the most valuable part of the whole design.
3.1. The principle
Every movement of money is a transaction made of at least two entries on accounts. A transaction’s entries sum to zero (convention: debits positive, credits negative). Entries are never edited or deleted; mistakes are fixed with a reversing transaction. An account’s balance is the sum of its entries.
| Account | Type | Meaning |
|---|---|---|
GATEWAY_CLEARING |
Asset | Money the payment gateway holds for us, not yet paid out to the bank |
BANK |
Asset | Money in the airline’s bank account |
CUSTOMER_PREPAID |
Liability | Money customers paid for flights not yet flown (unearned revenue) |
GATEWAY_FEE |
Expense | Payment gateway fees |
REVENUE |
Revenue | Recognized once the flight has flown |
| Event | Entries |
|---|---|
| Customer pays 6,820,000 VND through the gateway | Debit GATEWAY_CLEARING 6,820,000 / Credit CUSTOMER_PREPAID 6,820,000 |
| Gateway pays out after a 1.1% fee (75,020) | Debit BANK 6,744,980, Debit GATEWAY_FEE 75,020 / Credit GATEWAY_CLEARING 6,820,000 |
| Refund of the return leg, 3,410,000 | Debit CUSTOMER_PREPAID 3,410,000 / Credit GATEWAY_CLEARING 3,410,000 |
| Flight has flown | Debit CUSTOMER_PREPAID / Credit REVENUE |
The second row is why you need an entry table instead of debit_acc and credit_acc columns: the payout has three legs (bank, fee, gateway). Two columns can only express two-legged transactions.
3.2. Schema and the balance constraint
Three tables: account (code, type, currency), ledger_transaction (type, booking, an external_ref with a unique constraint so a gateway transaction is booked only once) and entry (transaction, account, signed amount). Two invariants are enforced by the database:
-- The entries of each transaction must sum to zero, checked at COMMIT
CREATE FUNCTION check_txn_balanced() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
IF (SELECT coalesce(sum(amount), 0) FROM entry WHERE txn_id = NEW.txn_id) <> 0 THEN
RAISE EXCEPTION 'ledger transaction % is not balanced', NEW.txn_id;
END IF;
RETURN NULL;
END $$;
CREATE CONSTRAINT TRIGGER entry_balanced
AFTER INSERT ON entry
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW EXECUTE FUNCTION check_txn_balanced();
-- Entries can only be added, never updated or deleted
CREATE FUNCTION forbid_change() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
RAISE EXCEPTION 'entries are append-only; post a reversing transaction instead';
END $$;
CREATE TRIGGER entry_append_only BEFORE UPDATE OR DELETE ON entry
FOR EACH ROW EXECUTE FUNCTION forbid_change();
The table is ledger_transaction, not transaction as in the notes: TRANSACTION is an SQL keyword, and in code “transaction” already means a database transaction. The clash causes confusion in logs and reviews.
Tested: a transaction with a single +100 entry still INSERTs but fails at COMMIT; UPDATE entry is blocked by the trigger; booking the same external_ref again is rejected by the unique constraint.
The ledger_transaction and entry tables, recording a payment, and test results
CREATE TABLE ledger_transaction (
txn_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
type text NOT NULL CHECK (type IN ('PAYMENT', 'REFUND', 'SETTLEMENT')),
booking_id bigint REFERENCES booking,
amount bigint NOT NULL CHECK (amount > 0),
payment_method text, -- CARD, DWALLET, VNPAY...
external_ref text, -- transaction id on the gateway / bank side
created_at timestamptz NOT NULL DEFAULT now(),
UNIQUE (type, external_ref) -- a gateway transaction is booked only once
);
CREATE TABLE entry (
entry_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
txn_id bigint NOT NULL REFERENCES ledger_transaction,
account_id bigint NOT NULL REFERENCES account,
amount bigint NOT NULL CHECK (amount <> 0), -- debit > 0, credit < 0
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX entry_txn_idx ON entry (txn_id);
CREATE INDEX entry_account_idx ON entry (account_id, created_at);
Recording a payment:
BEGIN;
WITH t AS (
INSERT INTO ledger_transaction (type, booking_id, amount, payment_method, external_ref)
VALUES ('PAYMENT', :bookingId, 6820000, 'VNPAY', :gatewayTxnNo)
RETURNING txn_id)
INSERT INTO entry (txn_id, account_id, amount)
SELECT t.txn_id, a.account_id, x.amount
FROM t,
(VALUES ('GATEWAY_CLEARING', 6820000), ('CUSTOMER_PREPAID', -6820000)) x(code, amount)
JOIN account a ON a.account_code = x.code;
COMMIT;
The error when an unbalanced transaction commits: ledger transaction 2 is not balanced. After a payment, a payout and a refund of the return leg, the whole ledger still sums to zero, and GATEWAY_CLEARING is at −3,410,000: the gateway refunded on the airline’s behalf and will deduct it from the next payout.
The entry table is append-only, so it needs neither updated_at nor version; booking needs both. Which technical columns suit which kind of table, with the table template applied to this very schema: The data modeling process and table templates.
4. Optimistic locking and booking_history
Key idea: several sources edit the same booking, so every write carries WHERE version = <read value> and bumps the version; zero rows affected means someone wrote first. A snapshot of each version goes into booking_history in the same transaction.
The writers: the customer (editing passenger details), the gateway’s IPN (its result notification, moving to PAID), the expiry job (EXPIRED), a call-centre agent (cancellation). Without a version, two simultaneous writes silently overwrite one another. The four-step pattern from v2’s diagram:
- Read the booking and see
version = 1. - Compute the new state with
version = 2. - Write conditionally:
UPDATE ... SET version = 2 WHERE booking_id = ? AND version = 1. No rows changed: re-read and decide again, or report a conflict. - Insert a snapshot of version 2 into
booking_history, in the same transaction.
CREATE TABLE booking_history (
his_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
booking_id bigint NOT NULL,
version int NOT NULL,
status text NOT NULL,
total_amount bigint NOT NULL,
paid_at timestamptz,
snapshot jsonb NOT NULL, -- the whole booking row at this version
changed_by text NOT NULL, -- 'user:42', 'ipn:vnpay', 'job:expire'
changed_at timestamptz NOT NULL DEFAULT now(),
UNIQUE (booking_id, version)
);
-- Steps 3 and 4 in one statement: history is written only when the UPDATE actually happens
WITH u AS (
UPDATE booking
SET status = 'PAID', paid_at = now(), version = version + 1, updated_at = now()
WHERE booking_id = :id AND version = :expectedVersion AND status = 'PENDING_PAYMENT'
RETURNING *)
INSERT INTO booking_history (booking_id, version, status, total_amount, paid_at, snapshot, changed_by)
SELECT booking_id, version, status, total_amount, paid_at, to_jsonb(u), 'ipn:vnpay' FROM u;
Tested: the booking is created at version 1 (history has version 1), the IPN moves it to PAID at version 2 (history has version 2). A “slow writer” still holding version 1 runs UPDATE ... WHERE version = 1 and gets UPDATE 0. UNIQUE (booking_id, version) on the history adds a layer: two writes of the same version can’t both get through.
With JPA in Spring Boot 3, steps 1–3 are @Version:
@Entity
@Table(name = "booking")
class Booking {
@Id @GeneratedValue(strategy = GenerationType.IDENTITY)
private Long bookingId;
@Enumerated(EnumType.STRING)
private BookingStatus status;
@Version // Hibernate adds "AND version = ?" and increments it
private int version;
void markPaid(Instant at) {
if (status != BookingStatus.PENDING_PAYMENT) throw new IllegalStateException("not payable: " + status);
status = BookingStatus.PAID;
paidAt = at;
}
// ...
}
A version conflict makes Spring throw ObjectOptimisticLockingFailureException. For system operations (the IPN): re-read and retry. For a user editing stale data: return 409 Conflict. The Java wasn’t run; the equivalent SQL was.
Comparing ways to keep history: code, trigger, Envers, CDC, event sourcing
5. Putting it together: the two transactions of a booking
Key idea: one booking is two short transactions: TX1 at checkout, TX2 when the IPN reports payment. Anything external (issuing tickets, email) goes through an outbox after commit.
sequenceDiagram
participant C as Client
participant S as Booking service
participant DB as PostgreSQL
participant PG as Payment gateway
C->>S: POST /bookings (request_id, outbound, return, 2 passengers)
S->>S: price, VAT, fees (outside the transaction)
S->>DB: TX1: INSERT booking, directions, lines, passengers, hold seats, history v1
DB-->>S: COMMIT
S-->>C: booking_code, total, payment deadline
C->>PG: pay
PG->>S: successful IPN
S->>DB: TX2: UPDATE booking version+1 PAID, history v2, seats HELD→BOOKED, ledger
DB-->>S: COMMIT
S->>DB: outbox: issue tickets, send email (processed after commit)
- TX1 (checkout): a booking with
UNIQUE (user_id, request_id), two directions, the lines, seat holds for both directions (both succeed or both roll back), history v1. Tested: all 4 seats held for 2 passengers × 2 directions, and the 6,820,000 header matches the sum of the lines. - TX2 (paid): UPDATE with version and status conditions, history, seats to
BOOKED, ledger; all or nothing. - The outbox (a table of work to do, processed after commit) handles ticket issuing and emails, outside TX2. The IPN flow, signatures and late IPNs: the payment gateway integration post.
6. Indexes for bookings and money
Key idea: most of these are foreign-key indexes, because PostgreSQL doesn’t create them (see part 1); plus a few partial indexes for the job and seats.
| Query | Index |
|---|---|
| My bookings, newest first | booking (user_id, created_at DESC) |
| Expiry job for pending-payment bookings | Partial booking (expires_at) WHERE status = 'PENDING_PAYMENT' |
| Directions, lines and tickets of a booking | booking_direction (booking_id) (already covered by UNIQUE (booking_id, direction)), booking_line (bd_id), ticket (booking_id) |
| Passenger list of a flight | booking_direction (flight_id), partial ticket (flight_id, seat_code) |
| Ledger account statement | entry (account_id, created_at) |
| Entries of a transaction, transactions of a booking | entry (txn_id), ledger_transaction (booking_id) |
Exercises
Exercise 1: Refunding one direction
Task: a round-trip booking for 2 passengers, 6,820,000 VND in total (3,410,000 per direction), already paid. The customer cancels the return leg before departure and gets a full refund. List the data changes.
Solution to exercise 1
In one transaction:
ticketrows of theRETURNdirection:status = 'REFUNDED'(the partial unique index releases the seats for others).seat_availabilityof the return flight: both seats back toAVAILABLE(orbooked = booked - 2with a counter).- A
REFUNDledger_transactionwith the gateway’s refund id asexternal_ref, with entries DebitCUSTOMER_PREPAID3,410,000 / CreditGATEWAY_CLEARING3,410,000. booking:version + 1, status staysTICKETED(the outbound leg is still valid), history written withchanged_by = 'agent:...'and the reason inextra_data.
Don’t touch booking_line or total_amount: they’re the price that was sold. The refunded amount is read from the ledger. A senior would add: calling the gateway’s refund API is an external call outside the transaction; record the refund as pending, call the gateway with an idempotency key, update when the result arrives.
Exercise 2: Ledger reconciliation queries
Task: write queries that detect (a) unbalanced transactions; (b) paid bookings without a PAYMENT transaction.
Solution to exercise 2
-- (a) also works on MySQL, which has no constraint triggers
SELECT txn_id, sum(amount) FROM entry GROUP BY txn_id HAVING sum(amount) <> 0;
-- (b)
SELECT b.booking_id FROM booking b
WHERE b.status IN ('PAID', 'TICKETED')
AND NOT EXISTS (SELECT 1 FROM ledger_transaction t WHERE t.booking_id = b.booking_id AND t.type = 'PAYMENT');
Run them daily and alert on any non-empty result. This is the “internal” side of reconciliation; matching against the gateway’s statement is in the payment gateway integration post.
Exercise 3: Keep coding the booking feature
Task (the course’s “continue coding booking” homework): implement POST /bookings for multi-passenger round trips with Spring Boot 3 on this schema.
Solution to exercise 3 (outline)
PricingServicecomputes the lines outside the transaction (returning an immutable record).BookingService.checkoutwith@Transactional: insert the booking (catchDuplicateKeyExceptionon(user_id, request_id)and return the existing booking), the directions, the lines, hold seats for both directions (part 2), history v1.- Return
201withbooking_code,total_amount,expires_at. - Test with Testcontainers: the same
request_idreturns the same booking; 50 threads fighting over the last 10 seats never share a seat; the header equals the sum of the lines.
Remember
- Can you explain why a booking splits into header, directions and lines, and which tables change when one direction is refunded?
- Do you know which type to store money in, and why VAT rounded per line differs from VAT on the total?
- Can you write the entries for a payment, a payout net of fees and a refund, and say why you need an
entrytable? - Do you know how PostgreSQL enforces “sums to zero”, and what you do on MySQL instead?
- Do you know when optimistic locking fits (bookings) and when it hurts (the seat counter, bulk UPDATEs)?
Interview questions
Junior
Why not store money as a double?
Because a double is binary floating point: it can’t represent many decimal values exactly (0.1 + 0.2 ≠ 0.3), and errors accumulate.
Store an integer in the currency’s minor unit (bigint, long) plus a currency code, or numeric/BigDecimal when you need rates, and settle the rounding rule in one place.
Why split a booking into booking_direction and booking_line?
Because a booking can have two directions, each with its own fare, fees and VAT, and each can be cancelled or refunded separately.
Keeping outbound and return as two columns is a repeating group. Splitting them into rows gives each direction its own tickets, money and status, and reporting by money type becomes a sum over lines.
Mid
How does optimistic locking with a version column work?
Read the row with its version; on write, add WHERE version = :readVersion and increment it; zero rows updated means someone else wrote first.
Then re-read and decide again, or return 409. No lock is held while the user thinks. It suits low contention; on heavily contended rows it causes retry storms. JPA does it automatically with @Version.
Does storing total_amount on the booking break normalization, and why store it anyway?
Yes, because it can be derived from the lines; store it anyway because it’s the figure locked in at checkout and sent to the payment gateway.
It must not change when prices change, and it keeps booking lists fast. Control the drift risk with a single code path that creates bookings, an internal CHECK, and a periodic reconciliation query.
Senior
How would you design a double-entry ledger for a booking system?
Three tables — accounts, transactions, entries — with two invariants: each transaction’s entries sum to zero, and entries are append-only.
- Transactions: type, booking, external reference with a unique constraint. Entries: transaction, account, signed amount.
- Balance: a deferred constraint trigger on PostgreSQL; a check in code plus a reconciliation query on MySQL. Mistakes are fixed with reversing transactions.
- Accounts: money held by the gateway, the bank, customer prepayments (a liability), fees, and revenue recognized once the flight has flown.
- Balances are sums of entries; only store them when measurements show you need to, because they create hot rows.
Why not just use debit_account and credit_account columns on the transaction?
Because many real transactions have more than two legs: a gateway payout net of fees has three (bank, fee, money held by the gateway).
Revenue shares with partners and tax splits are the same. Two columns force you to invent fake transactions or lose information. An entries table expresses every case, at the cost of guaranteeing balance yourself.
A job bulk-updates bookings to EXPIRED. What can go wrong with optimistic locking and history?
A bulk UPDATE bypasses JPA, so it doesn’t bump the version or write history: a request holding an old version can still overwrite the row, and the history has gaps.
Fix: the job sets version = version + 1 itself with a status condition (WHERE status = 'PENDING_PAYMENT'), writes history through a trigger or in the same statement with a CTE, and never expires a booking with a payment in progress (ask the gateway first).
Useful phrases for interviews
- “A booking is a header with directions and line items, like an invoice, so each leg can be priced and refunded on its own.”
- “We store money as a bigint in the currency’s minor unit, together with the currency code.”
- “The total on the header is deliberate denormalization: it’s the amount we sent to the gateway, and a reconciliation query checks it against the lines.”
- “Every money movement is a double-entry transaction whose entries sum to zero, and entries are append-only.”
- “On PostgreSQL a deferred constraint trigger enforces the balance at commit; on MySQL we check it in code and reconcile daily.”
- “Booking updates use optimistic locking with a version column, and the history row is written in the same transaction.”
- “Bulk updates bypass @Version, so jobs bump the version themselves or history comes from a trigger.”
- “I wouldn’t use optimistic locking on a hot counter; under heavy contention it turns into a retry storm.”
Notes based on the Advanced Backend course (Ronin Engineer), rewritten in my own words.