# PRD — crimemapping-webportal Evidence date: 2026-10-18. Source: `git@bitbucket.org:overdrive/crimemapping-webportal.git`, `master` at `f8a20d4` (2024-02-05, "Add notes to export crime log"). There is no prior PRD in the repository or in Silicon Ark. Every requirement below was recovered from the code, migrations, seeds, views, and the example `env`. Provenance is marked on each item. ## Product purpose Staff record crimes for an organisation, attach incident types, verify them, show them on a map, and export the list. A separate HTTP client can register a person and submit a crime with photos. A GIS page and a GIS API expose town-level crime points. Provenance: **Implemented**. A written product statement was not found. The README title is "Admin Overdrive Starter". ## Users and roles Role titles other than the seed are stored in `user_role` and are not in the repository. Only role id 1 is seeded. | Role | What the code does | Evidence | Provenance | |---|---|---|---| | Developer | Seeded `user_role.id = 1`, name `Developer`. Public registration also inserts `inputRole = 1` | `Seeds/UserRole.php`, `Welcome::registration` | Documented in the seed for the name. Registration behaviour is **Implemented** | | Member | Role id 2. The only place the word "Member" appears is an error when role id 4 tries to create a user with any other role | `Users::createUser` | **Inferred** name. The id check is **Implemented** | | Organisation-scoped user | Role id 4. User list, crime-log list, crime-log export, and crime map are limited to `users.organisation_id`. A commit subject calls this access "Org Managers" | `UserModel::getUser`, `CrimeLogModel::getCrimeLogs`, `CrimeMapModel::getCrimeMapIncidents`, commit `026bacf` | **Implemented** for the id. The title "Org Manager" is **Inferred** from the commit subject | | Any other role | Sees all organisations' users and crimes. Menu visibility still depends on `user_access` | same queries, `else` branch | **Implemented** | | API registrant | `POST apiusers` inserts `role_id = 2`, `active = 1`, `created_by = 1` | `apiUsers::create` | **Implemented** | | GIS vendor | `POST apigis/authenticate` looks up `users.name` and checks the posted `api_key` with `password_verify` against `users.password` | `Apigis::authenticate` | **Implemented** | | Anonymous browser | Can open `/`, `register`, and every `api*` route. Login and menu filters skip those prefixes | `Config/Filters.php` | **Implemented** | Whether production `user_role` rows use these titles is **Unknown / Requires Confirmation**. ## Main workflows ### W1 — Staff sign-in Trigger: browser opens `/`. Default controller in routes is overridden: `$routes->get('/', 'Welcome::index')`. Inputs: `inputEmail` (required to attempt login), `inputPassword`. Behaviour (`Welcome::index`): 1. If `session.isLoggedIn` is already true, redirect to `home`. 2. If `inputEmail` is missing, show `common/login`. 3. Look up `users` by email. Compare the password with `password_verify`. 4. On success, session stores `email`, `role_id`, and `isLoggedIn = true`, then redirects to `home`. 5. On failure, flash "Your ID or Password is Wrong" and redirect to `/`. The lookup does not test `users.active`. A user with `active = 0` can still sign in if the password matches. `GET welcome/logout` (auto-routed) destroys the session. The route file does not declare `logout`. Output: session cookie. Sessions are configured for the database handler, table `sessions`, match IP, regenerate-destroy. Provenance: **Implemented**. ### W2 — Public registration Trigger: `GET register`, then `POST register`. Inputs: `inputFullname`, `inputEmail`, `inputPassword`, `inputPassword2`. Validation: `inputEmail` must be unique on `users.username` (the users table column is `email`, not `username`), password required, confirmation must match. On success, `UserModel::createUser` is called with `inputRole = 1` and `inputUsername` set from the email. `createUser` reads `inputName`, `inputSurname`, `inputCellphone`, and `inputOrganisation`, which this form does not post under those names. Provenance: **Implemented**, and the field names disagree with `createUser`. Whether registration succeeds against a real database is **Unknown**. The route is not behind the login filter. ### W3 — Menu authorisation Trigger: any request that is not in the filter exception list. `Authentication` redirects to `/` when `isLoggedIn` is not true. `Authorization` takes URI segment 1, finds `user_menu.url`, and requires a `user_access` row for the session `role_id` and that menu id. No matching menu redirects to `/`. A menu with no grant redirects to `blocked` (`common/forbidden`). `home`, `register`, `/`, and all `api*` prefixes are excluded from the grant check. `users` is excluded from the login check and is not excluded from the grant check. CSRF and honeypot filters are commented out. A `cors` filter on every request sets `Access-Control-Allow-Origin: *` and answers `OPTIONS` by exiting. Provenance: **Implemented**. ### W4 — Record a crime (staff) Trigger: menu URL `crimeLog` (auto-routed `CrimeLog::index`), then `POST crimeLog/createCrimeLog`. The list shows id, reporter name, organisation, report date, address, verified, active, updated at, and an Update action. "Log New Crime" opens a modal. Inputs posted include `userID`, `inputOrganisation`, `inputReportDate`, `inputCoordinatesLong`, `inputCoordinatesLat`, `inputCoordinatesAddress`, `inputVerified`, `inputActive`, `inputNote`, `inputIncidentString` (comma-separated incident type ids). Behaviour: 1. Missing verified or active checkboxes become 0. An empty report date becomes `0000-00-00 00:00:00`. `T` in a datetime-local value is replaced with a space. 2. `created_by` and `updated_by` are the signed-in user's id. 3. Insert into `crime_log`. The insert does not set `parent_id`. 4. Each incident id is inserted into `crime_incident_map`. 5. Flash success or failure and redirect to `crimeLog`. Role id 4 sees only rows whose `organisation_id` equals their own. Other roles see every crime. The single-record query used by child and image lookups does not apply that organisation filter. Provenance: **Implemented**. ### W5 — Update, verify, and delete a crime Trigger: Update on a row, `POST crimeLog/updateCrimeLog`. Delete posts `crimeLog/deleteCrimeLog` with `crimeLogID`. Update behaviour (`CrimeLogModel::updateCrimeLog`): 1. If the stored `verified` is already 1, it stays 1 even when the checkbox is off. 2. Otherwise verified follows the checkbox. 3. When verified is 1 and `inputVerifiedByID` / `inputVerifiedDate` are posted, those values are stored. Otherwise verified user id becomes 0 and verified date becomes `0000-00-00`. 4. Posted incident ids are added. The update path does not remove incidents that were dropped from the string. Removal is a separate call, `crimeLog/removeIncidentFromCrime/{id}`. 5. `inputCrimeLogChildrenString` is split on commas and each id is written as `parent_id` of the child, pointing at this crime. A value without a comma is still passed to `addChildToCrimeLog`. Delete removes the `crime_log` row, matching `crime_incident_map` rows, and matching `image_log` rows. It is not a soft delete. The export controller's delete method expects the id in the URL and redirects to `crimeLog`, not to the export page. The crime-log modal prints a delete button for every role. Role id 1 gets one element id; every other role gets `btnOrgDelete`. Both submit the form. What the button's script does differently was not fully traced. Commit `64847b2` says delete restrictions were updated. Provenance: **Implemented**. ### W6 — Link same-day crimes Trigger: from the crime modal, `POST crimeLog/getCrimeLogPotentialChildren` and `getCrimeLogChildrenTable`. Potential children are crimes whose `report_date` contains the same calendar date (`LIKE` on the date portion). The child table lists crimes that share the parent id, with their incident-type badges, and a remove action that sets the child's `parent_id` to 0. Provenance: **Implemented**. ### W7 — Crime map Trigger: `CrimeMap::index` (auto-routed from menu URL `crimeMap`). Default date range is the current calendar month. `crimeMap/getCrimeMapIncidents` accepts a JSON argument (auto-routed method parameter) with `incidentTypeIDString`, `startDate`, `endDate`, and `attempted`. Behaviour: 1. Ids `0` and `-1` are stripped before the SQL `IN` list. In the filter UI, `-1` means "Attempted" and `0` means "All", and either one disables the individual checkboxes. 2. `attempted = 2` means incident types with `attempted` 0 or 1. Any other value is matched exactly. 3. Role id 4 adds `crime_log.organisation_id =` the user's organisation. 4. Rows are collapsed by `crime_log_id`. Incident names (`incident_type.name_group`) are concatenated. 5. The view loads Google Maps with `libraries=places`, MarkerClusterer from a rawgit CDN, and `assets/js/crimeMap.js`. Provenance: **Implemented**. `incident_type.attempted`, `name_group`, and `color` are not columns in the incident-type migration. ### W8 — GIS map page Trigger: `CrimeMapGis::index`. The view is a sidebar shell plus script tags: - `https://sungis10.stb.sun.ac.za/crimestatic/ol/v7_4_0/ol.min.js` (OpenLayers 7.4.0) - `https://sungis10.stb.sun.ac.za/crimestatic/main.js` as a module - Google Maps with the same env key - MarkerClusterer The controller does not query crimes. What the external script reads is **Unknown**. Provenance of the page shell: **Implemented**. Commit `3b92c10` ("implement theo map") is the likely introduction. ### W9 — Export the crime log Trigger: `CrimeLogExport::index`. Title in the view data is still "Crime Log". Columns: name, organisation, report date, report time, longitude, latitude, address, incident, verified, notes, active, updated at. DataTables Buttons offer copy, CSV, Excel, PDF, and print. There is no "Log New Crime" button on this view. The Notes cell prints `crime_log.note`. That column was added to the export query in the tip commit `f8a20d4`. For roles other than 4, the query inner-joins `crime_incident_map` and `incident_type`, so a crime with two incidents appears twice and a crime with none is omitted. For role 4, the query does not join incident types and does not select `incident_name`, while the view still prints that key. Provenance: **Implemented**. ### W10 — Organisations, suburbs, and locations Trigger: `organisations` and `locations` menu URLs. An organisation has name, contact number, address, town, suburb text, parent id, note, active flag, and `api_key`. Delete sets `active = 0`. Children are other organisations whose `parent` is set to this id. Only organisations with `parent = 0` appear in the "add sub organisation" dropdown. Suburbs come from `locations` (main-place name `mp_name`) filtered by town (`mn_name LIKE` the typed town). Links are rows in `org_location_map`. Removing a suburb sets `org_location_map.active = 0`. Adding inserts a new row. `locations` itself is a catalogue of South African municipal codes (`sp_*`, `mp_*`, `mn_*`, `dc_*`, shape length and area). The list is capped at 10,000 rows. There is no migration for `locations` or `org_location_map`. Provenance: **Implemented**. The meaning of the code prefixes is **Inferred** from the field names (main place, municipality, district). Commit `66b33b4` cites `CRIM-49` for organisation location changes. ### W11 — Incident types Trigger: `incidentType`. Create and update store `name`, `note`, and `active`. Delete sets `active = 0`. `created_by` is set to the session **role id**, not the user id. List and map queries filter on `incident_type.attempted` and order by `name_group`. The create method does not write `attempted`, `color`, or `name_group`. Provenance: **Implemented** for name/note/active. The extra columns are **Implemented** in queries and **absent** from migrations. ### W12 — Users, profile, and roles Trigger: `users`, `users/displayProfile`, `userRoles`, `users/userRoleAccess`. Create requires `inputEmail` unique on `users.email`. Password is `password_hash` with `PASSWORD_DEFAULT`. Update replaces the password only when `inputPassword` is non-empty. When the signed-in role is 4: - The new user's role must be the posted hidden value 2, or the action stops with the Member error. - The new user's organisation must equal the signed-in user's organisation. Update of an existing user does not repeat those checks. Profile update (`users/updateUserProfile`) can change password and note. Phone uniqueness is checked by `users/validatePhoneNumber`. Roles are free-text names in `user_role`. Menu category, menu, and submenu grants are rows in `user_access`, edited from the access screen. Provenance: **Implemented**. ### W13 — Activity log Trigger: `activityLog`. Staff manually create rows with user, IP address, type, and description. Login does not write this table. Delete removes the row. Provenance: **Implemented** as a manual CRUD screen, not as an audit trail. ### W14 — API user registration and login `POST apiusers` (resource `create`) does not read a bearer token. If `cellphone` is already stored, the response text says "Cellphone Number Already Exist" with status 403 inside a created-response. Otherwise it inserts role 2, the posted organisation, name, surname, email, cellphone, and a hashed password. `POST apiusers/login` reads JSON `cellphone` and `password`. On match it builds an HS256 JWT whose payload is the cellphone and `exp = time() + 60000` (60,000 seconds), stores that token on `users.token`, and returns `{id, token}`. Wrong password and unknown cellphone use the "not found" response. `apiusers/getUser` returns the user row for the bearer token. `apiusers/logout` clears `users.token`. `ForgotPassword` and `ChangePassword` are empty. The signing secret is the library default `'secret'`. It is not read from the environment. Provenance: **Implemented**. `users.token` is not in the users migration. ### W15 — API submit a crime `POST apicrimes` requires a bearer token that passes `JwtLibrary::is_jwt_valid`. Inputs: `report_date` as two comma-separated parts (date, then time), `user_id`, `organisation_id`, coordinates, address, `note`, JSON `incidents`, and files in `files[]`. Behaviour: 1. `parent_id` 0, `verified` 0, `active` 1. 2. Each incident id is inserted into `crime_incident_map`. 3. Images must be jpeg, jpg, or png and at most 12,097,152 bytes. Wider than 400px images are resampled. Files are stored at `assets/img/crime_images/{organisation_id}/{crime_id}/` with mode `0777`. A row is inserted into `image_log`. 4. The success response is sent even when the insert id is empty; a failure payload is overwritten by the success payload. `GET apicrimes` calls `ApiCrimeModel::getUsers()`, and that model has no `getUsers` method. `GET apicrimes/{id}` returns the crime, its incident types, and a second list built by `getCrimeImages`, which queries `crime_incident_map` again rather than `image_log`. Update and delete on this controller are commented out. Provenance: **Implemented**. ### W16 — API incidents and organisations `GET apiincidents` and `GET apiincidents/{id}` require a valid bearer token. `ApiIncidents::create` is a copy of the crime-create path and references `ApiCrimeModel` without a `use` statement in the portion read. It is not the incident-type catalogue create. `GET apiorganisations` returns every organisation with `findAll()` and does not check a token. `api_key` is a column on that table, so it is included in the row. Show, create, update, and delete check the bearer token. Create's success text says "Employee created successfully". The route-file comment that describes `POST organisations/login` does not match a method on `Organisations` or `ApiOrganisations`. Provenance: **Implemented**. The route comment is **Documented** in `Routes.php` and disagrees with the code. ### W17 — GIS vendor API `POST apigis/authenticate` with JSON `vendor` and `api_key`. Vendor is matched to `users.name`. The key is checked with `password_verify` against `users.password`. A JWT is stored on `users.token` and returned with the user id. `apigis/getActiveTowns` returns `COUNT` of organisations grouped by `organisations.town`. A suburb-based query against `org_location_map` is commented out, with a note that not every organisation has a map row. `apigis/getUserLocation` with JSON `email` returns that user's organisation town and the `mp_name` list from `org_location_map`. `apigis/getTownCrimeLog` with JSON `town` and optional `page` returns up to 100 crimes whose organisation town equals that string. Each item is shaped as a Feature with a Point (longitude, latitude), report date, address, and incident type name. `total_rows` and `total_pages` are attached on the same array. The page size is 100. Town is concatenated into the SQL string. All three reads require a bearer token. Provenance: **Implemented**. ## Functional requirements ### FR-001 — Staff can sign in with email and password Who: staff user. Trigger: `POST GetLogin` or `POST /` (the only declared login route is `POST GetLogin` to `Welcome::index`; the form action was not separately confirmed). Inputs: email, password. Behaviour: W1. Output: session and redirect to `home`. Validation: email required. Error: flash message, stay logged out. The `active` flag is not checked. Evidence: `Welcome.php`, `Filters.php`, `env` session settings. Implementation: **Implemented**. Confidence: **High**. ### FR-002 — Unsigned visitors can open a registration form Who: anonymous. Trigger: `GET/POST register`. Behaviour: W2. Output: attempt to insert a user with role id 1. Evidence: `Welcome::registration`, `Routes.php`. Implementation: **Implemented**, field names disagree with `createUser`. Confidence: **Medium** on whether a row is actually inserted. ### FR-003 — A signed-in user only opens menus granted to their role Who: any signed-in role. Trigger: URI segment 1. Behaviour: W3. Output: page, or redirect to `/` or `blocked`. Evidence: `Filters/Authorization.php`, `user_access`. Implementation: **Implemented**. Confidence: **High**. ### FR-004 — Staff can create and edit crimes with incident types, a note, coordinates, and an address Who: a role whose menu grant includes `crimeLog`. Trigger: W4 and W5. Inputs: as in W4. Output: `crime_log` and `crime_incident_map` rows, flash message. Evidence: `CrimeLog.php`, `CrimeLogModel.php`, `common/crimeLogList.php`. Implementation: **Implemented**. Confidence: **High**. ### FR-005 — Role id 4 sees crimes, the map, the export, and users only for their organisation Who: role id 4. Trigger: list and map queries. Other roles are not filtered. Single-id crime fetches are not filtered. Evidence: `CrimeLogModel`, `CrimeLogExportModel`, `CrimeMapModel`, `UserModel`. Implementation: **Implemented**. Confidence: **High**. ### FR-006 — Once a crime is verified, a later edit keeps it verified Who: staff editing a crime. Trigger: update when stored `verified` is 1. Output: `verified` remains 1. Evidence: `CrimeLogModel::updateCrimeLog`. Implementation: **Implemented**. Confidence: **High**. Whether this one-way rule was requested is **Unknown**. ### FR-007 — Staff can attach and detach same-day child crimes Who: staff on the crime modal. Trigger: W6. Output: `crime_log.parent_id`. Evidence: `CrimeLog.php`, `CrimeLogModel::getCrimeLogsPotentialChildren`. Implementation: **Implemented**. Confidence: **High**. ### FR-008 — Staff can plot crimes for a month, filtered by incident type and attempted flag Who: role with menu `crimeMap`. Trigger: W7. Output: JSON markers consumed by `crimeMap.js`. Evidence: `CrimeMap.php`, `CrimeMapModel.php`, `common/crimeMap.php`. Implementation: **Implemented**. Confidence: **High**, except the unmigrated `attempted` / `name_group` columns. ### FR-009 — Staff can open a GIS map that loads the Stellenbosch crime script Who: role with menu `crimeMapGis`. Trigger: W8. Output: HTML shell. The map data contract belongs to the external script. Evidence: `CrimeMapGis.php`, `common/crimeMapGis.php`. Implementation: **Implemented** as a host page. Confidence: **Medium** on what the external script does. ### FR-010 — Staff can export the crime log, including notes, from the browser Who: role with menu `crimeLogExport`. Trigger: W9. Output: DataTables copy, CSV, Excel, PDF, or print. Notes column is the tip commit. Evidence: `crimeLogListExport.php`, `CrimeLogExportModel.php`, commit `f8a20d4`. Implementation: **Implemented**. Confidence: **High**. The role-4 incident column mismatch is part of the same implementation. ### FR-011 — Staff can manage organisations, parent links, and suburbs Who: role with menu `organisations`. Trigger: W10. Output: `organisations` and `org_location_map`. Evidence: `Organisations.php`, `OrganisationsModel.php`. Implementation: **Implemented**. Confidence: **High**. ### FR-012 — Staff can maintain a locations catalogue and incident types Who: roles with those menus. Trigger: W10 and W11. Soft-delete sets `active = 0` for incident types. Locations delete is a hard delete in `LocationsModel::deleteLocation`. Evidence: `Locations.php`, `IncidentType.php`. Implementation: **Implemented**. Confidence: **High**. ### FR-013 — Role id 4 can create only Member users inside their organisation Who: role id 4. Trigger: `POST users/createUser`. Behaviour: W12. Output: user row or the Member / organisation flash error. Evidence: `Users::createUser`. Implementation: **Implemented**. Confidence: **High**. ### FR-014 — A client can register and sign in by cellphone and receive a bearer token Who: API caller. Trigger: W14. Output: user id and JWT. Cellphone must be unique on create. Evidence: `apiUsers.php`, `JwtLibrary.php`. Implementation: **Implemented**. Create is not token-gated. Confidence: **High**. ### FR-015 — A token holder can submit a crime with incident ids and images Who: API caller with a valid JWT. Trigger: W15. Output: `crime_log`, `crime_incident_map`, files on disk, `image_log`. Evidence: `apiCrimes.php`. Implementation: **Implemented**. Confidence: **High** for the create path. List and image-read paths call methods that do not return images. ### FR-016 — A GIS vendor can list towns and page crimes for a town Who: caller who authenticated with `apigis/authenticate`. Trigger: W17. Output: town counts, and GeoJSON-like features, 100 per page. Evidence: `Apigis.php`, `ApiGisModel.php`. Implementation: **Implemented**. Confidence: **High**. ### FR-017 — Developer tools can add menus and scaffold CRUD Who: a role granted `menuManagement` or `crudGenerator`. The seed grants those menus to role id 1. Trigger: the developer screens. Output: menu rows, and generated controller/model/view text from `developers/crudGenerator.php`. Evidence: `Developers/MenuManagement.php`, `Developers/CRUDGenerator.php`, `Seeds/UserMenu.php`. Implementation: **Implemented**. Confidence: **High**. ## Integrations | System | Purpose | Direction | Auth | Failure | |---|---|---|---|---| | Google Maps JavaScript | Map, places, marker clustering on crime log and both map pages | Browser to Google | `GOOGLE_MAP_API_KEY` in the environment. Not present in the committed `env` | Page still renders; map script fails closed in the browser. Not handled in PHP | | DataTables CDN | Table and export buttons | Browser to cdn.datatables.net and code.jquery.com | None | Export buttons do not appear | | Stellenbosch GIS static host | OpenLayers and `main.js` on the GIS page | Browser to `sungis10.stb.sun.ac.za` | None in this repo | GIS map does not load. No fallback in the view | | MarkerClusterer rawgit CDN | Cluster markers | Browser to cdn.rawgit.com | None | Clustering fails | | MySQL | All persistent data | App to `localhost` in the example `env` | username `root`, empty password in the example file | CodeIgniter database error | | Bitbucket SonarQube pipe | Scan `master` | Pipeline to `SONAR_HOST_URL` | `SONAR_TOKEN` | Pipeline step fails. Deploy step is separate | | FTP | `git ftp push` of `master` after the scan | Pipeline to `FTP_HOST` | `FTP_USERNAME`, `FTP_PASSWORD` | Deploy step fails. The `git ftp init` line is commented out | AWS SDK is a Composer dependency and is not called from `app/`. ## Constraints - PHP 8.0 or newer, extensions intl, curl, json, mbstring, mysqlnd, xml (`README.md`). - Auto-routing is on, so any public controller method is reachable in addition to `Routes.php`. - CSRF protection is off. - JWT secret is hard-coded in `JwtLibrary`. - Crime photos are stored on the local disk, not in object storage. - Several queries concatenate request values into SQL (`getTownCrimeLog` town, map date and incident id list, organisation town search). ## Behaviour that the code does and no requirement describes These are **Implemented**. They are not written down as approved rules anywhere in the repo. - Public registration aims at role id 1. - `GET apiorganisations` and `POST apiusers` do not require a token. - Login ignores `users.active`. - Verified crimes stay verified. - Geocoder calls in `crimeLogList.php` are commented out, and `getFormattedAddressFromCoordinates` is never called. Commit `7e3d7b9` says geocoding was disabled. Address is whatever was posted in `inputCoordinatesAddress` or `coordinates_address`. - The dashboard view `common/home.php` has an empty content section. ## Open points - Production base URL, database name, and whether the FTP target is still live. - Live `user_role` names for ids 2, 3, 4, and any others. - Whether `crimemapping-webportal` and the admin inside `crimemapping_website` are the same product line. - Contents of Jira `SI` / `CRIM` and Confluence `AO`. - What `sungis10` `main.js` expects from this portal. - Whether the unmigrated columns and tables were applied by hand on the databases that run this code.