Data Models Reference

Related docs: API Design · Payment Architecture · Order Lifecycle · Inventory System · ZRA Invoicing · Loyalty & Coupons

---

1. Core Models

User

model User {
  id               String    @id @default(cuid())
  email            String    @unique
  firstName        String?
  lastName         String?
  phone            String?
  role             UserRole  @default(CUSTOMER)
  isEmailVerified  Boolean   @default(false)
  passwordHash     String?   // null for OAuth users
  mfaEnabled       Boolean   @default(false)
  mfaSecret        String?   // Encrypted TOTP secret
  mfaMethods       String[]  // ['TOTP', 'EMAIL', 'SMS']
  createdAt        DateTime  @default(now())
  updatedAt        DateTime  @updatedAt

  // Relations
  orders           Order[]
  sessions         Session[]
  notifications    Notification[]
  shops            ShopOwner[]
  sellerApplication SellerApplication?
  deliveryAgent    DeliveryAgent?
  loyaltyPoints    LoyaltyPoint[]
  fraudEvents      FraudEvent[]
}

enum UserRole {
  CUSTOMER
  SHOP_OWNER
  SERVICE_PROVIDER
  DELIVERY_AGENT
  PLATFORM_ADMIN
  MODERATOR
  FRAUD_ANALYST
  FINANCE_ADMIN
  SUPPORT_AGENT
  SELLER
  FLEET_MANAGER
  SYSTEM
}

Shop

model Shop {
  id          String    @id @default(cuid())
  name        String    @unique
  slug        String    @unique
  description String?
  logoUrl     String?
  coverUrl    String?
  isApproved  Boolean   @default(false)
  isVerified  Boolean   @default(false)
  isLive      Boolean   @default(false)
  approvedAt  DateTime?
  approvedBy  String?
  tpin        String?   // ZRA TPIN for Smart Invoice
  createdAt   DateTime  @default(now())
  updatedAt   DateTime  @updatedAt

  // Relations
  owners      ShopOwner[]
  products    Product[]
  orders      OrderItem[]
  payoutMethods ShopPayoutMethod[]
  settlements Settlement[]
  coupons     Coupon[]
  loyaltyProgram LoyaltyProgram?
}

Product

model Product {
  id              String        @id @default(cuid())
  shopId          String
  name            String
  slug            String
  description     String?
  price           Decimal       @db.Decimal(10, 2)
  compareAtPrice  Decimal?      @db.Decimal(10, 2)
  pricingModel    PricingModel  @default(FIXED)
  sku             String        @unique
  barcode         String?
  stockQuantity   Int           @default(0)
  lowStockThreshold Int         @default(10)
  isActive        Boolean       @default(true)
  isModerated     Boolean       @default(false)
  moderationStatus ModerationStatus @default(PENDING)
  images          ProductImage[]
  createdAt       DateTime      @default(now())
  updatedAt       DateTime      @updatedAt

  // Relations
  shop            Shop          @relation(fields: [shopId], references: [id])
  variants        ProductVariant[]
  options         ProductOption[]
  wholesaleTiers  WholesaleTier[]
  orderItems      OrderItem[]
  stockMovements  StockMovement[]
}

enum PricingModel {
  FIXED
  RANGED
  NEGOTIABLE
  QUOTATION
  PROMOTIONAL
  WHOLESALE
}

enum ModerationStatus {
  PENDING
  APPROVED
  FLAGGED
  REJECTED
}

ProductVariant

model ProductVariant {
  id          String   @id @default(cuid())
  productId   String
  sku         String   @unique
  barcode     String?
  price       Decimal  @db.Decimal(10, 2)
  stockQuantity Int    @default(0)
  options     Json     // { "Color": "Red", "Size": "Large" }
  isActive    Boolean  @default(true)
  createdAt   DateTime @default(now())

  product     Product  @relation(fields: [productId], references: [id])
  orderItems  OrderItem[]
}

WholesaleTier

model WholesaleTier {
  id          String   @id @default(cuid())
  productId   String
  minQuantity Int
  maxQuantity Int?
  price       Decimal  @db.Decimal(10, 2)
  createdAt   DateTime @default(now())

  product     Product  @relation(fields: [productId], references: [id])
}

---

2. Order Models

Order

model Order {
  id              String        @id @default(cuid())
  orderNumber     String        @unique
  userId          String
  status          OrderStatus   @default(PENDING)
  paymentStatus   PaymentStatus @default(PENDING)
  paymentMethod   String?       // 'MTN_MONEY' | 'AIRTEL_MONEY' | 'ZAMTEL_MONEY' | 'CARD'
  subtotal        Decimal       @db.Decimal(10, 2)
  tax             Decimal       @db.Decimal(10, 2)
  deliveryFee     Decimal       @db.Decimal(10, 2) @default(0)
  discount        Decimal       @db.Decimal(10, 2) @default(0)
  loyaltyDiscount Decimal       @db.Decimal(10, 2) @default(0)
  total           Decimal       @db.Decimal(10, 2)
  shippingAddress Json          // Stored as JSON (acts as delivery address)
  couponCode      String?
  notes           String?
  consentGivenAt  DateTime?     // DPA consent timestamp
  createdAt       DateTime      @default(now())
  updatedAt       DateTime      @updatedAt

  // Relations
  user            User          @relation(fields: [userId], references: [id])
  items           OrderItem[]
  inboundPayments InboundPayment[]
  deliveryConfirmation DeliveryConfirmation?
  trackingEvents  TrackingEvent[]
  zraInvoice      ZraInvoice?
}

enum OrderStatus {
  PENDING
  CONFIRMED
  PROCESSING
  SHIPPED
  OUT_FOR_DELIVERY
  DELIVERED
  CANCELLED
  REFUNDED
}

enum PaymentStatus {
  PENDING
  PAID
  FAILED
  REFUNDED
}

OrderItem

model OrderItem {
  id               String           @id @default(cuid())
  orderId          String
  productId        String
  shopId           String
  variantId        String?
  quantity         Int
  price            Decimal          @db.Decimal(10, 2)
  vendorAmount     Decimal?         @db.Decimal(10, 2)
  platformFee      Decimal?         @db.Decimal(10, 2)
  settlementStatus SettlementStatus @default(HELD)
  createdAt        DateTime         @default(now())

  order            Order            @relation(fields: [orderId], references: [id])
  product          Product          @relation(fields: [productId], references: [id])
  shop             Shop             @relation(fields: [shopId], references: [id])
}

enum SettlementStatus {
  HELD
  RELEASABLE
  PAID_OUT
}

---

3. Payment Models

InboundPayment

model InboundPayment {
  id               String              @id @default(cuid())
  orderId          String
  gateway          PaymentGateway      // 'PAWAPAY' | 'FLUTTERWAVE' | 'MOCK'
  gatewayReference String?             // depositId (PawaPay) or tx_ref (Flutterwave)
  status           InboundPaymentStatus @default(PENDING)
  amount           Decimal             @db.Decimal(10, 2)
  currency         String              @default("ZMW")
  type             String              @default("DEPOSIT")
  idempotencyKey   String              @unique
  metadata         Json?
  fraudScore       Int?                // 0-100, null if not evaluated
  fraudStatus      String?             // 'PASSED' | 'BLOCKED' | 'REVIEW'
  createdAt        DateTime            @default(now())
  updatedAt        DateTime            @updatedAt

  order            Order               @relation(fields: [orderId], references: [id])
}

enum InboundPaymentStatus {
  PENDING
  CAPTURED
  FAILED
  REFUNDED
}

enum PaymentGateway {
  PAWAPAY
  FLUTTERWAVE
  MOCK
}

VendorPayout

model VendorPayout {
  id               String       @id @default(cuid())
  shopId           String
  orderId          String
  orderItemId      String
  gateway          PaymentGateway
  gatewayReference String?      // payoutId from PawaPay/Flutterwave
  amount           Decimal      @db.Decimal(10, 2)
  currency         String       @default("ZMW")
  status           PayoutStatus @default(PENDING)
  createdAt        DateTime     @default(now())
  updatedAt        DateTime     @updatedAt

  shop             Shop         @relation(fields: [shopId], references: [id])
}

enum PayoutStatus {
  PENDING
  PROCESSING
  COMPLETED
  FAILED
}

WebhookEvent

model WebhookEvent {
  id        String   @id @default(cuid())
  gateway   String   // 'PAWAPAY' | 'FLUTTERWAVE'
  reference String   // depositId / tx_ref (unique per gateway)
  event     String   // 'deposit.completed' | 'charge.completed' etc.
  payload   Json
  createdAt DateTime @default(now())

  @@unique([gateway, reference])
}

ShopPayoutMethod

model ShopPayoutMethod {
  id               String   @id @default(cuid())
  shopId           String
  type             String   // 'MOBILE_MONEY' | 'BANK'
  mno              String?  // 'MTN' | 'AIRTEL' | 'ZAMTEL'
  phoneNumber      String?
  bankName         String?
  accountNumber    String?
  accountName      String?
  isDefault        Boolean  @default(false)
  createdAt        DateTime @default(now())

  shop             Shop     @relation(fields: [shopId], references: [id])
}

---

4. Inventory Models

StockMovement

model StockMovement {
  id          String           @id @default(cuid())
  productId   String
  variantId   String?
  type        StockMovementType
  quantity    Int
  reason      String?
  referenceId String?          // Order ID, adjustment ID, etc.
  createdBy   String
  createdAt   DateTime         @default(now())

  product     Product          @relation(fields: [productId], references: [id])
}

enum StockMovementType {
  SALE
  RETURN
  ADJUSTMENT
  STOCK_TAKE
  RECEIPT
  DAMAGE
}

ProductImage

model ProductImage {
  id               String           @id @default(cuid())
  productId        String
  url              String
  alt              String?
  isPrimary        Boolean          @default(false)
  moderationStatus ModerationStatus @default(PENDING)
  createdAt        DateTime         @default(now())

  product          Product          @relation(fields: [productId], references: [id])
}

---

5. Delivery Models

DeliveryAgent

model DeliveryAgent {
  id              String              @id @default(uuid())
  userId          String              @unique
  agentType       DeliveryAgentType   @default(INDIVIDUAL)
  status          DeliveryAgentStatus @default(PENDING)
  isAvailable     Boolean             @default(true)
  vehicleType     String?
  licenseNumber   String?
  parentCompanyId String?
  createdAt       DateTime            @default(now())
  updatedAt       DateTime            @updatedAt

  user            User                @relation(fields: [userId], references: [id])
  confirmations   DeliveryConfirmation[]
  trackingEvents  TrackingEvent[]
}

enum DeliveryAgentType {
  INDIVIDUAL
  COURIER_COMPANY
}

enum DeliveryAgentStatus {
  PENDING
  ACTIVE
  SUSPENDED
  REJECTED
}

DeliveryConfirmation

model DeliveryConfirmation {
  id              String    @id @default(uuid())
  orderId         String    @unique
  agentId         String?
  pin             String
  signatureImage  String?   // Cloudinary URL
  signatureHash   String?   // SHA-256 tamper-evident hash
  signatoryName   String?
  signatoryPhone  String?
  photoUrl        String?
  confirmedAt     DateTime?
  status          String    @default("PENDING") // PENDING, CONFIRMED

  order           Order          @relation(fields: [orderId], references: [id])
  agent           DeliveryAgent? @relation(fields: [agentId], references: [id])
}

TrackingEvent

model TrackingEvent {
  id            String           @id @default(uuid())
  orderId       String
  agentId       String?
  type          TrackingEventType
  data          Json
  lat           Float?
  lng           Float?
  accuracy      Float?
  speed         Float?
  heading       Float?
  battery       Int?
  locationName  String?
  correlationId String?
  createdAt     DateTime         @default(now())

  order         Order            @relation(fields: [orderId], references: [id])
}

enum TrackingEventType {
  LOCATION_UPDATE
  STATUS_CHANGE
  PICKUP
  ARRIVING
  DELIVERED
  FAILED_ATTEMPT
}

---

6. Coupon & Loyalty Models

Coupon

model Coupon {
  id              String       @id @default(cuid())
  shopId          String?
  code            String       @unique
  type            CouponType
  value           Decimal      @db.Decimal(10, 2)
  minOrderAmount  Decimal?     @db.Decimal(10, 2)
  maxDiscount     Decimal?     @db.Decimal(10, 2)
  usageLimit      Int?
  usageCount      Int          @default(0)
  startDate       DateTime
  endDate         DateTime?
  applicableCategories String[]
  applicableProducts   String[]
  isActive        Boolean      @default(true)
  createdAt       DateTime     @default(now())

  shop            Shop?        @relation(fields: [shopId], references: [id])
}

enum CouponType {
  PERCENTAGE
  FIXED_AMOUNT
  FREE_DELIVERY
}

LoyaltyProgram

model LoyaltyProgram {
  id              String   @id @default(cuid())
  shopId          String   @unique
  pointsPerZMW    Decimal  @db.Decimal(10, 2) @default(1.00)
  redemptionRate  Decimal  @db.Decimal(10, 4) @default(0.0100)
  minRedeemPoints Int      @default(100)
  expiryMonths    Int      @default(12)
  isActive        Boolean  @default(true)
  createdAt       DateTime @default(now())

  shop            Shop     @relation(fields: [shopId], references: [id])
  points          LoyaltyPoint[]
}

LoyaltyPoint

model LoyaltyPoint {
  id          String   @id @default(cuid())
  userId      String
  shopId      String
  programId   String
  points      Int
  type        String   // 'EARNED' | 'REDEEMED' | 'EXPIRED'
  orderId     String?
  expiresAt   DateTime?
  createdAt   DateTime @default(now())

  user        User     @relation(fields: [userId], references: [id])
  program     LoyaltyProgram @relation(fields: [programId], references: [id])
}

---

7. Fraud Detection Models

FraudEvent

model FraudEvent {
  id              String   @id @default(cuid())
  userId          String?
  orderId         String?
  paymentId       String?
  score           Int      // 0-100
  status          String   // 'PASSED' | 'BLOCKED' | 'REVIEW'
  rulesTriggered  String[] // ['VELOCITY', 'AMOUNT_ANOMALY', 'SELF_DEALING']
  reviewedBy      String?
  reviewedAt      DateTime?
  reviewAction    String?  // 'APPROVED' | 'REJECTED'
  createdAt       DateTime @default(now())

  user            User?    @relation(fields: [userId], references: [id])
}

---

8. ZRA Invoicing Models

ZraInvoice

model ZraInvoice {
  id              String   @id @default(cuid())
  orderId         String   @unique
  tpin            String
  vsdcNumber      String?
  invoiceNumber   String?
  payload         Json
  transmissionStatus String @default("PENDING") // PENDING, SENT, FAILED, MOCK
  responsePayload Json?
  retryCount      Int      @default(0)
  lastRetryAt     DateTime?
  createdAt       DateTime @default(now())
  updatedAt       DateTime @updatedAt

  order           Order    @relation(fields: [orderId], references: [id])
}

---

9. Content Moderation Models

ModerationFlag

model ModerationFlag {
  id              String   @id @default(cuid())
  assetId         String
  assetType       String   // 'PRODUCT_IMAGE' | 'SHOP_LOGO' | 'SHOP_BANNER' | 'KYC_DOCUMENT'
  imageUrl        String
  scores          Json     // Sightengine scores
  status          String   @default("FLAGGED") // FLAGGED, APPROVED, REJECTED, ESCALATED
  reviewedBy      String?
  reviewedAt      DateTime?
  reviewAction    String?
  createdAt       DateTime @default(now())
}

---

10. Key Relationships

---

11. Enum Quick Reference

Enum	Values
UserRole	CUSTOMER, SHOP_OWNER, SERVICE_PROVIDER, DELIVERY_AGENT, PLATFORM_ADMIN, MODERATOR, FRAUD_ANALYST, FINANCE_ADMIN, SUPPORT_AGENT, SELLER, FLEET_MANAGER, SYSTEM
OrderStatus	PENDING, CONFIRMED, PROCESSING, SHIPPED, OUT_FOR_DELIVERY, DELIVERED, CANCELLED, REFUNDED
PaymentStatus	PENDING, PAID, FAILED, REFUNDED
SettlementStatus	HELD, RELEASABLE, PAID_OUT
InboundPaymentStatus	PENDING, CAPTURED, FAILED, REFUNDED
PaymentGateway	PAWAPAY, FLUTTERWAVE, MOCK
PayoutStatus	PENDING, PROCESSING, COMPLETED, FAILED
PricingModel	FIXED, RANGED, NEGOTIABLE, QUOTATION, PROMOTIONAL, WHOLESALE
ModerationStatus	PENDING, APPROVED, FLAGGED, REJECTED
StockMovementType	SALE, RETURN, ADJUSTMENT, STOCK_TAKE, RECEIPT, DAMAGE
DeliveryAgentType	INDIVIDUAL, COURIER_COMPANY
DeliveryAgentStatus	PENDING, ACTIVE, SUSPENDED, REJECTED
TrackingEventType	LOCATION_UPDATE, STATUS_CHANGE, PICKUP, ARRIVING, DELIVERED, FAILED_ATTEMPT
CouponType	PERCENTAGE, FIXED_AMOUNT, FREE_DELIVERY

---

For internal use only. Do not distribute outside Pakashop engineering.