How it gets built
On your stack: Symfony/PHP, Vue.js, MariaDB, OpenSearch. One data flow, nine steps.
The data flow
Shared clean-up listsMariaDB · city, country, employer→sector · maintained by AlumnForce
Per-school overridesMariaDB · the override wins
↓
Normalisation stepon every profile save
One-time backfillin batches, safe to re-run
↓
Cleaned columns next to untouched originals
city_norm country_norm sector_norm + status auto suggested confirmed↓
Nightly reliability jobreads last profile update + last login · per-school thresholds 2 / 4 years → reliability label column
↓
OpenSearch: 6 new fields3 cleaned values, 2 dates, label · copy-and-switch rebuild per school
↓
SegmenterCity filter reads
city_norm · 2 new date filters via the existing date filter type · 3 label counts in the same searchCorrection loop
Admin review screensuggested values only
→
Overrideper-school table
→
Background recompute
→
Affected members updatedin OpenSearch
MariaDBBackground stepOpenSearchScreen
Nine steps
1Shared clean-up lists, maintained by AlumnForce
WhatCollect every distinct raw value per field across schools, with member counts (read-only). Match cities via the segmenter's existing map lookup (canonical city + map point + country), countries via a fixed variant list, sector via the NAF industry code already on the current job, else a company list. People review the most frequent values first; rare ones stay suggested.
Where it runsMariaDB tables: city, country, employer→sector, plus a per-school override table (school, field, raw value → cleaned value, who, when).
Why this wayOne shared list serves every school. The override wins, so each school keeps the last word on its own data.
2Cleaned columns next to the originals
What
city_norm, country_norm, sector_norm + status.Where it runsMariaDB, beside the original profile fields.
Why this wayOriginals are never touched. The member's own entry stays exactly as typed.
3Filling the columns
WhatA small step in every profile save, plus a one-time backfill.
Where it runsSymfony: inside the profile save; the backfill runs in batches.
Why this wayNew entries are clean from day one. The backfill is safe to re-run and writes only on change.
4Reliability label
WhatComputes Current To confirm To reactivate from per-school thresholds into one column. Future dates are treated as unknown.
Where it runsA nightly job. The two dates are already recorded by the platform: the exercise export contains
date_maj_profil and derniere_connexion.Why this wayNightly because time passes: a label changes even when nobody edits the profile.
5OpenSearch
WhatSix new fields per school index.
Where it runsRebuilt as a copy, switched over only after checks (same member count, spot checks). Test schools first, then waves.
Why this wayNobody searches a half-built index. Afterwards, nightly partial updates only for members whose label changed.
6Segmenter
WhatThe City filter reads
city_norm: pick London once, see what was grouped. Two new date filters. Three label counts.Where it runsVue.js segmenter. The date filters reuse the existing date filter type; the three counts come from the same search.
Why this wayExisting building blocks, no extra search.
7Admin review screen
WhatShows suggested values only; the admin confirms or changes them.
Where it runsBack-office → override table → background recompute → search index update.
Why this wayLogged, undoable, and applies to that school only.
8Switches
WhatCleaned columns are filled for all schools. Labels are only computed and shown when the school switches
reliability on.Where it runsSchool settings.
Why this wayConsistent spelling infers nothing about a person. A reliability label is derived personal data, so the school decides.
9Checks per school
What
- Member counts equal before and after the rebuild.
- No raw field changed (fingerprint before and after).
- Before/after on a known question: the brief's own sentence returns 2 on raw values and 26 with clean spellings (Annex B).
- CS hand-checks 100 members on two schools to validate the 2/4-year cut-offs.
Where it runsAt each school's switch-over.
Why this wayEach school goes live on evidence.
Who and when
Backend developerSteps 1–5 (lists, cleaned columns, backfill, nightly label, OpenSearch rebuild)
Front-end developerSteps 6–7 (segmenter filters, review screen)
Inside deliverable 1, weeks 3–7The shared lists keep improving after launch.
Open points
To confirm with your Lead Developer in week 1
- Real table and field names.
- Whether profile saves can trigger the clean-up step.
- Rebuild time for the largest school (80,000 members), measured on a test copy in week 5.
Shared clean-up lists AlumnForce maintains; three cleaned columns next to the originals, filled on save and backfilled once; a nightly label from two dates you already record; six new fields in OpenSearch through a copy-and-switch rebuild, school by school; the segmenter reads the clean fields. The member's original entry is never touched.