# PRD — sawis_staging

Evidence date: 2026-10-04.

Source: `git@bitbucket.org:silicon_bit_bucket/sawis_staging.git`.

Behaviour below is from `qa_staging_2020` at `5040b8b` (2026-09-21) unless a requirement says another branch. `master` at `9d06011` (2025-06-20) is the default branch and is not that tree. See `project-plan.md` for the branch split.

There is no prior PRD in the source repository or in Silicon Ark. Requirements are recovered from the implementation. Provenance is labelled. Passwords are not reproduced.

## Product purpose

**Implemented.** `index.php` shows the caption "Redirect" and the text "There is no content here. Please visit www.sawis.co.za." It then destroys the PHP session.

**Implemented.** The application that does work is a database sync between a staging schema and other MySQL profiles (S3, API, IPW), plus a small operator page and an IP-restricted SQL page.

**Unknown / Requires Confirmation:** a written product purpose. None is in the repository.

## Users and roles

| Actor | Evidence | Status |
|---|---|---|
| Anonymous visitor | `index.php` | **Implemented.** No login form. Session is destroyed. |
| Person named in message copy | `message.php` cases `s13`, `s44`, `r09`, `r10` | **Implemented** text for expired session, logout, insufficient access, and internal error. The pages that would create `$_SESSION[USER]` are not in this repository. |
| Sync operator | `sync.ui.php` actions | **Implemented** as request actions. The file destroys the session and does not check a user or an IP address. |
| SQL operator | `query.php` | **Implemented.** Request continues only when `REMOTE_ADDR` contains `192.168.100` or `10.212.134`. Otherwise the script dies with `SECURITY CHECK FAILED`. |
| Deploy operator | `deploy.php` | **Implemented.** Any request runs `shell_exec` of git commands. No login check is in the file. |
| Cron identity | `cron.sync.php` | **Implemented.** If `$_SESSION[USER]->USERNAME` is empty, the sync user string is `SYNC `. |
| Developer role, security group | `message.php` prints `SECURITYGROUPID`. `CheckSecurityLock` reads `sysUser` | **Inferred** that another SAWIS application owns accounts. This repo does not implement login. |

## Systems the product talks to

1. MySQL databases selected by HTTP host. Profiles and hosts are listed in `technical-documentation.md`.
2. The visitor's browser, for `index.php`, `sync.ui.php`, `query.php`, `message.php`, and `deploy.php`.
3. PHP `mail()`, when `emailSyncLog()` runs. Recipients are rows in S3 `sysSettings`: `SMTP Send To` and `SMTP Send As`. The subject is `Sawis Online: Sync Log [<environment>]`.
4. `http://www.mymobileapi.com/api5/http5.aspx`, only if something calls `SendSMS`. The sync scripts do not.
5. Google's jQuery 1.12.4 CDN, from `layouts/header.incl.php`.
6. `www.sawis.co.za`, as text on the stub page. This application does not call it.
7. Bitbucket, when `deploy.php` fetches, and when the `master` pipeline runs SonarQube.

A GIS profile is commented out in `cron.sync.php`. It is not active.

## Major workflows

### WF-1 — Open the public URL

`index.php` builds a `NemoBasic` page, destroys the session, and displays the redirect message.

Provenance: **Implemented**. The comment `20150611 - changed to home from index.php` is the only clue that a home screen used to live here.

### WF-2 — Boot the application

`system.php` on the QA tip:

1. Turns on `display_errors` and reports all errors except strict, notice, and warning.
2. Includes PROD, then QA, then DEV server files, so later files overwrite the same host key.
3. Reads `SERVER_NAME`, protocol, script name, and request URI into `$SystemSettings`.
4. Sets `session.gc_maxlifetime` to `7200` and `session.save_path` from the staging profile for that host.
5. Starts the session.
6. Connects to the S3 profile (`switchBD($DATABASE_SETTINGS_S3, 2)`). On failure it prints a critical-error message and still continues to the next step.
7. On success, sets the connection charset to `utf8`, loads every `sysSettings` row into `$SystemSettings`, then switches to the staging profile (`switchBD($DATABASE_SETTINGS, 0)`).

`system.functions.inc.php` copies `$_POST` into globals when POST is non-empty, and otherwise copies `$_GET` and `$_COOKIE`. That file runs because `system.php` includes it.

On `prod_staging_2020` only, `system.php` also sets the timezone to `Africa/Johannesburg` and, when `php_sapi_name()` is `cli`, fills missing server keys. `SERVER_NAME` becomes the environment variable `SAWIS_SERVER_NAME` or the string `10.0.0.62`.

Provenance: **Implemented**.

If `SERVER_NAME` is empty, `switchBD` dies with `UNKNOWN SERVER_NAME`. If the host is not a key in the profile array, the property access fails. **Implemented.**

### WF-3 — Run sync from the cron script

`cron.sync.php` and `cron.sync.new.php` include `system.php`, raise `memory_limit` to `-1` in `cron.sync.php`, and define one object per entity (`Type`, keys, update SQL, insert SQL).

Environments registered in `cron.sync.php`:

| Key | Profile variable |
|---|---|
| S3 | `$DATABASE_SETTINGS_S3` |
| API | `$DATABASE_SETTINGS_API` |
| IPW | `$DATABASE_SETTINGS_IPW` |
| STG | `$DATABASE_SETTINGS` (set later in the script, then unset after use) |

`cron.sync.php` runs `SHOW VARIABLES LIKE 'sql_mode'` and, when it chooses to, `set global sql_mode=''`. The commit `2025-03-06` says this is to stop MySQL strict mode. Provenance: **Implemented** in that file. The same three checks were not re-verified line-by-line inside `cron.sync.new.php`.

If `sysSettings.blnEnableSync` is `0` and both `$Process` and `$Action` are null, the script dies with `Sync disabled / blnEnableSync = 0`. A non-empty `$Action` or `$Process` skips that stop. Provenance: **Implemented** (`cron.sync.php` around the `blnEnableSync` check, and the same condition in `cron.sync.new.php`).

Sync writes `sysSyncLog` rows with levels such as `Info`, `Error`, and `Warning`.

### WF-4 — Operator runs or controls sync

`sync.ui.php` groups entities and posts stage checkboxes. Actions:

| Action | What the code does |
|---|---|
| Execute | Includes `cron.sync.new.php` |
| Disable Sync | On the S3 connection, sets `sysSettings.blnEnableSync` to `0`, last user `CUSTOM SYNC RUNNER` |
| Enable Sync | Sets that value to `1` |
| Set Last Sync Date | Accepts `Y-m-d H:i:s` only. Writes `sysSettings.dtLastSyncDate` on S3 |
| ClearLogs, SetDatabaseCollation, UpdateMAdressFromMA, ResetData, LoadMapperData | Include `cron.sync.new.php`. Comments say the matching case lives in the cron file |
| Reset Sync | Sets `sysSyncProcess.blnProcessed` to `0` for today's date on the current connection |
| Set Synced | Sets `blnProcessed` to `1` for today's date |
| Email Sync Logs | Calls `emailSyncLog()` and then `die` |

Stage checkboxes and their labels:

| Posted key | Label on the page | Default in the HTML |
|---|---|---|
| `STAGE -1` | STAGE 1 - Reset Map date (status = new). The group heading above it says STAGE -1 - Pre Run | Disabled |
| `STAGE 1.1` | STAGE 1.1, under "STAGE 1 - Get from API" | Checked |
| `STAGE 2.0` | STAGE 2 - GET from S3, under "STAGE 2 - Sync from S3. Entity W. dtLE > dtLE" | Checked |
| `STAGE 2.1` | STAGE 2 - STG >> TB | Checked |
| `STAGE 2.2` | STAGE 2 - LOG > MAP | Checked |
| `STAGE 2.3` | STAGE 2 - LOG > ENV | Checked |
| `STAGE 3.0` | STAGE 3.0 - MAP > STG (dynamic), under "STAGE 3 - Import from Mapper" | Unchecked |
| `STAGE 3.0.5` | STAGE 3.0.5 - MAP > STG (!dynamic) | Unchecked |
| `STAGE 3.1` | STAGE 3.1 - STG >> TBL | Unchecked |
| `STAGE 3.3` | STAGE 3.3 - LOG > ENV | Unchecked |
| `STAGE 4.0` | STAGE 4, under "STAGE 4 - Data Quality Checks" | Checked |
| `STAGE O` | STAGE O, under "STAGE O - Finish Sync" | Checked |
| `STAGE O.1` | STAGE O - Send log email (wip) | Disabled |

Provenance: **Implemented** in `sync.ui.php`.

`sync.ui.php` does not include `system.php`. It uses `$Action`, `$xdb`, and `switchBD` anyway. Those symbols exist only after `system.functions.inc.php` and `system.php` have run. A direct request therefore does not show, in this file, how POST fields become `$Action` before the `switch`. **Unknown / Requires Confirmation** whether the web server prepends `system.php` or the page is included from a script that is not in git.

### WF-5 — Import a mapper file

`cron.mapper.php` reads `DATA/IN/<filename>`, uses `sysSyncMapping` for column mapping, and updates or inserts staging and live tables. The only entity in `$arrSyncEntities` is Cultivar, file `GRAPE VARIETY TABLE.csv`.

The first `switch ($Action)` has a `default` that runs `TRUNCATE TABLE sysSyncLog` and `break`. A request with an empty action truncates the sync log and does not enter `case "STAGE 1"`. Provenance: **Implemented**.

Directories `DATA/IN`, `DATA/OUT`, `DATA/Archive_IN`, and `DATA/Archive_OUT` are not in git.

### WF-6 — Email the sync log

`emailSyncLog()` reads `sysSyncLog` where `dtDate = '2017-01-31'` and `blnEmailed = 0`. A comment shows `CURDATE()` was the previous filter. It loads `SMTP Send To` and `SMTP Send As` from S3 `sysSettings`, builds an HTML table, and sends it with `NemoEmail`, which calls PHP `mail()`.

Provenance: **Implemented**. The fixed date is what the function queries. Whether anyone still calls it in production is **Unknown / Requires Confirmation**.

### WF-7 — Run SQL from the console

`query.php` includes `system.php` and applies the IP check in the users table. Action `Run Query` calls `Query::runQuery` when the query text and at least one database checkbox are non-empty.

`runQuery` appends a semicolon if the text does not end with one. If the foreign-key flag is set, it strips existing `SET FOREIGN_KEY_CHECKS` statements and wraps the batch in `SET FOREIGN_KEY_CHECKS=0` and `SET FOREIGN_KEY_CHECKS=1`.

The screen lists every global whose name starts with `DATABASE_SETTINGS`, keyed by database name and environment. Provenance: **Implemented**.

### WF-8 — Deploy

`deploy.php` prints the output of:

- `cd /var/www/html/sawis_staging/` (staging branches; `master` has no `cd`)
- `echo $PWD`, `whoami`, `git branch`
- `git fetch` and `git reset --hard` of one branch: `dev_staging_2020` on `master` and on `dev_staging_2020`, `qa_staging_2020` on the QA branch, `prod_staging_2020` on the production branch

The HTML title is `SAWIS Upload QA` on every branch inspected, including production.

Provenance: **Implemented**.

## Functional requirements

### FR-001 — Public stub

The public entry page tells the visitor to use `www.sawis.co.za` and destroys the session.

Evidence: `index.php`. Implementation: **Implemented**. Confidence: High.

### FR-002 — Host selects the database profile

The process connects using the profile whose array key equals `SERVER_NAME`.

Evidence: `switchBD`, `system.server.*.php`. Implementation: **Implemented**. Confidence: High.

Empty or unknown host: `switchBD` dies or the property access fails. Confidence: High.

### FR-003 — Settings come from the S3 database

After connecting to S3, every `sysSettings.strSetting` / `strValue` pair is copied onto `$SystemSettings`.

Evidence: `system.php`. Implementation: **Implemented**. Confidence: High.

S3 connection failure: a message is printed; settings are not loaded; the script still calls `switchBD` for the staging profile. Confidence: High.

### FR-004 — Sync can be disabled

When `blnEnableSync` is `0` and the request has no `$Process` and no `$Action`, the cron stops with `Sync disabled / blnEnableSync = 0`.

Evidence: `cron.sync.php`, `cron.sync.new.php`. Implementation: **Implemented**. Confidence: High.

The operator sets the flag with Disable Sync and Enable Sync on the S3 `sysSettings` row. Evidence: `sync.ui.php`. Confidence: High.

An action name bypasses the flag. That is what the condition says. Confidence: High.

### FR-005 — Operator chooses entities and stages

The UI lists the groups in `features.md` and the stage keys in WF-4. Execute includes `cron.sync.new.php`.

Evidence: `sync.ui.php`. Implementation: **Implemented** as the form and the include. Confidence: High for the form. Confidence for a successful browser submit is Medium, because this file does not load `system.php` (WF-4).

### FR-006 — Records move staging → table → other environments

`UpdateSTG` counts `stg<Type>`, then runs the entity update and insert SQL and writes `sysSyncLog`. `UpdateENV` applies log rows to the selected environment and logs `ENV UPDATE`, including the MySQL error text when the write fails.

Evidence: `project.functions.inc.php`. Implementation: **Implemented**. Confidence: High that these functions exist and are the path. Confidence in every entity's SQL is Medium: the SQL is present and was not executed against a database.

### FR-007 — BlendMaster duplicate keys are skipped

`isMysqlDuplicateKeyError` is true when the message contains `Duplicate entry` or `1062`. `doQueryUpdateStg` uses that only when `Type` is `BlendMaster`. It inserts a `Warning` / `STG Import` log row and returns false. Other entities use `doQuery` directly.

Evidence: `project.functions.inc.php` on the QA tip. Implementation: **Implemented** in that file. On the QA tip, `doQuery` does not declare the third parameter this function passes. See the branch table in `project-plan.md`. Confidence: High for the source text. Runtime result on QA is **Unknown / Requires Confirmation**.

On `prod_staging_2020`, `doQuery` catches `mysqli_sql_exception`, stores the message, and returns false when `$blnDieOnError` is false. Confidence: High for that source text.

### FR-008 — Lab analysis is update-only

A comment in `project.functions.inc.php` (2021-01-19) says lab analysis uses a custom update and must never insert. A later comment (2021-07-28) says the API lab-analysis status is set to `ST`. The hotfix branch message says the same.

Evidence: comments and `origin/fix_sync_hotfix20210630`. Implementation: **Inferred** from those comments. The full lab-analysis SQL was not extracted statement by statement. Confidence: Medium.

### FR-009 — Mapper import for grape varieties

Cultivar rows load from `DATA/IN/GRAPE VARIETY TABLE.csv` when `$Action` is `STAGE 1`, using `sysSyncMapping` and the update/insert SQL in `cron.mapper.php`.

Evidence: `cron.mapper.php`. Implementation: **Implemented**. Confidence: High.

Missing file: a log row `N/A: no file.` Confidence: High.

### FR-010 — Sync log email

The email body is the log query in WF-6. The fixed date `2017-01-31` is in the SQL.

Evidence: `emailSyncLog`. Implementation: **Implemented**. Confidence: High.

Missing `SMTP Send To`: the code uses `$rowSendTo->strValue` with no empty check. Result is **Unknown / Requires Confirmation** (likely a PHP notice or a mail to an empty address).

### FR-011 — SQL console

FR as WF-7. Failed IP check ends the request before the form. Implementation: **Implemented**. Confidence: High.

There is no allow-list of SQL verbs. Confidence: High.

### FR-012 — Session and access messages

`message.php` sets the message text for `MID` values `s13`, `s44`, `r09`, and `r10`. For `s44` and `r10`, or when `$_SESSION[USER]` is not set, it uses `NemoBasic` and unsets `$_SESSION[USER]`. Otherwise it uses `Nemo`. It renders `layout.menu.incl.php`.

Evidence: `message.php`. Implementation: **Implemented**. Confidence: High.

Other `MID` values produce an empty message unless `$M` is set, in which case the message is `htmlentities($M)`. Confidence: High.

### FR-013 — Deploy by hard reset

FR as WF-8. Implementation: **Implemented**. Confidence: High.

Failure of git (no repo, no credentials, dirty policy) is printed as command output. The script does not check the exit code. Confidence: High.

## Integrations

| Integration | Direction | Trigger | Data | Auth in repo | Failure |
|---|---|---|---|---|---|
| Staging MySQL | Read/write | Every boot and sync | Entity tables, `stg*`, `log*`, `sysSyncLog`, `sysSyncProcess` | Username and password in `system.server.*.php` | `switchBD` returns false and can print the profile |
| S3 MySQL (`sawis`) | Read/write | Boot (`sysSettings`); sync stages that say S3 | Settings and entity rows | Same | Boot prints a critical error and continues |
| API MySQL (`sawis_api`) | Read/write | Sync stages that target API | Entity rows, including lab analysis status | Same | Logged as `ENV UPDATE` error text |
| IPW MySQL | Read/write | Sync of the IPW entity | IPW rows. Production tip names table `sawis_compliance` in a comment | Same. Production user is `sawis_readon` | Same logging path |
| Mapper CSV | Read | `cron.mapper.php` stage 1 | `GRAPE VARIETY TABLE.csv` | Filesystem | Log `N/A: no file.` |
| PHP mail | Out | `emailSyncLog` | HTML log table | Server mailer, not configured here | `mail()` return value is stored on the email object; the sync function does not check it |
| MyMobileAPI | Out | `SendSMS` only | SMS number and message | `sysSettings` sms fields | Function returns the HTTP body or an error string |
| Bitbucket git | In | `deploy.php` | The selected branch | Whatever the server git remote uses | Output printed; exit code ignored |
| SonarQube | Out | `master` pipeline and pull requests | Source scan. Project key `sawis_staging` | `SONAR_TOKEN` pipeline variable | Not described in the YAML beyond the pipe |

## Important constraints

- Display errors are on. Error reporting hides strict, notice, and warning.
- Charset requested on connect is `utf8`, not `utf8mb4`.
- `cron.sync.php` may set the MySQL global `sql_mode` to empty for the whole server.
- `query.php` can disable foreign-key checks for the statements it runs.
- `gpc_extract` on the QA tip always runs `stripslashes` (`$is_magic_quotes = true`). Its `$target` parameter is not a reference. `localize` passes `$GLOBALS`. Whether POST values still become globals is **Inferred** as broken on PHP and **Requires Confirmation**.
- `qsQuery` still calls `get_magic_quotes_gpc()`, which PHP 8 removed. A call to `qsQuery` on PHP 8 is a fatal error. Provenance: **Implemented** call.

## Requirements discovered only as comments

These are **Inferred** from comments, not re-proved by reading every SQL branch:

- Wine of Origin codes changed length (`vc11` to `vc21`) and member `strWOLCode` is written back onto BG2 master rows.
- District and region are swapped between mapper and S3.
- If more than one harvest-item cultivar is present, harvest cultivar becomes `000`.
- Block rows for the current vintage come from `tblBlock.intYear = sysYear.intYear`.
- Member type codes named in a 2018 comment: `04` learner management, `05` EzyWine, `06` a type members can choose. Those screens are not in this repo.
- Address lines 1 and 5 are mandatory for the "MAx5" address change.
- A reset file is recognised when `strLastUser` matches `R:%` (earlier comment said `RESET!%`). `syncOperationType` becomes `R`.

## Assumptions not made

- That `master` is what production runs. The deploy scripts say otherwise.
- That the sync UI is authenticated.
- That `emailSyncLog` reports the current day.
- That the abbreviations S3, IPW, WIETA, WSB, BG2, and WOL have a glossary in this repository. They do not.