Unified Email Schedules Design

Date: 2026-02-10 Status: Approved Scope: Unify Tank Level Alert email sending into Email Schedules system with extensible framework

Problem

The system has two independent email sending mechanisms:

1. Email Schedules — Full-featured scheduling system with templates, variables, recipient management, and logs
2. Tank Level Alert — Separate Edge Function (send-tank-level-alert) with hardcoded HTML template, recipients in cfg_app_settings, sending via Microsoft Graph API

A database migration already created a system email schedule record for tank alerts, but it's not actually used. The tank alert system bypasses Email Schedules entirely.

Goals

1. Make Email Schedules the single entry point for all email configuration
2. Unify email sending through one Edge Function using Microsoft Graph API
3. Design an extensible framework for future system email types (e.g., Calibration Reminder)
4. Preserve Tank Alert detection and lifecycle management as-is (independent page)

Design Decisions

Decision	Choice
Scope	Only integrate Tank Level Alert; Calibration Reminder later
UI layout	Mixed list with "System" badge; no separate tabs
System schedule editability	Fully editable (recipients, subject, body, schedule, etc.)
Sending channel	All emails via Microsoft Graph API; remove Resend dependency
Trigger model	Follow schedule config (batch at scheduled time); no immediate trigger on detection
Send Now	Available on both Tank Alert page and Email Schedules page, shared logic
Old config fields	Migrate from cfg_app_settings then delete

Database Changes

1. New Fields on email_schedules

ALTER TABLE email_schedules
  ADD COLUMN schedule_type TEXT NOT NULL DEFAULT 'custom',
  ADD COLUMN system_type TEXT DEFAULT NULL;

-- schedule_type: 'custom' (user-created) | 'system' (auto-created, non-deletable)
-- system_type: NULL for custom, 'tank_level_alert' | 'calibration_reminder' | etc.

2. Update Existing System Schedule

The existing record a0000000-0000-0000-0000-000000000001 ("Tank Level Low Alert") will be updated:

UPDATE email_schedules
SET
  schedule_type = 'system',
  system_type = 'tank_level_alert'
WHERE id = 'a0000000-0000-0000-0000-000000000001';

Migrate recipients from cfg_app_settings:

-- Migrate tank_alert_recipients → email_schedules.to_recipients
-- Migrate tank_alert_enabled → email_schedules.status (active/paused)

3. Delete Deprecated cfg_app_settings Fields

After migration, delete the following rows from cfg_app_settings:

• tank_alert_recipients
• tank_alert_enabled

4. No Changes to Other Tables

• tank_level_alerts — unchanged (detection/lifecycle stays independent)
• email_logs — unchanged (already has schedule_id for association)
• email_variable_definitions / email_variable_templates — unchanged ({{summary_tank_level}} already defined)

Edge Function Consolidation

Unified send-email Edge Function

Before: 3 separate functions (send-email, send-tank-level-alert, send-calibration-reminder) After: 1 unified send-email function

Parameters:

interface SendEmailRequest {
  schedule_id: string;              // Required: which schedule to send
  override_recipients?: string[];   // Optional: temporary recipient override (for manual Send Now)
}

Execution Flow:

1. Read Email Schedule config by schedule_id (recipients, subject, body template, sender)
2. Check system_type:
   • If tank_level_alert → query pending tank_level_alerts for today
   • If null (custom) → no extra data needed
   • Future: register new data providers per system_type
3. Render variable templates ({{summary_tank_level}}, etc.) with queried data
4. Send via Microsoft Graph API
5. Write to email_logs with schedule_id
6. Post-send actions per system_type:
   • tank_level_alert → mark alerts as notified = true

Cron Trigger:

Existing cron mechanism queries all schedules where status = 'active' and next_send_at <= now(), then calls the send logic for each.

Deprecated Edge Functions

• send-tank-level-alert — logic merged into send-email, to be removed
• send-calibration-reminder — not fully implemented, to be removed (future implementation uses unified system)

Frontend Changes

Email Schedules Page

• List: All schedules in one list. System schedules show a "System" badge (e.g., blue tag)
• Delete protection: System schedules (schedule_type = 'system') cannot be deleted; hide/disable delete button
• Edit: System schedules use the same edit form as custom schedules — fully editable
• Send Now: Each schedule row has a "Send Now" action button, calls unified send-email with schedule_id
• Create: Users can only create custom type schedules; system schedules are created via database migrations

Tank Alert Management Page

• Preserved: Alert list, status tabs (Pending/Sent/Resolved), bulk operations, lifecycle management — all unchanged
• Send Now: Refactored to call unified send-email with the tank alert schedule_id. Manual recipient override still supported via override_recipients parameter
• Removed: Recipient configuration UI (replaced with link to Email Schedules page)
• Removed: Enable/disable toggle (replaced with link to Email Schedules page to toggle schedule status)

Settings Page

• Removed: tank_alert_recipients configuration (if UI exists)
• Removed: tank_alert_enabled configuration (if UI exists)

Extensible Framework

Future system email types (e.g., Calibration Reminder) require only 3 steps:

Step 1: Define Variable

Register in email_variable_definitions and create HTML template in email_variable_templates:

INSERT INTO email_variable_definitions (variable_name, display_name, description, fields)
VALUES ('summary_calibration', 'Calibration Summary', '...', '{...}');

INSERT INTO email_variable_templates (variable_name, name, html_template, is_default)
VALUES ('summary_calibration', 'Default', '<html>...</html>', true);

Step 2: Create System Schedule

Database migration inserts a new record:

INSERT INTO email_schedules (id, name, schedule_type, system_type, subject, body, ...)
VALUES ('a0000000-...', 'Calibration Reminder', 'system', 'calibration_reminder', ...);

Step 3: Register Data Provider

In send-email Edge Function, add a data provider for the new system_type:

const dataProviders: Record<string, () => Promise<VariableData>> = {
  tank_level_alert: fetchPendingTankAlerts,
  calibration_reminder: fetchUpcomingCalibrations, // new
};

No new Edge Functions needed. No frontend changes needed — the new schedule automatically appears in Email Schedules list with System badge.

Implementation Order

1. Database migration — Add fields, update system schedule, migrate cfg_app_settings data, delete deprecated fields
2. Unified send-email Edge Function — Merge tank alert logic, switch to Microsoft Graph API, implement variable rendering and system_type dispatch
3. Frontend: Email Schedules page — System badge, delete protection, Send Now button
4. Frontend: Tank Alert page — Refactor Send Now to use unified service, remove recipient config, add navigation links
5. Cleanup — Remove send-tank-level-alert Edge Function, remove Settings page config items, clean up old code references