Skip to main content

Lot Expiration Alerts

Status: Approved Service: eli-kpi Related: ShipBob Fulfillment KPIs, ShipBob Integration Pattern: Same idempotent Slack notification pattern as App Store Review Notifier

Why This Exists​

Eli Health ships cortisol test kits from two ShipBob warehouses (Elwood IL and Brampton ON). Each kit is tracked by manufacturing lot number with an expiration date. If a lot sits in a warehouse past its expiration, those units cannot be shipped — they become waste.

Manufacturing follows FIFO (First In, First Out): the oldest lot should always ship first. But without proactive alerting, lots can be overlooked. This system alerts the team on Slack when a lot enters its 90-day expiration window, giving enough time to prioritize shipping, transfer between warehouses, or trigger Klaviyo campaigns to encourage customers to use their existing tests.

Example scenario:

  • Lot CB0016 has 28 units in Elwood (IL), expiring 2026-11-17
  • Today is 2026-08-19 (90 days before expiry)
  • Alert fires once: "Lot CB0016 expires in 90 days, 28 units still in Elwood"
  • Team contacts ShipBob or adjusts marketing to move those units

How It Works​

Step-by-Step Process​

  1. Cloud Scheduler fires POST /alerts/lot-expiration at 8:00 AM EST daily
  2. Query BigQuery for all lots with onhand_quantity > 0 and expiration_date within 90 days of today
  3. Read Slack channel history for #alerts-shipbob-inventory to find all previously posted lot alerts
  4. Extract identifiers from context blocks (pattern: lot_alert:CB0016:2026-11-17)
  5. Filter to only lots that haven't been posted yet
  6. Post each new expiring lot as a separate Slack message with Block Kit formatting
  7. Return summary: total expiring lots, already alerted, newly posted

Idempotent Deduplication​

This follows the same pattern as the App Store Review Notifier (appstore-notifier.ts): Slack channel history is the deduplication database.

Why Slack History Instead of a BigQuery State Table?​

ApproachProsCons
Slack history (chosen)No extra table, single source of truth, survives app restarts, same proven pattern as app review alertsSlightly slower (reads channel history), O(n) on channel size
BigQuery state tableFast lookup, structured dataExtra table to manage, can drift from Slack reality, dual source of truth

Since lot alerts are low volume (at most 5-10 lots ever), reading Slack channel history is fast and keeps the implementation consistent with existing patterns.

Deduplication Key​

Each Slack message embeds a unique identifier in its context block:

lot_alert:{lot_number}:{expiration_date}

Example: lot_alert:CB0016:2026-11-17

The key includes both lot number and expiration date because the same lot number can appear with different expiration dates (different manufacturing runs of the same batch ID).

Reading Previously Posted Alerts​

// Pattern: same as getPostedReviewIds() in appstore-notifier.ts
async function getPostedLotAlerts(): Promise<Set<string>> {
  const client = getSlackClient();
  const alertKeys = new Set<string>();

  let cursor: string | undefined;
  do {
    const result = await client.conversations.history({
      channel: LOT_ALERT_CHANNEL_ID,
      limit: 200,
      cursor,
    });

    for (const msg of result.messages || []) {
      if (msg.blocks) {
        for (const block of msg.blocks) {
          if (block.type === 'context' && block.elements) {
            for (const el of (block as any).elements) {
              const text = el.text || '';
              const match = text.match(/lot_alert:([^\s|]+)/);
              if (match) {
                alertKeys.add(match[1]);
              }
            }
          }
        }
      }
      // Fallback: check plain text
      if (msg.text) {
        const match = msg.text.match(/lot_alert:([^\s|]+)/);
        if (match) {
          alertKeys.add(match[1]);
        }
      }
    }

    cursor = result.response_metadata?.next_cursor;
  } while (cursor);

  return alertKeys;
}

BigQuery Query​

Query to find lots approaching expiration:

-- Lots expiring within 90 days that still have stock
SELECT
  i.id as inventory_id,
  JSON_VALUE(i.data, '$.name') as item_name,
  JSON_VALUE(lot, '$.lot_number') as lot_number,
  JSON_VALUE(lot, '$.expiration_date') as expiration_date,
  DATE_DIFF(
    DATE(SAFE.PARSE_TIMESTAMP(
      '%Y-%m-%dT%H:%M:%E*SZ',
      JSON_VALUE(lot, '$.expiration_date')
    )),
    CURRENT_DATE(),
    DAY
  ) as days_until_expiry,
  CAST(JSON_VALUE(lot, '$.onhand_quantity') AS INT64) as onhand_quantity,
  CAST(JSON_VALUE(lot, '$.fulfillable_quantity') AS INT64) as fulfillable_quantity,
  CAST(JSON_VALUE(lot, '$.committed_quantity') AS INT64) as committed_quantity,
  -- Which fulfillment centers hold this lot
  ARRAY(
    SELECT AS STRUCT
      JSON_VALUE(fc, '$.name') as fc_name,
      CAST(JSON_VALUE(fc, '$.onhand_quantity') AS INT64) as onhand,
      CAST(JSON_VALUE(fc, '$.fulfillable_quantity') AS INT64) as fulfillable
    FROM UNNEST(JSON_QUERY_ARRAY(
      lot, '$.fulfillable_quantity_by_fulfillment_center'
    )) as fc
    WHERE CAST(JSON_VALUE(fc, '$.onhand_quantity') AS INT64) > 0
  ) as locations_with_stock
FROM `eli-health-prod.eli_health_shipbob.inventory` i,
UNNEST(JSON_QUERY_ARRAY(i.data, '$.fulfillable_quantity_by_lot')) as lot
WHERE CAST(JSON_VALUE(i.data, '$.is_active') AS BOOL) = true
  AND CAST(JSON_VALUE(i.data, '$.is_lot') AS BOOL) = true
  AND CAST(JSON_VALUE(lot, '$.onhand_quantity') AS INT64) > 0
  -- Expiring within 90 days
  AND DATE_DIFF(
    DATE(SAFE.PARSE_TIMESTAMP(
      '%Y-%m-%dT%H:%M:%E*SZ',
      JSON_VALUE(lot, '$.expiration_date')
    )),
    CURRENT_DATE(),
    DAY
  ) <= 90
  -- Not already expired
  AND DATE(SAFE.PARSE_TIMESTAMP(
    '%Y-%m-%dT%H:%M:%E*SZ',
    JSON_VALUE(lot, '$.expiration_date')
  )) > CURRENT_DATE()
ORDER BY DATE(SAFE.PARSE_TIMESTAMP(
  '%Y-%m-%dT%H:%M:%E*SZ',
  JSON_VALUE(lot, '$.expiration_date')
)) ASC

SQL File: eli-kpi/src/sql/bigquery/shipbob/lot-expiration-alert.sql

Slack Message Format​

Each expiring lot gets its own message, formatted with Block Kit:

┌─────────────────────────────────────────────────┐
│ ⚠️ *Lot Expiration Warning*                     │
│                                                 │
│ *CB0016* — Cortisol Pack                        │
│ Expires *2026-11-17* (87 days remaining)        │
│                                                 │
│ 📦 *Stock:*                                     │
│   Elwood (IL): 28 units on hand                 │
│                                                 │
│ Action needed: prioritize shipping this lot     │
│ or transfer to a higher-volume location.        │
│                                                 │
│ [📊 View Inventory Dashboard]                   │
│                                                 │
│ lot_alert:CB0016:2026-11-17                     │
└─────────────────────────────────────────────────┘

Block Kit Structure​

function formatLotAlertBlocks(lot: ExpiringLot): any[] {
  const daysText = lot.days_until_expiry <= 30
    ? `🔴 *${lot.days_until_expiry} days remaining*`
    : lot.days_until_expiry <= 60
    ? `🟡 *${lot.days_until_expiry} days remaining*`
    : `⚠️ *${lot.days_until_expiry} days remaining*`;

  const locationLines = lot.locations_with_stock
    .map(fc => `  ${fc.fc_name}: ${fc.onhand} units on hand (${fc.fulfillable} fulfillable)`)
    .join('\n');

  const blocks: any[] = [
    {
      type: 'header',
      text: {
        type: 'plain_text',
        text: '⚠️ Lot Expiration Warning',
        emoji: true,
      },
    },
    {
      type: 'section',
      text: {
        type: 'mrkdwn',
        text: [
          `*${lot.lot_number}* — ${lot.item_name}`,
          `Expires *${formatDate(lot.expiration_date)}* — ${daysText}`,
        ].join('\n'),
      },
    },
    {
      type: 'section',
      text: {
        type: 'mrkdwn',
        text: `📦 *Stock by location:*\n${locationLines}`,
      },
    },
    {
      type: 'section',
      text: {
        type: 'mrkdwn',
        text: '_Action needed: prioritize shipping this lot or transfer to a higher-volume location._',
      },
    },
    {
      type: 'actions',
      elements: [
        {
          type: 'button',
          text: {
            type: 'plain_text',
            text: '📊 View Inventory Dashboard',
            emoji: true,
          },
          url: 'https://kpi.eli.health/fulfillment',
          action_id: `view_inventory_${lot.lot_number}`,
        },
      ],
    },
    // Context block with deduplication key
    {
      type: 'context',
      elements: [
        {
          type: 'mrkdwn',
          text: `${new Date().toLocaleDateString('en-US', {
            year: 'numeric', month: 'short', day: 'numeric',
          })} | lot_alert:${lot.lot_number}:${lot.expiration_date.split('T')[0]}`,
        },
      ],
    },
  ];

  return blocks;
}

Urgency Color Coding​

Days Until ExpiryIndicatorMeaning
61–90 days⚠️ YellowEarly warning — plan to ship
31–60 days🟡 AmberAttention needed — should be actively shipping
1–30 days🔴 RedCritical — must act immediately or lose stock

Module Design​

File Structure​

eli-kpi/src/
├── lot-expiration-notifier.ts   # NEW — alert logic
├── shipbob-routes.ts            # MODIFY — add alert endpoint
├── shipbob-sync.ts              # existing (no changes)
└── sql/bigquery/shipbob/
    └── lot-expiration-alert.sql # NEW — BigQuery query

TypeScript Interface​

export interface ExpiringLot {
  inventory_id: number;
  item_name: string;
  lot_number: string;
  expiration_date: string;      // ISO 8601
  days_until_expiry: number;
  onhand_quantity: number;
  fulfillable_quantity: number;
  committed_quantity: number;
  locations_with_stock: Array<{
    fc_name: string;
    onhand: number;
    fulfillable: number;
  }>;
}

export interface LotExpirationAlertResult {
  total_expiring_lots: number;   // lots matching the 90-day query
  already_alerted: number;       // lots already posted in Slack
  newly_posted: number;          // lots posted this run
  failed: number;                // lots that failed to post
  lot_details: Array<{
    lot_number: string;
    expiration_date: string;
    days_until_expiry: number;
    onhand_quantity: number;
    status: 'posted' | 'already_alerted' | 'failed';
  }>;
}

Main Function​

export async function checkLotExpirations(): Promise<LotExpirationAlertResult> {
  // 1. Query BigQuery for lots expiring within 90 days
  const expiringLots = await fetchExpiringLots();
  console.log(`[Lot Expiration] Found ${expiringLots.length} lots expiring within 90 days`);

  // 2. Read Slack channel history for previously posted alerts
  const postedAlerts = await getPostedLotAlerts();
  console.log(`[Lot Expiration] Found ${postedAlerts.size} lots already alerted in Slack`);

  // 3. Filter to new lots only
  const newLots = expiringLots.filter(lot => {
    const key = `${lot.lot_number}:${lot.expiration_date.split('T')[0]}`;
    return !postedAlerts.has(key);
  });
  console.log(`[Lot Expiration] ${newLots.length} new lots to alert`);

  if (newLots.length === 0) {
    return {
      total_expiring_lots: expiringLots.length,
      already_alerted: postedAlerts.size,
      newly_posted: 0,
      failed: 0,
      lot_details: expiringLots.map(lot => ({
        lot_number: lot.lot_number,
        expiration_date: lot.expiration_date,
        days_until_expiry: lot.days_until_expiry,
        onhand_quantity: lot.onhand_quantity,
        status: 'already_alerted' as const,
      })),
    };
  }

  // 4. Post new alerts (soonest expiry first)
  const sorted = [...newLots].sort(
    (a, b) => a.days_until_expiry - b.days_until_expiry
  );

  let posted = 0;
  let failed = 0;
  const details: LotExpirationAlertResult['lot_details'] = [];

  for (const lot of sorted) {
    const ok = await postLotAlert(lot);
    if (ok) {
      posted++;
      details.push({ ...lotSummary(lot), status: 'posted' });
    } else {
      failed++;
      details.push({ ...lotSummary(lot), status: 'failed' });
    }
    // Slack rate limit protection
    await new Promise(resolve => setTimeout(resolve, 500));
  }

  return {
    total_expiring_lots: expiringLots.length,
    already_alerted: postedAlerts.size,
    newly_posted: posted,
    failed,
    lot_details: details,
  };
}

API Endpoints​

Add to shipbob-routes.ts:

EndpointMethodDescriptionTrigger
/alerts/lot-expirationPOSTCheck for expiring lots and send Slack alertsCloud Scheduler (daily)
/alerts/lot-expiration/statusGETShow current expiring lots and alert historyManual / dashboard

Route Implementation​

// POST /alerts/lot-expiration — Check and alert on expiring lots
router.post('/alerts/lot-expiration', async (_req, res) => {
  try {
    console.log('Starting lot expiration check...');
    const result = await checkLotExpirations();
    res.json({ success: true, ...result });
  } catch (error: any) {
    console.error('Lot expiration alert error:', error);
    res.status(500).json({ success: false, message: error.message });
  }
});

// GET /alerts/lot-expiration/status — Current expiring lots
router.get('/alerts/lot-expiration/status', async (_req, res) => {
  try {
    const expiringLots = await fetchExpiringLots();
    const postedAlerts = await getPostedLotAlerts();
    res.json({
      success: true,
      expiring_lots: expiringLots.length,
      already_alerted: postedAlerts.size,
      lots: expiringLots.map(lot => ({
        ...lot,
        alert_status: postedAlerts.has(
          `${lot.lot_number}:${lot.expiration_date.split('T')[0]}`
        ) ? 'alerted' : 'pending',
      })),
    });
  } catch (error: any) {
    res.status(500).json({ success: false, message: error.message });
  }
});

Slack Channel Setup​

Channel Configuration​

SettingValue
Channel name#alerts-shipbob-inventory
Channel IDTBD (create channel, then update)
VisibilityPrivate (finance + operations team)
Boteli_health_alerts (same bot as other alert channels)

Required Bot Permissions​

The eli_health_alerts bot already has these scopes (used by App Store review alerts):

ScopePurpose
channels:history / groups:historyRead channel history for deduplication
chat:writePost alert messages
channels:read / groups:readList channels

No additional Slack configuration needed — same bot, same scopes, new channel.

Adding the Bot to the Channel​

After creating #alerts-shipbob-inventory:

# Invite the bot to the channel
/invite @eli_health_alerts

Or via API:

curl -X POST "https://slack.com/api/conversations.join" \
  -H "Authorization: Bearer $SLACK_BOT_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"channel": "CHANNEL_ID"}'

Cloud Scheduler​

Job Configuration​

SettingValue
Namelot-expiration-alert
Schedule0 13 * * * (8:00 AM EST = 13:00 UTC)
TimezoneAmerica/New_York
TargetPOST https://kpi.eli.health/alerts/lot-expiration
Timeout120s
Retries2
AuthOIDC token (KPI service account)

Terraform Configuration​

Add to eli-devops/tf/modules/regional/kpi-compute/main.tf alongside the existing ShipBob sync scheduler:

resource "google_cloud_scheduler_job" "lot_expiration_alert" {
  name        = "lot-expiration-alert"
  description = "Daily check for manufacturing lots approaching expiration"
  schedule    = "0 13 * * *"
  time_zone   = "America/New_York"
  region      = var.region

  http_target {
    http_method = "POST"
    uri         = "${google_cloud_run_v2_service.kpi.uri}/alerts/lot-expiration"

    oidc_token {
      service_account_email = google_service_account.kpi.email
    }
  }

  retry_config {
    retry_count          = 2
    min_backoff_duration = "30s"
    max_backoff_duration = "120s"
  }
}

Sync Timeline (all times EST)​

Time (EST)Time (UTC)Job
10:00 PM3:00 AMShipBob data sync (inventory, orders, etc.)
11:00 PM4:00 AMLoopwork sync
12:00 AM5:00 AMVidenglobe Ads sync
8:00 AM13:00 UTCLot expiration alert (NEW)

The alert runs 10 hours after the ShipBob sync, ensuring the inventory data is fresh from the overnight sync before checking expirations.

Edge Cases​

Lot number reused across manufacturing runs​

The same lot number (e.g., CB0016) can appear multiple times with different expiration dates — this happens when the same batch ID is used for a new manufacturing run. The deduplication key includes both lot number AND expiration date (lot_alert:CB0016:2026-11-17) to handle this correctly.

Lot stock drops to zero before alert fires​

If a lot is fully shipped before it enters the 90-day window, no alert is needed. The query filters on onhand_quantity > 0, so fully depleted lots are automatically excluded.

Lot expires while still in stock​

If a lot passes its expiration date with stock remaining, the query excludes it (filter: expiration_date > CURRENT_DATE()). A separate "expired stock" report could be added later to flag lots that expired with unsold inventory.

Slack channel doesn't exist yet​

The alert will fail with a channel_not_found error. The endpoint returns this as a 500 with a clear error message. The channel must be created and the bot invited before the first run.

ShipBob sync hasn't run yet​

If the eli_health_shipbob.inventory table is empty, the query returns zero rows and no alerts fire. The endpoint returns total_expiring_lots: 0.

Multiple lots expiring on the same day​

Each lot gets its own Slack message. There's a 500ms delay between posts to respect Slack rate limits. With at most 5-10 lots, this completes in under 5 seconds.

Testing Plan​

Manual Testing​

  1. Trigger manually: POST /alerts/lot-expiration via curl or dashboard
  2. Verify query: Run the SQL directly in BigQuery to confirm which lots match
  3. First run: All matching lots should post to Slack
  4. Second run (idempotent): Same lots should NOT re-post; response shows already_alerted
  5. Status check: GET /alerts/lot-expiration/status should show each lot's alert state

Test with Current Data​

As of January 2026, lot CB0016 (expiry 2026-11-17) has 28 units in Elwood (IL). This is approximately 290 days from expiry — outside the 90-day window. To test before a real lot enters the window:

# Option 1: Temporarily change the window from 90 to 365 days for testing
# Option 2: Wait until a lot naturally enters the 90-day window
# Option 3: Add a query parameter to override the window for testing:
curl -X POST "https://kpi.eli.health/alerts/lot-expiration?window_days=365"

The window_days query parameter (default: 90) allows testing without modifying production thresholds.

Implementation Checklist​

Phase 1: Core Module​

  • Create eli-kpi/src/sql/bigquery/shipbob/lot-expiration-alert.sql
  • Create eli-kpi/src/lot-expiration-notifier.ts
    • fetchExpiringLots() — BigQuery query
    • getPostedLotAlerts() — Slack channel history dedup
    • postLotAlert() — Block Kit formatted message
    • checkLotExpirations() — main orchestrator
  • Add ExpiringLot and LotExpirationAlertResult interfaces

Phase 2: Routes & Integration​

  • Add POST /alerts/lot-expiration to shipbob-routes.ts
  • Add GET /alerts/lot-expiration/status to shipbob-routes.ts
  • Import lot-expiration-notifier.ts in routes
  • Build and deploy to Cloud Run

Phase 3: Slack & Scheduler​

  • Create #alerts-shipbob-inventory Slack channel
  • Invite eli_health_alerts bot to the channel
  • Update channel ID constant in lot-expiration-notifier.ts
  • Add Cloud Scheduler job via Terraform
  • Test end-to-end with manual trigger

Phase 4: Validation​

  • Verify first run posts alerts for matching lots
  • Verify second run skips already-posted lots (idempotent)
  • Verify status endpoint shows correct state
  • Verify Cloud Scheduler fires at 8:00 AM EST
  • Monitor first week of automated alerts

Revision History​

DateAuthorChange
2026-01-30ChipInitial technical spec