Construire le schéma Prisma qui deviendra la source de vérité unique du système.

À partir de ce schéma seront générés :

• PostgreSQL
• Prisma Client
• DTO
• Entités NestJS
• OpenAPI
• SDK TypeScript
• Migrations SQL

Le schéma doit refléter l'intégralité des domaines définis dans les 20 sprints.

Le schéma Prisma doit supporter :

Multi-tenant

Audit

Historisation

Soft Delete

RBAC

CRM

Catalogue

Réservations

Paiements

Contrats

OTA

IA

Revenue Management

Internationalisation

Sécurité Enterprise

Ne jamais commencer par les 80 tables.

Construire par couches.

Construire en premier :

Tenant

User

Role

Permission

UserRole

RolePermission

Ces tables sont nécessaires partout.

Créer :

Address

Country

Language

Currency

Timezone

Créer :

Property

PropertyType

PropertyMedia

PropertyFeature

PropertyAvailability

PropertyRate

Créer :

Reservation

ReservationGuest

ReservationStatus

ReservationEvent

Créer :

Contract

ContractTemplate

ContractSignature

Document

Créer :

Payment

Invoice

Refund

AccountingEntry

Créer :

Lead

Customer

Activity

Task

Pipeline

Créer :

Owner

OwnerProperty

OwnerDocument

Créer :

Notification

NotificationTemplate

Message

Campaign

Créer :

AuditLog

FeatureFlag

CustomField

Workflow

Créer :

AiConversation

AiMessage

KnowledgeDocument

AutomationRule

Créer :

Channel

ChannelConnection

PropertyDistribution

ChannelReservation

Créer :

PricingRule

DynamicPrice

RevenueSimulation

CompetitorSnapshot

Créer :

Consent

SecurityPolicy

Risk

ComplianceAudit

Créer :

Translation

CurrencyRate

Region

EnterpriseLicense

Au lieu d'un unique fichier géant :

prisma/

├── schema.prisma
│
├── models/
│
│   ├── tenant.prisma
│   ├── user.prisma
│   ├── property.prisma
│   ├── reservation.prisma
│   ├── contract.prisma
│   ├── payment.prisma
│   ├── crm.prisma
│   ├── owner.prisma
│   ├── notification.prisma
│   ├── audit.prisma
│   ├── ai.prisma
│   ├── channel.prisma
│   ├── revenue.prisma
│   ├── security.prisma
│   └── localization.prisma

Version minimale :

generator client {
  provider = "prisma-client-js"
}
 
datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

Créer :

prisma/models/tenant.prisma

model Tenant {
 
  id            String   @id @default(uuid())
 
  code          String   @unique
 
  name          String
 
  status        TenantStatus
 
  createdAt     DateTime @default(now())
 
  updatedAt     DateTime @updatedAt
 
  users         User[]
 
  properties    Property[]
 
}

enum TenantStatus {
 
  ACTIVE
 
  SUSPENDED
 
  TRIAL
 
  ARCHIVED
}

Créer :

prisma/models/user.prisma

model User {
 
  id              String @id @default(uuid())
 
  tenantId        String
 
  email           String @unique
 
  passwordHash    String
 
  firstName       String
 
  lastName        String
 
  active          Boolean @default(true)
 
  createdAt       DateTime @default(now())
 
  updatedAt       DateTime @updatedAt
 
  tenant          Tenant @relation(
                    fields:[tenantId],
                    references:[id]
                  )
 
  roles           UserRole[]
 
}

model Role {
 
  id            String @id @default(uuid())
 
  code          String @unique
 
  name          String
 
  userRoles     UserRole[]
 
  permissions   RolePermission[]
}

model Permission {
 
  id            String @id @default(uuid())
 
  code          String @unique
 
  name          String
 
  rolePermissions RolePermission[]
}

model UserRole {
 
  userId        String
 
  roleId        String
 
  assignedAt    DateTime @default(now())
 
  user          User @relation(
                  fields:[userId],
                  references:[id]
                )
 
  role          Role @relation(
                  fields:[roleId],
                  references:[id]
                )
 
  @@id([userId, roleId])
}

model RolePermission {
 
  roleId        String
 
  permissionId  String
 
  role          Role @relation(
                  fields:[roleId],
                  references:[id]
                )
 
  permission    Permission @relation(
                  fields:[permissionId],
                  references:[id]
                )
 
  @@id([roleId, permissionId])
}

Après chaque bloc important :

npx prisma format

npx prisma validate

npx prisma generate

npx prisma migrate dev \
--name init_security

Ouvrir :

npx prisma studio

Vérifier la présence :

Tenant

User

Role

Permission

UserRole

RolePermission

Vous avez raison.

La version précédente contient :

• Tenant
• User
• Role
• Permission
• UserRole
• RolePermission

mais il manque plusieurs éléments indispensables pour un véritable système Enterprise :

• propriétés du compte utilisateur
• préférences utilisateur
• sessions
• refresh tokens
• MFA
• audit de connexion
• catalogue complet des permissions

Sans ces éléments, le Sprint 1 (Authentification) et le Sprint 19 (Sécurité Enterprise) ne pourront pas être implémentés proprement.

model User {
 
  id                String      @id @default(uuid())
 
  tenantId          String
 
  email             String      @unique
 
  passwordHash      String
 
  firstName         String
 
  lastName          String
 
  phone             String?
 
  avatarUrl         String?
 
  languageCode      String?
 
  timezoneCode      String?
 
  active            Boolean     @default(true)
 
  emailVerified     Boolean     @default(false)
 
  lastLoginAt       DateTime?
 
  createdAt         DateTime    @default(now())
 
  updatedAt         DateTime    @updatedAt
 
  deletedAt         DateTime?
 
  tenant            Tenant      @relation(
                                  fields:[tenantId],
                                  references:[id]
                                )
 
  roles             UserRole[]
 
  sessions          UserSession[]
 
  refreshTokens     RefreshToken[]
 
  preferences       UserPreference?
 
  loginHistory      LoginHistory[]
 
  mfaMethods        UserMfaMethod[]
}

model UserPreference {
 
  id                String      @id @default(uuid())
 
  userId            String      @unique
 
  theme             String?
 
  language          String?
 
  timezone          String?
 
  notifications     Json?
 
  createdAt         DateTime    @default(now())
 
  updatedAt         DateTime    @updatedAt
 
  user              User @relation(
                        fields:[userId],
                        references:[id]
                      )
}

model UserSession {
 
  id                String      @id @default(uuid())
 
  userId            String
 
  ipAddress         String?
 
  userAgent         String?
 
  country           String?
 
  city              String?
 
  lastActivityAt    DateTime
 
  expiresAt         DateTime
 
  revokedAt         DateTime?
 
  createdAt         DateTime    @default(now())
 
  user              User @relation(
                        fields:[userId],
                        references:[id]
                      )
}

model RefreshToken {
 
  id                String      @id @default(uuid())
 
  userId            String
 
  tokenHash         String
 
  expiresAt         DateTime
 
  revokedAt         DateTime?
 
  createdAt         DateTime    @default(now())
 
  user              User @relation(
                        fields:[userId],
                        references:[id]
                      )
}

model LoginHistory {
 
  id                String      @id @default(uuid())
 
  userId            String
 
  success           Boolean
 
  ipAddress         String?
 
  userAgent         String?
 
  country           String?
 
  city              String?
 
  createdAt         DateTime @default(now())
 
  user              User @relation(
                        fields:[userId],
                        references:[id]
                      )
}

model UserMfaMethod {
 
  id                String      @id @default(uuid())
 
  userId            String
 
  methodType        String
 
  secret            String?
 
  enabled           Boolean     @default(true)
 
  createdAt         DateTime    @default(now())
 
  user              User @relation(
                        fields:[userId],
                        references:[id]
                      )
}

model Role {
 
  id                String      @id @default(uuid())
 
  code              String      @unique
 
  name              String
 
  description       String?
 
  systemRole        Boolean     @default(false)
 
  createdAt         DateTime    @default(now())
 
  updatedAt         DateTime    @updatedAt
 
  userRoles         UserRole[]
 
  permissions       RolePermission[]
}

model Permission {
 
  id                String      @id @default(uuid())
 
  code              String      @unique
 
  name              String
 
  description       String?
 
  module            String
 
  createdAt         DateTime    @default(now())
 
  rolePermissions   RolePermission[]
}

AUTH_LOGIN

AUTH_REGISTER

AUTH_RESET_PASSWORD

AUTH_MANAGE_USERS

USER_READ

USER_CREATE

USER_UPDATE

USER_DELETE

PROPERTY_READ

PROPERTY_CREATE

PROPERTY_UPDATE

PROPERTY_DELETE

PROPERTY_PUBLISH

RESERVATION_READ

RESERVATION_CREATE

RESERVATION_UPDATE

RESERVATION_CANCEL

CONTRACT_READ

CONTRACT_CREATE

CONTRACT_SIGN

PAYMENT_READ

PAYMENT_CREATE

PAYMENT_REFUND

CRM_READ

CRM_WRITE

CRM_EXPORT

ADMIN_READ

ADMIN_WRITE

ADMIN_AUDIT

ADMIN_TENANT

Accès complet plateforme.

Administration d'agence.

Gestion opérationnelle.

Extranet propriétaire.

Collaborateur.

Client final.

@@index([tenantId])
 
@@index([email])
 
@@index([active])

@@index([userId])
 
@@index([createdAt])

@@index([userId])
 
@@index([expiresAt])

Property

PropertyType

PropertyStatus

PropertyAddress

PropertyFeature

PropertyMedia

PropertyAvailability

PropertyRate

PropertyOwner

model Property {
 
  id                    String @id @default(uuid())
 
  tenantId              String
 
  propertyTypeId        String
 
  code                  String @unique
 
  reference             String?
 
  title                 String
 
  slug                  String @unique
 
  description           String?
 
  shortDescription      String?
 
  maxGuests             Int
 
  bedrooms              Int
 
  bathrooms             Int
 
  area                  Decimal? @db.Decimal(10,2)
 
  floor                 Int?
 
  constructionYear      Int?
 
  checkInTime           String?
 
  checkOutTime          String?
 
  active                Boolean @default(true)
 
  published             Boolean @default(false)
 
  createdAt             DateTime @default(now())
 
  updatedAt             DateTime @updatedAt
 
  deletedAt             DateTime?
 
  tenant                Tenant @relation(
                            fields:[tenantId],
                            references:[id]
                         )
 
  propertyType          PropertyType @relation(
                            fields:[propertyTypeId],
                            references:[id]
                         )
 
  address               PropertyAddress?
 
  features              PropertyFeature[]
 
  media                 PropertyMedia[]
 
  availabilities        PropertyAvailability[]
 
  rates                 PropertyRate[]
 
  owners                PropertyOwner[]
 
  reservations         Reservation[]
 
}

model PropertyType {
 
  id                String @id @default(uuid())
 
  code              String @unique
 
  name              String
 
  properties        Property[]
}

HOUSE

APARTMENT

VILLA

STUDIO

LOFT

CHALET

COTTAGE

MOBILE_HOME

model PropertyAddress {
 
  id                String @id @default(uuid())
 
  propertyId        String @unique
 
  addressLine1      String
 
  addressLine2      String?
 
  postalCode        String
 
  city              String
 
  state             String?
 
  countryCode       String
 
  latitude          Decimal? @db.Decimal(10,7)
 
  longitude         Decimal? @db.Decimal(10,7)
 
  property          Property @relation(
                      fields:[propertyId],
                      references:[id]
                    )
}

model PropertyFeature {
 
  id                String @id @default(uuid())
 
  propertyId        String
 
  featureCode       String
 
  featureValue      String?
 
  property          Property @relation(
                      fields:[propertyId],
                      references:[id]
                    )
}

POOL

WIFI

PARKING

AIR_CONDITIONING

TERRACE

SEA_VIEW

PET_ALLOWED

model PropertyMedia {
 
  id                String @id @default(uuid())
 
  propertyId        String
 
  fileName          String
 
  fileUrl           String
 
  mediaType         String
 
  position          Int
 
  isCover           Boolean @default(false)
 
  createdAt         DateTime @default(now())
 
  property          Property @relation(
                      fields:[propertyId],
                      references:[id]
                    )
}

IMAGE

VIDEO

VIRTUAL_TOUR

DOCUMENT

model PropertyAvailability {
 
  id                String @id @default(uuid())
 
  propertyId        String
 
  startDate         DateTime
 
  endDate           DateTime
 
  status            AvailabilityStatus
 
  property          Property @relation(
                      fields:[propertyId],
                      references:[id]
                    )
}

enum AvailabilityStatus {
 
  AVAILABLE
 
  RESERVED
 
  BLOCKED
 
  MAINTENANCE
}

model PropertyRate {
 
  id                String @id @default(uuid())
 
  propertyId        String
 
  startDate         DateTime
 
  endDate           DateTime
 
  nightlyRate       Decimal @db.Decimal(10,2)
 
  weekendRate       Decimal? @db.Decimal(10,2)
 
  cleaningFee       Decimal? @db.Decimal(10,2)
 
  securityDeposit   Decimal? @db.Decimal(10,2)
 
  currencyCode      String
 
  property          Property @relation(
                      fields:[propertyId],
                      references:[id]
                    )
}

model PropertyOwner {
 
  propertyId        String
 
  ownerId           String
 
  ownershipRate     Decimal @db.Decimal(5,2)
 
  property          Property @relation(
                      fields:[propertyId],
                      references:[id]
                    )
 
  owner             Owner @relation(
                      fields:[ownerId],
                      references:[id]
                    )
 
  @@id([propertyId, ownerId])
}

@@index([tenantId])
 
@@index([propertyTypeId])
 
@@index([published])
 
@@index([active])
 
@@index([title])
 
@@index([slug])

@@index([propertyId])
 
@@index([startDate])
 
@@index([endDate])

@@index([propertyId])
 
@@index([startDate])
 
@@index([endDate])

Owner

OwnerAddress

OwnerDocument

OwnerBankAccount

PropertyOwner

model Owner {
 
  id                    String @id @default(uuid())
 
  tenantId              String
 
  code                  String @unique
 
  companyName           String?
 
  firstName             String?
 
  lastName              String?
 
  email                 String
 
  phone                 String?
 
  mobile                String?
 
  taxIdentifier         String?
 
  vatNumber             String?
 
  active                Boolean @default(true)
 
  createdAt             DateTime @default(now())
 
  updatedAt             DateTime @updatedAt
 
  deletedAt             DateTime?
 
  tenant                Tenant @relation(
                            fields:[tenantId],
                            references:[id]
                         )
 
  address               OwnerAddress?
 
  documents             OwnerDocument[]
 
  bankAccounts          OwnerBankAccount[]
 
  properties            PropertyOwner[]
}

model OwnerAddress {
 
  id                    String @id @default(uuid())
 
  ownerId               String @unique
 
  addressLine1          String
 
  addressLine2          String?
 
  postalCode            String
 
  city                  String
 
  state                 String?
 
  countryCode           String
 
  owner                 Owner @relation(
                            fields:[ownerId],
                            references:[id]
                         )
}

model OwnerDocument {
 
  id                    String @id @default(uuid())
 
  ownerId               String
 
  documentType          String
 
  fileUrl               String
 
  createdAt             DateTime @default(now())
 
  owner                 Owner @relation(
                            fields:[ownerId],
                            references:[id]
                         )
}

IDENTITY

TAX_DOCUMENT

MANDATE

BANK_DETAILS

INSURANCE

model OwnerBankAccount {
 
  id                    String @id @default(uuid())
 
  ownerId               String
 
  iban                  String
 
  bic                   String?
 
  accountHolder         String
 
  active                Boolean @default(true)
 
  owner                 Owner @relation(
                            fields:[ownerId],
                            references:[id]
                         )
}

Reservation

ReservationGuest

ReservationStatusHistory

ReservationEvent

ReservationPricing

model Reservation {
 
  id                    String @id @default(uuid())
 
  tenantId              String
 
  propertyId            String
 
  customerId            String?
 
  reference             String @unique
 
  status                ReservationStatus
 
  checkInDate           DateTime
 
  checkOutDate          DateTime
 
  nights                Int
 
  adults                Int
 
  children              Int
 
  infants               Int
 
  totalGuests           Int
 
  notes                 String?
 
  source                ReservationSource
 
  createdAt             DateTime @default(now())
 
  updatedAt             DateTime @updatedAt
 
  cancelledAt           DateTime?
 
  tenant                Tenant @relation(
                            fields:[tenantId],
                            references:[id]
                         )
 
  property              Property @relation(
                            fields:[propertyId],
                            references:[id]
                         )
 
  guests                ReservationGuest[]
 
  events                ReservationEvent[]
 
  statusHistory         ReservationStatusHistory[]
 
  pricing               ReservationPricing?
}

enum ReservationStatus {
 
  DRAFT
 
  PENDING
 
  CONFIRMED
 
  SIGNED
 
  PAID
 
  CHECKED_IN
 
  COMPLETED
 
  CANCELLED
}

enum ReservationSource {
 
  WEBSITE
 
  BACKOFFICE
 
  AIRBNB
 
  BOOKING
 
  VRBO
 
  API
}

model ReservationGuest {
 
  id                    String @id @default(uuid())
 
  reservationId         String
 
  firstName             String
 
  lastName              String
 
  birthDate             DateTime?
 
  email                 String?
 
  phone                 String?
 
  isPrimary             Boolean @default(false)
 
  reservation           Reservation @relation(
                            fields:[reservationId],
                            references:[id]
                         )
}

model ReservationPricing {
 
  id                    String @id @default(uuid())
 
  reservationId         String @unique
 
  nightlyAmount         Decimal @db.Decimal(10,2)
 
  cleaningFee           Decimal @db.Decimal(10,2)
 
  touristTax            Decimal @db.Decimal(10,2)
 
  discountAmount        Decimal @db.Decimal(10,2)
 
  totalAmount           Decimal @db.Decimal(10,2)
 
  currencyCode          String
 
  reservation           Reservation @relation(
                            fields:[reservationId],
                            references:[id]
                         )
}

model ReservationEvent {
 
  id                    String @id @default(uuid())
 
  reservationId         String
 
  eventType             String
 
  payload               Json?
 
  createdAt             DateTime @default(now())
 
  reservation           Reservation @relation(
                            fields:[reservationId],
                            references:[id]
                         )
}

CREATED

CONFIRMED

SIGNED

PAID

CHECK_IN

CHECK_OUT

CANCELLED

model ReservationStatusHistory {
 
  id                    String @id @default(uuid())
 
  reservationId         String
 
  previousStatus        ReservationStatus?
 
  newStatus             ReservationStatus
 
  changedAt             DateTime @default(now())
 
  reservation           Reservation @relation(
                            fields:[reservationId],
                            references:[id]
                         )
}

Ajouter :

reservations Reservation[]

Ajouter :

owners Owner[]
 
reservations Reservation[]

@@index([tenantId])
 
@@index([email])
 
@@index([active])

@@index([tenantId])
 
@@index([propertyId])
 
@@index([status])
 
@@index([checkInDate])
 
@@index([checkOutDate])
 
@@index([reference])

@@index([reservationId])

npx prisma format
 
npx prisma validate
 
npx prisma generate

npx prisma migrate dev \
--name owners_and_reservations

À ce stade, le noyau fonctionnel est enfin présent :

Tenant

User

Role

Permission

Owner

Property

Reservation

avec :

Catalogue Immobilier

Propriétaires

Réservations

RBAC

Multi-tenant

Domaines financiers et contractuels :

Contract

ContractTemplate

ContractSignature

Document

Payment

Invoice

Refund

AccountingEntry

Ces tables permettront d'implémenter intégralement :

• Sprint 5 — Contrats & Signature Électronique
• Sprint 6 — Paiements & Facturation

et de disposer du premier flux métier complet :

Property

↓

Reservation

↓

Contract

↓

Payment

↓

Invoice