WonderWork — Data Model

Source of truth for the database schema. Decisions documented with rationale. Generated after full structural analysis — March 2026.

---

Entity Relationship Diagram

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

    Candidate ||--|| Passport : "has one"
    Candidate ||--o{ Bubble : "saved searches (≤20)"
    Passport ||--o{ PassportTargetJob : "Q7 — up to 3"
    Passport ||--o{ PassportSkill : "Q11 inline + Q12 review"
    Passport ||--o{ PassportPreferredCity : "Q13 — up to 4"
    Passport ||--o{ PassportQualification : "Q10 — STI"
    Passport ||--o{ PassportProfessionalExperience : "Q10 — employment history"
    Passport ||--o{ PassportPersonalProject : "Q10 — side projects"
    Passport ||--o{ PassportCultureValue : "Q14 — desired values"

    City ||--o{ Passport : "city of residence"
    City ||--o{ PassportPreferredCity : "preferred city"
    City ||--o{ PassportProfessionalExperience : "experience city"
    City ||--o{ PassportQualification : "institution city"

    SkillFamily ||--o{ Skill : "groups (thematic family)"
    Skill ||--o{ PassportSkill : "referenced by"
    Skill ||--o{ JobOfferSkill : "referenced by"
    Skill ||--o{ RomeJobSkill : "suggested for"
    RomeJob ||--o{ RomeJobSkill : "requires"
    RomeJob ||--o{ RomeJobAppellation : "also known as"

    Company ||--o{ Recruiter : "employs"
    Company ||--o{ JobOffer : "posts"
    Recruiter ||--o{ JobOffer : "creates"
    Recruiter ||--o{ Recruiter : "invites"

    JobOffer ||--o{ JobOfferSkill : "requires"
    JobOffer ||--o{ JobOfferStage : "defines (recruitment pipeline)"

    CultureValue ||--o{ CompanyCultureValue : "adopted by"
    Company ||--o{ CompanyCultureValue : "culture"
    CultureValue ||--o{ PassportCultureValue : "desired by"

    Candidate ||--o{ Application : "submits"
    JobOffer ||--o{ Application : "receives"
    Application }o--o| JobOfferStage : "current stage"
    Application ||--o{ ApplicationEvent : "events"
    JobOfferStage ||--o{ ApplicationEvent : "referenced in"
    Application ||--o{ ApplicationStageNotification : "scheduling / notice"
    JobOfferStage ||--o{ ApplicationStageNotification : "referenced in"
    ApplicationStageNotification ||--o{ InterviewFeedback : "post-interview feedback"

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

---

Entities

users

Abstract base table for all authenticated users — Class Table Inheritance (CTI). Authentication and identity fields live here. Domain-specific fields live in candidates and recruiters (the admins subtype carries no extra domain fields).

Column	Type	Notes
id	UUID PK
type	VARCHAR(20)	CTI discriminator: candidate / recruiter / admin
email	VARCHAR(255) UNIQUE
password	VARCHAR(255) nullable	null for future OAuth users
email_verified_at	TIMESTAMPTZ nullable	null = unverified
oauth_provider	VARCHAR(50) nullable	anticipated: google / linkedin — not implemented
oauth_id	VARCHAR(255) nullable	anticipated — not implemented
created_at	TIMESTAMPTZ
updated_at	TIMESTAMPTZ
last_login_at	TIMESTAMPTZ nullable	updated on every successful login
deleted_at	TIMESTAMPTZ nullable	soft delete — RGPD

Why CTI over two independent entities? Candidate and Recruiter share auth logic (password hashing, JWT, email verification). CTI keeps a single Symfony UserProvider and a single firewall. Domain-specific data stays in clean, non-sparse subclass tables.

Why not a single users table with a role column? Candidate and Recruiter have fundamentally different domain data — merging them would produce a sparse, semantically ambiguous table.

---

refresh_tokens

Issued by gesdinet/jwt-refresh-token-bundle. One row per active session. Rotated on every use — the old token is deleted and a new one issued. If a previously rotated token is submitted, all tokens for that user are immediately revoked (theft detection).

Column	Type	Notes
id	INT AUTO_INCREMENT PK	bundle default
refresh_token	VARCHAR(128) UNIQUE	opaque token
username	VARCHAR(255)	user email — identifier used by Symfony
valid	TIMESTAMPTZ	expiry — 30 days (candidate) or session (recruiter)

---

email_verification_tokens

One-time use tokens sent by email to verify account ownership. Linked to User (not Candidate) — works for both roles. Multiple tokens may exist per user (via resend), but only the latest is active — a resend revokes all previous tokens by setting used_at.

Column	Type	Notes
id	UUID PK
user_id	UUID FK → users
token	VARCHAR(64) UNIQUE	crypto-random hex string
expires_at	TIMESTAMPTZ	created_at + 24 hours
used_at	TIMESTAMPTZ nullable	null = active, set when consumed or revoked
created_at	TIMESTAMPTZ

---

candidates

Domain-specific data only. Authentication fields inherited from users via CTI.

Column	Type	Notes
id	UUID PK FK → users	shared PK, CTI pattern
first_name	VARCHAR(100)
last_name	VARCHAR(100)

---

passports

One passport per candidate. Contains all onboarding answers that are scalar or arrays. Structured sub-data lives in dedicated tables.

Column	Type	Notes
id	UUID PK
candidate_id	UUID FK UNIQUE	1:1 with candidates
has_experiences	BOOLEAN nullable	null = skipped, false = "I have none", true = has entries — UX nudging only
has_qualifications	BOOLEAN nullable	null = skipped, false = "I have none", true = has entries — UX nudging only
is_gender_visible	BOOLEAN DEFAULT false	Q2 privacy toggle
is_birth_date_visible	BOOLEAN DEFAULT false	Q3 privacy toggle
is_nationality_visible	BOOLEAN DEFAULT false	Q4 privacy toggle
is_city_visible	BOOLEAN DEFAULT false	Q5 privacy toggle
gender	VARCHAR(50) nullable	Q2 — free text for "Other"
birth_date	DATE nullable	Q3
nationality_id	UUID FK → nationalities nullable	Q4
city_of_residence_id	INTEGER FK → cities nullable	Q5 — GeoNames city reference
current_status	VARCHAR(50) nullable	Q6 — enum: student / active / unemployed / reconverting / freelance / entrepreneur / retired
market_positioning	VARCHAR(50) nullable	Q6 — enum: actively_searching / open / not_looking
work_mode	VARCHAR(20) nullable	Q13 — single value: remote / hybrid / on_site
preferred_company_sizes	JSON nullable	Q14 — array of: startup / small / medium / large / enterprise. null = indifférent. Bonus-only in BLOC 4 (no penalty for mismatch)
preferred_sectors	JSON nullable	Q14 — max 5
availability	VARCHAR(20) nullable	Q15 — now / lt_2w / lt_1m / lt_2m / lt_3m / 6m_plus
minimum_salary	INTEGER nullable	Q15 — brut €/year, cand.min for BLOC 5
profile_photo_url	VARCHAR(500) nullable	Q17
is_reconverting	BOOLEAN DEFAULT false	Computed — ROME distance (most recent is_current experience ROME vs Q7 target jobs)
is_open_to_relocation	BOOLEAN DEFAULT false	Q13 — willing to relocate; used in BLOC 5 location scoring
created_at	TIMESTAMPTZ
updated_at	TIMESTAMPTZ

Why separate from candidates? The passport has 25+ columns, a distinct lifecycle (privacy settings, generation state), and is invisible to recruiters for deleted accounts. Keeping it separate preserves candidates as a lean auth table.

No seniority_level column. Seniority is computed at scoring time from total professional experience years (employment only, overlapping periods merged). Thresholds: 0–2 → junior, 2–5 → mid, 5–10 → senior, 10+ → executive. This avoids self-declaration bias and removes one onboarding question.

No current_job_rome_code / current_job_label columns. The current job is the passport_professional_experiences entry with is_current = true. The matching algorithm extracts it from the experiences collection — no denormalized cache needed. If no current experience exists, seniority defaults to null (neutral score 25/50 in BLOC 3).

Arrays (JSON): preferred_sectors stored as JSON (JSONB in PostgreSQL) — simpler than native TEXT[] with equivalent GIN index support. Culture values are in passport_culture_values junction table for controlled vocabulary matching.

No q10_completed / q11_completed flags: completion is computed on the fly from COUNT(passport_qualifications) and COUNT(passport_professional_experiences) — storing it would be denormalized data with a synchronization risk.

---

passport_target_jobs

Q7 — up to 3 target jobs per passport, ROME-structured.

Column	Type	Notes
id	UUID PK
passport_id	UUID FK
rome_code	VARCHAR(6)	indexed — used in BLOC 1
label	VARCHAR(150)	display label
contract_types	JSONB NOT NULL DEFAULT '[]'	accepted contract types (ContractType[]). Empty = open to all. Drives the matching contract-fit penalty on the best-match target job

---

passport_preferred_cities

Q13 — up to 4 preferred work cities per passport.

Column	Type	Notes
id	UUID PK
passport_id	UUID FK
city_id	INTEGER FK → cities	GeoNames city reference — city name, country, coordinates via JOIN
position	SMALLINT NOT NULL	1 = most preferred. Order defines priority — no separate preference level

No denormalized city columns. City name, country code, and coordinates are accessed via JOIN to cities. Single source of truth — no label/country desync risk.

Position-based priority. The candidate ranks cities by order. Position 1 is the most desired. Used in BLOC 5 location matching — score = 45 - (position - 1) × 4 (position 1 → 45 pts, position 4 → 33 pts). Reordering is done via ↑/↓ buttons in the UI. Cities are conditional on work mode — only shown if candidate selects on_site or hybrid (not remote-only).

---

skills

Controlled vocabulary of skills, wholly owned by WonderWork (proprietary catalogue that replaced the raw France Travail import). No candidate or recruiter additions — the catalog is managed by the WonderWork team. ROME jobs stay the interoperability pivot: skills are linked to ROME jobs via rome_job_skills, but the skills themselves are no longer ROME-sourced.

Column	Type	Notes
id	UUID PK
label	VARCHAR(150)	Normalized display label
code	VARCHAR(10) nullable	Stable identifier — ISO 639-1 for language skills (fr, en), null for all other skills
category	VARCHAR(20) enum	hard_skill / soft_skill / language (PHP SkillCategory enum via Doctrine enumType)
family_id	UUID FK → skill_families, nullable	Thematic family the skill belongs to (one family per skill). Null until assigned by the catalogue generation
search_aliases	JSON (array of strings)	Search synonyms (e.g. ["ReactJS", "React.js"] for React), powering skill search alongside the label. Not exposed in the API
description	TEXT nullable	Description of the skill
created_at	TIMESTAMPTZ

Unique constraint: functional unique index on (LOWER(label), category) — the canonical key of the catalogue (case-insensitive on the label).

Category collapse. The former competence (savoir-faire) and knowledge (savoir) values were merged into hard_skill: the distinction was ROME heritage, used nowhere in the matching, and inconsistent in the data. The thematic family now carries the visual grouping, orthogonally to the category (nature) axis. soft_skill and language are unchanged.

Language skills: Skills with category = 'language' are seeded from shared/data/languages.json. The code column stores the ISO 639-1 code used by the frontend for flag display. Languages are referenced by passport_skills with a CEFR proficiency value — there is no separate passport_languages table.

Why a controlled vocabulary? Free-text skill names make matching unreliable ("React" vs "ReactJS" vs "React.js"). A shared referential enables exact FK matching in BLOC 2 and BLOC 6, removing the need for semantic similarity computation. search_aliases absorbs the wording variants at search time without polluting the canonical label.

---

skill_families

Thematic families used to navigate and group the skill catalogue in the selection UX. A family is not a selection unit — the leaf skill stays the unit of selection and matching; the family only groups.

Column	Type	Notes
id	UUID PK
label	VARCHAR(150)	Family display label
selection_mode	VARCHAR enum	bundle (coherent set owned as a block, opt-in with an optional select-all) / picklist (substitutable alternatives, strict opt-in)
created_at	TIMESTAMPTZ

Why a selection mode? The UX distinguishes families you possess as a whole (a trade's base gestures → bundle) from menus of substitutable alternatives (languages, tools → picklist, never bulk-selected). Populating families onto skills is out of scope of the data-model rework and handled by the catalogue generation.

---

cities

GeoNames cities dataset — ~140 000 cities with population > 1 000, covering all countries. Seeded offline from the GeoNames cities1000 export. Used for city autocomplete across the entire platform.

Column	Type	Notes
geonames_id	INTEGER PK	GeoNames stable identifier
name	VARCHAR(200)	City name — indexed with pg_trgm for autocomplete
region	VARCHAR(200) nullable	First administrative division — region (FR), state (US), Land (DE). Resolved from admin1CodesASCII.txt at import time. Displayed in dropdown: "Paris, Île-de-France, FR"
country_code	VARCHAR(2)	ISO 3166-1 alpha-2
population	INTEGER	Used for relevance sorting in autocomplete results (bigger cities first)

Index: GIN trigram index on name via pg_trgm extension — enables fast ILIKE '%par%' queries in < 5ms.

API endpoint: GET /v1/cities?q=Par&country=FR — debounce 300ms on frontend, returns top 10 results sorted by population descending.

Referenced by: passports.city_of_residence_id, passport_preferred_cities.city_id, passport_professional_experiences.city_id. All city data (name, country, coordinates) is accessed via JOIN — no denormalized labels anywhere.

Why local dataset over external API? Sovereign (no US CLOUD Act exposure), free, performant, scales to any country without API key or rate limits. Same pattern as the ROME catalogue: reference data imported locally, served by our own API.

---

rome_jobs

ROME 4.0 fiches métiers — the official French occupational referential maintained by France Travail. Imported locally via batch Command from the ROME 4.0 API. Used by candidates to select their target job and by recruiters to classify their job offers.

Column	Type	Notes
code	VARCHAR(6) PK	ROME code (e.g. M1805)
label	VARCHAR(200)	Official label (e.g. "Développeur / Développeuse informatique")
skills_curated_at	TIMESTAMPTZ nullable	Set when the job's skills have been regenerated into the proprietary catalogue. Null = not yet curated (still on ROME-imported skills)

~1 584 entries. Updated a few times per year when France Travail publishes new fiches.

Progressive catalogue rollout. skills_curated_at marks which jobs have proprietary curated skills. The /v1/appellations autocomplete only returns curated jobs (skills_curated_at IS NOT NULL), so the platform only ever exposes the new skill catalogue. Non-curated jobs keep their ROME-imported skills inert until the ROME-removal chore purges them. Progress = count(*) WHERE skills_curated_at IS NOT NULL.

---

rome_job_appellations

Variant names for ROME jobs — enables autocomplete when a candidate or recruiter searches for a job by name. One ROME job has 5-15 appellations on average (e.g. M1805 → "Développeur web", "Développeur fullstack", "Ingénieur logiciel", etc.).

Column	Type	Notes
code	VARCHAR(10) PK	OGR code (France Travail identifier)
label	VARCHAR(250)	Appellation label
rome_job_code	VARCHAR(6) FK → rome_jobs	Parent ROME code

~13 000 entries.

---

rome_job_skills

Junction table — WonderWork skills associated to a ROME job, curated by the WonderWork team. The rome in the name qualifies the job (which stays ROME-based), not the skill. Used to pre-fill the skill picker in the job offer creation and passport completion forms when a ROME code is selected.

Column	Type	Notes
skill_id	UUID FK → skills
rome_job_code	VARCHAR(6) FK → rome_jobs	ROME code
PRIMARY KEY	(skill_id, rome_job_code)

Not resolved at runtime. The catalog is imported via batch Command — no live API call on form load.

---

passport_skills

Q14 — skills selected from the WonderWork skill catalog.

Column	Type	Notes
id	UUID PK
passport_id	UUID FK
skill_id	UUID FK → skills	controlled vocabulary — replaces free-text skill_name
proficiency	VARCHAR(10) nullable	a1 / a2 / b1 / b2 / c1 / c2 / native — only set when skill.category = 'language', null otherwise
is_top	BOOLEAN DEFAULT false	boost ×1.20 in BLOC 2 — Top 5 for technical skills, Top 3 for soft skills

No skill level for non-language skills. Proficiency self-assessment is unreliable and biased. Binary presence + TOP flag is more honest and sufficient for matching. Only language skills use proficiency (CEFR standard A1–C2 + native).

Two TOP designations. Technical skills (hard_skill): Top 5, no selection cap. Soft skills (savoir-être): max 5 selected, Top 3 among them. Selected on separate screens (Q11 for technical, Q12 for soft skills). Displayed as two distinct sections on the Employment Passport. The ×1.20 boost in BLOC 2 applies to both types. Caps enforced in both frontend (disabled UI) and backend (service-level validation).

Languages merged here. PassportLanguage is removed — language skills are passport_skills entries with skill.category = 'language'. BLOC 6 filters on this category at computation time.

No hard cap on total skills. Candidates can select as many skills as they want. The matching algorithm compares against the job offer's required skills — having more candidate skills only increases matching breadth, not score inflation. The recruiter-side filtering (via the algo) handles relevance.

Why no free-text label? skill_id FK into the skills catalog enables exact matching in BLOC 2 — no semantic similarity computation needed.

---

passport_qualifications

Q18 — academic degrees, self-taught experiences, and certifications. Single Table Inheritance.

Column	Type	Notes
id	UUID PK
passport_id	UUID FK
type	VARCHAR(20)	discriminator: academic / self_taught / certification
education_level	VARCHAR(20) nullable	academic only — lycee / bac / bac_2 / bac_3 / bac_4 / bac_5 / bac_8 / other
field_of_study	VARCHAR(150) nullable	academic only
diploma_name	VARCHAR(200) nullable	academic only
institution_name	VARCHAR(200) nullable	academic only
year	SMALLINT NOT NULL	Year of obtention/completion — all types
domain	VARCHAR(150) nullable	self_taught only
learning_methods	JSON nullable	self_taught only — array of: online_courses / personal_projects / books / mentoring / open_source / bootcamp / other
certification_name	VARCHAR(200) nullable	certification only

Why STI over CTI? Three types with mostly shared fields. NULLs are limited and stable over time. No algo query on these fields (display data only). CTI would add 3 unnecessary JOINs for a simple list render.

Display only: PassportQualification data is never used in the matching algorithm. It is displayed on the Employment Passport for recruiter review (credibility signal — academic background and certifications).

---

passport_professional_experiences

Q10 — employment history. Each entry is a professional experience with dates, company, and ROME code.

Column	Type	Notes
id	UUID PK
passport_id	UUID FK
rome_code	VARCHAR(6) NOT NULL	indexed — ROME catalogue selection, feeds BLOC 3 + inline skills
job_title	VARCHAR(150) NOT NULL	position held
company_name	VARCHAR(150) NOT NULL	employer or client name (label adapts by contractType in UI)
sector	VARCHAR(150) nullable	sector of activity
city_id	INTEGER FK → cities nullable	GeoNames city reference (null if remote)
contract_type	VARCHAR(30) NOT NULL	cdi / cdd / interim / apprentissage / internship / freelance
start_year	SMALLINT NOT NULL
start_month	SMALLINT NOT NULL	1–12
end_year	SMALLINT nullable	null if is_current = true
end_month	SMALLINT nullable	null if is_current = true
is_current	BOOLEAN NOT NULL	drives seniority and reconversion detection
is_remote	BOOLEAN NOT NULL	100% remote — when true, city_id is null
created_at	TIMESTAMPTZ

Why year/month smallints instead of DATE? Candidates typically know the month and year, not the exact day. Storing DATE with a forced day convention (e.g. first of month) is a hidden contract — any consumer could misread it as a precise date. Two SMALLINT columns are semantically honest and avoid the convention risk.

Algo usage: rome_code + start_year/start_month/end_year/end_month/is_current are used in BLOC 3 (relevant experience years + seniority level), and to detect reconversion intent (ROME distance between most recent is_current entry and Q7 target titles).

Current job = is_current experience. There is no denormalized current_job_rome_code on passports. The matching algorithm extracts the current job from the experience marked is_current = true.

---

passport_personal_projects

Q10 — side projects. Demonstrative value (what was built, in which skills domain), not temporal. No dates.

Column	Type	Notes
id	UUID PK
passport_id	UUID FK
rome_code	VARCHAR(6) NOT NULL	required — feeds inline skill suggestions during onboarding. Also serves as the project's domain context (the ROME label is displayed to recruiters)
project_name	VARCHAR(200) NOT NULL
description	TEXT nullable	brief project description — displayed to recruiters
link	VARCHAR(500) nullable	GitHub, portfolio, live app
created_at	TIMESTAMPTZ

Why separate from professional experiences? The two entities share almost no fields (only rome_code). A single table would have 11+ nullable columns unused for each type. Separate tables are self-documenting, require no type discriminator, and eliminate all conditional logic in the service layer, matching algorithm, and validation. Personal projects are not scored in BLOC 3 — their rome_code serves only for inline skill suggestions (linking the project to relevant technical skills via BLOC 2).

Why no domain column? The ROME code already captures the project's professional domain. A separate text field would duplicate this information. The ROME label (resolved from the code) is displayed to recruiters as context.

---

companies

Column	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	company presentation — displayed on company page
website	VARCHAR(500) nullable
founded_year	SMALLINT nullable
employee_count	INTEGER nullable	actual headcount — may differ from size enum
created_at	TIMESTAMPTZ
updated_at	TIMESTAMPTZ
deleted_at	TIMESTAMPTZ nullable	soft delete

---

recruiters

Domain-specific data only. Authentication fields inherited from users via CTI.

Column	Type	Notes
id	UUID PK FK → users	shared PK, CTI pattern
company_id	UUID FK
first_name	VARCHAR(100)
last_name	VARCHAR(100)
role	VARCHAR(20)	owner / recruiter
invited_by_id	UUID FK nullable	self-ref — who sent the invitation
invitation_token	VARCHAR(255) nullable	tokenized invite link
invitation_expires_at	TIMESTAMPTZ nullable	48h expiry

---

admins

WonderWork team back-office accounts. Third CTI subtype of users, alongside candidates and recruiters. Carries no domain-specific columns: everything an admin needs (identity, credentials) already lives in users. The table exists only to complete the CTI shape (one row per admin, sharing the users PK).

Column	Type	Notes
id	UUID PK FK → users	shared PK, CTI pattern

Admin accounts are provisioned out of band (app:admin:create), never self-registered. Role ROLE_ADMIN is derived from the subtype, not stored. Sign-in is isolated on POST /v1/admin/auth/login (session only).

---

security_events

Append-only security audit log, transverse to every user type (candidate, recruiter, admin). One row per security-sensitive action: sign-in success/failure, sign-out, password change, profile photo change, personal-data export, account deletion request, account purge, and an admin consultation of a profile (admin_viewed_user, actor = admin, subject = the viewed account). Written through a single recorder, never updated, removed only by the one-year retention purge (app:security-events:purge, daily). Carries no raw personal data.

Column	Type	Notes
id	UUID PK
occurred_at	TIMESTAMPTZ	when the action happened
type	VARCHAR(40)	SecurityEventType discriminator
actor_id	UUID FK → users nullable, ON DELETE SET NULL	who acted, null = the system
subject_id	UUID FK → users nullable, ON DELETE SET NULL	the account concerned, nulled when that account is purged
subject_role_snapshot	VARCHAR(20) nullable	frozen role of the subject (candidate/recruiter/admin), survives the account purge; null when no subject
ip_address	VARCHAR(45) nullable	source IP (IPv6-capable), the only network metadata besides the user-agent
user_agent	VARCHAR(512) nullable	source user-agent, truncated
metadata	JSON nullable	minimal non-personal context (e.g. a failure reason), never a raw name, email, or profile content

Failed sign-in is enumeration-safe: the raw submitted email is never stored, and subject_id links to an account only when the submitted identifier matched an existing one. Not CASCADE: actor/subject use ON DELETE SET NULL so an event survives a purged account, de-identified, retaining only its role snapshot. Indexed on (subject_id, occurred_at) (per-account reads) and (occurred_at) (the retention purge). All writes go through the single SecurityEventRecorder, the shared append-only recording entry point reusable by future audit families.

---

job_offers

Column	Type	Notes
id	UUID PK
company_id	UUID FK
recruiter_id	UUID FK	created by
rome_code	VARCHAR(6)	ROME job title — required, indexed, used in BLOC 1
rome_label	VARCHAR(150)	display label
location	VARCHAR(200)	display label — "Paris 15e, Île-de-France"
location_geonames_id	INTEGER nullable	GeoNames ID — used in BLOC 5 location matching; null if not geocoded
contract_type	VARCHAR(30)	CDI / CDD / interim / apprentissage / internship / freelance
work_time	VARCHAR(20)	full_time / part_time
work_arrangement	VARCHAR(20)	remote / hybrid / on_site
target_availability	VARCHAR(20)	ASAP / lt_1m / lt_3m / flexible — backed by JobOfferTargetAvailability enum (distinct from candidate-side Availability — different semantics)
experience_level	VARCHAR(20) nullable	junior / mid / senior / lead / director / executive — BLOC 3, backed by ExperienceLevel enum
required_education_level	VARCHAR(20) nullable	high_school / bac / bac_2…bac_8 / other — backed by EducationLevel enum. NULL = no requirement. Display-only for now; matching effect delivered with the matching redesign (B2)
contract_duration	VARCHAR(100) nullable	CDD/interim/internship only
salary_min	INTEGER nullable	display only
salary_max	INTEGER nullable	offer.max used in BLOC 5 salary matching
display_salary	BOOLEAN DEFAULT true	hide salary from candidates when false
description	TEXT
status	VARCHAR(20)	draft / published / closed
published_at	TIMESTAMPTZ nullable
created_at	TIMESTAMPTZ
updated_at	TIMESTAMPTZ
deleted_at	TIMESTAMPTZ nullable	soft delete

---

job_offer_skills

Required skills per offer. Feeds BLOC 2 and BLOC 6 of the matching algorithm.

Column	Type	Notes
id	UUID PK
job_offer_id	UUID FK
skill_id	UUID FK → skills	controlled vocabulary — replaces free-text skill_name
importance	VARCHAR(20) nullable	critical / important / nice_to_have — NULL when skill.category = 'language' (handled by BLOC 6 separately, not BLOC 2); required for all other categories. Validated at write time on the Input DTO.
level_required	VARCHAR(20) nullable	beginner / intermediate / advanced / expert — null when skill.category = 'language'. Consumed by MatchingBloc2 via mapping of the candidate's is_top flag (top → expert/advanced equivalent, non-top → intermediate equivalent). When null, no level filter is applied for that skill.
proficiency_required	VARCHAR(10) nullable	a1 / a2 / b1 / b2 / c1 / c2 / native — null when skill.category != 'language'

Languages merged here. JobOfferLanguage is removed (entity, table, and references) — required languages are job_offer_skills entries with skill.category = 'language', proficiency_required set, and importance left NULL. BLOC 6 filters on this category.

Exact FK matching. skill_id enables deterministic comparison with passport_skills.skill_id — no semantic similarity needed. Importance levels weight the scoring penalty for absence in BLOC 2 (critical = weight 3, important = weight 2, nice_to_have = weight 1). Languages are scored separately in BLOC 6 by CEFR level — importance does not apply.

---

culture_values

Controlled vocabulary of company culture values. Seeded once — candidates and companies both pick from this same list, enabling precise matching in BLOC 4.

Column	Type	Notes
id	UUID PK
slug	VARCHAR(50) UNIQUE	machine-readable stable identifier — autonomy, work_life_balance
label	VARCHAR(100)	display label — Autonomie, Équilibre vie pro/perso

Why a controlled vocabulary? Free-text values make matching impossible — "bienveillance" and "bien-être" would not match despite being semantically close. A shared referential ensures candidate and company values speak the same language.

---

company_culture_values

Junction table — many-to-many between companies and culture_values.

Column	Type	Notes
company_id	UUID FK companies.id
culture_value_id	UUID FK culture_values.id
PRIMARY KEY	(company_id, culture_value_id)

---

passport_culture_values

Junction table — many-to-many between passports and culture_values. Replaces the former professional_values TEXT[] column.

Column	Type	Notes
passport_id	UUID FK passports.id
culture_value_id	UUID FK culture_values.id
PRIMARY KEY	(passport_id, culture_value_id)

---

job_offer_stages

Recruitment stages defined per job offer. Entity JobOfferStage (namespace App\Entity\Job) — renamed from JobOfferStage for namespace consistency. The product UI still calls this concept "Pipeline" (route /pipeline in recruiter/), but the data model uses the domain prefix JobOffer*.

Column	Type	Notes
id	UUID PK
job_offer_id	UUID FK
type	VARCHAR(50)	backed by JobOfferStageType enum (7 values): screening / interview / assessment / trial_day / reference_check / background_check / ats_step (system-only, not exposed in the manual selector)
custom_label	VARCHAR(150) nullable	displayed instead of type name when set
display_order	SMALLINT
default_mode	VARCHAR(10) nullable	StageMode: onsite / video / phone. Offer-level default prefilled into the schedule form.
default_location	VARCHAR(255) nullable	default address (on-site stages)
default_meeting_link	VARCHAR(2048) nullable	default meeting link (remote stages)
default_duration	SMALLINT nullable	default duration in minutes
default_interlocutor	VARCHAR(255) nullable	default interlocutor (falls back to the connected recruiter at scheduling time)
default_instructions	TEXT nullable	default instructions shown to the candidate
default_format	VARCHAR(10) nullable	assessment only: live / take_home

---

applications

Column	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)	see lifecycle below
peak_status	VARCHAR(20)	highest status ever reached — used for KPI calculations
created_at	TIMESTAMPTZ
updated_at	TIMESTAMPTZ	bumped by any write
status_changed_at	TIMESTAMPTZ	bumped only by an actual status transition — the reliable pipeline-inactivity clock (recruiter dashboard, candidate follow-up), never touched by unrelated writes
is_pinned	BOOLEAN NOT NULL DEFAULT false	Recruiter-only pin signal. Independent of application status.
was_headhunted	BOOLEAN NOT NULL DEFAULT false	Permanent origin marker set once when the candidate was invited (headhunting), never reset.
last_followed_up_at	TIMESTAMPTZ nullable	Date of the candidate's last stale-application follow-up. Null until the first. Doubles as the 14-day cooldown clock.
last_follow_up_message	TEXT nullable	Optional message sent with the last follow-up (personal data, purged with the application).
deleted_at	TIMESTAMPTZ nullable	soft delete

UNIQUE constraint on (candidate_id, job_offer_id) — one application per candidate per offer.

Status lifecycle:

[Candidate]  sent ──────────────────────────────→ withdrawn
               ↓ (recruiter opens)
             viewed ──────────────────────────────→ rejected
               ↓ (recruiter moves to pipeline)
           in_process ───────────────────────────→ rejected
               ↓ (recruiter makes employment offer)
           offer_pending
               ↓                  ↓
            hired              declined  (candidate action)

Status	Triggered by
invited	Recruiter invites the candidate via headhunting (before any candidate action)
sent	Candidate submits application
viewed	Recruiter opens the application for the first time
in_process	Recruiter moves to first pipeline stage
offer_pending	Recruiter sends an employment offer
hired	Candidate accepts the employment offer
declined	Candidate declines the employment offer
rejected	Recruiter rejects at any stage
withdrawn	Candidate withdraws their own application
invitation_declined	Candidate declines a headhunting invitation

---

application_events

Append-only journal of application lifecycle events: every status transition (including sent at creation and viewed on first recruiter view) and every stage advancement. Feeds the recruiter Suivi timeline and the candidate-side reason lookups (rejection, decline, withdrawal).

Column	Type	Notes
id	UUID PK
application_id	UUID FK
stage_id	UUID FK nullable	→ pipeline_stages (null for status-only events)
status	VARCHAR(20)	snapshot of applications.status at this point
message	TEXT nullable	optional message (500 chars max) — decline reason or recruiter rejection message
rejection_reason	VARCHAR(100) nullable	rejection reason code. Null for non-REJECTED events.
withdrawal_reason	VARCHAR(100) nullable	withdrawal reason code (e.g. found_another_job). Null for non-WITHDRAWN events.
withdrawal_reason_detail	TEXT nullable	free-text detail (500 chars max), only set when withdrawal_reason = other.
created_at	TIMESTAMPTZ	immutable — no updated_at on event rows

---

application_stage_notifications

Per-candidate scheduling and notification instance for the candidate's current stage, produced by the recruiter "Save & notify" action. Mutable confirmation lifecycle, kept separate from the append-only application_events.

Column	Type	Notes
id	UUID PK
application_id	UUID FK
stage_id	UUID FK	→ job_offer_stages
interaction	VARCHAR(10)	StageInteraction: confirm (synchronous appointment) / inform (asynchronous notice)
scheduled_at	TIMESTAMPTZ nullable	appointment date and time (confirm)
deadline	DATE nullable	submission deadline (inform, take-home assessment)
mode	VARCHAR(10) nullable	StageMode: onsite / video / phone (confirm)
location	VARCHAR(255) nullable	snapshot, editable per candidate
meeting_link	VARCHAR(2048) nullable	snapshot. For mode = video, system-generated internal join URL to the integrated interview room (no longer a recruiter-typed external link) — see design decision
duration	SMALLINT nullable	snapshot, minutes
interlocutor	VARCHAR(255) nullable	snapshot
note	TEXT nullable	snapshot, instructions / message to the candidate (500 chars max)
assignment_link	VARCHAR(2048) nullable	snapshot, take-home assignment link (inform)
estimated_effort	VARCHAR(100) nullable	snapshot, take-home estimated effort label (inform)
confirmation_status	VARCHAR(15) nullable	StageConfirmationStatus: pending / confirmed / not_available. Null for inform. Candidate-written (confirmed / not_available).
candidate_message	TEXT nullable	candidate message when not_available (500 chars max). Candidate-written.
requested_at	TIMESTAMPTZ	set/bumped on each notify, drives candidate pinning order
responded_at	TIMESTAMPTZ nullable	candidate response timestamp
cancelled_at	TIMESTAMPTZ nullable	set when the recruiter explicitly cancels the coordination, or when coordinating another stage supersedes a still-pending confirmation
cancellation_reason	TEXT nullable	optional recruiter-provided reason on explicit cancellation, shown to the candidate and included in the cancellation email (500 chars max). Reset to null on re-notify
ics_sequence	SMALLINT	iCalendar SEQUENCE of the last .ics emailed for this notification. Starts at 0, incremented on each subsequent confirm email so calendar apps auto-update an already-imported event. Exposed to the candidate as icsSequence
candidate_joined_at	TIMESTAMPTZ nullable	first participant_joined observed for the candidate in the integrated video room (mode = video). Derived from media-server lifecycle webhooks. Reset to null on reschedule
recruiter_joined_at	TIMESTAMPTZ nullable	first participant_joined observed for any company recruiter in the room. Reset to null on reschedule

One active notification per (application, current stage): reschedule upserts the row and resets confirmation_status to pending, and resets candidate_joined_at / recruiter_joined_at to null (a new appointment starts with no recorded attendance).

The recruiter-facing attendance status (pending / both attended / candidate no-show / recruiter no-show / nobody connected) is not stored: it is computed at read time from the two join timestamps and the join window (scheduled_at + (duration ?? 60) + 30 min grace), reusing the same window arithmetic as the interview-room access resolver.

RGPD: candidate_message is personal data. The FKs have no DB-level cascade (deletion is handled in the service layer, per the convention). When the application hard-purge is implemented, it must delete the related application_stage_notifications rows, otherwise the FK constraint blocks the purge and the personal data survives.

---

interview_feedbacks

Private post-interview feedback left by a party (candidate or recruiter) after an honored video interview. Visible only to WonderWork: never returned in any candidate- or recruiter-facing view. Acceptance is authorized by party membership and interview existence only, never gated on the webhook-derived attendance status.

Column	Type	Notes
id	UUID PK
notification_id	UUID FK	→ application_stage_notifications
author_role	VARCHAR(10)	candidate / recruiter (the side that authored it)
author_id	UUID	technical id of the authoring user/candidate. Not exposed; no display name stored
call_rating	SMALLINT nullable	step 1, call-experience rating. Null when that step was skipped
call_comment	TEXT nullable	step 1 optional comment (bounded length)
party_rating	SMALLINT nullable	step 2, rating of the other party. Null when that step was skipped
party_comment	TEXT nullable	step 2 optional comment (bounded length)
created_at	TIMESTAMPTZ

Unique on (notification_id, author_role): one submission per party per interview (server-enforced, 409 on duplicate). The two "steps" are one wizard submitted in a single request; a skipped step leaves its *_rating / *_comment null.

RGPD: call_comment and party_comment are personal data (the evaluative party comment concerns the other person, who never sees it). The FK has no DB-level cascade; the application hard-purge must delete the related interview_feedbacks rows via the service layer.

---

candidate_discarded_offers

Records job offers a candidate has marked as "not interested". Used to exclude offers from the discovery feed and swipe stack.

Column	Type	Notes
id	UUID PK
candidate_id	UUID FK	→ candidates
job_offer_id	UUID FK	→ job_offers
discarded_at	TIMESTAMPTZ

UNIQUE constraint on (candidate_id, job_offer_id) — idempotent discard.

Why no "undo" record? Undo is handled client-side during the toast window. The API is only called after the window expires; the record is never created if the candidate undoes within the window.

---

candidate_bubbles

A candidate's saved job search. Stores a named filter set replayed over the full catalogue (GET /v1/job-offers) and browsed as a swipe deck. Private to its owner.

Column	Type	Notes
id	UUID PK
candidate_id	UUID FK	→ candidates
name	VARCHAR(60)	required, trimmed, not unique
filters	JSONB	saved catalogue filter set: work_arrangement, contract_type, work_time, city_geonames_id, salary_min, experience_level, availability, published_since, and the free-text query q. Drift-tolerant on read.
created_at	TIMESTAMPTZ
updated_at	TIMESTAMPTZ	bumped on content edit (name/filters), never on a deck view
last_opened_at	TIMESTAMPTZ	moment the candidate last opened the deck; initialized at created_at, set to now() by POST /me/bubbles/{id}/seen. Basis of the "new offers" counter: offers matching the filters published after this instant, excluding discarded and applied. Distinct from the email-alert marker.
alert_enabled	BOOLEAN	daily email alert opt-in, false by default (AC1). Toggled by PUT /me/bubbles/{id}/alert and by the public unsubscribe endpoint.
last_notified_at	TIMESTAMPTZ, nullable	email-alert marker: cutoff for "new" offers in the next alert email. NULL while the alert has never been enabled; set to now() on opt-in and advanced after each send so offers are not re-listed (AC5). Distinct from last_opened_at (in-app badge).

Index on candidate_id. No UNIQUE constraint (duplicate names/filters allowed). Cap of 20 bubbles per candidate, enforced in the service layer. An empty filter set is valid (the deck then browses the full catalogue).

Why JSONB for filters? The filter set is opaque storage replayed verbatim against the catalogue query params; it is never queried by containment. Mirrors the Types::JSON pattern already used on passports.preferred_sectors.

Why no DB cascade? Deletion is handled in the service layer (convention). The account hard-purge must delete candidate_bubbles rows for the candidate.

Why no compatibility gauge stored? The deck reuses the catalogue scoring: offers with a match_scores row show their gauge, unscored offers show none. Bubbles never trigger mass on-demand scoring.

---

match_scores

Stored scores, always read from this table. Rows are produced by the async matching pipeline (perimeter-driven) or, since the on-demand US, by a synchronous candidate-triggered computation on an out-of-perimeter offer — the source column records which.

Column	Type	Notes
id	UUID PK
candidate_id	UUID FK
job_offer_id	UUID FK
global_score	FLOAT nullable	null = not yet computed ("Score incoming" state)
relevance_score	FLOAT nullable	Occupational relevance (blocs 1, 2, 3, 6) — the number the candidate gauge and the feed threshold use
feasibility_score	FLOAT nullable	Practical feasibility (blocs 4, 5)
score_bloc_1	FLOAT nullable	Job Title (20%)
score_bloc_2	FLOAT nullable	Skills Matrix (32%)
score_bloc_3	FLOAT nullable	Experience & Seniority (17%)
score_bloc_4	FLOAT nullable	Cultural & Contextual Fit (5%)
score_bloc_5	FLOAT nullable	Logistics (18%)
score_bloc_6	FLOAT nullable	Languages (8%)
confidence_level	VARCHAR(20) nullable	very_high / high / medium / low
source	VARCHAR(20)	feed (perimeter pipeline) / on_demand (candidate-triggered)
computed_at	TIMESTAMPTZ nullable
created_at	TIMESTAMPTZ
updated_at	TIMESTAMPTZ

UNIQUE constraint on (candidate_id, job_offer_id).

Why store per-bloc scores? GDPR Art. 22 requires the ability to explain automated decisions to candidates. Per-bloc scores provide the breakdown visible in the offer detail view.

Why a source column? An on_demand row means the matching perimeter missed an offer the candidate cared about — an analytics signal for future perimeter recalibration. Provenance is fixed at creation and never overwritten by recomputes.

Why no matched_critical / total_critical columns? BLOC 2 and BLOC 6 now use exact FK matching (skill_id). The frontend can recompute skill match counts deterministically by crossing passport_skills (from Zustand) with job_offer_skills (embedded in the match response). Storing counts that are always recomputable from source data would be redundant.

null vs 0: global_score = null means "not yet computed" (triggers "Score incoming" UI state). global_score = 0 means "computed and incompatible". Never conflate the two.

---

Pre-computation Architecture

TRIGGER: profile.saved OR job.published
         ↓
[Pre-filter — cheap checks, no algo]
  • work arrangement: candidate remote preference incompatible with offer remote_policy → score = 0, skip
  • salary floor: cand.min > offer.max → score = 0, skip
  • freshness: candidate last_login > 90 days ago (job.published trigger only) → skip entirely (no row created)
  Note: score = 0 means "computed, incompatible". score = null means "not yet computed" (Score incoming state).
  Pre-filter failures (work arrangement, salary) MUST produce score = 0, never null.
  Freshness failures produce no row — the API handles these states at read time.
         ↓
[Symfony Messenger — async queue]
  Enqueue MatchScoreJob(candidate_id, job_offer_id) for surviving pairs
         ↓
[Worker: compute 6 blocs]
  UPSERT match_scores SET global_score, bloc_1..6, confidence_level, computed_at
         ↓
[API read — always instantaneous]
  GET /v1/me/matches →
    SELECT * FROM match_scores WHERE candidate_id = X
    ORDER BY global_score DESC NULLS LAST
  No score filter — all offers returned (null scores appear at the bottom)

Scale considerations: - MVP (< 10K candidates, < 1K offers): pre-filter + Messenger sufficient - Growth (100K+ candidates): add pgvector approximate pre-filtering layer before the 6-bloc computation — architecture unchanged, only the pre-filter step is enriched

---

Key Indexes

-- Matching hot path
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);

-- is_reconverting computation
CREATE INDEX idx_passport_professional_experiences_rome ON passport_professional_experiences(passport_id, rome_code)
  WHERE rome_code IS NOT NULL;

-- Skill matching (BLOC 2 + BLOC 6)
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 6 — Language matching (filtered index on skill category)
CREATE INDEX idx_passport_skills_language ON passport_skills(passport_id)
  WHERE skill_id IN (SELECT id FROM skills WHERE category = 'language');
CREATE INDEX idx_job_offer_skills_language ON job_offer_skills(job_offer_id)
  WHERE skill_id IN (SELECT id FROM skills WHERE category = 'language');

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

-- City autocomplete (GeoNames)
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_cities_name_trgm ON cities USING GIN (name gin_trgm_ops);
CREATE INDEX idx_cities_country ON cities(country_code);

-- Soft delete filtering
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;

---

Reference Data Tables

Static reference data used in dropdowns, validation, and matching. Seeded via Symfony fixtures. Served to frontend via standard API Platform GET collection endpoints. The frontend caches these with staleTime: Infinity (TanStack Query) — fetched once per session.

nationalities

Column	Type	Notes
id	UUID PK
code	VARCHAR(3) UNIQUE	ISO 3166-1 alpha-2 (FR, US, DE…)
label	VARCHAR(100)	Display name — "France", "États-Unis"

~250 entries. Seeded from ISO 3166-1. Used in Q4 (nationality dropdown). Stored as FK on passports.nationality_id (replaces VARCHAR).

PHP Enums (not DB tables)

The following reference data is stored as VARCHAR on entities (no FK) and validated via PHP backed enums. Frontend uses matching TypeScript constants. No DB tables, no API endpoints.

• Sector — ~26 values (tech, finance, healthcare…). Used in Q6 (main sector), Q11 (experience sector), Q15 (target sectors). PHP enum: src/Enum/Sector.php.
• EducationLevel — 8 values (lycee, bac, bac_2… bac_8, other). Used in Q10 (academic degree form). PHP enum: src/Enum/EducationLevel.php.
• ContractType — 6 values (cdi, cdd, interim, apprentissage, internship, freelance). Used in Q10 (experience form). PHP enum: src/Enum/ContractType.php.

Language flags: language skills live in the skills table (category = 'language') with a code column (ISO 639-1). The flag emoji mapping is a frontend-only constant (LANGUAGE_FLAGS) — it is not stored in the database.

---

Computed Signals

Signals derived from the data model that are surfaced to users. Two categories: persisted flags (stored in DB, pre-computed on events) and derived badges (computed at query time, never stored).

---

Persisted Flags

Stored in DB because they are used in SQL filters or are too expensive to compute per-request.

Flag	Table	Type	Computation trigger	Rule
has_experiences	passports	BOOLEAN nullable	profile.saved event	null = skipped, false = "I have none", true = has entries. UX nudging only — not used in matching.
has_qualifications	passports	BOOLEAN nullable	profile.saved event	null = skipped, false = "I have none", true = has entries. UX nudging only — not used in matching.
is_reconverting	passports	BOOLEAN	profile.saved event	ROME distance between the most recent passport_professional_experiences.rome_code (type: employment, is_current = true) and Q7 passport_target_jobs.rome_code. Distance ≥ 2 ROME domains → true. Stored because it feeds BLOC 1 weighting and recruiter filters.

---

Derived Badges

Computed at API query time by the StateProvider or service layer. Never stored in the database. Displayed in the recruiter app when browsing match results or candidate profiles.

Badge	Display label	Source data	Derivation rule
Career Transition	"Reconversion"	passports.is_reconverting	is_reconverting = true
Atypical Profile	"Profil atypique"	passport_professional_experiences.rome_code	Count of distinct ROME domain letters (first character of rome_code) across all experience entries ≥ 3. Signals a multi-domain, versatile background.
High Potential	"Haut potentiel"	computed seniority + match_scores.global_score	Computed seniority IN ('junior', 'mid') AND global_score >= 75. Junior or early-mid profile with a strong compatibility score.
Overqualified	"Surqualifié"	computed seniority + job_offers.experience_level + match_scores.score_bloc_3	Computed seniority exceeds job offer experience_level by ≥ 2 steps on the scale (junior → mid → senior → executive) AND score_bloc_3 >= 80.

Why not store badges? Their definition may evolve as product intuition grows. Storing them creates a migration risk every time the rule changes. Computing them at query time (from already-loaded data) adds < 1ms and keeps the rules in code, not in stale DB values.

Display rule: a candidate can carry multiple badges simultaneously. Badges are shown as chips on match cards and candidate profile headers.

---

Modeling Decisions Summary

Decision	Choice	Rationale
User entity	CTI — abstract users + candidates + recruiters	Single UserProvider + firewall, domain data stays clean and non-sparse
Auth vs domain separation	Auth fields on users, domain fields on subclass tables	Candidate and Recruiter have fundamentally different domain data
Token strategy	Access JWT (15 min) + opaque refresh token (DB, rotated)	Re-login every 15 min is unacceptable on mobile PWA; DB sufficient at this scale
Refresh token storage	DB via gesdinet/jwt-refresh-token-bundle	Avoids Redis dependency; can migrate later if performance requires it
Logout invalidation	Delete refresh token from DB + clear cookies	Access token expires naturally in 15 min — acceptable tradeoff for MVP
Theft detection	Rotated token re-submitted → full revocation for that user	Detects token theft; forces re-login as a security signal
Candidate vs Passport	Separate entities (1:1)	50+ passport fields, distinct lifecycle
PassportQualification (Q18)	STI	3 types (academic / self_taught / certification), display only, no algo query
PassportProfessionalExperience / PassportPersonalProject (Q10)	Separate tables, all columns typed	Two distinct entities with no shared fields except rome_code
Skill levels	None (binary)	Self-assessment unreliable, presence + TOP flag sufficient
TOP skills	3 max, ×1.20 flat boost	Podium logic — signals top strengths, not diluted by 5
Salary (candidate)	minimum_salary only	Recruiter sets range, candidate sets floor — simpler and more honest
Salary matching	offer.max >= cand.min	Candidate asks "can this offer reach my floor?"
Score computation	Pre-computed async	Page load = 1 SQL read, never blocking computation
Score null vs 0	null = not computed, 0 = incompatible	Prevents "Score incoming" from showing on genuinely incompatible pairs
Pre-filters	work_arrangement + salary floor + freshness	Eliminate obvious mismatches before 6-bloc computation
GDPR deletion	soft delete everywhere	30-day retention then hard purge via async task
No cascade delete in DB	Handled in service layer	Full RGPD traceability on deletion
Application status	9 states (sent → hired/declined/rejected/withdrawn)	Full ATS funnel covered including employment offer lifecycle
Application events	Separate immutable table	Audit trail + recruiter Suivi timeline — never update, only insert
ATS sync (TeamTailor)	Deferred	Tackled when coding the ATS integration US
has_experiences / has_qualifications	BOOLEAN nullable on passports	null = skipped, false = "I have none", true = has entries. UX nudging only — matching treats empty collections as score 0 regardless.
is_reconverting (ex reconversion_intent)	BOOLEAN, stored in DB	ROME distance computation is costly — pre-computed on passport save, used in BLOC 1 weighting
Seniority level	Computed at scoring time, not stored	Derived from total professional experience years (0–2 junior, 2–5 mid, 5–10 senior, 10+ executive). Removes self-declaration bias and one onboarding question. BLOC 3 Part 2 uses computed value.
experience_level on job_offers	4 levels (junior / mid / senior / executive)	Aligned with computed seniority thresholds — BLOC 3 comparison requires same scale
Current job	Derived from passport_professional_experiences where is_current = true	No denormalized current_job_rome_code on passports. Matching algorithm extracts from experiences collection.
City references	FK to cities table (GeoNames) everywhere	No denormalized city labels. passports.city_of_residence_id, passport_preferred_cities.city_id, passport_professional_experiences.city_id all reference cities.geonames_id.
Reference data	DB tables (nationalities, sectors, education_levels, contract_types) + API endpoints	Single source of truth in DB, served via API, cached on frontend. Same pattern as culture_values. No shared JSON files.
Certifications in passport_qualifications	STI type certification added — no extra columns for MVP	credential_id / URL are not universal across certification types; defer to a future US if verification feature needed
Skill vocabulary	skills catalog + rome_job_skills junction + rome_jobs / rome_job_appellations referentials	Controlled vocabulary enables exact FK matching in BLOC 2 and BLOC 6 — no semantic NLP needed. Sourced from ROME 4.0 + WonderWork custom skills for tools and technologies. ROME jobs and appellations imported locally for autocomplete and skill suggestion.
PassportLanguage / JobOfferLanguage removed	Merged into passport_skills / job_offer_skills with skill.category = 'language'	Unified structure — BLOC 6 filters on category. CEFR levels handled via dedicated proficiency column.
PassportExpertise renamed	PassportExpertise → PassportQualification	"Expertise" was ambiguous (confused with skill expertise). "Qualification" is the standard HR term for academic background + certifications.
Skill levels	No level on candidate side for non-language skills — proficiency (CEFR) only for languages	Self-assessment unreliable and biased. Binary presence + TOP flag sufficient for BLOC 2. Recruiter can still specify level_required on job offer for display and future use.
matched_critical / total_critical removed from match_scores	Derivable from exact FK matching by frontend	With controlled vocabulary, skill match counts are always recomputable by crossing passport_skills ∩ job_offer_skills. Storing them would be redundant.
No score filter on GET /v1/me/matches	All offers returned, sorted DESC NULLS LAST	The 70% threshold is a UX concern, not an API filter. The frontend decides what to display. Null-score offers appear at the bottom.
Company culture values	culture_values reference table + company_culture_values + passport_culture_values junction tables	Controlled vocabulary required for matching — free-text TEXT[] makes BLOC 4 comparison unreliable
last_login_at on users	TIMESTAMPTZ nullable	Feeds candidate freshness filtering in pre-computation pipeline (skip candidates inactive > 90 days)
BLOC 6 (Credentials) removed	—	Certifications/education level was 5% of the score. Removed: adds UX complexity for recruiters (required to specify diploma/certs on every offer) for negligible algorithm value. Data remains on the passport as a display signal. 5% redistributed: +3% BLOC 2 Skills (38→41%), +2% BLOC 3 Experience (15→17%).
JobOffer.target_availability	Dedicated enum JobOfferTargetAvailability (4 values), separate from candidate-side Availability (6 values)	Two different semantics — "when the recruiter wants to fill" vs "when the candidate is ready". Mapped explicitly at scoring time, not via shared enum.
JobOfferSkill.importance nullable	NULL when skill.category = 'language'	Languages are scored in BLOC 6 by CEFR proficiency, not in BLOC 2 by importance. NULL is the honest representation of "not applicable" — avoids storing a fictitious default value in DB. Validated conditionally at write time.
PipelineStage renamed to JobOfferStage	Entity in App\Entity\Job\	Aligns with namespace convention JobOffer* (JobOffer, JobOfferSkill, JobOfferStage). Product term "Pipeline" is preserved at the UI layer (route /pipeline) — only the data model uses the domain prefix.
JobOfferStageType enum refined to 7 values	screening / interview / assessment / trial_day / reference_check / background_check / ats_step	Generalist, non-tech-specific catalogue. assessment merges the former live_coding / take_home / case_study; phone_screen renamed screening (phone or video). The phantom values video_screening / technical_test (documented but never implemented) are dropped. ats_step stays system-only. custom_label covers fine-grained renaming. Migration remaps existing rows (phone_screen→screening, live_coding/take_home/case_study→assessment).
job_offer_stages default_* columns	Nullable typed columns (mode, location, meeting_link, duration, interlocutor, instructions, format)	Offer-level scheduling defaults, prefilled into the per-candidate schedule form. Nullable columns chosen over a 1:1 table (would only relocate the nulls and add a join) and over JSONB (loses typing and DB-level validation).
ApplicationStageNotification entity	Per-candidate scheduling/notification instance	Producer side, written by the recruiter "Save & notify" action. Data driven by nature (confirm / inform) plus mode (onsite / video / phone), not by stage type. Holds a snapshot of the effective values, editable per candidate. Mutable confirmation lifecycle, kept separate from the append-only application_events. Candidate-written columns (confirmation_status, candidate_message, responded_at) are filled by the candidate response endpoint (separate consumer US).
Integrated video interview room	No Room entity — room identity = application_stage_notifications.id; per-participant access token minted on demand at join time (never persisted)	Video interviews run on a self-hosted media server (RGPD: French infra, no US SaaS). meeting_link becomes the system-derived internal join URL for mode = video; the external-link input is dropped. Access is time-windowed (scheduled − 15 min → scheduled + duration + 30 min) and identity-scoped (company recruiter + application candidate). No new persisted PII, no purge cascade. Media provider behind an InterviewRoomProvider port.
JobOfferSkill.level_required consumed by BLOC 2	Mapped via candidate's is_top flag — no candidate-side level required	Avoids re-introducing self-assessed levels on candidate skills (decision ligne 300). Coefficients: 1.0 (top × any required level), 1.0 (non-top × null/beginner/intermediate), 0.7 (non-top × advanced), 0.5 (non-top × expert), 0.0 (skill absent). Multiplied by importance weight (critical=3, important=2, nice_to_have=1). When level_required is null, no level pondération is applied for that skill — only presence is checked.