# Customer Data Isolation - RLS Implementation Guide

## Overview

This guide documents the implementation of Row Level Security (RLS) policies to ensure complete data isolation between customer IDs. Users can only access data (tasks, promos, kachings, stores, and surveys) for customer IDs they are linked to via the `customer_user` table.

## Implementation Date

January 2025

## Database Changes

### 1. Helper Function (Optional)

**File:** `database/functions/get_user_customer_ids.sql`

**Note:** This function is optional. The RLS policies use direct subqueries instead. The function is provided for consistency and potential future use, but is not required for the RLS policies to work.

If you want to use it:
- Function name: `get_user_customer_ids(user_uuid UUID)`
- Returns: Table of customer_id (INTEGER)
- Security: SECURITY DEFINER (runs with creator privileges)

**Current Implementation:** RLS policies use direct subqueries: `SELECT customer_id FROM customer_user WHERE user_id = auth.uid()`

### 2. RLS Policies

**File:** `database/functions/create_rls_policies.sql`

#### Tables with RLS Enabled:

**Direct customer_id tables:**
- `task`
- `promos_linked_users`
- `kaching_linked_users`
- `survey_store_link`
- `store_customer_link`

**Linked via junction tables:**
- `store` (via `store_customer_link`)
- `promos` (via `promos_linked_users`)
- `kaching` (via `kaching_linked_users`)
- `survey_responses` (via `survey_store_link`)
- `survey` (via `survey_store_link`)

#### Policy Pattern:

Each table has 4 policies:
1. **SELECT**: Users can view rows where customer_id matches their linked customers
2. **INSERT**: Users can insert rows with customer_id they are linked to
3. **UPDATE**: Users can update rows with customer_id they are linked to
4. **DELETE**: Users can delete rows with customer_id they are linked to

## Application Changes

### FlutterFlow App (datafy_ai)

#### Updated Files:

1. **Task Queries:**
   - `lib/pages/rep/tasks/tasks_list/tasks_list_widget.dart`
   - `lib/pages/rep/tasks/tasks_list_all/tasks_list_all_widget.dart`
   - `lib/pages/rep/tasks/tasks_list_spar/tasks_list_spar_widget.dart`
   
   **Changes:** Added `customer_id = :customer_id` filter to PowerSync SQL queries

2. **Promo Queries:**
   - `lib/pages/rep/promo/promos_list/promos_list_widget.dart`
   - `lib/pages/rep/promo/store_promos/store_promos_widget.dart`
   
   **Changes:** Added customer_id filtering to PowerSync queries and Supabase queries

3. **Kaching Queries:**
   - `lib/pages/rep/dashboard/dashboard/dashboard_widget.dart`
   - `lib/pages/rep/stores/store_items/store_items_widget.dart`
   - `lib/pages/rep/ka_ching/kaching_list/kaching_list_widget.dart`
   - `lib/pages/includes/notification/notifications/notifications_widget.dart`
   
   **Changes:** Added customer_id filtering to PowerSync queries

4. **Survey Queries:**
   - `lib/pages/rep/surveys/form_list/form_list_widget.dart`
   - `lib/pages/rep/stores/store_items/store_items_widget.dart`
   
   **Changes:** Added customer_id filtering to PowerSync queries

### Web Portal (datafy_web)

#### Updated Files:

1. **Task Queries:**
   - `lib/pages/tasks/tasks/tasks_widget.dart`
   
   **Changes:** Updated hardcoded customerId values to use `FFAppState().selectedCustomer`

2. **API Calls:**
   - `lib/backend/api_requests/api_calls.dart`
   
   **Note:** RLS policies automatically filter API calls. Some API calls query base tables (promos, kaching, survey) which are filtered via junction tables by RLS policies.

## Deployment Steps

### Step 1: Deploy Database Functions

1. Connect to your Supabase database
2. Run `database/functions/create_rls_policies.sql` to enable RLS and create policies
   - **Note:** The helper function (`get_user_customer_ids.sql`) is optional and not required. The RLS policies use direct subqueries instead.

### Step 2: Verify RLS Policies

1. In Supabase Dashboard, go to Authentication → Policies
2. Verify RLS is enabled on all listed tables
3. Verify policies are created for each table (SELECT, INSERT, UPDATE, DELETE)

### Step 3: Test Data Isolation

1. Create test users linked to different customers
2. Verify users can only see data for their linked customers
3. Test with users linked to multiple customers
4. Verify cross-customer access is blocked

### Step 4: Deploy Application Changes

1. Pull latest code from repository
2. The FlutterFlow app queries have been updated to include customer_id filtering
3. Test all pages that query tasks, promos, kachings, stores, and surveys

## FlutterFlow Step-by-Step Guide

### For FlutterFlow App (PowerSync Queries)

1. **Open FlutterFlow Project**
   - Navigate to your FlutterFlow project
   - Go to the page with PowerSync queries

2. **Update Task Queries:**
   - Find PowerSync queries with SQL: `SELECT * FROM task WHERE ...`
   - Add `AND customer_id = :customer_id` to WHERE clause
   - Add parameter: `'customer_id': FFAppState().selectedCustomer`

3. **Update Promo Queries:**
   - Find queries: `SELECT * FROM promos_linked_users`
   - Add `WHERE customer_id = :customer_id`
   - Add parameter: `'customer_id': FFAppState().selectedCustomer`

4. **Update Kaching Queries:**
   - Find queries: `SELECT * FROM kaching_linked_users`
   - Add `WHERE customer_id = :customer_id`
   - Add parameter: `'customer_id': FFAppState().selectedCustomer`

5. **Update Survey Queries:**
   - Find queries: `SELECT * FROM survey_store_link`
   - Add `AND customer_id = :customer_id` to WHERE clause
   - Add parameter: `'customer_id': FFAppState().selectedCustomer`

### For Web Portal (Supabase Queries)

1. **Update Supabase Queries:**
   - Find queries using `.queryRows()` or `.querySingleRow()`
   - Add `.eqOrNull('customer_id', FFAppState().selectedCustomer)` to query chain

2. **Update API Calls:**
   - Ensure `customerId` parameter is always passed
   - Use `FFAppState().selectedCustomer` instead of hardcoded values

## Security Considerations

1. **RLS as Primary Security Layer:**
   - RLS policies enforce data isolation at the database level
   - Even if application code has bugs, RLS prevents data leakage

2. **Application-Level Filtering:**
   - Provides defense in depth
   - Improves query performance by filtering at application level
   - Reduces data transfer

3. **User-Customer Linking:**
   - Users must be linked to customers via `customer_user` table
   - Users without customer links will see no data
   - Multiple customers per user are supported

4. **Admin Users:**
   - Verify if admin users need special handling
   - Consider creating separate policies for admin role if needed

## Testing Checklist

- [ ] User linked to single customer sees only that customer's data
- [ ] User linked to multiple customers sees all linked customers' data
- [ ] User not linked to any customer sees no data
- [ ] Cross-customer access attempt is blocked by RLS
- [ ] Tasks are filtered correctly
- [ ] Promos are filtered correctly
- [ ] Kachings are filtered correctly
- [ ] Stores are filtered correctly
- [ ] Surveys are filtered correctly
- [ ] All CRUD operations respect customer boundaries

## Troubleshooting

### Issue: Users see no data after RLS implementation

**Solution:**
1. Verify user is linked to customers in `customer_user` table
2. Check RLS policies are enabled
3. Verify `get_user_customer_ids()` function exists and works
4. Check user's authentication token is valid

### Issue: RLS policies blocking legitimate access

**Solution:**
1. Verify customer_id values match between tables
2. Check `customer_user` table has correct user_id and customer_id
3. Verify function returns correct customer IDs
4. Check for NULL customer_id values

### Issue: Performance degradation

**Solution:**
1. Ensure indexes exist on customer_id columns
2. Consider adding indexes on customer_user table
3. Monitor query performance in Supabase dashboard

## Files Modified

### Database:
- `database/functions/get_user_customer_ids.sql` (new)
- `database/functions/create_rls_policies.sql` (new)

### FlutterFlow App:
- `datafy_ai/lib/pages/rep/tasks/tasks_list/tasks_list_widget.dart`
- `datafy_ai/lib/pages/rep/tasks/tasks_list_all/tasks_list_all_widget.dart`
- `datafy_ai/lib/pages/rep/tasks/tasks_list_spar/tasks_list_spar_widget.dart`
- `datafy_ai/lib/pages/rep/promo/promos_list/promos_list_widget.dart`
- `datafy_ai/lib/pages/rep/promo/store_promos/store_promos_widget.dart`
- `datafy_ai/lib/pages/rep/dashboard/dashboard/dashboard_widget.dart`
- `datafy_ai/lib/pages/rep/stores/store_items/store_items_widget.dart`
- `datafy_ai/lib/pages/rep/ka_ching/kaching_list/kaching_list_widget.dart`
- `datafy_ai/lib/pages/includes/notification/notifications/notifications_widget.dart`
- `datafy_ai/lib/pages/rep/surveys/form_list/form_list_widget.dart`

### Web Portal:
- `datafy_web/lib/pages/tasks/tasks/tasks_widget.dart`

## Notes

- RLS policies are enforced at the database level, providing strong security
- Application-level filtering provides additional security and performance benefits
- All queries should respect customer_id boundaries
- Regular audits should be performed to ensure no data leakage
- Consider adding audit logging for policy violations (future enhancement)