# Technical documentation — bi_tool_backend_phase6 (SAWIS BI backend)

Evidence date: 2026-10-19.

Source: `git@bitbucket.org:overdrive/bitool_backend.git`, branch `phase6`, commit `717166ec` (2026-08-07, "Implement Gareth review schema hardening on Phase 6.").

This chapter describes the tree that was inspected. It does not prescribe a redesign.

## Stack

| Item | Evidence |
|---|---|
| Language | PHP. `composer.json` requires `^8.2`. Root README still says 8.0. Use the Composer constraint |
| Framework | CodeIgniter 4, `codeigniter4/framework` `^4.7` |
| Package name | `codeigniter4/adminstarter` (starter name kept) |
| HTTP | CI4 routes in `app/Config/Routes.php`. Front controller under `public/` |
| Database | MySQLi. Default group database name `sawis_bi`. Second group `SAWISAPI` database name `sawis_api`. PHPUnit group `tests` is SQLite `:memory:` |
| Excel | `phpoffice/phpspreadsheet` `^5.3` |
| PDF | `mpdf/mpdf` `^8.1` (dependency present; call sites not traced) |
| JWT library | `firebase/php-jwt` `^6.10` |
| OpenAI client | `orhanerday/open-ai` `^3.5` |
| AWS SDK | `aws/aws-sdk-php` `^3.254` (no `Aws\` usage found under `app/`) |
| Tests | `phpunit/phpunit` `^11.5`. Dev: Faker, vfsStream |
| CI | Bitbucket Pipelines, SonarQube scan on branch `master` only |
| Time zone in auth and domestic jobs | `Africa/Johannesburg` |

## Repository layout

```text
app/                 Controllers, Models, Libraries, Filters, Helpers, Views, Config
  Database/Migrations   empty except .gitkeep
  Database/Seeds        empty except .gitkeep
docs/                phase 6, phase-4, general, mapper, monthlySync
postman/             SAWIS BI collection
public/              CI front controller
scripts/             includes step2_field_metadata.py (INIT_COLS)
tests/               PHPUnit examples plus AdminProcessingTest and HealthTest
testsprite_tests/    generated backend plan and TC*.py cases
writable/            logs, cache, uploads (phase5 uploadedFiles / uploadedTestFiles)
spark                CI CLI entry
composer.json
bitbucket-pipelines.yml
phpunit.xml.dist
sonar-project.properties
env                  CI environment template
adminDetails.md      levy queue and final-sheet contract
CHANGELOG.md         through 2026-06-05
NewKnowledgeBase.md  implementation notes
```

`Phase6Testing/` is named in `composer.json` autoload-dev and in `phpunit.xml.dist` and is **not** in the tree. `app/Commands/` does **not** exist.

Phase 6 README says the working copy on a developer PC is `C:\wamp64\www\bitool_backend\` and that optional mirrors live under `C:\projects\specBuilder\`. Those paths are local to that author. They are not this knowledge checkout.

## Request path

1. `app/Config/Filters.php` runs `cors` before and after every request. CSRF, debug toolbar, honeypot, and a global auth filter are commented out.
2. `Cors` answers `OPTIONS` with HTTP 204 and `Access-Control-Allow-Origin: *`. Allowed headers include `Authorization` and `X-API-KEY`.
3. A route runs `authFilter` or `adminFilter` only when that route's options say so. Being inside the `auth` or `admin` group does **not** by itself apply the filter. Several routes in those groups omit it (`runDataSync`, `api-dashboard`, `email-test`, `site-health`, the three `mapper_import_unkown_*` imports, and others).
4. Controllers are mostly thin. Levy work is in `AdminModel`. Domestic rules are in `DomesticAuth`, `DomesticSubmissionModel`, `DomesticReconciliation`, and `DomesticSubmissionCron`.

## Authentication

| Mechanism | Where | Behaviour |
|---|---|---|
| Login | `POST /loginAuth` | Plain JSON or encrypted `payload`. Rate limit in cache. Writes `userjwt` |
| Auth filter | `app/Filters/AuthFilter.php` | Matches `Authorization` to `userjwt.JWT_token`. Named emails have fixed idle windows. Updates `Start_date`. Sets global `$Producer_details` from JSON |
| Admin filter | `app/Filters/AdminFilter.php` | Same session check, then role 1, 2, or 4. Query `admin_email` fallback for role 2 within 60 minutes. Local bypass user `EttieneOverdrive` when auth headers are disabled |
| Domestic helper | `DomesticAuth::currentUser()` | Separate expiry using `JWT_SECRET__CookieLoginExpires` or 30 minutes. Accepts `Bearer ` prefix |
| Encrypted payload | `UserLoginModel` | AES-256-CBC, key env `LOGIN_PAYLOAD_KEY` (64 hex chars = 32 bytes), body is base64(IV + ciphertext) |

Environment names seen in code and docs (values not copied):

- `CI_ENVIRONMENT`
- `JWT_SECRET`
- `JWT_SECRET__CookieLoginExpires`
- `LOGIN_PAYLOAD_KEY`
- `REACT_APP_USE_AUTH_HEADERS`
- `SAWIS_MYSQL_GONE_AWAY_MAX_RETRIES`
- `SAWIS_MYSQL_GONE_AWAY_RETRY_DELAY_MS`
- `DOMESTIC_FIRST_LOGIN_HELP_TEXT`
- `DOMESTIC_FIRST_LOGIN_HELP_FILE`

## Database

Connection groups are in `app/Config/Database.php`.

| Group | What the file shows |
|---|---|
| `default` (active) | Host `10.0.0.145`, database `sawis_bi`, port 3306, charset `utf8`, driver MySQLi. Username and password are hard-coded in the file. **Do not copy them.** Human review: rotate and move to environment configuration. A localhost alternative is commented out |
| `SAWISAPI` (active) | Host `localhost`, database `sawis_api`, user `root`, empty password. A remote block for the same host as default is commented out |
| `tests` | SQLite memory, table prefix `db_` |

There is no migration history in git for this revision. Schema for Phase 6 is applied by SQL scripts:

| Script | Purpose |
|---|---|
| `docs/phase 6/create_domestic_tables.sql` | `CREATE TABLE IF NOT EXISTS` for 16 `domestic_*` tables, including `domestic_audit_log` |
| `create_domestic_audit_log.sql` | Audit table only, for databases that already had 15 tables |
| `alter_domestic_submissions_country.sql` | `country` on `domestic_sawis_7_submissions`, backfill from producer VT |
| `alter_submissions_date_to_period.sql` | Header `date` renamed to `period` VARCHAR(6) |
| `alter_gareth_schema_hardening.sql` | Junction `domestic_user_producer_access`, audit columns, working `TOTAAL DECIMAL(18,2)`, working `JJJJMM VARCHAR(6)`. Selwin import/approved litre columns stay VARCHAR |
| `seed_domestic_lookups.sql` | Truncate and insert 9 lookup tables |

The phase 6 README's install example loads those scripts into database `sawis_bi`.

Tables named in code and docs (not a full catalog):

| Area | Tables |
|---|---|
| Identity | `userjwt`, `domestic_user_producer_access` |
| Exports | `export_data_qa`, `export_data_dev`, `export_data_dev_industry`, price-range export table, `mapper_data`, `mapperdata_imported` |
| Levy | `emails_sent`, `mapper_excel_init`, `mapper_excel_formatted`, `mapper_excel_formatted_test`, `mapper_process_prodnaam`, `mapper_process_134I62`, `mapper_process_dubbel_nommers`, `mapper_process_writebacks`, `mapper_process_mapper_data`, `unknown_producer_matches`, `unknown_cultivar_matches`, `unknown_country_matches`, `mcc_designation_lookup`, `email_log` |
| Domestic | `domestic_sawis_7_submissions`, `domestic_sawis_7_data`, `domestic_sawis_7_data_imported`, `domestic_sawis_7_data_approved`, `domestic_producer_list`, `domestic_privaat_produsente`, `domestic_wholesaler_groothandel`, `domestic_audit_log`, lookup tables seeded by SQL |

`emails_sent` is both the levy process state machine and an email thread table. Main row: `sub_email_link_to_process` null and `current_active_process = 1`. Test upload columns: `uploaded_test_filename`, `uploaded_test_excel_doc_link` (added in the 2026-06-04 change; migration class not in git).

Point-in-time local counts in `BACKEND_NEXT_STEPS_PLAN.md` (2026-08-06) are evidence of that day's database only. They were not re-measured.

## Route map

Base path is the CI4 public root. Dev docs use `http://localhost:8080`.

### Public (no filter on the route)

- `GET /`
- `POST /loginAuth`
- `GET /getFilters`
- Chart POSTs: `getTop25Data`, `getChartData`, `getCultivarChartData`, `getYearOverYearComparison`, `getTotale_Liters`, `getExports`, `getTotale_LitersData`
- `POST /tables/get*TableData` (five export tables)
- Vineyard and grape GET/POST routes listed in the PRD
- `GET /getDomesticReportFilters`, `POST /getDomesticReport`
- `GET/POST /sys/domestic/create-period-submissions`, `cancel-overdue-submissions`, `mark-synced`, `sync-producer-list`
- `GET /sys/domestic/verify-producer-list`
- `GET /sys/ExportDataTo__Data_export_dev_industry`, `GET /sys/createIndustryImport`

### `auth` group

Many routes add `['filter' => 'authFilter']` individually. Domestic capture, lookups, and domestic admin do. Confirmed **without** that filter option: `excel_import_check`, `mapper_import_lookup__import`, `dashboard`, `api-dashboard` (including layout), `email-test`, `site-health`, `runDataSync`, `runDataSync_VineyardStatus`, `runDataSync_GrapeProduction`. Audit event routes set the filter again even though they are already under `auth`.

Domestic capture prefix: `/auth/domestic/`.

Domestic admin prefix: `/auth/domestic/admin/`.

### `admin` group

Levy queue, final-sheet test, cancel, progress, downloads, history, retry, logs, and the four CRUD resources set `adminFilter`. The three `mapper_import_unkown_*_lookup__import` posts do not.

Full request and response notes for the queue and the final-sheet test are in the source file `adminDetails.md`. The live catalog with filter examples is `docs/general/SAWIS_BI_Live_Documentation.md` (June 2026, before Phase 6 was the focus). Phase 6 route text is also embedded in `app/Libraries/ApiRouteCatalog.php` for the API dashboard.

## Background work and jobs

| Job | How it starts | Notes |
|---|---|---|
| Levy queue | `AdminController` runs `php -d memory_limit=4G spark process:mapper-queue <id>` via `start /B` on Windows and `exec ... &` on Unix | No command class exists under `app/`. `spark list` cannot register a missing command. **Requires Confirmation** — background processing may fail at the CLI even though the HTTP spawn code is present |
| Create domestic headers | HTTP `sys/domestic/create-period-submissions` | No crontab file in the repo |
| Cancel overdue | HTTP `sys/domestic/cancel-overdue-submissions` | Day check is inside `DomesticSubmissionCron` |
| Mark synced | HTTP `sys/domestic/mark-synced` | Depends on Selwin having emptied staging |
| Producer sync | HTTP sys and admin routes | `DomesticProducerSync` |
| Export / vineyard / grape refresh | HTTP `auth/runDataSync*` | Can also be run as the SQL files in `docs/monthlySync/` from HeidiSQL. Those SQL files are documented as a mirror of the PHP |

There is no queue product (no Redis, SQS, or database-queue library in `composer.json`). "Queue" for the levy file means a row in `emails_sent` plus a spawned PHP process.

## Files and storage

| Path | Use |
|---|---|
| `writable/uploads/phase5/uploadedFiles/` | Production DAF uploads |
| `writable/uploads/phase5/uploadedTestFiles/` | Final-sheet test workbooks |
| `writable/logs/` | CI logs |
| PhpSpreadsheet file cache | `app/Libraries/PhpSpreadsheetFileCache.php`, used in step 3 to keep cells on disk |

Downloads are `GET /admin/getAdminFiles/{uploaded\|debug\|final\|test_uploaded}/{processId}` behind `adminFilter`. Completion emails append `admin_email` on links so the filter's query fallback can apply (changelog 2026-04-09).

## Email

- Helper `sawis_email_html_helper.php`: table layout, no flex or rounded CSS, aimed at Outlook.
- `App\Libraries\EmailLogger` writes `email_log`.
- Levy errors: `notifySawisPipelineError`.
- Domestic cancel and help use `sendMail` / `send_mail_debug`.
- SMTP host is CI email configuration. Not copied here.

## External systems

| System | In this repo |
|---|---|
| Selwin | Named consumer of `domestic_sawis_7_data_approved`. No client class found. Integration is "staging table empty means RAM finished" |
| OpenAI | `app/Helpers/chatgpt_helper.php` POSTs to `https://api.openai.com/v1/completions`. Controller `ChatGPT` has no route |
| SonarQube | `sonar-project.properties` key `overdrive_bitool_backend_341636ae-e1b5-4192-8790-74adfa3f49a7`. Pipeline image `maven:3.3.9`, pipe `sonarsource/sonarqube-scan:2.0.1`, variables `SONAR_HOST_URL` and `SONAR_TOKEN` |
| Jira / Confluence | URLs in the root README (project SI, space AO). Liveness **Unknown** |
| AWS | Composer only |

`bitbucket-pipelines.yml` header comments describe an FTP deploy. The YAML body is only the Sonar step on `{master}`. **Discrepancy.**

## Security notes (evidence, not a pentest)

These are visible in source. They need a human decision; this document does not change them.

1. Default database username and password are committed in `app/Config/Database.php`.
2. `AuthFilter` builds a SQL predicate by concatenating the Authorization header and a date string. That is an injection risk if the header is not strictly constrained.
3. CORS origin is `*`.
4. `sys/domestic/*`, public charts, public tables, public domestic reports, and three admin import routes have no auth filter.
5. `admin_email` query parameter can satisfy `AdminFilter` for a role-2 session.
6. Disabling `REACT_APP_USE_AUTH_HEADERS` selects a fixed user row.
7. Idle exceptions are hard-coded email addresses.
8. `docs/general/LOGIN_ENCRYPTED_PAYLOAD_FRONTEND.md` contains an example 64-character hex key. Confirm it is not a production key. It is not repeated here.
9. Login lockout is per IP in the CI cache, not per account.

## Development and test process

Documented setup in the root README:

1. Copy `env` to `.env` and set `baseURL` and database settings. The active `Database.php` still overrides the host in code, so `.env` alone may not move the default connection. **Requires Confirmation.**
2. `php spark db:create`, `php spark migrate`, `php spark db:seed users`, `php spark key:generate`, `php spark serve`.
3. The users seeder class was not found under `app/Database/Seeds/` (only `.gitkeep`). **Requires Confirmation** that `db:seed users` still works.
4. Phase 6 tables are loaded with the SQL scripts, not with `spark migrate`.

Tests present:

| Suite | What is in the tree |
|---|---|
| `tests/unit/HealthTest.php` | CI health sample |
| `tests/unit/AdminProcessingTest.php` | Levy processing unit coverage. Changelog says it covers bad DAF headers |
| `tests/database`, `tests/session` | CI examples |
| `Phase6Testing/tests` | Referenced, directory missing. Phase 6 README says PHPUnit lives there |
| `testsprite_tests/` | Live doc says 104 generated cases (2026-06), including single-value and multi-value filters. Cases are shape and status checks. They send JSON, not real multipart Excel, unless extended |
| `tests/Api/` | Mentioned by the live doc as older Python contract tests. Not re-counted in this pass |

`phpunit.xml.dist` bootstrap path assumes `vendor/codeigniter4/framework`. Vendor was not installed in the evidence clone. PHPUnit 11 is required while the XML schema comment still points at PHPUnit 9.3. **Requires Confirmation** that the suite still runs.

Sonar runs on `master` only. `phase6` pushes do not match the pipeline branch pattern.

WAMP virtual host example in the README (`adminOverdrive.local`) is starter documentation, not proof of the current SAWIS host.

## Monitoring and operations

- `GET /auth/site-health` and `GET /auth/audit-dashboard` exist. Filters on those routes are inconsistent (site-health has no `authFilter` in the route options that were read).
- Levy operators use history, step-2 progress, email log, and the dual error email.
- No application performance product (no Datadog, Sentry, or similar) was found in Composer.
- Structured correlation IDs are a recommendation in the live doc, not a described implementation. `process_id` is the levy correlation key.

## In-repo documents to read first

| Question | File |
|---|---|
| Export APIs and levy overview as of June 2026 | `docs/general/SAWIS_BI_Live_Documentation.md` |
| Levy request contract | `adminDetails.md` |
| Phase 6 behaviour delta | `docs/phase 6/README.md` and `REVISION_saved_totals_v2_and_onboarding.md` |
| Phase 6 signed scope narrative | `docs/phase 6/SAWIS_BI_Phase_6_SAWIS_7_Domestic_Capture_v1.7.html` |
| What the author still considered open on 2026-08-06 | `docs/phase 6/BACKEND_NEXT_STEPS_PLAN.md` |
| Change log through 2026-06-05 | `CHANGELOG.md` |
| Mapper field list | `scripts/step2_field_metadata.py` |

The June live doc and the August Phase 6 plan disagree about completeness. Prefer code on `717166ec` for what is built, and the August plan for what the author still called unproven.