Platform
Files, audit journal, settings, business-number sequences, idempotency records, the transactional outbox, export and import jobs, app version policy, dashboard layouts, back-office workspace preferences and snoozes, staff invitations, pinned reports, report schedules, the first-setup checklist and reference lists.
Cross-cutting tables used by every module (SRV-07, SRV-08, SRV-23): files, the activity journal, settings and numbering, idempotency and the transactional outbox, asynchronous jobs and administration data. Prisma file: platform.prisma.
- Files hold metadata only; objects live in S3-compatible storage (MinIO locally). Public files (product images, brand logos, banners) are served by the CDN, private ones through 5-minute signed URLs after the owning record's access check.
- AuditLog is append-only and written in the same transaction as the change (AUD-01).
- Setting keeps the exact shape of the scaffold (
key,value,description,updatedAt) plusupdatedById. - IdempotencyRecord and OutboxEvent make retries safe: a replayed request returns its stored response, and an event is never lost because it commits with the change it announces.
Files
File
Metadata of every object in S3-compatible storage — uploads and generated documents (INT-05, SEC-04). Table files · written by files (files.createUpload, files.completeUpload, files.downloadUrl); generated documents by billing/fulfillment/analytics.
| Field | Prisma type | Null | Default | Constraints / notes |
|---|---|---|---|---|
id | String @db.Uuid | — | uuid(7) | PK. Client-generated UUIDv7 accepted so an offline-queued photo can be referenced before its upload completes. |
bucket | String @db.VarChar(63) | — | — | hoco-public or hoco-private (per environment). |
key | String | — | — | Object key: <purpose>/<yyyy>/<mm>/<id>.<ext>. |
purpose | FilePurpose | — | — | — |
visibility | FileVisibility | — | — | Derived from purpose at creation, stored to choose the bucket and the URL kind. |
status | FileStatus | — | PENDING | — |
mimeType | String @db.VarChar(100) | — | — | — |
sizeBytes | Int | — | — | Declared at createUpload, checked at completion against the purpose limit (5 MB photos, 10 MB documents). |
checksumSha256 | String @db.VarChar(64) | yes | — | Hex SHA-256 sent by the client and verified by the storage on PUT. |
originalName | String | yes | — | — |
width | Int | yes | — | — |
height | Int | yes | — | — |
thumbnailKey | String | yes | — | Generated 400 px WebP thumbnail of images. |
uploadedById | String @db.Uuid | yes | — | FK → User · Restrict. null for documents generated by the system. |
uploadExpiresAt | DateTime | yes | — | Expiry of the pre-signed PUT URL (10 min); PENDING rows past it are purged hourly. |
completedAt | DateTime | yes | — | — |
deletedAt | DateTime | yes | — | — |
createdAt | DateTime | — | now() | — |
updatedAt | DateTime | — | @updatedAt | — |
- Relations:
uploadedBy→ User (uploadedById,onDelete: Restrict). - Referenced by: User.
avatarFileId, ResellerDocument.fileId, ResellerPhoto.fileId, Brand.logoFileId, ProductImage.fileId, OrderItem.imageFileId, Delivery.proofFileId, Delivery.deliveryNoteFileId, Invoice.pdfFileId, Payment.proofFileId, CreditNote.pdfFileId, Promotion.imageFileId, Banner.imageFileId, Pack.imageFileId, Reward.imageFileId, VisitPhoto.fileId, Announcement.imageFileId, ExportJob.fileIdand 1 more. - Keys & indexes:
@@unique([bucket, key])·@@index([uploadedById, createdAt])·@@index([status, createdAt])·@@index([purpose]). - Enums: FilePurpose, FileVisibility (proposed), FileStatus (proposed).
- Access to a private file is decided by the owning record (ResellerDocument, VisitPhoto, Invoice…), never by the File row alone.
- Files are referenced by explicit foreign keys or, for multi-attachment fields, by UUID arrays (
attachmentFileIds,photoFileIds): those arrays are validated on write (each fileREADY, uploaded by the caller, purpose allowed) because Postgres cannot enforce foreign keys on array elements.
Example row:
{
"id": "01a19b4c-f0ce-792f-b254-d23fb48067c2",
"bucket": "hoco-private",
"key": "store_photo/2026/11/01a19b4c-f0ce-792f-b254-d23fb48067c2.jpg",
"purpose": "STORE_PHOTO",
"visibility": "PRIVATE",
"status": "READY",
"mimeType": "image/jpeg",
"sizeBytes": 842311,
"checksumSha256": "a1f3c2d4e5b6978899aabbccddeeff00112233445566778899aabbccddeeff00",
"originalName": "facade.jpg",
"width": 1600,
"height": 1200,
"thumbnailKey": "store_photo/2026/11/01a19b4c-f0ce-792f-b254-d23fb48067c2.thumb.webp",
"uploadedById": "01a1a024-7d34-7424-adb6-099ecaff5095",
"uploadExpiresAt": "2026-11-02T09:48:50.000Z",
"completedAt": "2026-11-02T09:39:20.000Z",
"deletedAt": null,
"createdAt": "2026-11-02T09:38:50.000Z",
"updatedAt": "2026-11-02T09:39:20.000Z"
}Audit
AuditLog
The activity journal of cahier §10 (AUD-01): logins, creations, modifications, validations, exports, deletions, overrides — with user, time and context. Table audit_logs · written by audit (written by every module through one helper, in the business transaction).
| Field | Prisma type | Null | Default | Constraints / notes |
|---|---|---|---|---|
id | String @db.Uuid | — | uuid(7) | PK. |
at | DateTime | — | — | When the action took effect: request time, or the device occurredAt for replayed offline actions. |
actorUserId | String @db.Uuid | yes | — | FK → User · Restrict. null for SYSTEM actions and failed sign-ins of unknown phones. |
actorRole | RoleKey | yes | — | Role that granted the permission used (a staff member may hold several). |
permissionKey | String @db.VarChar(64) | yes | — | Permission checked for the action. |
app | ClientApp | — | — | — |
action | AuditAction | — | — | — |
entityType | String @db.VarChar(40) | — | — | Prisma model name (Order, Reseller, Setting…). |
entityId | String @db.VarChar(64) | yes | — | Text because some keys are not UUIDs (Setting keys). |
summary | String | — | — | Human-readable French line shown in BO-63. |
diff | Json | yes | — | Changed fields as before/after objects; restricted fields (purchase prices, password hashes, tokens) are never written. |
ip | String @db.Inet | yes | — | — |
userAgent | String | yes | — | — |
requestId | String @db.VarChar(40) | yes | — | Same id as the error envelope and the logs. |
createdAt | DateTime | — | now() | Insertion time (= at except for offline replays). |
- Relations:
actorUser→ User (actorUserId,onDelete: Restrict). - Keys & indexes:
@@index([entityType, entityId, at(sort: Desc)])·@@index([actorUserId, at(sort: Desc)])·@@index([action, at(sort: Desc)]). - Enums: RoleKey, ClientApp, AuditAction.
- Append-only: the application role has
INSERT/SELECTonly on this table (granted in the migration). Retention proposal: 3 years, purged monthly byat.
Constraints and indexes Prisma cannot express (raw SQL in the migration):
CREATE INDEX audit_logs_at_brin ON audit_logs USING brin (at);Example row:
{
"id": "01a1ac74-d3e0-74e3-8533-0303fea41b2e",
"at": "2026-11-05T16:05:00.000Z",
"actorUserId": "01a1a00a-4535-7ff7-af24-a0a3bb095b70",
"actorRole": "COMMERCIAL_ADMIN",
"permissionKey": "orders.validate",
"app": "DASHBOARD",
"action": "VALIDATE",
"entityType": "Order",
"entityId": "01a1ac4b-a100-74df-8237-614783cfeacd",
"summary": "Commande CMD-2026-000158 validée",
"diff": {
"before": {
"status": "SUBMITTED"
},
"after": {
"status": "VALIDATED"
}
},
"ip": "41.111.42.10",
"userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64)",
"requestId": "req_01JC9W5X7K2M",
"createdAt": "2026-11-05T16:05:00.000Z"
}Configuration
Setting
A business parameter (ADM-03). Shape fixed by the scaffold: key, JSON value, description, update time and author; each key has a Zod schema in packages/contracts. Table settings · written by settings (typed settings service; admin.settings.update, BO-64).
| Field | Prisma type | Null | Default | Constraints / notes |
|---|---|---|---|---|
key | String | — | — | PK. Dotted key, e.g. orders.minAmount, stock.reserveOn. |
value | Json | — | — | Validated by the key's Zod schema on every write. |
description | String | yes | — | — |
updatedAt | DateTime | — | @updatedAt | — |
updatedById | String @db.Uuid | yes | — | FK → User · SetNull. null for seeded values. |
- Relations:
updatedBy→ User (updatedById,onDelete: SetNull). - Keys & indexes: primary key only.
- No
idand nocreatedAt(scaffold shape). Every update writes AuditLogUPDATEwith before/after; values are cached in Redis and invalidated through the outbox eventsetting.updated.
Example row:
{
"key": "field.gpsToleranceMeters",
"value": 150,
"description": "Distance maximale (m) entre le check-in et le point de vente.",
"updatedAt": "2026-10-21T09:00:00.000Z",
"updatedById": null
}NumberSequence
Counter behind every human-readable business number (ADM-04). Table number_sequences · written by settings (numbering helper, called inside business transactions).
| Field | Prisma type | Null | Default | Constraints / notes |
|---|---|---|---|---|
key | String @db.VarChar(20) | — | — | PK. Part of the composite PK: RESELLER, DELEGATE, ORDER, INVOICE, DELIVERY_NOTE, PAYMENT, TOUR, VISIT, TICKET, RETURN, CREDIT_NOTE. |
year | Int | — | — | PK. Part of the composite PK. Africa/Algiers calendar year; 0 for sequences that never reset (RESELLER, DELEGATE). |
value | Int | — | 0 | Last number issued. |
createdAt | DateTime | — | now() | — |
updatedAt | DateTime | — | @updatedAt | — |
- Relations: none outgoing.
- Keys & indexes:
@@id([key, year]). - Allocation:
INSERT … ON CONFLICT (key, year) DO UPDATE SET value = number_sequences.value + 1 RETURNING valuein the transaction that creates the record — the row lock serialises concurrent allocations and a rollback leaves no gap (required for invoices and credit notes).
Example row:
{
"key": "ORDER",
"year": 2026,
"value": 158,
"createdAt": "2026-10-21T09:00:00.000Z",
"updatedAt": "2026-11-05T15:20:00.000Z"
}ReferenceItem
An item of an editable reference list (ADM-05): business types, person roles, reasons and issue tags. Table reference_items · written by settings (seeded; admin.referenceLists.crud, BO-65; served by reference.lists).
| Field | Prisma type | Null | Default | Constraints / notes |
|---|---|---|---|---|
id | String @db.Uuid | — | uuid(7) | PK. |
listKey | String @db.VarChar(40) | — | — | BUSINESS_TYPE, PERSON_ROLE, VISIT_NOT_DONE_REASON, VISIT_ISSUE_TAG, TOUR_SUSPEND_REASON, ORDER_REFUSAL_REASON, ORDER_CANCEL_REASON, PAYMENT_REJECT_REASON (AUTHORING §3). A string, so a list can be added without a migration. |
code | String @db.VarChar(40) | — | — | Stable UPPER_SNAKE code stored by the business tables. |
labelFr | String | — | — | — |
labelAr | String | yes | — | — |
labelEn | String | yes | — | — |
sortOrder | Int | — | 0 | — |
isActive | Boolean | — | true | Deactivate, never delete an item in use. |
requiresComment | Boolean | — | false | The free comment becomes mandatory (OTHER). |
isSystem | Boolean | — | false | Used by the system (e.g. ASSISTED_EXPIRED): cannot be deactivated or recoded. |
createdAt | DateTime | — | now() | — |
updatedAt | DateTime | — | @updatedAt | — |
- Relations: none outgoing.
- Keys & indexes:
@@unique([listKey, code])·@@index([listKey, isActive, sortOrder]). - Potentials, visit types, ticket types, carriers and payment methods are served by
reference.liststoo, but come from enums or their own tables.
Example row:
{
"id": "01a15db0-5680-7106-b45f-374b7877bd97",
"listKey": "VISIT_NOT_DONE_REASON",
"code": "STORE_CLOSED",
"labelFr": "Magasin fermé",
"labelAr": "المحل مغلق",
"labelEn": "Store closed",
"sortOrder": 1,
"isActive": true,
"requiresComment": false,
"isSystem": false,
"createdAt": "2026-10-21T09:00:00.000Z",
"updatedAt": "2026-10-21T09:00:00.000Z"
}AppVersionPolicy
Minimum and latest version, store link and maintenance mode per app and platform (ADM-07, AUTH-09). Table app_version_policies · written by settings (admin.appVersions.crud, BO-66; read by appConfig.get).
| Field | Prisma type | Null | Default | Constraints / notes |
|---|---|---|---|---|
id | String @db.Uuid | — | uuid(7) | PK. |
app | ClientApp | — | — | Not SYSTEM (CHECK). |
platform | DevicePlatform | — | — | WEB for the reseller web and back-office (maintenance flag only). |
minVersion | String @db.VarChar(20) | — | — | Semver; below it the API answers 426 UPGRADE_REQUIRED. |
latestVersion | String @db.VarChar(20) | — | — | Above the installed version → soft update prompt. |
storeUrl | String | yes | — | — |
maintenance | Boolean | — | false | — |
maintenanceUntil | DateTime | yes | — | Expected end of the maintenance (BO-66 « jusqu'à », SRV-42): appConfig.get → maintenance.until and 503 MAINTENANCE → details.until. Informative — maintenance stays on until it is switched off; cleared when it is. |
messageFr | String | yes | — | Maintenance / update message; the API returns message in the request locale. |
messageAr | String | yes | — | — |
messageEn | String | yes | — | — |
updatedById | String @db.Uuid | yes | — | FK → User · SetNull. |
createdAt | DateTime | — | now() | — |
updatedAt | DateTime | — | @updatedAt | — |
- Relations:
updatedBy→ User (updatedById,onDelete: SetNull). - Keys & indexes:
@@unique([app, platform]). - Enums: ClientApp, DevicePlatform (proposed).
Constraints and indexes Prisma cannot express (raw SQL in the migration):
ALTER TABLE app_version_policies ADD CONSTRAINT app_version_policies_app CHECK (app <> 'SYSTEM');Example row:
{
"id": "01a15db0-5680-75c2-bcb2-09e85b6c3096",
"app": "RESELLER_APP",
"platform": "ANDROID",
"minVersion": "1.0.0",
"latestVersion": "1.0.2",
"storeUrl": "https://play.google.com/store/apps/details?id=dz.hoco.reseller",
"maintenance": false,
"messageFr": null,
"messageAr": null,
"messageEn": null,
"updatedById": null,
"createdAt": "2026-10-21T09:00:00.000Z",
"updatedAt": "2026-10-21T09:00:00.000Z"
}DashboardLayout
Cards of the general dashboard per role (cahier §4.1 configurables par rôle, ANA-02). Table dashboard_layouts · written by analytics (admin.dashboard.layout.*).
| Field | Prisma type | Null | Default | Constraints / notes |
|---|---|---|---|---|
id | String @db.Uuid | — | uuid(7) | PK. |
roleKey | RoleKey | — | — | Unique. |
cards | Json | — | — | Ordered array of cardKey, size (S, M, L) and per-card default filters; unknown or unpermitted cards are dropped at read time. |
updatedById | String @db.Uuid | yes | — | FK → User · SetNull. |
createdAt | DateTime | — | now() | — |
updatedAt | DateTime | — | @updatedAt | — |
- Relations:
updatedBy→ User (updatedById,onDelete: SetNull). - Keys & indexes:
roleKeyunique. - Enums: RoleKey.
- No FK to Role:
roleKeyis the enum and the eight roles are fixed.
Example row:
{
"id": "01a15db0-5680-7380-8fb5-0c5aa68acea4",
"roleKey": "FINANCE_MANAGER",
"cards": [
{
"cardKey": "revenue.month",
"size": "M"
},
{
"cardKey": "payments.expected",
"size": "M"
},
{
"cardKey": "credit.used",
"size": "S"
},
{
"cardKey": "invoices.unpaid",
"size": "L"
}
],
"updatedById": "01a1a00a-4535-7ff7-af24-a0a3bb095b70",
"createdAt": "2026-10-21T09:00:00.000Z",
"updatedAt": "2026-10-21T09:00:00.000Z"
}Back-office workspace
Per-user state of the back-office shell (BO-03). Prisma files: workspace-preferences.prisma, workspace-snoozes.prisma · written by workspace (admin.workspace.*).
WorkspacePreference
Interface preferences of a staff user, one row per user, created on the first save (admin.workspace.preferences.update); until then the API answers the defaults. Table workspace_preferences.
| Field | Prisma type | Null | Default | Constraints / notes |
|---|---|---|---|---|
userId | String @db.Uuid | — | — | PK; FK → User · Cascade. |
startPage | WorkspaceStartPage | — | DASHBOARD | Rejected when the user lacks the page's permission. |
shortcuts | WorkspaceQuickAction[] | — | [] | Ordered, without duplicates, at most 9. |
topBarTheme | WorkspaceTopBarTheme | — | DARK | DARK, LIGHT. |
textSize | Int | — | 100 | CHECK workspace_preferences_text_size: 100, 112, 125 or 150 (percent). |
workloadLimit | Int | yes | — | CHECK workspace_preferences_workload_limit: 3–100; null = never propose the focused triage. |
createdAt | DateTime | — | now() | — |
updatedAt | DateTime | — | @updatedAt | — |
- Relations:
user→ User (userId,onDelete: Cascade).
WorkItemSnooze
An item of the « To handle » work list snoozed by a staff user until a time (at most 7 days ahead). A row whose until has passed is ignored and deleted lazily. Table work_item_snoozes.
| Field | Prisma type | Null | Default | Constraints / notes |
|---|---|---|---|---|
id | String @db.Uuid | — | uuid(7) | PK. |
userId | String @db.Uuid | — | — | FK → User · Cascade. |
kind | WorkItemKind | — | — | ORDER_TO_CONFIRM, PAYMENT_TO_VERIFY, ACCOUNT_TO_VALIDATE, TICKET_UNASSIGNED, STOCK_ALERT, RETURN_TO_HANDLE, VISIT_TO_REVIEW (DES-02 phase 7). |
entityId | String @db.Uuid | — | — | The order, payment, reseller, ticket, variant or return (no FK: polymorphic). |
until | DateTime | — | — | — |
createdAt | DateTime | — | now() | — |
- Relations:
user→ User (userId,onDelete: Cascade). - Keys & indexes: unique (
userId,kind,entityId); index (userId,until).
Back-office home and reports
Staff invitations, reports pinned to the dashboard, report schedules and the platform first-setup checklist (design 02). Prisma files: staff-invitations.prisma, pinned-reports.prisma, report-schedules.prisma, setup-steps.prisma · written by identity (auth.staffInvitations.*, admin.users.create, admin.users.resendInvitation), workspace (admin.workspace.pins.*), analytics (admin.reports.schedules.*, job reports.schedules) and home (admin.home.setup.*).
StaffInvitation
The single-use link of a new back-office user, valid 14 days. Only the SHA-256 of the token is stored; a new invitation sets revokedAt on the open one. Table staff_invitations.
| Field | Prisma type | Null | Default | Constraints / notes |
|---|---|---|---|---|
id | String @db.Uuid | — | uuid(7) | PK. |
userId | String @db.Uuid | — | — | FK → User · Cascade. |
invitedById | String @db.Uuid | yes | — | FK → User · SetNull. Told by e-mail when the invitee joins or asks for a new link. |
tokenHash | String @db.VarChar(64) | — | — | Unique. |
channel | StaffInvitationChannel | — | — | EMAIL, SMS. |
sentAt | DateTime | — | now() | — |
expiresAt | DateTime | — | — | sentAt + 14 days. |
acceptedAt | DateTime | yes | — | Set once; a second acceptance answers 410 STAFF_INVITATION_EXPIRED. |
revokedAt | DateTime | yes | — | Replaced by a newer invitation. |
renewalRequestedAt | DateTime | yes | — | The invitee asked for a new link (at most once a day). |
createdAt | DateTime | — | now() | — |
- Keys & indexes: unique
tokenHash; index (userId,createdAtdesc).
PinnedReport
A report pinned to the caller's dashboard with the report page's parameters, at most 8 per user. Table pinned_reports.
| Field | Prisma type | Null | Default | Constraints / notes |
|---|---|---|---|---|
id | String @db.Uuid | — | uuid(7) | PK. |
userId | String @db.Uuid | — | — | FK → User · Cascade. |
reportKey | String @db.VarChar(64) | — | — | A key of the report catalogue. |
params | String @db.VarChar(1000) | — | "" | The report page's query string, keys sorted (period, compare, group, filters). |
name | String @db.VarChar(120) | yes | — | Set when the parameters differ from the defaults. |
position | Int | — | 0 | Order on the dashboard. |
createdAt | DateTime | — | now() | — |
- Keys & indexes: unique (
userId,reportKey,params); index (userId,position).
SavedView
A work list's filters and visible columns saved under a name by a back-office user (BO-38 « Vues », BO-41, BO-69), at most 20 per user and list; one view per list may open by default. Table saved_views.
| Field | Prisma type | Null | Default | Constraints / notes |
|---|---|---|---|---|
id | String @db.Uuid | — | uuid(7) | PK. |
userId | String @db.Uuid | — | — | FK → User · Cascade. |
listKey | String @db.VarChar(40) | — | — | orders, orders.toConfirm, returns, resellers. |
name | String @db.VarChar(60) | — | — | Unique per user and list, case-insensitive (checked by the API). |
params | String @db.VarChar(2000) | — | "" | The list page's query string, keys sorted. |
columns | String[] | — | [] | Visible column ids in order; empty = the list's defaults. |
isDefault | Boolean | — | false | At most one per user and list (the API clears the others). |
position | Int | — | 0 | Order in the views menu. |
createdAt, updatedAt | DateTime | — | — | — |
- Keys & indexes: unique (
userId,listKey,name); index (userId,listKey,position).
ReportSchedule
A report e-mailed at 08:00 Africa/Algiers every day, every Sunday or on the 1st of the month, over a rolling period. The worker builds the file once per recipient, with that recipient's permissions; recipients are active back-office users. Table report_schedules.
| Field | Prisma type | Null | Default | Constraints / notes |
|---|---|---|---|---|
id | String @db.Uuid | — | uuid(7) | PK. |
createdById | String @db.Uuid | — | — | FK → User · Cascade. |
reportKey | String @db.VarChar(64) | — | — | — |
filters | Json | — | {} | The report's own filters, without dates. |
period | ReportSchedulePeriod | — | — | LAST_7_DAYS, LAST_30_DAYS, LAST_90_DAYS, YEAR_TO_DATE. |
frequency | ReportScheduleFrequency | — | — | DAILY, WEEKLY, MONTHLY. |
format | ExportFormat | — | — | XLSX, CSV, PDF. |
recipients | String[] | — | — | E-mails, at most 20. |
nextRunAt | DateTime | — | — | Claimed by the job before sending (no double send). |
lastRunAt | DateTime | yes | — | — |
lastOutcome | ReportScheduleOutcome | yes | — | SENT, PARTIAL, FAILED. |
lastError | String @db.VarChar(500) | yes | — | — |
createdAt, updatedAt | DateTime | — | — | — |
- Keys & indexes: index
nextRunAt; index (createdById,reportKey).
SetupStep
A step of the platform first-setup checklist marked done or reopened by hand. Without a row, a step is done when the data proves it (a catalogue, prices, zones with delegates, more than one staff user, the company identity). Table setup_steps.
| Field | Prisma type | Null | Default | Constraints / notes |
|---|---|---|---|---|
key | SetupStepKey | — | — | PK: COMPANY, CATALOGUE, PRICES, ZONES, TEAM, RULES. |
doneAt | DateTime | yes | — | Null = reopened. |
doneById | String @db.Uuid | yes | — | FK → User · SetNull. |
updatedAt | DateTime | — | @updatedAt | — |
Reliability: idempotency and outbox
IdempotencyRecord
Stored outcome of a mutating request carrying an Idempotency-Key, replayed for duplicates (SYN-05). Kept 30 days. Table idempotency_records · written by sync (Idempotency-Key middleware, sync.batch).
| Field | Prisma type | Null | Default | Constraints / notes |
|---|---|---|---|---|
id | String @db.Uuid | — | uuid(7) | PK. |
scopeKey | String @db.VarChar(80) | — | — | user:<userId> for authenticated calls, device:<X-Device-Id> for public ones (invitations.attribute). |
key | String @db.Uuid | — | — | The Idempotency-Key header (UUID); sync.batch stores one record per item key. |
userId | String @db.Uuid | yes | — | FK → User · SetNull. |
method | String @db.VarChar(8) | — | — | — |
path | String | — | — | Route template plus ids, e.g. /v1/cart/checkout. |
requestHash | String @db.Char(64) | — | — | SHA-256 of the canonical JSON body: same key + different hash → 409 IDEMPOTENCY_KEY_REUSED. |
status | Int | yes | — | HTTP status of the stored response; null while the first request is still running (a concurrent duplicate gets 409). |
responseBody | Json | yes | — | — |
createdAt | DateTime | — | now() | — |
updatedAt | DateTime | — | @updatedAt | — |
expiresAt | DateTime | — | — | createdAt + 30 days; purged daily. |
- Relations:
user→ User (userId,onDelete: SetNull). - Keys & indexes:
@@unique([scopeKey, key])·@@index([expiresAt]).
Example row:
{
"id": "01a1ac4b-a100-7987-ba7d-09c1e0eb8aa7",
"scopeKey": "user:01a1a077-1080-7aed-87e0-11ae957ae11e",
"key": "5d0c7a2e-3f4b-4c1d-9e8f-7a6b5c4d3e2f",
"userId": "01a1a077-1080-7aed-87e0-11ae957ae11e",
"method": "POST",
"path": "/v1/cart/checkout",
"requestHash": "e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855",
"status": 201,
"responseBody": {
"id": "01a1ac4b-a100-74df-8237-614783cfeacd",
"number": "CMD-2026-000158",
"status": "SUBMITTED"
},
"createdAt": "2026-11-05T15:20:00.000Z",
"updatedAt": "2026-11-05T15:20:01.000Z",
"expiresAt": "2026-12-05T15:20:00.000Z"
}OutboxEvent
Transactional outbox: the event announcing a committed change, dispatched after commit to notifications, both SSE streams, analytics and CRM automation. Table outbox_events · written by every module writes it in its transaction; jobs (worker) dispatches it.
| Field | Prisma type | Null | Default | Constraints / notes |
|---|---|---|---|---|
id | String @db.Uuid | — | uuid(7) | PK. UUIDv7, time-ordered: also the SSE event id, so Last-Event-ID resumes with id > :last. |
eventKey | String @db.VarChar(64) | — | — | — |
aggregateType | String @db.VarChar(40) | — | — | — |
aggregateId | String @db.VarChar(64) | — | — | — |
payload | Json | — | — | Event body, versioned by a v field; carries the ids needed to pick recipients (reseller, delegate, roles). |
availableAt | DateTime | — | now() | Not before; pushed back by exponential backoff after a failure. |
dispatchedAt | DateTime | yes | — | — |
attempts | Int | — | 0 | — |
lastError | String | yes | — | — |
createdAt | DateTime | — | now() | — |
updatedAt | DateTime | — | @updatedAt | — |
- Relations: none outgoing.
- Keys & indexes:
@@index([aggregateType, aggregateId, createdAt]). - The worker claims batches with
FOR UPDATE SKIP LOCKED. Consumers are idempotent on the event id. Dispatched rows are kept 30 days (SSE resume window, debugging), then purged.
Constraints and indexes Prisma cannot express (raw SQL in the migration):
CREATE INDEX outbox_events_pending ON outbox_events (available_at) WHERE dispatched_at IS NULL;Example row:
{
"id": "01a1ac74-d3e0-73ea-a171-faaab6aef49f",
"eventKey": "order.status_changed",
"aggregateType": "Order",
"aggregateId": "01a1ac4b-a100-74df-8237-614783cfeacd",
"payload": {
"v": 1,
"orderId": "01a1ac4b-a100-74df-8237-614783cfeacd",
"number": "CMD-2026-000158",
"resellerId": "01a19ba2-2fe0-7300-a148-73d1a4ef38c0",
"from": "SUBMITTED",
"to": "VALIDATED"
},
"availableAt": "2026-11-05T16:05:00.000Z",
"dispatchedAt": "2026-11-05T16:05:00.420Z",
"attempts": 1,
"lastError": null,
"createdAt": "2026-11-05T16:05:00.000Z",
"updatedAt": "2026-11-05T16:05:00.420Z"
}Jobs
ExportJob
An asynchronous PDF/XLSX/CSV export of a list or report (ANA-05, cahier §9). Table export_jobs · written by analytics (admin.exports.* and list exports); executed by jobs.
| Field | Prisma type | Null | Default | Constraints / notes |
|---|---|---|---|---|
id | String @db.Uuid | — | uuid(7) | PK. |
requestedById | String @db.Uuid | — | — | FK → User · Restrict. |
kind | String @db.VarChar(64) | — | — | List or report key, e.g. crm.resellers, reports.revenue-by-zone. |
format | ExportFormat | — | — | — |
filters | Json | — | {} | Filters as sent by the screen, re-validated by the report's Zod schema; the export never exceeds the requester's scope. |
status | JobStatus | — | QUEUED | — |
attempts | Int | — | 0 | — |
fileId | String @db.Uuid | yes | — | FK → File · Restrict. GENERATED_DOCUMENT. |
rowCount | Int | yes | — | — |
errorMessage | String | yes | — | — |
startedAt | DateTime | yes | — | — |
finishedAt | DateTime | yes | — | — |
expiresAt | DateTime | yes | — | finishedAt + exports.retentionDays (7); the file is purged, the row stays. |
createdAt | DateTime | — | now() | — |
updatedAt | DateTime | — | @updatedAt | — |
- Relations:
requestedBy→ User (requestedById,onDelete: Restrict) ·file→ File (fileId,onDelete: Restrict). - Keys & indexes:
@@index([requestedById, createdAt(sort: Desc)])·@@index([status, createdAt]). - Enums: ExportFormat, JobStatus.
Example row:
{
"id": "01a1f866-0d00-7486-93b6-b10d7dae0c5e",
"requestedById": "01a1a00a-4535-7ff7-af24-a0a3bb095b70",
"kind": "crm.resellers",
"format": "XLSX",
"filters": {
"status": [
"ACTIVE"
],
"wilayaCode": [
"16"
]
},
"status": "SUCCEEDED",
"attempts": 1,
"fileId": "01a19b4e-c2dc-79d4-96b7-afd2c468baa1",
"rowCount": 312,
"errorMessage": null,
"startedAt": "2026-11-20T10:00:02.000Z",
"finishedAt": "2026-11-20T10:00:09.000Z",
"expiresAt": "2026-11-27T10:00:09.000Z",
"createdAt": "2026-11-20T10:00:00.000Z",
"updatedAt": "2026-11-20T10:00:09.000Z"
}ImportJob
An Excel/CSV import run — dry-run validation first, then apply (CAT-06, ADM-06). Table import_jobs · written by jobs with type handlers in catalog, crm, identity, inventory (admin.imports.*, admin.products.import).
| Field | Prisma type | Null | Default | Constraints / notes |
|---|---|---|---|---|
id | String @db.Uuid | — | uuid(7) | PK. |
type | ImportType | — | — | — |
fileId | String @db.Uuid | — | — | FK → File · Restrict. IMPORT_FILE. |
status | JobStatus | — | QUEUED | — |
dryRun | Boolean | — | — | — |
parentJobId | String @db.Uuid | yes | — | FK → ImportJob · Restrict. For an apply run: the dry run it executes (must be SUCCEEDED and on the same file). |
report | Json | yes | — | Row-level report: array of row, status (CREATED, UPDATED, SKIPPED, ERROR), errors[] with column and message; also exported as a file of error rows. |
totalRows | Int | — | 0 | — |
validRows | Int | — | 0 | — |
errorRows | Int | — | 0 | — |
createdRows | Int | — | 0 | — |
updatedRows | Int | — | 0 | — |
skippedRows | Int | — | 0 | — |
attempts | Int | — | 0 | — |
requestedById | String @db.Uuid | — | — | FK → User · Restrict. |
startedAt | DateTime | yes | — | — |
finishedAt | DateTime | yes | — | — |
errorMessage | String | yes | — | — |
createdAt | DateTime | — | now() | — |
updatedAt | DateTime | — | @updatedAt | — |
- Relations:
file→ File (fileId,onDelete: Restrict) ·parentJob→ ImportJob (parentJobId,onDelete: Restrict) ·requestedBy→ User (requestedById,onDelete: Restrict). - Referenced by: PriceChange.
importJobId, ImportJob.parentJobId. - Keys & indexes:
@@index([type, createdAt(sort: Desc)])·@@index([status]). - Enums: ImportType (proposed), JobStatus.
- Apply runs are idempotent on natural keys (variant
sku, resellerphone, userphone/email), so re-running an apply updates instead of duplicating.
Example row:
{
"id": "01a18d1c-3b00-73ec-8845-a809d2af0dcb",
"type": "PRODUCTS",
"fileId": "01a19b3d-3615-7ee6-8fc6-40cb46d40ee0",
"status": "SUCCEEDED",
"dryRun": false,
"parentJobId": "01a18c2c-a896-7971-b889-53cc2e2a5063",
"report": [
{
"row": 14,
"status": "ERROR",
"errors": [
{
"column": "priceDemiGros",
"message": "Valeur manquante"
}
]
}
],
"totalRows": 420,
"validRows": 419,
"errorRows": 1,
"createdRows": 380,
"updatedRows": 39,
"skippedRows": 1,
"attempts": 1,
"requestedById": "01a19f89-c802-701b-83ec-271e6091a1ed",
"startedAt": "2026-10-30T14:00:03.000Z",
"finishedAt": "2026-10-30T14:01:12.000Z",
"errorMessage": null,
"createdAt": "2026-10-30T14:00:00.000Z",
"updatedAt": "2026-10-30T14:01:12.000Z"
}Support and notifications
Tickets and after-sales claims with their items and resolution, regional WhatsApp support, announcements, the notification inbox, delivery log, templates and rules.
API reference
Conventions, shared schemas and the full operation catalogue of the HOCO API — the contract every app is built against.