# PRD — datafy_lambda

Evidence date: 2026-10-17.

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

There is no prior PRD in the source repository or in Silicon Ark. Requirements below are recovered from `readme.md`, the SAM templates, the handlers, `file_processing/`, `read_edge_function/`, and `Scripts/`. Provenance is labelled. Secret values committed in the tree are omitted.

The SAM `README.md` files under `lambda/datafy`, `lambda/datafy_new`, and `lambda/processes` are the AWS SAM hello-world starter. They mention `hello_world`, `GET /hello`, and `sam local start-api`. The templates at HEAD do not declare an API event. Where the starter README disagrees with the template, the template is the implemented behaviour.

## Product purpose

**Documented.** Root `readme.md` titles the repository "Tools and Lambdas for Datafy" and describes importing a call sheet, a weekly SJ report, store files, store products, and tasks after a frontend upload.

**Implemented.** Scheduled Lambdas import `.xlsx` files from Supabase Storage into Postgres and build `task` rows from `sjreport` rows that are not "NO ISSUE - ITEM SELLING". A second stack crops training images. A third stack runs Rekognition custom labels on `product_images`.

**Unknown / Requires Confirmation:** the product owner, the live frontend, and whether this repository is still the one that production uses. Silicon Ark `datafy-lambda-new` describes a later repository whose readme still points at paths in this tree.

## Users and interactions

| Actor | Evidence | Status |
|---|---|---|
| Client who uploads the weekly SJ report | Root readme: "Once a week uploaded by client" | **Documented**. The upload UI is not in this repo |
| Field representative on a call sheet | `callsheet.rep_full_name`, `rep_cell_number`; `signup_users.js` creates `app_user` and Auth users from `group_by_users()` | **Implemented** in the script. Whether it is still run is **Unknown** |
| Person running the Node scripts | `file_processing/*.js` call `main()` at load. They read `SUPABASE_URL` and `SUPABASE_ANON` from the environment | **Implemented** |
| Person deploying SAM | `samconfig.toml` stack names `datafy`, `datafyNew`, `processes`, `Rekognition`. `confirm_changeset = true`, `CAPABILITY_IAM` | **Documented** in starter READMEs and **Implemented** in samconfig |
| EventBridge | `ReadFiles` and `ReadPromosKaching` use `rate(1 minute)` | **Implemented** |
| Authenticated Supabase user | Policies on `app_user` and `task` allow insert/select/update when `auth.role() = authenticated`. Task also allows delete | **Implemented** in the 2024-06-27 dump. The dump enables RLS only on `mock_data`. Whether those policies are enforced is **Unknown / Requires Confirmation** |
| HTTP caller of the edge function | `Deno.serve` on `read_sj_report` | **Implemented**. The function does not check a JWT in the handler |

No application role table is enforced by the Lambdas. They use a Supabase key (anon in the datafy template and in SSM parameter `/supabase/anon`; service role parameter path in Rekognition).

## Systems the product talks to

1. Supabase Postgres and Storage.
   - Readme dump host: `aws-0-eu-central-1.pooler.supabase.com`, port 6543, database `postgres`, user `postgres` plus the project ref written in that readme. Local restore uses `localhost:54322`.
   - Older Node files call `https://<that project ref>.supabase.co`.
   - `lambda/datafy/template.yaml` sets `SUPABASE_URL` to `https://alb.datafyapp.co.za` and puts a JWT in `SUPABASE_KEY`. The JWT is not repeated here.
   - `lambda/datafy_new` reads SSM `/supabase/url` and `/supabase/anon` in `eu-west-1`.
   - Rekognition reads `/datafy-supabase-stack/supabase-url` and `/datafy-supabase-stack/JwtSecret/ServiceRoleKey`, plus `/datafy-rekognition-stack/model-arn`, `project-arn`, and `model-version-name`.
   - Whether these hosts are one project is **Unknown / Requires Confirmation**.
2. AWS account and region hardcoded on IAM resources: `eu-west-1`. SAM config files do not set a region.
3. Lambda invoke, hardcoded function names:
   - `datafy-CreateTasks-EWGo39rP5O56` from `lambda/datafy/read_files/app.py`.
   - `datafyNew-CreateTasks-ccMR48YAjXLP` from `lambda/datafy_new/read_files/app.py`.
4. S3 buckets `datafy-prod-training-images` (created by the processes template) and `datafy-prod-training` (destination name in code, not created by the template).
5. Amazon Rekognition `start_project_version`, `detect_custom_labels` (minimum confidence 50), `stop_project_version`.
6. OneDrive, only in `old/read_callsheet/parse_callsheet_onedrive.js`, which downloads one hardcoded share link. The URL is not copied here. `@azure/msal-node` is a root dependency and is not imported by any checked-out script.
7. Application Insights on each SAM stack (`AutoConfigurationEnabled: true`).

No queue, mailer, or Bitbucket Pipeline is configured.

## Constraints

- Lambda timeout 900 seconds and 1024 MB on datafy, datafy_new, and Rekognition. Processes timeout is 600 seconds, 1024 MB.
- XLSX only. Other extensions return HTTP 400 "Unsupported file format" from `ReadFiles` and `ReadPromosKaching`.
- Insert batches of 1000 rows. Comments say larger inserts time out.
- `ReadFiles` and `ReadPromosKaching` process one unprocessed upload row per invocation (`limit(1)`).
- `CreateTasks` in `datafy_new` pages `get_sjreport_data_pages` by 100. The `lambda/datafy` copy pages by 1000 and sets the target date to the literal `2024-12-23` (the event date is commented out).
- Python runtime 3.9 except Rekognition, which is Python 3.10.
- Architectures `x86_64`.

## Major workflows

### WF-1 — Weekly SJ report import (documented)

**Documented** in `readme.md`:

1. A call sheet is uploaded about once a month and stored by `file_processing/1_parse_callsheet_files.js`.
2. Once a week the client uploads an SJ report. `file_processing/2_parse_sjreport_files.js` and `signup_users.js` run after the frontend upload.
3. `file_processing/3_parse_task.js` builds tasks per store per user.
4. Store and store-product files run when uploaded (`parse_store_files.js`, `parse_store_product_files.js`).

**Implemented** in those files, which disagrees with the readme on several points. See WF-2 and the conflict list.

### WF-2 — Scheduled SJ import (Lambda)

**Implemented** in `lambda/datafy_new/read_files/app.py` and `lambda/datafy_new/template.yaml`. The `lambda/datafy` handler is the same shape with the differences called out.

Trigger: EventBridge `rate(1 minute)`, input `{}`.

1. Load Supabase URL and key from SSM (`/supabase/url`, `/supabase/anon`). The older stack uses environment variables instead.
2. Call RPC `raw_sql` with `SET statement_timeout = '0'`. The older stack does not.
3. Select one `sjreport_uploads` row with `processed` equal to the string `FALSE`.
4. If none, return HTTP 400 body `Wrong upload folder`.
5. Download `public/<bucket_name>/<file_name>` from Supabase Storage. `bucket_name`, `file_name`, and the table name come from `payload`. The table name is `path_tokens[0]`.
6. If the file name does not end in `.xlsx`, return HTTP 400 and do not mark the row. If Excel parsing throws, set `processed` true and `error` "Unsupported file format", then return HTTP 400.
7. Lower-case headers and replace spaces with underscores. Empty cells become `""`.
8. For each row, if `channel` contains `SPAR`, prefix `store_code` with `S`. This prefix is in `datafy_new` only.
9. Parse `date` as the first 10 characters, format `%Y-%m-%d`. Week start is that date plus `(7 - weekday)` days, so the stored week start is the following Monday. A Monday in the sheet becomes the next Monday. The older handler parses the whole `date` string and does not prefix SPAR codes.
10. Add `row` (1-based), `week_start_date`, `file` (the storage file name), and `sheetname`.
11. Upsert in batches of 1000 on conflict `row, week_start_date, file, sheetname`.
12. A date that does not parse sets `processed` true and `error` "Invalid date format" and does not return, so the loop continues. A later success update sets `error` back to empty.
13. On insert exception, set `processed` true and `error` "Error inserting data", and return HTTP 400.
14. After all sheets, set `processed` true and `error` empty, call RPC `update_product_table` (`datafy_new` only), and invoke `CreateTasks` asynchronously with `{ "week_start_date": <last parsed Monday> }`.

`Scripts/Upload trigger.sql` is what fills `sjreport_uploads`: an `AFTER INSERT` trigger on `storage.objects` inserts a payload when `bucket_id = 'uploads'` and `path_tokens[1] = 'sjreport'`. That trigger is not in the June 2024 schema dump. Whether it is installed in the database the Lambda uses is **Requires Confirmation**.

### WF-3 — Build tasks from SJ rows

**Implemented** in `lambda/datafy_new/create_tasks/app.py`.

1. Read `event['week_start_date']`. Missing key raises. The older `lambda/datafy/create_tasks/app.py` ignores the event and uses `2024-12-23`.
2. Page RPC `get_sjreport_data_pages` until a short page.
3. The SQL file `Scripts/get_sjreport_data_pages.sql` returns rows where `week_start_date >= target_date` and `issue_for_feedback` is not `NO ISSUE - ITEM SELLING`, left-joined to `product` on category, brand, product, variant, and size.
4. Each row becomes a `task` with `type` `task` and `name` equal to `issue_for_feedback`. Numeric-looking strings have a trailing `.0` removed. Nulls become `""`, `0`, or `None` depending on the field.
5. Upsert batches of 1000 on conflict `chain, store_name, product_id, week_start_date, name, sjreport_id`.
6. `datafy_new` then calls RPC `update_store_issue_counts`.

The Python reads `article`, `customer_id`, and `dc_soh`. `Scripts/get_sjreport_data_pages.sql` does not return those columns. If the deployed function matches that file, those reads fail. **Requires Confirmation** of the deployed function signature.

The June 2024 `task` table has no unique constraint on the upsert columns. **Unknown** whether that constraint exists now.

### WF-4 — Promo / KaChing spreadsheet

**Implemented** in `lambda/datafy_new/read_promo_kaching/app.py`. Schedule `rate(1 minute)`.

Same XLSX download and header normalisation as WF-2, against `promo_kaching_uploads` instead of `sjreport_uploads`. It drops `id` and `created_at` columns, and upserts on `row, file, sheetname`. It does not compute a week start and does not invoke another Lambda.

On an insert exception it updates `sjreport_uploads`, not `promo_kaching_uploads`. **Implemented** as written. That looks like a copy error and **requires confirmation** before anyone treats it as intended.

No SQL in `Scripts/` creates `promo_kaching_uploads`. `Scripts/create KaChing.sql` creates table `kaching` and inserts example challenge rows. Whether the upload queue table exists is **Unknown / Requires Confirmation**.

### WF-5 — Node file importers

**Implemented** in `file_processing/`. Each script calls `main()` when loaded.

| Script | What it writes | Notes |
|---|---|---|
| `1_parse_callsheet_files.js` | `sjreport` | Readme says this is the call sheet. The function inserts `sjreport` and is invoked with the same SJ spreadsheet URL as script 2 |
| `2_parse_sjreport_files.js` | `sjreport` | Skips a row when `row`, `file`, `sheetname`, and `week_start_date` already exist. `week_start_date` is the argument, and the bottom call passes today's date, not a computed Monday |
| `3_parse_task.js` | none | Builds a task object from `get_list_callsheet()`, matching `sjreport` and `store` with `ilike`. The `task` insert is commented out |
| `parse_store_files.js` | `store` | Existence check reads `callsheet_test`, not `store` |
| `parse_store_product_files.js` | `store_product` | Inserts when the row key is absent |
| `signup_users.js` | Auth user and `app_user` | See WF-6 |
| `insert_one.js` | `sjreport` | Inserts one hardcoded row |

Header renames shared by the SJ parsers: `score_%` to `score`, `oos_-_place_order` to `oos_place_order`, the 7-day-cover header to `7_days_cover`, SOH-no-sales header to `soh_no_sales`, negative-stock header to `negative_stock`, 100-day header to `no_sales_100`, fixed/not-fixed header to `fixed`, ranged-no-stock header to `no_stock`.

`old/` repeats these scripts. Several `old/` files embed the Supabase URL and anon key in source. `old/callsheet/parse_callsheet_files.js` is the earlier call-sheet parser. It is not what `file_processing/1_parse_callsheet_files.js` does.

### WF-6 — Sign up reps from the call sheet

**Implemented** in `file_processing/signup_users.js`.

1. RPC `group_by_users`.
2. Sign in with `SUPABASE_USER` and `SUPABASE_USER_PASSWORD`.
3. Split `rep_full_name` on the first space into first name and surname.
4. Strip leading zeros from `rep_cell_number` and prefix `+27`.
5. Upsert `app_user` on `mobile_number`. Email is the phone plus `@datafy.co.za`.
6. `signUp` with that email, the phone, and a fixed password written in the source. The password is not copied here.
7. Then `pool.query` updates `auth.users.phone_confirmed_at`. `pool` is not created in this file. **Implemented** as a reference to an undefined `pool`.

### WF-7 — Training-image crop

**Implemented** in `lambda/processes/template.yaml` and `get_training_images/app.py`.

Trigger: `s3:ObjectCreated:*` on bucket `datafy-prod-training-images` for keys ending `.xml`.

1. URL-decode the key and replace `+` with space.
2. Expect a sibling `.jpg` with the same base name.
3. Retry the JPEG for up to 12 attempts, 5 seconds apart.
4. For each Pascal VOC `object`, crop `bndbox` and write JPEG to bucket `datafy-prod-training` at `<label>/<original>_<index>.jpg`.

`convert_xml_to_json/app.py` can write Rekognition-style normalised boxes back into the same bucket as JSON. Its function is commented out in the template. **Not deployed** by the current template.

### WF-8 — Rekognition label pass

**Implemented** in `lambda/Rekognition`.

The state machine `statemachine/rekognition.asl.json` runs Start Model, then Analyze Image, then Stop Model, with retries. The daily EventBridge rule in `template.yaml` is commented out. Nothing else in this repository starts the state machine. **Unknown** what invokes it.

1. Start loads the project ARN, version name, and model ARN from SSM, calls `start_project_version` with `MinInferenceUnits` 1, and waits until the version is running.
2. Analyze selects `product_images` where `processed` is false. For each row it downloads `image_path`, calls `detect_custom_labels` at minimum confidence 50, draws boxes, uploads the JPEG to storage bucket `ml` under `product/<name>.jpg`, inserts `product_ml`, and sets `processed` true.
3. Stop calls `stop_project_version`.

`product_images` and `product_ml` are created in `Scripts/Create product table.sql`. They are not in the June 2024 schema dump.

### WF-9 — Edge function read_sj_report

**Implemented** in `read_edge_function/supabase/functions/read_sj_report/index.ts`.

`Deno.serve` calls `checkNewFiles()`, which lists the first 100 objects in storage bucket `uploads` and does not call `processFile` (that loop is commented out). It then selects `app_user.first_name` limit 1 and returns `{ "message": "Hello <first_name>!" }`.

`processFile` contains an SJ insert path and references a variable `url` that the function does not define. It is not reached from `Deno.serve`.

Local invoke comment: POST `http://127.0.0.1:54321/functions/v1/read_sj_report`. `config.toml` project id is `read_edge_function`, API port 54321, database port 54322, Postgres major version 15.

## Integrations

| System | Direction | Trigger | Data | Auth as written | Failure |
|---|---|---|---|---|---|
| Supabase Storage | In | Schedule or manual script | `.xlsx` bytes | Anon key or SSM | HTTP 400 or a caught exception that marks the upload processed |
| Supabase Postgres | Out | After parse | Upsert into the table named by the storage folder | Same key | Insert error returns HTTP 400 on the Lambda path |
| Lambda `CreateTasks` | Out, async | After a successful `ReadFiles` pass | JSON `week_start_date` | IAM `lambda:InvokeFunction` on one ARN | Not checked. `InvocationType` is `Event` |
| S3 training buckets | In and out | S3 object created | XML in, JPEG crops out | `AmazonS3FullAccess` on the processes function | Missing JPEG after 12 retries logs an error and returns without a failed Lambda response (the 404 return is inside the helper and is ignored by the handler) |
| Rekognition | Out | State machine | Image bytes, model ARN | `AmazonRekognitionFullAccess` | Exceptions return a 500 body from Analyze, or the exception object from Start/Stop |
| SSM | In | Lambda start | URL, keys, model ARNs | `ssm:GetParameter` on named parameters. Rekognition allows `parameter/*` | Uncaught `get_parameter` failure fails the invocation |

## Conflict list

| Topic | Documented or earlier code | Code at `e985575` |
|---|---|---|
| Call sheet script | Readme points `1_parse_callsheet_files.js` at the monthly call sheet | That file inserts `sjreport` |
| Task build | Readme says `3_parse_task.js` builds tasks | The insert is commented out. Tasks are built by `CreateTasks` |
| SAM README | Hello-world API on port 3000 | No API event in the templates |
| `lambda/datafy` task date | Commit `ddc2986` says the date is sent to create-tasks | `lambda/datafy/create_tasks/app.py` uses `2024-12-23`. `lambda/datafy_new` uses the event |
| RLS | Policies exist for `app_user` and `task` | `ENABLE ROW LEVEL SECURITY` appears only for `mock_data` |
| Schema date | Dump is 2024-06-27 | Lambdas and `Scripts/` use tables that are not in that dump (`sjreport_uploads`, `kaching`, `product_images`, `product_ml`) |
| Sonar files | Discovery listing on 2026-09-28 | Not in git |

## Requirements discovered from the implementation

These are implemented behaviours, not a signed specification.

- FR-01. A spreadsheet under storage prefix `sjreport` in bucket `uploads` becomes a queue row, then table rows, then tasks for issue lines.
- FR-02. Only `.xlsx` is accepted by the Lambdas.
- FR-03. Re-import of the same sheet row updates on `(row, week_start_date, file, sheetname)`.
- FR-04. SPAR channel store codes gain an `S` prefix in `datafyNew` only.
- FR-05. Tasks are not created for `NO ISSUE - ITEM SELLING` when the SQL function in `Scripts/` is the one deployed.
- FR-06. Issue phrases counted onto stores are the five strings in `Scripts/Function - update_store_issue_counts.sql` and `Scripts/update_store_on_task_status_change.sql`.
- FR-07. Promo workbooks are imported by `ReadPromosKaching` into whatever table name is the first path token.
- FR-08. Unprocessed `product_images` are labelled and marked processed.
- FR-09. Rep sign-up uses a South African `+27` mobile and an email at `@datafy.co.za`.

## Non-requirements

The repository does not show a mobile UI, a web UI, push notifications, or a billing flow. KaChing reward text exists only as SQL insert values.