Schéma de Base de Données

Vue d'ensemble

Le projet utilise Supabase (PostgreSQL 17) comme base de données principale.

Propriété	Valeur
Projet Supabase	StageConnectV1
Région	eu-central-2 (Francfort)
Tables publiques	46
Vues	4
Vues matérialisées	2
Index	150+

Version interactive

Pour une version interactive du diagramme ER avec zoom complet :

• Consulter le fichier SQL source : backend/DATABASES/stageconnect_schema_evolutif_complet.sql
• Ou utiliser un outil comme DBeaver/PGAdmin pour le diagramme ER complet avec toutes les relations

Diagramme ER

Tables par catégorie

Utilisateurs

Table	Description
users	Table centrale d'authentification (Supabase Auth)
students	Profils étudiants avec niveau académique, université
academic_staff	Enseignants-chercheurs avec matricule, département
company_staff	Employés entreprise avec droits de supervision
admins	Administrateurs plateforme avec type (superadmin, university_admin)

Institutions

Table	Description
universities	Université avec code, type (public/private/international), config JSONB
schools_faculties	Écoles/facultés au sein d'une université
departments	Départements académiques
academic_cycles	Années académiques avec dates de stage
academic_programs	Programmes (L1-L3, M1-M2, ING1-ING5) avec règles métier

Stages & Candidatures

Table	Description
internship_offers	Offres de stage publiées par les entreprises
internship_applications	Candidatures étudiants aux offres
internship_conventions	Conventions de stage signées (workflow: PENDING → IN_PROGRESS → COMPLETED)
internship_reports	Rapports de stage soumis
internship_tasks	Tâches de suivi du stage
internship_todos	Todo list par convention
internship_comments	Commentaires sur les conventions
internship_inspections	Visites de stage (contrôle terrain)

Entreprises

Table	Description
companies	Entreprises avec secteur, localisation, vérification
company_staff	Personnel entreprise pouvant superviser des stages

Évaluations

Table	Description
academic_supervisor_evaluations	Évaluations par l'encadreur académique
internship_supervisor_evaluations	Évaluations par le superviseur entreprise
defense_evaluations	Notes des membres du jury
student_self_evaluations	Auto-évaluations étudiant

Soutenance

Table	Description
defenses	Sessions de soutenance avec date, lieu, status
defense_jury_members	Composition du jury
defense_deliberations	Délibérations du jury

Thèmes & Propositions

Table	Description
student_themes	Thèmes de stage proposés par les étudiants
student_pairs	Binômes pour travaux de groupe
proposed_themes	Thèmes proposés par les entreprises
theme_database	Base de thèmes validés
supervision_requests	Demandes d'encadrement

Données étudiants

Table	Description
student_documents	Documents uploadés (CV, lettres)
student_experiences	Expériences professionnelles
student_skills	Compétences techniques
student_certifications	Certifications obtenues
student_qualifications	Qualifications académiques
student_projects	Projets académiques
student_social_links	Liens LinkedIn, portfolio

Système & Configuration

Table	Description
notifications	Notifications utilisateurs
bug_reports	Signalements de bugs
subscriptions	Abonnements plateforme
subscription_plans	Plans tarifaires
activity_logs	Journal d'activité (audit)
system_configuration	Configuration globale
user_notification_preferences	Préférences de notification

Vues (Views)

Vue	Description
students_full_info	Vue complète des étudiants avec toutes les infos (université, thème, stage en cours)
conventions_with_actors	Conventions avec tous les acteurs (étudiant, entreprise, superviseurs)
themes_with_stats	Thèmes avec statistiques (binôme, partenaire)
platform_admins	Vue consolidée pour les administrateurs plateforme

Vues Matérialisées

Vue matérialisée	Description
university_statistics	Statistiques agrégées par université (étudiants, stages, entreprises partenaires)
program_statistics	Statistiques par programme académique (inscrits, stages, thèmes)

Index

Index primaires

• Toutes les tables ont une PK id de type uuid

Index sur FK fréquentes

Index	Table	Colonnes
idx_academic_staff_user	academic_staff	user_id
idx_academic_staff_university	academic_staff	university_id
idx_students_user	students	user_id
idx_students_university	students	university_id
idx_company_staff_company	company_staff	company_id
idx_company_staff_user	company_staff	user_id
idx_admins_user	admins	user_id
idx_admins_university	admins	university_id
idx_offers_company	internship_offers	company_id
idx_applications_offer	internship_applications	internship_offer_id
idx_applications_student	internship_applications	student_id
idx_conventions_student	internship_conventions	student_id
idx_conventions_company	internship_conventions	company_id
idx_conventions_university	internship_conventions	university_id
idx_conventions_academic_supervisor	internship_conventions	academic_supervisor_id

Index partiels (données actives)

Index	Table	Condition
idx_cycles_active	academic_cycles	is_active = true
idx_programs_active	academic_programs	is_active = true
idx_departments_active	departments	is_active = true
idx_academic_staff_available	academic_staff	is_available_for_supervision = true
idx_offers_active	internship_offers	is_active = true
idx_companies_verified	companies	is_verified = true

Index sur status (workflow)

Index	Table	Colonnes
idx_applications_status	internship_applications	status
idx_conventions_status	internship_conventions	status
idx_defenses_status	defenses	status
idx_inspections_status	internship_inspections	status
idx_bug_reports_status	bug_reports	status

IndexGIN

• idx_offers_levels : target_levels (tableau)
• Index sur JSONB (config, career_prospects)

Triggers & Automatisations

Le schéma inclut plusieurs triggers pour automatiser les tâches récurrentes :

Trigger	Table	Fonction
update_*_updated_at	Toutes les tables	Met à jour updated_at automatiquement
generate_theme_title_hash	student_themes, theme_database	Génère le hash SHA-256 du titre (anti-duplicata)
manage_application_counter	internship_applications	Incrémente/décrémente le compteur de candidatures
manage_supervisor_count	internship_conventions	Met à jour le nombre d'étudiants supervisés
extract_coords_*	internship_offers, companies, internship_conventions	Extrait lat/long depuis Google Maps URL
notify_theme_validation	student_themes	Crée une notification quand un thème est validé

Row Level Security (RLS)

Le schéma active RLS sur les tables sensibles :

• students : accès propre, superviseur académique, admin université, platform admin
• academic_staff : accès par université
• companies : accès public pour les offres, restreint pour les données internes
• internship_conventions : accès par acteur (étudiant, entreprise, université)
• notifications : accès destinataire uniquement

Conventions de nommage

Tables

• Pluriel : students, internship_offers
• snake_case : tout en minuscules avec underscores

Colonnes

• PK : toujours id (uuid)
• FK : {table_singulier}_id — ex: user_id, university_id, company_id
• Timestamps : created_at, updated_at (TIMESTAMP WITH TIME ZONE)
• Booléens : préfixe is_ — ex: is_active, is_verified, is_current

Contraintes

• UK : {table}_{colonne}_key — ex: companies_registration_number_key
• PK : {table}_pkey — ex: students_pkey
• FK : {table}_{colonne}_fkey — ex: students_user_id_fkey

Règles métier encodées

CHECK constraints

-- academic_programs.degree_level
CHECK (degree_level IN ('licence', 'master', 'doctorat', 'dut', 'bts'))

-- academic_programs.academic_year
CHECK (academic_year IN ('L1', 'L2', 'L3', 'M1', 'M2', 'D1', 'D2', 'D3', 'ING1', 'ING2', 'ING3', 'ING4', 'ING5'))

-- internship_conventions.status
CHECK (status IN ('PENDING', 'IN_PROGRESS', 'COMPLETED', 'CANCELLED', 'VALIDATED'))

-- users.user_type
CHECK (user_type IN ('STUDENT', 'ACADEMIC_STAFF', 'COMPANY_STAFF', 'ADMIN', 'SCHOOL_STAFF'))

Contraintes de programme

Les programmes académiques définissent des règles métier :

• requires_theme_validation : thème doit être validé
• requires_academic_supervisor : encadreur académique obligatoire
• allows_pair_work : travail en binôme autorisé
• min_internship_duration_months / max_internship_duration_months

Workflow des stages

internship_applications:
  pending → reviewed → accepted / rejected
           (ou withdrawn)

internship_conventions:
  planned → validated → active → completed
           (ou cancelled / suspended)

defenses:
  scheduled → in_progress → completed
           (ou cancelled / postponed)

Workflow des thèmes

student_themes:
  pending → auto_approved / auto_rejected

student_pairs:
  pending → confirmed / rejected
           (ou dissolved)