Skip to main content

​
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 TriggerEventTemplateStepDescription
status → submittedsubmittedRequest1Requested - Initial request submission
clinical_decision → prescription_createdprescription_createdRequest2Prescription Approved - Doctor approves and creates prescription
payment_status → paidpayment_confirmedRequest3Dispensed - Payment confirmed, being prepared
status → delivered (or from prescription)deliveredRequest4Delivered - Order completed and delivered

​
Prescription Orders - 4 Email Events

Database TriggerEventTemplateStepDescription
status → prescribedprescribedPrescription1Prescription Approved - Doctor creates prescription
status → being_dispensedbeing_dispensedPrescription2Details Confirmed - Pharmacy processing
status → dispatcheddispatchedPrescription3Dispensed - Ready for delivery/pickup
status → delivereddeliveredPrescription4Delivered - 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: 
{
  "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: 
// 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: 
-- 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: 
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