Summaries and Derived Data

Project Checkpoint: Building a Dashboard

---

The previous chapter laid out where summary queries belong in a working application: small focused queries in the route, simple display in the template, summary work in the database rather than in Python loops. This chapter applies that toolkit to build a real dashboard for the study tracker.

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 dashboard page that summarizes activity across all decks at a glance.

What the Dashboard Will Look Like

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

The project’s existing pages let the user manage decks and cards and rate individual cards on the deck detail page. What is missing is a page that shows the overall picture: how much material is in the study tracker, how much has been reviewed, where the activity has been concentrating. The dashboard is that page.

Concretely, the dashboard shows three things:

1. Four overview numbers at the top — the total number of decks, the total number of cards, the total number of reviews, and the average rating across all reviews. Four small statistics that orient the user.
2. A per-deck breakdown — for each deck, the number of cards it contains and the number of reviews those cards have received between them. This is where the user can see which decks they are studying actively and which are sitting idle.
3. A short list of recent reviews — the last ten reviews, each showing which deck and which card was reviewed and what rating was given. This is the activity feed.

That is the whole feature. There is no chart, no time-bucketed graph, no per-day breakdown — those are good options for later, but for the first dashboard the three components above are enough.

Building the page has four pieces:

1. a route that loads the four overview values, the per-deck breakdown, and the recent reviews,
2. each of those queries written cleanly and verified,
3. a template that displays the loaded data without computing anything itself,
4. an end-to-end check that the dashboard still works after the project is rebuilt from scratch.

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

Step 1: The Four Overview Summaries

The simplest part of the dashboard. Four single-row queries:

@app.get("/dashboard")
def dashboard(request: Request):
    with get_connection() as conn:
        deck_count = conn.execute(
            "SELECT COUNT(*) AS deck_count FROM decks"
        ).fetchone().deck_count

        card_count = conn.execute(
            "SELECT COUNT(*) AS card_count FROM cards"
        ).fetchone().card_count

        review_count = conn.execute(
            "SELECT COUNT(*) AS review_count FROM reviews"
        ).fetchone().review_count

        average_rating = conn.execute(
            "SELECT COALESCE(ROUND(AVG(rating), 2), 0) AS average_rating FROM reviews"
        ).fetchone().average_rating

        # ... more queries below

    return render(
        request,
        "dashboard.html",
        deck_count=deck_count,
        card_count=card_count,
        review_count=review_count,
        average_rating=average_rating,
        # ... more keyword arguments below
    )

Four queries, each returning one row with one summary value, each accessed by name with .fetchone().column_name. The route follows the conventions from Part 3: @app.get for the decorator, get_connection() for the connection, render() to compose the response.

Each COUNT(*) is a whole-table count. There is no WHERE, no GROUP BY. These are the simplest summary queries possible, and they are exactly right for the overview-numbers role. The average rating is only slightly richer: it still summarizes the whole reviews table, but it uses AVG and a COALESCE(..., 0) fallback so the page still shows a number even before any review exists.

A small note on aliases. Each query gives the count an explicit name (AS deck_count, etc.) even though the route then re-extracts the value into a Python variable of the same name. This is partly defensive (without an alias the column would be called count, which would clash if two queries’ results were combined) and partly readable — the alias documents what the number is, both in the SQL and in the Python.

Step 2: The Per-Deck Breakdown

This is the most interesting query in the dashboard, because it is exactly the count-inflation case from earlier. Each deck has many cards, and each card has many reviews; summing both in one grouped query produces wrong card counts.

The CTE pattern is the clean fix:

        per_deck = conn.execute("""
            WITH card_counts AS (
              SELECT deck_id, COUNT(*) AS card_count
              FROM cards
              GROUP BY deck_id
            ),
            review_counts AS (
              SELECT c.deck_id, COUNT(*) AS review_count
              FROM cards AS c
              INNER JOIN reviews AS r ON r.card_id = c.id
              GROUP BY c.deck_id
            )
            SELECT
              d.id, d.name,
              COALESCE(cc.card_count, 0) AS card_count,
              COALESCE(rc.review_count, 0) AS review_count
            FROM decks AS d
            LEFT JOIN card_counts AS cc ON cc.deck_id = d.id
            LEFT JOIN review_counts AS rc ON rc.deck_id = d.id
            ORDER BY d.name
        """).fetchall()

The query reads in three named steps:

• compute card counts per deck (one row per deck that has cards),
• compute review counts per deck (one row per deck that has reviews),
• combine them at the deck level, with COALESCE to handle decks that have no cards or no reviews yet.

Each intermediate result has one job. The final SELECT joins decks (the source of truth for “which decks exist”) to the two summaries via LEFT JOIN, so a deck with no cards still appears in the dashboard with card_count = 0. An INNER JOIN here would silently hide empty decks, which is not what the user wants.

COALESCE(cc.card_count, 0) and COALESCE(rc.review_count, 0) turn NULL into 0 in the result. For displayed counts, 0 is the right rendering — “0 cards” is meaningful, “NULL cards” would just be a bug surfacing in the UI.

A small verification before moving on. Run the query in psql and check one specific deck by hand:

-- check a specific deck against the underlying data
SELECT COUNT(*) FROM cards WHERE deck_id = 1;
SELECT COUNT(*) FROM reviews r INNER JOIN cards c ON c.id = r.card_id WHERE c.deck_id = 1;

The numbers from these two queries should match the card_count and review_count columns the dashboard query produces for deck 1. If they disagree, the CTE has a problem — usually a LEFT vs INNER join confusion or a missing condition in one of the subqueries.

Loading Exercise...

Step 3: The Recent Reviews List

A short list of the most recent reviews, with deck and card context for each:

        recent_reviews = conn.execute("""
            SELECT
                r.id, r.rating, r.reviewed_at,
                c.question, d.name AS deck_name
            FROM reviews AS r
            INNER JOIN cards AS c ON c.id = r.card_id
            INNER JOIN decks AS d ON d.id = c.deck_id
            ORDER BY r.reviewed_at DESC
            LIMIT 10
        """).fetchall()

This is a row query, not a summary query — the result is ten rows representing ten actual reviews, not ten summary buckets. It is in the dashboard because the dashboard mixes summaries with one short list, and that mix is normal for a dashboard.

The joins are all INNER JOIN. A review must belong to a real card and a card must belong to a real deck (foreign-key constraints from earlier parts guarantee both), so there are no orphan reviews to keep visible. Using LEFT JOIN here would have no effect on the result and would only confuse a reader.

ORDER BY r.reviewed_at DESC LIMIT 10 is the standard “most recent N rows” pattern. The DESC puts the newest first; the LIMIT 10 caps the list.

The aliases r., c., d. keep the query readable when several tables contribute columns. d.name AS deck_name is necessary because name would clash with any other name column in scope (none here, but the alias is good practice).

Loading Exercise...

Putting the Route Together

The full route, with the four overview summaries, the per-deck breakdown, and the recent reviews:

@app.get("/dashboard")
def dashboard(request: Request):
    with get_connection() as conn:
        deck_count = conn.execute(
            "SELECT COUNT(*) AS deck_count FROM decks"
        ).fetchone().deck_count

        card_count = conn.execute(
            "SELECT COUNT(*) AS card_count FROM cards"
        ).fetchone().card_count

        review_count = conn.execute(
            "SELECT COUNT(*) AS review_count FROM reviews"
        ).fetchone().review_count

        average_rating = conn.execute(
            "SELECT COALESCE(ROUND(AVG(rating), 2), 0) AS average_rating FROM reviews"
        ).fetchone().average_rating

        per_deck = conn.execute("""
            WITH card_counts AS (
              SELECT deck_id, COUNT(*) AS card_count
              FROM cards
              GROUP BY deck_id
            ),
            review_counts AS (
              SELECT c.deck_id, COUNT(*) AS review_count
              FROM cards AS c
              INNER JOIN reviews AS r ON r.card_id = c.id
              GROUP BY c.deck_id
            )
            SELECT
              d.id, d.name,
              COALESCE(cc.card_count, 0) AS card_count,
              COALESCE(rc.review_count, 0) AS review_count
            FROM decks AS d
            LEFT JOIN card_counts AS cc ON cc.deck_id = d.id
            LEFT JOIN review_counts AS rc ON rc.deck_id = d.id
            ORDER BY d.name
        """).fetchall()

        recent_reviews = conn.execute("""
            SELECT
                r.id, r.rating, r.reviewed_at,
                c.question, d.name AS deck_name
            FROM reviews AS r
            INNER JOIN cards AS c ON c.id = r.card_id
            INNER JOIN decks AS d ON d.id = c.deck_id
            ORDER BY r.reviewed_at DESC
            LIMIT 10
        """).fetchall()

    return render(
        request,
        "dashboard.html",
        deck_count=deck_count,
        card_count=card_count,
        review_count=review_count,
        average_rating=average_rating,
        per_deck=per_deck,
        recent_reviews=recent_reviews,
    )

Six queries in total: four scalar overview queries, one per-deck breakdown, and one recent-reviews list. The route is verbose, but each piece has one purpose. If a number on the dashboard is wrong, only one query has to be inspected.

You might recognize the Strategy A from the last chapter. The queries together still take only a few milliseconds against the project’s seed data; combining them into one CTE-based query would not measurably help and would substantially hurt readability.

Step 4: The Template

The template’s job is to display what the route loaded. No computation, no aggregation, no looping-with-running-totals. Just present the data:

<h1>Dashboard</h1>

<section class="overview">
  <p>Total decks: {{ deck_count }}</p>
  <p>Total cards: {{ card_count }}</p>
  <p>Total reviews: {{ review_count }}</p>
  <p>Average rating: {{ average_rating }}</p>
</section>

<section class="per-deck">
  <h2>Decks</h2>
  <table>
    <thead>
      <tr>
        <th>Deck</th>
        <th>Cards</th>
        <th>Reviews</th>
      </tr>
    </thead>
    <tbody>
    {% for row in per_deck %}
      <tr>
        <td>{{ row.name }}</td>
        <td>{{ row.card_count }}</td>
        <td>{{ row.review_count }}</td>
      </tr>
    {% endfor %}
    </tbody>
  </table>
</section>

<section class="recent">
  <h2>Recent reviews</h2>
  {% if recent_reviews %}
    <ul>
    {% for r in recent_reviews %}
      <li>{{ r.deck_name }} / {{ r.question }} -- rating {{ r.rating }}</li>
    {% endfor %}
    </ul>
  {% else %}
    <p>No reviews yet.</p>
  {% endif %}
</section>

Three sections, each one taking the data the route loaded and rendering it. The template loops over per_deck and recent_reviews, but it does not compute a running sum, an average, or any other aggregate. Every value on the page came from the database in the shape it appears.

The {% if recent_reviews %} branch handles the case of an empty list. Without it, an empty <ul> would render with no children, which is technically valid HTML but reads as broken (the <h2> would sit above blank space). The else branch shows a message instead.

Loading Exercise...

The route also has to be wired up so the dashboard is reachable from somewhere. A small addition to the navigation in the base template (or wherever the project’s nav lives):

<nav>
  <a href="/decks">Decks</a>
  <a href="/dashboard">Dashboard</a>
</nav>

The exact navigation markup depends on what the project already has; the principle is that the dashboard should be linked from the existing pages, not just available at a URL the user has to know.

End-to-End Verification

The dashboard 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 007_reviews_seed.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:

1. Open http://localhost:8000/dashboard. The page should show four overview values, one row per deck, and a list of recent reviews.
2. The total numbers should match the seed data. A quick psql cross-check:

SELECT
  (SELECT COUNT(*) FROM decks) AS decks,
  (SELECT COUNT(*) FROM cards) AS cards,
  (SELECT COUNT(*) FROM reviews) AS reviews,
  (SELECT COALESCE(ROUND(AVG(rating), 2), 0) FROM reviews) AS average_rating;

These four values should equal the four overview values on the dashboard.

3. Navigate to a deck, submit a new rating, and return to the dashboard. The total review count should have gone up by one. The relevant deck’s review count in the per-deck table should also have gone up. The new review should appear at the top of the recent-reviews list.
4. Walk through the per-deck breakdown by hand. For one deck, count its cards and its reviews directly:

SELECT COUNT(*) FROM cards WHERE deck_id = 1;
SELECT COUNT(*) FROM reviews r INNER JOIN cards c ON c.id = r.card_id WHERE c.deck_id = 1;

These numbers should match what the dashboard shows for deck 1.

If any of these checks does not behave as expected, the issue is somewhere in the chain you just built. Each query has one job; whichever output is wrong points at which query to look at.

Loading Exercise...

What Got Built, Concretely

This chapter took the summary toolkit from the rest of the part and surfaced it in the application as a working dashboard:

• one new route (the dashboard, with six queries),
• one new template (the dashboard page, displaying the loaded data),
• one small navigation update (so the dashboard is reachable).

The schema did not change. Every number on the dashboard is derived from the existing tables; nothing was added to the database to support the page. This is the “derive first” principle in concrete form: the dashboard reads what the schema already knows, and the schema does not have to track summary counts itself.

The project now functions as more than a CRUD application. A user can see, at a glance, how their study activity is going overall. The pieces of the part — aggregates, grouping, multi-table summaries with COUNT(joined_table.id) rather than COUNT(*), CTEs as a fix for inflation, COALESCE for empty-group display, and the rule that templates display rather than compute — are all visible in the page that just got built.

Submission

The deliverable for this checkpoint is the project zip, including the new dashboard route, the dashboard template, and any navigation changes. Before submitting, confirm the reproducibility check above passes from a clean state.

Loading Exercise...

Check Your Understanding

1. Why does the per-deck breakdown use a CTE pattern rather than joining cards and reviews directly in one query?
2. Why are the overview summaries written as several separate single-row queries rather than as one combined query?
3. Why does the template have a separate {% if recent_reviews %} branch for the empty case?