Platform

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) plus updatedById.
• 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.

FilesCopy Anchor Link

FileCopy Anchor Link

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.

• 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.fileId and 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 file READY, 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"
}

AuditCopy Anchor Link

AuditLogCopy Anchor Link

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).

• 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/SELECT only on this table (granted in the migration). Retention proposal: 3 years, purged monthly by at.

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"
}

ConfigurationCopy Anchor Link

SettingCopy Anchor Link

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).

• Relations: updatedBy → User (updatedById, onDelete: SetNull).
• Keys & indexes: primary key only.
• No id and no createdAt (scaffold shape). Every update writes AuditLog UPDATE with before/after; values are cached in Redis and invalidated through the outbox event setting.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
}

NumberSequenceCopy Anchor Link

Counter behind every human-readable business number (ADM-04). Table number_sequences · written by settings (numbering helper, called inside business transactions).

• Relations: none outgoing.
• Keys & indexes: @@id([key, year]).
• Allocation: INSERT … ON CONFLICT (key, year) DO UPDATE SET value = number_sequences.value + 1 RETURNING value in 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"
}

ReferenceItemCopy Anchor Link

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).

• 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.lists too, 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"
}

AppVersionPolicyCopy Anchor Link

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).

• 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"
}

DashboardLayoutCopy Anchor Link

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.*).

• Relations: updatedBy → User (updatedById, onDelete: SetNull).
• Keys & indexes: roleKey unique.
• Enums: RoleKey.
• No FK to Role: roleKey is 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"
}

Reliability: idempotency and outboxCopy Anchor Link

IdempotencyRecordCopy Anchor Link

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).

• 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"
}

OutboxEventCopy Anchor Link

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.

• 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"
}

JobsCopy Anchor Link

ExportJobCopy Anchor Link

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.

• 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"
}

ImportJobCopy Anchor Link

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).

• 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, reseller phone, user phone/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"
}