# Client ID Change Impact Analysis

**Change under review:** formatted Client ID `CA 1003/26-27` → `YA-CA 1003/26-27` (uppercase 2-letter prefix from the first two alphabetic characters of the Company Name).
**Scope:** analysis only. No code, data, or migrations were changed. Database access was read-only (counts/samples on the local `cumans` MySQL database).
**Date:** 2026-10-31.

---

## 1. Executive Summary

The change is **much safer than it first appears**, because every module in the system links to clients through the numeric primary key `clients.id`, not the formatted string. The formatted `clients.client_id` is a display/reference value. No code anywhere parses, splits, or regex-matches the formatted ID in a way the new format would break, and no other document number (MOU, invoice, proforma, paysheet, letter number, staff ID) is derived from it.

The real risks are concentrated in a few places:

1. **Where the ID is generated** — `ClientController::create()` builds the ID when the form *renders*, before the company name is typed. The new format needs the company name, so generation must move to JavaScript (on company-name input) and/or to `store()` server-side.
2. **Four stored copies of the formatted string** — `staffs.company_code`, `recruitments.company_code`, `bank_tran_files.client_code`, `payslips.entity_code`, plus the five timesheet tables' string `client_id` columns. Existing rows keep the old format forever unless migrated.
3. **Two bulk imports match the formatted ID exactly** — Staff and Recruitment bulk uploads look up `where('client_id', $code)`. Users must type whatever format is stored; mixed formats mean mixed expectations.
4. **Double-prefix hazard** — about 7 forms already build a `company_code` as `first-3-letters-of-name + '-' + client_id` in JS. With the new format this produces `YAS-YA-CA 1003/26-27`.
5. **Pre-existing defects worth fixing alongside** — no unique constraint on `client_id`, no locking on the next sequence number (concurrent creation can duplicate IDs), `value` stored as varchar (text-sort breaks at 9999→10000), FY suffix uses the calendar year, and no validation that the company name has 2 alphabetic characters.

**Recommended approach: Option B (prefix new clients only, keep existing IDs) — see §9/§10.** A code-only deployment is sufficient; no data migration is required for Option B.

---

## 2. Existing Client ID Generation Architecture

| Item | Finding |
|---|---|
| Model / table | `app/Models/Client.php` → table `clients` |
| Formatted ID column | `clients.client_id`, `varchar(255)`, nullable, **no unique index** (migration `database/migrations/2025_05_21_142855_add_columns_to_clients_table.php:15`) |
| Component columns | `prefix` ('CA '), `value` (sequence, **varchar(255)** not integer), `suffix` ('/yy-yy') — all nullable strings |
| Controller | `app/Http/Controllers/ClientController.php` |
| Generation | `create()` (lines 202–209): `$prefix = 'CA '`; `$suffix = "/{yy}-{yy+1}"` from `now()` (calendar year); `$value = Client::latest('value')->first()->value + 1`, starting at 1001 |
| Assembly | Server-side in Blade: `resources/views/template/client/create.blade.php:52-57` — readonly input `company_id = {{ $prefix.$value.$suffix }}` plus hidden `prefix`/`value`/`suffix` inputs. No JS concatenation. |
| Persistence | `store()` (lines 230–233): saves `client_id = $request->company_id` and the three hidden fields **as submitted** — only `'required'` validation, no uniqueness, no format check. "Readonly" is browser-only; a tampered form can submit any ID. |
| Frontend or backend? | Effectively **backend-computed at page-render time**, passed through the form. Not recomputed at save time. |
| Bulk upload | **Clients are never created in bulk.** `clientUpload()` (ClientController.php:117–181) reads a sheet and returns only the first row as JSON to pre-fill the create form (`create.blade.php:1107-1119`); the normal `store()` saves it. The upload mapping (`app/Exports/ClientExport.php` columns) contains no `company_id`/`prefix`/`value`/`suffix`, so the uploaded file cannot set the ID. The only `Client::create()` in app code is ClientController.php:279. |
| Regenerated on edit? | **No.** `edit()` (lines 362–366) recomputes prefix/suffix but the edit view (`edit.blade.php:46-47`) only displays the stored `client_id` readonly; `update()` (lines 370–427) never touches `client_id`/`prefix`/`value`/`suffix`. Existing IDs are preserved. |

### Generation-logic defects (pre-existing, verified)

- **Race condition:** the next `value` is computed when the form opens, not when it saves, with no lock/transaction/re-check. Two users opening the create page before either submits get the same ID. No DB constraint catches it. (ClientController.php:207-208)
- **Varchar sort bug:** `value` is a string; `latest('value')` sorts as text, so once value 10000 exists, "9999" > "10000" and 10000 is issued again.
- **FY suffix is calendar-year:** `now()->format('y')` — a client created Jan–Mar 2027 gets "/27-28" although the Indian FY would still be 26-27. The `value` sequence is global and never resets at a year change.
- **Reusable numbers after delete:** `destroy()` (lines 696–729) hard-deletes (no SoftDeletes); deleting the highest-value client lets that number be reissued.
- The same `latest('value')` pattern exists for projects (`ProjectController.php:122`).

---

## 3. Database Dependency Mapping

**Local DB (read-only inspection):** 2 clients, IDs `CA 1001/26-27` and `CA 1002/26-27` (PKs 9992/9993), values 1001–1002, no duplicates, no NULL/empty IDs, single format `CA … /26-27`. Only index on `clients` is the PRIMARY key. 19 staff rows reference clients.

### Tables referencing clients

| Table | Column | Type | Stores | FK constraint | Affected by format change? |
|---|---|---|---|---|---|
| clients | client_id, prefix, value, suffix | varchar(255) nullable | formatted string + parts | none / no unique | Yes — the thing being changed |
| client_contacts | client_id | foreignId | numeric PK | yes (cascade) | No |
| client_addresses | client_id | foreignId | numeric PK | yes (cascade) | No |
| client_cumans_wages / client_corporate_wages | client_id | foreignId | numeric PK | yes (cascade) | No |
| staffs | client_id | foreignId nullable | numeric PK | yes (restrict) | No |
| staffs | **company_code** | varchar(255) | `XXX-` + **formatted ID** copy | no | **Stale copy for old rows** |
| recruitments | client_id | foreignId nullable | numeric PK | yes (restrict) | No |
| recruitments | **company_code** | varchar(255) | `XXX-` + **formatted ID** copy | no | **Stale copy** |
| salaries | client_id | foreignId nullable | numeric PK | yes (restrict) | No |
| attendances | client_id | unsignedBigInteger | numeric PK | no (indexed) | No |
| m_o_u_s | client_id | integer | numeric PK | no | No |
| bank_tran_files | client_id | integer | numeric PK | no | No |
| bank_tran_files | **client_code** | varchar | **formatted ID** copy (BankBenefController.php:434) | no | **Goes into bank file** |
| proformas, invoices, pay_sheets, corporate_paysheet | client_id | integer | numeric PK | no | No |
| epf_temp, esi_temp, lwf_temp, pt_temp, emp_form, wages_form | client_id | unsignedBigInteger | numeric PK | no | No |
| project_allocation | client_id | unsignedBigInteger | numeric PK | yes (set null) | No |
| project_allocation | project_unique_id | varchar | contains payroll_id or **formatted ID** | no | Stale copy (display) |
| letters | client_id | foreignId nullable | numeric PK | yes (null on delete) | No |
| payslips | **entity_code** | varchar | **formatted ID** copy (PayslipController.php:381) | no | **Stale copy; used in LIKE filter** |
| daily/weekly/monthly/biweekly/project timesheets | **client_id** | **varchar(255)** | **formatted ID string** (not numeric!) | no | **Yes — see §5 timesheets** |

**Important:** `client_id` does *not* mean the same thing everywhere. On `clients` and the five timesheet tables it is the formatted string; everywhere else it is the numeric PK.

---

## 4. Connected Module Mapping

Risk levels assume **Option B** (new format for new clients only).

| # | Module | Key files | Client link | Formatted-ID usage | Risk |
|---|---|---|---|---|---|
| 1 | Client Mgmt | ClientController.php; client create/edit/view blades | n/a | Generated/stored here | **Critical to change correctly** |
| 2 | Client Bulk Upload | ClientController::clientUpload (117–181) | — | Pre-fills create form only; cannot set ID | None |
| 3 | MOU | MouController.php (174, 288), MouService.php, MOU.php | numeric | Dropdown text only (`mou/create.blade.php:33`, edit:66). **No MOU reference number exists**; docs use company name only | None |
| 4 | Recruitment | RecruitmentController.php (377, 828 numeric; 48/148/634 display; 727 LIKE search); **RecruitmentBulkImport.php:154 exact match**; `company_code` built in JS (`recruitment/create.blade.php:859`) and PHP (import:181-184) | numeric | Exact import lookup; double-prefix `company_code` | **Medium** |
| 5 | Staff | AllStaffController.php (504 numeric, 174 LIKE search); Staff ID from `Helper.php:28-66` ('CMS ' or project name — **not** client ID); `company_code` JS (`all-staff/create.blade.php:1415`, edit:1439) | numeric | **StaffCreateBulkImport::findCompany() (423–439)**: exact match, then regex `^(.{3})-(.+)$` on the company-code wrapper (parses the wrapper only, not the ID — new format still splits correctly). StaffBulkImport (backup restore) uses numeric only | **Medium** (import + double prefix) |
| 6 | Allotment / Project Allocation | ProjectAllocationController.php (160/206/224/287 numeric; 162 display; 43-44 pre-existing broken exact filter) | numeric | Display; `project_unique_id` stored copy | Low |
| 7 | Attendance (manual/bulk/import/report) | AttendanceController.php, AttendanceReportController.php (numeric filters; 372 LIKE search); BulkAttendanceImport.php (84, 189 — looks up staff by staff_id, copies numeric client_id) | numeric | Dropdown display only | None |
| 8 | Salary Generation | SalaryGenerateController.php (336 numeric; 74 display; 49-53 LIKE filter); SalarySheetImport.php:37 numeric | numeric | Display + LIKE | Low |
| 9 | Bank Beneficiary | BankBenefController.php (numeric queries; **434: `client_code = $client->client_id`** stored); BankTransactionExport.php:31; bank-trans-pdf.blade.php:23 | numeric | **Formatted ID written into the bank transaction file** | **Medium** — verify the bank accepts the longer hyphenated code |
| 10 | Paysheet | PaySheetController.php (285/403/597/747 numeric; 54-57 LIKE); paysheet/create.blade.php:856/890 JS company_code (not persisted by controller) | numeric | Display/LIKE; number is `PS/FY/...` (Helper::getPaysheetNumber) — no client part | Low |
| 11 | Corporate Paysheet | CorporatePaysheetController.php (numeric; number `CPS-`+id, line 615); corporate-paysheet create/edit JS company_code | numeric | Display only | Low |
| 12 | EPF / ESI / PT / LWF | EpfController, EsiController, PtController, LwfController — all numeric; LIKE search endpoints only | numeric | Dropdown display | None |
| 13 | Invoice / Credit / Debit | InvoiceController.php (271/490 numeric; 54 LIKE; bulk import row[3] = numeric id); invoice no. = `N/FY-random` (Helper.php:325) — no client part; credit/debit-note edit blades build JS company_code | numeric | Display; double-prefix JS | Low |
| 14 | Proforma | ProformaController.php (205/366/398 numeric); number `PI/FY/seq` (100–122) — no client part | numeric | Display | None |
| 15 | Letters | LetterService.php:71/226/382 — keyword `{{client_code}}` → `client->client_id` printed in letters; **letter number (LetterNumber.php:21-22) = first 3 letters of company_name + FY + seq — not the client ID**; IssuedLetterController.php:291 display, index.blade.php:313-327 exact JS match on returned code (self-consistent) | numeric | Printed in letter body for clients using the keyword | Low |
| 16 | Payslip | PayslipController.php (numeric links; 381 stores `entity_code` copy; 48 LIKE on entity_code) | numeric | Stored copy, LIKE filter | Low |
| 17 | Timesheets (daily/weekly/monthly/biweekly/project) | Controllers store `$request->client_id` = **formatted string** (Daily:118, Weekly:115, Monthly:111, BiWeekly:121, Project:112); **exact-match index filter** `where('client_id', $request->client_id)` (Daily:34 etc.); models declare numeric `belongsTo` that never matches (pre-existing); DailyTimeSheetController::update():169 saves the numeric id instead (pre-existing inconsistency) | **formatted string** | Old rows keep old format; exact filter splits old vs new | **Medium** |
| 18 | Dashboard | DashboardService.php (368, 374) | numeric | Never shown | None |
| 19 | Leave Mgmt / Payslip / Staff Relieving / All Forms / Manager Approval / Timesheet Reports | numeric throughout; LIKE searches; ManagerApprovalController.php:38-39 pre-existing broken exact filter | numeric | Display | None–Low |
| 20 | Exports | CumansPaysheetExport, CumansWageExport, StaffBackupExport, BankTransactionExport (client_code), template sheets (examples) | numeric | Template examples hardcode old format | Low |

---

## 5. Hardcoded Format Dependencies

### 5.1 Hardcoded `'CA '`
Only in `ClientController.php:202` and `:362` (the generator itself), plus example text and test fixtures:
- `app/Exports/Sheets/StaffTemplateSheet.php:38` (`'CA 1001/26-27'`), `StaffInstructionsSheet.php:25-26`
- `app/Exports/Sheets/RecruitmentTemplateSheet.php:38`, `RecruitmentInstructionsSheet.php:25` (example `CLT-001`, already outdated)
- Tests (fixtures only, won't break): `tests/Feature/StaffListFilterTest.php:113-114, 221-225`; `RecruitmentStageTest.php:318-319, 394-405`; `LetterKeywordTest.php:195-199`

### 5.2 Parsing / splitting
- **No code splits the client ID itself.** All `substr()` calls operate on company/project *names*.
- The only regex touching a client code is `StaffCreateBulkImport::findCompany()` `/^(.{3})-(.+)$/u` — it strips the 3-letter *company-code wrapper*, then matches the remainder exactly. It still works with the new format.

### 5.3 Double-prefix hazard (JS `name.substring(0,3) + '-' + client_id`)
Produces `YAS-YA-CA 1003/26-27` under the new format:
- `all-staff/create.blade.php:1415-1416`, `edit.blade.php:1439-1440` → persisted to `staffs.company_code` (AllStaffController.php:513, 1005)
- `recruitment/create.blade.php:859-860` → persisted to `recruitments.company_code` (RecruitmentController.php:411)
- `paysheet/create.blade.php:856-857, 890-891`; `corporate-paysheet/create.blade.php:563-564, 1241-1242`, `edit.blade.php:564-565`; `credit-note/edit.blade.php:316-317`; `debit-note/edit.blade.php:418-419` (form-only, not persisted by controllers)
- Same derivation in PHP: `StaffCreateBulkImport.php:188-191`, `RecruitmentBulkImport.php:181-184`

### 5.4 Exact string comparisons on the formatted ID
- `RecruitmentBulkImport.php:154` — `where('client_id', $clientCode)`
- `StaffCreateBulkImport.php:423-439` — exact match (+ wrapper regex fallback)
- Timesheet index filters — `where('client_id', $request->client_id)` exact (Daily:34, Weekly:34, Monthly:30, BiWeekly:36, Project:32)
- `issue-letter/index.blade.php:313-327` — exact JS match against codes the same page supplied (self-consistent, safe)

### 5.5 LIKE / partial match (safe — old search terms still match new IDs)
List filters: InvoiceController:51-54, DebitNoteController:38-41, CreditNoteController:39-42, PaySheetController:54-57, SalaryGenerateController:49-52, CorporatePaysheetController:59-62; PayslipController:48 (on entity_code). Select2 `searchClients` endpoints in ~20 controllers (Epf:208, Esi:205, Lwf:166, Pt:229, Attendance:372, Invoice:655, Proforma:440, BankBenef:87, Recruitment:727, timesheet controllers, etc.).

### 5.6 Length
All receiving columns are varchar(255); new IDs (~17–21 chars) fit everywhere. No fixed-width slicing exists. The only external unknown is the bank file's `client_code` field (§4 #9).

---

## 6. Existing Client Data Impact (local DB, read-only)

- 2 clients; IDs `CA 1001/26-27`, `CA 1002/26-27`; values 1001–1002; one format; **no duplicates, no NULL/empty IDs; unique in practice but not enforced** (no unique index, no validation rule).
- Formatted IDs are referenced (as copies) in `staffs.company_code`, `recruitments.company_code`, `bank_tran_files.client_code`, `payslips.entity_code`, timesheet `client_id` columns — counts depend on data; 19 staff rows link to clients by numeric PK.
- Numeric PKs (9992, 9993) are entirely independent of the formatted ID; **existing records keep their PKs under every option.**

---

## 7. Local vs Production / Git-Pull Deployment Risks

| # | Question | Finding |
|---|---|---|
| 1 | Code ↔ production schema compatible? | For Option B: yes — no schema change needed; `client_id` is already varchar(255). ⚠ Unverified: production schema was not inspected. |
| 2 | Older formats in production? | Likely (production predates local). Since all joins are numeric, mixed formats only affect display, timesheet exact filters, and bulk-upload typing. ⚠ Unverified. |
| 3 | Deployment migration required? | Option B: **no**. Option A: yes (data update). Option C: yes (new column). An optional migration adding a unique index on `clients.client_id` is recommended regardless (safe only after a duplicate check on production). |
| 4 | Would old IDs stop matching records? | No — records are linked by numeric PK. Old formatted IDs keep matching their own rows. |
| 5 | Dropdowns / history keep working? | Yes — dropdowns are built from live DB values; history shows whatever string was stored at the time. |
| 6 | Can old and new formats coexist? | Yes — nothing validates the format; LIKE searches match both. Cosmetic inconsistency in lists/letters/timesheets. |
| 7 | Code-only deployment sufficient? | **Yes, for Option B.** |
| 8 | Backup / data migration necessary? | Backup always advisable; data migration only for Option A (and optionally to normalize timesheet copies). |
| 9 | Concurrent creation duplicates? | **Yes — already possible today** (form-render-time sequence, no lock, no unique constraint). The change is the right moment to fix it (compute in `store()` inside a transaction with `lockForUpdate()`, add unique index). |
| 10 | Rollback risk? | Low for Option B: rolling back the code leaves some clients with `YA-CA …` IDs; everything keeps working (numeric links), but the old `create()` sequence logic would resume. If `value` handling changed, verify rollback doesn't mis-compute the next number. |

⚠ Could not verify: production DB contents/schema, hosting deployment process (no deploy scripts, CI workflows, or Envoy files exist in the repo), and the bank's accepted `client_code` format.

---

## 8. Risk Classification

| Risk | Level |
|---|---|
| Generation must move from page-render to name-driven (JS + server-side in `store()`); otherwise the prefix can't be derived | **Critical (to the implementation, not to existing data)** |
| No unique constraint + no lock → duplicate IDs under concurrency (pre-existing, worsened by trusting hidden form fields) | **High** |
| `value` varchar text-sort breaks at 10000 (pre-existing) | **High (latent)** |
| Double-prefix `company_code` (`YAS-YA-CA …`) in 7 forms + 2 imports | **High (cosmetic/data-quality, certain to occur)** |
| Staff/Recruitment bulk imports exact-match the formatted ID; users must type the stored format | Medium |
| Bank file `client_code` carries the new longer format — external acceptance unverified | Medium |
| Timesheet tables store the formatted string; exact filters split old/new rows | Medium |
| Template/instruction example text outdated | Low |
| Letters printing `{{client_code}}`, payslip `entity_code`, display dropdowns, LIKE filters | Low |
| FY suffix uses calendar year; no 2-alpha-char validation on company_name (e.g. name "1X" or "&Co") | Low–Medium (edge cases) |

---

## 9. Implementation Options

### Option A — Rewrite all existing formatted IDs with the new prefix
- **Pros:** uniform format everywhere; exact-match imports and timesheet filters see one format.
- **Cons:** must also rewrite every stored copy (`staffs.company_code`, `recruitments.company_code`, `payslips.entity_code`, `bank_tran_files.client_code`, 5 timesheet tables, `project_unique_id`) or they go stale; printed/issued documents (letters, bank files, payslips) retroactively disagree with the DB; users' known IDs change; requires a production data migration and full backup; hardest rollback (must reverse all rewrites).
- **DB impact:** multi-table UPDATE migration. **Deployment risk:** highest. **Rollback:** difficult.

### Option B — New format for new clients only; existing IDs untouched *(recommended)*
- **Pros:** zero data migration; code-only deployment; numeric links make mixed formats harmless; easy rollback.
- **Cons:** two formats coexist in lists/dropdowns/letters; bulk-upload users must type each client's actual stored ID; timesheet exact filters still exact-match per stored value (unchanged behavior).
- **DB impact:** none required (unique index recommended). **Deployment risk:** low. **Rollback:** easy.

### Option C — Keep PK + add a separate display/reference ID column for the new format
- **Pros:** cleanest separation; legacy `client_id` untouched; display layer switches to the new column where wanted.
- **Cons:** one migration (new column); every display point, search endpoint, letter keyword, and the two imports must choose which column to use (~40+ touchpoints); two "client IDs" confuse users and bulk-upload templates.
- **DB impact:** additive column. **Deployment risk:** medium (broad touch surface). **Rollback:** easy (column is additive).

---

## 10. Recommended Safe Approach

**Option B**, implemented as:

1. Compute the ID at **save time** in `store()` inside a DB transaction: derive the 2-letter prefix from the first two *alphabetic* characters of `company_name` (uppercased), recompute `value` with `lockForUpdate()` on a numeric comparison (cast or fix the column type), build `client_id` server-side — **stop trusting the submitted `company_id`/hidden fields**.
2. Keep the create form's readonly preview, updated by JS as the company name is typed (preview only).
3. Add `unique` validation + a unique DB index on `clients.client_id` (after confirming no production duplicates).
4. Fix the JS/PHP `company_code` builders (§5.3) so they don't double-prefix (e.g. use the client ID as-is, or name-prefix only when the ID lacks one).
5. Update template/instruction example text (§5.1).
6. Decide with the bank whether `bank_tran_files.client_code` may carry the new format.
7. Optional hardening in the same change: convert `value` to an integer column (or `CAST` in the max query), correct the FY suffix to April–March if business requires it, and require ≥2 alphabetic characters in `company_name`.

---

## 11. Exact Files Needing Changes (Option B)

| File | Change |
|---|---|
| app/Http/Controllers/ClientController.php (`create()` 202–209, `store()` 215–233, `edit()` 362–366) | Server-side generation at save time, locking, uniqueness, prefix from company name |
| resources/views/template/client/create.blade.php (52–57, validation ~1324) | JS live preview of ID from company name; remove trust in hidden fields |
| resources/views/template/all-staff/create.blade.php:1415, edit.blade.php:1439 | company_code double-prefix fix |
| resources/views/template/recruitment/create.blade.php:859 | same |
| resources/views/template/paysheet/create.blade.php:856, 890; corporate-paysheet create/edit; credit-note/edit:316; debit-note/edit:418 | same (form-only) |
| app/Imports/StaffCreateBulkImport.php:188-191; app/Imports/RecruitmentBulkImport.php:181-184 | same derivation in PHP |
| app/Exports/Sheets/StaffTemplateSheet.php:38, StaffInstructionsSheet.php:25-26, RecruitmentTemplateSheet.php:38, RecruitmentInstructionsSheet.php:25 | Update example IDs |
| database/migrations (new) | Optional: unique index on clients.client_id; optional `value` type fix |

---

## 12. Testing Checklist

- [ ] Create a client; verify ID = `XX-CA {next}/yy-yy` with correct 2-letter prefix (names starting with digits/symbols, 1-char names, lowercase names).
- [ ] Two simultaneous create-form sessions → no duplicate ID (unique rule rejects the second or sequence recomputed at save).
- [ ] Edit an existing old-format client → ID unchanged.
- [ ] Staff create & bulk upload against both an old-format and a new-format client (exact ID and `XXX-code` wrapper forms).
- [ ] Recruitment bulk upload with new-format client ID.
- [ ] `company_code` saved for staff/recruitment has a single prefix.
- [ ] All Select2 client searches (staff, attendance, EPF/ESI/PT/LWF, invoice, proforma, paysheet, timesheets) find clients by old and new IDs.
- [ ] List filters (invoice, paysheet, salary, credit/debit note, payslip) with partial old/new IDs.
- [ ] Bank transaction generation for a new-format client; inspect exported file/PDF `client_code`; confirm bank acceptance.
- [ ] Letter with `{{client_code}}` keyword renders the new ID; letter numbering unaffected.
- [ ] Timesheet create/filter for a new-format client; verify exact filter behavior across mixed rows.
- [ ] MOU, invoice, proforma, paysheet numbers unchanged in format.
- [ ] Client deletion flow and dashboard counts unaffected.
- [ ] Sequence crosses 9999 → 10000 correctly (if the `value` fix is included).

---

## 13. Unverified Items and Limitations

- **Production database** was not accessed: its client count, ID formats, duplicates, and schema drift are unknown. Run a read-only duplicate/format check there before adding a unique index.
- **Bank file specification** for `client_code` (length/charset) is external to the codebase.
- Local DB has only 2 clients; production-scale data patterns (legacy formats, NULL IDs) could differ.
- No deployment scripts/CI exist in the repo, so the hosting pull/deploy process itself could not be analyzed.
- Dynamic/JS behavior was reviewed statically, not executed.
- Pre-existing bugs noted but out of scope: timesheet `belongsTo` mismatch (string vs numeric), `DailyTimeSheetController::update()` storing numeric vs store() storing formatted, exact numeric filters in ProjectAllocation/ManagerApproval index, `corporateBulkUploadConfirm` null `client_id` read (CorporatePaysheetController.php:616).
