# PRD — datafy-supabase-functions

Evidence date: 2026-10-17. Source: `git@bitbucket.org:overdrive/datafy-supabase-functions.git`, `main` at `2a3c241` (2025-12-02).

There is no prior PRD in Silicon Ark and no standalone product specification in the repository. Requirements below are taken from the SQL and from the Markdown guides committed with it. Each item is labelled Documented, Implemented, Inferred, Planned, or Unknown.

The guides disagree with each other and with some SQL files. Where they disagree, both sides are recorded. The live database was not inspected.

## Product purpose

Datafy's Supabase database keeps retail store execution data separated by customer, turns shelf and stock tasks into store scores, and exposes those scores and user presence to the Datafy app and web portal.

Provenance: **Inferred** from function comments and `CUSTOMER_DATA_ISOLATION_RLS_GUIDE.md`, `TRIGGER_AND_FUNCTIONS_SUMMARY.md`, `FLUTTERFLOW_SETUP.md`. The repository itself is the SQL used to install that behaviour, not the client.

## Users and interactions

People do not use this repository directly. Database roles and client roles that the SQL knows about:

| Actor | What the SQL does for them | Evidence | Provenance |
|---|---|---|---|
| Authenticated Supabase user | May execute the granted reporting and activity functions | `GRANT EXECUTE ... TO authenticated` on `get_active_users`, `get_last_user_activity`, `get_store_scores`, `get_user_customer_ids`, `update_user_activity`, `diagnose_user_stores` | Implemented |
| Anonymous role | May execute `get_user_customer_ids` | `get_user_customer_ids.sql` grants `anon` as well as `authenticated` | Implemented. Whether that grant is intended is **Unknown / Requires Confirmation** |
| Admin | `get_store_scores` returns every `store_user_link` row for the optional customer filter | `get_store_scores.sql`, role read from `"user".role = 'Admin'` | Implemented |
| Anyone whose `"user".role` is not `Admin` | `get_store_scores` returns only `store_user_link` rows for that user with `active = 1` | Same function. The comment says "Manager". The predicate is `v_user_role != 'Admin'` | Implemented behaviour. The comment's "Manager only" wording is **Documented** and narrower than the code |
| User with no `"user"` row | `v_user_role` stays null. In SQL, `NULL != 'Admin'` is not true, and the Admin branch is false, so the function returns no stores | `get_store_scores.sql` | Inferred from the predicate |
| User linked in `customer_user` | RLS policies allow SELECT/INSERT/UPDATE/DELETE of rows for those `customer_id` values | `create_rls_policies.sql` | Implemented in the script. Whether the script has been applied is **Unknown** |
| User with no `customer_user` rows | The same policies match nothing, so those tables return no rows | Guide section "User-Customer Linking"; policies use `customer_id IN (SELECT ...)` | Documented and Implemented in the script |
| Reps and portal users | Named as consumers of task, promo, KaChing, and survey screens in the FlutterFlow and web apps | `CUSTOMER_DATA_ISOLATION_RLS_GUIDE.md` | Documented. The screens are not in this repository |
| Database owner / SQL editor operator | Installs functions by running the scripts | `FLUTTERFLOW_SETUP.md` prerequisites | Documented |

`get_store_scores` does not read `app_user.role`. A mobile user who exists only in `app_user` is treated as the null-role case above. Provenance: **Implemented**.

## Main workflows

### W1 — Recalculate store metrics for paired stores and weeks

Trigger: a caller executes `process_store_metrics_set_based` or `process_store_category_data_optimized`.

Inputs:

- Customer id (integer).
- Comma-separated store codes.
- Comma-separated week-start dates, paired by position with the store codes.
- For the set-based metrics function only, a per-store statement timeout, default `60s`.

Implemented behaviour:

1. Split both strings on commas, trim, drop empty tokens, and pair them by ordinal.
2. If the pair count is zero, return JSON `{"error":"No store_codes / week_start_dates provided or parsing resulted in zero items"}`.
3. If the two lists have different lengths, return JSON `{"error":"Mismatched input array lengths: stores=… weeks=…"}`.
4. Deduplicate `sjreport` to the latest row per `customer_id, store_code, week_start_date, channel, store_name, category, product, variant, size` (`created_at DESC, id DESC`).
5. `total_soh` is the count of those latest rows for the store and week. `category_soh` is the same count per normalised category.
6. Count `task` rows for the same customer, store, and week by exact task `name` and by exact status `Fixed` / `Not Fixed` (see business rules).
7. Availability, when `total_soh > 0`, is `100 * (total_soh - not_fixed_count) / total_soh`, truncated to bigint. Otherwise availability is 0. The same formula is used per category with `category_soh`.
8. Category image is the latest task image for that category, else the latest `sjreport` image, else `https://datafy.appmonitor.co.za/storage/v1/object/public/uploads/R%20(2).png`.
9. Update every `store_user_link` row with that `store_code` and `customer_id` (all users linked to the store). Set the metric columns, `week_start_date`, `category_data`, and `updated_at`.
10. Return a JSON array of store objects including `rows_updated`.

Category set differs by function:

- `process_store_metrics_set_based` cross-joins every distinct normalised category for the customer from `task` and `sjreport`, not only the stores being processed (`CHANGES_SUMMARY.md` calls this the match to the old loop-based function).
- `process_store_category_data_optimized` only uses categories that appear on the paired stores and weeks.

Provenance: **Implemented**. The original loop-based function is not in the repository (**Unknown** beyond the summary).

`update_store_metrics` wraps a single store and week into the set-based function. It checks a total budget (default 30 seconds) before the call. That check uses a timestamp taken at the start of the wrapper, so it does not measure the inner call. On any exception it returns a JSON error object and does not raise. Provenance: **Implemented** (`update_store_metrics.sql`).

### W2 — Task status becomes Fixed

Trigger: `trg_task_fixed_update_visibility` after `UPDATE` on `task`, when `lower(NEW.status) = 'fixed'` and the lower-cased status actually changed.

Implemented in `fn_task_fixed_update_visibility`:

1. Insert a `trigger_execution_log` row. A logging failure does not stop the trigger.
2. Skip (log status `skipped`) when the operation is not `UPDATE`, the new status is not `fixed`, or the status did not change.
3. Call `update_store_metrics` with a 10 second statement timeout and a 30 second budget argument. If that call raises, log `error` and re-raise, which rolls back the task update.
4. Call `update_product_store_data(customer_id, store_code, week_start_date)`. A failure is logged and execution continues. The function body is not in this repository.
5. Read `store_user_link.availability` for the customer, store, and week. If it is null, log `skipped` and return without writing visibility.
6. Insert or update `store_visibility` on `(customer_id, store_code, week_start_date)`, setting `availability_score` and `task_id`. A failure is logged and re-raised.

Provenance: **Implemented** in `task_status_fixed_trigger.sql` and `create_trigger.sql`. `TASK_UPDATE_TRIGGER_SUMMARY.md` says this trigger was removed. Whether production still has it is **Unknown / Requires Confirmation**.

### W3 — Any task update recalculates the store

Trigger: `trg_task_update_process_metrics` after every `UPDATE` on `task`. `create_task_update_trigger.sql` drops `trg_task_fixed_update_visibility` first.

Two function bodies exist for `fn_task_update_process_metrics`.

Shared behaviour:

1. If a recursion flag is already set, return `NEW` and do no work.
2. If `customer_id`, `store_code`, or `week_start_date` is null, clear the flag and return.
3. Log to `trigger_execution_log` under trigger name `trg_task_update_process_metrics`.
4. Call `process_store_category_data(customer_id, store_code, week_start_date text)` with a 10 second statement timeout. Failure is logged; the trigger continues.
5. Call `update_product_store_data` with the same three arguments. Failure is logged; the trigger continues.
6. Mark the log `success` unless a later uncaught error sets `error` and re-raises.

Differences:

| | `task_update_trigger.sql` | `deploy_task_update_trigger_fix.sql` |
|---|---|---|
| Recursion guard | Temp table `pg_temp.task_update_processing` keyed by backend pid | Custom setting `task_update_processing` |
| Third call | Not called. Header says `process_store_metrics_set_based` was removed so it would not overwrite category data | Calls `process_store_metrics_set_based` with a 10 second timeout |
| Summary doc | Disagrees with this file | Matches `TASK_UPDATE_TRIGGER_SUMMARY.md` |

`process_store_category_data` is not defined in this repository. Only `process_store_category_data_optimized` is. Provenance: **Implemented** scripts, **Unknown** which body is installed.

`verify_deployment.sql` checks that the function text contains `task_update_processing`. Both bodies contain that string, so the check does not tell the two versions apart. Provenance: **Implemented**.

### W4 — Drain the store processing queue

Trigger: something calls `process_store_processing_queue()`. No schedule, cron, or edge function is in the repository. Provenance of the caller: **Unknown**.

Implemented behaviour (`process_store_processing_queue.sql`):

1. Lock timeout 1 second, idle-in-transaction timeout 1 minute, statement timeout cleared, then 10 seconds per row.
2. Pick up to 10 rows from `store_processing_queue` where `processed = false` and status is null, `pending`, or (`timeout` or `error` with `retry_count < 2`). Pending rows come before retries. `FOR UPDATE SKIP LOCKED`.
3. Mark picked rows `processing`.
4. Stop the batch when 30 seconds have elapsed since the function started.
5. For each row, call `process_store_category_data`, then `update_product_store_data`.
6. Read `store_user_link.availability` for the store and customer (no week filter). If present, upsert `store_visibility.visibility` on `(customer_id, store_code, week_start_date)`. This is not the `availability_score` column written by W2.
7. Mark the queue row `completed`.
8. On `query_canceled`, set `timeout` and increment `retry_count`. When the new count reaches 2, set `failed`.
9. On any other error, set `error`, store `SQLERRM`, and increment `retry_count`. The row stays `error` rather than `failed` once retries are exhausted, and the pick rule then ignores it.

Returns a `text[]` of per-row messages. Provenance: **Implemented**.

### W5 — Combined week report

Trigger: a SQL caller (client not identified in this repo) executes one of:

- `get_combined_week_report_by_store(store_code, customer_id, week_start_date date)`
- `get_combined_week_report_by_region_channel(region, channel, customer_id, week_start_date date)`

Implemented behaviour:

1. The region/channel function first selects `store.store_code` where region and channel match. A null region or null channel skips that filter.
2. Both functions keep `task` rows for the customer and week (and store, or the filtered store list).
3. They return `sjreport` rows for those stores and week, `UNION ALL` with `task` rows.
4. A task is included only when `product_images` has a row for that `task_id` whose `uploaded_at` date falls in the six days up to and including `week_start_date`. The latest image in that window is kept (`DISTINCT ON (task_id)` ordered by `uploaded_at DESC`). `image_url` is `product_images.image_path`.
5. Rep name and GPS come from the latest `user_activity` row whose `item_id` equals `task.id::text` and whose `customer_id` matches. That join is a left join, so a task with a photo and no activity row is still returned.
6. For task rows, `channel` is `task.chain`, and `issue_for_feedback` is `task.name`. Each output row has `source_table` of `sjreport` or `task`, plus product, stock, and genai fields (`genai_position`, `genai_facing_count`, `genai_has_label`, `genai_confidence`).

The file headers say the shape matches `get_combined_week_report`, which is not in this repository. Provenance: **Implemented** for the two filtered functions. The base report is **Unknown**. No `GRANT` is in these two files.

### W6 — Show who is active

Trigger: an authenticated caller executes `get_active_users(active_minutes default 15, customer_id default null)` and, separately, clients are told to call `update_user_activity(user_id, platform)`.

`update_user_activity`:

- Platform `app`: set `app_user.active_last = now()` where `id` matches and `archived = 0`. Return whether a row was updated.
- Platform `web`: set `"user".updated_at = now()` where `id` matches and `active IS NOT NULL`. Return whether a row was updated.
- Any other platform: return false.
- `SECURITY DEFINER`. Granted to `authenticated`. The function does not check that `p_user_id` is the caller.

`get_active_users` returns users who have an `auth.sessions` row with `not_after` null or in the future, or whose activity timestamp is inside the window:

- App: `app_user`, `archived = 0`, activity column `active_last`, platform `app`, image `profile_image_url`.
- Web: `"user"`, `active IS NOT NULL`, activity column `updated_at`, platform `web`, image `profile_img`.
- Optional filter: a `customer_user` row for `p_customer_id`. Null customer id returns users for every customer.
- If the same id is in both tables, `COALESCE` prefers the app row, so `platform` is `app`.
- Order: currently logged-in first, then most recently active.

`FLUTTERFLOW_SETUP.md` says to call the update on login, every 5 minutes while the app is open, and when the app returns to the foreground. It also says web currently uses `updated_at`, and recommends adding `active_last` on `"user"`. That column change is not in `update_user_activity.sql`. Provenance: call sites **Documented** as a setup guide; function behaviour **Implemented**. The FlutterFlow actions the guide points at are not in this repo.

Header comment on `get_active_users` says sessions are active when `not_after` is null or in the future. `FLUTTERFLOW_SETUP.md` troubleshooting mentions `expires_at`. The function uses `not_after` only. Provenance: **Implemented** vs **Documented** discrepancy.

### W7 — Last thing a user did

Trigger: `get_last_user_activity(user_id, customer_id default null)`.

Returns one row from `user_activity` for that user (and customer, when provided), latest `created_at`:

- Type `store`: description `Logged into store: {store.name or item_id}`, store code is `item_id`.
- Type `task`: description `Fixed task: {task.name or item_id}`, joined where `task.id::text = item_id`.
- Any other type: `Activity: {item_id}`.

`SECURITY DEFINER`. No check that the caller is `p_user_id`. Provenance: **Implemented**. Who writes `user_activity` is **Unknown** (not in this repo).

### W8 — Store score list

Trigger: `get_store_scores(user_id, customer_id default null)`.

1. Read `"user".role` for `p_user_id`.
2. Current week start: if `EXTRACT(DOW FROM CURRENT_DATE) = 1` (Monday), use today; otherwise `CURRENT_DATE - (DOW - 1)`. The function assigns `DATE_TRUNC('week', CURRENT_DATE)` first and then overwrites it. On Sunday, `DOW` is 0, so `DOW - 1` is -1 and the date moves forward one day. Provenance: **Implemented**. Whether Sunday is supposed to belong to the following Monday is **Unknown / Requires Confirmation**.
3. Previous week start is seven days before that date.
4. Admin: all `store_user_link` rows, optionally filtered by customer. Other non-null roles: rows for that `user_id` with `active = 1`.
5. `current_availability_score` is `store_visibility.availability_score` for the current week, else 0.
6. `visibility_score` is the previous week's `availability_score`. If several rows exist, the latest `updated_at` wins. Missing score is 0.
7. Sort by current availability descending, then store name.

`diagnose_user_stores` returns counts of linked stores, active stores, and related diagnostics for the store-score widget. Provenance: **Implemented**. The widget is not in this repo.

### W9 — Isolate rows by customer

Trigger: an operator runs `create_rls_policies.sql`.

The script enables RLS and creates four policies (SELECT, INSERT, UPDATE, DELETE) on:

- Direct `customer_id`: `task`, `promos_linked_users`, `kaching_linked_users`, `survey_store_link`, `store_customer_link`.
- Via a link table: `store` through `store_customer_link.store_code`; `promos` through `promos_linked_users.promo_id`; `kaching` through `kaching_linked_users.kaching_id`; `survey` through `survey_store_link.survey_id`; `survey_responses` through `survey_store_link` matching both `survey_id` and `store_code`.

The allowed customer ids are `SELECT customer_id FROM customer_user WHERE user_id = auth.uid()`. The script does not call `get_user_customer_ids`, matching the guide's statement that the helper is optional.

Policies are created with `CREATE POLICY` and no preceding `DROP POLICY`. A second run fails if the policy names already exist. Provenance: **Implemented**.

The guide's testing checklist (single customer, multiple customers, no link, cross-customer blocked, CRUD on each area) is unchecked in the file. Provenance: **Documented** as a checklist, not as evidence the tests passed.

Application query changes listed in the guide (PowerSync filters, `FFAppState().selectedCustomer`) are **Documented** and are not in this repository.

### W10 — Intake an uploaded file

Trigger: `files_to_process()` is a trigger function (`RETURNS trigger`) but this file does not create the trigger. Attachment point is **Unknown / Requires Confirmation**.

Implemented behaviour:

1. If `bucket_id` or `path_tokens` is null, return `NEW`.
2. If `bucket_id` is not `uploads`, return `NEW`.
3. First path segment `sjreport`: insert into `sjreport_uploads` unless a row already has the same `payload.file_path` and `payload.bucket_name`.
4. First path segment `promos` or `kaching`: same check, insert into `promo_kaching_uploads`.
5. Payload keys: `bucket_name`, `file_name`, `path_tokens`, `storage_id`, `file_path`.
6. Any exception raises a warning and returns `NEW`, so the storage write is not failed by this function.

The comment says this stops `sj_api_trigger` firing twice. That trigger is not in the repository. Provenance: **Documented** intent, **Unknown** implementation of the downstream trigger.

## Integrations

| System | Direction | What is exchanged | Auth in this repo | Failure |
|---|---|---|---|---|
| Supabase Auth | Read | `auth.uid()` in RLS; `auth.sessions.not_after` in `get_active_users` | Session of the caller; reporting functions are `SECURITY DEFINER` | No session: RLS `auth.uid()` is null and policies match nothing. Functions granted only to `authenticated` are not executable by `anon`, except `get_user_customer_ids` |
| Supabase Storage | In (trigger on `NEW` storage row, attachment not in repo) | `uploads` objects whose first path token is `sjreport`, `promos`, or `kaching` | Trigger owner, not specified | Logged as a warning; storage operation continues |
| Datafy clients (`datafy_ai`, `datafy_web`) | They call these functions and tables | Customer id, user id, store and week filters | Not defined here | **Unknown** |
| `update_product_store_data` | Called by triggers and the queue | customer id, store code, week | Not defined here | Fixed trigger and queue swallow the error. The any-update trigger also swallows it |
| Image host `datafy.appmonitor.co.za` | Functions store a public URL as the default category image | No request is made by the SQL | None | If the URL 404s, the SQL still stores it |

## Constraints

- Store and week lists must be the same length and are paired by position, not by name.
- Task issue names and the statuses `Fixed` and `Not Fixed` are exact, case-sensitive strings in the metric counts. The Fixed trigger's guard uses `lower(status) = 'fixed'`.
- Metric updates rewrite `store_user_link` for every user linked to that store and customer, and overwrite `week_start_date` on those rows.
- Statement timeouts are 60 seconds inside `process_store_category_data_optimized`, the `p_per_store_timeout` argument (default 60 seconds) inside `process_store_metrics_set_based`, and 10 seconds per row in both triggers and the queue. The queue also stops the batch at 30 seconds.
- `sjreport` dedup index is described as optional because creation timed out on a table of 13+ million rows (`index_creation_status.md`). That live fact cannot be re-checked from git.
- `create_large_index_concurrently.sql` runs `CREATE INDEX CONCURRENTLY` inside a `DO` block. PostgreSQL rejects `CONCURRENTLY` inside a transaction block. Provenance: **Implemented** script. Whether anyone ran the uncommented statement outside a transaction is **Unknown**.

## Requirements that are assumptions

- That `process_store_category_data` in production has the same calculations as `process_store_category_data_optimized`. The names differ, and the optimized function narrows the category set. **Requires Confirmation**.
- That one of the two task triggers is the approved product behaviour. **Requires Confirmation**.
- That `store_visibility.availability_score` and `store_visibility.visibility` are the same business measure. The writers do not use the same column. **Requires Confirmation**.
- That `get_active_users` and `get_store_scores` are only called by the signed-in user for their own id. The SQL does not enforce that. **Requires Confirmation**.
- That the January 2025 RLS guide's FlutterFlow and web edits were merged in those other repositories. **Unknown** from this repo.

## Error conditions the SQL actually returns or logs

| Condition | Behaviour | Provenance |
|---|---|---|
| Empty or mismatched store/week lists | JSON `error` object, no update | Implemented |
| Statement timeout inside set-based metrics | Exception handler returns JSON with `query_canceled` or the SQL message | Implemented in `process_store_metrics_set_based_fixed.sql` exception block |
| No `store_user_link` row matched | Optimized function raises a warning and still returns JSON; `rows_updated` is 0 | Implemented |
| Missing customer, store, or week on an any-update trigger | Trigger returns without calling the metric functions | Implemented |
| Null availability before a visibility write | Fixed trigger skips; queue skips the upsert and still marks the queue row completed | Implemented |
| Logging insert fails | Trigger continues | Implemented |
| `files_to_process` exception | Warning, storage row kept | Implemented |
| Duplicate upload path | No second insert | Implemented |