From brief to schema

Design happens at three levels of zoom. Each level answers a different question and produces a different artefact. Most design mistakes come from working at the wrong level: picking database indexes before anyone knows what the feature does, or arguing about requirements while the deadline is already gone.

In this lesson we'll take one new Tiffin feature through all three levels, and look at what each level produces and how much detail it actually needs.

Three levels, three artefacts

Think of planning a wedding. First, the requirements: 300 guests, vegetarian food, one day, a fixed budget. Then the high-level plan: which venue, which caterer, how guests get from the station. Only then the details that could go wrong: how the caterer keeps 300 plates hot when the speeches run long. You don't pick serving trays before you've picked a caterer.

The brief: group ordering

Tiffin's sales team brings this:

"Offices want to order lunch together. One person starts a group order and shares a link. Everyone adds their own food. Then the organiser pays for the lot."

Let's take it through the three levels.

Level 1: Requirements

Functional (what it does):

• The organiser creates a group cart for one restaurant and gets a share link.
• Anyone with the link adds, edits or removes their own items.
• Everyone sees the cart update as others add things.
• The organiser checks out and pays for the whole cart.

Non-functional (how well):

• Changes show up for everyone within about 3 seconds.
• Once the organiser starts checkout, the cart must not change underneath them. They pay for exactly what they saw.
• A group cart is abandoned after 2 hours and nobody is charged.

Out of scope for now: splitting the bill between people, carts that mix restaurants.

The numbers

Tiffin does about 50,000 orders a day. Sales expects group orders to be 5% of lunch orders, and lunch is about a third of the day's orders:

• 50,000 × 1/3 × 5% ≈ 830 group carts a day, almost all between 12:00 and 13:30.
• Say up to 200 carts are open at once at peak, with 6 people each: 1,200 people looking at a group cart.

That second number matters for the next level.

Level 2: High-level design

Does group ordering need any new parts? Go through the requirements:

• Carts, items and members are just more data. The existing database handles it.
• "Changes within 3 seconds" means each person's app needs to learn about changes. The simplest option is for each app to ask the server every 3 seconds: polling. (Module 6 covers the alternatives.)
• Checkout reuses the existing payment flow.

So no new boxes. The work goes into the existing server and database:

%% caption: Group ordering adds no new components. The decisions are about how the existing ones behave.
flowchart TD
  O(["Organiser's phone"]) --> S
  MP(["Members' phones"]) -- "poll every 3 s" --> S
  S["Server<br/>carts, items, checkout"] --> DB[("Database")]:::store
  S --> P["Payment provider"]

Now check the polling cost against the numbers: 1,200 people asking every 3 seconds is 400 requests a second. That's on top of Tiffin's normal lunch traffic, and it could easily double the server's load at lunchtime.

That's a high-level decision worth making now: make the poll cheap. Instead of returning the whole cart every 3 seconds, the app sends the cart version it already has, and the server answers "no change" unless the version moved. Nearly every poll then costs one tiny indexed lookup. The full cart is only sent when something actually changed.

The API ends up as a handful of calls:

POST /group-carts                         -> create, returns share link
POST /group-carts/{id}/items              -> add an item
GET  /group-carts/{id}?since_version=17   -> "no change" or the new cart
POST /group-carts/{id}/checkout           -> lock the cart and pay

Level 3: Low-level design

You don't need low-level detail for everything. Adding an item is a plain insert. Spend your LLD effort on the part that can go wrong: the cart must not change while the organiser pays.

Picture it. At 12:41:07 the organiser taps Pay. At the same moment, a colleague adds a samosa. If both go through, the organiser is charged for a cart that no longer matches what was ordered, or the samosa is silently dropped.

First, give the cart a status:

Then make "add an item" and "start checkout" unable to interleave, using the cart row itself.

You don't need to read SQL fluently yet; the comments say what each part does. Two ideas are enough. A transaction (everything between BEGIN and COMMIT) is all or nothing: either every statement in it happens, or none does. And when two transactions try to change the same row, the database makes the second one wait until the first has finished. Module 2 covers both properly.

-- add an item: only works while the cart is open
BEGIN;
UPDATE group_carts
   SET version = version + 1
 WHERE id = :cart_id AND status = 'open';
-- 0 rows updated? the cart is locked: tell the member "checkout has started"
INSERT INTO group_cart_items (cart_id, member_id, dish_id, qty)
VALUES (:cart_id, :member_id, :dish_id, :qty);
COMMIT;

-- start checkout: lock the cart at the version the organiser saw
UPDATE group_carts
   SET status = 'locked'
 WHERE id = :cart_id AND status = 'open' AND version = :seen_version;
-- 0 rows updated? something changed since they looked: show the new cart

Both statements update the same row, so the database runs them one after the other, never at the same time. Either the samosa lands first (and the checkout sees a newer version and shows the organiser the updated cart), or the lock lands first (and the samosa gets a polite "checkout has started"). There's no in-between.

Drawn as a request flow, the two possible orders look like this:

%% caption: Both requests change the same cart row, so the database runs them one after the other.
sequenceDiagram
  autonumber
  participant O as Organiser
  participant MB as Member
  participant D as Database
  O->>D: lock cart if open and version = 17
  MB->>D: add samosa if cart is open
  alt the lock lands first
    D-->>O: locked, pay for version 17
    D-->>MB: 0 rows, "checkout has started"
  else the samosa lands first
    D-->>MB: added, version 18
    D-->>O: 0 rows, show the new cart
  end

The version column now does two jobs: it makes polling cheap, and it makes checkout safe. That's typical of good low-level design. One small mechanism serves several requirements.

How the levels inform each other

The levels aren't a one-way staircase. Two things happened above that are worth noticing:

• A number from Level 1 changed a Level 2 decision. Without the 1,200-viewer estimate, nobody would have thought about polling cost until lunch traffic doubled in production.
• A Level 3 detail raised a Level 1 question. What should happen if a member's add is rejected because checkout started? Is a toast message enough, or should the organiser be able to unlock the cart? That's a product question, so it goes back to the sales team as a requirement to decide.

Going back up a level when you learn something isn't a failure of the process. It's the process working.

How much detail is enough?

A rough guide for a feature this size:

• Requirements: half a page. If it's longer, you're probably designing a product, not a feature.
• HLD: a diagram and a list of API calls. If there are no new boxes, say so explicitly. That's a result, not a gap.
• LLD: only the parts that are risky, concurrent, expensive or hard to change later (here: the lock). Everything else can be decided in code review.