Back-office Concept · Trusted Segments ← Back to the prototype How it gets built

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 originalscity_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 search
Correction 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

Whatcity_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

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.