# Project plan — bitool_backend

Evidence date: 2026-10-19.

Source repository: `git@bitbucket.org:overdrive/bitool_backend.git`.

This plan describes default branch `master` at `094373732854a4c45c3c47faea53787877c287ed` (2026-09-11, "Merged in bugfix/industry_specific_import (pull request #5)").

The task-creation snapshot named `2ee38332` (2025-12-17, "Merge branch 'qa'"). That commit is in the history of this `master` tip (`2ee3833`). Behaviour below is the later tip, not that snapshot.

The local scan path `~/projects/sonar_scan/repos/bitool_backend` was not on this machine. Evidence is a read-only clone of the remote. The source repository was not modified.

Silicon Ark had no `bitool_backend/` directory before this set. `README.md` in the source is the Admin Overdrive starter and does not describe the SAWIS reports that the code implements.

## Related Silicon Ark sets

Same Git remote, different branch:

- `bi_tool_backend_phase6/` documents branch `phase6` at `717166ec` (2026-08-07). That set covers SAWIS 7 domestic capture, levy workbooks, and `userjwt.admin_role`. Those types and routes are not in the `master` tree inspected here. Whether `phase6` is deployed, and whether it should replace `master`, is **Unknown / Requires Confirmation**.

Other repositories that share the SAWIS BI name:

- `bitool_frontend/` and `bi_tool_frontend_phase6/` document `bitool_frontend`. They call paths with the same names as routes in this API. They are separate repositories.
- `bitool_dotnet/` documents `bitool_dotnet`. Its `develop` branch is an older SAWIS report API and page. It is not this repository.
- `bi_tool_php/` documents PHP scripts that write a database named `sawis_bitool`. This application connects to `sawis_bi` and `sawis_api`.

## Product purpose

**Inferred from implementation.** This is the HTTP API and operator sync console for SAWIS BI export, vineyard, and grape-production reports.

**Documented in code labels, not in a product spec:** dashboard title "SAWIS BI Auth Dashboard" (`app/Controllers/ImportData.php`), default mail from-name "SAWIS BI" (`app/Models/UserLoginModel.php`), deploy script project label "SAWIS: BIQA" (`public/deploy.php`).

`README.md` and `composer.json` still say "Admin Overdrive" / `codeigniter4/adminstarter`. That text is the starter kit. It is not what the routed controllers do.

## Scope

In this `master` tree:

- Login against the SAWIS WebBoon endpoint, then a JWT stored in MySQL.
- Unauthenticated export charts and tables from `export_data_qa`.
- Authenticated filter lists, price ranges, export tables, and an RSA exports report that can be industry-wide or producer-specific.
- Unauthenticated vineyard-status and grape-production report APIs.
- Operator HTTP endpoints that rebuild export tables from imported source tables, including vineyard and grape syncs.
- A small HTML dashboard for those syncs and for checking a public spreadsheet URL.

Not in this `master` tree:

- The interactive report UI. No report screens are rendered except the CodeIgniter welcome page, the sync dashboard, and error views.
- SAWIS 7 domestic capture and levy Excel flows documented for branch `phase6`.
- Database migrations. `app/Database/` is not in the repository. Schema lives in MySQL.
- A written milestone plan. `README.md` links to a Jira board and a Confluence space. Those sites were not read.

## System architecture

One CodeIgniter 4 PHP application. The browser or another service calls `public/index.php`. Controllers call models. Models run SQL against MySQL database `sawis_bi` through the `default` connection in `app/Config/Database.php`. A second connection, `SAWISAPI`, points at database `sawis_api` on the same host. No model inspected for this set calls `db_connect('SAWISAPI')`.

```text
Caller (frontend or operator browser)
        │  HTTP
        ▼
public/index.php  →  Routes.php  →  Filters (CORS, and authFilter on some routes)
        │
        ▼
Controllers  →  Models  →  MySQL sawis_bi
        │
        ├── WebBoon login  https://www.sawis.co.za/login.bisswo.php
        └── SMTP mail for missing industry producer codes
```

There is no queue, scheduler, or background worker in the repository. Long syncs run inside the HTTP request and raise `max_execution_time`.

## Major components

| Component | Role | Evidence |
|---|---|---|
| `LoginController` / `UserLoginModel` | WebBoon login, JWT, `userjwt` row, missing-industry mail | Implemented |
| `AuthFilter` | Bearer token must match `userjwt.JWT_token` and a sliding idle window | Implemented on selected `auth/*` routes only |
| `ChartDataApi` / `ChartDataModel` | Export litre charts from `export_data_qa` | Implemented, no auth filter |
| `FilterDataApi` / `AuthFilterDataApi` | Filter dropdown data. The auth variant adds price ranges and producer names | Implemented |
| `TableDataApi` / `AuthTableDataApi` | Export tables. Auth variant can return producer columns and the RSA report | Implemented |
| `priceRangeController` / `PriceRangeModel` | Price-band aggregates and per-user filter history | Implemented behind `authFilter` |
| `VineyardStatus` / `VineyardStatusModel` | Hectares, vines, age, status, ranking, uprootings, plantings, irrigation, trellising | Implemented, no auth filter |
| `GrapeProduction` / `GrapeProductionModel` | Tonnes by region and cultivar | Implemented, no auth filter |
| `ImportData` | Monthly export rebuild, vineyard sync, grape sync, pending industry import, operator dashboard | Implemented. Sync GETs are not behind `authFilter` |
| `public/deploy.php` | Token and IP gate, then a deploy action that notifies Slack | Implemented outside CodeIgniter routing |

Starter controllers `ChatGPT`, `DbAdmin`, and `ApiSimulation` have no entries in `app/Config/Routes.php`. Auto-routing is commented out, so those classes are not reachable through the router.

## Dependencies

From `composer.json`:

- PHP `^8.0`
- `codeigniter4/framework` `^4.0`
- `aws/aws-sdk-php` `^3.254`
- `mpdf/mpdf` `^8.1`
- `phpoffice/phpspreadsheet` `^1.25`
- `orhanerday/open-ai` `^3.5`
- `firebase/php-jwt` `^6.10`

Dev: `fakerphp/faker`, `mikey179/vfsstream`, `phpunit/phpunit` `^10.5`.

JWT login uses `firebase/php-jwt`. No `use` of the AWS SDK, mPDF, PhpSpreadsheet, or the OpenAI client was found under `app/`. Those packages match the starter `README.md` and are unused by the routed SAWIS code. **Inferred.**

Runtime services the code calls:

- MySQL `sawis_bi` (and a configured, unused `sawis_api` connection).
- `https://www.sawis.co.za/login.bisswo.php` when `typeOfLogin` is `"1"`.
- SMTP host `smtp.overdriveqa.co.za` from `app/Config/Email.php`.
- Slack, from `public/deploy.php` only.
- A Google Sheets CSV URL is the default argument of `ImportData::test_url_phase_4`. The dashboard asks the operator to paste a public sheet URL.

## Current status

**Implemented** on `master` as of 2026-09-11: export charts and tables, authenticated producer-specific tables, price ranges, vineyard status, grape production, login, and manual data sync.

**Operational, not scheduled:** industry rows are not built at login. Commit `5fb8861` ("Stop industry ETL on login; pending sync, notify, monthly catch-up.") matches `UserLoginModel` comments. An operator calls `GET sys/ExportPendingIndustryCodes`.

**Pipeline:** `bitbucket-pipelines.yml` runs a SonarQube scan on branch `master` only. The file header still describes an FTP push. The YAML does not implement FTP.

**Tests:** `tests/unit/HealthTest.php` checks that `APPPATH` is defined and that a base URL is set. The other tests are CodeIgniter examples. They do not cover reports, login, or sync.

**Secrets in Git:** database credentials, SMTP credentials, a deploy token, a Slack token, a Marketing Cloud client secret, and an encryption key are committed. Values are not copied into this document. See `technical-documentation.md`.

## Visible history

`git fetch --deepen=40` on 2026-10-19. This is not the full history. Oldest commit in that window: `36b6322` "fix final".

| Commit | Subject | What it indicates |
|---|---|---|
| `2ee3833` | Merge branch `qa` | Task-creation snapshot. 2025-12-17 |
| `0020cdd` | init for vineyardstatus | Vineyard status added after that snapshot |
| `e5da194` | fixed trellis and grape production data | Grape production and trellising in the same window |
| `8018f6f` | Stop tracking vendor (align with qa) | `vendor/` no longer committed |
| `dd3e408` | Vuln and sec fixes and ensuring pages work | Security work. Details are the diff, not a written spec |
| `5fb8861` | Stop industry ETL on login; pending sync, notify, monthly catch-up | Login no longer imports industry rows |
| `0943737` | Merged in `bugfix/industry_specific_import` (pull request #5) | Current `master` tip |

There is no authored roadmap in the repository. `README.md` "Development TODOS" is only the Jira URL `https://siliconsoftware.atlassian.net/jira/software/projects/SI/boards/12`.

## Implementation plan

No future milestone is written in this branch. The repeating operator procedure that the code implements is:

1. Source rows land in import tables (`mapperdata_imported`, vineyard import tables, grape import tables). How they get there is **Unknown / Requires Confirmation**. This repository does not show a loader for those tables except SQL that reads them.
2. An operator opens `GET auth/dashboard` and runs monthly export sync, vineyard sync, or grape sync.
3. On login, if a user's producer codes are missing from `export_data_dev_industry`, the app emails a mailbox once per user and missing-code set. An operator then calls the pending-industry import.

## Unknown / Requires Confirmation

- Which branch is production: `master`, `qa`, or `phase6`.
- The public base URL. `app/Config/App.php` sets `baseURL` to an empty string.
- Whether a `.env` on the server overrides `app/Config/Database.php`.
- Who is allowed to call the unauthenticated sync and report routes, and whether the web server restricts them.
- Whether `bitool_frontend` still calls this `master` API.
- The Jira and Confluence pages linked from `README.md` were not opened.