Tank Alert Dual Threshold Implementation Plan

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

Goal: Add a 5,000L critical threshold alongside the existing 15,000L warning threshold, each triggering independent alert emails.

Architecture: Add alert_level column to tank_level_alerts table. Modify alert detection to run two passes (warning < 15,000L, critical < 5,000L). Email sending splits pending alerts by level and sends separate emails. Each level has independent lifecycle (create/notify/resolve).

Tech Stack: PostgreSQL migration, Deno Edge Functions, React/TypeScript, Vitest

Task 1: Database Migration — Add `alert_level` Column

Files:

Create: supabase/migrations/20260212000000_add_alert_level_column.sql

Step 1: Create the migration file

sql

-- Add alert_level column to tank_level_alerts
-- Supports dual threshold: 'warning' (< 15,000L) and 'critical' (< 5,000L)

-- 1. Add column with default for existing rows
ALTER TABLE tank_level_alerts
ADD COLUMN alert_level text NOT NULL DEFAULT 'warning';

-- 2. Drop old unique index (per asset only)
DROP INDEX IF EXISTS idx_tank_alerts_unique_active;

-- 3. Recreate unique index: per asset + per level
--    Allows one tank to have both a warning AND critical active alert
CREATE UNIQUE INDEX idx_tank_alerts_unique_active
  ON tank_level_alerts (asset_id, alert_level)
  WHERE resolved_at IS NULL;

-- 4. Add comment
COMMENT ON COLUMN tank_level_alerts.alert_level IS 'Alert severity level: warning (< 15000L) or critical (< 5000L)';

Step 2: Commit

bash

git add supabase/migrations/20260212000000_add_alert_level_column.sql
git commit -m "feat: add alert_level column to tank_level_alerts"

Task 2: Client-Side Types — Add `alertLevel` Field

Files:

Modify: src/features/flow-meter/types.ts (lines 194-203, 234-243)
Modify: src/features/tank-alerts/types.ts (lines 24-48)

Step 1: Add alertLevel to TankLevelAlert type

In src/features/flow-meter/types.ts, add alertLevel to the TankLevelAlert type:

typescript

export type TankLevelAlert = {
  id: string;
  assetId: string;
  triggeredAt: Date;
  resolvedAt?: Date;
  notified: boolean;
  createdAt: Date;
  /** Scheduled date for aggregated batch email (null = send immediately) */
  scheduledSendDate?: Date;
  /** Alert severity level */
  alertLevel: 'warning' | 'critical';
};

Step 2: Add alertLevel to TankLevelAlertData type

In src/features/flow-meter/types.ts, add alertLevel to TankLevelAlertData:

typescript

export type TankLevelAlertData = {
  /** Alert threshold in litres */
  thresholdLitres: number;
  /** Report generation date */
  reportDate: string;
  /** Number of tanks in this alert batch */
  tankCount: number;
  /** Array of tank data items */
  tanks: TankLevelAlertItem[];
  /** Alert severity level */
  alertLevel: 'warning' | 'critical';
};

Step 3: Add alertLevel to TankAlertListItem type

In src/features/tank-alerts/types.ts, add alertLevel to TankAlertListItem:

typescript

export type TankAlertListItem = {
  // Alert basic info
  id: string;
  assetId: string;
  triggeredAt: Date;
  scheduledSendDate?: Date;
  notified: boolean;
  resolvedAt?: Date;
  createdAt: Date;
  /** Alert severity level */
  alertLevel: 'warning' | 'critical';

  // Tank info (joined from data_assets and cfg_tank_capacities)
  assetDisplayId: string;
  siteName: string;

  // Real-time water level (calculated)
  currentLitres?: number;
  capacityLitres?: number;
  percentRemaining?: number;

  // Computed status
  status: AlertStatus;

  // Flag for deleted tanks
  tankDeleted?: boolean;
};

Step 4: Commit

bash

git add src/features/flow-meter/types.ts src/features/tank-alerts/types.ts
git commit -m "feat: add alertLevel field to tank alert types"

Task 3: Client-Side Service — Dual Threshold Alert Logic

Files:

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

Step 1: Add CRITICAL_THRESHOLD_LITRES constant and update TankLevelAlertRow

At the top of the file, after DEFAULT_THRESHOLD_LITRES:

typescript

/** Critical threshold in litres — tanks below this are critically low */
export const CRITICAL_THRESHOLD_LITRES = 5000;

Update TankLevelAlertRow interface to include alert_level:

typescript

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

Step 2: Update mapAlertRow to include alertLevel

typescript

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),
  scheduledSendDate: row.scheduled_send_date
    ? new Date(row.scheduled_send_date)
    : undefined,
  alertLevel: (row.alert_level === 'critical' ? 'critical' : 'warning') as 'warning' | 'critical',
});

Step 3: Update hasActiveAlert to accept optional alertLevel

typescript

export async function hasActiveAlert(
  assetId: string,
  alertLevel?: 'warning' | 'critical'
): Promise<boolean> {
  // eslint-disable-next-line @typescript-eslint/no-explicit-any
  let query = (supabase as any)
    .from("tank_level_alerts")
    .select("id")
    .eq("asset_id", assetId)
    .is("resolved_at", null)
    .limit(1);

  if (alertLevel) {
    query = query.eq("alert_level", alertLevel);
  }

  const { data, error } = await query;

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

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

Step 4: Update createAlert to accept alertLevel

typescript

export async function createAlert(
  assetId: string,
  scheduledSendDate?: Date,
  alertLevel: 'warning' | 'critical' = 'warning'
): Promise<TankLevelAlert> {
  const sendDate = scheduledSendDate ?? addDays(new Date(), 1);

  // eslint-disable-next-line @typescript-eslint/no-explicit-any
  const { data, error } = await (supabase as any)
    .from("tank_level_alerts")
    .insert([
      {
        asset_id: assetId,
        triggered_at: new Date().toISOString(),
        notified: false,
        scheduled_send_date: format(sendDate, "yyyy-MM-dd"),
        alert_level: alertLevel,
      },
    ])
    .select()
    .single();

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

  return mapAlertRow(data as TankLevelAlertRow);
}

Step 5: Update resolveAlert to accept optional alertLevel

typescript

export async function resolveAlert(
  assetId: string,
  alertLevel?: 'warning' | 'critical'
): Promise<void> {
  // eslint-disable-next-line @typescript-eslint/no-explicit-any
  let query = (supabase as any)
    .from("tank_level_alerts")
    .update({ resolved_at: new Date().toISOString() })
    .eq("asset_id", assetId)
    .is("resolved_at", null);

  if (alertLevel) {
    query = query.eq("alert_level", alertLevel);
  }

  const { error } = await query;

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

Step 6: Update processAlerts for dual threshold

typescript

export async function processAlerts(
  thresholdLitres: number = DEFAULT_THRESHOLD_LITRES,
  criticalThresholdLitres: number = CRITICAL_THRESHOLD_LITRES
): Promise<{
  newAlerts: number;
  resolvedAlerts: number;
}> {
  // Get current active alerts
  const activeAlerts = await getActiveAlerts();
  const warningAlertedIds = new Set(
    activeAlerts.filter(a => a.alertLevel === 'warning').map(a => a.assetId)
  );
  const criticalAlertedIds = new Set(
    activeAlerts.filter(a => a.alertLevel === 'critical').map(a => a.assetId)
  );

  // Detect low tanks
  const flowMeterAssets = await fetchFlowMeterAssets();
  const tankLevels = await calculateAllTankLevels(flowMeterAssets);

  let newAlerts = 0;

  // Pass 1: Warning alerts (< 15,000L)
  const warningTanks = filterLowTanks(tankLevels, thresholdLitres);
  for (const tank of warningTanks) {
    if (!warningAlertedIds.has(tank.assetId)) {
      await createAlert(tank.assetId, undefined, 'warning');
      newAlerts++;
    }
  }

  // Pass 2: Critical alerts (< 5,000L)
  const criticalTanks = filterLowTanks(tankLevels, criticalThresholdLitres);
  for (const tank of criticalTanks) {
    if (!criticalAlertedIds.has(tank.assetId)) {
      await createAlert(tank.assetId, undefined, 'critical');
      newAlerts++;
    }
  }

  // Resolve: warning at >= 15,000L, critical at >= 5,000L
  const resolvedWarning = await resolveRefilledTanks(thresholdLitres, 'warning');
  const resolvedCritical = await resolveRefilledTanks(criticalThresholdLitres, 'critical');

  return {
    newAlerts,
    resolvedAlerts: resolvedWarning.length + resolvedCritical.length,
  };
}

Step 7: Update resolveRefilledTanks to accept alertLevel

typescript

export async function resolveRefilledTanks(
  thresholdLitres: number = DEFAULT_THRESHOLD_LITRES,
  alertLevel?: 'warning' | 'critical'
): Promise<string[]> {
  const resolvedAssetIds: string[] = [];

  const activeAlerts = await getActiveAlerts();
  // Filter by alert level if specified
  const relevantAlerts = alertLevel
    ? activeAlerts.filter(a => a.alertLevel === alertLevel)
    : activeAlerts;

  if (relevantAlerts.length === 0) {
    return resolvedAssetIds;
  }

  const alertedAssetIds = new Set(relevantAlerts.map((a) => a.assetId));

  const flowMeterAssets = await fetchFlowMeterAssets();
  const tankLevels = await calculateAllTankLevels(flowMeterAssets);

  for (const tank of tankLevels) {
    if (!alertedAssetIds.has(tank.assetId)) {
      continue;
    }

    if (
      tank.currentRemainingLitres !== undefined &&
      tank.currentRemainingLitres >= thresholdLitres
    ) {
      await resolveAlert(tank.assetId, alertLevel);
      resolvedAssetIds.push(tank.assetId);
    }
  }

  return resolvedAssetIds;
}

Step 8: Update generateAggregatedAlertData to include alertLevel

In the return statement of generateAggregatedAlertData, add the alertLevel parameter:

typescript

export function generateAggregatedAlertData(
  alerts: TankLevelAlert[],
  tankLevels: TankLevel[],
  thresholdLitres: number = DEFAULT_THRESHOLD_LITRES,
  alertLevel: 'warning' | 'critical' = 'warning'
): TankLevelAlertData {
  // ... existing mapping logic unchanged ...

  return {
    thresholdLitres,
    reportDate: formatDateTimeForEmail(new Date()),
    tankCount: tanks.length,
    tanks,
    alertLevel,
  };
}

Also update generateAlertEmailData to pass through alertLevel:

typescript

export function generateAlertEmailData(
  tank: TankLevel,
  thresholdLitres: number = DEFAULT_THRESHOLD_LITRES,
  alertLevel: 'warning' | 'critical' = 'warning'
): TankLevelAlertData {
  const mockAlert: TankLevelAlert = {
    id: "single",
    assetId: tank.assetId,
    triggeredAt: new Date(),
    notified: false,
    createdAt: new Date(),
    alertLevel,
  };
  return generateAggregatedAlertData([mockAlert], [tank], thresholdLitres, alertLevel);
}

Step 9: Commit

bash

git add src/features/flow-meter/services/tankAlertService.ts
git commit -m "feat: add dual threshold support to tankAlertService"

Task 4: Edge Function — Dual Threshold Alert Detection

Files:

Modify: supabase/functions/process-tank-alerts/index.ts

Step 1: Add CRITICAL_THRESHOLD_LITRES constant and update ActiveAlertRow

After DEFAULT_THRESHOLD_LITRES = 15000 (line 29), add:

typescript

const CRITICAL_THRESHOLD_LITRES = 5000;

Update ActiveAlertRow type to include alert_level:

typescript

type ActiveAlertRow = {
  id: string;
  asset_id: string;
  notified: boolean;
  scheduled_send_date: string | null;
  alert_level: string;
};

Step 2: Update processAlerts function for dual threshold

Replace the processAlerts function. Key changes:

Accept criticalThresholdLitres parameter
Build activeAlertMap as Map<string, Map<string, ActiveAlertRow>> (asset_id → alert_level → row)
Select alert_level from active alerts query
Two-pass detection: warning (< 15,000L) and critical (< 5,000L)
Two-pass resolution: warning (≥ 15,000L) and critical (≥ 5,000L)
Insert alert_level when creating new alerts

typescript

async function processAlerts(
  client: SupabaseClient,
  thresholdLitres: number,
  criticalThresholdLitres: number = CRITICAL_THRESHOLD_LITRES
): Promise<{
  tanksChecked: number;
  newAlerts: number;
  resolvedAlerts: number;
  lowTanks: Array<{ assetId: string; site: string; currentLitres: number; capacityLitres: number; percentRemaining: number; alertLevel: string }>;
  resolvedTanks: string[];
  errors: string[];
}> {
  const errors: string[] = [];

  const tankLevels = await calculateAllTankLevels(client, thresholdLitres);
  console.log(`Calculated levels for ${tankLevels.length} tanks`);

  // Get all active (unresolved) alerts, including alert_level
  const { data: activeAlerts, error: alertsError } = await client
    .from("tank_level_alerts")
    .select("id, asset_id, notified, scheduled_send_date, alert_level")
    .is("resolved_at", null);

  if (alertsError) {
    throw new Error(`Failed to fetch active alerts: ${alertsError.message}`);
  }

  // Build nested map: asset_id → alert_level → AlertRow
  const activeAlertMap = new Map<string, Map<string, ActiveAlertRow>>();
  for (const alert of (activeAlerts ?? []) as ActiveAlertRow[]) {
    if (!activeAlertMap.has(alert.asset_id)) {
      activeAlertMap.set(alert.asset_id, new Map());
    }
    activeAlertMap.get(alert.asset_id)!.set(alert.alert_level || 'warning', alert);
  }

  console.log(`Found ${(activeAlerts ?? []).length} active alerts`);

  // Calculate tomorrow in AWST
  const now = new Date();
  const awstOffset = 8 * 60 * 60 * 1000;
  const awstNow = new Date(now.getTime() + awstOffset);
  const awstTomorrow = new Date(awstNow.getTime() + 24 * 60 * 60 * 1000);
  const tomorrowStr = awstTomorrow.toISOString().split("T")[0];

  let newAlerts = 0;
  const lowTanks: Array<{ assetId: string; site: string; currentLitres: number; capacityLitres: number; percentRemaining: number; alertLevel: string }> = [];

  // Define threshold levels to process
  const thresholdLevels = [
    { threshold: thresholdLitres, level: 'warning' },
    { threshold: criticalThresholdLitres, level: 'critical' },
  ];

  for (const tank of tankLevels) {
    if (!tank.hasCorrection) continue;

    for (const { threshold, level } of thresholdLevels) {
      if (tank.currentLitres < threshold) {
        lowTanks.push({
          assetId: tank.assetId,
          site: tank.site,
          currentLitres: tank.currentLitres,
          capacityLitres: tank.capacityLitres,
          percentRemaining: tank.percentRemaining,
          alertLevel: level,
        });

        const assetAlerts = activeAlertMap.get(tank.assetId);
        const hasExisting = assetAlerts?.has(level);

        if (!hasExisting) {
          try {
            const { error: insertError } = await client
              .from("tank_level_alerts")
              .insert({
                asset_id: tank.assetId,
                triggered_at: new Date().toISOString(),
                notified: false,
                scheduled_send_date: tomorrowStr,
                alert_level: level,
              });

            if (insertError) {
              if (insertError.code === "23505") {
                console.log(`Alert (${level}) already exists for asset ${tank.assetId}, skipping`);
              } else {
                console.error(`Failed to create ${level} alert for ${tank.assetId}:`, insertError);
                errors.push(`Failed to create ${level} alert for ${tank.assetId}: ${insertError.message}`);
              }
            } else {
              newAlerts++;
              console.log(`Created ${level} alert for ${tank.assetId} (${tank.site}) — ${tank.currentLitres}L / ${tank.capacityLitres}L (${tank.percentRemaining}%)`);
            }
          } catch (err) {
            const msg = err instanceof Error ? err.message : String(err);
            errors.push(`Error creating ${level} alert for ${tank.assetId}: ${msg}`);
          }
        }
      }
    }
  }

  // Resolve alerts for tanks above their respective thresholds
  let resolvedAlerts = 0;
  const resolvedTanks: string[] = [];

  for (const [assetId, levelMap] of activeAlertMap) {
    const tank = tankLevels.find((t) => t.assetId === assetId);
    if (!tank || !tank.hasCorrection) continue;

    for (const [level, alert] of levelMap) {
      const resolveThreshold = level === 'critical' ? criticalThresholdLitres : thresholdLitres;

      if (tank.currentLitres >= resolveThreshold) {
        try {
          const { error: resolveError } = await client
            .from("tank_level_alerts")
            .update({ resolved_at: new Date().toISOString() })
            .eq("id", alert.id);

          if (resolveError) {
            console.error(`Failed to resolve ${level} alert for ${assetId}:`, resolveError);
            errors.push(`Failed to resolve ${level} alert for ${assetId}: ${resolveError.message}`);
          } else {
            resolvedAlerts++;
            resolvedTanks.push(`${assetId}:${level}`);
            console.log(`Resolved ${level} alert for ${assetId} — now at ${tank.currentLitres}L (${tank.percentRemaining}%)`);
          }
        } catch (err) {
          const msg = err instanceof Error ? err.message : String(err);
          errors.push(`Error resolving ${level} alert for ${assetId}: ${msg}`);
        }
      }
    }
  }

  return {
    tanksChecked: tankLevels.length,
    newAlerts,
    resolvedAlerts,
    lowTanks,
    resolvedTanks,
    errors,
  };
}

Step 3: Update HTTP handler to pass critical_threshold_litres

In the serve handler, after parsing threshold_litres, add:

typescript

let criticalThresholdLitres = CRITICAL_THRESHOLD_LITRES;
// inside the try block where body is parsed:
if (body.critical_threshold_litres && typeof body.critical_threshold_litres === "number") {
  criticalThresholdLitres = body.critical_threshold_litres;
}

Update the processAlerts call:

typescript

const result = await processAlerts(client, thresholdLitres, criticalThresholdLitres);

Step 4: Commit

bash

git add supabase/functions/process-tank-alerts/index.ts
git commit -m "feat: dual threshold detection in process-tank-alerts Edge Function"

Task 5: Edge Function — Email Sending Split by Alert Level

Files:

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

Step 1: Update TankLevelAlertData type in send-email

At line ~485, add alert_level to the type:

typescript

type TankLevelAlertData = {
  threshold_litres: number;
  report_date: string;
  tank_count: number;
  tanks: TankLevelAlertItem[];
  alert_level: 'warning' | 'critical';
};

Step 2: Add CRITICAL_THRESHOLD_LITRES constant

After DEFAULT_TANK_THRESHOLD_LITRES = 15000 (line 1293), add:

typescript

const CRITICAL_TANK_THRESHOLD_LITRES = 5000;

Step 3: Refactor fetchTankLevelAlertData to accept alertLevel filter

Add alertLevel parameter and filter the query:

typescript

async function fetchTankLevelAlertData(
  client: SupabaseClient,
  thresholdLitres: number = DEFAULT_TANK_THRESHOLD_LITRES,
  alertLevel: 'warning' | 'critical' = 'warning'
): Promise<TankLevelAlertData | null> {

In the pending alerts query (line ~1362), add .eq("alert_level", alertLevel):

typescript

  const { data: pendingAlerts, error: alertsError } = await client
    .from("tank_level_alerts")
    .select("*")
    .eq("scheduled_send_date", todayStr)
    .is("resolved_at", null)
    .eq("notified", false)
    .eq("alert_level", alertLevel);

In the return statement (line ~1560), add alert_level:

typescript

  return {
    threshold_litres: thresholdLitres,
    report_date: formatDateForDisplay(awstDate),
    tank_count: tankItems.length,
    tanks: tankItems,
    alert_level: alertLevel,
  };

Step 4: Update email body text in formatTankAlertAsHTML

In formatTankAlertAsHTML (line ~1679), update the greeting paragraph to use level-specific wording:

typescript

function formatTankAlertAsHTML(data: TankLevelAlertData): string {
  if (data.tanks.length === 0) {
    return "";
  }

  const isWarning = data.alert_level === 'warning';
  const levelText = isWarning ? 'minimum threshold' : 'critical threshold';

Replace the paragraph at line ~1717:

typescript

        <p style="margin: 0 0 12px 0; font-size: 14px; color: #475569; line-height: 1.6;">
          Please be advised that ${data.tank_count} water tank(s) have been identified as falling below the ${levelText} level of ${data.threshold_litres.toLocaleString()} litres. Immediate attention is required to ensure continuous operations.
        </p>

Step 5: Update the cron email processing to send two emails

In the cron processing path (line ~2458), replace the single tank alert check with a loop over both levels:

typescript

      // Check if this is a tank level alert email
      const isTankLevelAlert = locked.metadata?.type === "tank_level_alert";
      let tankAlertDataList: TankLevelAlertData[] = [];

      if (isTankLevelAlert) {
        console.log("Processing tank level alert email");
        const warningThreshold = (locked.metadata?.threshold_litres as number) ?? DEFAULT_TANK_THRESHOLD_LITRES;
        const criticalThreshold = (locked.metadata?.critical_threshold_litres as number) ?? CRITICAL_TANK_THRESHOLD_LITRES;

        // Fetch warning alerts
        const warningData = await fetchTankLevelAlertData(client, warningThreshold, 'warning');
        if (warningData && warningData.tank_count > 0) {
          tankAlertDataList.push(warningData);
        }

        // Fetch critical alerts
        const criticalData = await fetchTankLevelAlertData(client, criticalThreshold, 'critical');
        if (criticalData && criticalData.tank_count > 0) {
          tankAlertDataList.push(criticalData);
        }

        if (tankAlertDataList.length === 0) {
          console.log("No tank alerts to send, skipping email");
          await updateScheduleStatus(client, locked.id, "scheduled");
          results.push({
            id: locked.id,
            status: "skipped",
            error: "No tank alerts to send",
          });
          continue;
        }
      }

Then in the email sending section, loop over tankAlertDataList and send one email per level. Each iteration calls replaceTemplateVariables with the corresponding tankAlertData and sends the email.

Step 6: Apply the same pattern to the manual send path (line ~2918)

Same logic: fetch both warning and critical data, send separate emails for each level that has pending alerts.

Step 7: Commit

bash

git add supabase/functions/send-email/index.ts
git commit -m "feat: split tank alert emails by alert level in send-email"

Task 6: Tank Alert Management Service — Add `alertLevel` to List Items

Files:

Modify: src/features/tank-alerts/services/tankAlertManagementService.ts

Step 1: Update TankLevelAlertRow to include alert_level

typescript

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

Step 2: Update getAllAlerts mapping to include alertLevel

In the items mapping (line ~183), add alertLevel:

typescript

      return {
        id: row.id,
        assetId: row.asset_id,
        triggeredAt: new Date(row.triggered_at),
        scheduledSendDate: row.scheduled_send_date
          ? new Date(row.scheduled_send_date)
          : undefined,
        notified: row.notified,
        resolvedAt: row.resolved_at ? new Date(row.resolved_at) : undefined,
        createdAt: new Date(row.created_at),
        alertLevel: (row.alert_level === 'critical' ? 'critical' : 'warning') as 'warning' | 'critical',
        assetDisplayId: asset?.display_id ?? row.asset_id,
        siteName: capacity?.site ?? "Unknown",
        currentLitres: tankLevel?.currentLitres,
        capacityLitres: tankLevel?.capacityLitres ?? capacity?.capacity_litres,
        percentRemaining: tankLevel?.percentRemaining,
        status: deriveStatus(row),
        tankDeleted: !asset,
      };

Step 3: Commit

bash

git add src/features/tank-alerts/services/tankAlertManagementService.ts
git commit -m "feat: include alertLevel in tank alert management service"

Task 7: UI — Show Alert Level Badge in Table

Files:

Modify: src/features/tank-alerts/components/TankAlertTable.tsx

Step 1: Add AlertLevelBadge component

After the StatusBadge component (line ~117), add:

typescript

function AlertLevelBadge({ level }: { level: 'warning' | 'critical' }) {
  if (level === 'critical') {
    return (
      <span className="inline-flex items-center gap-1 px-2 py-0.5 rounded-full text-xs font-medium bg-red-100 text-red-700 dark:bg-red-900/30 dark:text-red-400">
        Critical
      </span>
    );
  }
  return (
    <span className="inline-flex items-center gap-1 px-2 py-0.5 rounded-full text-xs font-medium bg-amber-100 text-amber-700 dark:bg-amber-900/30 dark:text-amber-400">
      Warning
    </span>
  );
}

Step 2: Add "Level" column header

In the <thead>, after the "Tank" column header (line ~216), add:

tsx

            <th className="px-4 py-4 text-left text-xs font-semibold text-gray-500 dark:text-gray-400 uppercase tracking-wider">
              Level
            </th>

Step 3: Add level badge cell in each row

In the <tbody> row, after the Tank name cell (line ~303), add:

tsx

              <td className="px-4 py-4">
                <AlertLevelBadge level={alert.alertLevel} />
              </td>

Step 4: Commit

bash

git add src/features/tank-alerts/components/TankAlertTable.tsx
git commit -m "feat: show alert level badge in tank alert table"

Task 8: Update Unit Tests

Files:

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

Step 1: Update createMockAlert helper to include alertLevel

typescript

function createMockAlert(
  overrides: Partial<TankLevelAlert> = {}
): TankLevelAlert {
  return {
    id: "alert-001",
    assetId: "tank-001",
    triggeredAt: new Date(2024, 2, 14, 10, 0, 0),
    notified: false,
    createdAt: new Date(2024, 2, 14, 10, 0, 0),
    alertLevel: 'warning',
    ...overrides,
  };
}

Step 2: Update imports to include CRITICAL_THRESHOLD_LITRES

typescript

import {
  formatDateTimeForEmail,
  calculateTankHeights,
  filterLowTanks,
  generateAlertEmailData,
  generateAggregatedAlertData,
  getPendingAlertsForDate,
  markAlertsAsNotified,
  DEFAULT_THRESHOLD_LITRES,
  CRITICAL_THRESHOLD_LITRES,
} from "./tankAlertService";

Step 3: Add test for CRITICAL_THRESHOLD_LITRES value

typescript

  describe("constants", () => {
    it("has correct threshold values", () => {
      expect(DEFAULT_THRESHOLD_LITRES).toBe(15000);
      expect(CRITICAL_THRESHOLD_LITRES).toBe(5000);
    });
  });

Step 4: Add test for filterLowTanks with critical threshold

typescript

    it("filters tanks below critical threshold (5000L)", () => {
      const tanks = [
        createMockTank({ assetId: "tank-1", currentRemainingLitres: 3000 }),
        createMockTank({ assetId: "tank-2", currentRemainingLitres: 8000 }),
        createMockTank({ assetId: "tank-3", currentRemainingLitres: 4500 }),
      ];

      const result = filterLowTanks(tanks, CRITICAL_THRESHOLD_LITRES);

      expect(result).toHaveLength(2);
      expect(result.map((t) => t.assetId)).toEqual(["tank-1", "tank-3"]);
    });

Step 5: Add test for generateAggregatedAlertData with alertLevel

typescript

    it("includes alertLevel in generated data", () => {
      const alerts = [createMockAlert({ assetId: "tank-1", alertLevel: 'critical' })];
      const tanks = [createMockTank({ assetId: "tank-1" })];

      const result = generateAggregatedAlertData(alerts, tanks, 5000, 'critical');

      expect(result.alertLevel).toBe('critical');
      expect(result.thresholdLitres).toBe(5000);
    });

    it("defaults alertLevel to warning", () => {
      const alerts = [createMockAlert({ assetId: "tank-1" })];
      const tanks = [createMockTank({ assetId: "tank-1" })];

      const result = generateAggregatedAlertData(alerts, tanks);

      expect(result.alertLevel).toBe('warning');
    });

Step 6: Update existing getPendingAlertsForDate mock data to include alert_level

In the mock data for getPendingAlertsForDate tests, add alert_level: "warning" to each mock alert row:

typescript

      const mockAlerts = [
        {
          id: "alert-1",
          asset_id: "tank-1",
          triggered_at: "2024-03-14T10:00:00Z",
          resolved_at: null,
          notified: false,
          created_at: "2024-03-14T10:00:00Z",
          scheduled_send_date: "2024-03-15",
          alert_level: "warning",
        },
        // ... same for alert-2
      ];

Step 7: Run tests to verify

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

Step 8: Commit

bash

git add src/features/flow-meter/services/tankAlertService.test.ts
git commit -m "test: update tank alert tests for dual threshold support"

Task 9: Push Migration to Remote Database

Step 1: Push the migration

Run: pnpm supabase:push Expected: Migration applies successfully, alert_level column added

Step 2: Regenerate TypeScript types

Run: pnpm supabase:types Expected: Types regenerated with alert_level field

Step 3: Verify build

Run: pnpm build Expected: Build succeeds with no TypeScript errors

Step 4: Commit any type changes

bash

git add src/lib/supabaseTypes.ts
git commit -m "chore: regenerate Supabase types with alert_level"

Task 10: Deploy Edge Functions

Step 1: Deploy process-tank-alerts

Run: pnpm functions:deploy or deploy individually via scripts

Step 2: Deploy send-email

Verify the send-email function is deployed with the updated dual-threshold logic.

Step 3: Final verification

Trigger a test alert processing run
Verify warning and critical alerts are created separately
Verify emails are sent independently per level

Tank Alert Dual Threshold Implementation Plan ​

Task 1: Database Migration — Add alert_level Column ​

Task 2: Client-Side Types — Add alertLevel Field ​

Task 3: Client-Side Service — Dual Threshold Alert Logic ​

Task 4: Edge Function — Dual Threshold Alert Detection ​

Task 5: Edge Function — Email Sending Split by Alert Level ​

Task 6: Tank Alert Management Service — Add alertLevel to List Items ​

Task 7: UI — Show Alert Level Badge in Table ​

Task 8: Update Unit Tests ​

Task 9: Push Migration to Remote Database ​

Task 10: Deploy Edge Functions ​

Tank Alert Dual Threshold Implementation Plan

Task 1: Database Migration — Add `alert_level` Column

Task 2: Client-Side Types — Add `alertLevel` Field

Task 3: Client-Side Service — Dual Threshold Alert Logic

Task 4: Edge Function — Dual Threshold Alert Detection

Task 5: Edge Function — Email Sending Split by Alert Level

Task 6: Tank Alert Management Service — Add `alertLevel` to List Items

Task 7: UI — Show Alert Level Badge in Table

Task 8: Update Unit Tests

Task 9: Push Migration to Remote Database

Task 10: Deploy Edge Functions