Tank Level Low Alert Email Template Design

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, an individual alert email is sent for that specific tank.

Requirements

Trigger Threshold: Tank remaining < 15,000 litres
Alert Frequency: Once per tank until refilled above threshold (state-locked)
Email Scope: One email per low-level tank (not aggregated)
Recipients: Manually configured by admin
Visual Style: Match existing TankCylinder component + Dustac branding

Data Structure

TankLevelAlertData Type

typescript

type TankLevelAlertData = {
  // Threshold
  thresholdLitres: number;       // 15000
  reportDate: string;            // Report generation date

  // Tank info
  assetId: string;
  assetDisplayId: string;        // Tank display name
  siteName: string;              // Mine site name

  // Level data
  currentLitres: number;         // Current remaining litres
  capacityLitres: number;        // Tank capacity
  percentRemaining: number;      // Percentage remaining

  // Timestamps
  lastCorrection: string;        // Last correction datetime
  lastDispensing: string;        // Last dispensing datetime

  // Computed for template
  liquidHeight: number;          // CSS height for liquid (0-100)
  emptyHeight: number;           // CSS height for empty space (0-100)
};

Filter Logic

Only tanks where currentLitres < 15000 are included.

Database Changes

1. Variable Definition

Insert into email_variable_definitions:

sql

INSERT INTO email_variable_definitions (variable_name, display_name, description, fields) VALUES
(
  'summary_tank_level',
  'Tank Level Alert',
  'Low tank level alert for a single tank below threshold',
  '[
    {"name": "threshold_litres", "type": "number", "description": "Alert threshold in litres (15000)"},
    {"name": "report_date", "type": "string", "description": "Report generation date"},
    {"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"}
  ]'::jsonb
);

2. Alert Tracking Table

sql

CREATE TABLE tank_level_alerts (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  asset_id text NOT NULL,
  triggered_at timestamptz NOT NULL DEFAULT now(),
  resolved_at timestamptz,        -- Filled when tank refilled above threshold
  notified boolean DEFAULT false,
  created_at timestamptz DEFAULT now(),
  CONSTRAINT unique_active_alert UNIQUE (asset_id) WHERE (resolved_at IS NULL)
);

-- Enable RLS
ALTER TABLE tank_level_alerts ENABLE ROW LEVEL SECURITY;

-- Admin access
CREATE POLICY "Admins can manage tank alerts"
  ON tank_level_alerts
  FOR ALL
  USING (is_admin(auth.uid()))
  WITH CHECK (is_admin(auth.uid()));

-- Index for quick lookups
CREATE INDEX idx_tank_alerts_asset_active ON tank_level_alerts (asset_id) WHERE resolved_at IS NULL;

Email Template Structure

Full Email Layout

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 -->
  <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 level is below 15,000 litres and requires attention.
      </p>
    </td>
  </tr>

  <!-- Tank Card -->
  <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 Card Template (`{{summary_tank_level}}`)

html

<table width="100%" style="border: 1px solid #e2e8f0; border-radius: 8px; overflow: hidden;">
  <!-- Card Header: Site + Status Badge -->
  <tr>
    <td style="padding: 16px; background: #f8fafc; border-bottom: 1px solid #e2e8f0;">
      <table width="100%">
        <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">
            <span style="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%">
        <tr>
          <!-- Left: Tank Visualization -->
          <td width="120" valign="top">
            <!-- Simplified tank graphic -->
            <table width="80" style="margin: 0 auto;">
              <!-- Top ellipse -->
              <tr>
                <td style="background: #e2e8f0; height: 12px; border-radius: 40px 40px 0 0;"></td>
              </tr>
              <!-- Empty area -->
              <tr>
                <td style="background: #f1f5f9; height: {{empty_height}}px;"></td>
              </tr>
              <!-- Liquid area -->
              <tr>
                <td style="background: linear-gradient(180deg, #f97316, #ea580c); height: {{liquid_height}}px;"></td>
              </tr>
              <!-- Bottom ellipse -->
              <tr>
                <td style="background: #ea580c; height: 12px; border-radius: 0 0 40px 40px;"></td>
              </tr>
            </table>
            <!-- Percentage display -->
            <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%" 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="border-top: 1px solid #e2e8f0; padding-top: 12px; margin-top: 8px;"></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>

Dynamic Height Calculation

Total tank height: 100px
liquid_height = Math.round(percentRemaining)
empty_height = 100 - liquid_height

Example: 25% → liquid_height: 25px, empty_height: 75px

Color Scheme

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

Implementation Files

File	Action	Description
`supabase/migrations/2026XXXX_add_tank_level_alert.sql`	Create	Add variable definition + alert tracking table
`src/features/email-schedules/services/variableTemplateService.ts`	Modify	Add `DEFAULT_TANK_LEVEL_TEMPLATE` constant
`src/features/flow-meter/services/tankAlertService.ts`	Create	Alert detection, state management, email data generation
`src/features/flow-meter/types.ts`	Modify	Add `TankLevelAlertData` type
`supabase/functions/send-email/index.ts`	Modify	Add `{{summary_tank_level}}` variable replacement logic

Service API

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 (marks as triggered)
  async createAlert(assetId: string): 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 resolveReffilledTanks(thresholdLitres: number = 15000): Promise<string[]>;

  // Generate email data for a single tank
  async generateAlertEmailData(tank: TankLevel): Promise<TankLevelAlertData>;
};

Alert Logic Flow

1. Detect all tanks with currentLitres < 15000
2. For each low tank:
   a. Check if hasActiveAlert(assetId)
   b. If NO active alert:
      - createAlert(assetId)
      - generateAlertEmailData(tank)
      - Send individual email
   c. If YES active alert:
      - Skip (already notified)
3. Check tanks now above threshold:
   a. resolveRefilledTanks(15000)
   b. Resolved tanks can trigger alerts again in future

Future Considerations (Out of Scope)

Trigger mechanism (cron job, real-time, manual) - to be designed separately
Configurable threshold per tank/site
Email recipient configuration per tank/site
Dashboard UI for viewing alert history

Tank Level Low Alert Email Template Design ​

Overview ​

Requirements ​

Data Structure ​

TankLevelAlertData Type ​

Filter Logic ​

Database Changes ​

1. Variable Definition ​

2. Alert Tracking Table ​

Email Template Structure ​

Full Email Layout ​

Tank Card Template ({{summary_tank_level}}) ​

Dynamic Height Calculation ​

Color Scheme ​

Implementation Files ​

Service API ​

tankAlertService.ts ​

Alert Logic Flow ​

Future Considerations (Out of Scope) ​