Orders and delivery

From the cart to the delivered order (cahier §4.4, §5.3, §5.4, §6.7 — ORD, DLV). Prisma file: orders.prisma.

Rules the schema encodes:

• No automatic confirmation (ORD-14): every order — app, web, assisted, phone — reaches SUBMITTED (or PENDING when on hold) and waits for a back-office user with orders.validate. The queue of admin.orders.toConfirm is a partial index on orders.
• Snapshots, not references: an order line freezes SKU, names, brand, unit price of the reseller's customer type, discount and VAT; the order freezes the address, the customer type, the zone and the attributed delegate. Later catalog, price or assignment changes never alter a placed order.
• Money: integer centimes, HT unless the column name says Ttc; VAT per line, rounded half-up; totalTtc = subtotalHt − discountTotal + deliveryFee + vatTotal, enforced by a CHECK.
• History: every status change is an OrderStatusChange row, every controlled modification an OrderChange row; both feed the reseller's live updates (events.stream) and notifications (ORD-15).
• Returns are V1 (ORD-13): reception re-enters stock and issues a credit note automatically.

Transition rules, actors and error codes are on the lifecycles page; this page holds the columns they write.

CartCopy Anchor Link

CartCopy Anchor Link

The server-side cart shared by the reseller's devices (ORD-01): one persistent cart per reseller and owner user. Table carts · written by cart (cart.*).

• Relations: reseller → Reseller (resellerId, onDelete: Restrict) · user → User (userId, onDelete: Restrict).
• Referenced by: CartItem.cartId.
• Keys & indexes: @@unique([resellerId, userId]).
• Derived, not stored: totals, applied promotions and blocking issues returned by cart.get — recomputed on every read by the pricing and promotions engines; nothing priced is stored on the cart.
• Checkout (cart.checkout) creates the Order with snapshots of every line and deletes the cart items in the same transaction; the cart row stays and is reused.
• Assisted orders do not use carts: the delegate edits a DRAFT Order directly (delegate.assisted.*).

Example row:

{
  "id": "01a1a112-b440-7978-ad82-73497cd178f5",
  "resellerId": "01a19ba2-2fe0-7300-a148-73d1a4ef38c0",
  "userId": "01a1a077-1080-7aed-87e0-11ae957ae11e",
  "createdAt": "2026-11-03T11:02:00.000Z",
  "updatedAt": "2026-11-05T15:19:30.000Z"
}

CartItemCopy Anchor Link

One variant line of a cart. Table cart_items · written by cart.

• Relations: cart → Cart (cartId, onDelete: Cascade) · variant → ProductVariant (variantId, onDelete: Restrict).
• Keys & indexes: @@unique([cartId, variantId]).
• Transient: deleted on removal, cart clear and checkout. V2 packs add a nullable packId and make variantId nullable (expand migration).

Constraints and indexes Prisma cannot express (raw SQL in the migration):

ALTER TABLE cart_items ADD CONSTRAINT cart_items_quantity CHECK (quantity > 0);

Example row:

{
  "id": "01a1ac44-4e00-7264-ac40-c2e412a066ba",
  "cartId": "01a1a112-b440-7978-ad82-73497cd178f5",
  "variantId": "01a18298-9480-7f48-8522-52331c7620e9",
  "quantity": 20,
  "priceAtAddHt": 35000,
  "createdAt": "2026-11-05T15:12:00.000Z",
  "updatedAt": "2026-11-05T15:19:30.000Z"
}

OrdersCopy Anchor Link

OrderCopy Anchor Link

A reseller order from creation to a terminal state (ORD-03…ORD-11). All amounts are integer centimes of DZD; columns are HT unless their name says otherwise. Table orders · written by orders (lifecycle, controls, assisted flow); fulfillment (preparation → delivery transitions); billing (credit checks).

• Relations: reseller → Reseller (resellerId, onDelete: Restrict) · assistedConfirmedBy → User (assistedConfirmedById, onDelete: Restrict) · sourceDelegate → DelegateProfile (sourceDelegateId, onDelete: Restrict) · attributedDelegate → DelegateProfile (attributedDelegateId, onDelete: Restrict) · zone → Zone (zoneId, onDelete: Restrict) · wilaya → Wilaya (wilayaCode, onDelete: Restrict) · commune → Commune (communeId, onDelete: Restrict) · visit → Visit (visitId, onDelete: SetNull) · placedByUser → User (placedByUserId, onDelete: Restrict) · submittedBy → User (submittedById, onDelete: Restrict) · warehouse → Warehouse (warehouseId, onDelete: Restrict) · deliveryAddress → ResellerAddress (deliveryAddressId, onDelete: SetNull) · cancelledBy → User (cancelledById, onDelete: Restrict) · validatedBy → User (validatedById, onDelete: Restrict).
• Referenced by: OrderItem.orderId, OrderStatusChange.orderId, OrderChange.orderId, Delivery.orderId, ReturnRequest.orderId, Invoice.orderId, Payment.orderId, LoyaltyTransaction.orderId, Voucher.usedOrderId, Invitation.firstOrderId, Ticket.orderId, Ticket.replacementOrderId.
• Keys & indexes: number unique · @@index([resellerId, createdAt(sort: Desc)]) · @@index([status, submittedAt(sort: Desc)]) · @@index([channel, status]) · @@index([attributedDelegateId, submittedAt]) · @@index([sourceDelegateId, status]) · @@index([wilayaCode, submittedAt]) · @@index([zoneId, submittedAt]) · @@index([validatedAt]) · @@index([deliveredAt]).
• Enums: OrderChannel, OrderStatus, CustomerType, AssistedConfirmation, PaymentMethod.
• Lifecycle: 11 statuses of OrderStatus with the transition table and actors of the lifecycles page. Every channel lands in SUBMITTED (or PENDING on hold) and waits for an explicit back-office confirmation (ORD-14). Assisted flow: DRAFT → (delegate submits) PENDING + AWAITING_RESELLER → reseller/admin confirms → SUBMITTED → back-office confirmation → VALIDATED; rejection or expiry → CANCELLED with the system cancel codes. Every change reaches the reseller live (events.stream) and as a notification (ORD-15).
• History: Every status change → OrderStatusChange; every controlled modification of lines, address or payment method → OrderChange (ORD-06). Stock effects → StockMovement with refType = ORDER.
• Derived, not stored: invoice/delivery-note links and allowed actions returned by orders.get — from Invoice, Delivery and the transition table; days in status — from the milestone timestamps; credit exposure — billing query (see CreditAccount).
• Milestone timestamps are written by the transition that enters the status; when a status is entered twice (e.g. PENDING) the column holds the last entry and OrderStatusChange keeps the full sequence.
• Exception to no hard delete: a DRAFT assisted order discarded by its delegate (delegate.assisted.discard) is deleted with its items; the AuditLog DELETE entry remains. A draft has no number, so no gap appears.
• After-sales replacement (resolution REPLACEMENT, SUP-04): admin.tickets.resolve creates an order with channel BACK_OFFICE whose lines carry discountHt = lineSubtotalHt (total 0) and the ticket number in note; the ticket points to it (Ticket.replacementOrderId). It is confirmed and shipped like any order; billing issues no invoice for a zero total (assumption).

Constraints and indexes Prisma cannot express (raw SQL in the migration):

ALTER TABLE orders ADD CONSTRAINT orders_totals CHECK (
  total_ttc = subtotal_ht - discount_total + delivery_fee + vat_total
  AND subtotal_ht >= 0 AND discount_total BETWEEN 0 AND subtotal_ht AND delivery_fee >= 0 AND vat_total >= 0);
ALTER TABLE orders ADD CONSTRAINT orders_assisted_flag CHECK (is_assisted = (channel = 'DELEGATE_ASSISTED'));
ALTER TABLE orders ADD CONSTRAINT orders_submitted_complete CHECK (
  status = 'DRAFT' OR (number IS NOT NULL AND customer_type IS NOT NULL
    AND address_snapshot IS NOT NULL AND payment_method IS NOT NULL));
ALTER TABLE orders ADD CONSTRAINT orders_override_reason CHECK (
  NOT (stock_override OR credit_override) OR override_reason IS NOT NULL);
-- one live first order per reseller (a cancelled/refused first order frees the flag)
CREATE UNIQUE INDEX orders_one_first_order ON orders (reseller_id)
  WHERE is_first_order AND status NOT IN ('REFUSED', 'CANCELLED');
-- back-office confirmation queue, oldest first (admin.orders.toConfirm, BO-41)
CREATE INDEX orders_to_confirm ON orders (submitted_at) WHERE status IN ('SUBMITTED', 'PENDING');
-- expiry job for assisted confirmations
CREATE INDEX orders_assisted_awaiting ON orders (assisted_expires_at)
  WHERE assisted_confirmation = 'AWAITING_RESELLER';
CREATE INDEX orders_number_trgm ON orders USING gin (number gin_trgm_ops);

Example row:

{
  "id": "01a1ac4b-a100-74df-8237-614783cfeacd",
  "number": "CMD-2026-000158",
  "resellerId": "01a19ba2-2fe0-7300-a148-73d1a4ef38c0",
  "channel": "RESELLER_APP",
  "status": "DELIVERED",
  "statusChangedAt": "2026-11-08T11:45:00.000Z",
  "customerType": "DEMI_GROS",
  "isAssisted": false,
  "assistedConfirmation": null,
  "assistedExpiresAt": null,
  "assistedConfirmedAt": null,
  "assistedConfirmedById": null,
  "sourceDelegateId": null,
  "attributedDelegateId": "01a1a024-7d34-7424-adb6-099ecaff5095",
  "zoneId": "01a17280-b500-7f65-a38a-52982a7faa5e",
  "wilayaCode": "16",
  "communeId": "01a15db0-5680-7730-9115-ea5cf224e9ae",
  "visitId": null,
  "isFirstOrder": true,
  "placedByUserId": "01a1a077-1080-7aed-87e0-11ae957ae11e",
  "submittedById": "01a1a077-1080-7aed-87e0-11ae957ae11e",
  "warehouseId": "01a15db0-5680-7dc8-bb36-70c311a4c34e",
  "deliveryAddressId": "01a1a07e-6380-7674-b5bd-e46066ccc2c4",
  "addressSnapshot": {
    "recipientName": "Amine Benali",
    "phone": "+213555123456",
    "addressLine": "12 rue Didouche Mourad",
    "landmark": "En face de la poste centrale",
    "communeId": "01a15db0-5680-7730-9115-ea5cf224e9ae",
    "communeName": "Alger Centre",
    "wilayaCode": "16",
    "wilayaName": "Alger",
    "lat": 36.773105,
    "lng": 3.058821
  },
  "paymentMethod": "CREDIT",
  "note": "Livrer avant 12 h svp",
  "currency": "DZD",
  "subtotalHt": 700000,
  "discountTotal": 70000,
  "deliveryFee": 0,
  "vatTotal": 119700,
  "totalTtc": 749700,
  "appliedPromotions": [
    {
      "promotionId": "01a19656-4a80-7734-8064-1c5352836363",
      "code": "NOV-CABLES-10",
      "name": "-10 % sur les câbles",
      "type": "PERCENT_OFF",
      "value": 1000,
      "discountHt": 70000
    }
  ],
  "controlResults": {
    "checkedAt": "2026-11-05T15:20:01.000Z",
    "stock": {
      "ok": true
    },
    "prices": {
      "ok": true
    },
    "credit": {
      "ok": true,
      "limit": 2000000,
      "exposureBefore": 0,
      "available": 1250300
    },
    "address": {
      "ok": true
    }
  },
  "pendingReasons": [],
  "stockOverride": false,
  "creditOverride": false,
  "overrideReason": null,
  "refusalReasonCode": null,
  "refusalComment": null,
  "cancelReasonCode": null,
  "cancelComment": null,
  "cancelledById": null,
  "validatedById": "01a1a00a-4535-7ff7-af24-a0a3bb095b70",
  "submittedAt": "2026-11-05T15:20:00.000Z",
  "pendingAt": null,
  "validatedAt": "2026-11-05T16:05:00.000Z",
  "refusedAt": null,
  "preparingAt": "2026-11-06T09:00:00.000Z",
  "readyAt": "2026-11-06T11:30:00.000Z",
  "shippedAt": "2026-11-06T14:00:00.000Z",
  "deliveredAt": "2026-11-08T11:45:00.000Z",
  "cancelledAt": null,
  "returnedAt": null,
  "version": 6,
  "createdAt": "2026-11-05T15:20:00.000Z",
  "updatedAt": "2026-11-08T11:45:00.000Z"
}

OrderItemCopy Anchor Link

One line of an order, with a snapshot of the product and of the price computation. Table order_items · written by orders (written at checkout/draft edit; frozen from VALIDATED except controlled modifications).

• Relations: order → Order (orderId, onDelete: Cascade) · variant → ProductVariant (variantId, onDelete: Restrict) · product → Product (productId, onDelete: Restrict) · imageFile → File (imageFileId, onDelete: SetNull).
• Referenced by: ReturnItem.orderItemId, InvoiceLine.orderItemId, TicketItem.orderItemId.
• Keys & indexes: @@index([orderId]) · @@index([variantId]) · @@index([productId]).
• V2 packs add a nullable packId (lines of a pack share it).

Constraints and indexes Prisma cannot express (raw SQL in the migration):

ALTER TABLE order_items ADD CONSTRAINT order_items_math CHECK (
  quantity > 0
  AND line_subtotal_ht = unit_price_ht * quantity
  AND discount_ht BETWEEN 0 AND line_subtotal_ht
  AND line_total_ht = line_subtotal_ht - discount_ht
  AND line_total_ttc = line_total_ht + vat_amount);

Example row:

{
  "id": "01a1ac4b-a100-7f7e-bf8e-c3ea35df01d8",
  "orderId": "01a1ac4b-a100-74df-8237-614783cfeacd",
  "variantId": "01a18298-9480-7f48-8522-52331c7620e9",
  "productId": "01a18298-9480-70cc-80a7-8ddb761bc4d8",
  "sku": "HOCO-X88-C-BK-1M",
  "productName": "Câble X88 USB-A vers USB-C 3 A",
  "variantName": "Noir · 1 m",
  "brandName": "HOCO",
  "productReference": "X88",
  "imageFileId": "01a19b67-9409-7eb8-9344-665d55c91f89",
  "quantity": 20,
  "unitPriceHt": 35000,
  "discountHt": 70000,
  "promotionIds": [
    "01a19656-4a80-7734-8064-1c5352836363"
  ],
  "vatRate": 1900,
  "lineSubtotalHt": 700000,
  "lineTotalHt": 630000,
  "vatAmount": 119700,
  "lineTotalTtc": 749700,
  "unitPurchasePriceHt": 21000,
  "sortOrder": 0,
  "createdAt": "2026-11-05T15:20:00.000Z",
  "updatedAt": "2026-11-05T15:20:00.000Z"
}

OrderStatusChangeCopy Anchor Link

Append-only history of order statuses — dated, attributed and notified (cahier §5.4, ORD-06). Table order_status_changes · written by orders and fulfillment (only through the state machine).

• Relations: order → Order (orderId, onDelete: Cascade) · actorUser → User (actorUserId, onDelete: Restrict).
• Keys & indexes: @@index([orderId, createdAt]) · @@index([toStatus, createdAt]).
• Enums: OrderStatus, ClientApp.
• The timeline of orders.get (RA-22) is this table; each row emits order.status_changed through the outbox, delivered to the reseller live on events.stream and as push + in-app notification, and to the back-office on /v1/admin/events (ORD-15, SYN-07).

Example row:

{
  "id": "01a1ac74-d3e0-7a49-ba3b-0b24e401dcdf",
  "orderId": "01a1ac4b-a100-74df-8237-614783cfeacd",
  "fromStatus": "SUBMITTED",
  "toStatus": "VALIDATED",
  "actorUserId": "01a1a00a-4535-7ff7-af24-a0a3bb095b70",
  "actorApp": "DASHBOARD",
  "reasonCode": null,
  "comment": null,
  "trigger": null,
  "metadata": null,
  "occurredAt": "2026-11-05T16:05:00.000Z",
  "createdAt": "2026-11-05T16:05:00.000Z"
}

OrderChangeCopy Anchor Link

Append-only record of a controlled modification of an order, with the reason and before/after values (cahier §4.4 toute modification doit être historisée). Table order_changes · written by orders (admin.orders.modify, address/payment edits before validation).

• Relations: order → Order (orderId, onDelete: Cascade) · actorUser → User (actorUserId, onDelete: Restrict).
• Keys & indexes: @@index([orderId, createdAt]).

Example row:

{
  "id": "01a1ac6e-6b40-78fa-9337-c499ce9e64d7",
  "orderId": "01a1ab7b-0ce2-7d93-a4c3-1d4f23bf08ff",
  "actorUserId": "01a1a00a-4535-7ff7-af24-a0a3bb095b70",
  "changeType": "LINES",
  "reason": "Rupture partielle : quantité ramenée de 30 à 20",
  "changedFields": [
    "items[HOCO-X88-C-BK-1M].quantity"
  ],
  "before": {
    "items": [
      {
        "sku": "HOCO-X88-C-BK-1M",
        "quantity": 30,
        "lineTotalTtc": 1124550
      }
    ]
  },
  "after": {
    "items": [
      {
        "sku": "HOCO-X88-C-BK-1M",
        "quantity": 20,
        "lineTotalTtc": 749700
      }
    ]
  },
  "totalTtcBefore": 1124550,
  "totalTtcAfter": 749700,
  "notifiedAt": "2026-11-05T15:58:03.000Z",
  "createdAt": "2026-11-05T15:58:00.000Z"
}

DeliveryCopy Anchor Link

CarrierCopy Anchor Link

A delivery company or HOCO's own fleet entry (DLV-04). Table carriers · written by fulfillment (admin.carriers.crud, BO-43).

• Relations: none outgoing.
• Referenced by: Delivery.carrierId.
• Keys & indexes: code unique.

Example row:

{
  "id": "01a17ccd-6d00-7409-b28a-f3327843bccd",
  "code": "RAPIDE-EXP",
  "name": "Rapide Express",
  "phone": "+213770112233",
  "email": null,
  "trackingUrlTemplate": "https://suivi.rapide-express.example/{trackingNumber}",
  "isApiEnabled": false,
  "apiProvider": null,
  "coverageWilayaCodes": [],
  "isActive": true,
  "archivedAt": null,
  "notes": null,
  "createdAt": "2026-10-27T10:00:00.000Z",
  "updatedAt": "2026-10-27T10:00:00.000Z"
}

DeliveryCopy Anchor Link

The fulfilment record of an order from VALIDATED on: preparation, shipping and proof of delivery (DLV-01…03). One per order in V1 (no split shipments). Table deliveries · written by fulfillment (admin.deliveries.*, BO-42).

• Relations: order → Order (orderId, onDelete: Restrict) · carrier → Carrier (carrierId, onDelete: Restrict) · preparedBy → User (preparedById, onDelete: Restrict) · proofFile → File (proofFileId, onDelete: Restrict) · deliveryNoteFile → File (deliveryNoteFileId, onDelete: Restrict).
• Keys & indexes: orderId unique · number unique · @@index([status, updatedAt]) · @@index([carrierId, status]).
• Enums: DeliveryStatus, DeliveryMode.
• Lifecycle: TO_PREPARE → PREPARING → READY → SHIPPED → DELIVERED, SHIPPED → FAILED → SHIPPED (new attempt) or RETURNED. Each delivery transition drives the matching Order transition in the same transaction.
• Derived, not stored: cash to collect for CASH_ON_DELIVERY — the invoice amount due; picking list — order lines grouped by warehouse location at read time.

Constraints and indexes Prisma cannot express (raw SQL in the migration):

ALTER TABLE deliveries ADD CONSTRAINT deliveries_carrier_required CHECK (mode IS DISTINCT FROM 'CARRIER' OR carrier_id IS NOT NULL);
ALTER TABLE deliveries ADD CONSTRAINT deliveries_proof CHECK (status <> 'DELIVERED' OR recipient_name IS NOT NULL OR proof_file_id IS NOT NULL);

Example row:

{
  "id": "01a1ac74-d3e0-727e-8478-575a49c86e21",
  "orderId": "01a1ac4b-a100-74df-8237-614783cfeacd",
  "number": "BL-2026-000123",
  "status": "DELIVERED",
  "mode": "CARRIER",
  "carrierId": "01a17ccd-6d00-7409-b28a-f3327843bccd",
  "trackingNumber": "RX-7781-2390",
  "driverName": null,
  "driverPhone": null,
  "packageCount": 1,
  "scheduledDate": "2026-11-08",
  "preparedById": "01a1a03d-f3cf-7e2d-99bf-448a84afa69b",
  "preparingAt": "2026-11-06T09:00:00.000Z",
  "readyAt": "2026-11-06T11:30:00.000Z",
  "shippedAt": "2026-11-06T14:00:00.000Z",
  "deliveredAt": "2026-11-08T11:45:00.000Z",
  "attemptCount": 1,
  "failedAt": null,
  "failureReason": null,
  "nextAttemptOn": null,
  "cashCollectedAmount": null,
  "cancelledAt": null,
  "returnedAt": null,
  "recipientName": "Amine Benali",
  "proofFileId": "01a19b0e-463c-7aa1-acf3-a07740735863",
  "proofNote": null,
  "deliveryNoteFileId": "01a19aaa-d459-7736-a2e3-c84e4ad52a35",
  "createdAt": "2026-11-05T16:05:00.000Z",
  "updatedAt": "2026-11-08T11:45:00.000Z"
}

Returns (V1)Copy Anchor Link

Returns are V1 since the 17 Oct decisions (ORD-13): the reseller requests, the back-office approves, reception re-enters stock automatically and issues a credit note. They live on this page because they are order-scoped.

ReturnRequestCopy Anchor Link

A return of delivered items (ORD-13, V1 since the 17 Oct decisions), requested by the reseller or created by an after-sales resolution RETURN. Placed on this page because it is order-scoped. Table return_requests · written by orders (returns.create, admin.returns.decide); inventory posts the stock on reception (admin.returns.receive); billing issues the credit note.

• Relations: order → Order (orderId, onDelete: Restrict) · reseller → Reseller (resellerId, onDelete: Restrict) · requestedBy → User (requestedById, onDelete: Restrict) · decidedBy → User (decidedById, onDelete: Restrict) · receivedBy → User (receivedById, onDelete: Restrict).
• Referenced by: ReturnItem.returnRequestId, CreditNote.returnRequestId, Ticket.returnRequestId.
• Keys & indexes: number unique · @@index([resellerId, createdAt(sort: Desc)]) · @@index([status, createdAt]) · @@index([orderId]).
• Enums: ReturnStatus.
• Lifecycle: REQUESTED → APPROVED → IN_TRANSIT → RECEIVED → REFUNDED; APPROVED → RECEIVED when the goods come back without transport (delegate, counter); REQUESTED/APPROVED → REJECTED with a reason. Reception (admin.returns.receive) runs in one transaction: per item RETURN StockMovement of restockQty (automatic, STK-03), CreditNote for the accepted quantities (FIN-08), status RECEIVED then REFUNDED; nothing accepted → REJECTED.
• History: Status changes → AuditLog STATUS_CHANGE (reseller timeline via returns.get); outbox return.status_changed → events.stream + notification.
• The Order moves to RETURNED only when every delivered quantity has been returned and received; a partial return leaves it DELIVERED.
• Link to after-sales: the ticket holds returnRequestId (single source); the credit note holds returnRequestId.

Example row:

{
  "id": "01a1c4bd-4220-7f7e-bf49-85cc6160afd3",
  "number": "RET-2026-000012",
  "orderId": "01a1ac4b-a100-74df-8237-614783cfeacd",
  "resellerId": "01a19ba2-2fe0-7300-a148-73d1a4ef38c0",
  "status": "REFUNDED",
  "statusChangedAt": "2026-11-13T10:20:00.000Z",
  "reason": "SAV TKT-2026-000045 — 2 câbles défectueux",
  "photoFileIds": [],
  "requestedById": "01a1a00a-4535-7ff7-af24-a0a3bb095b70",
  "decidedById": "01a1a00a-4535-7ff7-af24-a0a3bb095b70",
  "approvedAt": "2026-11-10T14:00:00.000Z",
  "rejectedAt": null,
  "rejectionReason": null,
  "inTransitAt": "2026-11-11T09:00:00.000Z",
  "returnTrackingNumber": null,
  "receivedById": "01a19f89-c802-701b-83ec-271e6091a1ed",
  "receivedAt": "2026-11-13T10:20:00.000Z",
  "refundedAt": "2026-11-13T10:20:00.000Z",
  "refundTotalTtc": 74970,
  "note": null,
  "createdAt": "2026-11-10T09:15:00.000Z",
  "updatedAt": "2026-11-13T10:20:00.000Z"
}

ReturnItemCopy Anchor Link

Requested, received and accepted quantities of one order line in a return. Table return_items · written by orders (request, decision); inventory (reception quantities).

• Relations: returnRequest → ReturnRequest (returnRequestId, onDelete: Cascade) · orderItem → OrderItem (orderItemId, onDelete: Restrict) · variant → ProductVariant (variantId, onDelete: Restrict).
• Keys & indexes: @@index([returnRequestId]) · @@index([orderItemId]).

Constraints and indexes Prisma cannot express (raw SQL in the migration):

ALTER TABLE return_items ADD CONSTRAINT return_items_quantities CHECK (
  quantity > 0
  AND (received_qty IS NULL OR received_qty BETWEEN 0 AND quantity)
  AND (accepted_qty IS NULL OR accepted_qty BETWEEN 0 AND coalesce(received_qty, 0))
  AND (restock_qty IS NULL OR restock_qty BETWEEN 0 AND coalesce(accepted_qty, 0)));

Example row:

{
  "id": "01a1c4bd-4220-7a91-a932-e97d3771ca64",
  "returnRequestId": "01a1c4bd-4220-7f7e-bf49-85cc6160afd3",
  "orderItemId": "01a1ac4b-a100-7f7e-bf8e-c3ea35df01d8",
  "variantId": "01a18298-9480-7f48-8522-52331c7620e9",
  "quantity": 2,
  "reason": "Ne charge pas",
  "receivedQty": 2,
  "acceptedQty": 2,
  "restockQty": 0,
  "condition": "DEFECTIVE",
  "refundAmountTtc": 74970,
  "createdAt": "2026-11-10T09:15:00.000Z",
  "updatedAt": "2026-11-13T10:20:00.000Z"
}