# Technical documentation — datafy-supabase-functions

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

## What this repository is

A flat directory of PostgreSQL scripts and Markdown notes for a Supabase project used by Datafy. There is no application server, no `package.json`, no `supabase/config.toml`, no migration directory, and no pipeline file.

Language: SQL (`LANGUAGE plpgsql`) plus Markdown. `.gitignore` is the Bitbucket default template (Node, Java, Python, logs, IDE). None of those artefacts are in the tree. Provenance: **Implemented**.

Install method described by the guides: copy a file into the Supabase SQL editor, or psql `\i database/functions/<file>`. The `database/functions/` prefix is not the layout of this repository. Files are at the root. Provenance: **Documented** paths, **Implemented** layout.

## Repository map

| Path | Kind |
|---|---|
| `process_store_metrics_set_based_fixed.sql` | Creates `process_store_metrics_set_based` |
| `process_store_category_data_optimized.sql` | Creates `process_store_category_data_optimized` (not the name callers use) |
| `update_store_metrics.sql` | Wrapper around the set-based function |
| `process_store_processing_queue.sql` | Queue worker function |
| `task_status_fixed_trigger.sql` | `fn_task_fixed_update_visibility` and, at the bottom, the trigger |
| `create_trigger.sql` | Same Fixed trigger again, plus a verification `SELECT` |
| `task_update_trigger.sql` | `fn_task_update_process_metrics` without the set-based call; temp-table guard |
| `deploy_task_update_trigger_fix.sql` | Same function with the set-based call; GUC guard; does not create the trigger |
| `create_task_update_trigger.sql` | Drops the Fixed trigger; creates `trg_task_update_process_metrics` |
| `create_trigger_log_table.sql` | `trigger_execution_log` |
| `view_trigger_logs.sql` | Example queries |
| `get_combined_week_report_by_store.sql` | Report function |
| `get_combined_week_report_by_region_channel.sql` | Report function |
| `get_active_users.sql`, `update_user_activity.sql`, `get_last_user_activity.sql` | Presence |
| `get_store_scores.sql`, `diagnose_user_stores.sql` | Scores |
| `get_user_customer_ids.sql` | Helper, unused by the RLS script |
| `create_rls_policies.sql` | `ENABLE ROW LEVEL SECURITY` and 40 policies |
| `fix_files_to_process_deduplication.sql` | Replaces `public.files_to_process()` |
| `create_indexes_for_process_store_category_data.sql`, `create_missing_indexes_only.sql`, `create_large_index_concurrently.sql`, `check_existing_indexes.sql`, `quick_check_indexes.sql` | Indexes |
| `diagnose_trigger.sql`, `verify_deployment.sql`, `test_trigger.sql`, `test_combined_week_report_functions.sql` | Manual checks |
| `*.md` | Author notes. Several contradict the SQL. See project plan |

## Functions defined here

| Function | Security | Returns | Granted in-file |
|---|---|---|---|
| `process_store_metrics_set_based(text, text, integer, text default '60s')` | invoker | `jsonb` | no |
| `process_store_category_data_optimized(integer, text, text)` | invoker | `jsonb` | no |
| `update_store_metrics(integer, text, text, text default '10s', interval default '30s')` | invoker | `jsonb` | no |
| `process_store_processing_queue()` | invoker | `text[]` | no |
| `fn_task_fixed_update_visibility()` | invoker | `trigger` | no |
| `fn_task_update_process_metrics()` | invoker | `trigger` | no |
| `get_combined_week_report_by_store(text, integer, date)` | invoker | table | no |
| `get_combined_week_report_by_region_channel(text, text, integer, date)` | invoker | table | no |
| `get_active_users(integer default 15, integer default null)` | `SECURITY DEFINER` | table | `authenticated` |
| `update_user_activity(uuid, text default 'app')` | `SECURITY DEFINER` | `boolean` | `authenticated` |
| `get_last_user_activity(uuid, integer default null)` | `SECURITY DEFINER` | table | `authenticated` |
| `get_store_scores(uuid, integer default null)` | `SECURITY DEFINER` | table | `authenticated` |
| `diagnose_user_stores(uuid, integer default null)` | `SECURITY DEFINER` | table | `authenticated` |
| `get_user_customer_ids(uuid)` | `SECURITY DEFINER`, `STABLE` | table of integer | `authenticated`, `anon` |
| `public.files_to_process()` | invoker | `trigger` | no |

Functions called but not created in this repo: `process_store_category_data`, `update_product_store_data`, and (by comment only) `sj_api_trigger` and `get_combined_week_report`.

`get_user_customer_ids` casts `customer_user.customer_id` to integer. Provenance: **Implemented**.

## Triggers defined here

| Trigger | Table | When | Function |
|---|---|---|---|
| `trg_task_fixed_update_visibility` | `task` | `AFTER UPDATE` `FOR EACH ROW` when lower status becomes `fixed` | `fn_task_fixed_update_visibility` |
| `trg_task_update_process_metrics` | `task` | `AFTER UPDATE` `FOR EACH ROW`, no `WHEN` | `fn_task_update_process_metrics` |

`create_task_update_trigger.sql` drops the first before creating the second. Running `create_trigger.sql` afterwards would create the first again and leave both, unless something drops it. Provenance: **Implemented**.

`files_to_process` is a trigger function. No `CREATE TRIGGER` for it is in the repo. The `NEW` row is expected to have `bucket_id`, `path_tokens`, `name`, and `id`, which matches Supabase `storage.objects`. Provenance: **Inferred** from the columns, not from a trigger statement.

## Data the SQL reads and writes

No `CREATE TABLE` except `trigger_execution_log`. Other tables are dependencies. Columns below are only those the scripts reference.

### `trigger_execution_log` (created here)

`id` bigserial, `trigger_name`, `task_id`, `customer_id`, `store_code`, `week_start_date`, `old_status`, `new_status`, `execution_started_at`, `execution_completed_at`, `execution_duration_ms`, `status`, `error_message`, `error_code`, `update_store_metrics_result` jsonb, `update_product_store_data_called` boolean, `availability_found`, `store_visibility_updated` boolean, `note`, `created_at`.

Indexes: task id, `(customer_id, store_code)`, status, `created_at DESC`, week.

The any-update function writes `status = 'processing'` and later `success` or `error`. It does not set `store_visibility_updated`. The column comment does not list `processing`. Provenance: **Implemented**.

### `store_user_link` (updated)

Written: `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`.

Also read: `user_id`, `name`, `customer_id`, `region`, `channel`, `active`, `store_code`.

Match key for the metric update: `(store_code, customer_id)` only.

### `store_visibility` (upserted, two shapes)

Fixed trigger insert columns: `customer_id`, `store_code`, `week_start_date`, `created_at`, `updated_at`, `availability_score`, `task_id`. Conflict target `(customer_id, store_code, week_start_date)`.

Queue insert columns: `customer_id`, `store_code`, `week_start_date`, `created_at`, `visibility`. Same conflict target. It does not set `updated_at` or `availability_score`.

`get_store_scores` reads `availability_score`, `week_start_date`, `updated_at`, `store_code`, `customer_id`.

### `store_processing_queue` (updated)

Read or written: `id`, `status`, `retry_count`, `processed`, `created_at`, `store_code`, `customer_id`, `week_start_date`, `started_at`, `updated_at`, `processed_at`, `completed_at`, `processing_duration_seconds`, `error_message`, `last_retry_at`.

Status values the function writes: `processing`, `completed`, `timeout`, `failed`, `error`. Values it picks: null, `pending`, `timeout`, `error`.

### Other tables referenced

`task` (id, customer_id, store_code, store_name, week_start_date, status, name, category, category_image, created_at, updated_at, note, chain, product_id, article, dc_soh, last_ordered, last_received, store_soh, system_dros, scoring_system, last_sold, brand, product, variant, size, genai_position, genai_facing_count, genai_has_label, genai_confidence), `sjreport` (stock and product columns, plus channel), `product_images` (`task_id`, `uploaded_at`, `image_path`), `store` (`store_code`, `name`, `region`, `channel`), `customer_user` (`user_id`, `customer_id`), `"user"` (`id`, `role`, `full_name`, `first_name`, `last_name`, `email`, `updated_at`, `profile_img`, `active`), `app_user` (`id`, `full_name`, `first_name`, `last_name`, `email`, `role`, `active_last`, `profile_image_url`, `archived`), `user_activity` (`type`, `item_id`, `created_at`, `user_id`, `customer_id`, and for the week report `rep_name`, `gps`), `auth.sessions` (`user_id`, `not_after`), `sjreport_uploads` and `promo_kaching_uploads` (`id`, `payload`), link tables `store_customer_link`, `promos_linked_users`, `kaching_linked_users`, `survey_store_link`, and parents `promos`, `kaching`, `survey`, `survey_responses`.

`week_start_date` is compared both as text and cast to date. The optimized function assumes it can compare `task.week_start_date` to a text parameter with no cast, so that column is treated as text there. The Fixed trigger casts it to date for `store_visibility`. Provenance: **Implemented**. The actual column type is **Unknown** without the table DDL.

Week-report task rows are inner-joined to `product_images` (`pi_latest` in `get_combined_week_report_by_store.sql`). The image must fall in the six days ending on `week_start_date`. `user_activity.rep_name` and `user_activity.gps` are left-joined. Output `channel` for a task row is `task.chain`.

## Metric calculation (shared)

Normalised category: `regexp_replace(lower(trim(category)), '[^a-z0-9]+', '_', 'g')`.

Sjreport dedup: `row_number()` partitioned by customer, store, week, channel, store name, category, product, variant, size, ordered by `created_at DESC, id DESC`, keep `rn = 1`.

`total_soh` = count of those rows for the store and week.

Availability = `0` when `total_soh` is 0, else `((100 / total_soh::float) * (total_soh - not_fixed_count)::float)::bigint`.

Session settings inside the metric functions: `lock_timeout` 5 seconds, `idle_in_transaction_session_timeout` 10 minutes, `statement_timeout` 60 seconds (optimized) or `p_per_store_timeout` (set-based, default 60 seconds).

`category_data` is a JSON array of objects with `category_image`, issue counts, `availability`, `fixed_count`, `not_fixed_count`, `total_issues`. The set-based builder uses `jsonb_agg(... ) FILTER (WHERE norm_category IS NOT NULL)`.

## Queue and trigger timeouts

| Caller | Per-item statement timeout | Batch / budget |
|---|---|---|
| Fixed trigger | 10 seconds around `update_store_metrics` | 30 second argument passed into the wrapper; the wrapper compares it to time elapsed before the inner call, which is near zero |
| Any-update trigger | 10 seconds around category data, and around metrics in the deploy variant | `v_total_budget` is declared as 30 seconds and not used as a stop condition |
| Queue | 10 seconds | Stops picking further rows after 30 seconds; `lock_timeout` 1 second; idle timeout 1 minute; batch size 10; max retries 2 |

Timeouts use `set_config(..., true)` so they are local to the transaction. Provenance: **Implemented**.

## RLS

`create_rls_policies.sql` enables RLS then creates policies. It does not `DROP POLICY IF EXISTS`. It does not `FORCE ROW LEVEL SECURITY`. Table owners and `SECURITY DEFINER` functions bypass RLS unless forced. The reporting functions are `SECURITY DEFINER` and read `"user"`, `app_user`, `store_user_link`, `store_visibility`, `customer_user`, and `user_activity` as the owner. Provenance: **Implemented**.

Policy join keys:

| Table | Predicate |
|---|---|
| Direct customer tables | `customer_id IN (SELECT customer_id FROM customer_user WHERE user_id = auth.uid())` |
| `store` | `store_customer_link.store_code = store.store_code` and that link's customer is allowed |
| `promos` | `promos_linked_users.promo_id = promos.id` |
| `kaching` | `kaching_linked_users.kaching_id = kaching.id` |
| `survey` | `survey_store_link.survey_id = survey.id` |
| `survey_responses` | `survey_store_link` matches `survey_id` and `store_code` |

UPDATE policies set both `USING` and `WITH CHECK`.

`get_user_customer_ids` granted to `anon` can be called without a user session if the function is exposed through the API. It returns every customer id for the uuid passed in. It does not check `auth.uid()`. Provenance: **Implemented**. **Requires Confirmation** that this exposure is intended.

`get_active_users`, `get_store_scores`, `get_last_user_activity`, `update_user_activity`, and `diagnose_user_stores` also take a user id argument and do not compare it to `auth.uid()`. They are granted to `authenticated`. Provenance: **Implemented**.

## Indexes

Scripts create, if not exists:

- `idx_task_customer_store_week` on `task(customer_id, store_code, week_start_date)`
- `idx_task_customer_category` on `task(customer_id, category) WHERE category IS NOT NULL`
- `idx_task_name_status` on `task(name, status)` where `name` is one of the five issue titles
- `idx_sjreport_customer_store_week` on `sjreport(customer_id, store_code, week_start_date)`
- `idx_sjreport_customer_category` on `sjreport(customer_id, category) WHERE category IS NOT NULL`
- `idx_sjreport_dedup` on `sjreport(customer_id, store_code, week_start_date, channel, store_name, category, product, variant, size, created_at DESC, id DESC)`
- `idx_store_user_link_store_customer` on `store_user_link(store_code, customer_id)`

`index_status_summary.md` says different names already existed: `idx_task_cust_store_week`, `idx_task_normcat_expr`, `task_customer_category_idx`, `idx_sj_cust_store_week`, `idx_sj_normcat_expr`, `sj_customer_category_idx`. Those names are not created by these scripts. Provenance: **Documented**.

`create_large_index_concurrently.sql` places `CREATE INDEX CONCURRENTLY` inside `DO $$ ... $$`. PostgreSQL runs a `DO` block inside a transaction, and `CONCURRENTLY` cannot run inside a transaction block. The non-concurrent statement in the same file is commented out. Provenance: **Implemented** file. A successful production build of that index is **Unknown**.

`index_creation_status.md` says `sjreport` had 13+ million rows and index creation timed out. Provenance: **Documented** operational note, not re-measured.

## Deployment and environments

No environment variables, no Supabase project ref, and no branch-based deploy. The only host string in SQL is the default category image URL `https://datafy.appmonitor.co.za/storage/v1/object/public/uploads/R%20(2).png`.

`datafy_web` Silicon Ark notes record a Supabase client pointed at `https://datafy.appmonitor.co.za`. That is a sibling document, not proof this SQL was applied there. Provenance: cross-reference **Documented** in Silicon Ark; sameness is **Unknown / Requires Confirmation**.

`TASK_UPDATE_TRIGGER_SUMMARY.md` says "The trigger has been deployed via migration." There is no migration file in this repository. Provenance: **Documented** claim.

Suggested manual order, if an operator followed the Fixed-trigger guide (`TRIGGER_AND_FUNCTIONS_SUMMARY.md`):

1. `process_store_metrics_set_based_fixed.sql`
2. `update_store_metrics.sql`
3. `task_status_fixed_trigger.sql`
4. `create_trigger.sql` (also contained at the bottom of the trigger file)

The any-update guide instead says:

1. `task_update_trigger.sql` or `deploy_task_update_trigger_fix.sql` (these overwrite each other)
2. `create_task_update_trigger.sql`
3. `verify_deployment.sql`

RLS guide order: run `create_rls_policies.sql`; helper optional. Then the other repositories' query changes.

Index guide order: indexes first, then the optimized function. `PERFORMANCE_OPTIMIZATION_SUMMARY.md` then tells the operator to `SELECT process_store_category_data(...)`, which this repo does not create.

There is no rollback script. The performance guide says to drop the optimized function and recreate "the original", which is not in git.

## Auth

Supabase Auth is assumed:

- RLS uses `auth.uid()`.
- Active users use `auth.sessions`.
- Clients are expected to be logged in before `GRANT ... TO authenticated` functions run.

No JWT, password, or API-key handling is in this repo. `FLUTTERFLOW_SETUP.md` says rate limiting belongs in the application. Provenance: **Documented**.

## Jobs, queues, storage

- Queue table `store_processing_queue` plus `process_store_processing_queue()`. No `pg_cron`, Edge Function, or scheduled workflow is in the repo. Who calls it is **Unknown**.
- Storage intake is `files_to_process()`, expected to run as a trigger on insert of a storage object. The binding statement is **Unknown**.
- Downstream of `sjreport_uploads` / `promo_kaching_uploads` is named `sj_api_trigger` in a comment only.

## Monitoring

`trigger_execution_log` plus `view_trigger_logs.sql` and `RAISE NOTICE` / `RAISE WARNING` in the functions. No metrics exporter, log drain, or dashboard definition. Provenance: **Implemented** SQL logging only.

`verify_deployment.sql` prints notices. It treats any function body containing the text `task_update_processing` as "guard present". Both versions of `fn_task_update_process_metrics` contain that text.

## Security notes visible from the code

- `SECURITY DEFINER` reporting and activity functions do not bind the argument to `auth.uid()`.
- `get_active_users(NULL customer)` returns app and web users across customers to any authenticated caller, including session and profile fields (`email`, `full_name`, `role`, `profile` image).
- `get_user_customer_ids` is granted to `anon`.
- RLS is not forced, so table owners and definer functions are not subject to the new policies.
- `files_to_process` swallows errors.
- The any-update trigger runs heavy SQL on every `task` update, including updates that do not change status, with a 10 second statement timeout. The header of `task_update_trigger.sql` records an infinite-loop incident and adds a guard. `deploy_task_update_trigger_fix.sql` uses a different guard.

These are observations from the scripts, not a pentest. Provenance: **Implemented**.

## Development and testing

No unit-test runner, fixture database, or CI.

Manual scripts:

- `test_trigger.sql` — comments and example `UPDATE` against `task`.
- `test_combined_week_report_functions.sql` — finds sample keys; the function calls are commented out.
- `diagnose_trigger.sql`, `diagnose_user_stores.sql`, `verify_deployment.sql`, `check_existing_indexes.sql`, `quick_check_indexes.sql`.

`CHANGES_SUMMARY.md` lists recommended cases (one store, many stores, mismatched lists, empty input, timeout, category parity with the loop-based function). No results are stored.

Git history cannot show which script was applied last: `07f4d67` is `.gitignore`, `2a3c241` adds everything else on 2025-12-02.

## External systems named by the guides

| Name | Where | In this repo |
|---|---|---|
| Supabase SQL editor and dashboard (Auth policies, replication) | `FLUTTERFLOW_SETUP.md`, RLS guide | Instructions only |
| FlutterFlow app `datafy_ai` | RLS guide, setup guide | File paths listed, source absent |
| Web portal `datafy_web` | RLS guide | Absent here; separate Silicon Ark directory `datafy_web/` |
| PowerSync | RLS guide, as the query layer that needed `customer_id` filters | Absent |
| `https://datafy.appmonitor.co.za` | Default category image | URL literal only |

## Operator diagnostics already written

`TROUBLESHOOTING.md` walks through a missing or disabled `trg_task_fixed_update_visibility`, missing `fn_task_fixed_update_visibility`, status values, `client_min_messages`, and dependency functions `update_store_metrics`, `process_store_metrics_set_based`, `update_product_store_data`. It does not mention the any-update trigger. Use it only when the installed trigger is the Fixed one. Which trigger is installed is **Unknown / Requires Confirmation**.