Modèle de données¶

Source de vérité du schéma de base de données. Dérivé des entités Doctrine PHP — les entités font autorité sur ce document en cas de divergence.

Diagrammes ER¶

Le modèle est découpé en trois domaines pour rester lisible. Les entités partagées entre domaines (Candidate, JobOffer, Skill, CultureValue) sont répétées dans les diagrammes qui les concernent.

1. Identité & Authentification¶

erDiagram
    User ||--|| Candidate : "CTI subtype"
    User ||--|| Recruiter : "CTI subtype"
    User ||--o{ RefreshToken : "has"
    User ||--o{ EmailVerificationToken : "has"
    Company ||--o{ Recruiter : "employs"

2. Employment Passport¶

erDiagram
    Candidate ||--|| Passport : "has one"

    Passport ||--o{ PassportTargetJob : "up to 3"
    Passport ||--o{ PassportSkill : "skills + languages"
    Passport ||--o{ PassportPreferredCity : "up to 4"
    Passport ||--o{ PassportQualification : "STI"
    Passport ||--o{ PassportProfessionalExperience : "employment history"
    Passport ||--o{ PassportPersonalProject : "side projects"
    Passport ||--o{ PassportCultureValue : "desired values"

    Nationality ||--o{ Passport : "referenced by"
    City ||--o{ Passport : "city of residence"
    City ||--o{ PassportPreferredCity : "preferred city"
    City ||--o{ PassportProfessionalExperience : "experience city"

    Skill ||--o{ PassportSkill : "referenced by"
    CultureValue ||--o{ PassportCultureValue : "desired by"

3. Offres, Candidatures & Matching¶

erDiagram
    Company ||--o{ JobOffer : "posts"
    Recruiter ||--o{ JobOffer : "creates"
    Company ||--o{ CompanyCultureValue : "culture"
    CultureValue ||--o{ CompanyCultureValue : "adopted by"

    JobOffer ||--o{ JobOfferSkill : "requires"
    JobOffer ||--o{ JobOfferLanguage : "requires language"
    JobOffer ||--o{ PipelineStage : "defines"
    Skill ||--o{ JobOfferSkill : "referenced by"

    RomeJob ||--o{ RomeJobSkill : "requires"
    RomeJob ||--o{ RomeJobAppellation : "also known as"
    Skill ||--o{ RomeJobSkill : "suggested for"

    Candidate ||--o{ Application : "submits"
    JobOffer ||--o{ Application : "receives"
    Application }o--o| PipelineStage : "current stage"
    Application ||--o{ ApplicationStageHistory : "history"
    PipelineStage ||--o{ ApplicationStageHistory : "referenced in"

    Candidate ||--o{ MatchScore : "scored against"
    JobOffer ||--o{ MatchScore : "scored against"

Entités¶

`users`¶

Table abstraite CTI (Class Table Inheritance). Contient toute l'identité et l'authentification. Les données domaine vivent dans candidates et recruiters.

Colonne	Type	Notes
`id`	UUID PK	UUID v7 — ordonné
`type`	VARCHAR(20)	Discriminateur CTI : `candidate` / `recruiter`
`email`	VARCHAR(255) UNIQUE
`first_name`	VARCHAR(100)
`last_name`	VARCHAR(100)
`password`	VARCHAR nullable	null pour futurs utilisateurs OAuth
`email_verified_at`	TIMESTAMPTZ nullable	null = non vérifié
`oauth_provider`	VARCHAR(50) nullable	anticipé : google / linkedin
`oauth_id`	VARCHAR(255) nullable	anticipé
`created_at`	TIMESTAMPTZ
`updated_at`	TIMESTAMPTZ
`last_login_at`	TIMESTAMPTZ nullable	mis à jour à chaque login — filtre fraîcheur matching
`deleted_at`	TIMESTAMPTZ nullable	soft delete RGPD

Pourquoi CTI ? Candidate et Recruiter partagent la logique auth (hachage mot de passe, JWT, vérification email). CTI maintient un UserProvider Symfony unique et un seul firewall. Les données domaine restent dans des tables propres et non creuses.

`refresh_tokens`¶

Géré par gesdinet/jwt-refresh-token-bundle. Un token par session active. Rotation à chaque utilisation — l'ancien token est supprimé et un nouveau émis. Si un token déjà rotaté est soumis, tous les tokens de l'utilisateur sont révoqués immédiatement (détection de vol).

Colonne	Type	Notes
`id`	INT AUTO_INCREMENT PK	Défaut bundle
`refresh_token`	VARCHAR(128) UNIQUE	Token opaque
`username`	VARCHAR(255)	Email utilisateur
`valid`	TIMESTAMPTZ	Expiration — 30 jours (candidate) ou session (recruiter)

`email_verification_tokens`¶

Tokens à usage unique envoyés par email. Un resend révoque tous les tokens précédents (via used_at).

Colonne	Type	Notes
`id`	UUID PK
`user_id`	UUID FK → users
`token`	VARCHAR(64) UNIQUE	Hex crypto-random
`expires_at`	TIMESTAMPTZ	created_at + 24 heures
`used_at`	TIMESTAMPTZ nullable	null = actif
`created_at`	TIMESTAMPTZ

`candidates`¶

Données domaine uniquement. L'identité (prénom, nom, email) est héritée de users via CTI.

Colonne	Type	Notes
`id`	UUID PK FK → users	PK partagé, pattern CTI

`passports`¶

Un passport par candidate. Contient toutes les réponses d'onboarding scalaires ou tableaux. Les sous-données structurées vivent dans des tables dédiées.

Colonne	Type	Notes
`id`	UUID PK
`candidate_id`	UUID FK UNIQUE	1:1 avec candidates
`has_experiences`	BOOLEAN nullable	null = skippé, false = "j'en ai aucune", true = a des entrées
`has_qualifications`	BOOLEAN nullable	null = skippé, false = "aucune", true = a des entrées
`is_gender_visible`	BOOLEAN DEFAULT false	Toggle privacy Q2
`is_birth_date_visible`	BOOLEAN DEFAULT false	Toggle privacy Q3
`is_nationality_visible`	BOOLEAN DEFAULT false	Toggle privacy Q4
`is_city_visible`	BOOLEAN DEFAULT false	Toggle privacy Q5
`gender`	VARCHAR(20) nullable	Q2 — enum PHP `Gender`
`birth_date`	DATE nullable	Q3
`nationality_id`	UUID FK → nationalities nullable	Q4
`city_of_residence_id`	INT FK → cities nullable	Q5 — référence GeoNames
`current_status`	VARCHAR(20) nullable	Q6 — enum `CurrentStatus`
`market_positioning`	VARCHAR(30) nullable	Q6 — enum `MarketPositioning`
`work_mode`	VARCHAR(20) nullable	Q13 — enum `WorkArrangement`
`preferred_company_sizes`	JSON nullable	Q14 — array
`preferred_sectors`	JSON nullable	Q14 — max 5
`availability`	VARCHAR(20) nullable	Q15 — enum `Availability`
`minimum_salary`	INTEGER nullable	Q15 — brut €/an
`profile_photo_url`	VARCHAR(500) nullable	Q17
`is_reconverting`	BOOLEAN DEFAULT false	Calculé — distance ROME entre poste actuel et jobs cibles
`is_open_to_relocation`	BOOLEAN DEFAULT false	Q13 — mobile géographiquement
`created_at`	TIMESTAMPTZ
`updated_at`	TIMESTAMPTZ

Séniorité : calculée dynamiquement dans Passport::getSeniorityLevel() à partir des années d'expérience cumulées (périodes fusionnées). Seuils : 0–2 ans → junior, 2–5 → mid, 5–10 → senior, 10+ → executive. Non stockée en DB.

`passport_target_jobs`¶

Q7 — jusqu'à 3 métiers cibles par passport, structurés ROME.

Colonne	Type	Notes
`id`	UUID PK
`passport_id`	UUID FK
`rome_code`	VARCHAR(6)	Indexé — utilisé dans BLOC 1
`label`	VARCHAR(150)	Label d'affichage

`passport_skills`¶

Q11/Q12 — compétences et langues sélectionnées depuis le catalogue WonderWork.

Colonne	Type	Notes
`id`	UUID PK
`passport_id`	UUID FK
`skill_id`	UUID FK → skills	Vocabulaire contrôlé
`proficiency`	VARCHAR(10) nullable	CEFR a1–c2/native — uniquement pour `skill.category = 'language'`
`is_top`	BOOLEAN DEFAULT false	Boost ×1.20 dans BLOC 2 — Top 5 tech, Top 3 soft skills

`passport_preferred_cities`¶

Q13 — jusqu'à 4 villes préférées par passport.

Colonne	Type	Notes
`id`	UUID PK
`passport_id`	UUID FK
`city_id`	INT FK → cities	Référence GeoNames
`position`	SMALLINT NOT NULL	1 = plus préférée. Priorité ordinale pour BLOC 5

`passport_qualifications`¶

Q10 — diplômes, formations et certifications. Single Table Inheritance.

Colonne	Type	Notes
`id`	UUID PK
`passport_id`	UUID FK
`type`	VARCHAR(20)	Discriminateur STI : `academic` / `self_taught` / `certification`
`education_level`	VARCHAR(20) nullable	academic uniquement — `lycee` à `bac_8`
`field_of_study`	VARCHAR(150) nullable	academic uniquement
`diploma_name`	VARCHAR(200) nullable	academic uniquement
`institution_name`	VARCHAR(200) nullable	academic uniquement
`year`	SMALLINT NOT NULL	Année d'obtention — tous types
`domain`	VARCHAR(150) nullable	self_taught uniquement
`learning_methods`	JSON nullable	self_taught — array de méthodes
`certification_name`	VARCHAR(200) nullable	certification uniquement

`passport_professional_experiences`¶

Q10 — historique professionnel (emplois).

Colonne	Type	Notes
`id`	UUID PK
`passport_id`	UUID FK
`rome_code`	VARCHAR(6) NOT NULL	Indexé — BLOC 3 + détection reconversion
`job_title`	VARCHAR(150) NOT NULL
`company_name`	VARCHAR(150) NOT NULL
`sector`	VARCHAR(150) nullable
`city_id`	INT FK → cities nullable	null si remote
`contract_type`	VARCHAR(30) NOT NULL	enum `ContractType`
`start_year`	SMALLINT NOT NULL
`start_month`	SMALLINT NOT NULL	1–12
`end_year`	SMALLINT nullable	null si is_current = true
`end_month`	SMALLINT nullable	null si is_current = true
`is_current`	BOOLEAN NOT NULL	Poste actuel — pilote la séniorité
`is_remote`	BOOLEAN NOT NULL	100% remote → city_id est null
`created_at`	TIMESTAMPTZ

Pourquoi année/mois smallint plutôt que DATE ? Les candidats connaissent le mois et l'année, pas le jour exact. Stocker un DATE avec une convention de jour forcé serait un contrat implicite.

`passport_personal_projects`¶

Q10 — projets personnels (side projects). Pas de dates.

Colonne	Type	Notes
`id`	UUID PK
`passport_id`	UUID FK
`rome_code`	VARCHAR(6) NOT NULL	Domaine du projet — suggestions de compétences inline
`project_name`	VARCHAR(200) NOT NULL
`description`	TEXT nullable
`link`	VARCHAR(500) nullable	GitHub, portfolio, app
`created_at`	TIMESTAMPTZ

`passport_culture_values`¶

Junction — many-to-many entre passports et culture_values.

Colonne	Type	Notes
`passport_id`	UUID FK passports.id	PK composite
`culture_value_id`	UUID FK culture_values.id	PK composite

`companies`¶

Colonne	Type	Notes
`id`	UUID PK
`name`	VARCHAR(200)
`logo_url`	VARCHAR(500) nullable
`size`	VARCHAR(20) nullable	startup / small / medium / large / enterprise
`sector`	VARCHAR(150) nullable
`description`	TEXT nullable
`website`	VARCHAR(500) nullable
`founded_year`	SMALLINT nullable
`employee_count`	INTEGER nullable
`created_at`	TIMESTAMPTZ
`updated_at`	TIMESTAMPTZ
`deleted_at`	TIMESTAMPTZ nullable	soft delete

`recruiters`¶

Données domaine uniquement. L'identité est héritée de users via CTI.

Colonne	Type	Notes
`id`	UUID PK FK → users	PK partagé
`company_id`	UUID FK
`role`	VARCHAR(20)	`owner` / `recruiter`

`job_offers`¶

Colonne	Type	Notes
`id`	UUID PK
`company_id`	UUID FK
`recruiter_id`	UUID FK	Créé par
`rome_code`	VARCHAR(6)	Indexé — BLOC 1
`rome_label`	VARCHAR(150) nullable	Label d'affichage
`location`	VARCHAR(200)	Label texte — "Paris 15e, Île-de-France"
`location_geonames_id`	INTEGER nullable	ID GeoNames — BLOC 5 géolocalisation
`contract_type`	VARCHAR(20)	enum `ContractType`
`work_time`	VARCHAR(20)	enum `WorkTime` : full_time / part_time
`work_arrangement`	VARCHAR(20)	enum `WorkArrangement` : remote / hybrid / on_site
`target_availability`	VARCHAR(20) nullable	enum `Availability`
`experience_level`	VARCHAR(20) nullable	enum `ExperienceLevel` : junior / mid / senior / executive
`contract_duration`	VARCHAR(100) nullable	CDD/interim/stage uniquement
`salary_min`	INTEGER nullable	Affichage uniquement
`salary_max`	INTEGER nullable	offer.max utilisé dans BLOC 5
`display_salary`	BOOLEAN DEFAULT true	Masquer le salaire aux candidats
`description`	TEXT
`status`	VARCHAR(20)	draft / published / closed
`published_at`	TIMESTAMPTZ nullable
`closing_at`	TIMESTAMPTZ nullable	Date de clôture de l'offre
`created_at`	TIMESTAMPTZ
`updated_at`	TIMESTAMPTZ
`deleted_at`	TIMESTAMPTZ nullable	soft delete

`job_offer_skills`¶

Compétences requises par offre. Alimente BLOC 2.

Colonne	Type	Notes
`id`	UUID PK
`job_offer_id`	UUID FK
`skill_id`	UUID FK → skills	Vocabulaire contrôlé
`importance`	VARCHAR(20)	critical / important / nice_to_have
`level_required`	VARCHAR(20) nullable	beginner / intermediate / advanced / expert
`proficiency_required`	VARCHAR(10) nullable	CEFR a1–c2/native — pour les langues

`job_offer_languages`¶

Langues requises par offre. Entité distincte de job_offer_skills — utilise des codes ISO 639-1 directs sans passer par le catalogue skills.

Colonne	Type	Notes
`id`	UUID PK
`job_offer_id`	UUID FK
`language_code`	VARCHAR(5)	Code ISO 639-1 (ex: `fr`, `en`, `de`)
`min_cefr_level`	VARCHAR(6)	Niveau minimum CEFR : a1 / a2 / b1 / b2 / c1 / c2 / native

`pipeline_stages`¶

Étapes de recrutement définies par offre d'emploi.

Colonne	Type	Notes
`id`	UUID PK
`job_offer_id`	UUID FK
`type`	VARCHAR(50)	phone_screen / video_screening / technical_test / live_coding / take_home / case_study / interview / assessment / trial_day / reference_check / background_check / ats_step
`custom_label`	VARCHAR(150) nullable	Affiché à la place du type quand défini
`display_order`	SMALLINT	Ordre d'affichage

`applications`¶

Colonne	Type	Notes
`id`	UUID PK
`candidate_id`	UUID FK
`job_offer_id`	UUID FK
`current_stage_id`	UUID FK nullable → pipeline_stages
`status`	VARCHAR(20)	enum `ApplicationStatus`
`peak_status`	VARCHAR(20)	Statut le plus avancé atteint — KPI
`created_at`	TIMESTAMPTZ
`updated_at`	TIMESTAMPTZ
`deleted_at`	TIMESTAMPTZ nullable	soft delete

Contrainte UNIQUE sur (candidate_id, job_offer_id) — une candidature par candidat par offre.

Cycle de vie des statuts :

[Candidat]  sent ───────────────────────────────→ withdrawn
              ↓ (recruteur ouvre)
            viewed
              ↓ (recruteur shortliste)
          shortlisted ───────────────────────────→ rejected
              ↓ (recruteur déplace en pipeline)
           in_process ──────────────────────────→ rejected
              ↓ (recruteur envoie proposition)
          offer_pending
              ↓                  ↓
            hired             declined  (candidat)

`application_stage_history`¶

Trace immuable des transitions de statut.

Colonne	Type	Notes
`id`	UUID PK
`application_id`	UUID FK
`stage_id`	UUID FK nullable → pipeline_stages	null pour changements pré-pipeline
`status`	VARCHAR(20)	Snapshot du statut à ce moment
`message`	TEXT nullable	Message optionnel (500 chars max)
`created_at`	TIMESTAMPTZ	Immuable — pas d'updated_at

`match_scores`¶

Scores pré-calculés. Jamais calculés à la demande — toujours lus depuis cette table.

Colonne	Type	Notes
`id`	UUID PK
`candidate_id`	UUID FK
`job_offer_id`	UUID FK
`global_score`	FLOAT nullable	null = pas encore calculé ("Score incoming")
`score_bloc_1`	FLOAT nullable	Intitulé de poste (20%)
`score_bloc_2`	FLOAT nullable	Compétences (41%)
`score_bloc_3`	FLOAT nullable	Expérience & séniorité (17%)
`score_bloc_4`	FLOAT nullable	Valeurs culturelles (10%)
`score_bloc_5`	FLOAT nullable	Logistique (7%)
`score_bloc_6`	FLOAT nullable	Langues (5%)
`confidence_level`	VARCHAR(20) nullable	very_high / high / medium / low
`computed_at`	TIMESTAMPTZ nullable
`created_at`	TIMESTAMPTZ
`updated_at`	TIMESTAMPTZ

Contrainte UNIQUE sur (candidate_id, job_offer_id).

null vs 0 : global_score = null signifie "pas encore calculé" (UI "Score incoming"). global_score = 0 signifie "calculé, incompatible". Ne jamais confondre les deux.

Données de référence¶

`skills`¶

Catalogue contrôlé maintenu par WonderWork. Deux sources : ROME 4.0 (compétences France Travail) + skills custom WonderWork (outils, technos).

Colonne	Type	Notes
`id`	UUID PK
`label`	VARCHAR(150)
`code`	VARCHAR(10) nullable	ISO 639-1 pour les langues (`fr`, `en`), null sinon
`source`	VARCHAR(20)	`rome` / `wonderwork`
`category`	VARCHAR(20)	`competence` / `knowledge` / `soft_skill` / `language`
`description`	TEXT nullable
`created_at`	TIMESTAMPTZ

`cities`¶

Dataset GeoNames — ~140 000 villes (population > 1 000). Seedé offline depuis l'export GeoNames cities1000.

Colonne	Type	Notes
`geonames_id`	INTEGER PK	Identifiant GeoNames stable
`name`	VARCHAR(200)	Indexé GIN trigram (`pg_trgm`) pour autocomplete
`region`	VARCHAR(200) nullable	1ère division administrative
`country_code`	VARCHAR(2)	ISO 3166-1 alpha-2
`population`	INTEGER	Tri par pertinence dans l'autocomplete

`rome_jobs` / `rome_job_appellations` / `rome_job_skills`¶

Référentiel ROME 4.0 (~1 584 fiches métiers, ~13 000 appellations). Importé localement depuis l'API France Travail.

`culture_values`¶

Vocabulaire contrôlé des valeurs culturelles. Partagé entre candidats (passport_culture_values) et entreprises (company_culture_values) pour permettre le matching exact dans BLOC 4.

`nationalities`¶

~250 nationalités (ISO 3166-1). Utilisées dans Q4 du passport.

Architecture de pré-calcul¶

flowchart TD
    T1["TRIGGER\npassport.saved"] --> PF
    T2["TRIGGER\njob.published"] --> PF

    PF["Pre-filter\n(checks rapides)"]
    PF -->|"work_arrangement incompatible"| Z["score = 0\n(incompatible)"]
    PF -->|"cand.min > offer.max"| Z
    PF -->|"last_login > 90 jours"| SKIP["pas de ligne\n(freshness)"]
    PF -->|survivants| Q

    Q["Symfony Messenger\nqueue async"]
    Q --> W["Worker\n(6 blocs)"]
    W --> DB["UPSERT match_scores\n(global_score, bloc_1..6,\nconfidence_level, computed_at)"]

    DB --> API["GET /v1/me/matches\nSELECT * FROM match_scores\nORDER BY global_score DESC NULLS LAST"]

Index clés¶

-- Hot path matching
CREATE INDEX idx_match_scores_candidate ON match_scores(candidate_id, global_score DESC);
CREATE INDEX idx_match_scores_offer     ON match_scores(job_offer_id, global_score DESC);

-- BLOC 1 — ROME matching
CREATE INDEX idx_passport_target_jobs_rome ON passport_target_jobs(rome_code);
CREATE INDEX idx_job_offers_rome           ON job_offers(rome_code);

-- BLOC 2 + 6 — Skills matching
CREATE INDEX idx_passport_skills_passport  ON passport_skills(passport_id);
CREATE INDEX idx_passport_skills_skill     ON passport_skills(skill_id);
CREATE INDEX idx_job_offer_skills_offer    ON job_offer_skills(job_offer_id);
CREATE INDEX idx_job_offer_skills_skill    ON job_offer_skills(skill_id);

-- BLOC 4 — Culture values
CREATE INDEX idx_company_culture_values    ON company_culture_values(company_id);
CREATE INDEX idx_passport_culture_values   ON passport_culture_values(passport_id);

-- Autocomplete villes (pg_trgm)
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_cities_name_trgm ON cities USING GIN (name gin_trgm_ops);

-- Soft delete
CREATE INDEX idx_candidates_deleted  ON candidates(deleted_at) WHERE deleted_at IS NULL;
CREATE INDEX idx_job_offers_status   ON job_offers(status, deleted_at) WHERE deleted_at IS NULL;

Modèle de données¶

Diagrammes ER¶

1. Identité & Authentification¶

2. Employment Passport¶

3. Offres, Candidatures & Matching¶

Entités¶

users¶

refresh_tokens¶

email_verification_tokens¶

candidates¶

passports¶

passport_target_jobs¶

passport_skills¶

passport_preferred_cities¶

passport_qualifications¶

passport_professional_experiences¶

passport_personal_projects¶

passport_culture_values¶

companies¶

recruiters¶

job_offers¶

job_offer_skills¶

job_offer_languages¶

pipeline_stages¶

applications¶

application_stage_history¶

match_scores¶