Tank Level Low Alert Implementation Plan

For Claude: REQUIRED SUB-SKILL: Use superpowers:executing-plans to implement this plan task-by-task.

Goal: Implement {{summary_tank_level}} email template variable for low tank level alerts (< 15,000L).

Architecture: Create a new tankAlertService for alert state management, add the HTML template to variableTemplateService, create database migration for alert tracking, and integrate with send-email Edge Function.

Tech Stack: TypeScript, Supabase (PostgreSQL), Vitest for testing

Design Document: docs/plans/2026-02-06-tank-level-alert-email-design.md

Task 1: Database Migration - Alert Tracking Table & Variable Definition

Files:

Create: supabase/migrations/20260206000000_add_tank_level_alerts.sql

Step 1: Create the migration file

sql

-- Tank Level Alerts Migration
-- Creates alert tracking table and variable definition for low tank level emails

-- =============================================================================
-- Alert Tracking Table
-- =============================================================================
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,
  notified boolean DEFAULT false,
  created_at timestamptz DEFAULT now()
);

-- Partial unique index: only one active (unresolved) alert per asset
CREATE UNIQUE INDEX idx_tank_alerts_unique_active
  ON tank_level_alerts (asset_id)
  WHERE resolved_at IS NULL;

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

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

-- Admin-only 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()));

-- Authenticated users can read alerts
CREATE POLICY "Authenticated users can read tank alerts"
  ON tank_level_alerts
  FOR SELECT
  USING (auth.uid() IS NOT NULL);

-- =============================================================================
-- Variable Definition
-- =============================================================================
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
);

-- =============================================================================
-- Comments
-- =============================================================================
COMMENT ON TABLE tank_level_alerts IS 'Tracks low tank level alerts to prevent duplicate notifications';
COMMENT ON COLUMN tank_level_alerts.asset_id IS 'Flow meter asset ID that triggered the alert';
COMMENT ON COLUMN tank_level_alerts.triggered_at IS 'When the low level was first detected';
COMMENT ON COLUMN tank_level_alerts.resolved_at IS 'When tank was refilled above threshold (NULL = still active)';
COMMENT ON COLUMN tank_level_alerts.notified IS 'Whether email notification was sent';

Step 2: Verify migration syntax

Run: cd /Users/jackqin/Projects/Dashboard && cat supabase/migrations/20260206000000_add_tank_level_alerts.sql Expected: File contents displayed without syntax errors

Step 3: Commit

bash

git add supabase/migrations/20260206000000_add_tank_level_alerts.sql
git commit -m "feat(db): add tank level alerts table and variable definition"

Task 2: Add TypeScript Types

Files:

Modify: src/features/flow-meter/types.ts (append at end)

Step 1: Add TankLevelAlert and TankLevelAlertData types

Append to src/features/flow-meter/types.ts:

typescript


// =====================================================
// Tank Level Alert Types
// =====================================================

export type TankLevelAlert = {
  id: string;
  assetId: string;
  triggeredAt: Date;
  resolvedAt?: Date;
  notified: boolean;
  createdAt: Date;
};

export type TankLevelAlertData = {
  // Threshold
  thresholdLitres: number;
  reportDate: string;

  // Tank info
  assetId: string;
  assetDisplayId: string;
  siteName: string;

  // Level data
  currentLitres: number;
  capacityLitres: number;
  percentRemaining: number;

  // Timestamps
  lastCorrection: string;
  lastDispensing: string;

  // Computed for template
  liquidHeight: number;
  emptyHeight: number;
};

Step 2: Verify TypeScript compiles

Run: pnpm build:check Expected: Build succeeds without type errors

Step 3: Commit

bash

git add src/features/flow-meter/types.ts
git commit -m "feat(types): add TankLevelAlert and TankLevelAlertData types"

Task 3: Create Tank Alert Service - Core Functions

Files:

Create: src/features/flow-meter/services/tankAlertService.ts

Step 1: Create the service file with core functions

typescript

/**
 * Tank Alert Service
 * Handles low tank level alert detection, state management, and email data generation.
 */

import { supabase } from "@/lib/supabase";
import type { TankLevel, TankLevelAlert, TankLevelAlertData } from "../types";

// Default threshold in litres
export const DEFAULT_THRESHOLD_LITRES = 15000;

// Tank visualization height in pixels
const TANK_HEIGHT_PX = 100;

// =====================================================
// Type definitions for database rows
// =====================================================

interface TankLevelAlertRow {
  id: string;
  asset_id: string;
  triggered_at: string;
  resolved_at: string | null;
  notified: boolean;
  created_at: string;
}

// =====================================================
// Mapping functions
// =====================================================

const mapAlertRow = (row: TankLevelAlertRow): TankLevelAlert => ({
  id: row.id,
  assetId: row.asset_id,
  triggeredAt: new Date(row.triggered_at),
  resolvedAt: row.resolved_at ? new Date(row.resolved_at) : undefined,
  notified: row.notified,
  createdAt: new Date(row.created_at),
});

// =====================================================
// Helper functions
// =====================================================

/**
 * Format date for display in email (DD/MM/YYYY HH:MM)
 */
export function formatDateTimeForEmail(date: Date | undefined): string {
  if (!date) return "--";
  return date.toLocaleString("en-AU", {
    day: "2-digit",
    month: "2-digit",
    year: "numeric",
    hour: "2-digit",
    minute: "2-digit",
  });
}

/**
 * Calculate liquid and empty heights for tank visualization
 */
export function calculateTankHeights(percentRemaining: number): {
  liquidHeight: number;
  emptyHeight: number;
} {
  const clampedPercent = Math.max(0, Math.min(100, percentRemaining));
  const liquidHeight = Math.round(clampedPercent);
  const emptyHeight = TANK_HEIGHT_PX - liquidHeight;
  return { liquidHeight, emptyHeight };
}

// =====================================================
// Alert CRUD Operations
// =====================================================

/**
 * Check if an asset has an active (unresolved) alert
 */
export async function hasActiveAlert(assetId: string): Promise<boolean> {
  const { data, error } = await supabase
    .from("tank_level_alerts")
    .select("id")
    .eq("asset_id", assetId)
    .is("resolved_at", null)
    .limit(1);

  if (error) {
    console.error("Error checking active alert:", error);
    throw new Error(`Failed to check active alert: ${error.message}`);
  }

  return (data?.length ?? 0) > 0;
}

/**
 * Create a new alert record for an asset
 */
export async function createAlert(assetId: string): Promise<TankLevelAlert> {
  const { data, error } = await supabase
    .from("tank_level_alerts")
    .insert({ asset_id: assetId })
    .select()
    .single();

  if (error) {
    console.error("Error creating alert:", error);
    throw new Error(`Failed to create alert: ${error.message}`);
  }

  return mapAlertRow(data as TankLevelAlertRow);
}

/**
 * Mark an alert as notified
 */
export async function markAlertNotified(alertId: string): Promise<void> {
  const { error } = await supabase
    .from("tank_level_alerts")
    .update({ notified: true })
    .eq("id", alertId);

  if (error) {
    console.error("Error marking alert notified:", error);
    throw new Error(`Failed to mark alert notified: ${error.message}`);
  }
}

/**
 * Resolve an alert (when tank is refilled above threshold)
 */
export async function resolveAlert(assetId: string): Promise<void> {
  const { error } = await supabase
    .from("tank_level_alerts")
    .update({ resolved_at: new Date().toISOString() })
    .eq("asset_id", assetId)
    .is("resolved_at", null);

  if (error) {
    console.error("Error resolving alert:", error);
    throw new Error(`Failed to resolve alert: ${error.message}`);
  }
}

/**
 * Get all active (unresolved) alerts
 */
export async function getActiveAlerts(): Promise<TankLevelAlert[]> {
  const { data, error } = await supabase
    .from("tank_level_alerts")
    .select("*")
    .is("resolved_at", null)
    .order("triggered_at", { ascending: false });

  if (error) {
    console.error("Error fetching active alerts:", error);
    throw new Error(`Failed to fetch active alerts: ${error.message}`);
  }

  return ((data as TankLevelAlertRow[]) ?? []).map(mapAlertRow);
}

// =====================================================
// Alert Detection & Email Data Generation
// =====================================================

/**
 * Filter tanks that are below the threshold
 */
export function filterLowTanks(
  tanks: TankLevel[],
  thresholdLitres: number = DEFAULT_THRESHOLD_LITRES
): TankLevel[] {
  return tanks.filter(
    (tank) =>
      tank.currentRemainingLitres !== undefined &&
      tank.currentRemainingLitres < thresholdLitres &&
      tank.hasCorrectionRecord &&
      tank.hasCapacityConfig
  );
}

/**
 * Generate email alert data for a single tank
 */
export function generateAlertEmailData(
  tank: TankLevel,
  thresholdLitres: number = DEFAULT_THRESHOLD_LITRES
): TankLevelAlertData {
  const percentRemaining = tank.percentRemaining ?? 0;
  const { liquidHeight, emptyHeight } = calculateTankHeights(percentRemaining);

  return {
    thresholdLitres,
    reportDate: formatDateTimeForEmail(new Date()),
    assetId: tank.assetId,
    assetDisplayId: tank.assetDisplayId,
    siteName: tank.site,
    currentLitres: Math.round(tank.currentRemainingLitres ?? 0),
    capacityLitres: Math.round(tank.capacityLitres ?? 0),
    percentRemaining: Math.round(percentRemaining * 10) / 10,
    lastCorrection: formatDateTimeForEmail(
      tank.latestCorrection?.correctionDatetime
    ),
    lastDispensing: formatDateTimeForEmail(tank.lastDispensing),
    liquidHeight,
    emptyHeight,
  };
}

/**
 * Check tanks above threshold and resolve their alerts
 */
export async function resolveRefilledTanks(
  tanks: TankLevel[],
  thresholdLitres: number = DEFAULT_THRESHOLD_LITRES
): Promise<string[]> {
  const activeAlerts = await getActiveAlerts();
  const resolvedAssetIds: string[] = [];

  for (const alert of activeAlerts) {
    const tank = tanks.find((t) => t.assetId === alert.assetId);

    // Resolve if tank is now above threshold or tank no longer exists
    if (
      !tank ||
      (tank.currentRemainingLitres !== undefined &&
        tank.currentRemainingLitres >= thresholdLitres)
    ) {
      await resolveAlert(alert.assetId);
      resolvedAssetIds.push(alert.assetId);
    }
  }

  return resolvedAssetIds;
}

Step 2: Verify TypeScript compiles

Run: pnpm build:check Expected: Build succeeds without type errors

Step 3: Commit

bash

git add src/features/flow-meter/services/tankAlertService.ts
git commit -m "feat(service): add tankAlertService with core alert functions"

Task 4: Add Unit Tests for Tank Alert Service

Files:

Create: src/features/flow-meter/services/tankAlertService.test.ts

Step 1: Write tests for pure functions

typescript

import { describe, it, expect } from "vitest";
import {
  formatDateTimeForEmail,
  calculateTankHeights,
  filterLowTanks,
  generateAlertEmailData,
  DEFAULT_THRESHOLD_LITRES,
} from "./tankAlertService";
import type { TankLevel } from "../types";

describe("tankAlertService", () => {
  describe("formatDateTimeForEmail", () => {
    it("should return '--' for undefined date", () => {
      expect(formatDateTimeForEmail(undefined)).toBe("--");
    });

    it("should format date in AU locale", () => {
      const date = new Date("2026-02-06T14:30:00");
      const result = formatDateTimeForEmail(date);
      // Format: DD/MM/YYYY, HH:MM
      expect(result).toMatch(/\d{2}\/\d{2}\/\d{4}/);
    });
  });

  describe("calculateTankHeights", () => {
    it("should calculate heights for 25%", () => {
      const result = calculateTankHeights(25);
      expect(result.liquidHeight).toBe(25);
      expect(result.emptyHeight).toBe(75);
    });

    it("should calculate heights for 0%", () => {
      const result = calculateTankHeights(0);
      expect(result.liquidHeight).toBe(0);
      expect(result.emptyHeight).toBe(100);
    });

    it("should calculate heights for 100%", () => {
      const result = calculateTankHeights(100);
      expect(result.liquidHeight).toBe(100);
      expect(result.emptyHeight).toBe(0);
    });

    it("should clamp negative values to 0", () => {
      const result = calculateTankHeights(-10);
      expect(result.liquidHeight).toBe(0);
      expect(result.emptyHeight).toBe(100);
    });

    it("should clamp values over 100 to 100", () => {
      const result = calculateTankHeights(150);
      expect(result.liquidHeight).toBe(100);
      expect(result.emptyHeight).toBe(0);
    });
  });

  describe("filterLowTanks", () => {
    const createMockTank = (
      assetId: string,
      currentLitres: number | undefined,
      hasCorrection = true,
      hasCapacity = true
    ): TankLevel => ({
      assetId,
      assetDisplayId: `Tank ${assetId}`,
      site: "Test Site",
      hasCapacityConfig: hasCapacity,
      hasCorrectionRecord: hasCorrection,
      currentRemainingLitres: currentLitres,
      refillsSinceCorrection: 0,
      usageSinceCorrection: 0,
      status: "normal",
    });

    it("should filter tanks below threshold", () => {
      const tanks = [
        createMockTank("A", 10000),
        createMockTank("B", 20000),
        createMockTank("C", 14000),
      ];

      const result = filterLowTanks(tanks, 15000);

      expect(result).toHaveLength(2);
      expect(result.map((t) => t.assetId)).toEqual(["A", "C"]);
    });

    it("should exclude tanks without correction record", () => {
      const tanks = [createMockTank("A", 10000, false, true)];

      const result = filterLowTanks(tanks, 15000);

      expect(result).toHaveLength(0);
    });

    it("should exclude tanks without capacity config", () => {
      const tanks = [createMockTank("A", 10000, true, false)];

      const result = filterLowTanks(tanks, 15000);

      expect(result).toHaveLength(0);
    });

    it("should exclude tanks with undefined currentRemainingLitres", () => {
      const tanks = [createMockTank("A", undefined)];

      const result = filterLowTanks(tanks, 15000);

      expect(result).toHaveLength(0);
    });

    it("should use default threshold when not specified", () => {
      const tanks = [
        createMockTank("A", DEFAULT_THRESHOLD_LITRES - 1),
        createMockTank("B", DEFAULT_THRESHOLD_LITRES),
      ];

      const result = filterLowTanks(tanks);

      expect(result).toHaveLength(1);
      expect(result[0].assetId).toBe("A");
    });
  });

  describe("generateAlertEmailData", () => {
    const mockTank: TankLevel = {
      assetId: "asset-123",
      assetDisplayId: "Flow Meter 1",
      site: "Test Mine",
      mineSiteId: "site-1",
      capacityLitres: 50000,
      hasCapacityConfig: true,
      hasCorrectionRecord: true,
      currentRemainingLitres: 12500,
      refillsSinceCorrection: 1000,
      usageSinceCorrection: 500,
      percentRemaining: 25,
      status: "low",
      latestCorrection: {
        id: "corr-1",
        site: "Test Mine",
        assetId: "asset-123",
        assetDisplayId: "Flow Meter 1",
        correctionDatetime: new Date("2026-02-01T10:00:00"),
        remainingLitres: 12000,
      },
      lastDispensing: new Date("2026-02-05T15:30:00"),
    };

    it("should generate correct email data", () => {
      const result = generateAlertEmailData(mockTank, 15000);

      expect(result.thresholdLitres).toBe(15000);
      expect(result.assetId).toBe("asset-123");
      expect(result.assetDisplayId).toBe("Flow Meter 1");
      expect(result.siteName).toBe("Test Mine");
      expect(result.currentLitres).toBe(12500);
      expect(result.capacityLitres).toBe(50000);
      expect(result.percentRemaining).toBe(25);
      expect(result.liquidHeight).toBe(25);
      expect(result.emptyHeight).toBe(75);
    });

    it("should handle missing optional fields", () => {
      const minimalTank: TankLevel = {
        assetId: "asset-456",
        assetDisplayId: "Tank 2",
        site: "Site B",
        hasCapacityConfig: true,
        hasCorrectionRecord: true,
        currentRemainingLitres: 5000,
        refillsSinceCorrection: 0,
        usageSinceCorrection: 0,
        percentRemaining: 10,
        status: "low",
      };

      const result = generateAlertEmailData(minimalTank);

      expect(result.lastCorrection).toBe("--");
      expect(result.lastDispensing).toBe("--");
    });
  });
});

Step 2: Run tests to verify they pass

Run: pnpm test:unit src/features/flow-meter/services/tankAlertService.test.ts Expected: All tests pass

Step 3: Commit

bash

git add src/features/flow-meter/services/tankAlertService.test.ts
git commit -m "test(service): add unit tests for tankAlertService"

Task 5: Add Default Tank Level Template

Files:

Modify: src/features/email-schedules/services/variableTemplateService.ts

Step 1: Add DEFAULT_TANK_LEVEL_TEMPLATE constant

Add after DEFAULT_FLOW_METER_TEMPLATE (around line 47):

typescript


/**
 * Default HTML template for tank level alert variable
 */
export const DEFAULT_TANK_LEVEL_TEMPLATE = `<table width="100%" style="border: 1px solid #e2e8f0; border-radius: 8px; overflow: hidden; font-family: 'Helvetica Neue', Arial, sans-serif;">
  <!-- 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;">
              <!-- 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: #f97316; 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%" 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>`;

Step 2: Verify TypeScript compiles

Run: pnpm build:check Expected: Build succeeds

Step 3: Commit

bash

git add src/features/email-schedules/services/variableTemplateService.ts
git commit -m "feat(template): add DEFAULT_TANK_LEVEL_TEMPLATE for low level alerts"

Task 6: Add Tank Level Template Rendering to Send-Email Function

Files:

Modify: supabase/functions/send-email/index.ts

Step 1: Add TankLevelAlertData type definition

Add after FlowMeterSummary type (around line 461):

typescript


type TankLevelAlertData = {
  thresholdLitres: number;
  reportDate: string;
  assetId: string;
  assetDisplayId: string;
  siteName: string;
  currentLitres: number;
  capacityLitres: number;
  percentRemaining: number;
  lastCorrection: string;
  lastDispensing: string;
  liquidHeight: number;
  emptyHeight: number;
};

Step 2: Add formatTankLevelAsHTML function

Add after formatSummariesAsHTML function (around line 1021):

typescript


/**
 * Format tank level alert data as HTML for email body
 */
function formatTankLevelAsHTML(data: TankLevelAlertData): string {
  return `<table width="100%" style="border: 1px solid #e2e8f0; border-radius: 8px; overflow: hidden; font-family: 'Helvetica Neue', Arial, sans-serif;">
  <!-- 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;">📍 ${escapeHtml(data.siteName)}</p>
            <p style="margin: 0; color: #1e293b; font-size: 18px; font-weight: 700;">${escapeHtml(data.assetDisplayId)}</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;">
              <!-- 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: ${data.emptyHeight}px;"></td>
              </tr>
              <!-- Liquid area -->
              <tr>
                <td style="background: #f97316; height: ${data.liquidHeight}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;">
              ${data.percentRemaining}%
            </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;">${data.currentLitres.toLocaleString()} L</td>
              </tr>
              <tr>
                <td style="color: #64748b; padding: 8px 0;">Capacity</td>
                <td align="right" style="color: #1e293b; font-family: monospace;">${data.capacityLitres.toLocaleString()} 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;">${escapeHtml(data.lastCorrection)}</td>
              </tr>
              <tr>
                <td style="color: #64748b; padding: 8px 0;">Last Dispensing</td>
                <td align="right" style="color: #1e293b;">${escapeHtml(data.lastDispensing)}</td>
              </tr>
            </table>
          </td>
        </tr>
      </table>
    </td>
  </tr>
</table>`;
}

Step 3: Update replaceTemplateVariables function

Find the replaceTemplateVariables function and add tank level handling. Add after the {{date_range_flow_meter}} replacement block:

typescript

  // Replace {{summary_tank_level}} with formatted HTML tank card
  // This expects tankLevelData to be passed in metadata
  if (result.includes("{{summary_tank_level}}")) {
    // Tank level data should be passed via schedule metadata
    // For now, leave placeholder - will be replaced by caller
    console.log("Found {{summary_tank_level}} placeholder - requires tank data");
  }

Step 4: Commit

bash

git add supabase/functions/send-email/index.ts
git commit -m "feat(edge-fn): add tank level alert HTML rendering to send-email"

Task 7: Export Service Functions

Files:

Create: src/features/flow-meter/services/index.ts (if not exists)
Or modify existing index file

Step 1: Create or update service index

typescript

// Re-export tank alert service functions
export {
  DEFAULT_THRESHOLD_LITRES,
  hasActiveAlert,
  createAlert,
  markAlertNotified,
  resolveAlert,
  getActiveAlerts,
  filterLowTanks,
  generateAlertEmailData,
  resolveRefilledTanks,
  formatDateTimeForEmail,
  calculateTankHeights,
} from "./tankAlertService";

// Re-export tank service functions
export {
  calculateTankRemaining,
  getTankLevelType,
  fetchAllCorrections,
  fetchLatestCorrectionForAsset,
  insertCorrection,
  updateCorrection,
  deleteCorrection,
  fetchAllCapacities,
  fetchCapacityForAsset,
  upsertCapacity,
  deleteCapacity,
  calculateAllTankLevels,
  uploadCorrectionEvidence,
} from "./tankService";

Step 2: Verify imports work

Run: pnpm build:check Expected: Build succeeds

Step 3: Commit

bash

git add src/features/flow-meter/services/index.ts
git commit -m "feat(exports): add service index with tank alert exports"

Task 8: Final Integration Test

Step 1: Run all unit tests

Run: pnpm test:unit Expected: All tests pass

Step 2: Run TypeScript check

Run: pnpm build:check Expected: Build succeeds without errors

Step 3: Run linter

Run: pnpm lint Expected: No new lint errors

Step 4: Final commit with all changes

bash

git status
# Verify all files are committed

Summary

Task	Description	Files
1	Database migration	`supabase/migrations/20260206000000_add_tank_level_alerts.sql`
2	TypeScript types	`src/features/flow-meter/types.ts`
3	Tank alert service	`src/features/flow-meter/services/tankAlertService.ts`
4	Unit tests	`src/features/flow-meter/services/tankAlertService.test.ts`
5	Default template	`src/features/email-schedules/services/variableTemplateService.ts`
6	Edge function	`supabase/functions/send-email/index.ts`
7	Service exports	`src/features/flow-meter/services/index.ts`
8	Integration test	N/A (verification only)

Out of Scope (Future Tasks)

Trigger mechanism (cron job / real-time / manual button)
Admin UI for configuring recipients per tank
Alert history dashboard
Configurable threshold per tank/site

Tank Level Low Alert Implementation Plan ​

Task 1: Database Migration - Alert Tracking Table & Variable Definition ​

Task 2: Add TypeScript Types ​

Task 3: Create Tank Alert Service - Core Functions ​

Task 4: Add Unit Tests for Tank Alert Service ​

Task 5: Add Default Tank Level Template ​

Task 6: Add Tank Level Template Rendering to Send-Email Function ​

Task 7: Export Service Functions ​

Task 8: Final Integration Test ​

Summary ​

Out of Scope (Future Tasks) ​

Tank Level Low Alert Implementation Plan

Task 1: Database Migration - Alert Tracking Table & Variable Definition

Task 2: Add TypeScript Types

Task 3: Create Tank Alert Service - Core Functions

Task 4: Add Unit Tests for Tank Alert Service

Task 5: Add Default Tank Level Template

Task 6: Add Tank Level Template Rendering to Send-Email Function

Task 7: Export Service Functions

Task 8: Final Integration Test

Summary

Out of Scope (Future Tasks)