Aller au contenu

Base de données - Documentation Technique

🎯 Vue d'ensemble

La base de données de Teadle utilise PostgreSQL avec Doctrine ORM pour le mapping objet-relationnel. L'architecture suit les principes DDD avec des entités métier bien définies.

Table trainer_organization_contact

  • Les emails sont persistés en minuscules (normalisation dans l’entité TrainerOrganizationContact).
  • Unicité : couple (trainer_organization_assignment_id, email) via l’index uniq_toc_assignment_email (un même email ne peut pas apparaître deux fois pour la même affectation formateur–organisation). Les lignes sans email (ex. contact rattaché uniquement à un staff) peuvent coexister avec email NULL (comportement PostgreSQL sur les contraintes UNIQUE avec NULL).

Tables document, annexes facturation et documents entreprise

  • Namespace PHP : App\Domain\Entity\Document — Document (abstraite), TrainerDocumentBillingAttachment, TrainerDocumentCompany.
  • Héritage Doctrine JOINED : classe abstraite Document (champs communs : uuid, chemin de stockage, nom, libellé affiché, MIME, taille en octets, created_at / updated_at en datetimetz_immutable — updated_at rafraîchi via #[ORM\PreUpdate]) ; TrainerDocumentBillingAttachment ajoute trainer_id, attachment_type (TrainerDocumentBillingAttachmentType : factures/avoirs, CRA, etc.), included_by_default. Stockage S3 sous {uuid}/billing/documents/. Les PDF de facture figés (hors brouillon) sont stockés séparément sous {trainerUuid}/billing/invoices/{invoiceUuid} (StoragePathType::TRAINER_INVOICE_PDF), sans colonne sur invoicing_invoice.
  • Version20260326120000 (bloc fusionné) : création en une fois de document (avec created_at et updated_at), trainer_document_billing_attachment (discriminant trainer_billing_attachment) et trainer_document_company (documents entreprise, discriminant trainer_company) ; FK *.id → document.id, trainer_id → trainer.id.

Table billing_template

  • FK trainer_id : ON DELETE CASCADE — suppression du compte formateur = suppression de ses modèles ; les modèles système (trainer_id NULL) ne sont pas liés à un trainer et restent en base.

  • Namespace PHP : App\Domain\Entity\Invoicing\BillingTemplate.

  • Champs : uuid (unique), trainer_id (nullable, NULL = modèle système Teadle), name, type (INVOICE ou ACTIVITY_REPORT), is_default, active (bool, défaut true — false = désactivation logique, plus listé ni accessible en GET), use_billing_settings_appearance (bool, défaut true), settings (JSON), logo_url, created_at, updated_at.
  • Contrainte : index unique partiel uniq_billing_template_default_per_type sur (trainer_id, type) avec clause WHERE is_default = true pour garantir un seul modèle par défaut par type côté trainer.
  • Version20260326120000 (suite) : table billing_template sans colonne is_system (système = trainer_id NULL), FK trainer_id en ON DELETE CASCADE, seed des 2 modèles système avec settings JSON minimal (accent Teadle, typo, header.showLogo). Voir aussi .doc/backend/BILLING_TEMPLATE_SYSTEM_DEFAULTS.md.
  • Fixtures Alice : fixtures/billing_template.yaml recrée ces 2 lignes après purge (hautelook:alice:doctrine:load), avec les mêmes UUID et le même JSON settings que la migration.
  • Version20260330113000 : ajout de use_billing_settings_appearance (BOOLEAN NOT NULL DEFAULT TRUE) pour piloter l’héritage visuel (logo, couleur, taille typo) depuis trainer_billing_settings.
  • Version20260401120000 : colonne active (BOOLEAN NOT NULL DEFAULT TRUE) — DELETE /trainer/billing/templates/{uuid} désactive le modèle (plus de suppression physique ni suppression du fichier logo).

Table trainer_organization_assignment

  • color (VARCHAR(7), Doctrine : enum OrganizationColor) : seule source de vérité pour la couleur agenda / avatar (couple formateur–organisation). Attribuée à la création de l’assignation (TrainerOrganizationAssignmentRepository::assign via CalendarColorPicker::pickFirstAvailableOrDefault). Si besoin sur d’anciennes données sans couleur à la première synchro iCal : CalendarColorAssigner::assignColorIfMissing. Les couleurs déjà prises : TrainerOrganizationAssignmentRepository::findUsedOrganizationColorHexValuesByTrainer. Migrations : Version20260401140000 (copie initiale depuis calendar_configurator + backfill), Version20260401160000 (suppression de la colonne color sur calendar_configurator).

Table calendar_configurator

  • last_import_error_code (VARCHAR(64), nullable, PHP : CalendarSyncErrorCode — ex. UNKNOWN, CONNECTION_FAILED) : code de la dernière erreur de synchro iCal lorsque is_last_import_successful est false. Alimenté par RecordTrainerCalendarConfiguratorSyncFailureCommand (échec fetch URL/fichier ICS dans TrainerCalendarConfiguratorSynchronizer, ou erreur pendant PersistTrainerCalendarConnectorEventsCommand), effacé au succès. Le processus async expose aussi sync_error_code dans les métadonnées du Process. Migration Version20260401170000.
  • DISC-019 (FEAT-107) :
  • current_sync_process_id (FK nullable vers process.id, ON DELETE SET NULL) : pointeur vers la sync active, utilisé pour reconstruire le polling frontend après F5 / nouvel onglet.
  • consecutive_failures (INT, défaut 0) : compteur d'échecs consécutifs, incrémenté à l'échec et remis à zéro au succès.

  • last_sync_end_date (TIMESTAMPTZ, nullable) : snapshot terminal conservé côté configurateur, même si le Process est purgé plus tard.

  • provider_type (VARCHAR(50), nullable, FEAT-043) : choix utilisateur dans le hub connecteurs (google, outlook, apple, edusign, hyperplanning, ypareo, ical, ical_file). Distinct de la détection de format côté parseur iCal (PRODID). Valeur ical_file : import par fichier ; la clé objet S3 du .ics est {trainerUuid}/ics/{calendarConfiguratorUuid}.ics (voir TrainerCalendarIcalFileStorageKeyResolverInterface), sans colonne de chemin. Lors d’un import dont la stratégie connecteur est ical_file, PersistTrainerCalendarConnectorEventsCommand force provider_type à ical_file (même si le hub avait transmis un autre libellé), pour que la resynchro planifiée lise bien le fichier sur S3. Sérialisé dans CalendarConfiguratorResponse ; accepté en option sur ajout lien / upload iCal. Migrations Version20260401180000 ; suppression de ical_file_storage_path : Version20260414130000.

  • oauth_calendar_id (VARCHAR(512), nullable) : identifiant d’agenda côté fournisseur OAuth (Google calendarList id, Outlook calendars id). Renseigné au POST …/oauth/{provider}/callback après choix utilisateur. Migration Version20260420120000.

Table reservation_batch_message (FEAT-051)

  • Messages du thread formateur ↔ école pour un lot donné (reservation_batch_id → reservation_batch, sender_id → user).
  • Colonnes : uuid (unique), content (TEXT), created_at (TIMESTAMPTZ immutable). Index idx_reservation_batch_message_batch sur reservation_batch_id.
  • Migration Version20260404203000.

Table reservation_batch — idempotence réservation (BUG-041)

  • Colonnes ajoutées :
  • idempotency_key (VARCHAR(255), nullable, unique)
  • Rôle : garantir l’idempotence des soumissions de réservation (public/staff) lors des retries réseau et doubles soumissions.
  • Stratégie : la clé est portée en header HTTP Idempotency-Key et persistée sur le premier lot créé ; toute requête rejouée avec la même clé réutilise le lot existant.
  • Migration : Version20260416110000.

Messagerie directe / inbox (FEAT-065)

  • ConversationRepository étend AbstractServiceRepository (ORM) ; listes paginées exposées en PaginatedResult via les handlers SearchConversationsHandler / SearchConversationMessagesHandler (même principe que recherche organisations + TrainerOrganizationSearchProvider).
  • Namespace PHP : App\Domain\Entity\Messaging — Conversation, ConversationParticipant, Message ; enum ConversationType (direct, batch_thread, system).
  • conversation : uuid, type, subject, reservation_batch_id (nullable, FK vers reservation_batch pour futurs fils batch), last_message_at, last_message_preview, created_at, updated_at ; index idx_conversation_last_message, idx_conversation_type_batch (type, reservation_batch_id).
  • conversation_participant : conversation_id, user_id, last_read_at, is_archived, joined_at ; unique (conversation_id, user_id) ; index idx_participant_unread (user_id, last_read_at).
  • message : uuid, conversation_id, sender_id → user, content (TEXT), created_at, updated_at ; index idx_message_conversation_date (conversation_id, created_at).
  • Migration Version20260410120000.

Notifications in-app — dismiss, sous-types association / calendar (FEAT-066)

  • Table notification : colonne uuid (UUID, unique, non null après backfill) pour les appels API (ex. dismiss) ; colonne dismissed_at (TIMESTAMPTZ nullable) pour bannières / alertes dismissables.
  • Héritage JOINED (discriminant type sur notification) : nouvelles tables association_notification (organization_id → organization, related_user_id → user nullable) et calendar_notification (reservation_id → reservation nullable).
  • Migration Version20260410200000.
  • Migration Version20260411120000 : notification.uuid + index unique.

Statut de lot FULFILLED (FEAT-051)

  • Valeur VARCHAR supplémentaire dans reservation_batch.status (enum PHP BatchStatus) : auto-fermeture des autres lots du même groupe lorsqu’un formateur accepte (first-claim). Ne pas confondre avec CANCELLED (annulation par l’école). Aucune migration de type enum SQL : la colonne existante accepte la nouvelle valeur.

🗄️ Schéma de base de données

Diagramme ER complet

erDiagram
    %% Tables principales
    "user" {
        int id PK
        string firstname
        string lastname
        string email UK
        string password
        array roles
        string oauthProvider
        string oauthProviderId UK
        boolean acceptCgv
        boolean acceptOptIn
        boolean verifiedEmail
        boolean finishedOnBoarding
        string type
        datetime createdAt
        datetime updatedAt
    }

    "trainer" {
        int id PK
        string SIRET
        string cv
        string cv_analysis
        json preferences
    }

    "organization" {
        int id PK
        string name
        string organizationType
        string service
        phone_number phoneNumber
        json preferences
    }

    %% Tables d'événements
    "event" {
        int id PK
        int trainer_id FK
        datetime date_start
        datetime date_end
        string type
        string name
        text description
    }

    "intervention_event" {
        int id PK
        int organization_id FK
        string name
        float amount
        boolean is_approve_by_organization
        datetime date_start
        datetime date_end
    }

    "personal_event" {
        int id PK
        string name
        text description
    }

    %% Tables d'opportunités
    "opportunity" {
        int id PK
        int organization_id FK
        string name
        text description
        datetime date_start
        datetime date_end
        string status
        float budget
        string location
    }

    "opportunity_proposal" {
        int id PK
        int opportunity_id FK
        int trainer_id FK
        int candidacy_id FK
        string status
        datetime createdAt
        float proposedAmount
    }

    "candidacy" {
        int id PK
        int trainer_id FK
        datetime date
        string status
        text motivation
        float proposedAmount
    }

    %% Tables de facturation
    "trainer_company" {
        int id PK
        string companyName
        string siret
        string address
        string vatNumber
        string iban
        string bic
        string defaultVatRate
        string defaultUnit
        string paymentTerm
        text legalMentionVatExempt
        text legalMentionPayment
    }

    "invoice" {
        int id PK
        int trainer_id FK
        int organization_id FK
        int intervention_event_id FK
        string amount
        string status
        datetime issueDate
        datetime dueDate
        string invoiceNumber
    }

    %% Tables de profil
    "trainer_experience" {
        int id PK
        int trainer_id FK
        string title
        string company
        text description
        datetime startDate
        datetime endDate
        boolean current
    }

    "trainer_education" {
        int id PK
        int trainer_id FK
        string degree
        string institution
        string field
        datetime graduationDate
        string description
    }

    %% Tables de sécurité
    "auth_code" {
        int id PK
        int user_id FK
        string code
        datetime expiresAt
        boolean used
        string type
    }

    "password_reset_request" {
        int id PK
        int user_id FK
        string token
        datetime expiresAt
        boolean used
    }

    "refresh_tokens" {
        int id PK
        string refresh_token UK
        string username
        datetime valid
    }

    "task" {
        int id PK
        int trainer_id FK
        string title
        text description
        string status
        datetime dueDate
        int priority
    }

    %% Relations
    "user" ||--|| "trainer" : "inherits"
    "user" ||--|| "organization" : "inherits"

    "trainer" ||--o| "trainer_company" : "has"
    "trainer" ||--o{ "event" : "has"
    "trainer" ||--o{ "candidacy" : "has"
    "trainer" ||--o{ "invoice" : "has"
    "trainer" ||--o{ "trainer_experience" : "has"
    "trainer" ||--o{ "trainer_education" : "has"
    "trainer" ||--o{ "task" : "has"
    "trainer" ||--o{ "opportunity_proposal" : "proposes"

    "organization" ||--o{ "intervention_event" : "has"
    "organization" ||--o{ "opportunity" : "has"
    "organization" ||--o{ "invoice" : "has"

    "opportunity" ||--o{ "opportunity_proposal" : "has"
    "candidacy" ||--o{ "opportunity_proposal" : "linked_to"

    "intervention_event" ||--o{ "invoice" : "generates"

    "user" ||--o{ "auth_code" : "has"
    "user" ||--o{ "password_reset_request" : "has"

🏗️ Entités détaillées

1. User (Entité abstraite)

#[ORM\Entity]
#[ORM\Table(name: '`user`')]
#[ORM\UniqueConstraint(name: 'UNIQ_IDENTIFIER_EMAIL', fields: ['email'])]
#[ORM\InheritanceType('JOINED')]
#[ORM\DiscriminatorColumn(name: 'type', type: 'string')]
#[ORM\DiscriminatorMap([
    'trainer' => Trainer::class,
    'organization' => Organization::class
])]
abstract class User implements UserInterface, PasswordAuthenticatedUserInterface
{
    #[ORM\Id]
    #[ORM\GeneratedValue]
    #[ORM\Column]
    private ?int $id = null;

    #[ORM\Column(length: 255, nullable: true)]
    private ?string $firstname = null;

    #[ORM\Column(length: 255, nullable: true)]
    private ?string $lastname = null;

    #[ORM\Column(length: 180)]
    private ?string $email = null;

    #[ORM\Column]
    private array $roles = [];

    #[ORM\Column]
    private ?string $password = null;

    #[ORM\Column(length: 255, nullable: true)]
    private ?string $oauthProvider = null;

    #[ORM\Column(length: 255, nullable: true, unique: true)]
    private ?string $oauthProviderId = null;

    #[ORM\Column(type: 'boolean', nullable: true)]
    private bool $acceptCgv = false;

    #[ORM\Column(type: 'boolean', nullable: true)]
    private bool $acceptOptIn = false;

    #[ORM\Column(type: 'boolean', nullable: false, options: ['default' => false])]
    private bool $verifiedEmail = false;

    #[ORM\Column(type: 'boolean', nullable: false, options: ['default' => false])]
    private bool $finishedOnBoarding = false;
}

Caractéristiques : - Héritage : Table-per-class avec discriminator - Authentification : Implémente UserInterface - OAuth : Support multi-providers - Onboarding : Suivi du processus d'inscription - registered_at (TIMESTAMP, NOT NULL) : date d’inscription ; utilisée notamment pour calculer la fin de fenêtre sample data (registered_at + teadle.sample_data_duration_days, défaut 14). Migration Version20260429120000 : ajout de la colonne avec back-fill NOW() pour les comptes existants.

2. Trainer (Hérite de User)

#[ORM\Entity]
class Trainer extends User
{
    public const TYPE = 'trainer';

    #[ORM\Column(length: 255, nullable: true)]
    private ?string $SIRET = null;

    #[ORM\Column(type: 'text', nullable: true)]
    private ?string $cv = null;

    #[ORM\Column(type: 'json', nullable: true)]
    private ?array $cvAnalysis = null;

    #[ORM\Column(type: 'json', nullable: true)]
    private ?array $preferences = null;

    /**
     * @var Collection<int, Candidacy>
     */
    #[ORM\OneToMany(mappedBy: 'trainer', targetEntity: Candidacy::class)]
    private Collection $candidacies;

    /**
     * @var Collection<int, Event>
     */
    #[ORM\OneToMany(mappedBy: 'trainer', targetEntity: Event::class)]
    private Collection $events;

    /**
     * @var Collection<int, Invoice>
     */
    #[ORM\OneToMany(mappedBy: 'trainer', targetEntity: Invoice::class)]
    private Collection $invoices;
}

Caractéristiques : - SIRET : Numéro d'identification professionnel - CV : Stockage du CV et analyse - Préférences : Configuration JSON - Relations : Candidatures, événements, factures - Onboarding (DISC-016 / FEAT-086, FEAT-087) : onboarding_completed_steps (JSON, existant) ; Version20260429120000 — checklist_dismissed_at, onboarding_completed_at (5/5 étapes), sample_data_dismissed_at ; Version20260430120000 — first_real_reservation_received_at (1ère vraie ReservationBatch, désactive le sample data). L’état agrégé est exposé sur GET /me (formateur) dans onboardingProgress (voir GetTrainerOnboardingProgressHandler).

3. TrainerCompany (Entreprise du formateur)

Entité liée au formateur (OneToOne) : identité (nom, SIRET, adresse, statut juridique, NDA, capital, email / téléphone de contact), infos bancaires (IBAN, BIC). La présentation facture (mentions, corps d’e-mail, logo) est sur TrainerBillingSettings.

  • Relation : Un formateur a au plus une entreprise.

3bis. TrainerBillingSettings (Paramètres de facturation) — FEAT-017, BUG-031

Entité OneToOne avec Trainer : paramètres par défaut pour la facturation (tarif, TVA, unité, délai de paiement), numérotation des factures (art. 289 CGI), personnalisation des documents et coordonnées / contenus propres à la facturation.

  • Champs tarif : defaultPrice, defaultVatRate, defaultUnit, paymentTerm.
  • Numérotation facture : invoicePrefixTemplate (template avec variables (annee), (mois), (jour)), invoiceNextSequence, invoiceLastResolvedPrefix, invoiceSequenceLocked, invoiceLockedYear. Génération du code facture via InvoiceNumberGenerator (Domain/Service/Invoicing) ; verrouillage de la séquence après première finalisation par année fiscale ; déverrouillage au changement d’année.
  • Numérotation CRA (UX-039, même moteur que factures) : activityReportPrefixTemplate (défaut CRA-(annee)(mois)-), activityReportNextSequence, activityReportLastResolvedPrefix ; génération via InvoiceNumberGenerator::previewActivityReport / consommation de séquence ; pas de verrouillage CRA comme pour les factures.
  • Branding : accent_color, font_size (chaîne stockée : valeurs enum BillingDocumentFontSize : small, normal, large), logo (UUID fichier sur S3, dossier {trainerUuid}/billing/logo/).
  • Mentions légales (structurées) : legal_mentions_penalties, legal_mentions_confidentiality_enabled, legal_mentions_confidentiality, legal_mentions_custom_enabled, legal_mentions_custom ; assemblage texte pour PDF / config via BillingLegalMentionsComposer.
  • Email / coordonnées : invoice_email_body, billing_email, billing_phone (optionnels).
  • Migrations : facturation (tarif, TVA, unité, délai) déplacée de trainer_company vers trainer_billing_settings ; Version20260305140000 (BUG-031) : numérotation facture ; Version20260326120000 (bloc fusionné) : colonnes branding, mentions structurées, email, coordonnées, logo sur trainer_billing_settings ; copie initiale depuis trainer_company (email, téléphone, template, mentions) quand disponible ; suppression de default_legal_mentions, email_template, logo_url sur trainer_company ; tables document / annexes ; billing_template + seed (voir section dédiée ci-dessus).

4. Organization (Hérite de User)

#[ORM\Entity]
class Organization extends User
{
    public const TYPE = 'organization';

    #[ORM\Column(length: 255, nullable: true)]
    private ?string $name = null;

    #[ORM\Column(length: 255, nullable: true)]
    private ?string $organizationType = null;

    #[ORM\Column(length: 255, nullable: true)]
    private ?string $service = null;

    #[ORM\Column(type: 'phone_number', nullable: true)]
    private ?PhoneNumber $phoneNumber = null;

    #[ORM\Column(type: 'json', nullable: true)]
    private ?array $preferences = null;

    /**
     * @var Collection<int, Opportunity>
     */
    #[ORM\OneToMany(mappedBy: 'organization', targetEntity: Opportunity::class)]
    private Collection $opportunities;

    /**
     * @var Collection<int, InterventionEvent>
     */
    #[ORM\OneToMany(mappedBy: 'organization', targetEntity: InterventionEvent::class)]
    private Collection $interventionEvents;
}

Caractéristiques : - Informations : Nom, type, service - Téléphone : Value Object PhoneNumber - Préférences : Configuration JSON - Relations : Opportunités, interventions

5. Event (Événements)

#[ORM\Entity]
#[ORM\InheritanceType('JOINED')]
#[ORM\DiscriminatorColumn(name: 'type', type: 'string')]
#[ORM\DiscriminatorMap([
    'intervention' => InterventionEvent::class,
    'personal' => PersonalEvent::class
])]
abstract class Event
{
    #[ORM\Id]
    #[ORM\GeneratedValue]
    #[ORM\Column]
    private ?int $id = null;

    #[ORM\ManyToOne(inversedBy: 'events')]
    #[ORM\JoinColumn(nullable: false)]
    private ?Trainer $trainer = null;

    #[ORM\Column(type: 'datetime_immutable')]
    private ?DateTimeImmutable $dateStart = null;

    #[ORM\Column(type: 'datetime_immutable')]
    private ?DateTimeImmutable $dateEnd = null;

    #[ORM\Column(length: 255)]
    private ?string $name = null;

    #[ORM\Column(type: 'text', nullable: true)]
    private ?string $description = null;
}

Caractéristiques : - Héritage : Différents types d'événements - Planning : Dates de début et fin - Trainer : Propriétaire de l'événement

6. Opportunity (Opportunités)

#[ORM\Entity]
class Opportunity
{
    #[ORM\Id]
    #[ORM\GeneratedValue]
    #[ORM\Column]
    private ?int $id = null;

    #[ORM\ManyToOne(inversedBy: 'opportunities')]
    #[ORM\JoinColumn(nullable: false)]
    private ?Organization $organization = null;

    #[ORM\Column(length: 255)]
    private ?string $name = null;

    #[ORM\Column(type: 'text', nullable: true)]
    private ?string $description = null;

    #[ORM\Column(type: 'datetime_immutable')]
    private ?DateTimeImmutable $dateStart = null;

    #[ORM\Column(type: 'datetime_immutable')]
    private ?DateTimeImmutable $dateEnd = null;

    #[ORM\Column(length: 50)]
    private ?string $status = null;

    #[ORM\Column(type: 'decimal', precision: 10, scale: 2, nullable: true)]
    private ?string $budget = null;

    #[ORM\Column(length: 255, nullable: true)]
    private ?string $location = null;

    /**
     * @var Collection<int, OpportunityProposal>
     */
    #[ORM\OneToMany(mappedBy: 'opportunity', targetEntity: OpportunityProposal::class)]
    private Collection $proposals;
}

Caractéristiques : - Organisation : Créateur de l'opportunité - Planning : Dates et localisation - Budget : Montant disponible - Statut : État de l'opportunité

🔧 Value Objects

1. PhoneNumber

#[ORM\Embeddable]
class PhoneNumber
{
    #[ORM\Column(length: 20)]
    private string $value;

    public function __construct(string $value)
    {
        $this->validate($value);
        $this->value = $value;
    }

    private function validate(string $value): void
    {
        // Validation avec libphonenumber-for-php
        $phoneUtil = PhoneNumberUtil::getInstance();
        $phoneNumber = $phoneUtil->parse($value, 'FR');

        if (!$phoneUtil->isValidNumber($phoneNumber)) {
            throw new InvalidPhoneNumberException('Invalid phone number');
        }
    }

    public function getValue(): string
    {
        return $this->value;
    }

    public function __toString(): string
    {
        return $this->value;
    }
}

Caractéristiques : - Validation : Utilise libphonenumber-for-php - Immutabilité : Pas de modification après création - Embeddable : Intégré dans d'autres entités

📊 Index et contraintes

1. Index de performance

Index critiques (PERF-011) sur les colonnes fréquemment utilisées dans WHERE, JOIN et ORDER BY :

-- ReservationBatch : trainer_id, status, email, response_deadline
CREATE INDEX idx_reservation_batch_trainer ON reservation_batch (trainer_id);
CREATE INDEX idx_reservation_batch_status ON reservation_batch (status);
CREATE INDEX idx_reservation_batch_email ON reservation_batch (email);
CREATE INDEX idx_reservation_batch_deadline ON reservation_batch (response_deadline);

-- Event : trainer_id, plage de dates (planning)
CREATE INDEX idx_event_trainer ON event (trainer_id);
CREATE INDEX idx_event_dates ON event (date_start, date_end);

-- TrainerOrganizationAssignment : organization_id seul (trainer_id couvert par UniqueConstraint)
CREATE INDEX idx_toa_organization ON trainer_organization_assignment (organization_id);

Autres index :

-- Index sur les emails (unique)
CREATE UNIQUE INDEX UNIQ_IDENTIFIER_EMAIL ON "user" (email);

-- Index sur les providers OAuth
CREATE INDEX IDX_USER_OAUTH_PROVIDER ON "user" (oauthProvider, oauthProviderId);

-- Index sur les opportunités par organisation
CREATE INDEX IDX_OPPORTUNITY_ORG ON opportunity (organization_id, status);

-- Index sur les candidatures par formateur
CREATE INDEX IDX_CANDIDACY_TRAINER ON candidacy (trainer_id, status);

Index PERF-011 (Version20260316205032) – entités critiques :

Table Index Colonnes Usage
reservation_batch idx_reservation_batch_trainer trainer_id findByTrainer
reservation_batch idx_reservation_batch_status status findByTrainerNewAndPending
reservation_batch idx_reservation_batch_email email findByEmail
reservation_batch idx_reservation_batch_deadline response_deadline requêtes sur échéances
event idx_event_trainer trainer_id planning par formateur
event idx_event_dates date_start, date_end plage de dates
trainer_organization_assignment idx_toa_organization organization_id lookups par organisation

2. Contraintes de données

-- Contrainte sur les rôles
ALTER TABLE "user" ADD CONSTRAINT check_roles CHECK (roles IS NOT NULL AND array_length(roles, 1) > 0);

-- Contrainte sur les dates d'événements
ALTER TABLE event ADD CONSTRAINT check_event_dates CHECK (date_start < date_end);

-- Contrainte sur les montants
ALTER TABLE opportunity ADD CONSTRAINT check_budget CHECK (budget IS NULL OR budget > 0);

-- Contrainte sur les statuts
ALTER TABLE opportunity ADD CONSTRAINT check_status CHECK (status IN ('draft', 'published', 'closed', 'cancelled'));

🔄 Migrations

1. Structure des migrations

// Exemple : Version20250808213946.php
final class Version20250808213946 extends AbstractMigration
{
    public function up(Schema $schema): void
    {
        // Création de la table user
        $this->addSql(<<<'SQL'
            CREATE TABLE "user" (
                id SERIAL NOT NULL,
                firstname VARCHAR(255) DEFAULT NULL,
                lastname VARCHAR(255) DEFAULT NULL,
                email VARCHAR(180) NOT NULL,
                roles JSON NOT NULL,
                password VARCHAR(255) NOT NULL,
                oauthProvider VARCHAR(255) DEFAULT NULL,
                oauthProviderId VARCHAR(255) DEFAULT NULL,
                acceptCgv BOOLEAN DEFAULT false,
                acceptOptIn BOOLEAN DEFAULT false,
                verifiedEmail BOOLEAN DEFAULT false NOT NULL,
                finishedOnBoarding BOOLEAN DEFAULT false NOT NULL,
                type VARCHAR(255) NOT NULL,
                PRIMARY KEY(id)
            )
        SQL);

        // Contraintes d'unicité
        $this->addSql('CREATE UNIQUE INDEX UNIQ_IDENTIFIER_EMAIL ON "user" (email)');
        $this->addSql('CREATE UNIQUE INDEX UNIQ_USER_OAUTH_PROVIDER_ID ON "user" (oauthProviderId)');
    }

    public function down(Schema $schema): void
    {
        $this->addSql('DROP TABLE "user"');
    }
}

2. Commandes de migration

# Générer une migration
php bin/console doctrine:migrations:diff

# Exécuter les migrations
php bin/console doctrine:migrations:migrate

# Annuler la dernière migration
php bin/console doctrine:migrations:migrate prev

3. Migrations récentes (exemples)

  • Version20260430120000 (FEAT-087) : trainer.first_real_reservation_received_at (TIMESTAMP NULL) — date de la 1ère vraie reservation batch pendant la fenêtre sample data.
  • Version20260430153000 (DISC-019 / FEAT-107) : process.created_at + process.ended_at ; sur calendar_configurator ajout de current_sync_process_id (FK), consecutive_failures, last_sync_end_date.
  • Version20260429120000 (FEAT-086) : user.registered_at (NOT NULL, back-fill NOW()) ; sur trainer : checklist_dismissed_at, onboarding_completed_at, sample_data_dismissed_at ; back-fill onboarding_completed_at quand jsonb_array_length(onboarding_completed_steps::jsonb) = 5.
  • Version20260404190000 (FEAT-050) : reservation_batch.staff_last_view_at (TIMESTAMP TZ NULL) — dernière consultation de la réponse par un utilisateur staff ; sert au flag isNewResponse côté API école.
  • Version20260316205032 (PERF-011) : index de performance sur entités critiques. reservation_batch : trainer_id, status, email, response_deadline. event : trainer_id, date_start/date_end. trainer_organization_assignment : organization_id.
  • Version20260219120000 (FEAT-003) : ajout de intervention_date (DATE NULL) sur invoicing_invoice_line_item. Date de l'intervention dupliquée depuis l'événement pour traçabilité de la facture (non modifiable par API).
  • Version20260303100000 (FEAT-010) : ajout de public_id (VARCHAR 10, UNIQUE, NOT NULL) sur invoicing_invoice. ID technique court de 10 caractères alphanumériques (alphabet sans ambiguïté), généré à la création, distinct du numéro de facture légal (code). Permet de référencer les factures même en brouillon.
  • Version20260305140000 (BUG-031) : numérotation facture art. 289 CGI. Sur trainer_billing_settings : invoice_prefix_template, invoice_next_sequence, invoice_last_resolved_prefix, invoice_sequence_locked, invoice_locked_year. Migration des compteurs depuis trainer.last_invoice_number.
  • Version20260306120000 (UX-039) : numérotation CRA sur trainer_billing_settings (même moteur que factures) : activity_report_prefix_template, activity_report_next_sequence, activity_report_last_resolved_prefix, activity_report_sequence_locked, activity_report_locked_year. Migration des séquences depuis trainer.last_activity_report_number.
  • Version20260306150000 (Brevo #20) : ajout de send_trainer_summary_organization_info_date (TIMESTAMP NULL) sur calendar_configurator. Sert de flag d'idempotence pour l'email de synthèse organisation envoyé au formateur après validation de ses tarifs. Si non-null, l'email n'est pas renvoyé pour ce couple trainer/organisation.
  • Version20260303140000 (BUG-034) : relation CRA↔Facture en ManyToOne. Suppression des contraintes UNIQUE sur invoicing_invoice(activity_report_id), invoicing_activity_report_line_item(intervention_event_id) et invoicing_invoice_line_item(intervention_event_id) ; ajout d’index simples pour les jointures. Un CRA peut avoir plusieurs factures (ex. correction) ; un même événement peut apparaître dans plusieurs CRA et plusieurs factures.
  • Version20260219140000 (FACT-07) : sur trainer_company, ajout de default_vat_rate, default_unit, payment_term, legal_mention_vat_exempt, legal_mention_payment (paramètres de facturation par défaut et mentions légales).
  • Version20260326120000 inclut aussi la suppression sur trainer_company de default_legal_mentions, email_template, logo_url (déjà portés par trainer_billing_settings).
# Voir le statut des migrations
php bin/console doctrine:migrations:status

# Valider le schéma
php bin/console doctrine:schema:validate

🎯 Optimisations

1. Requêtes optimisées

// Repository avec requêtes optimisées
class TrainerRepository extends ServiceEntityRepository
{
    public function findWithEvents(int $trainerId): ?Trainer
    {
        return $this->createQueryBuilder('t')
            ->leftJoin('t.events', 'e')
            ->addSelect('e')
            ->where('t.id = :trainerId')
            ->setParameter('trainerId', $trainerId)
            ->getQuery()
            ->getOneOrNullResult();
    }

    public function findWithIncomeData(int $trainerId, DateTimeImmutable $startDate, DateTimeImmutable $endDate): array
    {
        return $this->createQueryBuilder('t')
            ->leftJoin('t.invoices', 'i')
            ->leftJoin('i.interventionEvent', 'ie')
            ->addSelect('i', 'ie')
            ->where('t.id = :trainerId')
            ->andWhere('i.issueDate BETWEEN :startDate AND :endDate')
            ->setParameter('trainerId', $trainerId)
            ->setParameter('startDate', $startDate)
            ->setParameter('endDate', $endDate)
            ->getQuery()
            ->getResult();
    }
}

2. Cache Doctrine

# config/packages/doctrine.yaml
doctrine:
    orm:
        metadata_cache_driver:
            type: pool
            pool: doctrine.system_cache_pool
        query_cache_driver:
            type: pool
            pool: doctrine.system_cache_pool
        result_cache_driver:
            type: pool
            pool: doctrine.result_cache_pool

🔒 Sécurité des données

1. Chiffrement des données sensibles

// Exemple : Chiffrement du CV
class Trainer extends User
{
    #[ORM\Column(type: 'encrypted_text', nullable: true)]
    private ?string $cv = null;

    #[ORM\Column(type: 'encrypted_json', nullable: true)]
    private ?array $cvAnalysis = null;
}

2. Audit des modifications

// Trait pour l'audit
trait AuditableTrait
{
    #[ORM\Column(type: 'datetime_immutable')]
    private DateTimeImmutable $createdAt;

    #[ORM\Column(type: 'datetime_immutable')]
    private DateTimeImmutable $updatedAt;

    #[ORM\PrePersist]
    public function setCreatedAt(): void
    {
        $this->createdAt = new DateTimeImmutable();
        $this->updatedAt = new DateTimeImmutable();
    }

    #[ORM\PreUpdate]
    public function setUpdatedAt(): void
    {
        $this->updatedAt = new DateTimeImmutable();
    }
}

📈 Monitoring et maintenance

1. Requêtes de monitoring

-- Taille des tables
SELECT 
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size
FROM pg_tables 
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

-- Index non utilisés
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes 
WHERE idx_scan = 0
ORDER BY schemaname, tablename;

-- Requêtes lentes
SELECT 
    query,
    calls,
    total_time,
    mean_time,
    rows
FROM pg_stat_statements 
ORDER BY mean_time DESC 
LIMIT 10;

2. Maintenance automatique

-- Nettoyage des tokens expirés
DELETE FROM password_reset_request 
WHERE expires_at < NOW();

DELETE FROM auth_code 
WHERE expires_at < NOW();

-- Archivage des événements anciens
INSERT INTO event_archive 
SELECT * FROM event 
WHERE date_end < NOW() - INTERVAL '2 years';

DELETE FROM event 
WHERE date_end < NOW() - INTERVAL '2 years';

Cette architecture de base de données garantit la performance, la sécurité et la maintenabilité du système Teadle.