Skip to content
notes.vinhnq.com

Search

Database & ORMSenior24 min read

Airline booking: away/return directions, a double-entry ledger and change history

Review 15 cards
On this page

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 a request_id but no unique constraint, so retries create duplicate bookings (see Idempotency part 1).
  • The v2 notes have a discount_count column, almost certainly meant as the discount amount; here it’s discount_amount.
  • v2 has both a bd_id column on ticket and a bd_ticket join 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.

  • passenger is the person travelling, not a user account: one person can book for the whole family. It links to bookings through booking_passenger.
  • ticket has UNIQUE (bd_id, passenger_id): one ticket per passenger per direction. The char(13) ticket number is a 3-digit airline code + a 10-digit serial.
  • ticket.flight_id could be derived via bd_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:

  1. Read the booking and see version = 1.
  2. Compute the new state with version = 2.
  3. Write conditionally: UPDATE ... SET version = 2 WHERE booking_id = ? AND version = 1. No rows changed: re-read and decide again, or report a conflict.
  4. 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:

  1. ticket rows of the RETURN direction: status = 'REFUNDED' (the partial unique index releases the seats for others).
  2. seat_availability of the return flight: both seats back to AVAILABLE (or booked = booked - 2 with a counter).
  3. A REFUND ledger_transaction with the gateway’s refund id as external_ref, with entries Debit CUSTOMER_PREPAID 3,410,000 / Credit GATEWAY_CLEARING 3,410,000.
  4. booking: version + 1, status stays TICKETED (the outbound leg is still valid), history written with changed_by = 'agent:...' and the reason in extra_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)
  • PricingService computes the lines outside the transaction (returning an immutable record).
  • BookingService.checkout with @Transactional: insert the booking (catch DuplicateKeyException on (user_id, request_id) and return the existing booking), the directions, the lines, hold seats for both directions (part 2), history v1.
  • Return 201 with booking_code, total_amount, expires_at.
  • Test with Testcontainers: the same request_id returns 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 entry table?
  • 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.