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{ ApplicationEvent : "events"
    PipelineStage ||--o{ ApplicationEvent : "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)	enum JobOfferStageType (7 valeurs) : screening / interview / assessment / trial_day / reference_check / background_check / ats_step (système, non sélectionnable)
custom_label	VARCHAR(150) nullable	Affiché à la place du type quand défini
display_order	SMALLINT	Ordre d'affichage
default_mode	VARCHAR(10) nullable	StageMode : onsite / video / phone. Défaut d'offre pré-rempli dans le formulaire de planif.
default_location	VARCHAR(255) nullable	Adresse par défaut (étapes présentiel)
default_meeting_link	VARCHAR(2048) nullable	Lien par défaut (étapes à distance)
default_duration	SMALLINT nullable	Durée par défaut en minutes
default_interlocutor	VARCHAR(255) nullable	Interlocuteur par défaut (retombe sur le recruteur connecté à la planif)
default_instructions	TEXT nullable	Consignes par défaut affichées au candidat
default_format	VARCHAR(10) nullable	assessment uniquement : live / take_home

---

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_events

Journal append-only des événements du cycle de vie : chaque transition de statut (dont sent à la création et viewed au premier view recruteur) et chaque avancement d'étape.

Colonne	Type	Notes
id	UUID PK
application_id	UUID FK
stage_id	UUID FK nullable → pipeline_stages	null pour les événements sans étape
status	VARCHAR(20)	Snapshot du statut à ce moment
message	TEXT nullable	Message optionnel (500 chars max)
rejection_reason	VARCHAR(100) nullable	Code motif de refus. Null hors REJECTED.
withdrawal_reason	VARCHAR(100) nullable	Code motif de retrait. Null hors WITHDRAWN.
withdrawal_reason_detail	TEXT nullable	Détail libre (500 chars max), seulement si withdrawal_reason = other
created_at	TIMESTAMPTZ	Immuable — pas d'updated_at

---

application_stage_notifications

Instance ponctuelle de planification/notification de l'étape courante du candidat, produite par l'action recruteur "Save & notify". Cycle de confirmation mutable, distinct du journal append-only application_events.

Colonne	Type	Notes
id	UUID PK
application_id	UUID FK
stage_id	UUID FK → pipeline_stages
interaction	VARCHAR(10)	StageInteraction : confirm (RDV synchrone) / inform (notice async)
scheduled_at	TIMESTAMPTZ nullable	date et heure du RDV (confirm)
deadline	DATE nullable	échéance (inform, assessment à rendre)
mode	VARCHAR(10) nullable	StageMode : onsite / video / phone (confirm)
location	VARCHAR(255) nullable	snapshot, éditable par candidat
meeting_link	VARCHAR(2048) nullable	snapshot
duration	SMALLINT nullable	snapshot, minutes
interlocutor	VARCHAR(255) nullable	snapshot
note	TEXT nullable	snapshot, consignes / message au candidat (500 chars max)
confirmation_status	VARCHAR(15) nullable	StageConfirmationStatus : pending / confirmed / not_available. Null si inform. Écrit côté candidat.
candidate_message	TEXT nullable	message du candidat si not_available (500 chars max). Écrit côté candidat.
requested_at	TIMESTAMPTZ	posé/bumpé à chaque notify, pilote l'ordre d'épinglage candidat
responded_at	TIMESTAMPTZ nullable	horodatage de la réponse candidat
cancelled_at	TIMESTAMPTZ nullable	posé quand le recruteur quitte l'étape par avancement

Une notification active par (application, étape courante) : le reschedule upsert la ligne et repasse confirmation_status à pending.

---

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;