# Business rules — Click Foundation - Migration

Evidence date: 2026-10-19. Source: `master` at `fd0c90b`, plus documents in that tree.

Rules are copied from evidence. They are not new policy. Each rule is marked **Documented** or **Inferred** or **Implemented**. Where a spreadsheet asks a question or proposes a change, that is recorded as unresolved, not as a rule the system enforces.

Primary sources:

- `System Business Rules.xlsx` (sheets named in the workbook XML include Rules, NEW Process Breakdown, SO Click Processes, School Renaming, Launches, and others). Cell layout is lost in text extraction; the rule text below is the shared-string wording.
- `Documentation and Sample Files - 20-12-2021/Learner Imports/CEMIS Learner Import/CEMIS Learner Import.docx` (Amanda, 5 and 14 February 2020).
- `DDD and CEMIS Import.docx` and `Click Import Process and Dependencies.docx`.
- `DDD Changed IDs exception report rules.docx`.
- `Click Foundation - eQuiz.docx`.
- `Maintenance Items - WIP.docx`.
- `SSIS and Procedures progress.txt`.
- SQL and Lambdas where a rule is also in code.

## Identity of a school and a learner

| ID | Rule | Provenance |
|---|---|---|
| BR-001 | EMIS code is the school identifier used to attach a learner file to a school. | **Documented** (CEMIS spec, masterlist note) |
| BR-002 | An EMIS code that starts with `1` is Western Cape. CEMIS validation requires that prefix. Username generation treats `WC` and `CEMIS` as EMIS starting with `1`, and `NotWC` and `DDD` as EMIS not starting with `1`. | **Documented** and **Implemented** (`internal.pgenerateusernames`) |
| BR-003 | If an EMIS value is 10 characters and the first character is `0`, strip the leading `0`. Otherwise the length must be 9. | **Documented** (CEMIS spec) |
| BR-004 | The school matched by EMIS must be status ACTIVE or PIPELINE on the current date, or the CEMIS file fails validation. | **Documented** (CEMIS spec). Not re-read in the validator body. |
| BR-005 | DDD learner identity for non-Western Cape schools is the DDD learner id. CEMIS learner identity is the CEMIS id only. | **Documented** |
| BR-006 | CEMIS learner id is 13 characters. The first 6 are a date `YYMMDD` and are also the date of birth. | **Documented** |
| BR-007 | A CEMIS row that does not start with a valid EMIS code is ignored (footer junk). If any remaining row fails validation, the whole file is rejected and moved to an error folder. A passing file is moved to an imported folder. | **Documented** |
| BR-008 | CEMIS surname and names are one field, split on the first comma. The value is uppercase in the file and must be stored in proper case. | **Documented** |
| BR-009 | The import date is the CEMIS "last submission" date, because the file has no date column. | **Documented** |

**Inferred** from `cemis-import-files/index.js`: rows with a null, empty, or longer-than-10 EMIS are deleted in SQL before later steps, and rows with `GradeID = -1` are deleted. The second statement fights BR-010. **Requires Confirmation.**

## Grades

| ID | Rule | Provenance |
|---|---|---|
| BR-010 | CEMIS grades are prefixed `GR`. Strip `GR`. `PRE-GR R` maps to grade -1. `LSEN UNIT CLASS` maps to grade 14. A number after `GR` must be 1–12. `R` is the reception grade. | **Documented** |
| BR-011 | The validator's own header maps `PRE-R` to -1 and `R` to 0, and then the count query excludes `PRE-R`. | **Implemented** as comments and SQL in `reporting.pvalidatecemislearner`. This is not the same mapping as BR-010. |
| BR-012 | Class may be blank. A blank CEMIS class does not create a class. If class starts with `GR` and the number does not match the grade, ignore the class. For `PRE-GR R` and `LSEN UNIT CLASS`, keep the class value and do not validate it. | **Documented** |
| BR-013 | For a school, every grade the school is active in must appear in the CEMIS file. A grade the school already supplied earlier in the year must appear. Learner count per grade must stay within 10% of the previous count. | **Documented**. The procedure comment instead lists new learner, possible duplicate, left, in two schools, movement, and rejoin. **Requires Confirmation** which checks run. |
| BR-014 | `CEMIS YEARS IN PHASE` is ignored. | **Documented** |
| BR-015 | Age must be numeric, from 3 to 99. Gender must be `MALE` or `FEMALE`. | **Documented** |

## DDD files

| ID | Rule | Provenance |
|---|---|---|
| BR-016 | DDD sends three files: the learner extract, a changed-id exception report, and a merged-id exception report. Processing expects all three. | **Documented** and **Implemented** (`ddd-get-files` requires `fileCount == 3`) |
| BR-017 | Changed-id file: DDD says a learner's id changed. The system keeps the oldest id and does not rewrite the associated data. Merged-id file: DDD says two ids are one learner and names the id that remains. Both ids may exist at once, so the merge must combine them. | **Documented** (business-rules sheet) |
| BR-018 | Changed-id checks in the 2021 note: the old id must already be known; the new id must not already be known unless this change is also in the merged file or was processed before. An id that was ever an old id in a merged file cannot be an old id in a later changed file. | **Documented** |
| BR-019 | Merged-id checks: both the old id and the shared id must be present. | **Documented** |
| BR-020 | For 2020 comparisons the note expected both ids, with interim flag 0 on the old id. For 2021 comparisons the old id may be absent or have interim 0, and the new id must be present. | **Documented**. Year-specific. Whether the converted procedure still branches on year is **Unknown**. |
| BR-021 | DDD and CEMIS may be imported in either order. | **Documented** |
| BR-022 | DDD files are retrieved Monday at 08:00 if that day's file is not already imported. The documented source is FTPS. The Lambda reads S3. | Schedule and FTPS **Documented**. S3 behaviour **Implemented**. The schedule is not in the Lambda YAML. |
| BR-023 | After import, a person reviews `DDD File Check.xlsx`. `CompleteDDDFileImport` showing a result means stop. Showing nothing means continue to finalise, then run the registration check, then transfer only if the counts look reasonable. | **Documented**. The `origin/prod` Step Function does not wait for a person between import and `pcompletedddfileimport`. **Requires Confirmation** whether the gate was removed on purpose. |
| BR-024 | The business-rules sheet says excessive learner movement (the example given is 8,000 learners disappearing) needs a threshold per school and overall, with end of term and end of year taken into account. The action when the threshold is breached is an open question ("What action do we take if an issue is detected"). | **Documented** as not decided. Do not treat a threshold as implemented. |
| BR-025 | DDD supplies an effective date set by the school. New-year rows whose effective date is before the configured "valid date" do not change learner records. Previous-year rows are still applied. | **Documented** |
| BR-026 | The valid date is configured by hand every year. The sheet says there is no real check that it was set. | **Documented** |

## Programmes, registration, and passwords

| ID | Rule | Provenance |
|---|---|---|
| BR-027 | Programme id 8 is Matific for username generation (`pgenerateusernamesmatific`). Programme id 9 is Reflective Learning (`pgenerateusernamesreflectivelearning`, which selects `programmeid = 9`). Programme id 4 is the id passed by the Reading Eggs Lambdas to `pUpdatePersonProgrammeActivity`. | 8 and 9 **Implemented**. 4 = Reading Eggs is **Inferred** from the call site. |
| BR-028 | When username generation is asked for all programmes, it loads ids 4, 8, and 9, and it only generates usernames for 8 and 9. | **Implemented** |
| BR-029 | Reading Eggs usernames and the school-shared password come from Reading Eggs after a registration file is sent. Matific learners who already have Reading Eggs credentials reuse them. Matific-only learners get a generated username. | **Documented** |
| BR-030 | One password is shared by all learners in a school. The login is unique per learner. | **Documented**. The sheet also asks "Find out how password generation for schools works", so the generation algorithm is **Unknown**. |
| BR-031 | A note on the school-password row says a 2-character province id (`EC`, `WC`, `GP`, `KZ`) is appended to an AutoID, with leading zeros so the AutoID is at least 4 characters. The same cell contains `???`. | **Documented** and internally uncertain. |
| BR-032 | Registration files: Reading Eggs learner file per region, Reading Eggs teacher file for a new school or new grade, one Matific file, one Reflective Learning file. | **Documented**. Reading Eggs and Reflective Learning extracts are **Implemented**. Matific extract procedures exist; the export Lambda name is on `origin/prod` only. |
| BR-033 | A human may intercept registration before files go out. The 10 May 2022 transcript says the new system was still on a manual kickoff during testing. | **Documented** |
| BR-034 | School level (automatic / self-managed / manual) no longer does anything at school level. Grade-level Automatic means the source system decides who is registered. Self Managed means Reading Eggs tells Click which learners exist. Manual is no longer used. The sheet says remove schools and learners that are not automatic, except a remaining set it describes as about 3,000 learners on Reading Eggs after school and ECD implementations. | **Documented**. The "remove" instruction is an open change, not a rule observed in code. |
| BR-035 | A school becomes active when Reading Eggs usage reaches 25% of the grade. A proposed change on the same sheet says 10% of the grade on any programme. | **Documented**. The 25% rule was not confirmed inside `pactivateschoolreadingeggs`. The 10% line is a proposed change, not evidence that it was adopted. |

## School and environment setup

| ID | Rule | Provenance |
|---|---|---|
| BR-036 | Creation order is fixed: school, then active school/grade/programme ("environment"), then learner-school links, then learner-programme registration. | **Documented** (`Click Import Process and Dependencies.docx`) |
| BR-037 | Masterlist import is rare and adjusted each time a file arrives. The September 2020 DDD load was a full file. The October 2021 CEMIS load was a reduced language file, and the import was loosened to that file. The note says that loosened logic cannot be reused for a DDD masterlist. | **Documented** |
| BR-038 | Masterlist and environment transfers run twice: `internal.pTransfer` with action `R` previews and rolls back; action `C` commits. An incorrect masterlist "will cause a serious mess". | **Documented** |
| BR-039 | A launch list from Click names the school, the programmes, and the grades. If the school is not already in the system, a non-Western Cape school can be requested from DDD; a Western Cape school is created by hand. | **Documented** |
| BR-040 | The school name on the launch list is the name Click wants the system to display, which may differ from the official name. | **Documented** |
| BR-041 | DDD masterlist is the primary school source outside the Western Cape. DBE masterlist is the primary WCED source in the sheet, with CEMIS called a better WCED source. Fee mapping if DDD has no fees: `N` / `NO` / `Fee Paying` / `NOT APPLICABLE` = Fee; `Y` / `YES` / `No Fee` = NoFee; blank or `.` = unknown. | **Documented**. Whether this mapping is in SQL was not confirmed. |
| BR-042 | Schools outside Western Cape that are not on DDD are not eligible for the programme. The sheet says if they are imported from the DBE list, store the source. | **Documented** as a proposed field, not as a confirmed column. |
| BR-043 | Planned schools (those Click will ask DDD for) were a manual extract sent to DDD on Fridays before 15:00. | **Documented** |
| BR-044 | ZAC codes are created by Click operations from a pipeline tracker and "a standard formula". The formula is not in the repository. Sample values appear in the workbook (for example `ZAC2600303`). | **Documented**. Formula **Unknown**. |
| BR-045 | Grade deactivation is described as annual in December and as "require only grades to switch off". How the desired-active list is produced is an open question on the sheet. | **Documented** as unresolved |

## Calendar rollover

All of the following are **Documented** in the Rules sheet. None has a Lambda named for it.

| ID | Rule |
|---|---|
| BR-046 | RO1. Before rollover, active schools and grades for the next year must already be loaded. They decide who rolls forward. |
| BR-047 | RO2. Import the latest school dataset before rollover so learners who have left are not rolled forward. |
| BR-048 | RO3. For learners who would be in an active grade next year, create a school-learner row for the next year, add 1 to the grade, and set class to `unknown`. |
| BR-049 | RO4. Learners who would not be in an active grade have learner-programme associations ended on 31 December of the current year, so they are not reported as expected usage. |
| BR-050 | RO5. DDD (not CEMIS) keeps sending Monday files through the holidays. |
| BR-051 | RO6. Before the valid date, new-year effective dates are ignored. Previous-year data is still applied, including terminations, and that can change learners already rolled forward. |
| BR-052 | RO7. Once a soft threshold of schools has submitted in the new year, request a one-off "end of last year" dataset from DDD, because DDD only sends the latest dataset and end-of-year rows can be skipped. The sheet says the termination date is no longer set to the school's date, so this may be unnecessary, and that it was still required for 2021 data. |
| BR-053 | UR1. From the end of the first week of term 1, usage is reported on rolled-over learners whose grade is active. That set can still change week to week from late previous-year submissions. |
| BR-054 | LL1. No learner lists or login cards are sent before the valid date. |
| BR-055 | LL2. After usable data: learner lists every week (all and new). Login cards for all learners at the first usable submission of term 1 and in the first week of later terms. Login cards for new learners every week with the usage report. |
| BR-056 | LM1. Learner management applies only to submissions whose effective date is after the valid date. |

## Usage reporting and quizzes

| ID | Rule | Provenance |
|---|---|---|
| BR-057 | Reading Eggs usage is the only programme usage ingested for phase 1. Matific and Reflective Learning usage were deferred. | **Documented** (10 May 2022 transcript and the rules sheet: "Matific User usage not done") |
| BR-058 | The usage file is collected from Sunday afternoon until 02:00 Monday. If it is missing, notify Blake. Success and failure notifications are described as separate emails. The sheet asks whether the Blake email is automatic. | **Documented**. Automation **Unknown**. |
| BR-059 | Usage reports are emailed to people flagged GET REPORT (principals, RSAs, and others). An exception report is written to a folder. | **Documented**. A user guide `Reading Eggs Exception Report User Guide V5.docx` is in the sample-files tree. |
| BR-060 | Term reports are a manual extract. `ParamYearTerm` must be set. | **Documented** |
| BR-061 | Mobenzi quiz configuration is manual: source row, question map, quiz, instance, and `QuizInstanceSchoolGrade` for a school, grade, and date range. | **Documented** in the eQuiz note. The workbook later says Mobenzi is closed. |
| BR-062 | After login at `https://learn.clicklearning.org`, the learner is sent to the programme URL or to the quiz. Quiz completion is imported once a day, so for the rest of that day the learner is sent back to the quiz even after finishing it. | **Documented** |
| BR-063 | The documented quiz job is weekdays at 19:00: fetch into `ClickSTAGING` quiz tables, then transfer to `LearnerDataQuiz` and related tables. | **Documented** for Mobenzi. TAO Lambdas **Implemented** without that schedule. |
| BR-064 | Only Mobenzi was a supported `QuizInstanceSource` in the eQuiz note. | **Documented**. Superseded in intent by TAO. |

## People

| ID | Rule | Provenance |
|---|---|---|
| BR-065 | Facilitator add, move, and remove are manual. Columns B–D find the person. Columns F–I are the new data. A facilitator removed from their last school is removed completely. | **Documented** |
| BR-066 | The facilitator SQL returns discrepancies (person not found, link to remove does not exist). Some are fixed by the operator; others go back to Kirsty. | **Documented** |
| BR-067 | A portal user lookup exists as `portal.pidentitybyusername`, `portal.puserbyusername`, `portal.puserbyuserid`, and `portal.puserredirectto`. | **Implemented** SQL. The access rule inside those functions was not extracted in this pass. |

## What is not a rule

The business-rules workbook contains questions and draft changes. They are not system behaviour:

- Why the DDD masterlist count differs from the DBE list.
- Whether to overwrite school rows or replace the list.
- Whether deactivated grades need a report.
- What the objective of the "new learners" email is.
- Whether additional schools not on DDD should be imported.
- The unfinished "Change rule to 10% of grade on any programme".
- Hours and fees in the planning workbook.

`Proposed Plan for Silicon Overdrive.xlsx` assigns "Complete Delivery of Phase 1" to school management, DDD import, CEMIS import, and learner programme registration, and "Defer to phase 2" to the school list. That is a priority statement, not a behavioural rule.