# Business rules — datafy-lambda-new

Evidence date: 2026-10-17. Source: `main` at `9519bea`.

Rules are taken from handlers, templates, changelog, and the knowledge base. Each rule is marked **Documented**, **Implemented**, or **Inferred**. RPC bodies are not in this repository, so rules that those functions enforce in Postgres are **Unknown** here even when the Lambda calls them.

No rule below was invented to fill a gap.

## SJ report import

### BR-001 — One waiting upload per run

**Implemented.** `ReadFiles` selects a single `sjreport_uploads` row with `processed` equal to the string `FALSE` and `times_ran` less than 5.

A Boolean `false` is not what the filter sends. If the column is boolean, whether PostgREST matches this string is **Unknown / Requires Confirmation**.

### BR-002 — Five attempts then stop

**Implemented.** Rows with `times_ran` of 5 or more are not selected. The handler increments `times_ran` during processing (`increment_times_ran`).

### BR-003 — Visible sheets only

**Implemented.** Sheets whose openpyxl `sheet_state` is not `visible` are skipped. If listing worksheets throws, all sheets are loaded.

### BR-004 — Header aliases

**Implemented.** Headers are lowercased and spaces become underscores, then matched to the canonical list with `rapidfuzz` `fuzz.ratio`. A score above 80 replaces the header with the canonical name. Otherwise the original header is kept. Canonical names are the 24 columns in `process_excel_file` (`date` through `customer_id`).

### BR-005 — Date and store code are mandatory

**Implemented** and **Documented** (changelog 2026-02-27). At least one sheet must contain `date` and `store_code` after matching. Otherwise the upload is marked processed, the error is stored, and admin and client email are sent. The file is not retried.

### BR-006 — Week start is a computed Monday offset

**Implemented.** `week_start_date = date + (7 - date.weekday())` days, with Monday as weekday 0. A row dated Monday is stored as the Monday seven days later. A row dated Sunday is stored as the next day.

**Inferred:** the field is meant to be the week the row belongs to. The formula does not use the Monday of the current week. Whether that matches the business calendar is **Unknown / Requires Confirmation**.

### BR-007 — Row identity for SJ upserts

**Implemented.** Conflict target is `row, week_start_date, file, sheetname`. `row` is the 1-based dataframe index. Re-importing the same file, sheet, week, and row replaces the previous upsert.

### BR-008 — Empty cells become empty strings

**Implemented.** Null or NaN cell values are stored as `""`.

### BR-009 — SPAR store-code prefix is not applied

**Implemented** as a comment. The block that set `store_code` to `S` plus the code when channel contained `SPAR` is commented out. It is not a current rule.

### BR-010 — Duplicate products are success

**Implemented** and **Documented** (root readme). `insert_distinct_products` errors with code `23505` or a duplicate-key message are ignored so later RPCs still run. The unique key described in the knowledge base is `(category, brand, product, variant, size)`. That key is **Documented** as the reason for `23505`. The constraint itself is not in this repo.

### BR-011 — A failed import is finished

**Implemented** and **Documented** (changelog 2026-02-27). Missing columns, insert-batch failure, RPC failure, and the top-level exception handler set `processed` true and write `error`. The upload is not left queued.

### BR-012 — Product and customer linking windows

**Implemented** and **Documented.** After insert, the Lambda loops until empty, or until a max-loop guard:

- `batch_update_sjreport_product_id` with batch size 1000 and hours 1
- `update_sjreport_customer_ids` with batch size 1000 and hours back 1
- `update_product_customer_ids` with the same shape

What the hour window means inside Postgres is **Unknown**.

### BR-013 — Statement timeout off, HTTP timeout 300 seconds

**Implemented** and **Documented.** `ReadFiles` sets Postgres `statement_timeout` to 0 via RPC `raw_sql` and raises the PostgREST HTTP read timeout to 300 seconds.

## Task build

### BR-014 — Import starts the bulk path

**Implemented.** `BatchUpdateSjreport` invokes `CreateTasksBulk` and does not call the product-id update function (that call is commented out and `total_updated` remains 0). The success message still says "Total records updated" with that zero.

### BR-015 — Bulk input is the week only

**Implemented.** The invoke payload is `{"week_start_date": week_start_date}`. `record_id` is not forwarded. The upload row is marked processed in this Lambda even if the async invoke is only queued.

### BR-016 — Task conflict key

**Implemented.** Upsert conflict columns are `chain`, `store_name`, `product_id`, `week_start_date`.

### BR-017 — Customer id text cleanup

**Implemented.** When `customer_id` is present it is stringified and a trailing `.0` is removed before upsert.

### BR-018 — Batch files do not overwrite each other

**Implemented** and **Documented** (changelog 2026-02-13). Prefix is `batches/{week_start_date}/{YYYYmmdd_HHMMSS}/`. Older logic that deleted existing objects for the date was removed.

### BR-019 — Staging objects expire

**Implemented.** S3 lifecycle on prefix `batches/` expires objects after 1 day. **Documented** in `CREATE_TASKS_CONTEXT.md` and `NewKnowledgeBase.md` as a way to avoid keeping payloads.

### BR-020 — Console start without batches still finalizes

**Documented** and **Implemented** in the list Lambda's pass-through versus list behaviour. An execution input of only `week_start_date` lists S3. If nothing is there, the Map inserts nothing and Finalize still runs `update_store_issue_counts` and `StartProcessQueue`.

### BR-021 — In-process create remains available

**Documented.** `CreateTasks` is for smaller runs and is expected to time out near 10,000 tasks (15-minute Lambda limit). It is still a deployed function. The upload chain does not call it.

## Queue, backup, and purge

### BR-022 — Queue rebuild deletes every row with id not 0

**Implemented.** `store_processing_queue` delete filter is `neq('id', 0)`, then RPC `populate_store_process_queue`. There is no customer or week filter on the delete.

### BR-023 — Phase errors do not stop the queue function

**Implemented** and **Documented** in `FLOWCHART.md`. Failures in customer-id update, archive, or queue rebuild are emailed and the function continues to the next phase, including the backup invoke.

### BR-024 — Nothing to back up means do not purge

**Implemented.** If `get_old_sjreport_rows_paged` returns no rows, `BackupSjreport` returns HTTP 200 and does not invoke the clear Lambda. The log text says all records already have the maximum `week_start_date`. The same empty-page pattern exists on task backup.

### BR-025 — Backup file location

**Implemented.** CSV upload uses Supabase Storage bucket name `uploads/backups` and file name `sjreport_old_dates_backup_{timestamp}.csv` (and the task equivalent in `backup_tasks`).

### BR-026 — Purge rules live in RPCs

**Implemented** as calls to `clear_old_sjreport_records` and `clear_old_task_records`. How many weeks are kept is **Unknown / Requires Confirmation**.

## Promo and KaChing files

### BR-027 — Only xlsx

**Implemented.** Any other extension marks the upload processed, emails, deletes the storage object, and returns HTTP 400 `Unsupported file format`.

### BR-028 — No required promo columns

**Implemented.** Headers are lowercased. There is no fuzzy match and no required-column check. Columns `id` and `created_at` are removed if present. Other columns are inserted as-is into the table named by `payload.path_tokens[0]`.

### BR-029 — Promo row identity

**Implemented.** Conflict target is `row`, `file`, and `sheetname`. Two call sites spell the conflict string with different spaces after commas (`row,file, sheetname` and `row, file, sheetname`). Whether both match the same Postgres constraint is **Unknown / Requires Confirmation**.

### BR-030 — Failed promo import deletes the file

**Implemented.** On format or insert errors the storage object is deleted so a corrected file can be uploaded again, and `processed` is set true.

### BR-031 — Promo polling schedule is off

**Implemented.** The `rate(1 minute)` event is commented out. Import runs when `/trigger-read-promos-kaching` is called.

## Reports

### BR-032 — Scheduled reports use customer 1

**Implemented.** Both EventBridge inputs set `customer_id` to 1. The weekly input also sets `is_cron` true. The KaChing input does not.

Whether every deployment has a single customer 1 is **Unknown / Requires Confirmation**. The same constant appears in `datafy_web` after login (other repository).

### BR-033 — Weekly email only when the run is a cron

**Implemented** and **Documented.** Email is sent when the event source is `aws.events` or the body contains `is_cron`, `isCron`, `send_email`, or `sendEmail`. A plain API call generates the Excel file and the public URL and does not email.

### BR-034 — Weekly report must bypass anon RLS

**Implemented** and **Documented** (changelog 2026-03-12). The Lambda uses `/supabase/service_role` because `get_combined_week_report_step_by_step` is `SECURITY INVOKER`. Without that key the changelog says the call returns 0 rows. Fallback to the anon key logs a warning and still runs.

### BR-035 — Report pages are 10,000 rows

**Implemented** and **Documented.** `limit_count` is 10000. The loop stops when a page is empty or shorter than the limit.

## Shelf image analysis

### BR-036 — Exact product, not the brand family

**Documented** (changelog 2026-02-20 and 2026-02-23) and **Implemented** in the prompt in `app.py`. A same-brand different product (the changelog examples are Fruit Cocktail versus Peach Slices, and PAKCO versus PARDO) must not be counted. When unsure, the model is told not to count the shelf.

### BR-037 — Size must match when a size is given

**Documented** and **Implemented** in the prompt. Example from the knowledge base: 200G does not count when the target is 100G. If the size is not legible, the item is excluded. Size `N/A` skips the check.

### BR-038 — Reference image wins over text

**Documented** (changelog 2026-02-23). If the reference image and the target text disagree, the prompt says to trust the image.

### BR-039 — Shelf numbering and positions

**Documented** and **Implemented.** Shelf 1 is the top visible row. Positions are `left`, `center-left`, `center`, `center-right`, and `right`. Counts are a single integer, not a range. `POSITIONS` is a JSON array of shelf, position, and facings. The API returns both that array and a string `position`.

The README's short API note still lists only three positions. The changelog is the later document.

### BR-040 — False result clears previous facings

**Documented** (changelog 2026-02-20) and **Implemented** in `update_analysis_result`. Facing count, position, label, and confidence are written on every run, using null when the model says N/A or the result is false, so the UI does not keep the previous run's numbers.

### BR-041 — Images are sent upright

**Documented** and **Implemented.** `ImageOps.exif_transpose` runs before the model sees the bytes. Artifacts store the resized bytes that were sent, not the original file (changelog 2026-02-23).

### BR-042 — Retry eligibility

**Implemented** and **Documented** (README and knowledge base, which supersede the 2026-04-21 changelog text that still listed `product_images` rows).

A task is retried when all of the following hold:

- `fixed_at` is not null
- `fixed_at` is inside the SAST calendar day (or the requested `run_date`)
- GenAI is not complete (`processed_genai` is not true)
- `image_url` is non-empty

Items are `{ "task_id" }` only. Page size default 500.

### BR-043 — Batch eligibility is stricter

**Documented** and **Implemented** in the batch list. Same window as retry, plus a `product_id`, plus `image_url`. The product must have `image_path` or that group fails. Chunk size default 6 shelves. The batch worker does not use the fallback profile (README).

### BR-044 — Test route is not production

**Documented** and **Implemented** by a separate handler. `/analyze-image-test` does not call `analyze_image_with_bedrock`, does not write the database, and does not write artifacts. Default model is Qwen `qwen.qwen3-vl-235b-a22b` unless `model_id` or `BEDROCK_TEST_MODEL_ID` is set. Ids starting with `qwen.` use Converse. Other ids use InvokeModel.

### BR-045 — Request product id does not replace the linked product

**Documented** in changelog 2026-02-23 as an optional `product_id` that overrides a wrong product link. **Not implemented** in `app.py` at this revision. The handler accepts `product_id` on a direct invoke and copies it into the logged body. The product used for the prompt is loaded from `product_images.product_id` or `task.product_id`. `body.get('product_id')` is never read. The route has no authorizer (BR-050).

### BR-046 — Trigger skip when the same shelf file was already analysed

**Implemented.** When `from_trigger` is true and `image_id` is set, the handler returns HTTP 200 with `skipped: true` if `processed_genai` is true and `last_processed_image_path` equals the current shelf path. It does not call Bedrock.

## Access and tenancy

### BR-047 — Lambdas use a shared key, not the caller's user

**Implemented.** Database access is the SSM anon key, except the weekly report which prefers the service role. There is no JWT from the HTTP caller and no role check for Admin, Manager, or Rep.

### BR-048 — Service role is only on the weekly report

**Implemented.** IAM allows `ssm:GetParameter` on `/supabase/service_role` for `CreateCombinedWeeklyReport` only.

### BR-049 — Gmail recipients are configuration

**Implemented.** Admin versus client is the `client_email` flag on `send_notification`, which selects `/gmail/to_emails` or `/gmail/to_emails_client`. Addresses are not in git.

Paths that pass `client_email=True` include the SJ start trigger, SJ import errors, and the weekly report cron email. Store-issue errors use `client_email=False`. The full matrix of every call site was not copied into this file.

### BR-050 — HTTP routes are unauthenticated in source

**Implemented.** Neither API sets an authorizer or API key. CORS origin is `*`. Any network client who can reach the API can POST the routes. Extra protection outside the template is **Unknown**.

### BR-051 — Hardcoded account and function names

**Implemented.** IAM ARNs use account `587594388832` and region `eu-west-1`. `ReadFiles` may invoke only the literal function `datafyNew-BatchUpdateSjreport-utMCtdWkdNo8`. `CreateTasks` may invoke only the literal `datafyNew-StartProcessQueue-r6W4pr83cx2y`. `migration.md` calls these out as breakages on redeploy. They are a deployment constraint, not a retail business rule.

## Training images

### BR-052 — XML arrival crops a sibling JPEG

**Implemented.** The processes function assumes `{stem}.jpg` next to the XML, Pascal VOC `<object><name>` and `<bndbox>`, and retries the image for up to 60 seconds. Output folders are the label text from the XML.

### BR-053 — Rekognition project id is a documented constant

**Documented** in `lambda/Rekognition/README.md`. Project name `Datafy`, project id `1725357683732`, and ARN shapes are stated as requirements and as something `fix_project_arn` enforces in code. Those functions are not resources in `template.yaml`. Treat the id as a rule of that code, not as proof it is deployed.

## Rules not in this repository

The following are called but not defined here. Their business meaning **Requires Confirmation** from the Supabase project:

`insert_distinct_products`, `batch_update_sjreport_product_id`, `update_sjreport_customer_ids`, `update_product_customer_ids`, `get_sjreport_data_pages_v2`, `update_store_issue_counts`, `update_task_customer_ids`, `archive_old_tasks`, `populate_store_process_queue`, `get_old_sjreport_rows_paged`, `clear_old_sjreport_records`, `get_old_task_rows_paged`, `clear_old_task_records`, `get_combined_week_report_step_by_step`, `get_active_kachings_with_activity_daily`, `get_store_data_pages`, `get_issue_counts`, `update_store_issues`, `update_total_counts`, `update_task_status_with_temp`, `raw_sql`.