Notifications

This page describes how notifications work in JodGig today.

JodGig is the legacy platform. It is written in PHP with Laravel. It is still running in production and still serving real users. Nothing on this page is a plan or a proposal. Everything here was read from the code in the jodgig-api repository. Every claim points to a file and a line number.

Two paths are covered:

The write path — how a notification row is created.
The read path — how the mobile app lists, reads, and deletes those rows.

Where the code could not answer a question, the page says so in Open questions.

Vocabulary

Read this list first. The rest of the page uses these words.

Tables

notifications — the table that holds one in-app notification for one user. Today this table is partitioned by day. See The tables.
notification_templates — the text of each message, before the system fills in the job title, outlet name, and dates.
email_sms_notifications — a log table. One row means "we sent this person an email or an SMS for this reason". It does not hold the message body.
jobs — the Laravel queue table. One row is one background task waiting to run.
failed_jobs — one row is one background task that failed after all retries.
users — every account: app users (Jodders) and portal users (managers).
jod_jobs — one gig job posted by a client.
slots — one working shift inside a gig job.
audits — the change log written by the owen-it/laravel-auditing package.

Columns

notifications.user_id — who receives this notification.
notifications.user_type — the kind of account. The comment in the migration says ADMIN, HQ, AREA, LOCATION, APP. In practice the write code always sets APP.
notifications.purpose — why this notification exists, for example applicant_push_noti_job_opening. It links the row back to a template.
notifications.subject and notifications.body — the message text. Both hold a PHP serialize() string, not JSON. See Known problems item 18.
notifications.action — a short word the mobile app uses to decide which screen to open, for example open_job, noti_page, applied_job, history_detail.
notifications.is_read — 0 means unread, 1 means read.
notifications.status — 1 means enabled, 0 means disabled. The read query only returns rows with status = 1.
notifications.deleted — 1 means the user pressed delete. See Known problems — the read query ignores this column.
users.ct — a flag that splits app users into two groups. ct = 1 means CleverTap has seen this account. ct = 0 means it has not. Two different code paths serve the two groups.
users.open_job_notification_status — the user's own setting: "tell me when new jobs are posted". 1 means yes.
users.user_setting_in_app_notification_status — the user's own setting for in-app notifications. 1 means yes.
users.device_key — the mobile device token the app sends after login.

Concepts

Channel — one way of reaching a person. JodGig has four live channels: in-app, push through CleverTap, email, and SMS.
Queue job — a unit of background work. The web request writes a row into the jobs table and returns. A separate worker process picks the row up and runs the work.
Partition — MySQL can split one table into several physical pieces. JodGig splits notifications into one piece per calendar day. Dropping a whole day is then one fast command instead of millions of DELETE statements.
Fan-out — one action creating many rows. In JodGig, one new gig job creates one notifications row for every eligible app user.

TL;DR

Channel	Where the code lives	What it writes	Queued or immediate	Who receives it
In-app	`app/Services/InAppNotificationService.php`	rows in `notifications`	queued — `App\Jobs\InAppNotification` and `App\Jobs\InAppNotificationByJobOpening`	app users with `ct = 0` (except the two acknowledgement reminders, which ignore `ct`)
Push through CleverTap	`app/Services/ClevertapCampaignService.php`, `app/Helpers/CleverTapHelper.php`	nothing in our database for the push itself; the same service also writes `notifications` rows	queued — `App\Jobs\CleverTapEventJob`	app users with `ct = 1`
Email	`app/Services/EmailNotificationService.php`	one row in `email_sms_notifications` per recipient	the row is written immediately; the send is queued through `App\Jobs\SendMail`	portal users and app users, depending on the method
SMS	`app/Services/SMSNotificationService.php`	one row in `email_sms_notifications`, written inside the queue job after a successful send	queued — `App\Jobs\SendSms`	portal users and app users, depending on the method

Two more services carry "Notification" in their name but do not send anything:

app/Services/NotificationSingService.php and app/Services/NotificationVNService.php both have exactly one method, addDeviceToken. It saves users.device_key. Nothing else. The two classes are identical in behaviour (NotificationSingService.php:21-28 and NotificationVNService.php:22-28).
app/Services/JodNotificationService.php is the read side only. It lists, marks as read, and deletes. It never creates a row.

The tables

`notifications`

This is the important one, and its history matters.

What happened to this table

The table was replaced twice. Here is the order, from the migration files:

Date	Migration file	What it did
2022-01-23	`2022_01_23_000557_create_notifications_table.php`	Created `notifications` with `$table->increments('id')`, an auto-increment integer key (line 20).
2025-04-13	`2025_04_13_182425_create_notifications_new.php`	Created a second table called `notifications_new` with `$table->uuid('id')->primary()` (line 17), plus foreign keys and an index on `created_at`.
2025-04-14	`2025_04_14_120731_drop_table_notifications.php`	Dropped the old integer-key `notifications` table.
2025-04-14	`2025_04_14_175304_rename_table_notifications_new.php`	Renamed `notifications_new` to `notifications` (line 16).
2025-05-08	`2025_05_08_161851_create_notifications_partitioned_table.php`	Created a third table, `notifications_partitioned`, with raw SQL. Primary key is `(id, created_at)`. Partitioned by day. Also created three stored procedures.
2025-05-09	`2025_05_09_233909_swap_notifications_tables.php`	Dropped `notifications` again and renamed `notifications_partitioned` to `notifications` (lines 21-24).
2025-06-02	`2025_06_02_153343_alter_table_notifications_add_user_id_index.php`	Added an index on `user_id`.

The answer to "what is the primary key today":

The primary key of notifications today is PRIMARY KEY (id, created_at), where id is char(36) holding a UUID string.
The migration that made it so is 2025_05_09_233909_swap_notifications_tables.php. It renamed the partitioned table over the top of the plain UUID table.
The table notifications_partitioned no longer exists under that name. It is notifications.
The uuid('id')->primary() from 2025_04_13_182425_create_notifications_new.php is not the live definition. That table was dropped one day before the swap.

MySQL requires every unique key on a partitioned table to include the partition column. created_at is the partition column. That is why the primary key is a pair, not just id.

Columns today

Read from the CREATE TABLE statement in 2025_05_08_161851_create_notifications_partitioned_table.php, lines 31-50, plus the index migration of 2025-06-02.

Column	Type	Null	Default	Meaning
`id`	`char(36)`	no	—	A UUID string. The application generates it with `Str::uuid()`.
`user_id`	`bigint unsigned`	yes	`NULL`	The receiver.
`user_type`	`varchar(255)`	no	—	Comment says `ADMIN, HQ, AREA, LOCATION, APP`. The write code always sets `APP`.
`job_id`	`bigint unsigned`	yes	`NULL`	The gig job this is about.
`slot_id`	`bigint unsigned`	yes	`NULL`	The shift this is about, when there is one.
`purpose`	`varchar(255)`	no	—	The template key, for example `applicant_push_noti_selection`.
`subject`	`varchar(255)`	no	—	A PHP serialized string holding one title per language.
`body`	`longtext`	no	—	A PHP serialized string holding one body per language.
`action`	`varchar(255)`	no	—	Which screen the app should open.
`is_read`	`tinyint(1)`	no	`0`	`0` unread, `1` read.
`status`	`tinyint(1)`	no	`1`	`1` enabled, `0` disabled.
`deleted`	`tinyint(1)`	no	`0`	`1` after the user pressed delete.
`created_at`	`DATETIME`	no	`CURRENT_TIMESTAMP`	The partition column.
`updated_at`	`timestamp`	yes	`NULL`	Last change.

Indexes and keys today:

PRIMARY KEY (id, created_at).
notifications_user_id_index on user_id, from 2025_06_02_153343_alter_table_notifications_add_user_id_index.php.
No foreign keys. MySQL does not allow foreign keys on partitioned tables. The foreign keys written in 2025_04_13_182425_create_notifications_new.php lines 31-33 belong to the table that was dropped. They are gone.
No index on created_at. The index in 2025_04_13_182425_create_notifications_new.php line 35 also belonged to the dropped table. The read query still filters on created_at, but partition pruning handles that instead of an index.

What one row means in plain words: one message, for one person, about one gig job, waiting in that person's notification list in the mobile app.

Partition scheme

From 2025_05_08_161851_create_notifications_partitioned_table.php, line 48:

PARTITION BY RANGE (TO_DAYS (created_at)) (
  PARTITION p_20250504 VALUES LESS THAN (TO_DAYS ('2025-05-05')),
  ...
  PARTITION p_future VALUES LESS THAN MAXVALUE
);

One partition per calendar day.
The name is p_ plus the date, for example p_20260805.
The boundary is the next day. Partition p_20260805 holds rows where created_at is on 2026-08-05.
p_future catches anything past the last named partition. It stops inserts from failing when a partition is missing.

`notifications_partitioned`

This table name no longer exists in production. 2025_05_09_233909_swap_notifications_tables.php renamed it to notifications.

One command still refers to the old name: app/Console/Commands/SyncNotificationsPartitioned.php. It was the tool used to copy rows across during the swap. It reads notifications and inserts into notifications_partitioned (lines 34 and 53-55). After the rename, the target table is gone, so the command cannot work. It is not in the schedule. Treat it as dead code.

`notification_templates`

Created by 2022_01_21_230116_create_notification_templates_table.php, then changed by four later migrations.

Column	Type	Added by	Meaning
`id`	`bigint` auto-increment	create migration	—
`type`	`smallInteger`	create migration	`1` email, `2` SMS, `3` in-app. See `app/Constants/Constants.php:167-169`.
`purpose`	`string`	create migration	The key the code looks up, for example `applicant_push_noti_ack2`.
`name`	`string`, nullable	`2022_05_12_150640_...`	A friendly name shown in the admin screen.
`subject`	`string`	create migration	The title.
`body`	`longText`	create migration	The message text, with placeholders such as `{job_title}`.
`description`	`longText`, nullable	`2022_03_09_014257_...`	An explanation for the admin screen.
`status`	`smallInteger`	create migration	`1` enabled, `0` disabled.
`is_available`	`boolean`, nullable, default `true`	`2022_05_12_181832_...`	Returned to the admin screen by `listNotificationTemplate` (`NotificationTemplateRepositoryEloquent.php:87`) and set to `1` by `CreateNewNotificationTemplateCommand.php:69`. No PHP code filters on it. What the admin screen does with it is not visible from this repository.
`lang`	`string`	`2022_12_09_172807_...`	Comment says `en, vn, cn`.
`created_at`, `updated_at`	timestamps	create migration	—

A template is found by purpose plus type plus status = 1. Email and SMS lookups add lang = config('app.language_code') and return one row (app/Repositories/NotificationTemplateRepositoryEloquent.php:41-59). In-app lookups do not filter by language and return a collection of rows, one per language (lines 61-68).

Portal admins can switch templates on and off through POST /portal/configuration/update-notifications (routes/api.php:537). That endpoint only flips status (NotificationTemplateRepositoryEloquent.php:106-127).

What one row means in plain words: the text of one message, in one language, for one channel.

`email_sms_notifications`

Created by 2022_01_23_004901_create_email_sms_notifications_table.php.

Column	Type	Meaning
`id`	auto-increment integer	—
`job_id`	`bigint`, nullable	The gig job, when there is one.
`slot_id`	`bigint`, nullable	The shift, when there is one.
`sender_id`	`bigint`, nullable	Who caused the message. `NULL` when the system did.
`sender_user_type`	`string`	For example `HQ`, or the literal `SYSTEM` for OTP messages.
`receiver_id`	`bigint`, nullable	Who received it.
`receiver_user_type`	`string`	The receiver's account kind.
`purpose`	`string`	The template key.
`subject`	`string`	The subject line that was used.
`type`	`smallInteger`	`1` email, `2` SMS.
`sms_response`	`string`, nullable	What the SMS gateway answered. Only set on the SMS path.
`created_at`, `updated_at`	timestamps	—

2023_02_08_101204_add_foreign_key.php lines 69-83 added foreign keys on job_id, slot_id, sender_id, and receiver_id.

What one row means in plain words: "we sent this person an email or an SMS, for this reason, at this time". The message text is not stored. To see what was sent, look up the template by purpose.

How a notification gets created

All four channels at a glance

Channel 1 — in-app notifications

All in-app text is built in app/Services/InAppNotificationService.php. The file has nine public methods that create notifications. Every one of them follows the same five steps:

Load the template by purpose and type = 3.
Load the location and build the replacement values (job title, outlet name, dates, hourly rate).
Build one $data array with subject and body already filled in for both languages.
Collect the user ids that should receive it.
Dispatch a queue job.

Here is every method, its trigger, and its audience.

Method	`purpose`	Triggered by	Audience
`applicantPushNotiJobOpening` (line 101)	`applicant_push_noti_job_opening`	`JodJobController::storeJodJobForAreaUser` (line 236), `storeJodJobForHqUser` (line 379), `storeJodJobForLocationUser` (line 464), `approveJobForHqUser` (line 3604), `BulkJodJobCsvImportService` (line 187)	every app user with `ct = 0` and the notification settings on
`applicantPushNotiJobCancellation` (line 182)	`applicant_push_noti_job_cancellation`	`JodJobController::cancelMultipleJob` (line 1181), `cancelJodJob` (line 1740)	applicants on that job whose status is applied or selected, with `ct = 0`
`applicantPushNotiSelection` (line 247)	`applicant_push_noti_selection`	`JodJobController::approveApplicantSelectForJob` (line 1573)	applicants just selected, with `ct = 0`
`applicantPushNotiUnselection` (line 323)	`applicant_push_noti_unselection`	`JodJobController::approveApplicantSelectForJob` (line 1574)	applicants just unselected, with `ct = 0`
`applicantPushNotiRejection` (line 385)	`applicant_push_noti_rejection`	`JodJobController::rejectApplicantInJob` (line 1833)	one applicant, with `ct = 0`
`applicantPushNotiPartialRejection` (line 450)	`applicant_push_noti_partial_rejection`	`JodJobController::rejectApplicantInSlot` (line 1916)	one applicant, with `ct = 0`
`applicantPushNotiManagerFeedback` (line 515)	`applicant_push_noti_manager_feedback`	`Mobiles\JodJobController::doConfirmFormInSlot` (line 1297)	one applicant, with `ct = 0`
`applicantPushNotiAck1` (line 564)	`applicant_push_noti_ack1`	cron `command:jod_notification_ack1` through `JodJobNotiAck1Command` (line 75)	applicants who have not done the first acknowledgement — no `ct` check
`applicantPushNotiFeedbackReminder` (line 629)	`applicant_push_noti_feedback_reminder`	cron `command:jod_notification_feedback_reminder` through `JodJobFeedbackReminderCommand` (line 72)	applicants who clocked out but gave no feedback — no `ct` check
`applicantPushNotiAck2` (line 694)	`applicant_push_noti_ack2`	cron `command:slot_notification_ack2` through `JodJobNotiAck2Command` (line 74)	applicants who have not done the second acknowledgement — no `ct` check

Seven of the ten methods contain the test $appUser->ct === 0. Three do not. That asymmetry is not explained anywhere in the code.

The queue job that writes the rows

Two queue jobs write into notifications.

app/Jobs/InAppNotification.php is the simple one. It receives a list of user ids and one $data array:

Lines 43-47: for each user id, put a fresh UUID into $data['id'] and the user id into $data['user_id'], then push a copy of $data onto a list.
Lines 49-55: split the list into chunks of 100 and call Notification::insert($chunk->toArray()) for each chunk.
Each chunk is wrapped in try/catch. A failed chunk is logged and the loop continues. The job itself does not fail.

app/Jobs/InAppNotificationByJobOpening.php is the fan-out one. It is covered in The all-users fan-out.

Notification::insert() is a bulk insert. It does not fire Eloquent model events. So it writes no audits rows and does not set created_at automatically. The service sets created_at and updated_at by hand (for example InAppNotificationService.php:157-158).

One create path, end to end

Where the user's own settings come from

The fan-out query filters on two columns of users. Both are set from the mobile app.

POST /auth/settings reaches Mobiles\UserController::setSettings (line 711), which calls SettingRepositoryEloquent::setSettings (line 70).
That method writes a row into user_settings for each setting the app sends (lines 85-91).
Only two setting keys are copied onto the users row: settings.new_jobs_notification writes users.open_job_notification_status (lines 94-100), and settings.unsubscribe_news writes users.unsubscribe_news (lines 102-108).
users.user_setting_in_app_notification_status defaults to 1 (2022_03_07_122957_...). The mobile settings endpoint never writes it. Only the portal user services do, and those handle manager accounts, not app users.

Channel 2 — push through CleverTap

CleverTap is an outside service. JodGig talks to it over HTTP. All calls go through app/Helpers/CleverTapHelper.php, and all of them are wrapped in the queue job app/Jobs/CleverTapEventJob.php.

CleverTapEventJob has four modes, chosen by its first argument (CleverTapEventJob.php:79-129):

Mode	What it does
`PROFILE`	Sends user profile data to CleverTap.
`EVENT`	Sends a portal-side (B2B) or app-side event.
`APP_EVENT`	Sends one app user's event, for example `JOB_EARLY_ACKNOWLEDGEMENT_REMINDER`.
`CAMPAIGN`	Asks CleverTap to send a push, an email, or an SMS to a list of identities.

app/Services/ClevertapCampaignService.php builds the campaigns:

Method	What it sends	Called from
`clevertapNewJobPostedPushCampaign` (line 42)	a push campaign and `notifications` rows for `ct = 1` users	`JodJobController` lines 237, 380, 465, 3607; `BulkJodJobCsvImportService:188`; `UKGCreateJob:82`; `UKGUpdateJob:318`; `UKGCreateJobCommand:95`; `UKGUpdateJobCommand:270`
`clevertapApplicantJobCancellationEmailCampaign` (line 115)	an email campaign to every applicant on the job	job cancellation
`clevertapManagerJobCancellationEmailCampaign` (line 146)	an email campaign to the HQ manager	job cancellation
`clevertapManagerJobCancellationSMSCampaign` (line 179)	an SMS campaign to the HQ manager	job cancellation
`clevertapApplicantJobCancellationSMSCampaign` (line 207)	an SMS campaign to every applicant on the job	job cancellation

For the push campaign, the recipient list is filled in inside the queue job, not at dispatch time. CleverTapHelper::constructPushPayload (line 2421) runs the same user query again with skip($position)->take(1000) and puts the 1000 ids into $data["to"]["Identity"] (lines 2425-2439).

The CleverTap send is fire and forget. sendClevertapCampaign (line 2121) captures the HTTP response into $response and never looks at it (line 2146). A rejected campaign leaves no record in our database.

Channel 3 — email

app/Services/EmailNotificationService.php is 4168 lines and has 66 public methods that send email. Examples: hqManagerEmailNotiJobApproval (line 114), applicantEmailNotiSelectionAndUnselection (line 834), financeEmailNotiPaymentApproval (line 2982), sendCreditStatement (line 3968).

Every one of them follows the same shape. hqManagerEmailNotiJobApproval is the clearest example:

Load the template by purpose, type = 1, status = 1, and the current language (line 117).
Find the recipients and check each one's user_setting_email_notification_status (lines 119-125).
Build the $contents array for the Blade view (lines 133-146).
For each recipient, dispatch(new SendMail(...)) (lines 165-174). This is a queue job.
For each recipient, write one email_sms_notifications row with type = 1 (lines 177-188).

Two details worth knowing:

The email_sms_notifications row is written immediately, in the same process as the web request or cron command. It is not written by the queue job. So the row says "we tried", not "it arrived".
app/Jobs/SendMail.php catches every exception and only logs it (lines 88-91). A failed email leaves a log line and nothing else. The email_sms_notifications row still says the email went out.

SendMail also reformats dates and money in formatContent (lines 97-163) before rendering the Blade template.

Channel 4 — SMS

app/Services/SMSNotificationService.php has 12 dispatch(new SendSms(...)) calls. userSmsVerificationCode (line 62) is the clearest example:

Load the SMS template by purpose and type = 2.
Replace {otp} with the code.
Build a $data array with sender_user_type = 'SYSTEM', the receiver, the purpose, and type = 2.
dispatch(new SendSms($contactNumber, $content, $data)).

The SMS path differs from the email path in one important way. app/Jobs/SendSms.php writes the email_sms_notifications row inside the queue job, and only when the gateway answered with HTTP 200 or 201 (lines 48-51). So an SMS row means the gateway accepted the message. An email row does not carry that meaning.

The gateway is chosen at run time by SmsServiceFactory from config('smss.sms_service') (line 43). In Singapore production the gateway is OneWay SMS, configured through the ONEWAY_* settings in .env.prod.

What is not a channel

Firebase / FCM. .env.prod still sets FIREBASE_SERVER_KEY (line 19) and TOPIC_NAME (line 79). No PHP code reads either one. A search for firebase, fcm, sendToTopic, and TOPIC_NAME across app/ and config/ returns exactly one hit, and it is a comment in an OpenAPI docblock (app/Http/Controllers/AuthController.php:604). The docblocks in InAppNotificationService that say topic: allDevice and topic: user_{id} are left over from that removed integration. They do not describe what the code does now.
WhatsApp. users.user_setting_whatsapp_notification_status exists (2024_05_21_103032_...) and portal screens let managers set it. No code sends a WhatsApp message. The only related code is EmailNotificationService::notifyAppUserWhatsappTemporaryUnavailable (line 3838), which sends an email telling app users that WhatsApp is unavailable.

The all-users fan-out

This is the part of the system that costs the most, so it gets its own section.

The rule in one sentence

Creating one gig job inserts one notifications row for every eligible app user — not one row in total.

The `ct = 0` path, step by step

Step 1. app/Services/InAppNotificationService.php:161 counts the eligible users:

$users = $this->userRepository->getAllAppUserForOpenJobWithInAppNotificationSetting();
$count = $users->count();

getAllAppUserForOpenJobWithInAppNotificationSetting is at app/Repositories/UserRepositoryEloquent.php:843-852. It is:

->where('user_type', 'APP')
->where('open_job_notification_status', 1)
->where('user_setting_in_app_notification_status', 1)
->where('status', 1)
->where('ct', 0)

There is no filter on distance, on job type, on skills, or on availability. Every enabled app user with both notification settings on and ct = 0 is eligible for every job.

Step 2. InAppNotificationService.php:164-167 dispatches one queue job per 1000 users:

$limit = 1000;
for ($position = 0; $position < $count; $position += $limit) {
    dispatch(new InAppNotificationByJobOpening($data, $position));
}

Step 3. app/Jobs/InAppNotificationByJobOpening.php:52-62 re-runs the same user query with an offset, and takes 1000 ids:

$users = DB::table('users')->select('id')
    ->where('user_type', 'APP')
    ->where('open_job_notification_status', 1)
    ->where('user_setting_in_app_notification_status', 1)
    ->where('status', 1)
    ->where('ct', $this->isCT)
    ->orderBy('id')->skip($this->position)->take(1000)
    ->pluck('id');

Step 4. Lines 69-73 build one row per user, each with a fresh UUID. Lines 75-81 insert them 100 at a time:

foreach ($notifications->chunk(100) as $chunk) {
    Notification::insert($chunk->toArray());
}

So one queue job runs 1 SELECT and 10 INSERT statements, and creates 1000 rows.

The `ct = 1` path

app/Services/ClevertapCampaignService.php:103-111 repeats the same shape for the other user segment:

$users = $this->userRepo->getAllAppUserForOpenJobWithInAppNotificationSettingCT();
$count = $users->count();

$limit = 1000;
for ($position = 0; $position < $count; $position += $limit) {
    $payload = $this->createPushPayload('New Job Posted', $subject, $content, $data->id);
    dispatch(new CleverTapEventJob('CAMPAIGN', 'B2C', $payload, eventName: 'New Job Posted', campaignType: 'Push', position: $position));
    dispatch(new InAppNotificationByJobOpening($insertData, $position, 1));
}

Note the difference: this loop dispatches two queue jobs per 1000 users. One asks CleverTap to send a push. One writes 1000 notifications rows, with isCT = 1 so the same job class reads the other segment.

getAllAppUserForOpenJobWithInAppNotificationSettingCT is at UserRepositoryEloquent.php:854-863 and is identical to the ct = 0 version except for ->where('ct', 1).

Every place that starts a fan-out

Call site	Which segment	Enclosing action
`app/Http/Controllers/JodJobController.php:236`	`ct = 0`	`storeJodJobForAreaUser`
`app/Http/Controllers/JodJobController.php:237`	`ct = 1`	`storeJodJobForAreaUser`
`app/Http/Controllers/JodJobController.php:379`	`ct = 0`	`storeJodJobForHqUser`
`app/Http/Controllers/JodJobController.php:380`	`ct = 1`	`storeJodJobForHqUser`
`app/Http/Controllers/JodJobController.php:464`	`ct = 0`	`storeJodJobForLocationUser`
`app/Http/Controllers/JodJobController.php:465`	`ct = 1`	`storeJodJobForLocationUser`
`app/Http/Controllers/JodJobController.php:3604`	`ct = 0`	`approveJobForHqUser`
`app/Http/Controllers/JodJobController.php:3607`	`ct = 1`	`approveJobForHqUser`
`app/Services/BulkJodJobCsvImportService.php:187`	`ct = 0`	CSV bulk job import
`app/Services/BulkJodJobCsvImportService.php:188`	`ct = 1`	CSV bulk job import
`app/Jobs/UKGCreateJob.php:82`	`ct = 1` only	UKG shift sync creates a job
`app/Jobs/UKGUpdateJob.php:318`	`ct = 1` only	UKG shift sync updates a job
`app/Console/Commands/UKGCreateJobCommand.php:95`	`ct = 1` only	UKG create command
`app/Console/Commands/UKGUpdateJobCommand.php:270`	`ct = 1` only	UKG update command

The four UKG entry points call only clevertapNewJobPostedPushCampaign. They never call applicantPushNotiJobOpening. So a job created by the UKG sync produces notifications rows for ct = 1 users and none for ct = 0 users.

A worked example

Call N0 the number of eligible ct = 0 users and N1 the number of eligible ct = 1 users. The exact SQL to get both numbers is in Open questions.

Take a manager who creates one gig job through the portal. Assume N0 = 30,000 and N1 = 20,000. These are illustration numbers, not measured ones.

applicantPushNotiJobOpening runs ceil(30000 / 1000) = 30 dispatches. That is 30 INSERT statements into the jobs queue table.
clevertapNewJobPostedPushCampaign runs ceil(20000 / 1000) = 20 loop passes, each dispatching two queue jobs. That is 40 more INSERT statements into jobs.
Total queue rows created by that one button press: 70.
When the workers run those 70 jobs:
- 50 of them are InAppNotificationByJobOpening. Each runs 1 SELECT and 10 INSERT statements of 100 rows.
- Rows written into notifications: 50,000.
- INSERT statements against notifications: 500.
- 20 of them are CleverTapEventJob, each making one HTTP call to CleverTap carrying 1000 identities.
Each finished queue job is one DELETE from jobs. So 70 more writes.

Now the CSV bulk import. app/Services/BulkJodJobCsvImportService.php:180 loops for ($i = 1; $i <= (int) $rowPayload['no_of_pax']; $i++) and creates one gig job per person needed. Lines 187-188 run inside that loop. One CSV row asking for 20 people therefore creates 20 gig jobs and starts 20 full fan-outs:

20 × 70 = 1,400 queue rows.
20 × 50,000 = 1,000,000 notifications rows.
20 × 500 = 10,000 INSERT statements.

A 50-row CSV each asking for 20 people would create 50 million rows.

Is any other notification purpose shaped like this?

No. Every other dispatch(new InAppNotification(...)) site is bounded by the number of people connected to one gig job or one shift:

Site	Recipient list comes from
`InAppNotificationService.php:233`	`$jodJob->jobUsers` — applicants on this job
`InAppNotificationService.php:309`	`getListUserSelectedByJobId` — selected applicants
`InAppNotificationService.php:371`	`getListUserUnSelectedByJobId` — unselected applicants
`InAppNotificationService.php:436`	one user id
`InAppNotificationService.php:501`	one user id
`InAppNotificationService.php:550`	one user id
`InAppNotificationService.php:608`	`getListUserDoNotAck1ByJobId` — applicants on this job
`InAppNotificationService.php:680`	`getListSlotUserDoneClockOutHasNotFeedbackBySlotId` — workers on this shift
`InAppNotificationService.php:739`	`getListSlotUserDoNotAck2BySlotId` — workers on this shift

So applicant_push_noti_job_opening is the only purpose that writes to every user.

One other place has the all-users shape but does not write notifications rows. app/Console/Commands/JodJobAutoJobPostingAllUserNotification.php:75-93 loads every user who logged in this month, then dispatches one CleverTapEventJob per job per user, plus one extra database query per pair (line 81). It is not scheduled — the entry in app/Console/Kernel.php lines 200-206 is commented out.

How a notification gets read

The routes

All five endpoints live inside the auth.mobile middleware group, which starts at routes/api.php:119. They are declared at routes/api.php:194-200:

Method	Path	Controller action
`GET`	`/notification/index`	`NotificationController::index`
`POST`	`/notification/readAll`	`NotificationController::readAllNotification`
`POST`	`/notification/read`	`NotificationController::readNotification`
`POST`	`/notification/delete-all-notifications`	`NotificationController::deleteAllNotification`
`POST`	`/notification/delete-notification`	`NotificationController::deleteNotification`

auth.mobile is app/Http/Middleware/AuthMobile.php. It checks the api guard and blocks unverified or disabled users on write methods. So only mobile app users reach these endpoints. Portal users have no notification API at all.

Which table does it read?

It reads notifications — which today is the partitioned table.

The chain is short and worth checking yourself:

NotificationRepositoryEloquent::model() returns Notification::class (line 29).
app/Entities/Notification.php:29 sets protected $table = 'notifications';.
2025_05_09_233909_swap_notifications_tables.php:24 renamed the partitioned table to notifications.

There is no second model and no second table. Reads and writes both go to the partitioned table.

`GET /notification/index`

Request class: app/Http/Requests/IndexNotificationRequest.php. It validates limit and page as integers. authorize() returns true. There is no maximum on limit.
Service: app/Services/JodNotificationService.php:28-50. It starts from config('portal.notification.limit') (which is 10, config/portal.php:98) and config('portal.notification.default_order') (which is desc, line 99). composeDataRequest then overwrites limit with the request value if the request has one (app/Services/BaseService.php:86-88).
Line 38 calls Notification::setStaticHidden(['body', 'subject']). That hides the two raw serialized columns from the JSON. The app gets body_json and subject_json instead, produced by the accessors at app/Entities/Notification.php:44-56.
Repository: app/Repositories/NotificationRepositoryEloquent.php:45-54. The SQL is:

SELECT * FROM notifications
WHERE user_id = ?
  AND status = 1
  AND created_at >= <yesterday at 00:00>
ORDER BY created_at DESC
LIMIT ? OFFSET ?

Two things follow from that query:

Notifications older than yesterday at midnight are never shown, even if the rows are still there.
The query does not filter on deleted. See Known problems.

`POST /notification/read`

Request class: app/Http/Requests/ReadNotificationRequest.php. It requires notification_id to be a UUID and to pass NotificationExistRule.
app/Rules/NotificationExistRule.php:31-39 checks that the row exists and belongs to the logged-in user and has status = 1. This is where the ownership check happens.
Service: JodNotificationService::readNotificationService (line 69) wraps the id in an array and calls makeReadAllNotification.
Repository: NotificationRepositoryEloquent.php:74-81. SQL is UPDATE notifications SET is_read = 1 WHERE id IN (?). There is no user_id in that WHERE clause. The ownership check lives only in the validation rule.

`POST /notification/readAll`

Request class: ReadAllNotificationRequest. No rules at all.
Repository: NotificationRepositoryEloquent.php:87-95. SQL is UPDATE notifications SET is_read = 1 WHERE user_id = ? AND is_read = 0.
There is no created_at bound. This updates every unread row the user has in every remaining partition.

`POST /notification/delete-notification`

Request class: DeleteNotificationRequest. It requires notification_id to be a UUID. It does not use NotificationExistRule, unlike the read request.
Service: JodNotificationService::deleteNotification (line 79). It calls Notification::find($id), sets deleted = true, and calls save().
Notification::find($id) finds the row by id with no user check.
Because this goes through save(), it fires Eloquent events, so the auditing package writes an audits row as well.

`POST /notification/delete-all-notifications`

Request class: DeleteAllNotificationRequest. No rules.
Repository: NotificationRepositoryEloquent.php:111-115. SQL is UPDATE notifications SET deleted = 1 WHERE user_id = ?.

Read path diagram

Dead read methods

Two repository methods exist and are never called from application code:

getListUnReadNotificationByUserId (line 61). The old code that used it is commented out at JodNotificationService.php:56-59.
getTotalUnReadNotificationByUserId (line 101). No caller anywhere in app/. There is no unread-count endpoint.

Retention and partitioning

What runs, and when

app/Console/Kernel.php:245-249 is the only notification entry in the schedule:

$schedule->command('command:manage-notification-partition', [
    '--days_to_keep=1',
    '--days_ahead=1',
    '--target_table_name=notifications',
])->daily()->at("00:00");

Command	Cron expression	Notes
`command:manage-notification-partition`	`0 0 * * *` (daily at 00:00)	The only notification command in the schedule. It does not call `->timezone(...)`, unlike almost every other entry in the file. It uses the scheduler default, which is `config('app.timezone')` — `Asia/Singapore` in production (`config/app.php:83`).
`command:delete-old-notifications`	not scheduled	`app/Console/Kernel.php:240-242`. Commented out and marked "deprecated".
`sync:notifications-partitioned`	not scheduled	Never appears in `Kernel.php`. It also targets a table that no longer exists.

`ManageNotificationsPartitionCommand`

app/Console/Commands/ManageNotificationsPartitionCommand.php:

Lines 46-59 refuse to run unless all three options are given.
Line 64 reads the application's current date: Carbon::now()->toDateString().
Lines 66-79 build and run CALL manage_notifications_partitions(?, ?, ?, ?) with days_to_keep, days_ahead, target_table_name, and that date.
Lines 81-83 catch every exception and print it. The command always returns 0, so a failure looks like a success to any monitor watching the exit code.

The stored procedures

Three procedures were created by 2025_05_08_161851_create_notifications_partitioned_table.php. Two of them were replaced by 2025_05_23_121428_update_manage_notification_partition_use_app_time.php. The versions below are the live ones.

add_notifications_partition(partition_date DATE, target_table_name VARCHAR(64)) Created in the 2025-05-08 migration, lines 56-116. Not replaced.

Builds the name p_YYYYMMDD from the date.
Asks information_schema.partitions whether that partition already exists. If yes, it does nothing and returns a message.
If p_future exists, it runs ALTER TABLE ... REORGANIZE PARTITION p_future INTO (<new day>, p_future).
If p_future does not exist, it runs a plain ALTER TABLE ... ADD PARTITION.

drop_old_notifications_partition(cut_off_date DATE, target_table_name VARCHAR(64)) Live version in the 2025-05-23 migration, lines 25-77.

Opens a cursor over every partition of the table except p_future.
For each one it works out the day the partition holds: DATE_SUB(FROM_DAYS(partition_description), INTERVAL 1 DAY).
If that day is earlier than cut_off_date, it runs ALTER TABLE ... DROP PARTITION.

The first version of this procedure took days_to_keep and worked out the cutoff itself with MySQL's CURDATE(). The 2025-05-23 migration changed it to receive the cutoff as a parameter, so that MySQL's clock cannot disagree with the application's clock.

manage_notifications_partitions(days_to_keep INT, days_ahead INT, target_table_name VARCHAR(64), app_current_date DATE) Live version in the 2025-05-23 migration, lines 82-131.

set cut_off_date = DATE_SUB(app_current_date, INTERVAL 1 DAY); (line 95).
CALL drop_old_notifications_partition(cut_off_date, target_table_name); (line 96).
Loop i from 0 to days_ahead, calling add_notifications_partition(app_current_date + i days, ...) (lines 100-109).
If p_future is missing, add it (lines 115-130).

Important: days_to_keep is declared as a parameter but is never used. The cutoff is always "yesterday", whatever value is passed. The schedule passes --days_to_keep=1, which happens to match, but changing that number would change nothing.

What retention actually is

Work through 2026-08-05 at 00:00, with days_ahead = 1:

cut_off_date = 2026-08-04.
Partition p_20260803 holds 2026-08-03. 2026-08-03 < 2026-08-04, so it is dropped.
Partition p_20260804 holds 2026-08-04. 2026-08-04 < 2026-08-04 is false, so it is kept.
The loop then creates p_20260805 and p_20260806 if they are missing.

So right after the daily run, the table holds yesterday and today, plus an empty partition for tomorrow. Rows are kept for roughly one to two days. This matches the read query, which only returns rows with created_at >= yesterday at 00:00.

Known problems

Each item below is a statement of fact with the evidence next to it. No fixes are proposed here.

1. One gig job writes one row per app user

InAppNotificationService.php:161-167 plus ClevertapCampaignService.php:103-111 mean that every new gig job writes N0 + N1 rows into notifications, where N0 + N1 is the whole eligible app user base. There is no filter by distance, job type, skills, or availability. The only filters are the two user settings and status = 1 (UserRepositoryEloquent.php:843-863).

2. The bulk CSV import multiplies the fan-out by `no_of_pax`

BulkJodJobCsvImportService.php:180-189. The fan-out calls sit inside the per-person loop. One CSV row asking for 20 people runs the whole fan-out 20 times.

3. The queue `retry_after` is shorter than the job timeout

config/queue.php:41 sets 'retry_after' => 90 for the database connection.
app/Jobs/InAppNotificationByJobOpening.php:22 sets public $timeout = 1200.

Laravel releases a reserved job back to the queue after retry_after seconds. If one fan-out job takes more than 90 seconds, another of the 8 workers can pick up the same job while the first one is still inserting. Both then insert 1000 rows, each with fresh UUIDs (InAppNotificationByJobOpening.php:70), so nothing stops the duplicates.

4. A retry always duplicates rows

The UUID is generated at insert time, not derived from the user and the job (InAppNotification.php:44, InAppNotificationByJobOpening.php:70). The supervisor config runs workers with --tries=5 (deploy-cron/supervisor.conf:10). Any retry after a partial insert writes the same notifications again with different ids. There is no unique key that would block it.

5. Deleting a notification has no visible effect

JodNotificationService::deleteNotification (line 79) sets deleted = true.
NotificationRepositoryEloquent::deleteAllNotificationsByUserId (line 113) sets deleted = 1 for all of a user's rows.
NotificationRepositoryEloquent::getListNotificationByUserId (lines 45-54) filters on user_id, status, and created_at. It never filters on deleted.

The server therefore returns deleted rows in the list. Whether the mobile app hides them itself is an open question.

6. Anyone can delete anyone else's notification

DeleteNotificationRequest only checks that notification_id is a UUID. JodNotificationService::deleteNotification calls Notification::find($id) with no owner check. Compare with the read path, which does check ownership through NotificationExistRule (app/Rules/NotificationExistRule.php:33-38).

7. `POST /ct` is a public endpoint that changes notification routing

routes/api.php:84 declares Route::post('/ct', [MobileUserController::class, 'ctCheck']); outside every middleware group. app/Http/Controllers/Mobiles/UserController.php:720-771 sets ct = true for whatever identity is posted. The ct flag decides which of the two notification paths serves that user.

8. The UKG sync only notifies half the users

UKGCreateJob.php:82, UKGUpdateJob.php:318, UKGCreateJobCommand.php:95, and UKGUpdateJobCommand.php:270 call clevertapNewJobPostedPushCampaign and nothing else. Users with ct = 0 get no notifications row for jobs created through UKG.

9. Reminder notifications can show the wrong person's data

In applicantPushNotiFeedbackReminder (line 629), the $data array is rebuilt inside the loop (lines 650-668) using that iteration's clock-out time (line 647). Only the last value survives. The dispatch at line 680 sends that one $data to every user in $userIds. So everybody receives the last person's clock-out time in the message body.

Six methods build $data inside a loop and then dispatch once, outside it: dispatches at lines 233, 309, 371, 608, 680, and 739. Only line 680 (the feedback reminder) puts per-user data into the message. The other five insert values that are the same for every recipient — job title, outlet name, job dates — so the outcome happens to be correct there. The pattern is the same in all six.

10. `ClevertapCampaignService` never sets `$lang2nd`

ClevertapCampaignService.php:28 declares protected $lang2nd;. The constructor (lines 30-40) never assigns it. Lines 88-95 then use $this->lang2nd as an array key when building subject and body. PHP turns a null key into an empty string. So the serialized value written for ct = 1 users has an "" key where InAppNotificationService writes "en" and the second language code.

InAppNotificationService.php:73 does set it, from config('app.language_code'). In Singapore that value is en (config/app.php:62), so both slots hold en.

11. Every fan-out failure is silent

applicantPushNotiJobOpening wraps its whole body in try/catch and only writes a log line (InAppNotificationService.php:170-172). All nine other methods in the file do the same. InAppNotificationByJobOpening.php:78-80 catches each failed chunk of 100 and continues. SendMail.php:88-91 catches every email failure. CleverTapHelper::sendClevertapCampaign (line 2146) throws away the CleverTap response. None of these paths raise an alert.

12. `notifications` has no index that fits the read query

The read query is WHERE user_id = ? AND status = 1 AND created_at >= ? ORDER BY created_at DESC. The only usable index is notifications_user_id_index on user_id alone. The created_at index from the earlier table did not survive the swap. Partition pruning limits the work to at most three partitions, but inside each partition MySQL still has to sort.

13. `limit` on the list endpoint has no maximum

IndexNotificationRequest validates limit as integer and nothing more. BaseService::composeDataRequest (lines 86-88) copies it straight into the query. A client can ask for any page size.

14. `SyncNotificationsPartitioned` cannot run

app/Console/Commands/SyncNotificationsPartitioned.php reads notifications and inserts into notifications_partitioned. That second table stopped existing on 2025-05-09. The command also loops while (true) with no exit condition other than an exception (line 30).

15. `EmailSmsNotificationRepositoryEloquent::checkCountRequestSendOTP` queries a column that does not exist

Line 44 filters ->where('resend_verify_total', '>=', 4) on email_sms_notifications. That column exists on users, added by 2022_02_28_152312_add_columns_resend_verify_total_and_last_verify_sms_sent_at_to_users_table.php:17. It was never added to email_sms_notifications. Nothing calls the method today, so the error is not reachable.

16. `NotificationVNService` imports a class that does not exist

app/Services/NotificationVNService.php:5 has use App\Jobs\PushNotificationJob;. There is no such file in app/Jobs/. PHP only resolves the name when it is used, and it is never used, so nothing breaks. It is left over from the removed push integration.

17. Firebase settings are still in the production environment file

.env.prod line 19 sets FIREBASE_SERVER_KEY and line 79 sets TOPIC_NAME. No code reads either. Anyone reading the environment file would reasonably conclude that JodGig sends Firebase push. It does not.

18. Text is stored as a PHP serialized string

InAppNotificationService.php:146-153 writes subject and body with PHP's serialize(). app/Entities/Notification.php:44-56 reads them back with unserialize(). This is not JSON. Any consumer outside PHP — a report, a BI tool, a future Rails service — cannot read these columns without writing a PHP-serialization parser.

19. The partition command reports success when it fails

ManageNotificationsPartitionCommand.php:81-83 catches the exception, prints an error line, and then return 0 at line 85. A monitor watching the exit code sees success. If the drop step ever stops working, partitions accumulate silently.

20. `days_to_keep` does nothing

The schedule passes --days_to_keep=1 (app/Console/Kernel.php:246). manage_notifications_partitions accepts the parameter and never reads it (2025_05_23_121428_... lines 82-96). The cutoff is hard-coded to app_current_date - 1 day.

21. Deleting notifications writes audit rows

app/Entities/Notification.php:17 declares the model Auditable, and config/audit.php:5 enables auditing. deleteNotification uses save(), which fires model events, so one delete writes one notifications update and one audits row. config/audit.php:168 sets 'console' => false, so cron and queue work is not audited. Bulk inserts use Notification::insert(), which fires no events, so the fan-out itself writes no audit rows.

22. The queue and the notifications live in the same database

.env.prod:46 sets QUEUE_CONNECTION=database. config/queue.php:37-43 points that connection at the jobs table with no connection key, so it falls back to config('database.default'), which is mysql (config/database.php:18). That is the same jodgig database that holds notifications, jod_jobs, users, and everything else. Every dispatch is an INSERT and every completion is a DELETE in the same MySQL instance the API reads from.

23. Two reminder methods can crash on a missing template

applicantPushNotiAck1 (InAppNotificationService.php:611-617) and applicantPushNotiAck2 (lines 742-748) each have an else branch that runs when the English in-app template is missing or disabled. Inside those branches:

$appUser = $this->userRepository->getAppUserWithInAppNotificationSettingByUserId(...);
dispatch(new CleverTapEventJob('APP_EVENT', 'B2C', $jodJob, '...', $appUser->id, null));

getAppUserWithInAppNotificationSettingByUserId (UserRepositoryEloquent.php:806-814) ends with ->first(), so it returns null when the user turned in-app notifications off or the account is disabled. Reading ->id on null raises a PHP Error. The surrounding catch (\Exception $e) does not catch Error, because Error and Exception are separate branches under Throwable. The cron command would then stop.

The if branch above does not have this problem — it checks if (!empty($appUser)) first (line 573 and line 704).

24. The distance setting the app offers is ignored

app/Http/Requests/Mobiles/SetSettingRequest.php:31 reads the setting key settings.notifications_by_distance. So the mobile app lets a user choose how far away a job may be.
UserRepositoryEloquent.php:816-841 contains getAllAppUserDeviceKeyForOpenJobWithNotificationSetting. It joins user_settings and address, works out the distance with a Haversine formula, and filters with havingRaw('distance <= ...'). This is the query that would honour the setting.
Nothing in app/ calls that method. A search for the name returns only its own definition and the interface declaration in app/Repositories/UserRepository.php:56.

The two queries that actually drive the fan-out (UserRepositoryEloquent.php:843 and 854) have no distance filter. A user who sets "only jobs within 5 km" still receives every job in the country.

The queue

The connection

.env.prod:46 — QUEUE_CONNECTION=database.
config/queue.php:16 — 'default' => env('QUEUE_CONNECTION', 'sync').
config/queue.php:37-43 — the database connection: table jobs, queue name default, retry_after 90 seconds, after_commit false.
The connection has no 'connection' key, so Laravel uses the default database connection.
config/database.php:18 — the default connection is mysql.
.env.prod:29 — that database is jodgig.

Failed jobs go to failed_jobs in the same database, with the database-uuids driver (config/queue.php:95-99).

What "database queue" means in practice

Dispatching a job is an INSERT into jobs, holding the serialized job class and its arguments.
A worker claims a job with a SELECT ... FOR UPDATE plus an UPDATE that sets reserved_at.
Finishing a job is a DELETE from jobs.
Failing after all retries is a DELETE from jobs plus an INSERT into failed_jobs.

So one gig job creation writes 70 rows into jobs (in the worked example above), then deletes 70 rows a short time later. That traffic hits the same MySQL server that serves every API read.

after_commit is false. A job can therefore be picked up before the surrounding database transaction commits.

The workers

deploy-cron/supervisor.conf:

[program:laravel-worker]
command=php artisan queue:work --queue=high,default --sleep=3 --backoff=5 --tries=5 --max-time=3600
numprocs=8

[program:laravel-scheduler]
command=php artisan schedule:work

Setting	Value	Meaning
`numprocs`	8	Eight worker processes run at once.
`--queue`	`high,default`	Each worker drains `high` first, then `default`.
`--tries`	5	A job is attempted up to five times.
`--backoff`	5	Five seconds between attempts.
`--sleep`	3	Three seconds of waiting when the queue is empty.
`--max-time`	3600	A worker restarts itself after one hour.
`stopwaitsecs`	3600	Supervisor waits up to one hour for a worker to stop.

All notification jobs go to default. Nothing in the notification code calls onQueue. The only jobs on high are the three UKG sync jobs (app/Console/Commands/UKGSyncSchedulesCommand.php:109, 114, 119).

Where the workers run

The workers do not run in the API container. There are two containers:

Container	Built from	Command	What it runs
`php-fpm_<timestamp>`	`Dockerfile.api`, deployed by `deploy/deploy.sh:106-110`	`CMD ["php-fpm"]` (`Dockerfile.api:74`)	the HTTP API only
`jodgig-cron`	`Dockerfile.cron.single-step`, deployed by `deploy-cron/deploy-cron.sh:27-30`	`supervisord` (`Dockerfile.cron.single-step:72`)	the 8 queue workers and `schedule:work`

schedule:work is the long-running form of the cron scheduler. So the same single container runs every scheduled command and every background job. If that container is down, no notification rows are written and no partitions are managed, while the API keeps accepting job creations and keeps writing rows into jobs.

Dockerfile.cron also exists and uses the same deploy-cron/supervisor.conf, but deploy-cron.sh:29 builds from Dockerfile.cron.single-step.

parallel_commands.sh at the repository root is not part of the queue setup. It is a helper used by the one-off data migration seeders (database/seeders/MGUserSeeder.php:16 and four others) and by the README setup steps.

Open questions

These need a production query or a person. The code cannot answer them.

1. How many users does one gig job actually notify?

Run both queries on the jodgig production database:

-- N0: the ct = 0 segment, served by InAppNotificationService
SELECT COUNT(*) AS n0
FROM users
WHERE user_type = 'APP'
  AND open_job_notification_status = 1
  AND user_setting_in_app_notification_status = 1
  AND status = 1
  AND ct = 0;

-- N1: the ct = 1 segment, served by ClevertapCampaignService
SELECT COUNT(*) AS n1
FROM users
WHERE user_type = 'APP'
  AND open_job_notification_status = 1
  AND user_setting_in_app_notification_status = 1
  AND status = 1
  AND ct = 1;

2. How big is the `notifications` table right now, and how many rows per day?

SELECT COUNT(*) AS total_rows FROM notifications;

SELECT DATE(created_at) AS day, COUNT(*) AS rows_created
FROM notifications
GROUP BY DATE(created_at)
ORDER BY day DESC;

SELECT partition_name, table_rows, ROUND(data_length/1024/1024, 1) AS data_mb,
       ROUND(index_length/1024/1024, 1) AS index_mb, partition_description
FROM information_schema.partitions
WHERE table_schema = 'jodgig' AND table_name = 'notifications'
ORDER BY partition_ordinal_position;

3. Do the three stored procedures still exist in production, with the 2025-05-23 versions?

SELECT routine_name, created, last_altered
FROM information_schema.routines
WHERE routine_schema = 'jodgig'
  AND routine_name IN (
    'add_notifications_partition',
    'drop_old_notifications_partition',
    'manage_notifications_partitions'
  );

-- confirm drop_old_notifications_partition takes a DATE cutoff, not an INT
SELECT specific_name, parameter_name, data_type, ordinal_position
FROM information_schema.parameters
WHERE specific_schema = 'jodgig'
  AND specific_name IN ('drop_old_notifications_partition', 'manage_notifications_partitions')
ORDER BY specific_name, ordinal_position;

4. Is the daily partition command actually running and succeeding?

The command returns exit code 0 even when the CALL throws (ManageNotificationsPartitionCommand.php:81-85). The proof has to come from the data:

-- if this returns partitions older than yesterday, the drop step is failing
SELECT partition_name, partition_description
FROM information_schema.partitions
WHERE table_schema = 'jodgig' AND table_name = 'notifications'
  AND partition_name <> 'p_future'
ORDER BY partition_ordinal_position
LIMIT 10;

5. Are duplicate notification rows being written?

This tests the retry_after 90 seconds against timeout 1200 seconds problem, and the retry problem.

SELECT user_id, job_id, purpose, COUNT(*) AS copies
FROM notifications
WHERE purpose = 'applicant_push_noti_job_opening'
  AND created_at >= CURDATE()
GROUP BY user_id, job_id, purpose
HAVING COUNT(*) > 1
ORDER BY copies DESC
LIMIT 50;

6. How deep does the queue get, and what fails?

SELECT queue, COUNT(*) AS waiting,
       MIN(FROM_UNIXTIME(available_at)) AS oldest_available
FROM jobs
GROUP BY queue;

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(payload, '"displayName":"', -1), '"', 1) AS job_class,
       COUNT(*) AS failures
FROM failed_jobs
WHERE failed_at >= NOW() - INTERVAL 7 DAY
GROUP BY job_class
ORDER BY failures DESC;

7. Does `notifications_partitioned` still exist as a leftover?

SELECT table_name, table_rows, create_time
FROM information_schema.tables
WHERE table_schema = 'jodgig'
  AND table_name LIKE 'notifications%';

8. Does the mobile app hide rows where `deleted = 1`?

The server returns them (see Known problems item 5). The deleted column is present in the JSON, because only body and subject are hidden. Someone from the mobile team needs to confirm whether the app filters on it.

9. Are there any rows with a `user_type` other than `APP`?

The column comment lists five values. No write path in app/ sets anything but APP.

SELECT user_type, COUNT(*) FROM notifications GROUP BY user_type;

10. Is `POST /ct` reachable from the public internet?

The route has no authentication middleware (routes/api.php:84). Whether a firewall, WAF, or IP allow-list sits in front of it in production has to be answered by whoever owns the infrastructure.

11. Why do three reminder methods skip the `ct` check?

applicantPushNotiAck1, applicantPushNotiAck2, and applicantPushNotiFeedbackReminder do not test $appUser->ct === 0. The other seven in-app methods do. Nothing in the code or the comments explains the difference. This needs a person who was there.

12. Which SMS gateway is live, and how many messages does it carry?

config('smss.sms_service') decides at run time (app/Jobs/SendSms.php:43). .env.prod holds OneWay SMS credentials. Confirm from the data:

SELECT DATE(created_at) AS day, type, COUNT(*) AS sent
FROM email_sms_notifications
WHERE created_at >= NOW() - INTERVAL 30 DAY
GROUP BY DATE(created_at), type
ORDER BY day DESC;

References

All paths are relative to the jodgig-api repository root.

Migrations

database/migrations/2022_01_21_230116_create_notification_templates_table.php
database/migrations/2022_01_23_000557_create_notifications_table.php
database/migrations/2022_01_23_004901_create_email_sms_notifications_table.php
database/migrations/2022_02_28_152312_add_columns_resend_verify_total_and_last_verify_sms_sent_at_to_users_table.php
database/migrations/2022_03_07_122957_add_columns_for_user_setting_notification_feature_on_users_table.php
database/migrations/2022_03_07_185659_add_column_open_job_notification_status_on_users_table.php
database/migrations/2022_03_09_014257_add_column_description_on_notification_templates_table.php
database/migrations/2022_05_12_150640_add_columns_name_to_notification_templates_table.php
database/migrations/2022_05_12_181832_add_is_available_field_to_notification_templates_table.php
database/migrations/2022_12_09_172807_add_column_lang_to_notification_templates.php
database/migrations/2023_02_08_101204_add_foreign_key.php
database/migrations/2023_03_09_115433_delete_foreign_key_table_notifications.php
database/migrations/2024_03_22_080248_add_ct_column_to_users_table.php
database/migrations/2024_05_21_103032_add_user_setting_whatsapp_notification_status_on_users_table.php
database/migrations/2024_07_02_220038_add_deleted_to_notifications_table.php
database/migrations/2025_04_13_182425_create_notifications_new.php
database/migrations/2025_04_14_120731_drop_table_notifications.php
database/migrations/2025_04_14_175304_rename_table_notifications_new.php
database/migrations/2025_05_08_161851_create_notifications_partitioned_table.php
database/migrations/2025_05_09_233909_swap_notifications_tables.php
database/migrations/2025_05_23_121428_update_manage_notification_partition_use_app_time.php
database/migrations/2025_06_02_153343_alter_table_notifications_add_user_id_index.php
database/seeders/NotificationToNotificationNewSeeder.php

Write path — in-app

app/Services/InAppNotificationService.php
app/Jobs/InAppNotification.php
app/Jobs/InAppNotificationByJobOpening.php
app/Repositories/UserRepositoryEloquent.php (lines 806, 816, 843, 854)
app/Repositories/UserRepository.php (lines 54-60)
app/Repositories/SettingRepositoryEloquent.php (lines 70-110, setSettings)
app/Http/Requests/Mobiles/SetSettingRequest.php (line 31)
app/Http/Controllers/Mobiles/UserController.php (line 711, setSettings)
app/Traits/ConfigurationTrait.php (line 20)
app/Services/ConfigurationService.php (line 115)
app/Repositories/NotificationTemplateRepositoryEloquent.php
app/Entities/Notification.php
app/Entities/NotificationTemplate.php
app/Traits/DynamicHiddenVisible.php

Write path — CleverTap

app/Services/ClevertapCampaignService.php
app/Jobs/CleverTapEventJob.php
app/Helpers/CleverTapHelper.php (lines 1494, 2121, 2421)
app/Http/Controllers/Mobiles/UserController.php (line 720, ctCheck)

Write path — email and SMS

app/Services/EmailNotificationService.php
app/Services/SMSNotificationService.php
app/Jobs/SendMail.php
app/Jobs/SendSms.php
app/Repositories/EmailSmsNotificationRepositoryEloquent.php
app/Entities/EmailSmsNotification.php

Callers that start a fan-out

app/Http/Controllers/JodJobController.php (lines 236, 237, 379, 380, 464, 465, 3604, 3607)
app/Services/BulkJodJobCsvImportService.php (lines 180, 187, 188)
app/Jobs/UKGCreateJob.php (line 82)
app/Jobs/UKGUpdateJob.php (line 318)
app/Console/Commands/UKGCreateJobCommand.php (line 95)
app/Console/Commands/UKGUpdateJobCommand.php (line 270)

Other callers of in-app methods

app/Http/Controllers/JodJobController.php (lines 1181, 1573, 1574, 1740, 1833, 1916)
app/Http/Controllers/Mobiles/JodJobController.php (line 1297)
app/Console/Commands/JodJobNotiAck1Command.php (line 75)
app/Console/Commands/JodJobNotiAck2Command.php (line 74)
app/Console/Commands/JodJobFeedbackReminderCommand.php (line 72)

Read path

app/Http/Controllers/NotificationController.php
app/Services/JodNotificationService.php
app/Services/BaseService.php (lines 59-120, composeDataRequest)
app/Repositories/NotificationRepositoryEloquent.php
app/Repositories/NotificationRepository.php
app/Http/Requests/IndexNotificationRequest.php
app/Http/Requests/ReadNotificationRequest.php
app/Http/Requests/ReadAllNotificationRequest.php
app/Http/Requests/DeleteNotificationRequest.php
app/Http/Requests/DeleteAllNotificationRequest.php
app/Rules/NotificationExistRule.php
app/Http/Middleware/AuthMobile.php
routes/api.php (lines 84, 119, 194-200, 537-538)

Retention and partitioning

app/Console/Kernel.php (lines 200-206, 239-249)
app/Console/Commands/ManageNotificationsPartitionCommand.php
app/Console/Commands/DeleteOldNotificationsCommand.php
app/Console/Commands/SyncNotificationsPartitioned.php
app/Console/Commands/JodJobAutoJobPostingAllUserNotification.php

Configuration and deployment

.env.prod (lines 8-9, 19, 26-31, 46, 74-79)
config/queue.php
config/database.php
config/app.php (lines 61, 62, 83, 97)
config/portal.php (lines 26-36, 97-100)
config/audit.php (lines 5, 141, 168)
app/Constants/Constants.php (lines 152, 167-173, 196-197)
app/Providers/RepositoryServiceProvider.php (lines 80, 110)
app/Http/Kernel.php (lines 43-44, 68)
deploy-cron/supervisor.conf
deploy-cron/deploy-cron.sh
deploy/deploy.sh
Dockerfile.api
Dockerfile.cron
Dockerfile.cron.single-step
parallel_commands.sh (checked and found unrelated to the queue — it is a seeder helper)

Services named "Notification" that do not send notifications

app/Services/NotificationSingService.php
app/Services/NotificationVNService.php
app/Services/Interfaces/NotificationServiceInterface.php

Vocabulary
TL;DR
The tables
How a notification gets created
The all-users fan-out
How a notification gets read
Retention and partitioning
Known problems
The queue
Open questions
References

Vocabulary​

Tables​

Columns​

Concepts​

TL;DR​

The tables​

notifications​

What happened to this table​

Columns today​

Partition scheme​

notifications_partitioned​

notification_templates​

email_sms_notifications​

How a notification gets created​

All four channels at a glance​

Channel 1 — in-app notifications​

The queue job that writes the rows​

One create path, end to end​

Where the user's own settings come from​

Channel 2 — push through CleverTap​

Channel 3 — email​

Channel 4 — SMS​

What is not a channel​

The all-users fan-out​

The rule in one sentence​

The ct = 0 path, step by step​

The ct = 1 path​

Every place that starts a fan-out​

A worked example​

Is any other notification purpose shaped like this?​

How a notification gets read​

The routes​

Which table does it read?​

GET /notification/index​

POST /notification/read​

POST /notification/readAll​

POST /notification/delete-notification​

POST /notification/delete-all-notifications​

Read path diagram​

Dead read methods​

Retention and partitioning​

What runs, and when​

ManageNotificationsPartitionCommand​

The stored procedures​

What retention actually is​

Known problems​

1. One gig job writes one row per app user​

2. The bulk CSV import multiplies the fan-out by no_of_pax​

3. The queue retry_after is shorter than the job timeout​

4. A retry always duplicates rows​

5. Deleting a notification has no visible effect​

6. Anyone can delete anyone else's notification​

7. POST /ct is a public endpoint that changes notification routing​

8. The UKG sync only notifies half the users​

9. Reminder notifications can show the wrong person's data​

10. ClevertapCampaignService never sets $lang2nd​

11. Every fan-out failure is silent​

12. notifications has no index that fits the read query​

13. limit on the list endpoint has no maximum​

14. SyncNotificationsPartitioned cannot run​

15. EmailSmsNotificationRepositoryEloquent::checkCountRequestSendOTP queries a column that does not exist​

16. NotificationVNService imports a class that does not exist​

17. Firebase settings are still in the production environment file​

18. Text is stored as a PHP serialized string​

19. The partition command reports success when it fails​

20. days_to_keep does nothing​

21. Deleting notifications writes audit rows​

22. The queue and the notifications live in the same database​

23. Two reminder methods can crash on a missing template​

24. The distance setting the app offers is ignored​

The queue​

The connection​

What "database queue" means in practice​

The workers​

Where the workers run​

Open questions​

1. How many users does one gig job actually notify?​

2. How big is the notifications table right now, and how many rows per day?​

3. Do the three stored procedures still exist in production, with the 2025-05-23 versions?​

4. Is the daily partition command actually running and succeeding?​

Vocabulary

Tables

Columns

Concepts

TL;DR

The tables

`notifications`

What happened to this table

Columns today

Partition scheme

`notifications_partitioned`

`notification_templates`

`email_sms_notifications`

How a notification gets created

All four channels at a glance

Channel 1 — in-app notifications

The queue job that writes the rows

One create path, end to end

Where the user's own settings come from

Channel 2 — push through CleverTap

Channel 3 — email

Channel 4 — SMS

What is not a channel

The all-users fan-out

The rule in one sentence

The `ct = 0` path, step by step

The `ct = 1` path

Every place that starts a fan-out

A worked example

Is any other notification purpose shaped like this?

How a notification gets read

The routes

Which table does it read?

`GET /notification/index`

`POST /notification/read`

`POST /notification/readAll`

`POST /notification/delete-notification`

`POST /notification/delete-all-notifications`

Read path diagram

Dead read methods

Retention and partitioning

What runs, and when

`ManageNotificationsPartitionCommand`

The stored procedures

What retention actually is

Known problems

1. One gig job writes one row per app user

2. The bulk CSV import multiplies the fan-out by `no_of_pax`

3. The queue `retry_after` is shorter than the job timeout

4. A retry always duplicates rows

5. Deleting a notification has no visible effect

6. Anyone can delete anyone else's notification

7. `POST /ct` is a public endpoint that changes notification routing

8. The UKG sync only notifies half the users

9. Reminder notifications can show the wrong person's data

10. `ClevertapCampaignService` never sets `$lang2nd`

11. Every fan-out failure is silent

12. `notifications` has no index that fits the read query

13. `limit` on the list endpoint has no maximum

14. `SyncNotificationsPartitioned` cannot run

15. `EmailSmsNotificationRepositoryEloquent::checkCountRequestSendOTP` queries a column that does not exist

16. `NotificationVNService` imports a class that does not exist

17. Firebase settings are still in the production environment file

18. Text is stored as a PHP serialized string

19. The partition command reports success when it fails

20. `days_to_keep` does nothing

21. Deleting notifications writes audit rows

22. The queue and the notifications live in the same database

23. Two reminder methods can crash on a missing template

24. The distance setting the app offers is ignored

The queue

The connection

What "database queue" means in practice

The workers

Where the workers run

Open questions

1. How many users does one gig job actually notify?

2. How big is the `notifications` table right now, and how many rows per day?

3. Do the three stored procedures still exist in production, with the 2025-05-23 versions?

4. Is the daily partition command actually running and succeeding?