Transactions, Normalization, and Performance

Project Checkpoint: Building a Study Mode

---

So far the study tracker has decks, cards, tags, a way to rate cards on the deck detail page, and a dashboard summarizing activity. What it still doesn’t have is an actual study mode — a page that presents one card at a time, hides the answer until you ask for it, and records how well you remembered before moving on.

This checkpoint adds that mode. It is a substantive new piece of the project: the user starts a study session on a deck, sees one question at a time, reveals the answer when ready, rates how well they remembered, and is advanced to the next card. The flow can continue as long as the user wants; the system always picks the card the user has reviewed least recently from the chosen deck.

The chapter is a worked example. The point is not to leave you to invent the implementation, but to walk through one consistent way to build it. By the end of the chapter, your project should have a working study mode that the user can actually use to study.

This is the first of two project checkpoints in this part. Both exercise the full toolkit from earlier chapters, but each one focuses on a different transaction concern. This checkpoint focuses on transactional atomicity — making sure a multi-statement write either fully happens or fully doesn’t. The next checkpoint focuses on concurrent access — making sure two users acting at once cannot corrupt each other’s work.

What the Feature Will Look Like

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

On the existing deck detail page, the user clicks a new link: “Start studying.” That takes them to a study view that shows just one question from the deck — no answer, no surrounding context. The user thinks about the answer in their head. When ready, they click “Show answer” to navigate to a second view that displays both the question and the answer, plus a rating form.

The user picks a rating from 1 (did not remember) to 5 (remembered easily) and clicks “Save and next.” The system records the review and redirects back to the study view for the same deck, which picks the next card to study — whichever card the user has reviewed least recently. The user is now looking at a fresh question.

The cycle continues until the user navigates away. There is no notion of “study session” tracked in the database — the user decides when to stop. Each rating they record adjusts which card they will see next, because the just-reviewed card is now the most recently reviewed, and the system naturally rotates to other cards.

If the deck has cards that have never been reviewed, those come first: the system prefers showing you a card you have never seen over one you have. Once every card has at least one review, the rotation continues with whichever card was reviewed least recently.

Concretely, the feature has five pieces:

1. a GET route that picks the next card to study and shows just the question,
2. a GET route that shows the full card — question and answer — with the rating form,
3. a POST route that records the rating, wrapped in a transaction so that the deck-lookup and the INSERT are atomic, then redirects back to the question view for the same deck,
4. small additions to the deck detail page so the user can launch the study flow,
5. a new migration that adds the project’s baseline indexes — these were recommended in Chapter 6, and this checkpoint is where they enter the project.

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

Three Concerns in This Feature

Each part-level idea shows up somewhere in this feature, and naming where it shows up before writing the code makes the implementation choices clearer.

Transactions (focus of this checkpoint). The POST that records a review does two database operations in sequence: it looks up the card’s deck (to know where to redirect) and inserts a row into reviews. If anything goes wrong between those two operations — a constraint violation we did not anticipate, a connection drop, an unexpected error — the user should not be left with a half-finished state. Wrapping both in a single transaction makes them atomic: either both succeed or both are rolled back.

Schema quality. Recency is derived from MAX(reviewed_at) rather than stored on cards. There is no cards.last_reviewed_at column. The system computes “which card was reviewed least recently” fresh every time, from the underlying reviews table. This is the “derive first, store reluctantly” principle from the previous chapters applied to a real read pattern.

Performance. The next-card query starts from the cards in one deck, joins those cards to their reviews, and groups by card. Both cards.deck_id and reviews.card_id need indexes to make the lookup fast. This checkpoint adds those indexes as part of its migration, alongside an index on card_tags.tag_id that other parts of the project benefit from.

Each concern has one job. The transaction concern is the one this checkpoint actively verifies; the other two are present but not the focus.

Step 1: The Question View

The first piece is a GET route that picks the next card to study and renders the question template.

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

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

        next_card = conn.execute(t"""
            SELECT c.id, c.question
            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
            ORDER BY MAX(reviewed_at) ASC NULLS FIRST, c.id ASC
            LIMIT 1
        """).fetchone()

    if next_card is None:
        return render(request, "study_empty.html", deck=deck)

    return render(request, "study_question.html", deck=deck, card=next_card)

The route does two things: it confirms the deck exists, and it picks the next card.

The deck lookup is a guard. If a user types /decks/99999/study into the URL bar with a deck id that doesn’t exist, the route should redirect somewhere sensible rather than render a page about nothing.

It is worth reading the next card query over a couple of times to understand how it works.

Reading the Next-Card Query

The query asks: of all the cards in this deck, which one has been reviewed least recently?

SELECT c.id, c.question
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
ORDER BY MAX(reviewed_at) ASC NULLS FIRST, c.id ASC
LIMIT 1

Reading it piece by piece:

The query starts from cards and keeps only cards in the chosen deck with WHERE c.deck_id = {deck_id}. This is the first performance win: the database can use the cards.deck_id index to find the small set of candidate cards before thinking about reviews.

The LEFT JOIN reviews AS r ON r.card_id = c.id then attaches reviews for those candidate cards. The LEFT JOIN is important: it keeps every card in the deck visible, including cards that have never been reviewed. For those cards, the joined review columns are NULL.

GROUP BY c.id, c.question creates one result row per card. MAX(reviewed_at) computes that card’s most recent review time. For a card with no reviews, MAX(reviewed_at) is NULL.

ORDER BY MAX(reviewed_at) ASC NULLS FIRST then sorts the grouped rows by recency, with two subtleties:

• ASC means smallest first — the oldest reviewed-at timestamps come first. That is the right direction: we want the card reviewed least recently.
• NULLS FIRST puts cards with no review at the very top of the order. Without this, PostgreSQL’s default places nulls last in ascending order, which would mean unreviewed cards would only surface after all the reviewed cards. We want the opposite: cards the user has never seen should be shown first.

c.id ASC is a tie-break. Two unreviewed cards both have NULL recency, and several reviewed cards might share a timestamp. Breaking ties by id makes the result deterministic — the same query against the same state always returns the same card.

LIMIT 1 keeps only the single top-ranked card.

The combined effect: the query returns the first card the user should review next, or no rows at all if the deck is empty.

When the Deck Is Empty

If the deck has no cards yet, the next-card query returns no rows. The route checks for this and renders the study_empty.html template:

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

<p>This deck has no cards yet. Add some on the deck page and come back.</p>

<p><a href="/decks/{{ deck.id }}">Back to deck</a></p>

A short page with a back link is enough. The user knows what to do.

The Question Template

The question template shows just the question and a link to the answer view:

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

<div class="study-card">
  <p><strong>Question:</strong></p>
  <p>{{ card.question }}</p>
</div>

<p><a href="/cards/{{ card.id }}/study">Show answer</a></p>

<p><a href="/decks/{{ deck.id }}">Back to deck</a></p>

Notice what is not on the page: the answer. The whole point of this view is to give the user a moment to think about the answer before seeing it. Once they have thought, they click “Show answer,” which is a GET link, not a form submission. Following that link is a pure read — no state changes, no rows are inserted — so a GET is the correct HTTP verb.

The “Back to deck” link is the escape hatch. If the user decides not to continue, they can leave without recording anything.

Step 2: The Answer View

The second GET route shows the full card with the rating form:

@app.get("/cards/{card_id}/study")
def study_answer_view(request: Request, card_id: int):
    with get_connection() as conn:
        card = conn.execute(t"""
            SELECT c.id, c.question, c.answer, c.deck_id, d.name AS deck_name
            FROM cards AS c
            INNER JOIN decks AS d ON d.id = c.deck_id
            WHERE c.id = {card_id}
        """).fetchone()

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

    return render(request, "study_answer.html", card=card)

One query, joining cards to decks so the template can show the deck name without a second query. The INNER JOIN is correct here: every card must belong to a real deck (the foreign-key constraint guarantees it), so there are no orphan cards to worry about.

The route is keyed by card_id, not deck_id. The deck context comes from the join, not from the URL. This keeps the URL simple and matches the existing pattern from the review route (POST /cards/{card_id}/reviews), which is also card-keyed.

The Answer Template

<h1>Studying {{ card.deck_name }}</h1>

<div class="study-card">
  <p><strong>Question:</strong></p>
  <p>{{ card.question }}</p>

  <p><strong>Answer:</strong></p>
  <p>{{ card.answer }}</p>
</div>

<form method="post" action="/cards/{{ card.id }}/study/review">
  <fieldset>
    <legend>How well did you remember?</legend>
    <label><input type="radio" name="rating" value="1" required> 1 (did not remember)</label>
    <label><input type="radio" name="rating" value="2"> 2</label>
    <label><input type="radio" name="rating" value="3"> 3</label>
    <label><input type="radio" name="rating" value="4"> 4</label>
    <label><input type="radio" name="rating" value="5"> 5 (remembered easily)</label>
    <button type="submit">Save and next</button>
  </fieldset>
</form>

<p><a href="/decks/{{ card.deck_id }}">Back to deck without rating</a></p>

The question is shown again so the user has full context when they pick a rating. The answer is below the question. The form is below that, with radio buttons for the rating.

The form posts to /cards/{{ card.id }}/study/review, which is the next route. The required attribute on the first radio button makes the browser refuse to submit an empty form.

Notice that this checkpoint introduces a separate POST route from the one we already built. The earlier review route records a review from the deck detail page and redirects back to the deck. This chapter’s POST /cards/{card_id}/study/review records a review from the study mode and redirects back to the study view for the next card. Both routes do the same INSERT; they differ only in where they redirect afterward. Having two routes with one job each is clearer than one route that has to figure out where it was called from.

Step 3: The Review-Recording Route

This is the route where the transaction discipline matters.

@app.post("/cards/{card_id}/study/review")
def study_review(card_id: int, rating: int = Form(...)):
    with get_connection() as conn:
        with conn.transaction():
            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}/study", status_code=303
    )

The route is short, but each piece has a reason.

The decorator is @app.post, because recording a review is a write.

The with conn.transaction(): block wraps two operations: the lookup of the card’s deck_id, and the INSERT into reviews. The lookup is necessary because the redirect needs to know which deck to send the user back to. The INSERT is the actual review-recording.

Transaction Protects Against Partial Failure

The transaction exists to make the lookup-and-insert pair atomic. Concretely: if the INSERT fails — a constraint violation, a connection drop, an unexpected error from the database — the transaction is rolled back. Because the lookup was a read, “rolling it back” has no visible effect; no rows change. But the atomicity guarantee still applies as a discipline: the route does not leave behind any partial state when something goes wrong.

The case where the transaction’s rollback does meaningful work is when the INSERT itself fails after starting to execute. PostgreSQL handles partial writes inside a single INSERT correctly on its own, but the transaction also covers a different failure mode: an application-level exception raised inside the with block (an unexpected error, a forced rollback during testing, a code path the route did not anticipate). In each of those cases, the transaction wrapper guarantees that no incomplete review is recorded.

The early-return case (when card is None) is a separate concern from the transaction. It is an input-validation guard: if the URL has a card id that doesn’t exist, the route returns a redirect without attempting the INSERT. The return exits the with block cleanly, committing a read-only transaction (which is a no-op since no rows changed) and redirecting the user.

What this transaction is not doing: it is not running the next-card query. The next card is picked by the GET route the redirect sends the user to, in a fresh database snapshot. So the redirect URL is /decks/{card.deck_id}/study, which sends the user back to the question view, which independently picks the next card from the current state of the database.

This keeps the POST route narrow — just the write and the redirect — and lets the GET route handle the read on its own. The user’s flow is still continuous: they see the question of the next card immediately after submitting a rating.

The route does not validate that rating is between 1 and 5. The schema’s CHECK constraint will reject out-of-range values, but with a 500 error rather than a friendly response. This is the same trade-off that we’ve already made earlier — a production application would validate the rating in the route too.

Step 4: Wiring Up the Deck Detail Page

The study mode is now reachable at /decks/{deck_id}/study, but there is no link to it from anywhere. The user has to type the URL. A small addition to the deck detail template fixes that:

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

<p>
  <a href="/decks/{{ deck.id }}/study">Start studying</a>
</p>

<ul class="cards">
  {% for card in cards %}
    ...

A single link near the top of the deck detail page is enough. The user can launch the study flow from any deck.

Step 5: Adding the Baseline Indexes

The next-card query starts from the cards in the chosen deck and then joins those cards to their reviews. The query plan depends on indexes being present on:

• cards.deck_id — to find the cards in the chosen deck quickly,
• reviews.card_id — to find the reviews belonging to those candidate cards.

In addition to the above, we add an index on card_tags.tag_id as a baseline for the tag-related queries that other parts of the project already use.

Create a new migration file called 008_indexes.sql in the migrations directory with the following content:

CREATE INDEX cards_deck_id_idx ON cards (deck_id);
CREATE INDEX reviews_card_id_idx ON reviews (card_id);
CREATE INDEX card_tags_tag_id_idx ON card_tags (tag_id);

Each index supports a frequent lookup path in the project: cards by deck, reviews by card, cards by tag. The first two are what this checkpoint’s next-card query depends on; the third is a baseline addition for the tag-related queries that other parts of the project already use.

After applying the migration, confirm the indexes exist with \di in psql:

=> \di cards*
                                  List of relations
 Schema |          Name           | Type  | Owner | Persistence | Access method | Table
--------+-------------------------+-------+-------+-------------+---------------+-------
 public | cards_deck_id_idx       | index | ...   | permanent   | btree         | cards
 public | cards_pkey              | index | ...   | permanent   | btree         | cards

=> \di reviews*
                                  List of relations
 Schema |          Name            | Type  | Owner | Persistence | Access method | Table
--------+--------------------------+-------+-------+-------------+---------------+---------
 public | reviews_card_id_idx      | index | ...   | permanent   | btree         | reviews
 public | reviews_pkey             | index | ...   | permanent   | btree         | reviews

=> \di card_tags*
                                  List of relations
 Schema |          Name            | Type  | Owner | Persistence | Access method | Table
--------+--------------------------+-------+-------+-------------+---------------+-----------
 public | card_tags_pkey           | index | ...   | permanent   | btree         | card_tags
 public | card_tags_tag_id_idx     | index | ...   | permanent   | btree         | card_tags

If any index is missing, the migration did not apply cleanly. The reproducibility check in the next step will surface that.

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 to 008_indexes.sql, and the application starts. If anything in the migration chain or the seed data is broken, this is where it surfaces.

Once the application is up, walk through the user flow:

1. Open http://localhost:8000/decks and pick a deck.
2. Click “Start studying.” The page should show the question of one card and a “Show answer” link.
3. Click “Show answer.” The page should show the same question, the answer, and a rating form.
4. Pick a rating and click “Save and next.” The page should redirect to the study view and show a different question — the card you just rated should not appear again immediately, because it is now the most recently reviewed card in the deck, while other cards have older or no reviews.
5. Continue rating cards for a while. The system should cycle through the deck.

Then verify the transaction concern explicitly.

Verification A: rollback on failure. Modify the route used to record a review so that it records an additional review with the score of 6. Adding the review should happen after the first review has been added. Adding a review with the score of 6 violates the CHECK constraint on reviews.rating and causes the INSERT to fail. The transaction wrapper should then roll back the entire block, so no review is recorded at all. To verify this, open the browser and walk through the flow until you get to the answer view. Pick a rating and click “Save and next.” The page should show an error (because of the constraint violation), but if you check the database, no review should have been recorded for that card.

Once you have verified the failure, revert the code change so the route is correct again.

Verification B: the next-card behavior is sensible. Pick a deck with several cards. Note which cards have been reviewed (you can check with SELECT card_id, COUNT(*) FROM reviews GROUP BY card_id). Start studying that deck. The first card shown should be:

• a card with no reviews, if any such card exists in this deck (the NULLS FIRST clause), or
• the card with the oldest MAX(reviewed_at) in this deck, if every card in the deck has been reviewed at least once.

Rate that card. The next card shown should be different from the one you just rated (assuming the deck has more than one card). Rate a few more. The system should rotate through the deck in order of least-recently-reviewed.

Verification C: the indexes are used. Run EXPLAIN ANALYZE on the next-card query:

EXPLAIN ANALYZE
SELECT c.id, c.question
FROM cards AS c
LEFT JOIN reviews AS r ON r.card_id = c.id
WHERE c.deck_id = 1
GROUP BY c.id, c.question
ORDER BY MAX(reviewed_at) ASC NULLS FIRST, c.id ASC
LIMIT 1;

The output should show Index Scan using cards_deck_id_idx for the cards lookup and use reviews_card_id_idx for finding reviews by card. If anything shows Seq Scan on either table, an index is missing or not being used. On the project’s seed data the tables may be small enough that the optimizer picks Seq Scan deliberately; what matters is that the indexes exist and would be used at larger data sizes.

If any of these verifications does not behave as expected, there is an issue somewhere in the chain. Each piece has one job; whichever output is wrong points at which piece to look at.

What Got Built, Concretely

This chapter took the toolkit from the rest of the part and surfaced it in a working study mode:

• three new routes (the question view, the answer view, the POST that records a rating),
• three new templates (the question view, the answer view, and a small empty-deck fallback),
• one small addition to the existing deck detail template (the “Start studying” link),
• one new migration (008_indexes.sql, adding the three baseline indexes the project’s queries depend on).

The schema’s tables did not gain a single column — the migration only added indexes. The “what to show next” decision is computed every time from the underlying reviews table. This is the “derive first” principle: the schema does not track current state, and the application reads what the schema already knows.

The project now functions as a real study tracker. The user can pick a deck, study its cards one at a time, see the answer when they want to, record how well they remembered, and have the system rotate them through the deck. Each rating shifts the rotation; cards the user struggles with do not pile up unseen.

Common Pitfalls

A few places where this kind of feature commonly goes wrong:

• forgetting NULLS FIRST on the ordering — unreviewed cards would never surface, because PostgreSQL’s default places nulls last in ascending order,
• forgetting the tie-break (, c.id ASC) — multiple unreviewed cards would be returned in indeterminate order, so the same user state could produce different results across calls,
• forgetting the with conn.transaction(): block in the POST route — the INSERT would auto-commit, and any application-level exception in the route would leave the database with a recorded review and no graceful response,
• using INNER JOIN instead of LEFT JOIN in the next-card query — cards with no reviews would be excluded, which is the exact opposite of what the study mode needs (a card the user has never seen should be the first thing the system shows them),
• forgetting the 008_indexes.sql migration entirely — the next-card query would still work but would be slow on growing data, and EXPLAIN ANALYZE would show Seq Scan instead of Index Scan,
• skipping the reproducibility check (docker compose down -v followed by docker compose up --build) and so missing a broken migration chain or a missing seed.

Each of these is a small habit. Together they are the difference between a feature that works in the demo and a feature that survives.

Submission

The deliverable for this checkpoint is the project zip, including the new routes (study_question_view, study_answer_view, study_review), the new templates (study_question.html, study_answer.html, study_empty.html), the “Start studying” link added to the deck detail page, and the new migration file 008_indexes.sql. Before submitting, confirm the reproducibility check above passes from a clean state.

Loading Exercise...

Check Your Understanding

1. Why does the next-card query use LEFT JOIN rather than INNER JOIN, and why does the order use NULLS FIRST?
2. Why are the deck-lookup and the INSERT in the POST route inside a single with conn.transaction(): block, when the deck-lookup is just a read?
3. Why does this chapter define a separate POST route for the study-mode review flow (/cards/{card_id}/study/review), rather than reusing the existing /cards/{card_id}/reviews route?