# Product requirements — bi_tool_backend_phase6 (SAWIS BI backend)

Evidence date: 2026-10-19.

Source: `git@bitbucket.org:overdrive/bitool_backend.git`, branch `phase6`, commit `717166ec` (2026-08-07).

Provenance labels used below:

- **Documented** — stated in in-repo docs or the Phase 6 spec HTML.
- **Implemented** — present in code on the inspected commit.
- **Inferred** — reasonably derived from code, not written as a requirement.
- **Planned** — described as future work.
- **Unknown** — not established.

This document records behaviour the repository shows. It does not decide new product scope.

## Product purpose

SAWIS staff, wine producers, and Overdrive operators use this backend to:

- Query packaged and bulk wine export statistics, vineyard status, and grape production.
- Turn a SAWIS levy declaration Excel file (DAF) into formatted workbooks and emails.
- Capture monthly domestic SAWIS 7 returns, approve them, and report on historic and modern domestic volumes.

The interactive UI is not in this repository. Clients are expected to call HTTP APIs. A Postman collection `postman/SAWIS BI.postman_collection.json` and an in-app API dashboard (`GET /auth/api-dashboard`) exist for operators.

## Users and roles

Roles are the integer `userjwt.admin_role`. Labels below are **Documented** in `docs/phase 6/SAWIS_BI_Phase_6_SAWIS_7_Domestic_Capture_v1.7.html` and in the `AdminFilter` class comment. Enforcement is **Implemented** in `App\Libraries\DomesticAuth` and `App\Filters\AdminFilter`.

| `admin_role` | Names used in the repo | What the code allows |
|---|---|---|
| 0 | Spec: "normal". Capture when linked to producer codes | Not an admin-filter role. `canAccessSubmissions` is true when `Phase6TestLogin::isDomesticProducer` is true and the user has at least one producer code. Submit is allowed for role 0 |
| 1 | AdminFilter comment: "Daff". Spec table: "Phase 5 admin" | Included in `ADMIN_FILTER_ROLES` (levy mapper and admin CRUD). Not in domestic user-manage, approve, or "see all submissions" sets |
| 2 | Developer | Admin filter, domestic user management, Super User assignment, all submissions, submit, approve/unlock, reopen cancelled, edit submission data even when not `New` (except synced and cancelled, which stay locked) |
| 3 | Super User | Domestic user management, approve/unlock, reopen cancelled. Sees only the assigned producer pool (not `SUBMISSION_ALL_ROLES`). Cannot submit (submit allows 0, 2, 4 only). Cannot assign Super User roles |
| 4 | Super User Admin | Same domestic powers as role 2 for assignment, all-submission visibility, submit, and approve. Also an admin-filter role |

A user counts as a domestic producer when role is 3 or 4, or `domestic_producer` is set, or `domestic_producer_codes` is non-empty (`Phase6TestLogin::isDomesticProducer`). **Implemented.**

Producer access is stored in `domestic_user_producer_access` (user id + producer code) and mirrored to the CSV column `userjwt.domestic_producer_codes` (`DomesticUserModel`). **Implemented.** The Phase 6 spec still describes assignment mainly in terms of the CSV column. **Documented requirement and implementation both exist; the spec HTML has not been rewritten for the junction table.**

Session identity is a row in `userjwt` whose `JWT_token` matches the `Authorization` header (optional `Bearer ` prefix on the domestic helper). Each successful filtered call updates `Start_date`. **Implemented.**

Named accounts in `AuthFilter` have fixed idle windows instead of `JWT_SECRET__CookieLoginExpires`: `hleroux` is 120 minutes; a hard-coded list including `EttieneOverdrive`, `Gareth89`, `nateley1`, `celestesmall`, `bonita`, and related addresses is 90 minutes. Everyone else uses the env minute value. **Implemented.** Whether those named exceptions are still approved policy is **Requires Confirmation**.

## Main workflows

### W1 — Analyst reads export statistics

1. Client calls `GET /getFilters` (no auth) or `GET /auth/getFilters` (auth).
2. Client posts a filter body to a public chart or table route, or to the matching `/auth/tables/...` route.
3. Models apply scalar or array filters (`IN` when the value is an array).

**Implemented.** Public and authenticated copies of the same table routes both exist. **Inferred:** two frontends, or a transition from open to authenticated access. Which one production uses is **Unknown**.

### W2 — User logs in

1. `POST /loginAuth` with JSON email/password/`typeOfLogin`, or with an encrypted `payload`.
2. Optional AES-256-CBC payload: base64(IV + ciphertext), key from env `LOGIN_PAYLOAD_KEY` (64 hex characters). Plain login still works when email is present. **Documented** in `docs/general/LOGIN_ENCRYPTED_PAYLOAD_FRONTEND.md` and **Implemented** in `UserLoginModel`.
3. After 5 failed attempts from one IP, the cache key blocks further attempts for 900 seconds and the API returns HTTP 429. **Implemented.**
4. On success the response includes a token stored on `userjwt`. Domestic producers also receive `domestic.first_login_help_text` and `show_onboarding_help` when configured. **Documented** in the Phase 6 README and **Implemented** via `DomesticAuth::domesticFirstLoginHelpText()`.

The frontend is told to show onboarding once (for example a localStorage flag). That client behaviour is **Documented**, not implemented in this repo.

### W3 — Operator processes a levy declaration (Phase 5)

Documented in `adminDetails.md` and `docs/general/SAWIS_BI_Live_Documentation.md`. Implemented in `AdminController` and `AdminModel::processSawisLevyDeclarationFile`.

1. Admin (role 1, 2, or 4) posts the DAF workbook to `POST /admin/mapper_processing_sheet_queue` with `admin_email`.
2. If another process is active, the API returns 409.
3. With no `step`, the server stores the file, emails an upload confirmation, and spawns `spark process:mapper-queue <processId>` (memory limit 4G). The command class is not in the tree. **Requires Confirmation** that background runs still start.
4. Direct `step` values run pieces in the request: step 1 imports into `mapper_excel_init`; steps 2–4 transform, build Excel, and email.
5. Step 0 validates DAF headers against `INIT_COLS` (`scripts/step2_field_metadata.py`) on a sample of the header plus three data rows. A bad file still returns success for the upload, sets status toward "User File Error", and emails the user and `ettiene@overdrive.co.za`. **Documented** (changelog 2026-06-04, later narrowed 2026-06-05 so empty `CultName` is not a blocking lookup error).
6. Step 3 does not generate the final workbook while unknown-match tables still contain `NA` corrections. **Documented** in the changelog and **Implemented** as described there.
7. The operator can poll `GET /admin/mapper_processing_step2_progress`, read history, retry, cancel, and download uploaded, debug, final, and test files.

Final-sheet test (`POST /admin/mapper_processing_final_sheet_test`) imports an expected workbook into `mapper_excel_formatted_test` and compares it with `mapper_excel_formatted`. Test files attach to the main active `emails_sent` row (`uploaded_test_*` columns), not a separate Testing process. **Documented** 2026-06-04.

### W4 — Producer captures a domestic SAWIS 7 return (Phase 6)

**Documented** in `docs/phase 6/README.md` and the v1.7 spec. **Implemented** on `auth/domestic/*` (auth filter).

1. A job or an operator calls `sys/domestic/create-period-submissions` to insert missing `New` headers for linked producer codes, period `YYYY-MM` (stored as `YYYYMM`), country from the producer list VT or `RSA`.
2. Producer opens or resumes `POST /auth/domestic/submissions` with `PRODKODE`, `period`, and `country` (`VT` alias accepted). Unique open key is PRODKODE + period + country.
3. Producer saves the front hierarchy with `PUT .../saved-totals` (version 2: sizes, then container totals, then wine-class totals, then grand total). Legacy flat `sub_totals` is accepted and upgraded.
4. Producer adds back-page lines with `POST .../data`. Server sets KB from `domestic_producer_list`, KWDI from the producer list when KB is G, H, or L (empty when KB is B or K), and VT from the submission country.
5. `GET .../reconciliation` checks hierarchy rollups and BR-15 (each size's litres equals the sum of matching `domestic_sawis_7_data` rows).
6. `POST .../submit` is allowed only when status is `New`, reconciliation is ok, and (for role 0) the submission is the most recent open month. Status becomes `submitted` on the header and on line rows.
7. Role 2, 3, or 4 calls `POST /auth/domestic/admin/submissions/{id}/approve`. Unlock sends the return back to `New` and, when it had been approved, removes matching rows from the approved staging table. Synced returns cannot be unlocked (catalog text in `ApiRouteCatalog`).
8. `sys/domestic/mark-synced` (BR-14) sets approved headers to `synced` after `domestic_sawis_7_data_approved` is empty, unless `force=1`.
9. `sys/domestic/cancel-overdue-submissions` (BR-31) runs on the 5th of the month unless `force=1`. It cancels the previous-previous month's `New` or `submitted` returns and emails Super Users and producers. Roles 2, 3, and 4 may reopen cancelled to `New`.

Help: `GET /auth/domestic/help` returns the linked Super User and onboarding text. `POST /auth/domestic/help` sends `{ subject, message, emailTo }`.

### W5 — Domestic BI report

`GET /getDomesticReportFilters` and `POST /getDomesticReport` are **not** inside the `auth` group. **Implemented** in `Routes.php`. Whether that is intentional is **Requires Confirmation**.

Layouts **Implemented** in `DomesticReportModel`:

| Layout | Behaviour |
|---|---|
| Dynamic, Price, Packaging | Main year, up to 4 additional years, grouping `month` / `period` / `year`, optional percent change `((main - compare) / compare) * 100`. Default compare year is the previous year; trends default on |
| Cultivar | Single year, sections by cultivar heading, subtotals, grand total. No multi-year trends |
| Historic vs modern | Filter `data_type` `historic` or `modern`. Historic dynamic can switch Front (container) vs Back (cultivar and price class) |

### W6 — Refresh export fact tables

`GET /auth/runDataSync?step=0..4` mirrors SQL in `docs/monthlySync/`. Step 0 refreshes `mapper_data` from the import for the oldest datum's window. Steps 1–4 refresh `export_data_qa`, `export_data_dev`, price ranges, and industry for `period >=` that window and keep older history. `full=1` is documented as a full truncate rebuild. Separate routes refresh vineyard status and grape production. **Documented and implemented.** These routes sit in the `auth` group; the group closure in `Routes.php` does not itself attach `authFilter` to every route. `runDataSync` has no per-route `authFilter`. **Requires Confirmation** that the group is protected in deployment.

## Functional requirements

### FR-001 — Export filter lists

The system returns year, month, country, cultivar, package type, and related filter lists.

- Who: any caller of the public route; authenticated caller of the auth route.
- Trigger: `GET /getFilters` or `GET /auth/getFilters`.
- Output: filter lists including `YearFilterList` and `CountryFilterList` as described in the live doc.
- Evidence: `FilterDataApi`, `AuthFilterDataApi`, live doc section 2.1.
- Implementation: **Implemented**. Confidence: high.

### FR-002 — Export charts and tables accept one or many filter values

Chart and table POSTs accept a filter body. Array values are applied as `IN` lists; scalars as equality.

- Routes (public): `POST /getTop25Data`, `/getChartData`, `/getCultivarChartData`, `/getYearOverYearComparison`, `/getTotale_Liters`, `/getExports`, `/getTotale_LitersData`, and `POST /tables/get*TableData` (export market per product type, exports, cultivar type exports, export market volume, TOP 25).
- Auth copies: `POST /auth/tables/get*` and `POST /auth/reports/getRSAExportsReport`.
- Evidence: live doc section 2; comment in that doc points at `ApplyFilters` in the chart model.
- Implementation: **Implemented**. Confidence: high for the route contract; the exact SQL was not re-read line by line in this pass.

### FR-003 — Vineyard status and grape production reports

The system returns vineyard and grape reports for year, region, and cultivar filters that may be scalar or array.

- Vineyard POSTs under `/vineyardStatus/` for total hectares, hectares, age distribution, status, ranking, uprootings, new plantings, net change, irrigation, trellising.
- Grape POSTs: `/grapeProduction/totalProduction`, `/grapeProduction/productionPerRegionAndCultivar`.
- Filter GETs: `/getVineyardStatusFilters`, `/getVineyardStatusReports`, `/getGrapeProductionFilters`, `/getGrapeProductionReports`.
- Evidence: `Routes.php`, live doc, `docs/phase-4/GRAPE_PRODUCTION_TESTING_README.md`.
- Implementation: **Implemented**. The phase-4 README's `quickTest` and `testAllFilters` paths are **not** in `Routes.php`. Confidence: high.

### FR-004 — Login and lockout

`POST /loginAuth` authenticates a user and returns a token, or an error.

- Inputs: email, password, `typeOfLogin`, `encrypt`, or encrypted `payload`.
- Validation: bad payload returns 400 with "Invalid or corrupted login payload." Missing key is logged and surfaced when generating a payload.
- Error: 5 failures per IP within 15 minutes returns 429.
- Evidence: `UserLoginModel` (`LOGIN_ATTEMPT_MAX = 5`, `LOGIN_ATTEMPT_TTL = 900`), changelog 2026-02-02, login payload doc.
- Implementation: **Implemented**. Confidence: high.

### FR-005 — Session required on filtered routes

Routes that set `authFilter` or `adminFilter` reject a missing or unknown token with HTTP 401 and a plain-text body ("Access denied…").

- Admin filter additionally requires role 1, 2, or 4. Other roles get "you are not an admin."
- When `REACT_APP_USE_AUTH_HEADERS` is false, admin filter falls back to the `userjwt` row for `User_email = EttieneOverdrive` and still checks role. **Implemented.** This bypass is a local-development behaviour. Whether it can be enabled in production is **Requires Confirmation**.
- Admin filter also accepts `?admin_email=` for a role-2 user whose session is less than 60 minutes old, when no Authorization header is present and auth headers are required. **Implemented.**
- Evidence: `app/Filters/AuthFilter.php`, `app/Filters/AdminFilter.php`.

### FR-006 — Levy file upload and processing

An admin can upload a `.xlsx` or `.xls` DAF and process it through import, transform, Excel generation, and email.

- Trigger: `POST /admin/mapper_processing_sheet_queue`.
- Inputs: `admin_email` (required unless resolved from the logged-in `userjwt.admin_email`), `excel_file`, optional `step`, `debug`, `process_id`, `row`, `collectErrorsBatch`, `skip_stop`.
- Outputs: JSON `success`, `process_id`, and either a "processing started" payload or the direct step result. Active-process conflict is 409.
- Validation: email pattern and file extension as documented in `adminDetails.md`.
- Errors: 400 validation, 404 missing process or file on resume, 500 on exception. Pipeline errors notify the user and `ettiene@overdrive.co.za`.
- Evidence: `adminDetails.md`, `AdminController`, `AdminModel`.
- Implementation: **Implemented**. Confidence: high for the documented contract. The missing Spark command class is a delivery risk for the unattended path (see project plan).

### FR-007 — Unknown-match correction before final Excel

Step 3 is blocked while cultivar, country, or producer correction rows still contain `NA`. The operator maintains those rows through admin CRUD.

- Routes: `/admin/unknown-cultivar-matches`, `/admin/unknown-country-matches`, `/admin/unknown-producer-matches`, each GET/POST/PUT/DELETE, behind `adminFilter`.
- MCC designation lookup uses the same four verbs at `/admin/mcc-designation-lookup`.
- Response shape **Documented** in `NewKnowledgeBase.md` (2026-05-07): list `{ success, data, count }`, create 201, update, delete, not-found message.
- Implementation: **Implemented**. Confidence: high.

Three import routes inside the `admin` group do **not** set `adminFilter`:

- `POST /admin/mapper_import_unkown_cultivar_lookup__import`
- `POST /admin/mapper_import_unkown_country_lookup__import`
- `POST /admin/mapper_import_unkown_producer_lookup__import`

**Implemented.** Whether they are meant to be public is **Requires Confirmation**.

### FR-008 — Domestic submission lifecycle

A domestic return moves through stored statuses `New`, `submitted`, `approved`, `synced`, `cancelled`. "Never Submitted" is computed for the admin dashboard and is not stored. **Implemented** constants in `DomesticAuth::SUBMISSION_STATUSES`.

- Producers edit line data only while status is `New`, except roles 2 and 4 who may edit when status is not synced and not cancelled (`canEditSubmissionData`).
- Submit requires reconciliation ok and status `New`.
- Approve and unlock require roles 2, 3, or 4.
- Synced is locked.
- Evidence: `DomesticAuth`, `DomesticSubmissionModel::submit`, `DomesticAdminController`.
- Implementation: **Implemented**. Confidence: high for these gates. Full UAT against a live database was not run.

### FR-009 — Domestic front totals reconcile to back lines

The server stores hierarchical `saved_totals` and flattens size rows for BR-15. Submit is rejected with HTTP 422 until reconciliation passes.

- Bottle-type (BT) is server-enforced: cans (`TH=B`) still wine class maps to `S`; cans sparkling maps to `V`; any sparkling container maps to `V`; still non-cans maps to null.
- Evidence: `docs/phase 6/README.md`, `REVISION_saved_totals_v2_and_onboarding.md`, `DomesticReconciliation.php`.
- Implementation: **Implemented** as documented in those files. Confidence: high for the written rules; numeric edge cases were not executed.

### FR-010 — Domestic reports

`POST /getDomesticReport` returns one of the layouts in workflow W5, including optional trend columns for dynamic, price, and packaging.

- Evidence: `DomesticReportModel` filter catalog and `docs/phase 6/README.md`.
- Implementation: **Implemented** in code. The 2026-08-06 plan says real QA was blocked because working `domestic_sawis_7_data` was empty on that local database. Current data state is **Unknown**.

### FR-011 — Domestic period jobs

The system can create monthly headers, cancel overdue returns on the 5th, mark approved returns synced, and verify or sync the producer list.

- Routes under `/sys/domestic/` (GET and POST pairs for create, cancel, mark-synced, sync-producer-list; GET for verify). Admin variants of verify and sync also exist under `/auth/domestic/admin/` with `authFilter`.
- Cancel supports `force`, `notify`, `dry_run`, and optional `period`.
- Evidence: `DomesticSystemController`, `DomesticSubmissionCron`, `ApiRouteCatalog` descriptions.
- Implementation: **Implemented** and **unauthenticated** on the `sys` paths. **Requires Confirmation** before treating that as an approved production requirement.

### FR-012 — Email attempt log

Every send attempt used by the levy flow and `mail_helper` is written to `email_log` without failing the send if logging fails. An admin can read `GET /admin/email_logs` as HTML or `?format=json`.

- Evidence: changelog 2026-02-25, `NewKnowledgeBase.md`, `AdminController`.
- Implementation: **Implemented**. The migration class that creates the table is **not** in `app/Database/Migrations/`. How the table is created in each environment is **Requires Confirmation** (manual SQL vs a migration that was never committed).

### FR-013 — Price range history

An authenticated user can read price-range data and read or save price-range filter history.

- Routes: `POST /auth/getPriceRangeData`, `/auth/getPriceRangeFilterHistory`, `/auth/setPriceRangeFilterHistory`, each with `authFilter`.
- Evidence: `Routes.php`, `priceRangeController`.
- Implementation: **Implemented**. Confidence: medium (routes confirmed; payload schema not fully read).

## Important constraints

- Time zone used by auth and domestic jobs: `Africa/Johannesburg`. **Implemented.**
- Domestic period storage is `VARCHAR(6)` `YYYYMM`, not a SQL `DATE`. API accepts `YYYY-MM` or `YYYYMM`. **Documented and implemented** (`DomesticAuth::periodToStorage()`).
- Header uniqueness: PRODKODE + period + country. **Documented.**
- CORS allows origin `*` and the `Authorization` header. **Implemented** in `app/Filters/Cors.php`.
- Levy step 3 is memory-heavy. Spawn uses `memory_limit=4G`. The CLI command comment in the changelog also sets 2G inside the command; that command file is absent, so only the spawn flag is visible. **Implemented** spawn flag.
- Analytics fact data is described as `export_data_qa` (or a dev equivalent) in the live doc. Sync steps also maintain `export_data_dev` and industry tables. Which table production charts read is **Requires Confirmation** if more than one is populated.
- Public analytical routes and `sys/domestic/*` do not require a token. **Implemented.** This is a security constraint as much as a product one.

## Integrations

| System | Direction | Trigger | Data | Auth | Failure |
|---|---|---|---|---|---|
| MySQL `sawis_bi` | App to DB | Every request | Users, exports, mapper tables, domestic tables | Credentials in `app/Config/Database.php` default group (host `10.0.0.145`, database `sawis_bi`). A localhost block is commented out. Secrets are not repeated here | Levy steps retry "MySQL server has gone away" using `SAWIS_MYSQL_GONE_AWAY_MAX_RETRIES` (default 25) and `SAWIS_MYSQL_GONE_AWAY_RETRY_DELAY_MS` (default 1500). **Documented** in README |
| MySQL `sawis_api` | App to DB | Group `SAWISAPI` | Not traced in this pass | Localhost `root` in the active group; a remote block is commented out | **Unknown** |
| SMTP | Outbound | Levy pipeline, domestic help, cancel notices | HTML or text email | CI email config / env. Not copied | Logged to `email_log`; levy errors also notify `ettiene@overdrive.co.za` |
| Selwin | Outbound staging table, inbound "staging empty" signal | `mark-synced` checks `domestic_sawis_7_data_approved` | Approved SAWIS 7 rows | **Unknown.** No HTTP client for Selwin was identified. The contract is operational: do not mark synced while staging still has rows unless `force=1` | Job skips when staging is non-empty |
| OpenAI completions | Outbound | `chatGPTMessage()` | Prompt text | API key in the helper environment. Not copied. No route calls the controller | **Unknown** |
| SonarQube | CI to Sonar | Bitbucket pipeline on `master` | Source scan | `SONAR_TOKEN` pipeline variable | Pipeline file has no other step |
| AWS | Declared only | Composer `aws/aws-sdk-php` | None found under `app/` | — | Not an active integration in the tree that was searched |

## Planned behaviour (not to be treated as done)

From `docs/phase 6/BACKEND_NEXT_STEPS_PLAN.md` (2026-08-06), still written as next steps at that date:

- Historic promote of `domestic_sawis_7_data_imported` into working `domestic_sawis_7_data` with `historic = 1`.
- Sample modern submissions so report QA can run.
- A real schedule and access control for system jobs.
- Report parity with the workbook `docs/phase 6/BI Tool Domestic June 26 (4).xlsx` (file is present; contents were not parsed).
- OpenAPI spec (recommendation in the live doc, section 6, not a committed spec).

The junction table item in that plan was subsequently implemented (see project plan).

## Requirements traceability snapshot

| ID | Requirement | Evidence | Implementation | Confidence |
|---|---|---|---|---|
| FR-001 | Filter lists | `FilterDataApi`, live doc | Implemented | High |
| FR-002 | Multi-value export filters | live doc, chart/table routes | Implemented | High |
| FR-003 | Vineyard and grape reports | `Routes.php`, phase-4 doc | Implemented | High |
| FR-004 | Login and lockout | `UserLoginModel` | Implemented | High |
| FR-005 | Session filters | `AuthFilter`, `AdminFilter` | Implemented | High |
| FR-006 | Levy pipeline | `adminDetails.md`, `AdminModel` | Implemented, CLI class missing | Medium |
| FR-007 | Unknown-match CRUD | routes, NewKnowledgeBase | Implemented | High |
| FR-008 | Domestic lifecycle | `DomesticAuth`, submission model | Implemented | High |
| FR-009 | BR-15 reconciliation | phase 6 README, `DomesticReconciliation` | Implemented | High |
| FR-010 | Domestic reports | `DomesticReportModel` | Implemented, data QA unproven | Medium |
| FR-011 | Domestic jobs | `DomesticSystemController` | Implemented, unauthenticated | High |
| FR-012 | Email log | changelog, admin route | Implemented; migration file absent | Medium |
| FR-013 | Price ranges | auth routes | Implemented | Medium |