Prescription Notification System - ADHD Simple Documentation

Overview

The prescription notification system sends email and push notifications to patients when their prescriptions change status or when new requests are created. The system is designed to be simple, reliable, and compliant with notification delivery standards. Key Features:

Multi-channel notifications - Email and push notifications via SendGrid and Expo
Manual triggering - Notifications sent via direct edge function calls for precise control
Event-based system - Supports prescription status changes and request creation
Patient preferences - Granular control over notification types and channels
Marketing preferences - Separate opt-in for marketing communications
Clinic-based organization - Preferences organized by patient and clinic

Architecture

How It Works

1. Manual Notification Triggering

Notifications are triggered manually via direct calls to the prescription-notifications edge function from other edge functions:

Request Created: When a patient submits a reorder request via checkout-submit-order
Prescription Status: When prescription status changes via create-prescription-order
Manual Calls: Direct API calls for testing or administrative purposes

2. Event-Based Processing

The prescription-notifications function processes different event types:

request_created - New patient app requests
prescribed - Prescription signed by doctor
checkout_received - Prescription ready for patient review
dispatched - Prescription shipped
delivered - Prescription delivered

3. Multi-Channel Delivery

Based on patient preferences, notifications are sent via:

Email - Professional HTML emails via SendGrid
Push - Mobile notifications via Expo Push Service

Edge Functions

prescription-notifications

Purpose: Send email and push notifications for prescription events
Technology: Deno with SendGrid and Expo Push integration
Key Functions:
- Patient preference checking
- Email and push content generation
- Multi-channel delivery
- Notification event logging

Usage Example:

// Called from checkout-submit-order for new requests
const notificationResponse = await functionClient.functions.invoke('prescription-notifications', {
  body: {
    prescriptionOrderId: 'REQUEST_' + appRequest.id,
    event: 'request_created',
    prescription: {
      order_number: appRequest.request_number,
      patients: {
        id: patient.id,
        first_name: patient.first_name,
        last_name: patient.last_name,
        email: patient.email
      },
      clinic_snapshot: clinicData
    }
  }
});

// Called for prescription status changes
const notificationResponse = await fetch(`${SUPABASE_URL}/functions/v1/prescription-notifications`, {
  method: 'POST',
  headers: {
    'Authorization': authHeader,
    'Content-Type': 'application/json'
  },
  body: JSON.stringify({
    prescriptionOrderId: prescriptionOrder.id,
    event: 'prescribed'
  })
});

manage-patient-notifications

Purpose: CRUD operations for patient notification preferences
Technology: Deno with Supabase client
Key Functions:
- Get patient preferences (GET)
- Update patient preferences (PUT/PATCH)
- Authorization checking (patient or clinic staff)
- Default preference creation

Usage Example:

// Get patient preferences
const response = await fetch(`${SUPABASE_URL}/functions/v1/manage-patient-notifications?patientId=${patientId}`, {
  headers: { 'Authorization': `Bearer ${token}` }
});

// Update preferences
const updateResponse = await fetch(`${SUPABASE_URL}/functions/v1/manage-patient-notifications`, {
  method: 'PUT',
  headers: {
    'Authorization': `Bearer ${token}`,
    'Content-Type': 'application/json'
  },
  body: JSON.stringify({
    patientId: patientId,
    preferences: {
      email_enabled: true,
      push_enabled: false,
      email_prescription_approved: true
    }
  })
});

Email Templates

Status-Based Email Content

Status	Subject	Message
`request_created`	”Order Request Received"	"Hi [Patient Name], your medication request has been received and is being reviewed by your clinician.”
`prescribed`	”Prescription Signed by Your Doctor"	"Dr. [Name] has signed your prescription [Order#]. Your medication will now be processed by the pharmacy.”
`checkout_received`	”Prescription Ready to View"	"Hi [Patient Name], your prescription [Order#] is ready to view and complete in the app.”
`dispatched`	”Prescription Dispatched for Delivery"	"Your prescription [Order#] has been dispatched and is on its way to you.”
`delivered`	”Prescription Delivered"	"Your prescription [Order#] has been successfully delivered.”

Email Template Configuration

SendGrid Template IDs:

request_created & checkout_received: d-5c933e93f5f246898b90dce3301cadab
prescribed: d-e5525d6f7afa49029fed564a54854b9a

Template Data Structure:

{
  "subject": "Order Request Received",
  "first_name": "Cameron",
  "email": "patient@example.com",
  "clinician_title": "Dr.",
  "clinician_first_name": "John",
  "clinician_last_name": "Smith",
  "clinic_name": "Your Clinic Name",
  "step_number": "1"
}

Push Notifications

Expo Push Integration

Push notifications are delivered via Expo’s push notification service:

const pushPayload = {
  to: userPushToken,
  title: notificationContent.title,
  body: notificationContent.body,
  data: {
    type: event,
    prescriptionOrderId: prescription.id,
    orderNumber: prescription.order_number
  },
  priority: 'high',
  sound: 'default'
};

Push Notification Content

Status	Title	Body
`request_created`	”📋 Order Request Received"	"Hi [Name], your medication request has been received and is being reviewed.”
`prescribed`	”Prescription Approved ✅"	"Hi [Name], your prescription has been approved by your doctor and will be sent to the pharmacy.”
`checkout_received`	”📋 Prescription Ready to View"	"Hi [Name], your prescription [Order#] is ready to view and complete.”
`dispatched`	”Prescription Dispatched 🚚"	"Hi [Name], your prescription has been dispatched and is on its way to you.”
`delivered`	”Prescription Delivered 📬"	"Hi [Name], your prescription has been successfully delivered.”

Database Schema

Patient Notification Preferences

CREATE TABLE patient_notification_preferences (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    patient_id UUID NOT NULL REFERENCES patients(id) ON DELETE CASCADE,
    clinic_id UUID NOT NULL REFERENCES clinics(id) ON DELETE CASCADE,
    user_id UUID REFERENCES app_users(id) ON DELETE SET NULL,
    
    -- Global preferences
    all_notifications_enabled BOOLEAN DEFAULT true,
    
    -- Email preferences
    email_enabled BOOLEAN DEFAULT true,
    email_prescription_created BOOLEAN DEFAULT true,
    email_prescription_approved BOOLEAN DEFAULT true,
    email_prescription_ready BOOLEAN DEFAULT true,
    email_prescription_dispatched BOOLEAN DEFAULT true,
    email_prescription_delivered BOOLEAN DEFAULT true,
    email_payment_reminders BOOLEAN DEFAULT true,
    email_request_created BOOLEAN DEFAULT true,
    
    -- Push notification preferences
    push_enabled BOOLEAN DEFAULT true,
    push_prescription_created BOOLEAN DEFAULT true,
    push_prescription_approved BOOLEAN DEFAULT true,
    push_prescription_ready BOOLEAN DEFAULT true,
    push_prescription_dispatched BOOLEAN DEFAULT true,
    push_prescription_delivered BOOLEAN DEFAULT true,
    push_payment_reminders BOOLEAN DEFAULT true,
    push_request_created BOOLEAN DEFAULT true,
    
    -- Marketing preferences (opt-in required)
    marketing_emails_enabled BOOLEAN DEFAULT false,
    marketing_push_enabled BOOLEAN DEFAULT false,
    
    -- Timing preferences
    quiet_hours_enabled BOOLEAN DEFAULT false,
    quiet_hours_start TIME DEFAULT '22:00',
    quiet_hours_end TIME DEFAULT '08:00',
    
    -- Metadata
    created_at TIMESTAMPTZ DEFAULT now(),
    updated_at TIMESTAMPTZ DEFAULT now(),
    
    CONSTRAINT unique_patient_notification_preferences UNIQUE(patient_id)
);

Automatic Preference Creation

When a new patient is created, default notification preferences are automatically created:

CREATE OR REPLACE FUNCTION create_default_patient_notification_preferences()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO patient_notification_preferences (
        patient_id,
        clinic_id,
        user_id,
        all_notifications_enabled,
        email_enabled,
        push_enabled,
        -- All notification types default to true
        -- Marketing preferences default to false (opt-in required)
    ) VALUES (
        NEW.id,
        NEW.clinic_id,
        NULL, -- Linked when patient creates app account
        true, -- all_notifications_enabled
        true, -- email_enabled
        true, -- push_enabled
        -- ... (all other preferences with appropriate defaults)
    );
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER create_patient_notification_preferences_trigger
    AFTER INSERT ON patients
    FOR EACH ROW
    EXECUTE FUNCTION create_default_patient_notification_preferences();

User Linking

When a patient creates a mobile app account, their notification preferences are automatically linked:

CREATE OR REPLACE FUNCTION link_patient_notification_preferences_to_user()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE patient_notification_preferences
    SET user_id = NEW.id
    WHERE patient_id = NEW.patient_id
    AND user_id IS NULL;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Database Triggers

Trigger Status: DISABLED

Database triggers for prescription notifications have been disabled as of migration 20250117_disable_prescription_notification_triggersNotifications are now handled manually via direct edge function calls for better control and to prevent duplicate notifications.

Historical Trigger Implementation

Previously, the system used database triggers to automatically send notifications when prescription status changed. These triggers have been disabled to prevent duplicate notifications and provide better control over notification timing. Disabled Triggers:

prescription_created_notification - Previously triggered on INSERT to prescription_orders
prescription_updated_notification - Previously triggered on UPDATE to prescription_orders

Migration Details:

-- Migration: 20250117_disable_prescription_notification_triggers
DROP TRIGGER IF EXISTS prescription_created_notification ON prescription_orders;
DROP TRIGGER IF EXISTS prescription_updated_notification ON prescription_orders;

The trigger functions (handle_prescription_created, handle_prescription_updated, send_prescription_notification) remain in the database but are no longer actively used.

Row Level Security (RLS)

Patient Access

Patients can view and update their own notification preferences:

-- Patients can view their own preferences
CREATE POLICY "Patients can view own notification preferences" 
ON patient_notification_preferences
FOR SELECT USING (auth.uid()::uuid = user_id);

-- Patients can update their own preferences
CREATE POLICY "Patients can update own notification preferences" 
ON patient_notification_preferences
FOR UPDATE USING (auth.uid()::uuid = user_id);

Clinical Staff Access

Clinical staff can manage patient preferences within their clinic:

-- Clinical users can view patient preferences in their clinic
CREATE POLICY "Clinical users can view patient preferences in their clinic" 
ON patient_notification_preferences
FOR SELECT USING (
    EXISTS (
        SELECT 1 FROM user_profiles
        WHERE user_profiles.id = auth.uid()::uuid
        AND user_profiles.clinic_id = patient_notification_preferences.clinic_id
        AND user_profiles.is_active = true
    )
);

Preference Checking Logic

The notification function checks multiple preference levels:

// Check if notifications are globally enabled
const globallyEnabled = preferences?.all_notifications_enabled !== false;

// Check if email notifications are enabled
const emailEnabled = preferences?.email_enabled !== false && 
                    globallyEnabled &&
                    patient.email;

// Check if push notifications are enabled
const pushEnabled = preferences?.push_enabled !== false && 
                   globallyEnabled &&
                   patientPushToken;

// Check specific event preferences
const eventEmailKey = `email_${mapEventToPreferenceKey(event)}`;
const eventPushKey = `push_${mapEventToPreferenceKey(event)}`;
const eventEmailEnabled = preferences?.[eventEmailKey] !== false;
const eventPushEnabled = preferences?.[eventPushKey] !== false;

// Send notifications based on combined preferences
if (emailEnabled && eventEmailEnabled) {
  await sendEmailNotification(patient, content);
}

if (pushEnabled && eventPushEnabled) {
  await sendPushNotification(patientPushToken, content);
}

Implementation Examples

Manual Trigger for Request Created

// Called from checkout-submit-order when patient creates a reorder request
const notificationResponse = await functionClient.functions.invoke('prescription-notifications', {
  body: {
    prescriptionOrderId: 'REQUEST_' + appRequest.id,
    event: 'request_created',
    prescription: {
      order_number: appRequest.request_number,
      patients: {
        id: patient.id,
        first_name: patient.first_name,
        last_name: patient.last_name,
        email: patient.email
      },
      clinic_snapshot: clinicData
    }
  }
});

Manual Trigger for Prescription Status

// Called from create-prescription-order when prescription is created
const notificationResponse = await fetch(`${Deno.env.get('SUPABASE_URL')}/functions/v1/prescription-notifications`, {
  method: 'POST',
  headers: {
    'Authorization': authHeader,
    'Content-Type': 'application/json'
  },
  body: JSON.stringify({
    prescriptionOrderId: prescriptionOrder.id,
    event: 'created'
  })
});

Direct API Call for Testing

// Direct API call for testing or administrative purposes
const response = await fetch(`${SUPABASE_URL}/functions/v1/prescription-notifications`, {
  method: 'POST',
  headers: {
    'Authorization': `Bearer ${token}`,
    'Content-Type': 'application/json'
  },
  body: JSON.stringify({
    prescriptionOrderId: 'uuid-here',
    event: 'prescribed'
  })
});

Status Change (Manual Trigger Required)

// Update prescription status - notification must be sent manually
const { error } = await supabase
  .from('prescription_orders')
  .update({ status: 'prescribed' })
  .eq('id', prescriptionId);

// Manually trigger notification after status change
if (!error) {
  await fetch(`${SUPABASE_URL}/functions/v1/prescription-notifications`, {
    method: 'POST',
    headers: {
      'Authorization': `Bearer ${token}`,
      'Content-Type': 'application/json'
    },
    body: JSON.stringify({
      prescriptionOrderId: prescriptionId,
      event: 'prescribed'
    })
  });
}

Managing Patient Preferences

// Get current preferences
const { data: preferences } = await supabase.functions.invoke('manage-patient-notifications', {
  method: 'GET',
  body: { patientId: 'patient-uuid' }
});

// Update preferences
const { data: updated } = await supabase.functions.invoke('manage-patient-notifications', {
  method: 'PUT',
  body: {
    patientId: 'patient-uuid',
    preferences: {
      email_enabled: true,
      push_enabled: false,
      marketing_emails_enabled: false
    }
  }
});

Configuration

SendGrid Settings

Sender: welcome@adhdsimple.co.uk
Name: ADHD Simple
Authentication: SPF, DKIM, DMARC configured
Format: HTML emails with professional styling

Expo Push Settings

Service: Expo Push Notification Service
Authentication: Expo Access Token
Priority: High for prescription notifications
Sound: Default notification sound

Security Considerations

Authentication

All functions require valid JWT authentication
Service role key used for database triggers
Patient data access controlled by RLS policies

Data Privacy

Patient PII protected in notification content
Email and push events logged for audit compliance
Notification preferences isolated per patient and clinic
Marketing preferences require explicit opt-in

Notification Security

Email authentication via SPF, DKIM, DMARC
Push tokens securely stored and managed
Preferences cannot be modified by unauthorized users

Monitoring and Debugging

Check Notification Events

-- View recent email events
SELECT * FROM email_events
WHERE recipient_email = 'patient@example.com'
ORDER BY created_at DESC;

-- Check patient preferences
SELECT * FROM patient_notification_preferences
WHERE patient_id = 'patient-uuid';

-- View push notification history
SELECT * FROM push_notification_history
WHERE user_id = 'user-uuid'
ORDER BY sent_at DESC;

-- Check prescription notification tracking
SELECT id, order_number, last_notification_sent_at, notification_count
FROM prescription_orders
WHERE patient_id = 'patient-uuid'
ORDER BY created_at DESC;

Edge Function Logs

Monitor the prescription-notifications function logs for:

📧 Sending email notification for request_created event...
📮 SendGrid response status: 202
📧 Email sent successfully

Common Issues

Emails not sending

Verify SendGrid API key is configured
Check patient has valid email address
Verify patient notification preferences are enabled
Check function logs for SendGrid errors

Duplicate notifications

Ensure database triggers are disabled (migration 20250117_disable_prescription_notification_triggers)
Check that manual function calls aren’t duplicated in edge functions
Verify only one notification call per event

Email event logging failures

Check if email_events table exists and has correct permissions
Verify the logging doesn’t fail the main notification flow
Look for Email event logged: { error: {}, success: false } in logs

Missing clinic information

Verify clinic data is fetched and passed to notification function
Check clinic_snapshot field in prescription data
Ensure clinic_id is valid on patient record

Troubleshooting Guide

Check Function Execution: Look for 🚀 === PRESCRIPTION NOTIFICATIONS FUNCTION INVOKED === in logs
Verify Event Processing: Check for event-specific log messages like 📧 Sending email notification for request_created event...
SendGrid Response: Look for 📮 SendGrid response status: 202 (success) or error codes
Database Triggers: Ensure triggers are disabled to prevent duplicates
Patient Preferences: Verify patient_notification_preferences table has correct settings

Future Enhancements

Advanced Scheduling

Notification frequency limits
Quiet hours by timezone
Custom notification schedules
Batch notification processing

Rich Content

Dynamic SendGrid template integration
Rich push notifications with images
Branded email template designs
Multilingual template support

Analytics

Notification delivery analytics
Patient engagement metrics
A/B testing for notification content
Delivery success rate monitoring

The notification system prioritizes reliability and patient control. All notifications respect patient preferences and provide granular control over notification types and delivery channels. Marketing communications require explicit opt-in and are separate from medical notifications.

​Overview

​Architecture

​How It Works

​1. Manual Notification Triggering

​2. Event-Based Processing

​3. Multi-Channel Delivery

​Edge Functions

​prescription-notifications

​manage-patient-notifications

​Email Templates

​Status-Based Email Content

​Email Template Configuration

​Push Notifications

​Expo Push Integration

​Push Notification Content

​Database Schema

​Patient Notification Preferences

​Automatic Preference Creation

​User Linking

​Database Triggers

​Trigger Status: DISABLED

​Historical Trigger Implementation

​Row Level Security (RLS)

​Patient Access

​Clinical Staff Access

​Preference Checking Logic

​Implementation Examples

​Manual Trigger for Request Created

​Manual Trigger for Prescription Status

​Direct API Call for Testing

​Status Change (Manual Trigger Required)

​Managing Patient Preferences

​Configuration

​SendGrid Settings

​Expo Push Settings

​Security Considerations

​Authentication

​Data Privacy

​Notification Security

​Monitoring and Debugging

​Check Notification Events

​Edge Function Logs

​Common Issues

​Troubleshooting Guide

​Future Enhancements

Advanced Scheduling

Rich Content

Analytics

Overview

Architecture

How It Works

1. Manual Notification Triggering

2. Event-Based Processing

3. Multi-Channel Delivery

Edge Functions

prescription-notifications

manage-patient-notifications

Email Templates

Status-Based Email Content

Email Template Configuration

Push Notifications

Expo Push Integration

Push Notification Content

Database Schema

Patient Notification Preferences

Automatic Preference Creation

User Linking

Database Triggers

Trigger Status: DISABLED

Historical Trigger Implementation

Row Level Security (RLS)

Patient Access

Clinical Staff Access

Preference Checking Logic

Implementation Examples

Manual Trigger for Request Created

Manual Trigger for Prescription Status

Direct API Call for Testing

Status Change (Manual Trigger Required)

Managing Patient Preferences

Configuration

SendGrid Settings

Expo Push Settings

Security Considerations

Authentication

Data Privacy

Notification Security

Monitoring and Debugging

Check Notification Events

Edge Function Logs

Common Issues

Troubleshooting Guide

Future Enhancements