Database design

PostgreSQL 17 is the single database of the ecosystem (cahier §2). It holds 106 models: 96 created by the V1 migration and 10 documented as the V2 baseline (not created before P4). Every model below uses exactly the canonical names of AUTHORING §3.

ConventionsCopy Anchor Link

Schema layout and toolingCopy Anchor Link

• Prisma 7, multi-file schema: one file per domain page of this section, plus schema.prisma (generator prisma-client with an explicit output, datasource postgresql) and enums.prisma (every enum, mirroring content/spec/enums.json). prisma.config.ts points schema to prisma/schema and migrations.path to prisma/migrations; the connection URL comes from the environment and the client uses the @prisma/adapter-pg driver adapter.
• Names: models PascalCase singular (OrderItem); tables snake_case plural through @@map("order_items"); every camelCase field maps to its snake_case column through @map (subtotalHt → subtotal_ht) — this rule is mechanical, so column names are not repeated in the tables; Postgres enum types are snake_case singular through @@map (OrderStatus → order_status); enum values are UPPER_SNAKE_CASE exactly as in enums.json, identical in the database, the API and the apps.
• Prisma relation fields are named after the foreign key without Id (resellerId → reseller, assignedDelegateId → assignedDelegate); every model page lists them with their onDelete.

Keys and identifiersCopy Anchor Link

• Primary key: id String @id @default(uuid(7)) @db.Uuid. UUIDv7 is time-ordered, so ORDER BY id DESC is a valid cursor for newest-first lists and B-tree inserts stay local.
• Client-generated ids (offline creation, AUTHORING §3): Reseller (field prospects), Visit (ad-hoc), VisitPhoto, VisitReport, Interaction (notes), ResellerPhoto and File accept a UUIDv7 sent by the app; the server checks the version, upserts idempotently and answers 409 if the id exists for another creator.
• Exceptions: Wilaya.code (official code), DelegateProfile.id (= the delegate's User.id), Setting.key, NumberSequence (key, year) and the link tables with a composite primary key — RolePermission, UserRole, DelegateZone, ResellerSegment, Favorite, AnnouncementRead, SupportRegionWilaya.
• Business numbers come from NumberSequence (see numbering).

TimestampsCopy Anchor Link

• createdAt DateTime @default(now()) and updatedAt DateTime @updatedAt on every table, with three deliberate exceptions: append-only and link rows have no updatedAt because they are never updated — RolePermission, UserRole, ZoneArea, DelegateZone, ResellerStatusChange, ResellerAssignment, ResellerSegment, ProductVisibilityRule, Favorite, PriceChange, StockMovement, OrderStatusChange, OrderChange, InvoiceLine, CreditLimitChange, PromotionScope, PromotionAudience, PackItem (V2), LoyaltyTransaction (V2), TourStatusChange, VisitPhoto, LocationPing, InvitationEvent, TicketMessage, SupportRegionWilaya, AuditLog; Setting has no createdAt (scaffold shape); AnnouncementRead uses readAt as its creation time.
• Every DateTime is @db.Timestamptz(3) (UTC instant); date-only values are @db.Date (tourDate, dueDate, periodStart). Business days are computed in Africa/Algiers.
• Offline actions keep two times: occurredAt (device) and the server receipt time. On append-only rows the receipt time is createdAt — exposed by the API as receivedAt; on rows that change state (Visit) the key receipt time has its own column (checkInReceivedAt).

Value typesCopy Anchor Link

Deletion, history and concurrencyCopy Anchor Link

• No hard deletes of business records. Records with a lifecycle change status (DISABLED, ARCHIVED, CANCELLED…); reference and configuration rows get archivedAt or isActive = false; nothing referenced is ever removed (SEC-06).
• Rows that may be deleted (technical or link rows, with the change audited where it matters): Session, Verification, Device, CartItem, Favorite, ProductImage, ProductVisibilityRule, RolePermission, UserRole, DelegateZone, ZoneArea, ResellerSegment, SupportRegionWilaya, PaymentAllocation (replaced as a set), PromotionScope and PromotionAudience of an unpublished promotion, and a DRAFT assisted order discarded by its delegate.
• onDelete policy: Cascade only for composition (lines of an order or invoice, items of a cart, areas of a zone…), SetNull for optional pointers to replaceable rows (avatar or image files, templates, devices), Restrict everywhere else. Since parents are never deleted, Cascade fires only on the allowed deletions above.
• History tables: ResellerStatusChange, ResellerAssignment, OrderStatusChange, OrderChange, TourStatusChange, StockMovement, PriceChange, CreditLimitChange, InvitationEvent, AuditLog (and V2 LoyaltyTransaction) — see the lifecycles page.
• Optimistic locking with version Int @default(1) on Order, Tour, Visit, CreditAccount (and Reseller, edited offline by delegates): every update is … SET version = version + 1 WHERE id = :id AND version = :expected; zero rows → 409 VERSION_CONFLICT (or a sync CONFLICT item). Counters that change under concurrency (stock levels, promotion caps, number sequences) use atomic UPDATE … SET x = x + :n instead.

JSON columnsCopy Anchor Link

Derived values that are not storedCopy Anchor Link

Stored caches are named as such on their model page and have a single writer in the same transaction as their inputs: Reseller.lastOrderAt/lastVisitAt/supportRegionId, Invoice.amountPaid/amountCredited, CreditNote.amountApplied, Product.popularityScore, Promotion.usedQuantity, Visit.durationSeconds, Ticket.lastMessageAt, the Invitation step columns, V2 LoyaltyAccount.pointsBalance.

Domain mapCopy Anchor Link

Core diagramCopy Anchor Link

The core entities and how a point of sale, its orders and the field work connect. Each domain page has its own detailed diagram.

Business numbersCopy Anchor Link

Human-readable numbers are allocated by NumberSequence inside the transaction that creates the record (INSERT … ON CONFLICT … DO UPDATE SET value = value + 1 RETURNING value): concurrent allocations serialise on one row and a rollback leaves no gap. Yearly sequences use the Africa/Algiers year; padding grows beyond 6 digits instead of wrapping.

Invitation codes are not sequential: 8 random characters without ambiguous letters, unique (Invitation.code).

Proposed enumsCopy Anchor Link

The design needs categorical columns that enums.json does not define yet. They are Prisma enums with these values; French labels are to be added to enums.json by Anis before SRV-02.

Migration strategyCopy Anchor Link

1. Prisma Migrate only. prisma migrate dev locally, prisma migrate deploy as the release step of the api service on Dokploy before the new image takes traffic; the worker waits for it.
2. Never edit an applied migration. A mistake is fixed by a new migration. CI runs prisma migrate diff between the migrations folder and the schema and fails on drift.
3. Expand / contract for every breaking change: add the new column or table (nullable or with a default) → deploy code that writes both → backfill in a job → switch reads → enforce NOT NULL / drop the old column in a later release. Renames are expand/contract too, never in place.
4. Raw SQL inside migrations for what the Prisma schema cannot express — created with prisma migrate dev --create-only and edited before applying: extensions pg_trgm and unaccent with the immutable wrapper below, every CHECK constraint, every partial or expression index, BRIN indexes and the INSERT/SELECT-only grant on audit_logs. Each model page shows its raw SQL; the same statements are listed in a comment at the top of the corresponding .prisma file so the schema stays self-explanatory.
5. Enum changes: adding a value is a migration (ALTER TYPE … ADD VALUE, outside a transaction block); a value is never removed while referenced.
6. V2 tables (PriceList, PriceListItem, Pack, PackItem, loyalty, PaymentReminder) and the V2 columns mentioned on the model pages arrive with the P4 migrations, not in V1.

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS unaccent;
-- immutable wrapper so unaccent() can be used in index expressions
CREATE OR REPLACE FUNCTION f_unaccent(text) RETURNS text
  LANGUAGE sql IMMUTABLE PARALLEL SAFE STRICT
  AS $$ SELECT public.unaccent('public.unaccent'::regdictionary, $1) $$;

Seed planCopy Anchor Link

Seeds are idempotent upserts on natural keys, split in two scripts: reference (every environment, re-runnable after each release) and demo (local and staging only, refused when the environment is production).

Setting keys seeded with their defaults (the values are tunable in BO-64; several are open decisions):

There is deliberately no automatic order confirmation setting (ORD-14) and no stock display setting (STK-07).

Indexing strategyCopy Anchor Link

Every foreign key used in a filter has an index; composite indexes follow the main queries. The model pages list the exact column lists; the important patterns:

Data volumes (planning assumptions)Copy Anchor Link

Assumption — orders of magnitude for sizing, to be replaced by HOCO's figures at the cadrage workshop (PRJ-03):

All of it fits one PostgreSQL instance with room to spare (tens of GB including indexes over three years); no table needs partitioning in V1. Files (photos, documents, PDFs) are in object storage, not in the database.

RetentionCopy Anchor Link

Proposals — the durations of personal data must be validated with HOCO (cahier §10, SEC-08, Law 18-07); they are settings where a job enforces them.

BackupsCopy Anchor Link

• Nightly logical dump (pg_dump -Fc) of production to object storage outside the VPS, encrypted, kept 7 daily, 4 weekly and 12 monthly copies (OPS-02, SEC-05).
• Point-in-time recovery with continuous WAL archiving (pgBackRest or WAL-G) is recommended once orders flow in production: RPO of minutes instead of 24 h — decision to confirm with HOCO.
• Object storage buckets are versioned and replicated to a second location; database rows and files are restored together.
• A restore test into a fresh database is part of hardening (SRV-28) and is repeated quarterly; procedure in deployment and operations.

All tablesCopy Anchor Link

Assumptions to reviewCopy Anchor Link

1. Better Auth e-mail stays NOT NULL: phone-only users get <id>@users.hoco.invalid, hidden by the API (verify in SRV-05).
2. Reseller.customerType is nullable for prospects and required for an approved account (CHECK); prices shown right after sign-up (catalog.pricesVisibleBeforeValidation = true).
3. Reseller.supportRegionId is a stored derivation of the wilaya, maintained by the crm and support modules.
4. Delegate profile key = user id, so every delegateId is a User.id.
5. Prices are stored HT; TTC is computed (VAT per line, half-up). If HOCO sets round TTC prices, the editor converts and the TTC may differ by one centime.
6. Stamp duty (Invoice.stampDuty) on cash-settled invoices — rates to confirm with HOCO's accountant.
7. Zero-total replacement orders issue no invoice.
8. Credit-note number format AVO-YYYY-NNNNNN and delegate code DLG-0007 are proposals.
9. 17 proposed enums (table) need French labels in enums.json.
10. Names aligned with the lifecycles page: assistedExpiresAt, pendingReasons, placedByUserId, trigger, inferred, unlockedUntil; that page's Tour.completedAt is closedAt here (orders are never auto-confirmed and returns are V1, per the 17 Oct decisions).