APIs and the Storage Schema

In one line: the schema contains both the right answer and the wrong one, side by side. Reading it carefully is the most instructive thing in the chapter.

The APIs

registerUser(username, email, password)          -> password hashed before storage
authenticateUser(username, password)

authorizePayment(amount, card_number, expiration_date, CVV, merchant_id)
                                                 -> returns an authorization code
capturePayment(authorization_code, amount)
checkPaymentStatus(transaction_id)
getTransactionHistory(customer_id, start_date, end_date)

Splitting authorize from capture is right, and the design never uses the second one

The API list gets the two-phase model correct. authorizePayment reserves and returns a code; capturePayment takes that code and moves the money. That is exactly the shape Lesson 1 described.

Then trace the detailed design's workflow: customer clicks pay → payment service → fraud check → gateway → issuer → "sends the payment to the merchant's account" → wallet updated.

capturePayment is never called. There is no capture step, no batching, no window during which an authorization is outstanding.

This is the same pattern as the previous chapter, where validateAndTest was specified and never invoked, and it is worth recognizing as a general tell: when the API list models a concept the workflow doesn't, the concept was understood and not designed.

One detail on the signature itself: capturePayment(authorization_code, amount) taking an independent amount is correct and deliberate — partial capture is a real requirement. A merchant authorizes $100 for an order, ships two of three items, and captures $70. What the signature does not express is the constraint that makes it safe: the captured amount may never exceed the authorized amount, and the sum of partial captures against one authorization may not either.

authorizePayment takes the raw card number and CVV — which puts your entire service in PCI scope

authorizePayment(amount, card_number, expiration_date, CVV, merchant_id)

Every one of those card fields crossing your API is a decision with enormous consequences, and it is the most expensive architectural choice in payments.

Any system that touches raw cardholder data falls under PCI DSS. Not just the database — every server the data transits, every log it might land in, every backup, every engineer with access, and the network segments in between. Scope determines audit cost, and audit cost is measured in months of engineering time per year, forever.

The alternative is tokenization, and it inverts the data flow:

THIS DESIGN:  browser -> YOUR server (raw PAN + CVV) -> gateway
              -> your whole stack is in PCI scope

TOKENIZED:    browser -> gateway DIRECTLY (via an embedded iframe or SDK)
              gateway returns a TOKEN
              browser -> your server (token only)
              -> your servers never see a card number; scope collapses

That is how Stripe Elements, Braintree, Adyen, and every modern PSP work, and it is why they work that way. The card data never touches your infrastructure, so the compliance burden lands on the party equipped to carry it.

And there is a second, harder rule: CVV may never be stored after authorization. Not encrypted, not hashed, not "temporarily." PCI DSS prohibits it outright, because CVV exists to prove the card is physically present at that moment — storing it destroys the only thing it proves.

Set that against the design's own answer about encryption: "when the payment service receives a payment event, it stores the event details in the database after applying the necessary encryption." If those event details include the CVV, encryption does not make it compliant.

The cheapest way to secure cardholder data is to never receive it. That sentence is what an interviewer is listening for.

The schema

The Customer table stores the card number in plaintext, next to a Tokens table built to prevent exactly that

Customer.creditcard_no: VARCHAR(16) is a sixteen-character field, which is precisely the length of a card number. There is no indication of encryption, hashing, or tokenization on it.

And three tables away sits a Tokens table — token_id, customer_id, token_description, expiry — which is the tokenization vault. The right answer is in the same diagram as the wrong one, and the prose never mentions either.

What the schema should hold instead:

INSTEAD OF: creditcard_no VARCHAR(16)          <- the PAN itself
STORE:      token_id      VARCHAR(20)          <- reference to the vault
            last_four     CHAR(4)              <- for display: "**** 4242"
            brand         VARCHAR(20)          <- Visa / Mastercard
            expiry        DATE                 <- for expiry warnings

Those four fields support every user-facing feature — showing a saved card, warning about expiry, letting a customer pick between cards — without your database ever holding a number that can be used to charge someone. The Tokens table already models it; the Customer table bypasses it.

This is the concrete form of the previous callout. Storing the PAN is what puts the database in scope; the token table is what takes it out. The schema contains both decisions and the design makes the wrong one.

Transaction has an amount and no currency — while Wallet has the currency

Transaction:  amount DECIMAL(10,2)              <- no currency column
Wallet:       balance DECIMAL(10,2), currency_code VARCHAR(3)

So a transaction row says 100.00 and nothing says whether that is dollars, euros, or yen. The currency lives on the merchant's wallet, which implicitly assumes every transaction is in the merchant's currency — an assumption that fails the moment a customer pays in a currency the merchant does not hold, which is routine in e-commerce.

An amount without a currency is not money; it is a number. Money is a pair, always:

{ amount_minor: 10000, currency: "USD" }     <- $100.00
{ amount_minor: 10000, currency: "JPY" }     <- ¥10,000 — a completely different value

Two more things the schema gets half right:

DECIMAL(10,2) is the right family and the wrong instance. Decimal rather than float is correct and important — floating point cannot represent 0.10 exactly, so accumulating float amounts produces drift that a ledger will eventually surface as a mismatch of a few cents that nobody can explain. But (10,2) caps at 99,999,999.99, and it hardcodes two decimal places, which is wrong for currencies that have none (JPY, KRW) or three (KWD, BHD).

The production convention is integer minor units. Store 10000 and "USD", not 100.00. Integers cannot drift, and the number of decimal places becomes a property of the currency rather than of the column.

Represent money as an integer count of minor units plus a currency code — never as a float, and never as a bare number.

The Ledger has no amount column, which means it is not a ledger

Ledger: transaction_id, transaction_date, token_id, action, description

Read that list again. There is no amount, no account, and no direction.

A ledger's entire purpose is to record how much moved between which accounts in which direction, so that balances can be derived and verified. This table records that something happened and points at a transaction — which makes it an audit log, not a ledger.

The consequences are concrete:

Balances cannot be derived from it. The Wallet.balance column becomes the only source of truth for how much a merchant has, with nothing to check it against. If that number is ever wrong — a double-applied update, a lost message, a bug — nothing in the system can detect it.

Reconciliation has nothing to reconcile. The design later describes comparing the internal ledger against the PSP's settlement file, with a worked example of "three payments totaling $225." You cannot total a table with no amounts.

The double-entry invariant is unavailable. Lesson 5 covers this in full; the short version is that a proper ledger records every transaction as balanced entries that sum to zero, and that arithmetic identity is what makes integrity checkable rather than merely hoped for.

A ledger without amounts and accounts is an audit log wearing a ledger's name, and the difference is whether your books can be proven to balance.

Wallet.balance has no version column — the textbook lost update

Wallet: wallet_id, creation_date, update_date, balance, merchant_id, currency_code

A merchant receiving concurrent payments gets concurrent updates to one row:

Balance = 500
Payment A reads 500, computes 500 + 30 = 530
Payment B reads 500, computes 500 + 45 = 545
A writes 530.  B writes 545.
Final balance: 545.  The $30 payment VANISHED.

This is the classic lost update, and in this domain it is money disappearing from a merchant's account.

Three standard fixes, in increasing order of how much they suit the domain:

Optimistic locking — add a version column; update WHERE version = X and retry on zero rows affected.

Atomic increment — never read-then-write; issue SET balance = balance + 30, which the database applies atomically.

Derive the balance from the ledger — do not store a mutable balance at all. Append immutable entries and compute the balance as their sum, materializing a cached total for reads.

The third is what real financial systems do, and it dissolves the problem rather than guarding against it: an append-only log has no lost updates because nothing is ever overwritten. It also gives you the audit trail and the reconciliation basis for free.

A mutable balance column is a lost update waiting to happen; a balance derived from an append-only ledger cannot lose one.

What the status column is really for

Transaction.status VARCHAR(20) is the one field that carries the two-phase model into the data, and it is worth being explicit about the state machine it implies:

Two things this makes visible that the prose never does.

Authorized → Expired is a real transition and it is the answer to the chapter's own unanswered question about authorization succeeding while settlement fails. Something must sweep expired holds, and nothing in the design does.

Every state is durable and terminal states are reachable from several paths. That is what makes checkPaymentStatus(transaction_id) meaningful, and it is what lets a client resolve the timeout ambiguity in Lesson 6 — "I don't know what happened, so I'll ask."

A payment is a state machine, not a request, and the design's single status column is the only place the schema admits it.

Next: the components, and what the workflow actually builds.