Geography and zones

Wilaya and commune reference, commercial zones and their areas, delegate profiles and zone assignments.

The official territorial reference (wilayas and communes) used by every address, filter and zone (GEO-01), the commercial zones delegates cover (GEO-02), and the delegate profile itself (FLD-01). Prisma file: geography.prisma.

Key choices:

Wilaya keeps its official two-digit code as primary key ("16"), so foreign keys read naturally (wilayaCode) and the code never changes. Commune gets a surrogate UUID: official commune codes change when a commune moves to a new wilaya, as happened with the 2026 reorganisation.
Commune centroids are seeded for all communes: they resolve an anonymous device position to a wilaya and then to a support region for the WhatsApp button (SUP-05).
A zone is a set of whole wilayas and/or single communes; a commune belongs to at most one zone through its own row, and a wilaya to at most one zone as a whole — so the zone of any address is deterministic.
DelegateProfile shares its primary key with the delegate's User.id: every delegateId in the schema is a user id that is guaranteed to be a delegate.

Official reference

Wilaya

Official wilaya reference (GEO-01). The two-digit official code is the primary key. Table wilayas · written by geo (seed only; read by every app).

Field	Prisma type	Null	Default	Constraints / notes
`code`	`String @db.VarChar(2)`	—	—	PK. Official code `01`…`69`, zero-padded (69 wilayas since the 2026 territorial reorganisation — see the seed plan).
`nameFr`	`String`	—	—	—
`nameAr`	`String`	—	—	—
`centroidLat`	`Decimal @db.Decimal(9,6)`	yes	—	Map centring only.
`centroidLng`	`Decimal @db.Decimal(9,6)`	yes	—	Map centring only.
`isActive`	`Boolean`	—	`true`	—
`createdAt`	`DateTime`	—	`now()`	—
`updatedAt`	`DateTime`	—	`@updatedAt`	—

Relations: none outgoing.
Referenced by: Commune.wilayaCode, ZoneArea.wilayaCode, Reseller.wilayaCode, ResellerAddress.wilayaCode, Warehouse.wilayaCode, Order.wilayaCode, PromotionAudience.wilayaCode, SupportRegionWilaya.wilayaCode.
Keys & indexes: primary key only.
Served by reference.wilayas with an ETag computed from max(updatedAt). Never deleted; a reorganisation adds rows and moves communes (Commune.wilayaCode).

Example row:

{
  "code": "16",
  "nameFr": "Alger",
  "nameAr": "الجزائر",
  "centroidLat": 36.753768,
  "centroidLng": 3.058756,
  "isActive": true,
  "createdAt": "2026-10-21T09:00:00.000Z",
  "updatedAt": "2026-10-21T09:00:00.000Z"
}

Commune

Official commune reference (1,541 communes). Surrogate UUID key because official commune codes change when a commune moves to a new wilaya. Table communes · written by geo (seed only).

Field	Prisma type	Null	Default	Constraints / notes
`id`	`String @db.Uuid`	—	`uuid(7)`	PK.
`code`	`String @db.VarChar(4)`	—	—	Unique. Official code (wilaya code + 2-digit rank). Updated in place on reorganisation; never used as a foreign key.
`wilayaCode`	`String @db.VarChar(2)`	—	—	FK → Wilaya.code · `Restrict`.
`nameFr`	`String`	—	—	—
`nameAr`	`String`	—	—	—
`postalCode`	`String @db.VarChar(5)`	yes	—	—
`centroidLat`	`Decimal @db.Decimal(9,6)`	yes	—	Commune centroid (seeded for all 1,541 communes): resolves a device position to the nearest commune → wilaya → support region (SUP-05), zone map hints, distance fallbacks.
`centroidLng`	`Decimal @db.Decimal(9,6)`	yes	—	WGS 84 longitude, degrees.
`isActive`	`Boolean`	—	`true`	—
`createdAt`	`DateTime`	—	`now()`	—
`updatedAt`	`DateTime`	—	`@updatedAt`	—

Relations: wilaya → Wilaya (wilayaCode, onDelete: Restrict).
Referenced by: ZoneArea.communeId, DelegateProfile.homeBaseCommuneId, Reseller.communeId, ResellerAddress.communeId, Warehouse.communeId, Order.communeId.
Keys & indexes: code unique · @@index([wilayaCode, nameFr]).
Nearest commune for an anonymous device position (support.whatsapp with lat/lng): the 1,541 centroids are cached in memory by the geo module and searched by haversine distance; no spatial extension needed.
Every table that stores both wilayaCode and communeId (Reseller, ResellerAddress, Warehouse, Order snapshots) requires commune.wilayaCode = wilayaCode; enforced by the geo helper assertCommuneInWilaya on write.

Example row:

{
  "id": "01a15db0-5680-7730-9115-ea5cf224e9ae",
  "code": "1601",
  "wilayaCode": "16",
  "nameFr": "Alger Centre",
  "nameAr": "الجزائر الوسطى",
  "postalCode": "16000",
  "centroidLat": 36.773105,
  "centroidLng": 3.058821,
  "isActive": true,
  "createdAt": "2026-10-21T09:00:00.000Z",
  "updatedAt": "2026-10-21T09:00:00.000Z"
}

Commercial zones

Zone

A commercial territory grouping wilayas and/or communes, assigned to one or more delegates (GEO-02). Table zones · written by geo (admin.zones.crud, BO-19).

Field	Prisma type	Null	Default	Constraints / notes
`id`	`String @db.Uuid`	—	`uuid(7)`	PK.
`code`	`String @db.VarChar(20)`	—	—	Unique. Short stable code shown in filters.
`name`	`String`	—	—	—
`description`	`String`	yes	—	—
`color`	`String @db.VarChar(7)`	yes	—	Hex colour on maps (`#RRGGBB`).
`archivedAt`	`DateTime`	yes	—	Archived zones keep their history but lose their areas (see ZoneArea).
`createdById`	`String @db.Uuid`	yes	—	FK → User · `SetNull`.
`createdAt`	`DateTime`	—	`now()`	—
`updatedAt`	`DateTime`	—	`@updatedAt`	—

Relations: createdBy → User (createdById, onDelete: SetNull).
Referenced by: ZoneArea.zoneId, DelegateZone.zoneId, Reseller.zoneId, ResellerAssignment.fromZoneId, ResellerAssignment.toZoneId, Order.zoneId.
Keys & indexes: code unique.
Derived, not stored: geometry: V1 zones have no polygon; delegate.zone returns commune centroids and counts computed from ZoneArea.

Example row:

{
  "id": "01a17280-b500-7f65-a38a-52982a7faa5e",
  "code": "ALG-CE",
  "name": "Alger Centre-Est",
  "description": "Alger Centre, Bab El Oued, Hussein Dey et communes de l'Est algérois.",
  "color": "#1d4ed8",
  "archivedAt": null,
  "createdById": "01a1a00a-4535-7ff7-af24-a0a3bb095b70",
  "createdAt": "2026-10-25T10:00:00.000Z",
  "updatedAt": "2026-10-25T10:00:00.000Z"
}

ZoneArea

One membership of a zone: either a whole wilaya or a single commune. Table zone_areas · written by geo.

Field	Prisma type	Null	Default	Constraints / notes
`id`	`String @db.Uuid`	—	`uuid(7)`	PK.
`zoneId`	`String @db.Uuid`	—	—	FK → Zone · `Cascade`.
`wilayaCode`	`String @db.VarChar(2)`	yes	—	Unique. FK → Wilaya.code · `Restrict`. Whole-wilaya membership. Unique: a wilaya belongs to at most one zone as a whole.
`communeId`	`String @db.Uuid`	yes	—	Unique. FK → Commune · `Restrict`. Single-commune membership. Unique: a commune belongs to at most one zone.
`createdAt`	`DateTime`	—	`now()`	—

Relations: zone → Zone (zoneId, onDelete: Cascade) · wilaya → Wilaya (wilayaCode, onDelete: Restrict) · commune → Commune (communeId, onDelete: Restrict).
Keys & indexes: wilayaCode unique · communeId unique.
Zone resolution for a commune: the zone holding the commune itself, else the zone holding its wilaya, else none. Used to pre-fill Reseller.zoneId at creation; the stored zoneId remains the reference (it can be overridden by hand).
Link rows: replaced on edit, deleted when the zone is archived (each change in AuditLog), so the uniqueness rules only see live zones.

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

ALTER TABLE zone_areas ADD CONSTRAINT zone_areas_exactly_one
  CHECK (num_nonnulls(wilaya_code, commune_id) = 1);

Example row:

{
  "id": "01a17285-48e0-7d2c-83a6-ee746fa82d61",
  "zoneId": "01a17280-b500-7f65-a38a-52982a7faa5e",
  "wilayaCode": null,
  "communeId": "01a15db0-5680-7730-9115-ea5cf224e9ae",
  "createdAt": "2026-10-25T10:05:00.000Z"
}

Delegates

DelegateProfile

Field profile of a delegate user (FLD-01). Its primary key is the user id, so every delegateId in the schema is a User.id that is guaranteed to have this profile. Table delegate_profiles · written by field (admin.delegates.crud, BO-16…18).

Field	Prisma type	Null	Default	Constraints / notes
`id`	`String @db.Uuid`	—	—	FK → User · `Restrict`. PK and FK: equals the delegate's `User.id` (1:1). No default.
`code`	`String @db.VarChar(10)`	—	—	Unique. `DLG-0007` from NumberSequence `DELEGATE` (not yearly).
`phone`	`String @db.VarChar(16)`	yes	—	E.164 contact number shown to resellers and in invitation texts; defaults to the sign-in phone.
`status`	`DelegateStatus`	—	`ACTIVE`	—
`homeBaseLabel`	`String`	yes	—	Starting point of the recommended visit order (FLD-17).
`homeBaseLat`	`Decimal @db.Decimal(9,6)`	yes	—	WGS 84 latitude, degrees.
`homeBaseLng`	`Decimal @db.Decimal(9,6)`	yes	—	WGS 84 longitude, degrees.
`homeBaseCommuneId`	`String @db.Uuid`	yes	—	FK → Commune · `Restrict`.
`locationConsentAt`	`DateTime`	yes	—	When the delegate accepted the location policy in the app (SEC-07, DA-04). A tour cannot start while `null`.
`locationConsentVersion`	`String @db.VarChar(20)`	yes	—	Version of the policy text accepted; a new version requires a new consent.
`note`	`String`	yes	—	Internal back-office note.
`managerId`	`String @db.Uuid`	yes	—	FK → User · `SetNull`. The staff supervisor (`admin.delegates.crud` `managerId` → `manager`; `delegate.profile` → `supervisor`) — SRV-42, backfilled from the audit trail of the delegate edits.
`createdAt`	`DateTime`	—	`now()`	—
`updatedAt`	`DateTime`	—	`@updatedAt`	—

Relations: user → User (id, onDelete: Restrict) · homeBaseCommune → Commune (homeBaseCommuneId, onDelete: Restrict) · manager → User (managerId, onDelete: SetNull).
Referenced by: DelegateZone.delegateId, Reseller.assignedDelegateId, ResellerAssignment.fromDelegateId, ResellerAssignment.toDelegateId, Order.sourceDelegateId, Order.attributedDelegateId, Objective.delegateId, Tour.delegateId, Visit.delegateId, LocationPing.delegateId, Invitation.delegateId.
Keys & indexes: code unique · @@index([status]) · @@index([managerId]).
Enums: DelegateStatus (proposed).
Derived, not stored: last known position: Redis key delegate:lastpos:<id> written by sync.locationPings, falling back to the latest LocationPing — not a column, to avoid a write on the profile every 5 minutes.

Example row:

{
  "id": "01a1a024-7d34-7424-adb6-099ecaff5095",
  "code": "DLG-0007",
  "phone": "+213661234567",
  "status": "ACTIVE",
  "homeBaseLabel": "Dépôt HOCO — Bab Ezzouar",
  "homeBaseLat": 36.713611,
  "homeBaseLng": 3.183056,
  "homeBaseCommuneId": null,
  "locationConsentAt": "2026-11-02T07:30:00.000Z",
  "locationConsentVersion": "2026-11",
  "note": null,
  "createdAt": "2026-10-26T09:30:00.000Z",
  "updatedAt": "2026-11-02T07:30:00.000Z"
}

DelegateZone

Assigns a zone to a delegate (many-to-many: several delegates may share a zone). Table delegate_zones · written by field.

Field	Prisma type	Null	Default	Constraints / notes
`delegateId`	`String @db.Uuid`	—	—	PK. FK → DelegateProfile · `Cascade`. Part of the composite PK.
`zoneId`	`String @db.Uuid`	—	—	PK. FK → Zone · `Cascade`. Part of the composite PK.
`assignedById`	`String @db.Uuid`	yes	—	FK → User · `SetNull`.
`createdAt`	`DateTime`	—	`now()`	—

Relations: delegate → DelegateProfile (delegateId, onDelete: Cascade) · zone → Zone (zoneId, onDelete: Cascade) · assignedBy → User (assignedById, onDelete: SetNull).
Keys & indexes: @@id([delegateId, zoneId]) · @@index([zoneId]).
Delegate scope (ACL-03) = resellers with assignedDelegateId = me or zoneId ∈ my DelegateZone.zoneId, plus my tours, visits, tasks and invitations. Implemented once as a reusable Prisma where fragment in access.
Link row: unassignment deletes it (AuditLog UPDATE on the delegate). The delegate app recomputes its cached scope from delegate.profile after a zone change.

Example row:

{
  "delegateId": "01a1a024-7d34-7424-adb6-099ecaff5095",
  "zoneId": "01a17280-b500-7f65-a38a-52982a7faa5e",
  "assignedById": "01a1a00a-4535-7ff7-af24-a0a3bb095b70",
  "createdAt": "2026-10-26T09:35:00.000Z"
}

Geography and zones

Official referenceCopy Anchor Link

WilayaCopy Anchor Link

CommuneCopy Anchor Link

Commercial zonesCopy Anchor Link

ZoneCopy Anchor Link

ZoneAreaCopy Anchor Link

DelegatesCopy Anchor Link

DelegateProfileCopy Anchor Link

DelegateZoneCopy Anchor Link

On this page

Official reference

Wilaya

Commune

Commercial zones

Zone

ZoneArea

Delegates

DelegateProfile

DelegateZone