Expiry Alerts

Food Tracker (React + Express)

Chapter 6 · Expiry Alerts

Every item added in Chapter 5 now has a real row, with a real (or null) expiry_date. This chapter turns that stored date into something actually useful: a list of what needs to be used soon.

The Alerts Route

// routes/items.js (appended)
router.get("/alerts", (req, res) => {
  const items = db.prepare(`
    SELECT * FROM items
    WHERE status = 'active'
      AND expiry_date IS NOT NULL
      AND expiry_date <= date('now', '+3 days')
    ORDER BY expiry_date ASC
  `).all();
  res.json(items);
});

status = 'active' excludes anything already marked used (Chapter 9's own territory); expiry_date IS NOT NULL excludes items with no expiry date at all — both conditions matter, since a used item retains its historical row but no longer has a live expiry date to alert on. date('now', '+3 days') is SQLite's own built-in date arithmetic, computing "three days from today" directly inside the query.

This comparison only works because dates are stored as ISO 8601 text

SQLite has no dedicated date type — expiry_date is a TEXT column, and <= between two text values compares them lexicographically (character by character), not chronologically. This happens to give the correct chronological result here only because every date in this schema is consistently stored as YYYY-MM-DD — a format where lexicographic order and chronological order agree. Storing even one date in a different format (MM/DD/YYYY, for instance) would silently break every comparison in this route, with no error at all — just wrong results.

No composite index needed here — a real, honest contrast

Food Tracker (React + Firebase)'s own equivalent query needed a composite index, because Firestore requires one whenever a query filters on one field and orders by another simultaneously — exactly this route's own shape (WHERE status = ..., ORDER BY expiry_date). SQLite has no such requirement: a query like this one runs correctly with no index declared at all, and at this app's own realistic scale (a single household's pantry, at most a few hundred rows), a full table scan on every request is genuinely fast enough that adding an index wouldn't even be noticeable. This isn't SQL being "better" in general — Firestore's index requirement exists for good reasons at Firestore's own intended scale — but at this specific app's own size, it's a real, fair point in this course's favor, named honestly rather than glossed over.

A Custom Hook for the Dashboard

// hooks/useExpiryAlerts.js
import { useState, useEffect, useCallback } from "react";

function useExpiryAlerts() {
  const [alerts, setAlerts] = useState([]);
  const [loading, setLoading] = useState(true);

  const refresh = useCallback(async () => {
    setLoading(true);
    const response = await fetch("/api/items/alerts");
    setAlerts(await response.json());
    setLoading(false);
  }, []);

  useEffect(() => { refresh(); }, [refresh]);

  return { alerts, loading, refresh };
}

refresh is exposed deliberately, not just called once internally — Chapter 9's own "mark used" action needs a way to trigger a fresh alerts fetch immediately afterward, since an item marked used should disappear from this list right away, not just on the next full page reload.

The Dashboard Component

function ExpiryDashboard() {
  const { alerts, loading } = useExpiryAlerts();

  if (loading) return <p>Loading...</p>;
  if (alerts.length === 0) return <p>Nothing expiring soon.</p>;

  return (
    <ul>
      {alerts.map((item) => (
        <li key={item.id}>
          {item.name} — expires {item.expiry_date}
        </li>
      ))}
    </ul>
  );
}

Keep the "how many days is soon" threshold in one place

'+3 days' is hardcoded directly in the SQL string above for clarity in this chapter, but a real app should pull that number from one shared constant (an environment variable, or a config file) rather than repeating the literal string anywhere the query might be duplicated later — the same "define it once" discipline this course already applied to the accent color and schema definitions.

Where This Course Is Headed

Item history and live search-as-you-type next — a debounced search built as a custom React hook, hitting a new Express search endpoint.

Hands-On Exercises

Exercise 1

Explain why the alerts query's date comparison only works correctly because every date in this schema is stored in YYYY-MM-DD format, and what would happen if one date were stored in a different format.

📄 View solution

Exercise 2

Explain why this course's alerts query needs no composite index while the equivalent Firestore query in Food Tracker (React + Firebase) does, and why this isn't simply "SQL is better than Firestore" in general.

📄 View solution

Exercise 3

Explain why useExpiryAlerts exposes its own refresh function rather than only fetching once internally on mount.

📄 View solution

Chapter 6 Quick Reference

Route: GET /api/items/alerts — status='active' AND expiry_date IS NOT NULL AND expiry_date <= date('now', '+3 days')
Real gotcha: SQLite text-date comparison only works because every date uses YYYY-MM-DD consistently
Fair SQL advantage: no composite index needed at this app's realistic scale, unlike the Firebase sibling's equivalent query
useExpiryAlerts: a custom hook exposing alerts, loading, and a callable refresh (needed by Chapter 9's own mark-used action)
Next chapter: Item History & Live Search-as-You-Type