# Project plan — bi_tool_backend_phase6 (SAWIS BI backend)

Evidence date: 2026-10-19.

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

Inspected revision: branch `phase6`, commit `717166ec` (2026-08-07, “Implement Gareth review schema hardening on Phase 6.”, author Ettiene).

This plan describes what the repository contains. It does not decide what the product should become.

The scan-host path named at task creation (`~/projects/sonar_scan/repos/bi_tool_backend_phase6`) was not present on the machine used for this onboarding. Evidence is a read-only clone of `phase6` at the commit above. The clone was `--single-branch --depth 1`, so other branches and older history were not compared.

## Product

The backend is a CodeIgniter 4 API for the South African wine industry information body SAWIS. It does three jobs that are all in this one repository:

1. Serve export, vineyard-status, and grape-production analytics (charts, tables, filters, RSA exports report, price ranges).
2. Run the Phase 5 levy-declaration mapper: upload a DAF Excel file, transform it, generate result workbooks, and email them.
3. Run Phase 6 domestic SAWIS 7 capture: producers enter a monthly return, Super Users approve it, a system job marks it synced after an external “Selwin” step, and domestic BI reports read the resulting data.

Provenance: **Documented** in `docs/general/SAWIS_BI_Live_Documentation.md` (analytics and Phase 5, last major update stated as 2026-06) and in `docs/phase 6/README.md` plus `docs/phase 6/SAWIS_BI_Phase_6_SAWIS_7_Domestic_Capture_v1.7.html` (Phase 6). **Implemented** in `app/Config/Routes.php` and the controllers named in the technical documentation.

The root `README.md` still titles the project “Admin Overdrive Starter (SAWIS BI Tool Backend)” and lists starter-kit features (user registration, menu management with auto-created controllers). No `register` route exists in `app/Config/Routes.php`. Treat the starter feature list as leftover template text, not as the current product.

Composer package name remains `codeigniter4/adminstarter`. Description: “Admin Overdrive”. Homepage: `https://www.overdrive.co.za`.

## Branch reality

| Item | Evidence |
|---|---|
| Remote | `git@bitbucket.org:overdrive/bitool_backend.git` |
| Branch inspected | `phase6` at `717166ec` |
| Other branches | **Unknown.** This inspection did not fetch `master` or other lines |
| Deployed branch | **Unknown / Requires Confirmation** |
| Pipeline | `bitbucket-pipelines.yml` runs SonarQube only on branch pattern `{master}`. `phase6` is not in that pipeline file |

## Scope in the repository

In scope of what is implemented on `phase6` at `717166ec`:

- Public and authenticated analytics APIs for exports, vineyard status, and grape production.
- Login against `userjwt`, with optional AES-256-CBC login payload and a per-IP failed-login lockout.
- Admin SAWIS levy pipeline (steps 0–4), final-sheet comparison, unknown-match CRUD, MCC designation lookup, email log, process history.
- Monthly/yearly style data sync endpoints that refresh `mapper_data` and `export_*` tables from an import window.
- Phase 6 domestic capture, lookups, admin, reports, audit, and unauthenticated `sys/domestic/*` job endpoints.
- Phase 6 schema as SQL scripts under `docs/phase 6/` (not as CodeIgniter migration classes).
- In-repo docs, Postman collection `postman/SAWIS BI.postman_collection.json`, PHPUnit stubs, and a generated TestSprite suite under `testsprite_tests/`.

Out of scope of this repository, or not present as committed code:

- The React (or other) frontend that calls these APIs. Phase 6 plan `docs/phase 6/BACKEND_NEXT_STEPS_PLAN.md` (2026-08-06) says the React capture/admin UI was not started in that plan.
- A committed Spark command class for `process:mapper-queue`. `AdminController` spawns that CLI name, but no `app/Commands` directory and no `BaseCommand` subclass exist in this tree.
- `Phase6Testing/tests`, which `phpunit.xml.dist` and `composer.json` autoload, is not in the tree.
- CodeIgniter migration files. `app/Database/Migrations/` contains only `.gitkeep`. Changelog entries refer to migrations (email log, 2026-06-04 test-upload columns) that are not in this revision.
- Native mobile apps. None were found.

## System architecture

```text
Browser / React client (not in this repo)
  → CodeIgniter 4 (public/ + app/)
      → CORS on every request (Allow-Origin *)
      → authFilter / adminFilter where a route sets them
      → Controllers → Models / Libraries
      → MySQL database sawis_bi (default group)
      → optional second group SAWISAPI → database sawis_api
  → SMTP email (levy pipeline, domestic help, overdue cancel)
  → Background PHP process: spark process:mapper-queue <id>
      (spawned by AdminController; command class not in the tree)
  → PhpSpreadsheet for large Excel
  → OpenAI completions helper (ChatGPT controller exists; no route wired)
  → AWS SDK is a Composer dependency; call sites were not found in app/
```

External parties named in code and docs:

| Party | Role in this repo |
|---|---|
| SAWIS | Industry client. Producer and Super User users. Spec sign-off for Phase 6 |
| Selwin | Named as the external process that consumes `domestic_sawis_7_data_approved` and, once that staging table is empty, allows BR-14 mark-synced. Implementation of Selwin is not in this repo |
| Overdrive | Development. Pipeline error mail is copied to `ettiene@overdrive.co.za` in the levy flow |

## Major components

| Component | Location | Role |
|---|---|---|
| Routes | `app/Config/Routes.php` | Public analytics, `auth` group, `admin` group, `sys/domestic/*` |
| Export charts | `ChartDataApi`, `ChartDataModel` | Top 25, white/red, cultivar, year-over-year, litres, exports |
| Export tables | `TableDataApi`, `AuthTableDataApi` | Market, cultivar, volume, TOP 25; RSA exports report on the auth copy |
| Filters | `FilterDataApi`, `AuthFilterDataApi` | Public `GET /getFilters` and `GET /auth/getFilters` |
| Vineyard status | `VineyardStatus` | Hectares, age, status, ranking, plantings, irrigation, trellising |
| Grape production | `GrapeProduction` | Total production and production per region and cultivar |
| Price ranges | `priceRangeController` | Authenticated price-range data and filter history |
| Data sync | `ImportData` | `runDataSync`, vineyard sync, grape-production sync |
| Levy mapper | `AdminController`, `AdminModel` | Queue, steps 0–4, final-sheet test, downloads, history |
| Login | `LoginController`, `UserLoginModel` | `POST /loginAuth` |
| Domestic capture | `DomesticCaptureController`, `DomesticSubmissionModel` | Open, lines, saved totals, reconcile, submit |
| Domestic admin | `DomesticAdminController` | Dashboard, users, approve, unlock, audit |
| Domestic lookups | `DomesticLookupController` | Container, cultivar, price, country, producer lookups |
| Domestic reports | `DomesticReportController`, `DomesticReportModel`, `DomesticReportEngine` | `getDomesticReport` |
| Domestic jobs | `DomesticSystemController`, `DomesticSubmissionCron` | Create headers, cancel overdue, mark synced, producer list |
| Authz | `AuthFilter`, `AdminFilter`, `DomesticAuth` | Session window on `userjwt`, role gates |
| Email | `mail_helper`, `sawis_email_html_helper`, `EmailLogger` | SMTP plus `email_log` |
| Phase 6 schema | `docs/phase 6/*.sql` | Create/alter/seed domestic tables |

## Phases and milestones

These phases are how the repository’s own docs organise the work. They are not a formal release calendar.

| Phase | What the repo says | Status on `phase6` at `717166ec` |
|---|---|---|
| Analytics / earlier BI | Live doc: public and auth chart, table, filter, vineyard, and grape APIs are operational | **Implemented** routes and models exist. Live doc (2026-06) says the site is working in full. Whether production data is current is **Unknown** |
| Phase 4 | `docs/phase-4/GRAPE_PRODUCTION_TESTING_README.md` — grape-production filter combination testing | **Implemented** grape and vineyard routes exist. The testing README also names `GET /grapeProduction/quickTest` and `testAllFilters`; those paths are not in `Routes.php` (the routed test paths are `grapeProduction/testReports/(:any)/(:any)` and `vineyardStatus/test_url_phase_4`). **Requires Confirmation** whether the extra test URLs were removed |
| Phase 5 | Levy declaration mapper. Documented in `adminDetails.md` and the live doc | **Implemented.** Changelog through 2026-06-05. Background CLI command class is missing from the tree (see discrepancies) |
| Phase 6 | SAWIS 7 domestic capture. Spec HTML v1.7 is called the sign-off pack. `REVISION_saved_totals_v2_and_onboarding.md` (2026-07) is the delta for hierarchical totals, country on the header, and onboarding text | **Implemented as APIs and SQL**, not proven end-to-end in production by this inspection. The 2026-08-06 plan estimated backend roughly 60–70% and recorded a local database with historic rows in `domestic_sawis_7_data_imported` (~496 000) and zero rows in working submissions, working data, approved, and audit. That row count is a point-in-time note in the plan, not a measurement taken on 2026-10-19 |
| Phase 6 schema hardening | Commit `717166ec` and `alter_gareth_schema_hardening.sql` | SQL and PHP for `domestic_user_producer_access` are in the tree (junction plus CSV mirror on `userjwt`). The 2026-08-06 plan had listed a junction table as deferred. The next day’s commit implemented it |

### Phase 6 sub-areas (from the 2026-08-06 plan)

| Sub-phase | Plan’s own estimate on 2026-08-06 | What this inspection can confirm in code |
|---|---|---|
| 6.1 Auth, users, producer sync, crons | ~75–85% | Routes and `DomesticAuth` / `DomesticSubmissionCron` / `DomesticProducerSync` exist. Real scheduler (Windows Task Scheduler or cron) is **not** in the repo. `sys/domestic/*` has no auth filter |
| 6.2 Capture and approve | ~80–90% | Capture and admin controllers exist, including submit gated on reconciliation |
| 6.3 Reports | ~50–70% | `DomesticReportModel` implements historic/modern, dynamic/cultivar/price/packaging, grouping, and trends |
| 6.4 Historic promote and Selwin | ~30–50% | `mark-synced` and producer sync/verify routes exist. A one-time promote from `_imported` into working `_data` was still described as a next step on 2026-08-06 |

The plan’s rough percentages are the author’s estimate, not a measured coverage figure.

## Implementation plan still open in the repo

`docs/phase 6/BACKEND_NEXT_STEPS_PLAN.md` (2026-08-06) lists remaining backend work, in the author’s order:

1. Send schema answers and spec v1.7 to Gareth (email draft is in the same folder). Whether it was sent is **Unknown**.
2. Promote historic import into `domestic_sawis_7_data` and add a small modern sample so reports can be checked.
3. Prove capture lifecycle New → submitted → approved → synced via API, without a frontend.
4. Document and protect cron endpoints; align the cancel day with SAWIS once Q1 cutoff is decided.
5. Finish report comparison against the June workbook.
6. Schema hygiene the plan still called deferred (real foreign keys, status lookup, split `userjwt` sessions). Junction access and DECIMAL `TOTAAL` / VARCHAR(6) `JJJJMM` on working tables were then implemented in `alter_gareth_schema_hardening.sql` on 2026-08-07.

Q1 monthly cutoff wording is explicitly still open with SAWIS in that plan.

## Known dependencies

| Dependency | Evidence |
|---|---|
| PHP | `composer.json` requires `php: ^8.2`. Root README still says PHP 8.0. **Discrepancy** — composer is the dependency declaration |
| CodeIgniter 4 | `codeigniter4/framework: ^4.7` |
| MySQL | Default database name `sawis_bi`. Phase 6 SQL uses `USE sawis_bi` |
| PhpSpreadsheet 5.3 | Excel import and export |
| mPDF 8.1 | Composer require. Call sites were not traced in this pass |
| AWS SDK 3.254 | Composer require. No `S3Client` / `Aws\` usage found under `app/` |
| OpenAI (`orhanerday/open-ai`) | `app/Helpers/chatgpt_helper.php` calls `https://api.openai.com/v1/completions`. `ChatGPT` controller is not routed |
| firebase/php-jwt | Required. `AuthFilter` looks up `userjwt.JWT_token` rather than only verifying a JWT signature |
| PHPUnit 11.5 | `composer.json`. `phpunit.xml.dist` still points its bootstrap at a PHPUnit 9 schema comment and includes a missing Phase6 suite |
| SonarQube | `sonar-project.properties` project key `overdrive_bitool_backend_341636ae-e1b5-4192-8790-74adfa3f49a7`. Pipeline on `master` only |
| SMTP | Levy and domestic emails. Host is environment/config, not copied here |
| Jira / Confluence | README links `https://siliconsoftware.atlassian.net/jira/software/projects/SI/boards/12` and wiki space `AO`. Whether those are still the live trackers is **Unknown** |

## Current project status

On the inspected commit the backend is a single evolving API, not a greenfield starter:

- Export analytics and the Phase 5 mapper are documented as operational as of June 2026.
- Phase 6 APIs and schema scripts are present through the 2026-08-07 hardening commit.
- The June live master doc and the August Phase 6 plan disagree about completeness. See discrepancies below.
- Changelog stops at 2026-06-05 and does not mention Phase 6 or the August hardening commit.

## Discrepancies

| Topic | What conflicts |
|---|---|
| “Working in full” vs Phase 6 | `docs/general/SAWIS_BI_Live_Documentation.md` says the site and APIs are working in full and its last major update is 2026-06. It does not describe Phase 6 domestic routes. `BACKEND_NEXT_STEPS_PLAN.md` (2026-08-06) says Phase 6 backend is roughly 60–70% and local lifecycle data was empty |
| Junction table | The August 6 plan says do not start a producer-access junction until Gareth prioritises it. `717166ec` and `DomesticUserModel` implement `domestic_user_producer_access` and still mirror `userjwt.domestic_producer_codes` |
| Migrations | Live doc says run `php spark migrate` for the 2026-06-04 test-upload columns. `app/Database/Migrations/` is empty except `.gitkeep` |
| Background worker | Controller spawns `php spark process:mapper-queue`. No command class is in the repository |
| Phase 6 tests | `phpunit.xml.dist` testsuite `Phase6` points at `./Phase6Testing/tests`. That directory is absent. Phase 6 README still mentions `Phase6Testing/AI_INSTRUCTIONS.md` |
| PHP version | README: 8.0. Composer: `^8.2` |
| Pipeline comment vs YAML | File header comments describe an FTP deploy. The YAML only defines a SonarQube step on `master` |

## Unknown / Requires Confirmation

- Which branch is deployed, and to which host.
- Whether the 2026-08-06 local row counts are still true.
- Whether the Gareth email in `docs/phase 6/Manager_Email_Reply_Schema_Questions.txt` was sent.
- The production schedule for `sys/domestic/*` and for `auth/runDataSync`.
- Whether unauthenticated public chart routes and unauthenticated `sys/domestic/*` routes are intentional in production.
- Whether credentials committed in `app/Config/Database.php` are still valid. They must not be copied into this knowledge base. Human review: rotate them and move them to environment configuration.
- Whether the example key printed in `docs/general/LOGIN_ENCRYPTED_PAYLOAD_FRONTEND.md` is a real secret or a sample. It was not copied here.