# Features — datafy-supabase-functions

Evidence date: 2026-10-17. Source: `main` at `2a3c241` (2025-12-02).

There is no features catalogue in the source repository. Status below is the status of the script in git, not a confirmed live deployment. "Present" means the SQL or guide is in this commit. "Live" is **Unknown** unless a guide claims a deployment, and those claims conflict.

## Current features (scripts present)

### F1 — Set-based store metrics

`process_store_metrics_set_based` in `process_store_metrics_set_based_fixed.sql`.

Writes `store_user_link` metric columns and `category_data` for every user row of the store and customer. Uses all categories known for the customer. Returns JSON.

`update_store_metrics` in `update_store_metrics.sql` is the single-store wrapper used by the Fixed-status trigger.

Provenance: **Implemented**.

### F2 — Optimized category metrics function

`process_store_category_data_optimized` in `process_store_category_data_optimized.sql`.

Same metric columns and availability formula as F1, with normalised categories computed once and week dates compared as text. Categories are limited to the paired stores and weeks. Statement timeout 60 seconds.

Callers (`task_update_trigger.sql`, `deploy_task_update_trigger_fix.sql`, `process_store_processing_queue.sql`) call `process_store_category_data`, which is a different name and is not created by this file.

`PERFORMANCE_OPTIMIZATION_SUMMARY.md` says to deploy this file and then call `process_store_category_data`. Provenance: **Documented** intent, **Implemented** under the `_optimized` name only.

### F3 — Trigger when a task is marked Fixed

`fn_task_fixed_update_visibility` and `trg_task_fixed_update_visibility`.

Files: `task_status_fixed_trigger.sql`, `create_trigger.sql`.

On a real status change to `fixed` (case-insensitive), runs `update_store_metrics`, `update_product_store_data`, and upserts `store_visibility.availability_score` plus `task_id`. Logs to `trigger_execution_log`.

Provenance: **Implemented** in those files. `TASK_UPDATE_TRIGGER_SUMMARY.md` says the trigger was removed (**Documented**, conflicts with the SQL that still creates it).

### F4 — Trigger on any task update

`fn_task_update_process_metrics` and `trg_task_update_process_metrics`.

Files: `task_update_trigger.sql`, `create_task_update_trigger.sql`, `deploy_task_update_trigger_fix.sql`, `verify_deployment.sql`.

`create_task_update_trigger.sql` drops F3's trigger before creating this one.

Two bodies are in the tree (see the PRD, workflow W3). One omits `process_store_metrics_set_based`. One includes it. Both guard against recursion.

Provenance: **Implemented** as alternative scripts. Which one is the product is **Requires Confirmation**.

### F5 — Trigger execution log

`create_trigger_log_table.sql` creates `trigger_execution_log` and indexes on task, customer/store, status, created time, and week.

`view_trigger_logs.sql` is a set of commented and uncommented example queries (recent rows, errors, skipped, performance).

Table comment still says the log is for `trg_task_fixed_update_visibility`. The any-update function writes the same table with a different `trigger_name`. Status comment lists `success`, `error`, `skipped`. The any-update function also writes `processing`.

Provenance: **Implemented**. `TRIGGER_LOGGING.md` describes the Fixed trigger only.

### F6 — Store processing queue

`process_store_processing_queue` in `process_store_processing_queue.sql`.

Batch of 10, two retries, skip locked, 10 second row timeout, 30 second batch budget. Completes, times out, or errors each queue row. Upserts `store_visibility.visibility`.

No scheduler is in the repo. Provenance: **Implemented** function, caller **Unknown**.

### F7 — Combined week report by store

`get_combined_week_report_by_store.sql`.

One store, one customer, one week. `UNION ALL` of `sjreport` rows and task rows that have a `product_images` row in the six days ending on `week_start_date`. Rep name and GPS come from `user_activity`. Task `channel` is `task.chain`.

`test_combined_week_report_functions.sql` finds sample `task` keys and contains commented `SELECT` examples. It is not an automated test.

Provenance: **Implemented**.

### F8 — Combined week report by region and channel

`get_combined_week_report_by_region_channel.sql`.

Store list comes from `store.region` and `store.channel`. Null region or channel does not filter that dimension. Same output columns as F7.

Provenance: **Implemented**.

### F9 — Active users

`get_active_users.sql` and `update_user_activity.sql`.

App activity is `app_user.active_last`. Web activity is `"user".updated_at`. Session presence is `auth.sessions.not_after`. Optional customer filter via `customer_user`.

`FLUTTERFLOW_SETUP.md` is the install and FlutterFlow wiring guide. The FlutterFlow action and widget paths it names are not in the repo.

Provenance: **Implemented** SQL, **Documented** client steps, client code **Unknown** here.

### F10 — Last user activity

`get_last_user_activity.sql`.

One latest `user_activity` row, with store or task wording.

Provenance: **Implemented**. Writer of `user_activity` is **Unknown**.

### F11 — Store scores

`get_store_scores.sql`.

Admin sees all linked-store rows in `store_user_link`. Any other role that is not null sees that user's `active = 1` rows. Visibility is previous week's `availability_score`. Current score is this week's `availability_score`.

`diagnose_user_stores.sql` supports the "no stores" troubleshooting case described in its header.

Provenance: **Implemented**.

### F12 — Customer data isolation (RLS)

`create_rls_policies.sql` and `get_user_customer_ids.sql`.

RLS on ten tables, four commands each, scoped by `customer_user`. Helper function is not used by the policies. Helper is granted to `authenticated` and `anon`.

Guide: `CUSTOMER_DATA_ISOLATION_RLS_GUIDE.md` (dated January 2025 in the file). Lists Dart files in `datafy_ai` and `datafy_web` that are not in this repo.

Provenance: **Implemented** script, **Documented** application changes.

### F13 — Upload deduplication

`files_to_process` in `fix_files_to_process_deduplication.sql`.

`uploads` bucket only. Prefixes `sjreport`, `promos`, `kaching`. Dedup on stored `file_path` plus bucket name.

The `CREATE TRIGGER` that would bind this function is not in the file. Provenance: **Implemented** function, binding **Unknown**.

### F14 — Indexes for the metric queries

| Script | What it creates |
|---|---|
| `create_indexes_for_process_store_category_data.sql` | `idx_task_customer_store_week`, `idx_task_customer_category`, partial `idx_task_name_status`, `idx_sjreport_customer_store_week`, `idx_sjreport_customer_category`, `idx_sjreport_dedup`, `idx_store_user_link_store_customer` |
| `create_missing_indexes_only.sql` | The store-user-link index, the partial task-name index, and `idx_sjreport_dedup` |
| `create_large_index_concurrently.sql` | `idx_sjreport_dedup` with `CONCURRENTLY` inside a `DO` block (see technical notes) |
| `check_existing_indexes.sql`, `quick_check_indexes.sql` | Read-only checks |

`index_status_summary.md` says the customer/store/week indexes already existed under different names (`idx_task_cust_store_week`, `idx_sj_cust_store_week`) and that only the store-user-link index was recommended. `index_creation_status.md` says the store-user-link index and `idx_task_name_status` were created, and `idx_sjreport_dedup` timed out.

Provenance: scripts **Implemented**. Which indexes exist in the database is **Unknown / Requires Confirmation** because the two notes disagree and neither is a schema dump.

### F15 — Diagnostics

| File | Purpose |
|---|---|
| `diagnose_trigger.sql` | Checks the Fixed trigger, its function, and dependencies |
| `diagnose_user_stores.sql` | Store-link counts for a user |
| `TROUBLESHOOTING.md` | Manual steps when the Fixed trigger does not fire |
| `verify_deployment.sql` | Notices whether `fn_task_update_process_metrics` contains a recursion guard |

Provenance: **Implemented** as operator scripts. `TROUBLESHOOTING.md` does not mention `trg_task_update_process_metrics`.

## Completed, as claimed by the authors

These claims are in the Markdown. They are not proven by a migration history.

- Set-based function corrected to match a loop-based function (`CHANGES_SUMMARY.md`). The loop-based function is not in git.
- Nested `DECLARE` bug removed from the set-based function (same file). The broken version is not in git; only the fixed file is.
- Some indexes created on a large `sjreport` table (`index_creation_status.md`). Contradicted by `index_status_summary.md`.
- Any-update trigger "has been tested and verified" (`TASK_UPDATE_TRIGGER_SUMMARY.md`). No test output is stored. The summary also disagrees with `task_update_trigger.sql` about the third function call.

Provenance: **Documented**. Treat as claims, not as release evidence.

## In development / alternate versions in the same commit

Nothing is marked in-progress in git (one content commit). These pairs are unfinished choices, not a branch:

- F3 versus F4.
- `task_update_trigger.sql` versus `deploy_task_update_trigger_fix.sql`.
- `process_store_category_data` (missing) versus `process_store_category_data_optimized`.
- `store_visibility.availability_score` (F3, F11) versus `store_visibility.visibility` (F6).

## Planned, stated but not implemented here

| Item | Where it is stated | What is missing |
|---|---|---|
| `active_last` on `"user"` and a function change to use it | `FLUTTERFLOW_SETUP.md` option B, marked recommended | No `ALTER TABLE` and the function still sets `updated_at` |
| Admin-specific RLS policies | `CUSTOMER_DATA_ISOLATION_RLS_GUIDE.md` "consider creating separate policies" | Not in `create_rls_policies.sql` |
| Audit log of RLS violations | Same guide, "future enhancement" | No table or policy for it |
| FlutterFlow custom actions and widgets for active users | `FLUTTERFLOW_SETUP.md` "Next steps" | `flutterflow/` directory absent |
| Realtime replication on `app_user` and `"user"` | Same guide, step 3, "optional" | Not a SQL file; it is a dashboard instruction |
| `idx_sjreport_dedup` if the concurrent script has not been applied | `index_creation_status.md` | Script exists; success is **Unknown** |
| Rate limiting of the activity functions | `FLUTTERFLOW_SETUP.md` "should be implemented at the application level" | Not in this repo |

Provenance: **Planned** or **Documented** recommendation. Not implemented in this tree.

## Recently changed

Git has no earlier version of these objects. The only change recorded is the addition of the whole tree on 2025-12-02 (`2a3c241`). Provenance: **Implemented**.

Inside that tree, comments describe these as changes relative to objects that are not stored here:

- Upload dedup changed from a time-based check to an exact `file_path` match (`fix_files_to_process_deduplication.sql` header).
- Any-update trigger removed the metrics call, and a deploy script put it back (`task_update_trigger.sql` versus `deploy_task_update_trigger_fix.sql`).
- Category scan in the optimized function was narrowed to paired stores and weeks (`process_store_category_data_optimized.sql` comment on `all_customer_categories`).

## Deprecated or removed

| Item | Evidence | Status |
|---|---|---|
| `trg_task_fixed_update_visibility` | `create_task_update_trigger.sql` drops it; `TASK_UPDATE_TRIGGER_SUMMARY.md` says it was removed | The create script for the old trigger is still in the repo. Removal happens only if that drop script is what was run last |
| Loop-based metrics function | `CHANGES_SUMMARY.md` | Not in this repo. **Unknown** whether it still exists in the database |
| Calling `process_store_metrics_set_based` from the any-update trigger | Comment in `task_update_trigger.sql` | Still present in `deploy_task_update_trigger_fix.sql` and in the summary |

No feature is deleted from git. Provenance: **Documented** as removed, **Implemented** files still create the old objects.

## Not features of this repository

- Flutter screens, PowerSync queries, and API calls named in the RLS guide.
- `update_product_store_data` and `get_combined_week_report`.
- Table creation for the Datafy domain tables (except `trigger_execution_log`).
- A job runner for the queue.