# Technical documentation — datafy_lambda

Evidence date: 2026-10-17.

Source: `git@bitbucket.org:overdrive/datafy_lambda.git`, `main` at `e985575` (2025-03-05). Full SHA `e9855759523cabec306577a106d5cd30e4311d0c`.

Secret material is described by where it lives, not by its value.

## Architecture

Four independently deployed AWS SAM applications plus local Node scripts and a Supabase CLI folder. They share a Supabase Postgres database and Storage by URL and key. They do not call each other except `ReadFiles`, which invokes `CreateTasks` by a hardcoded function name.

```text
file_processing/          Node, manual, dotenv
old/                      earlier Node copies
read_edge_function/       Supabase CLI, edge function, schema dump
Scripts/                  SQL text, not applied by this repo
lambda/datafy/            SAM stack "datafy"
lambda/datafy_new/        SAM stack "datafyNew"
lambda/processes/         SAM stack "processes"
lambda/Rekognition/       SAM stack "Rekognition"
```

Region written on IAM ARNs: `eu-west-1`. The readme's `pg_dump` host is `aws-0-eu-central-1.pooler.supabase.com`. SAM `samconfig.toml` files do not set `region`.

## Repository layout

| Path | What it is |
|---|---|
| `readme.md` | Dump/restore commands and the import narrative |
| `package.json` | Root dependencies `@azure/msal-node`, `axios`, `xlsx`. No `scripts` other than the defaults implied by a bare dependencies file. No source file imports `msal` |
| `.gitignore` | Ignores `node_modules/`, `dist/`, logs, IDE folders. Does not ignore `.aws-sam/`, `.env`, or `*.yaml` secrets |
| `file_processing/` | Package `read_xlxs` 1.0.0 |
| `old/` | `callsheet`, `read_callsheet`, `read_sjreport`, `store`, `store_product`, `task`, `users` |
| `read_edge_function/supabase/` | `config.toml`, `config.old`, migrations, `seed.sql` (empty), function `read_sj_report` |
| `Scripts/` | 40 SQL files. Names `Script.sql` through `Script-32.sql` plus descriptive names |
| `lambda/*/template.yaml` | SAM resources |
| `lambda/*/samconfig.toml` | Stack name and deploy flags |
| `lambda/*/.aws-sam/` | Build output committed under `datafy_new` and `Rekognition` |

`sonar-project.properties` and `sonarqube_scan.bat` were listed when this onboarding issue was opened. They are not in git at this revision.

`git ls-tree -r` counts 81,689 paths. Almost all of them are under `.aws-sam` (botocore data files and installed wheels). A checkout on Windows fails with "Filename too long" unless long paths or a sparse checkout is used.

## Languages and frameworks

| Area | Runtime | Libraries |
|---|---|---|
| `lambda/datafy`, `lambda/datafy_new` | Python 3.9, `x86_64` | `requests`, `supabase`, `boto3`, `pandas`, `openpyxl` |
| `lambda/processes` | Python 3.9, `x86_64` | `get_training_images/requirements.txt` adds `Pillow`. `convert_xml_to_json/requirements.txt` does not |
| `lambda/Rekognition` | Python 3.10, `x86_64`, tracing active | `boto3`, `Pillow`, `requests`, `supabase` |
| `file_processing` | Node (version not pinned) | `@supabase/supabase-js` ^2.43.1, `node-xlsx` ^0.24.0, `date-fns` ^3.6.0, `dotenv` ^16.4.5 |
| Edge function | Deno, Supabase Edge | `npm:@supabase/supabase-js`, `npm:@supabase/storage-js`, SheetJS from `deno.land/x/sheetjs` |
| Database | Postgres 15 in `config.toml` | PostGIS extension in the schema dump. Supabase Auth and Storage |

SAM transform `AWS::Serverless-2016-10-31`.

Globals:

| Stack | Timeout | Memory | Logging |
|---|---|---|---|
| `datafy`, `datafyNew` | 900 | 1024 | JSON |
| `processes` | 600 | 1024 | JSON |
| `Rekognition` | 900 | 1024 | JSON, X-Ray `Tracing: Active` |

## SAM resources

### Stack `datafy` (`lambda/datafy`)

| Resource | Handler | Trigger |
|---|---|---|
| `ReadFiles` | `read_files/app.lambda_handler` | Schedule `rate(1 minute)` |
| `CreateTasks` | `create_tasks/app.lambda_handler` | None. Invoked by `ReadFiles` |
| `UpdateStoreIssues` | `update_store_issues/app.lambda_handler` | None |

Environment on each function: `SUPABASE_URL=https://alb.datafyapp.co.za` and `SUPABASE_KEY` set to a JWT in the template.

`ReadFiles` IAM allows `lambda:InvokeFunction` on `arn:aws:lambda:eu-west-1:<account>:function:datafy-CreateTasks-EWGo39rP5O56`.

`template copy.yaml` is an older shape: `ReadFiles` is an API event, not a schedule.

### Stack `datafyNew` (`lambda/datafy_new`)

| Resource | Handler | Trigger |
|---|---|---|
| `ReadFiles` | `read_files/app.lambda_handler` | Schedule `rate(1 minute)` |
| `ReadPromosKaching` | `read_promo_kaching/app.lambda_handler` | Schedule `rate(1 minute)` |
| `CreateTasks` | `create_tasks/app.lambda_handler` | None. Invoked by name |
| `UpdateStoreIssues` | `update_store_issues/app.lambda_handler` | None |

No environment variables. SSM parameters `/supabase/url` and `/supabase/anon`. `ReadFiles` may also invoke `datafyNew-CreateTasks-ccMR48YAjXLP`.

Deploy config: `stack_name = "datafyNew"`, `capabilities = CAPABILITY_IAM`, `confirm_changeset = true`, `resolve_s3 = true`, cached parallel build.

### Stack `processes` (`lambda/processes`)

| Resource | Handler | Trigger |
|---|---|---|
| `ConvertTrainingImages` | `get_training_images/app.lambda_handler` | S3 `ObjectCreated` on `TrainingS3Bucket`, suffix `.xml` |
| `TrainingS3Bucket` | `AWS::S3::Bucket` name `datafy-prod-training-images` | |
| `ConvertXmlToJson` | commented out | would have been the same bucket and suffix |

Policy on the function: `AmazonS3FullAccess`.

The destination bucket `datafy-prod-training` is a string in `get_training_images/app.py`. The template does not create it.

### Stack `Rekognition` (`lambda/Rekognition`)

| Resource | Code | Role |
|---|---|---|
| `RekognitionStateMachine` | `statemachine/rekognition.asl.json` | Start, then Analyze, then Stop |
| `StartModelFunction` | `functions/start_model` | `start_project_version`, waiter `project_version_running` |
| `AnalyzeImageFunction` | `functions/analyze_image` | `detect_custom_labels` |
| `StopModelFunction` | `functions/stop_model` | `stop_project_version` |

Policies: `AmazonRekognitionFullAccess`, `AmazonS3FullAccess`, and `ssm:GetParameter` / `ssm:GetParameters` on `arn:aws:ssm:eu-west-1:<account>:parameter/*`.

Outputs: state machine ARN and role ARN.

The EventBridge daily rule and its invoke role are comments.

SSM names used by the functions:

- `/datafy-rekognition-stack/project-arn`
- `/datafy-rekognition-stack/model-version-name`
- `/datafy-rekognition-stack/model-arn`
- `/datafy-supabase-stack/supabase-url`
- `/datafy-supabase-stack/JwtSecret/ServiceRoleKey`

`MinInferenceUnits` is 1. `MinConfidence` is 50.

## Data

### Schema dump (2024-06-27)

File: `read_edge_function/supabase/migrations/20240627123825_remote_schema.sql` (about 1,300 lines of SQL). A second migration file `20240627120008_remote_schema.sql` is empty.

Tables created there:

`app_user`, `app_user_duplicate`, `callsheet`, `callsheet_20240606`, `callsheet_test`, `client`, `customer`, `customer_client`, `file`, `file_upload`, `log`, `logs`, `mock_data`, `product`, `sjreport`, `sjreport_20240606`, `store`, `store_product`, `task`, `task_20240618`, `task_file`, `task_test`, `user`.

`app_user.user_id` references `auth.users`. `task` references `client`, `customer` (two foreign keys on `customer_id`), `store`, and `auth.users`.

`store.location` is `geography`. The dump creates PostGIS.

Functions in that dump include `get_all_auth_users`, `get_all_auth_users2`, `get_callsheet_day`, `get_grouped_tasks`, `get_list_callsheet`, `group_by_items`, `group_by_stores`, `group_by_users`, `group_tasks_by_store_name`, `log_changes`.

`log_changes_trigger` is attached after insert, update, and delete on every public table in the dump.

RLS is enabled on `mock_data` only. Policies exist for `app_user` and `task` (see `business-rules.md`).

`seed.sql` is empty.

### Tables the later code expects that are not in the dump

| Name | Where it is defined or used |
|---|---|
| `sjreport_uploads` | `Scripts/Upload trigger.sql`. Columns `id`, `payload` jsonb, `processed` boolean default false, `created_at` |
| `promo_kaching_uploads` | Used by `ReadPromosKaching`. No `CREATE TABLE` in the repo |
| `kaching` | `Scripts/create KaChing.sql` |
| `product_images`, `product_ml` | `Scripts/Create product table.sql` |
| `brand`, `customer_brand` | `Scripts/Script-20.sql` |
| `store_temp` | Updated by `update_store_issue_counts_temp` |
| `sjreport_backup_20241209`, `task_backup_20241209` | `Scripts/sj-task-backup-restore.sql` |
| `realtime.chat`, `realtime.chat_messages` | `Scripts/Script-32.sql` |

`Scripts/Create product table.sql` also `CREATE TABLE product`, which the dump already created. Running that script on the dumped database would fail or replace the table depending on how it is applied. The file does not use `IF NOT EXISTS` for `product`.

### Task fields written by `datafy_new` CreateTasks

`chain`, `store_name`, `status`, `category`, `brand`, `product`, `variant`, `size`, `sjreport_id`, `store_code`, `type`, `name`, `store_soh`, `system_dros`, `scoring_system`, `last_sold`, `week_start_date`, `product_id`, `article`, `customer_id`, `dc_soh`.

The June 2024 `task` table has no `store_code`, `store_soh`, `system_dros`, `scoring_system`, `last_sold`, `week_start_date`, `product_id`, `article`, or `dc_soh` columns. Those columns were added later, or the insert fails. **Requires Confirmation.**

## Auth

- Node scripts: `createClient(SUPABASE_URL, SUPABASE_ANON)` from dotenv. `signup_users.js` also uses `signInWithPassword` and `auth.signUp`, then a raw SQL update of `auth.users` through an undefined `pool`.
- `lambda/datafy`: anon JWT in the CloudFormation template.
- `lambda/datafy_new`: SSM secure-string read (`WithDecryption=True`) of `/supabase/anon`.
- Rekognition analyze: service-role key from SSM.
- Edge function: `SUPABASE_URL` and `SUPABASE_ANON_KEY` from the Deno environment. The handler does not verify the caller.
- Several `old/` scripts embed the project URL and anon JWT in source.
- `.env` files are committed under `file_processing/` and `old/*`. Their contents were not copied into Silicon Ark.
- `signup_users.js` contains a fixed password literal.

No Cognito user pool, no API Gateway authorizer, and no IAM authorizer on an HTTP API. The schedules are not HTTP.

## Storage

| Bucket | Use |
|---|---|
| Supabase `uploads` | SJ files under prefix `sjreport`. The trigger checks that prefix. Node scripts also reference `callsheet`, `store`, and `sjreport/live` URLs |
| Supabase `ml` | Annotated product JPEGs at `product/<filename>.jpg` |
| Supabase `assets` | One commented store-file URL in `old/store` |
| S3 `datafy-prod-training-images` | Incoming VOC XML (and the sibling JPEG the code expects) |
| S3 `datafy-prod-training` | Cropped JPEGs. Not declared in the template |

`ReadFiles` downloads `public/<payload bucket>/<file name>`. The trigger stores `bucket_id` `uploads`, so the download path is `public/uploads/<file name>`.

## Background work

| Worker | How it starts | Concurrency note |
|---|---|---|
| `ReadFiles` | Every minute, both stacks | One queue row each. Overlapping invocations are not locked |
| `ReadPromosKaching` | Every minute | Same |
| `CreateTasks` | Async Lambda invoke | No reserved concurrency in the template |
| `UpdateStoreIssues` | Not wired | |
| `ConvertTrainingImages` | S3 event | Retries the JPEG inside the handler for up to 60 seconds |
| Rekognition state machine | Not wired to a schedule | Start waits until the model is running, inside the 900 second timeout |

There is no SQS queue and no DLQ in the templates.

## Environment configuration

| Name | Where |
|---|---|
| `SUPABASE_URL`, `SUPABASE_KEY` | `lambda/datafy` template environment |
| `SUPABASE_URL`, `SUPABASE_ANON` | `file_processing` scripts via dotenv |
| `SUPABASE_USER`, `SUPABASE_USER_PASSWORD` | `signup_users.js` |
| `SUPABASE_URL`, `SUPABASE_ANON_KEY` | Edge function |
| SSM `/supabase/url`, `/supabase/anon` | `datafy_new` |
| SSM `/datafy-supabase-stack/*`, `/datafy-rekognition-stack/*` | Rekognition |

`file_processing` contains both `.env` and a file named `env`.

Local database commands in the readme:

- `pg_dump` against the pooler, custom format, to `/tmp/remote_db_backup.dump`.
- `npx supabase db dump` for schema and for data.
- `psql` against `localhost` port `54322`, user `postgres`, database `postgres`.

## Deployment

Documented in the starter READMEs:

```text
sam build --use-container
sam deploy --guided
```

`samconfig.toml` already has a stack name, so a later deploy can use the saved flags. `confirm_changeset = true`.

Local invoke examples in `lambda/datafy/README.md`:

```text
sam local invoke ReadFiles --event events/uploadEvent.json
sam local invoke CreateTasks --event events/uploadEvent.json
sam local invoke UpdateStoreIssues --event events/uploadEvent.json
```

`events/event.json` and `events/uploadEvent.json` are the SAM sample events, not a captured SJ upload.

No `bitbucket-pipelines.yml`, Dockerfile, or serverless framework file.

## Observability

- Lambda log format JSON on all four stacks.
- CloudWatch via `sam logs` as described in the starter README (`--stack-name datafy` or `rekognition`). The README examples still say `HelloWorldFunction` and `StockCheckerFunction`.
- Application Insights auto-configuration on each stack.
- Rekognition functions have X-Ray tracing. The other stacks do not set `Tracing`.
- Handlers `print` progress (row counts, bucket, file name). Insert payloads are printed in `ReadFiles` for the tail batch (`print(f"Data : {data_to_insert}")`).

## Security notes for reviewers

These are observations, not a remediation plan.

- Anon JWTs are committed in `lambda/datafy/template.yaml` and in several `old/**/*.js` files.
- `.env` files are committed.
- IAM for processes is `AmazonS3FullAccess`. Rekognition functions also have `AmazonS3FullAccess` and `AmazonRekognitionFullAccess`, and SSM read on `parameter/*`.
- `ReadFiles` may invoke one specific function ARN and also has `lambda:InvokeFunction` in the statement.
- The signup script confirms phones by writing `auth.users` and uses a fixed password.
- RLS coverage in the checked-in dump does not match the policy names.
- `.aws-sam` build trees are in git, including installed packages.

## Development and tests

- SAM unit tests are the upstream hello-world and stock-trader samples. `python -m pytest tests/unit -v` is the command in the README. They import `from read_files import app` and build an API Gateway event. They do not assert SJ behaviour.
- Integration tests are the SAM samples and require `AWS_SAM_STACK_NAME`.
- `file_processing` `npm test` is a stub that exits 1.
- No PHPUnit, no CI, no coverage gate, no linter config beyond `samconfig.toml` `lint = true` for `sam validate`.
- `.vscode/settings.json` at the repo root and under `read_edge_function/.vscode/`.

## Local build notes from this inspection

A normal `git clone` of `main` tries to write the `.aws-sam` tree and fails on Windows with path-length errors. Sparse checkout of the 213 non-vendor paths succeeds. That is a property of the revision, not of Silicon Ark.

## Unknown / requires confirmation

- Whether `https://alb.datafyapp.co.za`, the Supabase project ref in the readme, and the SSM parameters are one database.
- Whether stacks `datafy` and `datafyNew` are both deployed.
- Whether `update_product_table`, `update_store_issue_counts`, `get_store_data_pages`, `get_issue_counts`, and `update_store_issues` exist, and whether their signatures match the Python calls.
- Whether the `task` unique index expected by `on_conflict` exists.
- What starts the Rekognition state machine.
- Whether the S3 bucket `datafy-prod-training` exists.
- Whether `datafy-lambda-new` has replaced this repository since 2025-03-05.