# Product requirements — edi_system (SAWIS Label System) Evidence date: 2026-10-16. Source repository: `git@bitbucket.org:overdrive/edi_system.git`, `master` at `75a67b5`. Provenance labels used below: **Documented**, **Implemented**, **Inferred**, **Planned**, **Unknown**. Nothing in this document is a new product decision. Where the code and the README disagree, both are stated. ## Product purpose **Implemented:** A logged-in user can submit wine-label PDF files, maintain label details (trademark, alcohol percentage, bottle size, year, warning, CC list, SAWIS agent), and move a submission through review. Reviewers can approve, approve with provisions, reject, or record a contest of a rejection. Approval and rejection generate a PDF certificate. Notification text is stored in `sent_queue` and can be sent manually. **Documented** in `README.md`: the project is an "Admin Overdrive Starter" whose features are authentication, authorization, user registration, and menu management that can create a controller and a view. **Unknown / Requires Confirmation:** why the repository is named `edi_system`. No EDI workflow was found. ## Users and roles Roles are integers on `users.role`. Names below come from comments and `switch` labels in `Welcome.php`, `Labels.php`, `Home.php`, and `LabelUserGroups.php`. Only role 1 is inserted by a seeder (`app/Database/Seeds/UserRole.php`). | Role id | Name used in code | How a user gets it | What the code then does | |---|---|---|---| | 1 | Developer / Dev Admin | Seed user; self-registration; SAWIS group `Admin Dev` | Sees all labels, admin label form, status and template screens, developer block in the layout | | 2 | SAWIS Staff | SAWIS groups `Sawis User` and `Sawis Inspector` | Sees all labels and the admin form. Included in meeting-group user queries | | 3 | SAWIS Board | Not assigned by the SAWIS switch or the seeder | Label list is limited to `label.status = 7`. Admin form. Dashboard uses the "all labels" counts | | 5 | Designer | SAWIS group `Designer` | Label list and form limited to `user_designer_id` = that user. Member form (`labelFormMember`). SAWIS navbar items hidden | | 6 | Members / Farmers (comment only) | Not assigned by the SAWIS switch or the seeder | Allowed to call create and resubmit. `Labels::index()` and `labelForm()` have no branch for this role | | 7 | SAWIS Admin | SAWIS group `Sawis Admin` | Same label visibility as Developer. Meeting-group admin actions that check role 1 alone are not included | | 0 | Unmatched SAWIS group | `default` of the SAWIS group switch | Comment says the person cannot use the system. The login method can still open a session if the user row exists | | 4 | — | Not referenced | No branch found | **Requires confirmation:** how roles 3 and 6 are granted in production, and whether role 0 users are meant to stay logged out. ## Main workflows ### Sign in 1. `GET /` shows `common/login` when `isLoggedIn` is not true. If the session is already logged in, the user is sent to `home`. 2. `POST GetLogin` (`Welcome::index`) requires `inputEmail`. 3. The controller opens database group `sawisAPIQa` and reads `sysUser` joined to an active `sysSecurityGroup` by email. 4. If that user exists, the newest `sysPassword` row is hashed as SHA-256 of `salt + password` and compared. 5. If the hash matches and the email is already in local `users`, the method returns true. The caller then opens a session **without** `password_verify` (that call is commented out). 6. If the hash matches and the email is not local, a local user is created with the mapped role and a password hash of the submitted password. The method returns the login view. That return value is truthy, so the caller still loads the new user and opens a session. 7. If SAWIS has no matching user, or the hash does not match, or the SAWIS connection throws, local login runs: `password_verify` against `users.password`. 8. Session keys set: `username`, `role`, `isLoggedIn`, `userID`, `labelID` (empty string). **Implemented** in `Welcome.php`. Failure flash text is "Your ID or Password is Wrong" when the local user is missing or the password does not verify. A SAWIS connection exception is also echoed to the response. ### Self-registration `GET register` / `POST register`. Email must be unique in `users.username`. Password is required and must match confirmation. Full name, email, and password are stored. Role is hardcoded to `1`. Success flash asks the user to log in. The handler returns the login view directly (it does not redirect). **Implemented.** The README lists registration as a feature. Assigning the Developer role is **Implemented** and **Requires confirmation** as a business rule. ### Open a screen `isLoggedIn` runs on every request except `/`, `getLogin`, `register`, `organisations`, `organisations/*`, `tracking-pixel`, `show404`, and `404`. A missing session redirects to `/`. `isGranted` runs except on those paths plus `blocked`, `home`, `Welcome/*`, and `chatGPT/*`. It loads `user_menu` by the first URL segment. No menu row redirects to `/`. A menu row the role cannot access redirects to `blocked`. **Implemented** in `app/Config/Filters.php` and the two filter classes. CSRF and honeypot aliases exist and are commented out of the global filters. Auto-routing is on (`setAutoRoute(true)`), so controller methods are reachable as URLs even when `Routes.php` does not list them, subject to the filters above. ### Submit a label Roles 1, 7, 5, and 6 may call `Labels::createLabel`. Other roles receive "You are not allowed to do this!". The request must include at least one uploaded file. Only extension `pdf` is kept. The file is moved under `uploads/` with a random numeric prefix. Imagick reads the PDF at 300 dpi and writes a JPEG of the first page beside it. A conversion failure sets a warning that the PDF may be password protected. `LabelModel::createLabelFiles` inserts a `label` row with `user_designer_id` = posted user id, `status` = `1`, `cc_list` from the form, and audit columns. It then inserts `file` rows. File field name maps to `file.type`: supporting document 2, front 3, back 4, neck 5, anything else 1. A `label_log` row records action `create`. Tamper checking is commented out on create. It is active on update, resubmit, delete, approve, reject, contest, file approval, and manual status change. The check dies with JSON "Invalid form data" unless posted `userID` and `labelID` equal the session. **Implemented.** Whether the insert succeeds on a database built only from the migrations is **Unknown** — see FR-012. ### Update label details `updateLabelData` writes trademark, alcohol percentage, warning, bottle size, year, CC list, and, when posted, SAWIS agent (`agent_id`). It builds a note from fields prefixed `prev-` that differ, inserts `label_note` and `label_log` action `update`. The member form shows agent and approver as read-only text. The admin form uses a user dropdown for the agent. Barcode is present on the table and commented out of the update array. ### Resubmit a file Same role gate and PDF rules as create. New `file` row uses the posted type and `file_version` = selected version + 1. Previous files are not marked inactive (a TODO in `LabelModel::resubmitLabelFile` says that was left undecided). Label `status` is not changed by resubmit. ### Review a label `approveLabel` sets status `5`, or `6` when `labelHasProvisions` is `1`. It stores the approver, a provisions string, a note, and a log action `approve`. It queues an email whose template `type` is that status id. The controller then builds an mPDF certificate. `rejectLabel` sets status `4`, note, log action `reject`, and queues a template of type `4`. It also builds the certificate. `contestRejectedlabel` sets status `7`, stores a note, and logs action `reject` with submission text "Label rejection contested". It does not queue email. `labelFileApprovalStatus` sets `file.file_approved` from the posted flag and logs approve or reject for that file. It does not change label status. `manualLabelSubmissionStatusUpdate` sets `label.status` to the posted status id, writes a note, and logs action `update`. No role check was found inside this method beyond the menu filter and tamper check. `reassignToMeetingGroup` batch-updates `meeting_group_id`. It does not write a label log. The list UI shows the control for roles 1 and 7. ### Approval certificate `labelApprovalPDF` loads the label, loads files with `file_approved = 1` and `type != 6`, and renders an mPDF document. Status 5 checks "approved", status 4 checks "rejected", status 6 checks "approved with provision". The PDF is saved under `uploads/approvalcerts/` and inserted as `file.type` 6 with the next version and `file_approved` 1. ### Email queue `Email::sendEmailNotification` loads `label_email_template` where `type` equals the status passed in. It replaces `{userName}`, `{LabelNumber}`, `{Note}`, and `{labelURL}`. It appends a SAWIS logo image and a 1×1 pixel whose URL is `tracking-pixel?code=sub{labelId}trId{uniqid}`. `sendMail()` is commented out. A `sent_queue` row is inserted with `processed` 0. `Labels::sendEmailNow` loads that row, calls `sendMail`, sets `processed` 1, and writes a note and log. `sendMail` uses CodeIgniter email from `admin@overdriveqa.co.za`, display name "Admin Overdrive". `Labels::rescheduleEmail` updates `send_date` and writes a note. No reader of `send_date` was found in this repository. `GET tracking-pixel` is public. If the viewer is **not** logged in, `EmailPixelTrackingModel` parses the code and sets `sent_queue.processed` to `2` for that label id and tracking code. The response is a 1×1 PNG. If the viewer is logged in, the pixel is returned and the row is not updated. ### Dashboard `Home::index` counts labels. Roles 1, 7, 2, and 3 count every label. Role 5 counts labels for that designer. Other roles produce a `user_designer_id IS NULL` count (the `$where` default). Counts: - Pending approval: status in (1, 2, 3) - Approved: status 5 - Approved with provision: status 6 - Rejected: status 4 - Contested: status 7 The same page still queries car `app_session` totals. `fixcredit` is forced to 0; the percentage formula is commented out. ## Functional requirements ### FR-001 — SAWIS-first login Requirement: A person can sign in with the email and password held in SAWIS Online. If that check fails, the local password is used. Who: Any visitor of `GET /`. Trigger: `POST GetLogin`. Inputs: `inputEmail`, `inputPassword`. Behaviour: As in [Sign in](#sign-in). Local passwords are `password_hash` / `password_verify`. SAWIS passwords are SHA-256 of salt plus password. A successful SAWIS match does not re-check the local hash. Outputs: Session and redirect to `home`, or to `redirectURL` when that session key exists. Error flash otherwise. Validation: Email is required. Password rules are not declared on the login validator. Error conditions: SAWIS connection exception is echoed and then local login runs. Missing local user after a SAWIS miss shows the wrong-id flash. Dependencies: MySQL group `sawisAPIQa`; table `users`. Evidence: `app/Controllers/Welcome.php`, `app/Config/Database.php`. Implementation: Implemented. Confidence: High. ### FR-002 — Local registration Requirement: A visitor can create a local user. Who: Unauthenticated visitor (`register` is filter-exempt). Inputs: full name, email, password, password confirmation. Behaviour: Unique email, matching passwords, role 1, then the login view. Evidence: `Welcome::registration`, `UserModel::createUser` (called from the controller). Implementation: Implemented. Confidence: High. Role 1 on a public form **Requires confirmation**. ### FR-003 — Session and menu authorization Requirement: Pages other than the public exceptions require a session. Pages other than the grant exceptions require a menu row for the first URL segment and a grant for the session role. Evidence: `app/Config/Filters.php`, `app/Filters/Authentication.php`, `app/Filters/Authorization.php`, `UserModel::checkUserAccess`. Implementation: Implemented. Confidence: High. ### FR-004 — Role-specific label lists Requirement: Designers see only their labels. Developers, SAWIS staff, and SAWIS admins see all labels. Board users see labels with status 7. Each list joins designer, approver, and status name. Evidence: `Labels::index`, `app/Views/label/labelList.php`. Implementation: Implemented for roles 1, 2, 3, 5, and 7. Roles 4 and 6 fall through with no `$data` and will error if they open the list. Confidence: High for the roles that have branches. Medium for production role assignment. ### FR-005 — Two label forms Requirement: Roles 1, 7, 2, and 3 receive the admin form. Role 5 receives the member form, and only when the label's designer is that user. Evidence: `Labels::labelForm`, `labelForm.php`, `labelFormMember.php`, `labelFormTab.php`, `labelFormTabAdmin.php`. Implementation: Implemented. Confidence: High. ### FR-006 — PDF submission Requirement: An allowed role can upload one or more PDFs and create a label in status 1, with file types for supporting document, front, back, neck, or a default label type. Evidence: `Labels::createLabel`, `LabelModel::createLabelFiles`, `contentTypeDisplay()` in `app/Common.php`. Implementation: Implemented. Confidence: High for the code path. Medium for database insert success (FR-012). ### FR-007 — Detail update and history Requirement: Changing trademark, alcohol %, warning, bottle size, year, CC list, or agent writes the label, a note describing each change, and a log row. Evidence: `LabelModel::updateLabelData`. Implementation: Implemented. Confidence: High. ### FR-008 — File version resubmit Requirement: An allowed role can add a new version of a chosen file type. The label status is left unchanged. Older rows stay active. Evidence: `Labels::resubmitLabelFile`, `LabelModel::resubmitLabelFile`. Implementation: Implemented. Leaving old files active is explicit in a code comment. Confidence: High. ### FR-009 — Approve, reject, contest Requirement: Approval sets status 5, or 6 when provisions are flagged, and queues mail. Rejection sets status 4 and queues mail. Contest sets status 7 and does not queue mail. Approve and reject also generate a certificate PDF. Evidence: `LabelModel::approveLabel`, `rejectLabel`, `contestRejectedlabel`; `Labels::approveLabel`, `rejectLabel`, `labelApprovalPDF`. Implementation: Implemented. Confidence: High for the code. The numeric ids matching names in `label_status` is **Unknown / Requires Confirmation**. ### FR-010 — Per-file approval Requirement: A reviewer can mark a single file approved or rejected. That flag is independent of label status. Certificate generation includes only files with `file_approved = 1`, excluding type 6. Evidence: `LabelModel::labelFileApprovalStatus`, `Labels::labelApprovalPDF`. Implementation: Implemented. Confidence: High. ### FR-011 — Meeting groups Requirement: An operator can create a meeting group (name, active, type), rename it, toggle active, and assign users in roles 1, 2, 3, and 7 to groups. Admins can point many labels at one active group. Evidence: `LabelUserGroups.php`, `LabelUserGroupsModel.php`, migration `MeetingGroups`, `LabelModel::reassignToMeetingGroup`. Implementation: Implemented. Confidence: High. The business meaning of group `type` is **Unknown** (free text column, no enum in code). ### FR-012 — Label record shape Requirement: A label stores designer, member, approver, cultivar code, wine type, WSB number, barcode, production area, year, warning, content type, alcohol percentage, bottle size, status, active flag, agent, trademark, provisions, meeting group, and CC list, plus audit columns. Evidence: migration `2023-10-16-090428_Label.php` and later `addColumn` migrations through 2024-02-09. Alcohol percentage is changed to `DECIMAL(10,2)` by `ModifyAlcoholPercentage`. The create insert does not populate member, approver, cultivar, wine type, WSB number, barcode, production area, year, warning, type, alcohol, or bottle size. Those original columns are `NOT NULL` without defaults. Implementation: Schema implemented in migrations. Create path **conflicts** with that schema unless the live database was changed outside this repository. Confidence: High that the conflict exists in source. Live schema is **Unknown**. ### FR-013 — Configurable statuses with protected names Requirement: Status rows have name, priority, an integer `status` flag, a group, and a parent. The status screen treats these names as read-only and non-deletable: Submitted, Pending, Rejected, Approved, Contested, Resubmit. Evidence: `app/Views/label/labelStatus.php`, `LabelStatus` controller and model, migrations `LabelStatus`, `AddFieldsToLabelStatus`, `AddGroupFieldToLabelStatus`, `AddParentChildFieldToLabelStatus`. No seeder inserts those names. Workflow code does not look up status by name. It writes ids. Implementation: Admin UI implemented. Agreement between names and ids is **Unknown**. Confidence: High for the UI list. Low for production ids. ### FR-014 — Email templates and queue Requirement: Templates are stored with a numeric `type`, body, active flag, and a `sent` integer. Sending looks up the template by type, merges placeholders, and inserts `sent_queue` (`processed` 0). A user can send one queued row immediately or change `send_date`. A public pixel can set `processed` to 2. Evidence: `Email.php`, `LabelEmailTemplates.php`, migrations `LabelEmailTemplate` and `SentQeue`, `Labels::sendEmailNow`, `rescheduleEmail`, `EmailPixelTracking`. Implementation: Implemented, except automatic dispatch. Confidence: High. ### FR-015 — Dashboard counts Requirement: Home shows submission, pending, approved, approved-with-provision, rejected, and contested counts, scoped by role as described above. Evidence: `Home.php`, `app/Views/common/home.php`. Implementation: Implemented, using fixed status ids that do not match `statusDisplay()`. Confidence: High for the queries. Medium for whether "pending" (ids 1, 2, 3) is what operators expect. ### FR-016 — User and menu administration Requirement: A granted user can list users, create, update, and delete users and roles, and toggle menu, category, and submenu grants. Menu management can create a category, menu, and submenu and write a blank controller and view onto disk. Evidence: `Users.php`, `Developers/MenuManagement.php`, seeds `UserMenu`, `UserAccess`, `UserMenuCategory`. Seeded menus: Dashboard (`home`), Users, Menu Management, CRUD Generator, UI Testing (`edit`), UI Testing Log (`list`), Data Files (`files`), Media Files (`media`). All seeded access rows use role id 1. Implementation: Implemented. Confidence: High. Production menu rows beyond the seed are **Unknown**. ### FR-017 — Organisations API Requirement: `organisations` is a resource route and is exempt from session filters. `POST` login with JSON `name` and `api_key` returns a JWT (HS256, expiry about 6000 seconds) when an organisation matches. Other actions require a valid bearer token. The comment in `Routes.php` says `POST /organisations/login`. Evidence: `Organisations.php`, `JwtLibrary.php`, `Routes.php` resource and comments, `OrganisationModel` allowed fields. Implementation: Implemented. JWT secret defaults to the string `secret` inside `JwtLibrary`. Login is not declared as its own route; with auto-routing, `organisations/login` maps to `Organisations::login`. Confidence: High. ### FR-018 — Car session API and import Requirement: The tree includes a car record, cost categories A/B/C, tiers, and an `app_session` walked through steps (login, range, model, affordability, maintenance, recommend, apply, congratulations, fix my credit). Spreadsheet import writes cars and categories. The home dashboard reads session counts. Evidence: `Car.php`, `CarApi.php`, car migrations, `app/Views/car/`. This behaviour is **Implemented**. It is not described in the README. Whether it belongs to the SAWIS label product is **Unknown / Requires Confirmation**. Confidence: High that the code exists. Low that it is a current label requirement. ### FR-019 — Master file import Requirement: Master-file screens list rows and import spreadsheets via PhpSpreadsheet (`importOverland`, `importModelliQ`) and can export CSV. Taste notes and history are truncated for display. Evidence: `MasterFile.php`, `MasterFileModel.php`, `MasterFileOverlandModel.php`. Implementation: Implemented. Product ownership **Requires confirmation**. Confidence: High that the code exists. ### FR-020 — ChatGPT page Requirement: `GET chatGPT` renders a page. `GET chatGPT/sent` posts the message to the OpenAI completions API and returns JSON. The grant filter skips `chatGPT/*`. The login filter does not, so a session is still required. Evidence: `ChatGPT.php`, `app/Helpers/chatgpt_helper.php`, `Filters.php`. Implementation: Implemented. The helper does not read `OPENAI_API_KEY` from `env`. A bearer token is hardcoded. The value is omitted here. Confidence: High. ## Integrations | System | Purpose | Direction | Trigger | Auth as found | Failure behaviour | |---|---|---|---|---|---| | SAWIS Online MySQL (`sawis_api` via `sawisAPIQa`) | Login and first-time local user create | Read, then local write | Login POST | Username and password in `Database.php` (values omitted) | Exception text echoed; method returns false; local login runs | | SMTP `smtp.overdriveqa.co.za:587` | Deliver a queued label email | Outbound | `sendEmailNow` only | SMTP user in `Email.php` (password omitted) | `sendMail` always calls `printDebugger`. No check of `$email->send()` return value was found | | OpenAI `v1/completions` model `text-davinci-003` | ChatGPT page | Outbound | `chatGPT/sent` | Hardcoded bearer (omitted) | cURL error string returned to the client. SSL peer verification disabled | | Slack webhook | Deploy start, finish, and deny | Outbound | `public/deploy.php` | Webhook URL in that file (omitted) | cURL result ignored | | SAWIS Online websites | Navbar links (members, blocks, and other SAWIS screens) | Browser navigation away from this app | Menu click | None in this repo | Host chosen from `base_url`: localhost → `http://sawis.local`; URL containing `qa` → `https://qa.sawisonline.co.za`; otherwise `https://sawisonline.co.za` | | Imagick / Ghostscript | PDF first page to JPEG | Local process | Label upload | n/a | Warning flash; upload may still continue after the catch | | mPDF | Approval certificate | Local file | Approve or reject | n/a | Warning flash; certificate skipped | Salesforce and Africa's Talking are not live integrations. Their helpers do not perform the call. ## Important constraints - PHP 8.0 is the documented minimum (`README.md`, `composer.json`). - Web root must be `public/` (`README.md` virtual-host example uses `adminOverdrive.local`). - Sessions in the committed `env` use the database handler, cookie `ci_session`, match IP true, regenerate-destroy true, update interval 1440. `app/Config/App.php` still defaults to a file handler path, match IP false, expiration 7200, timezone `America/Chicago`, and an empty `baseURL`. The `env` file overrides those keys only when it is copied to `.env` and those lines stay uncommented. - CSRF protection is present and turned off. - Auto-routing is on, so a new public controller method is a URL. - Label workflow ids are hardcoded and are not seeded. - Secrets are stored in Git. This knowledge base does not repeat them. ## Planned behaviour No roadmap file exists. `README.md` points at a Jira board for "Development TODOS" and leaves the CRUD-script section empty. In-code TODOs that describe unfinished work: - `Labels::createLabel`: "Look in to tamperProtection breaking this function" (the call is commented out). - `LabelModel::resubmitLabelFile`: maybe mark the replaced file inactive; left undecided. - `checkPDFValidation` uses the literal path `path/to/your/pdf/file.pdf` and echoes text. It is not called by the upload path. - `Labels::test` is a manual Imagick experiment against a fixed approval-certificate path. These are **Planned** only in the sense that a developer left a note. They are not a committed schedule. ## Requirements discovered only from implementation The README does not mention labels, SAWIS, meeting groups, certificates, or the email queue. Those requirements above are **Implemented** behaviour, not a signed PRD that already lived in the repository.