Phase 5: The Numbers Start Talking

My expense tracker could already remember everything.

It could register users, hash passwords, issue JWTs, isolate one user's expenses from another's, and do complete CRUD through a responsive React UI.

That was the end of Phase 4, Part 2: The Deviation: the app had a face, expired sessions returned me cleanly to login instead of leaving me stranded, the components had clearer jobs, and Tailwind had made the whole thing look less like a form recovered from an abandoned government portal.

Useful progress.

The code for this learning project lives in Smart Expense Manager on GitHub.

But the app still had no opinion.

It could show me every expense I had entered, but it could not answer the first question a normal person would ask an expense manager:

How much have I spent this month?

That is a different kind of feature.

CRUD asks the database to preserve and retrieve individual records.

Insights ask the database to look across many records and produce meaning.

So Phase 5 was where the app stopped being a digital shoebox and started becoming a dashboard.

Or at least that was the plan.

Because before I wrote the first aggregate query, the roadmap tried to make me invent data that did not exist.

The roadmap collision [the field I did not have]

My Phase 5 roadmap called for:

• Total spent this month
• Spend by category
• Top merchants
• Month-over-month comparison
• A chart
• Plain-language insights

Reasonable dashboard features.

One small problem: my Expense model did not have category or merchant fields.

It had:

id
user_id
amount
description
spent_on
created_at
updated_at

Category is not scheduled until Phase 7.

And description is not automatically a merchant just because both are strings. An expense described as Dinner after client meeting cannot honestly become a merchant named Dinner after client meeting because the roadmap would like a leaderboard.

This was the first decision of Phase 5, and no code was involved:

Do not manufacture a schema to make the roadmap look complete.

I could have pulled categorization forward, changed the database, created a migration, updated every schema and form, and turned one learning objective into six simultaneous changes.

That would have made the dashboard look busier.

It would also have destroyed the sequencing I had deliberately designed.

So I took the smallest vertical slice that used fields I actually had:

1. Calculate the authenticated user's total for the current month in SQL.
2. Verify it by hand.
3. Show it in React.
4. Make it refresh after CRUD changes.
5. Then compare the current month with the previous one.

Category spend, merchant rankings, and the category chart remain parked until the model can support them honestly.

Gotcha #15 — A roadmap dependency is not permission to lie to your model

This felt like a planning problem, but it was really a data-integrity problem.

A dashboard can only be as honest as the fields underneath it.

If the database does not distinguish a description from a merchant, the UI should not pretend it does.

The correct move was not to force Phase 5 to match every bullet immediately. It was to identify the dependency, document it, and build the valid slice first.

A roadmap is a guide.

The repository is ground truth.

The first real aggregate [let the database add]

Before this phase, most of my queries returned rows.

Find this expense.

List this user's expenses.

Update this row.

Delete that row.

A monthly total is different. I do not want every matching expense back. I want one value produced from all of them.

That is an aggregate.

The deliberately inefficient version would be:

1. Fetch all the user's expenses.
2. Move every row from SQLite into Python.
3. Loop through them.
4. Check each date.
5. Add each amount.

That would work on my tiny local database.

It would also teach the wrong habit.

Databases are built to filter and aggregate data. SQL can do the work close to where the data lives and return one number instead of shipping a pile of rows into application memory.

The shape I wanted was:

SELECT SUM(amount)
FROM expenses
WHERE user_id = ...
  AND spent_on >= ...
  AND spent_on < ...;

In SQLAlchemy 2.0, that became:

total = db.scalar(
    select(func.coalesce(func.sum(Expense.amount), 0)).where(
        Expense.user_id == current_user.id,
        Expense.spent_on >= month_start,
        Expense.spent_on < next_month_start,
    )
)

There are several ideas packed into that small block.

func.sum(...)

func is SQLAlchemy's bridge to SQL functions.

func.sum(Expense.amount)

means: ask the database to add the matching amount values.

Python is not performing that addition.

SQLite is.

db.scalar(...)

The query returns one cell: the total.

db.scalar(...) executes the statement and gives me that single value directly.

That is a better match than asking for a collection result and then digging one number out of it.

Owner scope still applies

The most important condition is easy to overlook because it is not mathematically interesting:

Expense.user_id == current_user.id

Without it, the total would combine every user's expenses.

The CRUD endpoints already close the IDOR hole by scoping records to the authenticated owner. Aggregate endpoints need the same discipline.

A dashboard number can leak data just as easily as a detail endpoint.

Possibly more quietly, because the user never sees the underlying rows.

Gotcha #16 — SUM() over no rows does not naturally give zero

My human expectation was simple:

No expenses means total spending is zero.

SQL's natural answer is different.

When SUM() has no matching rows, it returns NULL.

That is not the same as zero. NULL means there was no value to aggregate.

Useful database semantics; awkward dashboard semantics.

So I wrapped the sum with COALESCE:

func.coalesce(func.sum(Expense.amount), 0)

COALESCE returns the first non-null value.

If SUM() returns a real total, keep it.

If SUM() returns NULL, use 0.

The endpoint can now promise a number-shaped result even for a brand-new user.

I gave that result an explicit Pydantic response model:

class MonthlyTotalRead(BaseModel):
    total: Decimal

And the endpoint returns:

{
  "total": "6000.00"
}

The quotes are expected. My money values use Decimal, and they serialize as strings so JSON does not quietly introduce floating-point surprises.

The date filter [where most of the danger lived]

Adding numbers was the easy part.

Defining “this month” correctly was the part trying to become a bug.

A tempting filter is:

Expense.spent_on >= month_start

If the current month starts on August 1, that includes every expense from August 1 onward.

Including September.

And October.

And any future-dated expense I accidentally entered.

Gotcha #17 — A lower boundary without an upper boundary is not “this month”

The safe definition is a half-open range:

current_start <= spent_on < next_start

For August 2026:

August 1 <= spent_on < September 1

Why < September 1 instead of calculating the final date of August?

Because months have different lengths. The first date of the next month is a cleaner boundary than branching over 28, 29, 30, or 31 possible final dates.

If spent_on later becomes a timestamp, this same half-open-range idea will also avoid fragile end-of-day precision logic.

This is the same range idea I will reuse later for reports:

• Include the start.
• Exclude the next period's start.

No guessing about 28, 29, 30, or 31 days.

One helper, three boundaries [and my first useful tuple]

The monthly-total endpoint needed two dates:

• Current month start
• Next month start

The month-over-month endpoint would need three:

• Previous month start
• Current month start
• Next month start

I could have copied the date logic into both endpoints.

That would work until one copy got fixed and the other did not.

So I moved it into a helper:

def get_month_boundaries(
    reference_date: date,
) -> tuple[date, date, date]:
    current_start = reference_date.replace(day=1)

    if current_start.month == 1:
        previous_start = date(current_start.year - 1, 12, 1)
    else:
        previous_start = date(
            current_start.year,
            current_start.month - 1,
            1,
        )

    if current_start.month == 12:
        next_start = date(current_start.year + 1, 1, 1)
    else:
        next_start = date(
            current_start.year,
            current_start.month + 1,
            1,
        )

    return previous_start, current_start, next_start

A tuple is an ordered, fixed collection of values.

The function needs to return three related dates, so it groups them into one tuple:

(previous_start, current_start, next_start)

Python lets me unpack that tuple by position:

previous_start, current_start, next_start = get_month_boundaries(
    date.today()
)

For the monthly-total endpoint, I do not need the first value:

_, month_start, next_month_start = get_month_boundaries(date.today())

The underscore is a normal variable with a conventional meaning:

This value exists, but I am intentionally not using it here.

The type hint:

tuple[date, date, date]

makes the contract visible: exactly three date values come back.

Gotcha #18 — January and December are not normal subtraction problems

This does not work in January:

current_month - 1

January is month 1. Month 0 does not exist.

December has the mirror problem when calculating the next month: month 13 does not exist.

That is why the helper has explicit rollover branches:

January 2026 previous start -> December 1, 2025
December 2026 next start   -> January 1, 2027

My current test data happened to be in August.

Production data will eventually arrive in January.

Calendar code that works only in the month it was written is not calendar code. It is a seasonal demo.

There is still one deliberate limitation: date.today() uses the backend machine's local date. For this local learning build, that is acceptable. A production version should define the application's business timezone explicitly so a user near a month boundary does not receive a total based on the server's calendar instead of their own.

Proving the total [not just admiring a 200]

The request returned HTTP 200.

That proved the route existed and the response matched the schema.

It did not prove the number was right.

So I checked the relevant expense amounts by hand and compared their sum with:

GET /insights/monthly-total

Then I created an adversarial row:

Description: Previous-month test
Amount: 999.99
Spent on: 2026-07-15

My current-month total had to remain unchanged.

It did.

Then I deleted only that temporary row.

This was the adversarial-testing habit—the project’s running “GAN analysis” joke—in its simplest useful form: give the query awkward data that should be rejected by its boundaries.

A test with only current-month expenses cannot prove that previous-month filtering works.

It only proves that current-month rows are included.

The rejected row was the evidence.

Putting the number in React [and keeping it alive]

The backend could answer the question now.

The user still had to open /docs to hear it.

So I added a monthly-total request inside ExpenseList's existing fetchExpenses() flow.

That placement was deliberate.

fetchExpenses() already runs:

• When the expense screen loads
• After adding an expense
• After editing an expense
• After deleting an expense

If the total is fetched in the same refresh path, it stays synchronized with the list without inventing another global state owner.

The state is local to the component that displays it:

const [monthlyTotal, setMonthlyTotal] = useState("0.00");

The intended authenticated request follows the same shape as the expense request. This is the corrected form I want the code to reach; the later source audit found that the committed file does not fully match it:

const totalResponse = await fetch(
  "http://localhost:8000/insights/monthly-total",
  {
    headers: { Authorization: `Bearer ${token}` },
  },
);

if (totalResponse.status === 401) {
  onAuthError();
  return;
}

if (!totalResponse.ok) {
  throw new Error(
    `Monthly total request failed: ${totalResponse.status}`,
  );
}

const totalData = await totalResponse.json();
setMonthlyTotal(totalData.total);

And the first dashboard metric became a small card:

<div className="mb-6 rounded-lg bg-blue-50 p-4">
  <p className="text-sm font-medium text-blue-700">
    Spent this month
  </p>
  <p className="mt-1 text-3xl font-bold text-blue-900">
    {monthlyTotal}
  </p>
</div>

I tested it with another temporary expense:

Description: Monthly metric refresh test
Amount: 1.00
Spent on: 2026-08-07

The total increased by exactly 1.00 without a manual page refresh.

Then I deleted the temporary row and watched the total return.

That was the point where the dashboard stopped being decoration.

It was now a live projection of database state.

Month-over-month [two totals, one comparison]

The next valid roadmap item was month-over-month comparison.

The endpoint needed four values:

class MonthOverMonthRead(BaseModel):
    current_month_total: Decimal
    previous_month_total: Decimal
    change_amount: Decimal
    change_percentage: Decimal | None

The database still performs the aggregation.

One query sums the previous-month range:

previous_total = db.scalar(
    select(func.coalesce(func.sum(Expense.amount), 0)).where(
        Expense.user_id == current_user.id,
        Expense.spent_on >= previous_start,
        Expense.spent_on < current_start,
    )
)

A second query sums the current-month range:

current_total = db.scalar(
    select(func.coalesce(func.sum(Expense.amount), 0)).where(
        Expense.user_id == current_user.id,
        Expense.spent_on >= current_start,
        Expense.spent_on < next_start,
    )
)

Then Python compares the two already-aggregated numbers:

change_amount = current_total - previous_total

This is an important boundary between database work and application work.

SQL is good at finding and summing the relevant rows.

Python is good at applying the small piece of business logic to the two totals.

I am not hauling raw expenses into Python to recreate SUM() badly.

I am combining two scalar results.

The percentage trap [zero has opinions]

The percentage formula is ordinary:

(current - previous) / previous × 100

The denominator is not ordinary when the user had no expenses last month.

Gotcha #19 — “No previous spending” is not a zero-percent change

Suppose:

Previous month: 0
Current month: 6000

It is tempting to say spending increased by 100%.

It did not.

There is no finite percentage that turns zero into 6000, because the formula requires division by zero.

Python will reject that calculation, and mathematically it is undefined.

So the endpoint handles that state explicitly:

change_percentage = None

if previous_total != 0:
    change_percentage = round(
        (change_amount / previous_total) * 100,
        2,
    )

The JSON response uses null when there is no valid percentage baseline:

{
  "current_month_total": "6000.00",
  "previous_month_total": "0",
  "change_amount": "6000.00",
  "change_percentage": null
}

That gives the frontend enough information to say something honest:

no previous-month spending to compare

Not 0% change.

Not 100% increase.

Not Infinity%, which would at least be memorable.

Signed data versus human language

With real previous-month data, the endpoint might return:

{
  "current_month_total": "6000.00",
  "previous_month_total": "10000.70",
  "change_amount": "-4000.70",
  "change_percentage": "-40.00"
}

The math is correct:

6000.00 - 10000.70 = -4000.70

And:

-4000.70 / 10000.70 × 100 ≈ -40%

But displaying this:

-4000.70
40% less than last month

felt clumsy.

The negative sign and the word less communicate the same direction twice.

Gotcha #20 — Correct data can still be unclear UI

The backend should preserve the signed value because the sign is useful for logic.

The frontend can present its absolute magnitude because the words provide the direction.

So the amount display became:

{Math.abs(Number(monthComparison.change_amount)).toFixed(2)}

Three small transformations:

• Number(...) converts the API's decimal string into a JavaScript number.
• Math.abs(...) removes the sign for presentation.
• toFixed(2) displays exactly two decimal places.

The API remains truthful:

-4000.70

The card becomes readable:

4000.70
40% less than last month

Data representation and presentation are related, but they are not the same job.

The plain-English helper [let the card speak]

The comparison API returns one object containing related values, so React stores it as object state:

const [monthComparison, setMonthComparison] = useState(null);

null means the request has not produced data yet.

The card renders only when the object exists:

{monthComparison && (
  // comparison card
)}

For the text, I used a small pure helper:

function buildComparisonText(comparison) {
  if (comparison.change_percentage === null) {
    return "no previous-month spending to compare";
  }

  const percentage = Number(comparison.change_percentage);

  if (percentage > 0) {
    return `${Math.abs(percentage)}% more than last month`;
  }

  if (percentage < 0) {
    return `${Math.abs(percentage)}% less than last month`;
  }

  return "Spending is unchanged from last month";
}

A pure helper receives data and returns a result. It does not change React state, call the API, or mutate the input.

That makes the possible messages explicit:

• More than last month
• Less than last month
• Unchanged
• No valid previous-month baseline

The frontend is no longer just printing an aggregate.

It is interpreting it for a person.

That was one of the actual Phase 5 goals: plain-language insight statements, not just a number farm.

The second adversarial test [make the card move]

I reused the controlled previous-month row:

Description: Comparison refresh test
Amount: 100.00
Spent on: 2026-07-15

That row had to do several things at once:

1. Appear in previous_month_total.
2. Stay out of current_month_total.
3. Change change_amount correctly.
4. Produce a valid percentage instead of null.
5. Refresh the React card automatically after creation.
6. Return the card to its original state after deletion.

It passed all six.

This is the part of testing I am beginning to appreciate: one carefully chosen row can challenge several assumptions at once.

Then I ran:

npm run lint

And from the repository root:

git diff --check

Both passed during the feature work.

I committed the backend and frontend slices separately so the history preserved the vertical progression:

Add monthly spending total endpoint
Show monthly spending total in dashboard
Add month-over-month spending insight
Add month-over-mmonth dashboard insight

Yes, the final commit subject contains mmonth.

Git is a historical record, including the part where my fingers became optimistic.

I am not rewriting published history over a spelling mistake.

The audit after the victory lap [source code gets the final vote]

This article would be dishonest if it ended with “everything is hardened and perfect.”

When I reread the actual repository to prepare this write-up, I found several narrow cleanup items that the happy-path tests had not exposed.

The important one:

My previous article said I had placed the 401 guard into all authenticated calls, including update.

The current repository shows that handleUpdate() does not have the same explicit 401 branch before its generic failure path.

I do not know whether that guard was lost during a later edit or whether my earlier statement outran the committed code.

I do know which source wins the disagreement.

The repository.

I also found smaller issues in ExpenseList.jsx:

roundded-xl
text-3x1
Comapred with last month

And one broken error-message interpolation:

`Monthly total request failed: $(totalResponse.status)`

The interpolation syntax should use braces:

`Monthly total request failed: ${totalResponse.status}`

There is also one loose status comparison using == where === is clearer and safer.

Gotcha #21 — Passing the happy path does not mean the source is clean

None of those typos stopped my normal insight flow from working.

That is exactly why they survived.

• A misspelled Tailwind utility is ignored rather than crashing React.
• A misspelled label still renders.
• A broken error string stays hidden until that error branch runs.
• A missing 401 branch stays invisible while the token remains valid.

This is the adversarial lesson arriving late but usefully:

Code paths I did not trigger still deserve inspection.

So the next session begins with a narrow QA cleanup before Phase 6.

Not a giant refactor.

Not a shame spiral.

One focused correction at a time, with the same test-and-commit discipline.

The point of build-in-public is not to create the illusion that every checkpoint was flawless.

It is to preserve what I learned when the flaw became visible.

What Phase 5 actually completed

With the current schema, the app can now answer:

• How much did this authenticated user spend this month?
• How much did they spend in the previous month?
• What is the signed difference?
• What is the percentage change when a valid baseline exists?
• Is spending higher, lower, unchanged, or not yet comparable?

The dashboard updates those answers after expense mutations.

The database performs the aggregation.

The API remains owner-scoped.

The frontend translates the result into language.

What Phase 5 did not complete:

• Spend by category
• Top merchants
• Category chart

Those remain blocked by the schema, not forgotten.

Category arrives in Phase 7.

Merchant does not exist yet as a real field.

I am leaving both visible in the backlog rather than disguising descriptions as merchant data.

The mental model I am keeping

The biggest lesson was not the syntax of SUM().

It was the division of responsibility:

Database:
Find the correct owner's rows inside exact date boundaries and aggregate them.

Backend:
Define the business comparison, handle undefined cases, and return a stable schema.

Frontend:
Fetch the result, keep it synchronized, and translate signed data into readable language.

Or, shorter:

SQL finds and adds. Python decides. React explains.

That is the first architecture sentence I have written in this project that feels genuinely reusable.

Stuff I want to remember [the honest takeaways]

• An aggregate turns many rows into one result.
• Let the database perform SUM() instead of fetching every row into Python.
• db.scalar(...) is the right shape when the query returns one value.
• SUM() over no rows returns NULL; COALESCE(..., 0) gives the dashboard a usable zero.
• Aggregate endpoints need owner scoping just as much as CRUD endpoints.
• “This month” needs both boundaries: >= current_start and < next_start.
• Half-open ranges avoid month-length and end-of-day traps.
• January and December require explicit year rollover.
• A tuple can return several fixed, ordered values from one helper.
• Tuple unpacking assigns by position; _ conventionally marks an intentionally unused value.
• SQL should aggregate the rows; Python can compare the resulting scalar totals.
• Percentage change from a zero baseline is undefined, not automatically 0% or 100%.
• Preserve signed values in the API when they are useful for logic.
• Presentation can use an absolute value when words already communicate direction.
• Object state is useful when one API response contains several related values.
• A small pure helper can turn numeric states into predictable user-facing language.
• Put insight fetching in the existing refresh flow if the metrics must update after CRUD mutations.
• Test rejection boundaries with deliberately awkward data, not only happy-path rows.
• A 200 proves the endpoint responded; hand calculation helps prove the number.
• A passing happy path does not inspect dormant error branches.
• The repository outranks an article, handoff, roadmap, or memory when they disagree.
• A roadmap dependency is not permission to invent data.
• Commit at green checkpoints, subject plus why.
• Do not rewrite public Git history just to hide a typo.

Next up: Phase 6 [make the data leave the app]

The next roadmap phase is reporting and CSV export.

The planned vertical path is:

1. Add an authenticated, owner-scoped report endpoint filtered by a date range.
2. Verify that range manually.
3. Generate CSV with Python's csv module.
4. Return it as a streaming response with download headers.
5. Add an Export CSV button in React.

But first: the narrow QA cleanup this article's source audit exposed.

Because the app has learned to answer questions now.

Before I teach it to export those answers, I want every error path to tell the truth too.

The digital shoebox has become a dashboard.

Next, it becomes a report.