One oat latte, a missing payment confirmation, and a system design walkthrough that follows the order all the way to the counter.
Imagine Maya ordering an oat latte on her way to work. Her payment goes through, but the café’s tablet never receives the order. She arrives at the counter holding a receipt for a drink nobody has started.
Now the retry button looks less like a convenience and more like a very expensive question. Does tapping it recover her coffee, create another order, or charge her again?
I love a system design problem that gets interesting before we have even finished ordering breakfast. Everyone understands the customer’s expectation, which makes it much harder to hide a missing guarantee behind an impressive architecture diagram.
This walkthrough uses a Salesforce coffee-ordering question. We’ll build a concrete design, follow Maya’s order through it, then interrupt the flow at the moments that matter. Maya and the café are our worked example; the implementation choices below are a proposed solution.
First, decide when the barista is allowed to start
Our service lets customers choose a store, customize drinks, pay online or at the counter, and follow preparation status. Staff need a store-specific queue and a way to claim and complete orders.
Before choosing a database, settle two business rules. Can preparation begin before payment, and can a customer cancel once someone starts making the drink? These decisions change the allowed state transitions.
For this version, payment must be confirmed before an order enters the preparation queue. A staff member can record payment collected at the counter. Customers may cancel until preparation begins; later problems go through a staff-assisted resolution. Those are our explicit assumptions, and another café could choose differently.
Start with immediate pickup in one region. Delivery, inventory forecasting and loyalty points can wait. Scheduled pickup will be a follow-up, because placing a drink in tomorrow morning’s queue is different from asking someone to make it now.
Three guarantees keep the rest of the discussion focused. Retrying one checkout must not create another order, two staff members must not both win the same claim, and a successful payment must eventually produce either fulfillment or a recorded resolution such as a refund.
Draw one order’s journey before adding more services
A small ordering application backed by a relational database is enough for the initial design. Separate modules handle menus, orders and payments; they do not need separate deployments just to earn their own boxes.
Maya’s request reaches the Order API, which validates the selected store and drink options, calculates the price and saves order #42. The payment integration works with an external provider. Staff and customer screens read order state through authorized endpoints.
Whenever an important state change needs downstream delivery, save an event in an outbox table in the same database transaction. A relay publishes committed events for screen updates and notifications. This is the transactional outbox pattern, which addresses the gap between committing a change and sending its notification.
The relay can publish twice if it crashes after sending but before recording delivery. Consumers therefore track event IDs or use versioned upserts. The durable order remains queryable even when a notification is delayed.
A compact API surface makes the responsibilities easier to explain.
GET /stores/{store_id}/menu
POST /orders # checkout key + item choices
GET /orders/{order_id} # current authorized view
POST /orders/{order_id}/claim # staff, expected version
POST /orders/{order_id}/ready # staff, expected version
POST /orders/{order_id}/cancel
POST /payments/webhook # verified provider eventsDerive customer identity from authentication and check staff access to the order’s store. A guessed order ID must never be enough to read someone’s purchase or move their drink into preparation.
Save the receipt, not a pointer to today’s menu
Suppose Maya’s drink totals 650 cents. Ten minutes later, someone edits the menu price. Her existing order must still explain the 650 cents she approved.
Keep an Orders record with customer, store, currency, total, fulfillment status and version. OrderItems hold snapshots of the chosen drink, modifiers, quantity and charged amounts. PaymentAttempts link to the order and retain the provider reference, attempt identity, amount, currency and payment status.
Calculate totals on the server, using integer minor units for money. If the submitted menu version is stale and the price changed, return the updated quote for confirmation instead of quietly charging more.
Each checkout carries a client-generated key. Enforce uniqueness on customer_id plus checkout_key in the database, and save a canonical request fingerprint with it. The same key and same request return the existing order; the same key with different drink choices is rejected.
Create that identity record and the order atomically. Otherwise, two simultaneous requests can both pass an application-level lookup and insert two lattes. Keep the mapping for the supported retry window, and make a genuinely new purchase use a new key.
Payment and preparation need different state
A single paid flag becomes awkward as soon as refunds enter the conversation. Money can have arrived while preparation is cancelled, and the refund may still be pending. Both facts deserve to survive.
In this design, fulfillment moves from AWAITING_PAYMENT to QUEUED, then PREPARING, READY and COLLECTED. Cancellation is allowed from the first two states. Payment has its own lifecycle, including PENDING, SUCCEEDED and the refund states.
A verified successful payment queues an order only if it is still eligible. If cancellation has already won, preserve CANCELLED and arrange the refund instead. A late success callback is not permission to resurrect the order.
Serialize these decisions on the order row. Record the payment result, decide the fulfillment transition, and save any required event or refund intent in one transaction. The external charge or refund itself still happens outside that transaction.
Two baristas tap the same order
Both tablets can display order #42 as QUEUED. That is fine until both people press Start and both screens confidently report success.
Make the claim a conditional database update. Here is the essential PostgreSQL shape, with authorization checked before it runs.
UPDATE orders
SET fulfillment_status = 'PREPARING',
claimed_by = :staff_id,
version = version + 1
WHERE id = :order_id
AND store_id = :authorized_store_id
AND fulfillment_status = 'QUEUED'
AND version = :expected_version
RETURNING id, version;Only a caller that receives a returned row owns the claim. A competing caller rereads the order and updates its screen. The cancellation path uses the same row and a compatible conditional transition, so it cannot approve cancellation after a claim has already committed.
This is where the software meets the counter. Staff must wait for claim confirmation before starting. If that confirmation is lost, reread the owner before taking action. A database constraint cannot stop two humans who both decide to make the drink anyway.
Now lose the payment confirmation
We have reached Maya’s original problem. The provider captured her payment, but our application never received the event that would move the order forward.
A timeout tells us that we did not receive an answer. It does not prove that the payment failed. Starting a fresh charge at this point can make the customer’s morning considerably worse.
Before contacting the provider, persist a payment attempt with a stable identity and provider idempotency key. Save the provider’s payment reference when it becomes available. An uncertain retry resumes that same attempt using the provider’s documented idempotency behavior, rather than inventing a new payment.
Provider idempotency has a retention policy, so it is not a forever guarantee. Once safe replay is no longer available, unresolved attempts need provider lookup or manual reconciliation instead of another blind charge.
Webhooks handle the normal asynchronous path. Verify their signatures, durably record accepted events, deduplicate repeated event IDs, and process them through the order’s transition rules. Stripe’s webhook documentation explicitly covers duplicates and events arriving out of order, so delivery order is an unsafe foundation for payment state.
A reconciliation worker covers the missing-event path. It scans attempts that have remained unresolved beyond a chosen age, retrieves the existing payment from the provider, and checks its order association, account, amount and currency.
If P9 succeeded for Maya’s 650-cent order, the same transition handler records success and queues eligible order #42. If another worker or webhook already applied that result, it becomes a no-op. Old notifications must not regress a confirmed success to pending.
If #42 is cancelled, persist refund work using a stable refund identity and track that refund until the provider confirms its outcome. Never display refunded merely because a request was sent.
Notice the two recovery problems we have separated. Reconciliation discovers what happened outside our database. The outbox delivers a decision already committed inside it. Each has a different piece of missing information to recover.
A disconnected café needs an honest promise
Imagine the store tablet loses connectivity during the morning rush. Keeping the ordering button green does not make the barista aware of newly paid orders.
For the initial cloud dependent design, use store connectivity signals and a staff-controlled pause to stop accepting new pickup orders when the store is unavailable. Heartbeats can be stale, so orders accepted just before detection still need an explicit cancellation or fulfillment resolution.
After reconnecting, fetch the current store queue from the database. Versioned updates prevent old messages from moving the screen backward. Customers can use server-sent events for status, with a fresh snapshot on reconnect and polling as a fallback. A notification is a convenience; the current order record is the recovery path.
If offline operation is mandatory, the design needs a durable local queue, stable operation IDs and clear ownership of which orders the store may execute. Staff also need a reconnect reconciliation process. That is a substantial extension, so name the requirement before promising uninterrupted service.
Scheduled pickup changes when work becomes eligible
For an 8:15 pickup, store the requested time with a time zone and admit the order only if the store has capacity. A scheduler releases it near the preparation window using a conditional transition and an event saved atomically with that change.
Duplicate scheduler deliveries must not enqueue it twice. Cancellation before release must prevent preparation, and the payment authorization or capture policy needs to match how far ahead customers can order. Putting tomorrow’s latte straight into today’s queue is wonderfully efficient at producing cold coffee.
Scale the part that actually needs scaling
For a sizing exercise, assume 120 orders per second across 600 stores and 30 read requests per order. That gives roughly 3,600 reads per second, with large differences between quiet stores and busy ones. These are planning assumptions, not measured Salesforce traffic.
Cache menus by store and menu version, index active orders by store and status, and keep customer-status delivery separate from payment processing. Partitioning by store can come later if measurements justify it; a hot flagship location still needs its own capacity controls. If 20,000 customers are watching active orders, budget for those concurrent connections independently of order-write throughput.
Watch the age of unresolved payments, committed queue entries not acknowledged by a store, unpublished outbox events and pending refunds. An average API latency graph can look lovely while Maya is still standing at an empty pickup shelf.
Let the schema answer the SQL follow-up
The question also includes spend-related SQL. Before writing a query, define the word spend. An order total is not proof that money was collected, and joining orders to their items can multiply a payment amount.
For this exercise, use a payment ledger containing one deduplicated row per provider capture or refund. Store customer_id, currency, kind, amount_minor and posted_at. Define last month’s spend as USD captures minus USD refunds posted during a specified half-open time window. Enforce uniqueness on the provider account plus capture or refund object ID, so repeated notifications cannot add the same money movement again.
SELECT customer_id,
SUM(CASE WHEN kind = 'CAPTURE'
THEN amount_minor
ELSE -amount_minor END) AS net_spend
FROM payment_ledger
WHERE currency = 'USD'
AND kind IN ('CAPTURE', 'REFUND')
AND posted_at >= :month_start
AND posted_at < :next_month_start
GROUP BY customer_id
HAVING SUM(CASE WHEN kind = 'CAPTURE'
THEN amount_minor
ELSE -amount_minor END) > 0
ORDER BY net_spend DESC, customer_id
LIMIT 1;Supply the boundaries in the agreed reporting time zone, converted to the storage convention. This query picks one winner deterministically on ties. Returning all tied winners requires ranking the grouped totals instead. Refunds posted this month count this month under our definition, even if the original purchase was earlier.
For users who have never paid anything, use a different definition and check for the absence of any positive capture. A fully refunded customer has paid before, so they do not belong in this result.
SELECT u.id
FROM users AS u
WHERE NOT EXISTS (
SELECT 1
FROM payment_ledger AS p
WHERE p.customer_id = u.id
AND p.kind = 'CAPTURE'
AND p.amount_minor > 0
);This also includes users with no orders at all. If the interviewer means lifetime net spend of zero, say so and change the aggregation. Similar-sounding questions can describe different customer groups.
Walk one latte through the whole answer
Bring the explanation back to Maya. Her checkout identifies one order, its receipt preserves the agreed price, and its payment attempt identifies one external operation. A lost confirmation leaves work to reconcile. A confirmed payment makes an eligible order claimable, and one barista receives permission to prepare it.
Practice the walkthrough by changing one event at a time. Lose the checkout response, deliver the webhook twice, cancel just before preparation, then disconnect the store. For each change, name the durable record and the decision that still has to be made.
That is the part of this question worth taking into another interview. A convincing answer can follow one customer through uncertainty and explain how the system reaches a correct outcome. By then, every important arrow on the diagram has earned its place, and Maya finally has a reason to trust the pickup screen.
~ Emily
Top comments (1)
One thing that really helped for me was treating the receipt saying Paid and the tablet with no order as two different objects that never got locked together.
i'd name Payment, Order, and Preparation as separate objects with grain Outside the cafe flow, then make any status answer open the definition and the source rows. Otherwise a retry looks like recovery and quietly creates a second charge.