Company badges backfill: ramp-up for engineers

Written 2026-08-31. For an engineer who takes over the badge repair with no earlier context. Read it top to bottom once. It takes about 20 minutes.

This page is the operating guide for Phase 2 of the plan explained step by step in Company Badges Repair, the plan explained with pictures. Read that page first if you want the why. This page is the how.

Issue: https://github.com/jod-app/PORTAL_V2_BACKEND_GLOBAL/issues/2695. Parent: https://github.com/jod-app/roadmap/issues/74.

1. What this is about

When a jodder finishes a shift, the app asks for feedback. On that form the jodder can give the company a badge, for example "good teamwork". Each badge is stored as one row in the table company_badge_assignments.

That row should say four things: which jodder, which badge, which company, and which shift. For two years the app stored only the first three. So the reports page cannot show a badge on the shift it was given for.

The repair has three parts. Two are done. This document is about the third.

Part	What	State
Schema	New columns on company_badge_assignments that hold the shift, and a log table	Done. Live on production since 2026-08-30.
App	The feedback form now writes the shift on every new badge row	Done. Live since 2026-08-30 21:50 SGT.
Backfill	A command that works out the shift for the 138,742 old rows	Written. PR #2709. Not run yet. This document.

2. Words used in this document

Word	Meaning
jodder	A worker. A row in users with user_type = 'APP'.
job	A posting by a company. A row in jod_jobs. A job has one company and usually one location.
shift	One working period inside a job. A row in slots, with slot_start_date and slot_end_date. One job has one or more shifts.
slot_user	The table that says "this jodder worked this shift". Primary key (app_user_id, slot_id). Its column has_feed_back is 1 when the jodder sent the feedback form for that shift.
job_user	The table that says "this jodder was selected for this job". Its updated_at is stamped when the jodder sends feedback.
badge row	One row in company_badge_assignments. One badge, given by one jodder, to one company, for one shift.
company_badge_assignments.created_at	The moment the jodder pressed submit on the feedback form. Every step below works from this timestamp.
slot_user_badges	An old twin table. The app wrote one row here for every badge row, in the same transaction. Nothing reads it. It goes away in the last phase.
jod_jobs.slot_user_badge_id	An old column on the job that points at the twin row of the last feedback given on that job.
eber_points_logs	Eber is the loyalty points partner. Right after a feedback form is saved, the app sends points to Eber and logs one row here. The row names the jodder, the job, and the shift's start and end.
tier	One step of the backfill that uses one kind of evidence. The command runs the surest tier first.
slot_match	A new text column on the badge row. It records which step found the shift.
company_badge_backfill_log	A new table. One row for every decision the command makes, with the old and the new values, so a run can be reviewed and undone.
dry run	A run of the command that decides everything and writes nothing. The default.
run version	A label you give a real run, for example 2026-09-v1. Every log row carries it. --undo uses it.

3. The problem, in one example

Aisha (users.id 50123, a fictional jodder) worked a shift at company 268 on 14 March 2026, 09:00 to 17:00. At 18:42 she sent the feedback form and gave badge 11, "good teamwork". The app wrote:

Table	Row	Columns written in March
company_badge_assignments	id 120001	company_id: 268, badge_id: 11, slot_user_id: 50123 (this column holds the jodder's users.id despite its name), location_id: 2037, created_at: 2026-03-14 18:42:10
slot_user_badges	id 119870	slot_user_id: 50123, badge_id: 11, created_at: 2026-03-14 18:42:10
jod_jobs	id 476000	slot_user_badge_id: 119870
slot_user	(50123, 764000)	has_feed_back: 1

Nothing says "shift 764000". If Aisha worked three shifts for company 268 that week, the reports page cannot tell which one got the badge.

Since 2026-08-30 the app writes four more columns on every new badge row: app_user_id (the jodder, same value as slot_user_id), jod_job_id, slot_id, and slot_match: 'app'. The old rows still have jod_job_id, slot_id and slot_match empty. The backfill fills them.

4. The columns the backfill fills

On company_badge_assignments:

Column	Filled with	Rule
jod_job_id	The job	Only when a step is sure.
slot_id	The shift	Only when a step is sure, or when a guess is clearly better than the next one. A wrong shift is worse than no shift.
slot_match	The name of the step that set slot_id. When only the job was found, the name of the step that set jod_job_id.	See the table in section 5.
location_id	The job's location, where the row has none
app_user_id	slot_user_id, where empty	36 rows today, written in the two hours between the schema deploy and the app deploy.

Every change also writes one row in company_badge_backfill_log: the run version, the step (tier), how many candidates the step saw (job_candidates, slot_candidates), the distance to the second-best shift (runner_up_gap_minutes), and every old and new value (old_slot_id, new_slot_id, and so on). Rows a step looks at and leaves empty get a log row too, with the reason in the candidate columns.

5. What the command does

Command: php artisan badges:backfill-slots. Code: app/Console/Commands/BackfillCompanyBadgeSlotsCommand.php and one class per step under app/Services/BadgeBackfill/.

The idea

The command cannot ask anyone which shift a badge was for. It has to deduce it from traces the app left in other tables at the moment the jodder pressed submit. Some traces are certain. One is a guess. So the command works in steps, surest first. Each step only looks at rows the earlier steps left open, and never overwrites a value another step or the app wrote.

The steps, in order

#	Step	slot_match it writes	Evidence	What it finds	How sure
1	Duplicates	duplicate	The same jodder, company and badge within 10 seconds of the previous row. Two taps on submit.	Marks the later row. Leaves it empty.	Certain. About 500 rows.
2	Tier 1	jod_jobs_column	The twin row in slot_user_badges (same jodder, same badge, created_at within 2 seconds), and the job whose jod_jobs.slot_user_badge_id points at it. Exactly one twin and one job.	The job	Certain. About 89% of rows.
3	Tier 2	job_user_time	job_user.updated_at within 2 seconds of the badge's created_at. Exactly one.	The job	Certain.
4	Eber	eber_log	Exactly one eber_points_logs row of the jodder written between 5 seconds before and 10 minutes after the badge. It names the job and the shift's start and end. Exactly one slots row matches, and the jodder has has_feed_back = 1 on it. If tier 1 or 2 already gave a job, Eber's job must be the same.	The job and the shift	99.98% measured. Covers rows from 2024-09-27, when the Eber table starts (90% of rows).
5	Shift inside a known job	keeps jod_jobs_column or job_user_time	The row has a job but no shift. Look at the jodder's feedback shifts in that job.	The shift, when there is one, or when the nearest is 24 hours clearer than the next	High.
6	Tier 3	slot_schedule	The row has nothing. Look at all the jodder's feedback shifts at that company (and location, when set) that started in the 90 days before the badge. Remove every shift another badge row already holds. Take the shift whose end is closest to the badge time.	The shift, only when it is the single candidate or the second-closest is 24 hours or more further away. If the close candidates are all in one job, the job only.	A guess. 99.4% right where it accepts.
7	Second round	slot_schedule_2	Step 6 again on the rows still empty, with the shifts step 6 just accepted also removed.	A few more shifts	0 wrong in every measured sample.
8	Location fill	unchanged	The row has a job and no location	location_id
9	app_user_id copy	unchanged	app_user_id is empty	app_user_id = slot_user_id	36 rows.

Why the order matters: steps 2 to 5 are the sure ones. Step 6 is the guess. Every shift a sure step claims is taken away from the guess's list of candidates. One shift carries one badge, so removing a claimed shift can only remove a wrong answer. That is why the guess runs last.

Aisha's row through the steps

Step	What happens to row 120001
1	Her previous badge row was days earlier. Not a duplicate.
2	Twin row 119870 is found (same jodder, badge 11, same second). Job 476000 points at it. jod_job_id: 476000, slot_match: jod_jobs_column.
3	Skipped. The row already has a job.
4	One Eber row for Aisha at 18:43:05, inside the window. It says job 476000, start 2026-03-14 09:00, end 17:00. Slot 764000 has those values, and Aisha has has_feed_back = 1 on it. Eber's job equals the known job. slot_id: 764000, slot_match: eber_log.
5 to 9	Skipped. The row is complete.

Now suppose Aisha's Eber row was never written (she had not clocked out through the app). Step 4 finds nothing. Step 5 looks at her feedback shifts in job 476000: shift 764000 ended at 17:00 on 14 March, 1 hour 42 minutes before the badge; shift 763990 ended at 17:00 on 13 March, 25 hours 42 minutes before. The second is more than 24 hours further away, so the first is accepted. slot_id: 764000, slot_match stays jod_jobs_column.

If the two shifts had ended 3 hours apart, step 5 would leave slot_id empty, and log the row with runner_up_gap_minutes: 180. The row keeps its job. The reports page shows the badge at company level, not on a shift.

Where the rules come from

Every rule was measured on a copy of production before the command was written, using rows whose shift was known for certain, hiding the answer and finding it again:

Measure	Result
Tier 3 with the 24-hour rule	97.4% of hidden rows get a shift, 99.68% of them right.
Eber added before tier 3	99.6% get a shift. 3 wrong out of 2,577.
Expected on the whole table	About 540 rows stay without a shift, most of them older than the Eber table.

The measurements and the queries are in ISSUE-2695-HANDOVER-2.md, section 6a, in the repo root of jodgig-api (untracked file; ask Ali for it).

6. Safety

Protection	How
It writes nothing unless told to	Without --run-version the command is a dry run. It decides everything, prints the full report, and writes to no table. It works on a temporary copy of the rows that lives only inside the command's database connection.
It asks before writing	With --run-version it asks "Go on?". --yes skips the question.
A label cannot be reused	If the log already has rows for that run version, the command refuses.
Every write is logged first	For each step: log rows first, badge rows second, in one transaction.
Every run can be undone	--undo=<version> puts every old value back from the log and deletes the log rows. --only=<step> undoes one step. An undo is refused if a later run changed the same rows.
Step by step	Each step is its own transaction. If a step fails, the earlier steps stay written and can be undone.
The shift-level test never writes	--test runs the whole thing on rows whose shift is known and reports how often it found the same shift.

7. How to run it

Options

Option	Meaning
none	Dry run. Prints the report. Writes nothing.
--run-version=<label>	Real run. Label of at most 32 characters, for example 2026-09-v1.
--yes	Do not ask before writing.
--undo=<label>	Put back every row of that run.
--only=<step>	With --undo: only that step, for example slot_schedule_2.
--gap-hours=<n>	The 24-hour rule of steps 5 and 6. Default 24. Do not change it without a new measurement.
--no-second-round	Skip step 7.
--test	The shift-level test. Never writes.
--test-holdout=<n>	With --test: hide 1 known answer in n. Default 9.
--test-sample=<n>	With --test: only jodders with MOD(app_user_id, n) = 0. Default 1 (everyone).
--limit-user-mod=<n>	Dry run on a sample of jodders only. Refused with --run-version.

Where

Inside the application container on jodgig.prod.cron or jodgig.prod.api-3, the same way deploy.sh runs php artisan migrate:

sudo docker exec <container> php artisan badges:backfill-slots

The runbook, in order

1. Deploy PR #2709 (merge-prod). Nothing runs on deploy.
2. Check the database user's rights. The command builds its candidate lists in temporary tables, which need the MySQL right CREATE TEMPORARY TABLES. Without it the command stops at its first statement and writes nothing.

   php artisan tinker --execute='print_r(DB::select("SHOW GRANTS"));'

   Look for CREATE TEMPORARY TABLES or ALL PRIVILEGES.
3. Refresh the index statistics. Without this MySQL chose the wrong indexes for tier 1 on a test set and took minutes instead of seconds:

   ANALYZE TABLE company_badge_assignments, slot_user_badges, jod_jobs, slots, slot_user, job_user, eber_points_logs;

4. Rehearse on a copy first, if you can. The clean room (jodgig_ai) is a copy of production rebuilt every night. Its server has no PHP, so point a container's DB_HOST, DB_DATABASE, DB_USERNAME and DB_PASSWORD at it and run steps 5 to 7 there. If you cannot, steps 5 and 6 are safe on production: they only read.
5. The shift-level test. It must print PASS (99% or more same shift).

   php artisan badges:backfill-slots --test

6. The dry run. Read the report (section 8). The numbers should look like the measurements in section 5: about 89% tier 1, most of the rest Eber, a few hundred rows left empty.

   php artisan badges:backfill-slots

7. The real run. Takes under two minutes.

   php artisan badges:backfill-slots --run-version=2026-09-v1 --yes

8. The six checks, in MySQL. Every one must return what the comment says.

-- 1. Rows per step. Compare with the dry run.
SELECT slot_match, (slot_id IS NULL) AS slot_empty, COUNT(*)
FROM company_badge_assignments GROUP BY 1, 2;

-- 2. One badge per jodder per shift. Expect no rows.
SELECT app_user_id, slot_id, COUNT(*) FROM company_badge_assignments
WHERE slot_id IS NOT NULL GROUP BY 1, 2 HAVING COUNT(*) > 1;

-- 3. The job belongs to the company, and the shift to the job. Expect 0 and 0.
SELECT COUNT(*) FROM company_badge_assignments c JOIN jod_jobs j ON j.id = c.jod_job_id
WHERE j.company_id <> c.company_id;
SELECT COUNT(*) FROM company_badge_assignments c JOIN slots s ON s.id = c.slot_id
WHERE s.jod_job_id <> c.jod_job_id;

-- 4. The shift is one the jodder gave feedback on. Expect 0.
SELECT COUNT(*) FROM company_badge_assignments c
LEFT JOIN slot_user su ON su.slot_id = c.slot_id AND su.app_user_id = c.app_user_id
WHERE c.slot_id IS NOT NULL AND (su.slot_id IS NULL OR su.has_feed_back <> 1);

-- 5. Every old row has a log row for this run. Rows the app wrote (slot_match 'app') have none. Expect 0.
SELECT COUNT(*) FROM company_badge_assignments c
LEFT JOIN company_badge_backfill_log l ON l.company_badge_assignment_id = c.id AND l.run_version = '2026-09-v1'
WHERE l.id IS NULL AND (c.slot_match IS NULL OR c.slot_match <> 'app');

-- 6. The giver is a jodder. Expect 0.
SELECT COUNT(*) FROM company_badge_assignments c JOIN users u ON u.id = c.app_user_id
WHERE u.user_type <> 'APP';

9. If anything looks wrong:

   php artisan badges:backfill-slots --undo=2026-09-v1

   Then fix, and run again with 2026-09-v2.

8. Reading the report

The dry run and the real run print the same report. An example from the test fixtures:

+------------------+-----------------+------------+---------+------------+---------+
| step             | slot_match      | considered | written | left empty | seconds |
+------------------+-----------------+------------+---------+------------+---------+
| duplicate        | duplicate       | 15         | 1       | 14         | 0.004   |
| jod_jobs_column  | jod_jobs_column | 14         | 3       | 11         | 0.004   |
| eber_log         | eber_log        | 14         | 1       | 13         | 0.011   |
| slot_schedule    | slot_schedule   | 9          | 4       | 5          | 0.007   |
...
Step slot_schedule, rows left empty by reason:
  close in several jobs       2
  conflict                    2
  no candidate                1

slot_match over the badge rows in scope, after the run:
  (empty)                     4
  eber_log                    1
  jod_jobs_column             3
  ...
Rows left with slot_id IS NULL and slot_match IS NULL: 4
  id 1239567 last step slot_schedule_2 reason close in several jobs

Column or line	Meaning
considered	Rows the step looked at. Rows earlier steps settled are not counted.
written	Rows the step changed.
left empty	Rows the step looked at and did not change. They go on to the next step.
reasons	Why a step left rows empty. conflict means two badge rows wanted the same shift, so neither got it. close in several jobs means the candidate shifts were less than 24 hours apart and in different jobs.
slot_match over the badge rows	The final picture. (empty) rows have no job and no shift.
Rows left with slot_id IS NULL	The first 200 ids that ended with nothing, with the last step that looked at them and why.

The --test report adds a table per step with "same shift" and "different shift" counts, the tier 3 buckets, and ends with PASS or FAIL.

9. The code and the tests

What	Where
The command	app/Console/Commands/BackfillCompanyBadgeSlotsCommand.php
One class per step	app/Services/BadgeBackfill/DuplicateStep.php, JodJobsColumnStep.php, JobUserTimeStep.php, EberLogStep.php, SlotInKnownJobStep.php, SlotScheduleStep.php (used twice, for steps 6 and 7), LocationFillStep.php, AppUserIdCopyStep.php
The run: mirror table, log writer, counters	app/Services/BadgeBackfill/BackfillRun.php
Undo	app/Services/BadgeBackfill/BackfillUndo.php
The shift-level test	app/Services/BadgeBackfill/ShiftLevelTest.php
Tests, one per rule	tests/Feature/BadgeBackfillCommandTest.php

How a step is built: it reads the badge rows from a temporary mirror table (bb_work), builds its candidates in its own temporary tables with plain SQL, applies its rule and its conflict rule in SQL, fills one decision table, and hands it to BackfillRun::commitDecision(), which writes the log rows and the badge rows in one transaction and updates the mirror. No PHP loop touches a row. On a dry run only the mirror is updated.

To run the tests, use a database built from the migrations, never your working jodgig database. The tests create their own rows and clean up.

DB_HOST=127.0.0.1 DB_PORT=3306 DB_DATABASE=<scratch database> DB_USERNAME=root DB_PASSWORD= \
  php -d xdebug.mode=off vendor/bin/phpunit tests/Feature/BadgeBackfillCommandTest.php
# expected: OK (12 tests, 100 assertions)

To build such a database: CREATE DATABASE <name>; then DB_DATABASE=<name> php artisan migrate --force.

10. What comes after the backfill

Step	What	When
PR 5	The reports page shows a badge on its shift (company_badge_assignments.slot_id = slots.id). The "Employee Badges" column on /applicants/{id} reads user_badges like every other page. The app stops writing slot_user_badges, jod_jobs.company_badge_id, jod_jobs.slot_user_badge_id and company_badge_assignments.slot_user_id.	After the six checks pass.
Wait	Two weeks with PR 5 live.
PR 6	A migration that copies the old table and columns aside, deletes the 500 duplicate rows, makes app_user_id NOT NULL, and drops slot_user_id, the two jod_jobs columns and slot_user_badges. Needs an RDS snapshot first.	Two weeks after PR 5.
Later	The MySQL 8.4 upgrade. Its own task.

11. Where to read more

Document	What it has
PR #2709	The command. The description repeats the steps and the runbook.
PR #2703	The schema: what each new column and the log table mean.
PR #2707	The app change: how the form now writes the shift and refuses a second submit.
ISSUE-2695-HANDOVER-3.md (repo root, untracked, ask Ali)	The full state, every decision and why.
ISSUE-2695-HANDOVER-2.md section 6a	The measurements behind every rule, with the queries.
ISSUE-2695-FABLE-PLAN.md	The original plan with the SQL of every step.
Issue #2706	A separate bug: the feedback form returns an error after a submit that worked, because of the Eber call. Not part of this work.