Domain: Order

Scenario: Order Lifecycle (Creation → Fulfillment → Settlement)

1. Business Goal

Order is the final binding transaction after checkout. The system manages:

• Order creation: From successful checkout
• Fulfillment: Shipping/ticket delivery
• Refunds: Partial and full refunds
• Commission settlement: 30-day auto-settlement or manual
• Payout: Payment to merchants and promoters

2. Trigger & Entry

2.1 How Many Records Are Created Per Order?

Short Answer: One checkout creates:

Creation Logic

// 1. Order: Always 1 per checkout
Order {
  id: checkout.id,           // Reuse checkout ID
  orderNumber: checkout.orderNumber,
  consumerId: checkout.consumerId,
  status: COMPLETED
}

// 2. OrderLineItem: 1 per distinct product variant
// If user buys 2×VariantA + 1×VariantB = 2 OrderLineItems
OrderLineItem {
  id: new UUID(),
  orderId: Order.id,
  promoterVariantId,
  merchantVariantId,
  quantity: 2  // if 2 units of same variant
}

// 3. OrderLineItemUnit: 1 per quantity (tickets only)
// For 2×VariantA ticket = 2 OrderLineItemUnits with QR codes
OrderLineItemUnit {
  id: new UUID(),
  orderLineItemId: OrderLineItem.id,
  numberCode: "ABC-12345"  // QR code for scanning
}

// 4. MerchantOrder: 1 per distinct merchant
// Grouped by merchantId from OrderLineItems
MerchantOrder {
  id: new UUID(),  // Different from Order.id
  orderId: Order.id,
  merchantId: "M1",
  orderNumber: Order.orderNumber  // Same as Order
}

// 5. PromoterOrder: 0-1 per distinct promoter
// Only created if promoterId exists on OrderLineItem
// Grouped by promoterId from OrderLineItems
PromoterOrder {
  id: new UUID(),  // Different from Order.id
  orderId: Order.id,
  promoterId: "P1",
  orderNumber: Order.orderNumber  // Same as Order
}

Example Scenarios

Scenario 1: Simple Order (1 merchant, 1 promoter, 2 items)

Consumer buys: 2×ProductA from MerchantM1 via PromoterP1

Creates:
├── Order (1)
├── OrderLineItem (1) - quantity: 2
├── OrderLineItemUnit (2) - if tickets
├── MerchantOrder (1) - merchantId: M1
├── MerchantLineItem (1)
├── PromoterOrder (1) - promoterId: P1
└── PromoterLineItem (1)

Scenario 2: Multi-Merchant Order (2 merchants, same promoter)

Consumer buys: ProductA from M1 + ProductB from M2, both via P1

Creates:
├── Order (1)
├── OrderLineItem (2) - one for each product
├── OrderLineItemUnit (N) - if tickets
├── MerchantOrder (2) - one for M1, one for M2
├── MerchantLineItem (2)
├── PromoterOrder (1) - single PromoterOrder aggregates both merchants
└── PromoterLineItem (2)

Scenario 3: Multi-Promoter Order (different items, different promoters)

Consumer buys: ProductA via P1 + ProductB via P2

Creates:
├── Order (1)
├── OrderLineItem (2)
├── MerchantOrder (N) - depends on merchant count
├── PromoterOrder (2) - one for P1, one for P2
└── ...

Scenario 4: No Promoter (Direct from merchant)

Consumer buys: ProductA directly from MerchantM1 (no promoter)

Creates:
├── Order (1)
├── OrderLineItem (1)
├── MerchantOrder (1)
└── PromoterOrder (0) - NOT created when no promoterId

Real-World Example: Buying 2 Products from Different Stores

Let's say user 小乐 buys 2 products from different stores, via promoter 小红:

购物车内容:
├── 商品A (T恤) - 店铺: AppleStore, 推广者: 小红, 价格: $50
└── 商品B (耳机) - 店铺: SonyShop, 推广者: 小红, 价格: $150

支付总额: $200

What Gets Stored in Database?

-- 1. Order 表 (1条记录) - 消费者视角
INSERT INTO "Order" (
  id,                  -- = checkout.id (复用)
  orderNumber,         -- "ORD-2024-1207-001"
  consumerId,          -- "小乐的userId"
  status,              -- "COMPLETED"
  paymentStatus,       -- "PAID"
  totalToPay,          -- 200.00
  totalPaid,           -- 200.00
  totalLineItemSubtotal, -- 200.00
  totalShippingPrice,  -- 10.00
  commissionStatus     -- "PENDING"
);
-- 结果: 1条记录，小乐看到的是这个订单

-- 2. OrderLineItem 表 (2条记录) - 每个商品一条
INSERT INTO "OrderLineItem" (
  id,                  -- 新UUID
  orderId,             -- Order.id
  lineItemNumber,      -- "1", "2"
  promoterVariantId,   -- 商品A的推广变体ID, 商品B的推广变体ID
  merchantVariantId,   -- 商品A的商户变体ID, 商品B的商户变体ID
  merchantId,          -- "AppleStore", "SonyShop"
  promoterId,          -- "小红"
  quantity,            -- 1, 1
  unitPrice,           -- 50.00, 150.00
  lineItemSubtotal     -- 50.00, 150.00
) VALUES
  ('UUID-1', Order.id, '1', ..., 'AppleStore', '小红', 1, 50.00, 50.00),
  ('UUID-2', Order.id, '2', ..., 'SonyShop', '小红', 1, 150.00, 150.00);
-- 结果: 2条记录，按产品变体分组

-- 3. MerchantOrder 表 (2条记录) - 每个店铺一条
INSERT INTO "MerchantOrder" (
  id,                  -- 新UUID (不同于Order.id)
  orderId,             -- Order.id (关联到主订单)
  orderNumber,         -- "ORD-2024-1207-001" (与Order相同)
  merchantId,          -- "AppleStore", "SonyShop"
  promoterId,          -- "小红"
  subtotal,            -- 50.00, 150.00
  totalCommissionGross, -- 10.00 (20% of 50), 30.00 (20% of 150)
  totalMerchantPayoutToPay -- 40.00, 120.00
) VALUES
  ('UUID-M1', Order.id, 'ORD-2024-1207-001', 'AppleStore', '小红', 50.00, 10.00, 40.00),
  ('UUID-M2', Order.id, 'ORD-2024-1207-001', 'SonyShop', '小红', 150.00, 30.00, 120.00);
-- 结果: 2条记录，按merchantId分组，AppleStore和SonyShop各一条

-- 4. MerchantLineItem 表 (2条记录) - 每个商品对应商户一条
INSERT INTO "MerchantLineItem" (
  id,                  -- 新UUID
  merchantOrderId,     -- UUID-M1, UUID-M2 (关联到MerchantOrder)
  orderLineItemId,     -- UUID-1, UUID-2 (关联到OrderLineItem)
  promoterId,          -- "小红"
  merchantId,          -- "AppleStore", "SonyShop"
  lineItemSubtotal,    -- 50.00, 150.00
  commissionGross,     -- 10.00, 30.00
  commissionNet        -- 10.00, 30.00
) VALUES
  ('UUID-ML1', 'UUID-M1', 'UUID-1', '小红', 'AppleStore', 50.00, 10.00, 10.00),
  ('UUID-ML2', 'UUID-M2', 'UUID-2', '小红', 'SonyShop', 150.00, 30.00, 30.00);
-- 结果: 2条记录，与OrderLineItem一一对应

-- 5. PromoterOrder 表 (1条记录) - 只有一个推广者
INSERT INTO "PromoterOrder" (
  id,                  -- 新UUID (不同于Order.id)
  orderId,             -- Order.id (关联到主订单)
  orderNumber,         -- "ORD-2024-1207-001" (与Order相同)
  promoterId,          -- "小红"
  totalCommissionGross, -- 40.00 (10.00 + 30.00)
  totalCommissionNet,   -- 40.00
  totalPayoutToPay     -- 40.00 (小红的总佣金)
) VALUES
  ('UUID-P1', Order.id, 'ORD-2024-1207-001', '小红', 40.00, 40.00, 40.00);
-- 结果: 1条记录！因为只有小红一个推广者，两个商品的佣金合并到这里

-- 6. PromoterLineItem 表 (2条记录) - 每个商品对应推广者一条
INSERT INTO "PromoterLineItem" (
  id,                  -- 新UUID
  promoterOrderId,     -- UUID-P1 (关联到PromoterOrder)
  orderLineItemId,     -- UUID-1, UUID-2 (关联到OrderLineItem)
  promoterId,          -- "小红"
  merchantId,          -- "AppleStore", "SonyShop"
  lineItemSubtotal,    -- 50.00, 150.00
  commissionGross,     -- 10.00, 30.00
  commissionNet        -- 10.00, 30.00
) VALUES
  ('UUID-PL1', 'UUID-P1', 'UUID-1', '小红', 'AppleStore', 50.00, 10.00, 10.00),
  ('UUID-PL2', 'UUID-P1', 'UUID-2', '小红', 'SonyShop', 150.00, 30.00, 30.00);
-- 结果: 2条记录，与OrderLineItem一一对应

Summary Table

Key Points

1. Order.orderNumber = MerchantOrder.orderNumber = PromoterOrder.orderNumber

   • 三者共享同一个订单号，方便查询关联

2. 不同的 id:

   • Order.id = checkout.id (复用)
   • MerchantOrder.id = 新UUID (每个商户不同)
   • PromoterOrder.id = 新UUID (每个推广者不同)

3. PromoterOrder 合并:

   • 虽然商品来自2个不同店铺
   • 但推广者都是小红，所以只创建1条 PromoterOrder
   • 2个商品的佣金在这1条记录里汇总

4. 结算时:

   • AppleStore 收到: $50 - $10佣金 = $40
   • SonyShop 收到: $150 - $30佣金 = $120
   • 小红 收到: $40 佣金 (30天后结算)

Visual Diagram

┌─────────────────────────────────────────────────────────────────┐
│                        Order (小乐的订单)                        │
│  id: checkout-id                                                 │
│  orderNumber: "ORD-2024-1207-001"                               │
│  totalToPay: $200                                               │
│  consumerId: 小乐                                               │
└───────────────────────────┬─────────────────────────────────────┘
                            │
          ┌─────────────────┴─────────────────┐
          │                                   │
          ▼                                   ▼
┌───────────────────────┐         ┌───────────────────────┐
│  MerchantOrder (M1)   │         │  MerchantOrder (M2)   │
│  id: UUID-M1          │         │  id: UUID-M2          │
│  merchantId: AppleStore│       │  merchantId: SonyShop │
│  subtotal: $50        │         │  subtotal: $150       │
│  orderNumber: 同Order  │         │  orderNumber: 同Order  │
│  payoutToPay: $40     │         │  payoutToPay: $120    │
└───────────┬───────────┘         └───────────┬───────────┘
            │                                 │
            └─────────────────┬───────────────┘
                              │
                              ▼
              ┌───────────────────────────────────┐
              │     PromoterOrder (小红)          │
              │  id: UUID-P1                      │
              │  promoterId: 小红                 │
              │  orderNumber: 同Order             │
              │  totalCommissionGross: $40       │
              │  totalPayoutToPay: $40            │
              └───────────────────────────────────┘

Code Flow (How It Works)

// Step 1: CheckoutEntity.toCreateOrder() groups line items
const lineItemGroups = checkout.lineItems.map(item =>
  CheckLineItemDeriver.toPromoterAndMerchantLineItems(item)
);

// Step 2: Group by merchantId to create MerchantOrders
const merchantOrderInstances = _.groupBy(merchantLineItems, 'merchantId')
  .map((items, merchantId) => new MerchantOrderWithLineItemsEntity({
    id: v4(),              // NEW UUID for each merchant
    orderId: checkout.id, // Link to main Order
    merchantId: merchantId,
    orderNumber: checkout.orderNumber, // SAME as Order
    subtotal: sum(items.map(i => i.lineItemSubtotal))
  }));
// Result: 2 MerchantOrders (AppleStore, SonyShop)

// Step 3: Group by promoterId to create PromoterOrders
const promoterOrderInstances = _.groupBy(promoterLineItems, 'promoterId')
  .map((items, promoterId) => new PromoterOrderWithLineItemsEntity({
    id: v4(),              // NEW UUID for each promoter
    orderId: checkout.id, // Link to main Order
    promoterId: promoterId,
    orderNumber: checkout.orderNumber, // SAME as Order
    totalCommissionGross: sum(items.map(i => i.commissionGross))
  }));
// Result: 1 PromoterOrder (小红) - items from both merchants merged!

// Step 4: Create OrderLineItems (links everything together)
const orderLineItemInstances = lineItems.map(item => ({
  id: v4(),
  orderId: checkout.id,
  promoterVariantId: item.promoterVariantId,
  merchantVariantId: item.merchantVariantId,
  merchantId: item.merchantId,  // AppleStore or SonyShop
  promoterId: '小红'
}));
// Result: 2 OrderLineItems

3. Data Flow (Order Creation → Fulfillment → Settlement)

sequenceDiagram
    participant Consumer as Consumer
    participant Order as OrdersService
    participant DB as PostgreSQL
    participant Fulfillment as FulfillmentService
    participant Settlement as SettlementService
    participant Payout as PayoutService
    participant Stripe as Stripe API

    Note over Consumer: STEP 1: Order Creation (from Checkout)
    Consumer->>Order: POST /order (with payment confirmation)
    Order->>DB: SELECT Checkout WHERE id = checkoutId
    Order->>Order: validatePayment()

    Note over Order: Create main Order record
    Order->>DB: INSERT Order
    DB-->>Order: order.id, orderNumber

    Note over Order: Create OrderLineItem records
    loop for each checkout line item
        Order->>DB: INSERT OrderLineItem
        Note over Order: Create OrderLineItemUnits for tickets
        Order->>DB: INSERT OrderLineItemUnit[] (if ticket)
    end

    Note over Order: Create MerchantOrder per merchant
    loop for each unique merchantId
        Order->>DB: INSERT MerchantOrder
        Order->>DB: INSERT MerchantLineItem[]
    end

    Note over Order: Create PromoterOrder if applicable
    alt promoterId exists
        Order->>DB: INSERT PromoterOrder
        Order->>DB: INSERT PromoterLineItem[]
    end

    Order->>DB: UPDATE Checkout (status=COMPLETED)
    Order->>DB: DELETE Cart (purchased items)
    Order->>DB: UPDATE ProductVariant (inventoryQuantity -= sold)

    Order-->>Consumer: { order, orderNumber }

    Note over Consumer: STEP 2: Fulfillment
    Consumer->>Fulfillment: POST /orders/fulfillment/merchant/...
    Fulfillment->>DB: SELECT MerchantOrder WHERE orderNumber
    Fulfillment->>DB: INSERT OrderFulfillment

    Note over Fulfillment: Update line item fulfillment status
    Fulfillment->>DB: UPDATE OrderLineItem (fulfillmentStatus)
    Fulfillment->>DB: INSERT OrderFulfillmentLineItem[]

    alt Shopify integration
        Fulfillment->>Stripe: Sync fulfillment to Shopify
    end

    Fulfillment-->>Consumer: { fulfillment, trackingNumbers }

    Note over Consumer: STEP 3: Settlement (after 30 days or manual)
    Note over Settlement: Auto-settlement CronJob
    Settlement->>DB: SELECT Order WHERE commissionStatus=PENDING AND createdAt < 30 days
    loop for each order
        Settlement->>DB: UPDATE Order (commissionStatus=SETTLED)

        Note over Settlement: Create EarningShare records
        Settlement->>DB: INSERT EarningShare[]

        Note over Settlement: Update payout balances
        Settlement->>DB: UPDATE MerchantOrder (totalMerchantPayoutToPay)
        Settlement->>DB: UPDATE User (payoutBalance)
    end

    Note over Settlement: STEP 4: Payout
    Merchant->>Payout: POST /payout/withdraw
    Payout->>DB: SELECT User WHERE id AND payoutBalance > 0
    Payout->>DB: INSERT Payout (amount, status=IN_REVIEW)
    Payout-->>Merchant: { payoutId }

    Note over Payout: Admin approves payout
    Admin->>Payout: Admin approves
    Payout->>Stripe: POST /payouts (Stripe Connect)
    Stripe-->>Payout: { payoutId, status }
    Payout->>DB: UPDATE Payout (status=PAID)
    Payout->>DB: UPDATE User (payoutBalance -= amount)

4. DB Operations (Chronological)

5. Commission Calculation

// Per MerchantLineItem
commissionGross = lineItemSubtotal * commissionRate
commissionNet = commissionGross
              - lineItemDiscount
              - commissionDeduction
              - platformFee

// Merchant payout
totalMerchantPayoutToPay = SUM(merchantLineItems)
                        + shippingReimbursement
                        - totalCommissionDeduction
                        - merchantTaxDeduction
                        - paymentFeeDeduction

// Earning share (multi-level)
earningShared = earningBase * shareRate
totalPayoutBalance = earningShared - totalPayoutPaid

6. Order Status Flow

stateDiagram-v2
    [*] --> DRAFT: Create from checkout
    DRAFT --> IN_PROGRESS: Payment confirmed
    IN_PROGRESS --> COMPLETED: All items fulfilled
    IN_PROGRESS --> FAILED: Payment failed
    COMPLETED --> [*]

    note right of IN_PROGRESS
        Partial refunds allowed
        Commission status: PENDING
    end note

    note right of COMPLETED
        Auto-settle after 30 days
        Commission status: SETTLED
    end note

7. Ticket Fulfillment

For ticket products (Event tickets):

// OrderLineItemUnit tracks individual tickets
interface OrderLineItemUnit {
  id: string;
  orderLineItemId: string;
  status: 'IN_PROCESSING' | 'COMPLETED' | 'PARTIALLY_REDEEMED' | 'REFUNDED' | 'CANCELLED';
  numberCode: string;  // QR code value
  redeemedAt: DateTime | null;
  ticketRedemptions: TicketRedemption[];  // For multi-day passes
}

// QR Redemption flow
POST /order/qr-redemption/status
{
  orderNumber: "ORD-123",
  code: "ABC-123",
  action: "redeem"
}
→ Updates OrderLineItemUnit.status
→ Creates TicketRedemption record

8. Refund Handling

sequenceDiagram
    participant Admin as Admin
    participant Order as OrdersService
    participant Refund as RefundService
    participant Stripe as Stripe API
    participant DB as PostgreSQL

    Admin->>Refund: POST /orders/:orderId/refunds
    Refund->>DB: SELECT Order WHERE id
    Refund->>DB: SELECT OrderLineItem[] WHERE orderId

    Note over Refund: Calculate refund amounts
    Refund->>Refund: calculateRefund(lineItems, quantities)

    Note over Refund: Create OrderRefund record
    Refund->>DB: INSERT OrderRefund (amount, status=PENDING)

    Note over Refund: Process payment refund
    Refund->>Stripe: POST /refunds
    Stripe-->>Refund: { refundId, status }

    alt Refund successful
        Refund->>DB: UPDATE OrderRefund (status=SUCCESS)
        Refund->>DB: UPDATE Order (totalRefunded += amount)
        Refund->>DB: UPDATE OrderLineItem (refunded += refundAmount)

        Note over Refund: Adjust commission
        Refund->>DB: UPDATE MerchantLineItem (commissionRefunded)
        Refund->>DB: UPDATE MerchantOrder (totalMerchantPayoutToPay)
    else Refund failed
        Refund->>DB: UPDATE OrderRefund (status=FAILED)
    end

    Refund-->>Admin: { refundId, status }

9. Event/Queue Messages

10. File Map (Top 5 Must-Read)

11. DB Schema Snippet (Prisma)

model Order {
  id                   String      @id @default(uuid()) @db.Uuid
  consumerId           String      @map("consumer_id") @db.Uuid
  orderNumber          String      @unique @map("order_number")
  status               OrderStatus @default(DRAFT)
  type                 OrderType   @default(ONE_TIME_PURCHASE)

  // Contact info
  contact              Json?

  // Address
  shippingAddressInfo  Json?       @map("shipping_address_info")

  // Payment
  paymentStatus        PaymentStatus @default(PENDING)
  totalToPay           Float       @default(0.0) @map("total_to_pay")
  totalPaid            Float       @default(0.0) @map("total_paid")
  totalRefunded        Float       @default(0.0) @map("total_refunded")

  // Line items
  totalLineItemPrice   Float       @map("total_line_item_price")
  totalLineItemDiscount Float      @map("total_line_item_discount")
  totalLineItemSubtotal Float      @map("total_line_item_subtotal")

  // Shipping
  totalShippingPrice   Float       @map("total_shipping_price")
  totalShippingDiscount Float      @map("total_shipping_discount")

  // Tax
  totalTax             Float       @map("total_tax")

  // Commission
  commissionStatus     CommissionStatus @default(PENDING) @map("commission_status")

  // Relations
  lineItems            OrderLineItem[]
  merchantOrders       MerchantOrder[]
  promoterOrder        PromoterOrder?
  refunds              OrderRefund[]

  createdAt            DateTime    @default(now()) @map("created_at")
  updatedAt            DateTime    @updatedAt @map("updated_at")

  @@index([consumerId])
  @@index([orderNumber])
}

model OrderLineItem {
  id                  String              @id @default(uuid()) @db.Uuid
  orderId             String              @map("order_id") @db.Uuid
  order               Order               @relation(fields: [orderId], references: [id], onDelete: Cascade)
  lineItemNumber      String              @map("line_item_number")

  // Product refs
  promoterVariantId   String              @map("promoter_variant_id") @db.Uuid
  merchantVariantId   String              @map("merchant_variant_id") @db.Uuid
  variantInfo         Json                @map("variant_info")

  // Participants
  consumerId          String              @map("consumer_id") @db.Uuid
  merchantId          String              @map("merchant_id") @db.Uuid
  promoterId          String?             @map("promoter_id") @db.Uuid

  // Quantity & price
  quantity            Int                 @default(1)
  unitPrice           Float               @default(0.0) @map("unit_price")
  lineItemPrice       Float               @default(0.0) @map("line_item_price")
  lineItemDiscount    Float               @default(0.0) @map("line_item_discount")
  lineItemSubtotal    Float               @default(0.0) @map("line_item_subtotal")
  refunded            Float               @default(0.0)

  // Fulfillment
  fulfillmentStatus   FulfillmentStatus   @default(UNFULFILLED) @map("fulfillment_status")

  // Relations
  merchantLineItems    MerchantLineItem[]
  orderLineItemUnits  OrderLineItemUnit[]
  fulfillment         OrderFulfillmentLineItem[]

  createdAt           DateTime            @default(now()) @map("created_at")
  updatedAt           DateTime            @updatedAt @map("updated_at")

  @@index([orderId])
  @@index([promoterVariantId])
}

model OrderLineItemUnit {
  id                  String              @id @default(uuid()) @db.Uuid
  orderLineItemId     String              @map("order_line_item_id") @db.Uuid
  orderLineItem       OrderLineItem       @relation(fields: [orderLineItemId], references: [id], onDelete: Cascade)

  status              OrderLineItemUnitStatus @default(IN_PROCESSING)
  numberCode          String              @map("number_code")  // QR code
  redeemedAt          DateTime?           @map("redeemed_at")

  ticketRedemptions   TicketRedemption[]  // For multi-day passes

  createdAt           DateTime            @default(now()) @map("created_at")
  updatedAt           DateTime            @updatedAt @map("updated_at")

  @@index([orderLineItemId])
}

model MerchantOrder {
  id                        String         @id @default(uuid()) @db.Uuid
  orderId                   String         @map("order_id") @db.Uuid
  order                     Order          @relation(fields: [orderId], references: [id], onDelete: Cascade)

  merchantId                String         @map("merchant_id") @db.Uuid
  promoterId                String?        @map("promoter_id") @db.Uuid

  // Payment
  paymentStatus             PaymentStatus @default(PENDING)

  // Totals
  total                     Float
  totalCommissionGross      Float          @map("total_commission_gross")
  totalCommissionNet        Float          @map("total_commission_net")
  totalMerchantPayoutToPay  Float          @map("total_merchant_payout_to_pay")

  // Fulfillment
  fulfillment               OrderFulfillment?

  // Relations
  lineItems                 MerchantLineItem[]

  createdAt                 DateTime       @default(now()) @map("created_at")
  updatedAt                 DateTime       @updatedAt @map("updated_at")

  @@index([orderId])
  @@index([merchantId])
}

model MerchantLineItem {
  id                  String         @id @default(uuid()) @db.Uuid
  merchantOrderId      String         @map("merchant_order_id") @db.Uuid
  merchantOrder        MerchantOrder  @relation(fields: [merchantOrderId], references: [id], onDelete: Cascade)

  orderLineItemId      String         @map("order_line_item_id") @db.Uuid

  promoterId           String?        @map("promoter_id") @db.Uuid

  // Sale type
  saleType             SaleType       @default(PRODUCT)

  // Pricing
  lineItemSubtotal     Float          @map("line_item_subtotal")
  commissionGross      Float          @map("commission_gross")
  commissionNet        Float          @map("commission_net")
  commissionRate       Float?         @map("commission_rate")
  commissionRefunded   Float          @default(0.0) @map("commission_refunded")

  createdAt           DateTime        @default(now()) @map("created_at")

  @@index([merchantOrderId])
}

model PromoterOrder {
  id                  String        @id @default(uuid()) @db.Uuid
  orderId             String        @map("order_id") @db.Uuid
  order               Order         @relation(fields: [orderId], references: [id], onDelete: Cascade)
  promoterId          String        @map("promoter_id") @db.Uuid

  lineItems           PromoterLineItem[]

  createdAt           DateTime      @default(now()) @map("created_at")

  @@index([orderId])
  @@index([promoterId])
}

model OrderFulfillment {
  id                  String         @id @default(uuid()) @db.Uuid
  merchantOrderId      String         @unique @map("merchant_order_id") @db.Uuid
  merchantOrder        MerchantOrder  @relation(fields: [merchantOrderId], references: [id])

  trackingNumber      String?        @map("tracking_number")
  trackingNumbers     Json?          @map("tracking_numbers")  // Multiple
  trackingLink        String?        @map("tracking_link")
  vendor              String         @default("SHOPIFY")
  platform            String?

  lineItems           OrderFulfillmentLineItem[]

  createdAt           DateTime       @default(now()) @map("created_at")
  updatedAt           DateTime       @updatedAt @map("updated_at")
}

model OrderRefund {
  id                  String        @id @default(uuid()) @db.Uuid
  orderId             String        @map("order_id") @db.Uuid
  order               Order         @relation(fields: [orderId], references: [id])

  amount              Float
  status              RefundStatus  @default(PENDING)
  paymentVendor       String        @map("payment_vendor")

  reason              String?
  initiatedBy         String?       @map("initiated_by")

  createdAt           DateTime      @default(now()) @map("created_at")
  updatedAt           DateTime      @updatedAt @map("updated_at")

  @@index([orderId])
}

model EarningShare {
  id                  String              @id @default(uuid()) @db.Uuid
  orderId             String              @map("order_id") @db.Uuid
  merchantOrderId     String              @map("merchant_order_id") @db.Uuid

  shareMerchantId     String              @map("share_merchant_id") @db.Uuid
  recipientUserId     String              @map("recipient_user_id") @db.Uuid

  earningBase         Float              @map("earning_base")
  shareRate           Float              @map("share_rate")
  earningShared       Float              @map("earning_shared")

  settlementStatus    SettlementStatus    @default(PENDING) @map("settlement_status")
  totalPayoutBalance  Float              @map("total_payout_balance")

  createdAt           DateTime            @default(now()) @map("created_at")
  updatedAt           DateTime            @updatedAt @map("updated_at")

  @@index([orderId])
  @@index([recipientUserId])
}

model Payout {
  id                  String        @id @default(uuid()) @db.Uuid
  userId              String        @map("user_id") @db.Uuid
  amount              Float
  description         String?

  status              PayoutStatus  @default(IN_REVIEW)
  paymentMethod       Json?         @map("payment_method")

  totalPayoutBalance  Float         @map("total_payout_balance")

  createdAt           DateTime      @default(now()) @map("created_at")
  updatedAt           DateTime      @updatedAt @map("updated_at")

  @@index([userId])
}

enum OrderStatus {
  DRAFT
  IN_PROGRESS
  COMPLETED
  FAILED
}

enum PaymentStatus {
  PENDING
  PAID
  PARTIALLY_REFUNDED
  FULLY_REFUNDED
}

enum CommissionStatus {
  PENDING
  SETTLED
}

enum FulfillmentStatus {
  UNFULFILLED
  PARTIALLY_FULFILLED
  FULFILLED
}

enum OrderLineItemUnitStatus {
  IN_PROCESSING
  COMPLETED
  PARTIALLY_REDEEMED
  REFUNDED
  CANCELLED
}

enum SaleType {
  PRODUCT
  POST_COMMISSION
  POST_WHOLESALE
}

enum SettlementStatus {
  PENDING
  SETTLED
}

enum RefundStatus {
  PENDING
  SUCCESS
  FAILED
}

enum PayoutStatus {
  IN_REVIEW
  APPROVED
  PAID
  REJECTED
}

12. Order Tables - Complete Data Flow

This section explains when and how each Order-related table is populated throughout the order lifecycle.

12.1 Table Creation Timing

sequenceDiagram
    participant Checkout as Checkout Completed
    participant Order as Order Creation
    participant Fulfill as Fulfillment
    participant Refund as Refund
    participant Settle as Settlement (30d)
    participant Payout as Payout

    Note over Checkout: === PHASE 1: ORDER CREATION ===
    Checkout->>Order: 1. INSERT Order
    Checkout->>Order: 2. INSERT OrderLineItem[]
    Checkout->>Order: 3. INSERT OrderLineItemUnit[] (tickets only)
    Checkout->>Order: 4. INSERT MerchantOrder[] (per merchant)
    Checkout->>Order: 5. INSERT MerchantLineItem[] (per merchant order)
    Checkout->>Order: 6. INSERT PromoterOrder (if promoter)
    Checkout->>Order: 7. INSERT PromoterLineItem[] (if promoter order)

    Note over Fulfill: === PHASE 2: FULFILLMENT ===
    Fulfill->>Fulfill: 8. INSERT OrderFulfillment
    Fulfill->>Fulfill: 9. INSERT OrderFulfillmentLineItem[]
    Fulfill->>Fulfill: 10. UPDATE OrderLineItem.fulfillmentStatus
    Fulfill->>Fulfill: 11. UPDATE OrderLineItemUnit.status (tickets)

    Note over Refund: === PHASE 3: REFUND (if applicable) ===
    Refund->>Refund: 12. INSERT OrderRefund
    Refund->>Refund: 13. UPDATE Order.totalRefunded
    Refund->>Refund: 14. UPDATE OrderLineItem.lineItemRefunded
    Refund->>Refund: 15. UPDATE OrderLineItem.quantityRefunded
    Refund->>Refund: 16. UPDATE MerchantLineItem.commissionRefunded
    Refund->>Refund: 17. UPDATE PromoterLineItem.commissionRefunded
    Refund->>Refund: 18. UPDATE MerchantOrder.totalMerchantPayoutToPay
    Refund->>Refund: 19. UPDATE PromoterOrder.totalCommissionNet

    Note over Settle: === PHASE 4: SETTLEMENT (30 days later) ===
    Settle->>Settle: 20. UPDATE Order.commissionStatus = SETTLED
    Settle->>Settle: 21. INSERT EarningShare[] (revenue split)
    Settle->>Settle: 22. UPDATE MerchantOrder.totalMerchantPayoutToPay
    Settle->>Settle: 23. UPDATE PromoterOrder.totalPayoutToPay

    Note over Payout: === PHASE 5: PAYOUT ===
    Payout->>Payout: 24. INSERT PayoutTransaction
    Payout->>Payout: 25. UPDATE MerchantOrder.totalMerchantPayoutPaid
    Payout->>Payout: 26. UPDATE PromoterOrder.totalPayoutPaid
    Payout->>Payout: 27. UPDATE EarningShare.totalPayoutPaid

12.2 Table-by-Table Breakdown

12.3 Table Relationship Diagram

erDiagram
    Order ||--o{ OrderLineItem : contains
    Order ||--o{ MerchantOrder : splits
    Order ||--o| PromoterOrder : tracks
    Order ||--o{ OrderRefund : has

    OrderLineItem ||--o{ OrderLineItemUnit : units
    OrderLineItem ||--o| MerchantLineItem : mirrors
    OrderLineItem ||--o| PromoterLineItem : mirrors
    OrderLineItem ||--o{ OrderFulfillmentLineItem : fulfilled

    OrderLineItemUnit ||--o{ TicketRedemption : multi-day

    MerchantOrder ||--o{ MerchantLineItem : contains
    MerchantOrder ||--o| OrderFulfillment : tracked

    PromoterOrder ||--o{ PromoterLineItem : contains

    Order ||--o{ EarningShare : settles
    MerchantOrder ||--o{ EarningShare : settles

    User ||--o{ PayoutTransaction : requests

12.4 Key Data Flow Examples

Example 1: Simple Order (1 merchant, no promoter)

Order (id: O1)
├── OrderLineItem (id: LI1, merchantId: M1, quantity: 2)
│   ├── OrderLineItemUnit[] (id: U1, U2) - if tickets
│   └── MerchantLineItem (id: MLI1, merchantOrderId: MO1)
└── MerchantOrder (id: MO1, merchantId: M1)
    └── MerchantLineItem[] (id: MLI1)

Example 2: Multi-Merchant Order

Order (id: O1)
├── OrderLineItem (id: LI1, merchantId: M1) → MerchantOrder (MO1)
├── OrderLineItem (id: LI2, merchantId: M1) → MerchantOrder (MO1)
├── OrderLineItem (id: LI3, merchantId: M2) → MerchantOrder (MO2)
└── MerchantOrder[] (MO1, MO2)

Example 3: Promoter Order (with commission)

Order (id: O1, promoterId: P1)
├── OrderLineItem (id: LI1, promoterId: P1)
│   ├── MerchantLineItem (commissionGross: 10.00)
│   └── PromoterLineItem (commissionGross: 10.00, tier: 1)
├── MerchantOrder (id: MO1, merchantId: M1, promoterId: P1)
│   └── MerchantLineItem (commissionNet: 8.00 after deduction)
└── PromoterOrder (id: PO1, promoterId: P1)
    └── PromoterLineItem (commissionNet: 8.00)

Example 4: Multi-Level Commission (EarningShare)

Order (id: O1)
└── MerchantOrder (id: MO1)
    └── EarningShare[]:
        ├── { recipientUserId: P1, shareRate: 0.80, earningShared: 80.00 }
        ├── { recipientUserId: P2 (upline), shareRate: 0.15, earningShared: 15.00 }
        └── { recipientUserId: PLATFORM, shareRate: 0.05, earningShared: 5.00 }

12.5 Order vs MerchantOrder vs PromoterOrder - What & Why

These three tables serve different stakeholders with different calculations:

Order Table (Consumer Perspective)

Purpose: Track the consumer's order - what they paid, what they bought.

Key Focus: Consumer-facing totals

Who uses it: Consumer (view order), Admin (refund), Accounting (revenue)

// Example: Consumer buys 2 items for $100 total
Order: {
  totalToPay: 100.00,           // Consumer pays $100
  totalPaid: 100.00,            // Payment successful
  totalLineItemSubtotal: 90.00, // Items after $10 discount
  totalShippingSubtotal: 5.00,  // Shipping fee
  totalTax: 5.00,               // Tax
  totalLineItemDiscount: 10.00, // Coupon discount
  commissionStatus: "PENDING"   // Will settle in 30 days
}

MerchantOrder Table (Merchant Perspective)

Purpose: Per-merchant sub-order - tracks what merchant earns and what commission is deducted.

Key Focus: Merchant payout calculation

Who uses it: Merchant (view earnings), Finance (payout), Admin (settlement)

// Example: Merchant sells $90 worth of items, pays 20% commission
MerchantOrder: {
  merchantId: "M1",
  subtotal: 100.00,              // Items before discount
  total: 90.00,                  // After $10 consumer discount
  totalCommissionGross: 18.00,   // 20% of $90
  totalCommissionDeduction: 2.00, // Paid to downline
  totalCommissionNet: 16.00,     // Final commission to pay
  totalMerchantPayoutToPay: 74.00 // $90 - $16 commission
}

PromoterOrder Table (Promoter Perspective)

Purpose: Track promoter's commission across all merchants in one order.

Key Focus: Promoter commission tracking

Who uses it: Promoter (view earnings), Finance (payout), Admin (settlement)

// Example: Promoter sold items from 2 merchants
PromoterOrder: {
  promoterId: "P1",
  totalCommissionGross: 25.00,    // From Merchant1 ($18) + Merchant2 ($7)
  totalCommissionDeduction: 3.00,  // Paid to downline
  totalCommissionSubtotal: 22.00,
  transactionFees: 1.50,           // Payment processing
  totalCommissionNet: 20.50,       // Final earnings
  totalPayoutToPay: 20.50,
  postIds: ["post1", "post2"]      // Which posts generated sales
}

Comparison Summary

Data Flow Example

graph TD
    Checkout["Checkout: $100 total<br/>2 items from 2 merchants"]

    Checkout --> Order["Order<br/>totalToPay: $100<br/>commissionStatus: PENDING"]

    Checkout --> MO1["MerchantOrder M1<br/>subtotal: $60<br/>totalCommissionGross: $12<br/>totalMerchantPayoutToPay: $48"]
    Checkout --> MO2["MerchantOrder M2<br/>subtotal: $40<br/>totalCommissionGross: $8<br/>totalMerchantPayoutToPay: $32"]

    MO1 --> PO["PromoterOrder P1<br/>totalCommissionGross: $20<br/>totalPayoutToPay: $20"]
    MO2 --> PO

    Order -.->|30 days later| Settle["Settlement"]
    Settle --> Order2["Order.commissionStatus = SETTLED"]
    Settle --> ES["EarningShare[]"]

    ES --> Payout["Payout to P1: $20<br/>Payout to M1: $48<br/>Payout to M2: $32"]

Key Calculations

Merchant Payout Formula:

totalMerchantPayoutToPay =
  totalMerchantLineItemSubtotal     // Items sold
  + merchantShippingPayoutToPay     // Shipping reimbursement
  - totalCommissionNet              // Commission to promoter
  - merchantTaxDeduction            // Tax to collect
  - merchantPaymentFeeDeduction     // Payment processing fee

Promoter Payout Formula:

totalPayoutToPay =
  totalCommissionSubtotal           // Commission from sales
  - transactionOrderFee            // Platform fee
  - transactionItemFee             // Per-item fee
  - paymentFees                    // Payment processing

13. Multi-Day Pass Handling

For events with multi-day passes, each day is tracked separately:

// OrderLineItemUnit contains multiple TicketRedemption
interface TicketRedemption {
  id: string;
  orderLineItemUnitId: string;
  validDateTimeStart: DateTime;  // e.g., Nov 1 2024 20:00:00
  validDateTimeEnd: DateTime;    // e.g., Nov 2 2024 04:00:00
  status: 'IN_PROCESSING' | 'COMPLETED';
  redeemedAt: DateTime | null;
  redeemedBy: string | null;     // Admin/merchant who scanned
}

// Redemption flow
POST /order/ticket/status
{
  orderNumber: "ORD-123",
  code: "ABC-123",
  action: "redeem"
}
→ Finds OrderLineItemUnit by numberCode
→ Creates/updates TicketRedemption for current time slot
→ Updates OrderLineItemUnit.status to PARTIALLY_REDEEMED or COMPLETED

新增内容

Visual Diagram（可视化图）

Order (小乐的订单) - orderNumber: "ORD-2024-1207-001" │ ├── MerchantOrder (M1) - AppleStore, $50 │ └── MerchantOrder (M2) - SonyShop, $150 │ └── PromoterOrder (小红) - 佣金汇总 $40

Code Flow（代码流程）

展示了 CheckoutEntity.toCreateOrder() 中实际的分组逻辑：

1. 按 merchantId 分组 → 创建 2 个 MerchantOrder
2. 按 promoterId 分组 → 创建 1 个 PromoterOrder（因为只有小红一个推广者）

3. 创建 OrderLineItem → 连接所有记录

   完整的数据存储示例

4. Order: 1条（小乐的视角）

5. OrderLineItem: 2条（每个商品一条）
6. MerchantOrder: 2条（按店铺分组）
7. MerchantLineItem: 2条（与OrderLineItem对应）
8. PromoterOrder: 1条（按推广者合并）

9. PromoterLineItem: 2条（与OrderLineItem对应）

   关键点: 同一个 orderNumber (ORD-2024-1207-001) 贯穿所有表，方便查询关联，但每个表都有自己的 id（新UUID）。 已更新到上面。