Schema de Base de Donnees

Documentation complete du schema de base de donnees MyTelevision API utilisant Prisma ORM avec PostgreSQL 16.

Vue d'ensemble

Stack base de donnees

Composant	Technologie
Base de donnees	PostgreSQL 16
ORM	Prisma 6.19
Full-Text Search	PostgreSQL natif
Connection Pooling	PgBouncer (production)
Nombre de modeles	73

Emplacement du schema

prisma/
├── schema.prisma     # Definition du schema (73 modeles)
├── migrations/       # Historique des migrations
├── seed.ts           # Seeding de la base
└── seeds/            # Fichiers de donnees de seed

Caracteristiques cles

Cles primaires UUID sur toutes les entites
Soft deletes ou applicable (deletedAt)
Timestamps d'audit (createdAt, updatedAt)
Full-text search sur le contenu
Index optimises pour les requetes frequentes
Support bilingue : champs title/titleEn, overview/overviewEn

Diagramme Entite-Relation

Vue globale

Systeme multi-tenant

Systeme de contenu

Entites Core

User (Legacy)

model User {
  id                  String        @id @default(uuid())
  email               String        @unique
  password            String?
  firstName           String?
  lastName            String?
  avatar              String?
  role                UserRole      @default(USER)
  isActive            Boolean       @default(true)
  isVerified          Boolean       @default(false)
  firebaseUid         String?       @unique
  socialAccounts      SocialAccount[]
  createdAt           DateTime      @default(now())
  updatedAt           DateTime      @updatedAt
}

enum UserRole {
  USER
  ADMIN
  SUPER_ADMIN
}

info

Modele legacy. Les nouvelles implementations utilisent le systeme Account/Profile.

SocialAccount

model SocialAccount {
  id             String              @id @default(uuid())
  userId         String
  user           User                @relation(fields: [userId], references: [id], onDelete: Cascade)
  provider       SocialAuthProvider
  providerUserId String
  email          String?
  displayName    String?
  photoUrl       String?
  createdAt      DateTime            @default(now())
  updatedAt      DateTime            @updatedAt

  @@unique([provider, providerUserId])
  @@index([userId])
}

enum SocialAuthProvider {
  GOOGLE
  APPLE
  FACEBOOK
  TWITTER
  GITHUB
}

Relie les fournisseurs d'identite externes (Firebase) aux utilisateurs internes.

Systeme Account (Multi-Tenant)

Tenant

model Tenant {
  id                    String        @id @default(uuid())
  name                  String
  slug                  String        @unique
  domain                String?       @unique
  logoUrl               String?
  primaryColor          String?
  secondaryColor        String?
  status                TenantStatus  @default(ACTIVE)
  maxProfilesPerAccount Int           @default(4)
  maxDevicesPerAccount  Int           @default(5)
  maxConcurrentSessions Int           @default(4)
  accounts              Account[]
  createdAt             DateTime      @default(now())
  updatedAt             DateTime      @updatedAt
}

enum TenantStatus {
  ACTIVE
  SUSPENDED
  TRIAL
}

Account

model Account {
  id                  String          @id @default(uuid())
  tenantId            String
  tenant              Tenant          @relation(fields: [tenantId], references: [id])
  email               String
  password            String
  firstName           String?
  lastName            String?
  status              AccountStatus   @default(ACTIVE)
  deletionScheduledAt DateTime?
  profiles            Profile[]
  devices             AccountDevice[]
  createdAt           DateTime        @default(now())
  updatedAt           DateTime        @updatedAt
  deletedAt           DateTime?

  @@unique([tenantId, email])
  @@index([status])
}

enum AccountStatus {
  ACTIVE
  SUSPENDED
  LOCKED
  PENDING_VERIFICATION
  PENDING_DELETION
}

Profile

model Profile {
  id             String              @id @default(uuid())
  accountId      String
  account        Account             @relation(fields: [accountId], references: [id])
  name           String
  avatar         String?
  type           ProfileType         @default(STANDARD)
  status         ProfileStatus       @default(ACTIVE)
  pin            String?
  pinAttempts    Int                 @default(0)
  pinLockedUntil DateTime?
  isDefault      Boolean             @default(false)
  restrictions   ProfileRestriction?
  sessions       AccountSession[]
  signals        ProfileSignal[]
  createdAt      DateTime            @default(now())
  updatedAt      DateTime            @updatedAt

  @@index([accountId])
}

enum ProfileType {
  STANDARD
  KIDS
}

enum ProfileStatus {
  ACTIVE
  LOCKED
  DELETED
}

ProfileRestriction (Controles parentaux)

model ProfileRestriction {
  id                String    @id @default(uuid())
  profileId         String    @unique
  profile           Profile   @relation(fields: [profileId], references: [id])
  maxAgeRating      String?
  blockedCategories String[]
  allowedMediaTypes String[]
  timeWindowStart   String?
  timeWindowEnd     String?
  dailyLimitMinutes Int?
  createdAt         DateTime  @default(now())
  updatedAt         DateTime  @updatedAt
}

Devices et Sessions

model AccountDevice {
  id            String        @id @default(uuid())
  accountId     String
  account       Account       @relation(fields: [accountId], references: [id])
  fingerprint   String
  name          String?
  type          DeviceType    @default(UNKNOWN)
  status        DeviceStatus  @default(ACTIVE)
  trustScore    Int           @default(50)
  lastIp        String?
  lastUserAgent String?
  sessions      AccountSession[]
  createdAt     DateTime      @default(now())
  updatedAt     DateTime      @updatedAt

  @@unique([accountId, fingerprint])
}

model AccountSession {
  id               String        @id @default(uuid())
  profileId        String
  profile          Profile       @relation(fields: [profileId], references: [id])
  deviceId         String
  device           AccountDevice @relation(fields: [deviceId], references: [id])
  tokenHash        String        @unique
  refreshTokenHash String?
  status           SessionStatus @default(ACTIVE)
  ipAddress        String?
  userAgent        String?
  expiresAt        DateTime
  lastActivityAt   DateTime      @default(now())
  createdAt        DateTime      @default(now())

  @@index([profileId, status])
  @@index([deviceId])
}

Enums Devices / Sessions :

enum DeviceType {
  MOBILE_IOS
  MOBILE_ANDROID
  TABLET_IOS
  TABLET_ANDROID
  WEB_BROWSER
  SMART_TV
  STREAMING_DEVICE
  GAME_CONSOLE
  UNKNOWN
}

enum DeviceStatus { ACTIVE  REVOKED  SUSPICIOUS }
enum SessionStatus { ACTIVE  EXPIRED  REVOKED  CHALLENGED }

Entites de Contenu

Movie

model Movie {
  id               String          @id @default(uuid())
  title            String
  titleEn          String?
  overview         String?         @db.Text
  overviewEn       String?         @db.Text
  posterPath       String?
  backdropPath     String?
  releaseDate      DateTime?
  runtime          Int?
  voteAverage      Float?
  voteCount        Int?
  popularity       Float?
  originalLanguage String?
  tmdbId           Int?            @unique
  imdbId           String?
  status           ContentStatus   @default(DRAFT)
  accessType       AccessType      @default(FREE)
  videoUrl         String?
  trailerUrl       String?
  genres           Genre[]
  countries        Country[]
  languages        Language[]
  likeCount        Int             @default(0)
  dislikeCount     Int             @default(0)
  viewCount        Int             @default(0)
  reactions        ContentReaction[]
  favorites        Favorite[]
  views            ViewTracker[]
  createdAt        DateTime        @default(now())
  updatedAt        DateTime        @updatedAt

  @@index([status, accessType])
  @@index([releaseDate])
  @@index([tmdbId])
}

Champs cles :

title / titleEn : Support bilingue (francais par defaut, anglais optionnel)
tmdbId : Lien vers The Movie Database pour auto-fill des metadonnees
status : Etat de publication (DRAFT, PUBLISHED, ARCHIVED, COMING_SOON)
accessType : FREE, PREMIUM, SUBSCRIPTION

Series / Season / Episode

model Series {
  id               String          @id @default(uuid())
  title            String
  titleEn          String?
  overview         String?         @db.Text
  overviewEn       String?         @db.Text
  posterPath       String?
  backdropPath     String?
  firstAirDate     DateTime?
  lastAirDate      DateTime?
  numberOfSeasons  Int?
  numberOfEpisodes Int?
  status           ContentStatus   @default(DRAFT)
  accessType       AccessType      @default(FREE)
  tmdbId           Int?            @unique
  seasons          Season[]
  genres           Genre[]
  createdAt        DateTime        @default(now())
  updatedAt        DateTime        @updatedAt

  @@index([status])
}

model Season {
  id           String    @id @default(uuid())
  seriesId     String
  series       Series    @relation(fields: [seriesId], references: [id], onDelete: Cascade)
  seasonNumber Int
  name         String?
  overview     String?   @db.Text
  posterPath   String?
  airDate      DateTime?
  episodes     Episode[]
  createdAt    DateTime  @default(now())
  updatedAt    DateTime  @updatedAt

  @@unique([seriesId, seasonNumber])
}

model Episode {
  id            String   @id @default(uuid())
  seasonId      String
  season        Season   @relation(fields: [seasonId], references: [id], onDelete: Cascade)
  episodeNumber Int
  name          String?
  overview      String?  @db.Text
  stillPath     String?
  airDate       DateTime?
  runtime       Int?
  videoUrl      String?
  createdAt     DateTime @default(now())
  updatedAt     DateTime @updatedAt

  @@unique([seasonId, episodeNumber])
}

LiveTvChannel / RadioChannel

model LiveTvChannel {
  id          String        @id @default(uuid())
  name        String
  nameEn      String?
  logo        String?
  streamUrl   String?
  category    String?
  country     String?
  language    String?
  isHd        Boolean       @default(false)
  status      ContentStatus @default(DRAFT)
  accessType  AccessType    @default(FREE)
  sortOrder   Int           @default(0)
  createdAt   DateTime      @default(now())
  updatedAt   DateTime      @updatedAt

  @@index([status, category])
}

model RadioChannel {
  id          String        @id @default(uuid())
  name        String
  nameEn      String?
  logo        String?
  streamUrl   String?
  frequency   String?
  category    String?
  country     String?
  status      ContentStatus @default(DRAFT)
  accessType  AccessType    @default(FREE)
  sortOrder   Int           @default(0)
  createdAt   DateTime      @default(now())
  updatedAt   DateTime      @updatedAt
}

Podcasts

model PodcastCollection {
  id          String          @id @default(uuid())
  title       String
  titleEn     String?
  description String?         @db.Text
  coverImage  String?
  author      String?
  category    String?
  status      ContentStatus   @default(DRAFT)
  accessType  AccessType      @default(FREE)
  episodes    PodcastEpisode[]
  createdAt   DateTime        @default(now())
  updatedAt   DateTime        @updatedAt
}

model PodcastEpisode {
  id            String            @id @default(uuid())
  collectionId  String
  collection    PodcastCollection @relation(fields: [collectionId], references: [id], onDelete: Cascade)
  title         String
  description   String?           @db.Text
  audioUrl      String?
  duration      Int?
  episodeNumber Int?
  publishDate   DateTime?
  createdAt     DateTime          @default(now())
  updatedAt     DateTime          @updatedAt
}

Systeme Replay

model ReplayCategory {
  id          String              @id @default(uuid())
  name        String
  nameEn      String?
  slug        String              @unique
  description String?
  type        ReplayCategoryType  @default(OTHER)
  sortOrder   Int                 @default(0)
  collections ReplayCollection[]
  createdAt   DateTime            @default(now())
  updatedAt   DateTime            @updatedAt
}

model ReplayCollection {
  id            String               @id @default(uuid())
  categoryId    String
  category      ReplayCategory       @relation(fields: [categoryId], references: [id])
  title         String
  titleEn       String?
  description   String?              @db.Text
  coverImage    String?
  status        ContentStatus        @default(DRAFT)
  videoEpisodes ReplayVideoEpisode[]
  audioEpisodes ReplayAudioEpisode[]
  createdAt     DateTime             @default(now())
  updatedAt     DateTime             @updatedAt
}

model ReplayVideoEpisode {
  id           String              @id @default(uuid())
  collectionId String
  collection   ReplayCollection    @relation(fields: [collectionId], references: [id])
  title        String
  description  String?             @db.Text
  duration     Int?
  broadcastDate DateTime?
  sources      ReplayVideoSource[]
  createdAt    DateTime            @default(now())
  updatedAt    DateTime            @updatedAt
}

model ReplayVideoSource {
  id        String              @id @default(uuid())
  episodeId String
  episode   ReplayVideoEpisode  @relation(fields: [episodeId], references: [id])
  url       String
  quality   VideoQuality
  format    String?
  createdAt DateTime            @default(now())

  @@unique([episodeId, quality])
}

News et Evenements

model NewsArticle {
  id          String        @id @default(uuid())
  title       String
  titleEn     String?
  content     String        @db.Text
  contentEn   String?       @db.Text
  excerpt     String?
  coverImage  String?
  category    String?
  author      String?
  publishDate DateTime?
  status      ContentStatus @default(DRAFT)
  viewCount   Int           @default(0)
  createdAt   DateTime      @default(now())
  updatedAt   DateTime      @updatedAt

  @@index([status, publishDate])
}

model LiveEvent {
  id          String        @id @default(uuid())
  title       String
  titleEn     String?
  description String?       @db.Text
  coverImage  String?
  startTime   DateTime
  endTime     DateTime?
  streamUrl   String?
  status      ContentStatus @default(DRAFT)
  accessType  AccessType    @default(FREE)
  category    String?
  createdAt   DateTime      @default(now())
  updatedAt   DateTime      @updatedAt

  @@index([startTime, status])
}

Fonctionnalites d'Engagement

ContentReaction (Like / Dislike)

model ContentReaction {
  id           String       @id @default(uuid())
  userId       String
  mediaType    MediaType
  mediaId      String
  reactionType ReactionType
  createdAt    DateTime     @default(now())
  updatedAt    DateTime     @updatedAt

  @@unique([userId, mediaType, mediaId])
  @@index([mediaType, mediaId])
}

ViewTracker

model ViewTracker {
  id          String    @id @default(uuid())
  userId      String?
  sessionId   String
  ipAddress   String?
  userAgent   String?
  mediaType   MediaType
  mediaId     String
  viewedAt    DateTime  @default(now())

  @@index([mediaType, mediaId])
  @@index([userId])
}

Anti-abus : cooldown de 30 minutes entre vues dupliquees (userId + sessionId + ipAddress + userAgent).

Favorites et WatchHistory

model Favorite {
  id        String    @id @default(uuid())
  userId    String
  mediaType MediaType
  mediaId   String
  createdAt DateTime  @default(now())

  @@unique([userId, mediaType, mediaId])
  @@index([userId])
}

model WatchHistory {
  id          String    @id @default(uuid())
  userId      String
  mediaType   MediaType
  mediaId     String
  progress    Int       @default(0)
  duration    Int?
  completed   Boolean   @default(false)
  watchedAt   DateTime  @default(now())
  updatedAt   DateTime  @updatedAt

  @@unique([userId, mediaType, mediaId])
  @@index([userId, watchedAt])
}

Enums

Statut du contenu

enum ContentStatus {
  DRAFT        // Non visible aux utilisateurs
  PUBLISHED    // Visible aux utilisateurs
  ARCHIVED     // Cache, conserve pour les archives
  COMING_SOON  // Annonce mais pas encore disponible
}

Type d'acces

enum AccessType {
  FREE         // Disponible pour tous
  PREMIUM      // Achat unique
  SUBSCRIPTION // Necessite un abonnement actif
}

Type de media

enum MediaType {
  MOVIE      SERIES     EPISODE
  LIVETV     RADIO      PODCAST
  NEWS       EVENT      REPLAY
}

Autres enums

enum ReactionType { LIKE  DISLIKE }

enum VideoQuality { SD  HD  FHD  UHD }

enum ReplayCategoryType {
  SPORT  CULTURE  MUSIC  INFO  ENTERTAINMENT
  DOCUMENTARY  KIDS  EDUCATION  OTHER
}

Index de Performance

Index Prisma

// Movies - requetes frequentes
@@index([status, accessType])
@@index([releaseDate])
@@index([tmdbId])

// Series
@@index([status])

// Full-text search
@@index([title])

// User lookups
@@index([email])
@@index([firebaseUid])

// Session management
@@index([profileId, status])
@@index([tokenHash])

// View tracking
@@index([mediaType, mediaId])
@@index([userId])

// News
@@index([status, publishDate])

// Events
@@index([startTime, status])

// Channels
@@index([status, category])

Index composites SQL

-- Optimisation des requetes a fort trafic
CREATE INDEX idx_movies_status_release ON movies(status, release_date DESC);
CREATE INDEX idx_content_reactions_lookup ON content_reactions(media_type, media_id, reaction_type);

Migrations

Commandes

# Creer une nouvelle migration
npm run prisma:migrate:dev -- --name add_feature_x

# Appliquer les migrations (production)
npm run prisma:migrate:deploy

# Reset la base de donnees (developpement uniquement)
npx prisma migrate reset

# Voir le statut des migrations
npx prisma migrate status

Convention de nommage

YYYYMMDDHHMMSS_descriptive_name

Exemples :
20250115120000_add_user_preferences
20250116093000_add_content_reactions
20250117150000_add_multi_tenant_support

Bonnes pratiques

Toujours sauvegarder avant les migrations en production
Tester les migrations sur staging (port 3051) en premier
Garder les migrations petites et focalisees
Ne jamais editer les migrations existantes
Utiliser des transactions pour les changements complexes

Resume des entites (73 modeles)

Par domaine

Domaine	Entites	Nombre
Multi-Tenant	Tenant, Account, Profile, ProfileRestriction, AccountDevice, AccountSession, ProfileSignal, RiskEvent	8
Auth Legacy	User, SocialAccount	2
VOD	Movie, Series, Season, Episode	4
Live	LiveTvChannel, RadioChannel, LiveEvent	3
Audio	PodcastCollection, PodcastEpisode	2
Replay	ReplayCategory, ReplayCollection, ReplayVideoEpisode, ReplayVideoSource, ReplayAudioEpisode, ReplayAudioSource	6
Editorial	NewsArticle	1
Catalogue	Genre, Category, Country, Language	4
Engagement	ContentReaction, ViewTracker, Favorite, WatchHistory	4
Platform	Slider, AdvertisingBanner, SupportTicket, Notification, Setting, StreamingToken, DrmKey	7
Subscriptions	SubscriptionPlan, Subscription, PaymentMethod, Transaction	4+
Autres	Modeles additionnels (sync, config, etc.)	~28

References

Vue d'ensemble -- Architecture globale
Clean Architecture -- Detail des couches
Diagrammes -- Diagramme ER complet
Prisma Documentation

Vue d'ensemble​

Stack base de donnees​

Emplacement du schema​

Caracteristiques cles​

Diagramme Entite-Relation​

Vue globale​

Systeme multi-tenant​

Systeme de contenu​

Entites Core​

User (Legacy)​

SocialAccount​

Systeme Account (Multi-Tenant)​

Tenant​

Account​

Profile​

ProfileRestriction (Controles parentaux)​

Devices et Sessions​

Entites de Contenu​

Movie​

Series / Season / Episode​

LiveTvChannel / RadioChannel​

Podcasts​

Systeme Replay​

News et Evenements​

Fonctionnalites d'Engagement​

ContentReaction (Like / Dislike)​

ViewTracker​

Favorites et WatchHistory​

Enums​

Statut du contenu​

Type d'acces​

Type de media​

Autres enums​

Index de Performance​

Index Prisma​

Index composites SQL​

Migrations​

Commandes​

Convention de nommage​

Bonnes pratiques​

Resume des entites (73 modeles)​

Par domaine​

References​