# Business rules — datafy-supabase-functions

Evidence date: 2026-10-17. Source: `main` at `2a3c241`.

Rules are copied from SQL behaviour or from a guide. Nothing was added to fill gaps. **Inferred** means the code behaves this way and no guide states it as a rule. Conflicting rules are listed together.

## Customer access

**BR-001 — A user may see and change a row only for customers linked in `customer_user`.**

The RLS script allows SELECT, INSERT, UPDATE, and DELETE when `customer_id` is in `SELECT customer_id FROM customer_user WHERE user_id = auth.uid()`, either on the row itself or through the link table named in the policy.

Tables: `task`, `promos_linked_users`, `kaching_linked_users`, `survey_store_link`, `store_customer_link`, `store`, `promos`, `kaching`, `survey`, `survey_responses`.

Provenance: **Documented** (`CUSTOMER_DATA_ISOLATION_RLS_GUIDE.md`) and **Implemented** (`create_rls_policies.sql`).

**BR-002 — A user with no `customer_user` rows sees none of those tables.**

Provenance: **Documented** in the guide ("Users without customer links will see no data") and **Implemented** by the `IN (SELECT ...)` predicate.

**BR-003 — More than one customer per user is allowed.**

The policies use `IN`, not a single customer id. Provenance: **Documented** and **Implemented**.

**BR-004 — The policies do not grant a bypass to Admin or super-admin.**

`get_store_scores` treats Admin differently. The RLS script does not. The guide says to verify whether admins need separate policies. Provenance: absence of an admin policy is **Implemented**. The need for one is **Documented** as an open question, not a rule.

**BR-005 — `get_user_customer_ids` is not what the policies use.**

The guide says the helper is optional and that policies use the subquery. The SQL matches that. Provenance: **Documented** and **Implemented**.

**BR-006 — Survey response access needs both survey and store.**

`survey_responses` policies require a `survey_store_link` row with the same `survey_id`, the same `store_code`, and an allowed `customer_id`. `survey` policies match on `survey_id` only. Provenance: **Implemented**. Not called out as a separate rule in the guide.

## Who may see which stores

**BR-007 — Admin store scores include every `store_user_link` row for the customer filter.**

`v_user_role = 'Admin'`, role taken from `"user"`. If `p_customer_id` is null, customer is not filtered. Provenance: **Documented** in the function comment and **Implemented**.

**BR-008 — A non-Admin role sees only their own active store links.**

Condition: `v_user_role != 'Admin' AND store_user_link.user_id = p_user_id AND active = 1`, plus the optional customer filter. The comment says "Manager users". The code applies to every other non-null role, including a role named `Rep` if that string is stored. Provenance: comment **Documented**; broader predicate **Implemented**. **Requires Confirmation** which roles are supposed to be limited this way.

**BR-009 — A person with no `"user"` row gets no store scores from this function.**

`v_user_role` is null, so neither branch matches. Provenance: **Inferred** from the boolean tests. `app_user` is not consulted.

**BR-010 — Visibility score is the previous week's availability, and the current score is this week's.**

Both numbers are `store_visibility.availability_score`. Missing values become 0. Previous week uses the row with the greatest `updated_at` if duplicates exist. Provenance: **Documented** in the function comment and **Implemented**.

This rule conflicts with the queue, which writes `store_visibility.visibility` and does not write `availability_score`. If only the queue runs, BR-010 still reads a column the queue did not set. Provenance of the conflict: **Implemented** in two files. **Requires Confirmation**.

**BR-011 — The score week starts on Monday, except the Sunday calculation moves forward a day.**

Monday (`DOW = 1`) uses `CURRENT_DATE`. Any other day uses `CURRENT_DATE - (DOW - 1)`. Sunday (`DOW = 0`) therefore becomes the next day. Provenance: **Implemented**. The comment does not mention Sunday. **Requires Confirmation**.

## Store metrics

**BR-012 — Store codes and week dates are paired by list position.**

Both lists are comma-separated. Empty tokens are dropped. Unequal lengths return an error and do not update. Provenance: **Implemented**. `CHANGES_SUMMARY.md` lists mismatched lengths as a test case.

**BR-013 — Stock on hand is a count of deduplicated `sjreport` rows, not a quantity column.**

Latest row wins per `customer_id, store_code, week_start_date, channel, store_name, category, product, variant, size`, ordered by `created_at DESC, id DESC`. `total_soh` and `category_soh` are `COUNT(*)` of those rows. Provenance: **Implemented**. The business meaning "one row is one stocked item" is **Inferred** from the column name `total_soh`.

**BR-014 — Issue counts use these task names, matched exactly.**

| Column | `task.name` |
|---|---|
| `soh_no_sales` | `NO SALES - FIX BALANCE/SHOW STOCK ON SHELF` |
| `oos` | `OOS - PLACE ORDER` |
| `negative_stock` | `NEGATIVE STOCK - FIX STOCK BALANCE` |
| `no_sales_100_days` | `NO SALES 100 DAYS PLUS - FIX STOCK BALANCE` |
| `seven_day_cover` | `<7 DAYS COVER - SEND FD CATEGORY PHOTO` |

Provenance: **Implemented** in both metric functions. The partial index `idx_task_name_status` lists the same five names. Not restated in the Markdown guides.

**BR-015 — Fixed and not-fixed counts are case-sensitive.**

`status = 'Fixed'` and `status = 'Not Fixed'`. Other casings are not counted. Provenance: **Implemented**.

The Fixed trigger treats any casing of `fixed` as the event that starts a recalculation (`lower(status) = 'fixed'`). A status stored as `fixed` would fire the trigger and would not increment `fixed_count`. Provenance: **Implemented** in both places. **Requires Confirmation** which status strings are stored.

**BR-016 — Availability is the share of stock-on-hand rows that are not "Not Fixed" tasks.**

When `total_soh > 0`: `trunc(100 * (total_soh - not_fixed_count) / total_soh)` as bigint. Else 0. The category object uses `category_soh` the same way. Provenance: **Implemented**. The formula can exceed 100 or go negative if `not_fixed_count` is larger than `total_soh`, because `not_fixed_count` counts tasks and `total_soh` counts sjreport rows. No cap is in the SQL. Provenance of the missing cap: **Inferred**.

**BR-017 — `total_issues` is the sum of the five named issue counts.**

It is stored inside each category JSON object. It is not a column on `store_user_link`. Provenance: **Implemented**.

**BR-018 — Category names are grouped by a normalised key.**

`regexp_replace(lower(trim(category)), '[^a-z0-9]+', '_', 'g')`. The display name kept for a key is the one with the latest `created_at`. Provenance: **Implemented**.

**BR-019 — Category image prefers the task, then sjreport, then a fixed default URL.**

Default: `https://datafy.appmonitor.co.za/storage/v1/object/public/uploads/R%20(2).png`. Provenance: **Implemented**. `CHANGES_SUMMARY.md` states the same order.

**BR-020 — One metrics function includes every category the customer has ever recorded. The other includes only categories on the stores and weeks being processed.**

- `process_store_metrics_set_based`: all `task` and `sjreport` categories for `p_customer_id` (**Documented** in `CHANGES_SUMMARY.md` as intentional, **Implemented**).
- `process_store_category_data_optimized`: categories from the paired input only (**Implemented**; the performance guide says "the logic remains unchanged", which conflicts with this narrowing).

**Requires Confirmation** which category set is the rule.

**BR-021 — A metrics update changes every user link for that store and customer.**

`UPDATE store_user_link` matches `store_code` and `customer_id` only. The comment says this is so every user's row gets the same week metrics. `week_start_date` on those rows is overwritten with the processed week. Provenance: **Implemented**. The comment is in the SQL, so the "all users" part is **Documented** in code.

**BR-022 — Metric columns written on `store_user_link`.**

`soh_no_sales`, `oos`, `negative_stock`, `no_sales_100_days`, `seven_day_cover`, `total_soh`, `availability`, `fixed_count`, `not_fixed_count`, `week_start_date`, `category_data`, `updated_at`. Provenance: **Implemented**. Also listed in `TRIGGER_AND_FUNCTIONS_SUMMARY.md`.

## When metrics run

**BR-023 — Version A: metrics run only when status changes to fixed.**

Trigger `WHEN` clause and a second guard inside the function. Then `update_store_metrics`, `update_product_store_data`, then `store_visibility.availability_score` and `task_id`. If availability is null, visibility is not written. `update_store_metrics` failure aborts the task update. `update_product_store_data` failure does not. Provenance: **Documented** (`TRIGGER_AND_FUNCTIONS_SUMMARY.md`, `TROUBLESHOOTING.md`) and **Implemented** (`task_status_fixed_trigger.sql`).

**BR-024 — Version B: metrics run on every task update that has customer, store, and week.**

No status check. `create_task_update_trigger.sql` removes version A. A recursion flag skips re-entry. Per-step failures are logged and the trigger continues, except an error outside those steps re-raises and rolls back the update. Provenance: **Documented** (`TASK_UPDATE_TRIGGER_SUMMARY.md`) and **Implemented** (`task_update_trigger.sql`, `deploy_task_update_trigger_fix.sql`).

**BR-025 — Version B may or may not call the set-based metrics function after category processing.**

`task_update_trigger.sql` does not call it, and its header says that call overwrote category updates. `deploy_task_update_trigger_fix.sql` and `TASK_UPDATE_TRIGGER_SUMMARY.md` still call it, with a 10 second timeout. Provenance: **Documented** in two directions. **Requires Confirmation**.

**BR-026 — The queue processes a store even when no task status changed.**

It selects unprocessed queue rows and calls `process_store_category_data`. It does not call `process_store_metrics_set_based`. It writes `visibility`, not `availability_score`. A row is retried at most twice (`retry_count < 2`). Timeouts become `failed` on the second timeout. Other errors stay `error` and drop out of the pick once `retry_count` reaches 2. Provenance: **Implemented**. No guide states the retry rule in prose.

**BR-027 — Trigger runs are logged, and a failed log write must not block the trigger.**

Provenance: **Documented** (`TRIGGER_LOGGING.md`) and **Implemented** (exception around the insert).

## Week report

**BR-028 — A store week report is the sjreport rows for that store, customer, and week, plus task rows that have a product image in the week window.**

The image window is `uploaded_at::date` from `week_start_date - 6 days` through `week_start_date`, from `product_images`. The latest image per task is used. Tasks with no image in that window are omitted (`INNER JOIN`). Rep name and GPS are the latest matching `user_activity` and may be null. On task rows, the output `channel` is `task.chain` and `issue_for_feedback` is `task.name`. `source_table` is `sjreport` or `task`. Provenance: **Implemented**. The header says this matches `combined_week_report`. That object is not in the repo.

**BR-029 — A region/channel report uses `store.region` and `store.channel`.**

A null parameter does not filter that field. Customer and week are required and are not optional in the signature. Provenance: **Implemented**.

## Presence

**BR-030 — An app user is active when they have a live auth session or `active_last` within the requested minutes, and `archived = 0`.**

Default window is 15 minutes. Provenance: **Documented** in the function header and `FLUTTERFLOW_SETUP.md`, **Implemented**.

**BR-031 — A web user is active when they have a live auth session or `updated_at` within the window, and `active` is not null.**

Any non-null `active` value qualifies. The function does not test `active = true`. Provenance: **Implemented**. The guide says the same column is used. Whether `active` is a timestamp or a flag is **Unknown** (only the null test is visible).

**BR-032 — A live session is `auth.sessions.not_after` null or greater than `now()`.**

Provenance: **Implemented**. The setup guide's troubleshooting query uses `expires_at`, which this function does not read.

**BR-033 — Passing a null customer id to `get_active_users` does not limit by customer.**

Provenance: **Implemented** (`p_customer_id IS NULL OR EXISTS ...`). The header lists the customer link as a third condition; the SQL applies it only when the argument is not null.

**BR-034 — Activity heartbeats are platform-specific, and the caller chooses the user id.**

`app` updates `app_user.active_last` for non-archived rows. `web` updates `"user".updated_at` where `active` is not null. Other platforms return false. The function does not require `p_user_id = auth.uid()`. Provenance: **Implemented**. The guide says the client should call it on login, every five minutes, and on foreground. That cadence is **Documented**, not enforced in SQL.

**BR-035 — The last activity label depends on `user_activity.type`.**

`store` means a store login. `task` means a fixed task. Anything else is a generic activity. Optional customer filter. One row, newest first. Provenance: **Documented** in the function comment and **Implemented**. The comment's "etc." does not name other types.

## Uploads

**BR-036 — Only the `uploads` bucket is processed, and only paths whose first segment is `sjreport`, `promos`, or `kaching`.**

`sjreport` goes to `sjreport_uploads`. `promos` and `kaching` both go to `promo_kaching_uploads`. Provenance: **Implemented**.

**BR-037 — The same bucket and file path must not be inserted twice.**

Match is `payload->>'file_path'` and `payload->>'bucket_name'`. Provenance: **Documented** in the function comment and **Implemented**.

**BR-038 — A processing error must not fail the storage write.**

Provenance: **Documented** in the comment and **Implemented** (`RETURN NEW` after `RAISE WARNING`).

## Indexes and operations (not product rules)

**BR-039 — Deduplicating `sjreport` is allowed to run without `idx_sjreport_dedup`.**

`index_creation_status.md` says the index is optional and the function still works, more slowly. Provenance: **Documented**. Whether the index exists is **Unknown**.

## Rules that must not be treated as decided

| Topic | Conflict |
|---|---|
| When a task update recalculates metrics | BR-023 versus BR-024 |
| Whether set-based metrics run after category data on that trigger | BR-025 |
| Which categories appear on a store | BR-020 |
| Which `store_visibility` column is the score | BR-010 versus the queue write in BR-026 |
| Exact task status spelling | BR-015 versus the trigger's `lower()` |
| Admin bypass of RLS | BR-004 versus BR-007 (different layers) |