Identity and access

Logins and what they may do. A User is a login of one of three kinds (UserType: staff, delegate, reseller); the commercial record of a store is the Reseller and a delegate's field data is the DelegateProfile. Requirements: AUTH, ACL. Prisma file: packages/db/prisma/schema/identity.prisma.

Better Auth adaptation (SRV-05). User, Session, Account and Verification are Better Auth's core tables used through its Prisma adapter, with these choices:

Better Auth requires a non-null, unique email: phone-only users get a placeholder (see User.email). Assumption to verify in SRV-05 — if a later Better Auth release accepts phone-only users, the column becomes nullable through an expand migration.

Sessions, verification rows and devices are technical records: they are hard-deleted on expiry, revocation or unregistration (the audit trail keeps LOGIN/LOGOUT). Users are never deleted — DISABLED keeps history (SEC-06).

Accounts and Better Auth tablesCopy Anchor Link

UserCopy Anchor Link

A login: back-office staff, delegate or reseller. Holds identity and account status only — the commercial record of a point of sale is Reseller and the field profile is DelegateProfile. Table users · written by identity (Better Auth + admin user management).

• Relations: avatarFile → File (avatarFileId, onDelete: SetNull) · createdBy → User (createdById, onDelete: SetNull).
• Referenced by: User.createdById, Session.userId, Account.userId, RolePermission.grantedById, UserRole.userId, UserRole.assignedById, Device.userId, NotificationPreference.userId, Zone.createdById, DelegateProfile.id, DelegateZone.assignedById, Reseller.validatedById, Reseller.createdByUserId, Reseller.userId, ResellerDocument.reviewedById, ResellerDocument.uploadedById, ResellerPhoto.takenById, ResellerStatusChange.actorUserId and 67 more.
• Keys & indexes: email unique · phone unique · @@index([type, status]) · @@index([status, createdAt]).
• Enums: UserType, AccountStatus.
• Lifecycle: PENDING_VALIDATION → ACTIVE ⇄ SUSPENDED → DISABLED; transitions by admin.users.setStatus (staff, delegates) or reseller validation (admin.resellers.validate). Never deleted (SEC-06): DISABLED keeps every reference intact.
• History: Status changes, role changes and password resets are written to AuditLog (STATUS_CHANGE, PERMISSION_CHANGE).
• Better Auth is configured with advanced.database.generateId returning a UUIDv7, so its inserts satisfy @db.Uuid (verify in SRV-05). Only the credential provider and the phone-number plugin are used; the admin plugin is not (RBAC is our own).
• Account claim (AUTH-05): auth.signup.reseller with a verified phone equal to an existing Reseller.phone links that record (Reseller.userId) instead of creating a second point of sale.

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

ALTER TABLE users ADD CONSTRAINT users_phone_required
  CHECK (type = 'STAFF' OR phone IS NOT NULL);
ALTER TABLE users ADD CONSTRAINT users_locale_check
  CHECK (locale IN ('fr', 'ar', 'en'));

Example row:

{
  "id": "01a1a077-1080-7aed-87e0-11ae957ae11e",
  "type": "RESELLER",
  "status": "ACTIVE",
  "statusReason": null,
  "statusChangedAt": "2026-11-03T10:40:12.000Z",
  "name": "Amine Benali",
  "email": "01a1a077-1080-7aed-87e0-11ae957ae11e@users.hoco.invalid",
  "emailVerified": false,
  "image": null,
  "phone": "+213555123456",
  "phoneVerified": true,
  "locale": "fr",
  "avatarFileId": null,
  "mustChangePassword": false,
  "termsAcceptedAt": "2026-11-03T08:11:40.000Z",
  "termsVersion": "2026-11",
  "lastLoginAt": "2026-11-05T07:58:31.000Z",
  "createdById": null,
  "createdAt": "2026-11-03T08:12:00.000Z",
  "updatedAt": "2026-11-05T07:58:31.000Z"
}

SessionCopy Anchor Link

An authenticated session on one client (Better Auth core table plus three additional fields). Table sessions · written by identity (Better Auth).

• Relations: user → User (userId, onDelete: Cascade).
• Keys & indexes: token unique · @@index([userId]) · @@index([expiresAt]).
• Enums: ClientApp.
• Not a business record: rows are hard-deleted on sign-out, revocation (auth.sessions.revoke, user suspension) and by the nightly purge of expired sessions. The audit trail keeps LOGIN/LOGOUT.

Example row:

{
  "id": "01a1aab7-7058-7d0e-9739-d2a6fd5ec1fa",
  "token": "hoco_s_8Jd0q2Lx7cVb3nR5tY1wPz4kM6aE9fGh",
  "expiresAt": "2026-12-05T07:58:31.000Z",
  "userId": "01a1a077-1080-7aed-87e0-11ae957ae11e",
  "ipAddress": "105.235.131.20",
  "userAgent": "HOCO-Revendeur/1.0.0 (Android 14; SM-A145F)",
  "app": "RESELLER_APP",
  "deviceId": "a3f1c9e2-5b7d-4e10-9c3a-77d2e1f0b8c4",
  "appVersion": "1.0.0",
  "createdAt": "2026-11-05T07:58:31.000Z",
  "updatedAt": "2026-11-05T07:58:31.000Z"
}

AccountCopy Anchor Link

Better Auth credential record. V1 uses only the credential provider: one row per user holding the password hash. Table accounts · written by identity (Better Auth).

• Relations: user → User (userId, onDelete: Cascade).
• Keys & indexes: @@unique([providerId, accountId]) · @@index([userId]).

Example row:

{
  "id": "01a1a077-1080-7670-898a-e76d9386a515",
  "accountId": "01a1a077-1080-7aed-87e0-11ae957ae11e",
  "providerId": "credential",
  "userId": "01a1a077-1080-7aed-87e0-11ae957ae11e",
  "accessToken": null,
  "refreshToken": null,
  "idToken": null,
  "accessTokenExpiresAt": null,
  "refreshTokenExpiresAt": null,
  "scope": null,
  "password": "<scrypt hash — never exposed>",
  "createdAt": "2026-11-03T08:12:00.000Z",
  "updatedAt": "2026-11-03T08:12:00.000Z"
}

VerificationCopy Anchor Link

Short-lived secrets: OTP codes and the verification tokens returned by auth.otp.verify. Table verifications · written by identity (Better Auth + OTP service).

• Relations: none outgoing.
• Keys & indexes: @@index([identifier]) · @@index([expiresAt]).
• Enums: OtpPurpose.
• Rows are deleted when consumed, after 5 failed attempts (AUTH-07) and by the hourly purge of expired rows. Send-rate limits (1 per 60 s, 5 per hour per phone) live in Redis, not here.

Example row:

{
  "id": "01a1a070-cef0-761b-bef8-a54fac6f57e9",
  "identifier": "otp:SIGNUP:+213555123456",
  "value": "{\"h\":\"9b74c9897bac770ffc029102a200c5de\",\"a\":0}",
  "expiresAt": "2026-11-03T08:10:10.000Z",
  "createdAt": "2026-11-03T08:05:10.000Z",
  "updatedAt": "2026-11-03T08:05:10.000Z"
}

Roles and permissionsCopy Anchor Link

RoleCopy Anchor Link

One of the eight system roles of cahier §3. Labels come from RoleKey in enums.json. Table roles · written by access (seeded; permissions edited in BO-62).

• Relations: none outgoing.
• Referenced by: RolePermission.roleId, UserRole.roleId.
• Keys & indexes: key unique.
• Enums: RoleKey.
• Custom roles are out of V1 scope; adding them later means turning key into a String (expand migration).

Example row:

{
  "id": "01a15db0-5680-77fa-ae2b-171fb0ec90b2",
  "key": "COMMERCIAL_ADMIN",
  "isSystem": true,
  "description": "CRM, revendeurs, délégués, tournées, promotions, commandes et reporting.",
  "createdAt": "2026-10-21T09:00:00.000Z",
  "updatedAt": "2026-10-21T09:00:00.000Z"
}

PermissionCopy Anchor Link

A grantable <module>.<action> permission (ACL-02). Table permissions · written by access (seeded from the contract's permission catalogue).

• Relations: none outgoing.
• Referenced by: RolePermission.permissionId.
• Keys & indexes: key unique · @@unique([module, action]).
• Enums: PermissionAction.
• Only pairs used by at least one endpoint are seeded. Scopes self and delegate are query filters, not permissions.

Example row:

{
  "id": "01a15db0-5680-7e26-9e96-e375fbad6280",
  "key": "orders.validate",
  "module": "orders",
  "action": "VALIDATE",
  "description": "Valider ou refuser une commande, valider une commande assistée.",
  "isSensitive": true,
  "createdAt": "2026-10-21T09:00:00.000Z",
  "updatedAt": "2026-10-21T09:00:00.000Z"
}

RolePermissionCopy Anchor Link

Grants a permission to a role (the editable matrix of BO-62). Table role_permissions · written by access.

• Relations: role → Role (roleId, onDelete: Cascade) · permission → Permission (permissionId, onDelete: Cascade) · grantedBy → User (grantedById, onDelete: SetNull).
• Keys & indexes: @@id([roleId, permissionId]).
• Link row: admin.roles.updatePermissions replaces the set (delete + insert in one transaction); each change is an AuditLog PERMISSION_CHANGE with the before/after keys.

Example row:

{
  "roleId": "01a15db0-5680-77fa-ae2b-171fb0ec90b2",
  "permissionId": "01a15db0-5680-7e26-9e96-e375fbad6280",
  "grantedById": null,
  "createdAt": "2026-10-21T09:00:00.000Z"
}

UserRoleCopy Anchor Link

Assigns a role to a user. Staff may hold several roles; delegates hold exactly DELEGATE, resellers exactly RESELLER. Table user_roles · written by access (via admin.users.create/update).

• Relations: user → User (userId, onDelete: Cascade) · role → Role (roleId, onDelete: Restrict) · assignedBy → User (assignedById, onDelete: SetNull).
• Keys & indexes: @@id([userId, roleId]) · @@index([roleId]).
• The service rejects combinations that contradict User.type (a RESELLER user with a staff role, a staff user with RESELLER). Removal deletes the row and writes AuditLog PERMISSION_CHANGE.

Example row:

{
  "userId": "01a1a077-1080-7aed-87e0-11ae957ae11e",
  "roleId": "01a15cfa-7543-75d3-befa-bc785a1eb392",
  "assignedById": null,
  "createdAt": "2026-11-03T10:40:12.000Z"
}

Devices and preferencesCopy Anchor Link

DeviceCopy Anchor Link

A mobile installation able to receive push notifications (AUTH-09, NOT-02). Table devices · written by identity (me.devices.register).

• Relations: user → User (userId, onDelete: Cascade).
• Referenced by: NotificationDelivery.deviceId.
• Keys & indexes: fcmToken unique · @@unique([userId, deviceId]) · @@index([userId, lastSeenAt]).
• Enums: ClientApp, DevicePlatform (proposed).
• Technical record: me.devices.remove deletes the row; FCM UNREGISTERED errors null the token.

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

ALTER TABLE devices ADD CONSTRAINT devices_app_check
  CHECK (app IN ('RESELLER_APP', 'DELEGATE_APP'));

Example row:

{
  "id": "01a1aab7-9380-72b4-afca-8f9ed2d4913e",
  "userId": "01a1a077-1080-7aed-87e0-11ae957ae11e",
  "deviceId": "a3f1c9e2-5b7d-4e10-9c3a-77d2e1f0b8c4",
  "app": "RESELLER_APP",
  "platform": "ANDROID",
  "appVersion": "1.0.0",
  "osVersion": "Android 14",
  "model": "Samsung SM-A145F",
  "fcmToken": "eYx3…:APA91bH2…",
  "pushEnabled": true,
  "locale": "fr",
  "lastSeenAt": "2026-11-05T07:58:40.000Z",
  "createdAt": "2026-11-05T07:58:40.000Z",
  "updatedAt": "2026-11-05T07:58:40.000Z"
}

NotificationPreferenceCopy Anchor Link

A user's opt-out for one category on one channel (NOT-06), or — since smart notifications (SRV-39) — the push opt-out of one Notification Centre group. A missing row means the default from NotificationRule. Table notification_preferences · written by notifications (notifications.preferences.update).

• Relations: user → User (userId, onDelete: Cascade).
• Keys & indexes: @@unique([userId, category, channel]) (category rows; Postgres treats the null category of group rows as distinct) · @@unique([userId, group]) (one row per group).
• Enums: NotificationCategory, NotificationChannel, NotificationGroup.

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

ALTER TABLE notification_preferences ADD CONSTRAINT notification_preferences_mandatory
  CHECK (enabled OR category NOT IN ('TRANSACTIONAL', 'SECURITY'));
-- a row is either a category row or a group row; a group row mutes push only,
-- and URGENT cannot be muted (422 PREFERENCE_NOT_EDITABLE)
ALTER TABLE notification_preferences ADD CONSTRAINT notification_preferences_group_push
  CHECK ((("group" IS NULL) = (category IS NOT NULL))
    AND ("group" IS NULL OR (channel = 'PUSH' AND "group" <> 'URGENT')));

Example row:

{
  "id": "01a1b216-c880-76a8-9de9-534a5eae7aa7",
  "userId": "01a1a077-1080-7aed-87e0-11ae957ae11e",
  "category": "PROMOTIONAL",
  "channel": "PUSH",
  "enabled": false,
  "createdAt": "2026-11-06T18:20:00.000Z",
  "updatedAt": "2026-11-06T18:20:00.000Z"
}