Tank Level Low Alert Email Template Design (V2)

Overview

Create a new email template variable {{summary_tank_level}} for sending low tank level alerts. When a tank's remaining water drops below 15,000 litres, it is added to a pending alert queue. All pending alerts are aggregated and sent in a single email at 8:00 AM the next day.

Requirements

Trigger Threshold: Tank remaining < 15,000 litres
Alert Frequency: Once per tank until refilled above threshold (state-locked)
Queue Mechanism: Alerts are queued when detected, sent next day at 8:00 AM
Email Scope: One email containing ALL low-level tanks (aggregated)
Recipients: Manually configured by admin
Visual Style: Match existing TankCylinder component + Dustac branding

Key Changes from V1

Aspect	V1 (Original)	V2 (Updated)
Email Scope	One email per tank	One email with multiple tanks
Send Timing	Immediate	Next day 8:00 AM
Data Structure	Single tank object	Array of tank objects
Template Variable	`{{summary_tank_level}}` (single)	`{{summary_tank_level}}` (array with `{{#each}}`)

Data Structure

TankLevelAlertData Type (Updated for Array)

typescript

// Single tank alert data (unchanged)
type TankLevelAlertItem = {
  assetId: string;
  assetDisplayId: string;        // Tank display name
  siteName: string;              // Mine site name
  currentLitres: number;         // Current remaining litres
  capacityLitres: number;        // Tank capacity
  percentRemaining: number;      // Percentage remaining
  lastCorrection: string;        // Last correction datetime
  lastDispensing: string;        // Last dispensing datetime
  liquidHeight: number;          // CSS height for liquid (0-100)
  emptyHeight: number;           // CSS height for empty space (0-100)
};

// Aggregated alert data for email
type TankLevelAlertData = {
  thresholdLitres: number;       // 15000
  reportDate: string;            // Report generation date
  tankCount: number;             // Number of tanks in alert
  tanks: TankLevelAlertItem[];   // Array of tank data
};

Database Changes

1. Update Alert Tracking Table

Add scheduled_send_date to track which batch an alert belongs to:

sql

-- Add scheduled send date column
ALTER TABLE tank_level_alerts
ADD COLUMN scheduled_send_date date;

-- Index for batch queries
CREATE INDEX idx_tank_alerts_scheduled_date
  ON tank_level_alerts (scheduled_send_date)
  WHERE resolved_at IS NULL AND notified = false;

-- Comment
COMMENT ON COLUMN tank_level_alerts.scheduled_send_date IS 'Date when this alert will be included in the batch email (8:00 AM)';

2. Update Variable Definition

Update to reflect array structure:

sql

UPDATE email_variable_definitions
SET
  description = 'Low tank level alerts for multiple tanks below threshold (aggregated)',
  fields = '[
    {"name": "threshold_litres", "type": "number", "description": "Alert threshold in litres (15000)"},
    {"name": "report_date", "type": "string", "description": "Report generation date"},
    {"name": "tank_count", "type": "number", "description": "Number of tanks in alert"},
    {"name": "tanks", "type": "array", "description": "Array of tank alert data", "arrayItemFields": [
      {"name": "asset_id", "type": "string", "description": "Tank asset ID"},
      {"name": "asset_display_id", "type": "string", "description": "Tank display name"},
      {"name": "site_name", "type": "string", "description": "Mine site name"},
      {"name": "current_litres", "type": "number", "description": "Current remaining litres"},
      {"name": "capacity_litres", "type": "number", "description": "Tank capacity"},
      {"name": "percent_remaining", "type": "number", "description": "Percentage remaining"},
      {"name": "last_correction", "type": "string", "description": "Last correction datetime"},
      {"name": "last_dispensing", "type": "string", "description": "Last dispensing datetime"},
      {"name": "liquid_height", "type": "number", "description": "CSS height for liquid visualization"},
      {"name": "empty_height", "type": "number", "description": "CSS height for empty space"}
    ]}
  ]'::jsonb
WHERE variable_name = 'summary_tank_level';

Email Template Structure

Full Email Layout (Updated)

html

<!-- Email body 600px width -->
<table width="600" style="font-family: 'Helvetica Neue', Arial, sans-serif;">

  <!-- Header: Logo + Title -->
  <tr>
    <td style="padding: 20px 0;">
      <img src="https://dashboard.dustac.com.au/Dustac-logo.png" width="180" />
      <div style="border-left: 4px solid #ec4e20; padding-left: 20px; margin-top: 20px;">
        <p style="color: #64748b; font-size: 11px; text-transform: uppercase; letter-spacing: 2px;">
          ⚠️ Monitoring Alert
        </p>
        <h1 style="color: #1e293b; font-size: 28px; margin: 0;">
          Tank Level Low Warning
        </h1>
      </div>
    </td>
  </tr>

  <!-- Warning Banner with Count -->
  <tr>
    <td style="background: #fef2f2; border: 1px solid #fecaca; border-radius: 6px; padding: 16px; margin: 16px 0;">
      <p style="color: #991b1b; margin: 0; font-weight: 600;">
        ⚠️ {{tank_count}} tank(s) are below 15,000 litres and require attention.
      </p>
    </td>
  </tr>

  <!-- Tank Cards (Multiple) -->
  <tr>
    <td style="padding: 24px 0;">
      {{summary_tank_level}}
    </td>
  </tr>

  <!-- Footer -->
  <tr>
    <td style="border-top: 1px solid #e2e8f0; padding: 24px 0;">
      <p style="color: #94a3b8; font-size: 12px;">
        © 2026 Dustac. All rights reserved.
      </p>
      <p style="color: #cbd5e1; font-size: 11px;">
        Automated Alert - Please do not reply.
      </p>
    </td>
  </tr>

</table>

Tank Cards Template (`{{summary_tank_level}}`) - Multiple Tanks

html

{{#each tanks}}
<table width="100%" style="border: 1px solid #e2e8f0; border-radius: 8px; overflow: hidden; margin-bottom: 16px;">
  <!-- Card Header: Site + Status Badge -->
  <tr>
    <td style="padding: 16px; background: #f8fafc; border-bottom: 1px solid #e2e8f0;">
      <table width="100%" border="0" cellpadding="0" cellspacing="0">
        <tr>
          <td>
            <p style="margin: 0 0 4px; color: #64748b; font-size: 12px;">📍 {{site_name}}</p>
            <p style="margin: 0; color: #1e293b; font-size: 18px; font-weight: 700;">{{asset_display_id}}</p>
          </td>
          <td align="right" valign="top">
            <span style="display: inline-block; background: #fef2f2; color: #dc2626; padding: 6px 12px; border-radius: 20px; font-size: 12px; font-weight: 600;">
              🔴 Low
            </span>
          </td>
        </tr>
      </table>
    </td>
  </tr>

  <!-- Card Body: Tank Visualization + Data -->
  <tr>
    <td style="padding: 20px;">
      <table width="100%" border="0" cellpadding="0" cellspacing="0">
        <tr>
          <!-- Left: Tank Visualization -->
          <td width="120" valign="top">
            <table width="80" border="0" cellpadding="0" cellspacing="0" style="margin: 0 auto;">
              <tr>
                <td style="background: #e2e8f0; height: 12px; border-radius: 40px 40px 0 0;"></td>
              </tr>
              <tr>
                <td style="background: #f1f5f9; height: {{empty_height}}px;"></td>
              </tr>
              <tr>
                <td style="background: #f97316; height: {{liquid_height}}px;"></td>
              </tr>
              <tr>
                <td style="background: #ea580c; height: 12px; border-radius: 0 0 40px 40px;"></td>
              </tr>
            </table>
            <p style="text-align: center; margin: 12px 0 0; font-size: 24px; font-weight: 700; color: #dc2626;">
              {{percent_remaining}}%
            </p>
          </td>

          <!-- Right: Detailed Data -->
          <td valign="top" style="padding-left: 24px;">
            <table width="100%" border="0" cellpadding="0" cellspacing="0" style="font-size: 14px;">
              <tr>
                <td style="color: #64748b; padding: 8px 0;">Current Level</td>
                <td align="right" style="color: #dc2626; font-weight: 700; font-family: monospace;">{{current_litres}} L</td>
              </tr>
              <tr>
                <td style="color: #64748b; padding: 8px 0;">Capacity</td>
                <td align="right" style="color: #1e293b; font-family: monospace;">{{capacity_litres}} L</td>
              </tr>
              <tr>
                <td colspan="2" style="padding: 8px 0;"><hr style="border: none; border-top: 1px solid #e2e8f0; margin: 0;" /></td>
              </tr>
              <tr>
                <td style="color: #64748b; padding: 8px 0;">Last Correction</td>
                <td align="right" style="color: #1e293b;">{{last_correction}}</td>
              </tr>
              <tr>
                <td style="color: #64748b; padding: 8px 0;">Last Dispensing</td>
                <td align="right" style="color: #1e293b;">{{last_dispensing}}</td>
              </tr>
            </table>
          </td>
        </tr>
      </table>
    </td>
  </tr>
</table>
{{/each}}

Alert Logic Flow (Updated)

┌─────────────────────────────────────────────────────────────────┐
│                    FLOW METER DATA UPDATE                        │
│              (triggered by scraper or manual input)              │
└─────────────────────────────────────────────────────────────────┘
                              │
                              ▼
┌─────────────────────────────────────────────────────────────────┐
│                    DETECT LOW TANKS                              │
│              detectLowTanks(threshold = 15000)                   │
└─────────────────────────────────────────────────────────────────┘
                              │
                              ▼
┌─────────────────────────────────────────────────────────────────┐
│                 FOR EACH LOW TANK                                │
│                                                                  │
│  ┌─────────────────────────────────────────────────────────┐    │
│  │ hasActiveAlert(assetId)?                                 │    │
│  │                                                          │    │
│  │  YES → Skip (already in queue or notified)               │    │
│  │                                                          │    │
│  │  NO  → createAlert(assetId, scheduledDate = tomorrow)    │    │
│  │        Add to pending queue for next day 8:00 AM         │    │
│  └─────────────────────────────────────────────────────────┘    │
└─────────────────────────────────────────────────────────────────┘
                              │
                              ▼
┌─────────────────────────────────────────────────────────────────┐
│                 RESOLVE REFILLED TANKS                           │
│              resolveRefilledTanks(threshold = 15000)             │
│                                                                  │
│  Tanks now above threshold → set resolved_at = now()             │
│  (Removes from pending queue, can trigger again in future)       │
└─────────────────────────────────────────────────────────────────┘


┌─────────────────────────────────────────────────────────────────┐
│                    DAILY CRON JOB (8:00 AM AWST)                 │
└─────────────────────────────────────────────────────────────────┘
                              │
                              ▼
┌─────────────────────────────────────────────────────────────────┐
│                 GET PENDING ALERTS FOR TODAY                     │
│              getPendingAlertsForDate(today)                      │
│                                                                  │
│  WHERE scheduled_send_date = today                               │
│    AND resolved_at IS NULL                                       │
│    AND notified = false                                          │
└─────────────────────────────────────────────────────────────────┘
                              │
                              ▼
                    ┌─────────────────┐
                    │ Any pending?    │
                    └─────────────────┘
                      │           │
                     NO          YES
                      │           │
                      ▼           ▼
                    Exit    ┌─────────────────────────────────────┐
                            │ GENERATE AGGREGATED EMAIL DATA       │
                            │                                      │
                            │ For each pending alert:              │
                            │   - Fetch current tank level         │
                            │   - Generate TankLevelAlertItem      │
                            │                                      │
                            │ Create TankLevelAlertData:           │
                            │   - thresholdLitres: 15000           │
                            │   - reportDate: today                │
                            │   - tankCount: N                     │
                            │   - tanks: [item1, item2, ...]       │
                            └─────────────────────────────────────┘
                                          │
                                          ▼
                            ┌─────────────────────────────────────┐
                            │ SEND SINGLE AGGREGATED EMAIL         │
                            │                                      │
                            │ - Replace {{summary_tank_level}}     │
                            │ - Send to configured recipients      │
                            └─────────────────────────────────────┘
                                          │
                                          ▼
                            ┌─────────────────────────────────────┐
                            │ MARK ALERTS AS NOTIFIED              │
                            │                                      │
                            │ UPDATE tank_level_alerts             │
                            │ SET notified = true                  │
                            │ WHERE id IN (sent_alert_ids)         │
                            └─────────────────────────────────────┘

Service API (Updated)

tankAlertService.ts

typescript

export const TankAlertService = {
  // Detect tanks below threshold
  async detectLowTanks(thresholdLitres: number = 15000): Promise<TankLevel[]>;

  // Check if tank has active (unresolved) alert
  async hasActiveAlert(assetId: string): Promise<boolean>;

  // Create alert record with scheduled send date (tomorrow)
  async createAlert(assetId: string, scheduledSendDate?: Date): Promise<void>;

  // Resolve alert (when tank refilled above threshold)
  async resolveAlert(assetId: string): Promise<void>;

  // Check and resolve alerts for tanks now above threshold
  async resolveRefilledTanks(thresholdLitres: number = 15000): Promise<string[]>;

  // Get pending alerts for a specific date (for batch sending)
  async getPendingAlertsForDate(date: Date): Promise<TankLevelAlert[]>;

  // Generate aggregated email data for multiple tanks
  async generateAggregatedAlertData(
    alerts: TankLevelAlert[],
    thresholdLitres: number = 15000
  ): Promise<TankLevelAlertData>;

  // Mark alerts as notified after email sent
  async markAlertsAsNotified(alertIds: string[]): Promise<void>;

  // Process alerts: detect, queue, and resolve (called after flow meter update)
  async processAlerts(thresholdLitres: number = 15000): Promise<{
    newAlerts: number;
    resolvedAlerts: number;
  }>;
};

Implementation Files

File	Action	Description
`supabase/migrations/20260207000000_update_tank_level_alerts_v2.sql`	Create	Add `scheduled_send_date` column, update variable definition
`src/features/flow-meter/types.ts`	Modify	Update `TankLevelAlertData` to array structure, add `TankLevelAlertItem`
`src/features/flow-meter/services/tankAlertService.ts`	Modify	Add batch query methods, aggregation logic
`src/features/flow-meter/services/tankAlertService.test.ts`	Modify	Add tests for new batch methods
`src/features/email-schedules/services/variableTemplateService.ts`	Modify	Update `DEFAULT_TANK_LEVEL_TEMPLATE` for array iteration
`supabase/functions/send-email/index.ts`	Modify	Update `formatTankLevelAsHTML` for array data
`supabase/functions/send-tank-level-alert/index.ts`	Create	New Edge Function for daily batch sending

Cron Job Configuration

Add to Supabase cron jobs (via pg_cron or external scheduler):

sql

-- Daily at 8:00 AM AWST (00:00 UTC)
SELECT cron.schedule(
  'send-tank-level-alerts',
  '0 0 * * *',  -- 00:00 UTC = 08:00 AWST
  $$
  SELECT net.http_post(
    url := 'https://your-project.supabase.co/functions/v1/send-tank-level-alert',
    headers := '{"Authorization": "Bearer SERVICE_ROLE_KEY"}'::jsonb,
    body := '{}'::jsonb
  );
  $$
);

Color Scheme (Unchanged)

Element	Color	Hex
Dustac Primary	Orange	#ec4e20
Low Status	Red	#dc2626
Low Background	Light Red	#fef2f2
Low Border	Red	#fecaca
Liquid Gradient Start	Orange	#f97316
Liquid Gradient End	Dark Orange	#ea580c
Text Primary	Dark Gray	#1e293b
Text Secondary	Gray	#64748b
Background	Light Gray	#f8fafc
Border	Gray	#e2e8f0

Future Considerations

Configurable send time (not just 8:00 AM)
Configurable threshold per tank/site
Email recipient configuration per site
Dashboard UI for viewing pending alerts queue
Manual trigger to send alerts immediately

Tank Level Low Alert Email Template Design (V2) ​

Overview ​

Requirements ​

Key Changes from V1 ​

Data Structure ​

TankLevelAlertData Type (Updated for Array) ​

Database Changes ​

1. Update Alert Tracking Table ​

2. Update Variable Definition ​

Email Template Structure ​

Full Email Layout (Updated) ​

Tank Cards Template ({{summary_tank_level}}) - Multiple Tanks ​

Alert Logic Flow (Updated) ​

Service API (Updated) ​

tankAlertService.ts ​

Implementation Files ​

Cron Job Configuration ​

Color Scheme (Unchanged) ​

Future Considerations ​