Designing the data model

The schema is the most expensive thing to change later. A wrong query costs an afternoon; a wrong schema costs a migration, a data backfill and every piece of code that touched it. This is the lesson to go slowly on.

Module 6 modelled the catalogue. This one models everything else and shows how the pieces connect.

What the stories need

Reading back the stories, the nouns are: customer, address, product, variant, cart, cart item, order, order item, delivery slot, payment.

Nouns in stories are usually tables. Verbs are usually operations. It is a crude heuristic and it gets you most of the way.

Users and addresses

enum Role {
  CUSTOMER
  ADMIN
}

model User {
  id           String    @id @default(cuid())
  email        String    @unique
  passwordHash String
  name         String
  phone        String?
  role         Role      @default(CUSTOMER)
  isActive     Boolean   @default(true)
  createdAt    DateTime  @default(now())

  addresses    Address[]
  carts        Cart[]
  orders       Order[]
  refreshTokens RefreshToken[]

  @@map("users")
}

model Address {
  id        String  @id @default(cuid())
  userId    String
  user      User    @relation(fields: [userId], references: [id], onDelete: Cascade)

  label     String?
  line1     String
  line2     String?
  landmark  String?
  city      String  @default("Pune")
  pincode   String
  isDefault Boolean @default(false)

  orders    Order[]

  @@index([userId])
  @@map("addresses")
}

Addresses are a separate table, not columns on the user. A customer has a home and an office. More importantly, an order must keep the address it was delivered to — a customer moving house must not change where last month's order says it went.

landmark is not padding. In India an address without one is often undeliverable, and a form that does not ask for it produces delivery calls.

The cart

model Cart {
  id        String     @id @default(cuid())
  userId    String?
  user      User?      @relation(fields: [userId], references: [id], onDelete: Cascade)
  sessionId String?    @unique

  items     CartItem[]

  createdAt DateTime   @default(now())
  updatedAt DateTime   @updatedAt

  @@index([userId])
  @@map("carts")
}

model CartItem {
  id        String  @id @default(cuid())
  cartId    String
  cart      Cart    @relation(fields: [cartId], references: [id], onDelete: Cascade)
  variantId String
  variant   Variant @relation(fields: [variantId], references: [id], onDelete: Cascade)
  quantity  Int

  addedAt   DateTime @default(now())

  @@unique([cartId, variantId])
  @@map("cart_items")
}

Four decisions.

The cart is in the database, not a cookie. The story says it must survive closing the browser, and a cookie is per-device. A customer browsing on a phone and ordering on a laptop is normal.

userId is optional, sessionId covers signed-out customers. The story says an item added before signing in must survive signing in — so a signed-out cart is keyed by a session id in a cookie, and merged into the user's cart on login. That merge is a real piece of work in module 12.

@@unique([cartId, variantId]) so adding the same variant twice increases the quantity rather than creating a second line. That is a story criterion, enforced by the database rather than by remembering.

No price on the cart item. The cart shows the current price, which is the decision from the stories lesson. Storing a price here would mean storing a stale one.

Orders

Where it gets interesting.

enum OrderStatus {
  PENDING_PAYMENT
  PLACED
  PACKED
  OUT_FOR_DELIVERY
  DELIVERED
  CANCELLED
}

enum PaymentMethod {
  ONLINE
  CASH_ON_DELIVERY
}

model Order {
  id            String        @id @default(cuid())
  orderNumber   String        @unique
  userId        String
  user          User          @relation(fields: [userId], references: [id])

  status        OrderStatus   @default(PENDING_PAYMENT)
  paymentMethod PaymentMethod

  // The address as it was, not a live reference.
  addressId     String?
  address       Address?      @relation(fields: [addressId], references: [id])
  deliveryName     String
  deliveryPhone    String
  deliveryLine1    String
  deliveryLine2    String?
  deliveryLandmark String?
  deliveryCity     String
  deliveryPincode  String

  slotId        String?
  slot          DeliverySlot? @relation(fields: [slotId], references: [id])
  slotDate      DateTime?
  slotLabel     String?

  subtotalPaise Int
  deliveryPaise Int
  totalPaise    Int

  items         OrderItem[]
  payments      Payment[]

  placedAt      DateTime?
  packedAt      DateTime?
  deliveredAt   DateTime?
  cancelledAt   DateTime?
  createdAt     DateTime      @default(now())
  updatedAt     DateTime      @updatedAt

  @@index([userId, createdAt])
  @@index([status, createdAt])
  @@map("orders")
}

model OrderItem {
  id             String  @id @default(cuid())
  orderId        String
  order          Order   @relation(fields: [orderId], references: [id], onDelete: Cascade)

  variantId      String?
  variant        Variant? @relation(fields: [variantId], references: [id])

  // The product as it was when ordered.
  productName    String
  variantLabel   String
  sku            String
  unitPricePaise Int
  quantity       Int
  linePaise      Int

  @@map("order_items")
}

The important idea: orders are a snapshot

An order stores what it was, not a reference to what things are now.

The delivery address is copied onto the order. The product name, variant label, SKU and unit price are copied onto the order item. variantId is kept as an optional link for reporting, and nothing reads it to display the order.

Why this matters, concretely:

The customer moves house. Last month's order must still show where it went.
The owner renames "Atta 5kg" to "Aashirvaad Select Atta 5kg". Old invoices must not silently change.
The owner raises the price. Every past order must keep what was charged.
A product is discontinued and deactivated. Old orders must still make sense.

A live reference means your order history rewrites itself as the catalogue changes. That is not merely untidy — an invoice that does not match what the customer paid is a commercial and legal problem.

This is also why variantId is optional and not cascade-deleted. Deleting a variant must never delete order lines.

linePaise is stored rather than computed from price times quantity. Redundant today, and it is what a receipt says. If a future discount changes how a line total is worked out, the stored value is still the truth about that order.

Money on the order

subtotalPaise, deliveryPaise, totalPaise — all stored. The total is not recomputed on display, because the arithmetic that produced it is fixed at the moment of purchase.

orderNumber separate from id: a customer quoting KIR-2026-0912 on the phone is friendlier than a cuid, and it does not expose how many orders exist.

Timestamps per transition

placedAt, packedAt, deliveredAt, cancelledAt rather than one statusChangedAt. The owner wants to know how long orders sit before packing, and a single field only tells you about the most recent change.

Delivery slots

model DeliverySlot {
  id        String   @id @default(cuid())
  label     String
  startHour Int
  endHour   Int
  capacity  Int      @default(20)
  isActive  Boolean  @default(true)
  sortOrder Int      @default(0)

  orders    Order[]

  @@map("delivery_slots")
}

Slots are templates — "Morning, 9am to 12pm" — and the order records both the slot and the date. capacity lets the shop stop accepting twenty orders for one morning.

The order copies slotDate and slotLabel for the same snapshot reason: the owner renaming a slot must not change what past orders say.

Payments

enum PaymentStatus {
  PENDING
  SUCCEEDED
  FAILED
  REFUNDED
}

model Payment {
  id            String        @id @default(cuid())
  orderId       String
  order         Order         @relation(fields: [orderId], references: [id])

  provider      String
  providerRef   String?       @unique
  amountPaise   Int
  status        PaymentStatus @default(PENDING)

  idempotencyKey String?      @unique

  createdAt     DateTime      @default(now())
  updatedAt     DateTime      @updatedAt

  @@index([orderId])
  @@map("payments")
}

Payments are a separate table, and an order can have several. A failed attempt followed by a successful one is two rows, and you want both — "why was I charged twice" is answered by the payment history, not guessed at.

providerRef is unique, so the same gateway transaction cannot be recorded twice. That is the database enforcing what a webhook handler might get wrong.

idempotencyKey is the mechanism from module 1's note on POST not being idempotent. Module 14 builds it.

What this model does not do

Being explicit, as in module 6:

No stock reservation. Stock is decremented when the order is placed. A customer holding items in a cart does not reserve them. That is the simpler choice and it means a cart can go out of stock — which the stories already handle.

No partial fulfilment. An order ships complete or is cancelled.

No order editing after placement. Cancel and reorder.

No audit log. Who changed a price and when is not recorded. Worth adding for a real shop.

Check your work

Why addresses are a separate table: a customer has several, and an order must keep the one it was delivered to.

Why the cart is in the database rather than a cookie: it must survive closing the browser and moving between devices.

Why @@unique([cartId, variantId]): adding the same variant twice must increase quantity rather than create a second line, enforced by the database.

Why no price on a cart item: the cart shows the current price, so a stored one would be stale.

The snapshot principle: an order stores what things were, not references to what they are now — otherwise your order history rewrites itself as the catalogue changes.

Why variantId on an order item is optional and not cascading: deleting a variant must never delete order lines.

Why linePaise is stored despite being computable: it is what the receipt said, and it stays true if the calculation later changes.

Why payments are a separate table: an order can have a failed attempt and a successful one, and both matter.

Practice

Write the full schema and migrate it. Read the generated SQL.
Create a user, an address and an order through Prisma Studio.
Change the user's address and confirm the order still shows the old one.
Rename a product and confirm an existing order item keeps the old name.
Deactivate a variant and confirm past order items still render.
Try adding the same variant to a cart twice. Confirm the unique constraint stops a second line.
Delete a variant that appears on an order. Confirm the order item survives and the link becomes null.
Add two payments to one order, one failed and one succeeded. Write the query that finds the successful one.
Write down three things this model cannot express, and say whether each matters.
Argue the other side of the snapshot decision: what would be easier if order items referenced the live product? Decide whether it is worth it.

Next: writing the architecture decisions down.