# PRD — shipment_document_system Evidence date: 2026-10-04. Source: `git@bitbucket.org:overdrive/shipment_document_system.git`, `main` at `6f61b57` (2026-04-24, "Security Enhancements"). There is no prior PRD in Silicon Ark and no README in the source repository. Requirements below are recovered from controllers, services, views, entities, SQL scripts, and git history. Provenance is labelled. Secrets are not copied. `.env.example` names the variables. `appsettings.json` no longer contains credentials. ## Product purpose **Implemented.** The browser title is "RGBC - Shipment Document System". The header is "Logistics and Supply Chain". Users keep a shipment record (supplier, indent, vessel, port, container, dates, bill of entry) and attach checklist files. Files are stored in SharePoint under a folder named with the indent number. Users can also load many shipments from an Excel file and download the current list as Excel. **Inferred.** The audience is RGBC logistics staff who prepare an import shipment checklist before the shipment is submitted or cancelled. No audience statement was found in the repository. **Unknown / Requires Confirmation.** Legal name behind RGBC. Whether this system is internal-only. Production URL. Whether "submit" means the checklist is complete for another team (finance, warehouse, a forwarder) or only a status change inside this application. No outbound message is sent on submit. ## Users and roles `RoleEnum` in `rgbc_sds.services/Enums/RoleEnum.cs`: | Id | Enum | Session string | |---|---|---| | 1 | Admin | `ADMIN` | | 2 | Viewer | `VIEWER` | | 3 | Logistics | `LOGISTICS` | The id is stored on `mstUser.refUserGroupSDSID`. Login copies it into session keys `LoggedInUsername`, `LoggedInUserRole`, and `LoggedInUserId`. `mstUser` also has group columns for Superbowl, Forecast, Promotion, ScoreCard, FreeStock, and POR. Those columns are not read by this application. **Inferred:** `mstUser` is a shared user table. **Unknown / Requires Confirmation** which other systems own it. A role that is not 1, 2, or 3 hits the `default` branch in `AccountController.Login`. No session keys are set. The method still logs success and redirects to the shipment list, because the user object is non-null. See FR-001. There is no self-registration screen. ## Systems the product talks to 1. The operator's browser. 2. SQL Server, connection string from `RGBC_SDS_CONNECTION_STRING`. Database name is **Unknown**. 3. Microsoft identity platform, client-credentials token, authority `https://login.microsoftonline.com/{tenant}`. 4. Microsoft Graph, site id from configuration, drive path `/Shipment Checklist/{indent}/`. 5. Bitbucket Pipelines, SonarQube analysis on `main` only. No deploy step. 6. CDNs named in `_Layout.cshtml`: Font Awesome 4.1.0, jQuery 3.6.0, jQuery UI 1.12.1, jquery.inputmask 5.0.6. Windows / Active Directory is named in a TODO inside `ValidateUser` and by the `System.DirectoryServices` package reference. No directory call is implemented. `launchSettings.json` turns Windows authentication off for IIS Express. No email, queue, or background worker was found. ## Major workflows ### WF-1 — Sign in `GET /`, `GET account`, and `GET account/login` render `Views/Account/Login.cshtml`. The form posts to `account/login`. The page shows the RGBC logo and a Login button. It does not collect a username or password. On post, `AccountController` sets `model.Username = User.Identity.Name`. A local override `rgbc\maanie` is commented out and labelled LOCAL. The production line is labelled PRODUCTION. `UserService.ValidateUser` loads `mstUser` where `strDomainLogin` equals that name. It does not read `strPassword`. If no row matches, it returns `new UserModel()` rather than null. If the returned object is non-null (including the empty model), the controller switches on `refUserGroupSDSID`, writes session values for roles 1–3, logs "User logged in successfully.", and redirects to `shipment/shipmentlist`. If the method had returned null, `TempData["LoginMessage"]` would be "User could not be Authenticated. Please contact your System Administrator." The login view prints that text inside an alert styled `alert-success` with the heading "Success!". Exceptions log and add model error "An error occurred. Please try again." Provenance: **Implemented**. ### WF-2 — Open the shipment list `GET shipment/shipmentlist` loads every shipment joined to `sdsSupplier`, ordered as the query returns them (no `OrderBy` in `GetShipmentList`). Columns shown: row number, indent (link to detail), supplier name, requested by, date, status, cancel reason, edited by, last edit. Buttons, with no role check on this view: Filter, Clear, Add Shipment, Import, Export. Filter is `GET shipment/filtershipmentlist`. Search matches `indent_number.Contains`. Status dropdown ids map to `SAVED` (1), `SUBMITTED` (2), `CANCELLED` (3). The nav link text is rewritten by script to `Logout: {LoggedInUserName}`. ### WF-3 — Create a shipment `POST shipment/addnewshipment` builds an empty form. Supplier dropdown is active suppliers only (`IsActive == true`). Ports are hard-coded: 1 Cape Town, 2 Coega, 3 Durban, 4 P.E. ETA, container received, and redirect-to-SACD default to `DateTime.Now`. `POST shipment/addshipment` runs `ValidateScreen` with process `SAVE`. On success it inserts `sdsShipment` with `shipment_status = "SAVED"`, `created_by` from session, then uploads selected files and redirects to the detail page. TempData success: "Shipment Created Successfully". ### WF-4 — Update, submit, or cancel `GET shipment/shipmentdetails/{id}` loads the row, all suppliers (active and inactive), ports, SharePoint preview URLs, `CanSubmit`, and the signed-in role. `POST shipment/updateshipment` validates with process `UPDATE` (duplicate indent is not checked), writes the row, renames the SharePoint folder and files when the indent number changed, uploads replacements, and returns to the same detail page. `POST shipment/submitshipment` sets `shipment_status = "SUBMITTED"` and returns to the list. The button is shown only when `CanSubmit` is true and the status is not already `SUBMITTED`. `POST shipment/cancelshipment` requires a non-empty cancel reason, sets status `CANCELLED`, stores the reason, and returns to the list. `POST shipment/deletefile` clears one filename column and deletes `{indent}_{filename}` from SharePoint. ### WF-5 — Excel export `GET shipment/exportshipments` builds a workbook named `Shipments_Export_{yyyyMMdd_HHmmss}.xlsx` from the full list (no filter is applied). Header row is maroon with white text. Fourteen columns match the shipment form fields, including the port name. EPPlus license context is `NonCommercial`. ### WF-6 — Excel import 1. `GET shipment/import` and `GET shipment/importshipments` redirect to `import/upload`. 2. `GET import/downloadtemplate` returns `Shipment_Import_Template_{yyyyMMdd}.xlsx` with the same business columns and one grey sample row. The sample port cell is the text "Sample Port", which fails the port allow-list. 3. `POST import/upload` checks the anti-forgery token, extension (`.xlsx`, `.xls`), and size (maximum 10 MB). It creates `sdsImportSessions`, stages rows, and validates them. The signed-in name is `LoggedInUsername`, or the literal `Unknown User` when session is empty. 4. `GET import/preview/{sessionId}` pages staging rows (default page size 50). Execute is allowed when status is `Pending` and `ValidRows > 0`. 5. `GET import/errors/{sessionId}` lists validation errors. 6. `POST import/execute/{sessionId}` runs the import when status is `Pending`, then redirects to results. 7. `GET import/results/{sessionId}` shows counts. If TempData from the execute post is missing, it rebuilds a summary from the session. 8. `POST import/cancel/{sessionId}` sets status `Cancelled` only while the session is `Pending`. 9. `GET import/history` lists the last 20 sessions for the current user name. `CleanupOldImportSessionsAsync` deletes sessions older than 30 days. No controller action calls it. ### WF-7 — Suppliers `GET supplier/supplierlist` lists suppliers ordered by name. Filter matches name and active flag (1 active, 2 inactive). Add Supplier is rendered only when the session role is `ADMIN`. Every row name links to the update screen, with no role check in the view. Create requires a non-empty name and sets `created_by` from session. Update changes name and `is_active`. Delete sets `is_active` false when the row is currently active. Messages: "Supplier Created Successfully", "Supplier Updated Successfully.", "Supplier Deleted Successfully." ## Functional requirements ### FR-001 — Sign in with the host identity **Who:** a person who can open the site. **Trigger:** post of the login form. **Input:** `User.Identity.Name`. The form does not send a username. **Behaviour:** look up `mstUser.strDomainLogin`. For role ids 1, 2, and 3, store username, role label, and user id in session and open the shipment list. **Output:** redirect to `shipment/shipmentlist`, or the login view with a message. **Validation:** a matching row. Password is not checked. **Error:** exception message on the login view. The explicit "could not be authenticated" branch is reachable only when `ValidateUser` returns null. The current method returns an empty object instead, so an unknown name still redirects. **Evidence:** `AccountController`, `UserService.ValidateUser`, `Login.cshtml`. **Implementation:** Implemented. **Confidence:** High for the code path. Low for whether the host actually populates `User.Identity.Name`. `launchSettings.json` has Windows authentication off. ### FR-002 — Session lifetime **Behaviour:** `Program.cs` calls `AddSession` with idle timeout 10 minutes, HttpOnly, and `IsEssential`, then calls `AddSession()` again with no options. **Implementation:** Implemented registrations. Which options win at runtime is **Unknown / Requires Confirmation**. Logout (`GET account/logout`) returns the login view and does not call `Session.Clear`. ### FR-003 — Shipment list and indent search **Who:** anyone who can open `shipment/shipmentlist`. The action does not read the role. **Inputs:** optional search string and status id. **Behaviour:** join shipment to supplier. Search is contains-match on indent number only. Status ids 1, 2, 3 map to `SAVED`, `SUBMITTED`, `CANCELLED`. **Output:** `ShipmentList.cshtml`. **Evidence:** `ShipmentController.ShipmentList`, `FilterShipmentList`, `ShipmentService.FilterShipmentList`. **Implementation:** Implemented. ### FR-004 — Capture a shipment **Who:** the Add Shipment button is on the list for every role. The save button on the add view is inside the `ADMIN` and `LOGISTICS` switch arms. `VIEWER` has an empty arm. The controller does not reject a viewer who posts the action. **Inputs:** supplier id, indent number, shipshape number, mother vessel, ETA, bill of entry number, container number, port id, container received, number of flatcases, voyage, M/BL number, redirect to SACD, and optional files. **Behaviour:** `ValidateScreen` (see business rules). Insert with status `SAVED` and the session user as creator. Upload files. Redirect to detail. **Error:** `TempData["ErrorMessage"]` and the add view again. On validation failure the controller resets the three dates to `DateTime.Now`. **Evidence:** `ShipmentController.AddShipment`, `ValidateScreen`. **Implementation:** Implemented. ### FR-005 — Update a shipment and keep SharePoint aligned **Trigger:** save on the update screen. **Behaviour:** copy fields onto the existing row, set `updated_by` and `updated_at`. If the indent number changed, call `RenameFoldersFiles` for folder `Shipment Checklist` and the stored file names, then upload any newly chosen files. **Validation:** same screen rules as create, except duplicate indent runs only when process is `SAVE`. Update sets process to `UPDATE` before validation. **Evidence:** `ShipmentController.UpdateShipment`, `SharePointService.RenameFoldersFiles`. **Implementation:** Implemented. ### FR-006 — Checklist documents Each shipment has these filename columns. SharePoint object name is `{indent}_{original file name}` in `/Shipment Checklist/{indent}`. | Document type key | Column | Label on the update view | |---|---|---| | ClampingRequest | `clamping_request_file` | Clamping Request | | SupplierInvoice | `supplier_invoice_file` | Supplier Invoice | | BillOfLading | `bill_of_lading_file` | Bill of Lading | | ArrivalInvoice | `arrival_invoice_file` | Arrival Invoice | | ArrivalInvoice2 | `arrival_invoice_file2` | Arrival Invoice 2 | | CertificateOfOrigin | `certificate_of_origin_file` | Certificate of Origin | | PackingList | `packing_list_file` | Packing List | | DROToTransport | `dro_to_transport_file` | DRO to Transport | | CertificateOfRemoval | `certificate_of_removal_file` | Certificate of Removal | | ExtendedDetention | `extended_detention_file` | Extended Detention | | VOC | `voc_file` | VOC | | DeptOfHealth | `dept_of_health_inspection_file` | Dept of Health | | SARSDocuments | `sars_documents_file` | SARS Documents | | GRVCompleteRGBC | `grv_complete_RGBC_file` | GRV Complete RGBC | | GRVEdwardSnell | `grv_edward_snell_file` | GRV Edward Snell | | ACCPACReceipts | `accpac_receipts_file` | ACCPAC Receipts | Admin sees View and Delete when a filename is stored. Viewer and Logistics see View only. Delete is not hidden on the server. Preview URL comes from Graph `Preview()`. On HTTP 404, `GetSharepointPreviewURL` returns the text `File not Found`. Comments that would blank that string are commented out, so the detail page can render it as the link. The certificate-of-origin delete button posts `documentType=CertificateOfOrigin`. The controller switch case is `CerificateOfOrigin` (the second t is missing). Those strings do not match, so the case falls through to `default` and the delete action does not clear the column or call Graph. **Implementation:** Implemented. ### FR-007 — Submit when the checklist is complete **Trigger:** Submit on the update screen. **Behaviour:** `CheckShipmentSubmitStatus` returns true when every filename column is non-empty, except the two arrival invoices, where either file is enough. The controller then sets status `SUBMITTED`. The button is enabled when that check is true and status is not `SUBMITTED`. Admin and Logistics see it. Viewer does not. `ShipmentService.SubmitShipment` writes `COMPLETED` and is not called by the controller. **Implementation:** Implemented controller path. The service method is unused. ### FR-008 — Cancel with a reason **Trigger:** Cancel Shipment on the update screen, admin arm, when status is not `CANCELLED` and not `COMPLETE`. **Input:** `CancelReason` text. **Behaviour:** empty reason redisplays the update view with "Please enter a Cancel Reason before Cancelling Shipment." Otherwise status becomes `CANCELLED` and the reason is stored. The add screen's cancel button uses the opposite condition: it is enabled only when status is `CANCELLED` or `COMPLETE`. A new shipment does not have those statuses, so the button renders disabled. Submit on the add screen is always disabled. Logistics has no cancel button on the update screen. The cancel action does not check the role. **Implementation:** Implemented. ### FR-009 — Export the shipment list **Who:** Export on the list, no role check. **Output:** xlsx download of all shipments. **Error:** log and redirect to the list. The user is not told why. **Implementation:** Implemented. ### FR-010 — Import shipments from Excel **Who:** Import on the list, no role check. **Inputs:** file and import mode. Modes offered in the view model: | Value | Label in the model | Description in the model | |---|---|---| | `UpdateExisting` | Update Existing Shipments Only | Update if indent matches, skip if not found | | `AddNewOnly` | Add New Shipments Only | Create new rows, skip existing | Default mode on the view model is `UpdateExisting`. **Behaviour of execute:** for each validated staging row, if an `sdsShipment` with that indent exists, update it; otherwise insert it with status `SAVED`. The stored `ImportMode` is not read in `ExecuteImportAsync`. **Discrepancy:** the mode descriptions and the execute loop disagree. SQL also allows `ReplaceAll`, which the UI does not offer. **Requires Confirmation** which mode the business expects. **Validation:** see business rules BR-020 onward. A session can execute while status is `Pending` and at least one row is valid, including when other rows are errors (`CanExecute` uses `ValidRows > 0`, and execute loads rows whose staging status is `Validated`). **Implementation:** Implemented. ### FR-011 — Maintain suppliers **Who:** list is linked in the nav for every signed-in page that uses `_Layout`. Add is admin-only in the view. Update and delete actions do not check the role. **Behaviour:** name required. Delete is a soft deactivate (`is_active = false`) and only selects a row that is currently active. New shipments offer active suppliers only. The update screen loads every supplier. **Implementation:** Implemented. ### FR-012 — User maintenance screens Views exist under `Views/Maintenance/Users/`. `UserService.GetUserList`, `GetUser`, `AddUser`, `UpdateUser`, and `DeleteUser` have their database code commented out. They return an empty model, the string `"success"`, or `false`. The Users item in `_Layout.cshtml` is inside a comment. `UserController` actions have no `[Route]`, and the conventional route is commented out in `Program.cs`. **Implementation:** screens and controller methods exist. Persistence is not implemented. Reachability of the actions is **Unknown / Requires Confirmation** until the app is run. ### FR-013 — Startup refuses to run without configuration **Behaviour:** if `RGBC_SDS_CONNECTION_STRING` is missing, throw `InvalidOperationException`. If any of `SharepointCredentials:TENANT_ID`, `CLIENT_ID`, `CLIENT_SECRET`, `SCOPE`, or `SITE_ID` is blank, throw before the host listens. `.env.example` documents the same keys using the environment form `SharepointCredentials__KEY`. `SharePointService` reads `SharePointCredentials:...` (capital P). ASP.NET Core configuration keys are case-insensitive, so both spellings are expected to hit the same values. That binding was not executed here. **Implementation:** Implemented. ## Constraints - Indent number is also a SharePoint folder name. Characters `\ / : * ? " < > |` are rejected on the screen. - Bill of entry and number of flatcases must parse as `int` on the screen. The import validator does not apply that numeric rule. - M/BL numeric check is commented out (commit `5d06fdb`). - Offloading ports are the four hard-coded names. They are not a database table. - EPPlus is set to non-commercial. Whether that licence is acceptable is **Unknown / Requires Confirmation**. - CORS policy `CorsPolicy` allows any origin, method, and header. No `UseCors` call was found after the policy is registered, so the policy may be unused. **Requires Confirmation**. - Session is the only application auth state. Controllers do not require it. ## Edge cases visible in code - Unknown domain login still redirects to the list (FR-001). - Empty session on import is stored as created-by `Unknown User`. - `AddShipment` comment says session user id arrives null and "need to sort out". `user_id` is still assigned from `GetInt32("LoggedInUserId")`. - `GetShipment` returns `new sdsShipment()` when the id is missing, and the detail action treats a non-null entity as found. A missing id can render an update view for an empty entity. The method returns that empty object after the null check, so the `if (existingShipment != null)` branch is taken. - Filter plus search projects a shorter list-item shape than search-only (the combined branch omits several fields that the list view does not display). The list view itself only shows the short columns. - Import history and cleanup are per user name string, not per user id. - `SharePointController.UploadFile` returns OK without uploading. The live upload path is `SharePointService` from the shipment controller. - First-time Graph upload runs because `FileHelper` writes the new file name onto the entity before `UploadFilesAsync`, and the upload switch uploads only when that column is already non-empty. A failure inside the switch that surfaces as HTTP 404 is swallowed for that file (`ServiceException` NotFound). ## What this PRD does not claim No business owner has confirmed the status vocabulary, the import modes, the document checklist, or the role matrix. Where the code and an enum or a view-model description disagree, both are recorded and the code path that runs is marked Implemented.