# Business rules — bi_tool_backend_phase6 (SAWIS BI backend)

Evidence date: 2026-10-19.

Source: branch `phase6` at `717166ec`.

Rules are copied from docs or from code. Each rule is marked **Documented** or **Inferred**. Inferred means the code enforces it and no separate approved-rule statement was found beyond comments or the Phase 6 spec. Where the spec and the code use the same wording, the rule is **Documented** and **Implemented**.

Do not treat an inferred rule as a signed business decision.

## Identity and access

### BR-A1 — Session is a database row, not only a signed JWT

**Inferred** from `AuthFilter` and `DomesticAuth`. A request is authenticated when `Authorization` matches `userjwt.JWT_token` and `Start_date` is inside the idle window. A successful call rewrites `Start_date`, so the window slides.

`firebase/php-jwt` is a Composer dependency. The filter path that was read looks up the token string in MySQL. Whether login also signs a JWT with `JWT_SECRET` is **Implemented** in `UserLoginModel` (`getenv('JWT_SECRET')` is read on success). Both a secret and a database row are involved. Exact token format was not fully traced.

### BR-A2 — Idle window depends on the email address

**Inferred** from `AuthFilter`.

| Account | Idle window |
|---|---|
| `hleroux` | 120 minutes, hard-coded |
| `EttieneOverdrive`, `s matthyse`, `Gareth89`, `nateley1`, `nateley@sawis.co.za`, `celestesmall`, `celeste@sawis.co.za`, `bonita` | 90 minutes, hard-coded |
| Everyone else | `JWT_SECRET__CookieLoginExpires` minutes from the environment |

`DomesticAuth::currentUser()` does **not** use that named list. It uses `JWT_SECRET__CookieLoginExpires`, or 30 minutes when the value is missing or not positive. **Discrepancy** between Phase 5-style `AuthFilter` and Phase 6 `DomesticAuth`. **Requires Confirmation** which window domestic routes actually apply, because domestic routes set `authFilter` on the route and also call `DomesticAuth` inside the controller.

### BR-A3 — Admin tools require role 1, 2, or 4

**Documented** in the `AdminFilter` class comment (1 Daff, 2 Developer, 4 Super User Admin). Roles 0 and 3 are denied. **Implemented** via `DomesticAuth::ADMIN_FILTER_ROLES`.

Spec v1.7 labels role 1 "Phase 5 admin" rather than "Daff". **Requires Confirmation** which label is the business name.

### BR-A4 — Who may capture, submit, approve, and assign

**Documented** in spec v1.7 and **Implemented** in `DomesticAuth` / `DomesticSubmissionModel::submit`.

| Action | Roles |
|---|---|
| Capture when the user has producer codes | Role 0 with codes; also roles 2, 3, 4 |
| Submit | 0, 2, 4 only. Role 0 may submit only the most recent open (`New`) month |
| Approve or unlock | 2, 3, 4 |
| Reopen cancelled to `New` | 2, 3, 4 |
| See every submission (no producer pool) | 2 and 4 |
| Manage domestic users | 2, 3, 4 |
| Set `admin_role` / promote Super Users | 2 and 4 |
| Edit lines when status is not `New` | 2 and 4, and only when status is not `synced` and not `cancelled` |

Role 3 (Super User) approves a pool and does not submit. **Implemented.**

### BR-A5 — Producer pool

**Documented** in spec v1.7: Super User Admin assigns a `PRODKODE` pool; a Super User may link producers only inside that pool.

**Implemented** additionally on 2026-08-07: `DomesticUserModel` stores codes in `domestic_user_producer_access` and mirrors a CSV onto `userjwt.domestic_producer_codes`. Comment in the model: "No hard 3-code / 50-char cap". An older cap, if it existed in the spec, is not what this model comment describes. **Requires Confirmation** against spec section 5 if a numeric cap is still contractual.

### BR-A6 — Login lockout

**Documented** in changelog 2026-02-02 and **Implemented**: 5 failed attempts per IP, then HTTP 429 for 15 minutes (`LOGIN_ATTEMPT_TTL = 900`).

### BR-A7 — Encrypted login is optional

**Documented** in `docs/general/LOGIN_ENCRYPTED_PAYLOAD_FRONTEND.md`. **Implemented**: if `payload` is set and `email` is empty, decrypt AES-256-CBC; otherwise plain email/password is accepted. The frontend doc's example key was not copied into this knowledge base. **Requires Confirmation** that the example is not a live secret.

### BR-A8 — Local header bypass

**Inferred.** `REACT_APP_USE_AUTH_HEADERS=false` skips normal header checks. Admin filter then loads `User_email = EttieneOverdrive` and still enforces role. This is dangerous if the flag is set outside a developer machine. **Requires Confirmation** that production keeps the flag true (headers required). The code default when the env var is missing is to require headers (`AdminFilter::useAuthHeadersEnabled`).

## Export analytics

### BR-E1 — Filters may be one value or many

**Documented** in `docs/general/SAWIS_BI_Live_Documentation.md`. Models branch on `is_array` and emit `IN (...)` or `=`. **Implemented** as described. Empty results are valid when the filter matches no rows; TestSprite cases assert shape, not business totals.

### BR-E2 — Public and authenticated table APIs both exist

**Inferred** from `Routes.php`. The same five table operations are registered without auth and again under `/auth/tables/` with `authFilter`. No document was found that says the public copies must stay public. **Requires Confirmation.**

### BR-E3 — Monthly export refresh keeps history outside the import window

**Documented** in `docs/monthlySync/README.md`.

- Import supplies about 2–3 years in `mapperdata_imported`.
- Step 0 replaces `mapper_data` from `MIN(DATUM)` of that import.
- Steps 1–4 delete `export_*` rows with `period >=` that month and insert the new window.
- Older months stay.
- A whole month is recalculated even if the import starts mid-month.
- `full=1` truncates and rebuilds (documented as emergency/repair).
- Included product codes and excluded `LNDKDE` values are listed in that README (see features).

### BR-E4 — Sync steps relax ONLY_FULL_GROUP_BY for the session

**Documented** in the monthly sync README. Steps 1–4 save `sql_mode`, remove `ONLY_FULL_GROUP_BY`, run the original `GROUP BY`, then restore `sql_mode`.

## Phase 5 levy declaration

Rules below are **Documented** in `adminDetails.md` and the changelog unless marked inferred.

### BR-L1 — One active process

If `getActiveProcessId()` finds an active process, a new queue upload returns HTTP 409. Final-sheet test on that same main row skips 409 for its own run (changelog 2026-06-04).

### BR-L2 — File type

Upload must be `.xlsx` or `.xls`. `admin_email` must match a simple email pattern unless the logged-in admin's `userjwt.admin_email` is used (changelog 2026-04-22).

### BR-L3 — Steps

| Step | Rule |
|---|---|
| 0 | Validate DAF headers and order against `INIT_COLS`. Sample is the header plus the first 3 data rows (2026-06-05). Empty `CultName` is allowed. `CntryName`, `ApplName`, `TypeProdName`, `OrigName`, `ProdNo` stay required. Failure does not reject the upload; status can become "User File Error" and an email is sent |
| 1 | Load raw rows into `mapper_excel_init`. Stop at the first empty transaction date |
| 2 | Transform into `mapper_excel_formatted` and related `mapper_process_*` tables. Record unknown matches |
| 3 | Build Excel. Refuse while any unknown-match correction column is still `NA` |
| 4 | Email the pipeline admin, CC `ettiene@overdrive.co.za`, with download links |

### BR-L4 — Applicant name correction order

**Documented** in `adminDetails.md` section 4. `cleanApplicantName()` checks `unknown_producer_matches` by padded `prod_no` first, then by `appl_name_from_file`. `member_name_details` and `producer_number_change` are used only when no correction exists. `correct_member_name = 'NA'` means unknown and still needs review.

### BR-L5 — Final-sheet comparison

**Documented** in `adminDetails.md` and changelog 2026-04-09.

- Step 1 of the test imports into `mapper_excel_formatted_test` only.
- Step 2 compares test rows to production formatted rows.
- A second step-2 run can partially re-transform certificates that still differ.
- Comparison uses shared difference logic with `testEntry()` (serial normalisation, country, product, and producer matchers). `manufacturer` and `export_total_weight` are excluded.
- Test files are stored beside the main process, not as a second Testing process row (2026-06-04).

### BR-L6 — Currency-looking cellar codes

**Documented** changelog 2026-05-04. `WSNo` / `WSBNo` / `WSBExtraNo` use the calculated cell value so a display such as `R1,861` stores `1861`.

### BR-L7 — Serial numbers

**Documented** changelog 2026-05-06 and 2026-04-09. Values matching `dd/mm/yyyy` are kept. The exporter must not treat a glued batch code as a US date. Whole-string date formats still normalise.

### BR-L8 — Retry on dropped MySQL

**Documented** in the root README. Env `SAWIS_MYSQL_GONE_AWAY_MAX_RETRIES` default 25, `SAWIS_MYSQL_GONE_AWAY_RETRY_DELAY_MS` default 1500.

### BR-L9 — Debug does not send real pipeline mail

**Documented** in `adminDetails.md`. `debug=true` avoids real emails. Production SMTP is used otherwise (live doc section 5).

## Phase 6 domestic SAWIS 7

Spec identifiers (BR-14, BR-15, BR-31, BR-33) are the names used in code comments and `ApiRouteCatalog`. They are **Documented** by those comments and by `docs/phase 6/README.md`. The full legal text lives in the v1.7 HTML, which was not re-transcribed here.

### BR-D1 — Status set

**Implemented** `DomesticAuth`: stored statuses are `New`, `submitted`, `approved`, `synced`, `cancelled`. "Never Submitted" is dashboard-only.

### BR-D2 — One open header per producer, period, and country

**Documented** in the phase 6 README. Unique key PRODKODE + `period` + `country`. Period is stored `YYYYMM`. API accepts `YYYY-MM` or `YYYYMM`.

Cron creation uses the producer list VT, or `RSA` when VT is empty, so the key is populated. The capture UI may open more headers for other countries. **Documented** in `DomesticSubmissionCron` comments.

### BR-D3 — Line dimensions are not free-typed

**Documented** in the phase 6 README and **Implemented** in `DomesticAuth` helpers.

- KB comes from `domestic_producer_list` for the header PRODKODE.
- KWDI is empty when KB is `B` or `K`.
- KWDI is required from the producer list when KB is `G`, `H`, or `L`.
- Line VT equals the submission country, not a client-supplied territory.

Detail GETs return lookup fields as `{ "key", "value" }` for KB, KWDI, TH, HOUGRO, CUL, PK, BT, VT.

### BR-D4 — Front totals hierarchy and BT

**Documented** in `docs/phase 6/README.md` and `REVISION_saved_totals_v2_and_onboarding.md`.

- Size litres roll up to container total, then wine-class total (`still`, `sparkling`, `fortified`, `wine_component`), then `grand_total`.
- Cans (`TH=B`) on still wine: `BT=S`.
- Cans on sparkling: `BT=V`.
- Any sparkling container: `BT=V`.
- Still non-cans: `BT` null.
- Glass, plastic, bag-in-box, PET, foil, and carton share the same size-to-total pattern. No other special container rules are documented.
- Legacy flat `{ sub_totals, grand_total }` is accepted and upgraded to version 2 on save.

### BR-D5 — BR-15 blocks submit

**Documented** and **Implemented** in `DomesticSubmissionModel::submit`. Each size total must equal the sum of `domestic_sawis_7_data` for the same TH + HOUGRO + BT. Hierarchy rollups must match. Otherwise HTTP 422: "Submission cannot be submitted until all sub-totals reconcile." Only status `New` can be submitted.

### BR-D6 — BR-31 cancel window

**Documented** in `DomesticSubmissionCron` and `ApiRouteCatalog`. On the 5th of the month (unless `force=1`), previous-previous month submissions still `New` or `submitted` become `cancelled`. Emails go to Super User Admin, Super User, and the producer. `dry_run=1` reports without writing. `notify=0` suppresses mail.

The 2026-08-06 plan says the Q1 monthly cutoff is still open with SAWIS and may change the day wording. The code day is the 5th until that decision lands. **Requires Confirmation.**

### BR-D7 — BR-33 create headers

**Documented** in the cron class. Create missing `New` headers for active producer codes for the period (default current month). Existing PRODKODE+period+country rows are skipped.

### BR-D8 — BR-14 mark synced

**Documented** in `DomesticSubmissionCron`. After Selwin empties `domestic_sawis_7_data_approved`, matching approved headers become `synced` with `sync_date` and stay locked. If staging still has rows, the job skips unless `force=1`.

Selwin itself is not in this repository. The rule "wait until staging is empty" is **Documented** here; the external system's behaviour is **Unknown**.

### BR-D9 — Synced and cancelled are not producer-editable

**Implemented** `canEditSubmissionData` and `isSyncedLocked`. Synced cannot be edited. Cancelled cannot be edited until a role in BR-A4 reopens it to `New`.

### BR-D10 — Working litres vs Selwin text columns

**Documented** in `alter_gareth_schema_hardening.sql` header: working `TOTAAL` is `DECIMAL(18,2)` litres; Selwin `_imported` and `_approved` stay `VARCHAR`. Working `JJJJMM` is `VARCHAR(6)`. **Requires Confirmation** that every environment has run this script. It is not a CodeIgniter migration.

### BR-D11 — Onboarding text

**Documented.** `DOMESTIC_FIRST_LOGIN_HELP_TEXT` or `DOMESTIC_FIRST_LOGIN_HELP_FILE` is returned on login and on `GET auth/domestic/help` for domestic producers. The client decides when to show it. **Requires Confirmation** of the approved wording; the default string in code was not treated as approved copy.

## Email

### BR-M1 — Levy completion mail

**Documented** changelog 2026-04-22. To: pipeline `admin_email`. CC: `ettiene@overdrive.co.za`. HTML uses the Outlook-safe table helper (2026-06-04).

### BR-M2 — Failures are not silent

**Documented** in the live doc. `notifySawisPipelineError` sends a short message to the user and a detailed message including stack to `ettiene@overdrive.co.za`.

### BR-M3 — Log every attempt

**Documented** `NewKnowledgeBase.md` 2026-02-25. `email_log` stores to, subject, status, source, error. Body and attachments are not stored. Logging must not throw out of the send path.

## Rules that must not be invented

The following were **not** established as business rules:

- A price for domestic capture or a billing rule. A costing PDF is mentioned in the August plan (`0843_001.pdf`) as signed and out of scope to change. The PDF is not in the file list that was reviewed.
- A legally final Q1 cutoff day. The plan says it is open. The code uses the 5th.
- Which public analytics routes are allowed to stay unauthenticated in production.
- Chart totals, levy rates, or cultivar business definitions beyond what the lookup tables and filter docs already name. Workbooks in the repo were not parsed.