tosane932

Posted on Aug 21 Originally published at qiita.com

📝Growing pytest into an "Incident Prevention Log" — Stage 2: Strengthening Sales and Product Registration to 51 Tests

#python #flask #pytest #githubactions

https://github.com/tosane932/sales_data_app

Hello from Japan 🇯🇵

I’m a truck driver in Japan, and I’m teaching myself web application development with Python and Flask while continuing to work full-time.

This article is part of a series documenting how I’ve been strengthening pytest in my personal project — not just to increase the number of tests, but to turn them into a kind of “incident prevention log.”

pytest Improvement Series

Stage 1

https://qiita.com/tosane932/items/f3de1e190873a90de39f
Stage 2

https://qiita.com/tosane932/items/b91261e7103df5792f7d
Stage 3

https://qiita.com/tosane932/items/6d1ca5490979c8cf9d62
Stage 4

https://qiita.com/tosane932/items/372270330e73583a227f
Stage 5

https://qiita.com/tosane932/items/85fd24c7baa6fe7c76a7

Introduction

This article was originally published in Japanese on Qiita and has been translated and adapted for DEV Community.

I currently work as a truck driver while teaching myself web application development with Python and Flask.

At the time I wrote the original version of this article, I had logged 167 hours of study.

When I reviewed the pytest suite in my personal project, I realized that there were only three tests.

That made me wonder:

"I have tests, but are they actually protecting the important parts of the application?"

So I decided to stop thinking of pytest as just a tool for checking whether something works.

Instead, I started treating it as an:

"incident prevention log" — a record that helps prevent previously discovered problems from silently returning.

In Stage 1, I increased the number of tests from 3 to 9 and added checks around:

prompts sent to the AI
XSS protection
Gemini API integration using mocks
running the full pytest suite with GitHub Actions

This article continues from there with Stage 2.

By the end of this stage, the full suite had grown to:

51 passed

But increasing the number of tests was not the main goal.

What I really wanted to verify was this:

when handling sales and product data, invalid input or database save failures should not leave the database in an inconsistent state.

What I Checked in Stage 2

Broadly, I focused on the following areas:

rejecting invalid sales input
preventing sales from being registered against the wrong product
preventing duplicate sales for the same product and date at the database level
rolling back changes when database saves fail
rejecting invalid product registration and update input
preserving historical sales after a product is discontinued
verifying that dashboard aggregation matches the database

At first, I thought:

"I'll just add a few more tests around sales input."

But as I checked each part individually, I realized that the flow connected all the way through:

input → database save → history preservation → aggregated output

So the scope became much larger than I originally expected.

1. If Even One Sales Entry Is Invalid, Reject the Entire Request

The first thing I checked was the data submitted from the sales input form.

In this article, I'll refer to the Flask endpoint that receives the submitted form data as the sales POST endpoint.

For example, imagine submitting:

Product A: 5 units
Product B: 3 units

Flask receives that data and saves it to the database.

The problem was what happened when:

only part of the submitted data was invalid.

The cases I checked included:

invalid dates
empty quantities
non-numeric quantities
negative quantities
decimal quantities
mismatched numbers of product IDs and quantities
empty product IDs
non-numeric product IDs
duplicate product IDs
empty product and quantity arrays

Before the fix, there were paths where decimal values could be converted to integers, or where invalid rows were skipped while valid rows were still saved.

So I changed the policy to:

If even one value is invalid, reject the entire request.

The pytest tests do more than confirm that the endpoint returns HTTP 400.

They also verify that:

the contents of DailySales are unchanged before and after the POST request.

For example:

Product A: currently 5, requested update to 9
Product B: invalid data

↓ Reject the entire request

Product A: remains 5
Product B: nothing is saved

This ensures that partial updates do not occur.

2. "The Product Exists" Wasn't Enough

Next, I checked which products were allowed to receive sales entries.

Even if a product ID is numerically valid, I don't want sales registered against:

a product that does not exist
a product belonging to a different year or month
a discontinued product

So I added pytest cases that reject all of these with HTTP 400:

Unknown product
Product from another month
Discontinued product

Again, even if valid products are submitted together with an invalid one, DailySales must remain unchanged.

If a discontinued product already has historical sales data, those records must also remain untouched.

3. Teaching the Database That There Can Be Only One Sales Record per Product and Date

The application already had logic that worked like this:

If sales already exist for the same product and date, update the quantity instead of inserting another row.

However, the database itself did not have a rule preventing duplicate rows for:

same product
+
same date

So I added a unique constraint to:

(product_id, date)

A unique constraint basically tells the database:

"This combination may exist only once."

This gives me two layers of protection:

Application logic
+
Database constraint

In pytest, I bypass the Flask UI and attempt to insert two sales records with the same product and date directly into the test database.

The test confirms that the database raises:

IntegrityError

For duplicate sales on the same product and date, even if application-level logic is bypassed, the database itself now rejects the duplicate.

Creating that second layer of protection was the goal here.

4. Testing the Migration in an Isolated PostgreSQL Environment

Because I changed the database schema, I also added an Alembic migration.

I think of migrations as:

a mechanism for recording database schema changes and applying them in a controlled order.

This migration added the unique constraint to:

DailySales(product_id, date)

However, I did not want to immediately test it against the PostgreSQL database I normally use.

Instead, I created a separate Docker environment and isolated:

the container
the network
the database
the volume

Then I tested:

upgrading from an empty database to the latest schema
upgrading from a state similar to an existing database
downgrading
upgrading again
confirming that existing record counts and contents remained unchanged

I also attempted a duplicate INSERT and confirmed that PostgreSQL itself rejected it as a unique constraint violation.

After the test, I removed the isolated containers and volumes.

The idea was:

instead of trying something in the normal environment and recovering if it fails, test it first somewhere where failure cannot affect the normal environment.

5. Roll Back When Saving Fails

The next area I checked was:

what happens when the input is valid, but the final database save fails?

When saving changes to the database, the application eventually calls:

db.session.commit()

In simple terms, this means:

Make these changes permanent.

But commit() can fail too.

Sales POST

Imagine this sequence:

Product A: 5 → preparing to update to 9
Product B: preparing to insert 7

Then the final commit() fails.

In that situation, the application needs to explicitly cancel the pending changes and restore the database session to a usable state.

So I added:

db.session.rollback()

In pytest, I intentionally make commit() fail and then verify:

Product A DailySales: still quantity=5
Product B DailySales: not inserted
DailySales count: unchanged

Product POST

I also intentionally make commit() fail during product registration and updates.

The tests verify that the following remain unchanged from before the POST request:

existing product name
price
active/inactive status
newly added products
DailySales

In other words:

I added regression tests not only for input validation, but also for failures during the final save operation, ensuring that incomplete changes are not left behind.

6. Testing 17 Types of Invalid Product Input

I didn't stop at sales input.

I also reviewed the product registration and update form.

Before the fix, there were insufficiently protected paths involving cases such as:

zip() silently ignoring extra values when field counts did not match
non-numeric product IDs
product IDs that did not exist
product IDs belonging to another month
the same product ID being submitted multiple times
invalid prices
empty or non-numeric year and month
month=0
month=13

So I added 17 invalid-input cases.

Before the fix:

17 failed

After the fix:

17 passed

Again, the important part is not only returning HTTP 400.

The tests also confirm that:

both Product and DailySales remain unchanged before and after the POST request.

The new flow became:

Validate everything
↓
Everything is valid
↓
Only then modify the database

I also tested valid cases, including:

updating an existing product
adding a new product
products priced at 0
ensuring existing sales history is not modified

7. Keep Historical Sales Even After a Product Is Discontinued

In this application, discontinuing a product does not delete it from the database.

Instead, I use a form of soft deletion:

is_active=True
↓
is_active=False

There is a reason for this.

If the product itself were deleted, handling historical sales linked to that product would become much more difficult.

So I added pytest cases verifying that:

the product row still exists
the Product ID does not change
is_active=False
historical DailySales remain

I also tested what happens when an existing Product ID for a discontinued product is submitted again.

Instead of:

Create a new product

the application should do:

Same Product ID
↓
is_active=True

The tests also verify that:

the number of Product records does not increase
historical DailySales remain unchanged
the same Product ID continues to be used

These two tests passed immediately when I added them.

So in this case, I did not fix production code.

Instead:

I recorded behavior that was already correct so that future changes cannot accidentally break it.

8. Finally, Verifying Dashboard Aggregation

At the end of Stage 2, I also tested the dashboard API.

I inserted sales data into the database like this:

Product A
3 + 7 = 10

Product B
5

Then I requested:

/api/dashboard-data?year=2026&month=8

and verified with pytest that the API returned:

Product A: 10
Product B: 5

I also checked that the ordering and values of:

ranked_sales
chart_labels
chart_values

matched correctly.

Historical sales for discontinued products remain included in the aggregation.

And when no year/month filter is provided, I also verify that sales across:

all periods

are included in the aggregation.

All four dashboard API tests passed immediately when I added them.

Again, this was a case where:

the current implementation already behaved correctly, so I recorded that behavior as regression tests to prevent it from being broken later.

These API tests also verify that the Gemini client is not called.

That lets me test the database aggregation logic independently from the external AI API.

From 9 Tests to 51

At the end of Stage 1:

9 passed

At the end of Stage 2:

51 passed

But this does not mean:

51 tests = a safe application

pytest can only check what has actually been written as a test.

During this work, I also discovered several areas where the specification was still unclear:

how to aggregate different Product IDs that share the same product name
maximum product-name length
acceptable range for year
ranking order when products have identical quantities
API behavior when there are zero sales

I did not let Codex decide these specifications on its own and expand the scope of the current work.

Instead, I left them as unresolved items for later.

The number of tests itself matters less than understanding:

what kind of incident each test is supposed to prevent.

What I Learned from Stage 2

I did not begin Stage 2 with a perfectly designed, large test plan.

Instead, the process looked more like this:

"If this case is covered, what about this one?"

Every time I checked one condition, another possible gap appeared.

Then I added another test.

Eventually, I ended up testing the whole flow:

Input
↓
Product validation
↓
Database save
↓
Rollback on failure
↓
History preservation
↓
Aggregation

Thinking about it through my work as a truck driver, it feels similar to moving beyond simply saying:

"Be careful not to cause an accident."

Instead, you build mechanisms such as:

don't allow dangerous cargo to be loaded incorrectly
don't let the vehicle depart if the load is unsafe
stop the process if something goes wrong
preserve past records
check the final result

That feels much closer to what I'm trying to do with pytest.

Summary

In Stage 2 of strengthening my pytest suite, I verified:

full validation before processing sales POST requests
product existence, year/month association, and active status
a database unique constraint on (product_id, date)
an Alembic migration
upgrade and downgrade testing in isolated PostgreSQL
rollback behavior for sales POST requests
17 invalid-input cases for product POST requests
rollback behavior for product POST requests
soft deletion and preservation of historical sales
database aggregation in the dashboard API

The full pytest suite grew from:

9 passed
↓
51 passed

One thing became much clearer to me during this stage:

Instead of relying on people to remember not to repeat the same mistake, it is more effective to build a mechanism that automatically stops the system when the same dangerous state appears again.

At the same time, there are still specifications I haven't decided and areas I haven't tested yet.

In the next stage, I plan to look at authentication, authorization, CSRF protection, and questions such as:

"Who is actually allowed to perform this operation?"

My work on growing pytest into an "incident prevention log" still has a little way to go.

What I Was Thinking About at the Time — on Zenn

This article focused on the actual verification work I performed during Stage 2, including invalid input, rollback behavior, database constraints, and preserving historical data.

On Zenn, I looked at the same stage from a slightly different angle:

Why did I stop thinking "returning an error is enough" and start checking what the database looks like after the failure?

It is more of a reflection on how my thinking changed during development.

https://zenn.dev/tosane932/articles/abc04ddc9e74f4