Phase 6 — Give Me the Receipts: Date-Range Reports & CSV Export in FastAPI

Tagline: Let the database do the filtering, let Python do the formatting, and never hand-build a CSV.

Introduction

By the end of Phase 5 my app could already tell me how much I'd spent this month and whether I was up or down versus last month. Nice numbers on a dashboard. But numbers on a screen aren't something you can do anything with — you can't hand them to an accountant, drop them into a spreadsheet, or keep a copy.

So Phase 6 was about one deceptively small idea: let me pull my expenses for a date range, and let me download them as a CSV.

It sounds like a five-minute job. And the happy path is short. But doing it properly — query parameters that validate themselves, a file download that actually streams, and an authenticated button on the frontend — surfaced a few things that "just write an endpoint" glosses over. Here's what I actually did, and why, including the one bug that had me squinting at an indentation level.

Index

• Structure of this log
• Step 1: The date-range report endpoint (query parameters + validation)
• Step 2: The CSV export endpoint (csv + StringIO + StreamingResponse)
• The bug: my CSV only had one row
• Step 3: Don't Repeat Yourself — extracting a shared helper
• Step 4: The Export CSV button (why a plain link won't work)
• Key habits to keep

Structure of this log

• Add an authenticated, owner-scoped report endpoint that filters by an explicit start/end date range
• Add a CSV export endpoint that streams a real file download
• Refactor the shared logic into one helper so the two endpoints can't drift apart
• Wire an Export CSV button into the React dashboard
• Write down the snags and the "why", so future-me doesn't relearn them

Step 1: The date-range report endpoint

Every endpoint I'd built until now took either a JSON body (payload: ExpenseCreate) or a value baked into the URL path (expense_id in /expenses/{expense_id}). A report needs neither. It needs the caller to say "give me the expenses between these two dates." That's what query parameters are for — the ?key=value bits on the end of a URL:

GET /reports/monthly?start=2026-08-01&end=2026-08-31

The rule I learned for query params

In FastAPI, any function parameter that (a) isn't part of the path and (b) is a plain scalar type automatically becomes a query parameter, read from the URL. Give it no default value and it's required — FastAPI returns a 422 on its own if the caller leaves it out. No if not start: boilerplate needed.

Two layers of validation

This was the part I didn't want to skip.

Layer 1 — free validation from the type hint. By typing the params as date, FastAPI parses "2026-08-01" into a real Python date for me and auto-rejects garbage like "banana" or "2026-13-40" with a 422 before my function even runs. Same "let the types do the validating" lesson as Pydantic schemas, just applied to the URL.

Layer 2 — validation the types can't do. FastAPI can confirm each value is a valid date. It has no idea whether start is supposed to come before end. That's business logic, so I check it myself and raise a 400:

Status	When	Meaning
422	start=banana	The request is malformed — FastAPI can't even parse it
400	start after end	The request parsed fine, but it's logically wrong

The distinction matters: 422 is "I can't read this," 400 is "I read it, and it doesn't make sense."

Inclusive range vs the half-open trick

In Phase 5 I used half-open ranges (spent_on >= month_start AND spent_on < next_month_start) because the upper bound was "the start of the next month" — half-open is the clean way to not double-count a boundary.

Here it's different. The user hands me an end date they want included, and spent_on is a pure date column (no time-of-day to trip over). So the intuitive, correct choice is inclusive on both ends: >= start AND <= end. If I ask for Aug 1 to Aug 31, I expect Aug 31 in the result.

That's a genuine judgement call, not a rule to memorise: half-open when the boundary is "the start of the next period", inclusive when the user is naming the exact last day they mean.

The endpoint

@app.get("/reports/monthly", response_model=List[ExpenseRead])
def get_monthly_report(
    start: date,
    end: date,
    db: Session = Depends(get_db),
    current_user: User = Depends(get_current_user),
):
    if start > end:
        raise HTTPException(
            status_code=400,
            detail="start date must be on or before end date",
        )
    expenses = db.scalars(
        select(Expense).where(
            Expense.user_id == current_user.id,
            Expense.spent_on >= start,
            Expense.spent_on <= end,
        )
    ).all()
    return expenses

Everything else is the same owner-scoped select(...).where(Expense.user_id == current_user.id, ...) pattern I already trusted from list_expenses. The only new muscles are the query params and the 400 check.

How I tested it (the adversarial pass)

I don't just test the happy path any more — I try to break it the way a real user or an attacker would:

• Happy path: start=2026-08-01, end=2026-08-31 → my August expenses.
• Backwards dates → 400 with my message.
• start=banana → 422, automatically, without my code running.
• A month with no expenses → [], not an error.
• No token → 401. (Owner-scoping means another user's rows never show up either.)

Step 2: The CSV export endpoint

Now the download. Three ideas I hadn't used before.

Idea 1 — the csv module

My first instinct was to build CSV text by hand with f-strings and commas. That breaks the first time a description contains a comma — Lunch, coffee would split into two columns and shift every field after it. Python's built-in csv module handles all the escaping: hand csv.writer a row as a list, and it writes a correctly-quoted line. The comma-in-your-data attacker loses for free.

Idea 2 — io.StringIO, a file that lives in memory

csv.writer needs something file-like to write into, but I don't want to create a real file on disk just to hand it back over HTTP. io.StringIO is an in-memory text buffer that behaves exactly like an open file. csv.writer writes into it, and then I read the text back out.

The one non-obvious line is buffer.seek(0) afterwards: writing leaves the cursor at the end of the buffer, so I rewind it to the start, or the response reads from the end and hands back nothing.

Idea 3 — StreamingResponse + download headers

A normal FastAPI return becomes JSON. To make the browser treat the reply as a downloadable file, I return a StreamingResponse with two things:

Piece	What it does
media_type="text/csv"	Tells the client "this is CSV, not JSON"
Content-Disposition: attachment; filename="..."	The attachment keyword is what makes the browser download and name the file instead of showing it inline

The endpoint

@app.get("/reports/monthly.csv")
def export_monthly_report_csv(
    start: date,
    end: date,
    db: Session = Depends(get_db),
    current_user: User = Depends(get_current_user),
):
    if start > end:
        raise HTTPException(
            status_code=400,
            detail="start date must be on or before end date",
        )
    expenses = db.scalars(
        select(Expense).where(
            Expense.user_id == current_user.id,
            Expense.spent_on >= start,
            Expense.spent_on <= end,
        )
    ).all()

    buffer = io.StringIO()
    writer = csv.writer(buffer)
    writer.writerow(["id", "description", "amount", "spent_on"])
    for expense in expenses:
        writer.writerow(
            [expense.id, expense.description, expense.amount, expense.spent_on]
        )
    buffer.seek(0)

    filename = f"expenses_{start}_{end}.csv"
    return StreamingResponse(
        buffer,
        media_type="text/csv",
        headers={"Content-Disposition": f'attachment; filename="{filename}"'},
    )

Note: csv, io, and StreamingResponse are all standard library or already shipped with FastAPI — no new packages, so requirements.txt didn't change.

Learning shortcut vs production version: I build the whole CSV in memory first, which is perfect for a personal expense tracker. For a huge export you'd yield rows from a generator so you never hold the whole file in RAM — same StreamingResponse, just fed a generator instead of a buffer. Good to know it exists; not needed yet.

The bug: my CSV only had one row

First download: header row, then… one expense. I had more than one in August, and Step 1's report endpoint (same query!) returned all of them. So the data wasn't the problem — the writing was.

The tell was which expense showed up. It was the last one. That points at exactly one thing: my writer.writerow(...) had drifted out of the for loop. Python happily ran the loop doing nothing, then wrote the loop variable — left pointing at the final expense — exactly once.

# BROKEN — writerow is dedented, so it runs once after the loop
for expense in expenses:
    ...
writer.writerow([expense.id, expense.description, expense.amount, expense.spent_on])

# FIXED — writerow is indented inside the loop, so it runs per expense
for expense in expenses:
    writer.writerow([expense.id, expense.description, expense.amount, expense.spent_on])

In a language where indentation is the block structure, a single wrong indent level isn't a style nit — it changes what runs when. Lesson filed: when a loop "only does the last thing once", check the indentation before you check anything else.

Step 3: Don't Repeat Yourself — extracting a shared helper

With both endpoints working, I noticed they now carried the identical validation-plus-query block. That's a maintenance trap: the day I change the date logic (say, exclude refunds later), I have to remember to change it in both places, and the bug is that I'll forget one.

So I pulled the shared work into a single helper — the one source of truth.

def _expenses_in_range(
    start: date,
    end: date,
    db: Session,
    current_user: User,
) -> list[Expense]:
    if start > end:
        raise HTTPException(
            status_code=400,
            detail="start date must be on or before end date",
        )
    return db.scalars(
        select(Expense).where(
            Expense.user_id == current_user.id,
            Expense.spent_on >= start,
            Expense.spent_on <= end,
        )
    ).all()

Two small things I learned here:

• The leading underscore. I already knew _ as the throwaway-value convention. A leading underscore on a function name is a related-but-different convention: "this is an internal helper for this module, not a public endpoint." No decorator, so FastAPI never exposes it as a route.
• Raising from a helper is fine. I worried the 400 might get swallowed. It doesn't — FastAPI catches HTTPException no matter how deep it's raised, so the status still reaches the client.

The report endpoint's body then collapses to a single line, and the CSV endpoint keeps only its CSV-building:

@app.get("/reports/monthly", response_model=List[ExpenseRead])
def get_monthly_report(start: date, end: date, db: Session = Depends(get_db), current_user: User = Depends(get_current_user)):
    return _expenses_in_range(start, end, db, current_user)

This was a "prove I changed nothing observable" refactor: I re-ran the same adversarial checks on both endpoints and confirmed identical behaviour. Green before, green after.

Step 4: The Export CSV button (why a plain link won't work)

Last piece — the button. My first instinct was a plain <a href="...monthly.csv" download>. That does not work here, and the reason is worth internalising: a normal link navigation can't attach an Authorization header. My CSV endpoint is owner-scoped and needs Bearer <token> — a bare anchor click sends no token, so it'd just get a 401.

So I do the download in the code path I actually control:

1. fetch the endpoint with the auth header (same as every other authed call).
2. Read the body as a Blob via response.blob() — a Blob is an in-memory chunk of file data, the right shape for a file (unlike response.json(), which I use for data).
3. URL.createObjectURL(blob) mints a temporary in-browser URL pointing at that blob.
4. Create an <a> in code, set href to that URL and the download attribute to the filename, then programmatically click() it.
5. URL.revokeObjectURL(url) frees the memory afterward — the object URL pins the blob in memory until revoked. Skipping it is a small leak (the "left the tap running" case).

async function handleExportCsv() {
  const token = localStorage.getItem("token");

  // Build the current month's range as YYYY-MM-DD strings.
  const now = new Date();
  const year = now.getFullYear();
  const month = now.getMonth(); // getMonth() is 0-indexed (Jan = 0)
  const pad = (n) => String(n).padStart(2, "0");
  const start = `${year}-${pad(month + 1)}-01`;
  const lastDay = new Date(year, month + 1, 0).getDate(); // day 0 of next month = last day of this one
  const end = `${year}-${pad(month + 1)}-${pad(lastDay)}`;

  const response = await fetch(
    `http://localhost:8000/reports/monthly.csv?start=${start}&end=${end}`,
    { headers: { Authorization: `Bearer ${token}` } },
  );

  if (response.status === 401) {
    onAuthError();
    return;
  }
  if (!response.ok) {
    console.error("Export failed", response.status);
    return;
  }

  const blob = await response.blob();
  const url = URL.createObjectURL(blob);
  const link = document.createElement("a");
  link.href = url;
  link.download = `expenses_${start}_${end}.csv`;
  document.body.appendChild(link);
  link.click();
  link.remove();
  URL.revokeObjectURL(url);
}

Two date lines earned a second look. getMonth() is 0-indexed, so I add 1 for display. And new Date(year, month + 1, 0) is the classic "last day of the month" trick — day zero of the next month rolls back to the last day of this one. padStart(2, "0") makes sure I send 08, not 8.

I also reused my existing 401 → onAuthError() pattern, so an expired token during export logs me out cleanly instead of silently failing — consistent with the rest of the app. Hand-rolling date strings is fiddly, though; letting the user pick the month (or using a date library) is a natural future upgrade.

Key habits to keep

• Let the database filter, let Python format. The WHERE clause narrows the rows; Python only shapes what's left into CSV. Don't fetch everything and filter in a loop.
• Types validate your inputs for free — use them. Typing a query param as date gave me parsing and a 422 on bad input with zero extra code. Reserve hand-written checks for the logic types can't express (like start <= end).
• 400 vs 422 is a real distinction. "I can't read this" is not the same as "I read it and it's wrong." Return the one that tells the caller the truth.
• Never build a CSV by hand. The csv module exists precisely because commas and quotes in your data will bite you.
• When a loop only does the last thing once, suspect the indentation. In Python, an indent level is control flow, not decoration.
• DRY the moment you see the second copy. Two identical blocks is one bug waiting to be half-fixed. A tiny helper is cheaper than the drift.
• A download that needs auth can't be a plain link. Fetch with the header, turn the response into a Blob, and trigger the download yourself.

Phase 6 turned a dashboard of numbers into something I can actually take with me. Next up: Phase 7, where categories finally enter the schema and the app starts to get opinionated about what I'm spending on.