Schemas and Constraints

Project Checkpoint, Step 2: Building the Card Review Page

---

The previous chapter took the review feature from idea to migration. The schema now has a reviews table, seeded with starter data, and the constraints behave as intended. This chapter turns that schema into a working feature in the application.

What the Feature Will Look Like

Before any code, it helps to picture what the feature does from the user’s perspective.

The project’s existing deck detail page lists the cards in a deck, showing each card’s question and answer side by side. After this feature is in place, the same page also lets the user rate each card on a 1-to-5 scale. Next to (or under) each card, there is a small form — a dropdown or set of radio buttons for the rating, and a “Save rating” button. When the user picks a rating and submits, the page reloads and the card’s row now shows a small badge: “12 reviews, average rating 3.4” or similar. Over time, as the user rates cards, the badges update.

For showing the statistics in the badge, we’ll use aggregate functions that we will learn more about in the next part.

That is the whole feature. There is no separate study mode, no flashcard flow, no answer-revealing animation. The deck detail page already shows questions and answers; we are adding a write path to record a rating and a read summary to surface what has been recorded.

Concretely, building the feature has four pieces:

1. a route that records a review (a POST that takes a card id and a rating, validates them, inserts a row into reviews),
2. a small form on the deck detail template that submits to that route,
3. a query that, for each card in the deck, computes how many reviews it has and the average rating,
4. a small change to the deck detail template to show that summary as a badge per card.

Each piece is small. The chapter walks through them one at a time.

The Existing Deck Detail Page

The project’s existing deck detail route looks roughly like this (your version may differ in small ways):

@app.get("/decks/{deck_id}")
def deck_detail(request: Request, deck_id: int):
    with get_connection() as conn:
        deck = conn.execute(t"""
            SELECT id, name FROM decks WHERE id = {deck_id}
        """).fetchone()

        cards = conn.execute(t"""
            SELECT id, question, answer
            FROM cards
            WHERE deck_id = {deck_id}
            ORDER BY id
        """).fetchall()

    return render(request, "deck_detail.html", deck=deck, cards=cards)

The route uses the conventions we’re already somewhat familiar with: @app.get for the decorator, get_connection() for the connection, t"""...""" for parameterized SQL with {deck_id} interpolation, dict-row access for results, and render() to compose the template response.

The corresponding template lists each card’s question and answer:

<h1>{{ deck.name }}</h1>

<ul class="cards">
{% for card in cards %}
  <li>
    <strong>Q:</strong> {{ card.question }}<br/>
    <strong>A:</strong> {{ card.answer }}
  </li>
{% endfor %}
</ul>

That is the starting state. The chapter adds to both files.

Step 1: The Route That Records a Review

The first piece is a route that accepts a review submission. It takes the card id and the rating from the form, inserts a row, and redirects back to the deck detail page so the user sees the updated badge.

from fastapi import Form
from fastapi.responses import RedirectResponse

@app.post("/cards/{card_id}/reviews")
def create_review(card_id: int, rating: int = Form(...)):
    with get_connection() as conn:
        card = conn.execute(t"""
            SELECT deck_id FROM cards WHERE id = {card_id}
        """).fetchone()

        if card is None:
            return RedirectResponse(url="/decks", status_code=303)

        conn.execute(t"""
            INSERT INTO reviews (card_id, rating)
            VALUES ({card_id}, {rating})
        """)

    return RedirectResponse(url=f"/decks/{card.deck_id}", status_code=303)

A few things worth noticing:

The decorator is @app.post, not @app.get, because creating a review is a write. Write operations live behind POST endpoints, both because that matches HTTP semantics and because it prevents accidental writes from search-engine crawlers or page reloads.

The route looks up the card to find its deck_id before inserting. This serves two purposes: it confirms the card exists (a malicious or buggy form submission with card_id = 99999 redirects gracefully instead of producing a foreign-key error), and it gives the redirect a real deck id to send the user back to.

Both SQL statements use template-string parameterization. The card lookup uses {card_id}, the insert uses {card_id} and {rating}. Neither value is concatenated into the SQL string directly, which means a malicious user submitting a clever rating value cannot inject SQL.

The INSERT does not specify reviewed_at. The schema has DEFAULT CURRENT_TIMESTAMP, so the database fills it in automatically. The route does not need to know about it.

The redirect uses status code 303 (“See Other”). This is the standard pattern for “after a successful POST, redirect to a GET” — it prevents the browser from re-submitting the form if the user reloads the resulting page.

Loading Exercise...

What is missing? The route does not validate that the value of rating is between 1 and 5. The database CHECK constraint will reject out-of-range values, but with a 500 error rather than a friendly response. For our project, this is acceptable — the form will only ever submit valid ratings, because we control the form. A real application might validate the value in the route too, both to give a better error message and to defend against form submissions that bypass the dropdown. We note the limitation and move on.

Loading Exercise...

Step 2: The Form on the Deck Detail Template

The form lives in the existing deck_detail.html template, inside the loop that renders each card. The smallest version that works:

<h1>{{ deck.name }}</h1>

<ul class="cards">
{% for card in cards %}
  <li>
    <strong>Q:</strong> {{ card.question }}<br/>
    <strong>A:</strong> {{ card.answer }}<br/>

    <form method="post" action="/cards/{{ card.id }}/reviews">
      <label>
        Rate this card:
        <select name="rating" required>
          <option value="">--</option>
          <option value="1">1 (did not remember)</option>
          <option value="2">2</option>
          <option value="3">3</option>
          <option value="4">4</option>
          <option value="5">5 (remembered easily)</option>
        </select>
      </label>
      <button type="submit">Save rating</button>
    </form>
  </li>
{% endfor %}
</ul>

The form submits to /cards/{{ card.id }}/reviews, which matches the POST route added above. The name="rating" matches the rating: int = Form(...) parameter in the route.

The dropdown has a placeholder option (value="") so the user has to actively pick a rating. The required attribute on the <select> prevents submitting the form without a rating. The form would work without them, but it is friendlier to the user with them.

That is all the change to the template needed for the write path. Refresh the page, pick a rating, click “Save rating,” and the page reloads. A row has been added to reviews. You can confirm in psql:

SELECT id, card_id, rating, reviewed_at
FROM reviews
ORDER BY id DESC LIMIT 5;

The five most recent reviews should include the one you just submitted.

Step 3: The Summary Query

Now the read side. The deck detail page should show, for each card, how many reviews it has and the average rating.

The whole next part of the course is focused on summary queries. Here, you can just copy the query and see that it works.

The existing route fetched cards with SELECT id, question, answer .... The new version joins to reviews and aggregates:

@app.get("/decks/{deck_id}")
def deck_detail(request: Request, deck_id: int):
    with get_connection() as conn:
        deck = conn.execute(t"""
            SELECT id, name FROM decks WHERE id = {deck_id}
        """).fetchone()

        cards = conn.execute(t"""
            SELECT
              c.id,
              c.question,
              c.answer,
              COUNT(r.id) AS review_count,
              ROUND(AVG(r.rating)::numeric, 1) AS average_rating
            FROM cards AS c
            LEFT JOIN reviews AS r ON r.card_id = c.id
            WHERE c.deck_id = {deck_id}
            GROUP BY c.id, c.question, c.answer
            ORDER BY c.id
        """).fetchall()

    return render(request, "deck_detail.html", deck=deck, cards=cards)

A minor but important detail:

The join is LEFT JOIN, not INNER JOIN. A card with no reviews still belongs in the result, just with review_count = 0 and average_rating = NULL. An INNER JOIN would silently hide unreviewed cards from the deck detail page, which is exactly the wrong behavior.

We’ll learn what the other parts of the query do in the next part of the course

Loading Exercise...

Step 4: Showing the Summary in the Template

The template change is small: each card now shows a badge with the review count and average rating.

<h1>{{ deck.name }}</h1>

<ul class="cards">
{% for card in cards %}
  <li>
    <strong>Q:</strong> {{ card.question }}<br/>
    <strong>A:</strong> {{ card.answer }}<br/>

    <p>
      {% if card.review_count > 0 %}
        {{ card.review_count }} review{% if card.review_count != 1 %}s{% endif %},
        average rating {{ card.average_rating }}
      {% else %}
        not reviewed yet
      {% endif %}
    </p>

    <form method="post" action="/cards/{{ card.id }}/reviews">
      <label>
        Rate this card:
        <select name="rating" required>
          <option value="">--</option>
          <option value="1">1 (did not remember)</option>
          <option value="2">2</option>
          <option value="3">3</option>
          <option value="4">4</option>
          <option value="5">5 (remembered easily)</option>
        </select>
      </label>
      <button type="submit">Save rating</button>
    </form>
  </li>
{% endfor %}
</ul>

The {% if card.review_count > 0 %} branch handles the unreviewed case explicitly. Without it, an unreviewed card would render with “0 reviews, average rating None” (or a blank value), which is technically true but reads poorly. The {% else %} branch shows “not reviewed yet” instead, which is what a reader actually wants to see.

The plural-handling — “1 review” vs “2 reviews” — is a small touch that makes the page read naturally. It is not strictly necessary, but it is a one-line addition with visible polish.

Loading Exercise...

End-to-End Verification

The feature is now in place. Before submitting, walk through the full path once with a fresh database to confirm everything works.

docker compose down -v
docker compose up --build

The down -v removes the database volume, so the next up starts from an empty PostgreSQL instance. The migrations run from 001_schema.sql through 007_reviews_seed.sql, the seed data lands, and the application starts. If anything in the migration chain is broken, this is where it surfaces.

Once the application is up:

1. Open http://localhost:8000/decks and pick a deck.
2. The deck detail page should show the cards, each with a “review summary” line. Cards have one or more seeded reviews already, so most should show a count and average. A few may show “not reviewed yet.”
3. Pick a card and submit a rating. The page should reload and the card’s review count should have gone up by one. The average rating should have updated.
4. Submit a few more ratings on the same card. Each one should appear in the count.
5. Open psql and confirm the rows are there:

SELECT card_id, rating, reviewed_at
FROM reviews
ORDER BY reviewed_at DESC LIMIT 10;

If any of these steps does not behave as expected, the issue is somewhere in the chain you just built. The route, the form, the query, and the template each have one job; whichever output is wrong points at which piece to look at.

Loading Exercise...

What Got Built, Concretely

This chapter took a schema addition (one new table) and surfaced it in the application through:

• one new route (the POST that records a review),
• one modified route (the GET that now joins to reviews and aggregates),
• one modified template (the deck detail page, with a form and a summary line per card).

That is a small amount of code for a meaningful feature. Several of the project’s existing parts — the deck list, the card creation flow, the tag pages — are unchanged. The new feature integrates cleanly with what was already there because the schema change was additive and the application changes were localized to one page.

This is the shape that the next part will build on. Next part introduces the summary-query toolkit (aggregates, grouping, multi-table summaries, verification, CTEs) using the schema you just put in place. The deck detail’s “review count and average rating” will become one of the few summaries the project supports.

Check Your Understanding

1. Why did we retrieve a card from the database in the review-recording route, instead of just inserting with the provided card_id?
2. Why does the route that records a review use POST rather than GET, and why does it redirect with status code 303?
3. Why does the template have a separate {% else %} branch for the “not reviewed yet” case, when the database would technically return NULL for the average rating?

Submission

The deliverable for this chapter is the project zip, including the new migrations from the previous chapter and the route, query, and template changes from this chapter. Before submitting, confirm the reproducibility check above passes from a clean state.

Loading Exercise...