# Technical documentation — Click Foundation - Migration

Evidence date: 2026-10-19. Source: `master` at `fd0c90b`. `origin/prod` (`f47d0c3`) is cited only where the tree differs.

This repository is a migration workspace. It is not a single deployable application. It contains AWS Serverless Framework stacks, Node.js and Python Lambdas, PostgreSQL scripts converted from SQL Server, TAO API clients, and operational documents.

## Repository shape

Top level on `master`:

| Path | Contents |
|---|---|
| `Lambda Functions/` | One Serverless service per folder for DDD, CEMIS, Reading Eggs, registration, environment import, and experiments |
| `TAO Datastore API/` | TAO HTTP clients grouped as Delivery, DeliveryResult, Test, TestTaker |
| `functions/` | SQL: `DW` (72 files), `Staging` (15), `Portals` (5), `Other` (10), plus `foreign database config.sql` |
| `Deployment_Scripts_v5/` | Current test-account stacks and the deploy README |
| `Deployment_Scripts_v5 - PROD/` | Prod-named copy of those stacks |
| `Deployment_Scripts_v3/`, `Deployment Scripts - 08-06-2022/`, `Deployment Scripts - 06-06-2022/` | Earlier snapshots. June 6 uses service prefix `click-staging-`. Later snapshots use `tcf-test-` / `tcf-prod-`. |
| `click-example-templates/` | Generic Serverless examples (`serverless-template.yml`, deployment bucket, an actual policies example) |
| `S3-Lamda-SSIS/` | `Python Lambda.py` and `run_my_example.json` |
| `Documentation and Sample Files - 20-12-2021/` | Import specs, sample CSV/XLSX, facilitator SQL, Reading Eggs exception-report guide |
| Root workbooks and transcripts | `System Business Rules.xlsx`, planning workbook, `Proposed Plan for Silicon Overdrive.xlsx`, three "Click System" meeting transcripts |

`Click System_2022-04-26.docx` and `Click System_2022-05-10.docx` are timestamped meeting transcripts, not specifications. `Click System_2022-05-24` has no extension; the file is a Word package (`word/document.xml`) and is also a transcript.

On `origin/prod` only: `Step Functions/`, `dms-postgres-mssql-dw/`, `Grade deregistration/`, `LMS Updated Document - 10-10-2022.doc`.

Zip archives of deployment folders are also committed (`Deployment Scripts.zip`, `Deployment_Scripts_v3.zip`, and a zip inside each v5 folder).

## Languages and frameworks

| Piece | Version in git |
|---|---|
| Import Lambdas | Node.js 14.x, Serverless Framework 3, `pg` 8.7.x, AWS SDK v3 clients for S3 and Secrets Manager, plus AWS SDK v2 in some `package.json` files |
| `secrets-test` | Node.js 12.x |
| `get-secrets` | Python 3.8 |
| `cemis-launch-reports`, `ddd-launch-import-learner-ddd-finalize-NOT-USED` | Python 3.9, handler `handler.hello` |
| Deployment stacks | Serverless Framework, provider runtime Python 3.7 (CloudFormation only), plugins `serverless-deployment-bucket` and `serverless-parameters` |
| Database | Aurora PostgreSQL 13 (`Engine: aurora-postgresql`, family `aurora-postgresql13`, port 5432) |
| SQL dialect | PostgreSQL with AWS Schema Conversion Tool leftovers: `aws_sqlserver_ext`, comments marked Severity CRITICAL for `@@TRANCOUNT`, `SET NOCOUNT`, and explicit transactions |

No `Dockerfile`, no CI definition, and no automated test project were found. `.vscode/settings.json` is present.

`sonar-project.properties` and `sonarqube_scan.bat` were listed when the onboarding task was created. They are not in `master` or `origin/prod`.

## AWS layout (v5 templates)

Region `af-south-1` for VPC, NACL, EC2, RDS, backup, CloudTrail, SNS, and alarms. Budgets are `us-east-1` (`tcf-test-budget_serverless.yml`), which is required for AWS Budgets.

Deploy order from `Deployment_Scripts_v5/README.md`:

1. VPC — `tcf-test-vpc_serverless.yml`
2. NACL — `tcf-test-nacl_serverless.yml`
3. EC2 — `tcf-test-ec2_serverless.yml`
4. RDS — `tcf-test-rds_serverless.yml`
5. Backup — `tcf-test-bkp_serverless.yml`
6. CloudTrail — `tcf-test-ctl_serverless.yml`
7. Budgets — `tcf-test-budget_serverless.yml`
8. SNS technical notifications — `tcf-test-sns-tech-note_serverless.yml`
9. CloudWatch alarms — `tcf-test-cw-alarms_serverless.yml`

Profile in the test README: `tcf-test`. Customer name `tcf`, stage name `test`.

### Network and compute

- VPC stack exports subnets consumed by RDS (`PrivateSubnetA2`, `PrivateSubnetB2`).
- EC2 default instance type `t3.micro`, key name `tcf-test-ec2-key-01`, AMI description "Windows Server 2019", default AMI `ami-080467c693d304c8e`.
- The 10 May 2022 transcript says this Windows instance runs SQL Server, SSIS, and SSRS, and that the two web portals run there with only the database connection changed. Those applications are not in this repository.

### Aurora

From `tcf-test-rds_serverless.yml`:

- Cluster identifier pattern `tcf-test-auroradb-cluster`.
- Storage encrypted with a CMK `alias/rds`. Deletion protection `true`. PostgreSQL logs exported to CloudWatch.
- Two instances, identifiers `Primary` and `Read-Replica`, `PubliclyAccessible: false`, Performance Insights on.
- Backup window and retention are parameters. Tag `backup=daily`.
- Custom option group and parameter group for `aurora-postgresql13`.
- RDS event subscription publishes to the SNS topic exported as `tcf-test-SNSTechnicalNotification`.
- Parameter defaults include database name `applicationDb`, user `dbadmin`, instance identifier `myAuroraPsqlDb`. The Lambdas do not use `applicationDb`. They connect to databases `ClickSTAGING` and `ClickDW`. How those databases are created on the cluster is **not in the template**. **Requires Confirmation**.
- `DatabasePassword` has `NoEcho` commented out, so a value passed as a parameter can be stored in the template history in clear text.

`Deployment_Scripts_v5 - PROD/tcf-test-rds_serverless.yml` sets service `tcf-prod-rds`, profile `tcf-prod`, stage name `prod`. VPC, EC2, and NACL in that folder follow the same `tcf-prod` naming. CloudTrail, alarms, budget, and backup files in the prod folder were still named and, in the copies inspected at the service header, still labelled `tcf-test` in the earlier v5 generation. Confirm each file's `service:` and `profile:` before a prod deploy. **Requires Confirmation**.

### Budgets and alarms

The test budget is named `tcf-test-Cost-Budget`, limit amount 250 (USD is the usual Budgets unit; the currency line was not re-read). Notifications at 50%, 100%, and 150% for both `FORECASTED` and `ACTUAL`, to `Heinrich@overdrive.co.za`, `awsbudgetalerts@overdrive.co.za`, and for the 150% actual also `AWS.Escalations@overdrive.co.za`.

`recommendations.txt` records security and tagging items (budget reports, CloudTrail log encryption and CloudWatch logs, S3 lifecycle of 365 days on the flow-log bucket, SNS, EC2 alarms, detailed monitoring, termination protection, tags) as done in the scripts. `Remediations from v4 to v5` records a second assessment: budget thresholds corrected, CloudTrail made multi-region, flow logs set to all traffic, custom NACLs, security-group descriptions, KMS rotation, RDS events, option group, parameter group, backup cron moved to 02:00 (`0 2 * * ? *`), insufficient-data alarm actions removed. Items explicitly left to Terraform base setup or the management account: default route table, DHCP options, default NACL, default security group, GuardDuty, AWS Config rules. SNS `technical-notifications` has no subscription on purpose ("no-one wants to get the messages"). EBS encryption by default was noted as not possible from CloudFormation.

### Lambda network

Staging Lambdas set:

- Deployment bucket `click-staging-serverless-deployment`
- IAM role `arn:aws:iam::960234982993:role/click_lambda`
- Security group `sg-0470aacf16ea3329b`
- Subnets `subnet-02e3129ae35cebe65` and `subnet-06dddb3da043819e7`
- Region `af-south-1`, stage `staging`
- Memory 512 MB unless noted, timeout from 10 seconds to 900 seconds

`cemis-import-files` is 2560 MB and 120 seconds. `ddd-import-files` is 1024 MB and 60 seconds. Reading Eggs transfer functions and registration update/export use timeout 900.

Step Functions on `origin/prod` call Lambdas in account `409399318280`, not `960234982993`. Two account ids are therefore hardcoded. **Requires Confirmation** which account is live.

## Databases

Lambdas read secret `staging-aurora` and connect to database `ClickSTAGING`, or secret `dw-aurora` and connect to database `ClickDW`. Both secrets are expected to be JSON with `username`, `host`, `password`, and `port`.

`functions/foreign database config.sql` creates `postgres_fdw` servers:

- From staging, server `click_dw` points at host `tcf-prod-auroradb-dw` in `af-south-1`, database `ClickDW`.
- From the warehouse, server `click_staging` points at host `tcf-prod-auroradb-staging` in `af-south-1`, database `ClickSTAGING`.

The script also contains a plaintext password for user `postgres`. It is not repeated here. The script should be treated as compromised material: rotate the password and remove it from git. **Requires Confirmation** that this has been done. The deployment template's cluster identifier pattern (`tcf-test-auroradb-cluster` / `tcf-prod-auroradb-cluster`) does not match these two host names (`tcf-prod-auroradb-dw` and `tcf-prod-auroradb-staging`). The FDW script may describe a later, split-cluster layout. **Requires Confirmation**.

`functions/Other/After import from MSSQL to Postgres update.txt` records post-migration identity reseed values and `VACUUM` on `learner`, `SourceLearner`, and `DataImport`. It says views must be reloaded when procedures that came from SCT are updated.

Foreign tables on the warehouse side mirror staging import tables (`learner`, `learnerdddchangedid`, `learnerdddmergedid`, `readingeggs`, `quizsubmission`, `quizsubmissionresponse`, `parentInfoconsenthomelearning`, and data-import tables). Foreign tables on the staging side mirror warehouse school, programme, and source-learner objects.

Portal functions live in schema `portal`: `pidentitybyusername`, `puserbyusername`, `puserbyuserid`, `puserredirectto`, `pschoollistprovinceregiondistrictfilter`. A duplicate-style pair also exists under `functions/Other` (`portal.puserbyusername`).

### Procedures the Lambdas call

| Lambda | Database | Call |
|---|---|---|
| `update-dataimport` | staging | `CALL dbo.pupdatedataimport(id, 'Success', 'Completed')` |
| `ddd-complete-ddd-file-import` | ClickDW | `call internal.pcompletedddfileimport()` |
| `cemis-validate-learner` | staging | `reporting.pvalidatecemislearner(id, 1, 1)` |
| `transferdw-learner-registration-get-learner-count` | staging | `dbo.plearnersregisterfromsourcelearnerf` |
| `transferdw-learner-registration-update-learners` | ClickDW | `public.fupdatedone`, `internal.pnewdataimportid`, `internal.pTransferLearner`, `internal.pupdatelearnerfromsourcelearner`, `internal.pUpdateDataImport`, `internal.pgenerateusernames` |
| `transferdw-learner-registration-export` | ClickDW | `internal.pgetprogrammeregistrationregionlistf` for `'RE'`, Reading Eggs learner and class extract functions, `internal.pExtractReflectiveLearningRegistrationLearnerListf('South Africa')` |
| `import-readingeggs` | staging | `dbo.pnewdataimportid`, `aws_s3.table_import_from_s3`, `dbo.pUpdateDataImport` |
| `transferdw-readingeggs` | ClickDW | `internal.pNewDataImportID`, `internal.pUpdateLearnerFromReadingEggsf`, `internal.pTransferReadingEggs2`, `internal.pUpdatePersonProgrammeActivity('4')`, `internal.pactivateschoolreadingeggsf`, `internal.pupdatedataimport` |

`aws_s3` must be installed on the staging database. That extension is not created by the SQL in `functions/`. **Requires Confirmation** that the cluster has it.

Username generation includes programmes 4, 8, and 9 when the filter is `ALL`, and only calls generators for 8 (Matific) and 9 (Reflective Learning). Western Cape is `left(emiscode, 1) = '1'`.

Several procedures are duplicated with "copy" or "test" in the file name (`ptransferlearner - Copy.sql`, `ptransferlearner_test.sql`, `pupdatelearnercemis copy.sql`, `pgenerateusernamesreflectivelearining - copy.sql`, and a misspelled `pgenerateusernamesreflectivelearining.sql` beside `pgenerateusernamesreflectivelearning.sql`). Which file was applied to the database is **Unknown**.

## Orchestration

No Lambda `serverless.yml` on `master` defines `events:`. Nothing on `master` invokes another Lambda. The handlers return JSON (bucket, keys, data-import ids, record counts) that a caller must pass to the next handler.

`origin/prod` holds the callers, as Step Functions in `af-south-1` account `409399318280`, using `$LATEST`:

| State machine | Order |
|---|---|
| `Import_Learner_DDD.json` | `ddd-get-files` → `ddd-get-dataimportid` → `ddd-import-files` → `ddd-update-dataimport` → parallel(`ddd-complete-ddd-file-import` → `ddd-import-learner-ddd-finalize` → `learner-registration-check`, `cemis-launch-reports`, `ddd-move-files`) → `ddd-vacuum-learnerdatareadingeggs` → `send-email` |
| `Import_Learner_CEMIS_Simple.json` | `cemis-get-files` → `cemis-get-dataimportid` → `cemis-import-files` → `cemis-validate-learner` → `cemis-update-datejoined-dateleft` → `update-dataimport` → `cemis-update-source-learner` → `cemis-update-first-usable-data` → `cemis-update-shool-data-source` → parallel(registration check, `cemis-launch-reports`, `cemis-move-files`) → `send-email` |
| `TransferDW_Learner_Registration.json` | get learner count → update learners → export → `send-email` → `transferdw-learner-registration-export-refl` → `send-email` |
| `Import_Readingeggs.json` | `import-readingeggs` → `transferdw-readingeggs-start` → choice → update learner from Reading Eggs → `pTransferReadingEggs2` Lambda → person programme activity → activate school → `send-email` → back to start |
| `Import_Readingeggs_Learnerlist.json` | import learner list → transfer learner list → `transferdw-readingeggs-learnerlist-export-matific` → `send-email` |
| `Activate_School_Grade.json` | `import-environment` → `import-environment-transfer` |

`send-email`, the vacuum Lambda, the reflective export Lambda, and the Matific export Lambda are not directories on `master`. The DDD state machine's comment text is still the CEMIS comment.

There is no EventBridge schedule in these JSON files. What starts a state machine is **Unknown**.

## TAO client

`tao-get-delivery` and `tao-delivery-import`:

- POST client-credentials to `https://auth-ignite.prod.gcp-eu.taocloud.org/v1/oauth2/tokens?with-refresh-token=true`.
- Client id and secret are literals in source, with the comment "to move out creds". Do not copy them into other systems. Rotate them. They are in git history.
- Ensure `dbo.datasourcetype` has description `TAO: Click Foundation eQuiz`.
- Insert `dbo.datacontract` and `dbo.dataimport` on staging, and `internal.dataimport` on `ClickDW`.
- `tao-get-delivery` assigns the staging password onto the warehouse connection object. Host and user come from `dw-aurora`. That looks like a defect.
- `tao-get-delivery/serverless.yml` stage is `prod`. `tao-delivery-import` stage is `prod`. `tao-transferquiz` stage is `staging`.
- Sibling folders (`Single-Item`, delivery-result handlers, test metadata, test-taker) contain `index.js` and `package.json` and, for several, no `serverless.yml`. They look like API experiments rather than deployed services.

The Node client sets `pg` type parser 1114 (timestamp without time zone) to return the raw string.

## S3 and file names

`ddd-get-files` builds:

- `Click Foundation Extract {yyyy-mm-dd}.csv`
- `Click Foundation Exception Report Merged Ids {yyyy-mm-dd}.csv`
- `Click Foundation Exception Report Changed Ids {yyyy-mm-dd}.csv`

The date is 10 characters taken from the end of the first object key (`substr(length - 14, 10)`), which matches a key that ends in `{date}.csv`. The bucket is `event.detail.bucket.name`. A commented default in the same file is `click-poc-cpt-ddd`.

CEMIS import SQL in the Lambda creates a temp table with EMIS, id number, full name, age, gender, grade, and class. One statement sets `DataDateYear` to the literal `'2020'` and comments that it should be a parameter.

Reading Eggs import uses `aws_s3.table_import_from_s3`. The bucket and key come from the event. The progress note's old paths (`C:\OMS\Data\...`) belong to the SSIS era and are not used by the Lambda.

## Reporting hook

`cemis-launch-reports` is the Lambda the Step Functions call "Launch Reports Generate SSIS Package". On `master` its serverless file points at `handler.hello`. The body was not reviewed beyond the runtime.

`S3-Lamda-SSIS/Python Lambda.py` calls SSM `send_command` in `af-south-1` against instance `i-0adb9463103c3b57b` with document `AWS-RunPowerShellScript` and command `c:\rootfolder\testbat.bat`, timeout 30 seconds. Treat it as an example. The instance id is environment-specific.

## Security findings in the tree

These are observations, not a remediation plan. Secret values are intentionally omitted.

| Finding | Where |
|---|---|
| PEM private keys committed | `Deployment Scripts - 06-06-2022/tcf-test-ec2-key-01.pem`, the same file name under `Deployment Scripts - 08-06-2022`, `Deployment_Scripts_v3`, `Deployment_Scripts_v5`, and both `tcf-test-ec2-key-01.pem` and `tcf-prod-ec2-key-01.pem` under `Deployment_Scripts_v5 - PROD` |
| FTP password, username, and host in a Word document | `Documentation and Sample Files - 20-12-2021/Learner Imports/DDD and CEMIS Import.docx` |
| `postgres` password and RDS host names in SQL | `functions/foreign database config.sql` |
| TAO OAuth client credentials in source | `TAO Datastore API/Delivery/tao-get-delivery/index.js` and `tao-delivery-import/index.js` |
| Google Drive `credentials.json` | `origin/prod` only, under `get-file-from-gdrive` and `get-file-from-gdrive-py` |
| RDS master password parameter with `NoEcho` commented out | `tcf-test-rds_serverless.yml` |
| Lambda catch blocks log the error and then use credentials that may be undefined | Pattern across `index.js` handlers |
| SQL string concatenation of event values into queries | Registration, CEMIS, and Reading Eggs handlers. This is an injection risk if events are not fully trusted. |

## Local development and testing

Lambda READMEs are the Serverless Framework Node example (`serverless deploy`, `serverless invoke local`). They do not describe this project's data.

No PHPUnit, Jest, pytest, or other test directory was found. `secrets-test` and `files-test` are manual experiments.

SQL procedures contain `RAISE NOTICE` traces and `par_debugmode` on `pvalidatecemislearner`. That is the only in-repo testing aid observed.

A checkout on Windows fails for a few sample CSVs whose paths exceed MAX_PATH unless `core.longpaths` is enabled. Those files are sample extracts under `Documentation and Sample Files - 20-12-2021`. They are not required to read the code.

## Configuration inventory

| Name | Used by |
|---|---|
| Secret `staging-aurora` | Staging Lambdas and the TAO client |
| Secret `dw-aurora` | Warehouse Lambdas and the TAO client |
| Role `click_lambda` | Lambda serverless files (account `960234982993`) |
| Bucket `click-staging-serverless-deployment` | Lambda deploy bucket |
| Profile `tcf-test` / `tcf-prod` | Stack deploy |
| Programme ids 4, 8, 9 | Username and Reading Eggs activity SQL |
| Data source description `TAO: Click Foundation eQuiz` | TAO insert |

Environment variables are not the main configuration path. Behaviour is hardcoded in each `index.js` (secret name, database name, region, programme id).