# Features — Click Foundation - Migration

Evidence date: 2026-10-19. Source: `master` at `fd0c90b` unless a row says `origin/prod`.

Status values: **Implemented** (present in code or SQL on the named ref), **Documented** (described in a workbook, Word file, or progress note, and not established in code by this pass), **Planned** (explicitly future or deferred), **Partial** (started, stubbed, or split across refs), **Deprecated / unused** (named as unused, closed, or superseded).

## Current features (on `master`)

| Feature | What it does | Where | Status |
|---|---|---|---|
| DDD file detection | Counts three dated CSVs (extract, changed ids, merged ids) in the event bucket | `Lambda Functions/ddd-get-files/index.js` | **Implemented** |
| DDD staging load | Loads those files into `ClickSTAGING` | `ddd-import-files`, `ddd-get-dataimportid`, `ddd-update-dataimport` | **Implemented** |
| DDD complete and finalise | `internal.pcompletedddfileimport()` then the finalise Lambda | `ddd-complete-ddd-file-import`, `ddd-import-learner-ddd-finalize` | **Implemented** |
| CEMIS staging load and validation | Import, then `reporting.pvalidatecemislearner` | `cemis-import-files`, `cemis-validate-learner` | **Implemented** |
| CEMIS warehouse update | Date joined/left, source learner, first usable data, school data source | `cemis-update-*` | **Implemented** |
| Data-import status | Marks an import Success | `update-dataimport` calls `dbo.pupdatedataimport` | **Implemented** |
| Learner registration count | `dbo.plearnersregisterfromsourcelearnerf` on staging | `transferdw-learner-registration-get-learner-count` | **Implemented** |
| Learner transfer and usernames | Transfer, movement, `pgenerateusernames` for programmes 8 and 9 | `transferdw-learner-registration-update-learners`, `functions/DW/procedure internal.pgenerateusernames.sql` | **Implemented** |
| Registration file export | Reading Eggs regional learner and class extracts; Reflective Learning extract for South Africa | `transferdw-learner-registration-export` | **Implemented** |
| Combined registration function | Same SQL as the split functions, in one handler | `transferdw-learner-registration/index.js` | **Implemented**. Overlaps the split functions. Which one production calls is **Unknown**. |
| Reading Eggs usage import | `aws_s3.table_import_from_s3` into staging | `import-readingeggs` | **Implemented** |
| Reading Eggs learner-list import | Same pattern for the learner list | `import-readingeggs-learnerlist` | **Implemented** |
| Reading Eggs warehouse transfer | New import id, update learners, `pTransferReadingEggs2`, programme activity for `'4'`, school activation | `transferdw-readingeggs*` | **Implemented**. Activation call is commented as a conversion error in the combined function. |
| Reading Eggs learner-list transfer | `internal.ptransferreadingeggsnewlearnerlist` | `transferdw-readingeggs-learnerlist` | **Implemented** |
| Environment import | Load file to staging and transfer | `import-environment`, `import-environment-transfer` | **Implemented** |
| Learner registration check | Calls a check on `ClickDW` | `learner-registration-check` | **Implemented** (the SQL body of the check was not fully read) |
| TAO eQuiz pull | OAuth to TAO and insert of quiz import rows | `TAO Datastore API/Delivery/tao-get-delivery`, `tao-delivery-import` | **Implemented** |
| TAO quiz transfer | `tao-transferquiz` service, stage `staging` | `TAO Datastore API/Delivery/tao-transferquiz` | **Partial**. Service file exists. Behaviour not fully traced. |
| Portal SQL | Identity by username, user by username/id, redirect, school list filter | `functions/Portals/` | **Implemented** SQL only |
| Foreign tables | `postgres_fdw` between staging and `ClickDW` for learner, quiz, Reading Eggs, programme, school | `functions/DW/foreign databases *.sql`, `functions/Staging/foreign databases *.sql`, `functions/foreign database config.sql` | **Implemented** as scripts. The config file contains a plaintext password. |
| AWS foundation, test | VPC, NACL, Windows EC2, Aurora PostgreSQL 13, Backup, CloudTrail, Budgets, SNS, alarms | `Deployment_Scripts_v5/` | **Implemented** as templates |
| AWS foundation, prod-named | Same stacks with `CustomerName: tcf`, `StageName: prod`, profile `tcf-prod` on the RDS/VPC/EC2/NACL files | `Deployment_Scripts_v5 - PROD/` | **Implemented** as templates. Some sibling files in that folder still say `tcf-test`. |
| Earlier stack snapshots | `click-staging-*` (6 June 2022) and `tcf-test-*` without NACL (8 June and v3) | Dated folders and `Deployment_Scripts_v3` | **Implemented** historical copies |
| SSM-to-SSIS sample | Run a `.bat` on a Windows instance | `S3-Lamda-SSIS/Python Lambda.py` | **Implemented** sample |
| Secrets Manager examples | Read `staging-aurora` | `get-secrets` (Python 3.8), `secrets-test` (Node 12) | **Implemented** examples, not a product feature |
| File-list experiment | `files-test` | `Lambda Functions/files-test` | **Partial** / experimental name |

## Completed relative to the old SSIS list

`SSIS and Procedures progress.txt` is an inventory of the SQL Server packages and the procedures they called. It marks several items "in progress" or "to be done" (report generation, Reading Eggs S3 script, Matific validation, masterlist `pTransferMasterList`, environment `pTransferEnvironment`). The Lambda and SQL tree is the later attempt to replace that list.

| Old package (progress note) | Later form | Status |
|---|---|---|
| `Fetch_Learner_DDD.dtsx` / `Import_Learner_DDD.dtsx` | `ddd-get-files`, `ddd-import-files` | **Implemented** as Lambdas. SSIS package not in git. |
| `Import_learner_DDD_Finalize.dtsx` | `ddd-import-learner-ddd-finalize`, `internal.pupdatedddids` and related procedures | **Implemented** |
| `TransferDW_Learner_Registrations.dtsx` | `transferdw-learner-registration*` | **Implemented** |
| `Import_learner_cemis_simple.dtsx` | `cemis-*` | **Implemented** |
| `Reports_Generate.dtsx` | `cemis-launch-reports` plus transcript statement that SSRS remains | **Partial** |
| `FetchImport_AWS-S3-CSV.dtsx` (Reading Eggs) | `import-readingeggs` | **Implemented** |
| `Import_ReadingEggs_LearnerList.dtsx` | `import-readingeggs-learnerlist`, `transferdw-readingeggs-learnerlist` | **Implemented** |
| `Export_Matific_Registration.dtsx` | `internal.pextractmatificregistrationlearnerlist`, `internal.pextractmatificregistrationschoollist` | **Partial**. Procedures are on `master`. The progress note said the procedures were "to be done". A Matific export Lambda is named only on `origin/prod`. |
| `Import_Environment.dtsx` | `import-environment*` | **Implemented** |
| `Import_Masterlist.dtsx` | No Lambda folder | **Documented** only |
| `FetchImport_QuizResults_Mobenzi.dtsx` / `TransferDW_Quiz.dtsx` | TAO Lambdas; workbook says Mobenzi is closed | **Deprecated** as Mobenzi. TAO path **Partial**. |

The 26 April 2022 transcript says DDD, CEMIS, and learner programme registration had been moved to Lambdas and state machines. The state machine files are on `origin/prod`, not on `master`.

## On `origin/prod` only

These names were listed from `git ls-tree` / the Step Function JSON. The handlers were not reviewed line by line.

| Feature | Evidence | Status |
|---|---|---|
| DDD import state machine | `Step Functions/Import_Learner_DDD.json` | **Implemented** as JSON on `prod` |
| CEMIS import state machine | `Step Functions/Import_Learner_CEMIS_Simple.json` | **Implemented** as JSON on `prod` |
| Learner registration state machine | `Step Functions/TransferDW_Learner_Registration.json` | **Implemented** as JSON on `prod` |
| Reading Eggs usage state machine | `Step Functions/Import_Readingeggs.json` | **Implemented** as JSON on `prod` |
| Reading Eggs learner-list state machine, including a Matific export step | `Step Functions/Import_Readingeggs_Learnerlist.json` | **Implemented** as JSON on `prod` |
| Activate school/grade state machine | `Step Functions/Activate_School_Grade.json` | **Implemented** as JSON on `prod` |
| Send email | Lambda `send-email` referenced by four state machines | **Unknown** beyond the name. Not on `master`. |
| Reflective Learning registration export Lambda | `transferdw-learner-registration-export-refl` | Name only on `prod` |
| Matific export from learner list | `transferdw-readingeggs-learnerlist-export-matific` | Name only on `prod` |
| Vacuum after DDD | `Lambda Functions/ddd-vacuum-learnerdatareadingeggs` | Folder exists on `prod` |
| Fetch file from FTP / Google Drive / S3 | `get-file-from-ftp-py`, `get-file-from-gdrive`, `get-file-from-gdrive-py`, `get-file-from-bucket` | Folders exist on `prod`. `credentials.json` is in the Drive folders. |
| DMS table scripts and notes | `dms-postgres-mssql-dw/` | **Implemented** as SQL files on `prod` |
| Grade deregistration workbook and SQL | `Grade deregistration/` | **Documented** / SQL on `prod`. Not reviewed. |
| Weekly usage and Reading Eggs exception report functions | `functions/DW/Reporting/` on `prod` (for example `reporting.plearnerexceptionreportreadingeggsf`) | **Implemented** as SQL on `prod`. Not reviewed. |
| `LMS Updated Document - 10-10-2022.doc` | Top-level file on `prod` | Not read (binary `.doc`) |

## Planned or deferred

From `Proposed Plan for Silicon Overdrive.xlsx`, `System Business Rules.xlsx`, and the February 2022 planning workbook. These are not implemented as product features in this repository unless a row above already covers them.

| Item | What the document says | Status |
|---|---|---|
| School list / masterlist as a stable import | "Defer to phase 2". Import is still described as a manual, twice-run SSIS process. | **Planned** / still **Documented** manual |
| School, grade, and programme deactivation | Questions in the business-rules sheet ("how do we create the list?"). Annual, December, for grades. | **Documented** as an open process. Grade SQL appears only on `origin/prod`. |
| Learner rollover | RO1–RO7 in the business-rules sheet | **Documented**. No rollover Lambda. |
| Learner programme de-registration reporting | Named in the proposed plan and the rules sheet | **Documented** |
| Matific usage import | "not done new process to be defined" | **Planned** |
| Reflective Learning usage import | Deprioritised in the 10 May 2022 meeting, with Matific | **Planned** |
| Reading Eggs, Matific, and Reflective Learning registration APIs | "Move to API integration once available" | **Planned** |
| Automated DDD sanity thresholds | The rules sheet asks for a percent-movement check and says the action on failure is undefined | **Documented** as not specified |
| Facilitator self-service and Google sign-in | Workbook: outside the statement of work. Maintenance doc: manual SQL. | **Planned** |
| Rebuild both portals onto S3 or Amplify | Workbook: outside the statement of work | **Planned** |
| Import wizards on the admin portal | Workbook | **Planned** |
| Scheduled report burst | Workbook | **Planned** |
| Mathseeds import | Workbook column "Will Replicate" = N | **Documented** as not in scope |
| Datamart / DDD performance data | Named in the proposed plan. `Click Import Process and Dependencies.docx` says datamart behaviour was unknown to the authors. | **Unknown** |
| Glue, QuickSight, Aurora MySQL, Terraform, Amplify | February 2022 architecture sketch | **Planned** in that workbook. Contradicted by the PostgreSQL Serverless stacks. |

## Deprecated, unused, or superseded

| Item | Evidence | Status |
|---|---|---|
| Mobenzi quiz import | Planning sheet: "Mobenzi closed so no longer required". eQuiz doc still describes Mobenzi tables and a 19:00 job. | **Deprecated** as a source. Historical rules remain in the Word file. |
| `ddd-launch-import-learner-ddd-finalize-NOT-USED` | Folder name. Python 3.9 hello handler. | **Deprecated / unused** |
| `secrets-test`, `get-secrets`, `files-test` | Example and test handlers | Not product features |
| School "level" (automatic / self-managed / manual) | Business-rules sheet: "Level assigned to a school does not do anything in the new version of the system." Level moved to grade: Automatic (source system decides registration), Self Managed (Reading Eggs tells Click which learners), Manual (no longer used). | **Documented** |
| Deployment folders before v5 | 6 June, 8 June, and v3 copies | Superseded as templates by `Deployment_Scripts_v5`, which is the set whose README and remediation notes match the later script. They are still in git. |
| SQL Server SSIS as the primary import runner | Progress note and Word files | Superseded in intent by Lambdas. The May 2022 transcript says SSIS/SSRS remains for reporting. |

## Recently changed (end of `master` history)

The last commits on `master`, newest first:

| Commit | Date | Message |
|---|---|---|
| `fd0c90b` | 2022-12-09 | Adding lambdas and changes to lambdas |
| `a9882c1` | (parent of the above) | Adding function cahnges |
| `35d635e` | | added token request code |
| `88c0908` | | changes |
| `d901535` | | optimization changes |

`35d635e` matches the TAO token request present in `tao-get-delivery`. No commit after 2022-12-09 is on `master`.

`origin/prod` tip `f47d0c3` message is "Changes to csv format". That commit is not on `master`.