User bans

A user ban stops one jodder from working for one client. Ops create it. The client never sees it and never manages it.

This page is the overview. Two pages hold the details:

• Read paths: the three places where the code checks user_bans
• Write paths: the four things that write user_bans rows

Ali settled the decisions on this page on 2026-09-16 and 2026-09-17. The first draft of this design was a page named "Banned users" in docs PR 132. That page is deleted. The section "Differences from the first draft in docs PR 132" keeps the record of what changed and why.

Project files on Google Drive

• The user bans project folder on Google Drive
• The copy of the banned jodders sheet that ops keep, the source for bans:import
• A shortcut to the original banned jodders sheet that ops edit today

Both sheets hold jodder names and client complaints. Do not copy their rows into this repo, into an issue, or into a pull request.

Reasoning for user_bans

The problem: McDonald's asked ops to select applicants by hand

McDonald's told JOD which jodders they do not want. The system kept showing those jodders to hiring managers. The auto-select cron kept selecting them. So McDonald's asked ops to select applicants by hand. Their reason: "your system cannot handle banned users".

McDonald's runs a large event in October 2026. On 2026-09-16 they already had 1,558 October jobs in the database. Selecting by hand for that many jobs would overload ops.

The goal is wider than McDonald's. A client may say: "turn off auto-select, you keep selecting people we banned". The answer must be: the system already excludes them.

What exists today: one hard-coded excluded user

There is no ban table. There is one hard-coded user id in JodJobService::SOFT_EXCLUDED_APPLICANT_IDS (app/Services/JodJobService.php:186). That user can apply anywhere. Two places skip that user:

• the applicant list a hiring manager sees for a job
• the auto-select cron

Nothing else skips them. The select endpoint would still accept their id. That constant shipped on 2026-07-05 in PR #2622. The user last applied on 2026-06-29.

Why a user_bans table, and why these rules

Rule	Reason
A banned jodder cannot apply to the client's jobs. The app shows a short, kind message.	If the jodder could apply, the code would write a job_user row. Today every new job_user row causes three things: - the apply code adds 1 to jod_jobs.no_users_apply_job - RecalculateApplicantRankingJob gives the row an applicant_rank - the applicant list shows the row to the hiring manager Each of the three would need its own ban rule. Writing no row means none of that code has to know about the ban. The jodder sees a red toast. The text is the one under user_banned_at_location or user_banned_at_company. The Apply button stays enabled. See read paths, place 1. Applications made before the ban already have a row. The next rule covers those.
Creating a ban cancels the jodder's waiting applications at that client.	After the ban is saved, the jodder has nothing waiting at that client. - the hiring manager's applicant list has nothing of theirs to show - jod_jobs.no_users_apply_job goes down by one per cancelled application, the same as a withdrawal. So the number on the job card and the list agree. - the auto-select cron has nothing of theirs to pick - selected shifts stay as they are
The select endpoint rejects a request that includes a banned jodder.	This is a second check. It covers the short time between the ban and a page the manager opened before it. - the endpoint accepts any id with job_user.status: 1 - it does not read the applicant list - a page opened before the ban still holds the id - the check is one query
The auto-select cron excludes banned jodders inside its SQL.	This is also a second check. It covers a cron run that loaded its jobs before the ban. - the cron takes the first row by applicant_rank - if that row fails a later check, the cron gives up on the job. It never tries the second row. - a check inside the SQL makes the query return the second row instead
One row can cover every outlet of a client.	The Google Sheet ops keep today says "all McDonalds". - McDonald's has 79 outlets today and opens more - one row per outlet stops covering the client when the next outlet opens - location_id: NULL covers outlets that do not exist yet
A lifted ban stays in the table.	Ops need to see who banned whom, when, and who lifted it. - the audit package does not record console commands (config/audit.php:168) - bans:import runs as a console command - so the table carries banned_by, banned_at, lifted_by and lifted_at itself
A ban is always for one client. company_id is required.	A jodder who must be kept off every client is a different case. - ops suspend the account for that - a suspended account cannot apply anywhere, and no manager sees it - a table of per-client bans should not carry a second meaning

Differences from the first draft in docs PR 132

First draft said	This design says	Why
The jodder can still apply.	The jodder cannot apply. The app shows a short, kind message.	Ali's decision 1. No stored application means no work for the count, the ranking or the list.
The applicant is removed from the applicant list.	Creating the ban cancels the application. There is nothing left to remove.	Ali's decision on 2026-09-17. jod_jobs.no_users_apply_job goes down with the cancellation, so the number on the job card and the list agree.
Excluding the applicant from the list also excludes them from ranking and auto-select.	Auto-select has its own query. It needs its own condition.	Two facts in the code: - AutoSelectApplicantService::determineTopApplicant does not pick from "ranked applicants". It reads job_user rows with status: 1, orders them by applicant_rank, and takes the first row. There is no IS NOT NULL filter. - an applicant that RecalculateApplicantRankingJob skipped has applicant_rank: NULL. MySQL sorts NULL first. So a banned jodder left out of ranking is picked first, not skipped. With decision 1, no new application from a banned jodder exists. Creating the ban cancels the old ones. So the ranking input needs no change at all.
The methods getApplicantsByJobId and getApplicantsByJobIdOrderedByRank build the manager's list.	The manager's list is UserRepositoryEloquent::searchApplicantUserApplyJodJobByJobId.	getApplicantsByJobId feeds RecalculateApplicantRankingJob. getApplicantsByJobIdOrderedByRank has no callers.
Nothing about the select endpoint.	The select endpoint checks every id.	A page opened before the ban still holds the id.
A ban can be updated while active.	A ban cannot change. Ops lift it and create a new one.	The draft said both. This design keeps the stricter rule.
status: active or removed, plus removed_at.	lifted_at: NULL means active. There is no status column.	One column says the same thing.
Nothing about the Google Sheet ops keep today.	The command bans:import loads it, with a dry run first.	The sheet has names, not ids. Ops must confirm the matches.

Vocabulary

Name	What it is
jod_jobs	One row is one worker position on one shift. A client who needs four workers gets four rows.
job_user	One row is one jodder's application to one jod_jobs row. The primary key is (app_user_id, job_id).
job_user.status: 1	Applied and waiting. The constant is Constants::JOB_USER_STATUS_APPLIED. Selected is 2. Unselected is 3. Cancelled by a ban is 14, Constants::JOB_USER_STATUS_ADMIN_DISABLING_APPLICANT_REJECTED.
job_user.applicant_rank	A number. 1 is the best applicant for that job. RecalculateApplicantRankingJob writes it after every apply and every withdrawal.
jod_jobs.no_users_apply_job	The number of jodders who applied to that job. Code adds 1 on apply and subtracts 1 on withdraw. Creating a ban subtracts 1 for each application it cancels.
companies, locations	A client and its outlets. McDonald's is companies.id: 151 with 79 active outlets.
Hiring manager	A client user on the portal. Roles HQ, AREA, LOCATION, SUPER_HQ_EXTERNAL, SUPER_HQ_INTERNAL. They see applicants and select them.
Ops	Internal users. Roles SUPER_ADMIN, INTERNAL, SG_OPS. They create and lift bans.
Auto-select cron	Two commands that run at 09:00 and 17:00 Singapore time. They select one applicant per open job of the next day. Both call AutoSelectApplicantService::determineTopApplicant.
user_bans	The table this page defines. One row is one ban.
bans:import	The artisan command that loads the Google Sheet ops keep today into user_bans.
user_banned_at_location, user_banned_at_company	Two new error keys under messages.jobs.errors. The apply endpoint returns the first for an outlet ban and the second for a company-wide ban. Both apps show the text as it is. The texts are on the read paths page, place 1.
error_approve_applicant	An existing error key. The select endpoint already returns it for a time clash. A request with a banned id gets the same key.

user_bans table definition

Table user_bans {
  id            bigint      [pk, increment]
  user_id       bigint      [not null, ref: > users.id]
  company_id    bigint      [not null, ref: > companies.id]
  location_id   bigint      [null, ref: > locations.id]
  banned_reason text        [not null]
  banned_at     datetime    [not null]
  banned_by     bigint      [null, ref: > users.id]
  source        varchar(16) [not null]
  lifted_at     datetime    [null]
  lifted_by     bigint      [null, ref: > users.id]
  lift_reason   text        [null]
  created_at    timestamp   [not null]
  updated_at    timestamp   [not null]

  indexes {
    (user_id, lifted_at, company_id, location_id) [name: 'user_bans_lookup']
    (company_id, location_id, lifted_at) [name: 'user_bans_scope']
  }
}

Column	Why it exists
user_id	The jodder. Always a users row with user_type: APP.
company_id	The client. Required. A ban never covers more than one client.
location_id	The outlet. NULL means every outlet of company_id, including outlets created after the ban.
banned_reason	The client's words. Free text. It will contain names, so the clean-room rule for this column must blank it.
banned_at	When the ban started. For imported rows, the date in the sheet. For portal rows, the time ops pressed the button.
banned_by	The ops user who created the ban. NULL for imported rows, because bans:import runs as a console command with no user.
source	portal or seed. Says which write path created the row.
lifted_at	NULL means the ban is active. A value means ops lifted it at that time.
lifted_by	The ops user who lifted the ban.
lift_reason	Why ops lifted it. Free text. The clean-room rule blanks it, like banned_reason.

Two rules the table cannot enforce by itself:

Rule	Who enforces it
One active ban per (user_id, company_id, location_id).	The create endpoint and bans:import, inside their transaction. MySQL lets many rows share a NULL, so a unique index does not work.
location_id must belong to company_id.	The create endpoint and bans:import.

Copy database/migrations/2024_08_31_132226_create_eber_points_logs_table.php for the migration. It has the right column types (unsignedBigInteger) and the right foreign key style. Do not copy cancellation_penalties. That table uses INT for its ids and has no foreign keys.

List of user stories and use cases

#	As	I want	So that
1	Ops	to ban a jodder at one outlet, or at every outlet of a client	the client never sees that jodder selected again
2	Ops	a ban to last until I lift it	a ban never stops working on its own
3	Ops	to lift a ban and record why	I can correct a mistake, and the record shows who did it
4	Ops	to see every active ban, filtered by client and outlet	I can review what is in effect
5	Ops	to load the bans we track in a spreadsheet today	the October jobs are protected before the first shift
6	Ops	bans:import to show me every name match before it writes anything	a jodder with a common name is not banned by mistake
7	Hiring manager	a banned jodder to be unselectable on my jobs	I do not need to ask JOD to select by hand
8	Hiring manager	the applicant count and the applicant list to agree	I do not open a ticket about a missing applicant
9	The auto-select cron	to skip banned jodders and take the next best applicant	the job still gets filled
10	A banned jodder	the app to tell me kindly that this outlet is not taking my applications	I am not left guessing, and I keep applying elsewhere
11	JOD	to tell any client "the system already excludes your banned jodders"	no client has a reason to turn off auto-select or hand us their selection

Worked example: one jodder banned from McDonald's

Jodder 555 and jodder 777 are made up. Job 9001 is a McDonald's Bendemeer shift on 3 October.

1. On 20 September jodder 555 applies to job 9001. The apply code writes a job_user row with status: 1. Job 9001 now has no_users_apply_job: 1.
2. On 21 September jodder 777 applies. Job 9001 now has no_users_apply_job: 2. RecalculateApplicantRankingJob gives 555 applicant_rank: 1 and 777 applicant_rank: 2.
3. On 22 September McDonald's tells ops to ban jodder 555 from all outlets. Ops create one user_bans row: user_id: 555, company_id: 151, location_id: NULL. The same transaction cancels jodder 555's application on job 9001: job_user.status: 14. Job 9001 goes back to no_users_apply_job: 1. RecalculateApplicantRankingJob runs for job 9001 and gives jodder 777 applicant_rank: 1. Jodder 555 gets no message. Job 9001 leaves their pending list.
4. The same day a hiring manager opens job 9001. The card says 1 applicant. The list shows jodder 777.
5. On 25 September jodder 555 applies to another McDonald's job. The ban covers every outlet, so the API returns user_banned_at_company. The app shows: "We're sorry, this employer is not accepting your applications right now. Other employers are waiting for you, so please keep applying." The code writes no row.
6. On 2 October at 09:00 the auto-select cron reaches job 9001. It finds one applicant, jodder 777, and selects them. Jodder 555's row keeps status: 14.
7. On 15 October ops lift the ban. lifted_at = that moment. lifted_by = the ops user. Jodder 555 applies to a November shift and succeeds.

Read paths for user_bans

Three places check whether a jodder is banned. Each one uses the same SQL condition, written once. Two more endpoints serve the ops page: one lists bans, one lists the applications a ban would cancel. The read paths page has the condition, the file and line for each place, and the rules for each.

The diagram follows one application. Blue boxes read user_bans. Grey boxes do not change. The two lists need no change. After a ban, the jodder has no waiting application for them to show.

#	Place	File	Rule
1	Jodder applies	app/Services/JodJobService.php:1394-1497	Returns user_banned_at_location for an outlet ban. Returns user_banned_at_company for a company-wide ban. Writes no row.
2	Hiring manager selects	app/Services/JodJobService.php:2459-2533	One query over all ids, before DB::beginTransaction() at line 2506. Any banned id rejects the whole request. A second check, for a page opened before the ban.
3	Auto-select cron picks the top applicant	app/Services/AutoSelectApplicantService.php:232-244	The condition is inside the SQL. A second check, for a run that loaded its jobs before the ban. One log line per job where it removed an applicant.
4	Ops list bans	New UserBanController, GET /portal/user-bans	Paginated. Loads the jodder, the client, the outlet and the two ops users with with(). Builds the JSON by hand. See Read paths: the ops list.
5	Ops see what a ban would cancel	GET /portal/applicantuser/{id}/pending-applications	The jodder's waiting applications at the client. The same query the create endpoint cancels with. See Read paths: the applications a ban would cancel.

The places that do not read the table, and why: Read paths: what does not check user_bans.

Write paths for user_bans

Four things write the table. Creating a ban also cancels the jodder's waiting applications at that client. That lowers jod_jobs.no_users_apply_job on those jobs, the same as a withdrawal does. Nothing sends a notification. Nothing cancels a selected shift.

#	Writer	Writes	Detail
1	Ops create a ban on the portal	One row with source: portal and banned_by = the ops user. Cancels the jodder's waiting applications at that client.	Write paths: ops create a ban
2	Ops lift a ban on the portal	lifted_at, lifted_by and lift_reason on one row.	Write paths: ops lift a ban
3	The command bans:import	One row per confirmed sheet row, with source: seed and banned_by: NULL. Cancels waiting applications the same way the portal does.	Write paths: the import command bans:import
4	The migration	The table only.	Write paths: the migration

Guidelines for every pull request

• The SQL condition lives in one place. Every read path calls it.
• The auto-select check is inside the SQL of determineTopApplicant. It is not a PHP if after the query.
• The select check runs before DB::beginTransaction(). No new return inside a transaction.
• jod_jobs.no_users_apply_job changes only in the statement that cancels waiting applications. It follows the same rule as a withdrawal.
• Writes use a DB::transaction closure. Only database writes go inside it.
• New test files are listed by name in phpunit.xml. The Feature suite does not scan the folder. The PR description shows the phpunit output, because CI runs no tests.
• database/clean_room/manifest.csv has a rule for user_bans. The rule blanks banned_reason and lift_reason.
• No jodder name and no reason text appears in the repo, in a test fixture, or in a PR description.

Order of work:

1. Backend: the table, the migration, the three read paths, the cancellation of waiting applications, tests. On prod before the first October selection window.
2. Backend: bans:import, then the first import with ops.
3. Backend: the ops endpoints to list, create and lift. A copy of cancellation penalties, with the endpoint paths from the write paths page.
4. Frontend: the ops page.