# PRD — Complaint & Returns (C&R) Module > ## ATURAN ABSOLUT #0 — legacy files are LOCAL ONLY > The 81 legacy PHP reference screens live in `Just An Example, Do not Link/` in this folder and > are **never** staged, committed or pushed — guarded by `.gitignore`, enforced by `README.md` > rule 1. **Files, not knowledge:** summarise a legacy rule in your own words and cite its > filename here, but never paste verbatim legacy PHP or SQL-as-written into this committed > document. Never read the raw legacy install at `/opt/lampp` to work around a missing file — > that is a hard rail. > > ## ATURAN ABSOLUT #0b — parallel sessions share this tree > Several Claude sessions edit this checkout at once. mtime-check before every edit, create new > files only after `ls`/`git status` on the exact path, commit by explicit pathspec only, and > never run repo-global destructive git. See `README.md` rules 2 and 12. > > ## 🔴 ATURAN ABSOLUT #0c — LEGACY IS A REFERENCE, NOT A TEMPLATE > User directive, 2026-07-21, verbatim: *"legacy is just a reference. do not use it as is. > follow the claude md, and memory."* Everything in §4, §5 and §6 below is the **business rule**, > read out of the legacy source and recorded as fact. **How it gets implemented is governed by > CLAUDE.md, not by the legacy code** — Eloquent not raw SQL, FormRequest not inline checks, > Policy + `rolemenu` gate not "whoever loads the page", transactions, TDD. §7 lists the legacy > defects that must **not** be carried forward. See `README.md` rule 15. > > ## ⚠️ ATURAN ABSOLUT #0d — three column names in this module lie > `DepartmentID` is not `department.ID`; `SatuanPackingID` is not a `satuan`; and > `BarangListID` is not a foreign key. All three are measured, all three will bite silently. > See §5.8. A fourth trap has the same shape but is about *values*, not names: no datetime > column here is ever NULL — "no date" is stored as the zero-date `0000-00-00` (§5.8 #9). > > ## 🔁 ATURAN ABSOLUT #0e — this document was ADVERSARIALLY RE-VERIFIED on 2026-07-21 > Every §4–§7 claim was re-read against the 81 legacy files, the live dev DB, the ten generated > models, the prototype and the mail Blade. Most survived. **These did not, and are corrected > in place — do not reinstate them from memory or from an older copy:** > > | Was claimed | Actually | > |---|---| > | "`users.DepartmentID` is never used" | Used — it hides every commercial column from Logistic (§4.1 #1) | > | "Status 9 has no writer / is dead" | No *workflow* writer, but written by the override and **live on 11 action rows** (§5.7, §6.4) | > | "The generated `satuanPacking()` encodes the trap" | It is **correct**; the three `department()` relations are the wrong ones (§5.8 #8) | > | "Search terms concatenated into `LIKE`" | Only one `LIKE` exists module-wide; the rest is raw **numeric equality** + raw `ORDER BY`/`LIMIT` (§7) | > | "CEO assign emails once per department" | Once per **(line × department)** row (§6.6) | > | "View All Read Only is byte-identical to View All" | Differs on lines 2–3 (§4.2) | > | "View Request displays `UserIDSales`" | Selected by all five lists, **rendered by none** (§4.2) | > | "Prototype: nine files, four `Handling*`" | **Eleven** files, **six** `Handling*` (§11) | > | "`header.php` was deliberately not supplied" | **Found and read** — and it discards its own authorization check (§5.8 #11) | > > ⚠️ **One claim was nearly deleted as false and is TRUE:** the two self-referencing > `INSERT…SELECT`s (§7). A single-line grep misses them because the `SELECT` is on the next > line. Verify multi-line before "correcting" anything in §7. --- ## 1. Document Metadata | Field | Value | |---|---| | **Title** | PRD — Complaint & Returns (C&R) Module | | **Module** | Complaint & Returns — a customer complaint about delivered goods, filed per product line, fanned out to six departments for review, assigned by the CEO, worked, and closed. Legacy root menu **225** (`ShowName` "Complain & Returns"). | | **Status** | **NOT BUILT.** No controller, routes, pages, policy, request, seeder or tests. Exists: 10 auto-generated models, a dormant mail Blade, a UI prototype, four live lookup tables. **Legacy source RECEIVED and ANALYSED 2026-07-21 (81 PHP files, 77 distinct filenames).** **RE-VERIFIED adversarially 2026-07-21** — nine claims corrected, three new traps found, `header.php` located; see Aturan Absolut #0e. | | **Owner** | IT — PT Colorindo Chemtra | | **Date** | 2026-07-21 (data model verified against live dev `colorindochemtrainertia`; workflow verified against the legacy source) | | **Stack** | Laravel 13.8 / PHP 8.4 / Inertia.js v3 / React 19.2 / Tailwind v4 / MySQL 8.4 — existing production schema, no structural changes | | **Document Type** | Hybrid PRD — **verified** data model and **verified** workflow, the prototype's design inventory, a defect inventory, a phased roadmap, and the residual open questions. Inferences are labelled as such. | ### Locked decisions (user) | Date | Decision | |---|---| | 2026-07-21 | **Scope is legacy root menu 225 ONLY.** "Complain Handling" (root **363**, the `complainitem*` + `complaintosupplier*` families, 18 leaves) is a **separate module**, out of scope. See `README.md` rule 20. | | 2026-07-21 | Module folder is `Pages/MenuComplaintAndReturns/`, PRD filename `ComplaintAndReturns PRD.md`. | | 2026-07-21 | Documents use the **UI spelling "Complaint"**; all code touching the schema uses the DB's **"Complain"** verbatim. Do not rename either. | | 2026-07-21 | Self-contained README rather than cross-referencing Visit Plan's. Rules shared with Visit Plan / Budget and Target are tagged `[SHARED]` and change together. | | 2026-07-21 | Read-only DB access (`SHOW` / `DESCRIBE` / `SELECT COUNT`) pre-approved. | | **2026-07-21** | **Legacy is a REFERENCE, not a template** — extract the business rule, implement per CLAUDE.md and the memories. Aturan Absolut #0c. | | **2026-07-21** | **Email is built but NOT enabled** in the final phase; the session stops for explicit sign-off before any live dispatch. | | **2026-07-21** | **The two ERP print screens are OUT OF SCOPE** — `complainprintsuratjalan.php` / `complainprintinvoice.php` read a separate PostgreSQL ERP database. Documented in §6.8, not ported. | | **2026-07-22** | **Q17 answered — rebuild the admin status-override as a gated capability**, not the legacy's ungated version: its own menu leaf, its own Policy, an explicit allowed-transition matrix, and an audit row recording who overrode what. See §6.7, §9. | | **2026-07-22** | **Q19 answered — Logistic's commercial-column blackout applies module-wide** across Complaint & Returns (business rule: Logistic must not see money-related data anywhere in this module), not just the one legacy screen. Decision is scoped to **this module only** — no claim is made about any other module. See §4.1 #1, §9. | | **2026-07-22** | **Q9 answered — Finance/Accounting/Logistic queues stay unscoped**, reproducing legacy exactly (company-wide visibility to anyone holding that queue's menu grant). No department-membership scoping added. | | **2026-07-22** | **Q13 answered — promotion stays complaint-wide**, reproducing legacy exactly: a complaint reaches the CEO only once all six departments have reviewed every line (not line-by-line), and becomes closeable only once every department the CEO assigned across every line has finished (also not line-by-line). User's own words: "the complain only ask for CEO's approval once everyone has reviewed it, and can be closed once everyone that CEO had ask has complete their assignment" — confirmed as a restatement of complaint-wide promotion. | | **2026-07-22** | **Q4 answered — the 6 Feedback screens (244–249) are dropped.** Zero live usage, never supplied, structurally unreachable via the seeded menu tree (`IsShow=0` on the parent group). No phase budgeted for them. | | **2026-07-22** | **Q11 answered — Cancel/Close authorization is purely `rolemenu`-grant-based, no separate ownership field-check.** Cancel: derives from which list-scoping menu a role holds (a role granted only View Request can only reach/cancel its own complaints, because that list itself scopes to `UserIDInput=actor`; a role granted View All can cancel any complaint), gated on header status ∈ {1,2,3}. Close: its own standalone menu-256 grant, gated on status 4, no ownership check at all — matches legacy exactly. | | **2026-07-22** | **Q6 answered — attachments ARE built** (header-level upload on Create, despite 0-of-34 legacy usage), using the `file_uploads` convention. `{menu_name}` = **`complaintreturns`**. | | **2026-07-22** | **Q8 answered — role/menu grants seeded for Administrator (RoleID 1) only for now.** User will assign the remaining 28 leaves to other roles later via the existing Role-Menu Assignments admin screen; not blocking the build. | | **2026-07-22** | **Q12 answered — no cap on `ComplainQuantity`**, matching legacy (the apparent cap is dead code reading warehouse stock, not a real business limit). | | **2026-07-22** | **Q14 answered — Create for Others (325) IS built**, but rebuilt properly: real `HeadID`-subtree scoping on the Sales picker, fixed reason-dropdown bug, fixed refund-field JS bug — the functionality is real, just badly implemented in legacy. | | **2026-07-22** | **Q15 answered — the department code map is kept as a hardcoded module constant** (1=SM, 2=PM, 5=Logistic, 6=Finance, 8=CS, 11=Accounting), never resolved through the `department` table (rule 19/§5.9). User: "keep the hardcode, I'm not sure where they got it either... hardcode works as of now." | | **2026-07-22** | **Q16 answered — the two send-back branches (Back to Review, Back to Approval CEO) DO notify**, unlike legacy (which notifies nobody). New behavior beyond the port — recipients/wiring decided in Phase 7/8. | | **2026-07-22** | **Q18 answered — the 19 legacy zero-dates render as `—`** (blank dash) at the display boundary, never written back. | | **2026-07-22** | **Phase 1 (`complaintype` lookup CRUD, menu 253) — DONE.** See §8. | | **2026-08-07** | **The detail screen gets PAGE-LEVEL header actions**, matching `/quotations/view-all/{id}`: the cluster sits on the right of the hero, left of Back to List. The Close screen keeps its pinned `DecisionBar` pill — Close/Back-to-Review/Back-to-CEO are decisions against the record, Print/Cancel/Change Status are page-level actions (`.claude/rules/ui-conventions.md`). | | **2026-08-07** | **One detail ROUTE per list scope, one shared page** (`complaints.show`, `.head-dept.show`, `.all.show`, `.all-readonly.show`, `.all-report.show`). The scope is named by the URL and `show()` authorizes against it (`ComplainPolicy::viewInScope`). Replaces `effectiveView()`, which returned the first grant that matched and so gave multi-grant users the wrong scope, the wrong Back-to-List target, and (once buttons existed) the wrong action set. Cross-module drill-through goes through `complaints.open`, the single place a scope may be inferred. | | **2026-08-07** | **Cancel is narrowed to View Request + View All** (`ComplainPolicy::CANCEL_VIEWS`), button and POST alike. Q11 only ever specified those two; delegating to `view()` had silently also granted Cancel to head-dept / all-readonly / all-report holders. Legacy rendered no Cancel button on any of the three. | | **2026-08-07** | **Print appears on every detail screen EXCEPT head-dept**, whose legacy Action row is commented out in full. Enabled at status {3,4,5,6}, disabled "Cannot Print" twin otherwise. Legacy's btn-print writes nothing, so neither does ours. | | **2026-08-07** | **The printed report is LEGACY-EXACT and ignores two on-screen restrictions**: all-report's per-line scope and the Logistic commercial blackout. ⚠️ Raised and accepted: a Logistic-department user can read the commercial columns their screen blanks by pressing Print. See §7 divergences; do not "fix" without going back to the user. | | **2026-08-07** | **The Change Status dropdown offers all 9 `complainstatus` rows** (legacy parity), Cancel and Reject by CEO included. It stays the only path that can set status 9 (Re-Review). ⚠️ This **overrides the "explicit allowed-transition matrix"** half of Q17 (2026-07-22). The other three halves of Q17 are built: its own Policy (`ComplainPolicy::changeStatus`), a required comment, and an audit row per override. **Still open from Q17: a dedicated menu leaf** — the capability is currently keyed on the View All grant (`/complaints/all`), which is where legacy put it. | *(Add every future user decision here, dated — `README.md` §5 On-end requires it.)* --- ## 2. Overview & Purpose A customer receives goods and something is wrong — wrong item, wrong quantity, quality defect, damaged packaging, or an overpayment. A salesperson files a **Complaint & Returns report**: one header identifying the company, contact person, division and sales owner, plus one or more **detail lines**, each naming a product and carrying the commercial paper trail that proves the claim — delivery note (SJ) number and date, invoice number and date, quantity received vs quantity complained about, unit price in USD and IDR with the exchange rate, total value at stake, whether it has been paid, and refund bank details. Each line also records three narrative fields that are the actual point of the document: **what went wrong** (`ComplainDesc`), **what the customer wants** (`CustomerExpectation`), and **what the reporter is asking the company to do** (`ReporterRequestExpectation`). **The mechanic that defines this module:** at creation, every product line is fanned out to **all six departments at once** — PM, SM, CS, Finance, Accounting, Logistic — by inserting six `complaindetailsaction` rows per line. Each department then reviews its own row. Once every department has reviewed, the complaint reaches the **CEO**, who picks which departments must actually *act* and gives each one an instruction. Those departments complete their assignment, and the complaint is closed. **So this is not an approval ladder.** It is a parallel fan-out to six work queues with a central dispatcher in the middle. Visit Plan's linear tier chain is the wrong mental model; the closer analogy is a ticket broadcast to every team, triaged by one person, then worked by the teams that were picked. ### 2.1 What the whole menu is for (screen-by-screen) Root **225** holds 28 leaves under 3 group rows; Pengelolaan menu **252** holds one more. Verified against the dev `menu` table 2026-07-21 (full table in §10). - **Create** (226) and **Create for Others** (325) — the entry forms. On-behalf is real but rare: **1 of 34** live headers has `UserIDInput ≠ UserIDSales`. - **Review** (group 227) → six per-department queues (228–234). - **Approval CEO** (235) — the dispatcher. Picks departments, attaches per-department instructions, or rejects the whole complaint. - **Complete Assignment** (group 236) → six per-department queues (237–242). - **Feedback** (group 243) → six screens (244–249) — ⚠️ **almost certainly dead**, see §9 Q4. - **Close** (256) — the terminal action, plus two send-back paths. - **View Request** (250) / **View Head Dept** (422) / **View All** (251) / **View All (Read Only)** (326) / **View All Report (Read Only)** (385) — the same list scoped five ways (§4.2). - **Complain & Returns Type** (253, under Pengelolaan 252) — the `complaintype` lookup CRUD. --- ## 3. Scope ### 3.1 In Scope — nothing built yet No phase has been scoped or approved. §8 proposes an order; each phase must be confirmed with the user before it starts. ### 3.2 Explicitly NOT in this pass (2026-07-21) - Any controller, route, page, policy, request, seeder, migration or test. **Nothing was built.** - Any change to the models, the dormant mail Blade, or the prototype. ### 3.3 Out of Scope until explicitly scheduled - **Complaint Handling** (legacy root 363) — a different module. §1 Locked decisions. - **Live email.** Built in Phase 8 but **not enabled** without sign-off (§1, §8). - **A dedicated Status-9 (Re-Review) transition.** There is no *workflow* screen that produces it — but it is **not** unwritten: the generic admin status-override (§6.7) can set any status in the lookup, and **live data carries status 9 on 1 header, 1 line and 11 action rows**. So: build no bespoke transition, but the value **must render** everywhere status is displayed (§6.4). Treating 9 as non-existent will break the read model on real rows. - **The two ERP print screens** — `Review/complainprintsuratjalan.php` and `complainprintinvoice.php` query a **second, PostgreSQL ERP database**, not the app's MySQL (§6.8). **User decision 2026-07-21: out of scope — documented, not ported.** - **Status 5 (Feedback) as a workflow stage** — §9 Q4. - **The legacy PDF report** (`reportcomplaint.php`, FPDF). If a printable report is wanted it is a new build on the client-side react-pdf convention (CLAUDE.md), not a port. --- ## 4. Users & Permissions — VERIFIED from the legacy source, 2026-07-21 Access follows the project-wide Role/Menu contract (CLAUDE.md): a `rolemenu` grant from the user's role to a `menu` row, joined on `menu.LinkLaravel`. **Sidebar visibility and the server 403 gate must use IDENTICAL grant conditions**, enforced at the leaf. > ⚠️ **The legacy has no authorization to port.** Every screen's only gate is an `include` of > the app-wide `header.php` (deliberately not supplied — `README.md` rule 17); the write handlers > take the department from `?DepID=` in the URL and never verify the caller may act for it; the > two "Read Only" screens still carry live cancel and status-change POST handlers. **The scopes > below are what the legacy uses to decide what to SHOW. The port must additionally ENFORCE > them server-side** (Policy + `authorize()`), which the legacy never did. ### 4.1 The six department queues — three different rules, not one Each queue applies **two independent filters**. Filter A is the same everywhere; filter B is not. **Filter A — the department gate (all six, identical).** An INNER JOIN to a derived table over `complaindetailsaction` selecting lines where `ComplainDetailsStatusID = 1` AND `DepartmentID = `. A line appears only while *that department's own action row* is still un-reviewed. ⚠️ The legacy subquery omits `IsDeleted = 0` — the port must add it. **Filter B — the ownership gate (differs per queue).** | Queue | Dept code | Ownership predicate | Keyed on | |---|---|---|---| | **PM** | 2 | `userprincipal` where `IsHeadDiv=1` AND not deleted AND `UserID` = actor | the **product's** principal (`barang.PrincipalID`) — *not* `complaindetails.PrincipalID` | | **SM** | 1 | `userdivision` where `IsDeleted=0` AND **`IsHeadDiv=1`** AND `UserID` = actor | the **complaint header's** `DivisionID` | | **CS** | 8 | `userdivision` where `IsDeleted=0` AND `UserID` = actor — **no `IsHeadDiv`** | the header's `DivisionID` | | **Finance** | 6 | **NONE** | — | | **Accounting** | 11 | **NONE** | — | | **Logistic** | 5 | **NONE** | — | Three findings worth stating plainly: 1. **`users.DepartmentID` does not bind a user to a queue — but it is NOT unused.** No queue consults it (hence the puzzle that no live user carries `DepartmentID` 1 or 2 — as *queue* codes those are screen constants, never user attributes). It is nevertheless read in exactly one place, and it carries a real rule there: `listcomplainallreportdetailsreadonlyview.php` loads the actor's `users.DepartmentID` (lines 6–11) and passes it to four gates (lines 107, 118, 176, 222) that suppress **every commercial column** — unit prices, total values, USD rate, invoice no/date, bank/account/refund, paid flag — when the actor's department is **5 (Logistic)**. **28 live users sit at `DepartmentID = 5`, so this fires.** It is the module's only column-level access control. ⚠️ A previous revision of this PRD claimed the column was "never used"; porting on that basis would silently drop the rule. (Live spread: 12×29, 5×28, 6×13, 4×11, 9×6, 0×6, 8×4, 11×4, 10×2, NULL×1 — **none at 1 or 2**.) **Answered 2026-07-22 (§9 Q19):** apply the blackout **module-wide** — every screen in Complaint & Returns that would otherwise show a commercial column to a `DepartmentID = 5` user suppresses it, not just this one legacy screen. Decision is scoped to this module only. 2. **CS is deliberately weaker than SM** — the two subqueries are otherwise identical, and CS simply omits `IsHeadDiv`, so any division member sees the CS queue. Reads as intentional; **confirm with the user** (§9 Q9). 3. **Finance, Accounting and Logistic have no ownership scoping at all** — every pending line company-wide, to anyone who can open the page. Verified: the only session-variable references in those three files sit inside **commented-out Visit Plan leftovers** (`vp.`/`vd.` aliases), not live SQL. This is the single biggest access-control decision in the module — **§9 Q9.** ⚠️ Because PM's gate joins through `barang`, any line whose `BarangID` does not resolve to a `barang` row is silently invisible to PM. Reproduce that only if the user wants it. The **Complete Assignment** queues (237–242) apply the identical six-way pattern, one status later (their department gate looks for `ComplainDetailsStatusID = 3`). ### 4.2 The five list views — five different scopes | View | Menu | Scope predicate | Tables | |---|---|---|---| | **View Request** | 250 | `complain.UserIDInput` = actor — **creator only**. ⚠️ It is the **only** one of the five that sanitises the session id (`abs(intval(…))`) | `complain` | | **View Head Dept** | 422 | `complain.UserIDSales` IN (actor + the actor's full **`users.HeadID` subtree**, computed recursively) | `complain`, `users` | | **View All** | 251 | unscoped (`IsDeleted = 0` only) | `complain` | | **View All (Read Only)** | 326 | unscoped — the view file differs from View All's **only on lines 2–3** (the two link targets: `HrefURL`, `paginationUrl`). ⚠️ Not "byte-identical" as an earlier revision claimed — 23,169 vs 23,153 bytes | `complain` | | **View All Report (Read Only)** | 385 | complaints the actor holds an **assignment row** on (`complaindetailsassignment.userid`) — "things that passed through my hands". **Plus two scopes the other four do not have**, see below | `complain`, `complaindetailsassignment` | ⚠️ **View Request scopes on `UserIDInput` while View Head Dept scopes on `UserIDSales`.** A head who *creates* a complaint whose sales owner sits outside their subtree sees it in one list and not the other. Verified asymmetry — decide deliberately rather than inheriting it. ⚠️ **"Read only" is a scope difference for one of the two.** For **View All (Read Only)** the label is honest: same query, the difference is only which actions the linked detail page renders — and in the legacy those actions are merely *hidden*, not blocked (§7). For **View All Report (Read Only)** it is not: that screen is simultaneously read-only **and the most heavily scoped variant in the module**, because its detail page is a *fork* of the shared one (`listcomplainallreportdetailsreadonlyview.php`, where the other four include the shared `listcomplaindetailsview.php`) and adds two rules found nowhere else: 1. **Detail lines are assignment-scoped too** — an inner join on `complaindetailsassignment.userid = actor` restricts which *lines* of a complaint are shown. The other four detail pages show every line. 2. **Commercial columns are hidden from Logistic** — the `users.DepartmentID != 5` gates of §4.1 #1. ⚠️ **All five lists render an IDENTICAL column set** (ReportNo · Details · ComplainType · CompanyName · Status · Creator · Division · ComplainDate · InputDate · Address · Telp). They differ **only** in scope. `UserIDSales` is SELECTed by every one of them and **rendered by none** — it appears only on the detail page. (An earlier revision said View Request "displays" it.) The **CEO** queue (235) filters on header status = 2 with **no user scoping**; the **Close** queue (256) on header status = 4, likewise unscoped. Both need a real grant + Policy in the port. ### 4.3 Cancel and Close — VERIFIED 2026-07-21 Both rules are real; they simply live in the **button render conditions**, not in the POST handlers (which is why a handler-only reading of the source misses them). | Action | Status window | Who | |---|---|---| | **Cancel** → 8 | header status **1, 2 or 3** | the **creator**, via View Request — that screen is scoped to `complain.UserIDInput` (§4.2), so the only complaints a user can reach there are their own. The same button also renders on **View All**, which is unscoped — so anyone holding the View-All grant can cancel anyone's complaint. Outside the window a "Cannot Cancel" placeholder is shown | | **Close** → 6, plus **Back to Review** → 1 and **Back to Approval CEO** → 2 | header status **4** only | **whoever holds the menu grant for menu 256.** The Close queue applies no user scoping at all, so this is purely a `rolemenu` question — exactly the legacy's intent | ⚠️ **On the two "Read Only" detail screens the Cancel button is commented out, but the POST handler is still live** — a forged or replayed request still cancels. That is a defect (§7), not a permission rule: in the port, hiding a button is never a control. **Port guidance:** implement Cancel as `creator OR view-all grant`, gated on status ∈ {1,2,3}, enforced in a Policy; implement Close/send-back as a pure menu-grant capability gated on status 4. Both need the server-side check the legacy never had. **Unresolved:** which roles get which of the 29 leaves (§9 Q8). --- ## 5. Data Model — VERIFIED against dev DB, 2026-07-21 Six data tables + four lookups, all `ID int auto_increment` PK + manual `IsDeleted`, `$timestamps = false`. **Every one of the six data tables has zero soft-deleted rows.** ### 5.0 Header + lines + a per-department matrix + two history systems ``` complain (header, 34) ├── complainassignment (197) ← header history └── complaindetails (35, the product lines) ├── complaindetailsassignment (785) ← line history, carries DepartmentID └── complaindetailsaction (210 = 35 × 6) ← ONE ROW PER (line × department) └── complaindetailsactionassignment (982) ← history of an action row ``` **`complaindetailsaction` is the real state machine.** Its `ComplainDetailsStatusID` is the per-department state; `complaindetails.ComplainDetailsStatusID` and `complain.ComplainStatusID` are **roll-ups** derived from it (§6.3). ### 5.1 `complain` — the header (34 rows) — model `App\Models\Complain` | Column | Type | Notes | |---|---|---| | `ID` | int AI | PK | | `IsDeleted` | int | 0 on all 34 rows | | `ComplainStatusID` | int NULL | FK → `complainstatus` | | `ComplainTypeID` | int NULL | FK → `complaintype` — where "Returns" lives | | `ComplainReasonID` | int NULL | FK → `complainreason` — ⚠️ **`0` on all 34 rows, §5.8 #1** | | `DivisionID` | int NULL | FK → `division` — **derived from the company** on the main create form | | `CompanyID` / `CompanyCPID` | int NULL | FK → `company` / `companycp` | | `ComplainDate` | datetime | the customer's complaint date — live range **2019-04-16 … 2024-03-25** | | `InputDate` | datetime | server clock at filing (`Asia/Jakarta`) — distinct from `ComplainDate` | | `UserIDInput` / `UserIDSales` | int NULL | creator / salesperson — differ on **1 of 34** | | `CompanyAddress` | varchar(500) | ⚠️ AJAX-prefilled from the company but **user-editable**; a frozen snapshot | | `Telp` | varchar(200) | ⚠️ likewise | | `UploadName/Type/Size/Content` | varchar / longblob | legacy attachment quad — **unused in live data, §5.8 #5** | | `RemarkCEO` | varchar(500) | written **only** on CEO reject; set on 1 row | | `CountAssignFromCEO` | int | ⚠️ **write-only** — set to the number of rows the CEO ticked, and **read nowhere** in the entire legacy codebase. Max 12 live | **FKs enforced on dev** (all RESTRICT): `ComplainStatusID`, `ComplainTypeID`, `ComplainReasonID`, `DivisionID`, `CompanyID`, `CompanyCPID`, `UserIDInput`, `UserIDSales`. ### 5.2 `complaindetails` — the product lines (35 rows) 34 headers, 35 lines — live complaints are almost always single-line, but the schema, the menus and the prototype are all multi-line. Do not collapse it. | Column | Type | Notes | |---|---|---| | `ComplainID` | int NULL | FK → `complain` | | `ComplainDetailsStatusID` | int NULL | FK → `complaindetailsstatus` — a roll-up (§6.3) | | `PrincipalID` + `PrincipalName` | int + varchar(400) | FK → `principal`, **plus a denormalised snapshot of the dropdown label** | | `BarangID` + `ProductName` | int + varchar(400) | FK → `barang`, **plus** a name snapshot | | `ApplicationID` | int NULL | FK → `application` | | `QuantityPacking` | decimal(20,5) | | | `SatuanPackingID` | int NULL | ⚠️ FK → **`quotationpack`**, NOT `satuan` — §5.8 #2 | | `BarangListID` | varchar(200) | ⚠️ **free-text lot number, not a foreign key** — §5.8 #3 | | `ComplainQuantity` | decimal(20,5) | ⚠️ **uncapped** — §5.8 #4 | | `SatuanComplainQuantityID` | int NULL | FK → `satuan` | | `UnitPriceUSD` / `UnitPriceIDR` / `USDRate` | decimal(20,10) | ⚠️ **10 decimal places** | | `SatuanUnitPriceUSD` | int NULL | FK → `satuan` | | `TotalValueUSD` / `TotalValueIDR` | decimal(20,10) | stored, not recomputed on read | | `SJNo` / `SJDate` / `InvoiceNo` / `InvoiceDate` | varchar(200) / datetime | the paper trail | | `BankName` / `BankAccountName` / `BankAccountNumber` | varchar(200) | refund destination — free text, **no FK to `bank`**; required by the legacy only when type = 4 (Refund) | | `TotalRefund` | decimal(20,5) | ⚠️ 5 decimals, unlike the prices | | `IsPaid` / `PaidDate` | int / datetime | ⚠️ legacy always writes `IsPaid = 1` — §7 | | `ComplainDesc` / `CustomerExpectation` / `ReporterRequestExpectation` | varchar(500) ×3 | the narrative core | **FKs enforced on dev:** `ComplainID`, `ComplainDetailsStatusID`, `PrincipalID`, `BarangID`, `ApplicationID`, `SatuanPackingID` (→ `quotationpack`), `SatuanComplainQuantityID` (→ `satuan`), `SatuanUnitPriceUSD` (→ `satuan`). **No 0-sentinels and no NULLs** — this table is clean. ### 5.3–5.6 The history and action tables - **`complainassignment`** (197) — `ComplainID`, `ComplainStatusID`, `Tanggal`, `UserID`, `Comment` (**text**). The house `assignment` pattern. ~5.8 rows per header. - **`complaindetailsassignment`** (785) — adds `ComplainDetailsID`, **`DepartmentID`**, and **five** remark columns: `ReviewRemark`, `AssignRemark`, `DoneRemark`, `FeedbackRemark`, `Remark`. **One column per lifecycle stage** — §6.2 says which stage writes which. `DepartmentID` distribution: `0`×86, `1`×129, `2`×125, `5`×115, `6`×109, `8`×109, `11`×112. - **`complaindetailsaction`** (210) — the (line × department) matrix. Same four stage remarks, plus the legacy upload quad, `Tanggal`, `UserID`. **Exactly 35 × 6, no gaps** — because the six rows are created eagerly at complaint creation (§6.1). - **`complaindetailsactionassignment`** (982) — history of an action row. ⚠️ **`DepartmentID` is `0` on all 982 rows** — dead weight that still violates its own FK. **Mechanism (verified):** the column exists in the table, but it is **not in the INSERT column list of any legacy write path**, so every row takes the DB default `0`. The department of such a row is knowable only transitively, via `ComplainDetailsActionID → complaindetailsaction. DepartmentID` — and on a repeat submit even that FK is left NULL, making those rows unattributable. **Port rule:** either write a real code or drop the column from the model's concerns; never resolve it through `Department` (§5.9). ### 5.7 Lookups - **`complainstatus`** (9) and **`complaindetailsstatus`** (9) — **identical vocabularies**: | ID | Name | | ID | Name | |---|---|---|---|---| | 1 | Request | | 6 | Close | | 2 | Review | | 7 | Reject by CEO | | 3 | Assign | | 8 | Cancel | | 4 | Done | | 9 | Re-Review ⚠️ **no workflow writer — but present in live data** | | 5 | Feedback ⚠️ **never reached in live data** | | | | ⚠️ Both column names are **`StatusName`** (not `ComplainStatusName` / `…DetailsStatusName`). Neither 5 nor 9 has a workflow screen; both are reachable only via the admin status-override (§6.7). The difference between them is empirical, not structural: **5 has never been used (0 rows anywhere, `FeedbackRemark` non-empty on 0 of 785), while 9 has** (1 header, 1 line, 11 action rows). Do not model either as dead; model 5 as unused and 9 as live-but-orphaned. Live header distribution: **1**×13, **2**×3, **3**×5, **4**×5, **6**×2, **7**×1, **8**×4, **9**×1. Lines match except **2**×4 — header and line status can diverge. - **`complaintype`** (6 rows, 1 soft-deleted) — **where "Returns" lives**: | ID | Name | Live headers | Note | |---|---|---|---| | 1 | Complaint | 25 | | | 2 | Retur Barang *(goods return)* | 2 | | | 3 | Tukar Barang *(goods exchange)* | 1 | | | 4 | **Refund** | 5 | ⚠️ a **mode switch** — see §6.5 | | 5 | Others | 1 | | | 6 | ~~www~~ | — | `IsDeleted=1`, junk | - **`complainreason`** (6) — Overpayment, Purchased Wrong Item, Quality Problem, Quantity Problem, Packaging Problem, Received Wrong Item. **Referenced by zero live records** despite the legacy form requiring it (§5.8 #1). No `ID = 0`; min ID is 1. ### 5.8 ⚠️ Schema traps 1. **`ComplainReasonID = 0` on 100% of headers — and the legacy says that should be impossible.** No `complainreason.ID = 0` exists, yet dev enforces the FK with RESTRICT, so those 34 rows violate their own constraint and a **new** insert copying the sentinel dies with `SQLSTATE[23000]`. Prod probably lacks the FK — a dev-only failure that looks mysterious. ⚠️ The legacy create screen **does** render a reason dropdown from `complainreason` and **does** include it in the submit guard (and PHP's `empty('0')` is true, so the "Select…" placeholder is rejected). So the existing rows predate the field or came from an older revision. **Resolution: collect it, write a real FK, never `0`.** 2. **`SatuanPackingID` is not a `satuan`** — it is FK'd to **`quotationpack`** (`ID`, `QuotationPackName` — bag/box/drum/kg), while `SatuanComplainQuantityID` and `SatuanUnitPriceUSD` really do point at `satuan` (`SatuanName`). The legacy **create** form agrees with the FK (it populates that field from `quotationpack`), but every legacy **display** query joins it against `satuan` — so the packing unit shown on the assignment screens and in the PDF is whichever `satuan` row happens to share an ID. **Port the `quotationpack` relation; do not port the join.** ⚠️ The dormant mail Blade repeats the same error. Live values used: 1, 2, 3, 4, 5, 14, 15, 18. Same lesson as [[company-product-pivots]]. 3. **`BarangListID` is a free-text lot number, not a foreign key.** It was once fed by a lot picker (`getlotnumber.php`, returning `baranglists.ID`), but **every call site is commented out** and the form is now a plain text input whose typed string is written into the column. Expect a mix of genuine ids and free text. Model it as a string; do not add a relation. 4. **Complaint quantity is uncapped.** `getmaxquantity.php` looks like a cap but (a) is never called — its JS wrapper has no trigger and its target element does not exist — and (b) reads **warehouse stock for a lot**, not the delivered or invoiced quantity. There is no check anywhere that `ComplainQuantity ≤ QuantityPacking`. **Whether to add one is a user decision (§9 Q12), not a port.** 5. **The upload quad is unused in live data** (0 of 34, 0 of 210) even though the legacy create form writes it. **Good news:** no legacy-BLOB fallback is needed — go straight to the `file_uploads` convention (CLAUDE.md), and ask the user for the `{menu_name}` (§9 Q6). ⚠️ The legacy stores bytes **in the database** as a BLOB; the project convention is bytes on a private disk, metadata in columns, written after commit. 6. **A hidden menu group orphans its visible children.** Menu **243** "Feedback" is `IsShow=0` while its six children are `IsShow=1`. `Menu::navTreeForRole()` filters `IsShow=1` across the **whole** tree including group rows, then builds children from surviving parents — so the six Feedback screens cannot appear in the sidebar as seeded. Menu **232** is likewise a dead `IsShow=0` duplicate of **233**. 7. **The header freezes `CompanyAddress` and `Telp`.** Both are AJAX-prefilled from the company but remain editable, so the stored value is a deliberate snapshot. Display it; do not join `company` to "correct" it ([[userid-sales-snapshot]]). 8. **The models are auto-generated — now INSPECTED (2026-07-21), not merely "unverified".** Attribute style (`#[Table]`/`#[Fillable]`), so `protected $table` greps find nothing. **Do not regenerate.** What the inspection actually found: - ✅ **`ComplainDetails::satuanPacking()` is CORRECT** — it already targets `QuotationPack::class` (`ComplainDetails.php:86`), matching the real FK. ⚠️ Earlier revisions of this PRD, the README and the BUILD PROMPT all warned that this relation "encodes trap #2". **It does not.** The wrong `satuan` join lives in the legacy SQL and in the dormant mail Blade — not in the model. - 🔴 **Three `department()` relations DO encode §5.9** — `ComplainDetailsAssignment.php:68`, `ComplainDetailsAction.php:68`, `ComplainDetailsActionAssignment.php:74`, each `belongsTo(Department::class, 'DepartmentID', 'ID')`. On 1,215 live rows that relation resolves to nothing. **Remove or rename all three**; resolve the code through a module constant instead. - 🔴 **No datetime casts and no decimal casts anywhere.** `casts()` covers `IsDeleted` + the FK ints only. Money/quantity columns therefore arrive as **strings**, and `IsPaid` is not cast to bool. Adding a naive `'datetime'` cast is actively unsafe — see trap #9. 9. **⚠️ MySQL zero-dates live in `complaindetails` — and they fail SILENTLY, not loudly.** Measured 2026-07-21: `SJDate` **×4**, `InvoiceDate` **×4**, `PaidDate` **×11** hold `0000-00-00 00:00:00`. **Not one datetime column in the family is ever NULL** — the legacy writes the zero-date instead of NULL. Consequences, all tested rather than assumed: - `Carbon::parse('0000-00-00 00:00:00')` **does not throw** — it returns **`-0001-11-30`**. So a `'datetime'` cast produces a plausible-looking Carbon object holding year −1. This passes every smoke test and corrupts only the specific records that carry it. - The dev connection's `sql_mode` is `ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION` — i.e. **`NO_ZERO_DATE` is absent**, so dev currently *accepts* these writes. MySQL 8.4's stock default does include it, so this is server configuration, not a guarantee. An edit-and-save round trip that echoes the value back is one `sql_mode` change from failing. - **Port rule:** treat the zero-date as "no date" at the boundary (cast to `null`), never write it back, and never add a bare `'datetime'` cast to `SJDate`/`InvoiceDate`/`PaidDate` without that guard. This is the date-shaped twin of the 0-vs-NULL rule in trap #1. 10. **🔴 The CEO assign path is BROKEN in the supplied revision — do not port it as written.** `listcomplaindetailsceo.php` matches ticked departments with an equality test between the posted detail-id array and the ticked-checkbox indexes, where the detail-id array comes from a POST field named **`CID`**. But that field is emitted **only inside the commented-out old renderer** (`listcomplaindetailsviewceo.php:399`); the live renderer (`:467-792`) never emits it. So on the code as shipped: **no `complaindetailsaction` row, no `complaindetails` row and no per-line history is written, and no assign email is sent** — yet the header **still** advances to status 3 and `CountAssignFromCEO` is still set. A second regression rides along: the checkbox `value` counter is re-initialised **inside** the per-line loop (`:727`), so values restart at 0 on every line while the payload arrays stay flat — index aliasing across lines. **Live data proves it used to work** (65 action rows carry `AssignRemark`; 15 sit at status 3), so this is a regression introduced by the renderer rewrite, not the design. ➜ **Port the INTENT documented in §6.2/§6.4 — one write per (line × department) — never the shipped matcher.** And do not conclude from reading this file that assignment "doesn't write action rows". 11. **`header.php` — FOUND and read, 2026-07-21.** It was long recorded as "deliberately not supplied". A copy is present in a sibling module's curated legacy folder (`MenuBudgetAndTargets/Just An Example, Do not Link/Additional File/header.php`, git-ignored like ours). Treating it as the same app-wide file is justified: the three helpers present in **both** curated folders (`getcompanydetail`, `getcompanydivision`, `getpmapproval`) are **byte-identical**, so these folders are extracts of one flat legacy directory. Two verified facts, both load-bearing: - **The `rolemenu` authorization check is computed and discarded.** It joins `menu` + `rolemenu` for the user's `RoleID` and sets `$cek = false/true` — and **`$cek` is never read again** (its only three occurrences are a comment, the init, and the assignment). **Any authenticated legacy user could open any screen regardless of grants.** - **The authentication redirect does not halt.** The anonymous path issues a PHP `Location` redirect to the login page, and the file contains **no `exit`/`die` at all** (count: 0), so execution continues into whatever included it. ➜ This finally explains §4's "the legacy has no authorization to port": it is not that the gate lived somewhere unseen — **the gate was written and then ignored.** The port builds authorization fresh on the `rolemenu` contract (CLAUDE.md), and this is the reason it must. ### 5.9 `DepartmentID` — the lying FK Every `DepartmentID` column carries a legacy **department code**, now confirmed three ways in the source (the create screen's six-iteration loop; each queue's hardcoded constant; the label switch in the write handlers): | Code | Department | | Code | Department | |---|---|---|---|---| | 1 | SM | | 6 | Finance | | 2 | PM | | 8 | CS | | 5 | Logistic | | 11 | Accounting | The FK points at `department.ID`, whose rows run **1–10 with no 11**. Measured violation count on dev: **1,215 rows** — 198 in `complaindetailsassignment` (86 at `0`, 112 at `11`), 35 in `complaindetailsaction` (at `11`), all 982 of `complaindetailsactionassignment` (at `0`). Treat the six codes as a **module constant**; never resolve them through `Department` ([[dev-db-fk-drift]]). --- ## 6. The Workflow — VERIFIED from the legacy source, 2026-07-21 ### 6.1 Creation One transaction. Inserts, in order: `complain` (1) → `complainassignment` (1) → then per product line: `complaindetails` (1) → `complaindetailsassignment` (1) → **`complaindetailsaction` (6, one per department code 1, 2, 5, 6, 8, 11)** → `complaindetailsactionassignment` (6). **Total = 2 + 14N rows for N lines.** Everything starts at **status 1 (Request)**; the header history comment is a fixed string. **The six action rows are created eagerly — this is the module's defining fact.** No department is "routed to" later; all six are live from the moment the complaint exists, which is exactly what populates all six Review queues. **No email is sent on create** (the only mail call is commented out, and it is a copy-paste of a Quotation function). **Three creation rules an earlier revision omitted, all verified in `createcomplain.php`:** 1. **Who you may file against is scoped.** The Company dropdown is not "all companies" — it is restricted to companies whose sales owner is the actor **or anyone in the actor's full recursive `users.HeadID` subtree** (`:1334-1339`, using the same recursion helper as View Head Dept). So a create-time access rule exists and must be ported alongside the list scopes. The option label appends the owner's name when the company belongs to a subordinate. 2. **The visible "Sales" dropdown is discarded.** The form renders a Sales `