# PRD — bitool_backend Evidence date: 2026-10-19. Source: `git@bitbucket.org:overdrive/bitool_backend.git`, branch `master` at `09437373` (2026-09-11). Provenance labels: - **Documented** — stated in `README.md` or in a comment that describes intended behaviour. - **Implemented** — present in code on this commit. - **Inferred** — reasonably derived from code, not written as a requirement. - **Planned** — described as future work or left commented as a route to turn on. - **Unknown** — not established. There is no PRD in the source repository. `README.md` describes the Admin Overdrive starter (authentication, authorization, registration, menu management). The routed application is a SAWIS BI report API. Where those disagree, the code is treated as implemented behaviour and the README as a stale starter document. `bi_tool_backend_phase6/prd.md` documents branch `phase6` of this same remote. Domestic capture and levy features in that PRD are not in this `master` tree. ## Product purpose **Inferred.** Callers load wine-export statistics, vineyard status, and grape production for a chosen period and filters. A logged-in caller can also ask for a named producer's figures when enough other producers are in the same slice. Operators rebuild the reporting tables from imported SAWIS data. **Documented** only as labels: "SAWIS BI Auth Dashboard", mail from-name "SAWIS BI", deploy label "SAWIS: BIQA". The interactive UI is not in this repository. `GET /` returns the CodeIgniter welcome view (`app/Controllers/Home.php`). ## Users and roles | Actor | What the code allows | Provenance | |---|---|---| | Anonymous API caller | Charts, public tables, public filters, vineyard reports, grape reports, login, and the `sys/*` and `auth/runDataSync*` GETs | Implemented. Those routes do not set `authFilter` | | Logged-in caller | `auth/*` routes that set `authFilter`. Identity is the email inside a JWT that also exists in `userjwt` | Implemented | | Producer user | WebBoon returns `bi_tool.prodkodes`. Names are looked up in `mapper_data`. The industry dropdown lists a name only when that code has rows in `export_data_dev_industry` | Implemented | | Special login `EttieneOverdrive` | Ignores the WebBoon producer list and stores a fixed list of producer codes | Implemented in `UserLoginModel::loginAuth` | | Extended-session user | One email hardcoded in `SessionLoginExpiry::EXTENDED_USER` gets a 120-minute idle window | Implemented. Whether that exception is approved is **Unknown / Requires Confirmation** | | Operator | Opens the sync dashboard and runs rebuilds. No role check | Implemented. Access control is **Unknown** beyond "the route is public" | | Admin Overdrive user with menus and registration | Described in `README.md` | **Documented** in the starter README. No registration or menu route exists | There is no `admin_role` column usage on this branch. That role model belongs to the `phase6` documentation. ## Systems the product talks to 1. The caller's HTTP client. 2. MySQL database `sawis_bi` on the host configured in `app/Config/Database.php`. 3. SAWIS WebBoon: `POST https://www.sawis.co.za/login.bisswo.php` with JSON `token`, `username`, `password`. 4. SMTP `smtp.overdriveqa.co.za` when a login's producer codes are missing from the industry export table. 5. Slack, only from `public/deploy.php`, after a deploy token check. 6. A public spreadsheet URL supplied by the operator to the dashboard check. `test_url_phase_4` defaults to a Google Sheets CSV publish URL. `sawis_api` is configured and unused by application code on this branch. ## Major workflows ### WF-1 — Log in `POST loginAuth`. Body is JSON `{ email, password, typeOfLogin }`. If the body is empty, the same three fields are read from the query string. `typeOfLogin` must be the string `"1"`. Any other value fails login. `"1"` posts the credentials to WebBoon. The env var `WebBoon_API_TOKEN` is sent as `token`. Success requires the JSON response to contain `bi_tool`. Producer codes on `bi_tool.prodkodes` are kept. Each code is named from `mapper_data.PRODUSENTNAAM`. A code with no mapper row is dropped. The handler then writes or updates `userjwt` for that email: new JWT, `Start_date` (Africa/Johannesburg), and `Producer_details` JSON. It does not import industry rows. If any of those codes are absent from `export_data_dev_industry`, it emails `UserLoginModel::MISSING_INDUSTRY_EMAIL_TO` once per user and missing-code set. The cache TTL is one year. The cache entry is written only after SMTP accepts the message. Response `200`: `{ message: "Login Succesful", token }`. The token is not also returned in a cookie by this method. Failed password: `401` `{ message: "The Password does not Match.", token: "" }`. Five failures from the same IP within the cache window return `429` and the message "Too many login attempts. Please try again in 15 minutes." The window is 900 seconds. A database or network exception after WebBoon said yes does not increment the counter; it returns `401` with message "Catched, Unable to connect to the database." and the exception object in `token`. Provenance: **Implemented**. ### WF-2 — Call an authenticated route The client sends header `Authorization` with the raw JWT, or `Bearer `. `AuthFilter` loads `userjwt` where `JWT_token` equals that string. Missing token, unknown token, or `Start_date` older than the idle window returns `401` JSON `{ message }` and stops the request. A known fresh token updates `Start_date` to now (sliding session) and sets global `$Producer_details` from the stored JSON. Idle minutes come from env `JWT_SECRET__CookieLoginExpires`. If that value is missing or not positive, the idle window is 30 minutes. The extended user is 120 minutes, both for JWT `exp` at login and for this idle check. Provenance: **Implemented**. The filter is attached only to the routes listed under FR-010. It is not global. The commented global registration in `app/Config/Filters.php` is not active. ### WF-3 — Read export charts and public tables `POST` bodies are JSON filter objects. Chart and public table routes do not require a JWT. Charts and the public tables aggregate `export_data_qa`. Litres are summed. The reporting window is built from `year`, `month`, and `period` (a length in months). If the requested month is past the latest `period` in `export_data_qa` and the year is that latest year, the end month is clamped to the latest data month. The previous-year window is the same months one year earlier. `PACKAGED` is stored and queried as `BOTTELS`. `BULK` stays `BULK`. Provenance: **Implemented**. ### WF-4 — Read authenticated tables, price ranges, and the RSA report Same JSON shape, plus `Authorization`. If `producer_details` is present and its first value is not `"Industry"` and not empty, the query adds that producer's rows from `export_data_dev_industry` / `mapper_data`. The producer slice is refused when too few distinct producers match. The HTTP body is then `400` with "Not enough unique producers for your call to get your specific producer info." The string `"Industry"` selects the industry aggregate (`export_data_dev` for the RSA report, `export_data_qa` for the other tables). Price-range history is stored on `userjwt.Filter_history` for the email taken from the caller. If the email contains `;`, only the part before the semicolon is used. Provenance: **Implemented**. The minimum-producer thresholds are in `business-rules.md`. ### WF-5 — Vineyard status and grape production `GET` filter and report-list routes read lookup tables and the `Reports` table. `POST` report routes require a non-empty JSON object (`ReportFilterPayload`). An empty body or empty object returns `400` "A non-empty JSON filter object is required." Vineyard figures come from `export_data_VineyardStatus`, except new plantings, which read `INFONEWPLANT_imported`. Grape figures come from `export_data_GrapeProduction` and are tonnes. These routes do not use `authFilter`. Provenance: **Implemented**. ### WF-6 — Rebuild reporting data An operator opens `GET auth/dashboard` (no `authFilter`). The page shows vineyard and grape sync status and buttons that call: - `GET auth/runDataSync` - `GET auth/runDataSync_VineyardStatus` - `GET auth/runDataSync_GrapeProduction` Monthly export sync takes the oldest `DATUM` in `mapperdata_imported`, backs up working tables, deletes `mapper_data` from that date forward, inserts all of `mapperdata_imported` into `mapper_data`, then rebuilds `export_data_qa`, `export_data_dev`, `export_data_price_ranges`, and the industry export. If the rebuild does not return `"SUCCESS"`, it tries to restore the backups. `Routes.php` comments say these GETs are destructive and have no `authFilter`, and that production should restrict them at the web server or add auth before they are reachable beyond localhost. `GET sys/ExportPendingIndustryCodes` imports producer codes that appear in `userjwt.Producer_details` and do not yet exist in `export_data_dev_industry`. `dry=1` or `list=1` lists them and does not write. The access flag on the producer code is ignored. Provenance: **Implemented**. Who may call these URLs in production is **Unknown / Requires Confirmation**. ## Functional requirements ### FR-001 — Welcome page Requirement: `GET /` returns the CodeIgniter welcome view. Who: any caller. Trigger: `GET /`. Inputs: none. Expected behaviour: `Home::index` returns `view('welcome_message')`. Outputs: HTML. Validation: none. Errors: framework 404 for unknown paths. `set404Override` is commented out. Evidence: `app/Config/Routes.php`, `app/Controllers/Home.php`. Implementation: Implemented. Confidence: High. ### FR-002 — Login Requirement: A caller with WebBoon credentials receives a JWT and a `userjwt` session row. Who: API caller. `typeOfLogin` `"1"` is the only accepted mode. Trigger: `POST loginAuth`. Inputs: `email`, `password`, `typeOfLogin` as JSON, or the same fields on the query string when JSON is null. Expected behaviour: WF-1. Outputs: `200` and a token, or `401` / `429`. Validation: WebBoon response must include `bi_tool`. IP failure counter max 5, TTL 900 seconds. Errors: WebBoon transport failure returns the password-mismatch `401`. Exceptions after a successful WebBoon check return the database `401` and put the exception in `token`. Evidence: `app/Controllers/LoginController.php`, `app/Models/UserLoginModel.php`. Implementation: Implemented. Confidence: High. ### FR-003 — Session check Requirement: Selected routes accept only a JWT that is stored and not idle. Who: logged-in caller. Trigger: routes that set `'filter' => 'authFilter'`. Inputs: `Authorization` header. Expected behaviour: WF-2. Each success slides `Start_date`. Outputs: the route's normal response, or `401` JSON. Validation: exact `JWT_token` match, then `Start_date` inside the idle window. Errors: "Access denied, no Authorization set...", "Access denied, session expired...", or "Access denied, try logging out and back in." Evidence: `app/Filters/AuthFilter.php`, `app/Libraries/SessionLoginExpiry.php`. Implementation: Implemented. Confidence: High. ### FR-004 — Export filter lists Requirement: The caller can load years, months, periods 1–12, product colours, countries, package types, cultivars, and the latest year/month. Who: any caller on `GET getFilters`. A logged-in caller on `GET auth/getFilters` also receives price ranges and producer names. Trigger: those GETs. Inputs: none for the public list. The auth list uses `$Producer_details` set by `AuthFilter`. Expected behaviour: Years, months, and latest period come from `export_data_qa`. Periods are the integers 1 through 12, not distinct values from the table. Product colours and cultivars come from `product_type_lookup` for a fixed `prd_kde` list. Countries come from `country_group_lookup` where `YN = 'Y'`, excluding the labels `ALL COUNTRIES` and `UNITED KINGDOM total`. Package types are the fixed pair `BULK` and `PACKAGED`. The auth producer list always starts with the string `Industry`. Further names are included only when the code has at least one `export_data_dev_industry` row. The access flag is not consulted. The public list sets `Producer_detailsFilterList` to an empty array. Outputs: a `FilterModel` object. Evidence: `app/Models/FilterDataModel.php`, `app/Models/AuthFilterDataModel.php`. Implementation: Implemented. Confidence: High. ### FR-005 — Export charts Requirement: The caller can request litre charts for countries, cultivars, year-on-year comparison, total litres, and exports. Who: any caller. No auth filter. Trigger: `POST getTop25Data`, `getChartData`, `getCultivarChartData`, `getYearOverYearComparison`, `getTotale_Liters`, `getExports`, `getTotale_LitersData`. Inputs: JSON. Chart date logic uses `year`, `month`, and `period`. Expected behaviour: Aggregates on `export_data_qa` for the calculated `period` range (`YYYYMM`). Bulk rows are those with `BU = 'X'` plus a union of rows where `BU` is not `X`. Packaged rows use `BO != 'X' OR BO IS NULL`. Outputs: `200` JSON, or `400` with the exception message. Evidence: `app/Controllers/ChartDataApi.php`, `app/Models/ChartDataModel.php`. Implementation: Implemented. Confidence: High. ### FR-006 — Public export tables Requirement: The caller can request five export tables without a JWT. Who: any caller. Trigger: `POST tables/getExportMarketPerProductTypeTableData`, `tables/getExportsTableData`, `tables/getCultivarTypeExportsTableData`, `tables/getExportMarketVolumeByProductTypeTableData`, `tables/getTOP25TableData`. Inputs: JSON filters, including optional `producer_details`. Expected behaviour: Queries `export_data_qa` unless `producer_details` names a producer, in which case the industry-specific path runs even though this route did not pass `$auth = true`. A failed producer check returns `400` and the "Not enough unique producers" message. SQL mode `ONLY_FULL_GROUP_BY` is removed for the session before the query. Outputs: `200` row list, or `400`. Evidence: `app/Controllers/TableDataApi.php`, `app/Models/TableDataModel.php`. Implementation: Implemented. Confidence: High. Whether public routes should accept `producer_details` is **Unknown / Requires Confirmation**. ### FR-007 — Authenticated export tables Requirement: The same five tables, plus producer columns when a producer was requested, for a logged-in caller. Who: caller passing `authFilter`. Trigger: `POST auth/tables/...` mapped to `AuthTableDataApi`. Inputs: JSON plus `Authorization`. Expected behaviour: The model is called with `$auth = true`. When the result is producer-specific, rows are labelled so Industry and the producer name are both represented. Refusal threshold matches FR-006. Outputs: `200` or `400`. Evidence: `app/Controllers/AuthTableDataApi.php`, `TableDataModel` around the `$this->auth` branch. Implementation: Implemented. Confidence: High. ### FR-008 — RSA exports report Requirement: A logged-in caller can request the RSA exports report in litres and in ZAR, EUR, GBP, and USD. Who: caller passing `authFilter`. Trigger: `POST auth/reports/getRSAExportsReport`. Inputs: JSON. `producer_details` of null, empty, or `Industry` reads `export_data_dev`. Any other producer list reads `export_data_dev_industry` filtered by `prodname`. Expected behaviour: Two periods produce a trend block as a percent change. If a producer filter is set and `checkMoreThanThreeProducers` is false for a period, the method returns `0` and the controller responds `400`. Outputs: structured `productTypes`, `grandTotals`, and `uniqueIdentifiers`, or `400`. Evidence: `app/Models/TableDataReportModel.php`, `AuthTableDataApi::getRSAExportsReport`. Implementation: Implemented. Confidence: High. ### FR-009 — Price ranges Requirement: A logged-in caller can query price-band litre data and save or load filter history. Who: caller passing `authFilter`. Trigger: `POST auth/getPriceRangeData`, `auth/getPriceRangeFilterHistory`, `auth/setPriceRangeFilterHistory`. Inputs: JSON filters. History is keyed by the user's email. Expected behaviour: Bands come from `price_ranges`. An end value of `-1` is presented as `>= {start}`. Data is read from `export_data_price_ranges` where `totale_liters != 0`. Currencies handled in `ExportDataToPriceRange` are `Rand`, `USD`, and `EUR`. History is a string on `userjwt.Filter_history`. Unknown email on save returns `403` "Email not found". The apostrophe in `COTE D'IVOIRE` is escaped before it is placed in SQL. Outputs: JSON data, or the history string. Evidence: `app/Controllers/priceRangeController.php`, `app/Models/PriceRangeModel.php`. Implementation: Implemented. Confidence: High. The exact JSON fields the SPA sends for a price-range query were not separately specified in a schema file. ### FR-010 — Authenticated route list Requirement: Only these routes run `authFilter`. Who: the router. Trigger: request path. Expected behaviour: `auth/getCountryGroup`, `auth/getFilters`, `auth/getPriceRangeData`, `auth/getPriceRangeFilterHistory`, `auth/setPriceRangeFilterHistory`, the five `auth/tables/*` posts, and `auth/reports/getRSAExportsReport`. `auth/excel_import_check`, `auth/dashboard`, and the three `auth/runDataSync*` routes are in the `auth` group and do not set the filter. Evidence: `app/Config/Routes.php`. Implementation: Implemented. Confidence: High. ### FR-011 — Vineyard status Requirement: The caller can list vineyard filters and reports, and can request hectare and vine breakdowns. Who: any caller. Trigger: `GET getVineyardStatusFilters`, `GET getVineyardStatusReports`, and the `POST vineyardStatus/*` routes: `totalHectares`, `hectares`, `ageDistribution`, `status`, `ranking`, `uprootings`, `newPlantings`, `netChange`, `irrigation`, `trellising`. Inputs: POST body must be a non-empty JSON object. Filter fields used by `setFilters` are `year`, `region`, `cultivar`, `age` (only when the query passes `$age = 'yes'`), and `white/red`. Expected behaviour: Standard queries exclude cultivar code `031` and keep codes whose first character is `0` or `4`. New plantings read `INFONEWPLANT_imported`. Report metadata is `Reports` rows with `Type = 'VineyardStatus'` ordered by `Order`. Empty text cells in the formatted table become `-`. Empty number cells become `0` when the key contains `VINES`, otherwise `0.00`. `GET vineyardStatus/testReports/{report}/{count}` and `GET vineyardStatus/test_url_phase_4` are also routed. Outputs: `200` table payload, or `400`. Evidence: `app/Controllers/VineyardStatus.php`, `app/Models/VineyardStatusModel.php`, `app/Libraries/ReportFilterPayload.php`, `app/Traits/NormalizesEmptyReportValues.php`. Implementation: Implemented. Confidence: High. No routed method passes `$sultana = true`. Sultana rows are excluded from every routed vineyard query. Whether a sultana report is still required is **Unknown / Requires Confirmation**. ### FR-012 — Grape production Requirement: The caller can list grape-production filters and reports, and can request total tonnes and tonnes by region and cultivar. Who: any caller. Trigger: `GET getGrapeProductionFilters`, `GET getGrapeProductionReports`, `POST grapeProduction/totalProduction`, `POST grapeProduction/productionPerRegionAndCultivar`. Inputs: POST body must be a non-empty JSON object. Filters: `year`, `region`, `cultivar`, `white/red`. Expected behaviour: Sums `TONNE` from `export_data_GrapeProduction` into red, white, and total columns. If `year` is omitted, the model uses the previous calendar year and the ten years before it. White/red choices offered by `getWhiteRed` are `WHITE` and `RED`. Report metadata is `Reports` where `Type = 'GrapeProduction'`. `GET grapeProduction/testReports/{report}/{count}` is also routed. Outputs: `200` or `400`. Evidence: `app/Controllers/GrapeProduction.php`, `app/Models/GrapeProductionModel.php`. Implementation: Implemented. Confidence: High. ### FR-013 — Pending industry import Requirement: An operator can list or import producer codes that users have logged in with and that are not yet in `export_data_dev_industry`. Who: any caller who can reach the URL. No auth filter. Trigger: `GET sys/ExportPendingIndustryCodes`. `GET sys/createIndustryImport` calls `LoginController::createIndustryImport` with no argument. Inputs: optional `dry` or `list` query flag (`1`, `true`, or `yes`). Expected behaviour: Collects codes from every non-empty `userjwt.Producer_details`. Ignores the access flag. `dry` or `list` returns `{ message, pending, count, dry_run: true }` and does not write. Otherwise it runs the industry export for those codes. Empty pending list returns `200` "No pending industry prodkodes". A failed import returns `500`. Outputs: JSON. Evidence: `ImportData::ExportPendingIndustryCodes`, `ImportData::listPendingIndustryProdkodes`. Implementation: Implemented. Confidence: High. ### FR-014 — Data sync dashboard Requirement: An operator can see whether vineyard and grape import years differ from live and export tables, and can start a sync. Who: any caller who can open the URL. Trigger: `GET auth/dashboard` and the three run-sync GETs. `GET auth/excel_import_check` runs `ImportData::testing_query`. Inputs: the dashboard form also posts a public sheet URL and a type of `FullYear`, `FullMonths`, or `FullMonthsCountry`, plus detail and industry checkboxes. Expected behaviour: WF-6. Status strings produced for the cards are `already_synced`, `new_years`, `needs_refresh`, and `no_import_data`. Outputs: HTML dashboard, or JSON `{ message }` from the sync methods. Sync failure still uses HTTP status from the controller's `respond` of the model's `[$response, 200]` pair when the controller returns that pair. Several sync methods return the pair directly from the controller method, so the HTTP status is the framework default for a PHP array return. **Requires confirmation** of the status code the SPA or operator actually sees. Evidence: `app/Controllers/ImportData.php` `dashboard`, `runDataSync`, `runDataSync_VineyardStatus`, `runDataSync_GrapeProduction`. Implementation: Implemented. Confidence: Medium on the HTTP status of a raw array return. High on the SQL sequence. ### FR-015 — CORS Requirement: Browsers can call GET and POST, including JSON preflight, from any origin. Who: any browser. Trigger: every request, including unknown paths. `cors` is in `$globals` and in `$required`. Inputs: any method. `OPTIONS` is answered in the filter. Expected behaviour: `Access-Control-Allow-Origin: *`. Methods `GET, POST, PUT, PATCH, DELETE, OPTIONS, HEAD`. `Authorization` is an allowed header. Preflight returns `204` with an empty body. `Allow-Credentials` is not set. Several controllers also send the same headers with PHP `header()` and `die()` on `OPTIONS` before the class runs. Outputs: CORS headers on the response. Evidence: `app/Filters/Cors.php`, `app/Config/Filters.php`. Implementation: Implemented. Confidence: High. ### FR-016 — Deploy hook Requirement: A caller who presents the deploy token and whose IP is on the allowlist can run `public/deploy.php`. Failures notify Slack. Who: deploy caller. This script is not a CodeIgniter route. Trigger: HTTP request to `public/deploy.php` with query `tkn`. Inputs: token and client IP. Expected behaviour: Wrong or missing token notifies Slack and returns the text `Nope`. The script's project label is `SAWIS: BIQA`. Outputs: deploy side effects in the rest of that file, or `Nope`. Evidence: `public/deploy.php`. Implementation: Implemented. Confidence: High that the gate exists. The steps after a successful gate were not copied here because the file contains live tokens. ## Integrations | System | Direction | Trigger | Data | Auth | Failure | |---|---|---|---|---|---| | WebBoon `login.bisswo.php` | Outbound POST | `loginAuth` with `typeOfLogin` `"1"` | JSON token, username, password. Response `bi_tool.prodkodes` | Shared token from `WebBoon_API_TOKEN` | Exception becomes login `401` | | MySQL `sawis_bi` | Read/write | Almost every request | Report tables, `userjwt`, lookups | Username in `Database.php` | Login catch returns database `401`. Other controllers return `400` with the exception message | | SMTP | Outbound | Login when industry codes are missing | User email, missing codes, names, sync URL | SMTP user in `Email.php` | Logged. Cache is not set, so a later login can retry | | Slack | Outbound | Deploy token failure, and the success path in `deploy.php` | Project label and message | Webhook in that file | Not handled beyond `curl_exec` | | Public spreadsheet | Outbound GET of a CSV URL | Operator dashboard / `test_url_phase_4` | Sheet URL and check type | The sheet is expected to be public | **Unknown** beyond the method's own error handling | ## Important constraints - PHP 8.0 or newer is what `composer.json` and `README.md` require. The README also lists `intl`, `mbstring`, `mysqlnd`, and `xml`. - CSRF, the debug toolbar, and the honeypot filter are commented out. - Report SQL is built by concatenating request values. That is how filters are applied. It is also why a crafted filter can change the query. No separate validation layer was found. - Sync and `sys/*` routes are unauthenticated GETs that change data. The route file says so. - Credentials are stored in the repository. See technical documentation. They are not repeated in this PRD. ## Planned or leftover starter behaviour | Item | Evidence | Status | |---|---|---| | User registration, menu management, role menus | `README.md`, `app/Helpers/menu_helper.php`, `useraccess_helper.php` | Documented in the README. Helpers are not in `Autoload::$helpers`. No routes | | ChatGPT page, DB admin page, API simulation | Controllers exist. Views they name were not in `app/Views` except the welcome and error views | Not routed. Auto-route is off | | Commented export routes under `auth` | `Routes.php` lines for `setPriceRanges` and the older `ExportDataTo__*` GETs | Commented. The methods still exist on `ImportData` | | Jira and Confluence | URLs in `README.md` | Not read | ## Unknown / Requires Confirmation - Production base URL and which branch is deployed. - Whether the web server blocks `auth/runDataSync*`, `auth/dashboard`, and `sys/*`. - Whether public table routes are supposed to honour `producer_details`. - Whether sultana (cultivar `031`) reports are still required. - The contract of the WebBoon `access` flag. The industry dropdown and the pending import both ignore it. - Whether `EttieneOverdrive` and the extended-session email are approved exceptions. - HTTP status actually returned when a sync method returns a PHP array pair. - How rows arrive in `mapperdata_imported` and the vineyard and grape import tables.