Company Badges Repair
A record of what we changed, in what order, and why we chose each way. Written as the work finished, so the numbers here are what actually happened, not what we expected.
- Started 2026-08-27. Phases 0, 1 and 2 shipped between 2026-08-29 and 2026-09-01. Phases 3 and 4 are still to do.
- Code issue PORTAL_V2_BACKEND_GLOBAL#2695, parent roadmap#74.
- To run the backfill command, read Company badges backfill: ramp-up for engineers. That page is the operating guide. This page is the history.
What was broken
Five things take part in a badge. A company posts a job. A job has one or more shifts. A jodder works a shift. After the shift, the jodder can give the company one badge.
When a jodder submitted the feedback form, the app wrote in five places at once.
The badge row landed in company_badge_assignments, holding the jodder, the
badge and the company. It never held the shift. So a badge could not be placed
on the shift it was given for, and the reports page showed every badge against
the company instead.
Worse, the column that held the jodder was named slot_user_id, and its foreign
key pointed at slot_user.app_user_id — one half of that table's two-column
primary key. That is what a half key looks like from the badge row's side:
Two consequences followed. MySQL 8.4 rejects a foreign key that points at half a
key, so the database could not be upgraded. And the constraint used
ON DELETE CASCADE, so deleting one of a jodder's slot_user rows deleted every
badge that jodder had ever given. We reproduced that on a test database before
touching anything.
The phases
| Phase | What | PR | Shipped |
|---|---|---|---|
| 0 | Drop the two foreign keys that point at slot_user. Regenerate the schema dump. Move CI to MySQL 8.4. | #2702 | 2026-08-29 |
| 1, migration | Add the four columns that hold the shift, the unique key, three foreign keys, and the backfill log table. | #2703 | Ran on production 2026-08-30 |
| 1, code | The feedback form writes the shift on every new badge, and refuses a second submit. | #2707 | 2026-08-30, about 21:50 SGT |
| 2 | The badges:backfill-slots command gives the old rows their shift. | #2709 | Run on production 2026-09-01 |
| 3 | The reports page reads the shift. The old writes stop. | PR 5 | To do |
| 4 | Drop the old table and columns. | PR 6 | To do, two weeks after phase 3 |
| 5 | The MySQL 8.4 upgrade. | Separate issue | To do |
A supporting change went in alongside: eber_points_logs now ships to the clean
room (#2708), so
we could measure whether it was usable. It turned out to be the best signal we
had. More on that under phase 2.
Phase 0. Remove the blocker
We dropped the two foreign keys pointing at slot_user.app_user_id, one on
company_badge_assignments and one on slot_user_badges. Nothing else.
Why first, and why alone. The schema dump would not even load on MySQL 8.4, so nothing downstream could be tested against the version we are moving to. Dropping the constraints removed the blocker without changing a single row. Doing it in its own deploy meant that if anything went wrong, the cause was unambiguous.
Why the schema dump had to be regenerated in the same PR. Laravel loads
database/schema/mysql-schema.dump before running migrations when the
migrations table is missing. A dump still carrying the dropped constraints
would recreate them on any fresh database, including CI.
Phase 1. Make the row hold the whole fact
The migration added four columns to company_badge_assignments:
| Column | Meaning |
|---|---|
app_user_id | The jodder. Replaces slot_user_id, which held a users.id despite its name. |
jod_job_id | The job. |
slot_id | The shift. |
slot_match | How we know the shift: which step filled it in. |
Why add columns instead of renaming slot_user_id. Two reasons. The type had
to change from signed to unsigned to match users.id. And our deploy runs the
migration while the old container is still serving traffic, so for a few minutes
the old code meets the new schema; a rename would break it, while adding
nullable columns is invisible to it.
Why a unique key on (app_user_id, slot_id) and not a foreign key to
slot_user. slot_id can be empty and app_user_id cannot. A two-column
foreign key with ON DELETE SET NULL would try to empty both, which MySQL
refuses; ON DELETE RESTRICT would block every deletion of a slot_user row.
Laravel cannot express a two-column belongsTo either — that limitation is what
produced the original bug. The unique key costs nothing on the old rows, because
MySQL ignores NULLs in a unique key, and it blocks double submissions from the
day the new code is live.
Why ON DELETE RESTRICT on the jodder. A badge is the company's record of
its own experience. The old CASCADE could erase it as a side effect of tidying
a jodder's shifts. RESTRICT means a future flow that deletes users must deal
with the badges deliberately.
Why a log table. company_badge_backfill_log records every decision the
backfill makes, with the values before and after. Without it, a backfill is a
one-way door.
Then the code change. The form now writes all four columns with
slot_match: 'app', and the check that stops a second submit moved inside the
transaction, where it became a conditional update:
UPDATE slot_user
SET has_feed_back = 1, applicant_rating_slot = ?, applicant_feedback_slot = ?
WHERE slot_id = ? AND app_user_id = ? AND has_feed_back = 0
Why that had to change. The old check ran before the transaction started and held no lock, so two requests could both pass it. The data showed 500 rows where that had happened. Now the second request changes 0 rows and stops, and if a duplicate still reached the insert, the unique key rejects it. We proved it on QA by firing two submits at the same moment from two processes: one badge row, one refusal.
Phase 2. Give the old rows their shift back
139,060 old rows had no shift. Nobody could be asked which shift each badge was for, so the command deduces it from traces the app left at the moment of submit. Surest evidence first; each step only looks at what earlier steps left open.
| Step | slot_match | Evidence | How sure |
|---|---|---|---|
| Duplicates | duplicate | Same jodder, company and badge within 10 seconds. Two taps on submit. | Certain |
| Tier 1 | jod_jobs_column | The twin row in slot_user_badges and the job pointing at it. | Certain |
| Tier 2 | job_user_time | job_user.updated_at within 2 seconds of the badge. | Certain |
| Eber | eber_log | The eber_points_logs row written just after the submit, which names the job and the shift's start and end. | 99.98% |
| Shift in a known job | keeps tier 1 or 2 | The jodder's feedback shifts inside the job already found. | High |
| Tier 3 | slot_schedule | The jodder's feedback shift at that company whose end is nearest the badge. | A guess |
| Second round | slot_schedule_2 | Tier 3 again, with the shifts it just took removed. | A guess |
Why steps at all, in that order. One shift carries one badge. So every shift a certain step claims can be removed from the guess's list of candidates, and removing a claimed shift can only ever remove a wrong answer. The guess has to run last for that to work.
Why the guess refuses so often. A wrong shift is worse than no shift: it puts a badge on a day the jodder can see is wrong. So tier 3 accepts the nearest shift only when the second nearest is at least 24 hours further away.
The 24 hours is not a guess about a guess. We measured it on rows where the answer was already known:
| Gap to the second-nearest shift | Rows | Right |
|---|---|---|
| No second candidate | 285 | 100% |
| 24 hours or more | 822 | 98.7% |
| Between 6 and 24 hours | 106 | 92.5% |
| Under 6 hours | 26 | 61.5% |
Why we could test it before running it. About 119,500 rows have a shift that is known for certain: tier 1 found the job, and the jodder gave feedback on exactly one shift in that job. Those rows are an answer key. The test hides the answer on one in nine of them, runs the guessing steps, and compares.
This replaced an earlier plan to wait two weeks and test on newly written rows. The answer key already existed, so the wait bought nothing.
On production the test scored 99.95%: 13,297 rows held out, 13,258 resolved,
7 wrong. Then the run, as 2026-09-v1, in 142 seconds.
| Result | Rows |
|---|---|
| Carry their shift | 137,964 (99.21%) |
| Duplicates, left empty on purpose | 500 |
| A job but no shift | 321 |
| Nothing | 275 |
All six verification checks returned zero or the expected distribution, run
twice: by the engineers on production, and again on the clean room the next day.
The log holds 278,739 rows, so every change can still be reversed with
--undo=2026-09-v1.
Around 60 to 70 shifts across the whole table are likely wrong, extrapolating
the test's 7 in 13,258. That was the accepted price of filling 99.21% instead of
89%. slot_match records which step made each call, so a suspicious badge can
be traced to its step and that step alone can be undone.
Phases 3 and 4. Still to do
Phase 3 points the readers at the new columns. The reports page matches on
app_user_id and slot_id instead of company alone. Two broken subqueries that
joined slot_user_badges.id to badges.id — comparing a row number with a badge
id, so they returned almost nothing for years — get the correct user_badges
subquery. Then the app stops writing slot_user_badges, the two jod_jobs
badge columns and slot_user_id.
Phase 4 removes them, at least two weeks later: one table
(slot_user_badges), three columns, and the 500 duplicate rows. Everything is
copied aside first, and an RDS snapshot under an hour old is a precondition.
Why two weeks. Rolling the phase 3 code back needs the old schema to still exist. Two weeks is long enough for anything that still reads the old columns to show itself.
The decisions, in one place
| Decision | Why |
|---|---|
| New columns, not a rename | The type changes, and the migration runs while the old code is still serving. |
Unique key, not a foreign key to slot_user | slot_id is nullable; a two-column foreign key cannot empty one column, and Laravel cannot express it. |
RESTRICT on the jodder, SET NULL on job and shift | A badge is company data. The old CASCADE could delete it by accident. |
Keep jod_job_id even though slots.jod_job_id exists | Some old rows get a job but no shift, and job-level queries then need no join. |
A slot_match column | Anyone can tell an app-written row from a filled-in one, and which step filled it. |
| A log table with before and after values | Makes the backfill reversible, one run or one step at a time. |
| Accept a guessed shift only on a 24-hour gap | Measured. Above the line 98.7% right, below it 92.5% and 61.5%. |
| Rows with close candidates keep an empty shift | A wrong shift is worse than no shift. They still count in company-level lists. |
| Remove already-claimed shifts before the guess | One shift carries one badge, so this can only remove wrong candidates. |
| The test must pass before the run | The answer key existed, so there was no reason to run blind. |
| Copy, snapshot and wait before dropping anything | Rolling back phase 3 needs the old schema. |
| The MySQL 8.4 upgrade is a separate, later task | Phase 0 removed the blocker. The upgrade itself also needs PHP and driver work. |
What we learned along the way
Six things that were not in the plan when we started.
eber_points_logs turned out to be the best signal, and we nearly missed it.
The app writes a row there right after the feedback commits, naming the job and
the shift's exact start and end. Nobody had thought of it as evidence. Adding it
as a step lifted the result from about 89% to 99.21%, and because it runs before
the guess, it also cut the guess's errors from 8 to 3 per 2,577 rows. One
subtlety: the row lands after three or four calls to Eber's API, so a
ten-second search window misses 31% of them. The window has to be ten minutes.
A second round of the guess was safe after all. The plan forbade a guessed
shift from removing a candidate for another guessed row, in case a wrong pick
misled its neighbour. Measured, one extra round was right 23 times out of 23. It
runs, tagged slot_schedule_2 so it can be undone on its own.
MySQL rejects COUNT(DISTINCT ...) OVER (PARTITION BY ...). The plan's SQL
used it. Both 8.0 and 8.4 refuse. It became a GROUP BY aggregate joined back.
MySQL chose the wrong index and turned 5 seconds into 4 minutes. With stale
statistics it walked slot_user_badges.badge_id, which has 25 values across
139,000 rows. The step now forces the right index, and ANALYZE TABLE is part
of the runbook.
A branch we measured as dead was alive. "Close candidates, all in one job, so write the job only" caught zero rows in every sample. On production it caught 17. Rare, but real.
Small numbers reproduced almost exactly. The 500 duplicates predicted from a sample were 500 exactly. Eber coverage predicted at 84.80% came out at 84.64%. The measurements were worth the day they cost.
For engineers
- How to run the command, read its report, and verify a run: Company badges backfill: ramp-up for engineers.
- The full plan with every SQL statement, and the implementation handovers, live
as
ISSUE-2695-*.mdfiles in thejodgig-apirepository root. Ask Ali. - A separate bug found on the way: #2706. The feedback form can return an error after a submit that actually worked, because the Eber call runs after the transaction commits. Not part of this repair.
Sources for the MySQL behaviour: foreign key constraints, online DDL operations, and MySQL bug 114838, which reports that 8.4 requires a unique key for a foreign key.