Prisma Data Models & Query Patterns

1. Core Prisma Database Schema

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

generator client {
  provider = "prisma-client-js"
}

model User {
  id                   String                @id @default(uuid())
  whatsappNumber       String                @unique
  name                 String?
  email                String?               @unique
  role                 Role                  @default(CUSTOMER)
  allowedMenus         Json?                 @default("[]")
  position             String?
  createdAt            DateTime              @default(now())
  updatedAt            DateTime              @updatedAt

  orders               Order[]
  addresses            UserAddress[]
  pushSubscriptions    PushSubscription[]
  financialEntries     FinancialEntry[]      @relation("FinancialEntryCreator")
  paymentVerifications PaymentVerification[] @relation("AdminVerifier")

  @@index([whatsappNumber])
  @@map("users")
}

model Menu {
  id               String        @id @default(uuid())
  name             String        @unique
  description      String?
  price            Float
  cogs             Float         @default(0) // Cost of Goods Sold (HPP)
  isAvailable      Boolean       @default(true)
  isCatering       Boolean       @default(false)
  cateringType     CateringType?
  preparationTime  Int           @default(30) // in minutes
  addons           Json?         @default("[]")
  categoryId       String?
  category         Category?     @relation(fields: [categoryId], references: [id], onDelete: Cascade)
  orderItems       OrderItem[]
  reviewsList      Review[]

  @@index([categoryId])
  @@index([isAvailable])
  @@map("menus")
}

model Order {
  id                    String               @id @default(uuid())
  orderNumber           String               @unique
  userId                String
  status                OrderStatus          @default(PENDING_PAYMENT)
  totalPrice            Float
  shippingCost          Float                @default(0)
  shippingType          ShippingType         @default(DINE_IN)
  shippingAddress       String?
  shippingCity          String?
  shippingPhone         String?
  qbizInvoiceId         String?
  qbizPaymentUrl        String?
  user                  User                 @relation(fields: [userId], references: [id], onDelete: Cascade)
  items                 OrderItem[]
  paymentVerification   PaymentVerification?
  createdAt             DateTime             @default(now())
  updatedAt             DateTime             @updatedAt

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

---

2. Common Prisma Query Patterns in Codebase

Fetching Order Details with Relations (orderService.ts)

const order = await prisma.order.findUnique({
  where: { id: orderId },
  include: {
    items: {
      include: {
        menu: {
          select: { name: true, image: true, price: true }
        }
      }
    },
    user: {
      select: { name: true, whatsappNumber: true, email: true }
    },
    paymentVerification: true
  }
});

Atomic Financial Transaction Logging (financeService.ts)

await prisma.$transaction(async (tx) => {
  // 1. Update order status
  const updatedOrder = await tx.order.update({
    where: { id: orderId },
    data: { status: 'PAYMENT_VERIFIED' }
  });

  // 2. Log double-entry financial ledger income
  await tx.financialEntry.create({
    data: {
      type: 'INCOME',
      categoryId: salesCategoryId,
      amount: updatedOrder.totalPrice,
      description: `Order Settlement #${updatedOrder.orderNumber}`,
      reference: updatedOrder.id,
      createdBy: adminUserId
    }
  });
});