# FlutterFlow Setup Guide - Active Users Functions

## Database Functions Setup

### Prerequisites
- Access to Supabase SQL Editor
- Admin or database owner permissions

### Step 1: Create Database Functions

1. **Open Supabase Dashboard**
   - Navigate to your project dashboard
   - Go to SQL Editor

2. **Run `update_user_activity.sql`**
   - Copy the contents of `update_user_activity.sql`
   - Paste into SQL Editor
   - Click "Run" to execute
   - Verify success message

3. **Run `get_active_users.sql`**
   - Copy the contents of `get_active_users.sql`
   - Paste into SQL Editor
   - Click "Run" to execute
   - Verify success message

### Step 2: Verify Functions

Run these test queries in SQL Editor:

```sql
-- Test update_user_activity function
SELECT update_user_activity('your-user-id-here'::UUID, 'app');
SELECT update_user_activity('your-user-id-here'::UUID, 'web');

-- Test get_active_users function
SELECT * FROM get_active_users(15);
```

### Step 3: Enable Realtime (Optional but Recommended)

For real-time updates in FlutterFlow:

1. Go to **Database** → **Replication**
2. Enable replication for `app_user` table:
   - Check the box next to `app_user`
   - This allows real-time subscriptions

3. Enable replication for `user` table:
   - Check the box next to `user`
   - This allows real-time subscriptions

### Step 4: Set Up Activity Tracking

#### For Mobile App (app_user table)

The `app_user` table already has `active_last` column. You need to call `update_user_activity()` periodically:

- On app login
- Every 5 minutes while app is active
- When app comes to foreground

#### For Web Portal (user table)

The `user` table uses `updated_at` for activity tracking. You can:

**Option A: Use existing `updated_at`** (Current implementation)
- Function uses `updated_at` column
- Updates automatically when user data changes

**Option B: Add `active_last` column** (Recommended for better tracking)
```sql
ALTER TABLE "user" ADD COLUMN IF NOT EXISTS active_last TIMESTAMP;
CREATE INDEX IF NOT EXISTS idx_user_active_last ON "user"(active_last);
```

Then update the function to use `active_last` instead of `updated_at`.

### Step 5: Security & Permissions

The functions use `SECURITY DEFINER` which means they run with the privileges of the function creator (typically postgres/admin). 

**Important Security Notes:**
- Functions check for authenticated users via `GRANT EXECUTE TO authenticated`
- Consider adding RLS policies if needed
- Rate limiting should be implemented at the application level

### Step 6: Testing

1. **Test with multiple users:**
   - Login as different users (app and web)
   - Call `update_user_activity()` for each
   - Query `get_active_users()` to verify they appear

2. **Test real-time updates:**
   - Open FlutterFlow app
   - Subscribe to `app_user` table changes
   - Update a user's `active_last` timestamp
   - Verify UI updates automatically

## Troubleshooting

### Function not found error
- Ensure you ran both SQL files completely
- Check function exists: `SELECT proname FROM pg_proc WHERE proname = 'get_active_users';`

### No users returned
- Verify users have `active_last` (app) or `updated_at` (web) within last 15 minutes
- Check for active Supabase sessions: `SELECT * FROM auth.sessions WHERE expires_at > NOW();`
- Verify `archived = 0` for app_user and `active IS NOT NULL` for user table

### Permission denied
- Ensure you're logged in as authenticated user
- Check function grants: `SELECT * FROM information_schema.routine_privileges WHERE routine_name = 'get_active_users';`

## Next Steps

After setting up database functions:
1. Create FlutterFlow Custom Actions (see `flutterflow/custom_actions/`)
2. Create FlutterFlow Widgets (see `flutterflow/widgets/`)
3. Follow integration guide in `flutterflow/widgets/INTEGRATION_GUIDE.md`