> ## Documentation Index
> Fetch the complete documentation index at: https://docs.adhdsimple.co.uk/llms.txt
> Use this file to discover all available pages before exploring further.

# Status-Based Email Notification Services

> Automated email notifications for status changes in patient app requests and prescription orders

## Overview

This system provides automated email notifications for status changes in patient app requests and prescription orders. It integrates with SendGrid using dynamic templates and includes step-based notifications for different stages of the process.

## Architecture

### Services Created

1. **`request-status-notifications`** - Handles patient app request status changes
2. **`prescription-order-status-notifications`** - Handles prescription order status changes

### Database Triggers

* **`request_status_notification_trigger`** - Automatically triggers notifications when `patient_app_requests` status changes
* **`prescription_order_status_notification_trigger`** - Automatically triggers notifications when `prescription_orders` status changes

## Email Templates

### Request Template: `d-5c933e93f5f246898b90dce3301cadab`

Used for patient app request notifications with 4-step progression:

* **Step 1**: Requested
* **Step 2**: Prescription Approved
* **Step 3**: Dispensed
* **Step 4**: Delivered

### Prescription Template: `d-e5525d6f7afa49029fed564a54854b9a`

Used for prescription order notifications with 4-step progression:

* **Step 1**: Prescription Approved
* **Step 2**: Details Confirmed
* **Step 3**: Dispensed
* **Step 4**: Delivered

## Email Notifications (4-Step Process)

The system sends emails for **4 specific events only** per template, as shown in the email template screenshots. Other database events are triggered but ignored by the notification functions.

### Patient App Requests - 4 Email Events

| Database Trigger                              | Event                  | Template | Step | Description                                                          |
| --------------------------------------------- | ---------------------- | -------- | ---- | -------------------------------------------------------------------- |
| `status` → `submitted`                        | `submitted`            | Request  | 1    | **Requested** - Initial request submission                           |
| `clinical_decision` → `prescription_created`  | `prescription_created` | Request  | 2    | **Prescription Approved** - Doctor approves and creates prescription |
| `payment_status` → `paid`                     | `payment_confirmed`    | Request  | 3    | **Dispensed** - Payment confirmed, being prepared                    |
| `status` → `delivered` (or from prescription) | `delivered`            | Request  | 4    | **Delivered** - Order completed and delivered                        |

### Prescription Orders - 4 Email Events

| Database Trigger             | Event             | Template     | Step | Description                                             |
| ---------------------------- | ----------------- | ------------ | ---- | ------------------------------------------------------- |
| `status` → `prescribed`      | `prescribed`      | Prescription | 1    | **Prescription Approved** - Doctor creates prescription |
| `status` → `being_dispensed` | `being_dispensed` | Prescription | 2    | **Details Confirmed** - Pharmacy processing             |
| `status` → `dispatched`      | `dispatched`      | Prescription | 3    | **Dispensed** - Ready for delivery/pickup               |
| `status` → `delivered`       | `delivered`       | Prescription | 4    | **Delivered** - Successfully delivered                  |

### Ignored Events

The following database events **trigger the functions but do not send emails**:

* Request: `approved`, `rejected`, `completed`, `cancelled`, `payment_failed`
* Prescription: `payment_confirmed`, `pickup`, `awaiting_courier`, `ready_for_pickup`, `cancelled`

## SendGrid Template Data Structure

Both templates receive the following dynamic data:

```json theme={null}
{
  "patient_name": "Cameron",
  "step": "1",
  "clinician_name": "Dr. Harry Katsu"
}
```

Note: The system dynamically fetches real clinician information from the `user_profiles` table based on the patient's associated clinic or prescription prescriber.

## Patient Notification Preferences

The system respects patient notification preferences:

* `email_enabled` in `patient_notification_preferences` table (defaults to enabled if no preference record exists)
* Patient email addresses are fetched from the `patients` table
* Clinician information is dynamically fetched from the `user_profiles` table using:
  * Prescription's `prescriber_id` (for prescription notifications)
  * Request's linked prescription's `prescriber_id` (for request notifications)
  * Fallback to clinic-based lookup if needed

## Testing the System

### 1. Direct Function Testing

Test the functions directly by calling them with the Supabase client:

```javascript theme={null}
// Test request status notifications (4 events that send emails)
const { data, error } = await supabase.functions.invoke(
  "request-status-notifications",
  {
    body: {
      requestId: "uuid-of-request",
      event: "submitted", // or: prescription_created, payment_confirmed, delivered
    },
  }
);

// Test prescription order status notifications (4 events that send emails)
const { data, error } = await supabase.functions.invoke(
  "prescription-order-status-notifications",
  {
    body: {
      prescriptionOrderId: "uuid-of-prescription",
      event: "prescribed", // or: being_dispensed, dispatched, delivered
    },
  }
);
```

### 2. Database Trigger Testing

Test the automatic triggers by updating status in the database:

```sql theme={null}
-- Test patient app request notifications (4-step email flow)
UPDATE patient_app_requests SET status = 'submitted' WHERE id = 'your-request-id';
UPDATE patient_app_requests SET clinical_decision = 'prescription_created' WHERE id = 'your-request-id';
UPDATE patient_app_requests SET payment_status = 'paid' WHERE id = 'your-request-id';
UPDATE patient_app_requests SET status = 'delivered' WHERE id = 'your-request-id';

-- Test prescription order notifications (4-step email flow)
UPDATE prescription_orders SET status = 'prescribed' WHERE id = 'your-prescription-id';
UPDATE prescription_orders SET status = 'being_dispensed' WHERE id = 'your-prescription-id';
UPDATE prescription_orders SET status = 'dispatched' WHERE id = 'your-prescription-id';
UPDATE prescription_orders SET status = 'delivered' WHERE id = 'your-prescription-id';

-- Test events that trigger functions but don't send emails
UPDATE patient_app_requests SET status = 'approved' WHERE id = 'your-request-id'; -- No email
UPDATE patient_app_requests SET clinical_decision = 'rejected' WHERE id = 'your-request-id'; -- No email
```

### 3. Monitoring and Logs

Check the function logs in the Supabase dashboard to monitor:

* Function invocations
* Email sending success/failures
* SendGrid API responses
* Patient notification preference checks

## Configuration Requirements

### Environment Variables

Ensure these are set in your Supabase project:

* `SENDGRID_API_KEY` - Your SendGrid API key
* `SUPABASE_URL` - Your Supabase project URL
* `SUPABASE_SERVICE_ROLE_KEY` - Service role key for internal function calls

### Database Settings

Set these database-level configuration settings:

```sql theme={null}
ALTER DATABASE postgres SET app.supabase_url = 'https://your-project-ref.supabase.co';
ALTER DATABASE postgres SET app.supabase_service_role_key = 'your-service-role-key';
```

## Error Handling

The system includes comprehensive error handling:

* **Missing patient data**: Gracefully skips notification
* **Disabled notifications**: Respects patient preferences
* **SendGrid failures**: Logs errors and continues
* **Database errors**: Proper error responses with details

## Integration with Existing System

This system works alongside the existing `prescription-notifications` function:

* No conflicts or duplicates
* Complementary functionality
* Shared notification preference system
* Consistent email template structure

## Future Enhancements

Potential improvements:

* SMS notifications
* Push notifications for mobile app
* Email template versioning
* Advanced notification scheduling
* Delivery confirmation tracking