# Business rules — bitool_backend

Evidence date: 2026-10-19.

Source: `git@bitbucket.org:overdrive/bitool_backend.git`, `master` at `09437373`.

Rules are what the code enforces. A rule is **Inferred** when the code behaves that way and no comment or document states it as policy. A comment that states the rule is marked **Documented** even when the only place it is written is source.

This file does not copy secrets, producer lists, or tokens.

## Identity

### BR-001 — Only WebBoon login type 1 is accepted

**Implemented.** `UserLoginModel::checkLoginDetails` switches on `typeOfLogin`. Case `'1'` calls WebBoon. The default returns `'false'`, which `loginAuth` turns into `401` "The Password does not Match."

The comment in that switch says "1 is the correct call".

### BR-002 — A producer code without a mapper name is dropped

**Implemented.** `checkCreateExport` looks up `mapper_data.PRODUSENTNAAM` for each WebBoon code. A null row sets the name to empty and does not add the code to the stored producer array.

**Inferred.** Codes SAWIS does not know in `mapper_data` never become selectable producers.

### BR-003 — One username receives a fixed producer list

**Implemented.** If `email` equals `EttieneOverdrive`, the producer array built from WebBoon is replaced with a hardcoded list of codes, names, and `access` `"1"`.

Whether this account is still required is **Unknown / Requires Confirmation**.

### BR-004 — WebBoon access flags are stored and then ignored for industry visibility

**Implemented.** The WebBoon map is `prodkode => access`, and that access value is stored on `Producer_details`.

`AuthFilterDataModel::getFilters` comments: "Industry dropdown: include producer name only when prodkode has rows in export_data_dev_industry (not access flag)."

`ImportData::listPendingIndustryProdkodes` comments: "Access flag is intentionally ignored (0/1) for pending import detection."

**Inferred.** An access value of `0` does not, by itself, hide a producer or skip an import.

### BR-005 — Industry import does not run at login

**Documented** in `loginAuth`: "Industry import is manual/monthly, not on login."

**Implemented.** Login only calls `sendMissingIndustryCodesEmailOnce`.

### BR-006 — Missing industry codes generate one email per user and code set

**Implemented.**

- Recipient is the constant `UserLoginModel::MISSING_INDUSTRY_EMAIL_TO` (`ettiene@overdrive.co.za`).
- The message lists the user, each missing code, the producer name when known, a list URL (`sys/ExportPendingIndustryCodes?dry=1`), and the import URL.
- Cache key is an MD5 of the lower-cased email and the sorted missing codes.
- TTL is 31536000 seconds (the constant comment says 1 year).
- The cache is written only after `email->send()` returns true.
- A send failure is logged and does not block the `200` login response.

## Sessions

### BR-007 — Five failed logins from one IP lock that IP for 15 minutes

**Implemented.** `LOGIN_ATTEMPT_MAX` is 5. `LOGIN_ATTEMPT_TTL` is 900. The cache key is `login_attempt_` plus an MD5 of the request IP. The sixth attempt in the window returns `429` before WebBoon is called.

A successful login deletes the key. A database exception after WebBoon succeeds does not increment the counter. The comment says this is so a DB or network error is not treated as a bad password.

### BR-008 — JWT lifetime and idle window match, and each API call slides the window

**Implemented.** At login, `exp` is `iat` plus `SessionLoginExpiry::jwtTtlSecondsForUser`. The filter does not read the JWT `exp` claim. It looks up the raw token in `userjwt` and requires `Start_date` newer than `now - idleMinutes`. On success it sets `Start_date` to now.

Default idle is `(int) getenv('JWT_SECRET__CookieLoginExpires')` minutes. Values `<= 0` become 30.

### BR-009 — One user has a fixed 120-minute window

**Implemented.** `SessionLoginExpiry::EXTENDED_USER` is a single email compared case-insensitively. That user gets 120 minutes for both JWT `exp` and the idle check. The value is not read from the environment. The class comment says default users use the env setting.

The email is not repeated here. It is in that class. Approval of the exception is **Unknown / Requires Confirmation**.

### BR-010 — A token is valid only if that exact string is the user's current `JWT_token`

**Implemented.** Login replaces `JWT_token` on the existing `userjwt` row (matched by email). A previous token no longer matches. The filter does not accept a token that verifies cryptographically but is not the stored row.

Timezone for `Start_date` is set to `Africa/Johannesburg` in both login and the filter.

### BR-011 — Authenticated identity is a global

**Implemented.** `AuthFilter` assigns `global $Producer_details` from `userjwt.Producer_details`. `AuthFilterDataModel::getFilters` reads that global. A route that does not run the filter does not set it.

## Who can call what

### BR-012 — Most report and all sync routes are unauthenticated

**Documented** in `Routes.php`: "WARNING: no authFilter / CSRF — GET sync endpoints are destructive if publicly reachable. Restrict at web-server/IP level in prod, or add auth before exposing outside localhost."

**Implemented.** `authFilter` is attached only to:

- `auth/getCountryGroup`
- `auth/getFilters`
- `auth/getPriceRangeData`
- `auth/getPriceRangeFilterHistory`
- `auth/setPriceRangeFilterHistory`
- `auth/tables/*` (five posts)
- `auth/reports/getRSAExportsReport`

Charts, public tables, public filters, vineyard posts, grape posts, `loginAuth`, `sys/*`, `auth/dashboard`, `auth/excel_import_check`, and `auth/runDataSync*` do not use it.

CSRF is commented out in `app/Config/Filters.php`.

### BR-013 — CORS allows any origin and does not allow credentials

**Implemented.** `Access-Control-Allow-Origin` is `*`. The filter comment says credentials must not be combined with origin `*`.

## Export reporting

### BR-014 — The reporting window is a number of months ending at the selected month

**Implemented.** `ChartDataModel::calculateDateRanges`. `period` is a length. The start month is `period - 1` months before the end month. Both dates are forced to the first of the month. `period` in SQL is `YYYYMM`.

The same comment says a copy of this logic lives in the table model and should be kept in step. `TableDataModel::calculateDateRanges` exists.

### BR-015 — A future month in the latest data year is clamped

**Implemented.** If `model->year` equals the latest year in `export_data_qa` and `model->month` is greater than the latest month, the end date is the last day of that latest month, then moved to the first of the month.

### BR-016 — Package type PACKAGED means BOTTELS

**Implemented.** Filter lists expose `BULK` and `PACKAGED`. Where the query binds package type, `PACKAGED` is written as `BOTTELS`. `BULK` stays `BULK`. Chart SQL uses column flags `BU = 'X'` for bulk and `BO != 'X' OR BO IS NULL` for the packaged branch.

### BR-017 — Country list hides two aggregate labels

**Implemented.** Both filter models skip `EngelseLandBeskrywing` equal to `ALL COUNTRIES` or `UNITED KINGDOM total`. The source query is `country_group_lookup` where `YN = 'Y'`.

### BR-018 — Product and cultivar lists are limited to a fixed product-code set

**Implemented.** `getProductTypeFilterList` uses `prd_kde IN ('1', '2', '3', '3A', '3B', '3C', '3D', '4', '5', '6', '7', '8', '9', '9A', '9B', '9C', '9D', '10', '11', '11A', '11B', '36', '90')`.

`getCultivarFilterList` uses a different list: it includes `3A`–`3D` and `9A`–`9D` and `11A`/`11B`, and it does not include bare `'3'`, `'9'`, or `'11'`.

**Inferred.** Those two dropdowns can disagree for codes that appear in only one list. No comment explains the difference.

### BR-019 — "Industry" means the all-producer aggregate

**Implemented.** Table methods treat `producer_details` missing, empty, or first element `"Industry"` as the industry query. The RSA report then reads `export_data_dev`. A real producer list reads `export_data_dev_industry` and adds `prodname IN (...)`.

The authenticated filter list always inserts the literal `Industry` as the first producer option.

### BR-020 — Producer-specific tables require at least four distinct producer codes, and zero codes is allowed

**Documented** in `TableDataModel::findOrRun`: "There must be atleast 4 distinct producers" and "run queries directly to mapper data and check if the call is valid with atleast 4 different producers' data".

**Implemented** for export-market, exports, cultivar exports, and export-market volume: `COUNT(DISTINCT mp.PRODKODE) < 4` fails the check only when the count is not `0`. A count of `0` stays valid. Counts `1`, `2`, and `3` set `validated = 0`. The controller then returns `400` "Not enough unique producers for your call to get your specific producer info."

This check runs when `producer_details` is a non-industry list. It runs for both `TableDataApi` and `AuthTableDataApi`. The public route does not require a JWT before this path.

### BR-021 — Top 25 producer view requires every ranked group to have more than four producers

**Implemented.** For `GetTOP25TableData`, each `SAWISLANDNAAM` + `WHITE_OR_RED` group in the top 25 (by litres, inside the date window) contributes `AllowCall` 1 only when `COUNT(DISTINCT PRODKODE) > 4`. The sum of `AllowCall` must be at least 25, or at least the number of countries in `countryFilterList` when that filter is set. Otherwise the call is rejected with the same `400` message.

The threshold here is `> 4`. BR-020 fails when count `< 4`. A group of exactly 4 fails the top-25 check and passes the other table check. **Implemented.** No comment explains the off-by-one.

### BR-022 — RSA producer report requires more than three distinct producer names

**Documented** in `TableDataReportModel`: "industry check to see bussiness rule that if there are less than 4 producers, then return 0".

**Implemented.** `checkMoreThanThreeProducers` is true only when `COUNT(DISTINCT prodname)` from `export_data_dev_industry` is greater than 3. If a producer filter is set and any period fails, `getRSAExportsReport` returns `0` immediately, and the controller responds `400` with the same "Not enough unique producers" message.

The count is `prodname`, not `prodkode`.

### BR-023 — Two RSA periods produce a percent trend

**Implemented.** When two period windows are built, trend litres and ZAR, EUR, GBP, and USD are the percent change from the first window to the second. A zero base is handled in the grand-total branch as `"0%"`.

### BR-024 — Price band with end -1 is open-ended

**Implemented.** `getpriceRangesList` renders `price_range_end == '-1'` as `>= {price_range_start}`. Other bands render `{start} - {end}`.

### BR-025 — Price-range history is per email, and a semicolon truncates the email

**Implemented.** `setPriceRangeFilterHistory` uses the substring before the first `;` when one is present. Unknown email returns `403` "Email not found". The history string is written to `userjwt.Filter_history`.

### BR-026 — Price-range rows with zero litres are excluded

**Implemented.** The select adds `totale_liters != 0`.

## Vineyard and grape

### BR-027 — Vineyard queries exclude sultana and keep only cultivar codes starting with 0 or 4

**Documented** by the comment "Business rule logic" immediately above the second predicate in `VineyardStatusModel::setFilters`.

**Implemented.**

- If `$sultana` is false: `CULTIVAR_KODE NOT IN ('031')`.
- If `$sultana` is true: `CULTIVAR_KODE IN ('031')`.
- Always: `LEFT(CULTIVAR_KODE, 1) IN ('0', '4')`.

Every routed vineyard method uses the default `$sultana = false`. No route was found that requests sultana.

### BR-028 — Age is not a vineyard filter unless the query asks for it

**Implemented.** `setFilters` applies `AGE` only when the `$age` argument is `'yes'`. The public `setFilters` signature defaults `$age` to `'no'`.

### BR-029 — Vineyard and grape colour lists are WHITE and RED

**Implemented.** `getWhiteRed` on both models returns `['WHITE', 'RED']`. It does not read the database. Export product colours are a different list, from `product_type_lookup.white_or_red`.

### BR-030 — Blank vineyard and grape cells are shown as a dash or zero

**Documented** on `NormalizesEmptyReportValues`: "Old Single Vineyard rows can have blank text/number values. Text/string cells send "-"; number cells send 0 or 0.00."

**Implemented.** Null or blank string labels become `-` for `string` and `text` cells. Blank numbers become `0` when the cell key contains `VINES`, otherwise `0.00`. Applied to `tableBody` and `tableFooter`.

### BR-031 — Grape production without a year uses eleven seasons ending last calendar year

**Implemented.** `GrapeProductionModel::totalProduction`, when `year` is empty, sets the current year to `date('Y') - 1` and loops `i` from 10 down to 0. That is eleven years. Values are tonnes (`TONNE`), split into `RED_{year}`, `WHITE_{year}`, and `TOTAL_{year}`.

### BR-032 — New plantings are not read from the vineyard export table

**Implemented.** `newPlantings` queries `INFONEWPLANT_imported`. The other vineyard reports query `export_data_VineyardStatus`.

## Sync and privacy of rebuilds

### BR-033 — Monthly export sync replaces mapper rows from the oldest imported date forward

**Implemented.** `runDataSync` reads the oldest `mapperdata_imported.DATUM`, backs up, deletes `mapper_data` where `DATUM` is greater than or equal to that value, inserts every row of `mapperdata_imported`, then regenerates export QA, export dev, price ranges, and industry tables. Failure attempts a restore.

`max_execution_time` is set to 1500 for this method. The comment on that line says "300 seconds = 5 minutes", which does not match 1500.

### BR-034 — Pending industry sync is idempotent and can be a dry run

**Documented** on `ExportPendingIndustryCodes`: "Manual, idempotent import endpoint for missing industry prodkodes only. dry=1 or list=1 returns pending list without mutating data."

**Implemented.** Flags `dry` and `list` accept `1`, `true`, and `yes`, case-insensitive. Time limit is 600 seconds.

### BR-035 — Vineyard and grape dashboards describe four sync states

**Implemented.** Labels used by the dashboard script: `already_synced`, `new_years`, `needs_refresh` ("years overlap, data differs"), `no_import_data`. Anything else is shown as the raw status or `unknown`.

## Rules that are not enforced

| Starter or comment claim | What master does |
|---|---|
| README: authentication and authorization as product features | Login exists. Menu and role helpers are not loaded. No registration route |
| README: `php spark db:seed users` creates a default user | No `app/Database/Seeds` directory is in the repository |
| Commented global auth filter | Not active |
| `JwtLibrary` default secret `'secret'` | Login does not call this class. It uses `JWT_SECRET` from the environment and `Firebase\JWT` |

## Unknown / Requires Confirmation

- Business owner of the four-producer rule, and whether top-25 should use `> 4` while other tables use `< 4`.
- Whether a count of zero producers should return data.
- Whether `EttieneOverdrive` and the 120-minute user are still approved.
- Whether cultivar `031` must stay excluded.
- Whether the access flag from WebBoon should hide producers.
- Production policy for who may run sync URLs.