Expiry Alerts

Food Tracker (FastAPI)

Chapter 6 · Expiry Alerts

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

The Alerts Query

# routers/items.py
from datetime import date, timedelta

@router.get("/alerts", response_model=list[schemas.ItemResponse])
def get_alerts(db: Session = Depends(get_db)):
    threshold = date.today() + timedelta(days=3)
    return (
        db.query(models.Item)
        .filter(models.Item.status == "active")
        .filter(models.Item.expiry_date.isnot(None))
        .filter(models.Item.expiry_date <= threshold)
        .order_by(models.Item.expiry_date)
        .all()
    )

status == "active" excludes anything already marked used (Chapter 8's own territory); expiry_date.isnot(None) excludes items with no expiry date at all — the same two conditions every sibling course's own alerts query applies.

A real advantage of a typed column, paid off here

Food Tracker (React + Express)'s own equivalent route had to warn explicitly that its date comparison only worked correctly because every date was consistently stored as YYYY-MM-DD text — SQLite has no dedicated date type, so that course's own expiry_date <= date('now', '+3 days') was comparing strings lexicographically, and would have silently broken if even one date were ever stored in a different format. Chapter 2's decision to model expiry_date as a real SQLAlchemy Date column, not a raw string, removes that entire class of bug here: models.Item.expiry_date <= threshold compares real date values, correctly, regardless of how any particular database driver happens to store them internally. This is a genuine, direct payoff of choosing an ORM with real typed columns back in Chapter 2, not something this route had to work around on its own.

Not every route benefits equally from async

Unlike Chapter 3's genuinely async httpx.AsyncClient call, get_alerts is a plain def, not async def — this uses the classic, synchronous SQLAlchemy session pattern (Session, not AsyncSession). A local SQLite query is fast enough that this rarely matters in practice at this app's own realistic scale, but it's worth naming honestly rather than implying every route in this course is async by default: Chapter 3's route benefits from async because it waits on a genuinely slow external network call; this one queries a local file, where the same non-blocking benefit doesn't apply nearly as much, and a fully async SQLAlchemy setup (AsyncSession, an async database driver) was a deliberate scope decision this course doesn't take on.

The Dashboard View

// static/app.js
async function loadAlerts() {
  const response = await fetch("/api/items/alerts");
  const alerts = await response.json();

  const list = document.getElementById("alerts-list");
  list.innerHTML = alerts.length === 0
    ? "<li>Nothing expiring soon.</li>"
    : alerts.map((item) => `<li>${item.name} — expires ${item.expiry_date}</li>`).join("");
}

The threshold is computed in Python, not SQL

date.today() + timedelta(days=3) runs once in application code before the query executes, rather than being computed inside the SQL statement the way Food Tracker (React + Express)'s own date('now', '+3 days') was. Both approaches are valid; this one keeps the "how many days is soon" logic visible directly in Python, right next to the route that uses it, rather than buried inside a SQL string.

Where This Course Is Headed

Item history and live search next — a ?q= prefix-search endpoint and debounced frontend fetching.

Hands-On Exercises

Exercise 1

Explain the specific bug class this course's typed Date column avoids that Food Tracker (React + Express) had to name explicitly, tracing it back to a decision made in Chapter 2.

📄 View solution

Exercise 2

Explain why get_alerts is a plain def rather than async def, contrasting it with Chapter 3's own async lookup route.

📄 View solution

Exercise 3

Explain the difference between computing the expiry threshold in Python (date.today() + timedelta(days=3)) versus computing it inside the SQL query itself, as Food Tracker (React + Express) did.

📄 View solution

Chapter 6 Quick Reference

Route: GET /alerts — status='active', expiry_date not null, expiry_date <= today+3 days, ordered by expiry_date
Real advantage: a typed Date column avoids the TEXT-comparison gotcha Food Tracker (React + Express) had to warn about explicitly
Honest note: this route is a plain def, not async def — a local SQLite query doesn't benefit from async the way Chapter 3's external API call did
Threshold computed in Python: date.today() + timedelta(days=3), kept visible next to the route, not buried in a SQL string
Next chapter: Item History & Live Search-as-You-Type