# Project plan — Click Foundation - Migration

Evidence date: 2026-10-19.

Source repository: `git@bitbucket.org:overdrive/click-foundation-migration.git`.

Inspected revision: `master` at `fd0c90b` (2022-12-09 12:01:12 +0200, author Dirk, message "Adding lambdas and changes to lambdas"). History on `master` runs from `2022-02-07` ("Initial commit", heinrichoverdrive) to that commit. `git rev-list --count HEAD` is 201. Authors by commit count: heinrichoverdrive (71), Lloyd (68), Dirk (55), Dirk Jansen van rensburg (23), silmaanie (15).

The scan-host path named in the onboarding task (`~/projects/sonar_scan/repos/Click Foundation - Migration`) was not on this machine. Evidence comes from a read-only clone. `silicon_ark/Click Foundation - Migration/` did not exist before this set.

This plan describes what the repository contains. It does not decide what the product should become.

## Product

The planning workbook names the project **The Click Foundation AWS Migration and Modernization** (`The Click Foundation Project Planning and Monitoring 1.3 01_02_2022.xlsx`, sheet "2. Project Data").

What the repository holds is the migration of The Click Foundation's learner, school, and programme data platform off the previous SQL Server / SSIS estate and onto AWS in `af-south-1`: Aurora PostgreSQL databases `ClickSTAGING` and `ClickDW`, Node.js Lambdas that replace SSIS steps, Serverless Framework stacks for VPC, EC2, RDS, backup, CloudTrail, budgets, SNS, and alarms, and a TAO eQuiz client. The operational product — schools, learners, Reading Eggs, Matific, Reflective Learning, and quizzes — is described in spreadsheets and Word files in the same repository. The portals themselves are not application source in this repository.

Provenance: **Documented** (workbook title and process lists). **Implemented** (Aurora, Lambdas, SQL, deployment stacks on `master`).

## Branch reality

| Ref | Tip | Relationship to `master` |
|---|---|---|
| `origin/master` (`HEAD`) | `fd0c90b` 2022-12-09 | Inspected tree |
| `origin/dev` | `6afdf9c` | No commits that are not already on `master` |
| `origin/changes-secrets-24-08-2022` | `1918877` | No commits that are not already on `master` |
| `origin/prod` | `f47d0c3` | Diverged. Unique commits include "adding delivery functions and Step functions" and "Update step functions" |

`origin/prod` adds top-level paths that are absent from `master`:

- `Step Functions/` — six Amazon States Language definitions
- `dms-postgres-mssql-dw/` — DMS notes and a large set of `dbo.*.sql` table scripts
- `Grade deregistration/` — `Grade Maintenance.sql` and `Grade Maintenance.xlsx`
- `LMS Updated Document - 10-10-2022.doc`

`master` also has commits that are not in `prod` (the five newest on `master`, from "optimization changes" through `fd0c90b`). This document set is written from `master`, with the `prod`-only Step Functions read via `git show` because they are the orchestration the Lambdas were built for. Line-by-line review of the `prod` SQL delta (about 290 files, including reporting functions) was not done.

No release tags were present.

The task-creation scan listed `sonar-project.properties` and `sonarqube_scan.bat` at the repository root. Neither file is in `master` or `origin/prod`. Provenance: **Unknown** — they may have been untracked files on the scan host.

## Objectives

### Written in the planning workbook

`The Click Foundation Project Planning and Monitoring 1.3 01_02_2022.xlsx` is a generic project-monitoring template filled in for this engagement. Documented phases include proposal planning and design, AWS account linking, discovery, a statement of work, DDD and CEMIS modernisation, Reading Eggs, Matific, school imports, an AWS staging account, a production account, and optional optimisation and support phases.

Discovery items written there:

- Create 3 PostgreSQL databases and convert tables.
- Copy both React web portals and connect them to PostgreSQL.
- Investigate stored procedures behind the two portals.
- Investigate import and export processes. The sheet says these will be replicated: DDD (26 stored procedures), school import, CEMIS, Reading Eggs, Matific, Mathseeds, quizzes.
- Test rollover in SQL and in PostgreSQL.
- A "final import from Gavin".
- The same process list is also marked "will modernise".

The same workbook's architecture sketch for staging says: React.js web applications, Glue, QuickSight, and single-AZ Aurora MySQL, with Windows EC2 for a .NET front end, Aurora MySQL databases named ClickDW, ClickMart, and ClickStaging, Amplify admin and learner sites, Lambda, Terraform, and related AWS services. Production is described as multi-AZ Aurora MySQL with read replicas.

Provenance: **Documented** plan as of the workbook dated in the filename `01_02_2022`.

### What the repository actually built

The deployment scripts and Lambdas implement a different target from that sketch:

- Aurora **PostgreSQL** 13, not Aurora MySQL (`Deployment_Scripts_v5/tcf-test-rds_serverless.yml`, `Engine: aurora-postgresql`, `MajorEngineVersion: 13`).
- Databases used by Lambdas are `ClickSTAGING` and `ClickDW`. A `ClickMart` database is named in the plan and is not referenced by the Lambda code read for this document.
- Infrastructure is Serverless Framework CloudFormation, not Terraform in this repository. The v4-to-v5 remediation notes say some base resources (default route table, DHCP options, default NACL, default security group) are created by "Silicon Terraform Base Setup Scripts", which are not in this repository.
- Reporting and the two portals were, in the 10 May 2022 meeting transcript, still the existing SSRS and portal applications running on a Windows EC2, with Lambdas triggering reporting. Provenance: **Documented** in `Click System_2022-05-10.docx` (spoken). The EC2 template describes Windows Server 2019. The portal and SSRS source are **not in this repository**.

`Proposed Plan for Silicon Overdrive.xlsx` is a short priority list, not a schedule. It marks "Complete Delivery of Phase 1" against school management, DDD learner import (all schools except Western Cape), CEMIS learner import (Western Cape only), and learner programme registration. School list is "Defer to phase 2". Several other rows (school activation, grade activation, programme activation, deactivation, programme-side school creation, rollover, de-registration, Reading Eggs / Matific / Reflective Learning programme data, DDD performance data, eQuiz, people management, datamart) have no priority value in the shared strings.

## Scope

### In this repository

- Serverless Framework stacks for a test account (`tcf-test`) and a production-named copy (`Deployment_Scripts_v5 - PROD`, customer stage `prod`).
- Earlier stack snapshots: `Deployment Scripts - 06-06-2022` (`click-staging-*`) and `Deployment Scripts - 08-06-2022` / `Deployment_Scripts_v3` (`tcf-test-*` without the NACL stack that v5 adds).
- About 38 Lambda service folders under `Lambda Functions/`, plus TAO client Lambdas under `TAO Datastore API/`.
- PostgreSQL functions, procedures, and foreign-table definitions under `functions/`, converted from SQL Server (AWS SCT comments such as "Severity CRITICAL" are in the SQL).
- Operational write-ups, sample CSV/XLSX files, and business-rule spreadsheets under `Documentation and Sample Files - 20-12-2021/` and at the repository root.
- A one-file Python example, `S3-Lamda-SSIS/Python Lambda.py`, that sends an SSM Run Command to a Windows instance.

### On `origin/prod` and not on `master`

- Step Functions that chain the Lambdas (see technical documentation).
- Extra Lambda folders referenced by those state machines, including `send-email`, `ddd-vacuum-learnerdatareadingeggs`, `get-file-from-ftp-py`, `get-file-from-gdrive`, and `get-file-from-gdrive-py`.
- `dms-postgres-mssql-dw/` and `Grade deregistration/`.

### Not in this repository

- The React admin and learner portal UI. Meeting transcript of 10 May 2022 says those portals were copied onto the Windows EC2 with the database connection changed. Separate Silicon Ark projects `ClickPortal` and `ClickLearnerPortal` exist; that they are those portals is **Inferred** from matching `portal.*` procedure names, and **Requires Confirmation**.
- SSIS package binaries (`.dtsx`). The packages are named in `SSIS and Procedures progress.txt` and in the Word process notes. The packages themselves are not in git.
- Terraform "base setup" scripts referred to by the v5 remediation notes.
- Mathseeds import implementation. The planning sheet lists `Import_Mathseeds.dtsx` / `TransferDW_Mathseeds.dtsx` with "Will Replicate" = N.
- A test suite. No test project was found.
- Sonar project files in git.

## System architecture

```text
Sources (documented)
  DDD FTPS extracts (3 CSV files)
  CEMIS files prepared by Click staff (Google Drive, then CSV)
  Reading Eggs files (S3 / aws_s3.table_import_from_s3 in Lambda)
  Environment / masterlist files
  TAO eQuiz API (OAuth client-credentials)
        │
        ▼
S3 buckets (bucket name is passed in the Lambda event; ddd-get-files
reads event.detail.bucket.name and expects 3 files for one date)
        │
        ▼
Lambda (Node.js 14, af-south-1)  ── on origin/prod only ──►  Step Functions
  Secrets Manager: staging-aurora, dw-aurora
        │
        ├─ ClickSTAGING   (Aurora PostgreSQL, port 5432)
        └─ ClickDW        (Aurora PostgreSQL, port 5432)
              postgres_fdw links the two (functions/foreign database config.sql)
        │
        ▼
Windows Server 2019 EC2 (deployment template)
  SSIS / SSRS still used for reporting, per the 10 May 2022 transcript
  S3-Lamda-SSIS example starts a PowerShell script on an instance via SSM
```

Lambdas on `master` have no `events:` schedules. Nothing in `master` defines the Step Functions. The state machines exist only as JSON on `origin/prod`. Whether those machines are deployed, and whether anything still invokes the Lambdas, is **Unknown / Requires Confirmation**.

## Major components

| Component | Role | Evidence |
|---|---|---|
| Deployment stacks v5 | VPC, NACL, EC2, Aurora PostgreSQL, Backup, CloudTrail, Budgets, SNS technical notifications, CloudWatch alarms | `Deployment_Scripts_v5/README.md` deploy order |
| DDD Lambdas | List S3 files, open a data-import id, load the three DDD CSVs, update the import, complete and finalise into `ClickDW` | `Lambda Functions/ddd-*` |
| CEMIS Lambdas | Load CEMIS rows, validate, update dates, source learner, school data source, move files, launch reports | `Lambda Functions/cemis-*` |
| Learner registration Lambdas | Count, transfer, generate usernames, export Reading Eggs and Reflective Learning files | `Lambda Functions/transferdw-learner-registration*` |
| Reading Eggs Lambdas | Import usage and learner lists from S3, transfer into `ClickDW`, update person-programme activity, activate schools | `Lambda Functions/import-readingeggs*`, `transferdw-readingeggs*` |
| Environment import | Load an environment file into staging and call the transfer | `import-environment`, `import-environment-transfer` |
| TAO client | OAuth to TAO, write quiz submissions into staging and `ClickDW` | `TAO Datastore API/` |
| SQL layer | Procedures converted from the SQL Server estate | `functions/DW`, `functions/Staging`, `functions/Portals` |
| Business rules workbook | Operational rules for rollover, imports, activation, registration, usage | `System Business Rules.xlsx` |

## Milestones as written in February 2022

These are planning-workbook items, not a status that was updated in git after December 2022.

| Phase in the workbook | What it says | What the repo shows |
|---|---|---|
| Discovery | PostgreSQL conversion, portal copy, import investigation | SQL in `functions/` is PostgreSQL with SQL Server compatibility leftovers. Portal UI is not here. |
| DDD + CEMIS | Automate `LearnerRegistrationCheck` and `TransferDW_Learner_Registrations` "if possible"; sFTP to S3 to Lambda Step Functions to SSIS | Lambdas and, on `prod`, Step Functions exist. SSIS packages are not in git. The 10 May 2022 transcript says DDD/CEMIS processing was still a manual kickoff during testing. |
| Reading Eggs | Rewrite export SSIS for PostgreSQL; later an API | Lambdas call extract functions and copy CSVs. API registration is still written as future in the business-rules sheet. |
| Matific | Rewrite export; API "TBC" | Extract procedures and a registration export path exist. Usage import is "not done" in the business-rules sheet. |
| School imports | Masterlist remains irregular | Documented as a manual SSIS-plus-`pTransfer` process. No masterlist Lambda folder on `master`. |
| AWS platform | Staging then production accounts in Cape Town | v5 test stacks and a prod-named copy of the stacks are in git. Whether they were applied, and whether they are still the live accounts, is **Unknown**. |
| Portals | Copy React portals; Amplify rebuild is "outside SoW" | Transcript says the portals were copied onto EC2. Amplify is not in this repo. |

Spoken status on 26 April 2022 (`Click System_2022-04-26.docx`): DDD and CEMIS had been migrated to Lambdas and state machines; learner programme registration was part of that conversion; programme/grade activation was expected that week; parallel testing against a database backup was planned from 3 May 2022.

Spoken status on 10 May 2022 (`Click System_2022-05-10.docx`): imports, learner programme registration, and programme/grade activation were described as complete enough to test; SSRS had been migrated onto PostgreSQL but still used the existing reporting technology on Windows EC2; Reading Eggs usage and TAO were the next focus; Matific and Reflective Learning usage imports were deprioritised to a later phase; eQuiz specification was still outstanding.

Spoken status on 24 May 2022 (`Click System_2022-05-24`, a Word file without an extension): deployment scripts were being changed after a validation pass, then redeployed for another validation. That matches `Deployment_Scripts_v5/Remediations from v4 to v5` and `recommendations.txt`, which record Cloud Conformity-style findings and mark most of them done in the scripts.

The last commit on `master` is 2022-12-09. Nothing in git records a go-live or a project close.

## Known dependencies

| Dependency | Role | Provenance |
|---|---|---|
| DDD | Learner extracts and masterlist for provinces other than Western Cape | **Documented** |
| CEMIS / WCED | Western Cape learner files, supplied manually | **Documented** |
| Reading Eggs | Programme registration files and usage files. People named in the rules include Carol (registration) | **Documented** |
| Matific | Registration extract; usage process "to be defined" | **Documented** |
| Reflective Learning | Registration extract; usage process not defined in the sheet | **Documented** |
| TAO Cloud | eQuiz OAuth and delivery results. Auth host in code: `auth-ignite.prod.gcp-eu.taocloud.org` | **Implemented** |
| Mobenzi | Previous quiz source. Planning sheet says "Mobenzi closed so no longer required" | **Documented** |
| AWS `af-south-1` | Lambdas, Aurora, VPC. Budgets stack is `us-east-1` | **Implemented** in templates |
| Secrets Manager secrets `staging-aurora` and `dw-aurora` | Database credentials | **Implemented** |
| Windows EC2 with SSIS/SSRS | Reporting and, per the May 2022 transcript, the portals | **Documented** transcript; EC2 template **Implemented** |
| Google Drive | CEMIS submission drive, described in the import notes | **Documented**. A `get-file-from-gdrive` Lambda exists only on `origin/prod` |
| Separate portal repositories | Inferred consumers of `functions/Portals` | **Inferred** |

## Current project status

As of the inspected git history, implementation work on `master` stopped on 2022-12-09. The repository is a migration workspace: infrastructure templates, converted SQL, Lambdas, sample files, and operational documents from 2021–2022. It is not an application with a UI, a test suite, or a pipeline definition.

Whether the Aurora clusters, Lambdas, or Step Functions are still deployed, and whether The Click Foundation still runs this path in parallel with or instead of the older SQL Server estate, is **Unknown / Requires Confirmation**.

## Human confirmation

- Is `master` or `origin/prod` the tree that was deployed? They have diverged.
- Did the project go live, and is any of this stack still in use in 2026?
- Were the three databases in the plan (ClickDW, ClickStaging, ClickMart) all created? Lambda code uses ClickSTAGING and ClickDW only.
- Are `ClickPortal` and `ClickLearnerPortal` the two portals the May 2022 transcript says were copied onto the Windows EC2?
- Should the `prod`-only Step Functions, DMS scripts, and grade-deregistration files be treated as part of the supported system?