# Technical documentation — indawo_84

Evidence date: 2026-10-12. Revision: `main` `6ce3e8d` (2026-10-12, "days differences added").

The in-repo document `docs/INDAWO-Online-Technical-Documentation.md` (2026-05-26) is the longest architecture note. It is accurate for the nightly SQL-to-MySQL picture and stale for realtime sync. This file records the tree that was read. It does not repeat credentials.

## Stack

| Layer | Choice | Evidence |
|---|---|---|
| Language | PHP `>=8.4` | `composer.json`, `setup_composer.md` |
| Style | Page-at-a-time scripts, not a router framework. Shared kernel is the Nemo classes | Root `*.php`, `_nemo*.cls.php` |
| App database | MySQL, database name `indawo_online` in local defaults and in the May document | `system.php`, May technical document |
| Back office | Microsoft SQL Server via ODBC (`odbc_connect` / `odbc_exec` in `NemoDatabaseSQLSERVER`) | `_nemo.database.SQL_SERVER.cls.php` |
| Config | `vlucas/phpdotenv` loads a gitignored `.env`. Template file is `env` | `setup_composer.md`, `system.php` |
| MFA | `robthree/twofactorauth` `^3.0`, `endroid/qr-code` `^6.1`, `ext-gd` | `composer.json`, `login.php` |
| PDF | Composer `mpdf/mpdf` `^8.2` and `spipu/html2pdf` `^5.3`. Vendored `mpdf/` (5.x comments) and `html2pdf/` are also in the tree. `project.functions.inc.php` includes `mpdf/mpdf.php` and uses `Spipu\Html2Pdf\Html2Pdf` | Those paths |
| Excel | Vendored `PHPExcel/` included from `project.functions.inc.php` | **Implemented** |
| Charts | Vendored Highcharts and canvg under `plugins/` | **Implemented** |
| Web server | Apache `RewriteEngine` in `.htaccess`. Nginx is mentioned only as a possible production server in `setup_composer.md` | `.htaccess`, `setup_composer.md` |
| CI | Bitbucket Pipelines, SonarQube pipe `sonarsource/sonarqube-scan:3.0.2` | `bitbucket-pipelines.yml` |
| Static analysis key | `sonar.projectKey=overdrive_indawo_65_fbe8b635-8a1f-460d-ad73-ab11cacca02f` | `sonar-project.properties` |

Composer package name: `russel-sauer/indawo`. There is no `require-dev` block and no PHPUnit dependency. The regression script is a PHP CLI file, not a PHPUnit suite.

`composer.json` says: do not edit `composer.lock` by hand, do not copy it from another project, do not commit `vendor/`, use `composer install` in production, and do not use `composer update` on production.

## Application structure

```text
indawo_84/
  index.php, login.php, home.php          entry and session gate
  *.php                                   one script per screen or cron
  includes/*.cls.php                      screen classes (NemoList / Nemo details)
  includes/reports/                       report renderers
  includes/realtime_*.inc.php             queue retry and morning catch-up
  includes/syssyncqueue_schema.inc.php    CREATE/ALTER sysSyncQueue
  _nemo*.cls.php                          framework: DB, list, details, menu, email, report, password
  _sync.cls.php                           batch Sync and realtime SyncRecord
  system.php                              host switch, dotenv, mysqli, session path
  system.functions.inc.php                shared helpers
  project.functions.inc.php               company context, filesystem base, dumps, PDF helpers
  layouts/                                header and layout includes
  docs/                                   in-repo architecture and plans
  tests/ett_odbc_regressions.php          CLI regression script
  mpdf/, PHPExcel/, html2pdf/, plugins/   vendored libraries
  env                                     commented environment template
```

A typical screen includes the Nemo bootstrap (through `system.php`), checks the session, and instantiates a class in `includes/`. Company-scoped SQL calls `getOWC()` or `whereOWC()`.

Nemo classes on the root:

| File | Role |
|---|---|
| `_nemo.cls.php` | Front include used by many crons |
| `_nemo.basic.cls.php` | Page object, settings, modules |
| `_nemo.database.cls.php`, `_nemo.database2.cls.php` | MySQL |
| `_nemo.database.SQL_SERVER.cls.php` | ODBC |
| `_nemo.list.cls.php`, `_nemo.list.basic.cls.php` | Lists, sort, pager |
| `_nemo.details.cls.php` | Edit forms |
| `_nemo.menu.cls.php`, `_nemo.menu.basic.cls.php` | Menu |
| `_nemo.email.cls.php` | Outbound mail |
| `_nemo.password.cls.php` | Password policy |
| `_nemo.report.cls.php` | Reports |
| `_nemo.control.cls.php` | Form controls |
| `_sync.cls.php` | `Sync` (SQL to MySQL) and `SyncRecord` (MySQL to SQL) |

## Hosts and configuration

`system.php` normalises the host, then switches:

| `SERVER_NAME` after normalisation | Credential source | Notes |
|---|---|---|
| `vm-indawo-lamp-prod.southafricanorth.cloudapp.azure.com` | Rewritten to `online.indawo.biz` before the switch | Azure production name |
| `online.indawo.biz` | `envv("DB_*")` and `envv("*_SQL")` | `SERVER_PROTOCOL` forced to `HTTPS`. Session path `DOCUMENT_ROOT/SESSION/` |
| `10.50.1.4` | Same `.env` keys | Display errors on. No `SERVER_PROTOCOL` line in the case that was read |
| `www.silicon-overdrive.co.za` | Rewritten to `silicon-overdrive.co.za` | No matching `case` was seen after the rewrite. **Inferred** to hit `default` |
| `localhost` (also forced from an earlier `localhost` check to `localhost:8888` in one branch, then a later check sets `localhost:8888` only when the name is exactly `localhost`) | See cases below | The function order in `system.php` is easy to misread. Read the file before changing it |
| `localhost:8888` | Literal MySQL user `test`, database `indawo_online`, FreeTDS DSN `SQL-SERVERR2` | Password is in source. Not copied here |
| `localhost` and `127.0.0.1` | `.env` with local defaults: MySQL `127.0.0.1` / `root` / database `indawo_online`. ODBC Driver 18, server `CPTSQL02`, database `CLIENTS` | SQL username and password defaults are in source. Not copied here |
| `default` | Same as the localhost `.env` defaults, then forces `SERVER_NAME` back to `localhost` | |

Every case sets `display_errors` to 1 and logs to `DOCUMENT_ROOT/error.log`, including `online.indawo.biz`. **Implemented**.

`setup_composer.md` tells production to `cd /var/www/indawo`, run `composer install --no-dev --optimize-autoloader`, create `.env` with mode `640`, and create an ODBC system DSN named `CPTSQL02` aimed at `192.168.0.9,53950`. The May document instead uses host `192.168.0.28`, app path `/var/www/html/indawo_online/`, PHP `/usr/local/bin/php`, and `SERVER_NAME=192.168.0.28` (that value is not a `case` in the current switch). **Requires Confirmation** which description matches the server that is actually running.

Environment keys in the tracked `env` template:

| Key | Purpose |
|---|---|
| `APP_ENV` | Commented examples: local, development, qa, production |
| `SERVER_PROTOCOL` | `HTTP` or `HTTPS` |
| `DB_HOSTNAME`, `DB_USERNAME`, `DB_PASSWORD`, `DB_DATABASE` | MySQL |
| `HOSTNAME_SQL` | Full ODBC connection string, not only a hostname |
| `USERNAME_SQL`, `PASSWORD_SQL` | SQL Server login |
| `realTimeSync` | `false` skips normal-page MySQL-to-SQL writes |
| `SHAREPOINT_SITE`, `SHAREPOINT_LIBRARY` | Used by `Foreman::publicFileUrl()` even though they are not in the `env` template. Defaults: `https://indawocape.sharepoint.com/sites/OnlineProjects` and `Indawo Online` |

`.env*` is gitignored and denied by Apache.

## Data

No schema dump is committed. `docs/schema/README.md` says to run `docs/schema/export_schema.php` on a machine that can see MySQL `indawo_online`. That README includes a sample command with a database password. The password is not repeated here.

`MFATables.sql` alters `sysUser` (`blnMFAEnabled`, `blnMFARequired`) and creates `tblUserMFA`, `tblUserMFARecoveryCodes`, and `tblUserMFARememberedDevices`.

`includes/syssyncqueue_schema.inc.php` creates `sysSyncQueue` if it is missing and adds columns the logger needs: remote database and table, SQL text, `txtAttemptValues`, `blnSuperseded`, `dtSynced`, `txtError`, retry count, last retry, last user, `dtLastEdit`.

Conceptual tables, from the May document and from SQL strings read in this pass:

| Area | Tables |
|---|---|
| Security | `sysUser`, `sysSecurityGroup`, `sysSecurity`, `sysMenuLevel1`, `sysMenuLevel2`, `sysLogin` (login audit via `tblLoginInsert`) |
| Company | `sysCompany` (`strCompany`, `strFolderPath`, `syncRemoteDatabase`, `blnActive`) |
| Settings and sync | `sysSettings`, `sysSyncMappings`, `sysSyncLog`, `sysSyncQueue` |
| CRM | `tblClient`, `tblContact`, `tblContactLog`, `tblClientType` |
| Jobs | `tblForeman`, `tblForemanActual`, `tblForemanInvoices`, `tblForemanReceipts`, `tblForemanCostings`, `tblForemanOpenItem`, `tblForemanStatusUpdater`, `tblForemanStartEndDateHistory`, `tblJobType`, `tblJobSuggestions`, `tblSalesPerson` |
| Orders | `tblOrder`, `tblOrderAuthorisations`, `sysOrderCancelLog` |
| Workflow | `tblPSA` and related PSA tables, `tblBAA` (May document) |
| Assets | `tblAssetList`, `tblAssetContact` (May document) |
| Mail | `tblEmail`, `tblEmailTemplate` (May document) |

Staging tables are named `stg*` and are recreated from the `tbl*` structure during batch sync. **Documented**.

Sync key columns such as `syncClientId` and `syncForemanId` are **Documented**. Batch delete only removes rows where the sync key is not NULL.

## Sync behaviour

Batch (`Sync` in `_sync.cls.php`), **Documented** algorithm:

1. Log `Sync Start` on `sysSyncLog`.
2. Disable foreign key checks.
3. For each entity and company, rebuild `stg*`, ODBC-select from SQL Server, insert staging, delete synced `tbl*` rows, insert from staging, run `LocalEvalTO`.
4. Enable foreign key checks and log completion.

Realtime (`SyncRecord`):

- Entities: Client, Contact, Foreman, Foreman Job Start End Date History (`includes/realtime_entities.inc.php`).
- Attempts are logged with a JSON snapshot in `txtAttemptValues`.
- `realTimeSync=false` returns a skip string and does not call the SQL write.
- Morning catch-up re-applies pending snapshots onto MySQL after the pull. The re-apply path skips CASE/JOIN mappings when writing back (commit `e7c3a4f`, "Skip CASE/JOIN mappings when re-applying realtime queue to MySQL").
- Evening retry selects open failures in the default 7-day window.

The May document's statement "there is no `sysSyncQueue`" is false for this revision.

## Cron scripts

HTTP access is rewritten:

```text
^(cron[^/]*\.php|Cron\.[^/]*\.php|Zcron\.daily\.php)$
  -> http_cron_gate.php?_cronfile=$1
```

CLI is unaffected (`.htaccess` comment).

| Script | Role on current `main` |
|---|---|
| `Cron.daily.1.Lookups.php` through `Cron.daily.6.DataDumps.php` | Nightly chain. Schedules **Documented** in the May doc |
| `cron.CashFlow.php` | Cash-flow PDF. Under/over formula updated `41f6b52` |
| `cron.daily.orderauth.php` | Pending authorisation email |
| `cron.daily.ordercancel.php` | 15th-of-month cancel for company 1 (**Documented** rule) |
| `cron.daily.ordercancel.1.php`, `.4.php`, `.6.php`, `.9.php` | Present. Schedule **Unknown** |
| `cron.daily.manuel.php` | Manual full sync |
| `cron.morning.realtime-catchup.php` | Post-pull re-apply. Header says Cron 5 and manuel call it |
| `cron.evening.realtime-retry.php` | Header says 20:00 SAST |
| `cron.friday.odbc-test-cleanup.php` | Marked test-data cleanup |
| `cron.fix.approved.suggestions.php`, `fix_approved_suggestions.php` | Suggestion repair. Behaviour not read in full |
| `cron.daily.repeater.update.php` | Placeholder print |
| `cron.daily2.php`, `cron.daily.jobs.php`, `Zcron.daily.php`, `includes/cron.daily.php` | Legacy. May doc says not in the live crontab |
| `cron.DataDumps.php` | Dump variant beside Cron 6 |
| `cron.CashFlow - B VAT.php`, `cron.CashFlow - B VAT Update.php` | VAT copies |

May document schedule (SAST), not stored as crontab in Git:

| When | Script |
|---|---|
| 07:00 and 16:00 daily | `cron.daily.orderauth.php` |
| 17:00 on the 15th | `cron.daily.ordercancel.php` |
| 04:00–05:10 Sun–Fri | Cron 1 through cash flow, ten minutes apart (Cron 4 at 04:40, Cron 5 at 04:50) |
| 08:00–09:00 Saturday | Same chain later |
| Every 5 minutes 07:00–20:00 Mon–Fri | Repeater placeholder |
| On demand | `cron.daily.manuel.php` |

`MAILTO` `pieter@overdrive.co.za` is **Documented** as OS cron mail, not as application mail.

## Auth and session

- Session save path comes from the host case (`SESSION/` under the document root, or `__DIR__/SESSION` for localhost and `127.0.0.1`).
- Logged-in user object is `$_SESSION["ONLINE"]`. Changelog notes a legacy `USER` fallback in some pages.
- Company id is `ONLINE->OWCOMPANYID`. Security group id is `ONLINE->SECURITYGROUPID` (`http_cron_gate.php`). Suggestion approval uses `ONLINE->ID`.
- Password storage and expiry rules live in `_nemo.password.cls.php` (not fully read). Login calls `checkPasswordMatch` and `checkPasswordExpiry`.
- MFA tables are in `MFATables.sql`. Whether that script has been applied on production is **Unknown / Requires Confirmation**. The login query selects `blnMFAEnabled` and `blnMFARequired`, so production MySQL must have those columns or login fails.

## Files and SharePoint

`getFilesystemBase()`:

- Host contains `localhost`, or is `127.0.0.1` or `::1`: `<project>/indawo_filesystem/`.
- Otherwise: `/var/www/html/indawo_filesystem/`.

`Foreman::publicFileUrl()` builds `SHAREPOINT_SITE + "/" + library + "/" + encoded relative path`. It does not call a SharePoint API.

`folderUiState()` returns `icon`, `create`, or `archive_alert` as in `business-rules.md` BR-036 and BR-037.

Gitignored runtime output: `orders/`, `RequestInvoice/`, `ProfitOverviewPDF/`, `SESSION/`, `JobReport/`, `.env*`, `*.log`. `Cash Flow Report/` is both gitignored and present in the commit.

## Testing and quality

| Check | What exists |
|---|---|
| CLI regressions | `php tests/ett_odbc_regressions.php`. Covers NULL ratings, empty ints, client SQL shape, order zero-dates, supplier `GROUP BY`, toolbar buttons, SharePoint URLs, folder relocate in a sandbox. The script includes `_nemo.cls.php`, so it expects a bootable environment |
| PHPUnit | Not in `composer.json` |
| SonarQube | Pipeline step on branch pattern `{master}` only. Remote heads on 2026-10-12: `main`, `development`, `staging`, `feature/odbc-connections-dashboard`. No `master` head. **Unknown** whether any pipeline runs for `main` |
| Display errors | On for every host case, including the public hostname |

## Security notes (no secret values)

- `system.php` localhost, `127.0.0.1`, `localhost:8888`, and `default` contain SQL and MySQL passwords in source. Production-named cases use `.env`. **Requires Confirmation** and removal from Git. Silicon Ark does not copy the values.
- `docs/schema/README.md` sample command contains a database password. Not copied here.
- `display_errors=1` on `online.indawo.biz` can expose errors to browsers.
- `ODBC_connections.php` is not in the Apache deny list. HTTP cron files are gated. The dashboard's own authentication was not fully read. README presents it as an admin tool, with a local URL example and no extra login of its own described in the opening section.
- Job suggestion accept concatenates `strFieldName` into an `UPDATE`. **Implemented**.
- Apache denies `.env` and several debug scripts. That deny depends on Apache honouring `.htaccess`.

## In-repo documents

| Path | Use |
|---|---|
| `docs/INDAWO-Online-Technical-Documentation.md` | 2026-05-26 architecture. Trust it for the nightly entity list. Do not trust it for "no queue" |
| `docs/INDAWO-Realtime-Sync-Queue-Retry-Implementation-Plan.md` | 2026-06-09 plan. Code has moved past section 1 |
| `docs/Section-1-Live-Changes-Log.md` | Section 1 execution log |
| `docs/old/` | Older copy of the robustness note |
| `docs/ONLY-FULL-GROUP-BY-Cursor-Plan.md` | SQL mode plan |
| `docs/schema/README.md` | How to export live schema. Contains a sample password |
| `README-odbc-dashboard.txt` | How to use `ODBC_connections.php` |
| `setup_composer.md` | PHP 8.4 and Composer install |
| `CHANGELOG.md`, `README.md`, `NewKnowledgeBase.md` | PHP 8.4 notes through 2026-02-11 |

## Requires confirmation

1. Production host, document root, PHP binary, and whether `SERVER_NAME` is `online.indawo.biz`, `10.50.1.4`, or `192.168.0.28`.
2. Whether `MFATables.sql` and `sysSyncQueue` have been applied on production MySQL.
3. Live crontab versus the May schedule.
4. Whether Sonar is expected on `main`.
5. Difference between `development`, `staging`, and `main`.
6. Which PDF library each screen actually instantiates (vendored mPDF 5, Composer mPDF 8, or Html2Pdf).