# Technical documentation — bitool_backend

Evidence date: 2026-10-19.

Source: `git@bitbucket.org:overdrive/bitool_backend.git`, `master` at `09437373` (2026-09-11, "Merged in bugfix/industry_specific_import (pull request #5)").

The task-creation snapshot `2ee38332` (2025-12-17, "Merge branch 'qa'") is an ancestor of this tip. This document describes the tip.

## Stack

| Item | Evidence |
|---|---|
| Language | PHP `^8.0` in `composer.json`. `README.md` says PHP 8.0 |
| Framework | CodeIgniter 4 (`codeigniter4/framework` `^4.0`). Front controller `public/index.php`. CLI `spark` |
| Package name | `codeigniter4/adminstarter`, description "Admin Overdrive", license MIT, homepage `https://www.overdrive.co.za` |
| Auth token library | `firebase/php-jwt` `^6.10`. Algorithm `HS256` in `UserLoginModel::loginAuth` |
| Database driver | MySQLi for `default` and `SAWISAPI`. SQLite3 memory for the `tests` group |
| Tests | PHPUnit `^10.5`. `phpunit.xml.dist` schema URL is PHPUnit 9.3 |
| Other Composer packages | `aws/aws-sdk-php`, `mpdf/mpdf`, `phpoffice/phpspreadsheet`, `orhanerday/open-ai`. No `use` of these under `app/` |

`README.md` extensions: `intl`, `libcurl` if HTTP\CURLRequest is used, `mbstring`, `mysqlnd`, `json`, `xml`.

## Repository layout

```text
app/                 controllers, models, filters, config, views
app/Config/Routes.php
app/Controllers/
app/Models/
app/Filters/         AuthFilter, Cors
app/Libraries/       SessionLoginExpiry, ReportFilterPayload, JwtLibrary
app/Helpers/         not registered in Autoload
app/Traits/NormalizesEmptyReportValues.php
app/Views/           welcome_message and error views only
public/index.php     web front controller
public/deploy.php    standalone deploy script
public/config.php    Marketing Cloud settings, unused by routed code
public/assets/       Bootstrap, DataTables, jQuery, pdfmake, logos
bitbucket-pipelines.yml
composer.json
env                  CodeIgniter env template, not a secret store to copy
phpunit.xml.dist
sonar-project.properties
sonarqube_scan.bat
spark
tests/               HealthTest plus CodeIgniter examples
writable/            cache, logs, session, uploads (index.html placeholders)
builds               empty path named by the task; no build scripts were in the tree inspected
```

There is no `app/Database/Migrations` or `app/Database/Seeds` directory. `Config\Database::$filesPath` still points at `app/Database/`.

`vendor/` is not in Git (`8018f6f`, "Stop tracking vendor"). Install with Composer. `README.md` says `composer update`.

Auto-routing is commented out in `Routes.php` (`setAutoRoute` is not enabled). Only explicit routes run. `App::$indexPage` is `index.php`.

## Request path

1. `public/index.php` boots CodeIgniter.
2. `Config\Filters` runs `cors` before and after every request, including unknown routes (`$required`).
3. `OPTIONS` returns `204` from `App\Filters\Cors` and does not need a matching route.
4. `Routes.php` dispatches to a controller.
5. Routes that set `authFilter` run `App\Filters\AuthFilter` first.
6. Several API controllers also call PHP `header()` for CORS and `die()` on `OPTIONS` at file scope, before the class is used. That is a second CORS implementation in front of the filter.

CSRF, debug toolbar, and honeypot aliases are commented out. `app.sessionDriver` in the `env` template is `CodeIgniter\Session\Handlers\DatabaseHandler`, cookie `ci_session`, match IP true. Login state for the API is the `userjwt` table, not that session.

## Databases

`app/Config/Database.php` defines three groups. Host, username, and password are literals in that file. They are not repeated here.

| Group | Driver | Database | Used by |
|---|---|---|---|
| `default` | MySQLi, port 3306, charset `utf8` | `sawis_bi` | `db_connect('default')` and `Database::connect()` with no argument. This is the application database |
| `SAWISAPI` | MySQLi, same host and user | `sawis_api` | No `db_connect('SAWISAPI')` under `app/` |
| `tests` | SQLite3 `:memory:`, prefix `db_` | in-memory | Selected when `ENVIRONMENT === 'testing'` |

`DBDebug` is true when `ENVIRONMENT` is not `production`.

The committed `env` template sets `database.default.*` to `localhost` / `adminoverdrive` / `root` and an empty password, and sets `CI_ENVIRONMENT = development`. CodeIgniter can overlay `.env` onto config. Which values a server actually uses is **Unknown / Requires Confirmation**. The PHP file and the env template disagree.

### Tables the application SQL names

Reporting and auth tables in `sawis_bi` (names as written in queries):

| Table | Role |
|---|---|
| `userjwt` | `User_email`, `JWT_token`, `Start_date`, `Producer_details`, `Filter_history`, `ID` |
| `export_data_qa` | Public charts and tables. Columns used include `year`, `month`, `period`, `totale_liters`, `white_or_red`, `description_for_reports`, `BU`, `BO` |
| `export_data_dev` | Industry aggregate for the RSA report |
| `export_data_dev_industry` | Per-producer export rows. `prodkode`, `prodname` |
| `export_data_price_ranges` | Price-band facts |
| `price_ranges` | Band definitions: `currency`, `packaging`, `price_range_start`, `price_range_end` |
| `mapper_data` | Live mapped levy/export rows (`PRODKODE`, `PRODUSENTNAAM`, `DATUM`, `TOTALELITERS`, and joined codes) |
| `mapperdata_imported` | Staging copy used by monthly sync |
| `country_group_lookup` | Country labels. `YN = 'Y'` includes a country. `LND_KDE` joins mapper data |
| `product_type_lookup` | `prd_kde` / `PRD_KDE`, `white_or_red`, `` `DESCRIPTION FOR REPORTS` `` |
| `export_data_VineyardStatus` | Vineyard report facts |
| `INFONEWPLANT_imported` | New-plantings facts |
| `INFOBLOCKS` | Vineyard year list (`INFOYRREPORT`) |
| `regions` | `VOLLEBESKRYWING` |
| `Reports` | Report catalogue. `Type` is `VineyardStatus` or `GrapeProduction`. `Order` sorts them |
| `export_data_GrapeProduction` | Grape tonnes. `YEAR`, `REGION`, `TONNE`, `WHITE_RED`, `CULTIVAR` |
| `industries` | Read by `createIndustryImport` (`IndustryProdKode`) |
| `uploaded_file` | `UploadedFileModel` only. No routed caller |
| Backup tables | Created by `createBackupsForDataSync*` and restored on failure. Names are in those methods |

Column types, indexes, and the procedure that fills `mapperdata_imported` are not in Git.

Sync methods run `SET SESSION sql_mode` to strip `ONLY_FULL_GROUP_BY` before grouped selects.

## HTTP API

Base path is the site root plus optional `index.php`, because `indexPage` is `index.php` and `baseURL` in `App.php` is `''`.

### Unauthenticated

| Method | Path | Controller |
|---|---|---|
| GET | `/` | `Home::index` |
| POST | `getTop25Data` | `ChartDataApi::getTop25Data` |
| POST | `getChartData` | `ChartDataApi::getChartData` |
| POST | `getCultivarChartData` | `ChartDataApi::getCultivarChartData` |
| POST | `getYearOverYearComparison` | `ChartDataApi::getYearOverYearComparison` |
| POST | `getTotale_Liters` | `ChartDataApi::getTotale_Liters` |
| POST | `getExports` | `ChartDataApi::getExports` |
| POST | `getTotale_LitersData` | `ChartDataApi::getTotale_LitersData` |
| GET | `getFilters` | `FilterDataApi::getFilters` |
| POST | `loginAuth` | `LoginController::loginAuth` |
| GET | `sys/ExportDataTo__Data_export_dev_industry` | `ImportData::ExportDataTo__Data_export_dev_industry` |
| GET | `sys/ExportPendingIndustryCodes` | `ImportData::ExportPendingIndustryCodes` |
| GET | `sys/createIndustryImport` | `LoginController::createIndustryImport` |
| POST | `tables/getExportMarketPerProductTypeTableData` | `TableDataApi` |
| POST | `tables/getExportsTableData` | `TableDataApi` |
| POST | `tables/getCultivarTypeExportsTableData` | `TableDataApi` |
| POST | `tables/getExportMarketVolumeByProductTypeTableData` | `TableDataApi` |
| POST | `tables/getTOP25TableData` | `TableDataApi` |
| GET | `getVineyardStatusFilters` | `VineyardStatus` |
| GET | `getVineyardStatusReports` | `VineyardStatus` |
| POST | `vineyardStatus/totalHectares` | and `hectares`, `ageDistribution`, `status`, `ranking`, `uprootings`, `newPlantings`, `netChange`, `irrigation`, `trellising` |
| GET | `vineyardStatus/testReports/(:any)/(:any)` | `VineyardStatus::testReportWithAllFilters` |
| GET | `vineyardStatus/test_url_phase_4` | `ImportData::test_url_phase_4` |
| GET | `getGrapeProductionFilters` | `GrapeProduction` |
| GET | `getGrapeProductionReports` | `GrapeProduction` |
| POST | `grapeProduction/totalProduction` | `GrapeProduction` |
| POST | `grapeProduction/productionPerRegionAndCultivar` | `GrapeProduction` |
| GET | `grapeProduction/testReports/(:any)/(:any)` | `GrapeProduction::testReportWithAllFilters` |

### Group `auth`

These have **no** `authFilter`:

| Method | Path | Controller |
|---|---|---|
| GET | `auth/excel_import_check` | `ImportData::testing_query` |
| GET | `auth/dashboard` | `ImportData::dashboard` |
| GET | `auth/runDataSync` | `ImportData::runDataSync` |
| GET | `auth/runDataSync_VineyardStatus` | `ImportData::runDataSync_VineyardStatus` |
| GET | `auth/runDataSync_GrapeProduction` | `ImportData::runDataSync_GrapeProduction` |

These set `authFilter`:

| Method | Path | Controller |
|---|---|---|
| GET | `auth/getCountryGroup` | `AuthFilterDataApi::getGroupedCountryFilters` |
| GET | `auth/getFilters` | `AuthFilterDataApi::getFilters` |
| POST | `auth/getPriceRangeData` | `priceRangeController::getPriceRangeData` |
| POST | `auth/getPriceRangeFilterHistory` | `priceRangeController::getPriceRangeFilterHistory` |
| POST | `auth/setPriceRangeFilterHistory` | `priceRangeController::setPriceRangeFilterHistory` |
| POST | `auth/tables/getExportMarketPerProductTypeTableData` | `AuthTableDataApi` |
| POST | `auth/tables/getExportsTableData` | `AuthTableDataApi` |
| POST | `auth/tables/getCultivarTypeExportsTableData` | `AuthTableDataApi` |
| POST | `auth/tables/getExportMarketVolumeByProductTypeTableData` | `AuthTableDataApi` |
| POST | `auth/tables/getTOP25TableData` | `AuthTableDataApi` |
| POST | `auth/reports/getRSAExportsReport` | `AuthTableDataApi::getRSAExportsReport` |

Environment-specific routes are loaded when `app/Config/{ENVIRONMENT}/Routes.php` exists. That file is not in the repository.

### Typical payloads

Login JSON: `email`, `password`, `typeOfLogin`. Query-string fallback uses the same names.

Chart and export-table JSON uses `year`, `month`, `period`, and filter lists (`productTypeFilterList`, `cultivarFilterList`, `countryFilterList`, `packageTypeFilterList`, `comparisonYear`, `producer_details`). Property names inside list items are read as both object style (`whiteOrRed`, `descriptionForReports`, `engelseLandBeskrywing`, `packageType`) in the industry path.

Vineyard and grape POST bodies must be non-empty JSON. Fields: `year`, `region`, `cultivar`, `white/red`, and `age` for vineyard when the query enables it. Arrays and scalars are both accepted by `setFilters`.

`ReportFilterPayload::fromRequest` throws `InvalidArgumentException` with "A non-empty JSON filter object is required." Controllers catch `\Exception`. `InvalidArgumentException` extends `\Exception`, so the message is returned as `400`.

### Auth header

`AuthFilter` reads `apache_request_headers()` and, if needed, `Authorization` from the request. A leading `Bearer ` is stripped. The remainder must equal `userjwt.JWT_token`.

JWT payload claims written at login: `iss` "Issuer of the JWT", `aud` "Audience that the JWT", `sub` "Subject of the JWT", `iat`, `exp`, `email`. Signing key is `getenv('JWT_SECRET')`.

Failure body: `{ "message": "..." }` with status `401`.

## Environment variables the PHP code reads

| Name | Where | Effect |
|---|---|---|
| `WebBoon_API_TOKEN` | `UserLoginModel::checkLoginDetails` | Sent as `token` to WebBoon |
| `JWT_SECRET` | `loginAuth` | HMAC key for `JWT::encode` |
| `JWT_SECRET__CookieLoginExpires` | `SessionLoginExpiry` | Idle and JWT lifetime in minutes. `<= 0` becomes 30 |

These are not given values in the committed `env` template. A deployment `.env` was not in Git.

The `env` template also sets an `encryption.key`. That value is not copied here.

## External integrations

### WebBoon

`POST https://www.sawis.co.za/login.bisswo.php`

Headers: `Accept: application/json`, `Content-Type: application/json`.

Body: `token`, `username` (the email), `password`.

Transport: `file_get_contents` with an HTTP stream context. No timeout is set in that call.

Success shape used by the code: JSON property `bi_tool`, optionally `bi_tool.prodkodes` as a map of code to access.

### Email

`app/Config/Email.php`: protocol `smtp`, host `smtp.overdriveqa.co.za`, port `587`, SMTP user `admin@overdriveqa.co.za`. The password is in that file and is not copied here.

`fromEmail` and `fromName` are declared and not assigned in the config class. The industry mailer uses them when `fromEmail` is non-empty, otherwise it still sends. Default from-name in the mailer is `SAWIS BI`.

### Deploy and Slack

`public/deploy.php` is not routed by CodeIgniter. It requires query parameter `tkn` to match a token literal, and the client IP to fall in an allowlist of CIDR ranges. Failure posts to a Slack webhook and prints `Nope`. The project string is `SAWIS: BIQA`.

The token, webhook, and bot token are in that file. They are not copied here.

### Marketing Cloud file

`public/config.php` returns `appsignature`, `clientid`, `clientsecret`, and `defaultwsdl` `https://buildingtomorrow.soap.marketingcloudapis.com/etframework.wsdl`. Nothing under `app/` references this file. The client secret is not copied here.

### Spreadsheet check

`ImportData::test_url_phase_4` defaults to a published Google Sheets CSV URL and type `vineyard_status`. The dashboard form lets the operator replace the URL.

## Data sync

No cron, queue, or worker class exists.

| Entry | Time limit set in code | What it changes |
|---|---|---|
| `runDataSync` | 1500 seconds | Backup, delete/insert `mapper_data` from `mapperdata_imported`, rebuild QA, dev, price-range, and industry export tables. Restore on failure |
| `runDataSync_VineyardStatus` | Set inside that method | Vineyard import versus `INFOBLOCKS` and `export_data_VineyardStatus` |
| `runDataSync_GrapeProduction` | Set inside that method | Grape import versus live `PressRegister` (named on the dashboard) and `export_data_GrapeProduction` |
| `ExportPendingIndustryCodes` | 600 seconds | Inserts missing producer codes into the industry export, unless `dry` or `list` is set |
| `regenerateDataTables` | 600 seconds | Calls the four export builders. Returns the section name on failure or `"SUCCESS"` |

Backup and restore methods: `createBackupsForDataSync`, `restoreBackupsForDataSync`, and the `_GrapeProduction` and `_VineyardStatus` variants.

The dashboard is a single HTML string echoed from `ImportData::dashboard`, with jQuery loaded from `ajax.googleapis.com`.

## Auth implementation notes

`SessionLoginExpiry` is the only idle policy. `JwtLibrary` is a separate HMAC implementation with default secret `'secret'`. Routed login does not use it.

`AuthFilter` does not verify the JWT signature or the `exp` claim. Possession of the stored token string is enough, until `Start_date` falls outside the idle window.

Filter history and producer JSON are written with SQL string concatenation, as are report filters (`year`, `region`, `cultivar`, names, codes). `COTE D'IVOIRE` is specially escaped in the price-range model only.

`loginAuth` can put a caught `Throwable` into the JSON `token` field.

## Security findings (no secret values)

These are facts about the tree, for human review. They are not instructions.

| Location | Fact |
|---|---|
| `app/Config/Database.php` | Production-looking username and password for `sawis_bi` and `sawis_api` are committed |
| `app/Config/Email.php` | SMTP password is committed |
| `public/deploy.php` | Deploy token, Slack bot token, and Slack webhook are committed |
| `public/config.php` | Marketing Cloud client id and client secret are committed |
| `env` | An `encryption.key` hex value is committed in the template |
| `Routes.php` | Destructive sync GETs and `sys/*` imports have no application auth |
| Report models | Request text is concatenated into SQL |
| `AuthFilter` | Does not check JWT signature |
| CORS | `Allow-Origin: *` on every response |
| `loginAuth` | Passwords are accepted on the query string when the JSON body is null |

**Unknown / Requires Confirmation:** whether those credentials are still valid, and whether they have been rotated since this commit.

## Deployment and CI

`bitbucket-pipelines.yml`:

- Image `maven:3.3.9`.
- Clone depth `full`.
- On branch `master` only: pipe `sonarsource/sonarqube-scan:2.0.1` with `SONAR_HOST_URL` and `SONAR_TOKEN` from repository variables.
- Cache `~/.sonar`.
- The header comment describes an FTP push on every commit. The YAML does not.

`sonar-project.properties` sets `sonar.projectKey=overdrive_bitool_backend_341636ae-e1b5-4192-8790-74adfa3f49a7`.

`sonarqube_scan.bat` is present for a local scan. Its host URL was not treated as a product requirement.

No Dockerfile, no `app/Config/Boot` overrides beyond the stock `development.php`, `production.php`, and `testing.php`, and no infrastructure-as-code directory.

`README.md` local setup: copy `env` to `.env`, `php spark db:create`, `php spark migrate`, `php spark db:seed users`, `php spark key:generate`, `php spark serve`. Migrate and seed have no migration or seed files in this tree, so those commands have nothing to run. An Apache vhost example uses the name `adminOverdrive.local`.

## Frontend assets

`public/assets` contains Bootstrap 5, DataTables, jQuery, pdfmake, JSZip, Font Awesome, and logo images. The sync dashboard does not use this bundle. It loads jQuery from Google. The welcome view is the framework page. No report SPA is compiled from this repository.

## Testing

`phpunit.xml.dist`:

- Bootstrap: `vendor/codeigniter4/framework/system/Test/bootstrap.php`.
- Suite `App` includes `./tests`.
- Coverage includes `./app` and excludes `./app/Views` and `app/Config/Routes.php`.
- `app.baseURL` for tests is `http://example.com/`.
- Test database env vars are commented out. The `tests` connection is in-memory SQLite.

`tests/unit/HealthTest.php` asserts `APPPATH` is defined and that either `.env` contains `app.baseURL` or `Config\App` has a non-empty `baseURL`. `App::$baseURL` is `''`, so this test passes only when a `.env` sets `app.baseURL`.

`tests/session/ExampleSessionTest.php`, `tests/database/ExampleDatabaseTest.php`, and `tests/_support/*` are the framework samples (`ExampleModel`, example migration, `ExampleSeeder`).

No test calls a report, login, or sync method.

Composer script `test` runs `phpunit`.

## Logging and monitoring

CodeIgniter logging is configured in `app/Config/Logger.php` (framework default file). Industry-mail failures call `log_message('error', ...)`.

SonarQube is the only monitoring integration in the pipeline. There is no application metrics exporter, health route, or queue dashboard. The operator dashboard is the sync UI.

## Background work, queues, storage

No queue worker, no cron definition, no Redis configuration.

Writable directories under `writable/` are `cache`, `logs`, `session`, and `uploads`, each with an `index.html`. Cache is what the login lockout and the industry-email dedupe use (`Config\Services::cache()`). The cache handler class is the framework default unless `.env` changes it. **Unknown** which handler production uses.

`UploadedFileModel` writes `uploaded_file` rows. No routed controller calls it.

## Related code that this branch does not run

Documented so later diffs are easier to read:

- Branch `phase6` of this same remote is documented in Silicon Ark as `bi_tool_backend_phase6/`. It is not this tree.
- `ChatGPT`, `DbAdmin`, `ApiSimulation`, `JwtLibrary`, `AppSessionModel`, and the helpers for menus, Africa's Talking, and ChatGPT are starter leftovers and are not routed.
- `app/Middleware/CORS.php` is not the class registered in `Filters.php`.