Support and notifications

Support tickets and after-sales claims (cahier §2.1, §14.1 — SUP), the regional WhatsApp support of the reseller apps, back-office announcements and the notification system (cahier §8 — NOT). Prisma file: support.prisma.

After-sales in V1 (SUP-04): a reseller opens a ticket of type AFTER_SALES on one of their orders, lists the affected lines and quantities (TicketItem) with the issue (AfterSalesIssue) and photos. The back-office resolves it with one AfterSalesResolution, and the ticket points to what the resolution created:

Regional WhatsApp (SUP-05, ADM-08): SupportRegion groups wilayas and holds the WhatsApp number and hours; the button opens the region of the signed-in reseller's wilaya, else of the device position (nearest commune centroid), else of a picked wilaya, else the default region.

Notifications: one Notification per user and event (the in-app inbox), one NotificationDelivery per channel attempt (push, SMS, e-mail, WhatsApp); texts come from NotificationTemplate and routing from NotificationRule (the notifications matrix).

Tickets and after-salesCopy Anchor Link

TicketCopy Anchor Link

A reseller support request (SUP-01/02) — including after-sales claims (type = AFTER_SALES, SUP-04) on items of an order, handled from diagnosis to resolution. Table tickets · written by support (tickets.*, admin.tickets.*; admin.tickets.resolve for after-sales).

• Relations: reseller → Reseller (resellerId, onDelete: Restrict) · openedBy → User (openedById, onDelete: Restrict) · order → Order (orderId, onDelete: Restrict) · assignedTo → User (assignedToId, onDelete: Restrict) · resolvedBy → User (resolvedById, onDelete: Restrict) · replacementOrder → Order (replacementOrderId, onDelete: Restrict) · returnRequest → ReturnRequest (returnRequestId, onDelete: Restrict) · creditNote → CreditNote (creditNoteId, onDelete: Restrict).
• Referenced by: TicketItem.ticketId, TicketMessage.ticketId.
• Keys & indexes: number unique · replacementOrderId unique · returnRequestId unique · creditNoteId unique · @@index([status, priority, lastMessageAt]) · @@index([resellerId, createdAt(sort: Desc)]) · @@index([assignedToId, status]) · @@index([type, status]) · @@index([orderId]).
• Enums: TicketType, TicketStatus, TicketPriority (proposed), AfterSalesResolution.
• Lifecycle: TicketStatus per the lifecycles page; after-sales adds IN_PROGRESS → RESOLVED with a resolution (REPLACEMENT, RETURN, CREDIT_NOTE, REPAIR) or IN_PROGRESS → REJECTED with a reason. The resolution, its linked record and the status change are written in one transaction.
• History: Status, assignment, priority and resolution changes → AuditLog; every message → TicketMessage; the reseller timeline gets an Interaction CLAIM.
• Derived, not stored: SLA timers (time to first response, time to resolution) — from createdAt, firstResponseAt, resolvedAt.

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

ALTER TABLE tickets ADD CONSTRAINT tickets_after_sales_order CHECK (type <> 'AFTER_SALES' OR order_id IS NOT NULL);
ALTER TABLE tickets ADD CONSTRAINT tickets_resolution CHECK (
  (resolution IS NULL OR type = 'AFTER_SALES')
  AND (resolution IS DISTINCT FROM 'REPLACEMENT' OR replacement_order_id IS NOT NULL)
  AND (resolution IS DISTINCT FROM 'RETURN' OR return_request_id IS NOT NULL)
  AND (resolution IS DISTINCT FROM 'CREDIT_NOTE' OR credit_note_id IS NOT NULL)
  AND (resolution IS DISTINCT FROM 'REJECTED' OR (rejection_reason IS NOT NULL AND status IN ('REJECTED', 'CLOSED'))));
ALTER TABLE tickets ADD CONSTRAINT tickets_rating CHECK (rating IS NULL OR rating BETWEEN 1 AND 5);

Example row:

{
  "id": "01a1bfc0-1900-71ab-83c8-a3cc1a5d0ccd",
  "number": "TKT-2026-000045",
  "resellerId": "01a19ba2-2fe0-7300-a148-73d1a4ef38c0",
  "openedById": "01a1a077-1080-7aed-87e0-11ae957ae11e",
  "type": "AFTER_SALES",
  "subject": "Câbles X88 qui ne chargent pas",
  "status": "RESOLVED",
  "statusChangedAt": "2026-11-10T14:00:00.000Z",
  "priority": "HIGH",
  "orderId": "01a1ac4b-a100-74df-8237-614783cfeacd",
  "assignedToId": "01a1a00a-4535-7ff7-af24-a0a3bb095b70",
  "lastMessageAt": "2026-11-10T14:00:00.000Z",
  "firstResponseAt": "2026-11-09T11:20:00.000Z",
  "resolution": "RETURN",
  "resolvedAt": "2026-11-10T14:00:00.000Z",
  "resolvedById": "01a1a00a-4535-7ff7-af24-a0a3bb095b70",
  "replacementOrderId": null,
  "returnRequestId": "01a1c4bd-4220-7f7e-bf49-85cc6160afd3",
  "creditNoteId": null,
  "rejectionReason": null,
  "closedAt": null,
  "rating": null,
  "ratingComment": null,
  "createdAt": "2026-11-09T10:00:00.000Z",
  "updatedAt": "2026-11-10T14:00:00.000Z"
}

TicketItemCopy Anchor Link

An order line claimed in an after-sales ticket, with the quantity, the issue and photos (SUP-04). Table ticket_items · written by support (tickets.create for AFTER_SALES).

• Relations: ticket → Ticket (ticketId, onDelete: Cascade) · orderItem → OrderItem (orderItemId, onDelete: Restrict).
• Keys & indexes: @@unique([ticketId, orderItemId]).
• Enums: AfterSalesIssue.
• A RETURN resolution copies the claimed lines and quantities into ReturnItem rows; REPLACEMENT copies them into the free order's lines.

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

ALTER TABLE ticket_items ADD CONSTRAINT ticket_items_quantity CHECK (quantity > 0);

Example row:

{
  "id": "01a1bfc0-1900-72fe-ba51-98ed73082185",
  "ticketId": "01a1bfc0-1900-71ab-83c8-a3cc1a5d0ccd",
  "orderItemId": "01a1ac4b-a100-7f7e-bf8e-c3ea35df01d8",
  "quantity": 2,
  "issue": "DEFECTIVE",
  "description": "Le téléphone ne charge pas avec ces deux câbles.",
  "photoFileIds": [
    "01a19b9e-fb9e-7637-b9d4-0ea1412c6022"
  ],
  "createdAt": "2026-11-09T10:00:00.000Z",
  "updatedAt": "2026-11-09T10:00:00.000Z"
}

TicketMessageCopy Anchor Link

A message of a ticket conversation, public or internal (SUP-02). Table ticket_messages · written by support.

• Relations: ticket → Ticket (ticketId, onDelete: Cascade) · author → User (authorId, onDelete: Restrict).
• Keys & indexes: @@index([ticketId, createdAt]).
• Immutable (no edits, no updatedAt). A reseller reply emits ticket.replied on the staff side; a staff public reply reaches the reseller on events.stream and by push.

Example row:

{
  "id": "01a1c009-5700-7d7b-b8ee-08fc5aeda2fb",
  "ticketId": "01a1bfc0-1900-71ab-83c8-a3cc1a5d0ccd",
  "authorId": "01a1a00a-4535-7ff7-af24-a0a3bb095b70",
  "body": "Bonjour, pouvez-vous nous renvoyer les deux câbles ? Nous vous émettrons un avoir.",
  "isInternal": false,
  "isQuestion": true,
  "attachmentFileIds": [],
  "createdAt": "2026-11-09T11:20:00.000Z"
}

Support regionsCopy Anchor Link

SupportRegionCopy Anchor Link

A support region grouping wilayas, with the WhatsApp number and hours behind the fixed WhatsApp button of both reseller apps (SUP-05, ADM-08). Table support_regions · written by support (admin.supportRegions.crud, BO-70; read by support.whatsapp).

• Relations: none outgoing.
• Referenced by: Reseller.supportRegionId, SupportRegionWilaya.supportRegionId.
• Keys & indexes: code unique.
• Region resolution (support.whatsapp, SUP-05): signed in → Reseller.supportRegionId (from the account's wilaya); otherwise the request's lat/lng → nearest Commune centroid → its wilaya → SupportRegionWilaya; otherwise an explicit wilayaCode; otherwise the default region. The response carries the number, the wa.me link and the hours.

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

CREATE UNIQUE INDEX support_regions_one_default ON support_regions (is_default) WHERE is_default AND archived_at IS NULL;

Example row:

{
  "id": "01a17c96-7e80-7bd8-b8aa-2b5b2d991956",
  "code": "CENTRE",
  "name": "Centre (Alger, Blida, Boumerdès, Tipaza)",
  "whatsappNumber": "+213770123456",
  "phone": null,
  "hours": "Dim–Jeu 08:30–17:00 · Sam 09:00–13:00",
  "hoursAr": null,
  "isDefault": true,
  "isActive": true,
  "sortOrder": 1,
  "archivedAt": null,
  "createdAt": "2026-10-27T09:00:00.000Z",
  "updatedAt": "2026-10-27T09:00:00.000Z"
}

SupportRegionWilayaCopy Anchor Link

Assigns a wilaya to a support region. Table support_region_wilayas · written by support.

• Relations: supportRegion → SupportRegion (supportRegionId, onDelete: Cascade) · wilaya → Wilaya (wilayaCode, onDelete: Restrict).
• Keys & indexes: @@id([supportRegionId, wilayaCode]) · wilayaCode unique.
• Link row: editing a region's wilayas replaces its rows and re-derives Reseller.supportRegionId for the resellers of the moved wilayas in the same transaction (AuditLog keeps the diff). Seeded so that all 69 wilayas are covered.

Example row:

{
  "supportRegionId": "01a17c96-7e80-7bd8-b8aa-2b5b2d991956",
  "wilayaCode": "16",
  "createdAt": "2026-10-27T09:00:00.000Z"
}

AnnouncementsCopy Anchor Link

AnnouncementCopy Anchor Link

A message from the back-office to delegates and/or resellers by target (Messages, NOT-07). Table announcements · written by support (admin.announcements.*, BO-57); notifications fans it out.

• Relations: imageFile → File (imageFileId, onDelete: SetNull) · createdBy → User (createdById, onDelete: Restrict) · product, promotion, category, brand, file (all onDelete: SetNull).
• Referenced by: AnnouncementRead.announcementId.
• Keys & indexes: @@index([publishedAt]) · @@index([scheduledAt]).
• Enums: UserType, CustomerType, AnnouncementKind, NotificationGroup (smart notifications).
• Derived, not stored: status — draft (no publishedAt, no scheduledAt), scheduled, published, expired (expiresAt passed), archived: from the timestamps, no enum.

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

ALTER TABLE announcements ADD CONSTRAINT announcements_audience CHECK (cardinality(target_user_types) >= 1);

Example row:

{
  "id": "01a19b45-b800-79d2-9a2f-13ba8dab597d",
  "title": "Nouvelle gamme ACEFAST disponible",
  "body": "Les chargeurs ACEFAST GaN 65 W arrivent cette semaine. Présentez-les en priorité.",
  "imageFileId": null,
  "targetUserTypes": [
    "DELEGATE"
  ],
  "targetZoneIds": [],
  "targetWilayaCodes": [],
  "targetSegmentIds": [],
  "sendPush": true,
  "scheduledAt": null,
  "publishedAt": "2026-11-02T08:00:00.000Z",
  "expiresAt": "2026-11-30T00:00:00.000Z",
  "archivedAt": null,
  "createdById": "01a1a00a-4535-7ff7-af24-a0a3bb095b70",
  "createdAt": "2026-11-02T08:00:00.000Z",
  "updatedAt": "2026-11-02T08:00:00.000Z"
}

AnnouncementReadCopy Anchor Link

A user's acknowledgement of an announcement. Table announcement_reads · written by notifications (announcements.markRead).

• Relations: announcement → Announcement (announcementId, onDelete: Cascade) · user → User (userId, onDelete: Cascade).
• Keys & indexes: @@id([announcementId, userId]) · @@index([userId]).

Example row:

{
  "announcementId": "01a19b45-b800-79d2-9a2f-13ba8dab597d",
  "userId": "01a1a024-7d34-7424-adb6-099ecaff5095",
  "readAt": "2026-11-02T09:12:00.000Z"
}

NotificationsCopy Anchor Link

NotificationCopy Anchor Link

An entry of a user's in-app inbox (NOT-01); its push/SMS/e-mail copies are NotificationDelivery rows. Table notifications · written by notifications (from outbox events; notifications.markRead*).

• Relations: user → User (userId, onDelete: Cascade).
• Referenced by: PaymentReminder.notificationId, NotificationDelivery.notificationId.
• Keys & indexes: @@index([userId, createdAt(sort: Desc)]) · @@index([userId, group, createdAt(sort: Desc)]) (inbox tabs) · @@index([expiresAt]).
• Enums: NotificationCategory.
• Creation emits notification.created on events.stream so an open reseller app or page refreshes its badge live.

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

-- unread badge (notifications.unreadCount)
CREATE INDEX notifications_unread ON notifications (user_id) WHERE read_at IS NULL;

Example row:

{
  "id": "01a1ac74-d7c8-7c97-a660-3603cea892ac",
  "userId": "01a1a077-1080-7aed-87e0-11ae957ae11e",
  "category": "TRANSACTIONAL",
  "eventKey": "order.status_changed",
  "title": "Commande validée",
  "body": "Votre commande CMD-2026-000158 a été validée.",
  "data": {
    "route": "orders/detail",
    "params": {
      "orderId": "01a1ac4b-a100-74df-8237-614783cfeacd"
    },
    "orderNumber": "CMD-2026-000158",
    "status": "VALIDATED"
  },
  "readAt": "2026-11-05T16:30:00.000Z",
  "expiresAt": "2027-11-05T16:05:01.000Z",
  "createdAt": "2026-11-05T16:05:01.000Z",
  "updatedAt": "2026-11-05T16:30:00.000Z"
}

NotificationDeliveryCopy Anchor Link

One attempt to deliver a notification on one channel — the delivery log of admin.notifications.log (NOT-04). Table notification_deliveries · written by notifications (worker).

• Relations: notification → Notification (notificationId, onDelete: SetNull) · user → User (userId, onDelete: SetNull) · template → NotificationTemplate (templateId, onDelete: SetNull) · device → Device (deviceId, onDelete: SetNull).
• Keys & indexes: @@index([status, createdAt]) · @@index([userId, createdAt]) · @@index([notificationId]) · @@index([provider, providerMessageId]).
• Enums: NotificationChannel, NotificationDeliveryStatus (proposed).

Example row:

{
  "id": "01a1ac74-dbb0-7272-8f3e-c3bce69763e7",
  "notificationId": "01a1ac74-d7c8-7c97-a660-3603cea892ac",
  "userId": "01a1a077-1080-7aed-87e0-11ae957ae11e",
  "channel": "PUSH",
  "status": "SENT",
  "eventKey": "order.status_changed",
  "templateId": "01a15db0-5680-73a9-8d1e-e4fb0c9693b3",
  "deviceId": "01a1aab7-9380-72b4-afca-8f9ed2d4913e",
  "destination": null,
  "provider": "fcm",
  "providerMessageId": "projects/hoco-prod/messages/0:1730822702%31bd1c9631bd1c96",
  "attempts": 1,
  "errorCode": null,
  "errorMessage": null,
  "sentAt": "2026-11-05T16:05:03.000Z",
  "deliveredAt": null,
  "failedAt": null,
  "createdAt": "2026-11-05T16:05:02.000Z",
  "updatedAt": "2026-11-05T16:05:03.000Z"
}

NotificationTemplateCopy Anchor Link

Localised text of one event on one channel (NOT-04). Table notification_templates · written by notifications (seeded; admin.notifications.templates, BO-58).

• Relations: updatedBy → User (updatedById, onDelete: SetNull).
• Referenced by: NotificationDelivery.templateId.
• Keys & indexes: @@unique([eventKey, channel, locale]).
• Enums: NotificationChannel.

Example row:

{
  "id": "01a15db0-5680-73a9-8d1e-e4fb0c9693b3",
  "eventKey": "order.status_changed",
  "channel": "PUSH",
  "locale": "fr",
  "title": "Commande {{order.statusLabel}}",
  "body": "Votre commande {{order.number}} est maintenant « {{order.statusLabel}} ».",
  "isActive": true,
  "updatedById": null,
  "createdAt": "2026-10-21T09:00:00.000Z",
  "updatedAt": "2026-10-21T09:00:00.000Z"
}

NotificationRuleCopy Anchor Link

One row of the event → recipient → channels matrix (NOT-04, cahier §8). Table notification_rules · written by notifications (seeded from the notifications matrix; admin.notifications.rules).

• Relations: updatedBy → User (updatedById, onDelete: SetNull).
• Keys & indexes: @@unique([eventKey, recipient]).
• Enums: NotificationChannel, NotificationCategory.

Example row:

{
  "id": "01a15db0-5680-74ba-81f7-6eea77d9740b",
  "eventKey": "order.status_changed",
  "recipient": "RESELLER",
  "channels": [
    "IN_APP",
    "PUSH"
  ],
  "category": "TRANSACTIONAL",
  "isEnabled": true,
  "isMandatory": true,
  "updatedById": null,
  "createdAt": "2026-10-21T09:00:00.000Z",
  "updatedAt": "2026-10-21T09:00:00.000Z"
}