# PRD — CCID (Cape Town CCID contact database) Evidence date: 2026-10-19. Source: `git@bitbucket.org:overdrive/ccid.git`, `master` at `1d3e6bf` (2026-04-16, "Add global select checkboxes"). There is no prior PRD in the repository or in Silicon Ark. Every requirement below was recovered from the code. Provenance is marked on each item. Secrets that are hardcoded (database passwords in `system.php`) are not copied into this document. ## Product purpose CCID lets signed-in staff find and maintain organisations and people in the Cape Town Central City Improvement District, and run saved searches across that directory. The login page is titled Login. Help is a mailto link to `reception@capetownccid.org`. Provenance: **Implemented** (`login.php`). After a successful login the browser is sent to `contact2024.php`. A commented branch used to send security group `4` to that page and everyone else to search. Both arms now go to `contact2024.php`. Provenance: **Implemented** (`login.php`). ## Users and roles | Role | What they do | Evidence | Provenance | |---|---|---|---| | Signed-in staff user | Uses whatever menu URLs their security group may view | `Nemo::iniSecurity`, `NemoMenu` | Implemented. The group names and the menu rows are in the database, not in Git | | Inactive user | Cannot log in. Message: "User account is inactive." | `login.php` checks `sysUser.blnActive` and `sysSecurityGroup.blnActive` | Implemented | | User with no view right on a URL | Sees "You do not have sufficient access to view this page" | `message.php` `MID=r09` | Implemented | | Anonymous visitor | Can open `login.php`, submit a reset email, and open `resetPassword.php` with a reset key | Those scripts unset `$_SESSION["USER"]` or never require it | Implemented | | Security group `4` | Company list hides the Make Inactive button when the group id is `4`. The same check on `contact2024.php` is commented out, so that list shows Make Inactive to every signed-in user who can open the page | `company.php`, `contact2024.php` | Implemented. What group `4` is named is **Unknown / Requires Confirmation** | Permissions on a menu URL are five flags: `blnView`, `blnNew`, `blnSave`, `blnDelete`, `blnSpecial`. They are stored per security group in `sysSecurity`. Provenance: **Implemented**. Which flag the toolbar actually consults, beyond the view gate in `Nemo::iniSecurity`, is **Unknown / Requires Confirmation** (the toolbar is framework code driven by those flags; this PRD does not restate every button). The header shows the signed-in `strUser` and a logout link. Provenance: **Implemented** (`Nemo::Header`). ## Main workflows ### W1 — Sign in Trigger: visitor opens `login.php` and posts `Action=Login` with `strEmail` and `strPassword`. 1. Any existing `$_SESSION["USER"]` is cleared at the start of the script. 2. Load `sysUser` joined to `sysSecurityGroup` where `strEmail` matches and the group is active. 3. If no row: "Login Details are invalid." A login row is written with result "Login failed: User not found or SG not active". 4. If `blnActive` is not `1`: "User account is inactive." 5. If the account is active, `NemoPassword::checkPassword("User", UserID, password)` compares SHA-256 of `salt + password` with the latest `sysPassword` row. 6. On success, session `USER` stores `ID`, `USERNAME` (`strUser`), `EMAIL`, and `SECURITYGROUPID`. The browser is sent to `contact2024.php`. 7. On a bad password, the page shows "Username or Password incorrect." and appends the result of `CheckSecurityLock`. Provenance: **Implemented**. `CheckSecurityLock` counts recent `sysLogin` rows for that user. The query joins `sysLogin.strUsername` to `sysUser.strEmail`. `tblLoginInsert` writes `strUsername` from the global `$strUsername`. `login.php` sets `$strEmail`, not `$strUsername`. The failed-attempt counter therefore does not see the rows this login page writes, unless some other code sets `$strUsername`. Provenance: **Implemented**. Whether production has a different login path that does set `$strUsername` is **Unknown / Requires Confirmation**. The function tells the user the account is suspended after five consecutive "Incorrect password" results, and it sends a security-warning email. The line that would save `blnLogin = 0` is commented out. Login itself never reads `blnLogin`. Provenance: **Implemented**. The suspension message does not lock the account. There is no captcha and no second factor. ### W2 — Reset a forgotten password Trigger: on the login page, "Forgot Password?" reveals an email field. The browser POSTs `ajaxfunctions.php` with `type=SendResetEmail`. - Empty email: the page says "Please type in your email address." (client side). - Unknown email: response `0`, shown as "does not exist in our database." - Known email: a new random password is stored in `sysPassword` with `dtPassword` `2010-01-01`, and a message is sent from the template named "Reset Password". The link is `{BASE_URL}/resetPassword.php?strEmail={email}&ResetKey={hash}`. Response `1`. `ajaxfunctions.php` does not require a session for this action. Provenance: **Implemented**. On `resetPassword.php` the reset key must equal the stored hash, and `getLastPassword` only accepts a token whose `dtLastEdit` is inside `PASSWORD_Reset_Interval`. The seeded default for that interval is `60 MINUTE`. Live value is whatever is in `sysSettings`. Provenance: **Implemented** seed in `_nemo.password.cls.php`; live value **Unknown / Requires Confirmation**. The new password must pass the history check (`PASSWORD_MaxHistory`, seeded default `8`) and the browser strength check: minimum length (seeded default `8`), at least one lower-case letter, one upper-case letter, and one digit, and must not contain a restricted word (seeded default `password` and `123`, case-insensitive). Provenance: **Implemented**. ### W3 — Find a contact Trigger: signed-in user opens `contact2024.php`. The list reads `tblContact2024`, left-joined to `tblCompanyContact`, `tblCompany`, and `tblPrecinct`. Default status filter is Active (`frStatus` default `1`). Precinct default is all (`0`). Search text is split on spaces. Each word must match at least one of: contact id, title, name, surname, email, telephone, cell, company name, company physical address. Words are combined with AND. A single word uses the same columns. Provenance: **Implemented** (`Contact::getList` in `includes/contact2024.cls.php`). The list is grouped by `ContactID`, so a person linked to several companies appears once. The company, address, and precinct columns come from the join. Which company wins when there are several is **Unknown / Requires Confirmation** (no `MIN`/`MAX` on those columns; the engine's loose `GROUP BY` decides). Columns shown: name, surname, company, telephone, cell, email, address, precinct, status, last edit. Provenance: **Implemented**. Export posts `Action=Export` and returns the same list with paging off, as `application/ms-excel`. Provenance: **Implemented**. ### W4 — Save a contact Trigger: Save on the contact detail screen. `Contact::Save`. The detail form is generated from `tblContact2024` (title field used as the heading key is `strSurname`). The page then adds company fields that are not columns of `tblContact2024`: company name, precinct, emergency number, website, physical address, company email, company telephone, position, category type, and category. Provenance: **Implemented** (`contact2024.php`). Rules in `Contact::Save`: - A new contact whose `strName` and `strSurname` already exist is rejected with "{name} {surname} already exists". The check does not use email, and it does not run when `ContactID` is already set. Provenance: **Implemented**. - Groups: all `tblContactGroup` rows for the contact are deleted and re-inserted from the checked boxes. Provenance: **Implemented**. - If `CompanyID` is posted, that company row is updated (name, and any of precinct, telephone, email, emergency number, website, address that were non-empty). The position on `tblCompanyContact` is updated when the link already exists. A posted category deletes every `tblCompanyCategory` row for that company and inserts one. Provenance: **Implemented**. - If a company name is posted, every `tblCompanyContact` row for this contact is deleted first. The company is then found by exact `strCompany`. If it exists, it is updated and one link is inserted. If it does not exist, a new active company is inserted and one link is inserted. A posted category again replaces all categories on that company with one row. Provenance: **Implemented**. - Precinct posted as `0` is stored as precinct id `5`. Provenance: **Implemented**. What precinct `5` is named is **Unknown / Requires Confirmation**. - If there is no company name, and a category other than `0` or a precinct other than `0` was posted, the save message adds "Please add a company name before saving additional company details". The contact row itself has already been saved. Provenance: **Implemented**. `getcompanyDetails` returns one company row (`getRowSQL`). The edit form therefore shows a single company even when `tblCompanyContact` has several links. Provenance: **Implemented**. The right-hand pane lists other contacts of that same company (with a Remove link) and the contact's groups, when both the contact and a company id exist. Provenance: **Implemented**. ### W5 — Maintain a company and its people Trigger: `company.php`. The detail form is generated from `tblCompany`. Categories are a checkbox list (`Company::renderCategoryList`). Saving replaces `tblCompanyCategory` for that company from the checked boxes. Group saving on the company is commented out. Provenance: **Implemented** (`Company::Save`). When the company already has an id, the page lists its contacts from `tblContact2024` via `tblCompanyContact`, with View and Remove. Add Contact requires a contact and a position (browser check and server check). The same company, contact, and position string cannot be inserted twice. Provenance: **Implemented** (`Company::AddContact`). Remove deletes the `tblCompanyContact` row matched on company, contact, and position, then returns to the company edit screen. Provenance: **Implemented**. Make Inactive on the company list sets `tblCompany.blnActive = 0`. The confirm text and the result text say "contact(s)". Provenance: **Implemented**. The button is hidden when `SECURITYGROUPID` is `4`. Export works the same way as the contact export. Provenance: **Implemented**. ### W6 — Run a saved search Trigger: user saves a search on `search.php`, or chooses Run Query / Run Selected Query. A new search stores `refUserID` as the current user. Later saves keep the original owner (`refUserID` is only set when `SearchID` is `0`). Provenance: **Implemented** (`Search::Save`). Criteria are stored in `tblSearchField` with `strSearchType` of `CONTACT`, `CATEGORY`, or `GROUP`. Saving a type deletes that type's rows for the search and inserts the posted ones. Run Selected Query allows exactly one checked search. Zero checks: "No search selected." More than one: "Please select only one search." Provenance: **Implemented**. Results (`SearchResult::getList`) read `vieCompanyContact` (contact id and company id, plus any contact columns marked Show). Contact criteria are AND. "Not contain" uses `NOT LIKE`. "Is empty" matches NULL or `''`. Category filters are OR'd together when more than one category is selected; a single category filter stays a single predicate. A category with sub-categories and an empty sub-category is treated as a wildcard on the parent `strCategory`. Group filters stay AND (`strCriteria = 1` means the contact is in that group). Provenance: **Implemented** (`includes/search.result.cls.php`, commit `3aeb09f`). Show columns for categories and groups render Yes or No. Provenance: **Implemented**. The definition of `vieCompanyContact` is not in Git. Settings → Create Views runs every `sysSettings.strValue` whose `strSetting` starts with `vie`. Provenance: **Implemented** (`Settings::CreateViews`). The SQL text is **Unknown / Requires Confirmation**. ### W7 — Maintain lookups, users, and settings | Screen | Table | Delete control | Provenance | |---|---|---|---| | `category.php` | `tblCategory` plus `tblCategoryType` | Sets `blnActive = 0`. Message says "Records Deleted." Alias must be unique across `tblGroup`, `tblCategory`, and `tblField` | Implemented | | `group.php` | `tblGroup` plus `tblGroupType` | Sets `blnActive = 0`. Same alias rule | Implemented | | `precinct.php` | `tblPrecinct` | Sets `blnActive = 0`. Duplicate precinct name is rejected, including when editing the same row (the check does not exclude the current id) | Implemented | | `positions.php` | `tblPosition` | Sets `blnActive = 0`. Duplicate `strPosition` is rejected | Implemented | | `user.php` | `sysUser` | Sets `blnActive = 0`. Message says "Records Deleted." | Implemented | | `security.group.php` | `sysSecurityGroup` | Hard-deletes the group. Save rewrites every `sysSecurity` row for that group and can edit menu entity labels | Implemented | | `settings.php` | `sysSettings` | Save updates value and comment. The setting name is shown read-only on edit. Create Views runs stored view SQL | Implemented | | `my.profile.php` | current `sysUser` | Name and email are read-only. A new password requires the old password and must not match the last `PASSWORD_MaxHistory` hashes | Implemented | A brand-new user does not type a password on the user form. `User::ResetPassword` stores a random password dated `2010-01-01` and emails the reset link to `$_POST["strEmail"]`. Provenance: **Implemented**. Contact and company positions typed on those screens are free text on `tblCompanyContact.strPosition`. They are not a dropdown of `tblPosition`. Provenance: **Implemented**. Whether staff are expected to pick from the positions list is **Unknown / Requires Confirmation**. ## Functional requirements ### FR-001 — Email and password login The system authenticates an active user whose security group is active, using the latest salted SHA-256 password, and opens the contact list. - Actor: staff user. - Trigger: `Action=Login`. - Inputs: `strEmail`, `strPassword`. - Output: session `USER`, redirect to `contact2024.php`. - Validation: user row exists, user active, group active, hash matches. - Errors: invalid details, inactive user, incorrect password, plus the lockout text from `CheckSecurityLock` when that function returns it. - Evidence: `login.php`, `_nemo.password.cls.php`. - Implementation: Implemented. - Confidence: High. ### FR-002 — Session gate Any script that constructs `Nemo` (not `NemoBasic`) redirects to `login.php` when `$_SESSION["USER"]` is missing. - Evidence: `Nemo::__construct`. - Implementation: Implemented. - Confidence: High. `login.php`, `resetPassword.php`, and `ajaxfunctions.php` use `NemoBasic` or include `system.php` directly, so they do not use that gate. `phpinfo.php` includes nothing and calls `phpinfo()`. ### FR-003 — Menu permission to view If the current script name is not a URL this user's group may view (`blnView = 1`), the system sends the browser to `message.php` with `MID=r09`. After `$_SESSION["intAttempt"]` is already greater than 5, it uses `MID=e10`. `message.php` has no `e10` case, so that response has an empty message. `windowLocation` prints a script tag and does not exit, and the attempt counter is incremented after the redirect is printed. - Evidence: `Nemo::iniSecurity`, `message.php`, `windowLocation`. - Implementation: Implemented. - Confidence: High. - Requires confirmation: whether `intAttempt` is relied on in production. The redirect does not stop the rest of the request. ### FR-004 — Contact directory Signed-in users with view access can list, filter, export, open, and save contacts as in W3 and W4. - Implementation: Implemented. - Confidence: High. ### FR-005 — One company on the contact form Saving a contact with a company name leaves that contact with a single `tblCompanyContact` row. The edit form displays one company. - Implementation: Implemented. - Confidence: High. - Requires confirmation: this conflicts with the company screen, which can attach many contacts and the same contact to a company alongside others. A later contact save drops the other links for that person. ### FR-006 — Company categories from the contact form Saving a contact with a category deletes all `tblCompanyCategory` rows for that company and inserts one. - Implementation: Implemented. - Confidence: High. - Requires confirmation: the company screen can store many categories. A contact save replaces them. ### FR-007 — Company directory Signed-in users can list, filter, export, save, categorise, and attach or detach contacts as in W5. Group id `4` does not see Make Inactive on the company list. - Implementation: Implemented. - Confidence: High. ### FR-008 — Saved search Users can store a named search and run it. Results come from `vieCompanyContact`. Multiple selected categories match with OR. Contact field filters match with AND. Group membership filters match with AND. - Implementation: Implemented. - Confidence: High for the SQL builder. The view definition is Unknown. ### FR-009 — Lookup uniqueness - Category and group report-column aliases (`strAlias`) must be unique across categories, groups, and `tblField`. - Position names must be unique. - Precinct names must be unique against every existing precinct row, including the row being edited. - Evidence: `category.cls.php`, `group.cls.php`, `position.cls.php`, `precinct.cls.php`. - Implementation: Implemented. - Confidence: High. ### FR-010 — User administration An administrator who can open `user.php` can create and edit users and can mark them inactive. Creating a user emails a reset link. The administrator can also post Reset Password for an existing user (`user.php` action "Reset Password"). - Implementation: Implemented. - Confidence: High. - Requires confirmation: the email template "Reset Password" must exist in `tblEmailTemplate`. It is not in Git. ### FR-011 — Password policy Passwords are stored as SHA-256(`salt` + password) in `sysPassword`, with a 32-byte random salt. Strength, history, restricted words, maximum age, and reset-link lifetime are read from `sysSettings`. Seeded defaults, inserted when the password class creates missing rows: | Setting | Seed | |---|---| | `PASSWORD_Enable_Policy` | `1` | | `PASSWORD_Strength_Check` | `true` | | `PASSWORD_Strength_Check_MinLength` | `8` | | `PASSWORD_MaxAge` | `false` (expiry check does not run) | | `PASSWORD_MaxHistory` | `8` | | `PASSWORD_Restricted_Words` | `password\|123` | | `PASSWORD_Reset_Interval` | `60 MINUTE` | - Evidence: `_nemo.password.cls.php`. - Implementation: Implemented as seeds. Live values may have been edited in `sysSettings`. - Confidence: High for the code path. Live values Unknown. ### FR-012 — Logout and session length Logout goes to `SystemSettings["LogoutRedirect"]`. Session garbage-collection lifetime is set to `7200` seconds (two hours) before `session_start`. Session files are stored in a path that depends on the host (`sessionpath` in `system.php`). - Implementation: Implemented. - Confidence: High. The logout URL value is a setting. Unknown until read from the database. ## Integrations | System | Purpose | Direction | Trigger | Auth | Failure | |---|---|---|---|---|---| | MariaDB/MySQL on host-h.net | All application data | App reads and writes | Every page | Username and password in `system.php`, chosen by `SERVER_NAME` | Connection failure prints the settings object and "Database Connection has Failed" | | PHPMailer | Password reset and the security-warning email | Outbound | Reset, new user, five-failure warning | PHPMailer `Send()` with no `isSMTP()` in `doMail`. Mail uses the local mail transport. From and BCC come from settings `SMTP Send As` and `SMTP BCC` | `doMail` records "Mail not sent" and can `var_dump` the result | | MyMobileAPI `http://www.mymobileapi.com/api5/http5.aspx` | SMS | Outbound HTTP POST | Only if something calls `SendSMS`. No CCID page does | Settings `smsUsername`, `smsPassword`, `smsLive` | Prints the URL problem and stops | | `reception@capetownccid.org` | Help link on the login page | Browser mailto | User clicks Help | None | Handled by the user's mail client | | SonarQube | Static analysis on `master` | CI push | Bitbucket Pipelines | `SONAR_TOKEN`, `SONAR_HOST_URL` repository variables | Pipeline failure. No deploy is tied to the scan | Provenance: **Implemented** as coded. Whether SMTP is actually local sendmail on the host, and whether SMS settings are filled in, is **Unknown / Requires Confirmation**. ## Constraints - Pages that use `Nemo` require a PHP session. Lifetime configured in code: two hours. - Detail forms follow the live table. A column added only in the database appears on the form without a code change. A column removed in the database breaks the form. Provenance: **Inferred** from `AssimulateTable`. - List page size is `sysSettings` "Rows per Page". The number is **Unknown / Requires Confirmation**. - HTTPS is forced by `.htaccess` when `RewriteEngine` is enabled. - `error_reporting` hides strict, notice, and warning. `display_errors` is `1`. Provenance: **Implemented** (`system.php`). - No automated test suite is in the repository. ## Out of scope for the contact database These are in the tree and are not part of the contact, company, or search workflows: - `contact.php` and `includes/contact.cls.php` against `tblContact` (precinct on the contact, categories on the contact). - `report.php` and `includes/engine.rpt.php` (timesheet / study / site aggregation). - `ActiveProjectListPDF` in `project.functions.inc.php` (locations, programmes, projects, Dompdf). - `SendSMS`. - Email-template classes with no page script. - `getNewEmails` and `getNewTickets` in `project.functions.inc.php`, which return the constants `6` and `1`. Provenance: **Implemented** as leftover or unused code. Whether any menu item still points at `contact.php` or `report.php` is **Unknown / Requires Confirmation**. ## Requires confirmation - Live `Brand`, `Title`, logout URL, rows per page, SMTP from-address, and password-policy values. - Menu contents and security-group names, including group `4`. - Definition of view `vieCompanyContact`, and whether Create Views is still used. - Meaning of precinct id `5`. - Whether a contact is supposed to have one company or many. - Whether a company is supposed to have one category or many. - Whether `contact.php` and `report.php` are still linked from the menu. - Whether login rows in production store a plaintext password (`tblLoginInsert` writes `$strPassword` into `sysLogin.strPassword`). - Deploy process and current PHP version. - Whether `phpinfo.php` is deployed.