# Business rules — datafy_lambda Evidence date: 2026-10-17. Source: `git@bitbucket.org:overdrive/datafy_lambda.git`, `main` at `e985575` (2025-03-05). Rules below are taken from code and SQL. Each rule is labelled. A rule that is only a reasonable reading of a name is marked **Inferred**. No rule was added because it would be sensible. Secret values are not repeated. ## Domain language **Documented** in `readme.md`: - SJ Report means Spinning Jenny Report. - A call sheet (also called a schedule) holds rep details and is described as a monthly upload. - Tasks are built per store per user after those files are uploaded. **Inferred** from column names and the issue phrases: a task is a store-level stock or ranging problem that a rep is expected to fix. The repository never defines "fixed" as a business outcome beyond the status strings below. ## Who may do what ### BR-01 — Authenticated users and app_user **Implemented** in `read_edge_function/supabase/migrations/20240627123825_remote_schema.sql`. Policies `Insert own app_user`, `Select app_user`, and `Update own app_user` allow the command when `auth.role() = 'authenticated'`. The policy names say "own". The expressions do not compare `user_id` to `auth.uid()`. `ENABLE ROW LEVEL SECURITY` in that file is only on `mock_data`. **Unknown / Requires Confirmation** whether `app_user` RLS is on in the database this dump came from. ### BR-02 — Authenticated users and task **Implemented** in the same dump. Policies `task_insert`, `task_update`, and `task_delete` allow the command when `auth.role() = 'authenticated'`. There is no `task` select policy in the dump. There is no row owner check. Same RLS uncertainty as BR-01. ### BR-03 — Lambdas use a project key, not an end-user session **Implemented.** - `lambda/datafy` puts the key in the function environment. - `lambda/datafy_new` reads SSM `/supabase/anon`. - Rekognition analyze reads a parameter path named `JwtSecret/ServiceRoleKey`. The Lambdas do not receive a user id and do not filter by customer except where a SQL function argument includes `p_customer_id`. ### BR-04 — Rep signup is an operator script **Implemented** in `file_processing/signup_users.js`. The script signs in as `SUPABASE_USER`, then signs people up. It is not an HTTP route. Password rules, rate limits, and captcha are not in this file. ## Upload and import rules ### BR-05 — Which storage writes enter the SJ queue **Implemented** in `Scripts/Upload trigger.sql`. After insert on `storage.objects`, if `bucket_id` is `uploads` and the first path token is `sjreport`, insert `sjreport_uploads` with `processed` default false. Other folders do not enter that queue through this trigger. ### BR-06 — One upload per minute **Implemented** in both `ReadFiles` handlers. `limit(1)` on rows whose `processed` equals the string `FALSE`. **Inferred:** a backlog drains at one file per minute per stack. Two stacks (`datafy` and `datafyNew`) would each take a row if both are deployed. **Requires Confirmation** that both are not running against the same table. ### BR-07 — Only spreadsheets **Implemented.** File name must end with `.xlsx`. Anything else is "Unsupported file format". ### BR-08 — Header normalisation **Implemented** in the Python importers. Column names are lower-cased and spaces become underscores. Null cells become empty strings. **Implemented** in the Node SJ parsers, with extra renames: `score_%` → `score`, out-of-stock header → `oos_place_order`, seven-day-cover header → `7_days_cover`, SOH-no-sales header → `soh_no_sales`, negative-stock header → `negative_stock`, 100-day header → `no_sales_100`, fixed/not-fixed header → `fixed`, product-ranged-no-stock header → `no_stock`. ### BR-09 — Identity of an imported sheet row **Implemented** in `ReadFiles`. Upsert conflict target is `row`, `week_start_date`, `file`, `sheetname`. `row` is the dataframe index plus 1, not a column from the workbook. The Node SJ parser treats an existing row with the same four fields as already imported and does not update it. ### BR-10 — Week start is the following Monday **Implemented** in both `ReadFiles` handlers. `week_start_date = date + (7 - date.weekday())` days, with Monday as weekday 0. A row dated Monday is stored against the next Monday. Sunday moves one day forward. The Node script `2_parse_sjreport_files.js` does not use this calculation. It stores the date argument, and the call at the bottom of the file passes the current date. ### BR-11 — SPAR store codes **Implemented** only in `lambda/datafy_new/read_files/app.py`. If the string `SPAR` is in `channel`, `store_code` becomes `S` plus the existing code. No other channel is rewritten. ### BR-12 — A bad date still continues **Implemented** in `lambda/datafy_new/read_files/app.py`. An unparseable `date` marks the upload `processed` with error "Invalid date format" and does not stop the sheet loop. A later successful finish clears `error`. **Inferred** that this is accidental. It is what the code does. ### BR-13 — Promo queue is separate **Implemented** in `ReadPromosKaching`. It reads `promo_kaching_uploads`, not `sjreport_uploads`, and does not set `week_start_date`. The insert-error path updates `sjreport_uploads` by the promo row id. **Implemented.** Do not treat that as a confirmed business rule; it needs confirmation. ### BR-14 — KaChing row shape **Implemented** in `Scripts/create KaChing.sql`. Table `kaching` columns: `channel`, `region`, `category`, `brand`, `product`, `variant`, `size`, `challenge_type`, `kaching_challenge_prompt`, `start_date`, `end_date`, `reward`. The file inserts example rows (channels `ALL` and `SUPERSPAR`, rewards written as currency text). Those values are sample data in git, not proof of the live catalogue. `app_user.kaching` is a bigint on the June 2024 `app_user` table. Nothing in the Lambdas updates it. ## Task rules ### BR-15 — Issues become tasks; clean lines do not **Implemented** in `Scripts/get_sjreport_data_pages.sql`. A row is returned when `week_start_date >= target_date` and `issue_for_feedback <> 'NO ISSUE - ITEM SELLING'`. `CreateTasks` sets `task.name` from `issue_for_feedback` and `task.type` to `task`. ### BR-16 — Product match for a task **Implemented** in that SQL function. Left join `product` on `category`, `brand`, `product`, `variant`, and `size`. The task stores `product.id` as `product_id`. A miss leaves `product_id` null. ### BR-17 — Task upsert key **Implemented** in `lambda/datafy_new/create_tasks/app.py`. Conflict columns: `chain`, `store_name`, `product_id`, `week_start_date`, `name`, `sjreport_id`. The June 2024 dump does not declare that unique key. **Unknown** whether the database has it. ### BR-18 — Older stack ignores the file's week **Implemented** in `lambda/datafy/create_tasks/app.py`. The date sent by `ReadFiles` is commented out. The function always requests `2024-12-23`. ### BR-19 — Node task matching, not used for insert **Implemented** in `file_processing/3_parse_task.js`, and the insert is commented out. Match `sjreport` with `ilike` on `store_name` and `channel` against the call sheet's `store_name` and `chain`. Match `store` the same way. The object includes `rep_full_name`, `callsheet_id`, `day`, `week`, and `app_user_mobile` from the call sheet. The Lambda task row does not copy those rep fields. ### BR-20 — Rename of an old issue phrase **Implemented** as a script, not as a trigger, in `Scripts/Update issue for feedback.sql`. `NO ISSUE - IGNORE` on `sjreport.issue_for_feedback` and on `task.name` is updated to `NO ISSUE - ITEM SELLING`. A following statement deletes `task` rows still named `NO ISSUE - IGNORE`. ### BR-21 — Store counts by issue phrase **Implemented** in `Scripts/Function - update_store_issue_counts.sql` (function `update_store_issue_counts_temp`, updates `store_temp`). | `issue_for_feedback` | Count column | |---|---| | `NO ISSUE - ITEM SELLING` | `non_issues` | | `NO SALES - FIX BALANCE/SHOW STOCK ON SHELF` | `soh_no_sales` | | `OOS - PLACE ORDER` | `oos` | | `NEGATIVE STOCK - FIX STOCK BALANCE` | `negative_stock` | | `NO SALES 100 DAYS PLUS - FIX STOCK BALANCE` | `no_sales_100_days` | | `<7 DAYS COVER - SEND FD CATEGORY PHOTO` | `seven_day_cover` | `total_soh` and `active_products` add those counts and add `seven_day_cover` a second time. `availability` is `non_issues / total_soh * 100` when `total_soh > 0`, else 0. The function body sets `max_week_start_date` to the literal `2024-11-11` and comments out the query that would take the max from `sjreport`. `Scripts/update_store_on_task_status_change.sql` uses the same five issue phrases, excluding `NO ISSUE - ITEM SELLING`, and runs when `task.status` changes from `Not Fixed` to `Fixed`. It reads `MAX(week_start_date)` from `sjreport` for `NEW.store_code`. After the count update it adds 1 to `store.non_issues` and recomputes `availability`. The file also creates trigger `task_status_update_trigger` `AFTER UPDATE ON task` for each row, with the same `WHEN` condition, executing that function. Whether the script has been applied is **Unknown / Requires Confirmation**. The Lambda RPC name is `update_store_issue_counts`, not `update_store_issue_counts_temp`. **Unknown** whether those are the same function. ### BR-22 — Customer id stamped on one week **Implemented** as a one-off statement in `Scripts/Update issue for feedback.sql`. `task.customer_id` is set to `1` where `week_start_date` is `2024-12-16`. ## People and stores ### BR-23 — South African mobile for signup **Implemented** in `signup_users.js`. Leading zeros are removed from `rep_cell_number`. The value stored and sent to Auth is `+27` plus the remainder. Email is that phone plus `@datafy.co.za`. First name is the first word of `rep_full_name`. Surname is the second word. Further words are dropped. ### BR-24 — Store file identity **Implemented** in `file_processing/parse_store_files.js`. Before insert into `store`, the script looks for an existing row in `callsheet_test` using the same row key. **Inferred** that the check table is wrong relative to the insert table. It is what the file does. ### BR-25 — Call sheet columns **Implemented** as the `callsheet` table in the June 2024 dump. Region, channel, division, rep name, rep cell, chain, store name, week, Monday through Friday, manager fields, `row`, `file`, `sheetname`, `day`. The active `file_processing` script named for the call sheet does not write this table. ## Images ### BR-26 — A product image is processed once **Implemented** in `lambda/Rekognition/functions/analyze_image/app.py`. Select `product_images` where `processed` is false. After a detect call, insert `product_ml` and set `processed` true. A detect exception returns an empty list and the row is still marked processed. Minimum confidence is 50. Bounding boxes are drawn in green on a copy uploaded to storage bucket `ml`. ### BR-27 — Training crops follow the XML label **Implemented** in `get_training_images/app.py`. Each VOC `object/name` is a folder in `datafy-prod-training`. The file name is `_.jpg`. The source image must be a `.jpg` next to the `.xml`. The function waits up to 60 seconds for that JPEG. ## Reporting functions These are defined in `Scripts/` and are not called by the Python handlers that were read, except `get_sjreport_data_pages`. | Function | Rule in the file | |---|---| | `get_store_brand_totals` | Totals for one store code, week start, and customer id | | `get_store_categories_brand_totals` | Same, filtered by a category list | | `get_store_category_totals` | One store, one category, one week start | | `get_max_week_start_date` | Max week start. File `Script-9.sql` | | `get_max_week_start_date_by_store_codes` | Max week start for a set of store codes. More than one signature is in `Scripts/` | | `get_total_tasks_by_user` | Count of tasks for a user id. `Script-13.sql` | | `process_sjreport_data` | Two versions, `Script-23.sql` and `Script-24.sql` | Which version is installed, if any, is **Unknown / Requires Confirmation**. ## What is not a rule here - No price, tax, or payment rule is enforced in the Lambdas. `task.price` and `kaching.reward` are columns and sample text. - No approval workflow is coded beyond `task.approve` default `1` in the June 2024 table. - No office-hours or timezone conversion is applied. Week-start arithmetic uses the date in the cell and Python's weekday, with no timezone.