{{ site.title }}

{{ theme.darkModeSwitchLabel || 'Appearance' }}

Tank Alert Dual Threshold Design ​

Overview ​

Add a second alert threshold at 5,000L (Critical) alongside the existing 15,000L (Warning). Each threshold triggers an independent email notification with its own lifecycle.

Requirements ​

  • Warning: tank < 15,000L → send warning email
  • Critical: tank < 5,000L → send critical email
  • Each level triggers independently (two separate emails at two different time points)
  • Each level resolves independently (warning resolves at ≥ 15,000L, critical resolves at ≥ 5,000L)
  • Same recipient list for both levels
  • Each level only notifies once per alert cycle (no repeated emails until resolved and re-triggered)
  • Email title and styling are the same for both levels; only the body text and threshold number differ

Database Changes ​

New migration on tank_level_alerts table:

sql
-- Add alert_level column
ALTER TABLE tank_level_alerts
ADD COLUMN alert_level text NOT NULL DEFAULT 'warning';

-- Backfill existing data
UPDATE tank_level_alerts SET alert_level = 'warning' WHERE alert_level = 'warning';

-- Drop old unique index (per asset only)
DROP INDEX idx_tank_alerts_unique_active;

-- Recreate unique index: per asset + per level
CREATE UNIQUE INDEX idx_tank_alerts_unique_active
  ON tank_level_alerts (asset_id, alert_level)
  WHERE resolved_at IS NULL;

This allows one tank to have both a warning and critical active alert simultaneously.

Alert Detection (process-tank-alerts Edge Function) ​

Constants:

  • DEFAULT_THRESHOLD_LITRES = 15000 (warning, unchanged)
  • CRITICAL_THRESHOLD_LITRES = 5000 (new)

Processing flow:

  1. Calculate all tank levels (unchanged)
  2. Pass 1: check < 15,000L → create alert_level = 'warning' if no active warning exists
  3. Pass 2: check < 5,000L → create alert_level = 'critical' if no active critical exists
  4. Resolve pass 1: ≥ 15,000L → resolve warning alerts
  5. Resolve pass 2: ≥ 5,000L → resolve critical alerts

activeAlertMap changes from Map<string, AlertRow> to Map<string, Map<string, AlertRow>> (outer key: asset_id, inner key: alert_level).

Request body optionally accepts critical_threshold_litres (default 5000).

Email Sending (send-email Edge Function) ​

fetchTankLevelAlertData changes:

  1. Query pending alerts for today, grouped by alert_level
  2. If warning pending alerts exist → generate warning email data
  3. If critical pending alerts exist → generate critical email data
  4. Send two separate emails if both levels have pending alerts
  5. Each email marks its own alerts as notified = true

TankLevelAlertData type adds alert_level: 'warning' | 'critical'.

Email content differences:

  • Warning body: "...falling below the minimum threshold level of 15,000 litres"
  • Critical body: "...falling below the critical threshold level of 5,000 litres"
  • Key Summary "Alert Threshold" card shows the corresponding threshold number
  • All other structure, colors, layout remain identical

Client-Side Changes ​

Types (src/features/flow-meter/types.ts) ​

  • TankLevelAlert adds alertLevel: 'warning' | 'critical'
  • TankLevelAlertData adds alertLevel: 'warning' | 'critical'

tankAlertService.ts ​

  • New constant CRITICAL_THRESHOLD_LITRES = 5000
  • createAlert(assetId, scheduledSendDate?, alertLevel?) — writes alert_level column
  • hasActiveAlert(assetId, alertLevel?) — optional level filter
  • processAlerts() — two-pass detection (warning + critical)
  • resolveRefilledTanks() — resolve by level (warning at ≥ 15,000L, critical at ≥ 5,000L)
  • getActiveAlerts() — returns alerts with alertLevel field populated

tankAlertService.ts (database row mapping) ​

  • TankLevelAlertRow adds alert_level: string
  • mapAlertRow maps alert_level → alertLevel

Tank Alert Management UI ​

  • Alert list shows level badge (Warning / Critical) per row

Cron Script ​

No changes needed. Both thresholds are handled internally by the Edge Function.

Files to Modify ​

  1. supabase/migrations/new — new migration for alert_level column
  2. supabase/functions/process-tank-alerts/index.ts — dual threshold detection
  3. supabase/functions/send-email/index.ts — split emails by level
  4. src/features/flow-meter/types.ts — type additions
  5. src/features/flow-meter/services/tankAlertService.ts — dual threshold logic
  6. src/features/tank-alerts/components/TankAlertManagement.tsx — level badge display
  7. src/features/tank-alerts/types.ts — if separate types exist
  8. src/features/flow-meter/services/tankAlertService.test.ts — update tests