# Project plan — datafy-supabase-functions

Evidence date: 2026-10-17.

Source repository: `git@bitbucket.org:overdrive/datafy-supabase-functions.git`.

Inspected revision: `main` at `2a3c241` (2025-12-02, "Add Supabase database functions and documentation"). Parent of that commit is `07f4d67` (same day, "Initial commit"), which adds only `.gitignore`.

This plan describes what the repository contains. It does not decide what the product should become.

There was no `silicon_ark/datafy-supabase-functions/` directory before this document set. The source repository has no `README`. Product intent is stated in the Markdown guides that were committed with the SQL.

## Product

This repository is a manual deployment kit for PostgreSQL functions, triggers, indexes, and row-level security policies used by Datafy on Supabase. It calculates store stock metrics from `task` and `sjreport`, refreshes those metrics when tasks change, reports active users and store scores, isolates customer data, and routes storage uploads into processing tables.

Provenance: **Documented** in the Markdown guides and **Implemented** in the SQL files at the repository root.

It is not an application. There is no runtime, package manifest, migration framework, or CI config in the repository. Scripts are written to be pasted into the Supabase SQL editor (`FLUTTERFLOW_SETUP.md`) or run with psql `\i` paths that do not match this repository's layout.

The guides name two clients that consume the database:

- FlutterFlow mobile app `datafy_ai`
- Web portal `datafy_web`

Those clients are not in this repository. Silicon Ark already has a separate knowledge set at `silicon_ark/datafy_web/`. Whether `datafy_ai` has its own Silicon Ark directory was not part of this inspection. Whether both clients use the same Supabase project as the default image host `https://datafy.appmonitor.co.za` is **Unknown / Requires Confirmation**. The host appears in the category-image fallback inside `process_store_metrics_set_based_fixed.sql` and `process_store_category_data_optimized.sql`.

## Branch reality

Default branch is `main`. `origin/HEAD` points at `main`. No other branches were present on the clone.

| Line | Tip | Date | What it is |
|---|---|---|---|
| `main` | `2a3c241` | 2025-12-02 | All SQL and Markdown (40 files, 5706 insertions) |
| parent | `07f4d67` | 2025-12-02 | `.gitignore` only (Bitbucket default template) |

There is no history of incremental function changes. Every script in the tree arrived in one commit, including scripts that contradict each other. Provenance: **Implemented** (git history).

## Scope in the repository

In scope of what is stored on `main`:

- SQL function definitions for store metrics, a store processing queue, week reports, active users, last activity, store scores, a customer-id helper, and a storage-upload handler.
- Two competing `AFTER UPDATE` trigger designs on `task`, plus a trigger-execution log table.
- RLS enablement and SELECT/INSERT/UPDATE/DELETE policies for tasks, promos, KaChing, stores, and surveys.
- Index-creation scripts for `task`, `sjreport`, and `store_user_link`.
- Diagnostic and verification SQL, and Markdown that records deployment notes from the authors.

Out of scope of this repository, though referenced by the scripts:

- The original `process_store_category_data` function body (callers invoke that name; this repo defines `process_store_category_data_optimized` instead).
- `update_product_store_data`.
- `get_combined_week_report` (the filtered functions say they are based on it).
- The trigger that attaches `files_to_process()` to `storage.objects`, and `sj_api_trigger`.
- Table DDL for `task`, `sjreport`, `store`, `store_user_link`, `store_visibility`, `user`, `app_user`, `customer_user`, and the upload tables. Columns are only visible where the functions read or write them.
- FlutterFlow custom actions and widgets cited by `FLUTTERFLOW_SETUP.md` (`flutterflow/custom_actions/`, `flutterflow/widgets/`). Those paths are not in the tree.
- Any scheduler that calls `process_store_processing_queue()`.
- Proof of what is actually installed on the live database.

## Major components

| Component | Role | Evidence |
|---|---|---|
| Store metrics | Count named task issues and stock-on-hand rows, write `store_user_link` | `process_store_metrics_set_based_fixed.sql`, `process_store_category_data_optimized.sql`, `update_store_metrics.sql` |
| Task triggers | Recalculate metrics after a task update | `task_status_fixed_trigger.sql`, `create_trigger.sql`, `task_update_trigger.sql`, `create_task_update_trigger.sql`, `deploy_task_update_trigger_fix.sql` |
| Processing queue | Batch worker over `store_processing_queue` | `process_store_processing_queue.sql` |
| Week reports | Union of `sjreport` and `task` rows for one store, or for a region and channel | `get_combined_week_report_by_store.sql`, `get_combined_week_report_by_region_channel.sql` |
| Presence | Active users and last activity | `get_active_users.sql`, `update_user_activity.sql`, `get_last_user_activity.sql` |
| Store scores | Visibility (previous week) and current availability | `get_store_scores.sql`, `diagnose_user_stores.sql` |
| Customer isolation | RLS policies keyed by `customer_user` | `create_rls_policies.sql`, `get_user_customer_ids.sql` |
| Upload intake | Deduplicated inserts from the `uploads` bucket | `fix_files_to_process_deduplication.sql` |
| Indexes | Support the metric joins | `create_indexes_for_process_store_category_data.sql`, `create_missing_indexes_only.sql`, `create_large_index_concurrently.sql` |

## Architecture

```text
Supabase Storage (uploads bucket)
  files_to_process()  -->  sjreport_uploads | promo_kaching_uploads
                              (downstream sj_api_trigger is not in this repo)

task UPDATE
  either trg_task_fixed_update_visibility   (status becomes 'fixed')
  or     trg_task_update_process_metrics    (any update; drops the other trigger)
        --> process_store_category_data and/or process_store_metrics_set_based
        --> update_product_store_data          (body not in this repo)
        --> store_user_link
        --> store_visibility                   (column set differs by script)

store_processing_queue
  process_store_processing_queue()  (caller/schedule unknown)
        --> process_store_category_data
        --> update_product_store_data
        --> store_visibility.visibility

Clients (not in this repo)
  get_active_users / update_user_activity
  get_store_scores / get_last_user_activity
  get_combined_week_report_by_store
  get_combined_week_report_by_region_channel
  RLS on task, store, promos, kaching, survey*
```

Which trigger is installed, and which function body is installed for `fn_task_update_process_metrics`, cannot be decided from git. Both designs are in the same commit. See the conflict list below.

## Milestones visible in the files

These are author notes, not a dated project plan. Provenance: **Documented**.

1. A loop-based store-metrics function existed outside this repo. `CHANGES_SUMMARY.md` says the set-based function was corrected to match it. The loop-based source is not here.
2. `process_store_category_data` was reported at about 30 seconds. `PERFORMANCE_OPTIMIZATION_SUMMARY.md` and `process_store_category_data_optimized.sql` describe a rewrite. The optimized object has a different function name from the one callers use.
3. A status-to-Fixed trigger (`trg_task_fixed_update_visibility`) was written, then a later note says it was replaced by `trg_task_update_process_metrics` on any task update. Both scripts remain.
4. That any-update trigger was then changed again to stop calling `process_store_metrics_set_based`, because it overwrote category-data updates (`task_update_trigger.sql` header). `deploy_task_update_trigger_fix.sql` still calls the metrics function. `TASK_UPDATE_TRIGGER_SUMMARY.md` also still says all three functions are called.
5. RLS for customer isolation is dated "January 2025" in `CUSTOMER_DATA_ISOLATION_RLS_GUIDE.md`. The git commit is 2025-12-02. The guide's file paths (`database/functions/...`, FlutterFlow Dart files) do not exist in this tree.
6. Index notes say some indexes were created on a live database and `idx_sjreport_dedup` timed out on a `sjreport` table of 13+ million rows (`index_creation_status.md`). `index_status_summary.md`, in the same commit, still lists those indexes as missing. Live state is **Unknown / Requires Confirmation**.

## Implementation status

| Area | Status in this repository |
|---|---|
| SQL scripts for the functions listed above | Present, single commit |
| Automated deploy, migrations, tests that execute | Not present. `test_*.sql` and `verify_deployment.sql` are manual SQL editor scripts |
| Agreement on which task trigger to install | Conflicting scripts. **Requires Confirmation** |
| `process_store_category_data` (name used by triggers and the queue) | Not defined here. Only `process_store_category_data_optimized` is defined |
| Live Supabase installation | **Unknown / Requires Confirmation** |

## Known dependencies

- PostgreSQL / Supabase (`auth.uid()`, `auth.sessions`, `storage.objects` shape: `bucket_id`, `path_tokens`, `name`, `id`).
- Tables the functions assume already exist: `task`, `sjreport`, `store`, `store_user_link`, `store_visibility`, `store_customer_link`, `store_processing_queue`, `customer_user`, `"user"`, `app_user`, `user_activity`, `promos`, `promos_linked_users`, `kaching`, `kaching_linked_users`, `survey`, `survey_store_link`, `survey_responses`, `sjreport_uploads`, `promo_kaching_uploads`, `trigger_execution_log` (created by `create_trigger_log_table.sql`).
- Functions not defined here: `process_store_category_data`, `update_product_store_data`, and whatever consumes `sjreport_uploads` / `promo_kaching_uploads`.
- Clients: Datafy web and the FlutterFlow app, as named in the guides.

## Conflicts that block a single "current design"

1. **Trigger to install.** `create_trigger.sql` and the bottom of `task_status_fixed_trigger.sql` create `trg_task_fixed_update_visibility` (only when status becomes `fixed`). `create_task_update_trigger.sql` drops that trigger and creates `trg_task_update_process_metrics` with no `WHEN` clause.
2. **Body of `fn_task_update_process_metrics`.** `task_update_trigger.sql` calls `process_store_category_data` and `update_product_store_data`, and uses a temp table as a recursion guard. `deploy_task_update_trigger_fix.sql` calls those two plus `process_store_metrics_set_based`, and uses the custom GUC `task_update_processing`. `TASK_UPDATE_TRIGGER_SUMMARY.md` matches the deploy script's three calls, not the slimmer function file.
3. **Category scope.** `process_store_metrics_set_based` cross-joins every category seen for the customer. `process_store_category_data_optimized` only uses categories present on the paired stores and weeks. `PERFORMANCE_OPTIMIZATION_SUMMARY.md` says logic is unchanged, which disagrees with that narrower category set if the previous function matched the set-based "all customer categories" rule.
4. **`store_visibility` columns.** The Fixed trigger writes `availability_score` and `task_id`. The queue writes `visibility`. `get_store_scores` reads `availability_score`.
5. **Documented paths vs tree.** Guides refer to `database/functions/` and `flutterflow/`. Files sit at the repository root. The FlutterFlow tree is absent.
6. **Index snapshots.** `index_status_summary.md` and `index_creation_status.md` disagree about which indexes already exist.

## Human confirmation still required

- Which trigger and which `fn_task_update_process_metrics` body are approved for the live database.
- Whether `process_store_category_data` in production is the optimized body, an older body, or missing.
- Which `store_visibility` column is canonical.
- Whether the queue has a scheduler, and what invokes `files_to_process`.
- Whether this Supabase project is the one used by `datafy_web` (`https://datafy.appmonitor.co.za`).
- Current index state, including whether `idx_sjreport_dedup` was created.
- Whether `get_user_customer_ids` should be executable by `anon`, and whether the `SECURITY DEFINER` reporting functions may be called for any user id by any authenticated session.