DEV Community: tosane932

Can You Explain the System AI Built for You? — I Audited My Own Web App with 20 Questions

tosane932 — Sun, 30 Aug 2026 19:51:19 +0000

Hello from Japan 🇯🇵

AI has made it possible to build web applications much faster than before.

Paste in an error, and AI can help investigate the cause.

Say, "I want to add a feature like this," and it can write the code.

It can help create a Dockerfile, pytest tests, authentication logic, and more.

It's incredibly useful.

But while continuing to build my own applications, one question started bothering me:

Can I actually explain the system I built?

Code working correctly and understanding why it works are not the same thing.

So this time, I decided to audit my own understanding of the Flask application I'm developing.

The rule was simple:

Answer 20 questions in my own words—without looking at the code, without searching, and without asking AI.

Only after answering would I compare my understanding with the actual implementation.

The results were surprisingly interesting.

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

The Web Application I Audited

The application is a bakery sales-management system I've been building as a personal project.

Its basic workflow is:

Register products
↓
Enter daily sales quantities
↓
Save sales data
↓
Visualize rankings and charts
↓
Send sales data to the Gemini API
↓
Display suggestions that may help with business decisions

The main technologies include:

Python / Flask
PostgreSQL
SQLAlchemy
Flask-Login
Flask-WTF
Alembic / Flask-Migrate
Docker
Gunicorn
Render
Gemini API
pytest
GitHub Actions

The application works.

At the time of this audit, it had 91 pytest tests.

But this time, I wasn't trying to answer:

"Do I know the names of the technologies I'm using?"

I wanted to answer:

"Can I explain why each one is there and what it actually does?"

I Defined Three Levels of Understanding

I evaluated myself using three levels.

Rating	Meaning
🟢	I can explain it in my own words
🟡	I understand the general direction, but some parts are vague or misunderstood
🔴	I cannot explain it, or my understanding is incorrect

The most important rule was:

Do not look at the code first.

If I looked at the implementation before answering, I could always say:

"It works this way because the code here says so."

But that would not tell me whether I actually understood the system beforehand.

So I first answered entirely from memory.

Only afterward did I inspect the real code.

The 20 Questions

These were the questions I used.

#	Question	Initial Rating
1	Whose problem does this system solve, and what problem is it?	🟡
2	Can I explain it to a non-technical store manager in 30 seconds?	🟡
3	How is it different from managing everything in Excel?	🟡
4	What remains usable if the AI becomes unavailable?	🟢
5	Where does entered sales data go?	🟡
6	How does data travel from the browser to the database?	🔴
7	What is GitHub used for?	🟡
8	Why does the app keep running when my own PC is turned off?	🟡
9	Why am I using Docker?	🟢
10	What is the difference between Docker and Render?	🟡
11	What are the different roles of GitHub, Render, and PostgreSQL?	🟢
12	What does SQLAlchemy do?	🔴
13	What does the login system protect?	🟡
14	Why does the login state remain when moving between pages?	🔴
15	What exactly am I sending to Gemini?	🟡
16	What is an API?	🔴
17	Why shouldn't an API key be stored on GitHub?	🟢
18	What are the roles of environment variables and `.gitignore`?	🟡
19	Why did I grow pytest to 91 tests?	🟢
20	If all pytest tests are GREEN, does that mean there are no bugs?	🟢

My initial result was:

🟢 6
🟡 10
🔴 4

The purpose was not to compete for a score.

I used it as a diagnostic tool to find:

where my own understanding stopped.

I Could Explain the User-Facing Purpose Relatively Well

One interesting result was that I could explain fairly well:

why I had built the system in the first place.

For example, I understood the role of AI as:

It is not a system where AI makes every decision. It provides reference suggestions based on sales data so that the store manager can think about what to try next.

I could also explain that:

Even if the AI feature becomes unavailable, the core functions—product registration, sales entry, rankings, and charts—still remain usable.

So I had a reasonable understanding of:

the purpose of the system from the user's perspective.

The problems appeared behind the scenes.

I Thought Sales Data Went "Through GitHub" to the Database

One question was:

How does sales data entered in the browser reach PostgreSQL?

My first explanation was something like:

It goes through GitHub and then reaches the database.

That was wrong.

The actual path is:

Browser
↓
Flask application
↓
Input validation
↓
SQLAlchemy
↓
PostgreSQL

GitHub is not part of this runtime data path.

I now separate the roles like this:

GitHub
= Storage for source code and change history

Render
= A place where the web application runs on the internet

PostgreSQL
= A warehouse that stores product and sales data

I knew all three technology names.

But I could not explain them as parts of one coherent system.

That was the gap.

I Also Thought Docker Sent My Finished Local Container Directly to Render

I had another misunderstanding about Docker.

My mental model was roughly:

I build a Docker container on my PC, then send that completed container directly to Render.

But when I went through the actual Dockerfile line by line, my understanding changed.

For example:

FROM python:3.12-slim AS builder

creates a builder stage based on Python 3.12 slim.

Then:

COPY requirements.txt .
RUN pip install --no-cache-dir --user -r requirements.txt

prepares the required Python packages.

After that:

FROM python:3.12-slim

creates another clean stage for production.

In other words, this is a multi-stage build.

I understood it using a Mini 4WD analogy:

builder
= Assembly pit

gcc / libpq-dev
= Tools such as cutters and files needed during assembly

requirements.txt
= Parts list

Production stage
= The actual machine that enters the race

There is no need to load every assembly tool onto the race car.

Only the required finished components are moved into the production stage:

COPY --from=builder /root/.local /root/.local

Then:

COPY . .

copies the application files from the Docker build context into the production image.

I now think of a Dockerfile as:

not the finished cargo itself, but a loading and assembly instruction sheet describing how the cargo should be prepared.

That mental model made much more sense to me.

I Knew the Name SQLAlchemy, but I Couldn't Explain It

SQLAlchemy was one of the biggest discoveries in this exercise.

I had seen the name many times.

It appears throughout my own application.

But when I asked myself:

What is SQLAlchemy?

I couldn't explain it.

Looking at the real code, I found:

from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

and then:

db.init_app(app)

to connect SQLAlchemy with the Flask application.

When saving sales data, the application uses:

db.session.add(sale)
db.session.commit()

My current mental model is:

PostgreSQL
= A warehouse where data is stored

SQLAlchemy
= An intermediary between Python and the database

Alchemy literally means "alchemy," so I decided to remember it as:

a SQL alchemist that helps Python work with database data in a convenient form.

I also started thinking of these operations like this:

db.session.add()
= Put something onto the shipping line as a candidate for storage

commit()
= Finalize this transaction

rollback()
= Cancel the uncommitted changes from this transaction

Strictly speaking, SQL can sometimes be sent to the database before commit().

But the transaction as a whole has not yet been committed.

Before this exercise, I could list SQLAlchemy under "technologies used."

I couldn't explain it this far.

I Was Confusing Cookies with Browser Cache

Authentication exposed another major misunderstanding.

At first, I explained login persistence as:

The browser stores the login information in its cache.

But browser cache and cookies are different things.

Then I inspected the actual implementation and found another mismatch:

the main branch at the time of this audit was not using Google OAuth.

It was using a single-administrator login system.

So even my belief that:

"My application uses Google login."

was out of sync with the implementation I was actually auditing.

I now think of the responsibilities like this:

Cookie
= Entry ticket or wristband held by the browser

Session
= State such as "this user is logged in"

Flask-Login
= Staff who check that state

@login_required
= A gate saying "only authenticated users may continue"

For example:

@login_required
def dashboard():

means that if an unauthenticated user directly visits /dashboard, Flask-Login checks their authentication state.

The implementation at the time of this audit also stored a SHA-256 fingerprint derived from ADMIN_PASSWORD_HASH in the session and compared it with a fingerprint derived from the current ADMIN_PASSWORD_HASH value.

A technical note about Flask sessions

Flask's default session is not necessarily a separate "box" stored on the server.

By default, Flask stores session data in a signed—not encrypted—cookie on the browser side.

For the purpose of understanding responsibilities, I separate the concepts like this:

Cookie = entry ticket

Session = entry state

This was another area where:

"It works" had made me think I understood it.

I Was Mixing Up APIs and API Keys

When I asked myself what an API was, my first answer was:

An API is something like a key.

That was another misunderstanding.

Now I separate them like this:

API
= A defined interface through which different applications or services communicate

API key
= Authentication information used to access that interface

Environment variable
= A way to keep values such as API keys outside the source code

The application retrieves the Gemini API key using:

api_key = os.environ.get("GEMINI_API_KEY")

and prepares the Gemini client with:

client = genai.Client(api_key=api_key)

So this does not mean:

Use the API key to log into the operating system.

os.environ.get() means:

Retrieve the value named GEMINI_API_KEY from the environment variables available to the running application.

`.gitignore` Is Not a Feature That Hides Secrets

I also had an inaccurate understanding of .gitignore.

I thought of it as:

Something that hides variables people should not see.

That's not quite right.

.gitignore is:

a set of rules telling Git which untracked files and directories it should ignore.

The .gitignore I inspected during this audit included entries such as:

過去売上高/
*.xlsx
*.db
.env

Using a logistics analogy:

It is a "do not load" list for the truck heading to GitHub.

.gitignore itself does not encrypt anything.

And if secret information has already been committed to Git, adding the file to .gitignore afterward does not remove that secret from the existing Git history.

Trying to explain it forced me to understand that distinction much more clearly.

What Am I Actually Sending to Gemini?

I also inspected the AI part of the application.

Initially, I thought:

I'm sending the prompt written in the dashboard together with the sales numbers to Gemini.

In reality, the Gemini prompt-generation logic is separated into prompts.py.

For example:

def build_sales_prompt(sales_summary: str) -> str:

Then app.py creates a sales summary:

sales_summary = ", ".join(
    [f"{name}: {qty}個" for name, qty in ranked_sales]
)

prompt = build_sales_prompt(sales_summary)

This inserts product names and sales quantities into the prompt.

For example:

Croissant: 80 units
White bread: 65 units
Melon bread: 42 units

Then the request is sent to Gemini:

response = client.models.generate_content(
    model=config.GEMINI_MODEL,
    contents=prompt,
)

and the returned text is displayed on the dashboard.

So the flow is:

PostgreSQL
↓
Aggregate sales data in Flask
↓
Product names + sales quantities
↓
Build instructions in prompts.py
↓
Gemini API
↓
Receive suggestions
↓
Display them on the dashboard

Saying "It Analyzes Current Social Media Trends" Was an Overstatement

This was one of the biggest discoveries in the audit.

The prompt I inspected at the time asked Gemini to consider things such as:

Recent food trends
Potential social media appeal
Patterns involving weekdays, seasons, and holidays

Because of that wording, I had started thinking:

Gemini is actively looking up current social media and internet trends and using them in its analysis.

But when I inspected the implementation, the store-specific information sent to Gemini by this feature was basically:

product names + aggregated sales quantities

There was also no implementation in this flow that retrieved current web-search results or social media data and passed them to Gemini.

So:

Writing "consider recent trends" in a prompt

and:

actually searching the current web and analyzing fresh information

are different things.

I noticed another related issue.

The prompt also asked Gemini to consider:

latent demand based on weekday, seasonal, and holiday patterns

But the store data sent to Gemini was primarily product names and aggregated sales quantities.

The application was not directly sending detailed daily or weekday-level sales records to Gemini in this flow.

So:

Asking the AI to analyze weekdays

and:

giving the AI enough data to perform weekday analysis

are also different things.

The lesson was:

Writing "search for it" in a prompt
≠
Implementing search

Writing "analyze weekdays" in a prompt
≠
Providing enough weekday-level data for that analysis

This was probably the clearest example in the entire audit of:

a difference between what I thought my working application could do and what the implementation actually did.

If I genuinely want future recommendations to include current web information or detailed weekday trends, I would need additional design work such as:

Explicitly integrating a web-search capability
Sending daily or weekday-level sales data
Defining the analysis period and conditions clearly

Even 91 GREEN pytest Tests Do Not Mean "Safe"

One area I could explain relatively well was pytest.

At the time of this audit, the application had 91 pytest tests.

Why had I grown the suite that far?

Because I assumed:

Users will not always operate the system exactly the way I expect them to.

Examples include:

Sending a product ID that does not exist
Registering sales against a product from another month
Registering sales for a discontinued product
Directly accessing a protected URL without authentication
Tampering with a CSRF token
The Gemini API returning an error
A database operation failing halfway through

In truck driving, I cannot think about safety only in terms of:

"Normally, nobody would do that."

Unexpected things happen.

So I try to think about dangerous scenarios before they occur.

I approach pytest in a similar way.

I have been growing it into:

an "incident prevention log" that records failure patterns before they can become repeat incidents.

But:

91 passed

does not mean:

There are no bugs.

It means:

All 91 expectations represented by the tests at that point passed in that run.

If I discover a new blind spot, I fix it and add another regression test.

I used to feel reassured simply by seeing GREEN.

Now I try to ask:

"What exactly does this GREEN result guarantee?"

Not Being Able to Explain Something Wasn't Embarrassing — It Showed Me What to Learn Next

At first, I felt a little embarrassed.

This was my own application, yet I discovered that:

I couldn't explain SQLAlchemy
I was confusing cookies with cache
I was confusing APIs with API keys
I thought GitHub was part of the sales-data path
I thought my local Docker container was simply sent to Render
I thought the application was retrieving current social media trends

But my perspective changed during the exercise.

Finding something I don't understand means I've found the next place to learn.

What seems more dangerous is:

continuing to use something while only thinking that I understand it.

Being Able to Explain Something Simply Does Not Mean Complete Understanding

There is an important limitation here.

Being able to say:

PostgreSQL is a warehouse.

does not mean I completely understand PostgreSQL.

Calling SQLAlchemy an intermediary does not mean I understand all of its internals.

What I did here was:

use explanation as a way to locate gaps in my understanding.

Being able to explain something is not the final goal.

I see it as:

one diagnostic method for checking understanding.

I Don't Need to Avoid Technical Terms

I also don't think technical terms need to be completely removed when explaining a system.

What matters is:

Can I translate them into words the listener can understand?

For example:

PostgreSQL
= A warehouse that stores application data

SQLAlchemy
= An intermediary between Python and the database

Dockerfile
= A loading and assembly instruction sheet for building the application

GitHub
= Storage for source code and change history

Render
= Rented infrastructure where the application runs

Cookie
= Entry ticket

Session
= Entry state

Flask-Login
= Entry-management staff

API
= A communication interface with another service

API key
= Authentication information used to access that interface

Combining:

the official technical term + language I personally understand

made the system much easier to organize in my head.

Writing Your Own Manual Can Be a Surprisingly Good Test

One thing I learned from this exercise is that asking:

Can I write the manual for my own system?

is a surprisingly powerful test.

For a user-facing manual, I need to explain:

What can the application do?
How do you operate it?
When is it useful?
What can it not do?

For a developer-facing explanation, I need to explain:

How does the data flow?
Why is this technology being used?
How does authentication work?
What is sent to the AI?
What remains available during failures?
What do the tests actually guarantee?

If I get stuck while trying to write one of these explanations, that may point to a gap in my understanding.

And if I find myself thinking:

"This operation is extremely difficult to explain."

the problem may not only be my understanding.

There may also be a UI/UX problem.

Writing documentation can itself become a system inspection.

This Is Not an Argument Against Using AI

This article is not saying:

Using AI to write code is bad.

I will continue using AI.

Without AI, I probably could not have experimented this much in such a short period of time.

But instead of stopping at:

AI created it
↓
It works
↓
Done

I think there is more value in continuing:

AI created it
↓
It works
↓
Why does it work?
↓
What does this component do?
↓
If I break it, will pytest detect it?
↓
Can I explain it to the user?
↓
Does my understanding match the actual code?

At that point, AI stops being only a code-generation machine.

It becomes a powerful learning tool.

Summary

I audited my own web application using 20 questions.

My initial result was:

🟢 6
🟡 10
🔴 4

But the score itself was not important.

Comparing my answers with the actual code revealed gaps such as:

GitHub is not part of the runtime path for sales data
A Dockerfile is an assembly instruction, not the finished product
SQLAlchemy helps connect Python application logic with the database
Cookies, sessions, and Flask-Login have different responsibilities
An API and an API key are different things
.gitignore is not an encryption feature
I verified what data was actually being sent to Gemini
My belief that the application was analyzing current social media trends did not match the implementation
What I ask an AI to analyze is not necessarily the same as the data I actually provide for that analysis

The biggest lesson was:

What I thought I knew was more dangerous than what I knew I didn't know.

In the AI era, the speed at which we can generate code will probably continue to increase.

That may make it even more important to spend time asking, in our own words:

What is this here for?

How does it work?

What happens if it breaks?

Can I explain it to the user?

I want to continue building with one rule in mind:

Don't stop at "it works." Keep going until I can explain it.

pytest Improvement Series

📝 Growing pytest into an “Incident Prevention Log” — Stage 5: Questioning GREEN with Mutation Testing

tosane932 — Thu, 27 Aug 2026 23:03:25 +0000

Hello from Japan 🇯🇵

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

In my personal Flask application, I've been gradually growing pytest into more than just a tool for checking whether the application works.

I think of it as an "incident prevention log":

a regression suite that helps stop previously discovered failures and near misses from silently returning.

This is Stage 5, the final stage of that series.

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

https://github.com/tosane932/sales_data_app

87 GREEN Tests Still Had Five Blind Spots

The pytest suite originally contained only:

3 passed

From there, I strengthened it step by step:

Stage 1:  3 → 9
Stage 2:  9 → 51
Stage 3: 51 → 69
Additional demo seed tests: 69 → 71
Stage 4: 71 → 87

Stage 5 took a slightly different direction.

Up through Stages 1–4, the process had mainly been:

There might be a problem
↓
Write a pytest test
↓
RED
↓
Fix the problem
↓
GREEN

But once all 87 tests were GREEN, one question came to mind:

Can I really trust that GREEN?

If the tests themselves have blind spots, broken code may still remain GREEN.

So in Stage 5, I decided to do the opposite:

temporarily break working code on purpose and check whether pytest actually turns RED.

This time, I borrowed the idea of falsification: instead of assuming that GREEN meant the tests were strong, I tried to disprove that assumption with manual mutation testing.

The result was:

Starting point: 87 passed

Mutations performed: 11

Initially KILLED:   6
Initially SURVIVED: 5

Strengthen pytest for the 5 survivors
↓
Apply the same mutations again
↓
Confirm all 5 now turn RED

Final result:
91 passed, 2 known warnings

So even with 87 GREEN tests, five kinds of failures could still slip through.

This article focuses mainly on those five survivors.

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

In Stage 5, I Started Questioning GREEN

When pytest is GREEN, it means:

the current implementation satisfied the conditions expressed by the tests that currently exist.

It does not mean:

GREEN = completely safe

For example, imagine this safe JavaScript code:

productName.textContent = item[0];

Suppose pytest only checks that:

textContent is used

Then someone adds:

productName.innerHTML = item[0];

directly after it.

The original textContent is still present.

So the test may still pass even though a dangerous HTML sink has been introduced.

In other words:

Safe code exists

and:

Dangerous code does not exist

are two different things.

Instead of only imagining this possibility, I decided to:

actually break the code and see what happened.

What Is Mutation Testing?

Mutation testing intentionally introduces small defects into otherwise working code.

For example, change:

if not product.is_active:

to:

if False:

Or take this query:

DailySales.query.filter_by(
    product_id=product.id,
    date=sale_date,
).first()

and remove:

date=sale_date

Then run pytest against the mutated code.

The result generally falls into two categories.

KILLED

A mutation is KILLED when introducing the defect causes pytest to turn RED.

Normal code
↓
GREEN

Mutation
↓
RED

That means the test suite can detect that failure.

SURVIVED

A mutation SURVIVES when pytest remains GREEN even after the defect is introduced.

Normal code
↓
GREEN

Mutation
↓
GREEN

That suggests the tests may not be able to distinguish:

the correct implementation from the broken implementation.

I did not introduce a dedicated mutation testing framework in this stage.

Instead, Codex and I selected representative defects and manually applied them:

one mutation at a time.

Because I Was Intentionally Breaking Working Code, I Defined Safety Rules First

Mutation testing means intentionally putting production code into a broken state.

So before starting, I defined strict boundaries:

Do not mutate main

Work only on feature/pytest-stage5

Only one mutation at a time

Do not connect to the production database

Do not connect to the development database

Do not connect to Render

Do not call the real Gemini API

Do not access external networks

Use only isolated pytest SQLite / temporary databases

Do not commit mutations

Do not push mutations

Always restore the original code after verification

If a mutation SURVIVED, I also did not start modifying pytest while the broken production code was still in place.

Every survivor followed this process:

Apply mutation
↓
Run pytest
↓
Confirm SURVIVED
↓
Restore normal code
↓
Add or strengthen pytest formally
↓
Confirm GREEN with normal code
↓
Apply the same mutation again
↓
Confirm RED
↓
Restore again
↓
Confirm GREEN with normal code

All five surviving mutations followed this same sequence.

The 11 Mutations

These are the 11 mutations I selected.

#	Mutation	Initial Result	After Strengthening
1	Add `innerHTML` next to XSS-safe `textContent`	SURVIVED	KILLED
2	Disable CSRF protection	KILLED	-
3	Remove session fingerprint mismatch rejection	KILLED	-
4	Change year/month validation from `or` to `and`	KILLED	-
5	Disable discontinued-product validation	KILLED	-
6	Change the model `UniqueConstraint`	KILLED	-
7	Change the migration `UniqueConstraint`	KILLED	-
8	Remove the `year` condition from AI advice data retrieval	SURVIVED	KILLED
9	Add the Jinja `safe` filter to the initial ranking	SURVIVED	KILLED
10	Treat a session with a missing fingerprint as valid	SURVIVED	KILLED
11	Remove the `date` condition from sales lookup	SURVIVED	KILLED

The first run produced:

KILLED   6
SURVIVED 5

The six mutations that were KILLED immediately were already detected by existing pytest coverage, so I did not add new test functions for them.

The rest of this article focuses mainly on:

the five mutations that survived.

Six Mutations Were KILLED Immediately

The six mutations detected by the existing suite were:

#2 Disable CSRF protection
#3 Remove session fingerprint mismatch rejection
#4 Break year/month validation
#5 Disable discontinued-product validation
#6 Break the model UniqueConstraint
#7 Break the migration UniqueConstraint

For example, in Mutation #2, I temporarily disabled CSRF protection.

Requests that should have been rejected with HTTP 400 were then able to reach application logic, and the existing tests turned RED.

For Mutation #5, I changed:

if not product.is_active:

to:

if False:

This allowed discontinued products to pass through sales processing.

The existing pytest suite detected that too.

Mutation #7 was especially interesting.

The migration originally had a unique constraint on:

product_id + date

I temporarily changed that to:

product_id + quantity

The migration itself still completed successfully.

But the pytest test that inspected the resulting schema detected that:

the required UniqueConstraint does not exist

and turned RED.

That confirmed another distinction:

"The migration completed successfully" and "the correct schema was created" are not the same thing.

SURVIVED #1: A Safe `textContent` Check Missed a Dangerous `innerHTML`

The first surviving mutation involved XSS protection.

The dynamic ranking displays product names using:

productName.textContent = item[0];

I temporarily added:

productName.innerHTML = item[0];

immediately after it.

So the mutated code looked like this:

productName.textContent = item[0];
productName.innerHTML = item[0];

The safe textContent line still existed.

But now the unsafe innerHTML assignment existed too.

I ran the existing XSS regression tests.

The result was:

GREEN.

The mutation SURVIVED.

Why Did It Survive?

The existing pytest test checked that:

textContent is used

But it did not check that:

innerHTML is not used

The test only proved:

Safe behavior exists

It did not prove:

Dangerous behavior does not also exist

So I strengthened the regression tests to also detect dangerous HTML sinks that could become XSS vectors when used with untrusted data, including:

innerHTML
outerHTML
insertAdjacentHTML

After confirming GREEN with the normal implementation, I applied the exact same mutation again.

This time, pytest turned RED.

SURVIVED
↓
Strengthen assertion
↓
Apply same mutation again
↓
KILLED

The test count became:

87 → 88 tests

The first lesson was:

sometimes it's not enough to confirm that something safe exists. You also need to confirm that something dangerous does not exist.

The Second Wave Focused Only on Failures Existing pytest Might Miss

You can create almost unlimited mutations if you want to.

Change a condition.

Remove a return.

Disable exception handling.

Remove a database filter.

Add unsafe template behavior.

But the purpose of Stage 5 was not to maximize the number of mutations.

After the first wave, I inspected the code again without changing production code.

I selected four additional mutations where I thought:

The current fixture might not distinguish
correct code from broken code

The current assertion might miss this failure

This mutation might reveal something different
from the existing tests

Those became Mutations #8 through #11.

I decided these would be the final group for Stage 5.

The result:

all four SURVIVED.

This became the most important part of Stage 5 for me.

SURVIVED #8: Removing the `year` Filter Changed Nothing Because the Fixture Was Too Simple

The AI business-advice feature retrieves sales data for a selected year and month.

The normal code calls:

_get_sales_from_db(target_year, target_month)

I temporarily removed the year condition:

_get_sales_from_db(None, target_month)

So instead of asking for:

August 2026

the mutated code effectively asked for:

August from any year

I ran the existing pytest test.

The result:

GREEN.

The mutation SURVIVED.

The Problem Was the Fixture, Not the Production Code

At the time, the fixture only contained data for:

August 2026
July 2026

So even after removing the year condition:

month = August

still returned only the August 2026 data.

The correct code and the mutated code produced:

the same result with the existing fixture.

So I added:

August 2025

data to the fixture.

The product name was:

Previous-year August product

with quantity:

Then, when requesting August 2026, I strengthened the existing test with an assertion equivalent to:

assert "Previous-year August product" not in contents

Normal code:

GREEN.

Same mutation applied again:

RED.

The mutation was now KILLED.

Because I strengthened an existing test function instead of adding a new one, the total remained:

88 tests

What I Learned Here

Test data does not merely need to:

exist.

It needs enough discriminating power to produce different results between:

correct code

and:

broken code

A test can execute a line of code without proving:

that the condition on that line is actually necessary.

SURVIVED #9: Existing XSS Tests Missed Jinja `|safe`

The next mutation involved the dashboard's initial server-rendered ranking.

Normally, Jinja autoescapes:

{{ name }}

I temporarily changed it to:

{{ name | safe }}

Then I ran the existing XSS tests.

The result:

6 passed

The mutation SURVIVED.

Why Did It Survive Even Though I Already Had XSS Tests?

The existing tests focused mostly on:

rankings generated later by JavaScript
AI responses
unsafe HTML sinks

But they did not directly verify:

the product name rendered by the server in the initial ranking.

So I inserted a product name like:

<em>HTML-like product name</em>

into the database.

If normal Jinja autoescaping is working,  should appear as text rather than becoming an actual HTML element.

The new pytest test verifies that:

escaped text exists in the raw HTML
.prod-name displays the original product-name text
no em element appears inside .prod-name

Normal implementation:

GREEN.

Reapply the |safe mutation:

RED.

SURVIVED
↓
Add server-rendered XSS coverage
↓
KILLED

The test count became:

88 → 89 tests

This taught me that:

having something called an "XSS test" does not mean every rendering path is protected by that test.

SURVIVED #10: A Normal Login Fixture Could Never Produce a Missing-Fingerprint Session

The existing session tests verified whether an old session was rejected after administrator credentials changed.

But what happens if:

the fingerprint itself disappears from the session?

The normal implementation rejects a session if the fingerprint is missing.

So I introduced a mutation that treated a missing fingerprint as though it matched the current fingerprint.

Conceptually:

Fingerprint missing
↓
Pretend it matches the current fingerprint

The existing pytest tests returned:

2 passed

The mutation SURVIVED.

The Problem Was the "Normal Login" Fixture

The existing tests logged in normally.

A successful login naturally stores the fingerprint in the session.

So those tests never created:

a session where only the fingerprint is missing

For the new test, I performed a normal login and then used:

session_transaction()

to remove only the fingerprint from the session.

The Flask-Login _user_id remained.

Now the behavior differed:

Normal implementation:

redirect to login

Mutated implementation:

dashboard → 200

I confirmed GREEN with the normal code.

Then I applied the mutation again.

pytest turned RED.

KILLED.

89 → 90 tests

This showed me that:

fixtures created only through normal user flows may be unable to reproduce abnormal states.

SURVIVED #11: Removing the `date` Filter Changed Nothing Because There Was No Same-Product, Different-Date Data

This was the final mutation.

DailySales searches for the row to update with:

DailySales.query.filter_by(
    product_id=product.id,
    date=sale_date,
).first()

So both:

product
+
date

must match.

I temporarily removed the date condition:

DailySales.query.filter_by(
    product_id=product.id,
).first()

Then I ran the existing normal sales POST tests.

PASS.

I also ran the database UniqueConstraint tests.

PASS.

The mutation SURVIVED.

Again, the Fixture Was the Problem

The existing fixture did not contain:

sales for the same product on another date.

If a product only has:

August 2 sale

then removing the date filter may still return that exact same row by coincidence.

So I created an existing sales record:

2026-08-01
quantity=5

Then I POSTed:

2026-08-02
quantity=9

With the normal implementation, the expected result is:

August 1 → quantity=5
August 2 → quantity=9

With the mutated implementation:

August 1 → quantity=9

The bug causes:

the historical August 1 sale to be incorrectly changed from 5 to 9
the new August 2 sale to never be created

So the new pytest test verifies both:

The August 1 record remains unchanged

and

The August 2 record is created

Normal implementation:

GREEN.

Same mutation reapplied:

RED.

KILLED.

The total became:

90 → 91 tests

All Four Mutations in the Second Wave Initially SURVIVED

The second-wave results were:

Mutation	Initial Result	Why It Survived	After Strengthening
#8 Remove `year` condition	SURVIVED	No previous-year same-month fixture	KILLED
#9 Add Jinja `\	safe`	SURVIVED	Initial server rendering wasn't directly tested
#10 Missing fingerprint	SURVIVED	No abnormal-session fixture	KILLED
#11 Remove `date` condition	SURVIVED	No same-product, different-date sales data	KILLED

This really stayed with me.

By the end of Stage 4, the suite had already grown to:

87 passed

Yet all four mutations I deliberately selected because:

"This might still be weak"

passed straight through the suite.

More tests can create a greater sense of confidence.

But:

There are 87 tests

and:

Those 87 tests can distinguish important failures

are not the same statement.

In the End, All 11 Selected Mutations Became Killable

I performed 11 mutations.

The first run produced:

KILLED   6
SURVIVED 5

The five survivors were:

#1
#8
#9
#10
#11

For each of them, I followed the full process:

Restore mutation
↓
Formally add or strengthen pytest
↓
Confirm GREEN with normal code
↓
Apply the same mutation again
↓
Confirm RED
↓
Restore again
↓
Confirm final GREEN

So for the 11 representative mutations I selected, pytest was ultimately able to KILL all of them.

But I want to be careful about what that means.

It does not mean:

Mutation Score for the whole application = 100%

I only mutated:

11 representative cases that I selected manually.

pytest Grew from 87 to 91 Tests

At the beginning of Stage 5:

87 tests

At the end:

91 tests

Only four new test functions were added.

Since I performed 11 mutations, I initially thought the suite might grow more.

But:

six mutations were already KILLED by existing tests
#8 only required strengthening the fixture in an existing test function
new test functions were added for #1, #9, #10, and #11

So:

87 + 4 = 91

I actually think that was a good result.

If my goal had been:

Reach 100 tests

I might have added tests simply to make the number larger.

But the purpose of Stage 5 was not:

to increase the test count.

It was:

to evaluate the failure-detection ability of the GREEN tests I already had.

From the Original Three Tests to About 30 Times as Many

Before I started strengthening pytest:

3 tests

By the end:

Stage 1    9
Stage 2   51
Stage 3   69
Demo seed 71
Stage 4   87
Stage 5   91

Numerically, that is roughly 30 times the original count.

But after Stage 5, what matters more to me is not:

that I have 91 tests.

It is:

that I can explain which failures cause those tests to turn RED.

Stage 5 Made No Formal Production-Code Changes

The only files with formal changes from Stage 5 were:

test_ai_integration.py
test_auth.py
test_sales.py
test_xss_regressions.py

There were no formal production-code changes:

app.py        no formal diff
templates     no formal diff
models        no formal diff
migrations    no formal diff

Every temporary mutation was restored.

So Stage 5 did not strengthen the application by permanently changing production code.

Instead:

it strengthened pytest's ability to detect defects.

Five Things I Learned from Stage 5

1. GREEN Can Still Miss Broken Code

Five mutations actually passed through the suite while it remained GREEN.

So:

pytest is GREEN

does not guarantee:

No broken code exists

GREEN means:

the current tests did not detect a violation of the conditions they express.

2. Fixtures Need Discriminating Power

Mutation #8 could not be detected without data from the same month in another year.

Mutation #11 could not be detected without sales for the same product on another date.

So a fixture should not merely be:

data that lets the test run

It may need to be:

data that distinguishes correct behavior
from broken behavior

3. Assertions Sometimes Need to Check Both "Exists" and "Does Not Exist"

Mutation #1 showed this clearly.

I already verified that:

textContent is used

But that assertion still passed after dangerous innerHTML was added next to it.

So I needed to test both directions:

Safe behavior exists

and:

Dangerous behavior does not exist

4. Normal Fixtures Cannot Always Represent Abnormal States

Mutation #10 involved a session where only the fingerprint was missing.

A normal login always creates that fingerprint.

So I had to explicitly construct:

an abnormal session state

Normal user-flow fixtures alone do not always reach the failure condition you actually need to test.

5. RED Does Not Automatically Mean the Mutation Was KILLED

During mutation testing, I did not classify something as:

KILLED

just because pytest became RED.

I checked whether:

the intended mutation caused the intended assertion to fail.

If the test failed for an unrelated reason, that would not prove that the mutation itself had been detected.

The Scariest Part Wasn't Breaking the Code — It Was Accidentally Leaving It Broken

The part I was most careful about was not the mutation itself.

It was:

accidentally leaving intentionally broken code behind.

So every mutation followed the rule:

Do not commit
Do not push

And every cycle included:

Mutation
↓
pytest
↓
restore
↓
git diff
↓
pytest with normal code

Mutation testing is often described as:

"break it and see if the tests catch it."

But for me, reliably restoring the code is part of the same operation.

Final Result

The final pytest result was:

91 passed, 2 warnings

The two warnings are the previously known:

Flask-SQLAlchemy get_engine() DeprecationWarning

from:

migrations/env.py

They were not introduced by Stage 5.

I'm treating them as separate maintenance work, so I did not fix them as part of this stage.

This Still Does Not Mean the Application Is Completely Safe

I performed 11 mutations and eventually strengthened the suite so that all 11 selected failures could be detected.

But I still cannot say:

This application is completely safe.

There are many areas outside the scope of this work, including:

PostgreSQL-specific behavior
locking
concurrency
race conditions
simultaneous POST requests
duplicate submissions
differences from the Render environment
production environment variables
the real Gemini API
API quotas
timeouts
SDK changes
real-browser DOM behavior
CSP
specifications I have not decided yet
future code
failures I have not imagined yet

And I did not mutate the entire application exhaustively.

What I verified was narrower:

Can pytest actually detect these 11 representative types of failure that I selected?

It Feels a Little Like Testing a Truck's Safety Equipment

I work as a truck driver.

A truck has many safety systems.

But simply saying:

The safety equipment exists

does not mean accidents can never happen.

When a dangerous situation occurs:

does that equipment actually work the way it is supposed to?

And:

am I becoming overconfident simply because the equipment exists?

I started seeing something similar with pytest.

It is not enough to say:

pytest exists
CI exists
authentication exists
CSRF protection exists
database constraints exist

I also need to ask:

if one of those things breaks, can the system actually detect that something is wrong?

And even excellent safety equipment does not automatically make every human decision safe.

Years of experience do not guarantee perfect judgment either.

Familiarity can sometimes lead to:

This is probably fine.

Software may have a similar trap:

All pytest tests are GREEN,
so it's probably fine.

If I stop thinking there, I may miss something important.

Not "It's Probably Fine," but "What If?"

In Stage 5, I used a mindset from my day job almost directly.

Instead of:

"It's probably fine."

I try to think:

"What if?"

For example:

pytest is GREEN,
but it might still miss a failure

Dangerous behavior might be added
next to safe behavior

The fixture might be too simple
to distinguish broken code
from correct code

A migration might succeed
while producing the wrong schema

I might only be testing normal login
and never checking an abnormal session

I thought through those possibilities and then:

introduced small defects to test them for real.

That was Stage 5.

Summary

Stage 5 moved the suite from:

87 passed
↓
91 passed

But the important part was not the additional four tests.

I introduced 11 representative defects.

The first run produced:

Initially KILLED   6
Initially SURVIVED 5

Investigating the five survivors showed that the problem was not always simply:

Not enough tests

The blind spots included:

Assertions that only checked one direction

Fixtures without enough comparison data

Server-rendered output not being tested

No abnormal-session fixture

No different-date sales data for the same product

For all five survivors, I completed this sequence:

Restore normal code
↓
Add or strengthen pytest
↓
GREEN with normal code
↓
Apply the same mutation again
↓
RED
↓
Restore again
↓
Final GREEN

So in the end:

pytest could KILL all 11 mutations selected for this stage.

Stage 1 began with only three pytest tests and the idea of turning them into a regression suite.

Stage 2 expanded into validation, database updates, and rollback behavior.

Stage 3 expanded into authentication, CSRF protection, and access control.

Stage 4 looked ahead to failures that had not happened yet, including migrations, AI-service failures, and session problems.

And Stage 5:

questioned the GREEN result itself.

The biggest lesson for me was:

GREEN ≠ proof of safety

GREEN means:

the current implementation satisfied the conditions expressed by the current tests.

That is why I think there is value in occasionally turning the question around:

Can this test actually detect the failure
it is supposed to detect?

pytest started with three tests.

It ended this series with:

91 tests

But now I care more about:

being able to explain what I can break to make those tests turn RED

than simply saying:

"I have 91 tests."

For me, this marks the end of Stage 5.

What began as a small list of normal behavior checks gradually became an:

"incident prevention log" that I also crash-tested to see whether it could actually detect failures.

pytest Improvement Series

https://github.com/tosane932/sales_data_app

https://qiita.com/tosane932

📝 Growing pytest into an “Incident Prevention Log” — Stage 4: Testing Failure Scenarios (71 ➡ 87 Tests)

tosane932 — Sun, 23 Aug 2026 09:26:35 +0000

Hello from Japan 🇯🇵

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

This article is Stage 4 of my ongoing effort to strengthen pytest in a Flask application I'm building.

Instead of only adding more tests for normal behavior, Stage 4 focuses on questions like:

"What if the input is invalid?"

"What if the external AI service goes down?"

"What if authentication settings change after login?"

It is similar to the kind of hazard anticipation I use in my day job as a truck driver: thinking about what might happen before it becomes an accident.

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

I used Codex to help with investigation and implementation.

Some of the internal behavior became complicated enough that there were points where my own understanding did not fully keep up.

So rather than pretending to explain internal details I don't completely understand, this article focuses on:

what I considered a problem, what I checked, and what the result was.

https://github.com/tosane932/sales_data_app

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

I've been gradually strengthening pytest in my personal Flask application.

The test suite originally had only three tests.

It grew through the previous stages:

Stage 1: 3 → 9 tests
Stage 2: 9 → 51 tests
Stage 3: 51 → 69 tests
additional demo seed tests: 69 → 71 tests

So at the beginning of Stage 4:

71 passed

By this point, I wanted to check more than:

"Does the application work correctly under normal conditions?"

I also wanted to ask:

"When something abnormal happens, does the application fail safely?"

In my work as a truck driver, I constantly think about things that might happen:

someone might suddenly step or drive out
cargo might shift or collapse
a delivery mistake might happen
a safety device might not work correctly

The idea is to anticipate danger before an accident occurs.

I approached Stage 4 of pytest in much the same way.

Instead of:

"Fix the accident after it happens."

I wanted to move toward:

"Find the places where an accident might happen before it does."

That became the theme of Stage 4.

Stage 4 Results

By the end of this stage:

71 tests
↓
87 tests

I added 16 tests.

Breakdown of the 16 New Tests

Category	Added
Empty-database migration	1
Non-integer dashboard query parameters	6
Gemini failure fallbacks	3
Authenticated AI advice happy path	1
Fail-closed sessions	2
Invalid CSRF tokens	3
Total	16

The final result was:

======================= 87 passed, 2 warnings in 15.65s ========================

1. Can Migrations Build the Database from Nothing?

The first thing I checked was the Alembic migration history.

Instead of testing only against a database that already existed, I wanted to verify:

"Can the current database schema be rebuilt from a completely empty database using only the migration history?"

The test creates an isolated SQLite database under tmp_path and runs:

Empty database
↓
Alembic base
↓
upgrade head
↓
current schema

The test verifies:

products
daily_sales
alembic_version
required columns
the composite unique constraint on daily_sales(product_id, date)
the database revision matches the current Alembic head

I did not hard-code the revision ID into the test.

Instead, the test dynamically checks the current Alembic head.

This test verifies that the migration chain can rebuild the expected schema in an isolated SQLite database. It does not replace PostgreSQL-specific migration testing.

If I compare this to a company, this isn't asking:

"Can the headquarters that already exists keep operating?"

It's asking:

"If we start from an empty lot, can we build a new branch correctly using only the blueprints?"

Result:

1 passed

2. Invalid `year` / `month` Should Return 400, Not 500

Next, I investigated dashboard-related query parameters.

The affected routes were:

/dashboard
/api/dashboard-data
/api/ai-advice

At the time, values such as:

?year=abc

or:

?month=abc

were passed directly to int().

That meant a ValueError could occur and potentially result in an HTTP 500 response.

So I defined the expected behavior as:

non-integer year or month values should be rejected with HTTP 400 Bad Request.

I added:

3 routes × year/month
= 6 cases

All six were RED at first:

Expected: 400
Actual:   500

I then added a small helper that converts query parameters to integers and calls abort(400) when the value is invalid.

Result:

6 passed

For the AI advice API, I also verified that invalid query parameters are rejected before the Gemini client is called.

3. Locking Down Gemini Failure Fallbacks

This application uses the Gemini API.

But Gemini is an external service, so failures can happen:

429
503
other exceptions

The application already had fallback behavior for these cases.

Stage 4 added regression tests to preserve that behavior.

I tested:

429
→ fallback for rate limiting

503
→ fallback for service unavailable

other exception
→ general fallback

I did not connect to the real Gemini API.

All external calls were mocked.

These tests did not uncover a new bug.

Instead, the goal was:

to record an existing safety mechanism as regression tests so future changes don't accidentally remove it.

Result:

3 passed

In company terms, this is like asking:

"If one of our external business partners goes offline, does our own system stay standing?"

4. Testing the Authenticated AI Advice Happy Path

I also added one test for the normal AI advice flow.

I wanted to verify that the different parts of the system still worked together correctly when nothing was failing.

The flow was:

Authentication
↓
query processing
↓
SQLite database aggregation
↓
prompt generation
↓
mock Gemini
↓
JSON response

For example, suppose the database contains:

August 2026

Product A: 10
Product B: 5

The test verifies that:

Product A and Product B for August are included in the prompt
products from another month are not included
the mocked Gemini response is returned as JSON

Result:

1 passed

5. Fail Closed: Don't Trust an Old Session After Authentication Settings Change

This was the part of Stage 4 where I spent the most time experimenting.

The theme was:

fail-closed behavior.

Fail-closed means:

when something is wrong or uncertain, default to the safer state.

A traffic signal is an easy analogy.

If the signal system breaks, you don't want:

"Something is wrong, so let's assume green."

You want:

"Something is wrong, so stop."

The First Problem I Found

I found that an existing authenticated session might still restore the administrator even after:

ADMIN_PASSWORD_HASH

had been changed to an invalid or different value.

So I created a RED test:

Successful login
↓
change ADMIN_PASSWORD_HASH
↓
GET /dashboard
↓
expect 302 redirect to /login

But then another problem appeared.

The RED Test Wasn't Following the Path I Thought It Was

In the test environment, the application context remained alive longer than I expected.

That meant:

g._login_user

still contained authentication information from the previous request.

I thought I was testing:

session
↓
load_user()

but the request was actually using:

previous request's g._login_user
↓
still authenticated

So I learned something important:

A test being RED does not automatically mean it is RED for the reason you intended.

I changed the test so that the cached authentication state from the previous request was removed.

I also used a spy to confirm that:

load_user("admin")

was actually called again.

Even then:

Expected: 302
Actual:   200

This time, I had confirmed the real RED condition.

One lesson that stood out during this work was:

It's not enough to ask whether GREEN is GREEN for the right reason. RED also needs to be RED for the right reason.

My First Fix Was a Roughly 60-Line Hash Parser

My first implementation tried to validate whether a Werkzeug password hash itself had a valid format.

I wrote a helper that parsed things such as:

scrypt
pbkdf2
digest
parameters

The tests passed.

But after reviewing the implementation, another problem became obvious:

it was far too complicated for what I was actually trying to achieve.

Now the application itself would need to understand and maintain Werkzeug's password-hash formats.

If Werkzeug added or changed supported formats in the future, my own parser might also need to change.

So I stopped and redesigned the approach.

Switching to a Fingerprint

The real question I wanted to answer was not:

"Is the current password hash in a valid format?"

It was:

ADMIN_PASSWORD_HASH at login
==
Current ADMIN_PASSWORD_HASH

So instead, I generate a SHA-256 fingerprint from ADMIN_PASSWORD_HASH when login succeeds and store that fingerprint in the session.

Conceptually:

Successful login
↓
record a "fingerprint" of the current ADMIN_PASSWORD_HASH
↓
next request
↓
generate a fingerprint from the current ADMIN_PASSWORD_HASH
↓
match    → continue
mismatch → require login again

Only the fingerprint is stored in the session.

I do not store:

the password hash itself
the real password
a plaintext password

The fingerprint logic was separated into a small helper.

In company terms:

instead of storing the key itself, I added someone who checks the key's fingerprint.

The final two tests verify:

ADMIN_PASSWORD_HASH changed
→ reject existing session

ADMIN_PASSWORD_HASH unchanged
→ restore session normally

Result:

2 passed

6. Can the Application Reject a Tampered CSRF Token?

The final area I checked was CSRF protection.

The existing pytest suite already verified:

No CSRF token
→ reject the POST request

But I had not yet tested:

a token that exists but has been modified or forged.

Using an airport analogy:

No passport
→ rejected

is not the only case worth checking.

I also wanted:

Forged passport
→ rejected

For these tests, I did not generate an entirely fake token from scratch.

Instead:

retrieve a valid CSRF token using the same client
modify only the first character
send the POST request

I added three tests.

Login

Correct username/password
+
tampered CSRF token
↓
400
↓
authentication not created

Product Registration

Tampered CSRF token
↓
400
↓
Product unchanged
DailySales unchanged

Sales Registration

Tampered CSRF token
↓
400
↓
DailySales unchanged

Result:

3 passed

All three tests were GREEN immediately because Flask-WTF was already rejecting the tampered tokens correctly.

So this wasn't a case where I added a new security feature.

Instead:

I added evidence to the regression suite that an existing security checkpoint was actually working.

Final pytest Result

After all Stage 4 changes, I ran:

pytest -v

The result:

collected 87 items

...

======================= 87 passed, 2 warnings in 15.65s ========================

There were no failures or errors.

Stage 4 Commit List

bb8b503 test: cover empty database migrations
a1a5578 fix: reject invalid dashboard query parameters
b36ad7a test: cover Gemini failure fallbacks
757ef6f test: cover authenticated AI advice route
412fcb3 fix: invalidate sessions when admin credentials change
26ae4f8 test: cover invalid CSRF tokens

Verifying Everything Again with GitHub Actions

After confirming all 87 tests were GREEN locally, I used the following flow:

feature/pytest-stage4
↓
Pull Request
↓
GitHub Actions
↓
main

In the Pull Request, I confirmed:

All checks have passed

and verified that there were no conflicts before merging.

After the merge, I updated my local main branch:

git switch main

git pull

Then, as an additional check, I ran:

git merge-base --is-ancestor feature/pytest-stage4 main \
  && echo "OK: feature/pytest-stage4 is fully included in main" \
  || echo "NG: main still has missing commits"

The result:

OK: feature/pytest-stage4 is fully included in main

The SHA values for main and origin/main also matched.

There were no file differences between the feature branch and main.

Finally, after the merge, I ran:

pytest -v

again on main.

The final result remained:

87 passed, 2 warnings

In company terms, I think of this as:

Build the safety equipment in the test department

↓

Send it through headquarters review

↓

Approve it for official use

↓

Run all 87 safety checks again at headquarters

The Two Remaining Warnings

There are still two warnings during the migration tests:

DeprecationWarning:
'get_engine' is deprecated and will be removed in Flask-SQLAlchemy 3.2.

They originate from:

migrations/env.py

where get_engine() is still used.

The new migration test made these warnings more visible, but they were not introduced by the Stage 4 changes.

I am aware of these warnings, but I deliberately left them outside the scope of Stage 4.

Instead of fixing something just because I happened to notice it, I prefer to treat unrelated changes as separate tasks.

What I Learned from Stage 4

The test suite grew from:

71
↓
87

But once again, the number itself was not the most important part.

The fail-closed work in particular followed this path:

1. Write a RED test
2. Confirm RED
3. Investigate why it is RED
4. Discover a problem in the test fixture
5. Fix the RED test
6. Confirm the real RED condition
7. Implement a fix
8. Decide the implementation is too complicated
9. Throw away the first implementation
10. Redesign it using a simpler fingerprint approach

That experience made me think beyond:

"Does the code work?"

I also needed to ask:

"Do I really want to maintain this implementation in the future?"

And pytest has become easier for me to understand when I think of it not merely as a behavior check, but as:

a mechanism for checking dangerous places before an incident happens.

That feels very similar to hazard anticipation in logistics.

Instead of waiting for danger to appear, you constantly ask:

"What might happen next?"

Next: Stage 5 Will Be a Crash Test

I plan for Stage 5 to be the final stage of this pytest improvement series.

Up to this point, most of the work has been about:

adding tests and building safety mechanisms.

Stage 5 reverses the question:

"Can those safety mechanisms actually detect an accident?"

On a feature branch, I plan to temporarily introduce small intentional defects into the code and check whether pytest correctly turns RED.

Something like:

Intentionally introduce a small defect
↓
Run pytest
↓
Confirm the appropriate test turns RED
↓
Immediately restore the original code

This is similar to a manual form of mutation testing.

The intentionally broken code will not be committed or pushed.

I will always restore the original implementation before moving to the next check.

This is not something I will perform in the production environment.

At the end of Stage 1, the suite had only 9 tests.

At the end of Stage 4:

87 tests

In the final stage, the question will no longer be:

"Do I have 87 tests?"

It will be:

"Can those 87 tests actually detect the failures they are supposed to catch?"

pytest Improvement Series

https://qiita.com/tosane932

https://github.com/tosane932/sales_data_app

📝 Growing pytest into an “Incident Prevention Log” — Stage 3: Authentication, CSRF, and Access Control (51 ➡ 69 Tests)

tosane932 — Fri, 21 Aug 2026 19:02:46 +0000

Hello from Japan 🇯🇵

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

In my personal Flask project, I've been trying to turn pytest into more than just a tool for checking whether the application works.

I think of it as an "incident prevention log":

a set of regression tests that helps keep previously discovered problems and near misses from silently returning.

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

https://github.com/tosane932/sales_data_app

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

Stage 3 became a fairly long article because it covers authentication, CSRF protection, and access control.

To make it easier to read, I've placed some of the implementation details inside expandable <details> sections.

You can follow the overall story without opening them, so feel free to expand only the sections that interest you.

So far, I've strengthened the test suite in stages:

Stage 1: 3 → 9 tests
Stage 2: 9 → 51 tests

In Stage 3, I focused mainly on:

authentication, CSRF protection, and access control

and increased the regression suite from:

51 passed
↓
69 passed

But once again, increasing the number of tests was not the goal itself.

What I wanted to check in Stage 3 was whether situations like these were possible:

Can anyone modify application data?

Even if someone is logged in,
can an external site cause an unintended POST request?

Can anonymous users access application pages or APIs
that should require authentication?

Can an anonymous user trigger the AI API?

As in the previous stages, I worked with Codex in this order:

Investigate
↓
RED
↓
Smallest necessary fix
↓
GREEN

Stage 3 Started with 51 Passing Tests

At the end of Stage 2:

51 passed

The existing pytest suite already covered areas such as:

sales input validation
product registration validation
invalid product IDs
products belonging to another month
discontinued products
database unique constraints
rollback when commit() fails
soft deletion and preservation of sales history
dashboard aggregation

However, when I investigated authentication-related behavior, I found that:

Authentication
CSRF protection
Access control

were still not sufficiently implemented.

So I decided to work through Stage 3 in this order:

Authentication
↓
CSRF
↓
Access control for application pages and APIs

First, I Investigated the Current State Without Fixing Anything

The first thing I asked Codex to do was not to implement authentication.

I asked it only to:

investigate the current authentication state.

At that point, the application had:

no login page
no User model
no Flask-Login
no CSRF tokens
anonymous access to application pages and APIs

For state-changing POST requests in particular:

POST /
POST /input

could be reached without authentication.

In other words:

an unauthenticated user could reach the code that modifies product and sales data.

Instead of fixing that immediately, I first decided to express the dangerous state as failing pytest tests.

Authentication: Make Unauthenticated POST Requests RED

The first tests I added were:

Reject unauthenticated product POST requests.

Reject unauthenticated sales POST requests.

The future behavior I wanted was:

anonymous
↓
POST
↓
302
↓
/login
↓
no database changes

Before implementing authentication, however:

Expected: 302
Actual:   200

and the request actually reached the database modification logic.

That was exactly the RED state I wanted to confirm.

🧪 Authentication: From RED Tests to Implementation

I Defined the Login Behavior with RED Tests First

Next, I added tests for the behavior I wanted:

GET /login should render successfully
correct credentials should log the user in
an incorrect password should not log the user in
the login state should persist in the same test client
unauthenticated POST requests should be rejected

Because no login functionality existed yet:

5 failed

Only after confirming that RED state did I move on to implementing authentication.

I Chose a Single-Admin Login

For this application, I deliberately did not expand the scope into full multi-user management.

Instead, I chose:

single-admin authentication

using Flask-Login.

The credentials are loaded from environment variables:

SECRET_KEY
ADMIN_USERNAME
ADMIN_PASSWORD_HASH

Rather than storing a production plaintext password in the source code, I verify a password hash using:

check_password_hash()

At this stage, I did not add:

a User database model
general users
roles
tenants
store-specific permissions

For the current specification:

authenticated user
=
single administrator

So Stage 3 does not implement detailed role-based authorization.

The target here was simply:

only the authenticated administrator can access protected application functionality.

After Authentication: 56 Tests GREEN

After implementing the single-admin authentication:

pytest test_auth.py -v
→ 5 passed

pytest -v
→ 56 passed

At that point, I created a local commit:

feat: add single-admin authentication

I wanted to preserve the point where authentication alone was GREEN instead of continuing straight into CSRF changes in the same step.

Next, I Investigated CSRF Protection

Adding authentication does not automatically mean:

"The application is now safe."

When authentication uses a session cookie, the browser automatically includes that cookie with matching requests.

That means I also needed to consider whether:

a logged-in user's session could be abused to send a POST request that the user did not intend.

So the next step was CSRF protection.

I Created RED Tests for CSRF Before Implementing It

I added six CSRF-related tests.

Before implementation:

6 failed, 56 passed

The forms had no CSRF token.

And while authenticated, product and sales POST requests still worked without a token.

Even the login POST succeeded with the correct username and password when no CSRF token was supplied.

🧪 The Six CSRF RED Tests and Implementation Details

CSRF Tests I Added

The login form contains a CSRF token.

The product form contains a CSRF token.

The sales form contains a CSRF token.

Reject a login POST without a token.

Reject a product POST without a token.

Reject a sales POST without a token.

Without a Token, the Request Could Still Modify the Database

Before adding CSRF protection, a product POST without a token resulted in:

status=200
products_unchanged=False

In other words:

a request without a CSRF token could still reach the Product modification logic.

The sales POST behaved similarly:

status=200
sales_unchanged=False

and DailySales was modified.

Instead of stopping at the observation:

There is no CSRF protection.

I wanted the RED tests to show:

how far the current request could actually proceed.

Adding `CSRFProtect` with Flask-WTF

I used Flask-WTF for CSRF protection.

CSRFProtect(app)

Then I added a hidden CSRF field to the three POST forms:

The protected forms were:

/login
/
/input

I did not add CSRF exemptions.

I also did not disable CSRF protection in the test environment.

Existing Tests Also Had to Become "Legitimate POST Requests"

Once CSRFProtect was enabled, existing positive-path tests also returned 400 if they posted without a token.

But tests intended to verify things such as:

product validation

sales rollback

need to reach the business logic.

If CSRF blocks them first, those tests are no longer testing what they are supposed to test.

So I changed the test flow to:

GET
↓
retrieve csrf_token
↓
POST with a valid token

I created a test fixture for retrieving CSRF tokens.

The authenticated test client also does not directly inject a logged-in state into the session.

Instead, it follows a flow closer to the real login process:

GET /login
↓
retrieve CSRF token
↓
username
password
csrf_token
↓
POST /login
↓
authenticated client

After CSRF Protection: 62 Tests GREEN

After implementation:

pytest test_csrf.py -v
→ 6 passed

pytest test_auth.py -v
→ 5 passed

pytest -v
→ 62 passed

For product and sales POST requests without a token:

CSRFProtect rejects the request with 400
↓
no database changes

For the product POST, I compare snapshots of both Product and DailySales.

For the sales POST, I verify that DailySales is unchanged.

For login without a token:

400
↓
no authenticated session is created

I then created another commit:

feat: add CSRF protection

Even After Adding Authentication and CSRF, Anonymous Users Could Still Access Application Pages

After adding authentication and CSRF protection, I investigated the GET routes and APIs.

The routes I checked were:

/login
/
/input
/dashboard
/api/dashboard-data
/api/ai-advice
/api/greeting

At that point, anonymous requests to all of them returned:

HTTP 200

/login should of course remain public.

But the application pages and APIs that were intended to require authentication were still accessible anonymously.

Anonymous Users Could Even Reach the AI API

The endpoints that concerned me most were:

/api/ai-advice
/api/greeting

During the investigation, I did not connect to the real Gemini API.

Instead, I mocked the Gemini client.

The result was that an anonymous request still caused:

Gemini Client
→ called once

So under the right conditions:

an unauthenticated user could reach the AI-processing logic.

Access Control Also Started with RED Tests

So I added a new test file:

test_authorization.py

The filename uses authorization, but I want to be precise about what it means in this project.

I was not testing detailed role-based authorization.

What I was testing was:

access control that prevents anonymous users from reaching application pages and APIs that should require authentication.

The expected behavior became:

GET /login
→ anonymous 200

All other application pages/APIs that require authentication
→ anonymous 302
→ /login

The initial result:

1 passed, 6 failed

Only the public /login route was GREEN.

The other six routes still returned HTTP 200 to anonymous users, so they were RED.

🔐 Access Control Implementation and Existing Test Adjustments

Codex Stopped When It Realized Existing Tests Needed Changes

Something memorable happened at this point.

While preparing to implement access control, Codex determined that:

some existing tests would also need to change.

And it stopped before implementing anything.

For example:

test_dashboard.py

was checking API aggregation using an anonymous client.

Once the API became authentication-protected, that client would receive a 302 redirect and never reach the aggregation logic.

Some CSRF tests similarly retrieved tokens from application pages while anonymous.

So I reorganized the responsibilities:

test_authorization.py
→ verifies anonymous users cannot enter protected areas

test_dashboard.py
→ verifies correct aggregation after authentication

test_csrf.py
→ verifies CSRF behavior

test_auth.py
→ verifies authentication behavior

I had already been instructing Codex:

if an unexpected change becomes necessary, do not continue automatically — report it first.

This time, it followed that rule.

It stopped instead of silently modifying existing tests, so I reviewed the reason and then allowed only the minimum necessary changes.

Protecting Six Routes with `login_required`

In the end, I added login_required to:

/
/input
/dashboard
/api/dashboard-data
/api/ai-advice
/api/greeting

After implementation:

GET /login
→ anonymous 200

Other six routes
→ anonymous 302
→ /login

For the AI endpoints, I also added regression tests confirming that, during anonymous access:

Gemini Client
→ 0 calls

So an anonymous user no longer reaches the AI-processing code.

Stage 3 Finished with 69 GREEN Tests

The progression was:

51 passed
↓
56 passed
↓
62 passed
↓
69 passed

Stage 3 directly added:

Authentication     5 tests
CSRF               6 tests
Access control     7 tests

for a total of 18 additional tests:

51
↓
69

✅ Final pytest Results and Test Responsibilities

Final pytest Results

pytest test_authorization.py -v
→ 7 passed

pytest test_auth.py -v
→ 5 passed

pytest test_csrf.py -v
→ 6 passed

pytest test_dashboard.py -v
→ 4 passed

pytest -v
→ 69 passed

The Tests Started to Develop Clear Responsibilities

One of the biggest things I noticed during Stage 3 was not simply that the test count increased.

It was that:

each group of tests started to have a clearer responsibility.

test_auth.py
→ login behavior and rejection of unauthenticated POST requests

test_csrf.py
→ CSRF tokens and rejection of tokenless POST requests

test_authorization.py
→ anonymous users cannot access protected pages/APIs

test_dashboard.py
→ aggregation after authentication

test_products.py
→ product behavior after authentication and CSRF checks

test_sales.py
→ sales behavior after authentication and CSRF checks

Earlier, many of my pytest tests were closer to:

A response came back.

Now they were gradually becoming:

"Which specific incident is this test responsible for stopping?"

I Also Used a Feature Branch and Pull Request

For Stage 3, I worked on:

feature/auth-hardening

After confirming all 69 tests were GREEN locally, I followed this flow:

feature/auth-hardening
↓
push to GitHub
↓
Pull Request
↓
GitHub Actions
↓
merge into main

I had already been using local pytest and GitHub Actions.

But this time, I also went through the process of:

creating a Pull Request from a feature branch, confirming CI success, and only then merging into main.

🚚 Commit, Pull Request, and GitHub Actions Details

I Split the Commits by Stage

I used separate commits:

feat: add single-admin authentication

feat: add CSRF protection

feat: protect authenticated routes

In the Pull Request, I checked:

base: main
compare: feature/auth-hardening
three commits
the diff
whether there were merge conflicts
GitHub Actions results

Only after that did I merge.

GitHub Actions Also Passed All 69 Tests

After creating the Pull Request, GitHub Actions ran Run Tests.

CI collected:

collected 69 items

and finished with:

69 passed

GitHub Actions reported:

Success

Only after confirming that result did I merge into main.

So the Stage 3 flow became:

Local pytest
69 passed
↓
Push feature branch
↓
Pull Request
↓
GitHub Actions
69 passed
↓
Merge into main

I Ran All 69 Tests Again After the Merge

After merging the Pull Request, I switched my local environment back to main and synchronized it with origin/main.

I confirmed that the local and remote main HEADs matched.

Then I ran:

pytest -v

one more time.

The result:

69 passed

The working tree was also clean.

At that point, I considered Stage 3 complete.

A Pull Request Feels Like a Shipping Checkpoint to Me

I've previously compared pytest to:

a pre-departure inspection

and GitHub Actions to:

an automated inspection at the shipping gate.

Using Pull Requests added another checkpoint:

working branch
↓
Pull Request
↓
CI
↓
main

Thinking about it through my work as a truck driver, it feels something like:

Inspect in the work area
↓
Check whether it is ready to ship
↓
Automated inspection
↓
Send it onto the main route

Instead of saying:

"It works locally, so put it straight into main."

I now have another place to review the diff and test results before the code enters main.

Using the process myself helped me understand why that extra checkpoint matters.

State of the Application at the End of Stage 3

At the end of Stage 3:

Single-admin authentication
✅

CSRF protection
✅

Anonymous access blocked for protected pages/APIs
✅

Anonymous AI API execution blocked
✅

pytest
69 passed

GitHub Actions
69 passed / Success

Pull Request
✅

Merged into main
✅

Post-merge main
69 passed

For the scope I defined:

Stage 3 was complete.

What I Deliberately Did Not Do in Stage 3

During the final review, I also found several things where I decided:

"I found this, but I'm not fixing it in this stage."

If I expanded into every improvement I discovered, the goal of Stage 3 would become unclear.

So I kept the scope to:

Single-admin authentication
CSRF protection
Blocking anonymous access to protected pages/APIs

📋 Improvements I Deliberately Left for Later

The final review identified possible future improvements such as:

logout
dedicated fail-closed tests for missing configuration
dedicated invalid-CSRF-token tests
Session Cookie settings
login attempt rate limiting
JSON 401 responses for APIs
cleaning up duplicated authentication checks

Logout Is Not Implemented Yet

At this point, there is still no:

logout route
logout_user()
logout button

Anonymous access protection itself works.

However, because the authentication system uses a Session Cookie, allowing an administrator to explicitly end a session is something I want to add later.

This becomes especially important if the application is used on a shared device.

I plan to reconsider it before a real production rollout.

There Is No Detailed Role-Based Authorization Yet

The current application still has no:

User database model
general users
roles
tenants
store IDs
store-level access control

The only user who can log in is the single administrator configured through environment variables.

So Stage 3 assumes:

authenticated user
=
administrator

and considers the following sufficient for this stage:

only the authenticated administrator can reach protected application functionality.

If I introduce multiple users in the future, I will need to design role-based and store-level permissions separately.

Production Requires Environment Variables

The authentication implementation reads:

SECRET_KEY
ADMIN_USERNAME
ADMIN_PASSWORD_HASH

from environment variables.

So these values must be configured correctly before using authentication in production.

In particular:

ADMIN_PASSWORD_HASH

expects a Werkzeug-compatible password hash, not a plaintext password.

Stage 3 did not include changing the production environment configuration on Render.

Summary

Stage 3 of strengthening pytest moved the suite from:

51 passed
↓
69 passed

The main additions were:

single-admin authentication
CSRF protection
access control for protected pages and APIs
preventing anonymous AI API execution
regression tests for authentication, CSRF, and access control

Stage 1 began with:

turning only three pytest tests into the beginning of a regression suite.

Stage 2 expanded into:

database updates, rollback behavior, soft deletion, and aggregation.

And Stage 3 expanded the question again.

It was no longer only:

"Can the application save the correct data?"

It also became:

"Who is allowed to reach the code that performs that operation?"

This was also the first stage where I actually used the full flow:

feature branch
↓
Pull Request
↓
GitHub Actions
↓
merge into main

There are still areas I can improve.

But by continuing with:

Investigate
↓
RED
↓
Smallest necessary fix
↓
GREEN
↓
Commit
↓
Next problem

pytest has gradually started to feel less like:

something I run after an incident

and more like:

a mechanism that catches known failure conditions before they can cause the same problem again.

Next, I plan to move on to Stage 4.

pytest Improvement Series

https://github.com/tosane932/sales_data_app

https://qiita.com/tosane932

🐕‍🦺 Don’t Let Codex “Eat Things Off the Ground”: Preventing Scope Creep in pytest Stage 3

tosane932 — Fri, 21 Aug 2026 10:17:55 +0000

Hello from Japan 🇯🇵

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

This article is part of my ongoing record of strengthening pytest in my personal Flask project.

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

https://github.com/tosane932/sales_data_app

Introduction

Recently, I've been using Codex in VS Code while strengthening pytest in a Flask application I'm building.

I'm currently working on Stage 3.

This stage focuses on authentication, authorization, CSRF protection, and questions such as:

"Who is allowed to perform this operation?"

and:

"Was this action actually intended by the user?"

While working with Codex on this, a strange analogy suddenly came to mind:

Isn't this a bit like a dog eating random things off the ground during a walk? 🦮

In Japanese, there's a word for this: 拾い食い (hiroi-gui).

It describes a dog finding something on the ground and eating it without waiting for its owner.

And the more I worked with Codex, the more similar it started to feel.

Codex Finds Problems I Didn't Ask It to Find

When I ask Codex to investigate code, it sometimes finds problems outside the scope of the current task.

For example, even if I say:

Only investigate authentication this time.

Codex may respond with things like:

There is also a problem in another API.

This process could also be improved.

This part probably needs to be fixed as well.

That isn't a bad thing.

In fact:

having it discover problems that I didn't notice myself is extremely useful.

But the situation changes when it goes one step further and says:

I fixed that while I was at it.

That's where I become cautious.

"While I'm Here, I'll Fix This Too" Started Looking Like a Dog Eating Things Off the Ground

This is the analogy that came to mind while working with Codex.

Imagine a dog out for a walk:

Finds something on the ground
↓
Gets curious
↓
Puts it in its mouth without asking the owner

That's the behavior I'm talking about.

Codex can sometimes behave similarly:

Investigates the code
↓
Finds another problem
↓
Decides it looks worth fixing
↓
Fixes it while it's there

From its perspective, it may feel like:

"I did something helpful! 🐶"

But from the owner's perspective:

Wait, wait!

Where did you get that?

You can't just eat random things!

And with Codex, my reaction becomes:

Wait, wait!

I didn't ask you to touch that this time!

Don't expand the scope on your own!

Finding Problems Is Fine ⭕ — Fixing Them Without Asking Is Not ✖

Recently, I've started making my instructions to Codex much more explicit.

For example:

If you discover an unexpected problem,
do not expand the scope of the current task.

Report the problem only and stop.

The idea is simple:

Finding a problem is fine.

Fixing it without permission is not.

When a new issue is discovered, I want the flow to look like this:

Problem discovered
↓
Report it
↓
Stop the current work
↓
Human reviews it
↓
If necessary, create a separate task

This lets me separate:

discovering a problem

from:

deciding to fix it.

Why Are Unplanned Fixes Risky?

At first glance, you might think:

If you've already found the problem, wouldn't it be faster to fix it at the same time?

I thought the same thing at first.

But after working through several changes, I started seeing some problems with this approach.

The Scope of the Change Becomes Hard to Understand

Imagine that the original task was:

Add authentication

But the final change includes:

Authentication
API fixes
Database changes
Template changes
Logging improvements

Now it becomes much harder to understand:

which change was made for which purpose.

When Something Breaks, Finding the Cause Becomes Harder

If there is only one change, I can reasonably think:

This change is probably what caused the problem.

But if several unrelated fixes are mixed together:

Which change caused it?

becomes much harder to answer.

The Meaning of the Tests Also Becomes Less Clear

Recently, I've been thinking of pytest as an:

"incident prevention log."

When I discover one problem, I try to follow this process:

Reproduce the dangerous state with a test
↓
Confirm that the test fails
↓
Make the smallest necessary fix
↓
Confirm that the test passes

But if I also fix unrelated problems in the middle of that process, it becomes harder to tell:

which code change was actually intended to make which failing test pass.

I Used "Wait 🖐" During pytest Stage 3 Too

In the current Stage 3 of my pytest work, I'm focusing on authentication-related behavior.

First, without changing any production code, I investigated the current state of:

Authentication
Authorization
CSRF

Then I added a future requirement as a failing test:

Reject unauthenticated POST requests.

I also added login behavior as another failing test.

At that point, I confirmed:

5 failed

Only after that did I move on to the smallest authentication implementation needed.

After the fix:

56 passed

During this process, I kept the scope deliberately narrow:

Authentication only this time

Do not touch CSRF yet

Do not touch logout yet

Do not protect the APIs yet

Even when Codex could clearly continue into the next area:

I stopped it.

"It Can Do It" and "It Should Do It Now" Are Different

One thing became especially clear to me during this stage:

what Codex can do and what Codex should do right now are two different things.

For example:

It can implement CSRF protection.

It can add logout.

It can protect the APIs.

But that does not mean:

Do all of them now.

The human still needs to decide:

This is where today's task ends.

I think this applies far beyond programming.

`git status` and `git diff` Are Like Checking the Dog's Mouth After a Walk

Imagine your dog comes home from a walk.

You might wonder:

Did you eat anything strange out there?

After Codex finishes working, I do something similar by checking the Git state and diff.

For example:

git status --short

git diff --stat

git diff

git diff --check

I use these to check:

whether only the requested files were modified
whether unexpected files appeared
whether unrelated fixes were mixed in
whether there are whitespace errors

In my head, it looks something like this:

Codex:
"I finished the walk properly! 🐶"

git diff:
"Okay... let me see what's in your mouth."

Even if the AI reports that there were no changes outside the requested scope, I still check the diff myself.

pytest Is Not a Tool for Checking Whether the Dog Ate Something

I run pytest too.

But pytest is not primarily a tool for checking whether Codex made unauthorized changes.

What pytest checks is:

Did we break behavior that the application was already supposed to preserve?

So these checks have different jobs:

Task scope
↓
git diff
↓
pytest

In my head:

Task scope
= deciding the walking route

Stop on unexpected issues
= don't eat random things

git diff
= checking after the walk

pytest
= checking existing rules and system health

Each one protects a different part of the workflow.

I Don't Let Codex Roam Free

I previously wrote another article about not letting Codex "roam free."

👉 Don't Let Codex Roam Free: 6 Guardrails I Use for AI-Assisted Coding

This idea of preventing it from "eating things off the ground" is really an extension of the same approach.

Codex can work very quickly.

That also means it can move through a sequence like this very quickly:

Investigate
↓
Discover a problem
↓
Fix it
↓
Discover another problem
↓
Fix that too

That's exactly why I think:

the faster an AI can move, the more important it is to define where it must stop.

An Instruction I Use More Often Now

Recently, I've started giving Codex instructions like this:

If you discover an unexpected problem,
do not expand the scope of the current task.

Report it and stop.

Do not continue to the next task.

Do not commit or push.

That may sound overly cautious.

But it isn't really because I distrust Codex.

It's more that:

because Codex can move so quickly, I want its working boundaries to be explicit.

Summary

While working through pytest Stage 3 with Codex, I realized:

Codex making unplanned "while I'm here" fixes feels a little like a dog eating random things off the ground. 🐩

My current rules for preventing that are:

define the scope of the current task
report unexpected problems instead of fixing them automatically
separate discovery from fixing
don't let the AI move into the next task on its own
inspect the actual changes with git diff
use pytest to verify that existing behavior still works

The most important rule is not:

"Don't find problems."

Actually, I want Codex to find them.

What I really mean is:

Find them.

Just don't eat them without asking.

Codex is a very capable dog. 🐾

And for now, I've found that I work best with it when I'm able to say:

Wait.

We're not going there yet.

Next, I plan to continue Stage 3 by working on CSRF protection.

https://github.com/tosane932/sales_data_app

https://qiita.com/tosane932

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

tosane932 — Fri, 21 Aug 2026 09:13:49 +0000

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

pytest Improvement Series

https://github.com/tosane932/sales_data_app

https://qiita.com/tosane932

Don't Let Codex Roam Free: 6 Guardrails I Use for AI-Assisted Coding

tosane932 — Fri, 21 Aug 2026 08:01:14 +0000

Leaving Everything to AI Feels Risky. But Codex Became Much More Reliable Once I Put Up Some Guardrails

https://github.com/tosane932/sales_data_app

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 using Python and Flask.

Recently, I've been using Codex in VS Code while working on my personal application, including:

improving security
strengthening pytest coverage
validating database behavior
checking migrations
handling invalid input

When I read about AI-assisted coding, I often come across warnings like:

If you leave everything to AI, things can go badly wrong.

I think that's true.

But after using Codex for a while, I started to feel that there is a big difference between:

letting AI roam free

and:

defining its working area before assigning the task.

In this article, when I say "guardrails," I mean deciding not only what Codex should do, but also what it must not do and where it should stop.

I'm still a beginner in programming, but these are the six guardrails I've started using when working with Codex.

1. Define What It Is Allowed to Touch

The first thing I do is limit the scope of the task.

In other words:

What exactly are we changing this time?

For example:

only validate input for the sales POST endpoint
only handle rollback when a database save fails
only validate POST requests for the product master

I try to keep each task focused on one small theme.

I also tell Codex:

If you find another problem outside the current scope, do not fix it automatically. Report it instead.

There is nothing wrong with Codex discovering another issue while investigating.

But if it keeps expanding the task and fixing additional problems along the way, I can eventually end up wondering:

What exactly changed?

So I try to separate:

discovering a problem

from:

fixing a problem.

2. Create a Failing Test First

Recently, I've increasingly asked Codex to write pytest tests before modifying the production code.

My basic workflow is:

Reproduce the dangerous condition with a test
Run the test before making the fix
Confirm that the test fails
Make the smallest necessary change
Confirm that the target test passes
Run the entire pytest suite again

For example, when I strengthened input validation for my product master, I added 17 invalid-input cases.

Before the fix:

17 failed

After the fix:

17 passed

By confirming that the test fails first, I can verify more than just:

"It seems safer now."

I can confirm that the problem actually existed, that the test reproduced it, and that the change closed the gap.

Recently, I've stopped thinking of pytest as just a way to confirm that the application works.

Instead, I think of it more like an:

incident-prevention log.

Once I discover a dangerous condition, I record it as a test so the application cannot silently return to that state later.

3. Don't Let It Directly Touch the Normal Database or Production

During testing, I generally avoid connecting Codex-driven tests directly to:

the PostgreSQL database used during normal development
the normal Docker database
the actual Gemini API
the production environment

For ordinary pytest runs, I separate the test database from the normal database and use a disposable in-memory SQLite database:

sqlite:///:memory:

For regular tests, this gives me a fast and disposable environment.

However, I do not assume SQLite can fully reproduce PostgreSQL-specific behavior.

When I need to verify PostgreSQL-specific behavior or migrations, I create a separate isolated PostgreSQL environment.

I separate things such as:

containers
networks
databases
volumes

from the normal environment.

My thinking is not:

"It's only a test, so it's probably fine."

Instead:

"Test in a place where failure cannot easily spread into the normal environment."

The goal is not simply to avoid using the production or normal database.

The real goal is:

to reduce the impact if something goes wrong.

4. Don't Let an Unexpected Problem Expand the Task Automatically

While Codex is investigating one issue, it sometimes discovers another unrelated problem.

In the past, I might have said:

Go ahead and fix that too.

Now I usually stop there.

I increasingly tell Codex:

If you discover an unexpected problem, do not expand the current scope. Stop and report it.

For example:

Current task:
Validate the sales POST endpoint
        ↓
Codex discovers another database issue
        ↓
Do not fix it immediately
        ↓
Report it as an unresolved issue
        ↓
Create a separate task for it later

This prevents a single change from becoming unnecessarily large.

For me:

being able to trace what changed is more important than fixing every discovered problem immediately.

5. Keep a Human Check Before Commit and Push

Even after the code changes are complete and the tests pass, I don't immediately let Codex commit or push.

First, I have it check things like:

pytest -v
git diff --check
git status --short
git diff --stat
git diff

Then I review the results and check:

Were only the intended files modified?
Did all pytest tests pass?
Did unrelated changes get mixed in?
Were any unexpected files added?

Only after that do I give permission:

"Everything looks good up to this point. You can commit."

Codex can write code for me, but I don't want it to automatically pass through the final Git gate.

After the commit, I also ask it to report:

commit hash
commit message
push destination
push result
final git status

Recently, I've also started thinking that relying only on a written instruction like:

"Do not push."

is not strong enough.

If I forget to include that instruction even once, the AI may interpret pushing as allowed.

So ideally, I want two layers of protection:

Restrict the action in the prompt
+
Restrict the actual permissions

If I also want to account for human mistakes, simply saying:

"Don't do this."

is weaker than creating an environment where the action is difficult or impossible without explicit permission.

6. Let pytest and CI Inspect the Result Too

After pytest passes locally, the relevant pushes and Pull Requests trigger another pytest run through GitHub Actions.

In my current setup, GitHub Actions runs when:

code is pushed to main
a Pull Request targeting main is created

In another article, I compared this process to truck inspections.

For me, the analogy looks like this:

pytest: the inspection tool
pytest tests: the inspection checklist
local pytest: the pre-departure inspection
GitHub Actions: the automated inspection at the shipping gate

The task isn't finished just because Codex says:

The fix is complete!

Instead, I try to make every change pass through several checkpoints:

Codex
  ↓
pytest
  ↓
git diff
  ↓
commit
  ↓
push
  ↓
GitHub Actions

I don't use the AI's own response as the final source of truth.

Instead:

I use other mechanisms to verify the work performed by the AI.

What Scares Me About Letting Codex "Roam Free"?

Imagine giving Codex a prompt like:

Make this application secure.

That's all.

Now the AI has to guess many things:

How much is it allowed to change?
Can it modify the database?
Can it connect to production?
Should it fix additional problems it discovers?
Can it commit?
Can it push?
Can it change the specification?

That's a lot of decisions.

What worries me isn't Codex itself.

What worries me more is:

the human defining nothing and then delegating not only the work, but also all of the decisions to the AI.

Delegating work to AI and dumping everything on AI may look similar, but I think they are very different.

It Reminded Me of My Work as a Truck Driver

When I thought about it, this approach felt surprisingly similar to my main job as a truck driver.

Normally, you wouldn't tell a new driver:

Just deliver this cargo somehow.

You would confirm things such as:

where they are going
which route they should take
the delivery time
what they need to watch out for
what they must not do
what to do if something unexpected happens

And when something unusual occurs, there are situations where the right action is not to make an arbitrary decision and continue.

Sometimes the correct action is:

stop and confirm.

While working with Codex, I started thinking:

This is actually similar to assigning work to someone in the real world.

Don't Just Give AI a Task — Define the Area Where It Can Work

I'm still relatively new to programming.

That means I cannot always evaluate 100% of Codex's output by myself.

And that's exactly why I don't want to completely trust the AI and give it unlimited freedom.

Instead, I try to create an environment where:

even if something goes wrong, the damage is less likely to spread.

The same idea applies to the pytest improvements I've been working on.

My goal is not:

Be careful not to cause the same bug again.

My goal is:

If the application ever returns to the same dangerous state, pytest should stop it.

I think the same way about Codex.

I don't let Codex roam free.

Define the scope

Create failing tests first

Keep it away from normal databases and production

Make it stop when something unexpected appears

Review changes before commit and push

Inspect the result again with pytest and CI

I create those guardrails first, and then I let the AI work inside them.

And ideally, I don't want those guardrails to exist only in the prompt.

Where possible, I also want to enforce them through actual permissions and environment restrictions.

That's something I plan to pay more attention to going forward.

Letting Codex roam free still scares me.

But with clear guardrails, I've found it can be extremely useful.

That's what I've learned through my recent personal development work.

https://github.com/tosane932/sales_data_app

https://qiita.com/tosane932

🔰 pytest, GitHub Actions, and CI — Explained Through a Truck Driver’s Inspection Log

tosane932 — Mon, 17 Aug 2026 12:12:00 +0000

Update

This article records how I understood the relationship between pytest, GitHub Actions, and CI at the point when I had expanded my test suite from 3 tests to 9.

Since then, I have continued developing pytest as what I call an “incident-prevention ledger.”

The regression suite has expanded into areas such as sales input, product registration, database constraints, rollback behavior, historical-data preservation, and dashboard aggregation.

Therefore, references to “9 tests” and “areas I want to test next” describe the state of the project at the time this article was written.

Introduction

Hello from Japan! 🇯🇵

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 this article, my total learning time had reached 159 hours.

GitHub:

https://github.com/tosane932/sales_data_app

In my personal project, I had been working on:

XSS protection
Database migration repairs
Strengthening pytest
Automated testing with GitHub Actions

But because I was still a beginner, I kept running into very basic questions:

What exactly is pytest doing?

What is the difference between pytest and GitHub Actions?

And where did this thing called CI suddenly come from?

Rather than memorizing the terminology, I tried to understand it using concepts from my day job:

Truck inspection logs and workplace safety management.

This article is my beginner-level explanation of pytest, GitHub Actions, CI, and regression testing through that analogy.

What I Was Actually Building

At the time, my development workflow was starting to look like this:

Discover a bug or near miss
        ↓
Investigate the cause
        ↓
Fix the code
        ↓
Add a regression test to pytest
        ↓
Push to GitHub
        ↓
GitHub Actions automatically runs pytest
        ↓
If the dangerous state returns, CI fails

At first, I thought I was simply:

Writing pytest tests

But gradually, I realized I was building something more useful:

A system that records previously discovered problems and automatically warns me if the application returns to the same dangerous state.

What Is pytest?

pytest is a tool for writing and running tests in Python.

Very simply:

It automatically checks whether your program still behaves the way you expect.

For example, imagine this function:

def add(a, b):
    return a + b

You could write:

def test_add():
    assert add(2, 3) == 5

Here:

assert

means something like:

I expect this to be true.

If the result is correct, the test passes.

PASSED

If the result is wrong, it fails.

FAILED

That's the basic idea.

In Trucking Terms, a pytest Test Is an Inspection Item

Before taking a truck onto the road, there are things that must be checked.

For example:

□ Are the tires in good condition?
□ Do the lights work?
□ Is the oil level okay?
□ Are the brakes working normally?

pytest started making more sense to me when I thought about it the same way.

For a software system, the inspection items might be:

□ Is the AI prompt still structured correctly?
□ Is untrusted text still rendered safely?
□ Is product data processed correctly?
□ Does invalid sales input leave the database unchanged?

So in my head:

One pytest test ≈ one inspection item.

That simple analogy made the concept much easier to understand.

A Test Suite Feels Like an Inspection Logbook

When multiple pytest tests are collected together, they start to resemble an inspection logbook.

At the time this article was written, I had expanded the project from 3 pytest tests to 9.

But I was not simply trying to increase the number.

For example, I had previously discovered a place where AI-generated responses were displayed using JavaScript:

innerHTML

After fixing that XSS risk, I added a pytest regression test to check:

Has this rendering path accidentally returned to the dangerous implementation?

The workflow became:

Discover an XSS risk
        ↓
Fix it
        ↓
Record the fix as a pytest regression test

Even if I forget the details six months later, pytest can still remember the inspection item for me.

I have continued expanding the test suite using this idea.

“I'll Be More Careful Next Time” Is Not Enough

This is another idea that comes from my day job.

After an accident or mistake, saying:

I'll be more careful next time.

is weak as a prevention strategy.

It still depends entirely on human attention.

I think an important distinction is between:

Never make the same mistake again.

and:

Prevent the same dangerous situation from existing again.

Suppose there is a location on a delivery route where a dangerous situation repeatedly occurs at the same time of day.

One response is:

Drive more carefully there.

But stronger options might be:

Change the delivery time
Change the delivery order
Use another route

Instead of relying only on attention, change the conditions that create the risk.

I found the same idea useful in software development.

XSS Is a Good Example

My application previously displayed AI-generated text using:

innerHTML

A weak response would be:

Be careful not to pass dangerous HTML into it.

Instead, I changed the implementation so that:

Even if HTML-like text arrives,
it is not interpreted as HTML.

Then I added a pytest regression test that checks whether the relevant code path has returned to its previous dangerous form.

This changes the strategy from:

Be careful.

to:

Build the system so that the unsafe state is harder to recreate.

That feels very similar to workplace safety management.

So What Is GitHub Actions?

This is where I originally became confused.

To run pytest locally, I type:

pytest -v

So if pytest already runs the tests, what does GitHub Actions do?

In my repository, GitHub Actions is configured so that when I push code to GitHub:

GitHub automatically prepares an environment and runs pytest for me.

For example:

git push origin main

can trigger:

GitHub Actions starts
        ↓
Prepare a Python environment
        ↓
Install dependencies
        ↓
Run pytest -v
        ↓
Report the result

In my current configuration, tests also run for Pull Requests targeting main.

So pytest is the testing tool.

GitHub Actions is the automation platform that runs those tests in response to GitHub events.

Pre-Trip Inspection and the Shipping Gate

The trucking analogy helped here too.

When I run:

pytest -v

on my own machine, I think of it as:

A pre-trip inspection I perform myself.

Then I push the code.

GitHub Actions runs pytest again in GitHub's environment.

I think of that as:

Another automated inspection at the shipping gate.

Conceptually:

Developer:
"I inspected it locally."
        ↓
push
        ↓
GitHub Actions:
"We'll inspect it here too."
        ↓
Tests passed
        ↓
OK

The useful part is that the code is checked not only in my own development environment, but also in another automated environment.

Then What Is CI?

This brings us to:

which stands for:

Continuous Integration

When I first heard that term, I thought:

Okay... but what does it actually do?

For my current level and project, the easiest way to understand CI is:

A workflow that automatically checks code changes when they are integrated into the shared repository.

CI can include more than testing.

For example, it may also include:

Linting
Static analysis
Builds
Security checks
Formatting checks

But in my repository, the central CI activity is:

GitHub Actions automatically running pytest

So I stopped trying to understand CI as an abstract buzzword and instead connected it to what my project was actually doing.

pytest vs. GitHub Actions vs. CI

This is how I currently organize the concepts in my head:

Name	My Understanding
pytest	The inspection tool
One pytest test	One inspection item
Test files	The inspection logbook
GitHub Actions	The equipment that runs inspections automatically
CI	The ongoing process of automatically checking code changes

Once I separated the responsibilities this way, everything became much easier to understand.

My Actual GitHub Actions Configuration

The important part of my workflow looks roughly like this:

on:
  push:
    branches: [ main ]

  pull_request:
    branches: [ main ]

jobs:
  test:
    runs-on: ubuntu-latest

    steps:
      - uses: actions/checkout@v4

      - uses: actions/setup-python@v5
        with:
          python-version: '3.12'

      - run: pip install -r requirements.txt

      - run: pytest -v

A push to main or a Pull Request targeting main causes GitHub Actions to run pytest automatically.

Previously, the workflow used:

pytest test_prompts.py -v

That only executed one specific test file.

So even if I added:

test_security.py
test_sales.py
test_database.py

GitHub Actions would ignore them.

I changed it to:

pytest -v

Now pytest uses its normal discovery rules and automatically includes newly added tests.

Why Is This Useful?

Imagine I change a completely different feature in the future.

The new feature itself may work correctly.

But without realizing it, I could accidentally break an XSS protection that I fixed months earlier.

It would be difficult for a human to remember every previous issue:

Check XSS
Check prompts
Check Jinja
Check Gemini
Check sales behavior
Check database state
...

every single time.

Instead, I can record those checks in pytest.

Then:

pytest performs the inspections

for me.

And with GitHub Actions:

Even if I forget to run pytest locally,
GitHub performs another automated check.

That does not mean GitHub Actions makes local testing unnecessary.

My mental model is still:

Local pytest = pre-trip inspection

GitHub Actions = automated inspection at the shipping gate

Two layers are better than relying on one.

Turning Small Near Misses into Tests

At the time I wrote this article, I wanted to continue this cycle:

Discover a small bug
        ↓
Investigate the cause
        ↓
Fix it
        ↓
Write a regression test
        ↓
Let GitHub Actions check it every time

This is why I started calling pytest:

An incident-prevention ledger.

Every time I discover another problem, another inspection item can be added to the logbook.

Since then, I have expanded the idea into database-changing operations such as:

Sales entry
Product registration

For those tests, I no longer want to check only:

Did the HTTP request return the expected response?

I also want to check:

What happened to the database after the request failed?

That is a much stronger inspection.

pytest Alone Does Not Make a System Safe

This is important.

At the time this article was written, I had 9 pytest tests.

That did not mean:

This application is now completely safe!

Many areas were still untested.

At the time, I wanted to expand coverage into areas such as:

PostgreSQL migrations
Authentication
CSRF
Invalid sales input
Gemini API failures
Real browser DOM behavior

Since then, I have added regression coverage for areas including:

Invalid sales input
Product registration
Database uniqueness constraints
Rollback behavior
Historical-data preservation after soft deletion
Dashboard aggregation

Some other areas, particularly:

Authentication
Authorization
CSRF

are still planned for separate stages.

Having an inspection logbook does not guarantee that an accident can never happen.

But it does make it harder to overlook a dangerous state that has already been discovered once.

That alone is valuable.

Conclusion

If someone had told me when I first started programming:

pytest
GitHub Actions
CI
regression tests

I probably would not have understood much.

But translating those concepts into my own work experience made them much clearer.

My current mental model is:

pytest
= inspection

pytest tests
= inspection items

test files
= inspection logbook

GitHub Actions
= automated inspection equipment

CI
= a process that continuously checks code changes

My development workflow has gradually become:

Discover a problem
        ↓
Investigate the cause
        ↓
Fix it
        ↓
Add a regression rule to pytest
        ↓
Verify locally
        ↓
Push
        ↓
GitHub Actions performs another automated inspection

At the time I wrote this article, the suite contained 9 tests.

Since then, I have continued adding real bugs and specifications that I want to protect.

My goal is for pytest to become not merely:

A tool for checking whether things work

but:

An incident-prevention ledger containing lessons from previous bugs and near misses.

I am still learning.

But I have found that understanding technical terminology does not always mean memorizing definitions.

Sometimes the most useful question is:

What is this actually doing?

And then translating that answer into words I already understand.

GitHub

https://github.com/tosane932/sales_data_app

📝 Turning pytest into an “Incident-Prevention Ledger” — Phase 1: From 3 Simple Tests to 9 Regression Tests

tosane932 — Fri, 14 Aug 2026 22:21:26 +0000

Update

This article records Phase 1, when I expanded the pytest suite from 3 tests to 9.

At that time, AI responses were rendered using a combination of createTextNode() and   elements.

After publication, a reader suggested using innerText. I tested that approach in my own environment and confirmed that, for this use case, it preserved line breaks while keeping HTML-like strings from being interpreted as HTML. The current implementation therefore uses innerText.

The XSS regression tests have also been updated to match the current implementation. They now verify that innerText remains in use and that dangerous HTML sinks such as innerHTML have not returned to the relevant rendering paths.

For that reason, references to createTextNode() and   in this article should be understood as a record of the implementation at the time Phase 1 was completed.

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

GitHub:

https://github.com/tosane932/sales_data_app

pytest Improvement Series

Introduction

Hello from Japan! 🇯🇵

I have been reviewing the security and maintainability of a Flask application I am developing, with help from Codex.

So far, I have fixed issues such as:

XSS risks in AI response rendering
Stored XSS in dynamically generated product rankings
A broken migration history that could not build the application from an empty PostgreSQL database

While working through those fixes, one question started bothering me:

Is my current pytest suite far too simple?

At the time, I had only three pytest tests.

All three focused on a single function:

build_sales_prompt()

So I decided to change how I thought about pytest.

Instead of treating it only as:

A tool for checking whether the application currently works

I wanted to develop it into:

An incident-prevention ledger that records bugs, vulnerabilities, and near misses so the application cannot silently return to the same dangerous state later.

For Phase 1, I added or strengthened:

Full pytest discovery in GitHub Actions
XSS regression tests
Jinja autoescape regression tests
Prompt contract tests
A test confirming that the complete prompt is actually sent to Gemini

The Original pytest Suite

Before this improvement, only three tests existed in test_prompts.py.

They mainly checked that:

Sales data appeared somewhere in the prompt
The phrase requesting three suggestions existed
The return value was a string

At first glance, that sounds like a test suite.

But after asking Codex to review the tests statically, I realized how weak they were.

In an extreme case, an implementation like this could potentially still pass:

return sales_summary + " Give me three suggestions. Three bullet points."

That would still satisfy:

The input sales data exists
A fixed phrase exists
The return value is a string

But important parts of the real prompt could disappear, including:

The AI's role
Analysis perspectives
Number of recommendations
Response format
Expected answer length

and the tests might still pass.

In other words:

Tests passing did not necessarily mean the intended specification was protected.

Another Problem: CI Was Not Running Every Test

During the investigation, I also found a problem in GitHub Actions.

The CI workflow was running:

pytest test_prompts.py -v

That meant if I later added:

test_security.py
test_sales.py
test_api.py

GitHub Actions would not run them.

They might pass locally but be completely ignored by CI.

That is a serious problem if pytest is supposed to become an incident-prevention system.

I changed the command to:

pytest -v

This lets pytest use its normal test-discovery rules and automatically collect newly added test files.

The actual change was only one line:

- pytest test_prompts.py -v
+ pytest -v

But for the future of the test suite, that one line was important.

The Goal of Phase 1

The goal was not simply:

Increase the number of tests.

Instead, I wanted this behavior:

If a bug or vulnerability that I have already fixed returns in the future, pytest should fail immediately.

For this phase, I deliberately avoided changing application behavior.

I focused on converting already-fixed behavior into regression tests.

Growing the Suite from 3 Tests to 9

By the end of Phase 1, the suite had grown from three tests to nine.

1. Verify That Sales Data Appears in the Correct Section

The first strengthened test was:

test_build_sales_prompt_places_sales_data_in_its_section

Previously, the test only checked whether the sales data appeared somewhere in the prompt.

The new test checks that it appears in the correct sales-data section.

This makes it harder for a broken prompt structure to pass unnoticed.

2. Protect the AI Role and Analysis Contract

The next test was:

test_build_sales_prompt_preserves_role_and_analysis_contract

The prompt contains important instructions defining:

The AI's role
The perspectives the AI should use when analyzing the sales data

Previously, a few surviving fixed strings could be enough for the tests to pass.

Now, the test protects the broader analytical contract of the prompt.

3. Protect the Output Contract

I also added:

test_build_sales_prompt_preserves_output_contract

This verifies important response requirements such as:

Number of recommendations
Bullet-point formatting
Expected answer length
Conciseness

I intentionally did not compare the entire prompt character-for-character.

A full exact-string comparison would make the test excessively fragile.

Even a harmless wording improvement could break it.

Instead, I test the important pieces of the contract.

Does the Complete Prompt Actually Reach Gemini?

One test I considered especially important was:

test_generate_ai_advice_sends_complete_sales_prompt

Previously, I only tested:

build_sales_prompt()

in isolation.

But even if that function works perfectly, it is useless if the actual Gemini request does not use the prompt it creates.

So the new test verifies that:

The content generated by build_sales_prompt() is actually passed to Gemini
The configured model name is used
Gemini's returned text is passed back correctly

This connects the unit-level prompt logic to the actual AI integration path.

Mocking Gemini Instead of Calling the Real API

I did not want pytest to call the real Gemini API every time the suite ran.

That would introduce unnecessary problems:

Real API keys would be required
External network access would be required
Tests could consume API quota
Results could depend on network availability
External service failures could make local tests fail

So I used:

unittest.mock.Mock

to mock the Gemini client.

I configured a dummy API key and replaced:

genai.Client

with a mock.

The response was also fixed:

SimpleNamespace(
    text="Mocked AI advice"
)

This created a test with:

No real API key
No external communication
No real Gemini request
No dependency on network conditions

Regression Tests for XSS

One of the most important goals of this phase was:

Preserve the XSS fixes I had recently made.

I added:

test_dynamic_ranking_product_name_uses_text_dom_api
test_dashboard_ai_responses_use_text_dom_api
test_input_ai_response_uses_text_dom_api

Stored XSS in the Dynamic Ranking

Previously, dynamically displayed product names were inserted using:

innerHTML

I had already changed that code to use safe DOM APIs and:

textContent

The regression test protects that specific rendering path so that a future refactor does not accidentally return product names to a dangerous HTML sink.

XSS in AI Response Rendering

AI business advice and AI greetings had also previously used:

innerHTML

At the time Phase 1 was implemented, I had replaced that with:

createTextNode()
 
DOM APIs

so that the AI response itself would never be interpreted as HTML.

I added source guards to ensure that those rendering paths did not silently return to innerHTML.

After publishing the earlier article, I tested and adopted innerText.

The regression tests have since been updated to match the current implementation.

They now verify:

innerText remains in the relevant rendering path
innerHTML does not return there
outerHTML does not return there
insertAdjacentHTML does not return there

The implementation changed.

The protected security property did not.

I Did Not Ban the Word `innerHTML` Everywhere

I thought carefully about this.

It would be very easy to write a test like:

Fail if the string "innerHTML" appears anywhere in the file.

But that would be too broad.

A future feature might have a legitimate and safely controlled reason to use innerHTML.

A global ban could create false failures unrelated to the original vulnerability.

So I limited the regression tests to the actual external-string rendering paths that previously caused problems:

Product rankings
AI business advice
AI greetings

The goal of pytest is not:

Ban the word innerHTML.

The goal is:

Prevent the application from returning to the same dangerous state that previously existed.

Turning Jinja Autoescape into a Regression Test

The initial AI response display had previously used:

| safe

I removed it and returned to Jinja's default autoescaping behavior.

So I added:

test_dashboard_initial_ai_binding_does_not_disable_autoescape

This source guard checks that:

{{ ai_advice }}

is used and that:

| safe

has not returned to that rendering path.

This makes it easier to detect a future regression where autoescaping is accidentally disabled again.

Actually Rendering HTML-Like Text Through Jinja

I added another test:

test_dashboard_initial_ai_advice_autoescapes_html_like_text

This one goes further than inspecting the template source.

For example, I pass strings such as:

<b>Test</b>
<img src=x>

into the Jinja template.

The test then confirms that they do not become real:

<b>

or:

<img>

elements.

Instead, they must remain escaped text.

This test actually renders the template and inspects the generated HTML using BeautifulSoup.

So I now had both:

A source guard
A behavior-level rendering test

protecting the same security boundary from different directions.

pytest Results

First, I checked test discovery:

pytest --collect-only -q

At the time Phase 1 was completed:

9 tests collected in 8.23s

All new tests were successfully discovered.

Then I ran:

pytest -v

The result was:

9 passed in 6.37s

Summary:

Passed: 9
Failed: 0
Skipped: 0

These nine tests represent the suite at the end of Phase 1.

I have continued adding regression tests since then as I discover more bugs and specifications worth protecting.

Phase 1 Did Not Use PostgreSQL, Docker, or the Real Gemini API

For this first phase, I intentionally focused on lightweight regression tests.

The tests did not require:

PostgreSQL
Docker
The real Gemini API

They also performed no real database operations.

My priority was to build:

Fast tests with minimal external dependencies.

Database-related integration testing would come later.

Files Changed

Phase 1 changed four files:

.github/workflows/test.yml
test_prompts.py
test_ai_integration.py
test_xss_regressions.py

The diff was:

4 files changed, 195 insertions(+), 13 deletions(-)

I did not modify the application code in:

app.py
prompts.py
models.py
templates/
migrations/

for this commit.

The goal was not to change the existing implementation.

It was:

Lock the already-correct behavior in place with tests.

Commit

After final verification, I created:

01d76e8 test: strengthen regression coverage

and pushed it to:

origin/main

What I Learned

1. The Number of Tests Matters Less Than What They Protect

Previously, I had three pytest tests.

But all three mostly examined one function.

Numerically:

3 tests
        ↓
9 tests

looks like the main improvement.

It was not.

The important improvement was that the protected surface expanded from:

Basic prompt string checks

to:

XSS
Jinja autoescape
Prompt contracts
Gemini integration
Full CI test discovery

Test count is easy to measure.

Protected behavior is what actually matters.

2. “HTTP 200” Is Not Enough

This work also reinforced another lesson:

HTTP 200

alone is a weak definition of success.

A request can return successfully while:

The database ends up in the wrong state
HTML is rendered unsafely
Duplicate records are created

So future tests need to check not only the response itself, but also:

What state exists after the request finishes?

3. pytest Can Become an Incident-Prevention Ledger

The biggest change was how I started thinking about pytest.

Before this work, my mental model was:

pytest
=
Check whether things work correctly

Now I think of it more like:

A record of previous incidents, bugs, and near misses that automatically stops the application from returning to the same dangerous condition.

For example:

Discover XSS
        ↓
Fix it
        ↓
Add a regression test
        ↓
A future change recreates the unsafe state
        ↓
pytest fails
        ↓
CI turns red

That means I do not need to remember every security fix forever.

The test suite remembers for me.

Not “Never Make the Same Mistake Again,” but “Never Recreate the Same Dangerous State”

This way of thinking comes partly from my day job.

When an incident or mistake happens, saying:

I'll be more careful next time.

is not enough.

That still relies on human attention.

The stronger question is:

How can we prevent the same dangerous condition from existing again?

I found the same principle useful in software development.

For XSS, the goal should not be:

Do not enter dangerous HTML.

Instead:

Even if HTML-like input arrives,
do not allow it to execute as HTML.

And pytest adds another layer:

If the safe implementation is broken later,
automatically stop the change.

That turns a lesson into a system.

What I Planned to Improve After Phase 1

At the time this Phase 1 article was written, I had focused on lightweight regression tests that did not require external APIs or a database.

Areas that were still largely unprotected included:

PostgreSQL empty-database migrations
Migration behavior on existing databases
Authentication and authorization
CSRF
Invalid sales input
Soft deletion and historical data preservation
Duplicate daily-sales prevention
Gemini API 429 responses
Gemini API 503 responses
General Gemini API exceptions
API response behavior
Dashboard aggregation

Instead of changing all of these areas at once, I planned to expand coverage gradually using this cycle:

Choose one dangerous state
        ↓
Write the test first
        ↓
Fix the implementation
        ↓
Run the full test suite

For database-related tests in particular, I also started paying more attention to:

The state of the database after invalid input or a failed save

rather than checking only HTTP responses.

Authentication, authorization, CSRF, and some other areas would be addressed in later phases.

Conclusion

During Phase 1, my pytest suite grew from 3 tests to 9.

But increasing the number was not the real objective.

The goal was to take bugs and vulnerabilities I had already discovered and preserve them as:

Regression rules that prevent the same dangerous states from returning.

The workflow became:

Discover
        ↓
Understand the cause
        ↓
Fix
        ↓
Add a regression test to pytest
        ↓
Verify automatically in CI

I want pytest to evolve from:

A tool that confirms normal behavior

into:

An incident-prevention ledger.

This article records only the first phase.

Since then, I have continued expanding the areas protected by tests using the same idea.

The AI-response rendering implementation also evolved after publication.

A reader suggested innerText, so I tested it instead of accepting the suggestion immediately.

That reinforced another principle I want to keep using:

Whether a suggestion comes from a human or an AI, verify it in your own environment before adopting it.

pytest Improvement Series

GitHub

https://github.com/tosane932/sales_data_app

Qiita

https://qiita.com/tosane932

Securing AI Response Rendering in Flask — Replacing innerHTML After a Codex Review

tosane932 — Fri, 14 Aug 2026 20:09:13 +0000

Update

This article mainly records the first fix I implemented, which used createTextNode() together with   elements.

After publication, a reader suggested using innerText instead.

I tested that approach in my own environment and confirmed that, for this particular use case, it preserved both line breaks and the important security property that HTML-like strings are not interpreted as HTML.

The current implementation therefore uses innerText, which is simpler.

For that reason, the sections below describing createTextNode(),   generation, and the resulting DOM structure should be understood as a record of the initial fix.

Introduction

Hello from Japan! 🇯🇵

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

I have been reviewing the security of a Flask application I am developing, with help from Codex.

This time, I found a place where text returned by an AI model was being displayed in the browser using JavaScript innerHTML.

The application appeared to work normally.

However, innerHTML does not treat a string as plain text.

It interprets that string as HTML.

That means if an HTML-like string reaches the page through an AI response or another external input, the browser may create unintended elements.

In the initial fix, I changed the rendering logic so that:

innerHTML was no longer used
AI responses were treated as text nodes
Only line breaks were created as   elements

I then verified:

Real Gemini responses
DOM structure
HTML-like test strings
JavaScript errors

After publishing the article, a comment led me to test an implementation using innerText.

That approach turned out to be simpler while preserving the behavior I needed, so the current implementation has since been refactored to use innerText.

GitHub:

https://github.com/tosane932/sales_data_app

The Application

The target is a sales management and analysis application I am developing for bakery stores.

The main stack includes:

Python
Flask
PostgreSQL
SQLAlchemy
Alembic
JavaScript
Gemini API
Docker
pytest
GitHub Actions

One of the application features sends sales data to the Gemini API and displays business-improvement advice returned by the model.

The Problem Codex Found

During a Codex-assisted security review, I discovered that the AI-response rendering logic used innerHTML.

For example, the dashboard contained code like this:

aiText.innerHTML = data.ai_advice.replace(/\n/g, '<br>');

The AI greeting on the input page used a similar pattern:

greetingText.innerHTML = data.message.replace(/\n/g, '<br>');

The original reason was simple:

I wanted to convert line breaks into   elements.

The problem was not the line breaks.

The problem was innerHTML.

For example, if this string were passed in:

<b>AI Test</b>

innerHTML would not display  as ordinary characters.

It would interpret the string as HTML markup.

Because this feature displays AI-generated text, it is tempting to assume:

The AI will probably return only normal text.

And in normal operation, that may often appear to work.

But I think a safer security principle is:

Do not depend on dangerous input never arriving.

Design the rendering path so that even dangerous-looking input does not become dangerous.

The Initial Jinja Rendering Also Used `safe`

The initial dashboard rendering also contained this Jinja expression:

{{ ai_advice | replace('\n', '<br>') | safe }}

Using safe disables Jinja's automatic escaping for that value.

At the time, the initial message was fixed text, so this was not immediately exploitable in the same way.

However, if that value later became dynamic, the same pattern could become dangerous.

I therefore reviewed this path as part of the same fix.

The Initial Fix

This feature did not actually need to allow HTML.

The only requirements were:

Japanese text
Multiple lines
Bullet-like symbols
Line breaks

So instead of adding a sanitization library such as DOMPurify, I chose a simpler rule:

Do not interpret the AI response as HTML at all.

The initial helper looked like this:

function setTextWithLineBreaks(element, value) {
    const fragment = document.createDocumentFragment();
    const lines = String(value ?? '').split('\n');

    lines.forEach((line, index) => {
        if (index > 0) {
            fragment.append(document.createElement('br'));
        }

        fragment.append(document.createTextNode(line));
    });

    element.replaceChildren(fragment);
}

The important part is:

document.createTextNode(line)

With this approach, even if the input contains:

<b>AI Test</b>

the browser does not create a b element.

It remains plain text.

This was the implementation used in the first fix.

After additional testing following publication, I later refactored the code to use innerText.

The Initial Fix Created Only Line Breaks as HTML Elements

AI responses often contain multiple lines, so I wanted to preserve line breaks.

In the first implementation, I split the response on \n and inserted only:

document.createElement('br')

between lines.

The DOM therefore looked conceptually like this:

Text node
BR
Text node
BR
Text node

The AI response itself remained entirely inside text nodes.

The only HTML elements created from this formatting process were the   elements.

The current implementation achieves the same goal more simply by combining innerText with:

white-space: pre-line;

Loading and Error Messages Were Changed to `textContent`

Fixed messages did not need innerHTML either.

I changed them to use textContent.

For example:

aiText.textContent = '🌀 The AI assistant is thinking about detailed improvement ideas...';

Even for fixed text, using textContent makes the intent clearer when HTML rendering is unnecessary.

Removing Jinja `safe`

I also restored normal Jinja auto-escaping for the initial render.

{{ ai_advice }}

Line breaks were handled in CSS instead:

white-space: pre-line;

This preserved line breaks without generating HTML from the template.

The same CSS remains useful in the current innerText implementation.

Confirming That Unnecessary `innerHTML` Usage Was Gone

Using Codex, I searched the target templates and confirmed that the unnecessary innerHTML usage had been removed from:

templates/dashboard.html
templates/input.html

Those were the only two files changed in the initial fix.

Browser Verification of the Initial Fix

I did not stop after changing the code.

For the initial implementation, I also used headless Chrome to verify the actual browser behavior.

1. Dashboard Initial Render

I checked the dashboard's initial state.

The following remained normal:

AI business-advice area
Buttons
Charts
Layout
Horizontal overflow

The original initial message did not contain a real line break, so I manually assigned this value in the browser DOM:

Initial line 1
Initial line 2

The verification result was:

lineBreakPreserved=true

So the line break was preserved.

2. Real Gemini Response

Next, I made one real request to the Gemini API.

HTTP 200
Gemini AI advice generated successfully

The AI response displayed correctly in Japanese.

Multiple lines were preserved.

Markdown-like characters such as:

*
**

were displayed as text rather than converted into HTML.

For the initial implementation, the resulting DOM looked like:

#text
BR
#text
BR
#text
BR
#text
BR
#text

The counts were:

Text nodes: 5
BR elements: 4
Other child elements: 0

That confirmed that the AI response itself was not producing HTML elements.

3. AI Greeting on the Input Page

I also tested the AI greeting on the input page.

HTTP 200
AI daily greeting generated successfully

The response displayed normally as two lines of Japanese text.

The initial DOM structure was:

#text
BR
#text

The counts were:

Text nodes: 2
BR elements: 1
Other child elements: 0

The form and button layout also remained intact.

Testing with HTML-Like Strings

Next, without sending anything to the database or Gemini API, I injected the following strings directly in the browser:

<b>AI Test</b>
<div>Test</div>
<em>Safety Check</em>

The result was the same on both the dashboard and input page.

b/div/em elements: 0
BR elements: 2

The DOM was:

#text
BR
#text
BR
#text

The text nodes literally contained:

<b>AI Test</b>
<div>Test</div>
<em>Safety Check</em>

Represented as HTML, the browser state was equivalent to:

&lt;b&gt;AI Test&lt;/b&gt;<br>
&lt;div&gt;Test&lt;/div&gt;<br>
&lt;em&gt;Safety Check&lt;/em&gt;

In other words:

<b>
<div>
<em>

were not interpreted as real HTML elements.

They remained ordinary text.

Again, this DOM result describes the first implementation using createTextNode() and  .

Checking for JavaScript Errors

During the initial fix, I monitored Chrome DevTools Protocol events including:

Runtime.exceptionThrown
console.error

The result was:

JavaScript errors: 0

Chrome itself produced some GPU and inotify warnings, but those were unrelated to the application's JavaScript.

pytest

I also ran the existing pytest suite at the time of writing.

PYTHONDONTWRITEBYTECODE=1 pytest -p no:cacheprovider

The result was:

collected 3 items
test_prompts.py ... [100%]

3 passed in 0.40s

All existing tests passed.

However, this revealed another problem:

At the time, there were only three pytest tests, and none of them could automatically detect this XSS rendering issue.

That meant the bug could potentially return later without the existing tests noticing.

This experience became one of the reasons I decided to strengthen pytest using actual bugs and vulnerabilities as regression tests.

Checking the Git Diff

Before committing the initial fix, I ran:

git diff --check

No whitespace errors were reported.

The only changed files were:

M templates/dashboard.html
M templates/input.html

Commit and Push

After completing the initial verification, I created this commit:

f354d5d fix: sanitize AI response rendering

I pushed it to origin/main.

After the article was published, a reader suggested testing innerText.

I did so and later refactored the implementation to the simpler version that is currently in use.

What I Learned

1. Do Not Design Around “The AI Probably Won't Return Anything Dangerous”

Even if an AI normally returns ordinary prose, safety should not depend on that assumption.

Whether the input comes from:

An AI model
A user
An external API

I think the safer principle is:

Treat external strings as strings.

2. If You Do Not Need HTML, Do Not Use `innerHTML`

For this feature, I only needed:

Text
Line breaks

There was no real need for:

innerHTML

The initial fix used text nodes and   elements.

After the article was published, I tested another approach and confirmed that, in this use case:

innerText

together with:

white-space: pre-line;

could achieve the same goal with less code.

The important lesson was not that one specific API is always correct.

It was:

If a value does not need to be interpreted as HTML, do not put it through an HTML-rendering API in the first place.

3. Jinja `safe` Is Useful, but It Disables Auto-Escaping

Jinja's safe filter is convenient.

But if I cannot clearly explain why a value needs to bypass escaping, it is safer not to use it.

In this case, the initial display could preserve line breaks using:

white-space: pre-line;

without disabling auto-escaping.

4. Inspecting the DOM Gives More Confidence Than Looking at the Screen Alone

A page can look completely normal while still creating unintended HTML elements internally.

During the first fix, checking the actual DOM structure:

Text node
BR
Text node

made it much easier to verify that HTML-like strings were not being turned into elements.

The implementation has since changed to innerText, but the principle remains useful:

Do not verify only how the page looks. Verify how the browser is actually treating the data.

5. Security Is About Preventing the Dangerous State, Not Just Avoiding the Same Mistake

This was probably the strongest lesson for me.

Instead of:

Be careful not to enter dangerous HTML.

I prefer:

Build the system so that even if HTML-like text arrives, it is not executed as HTML.

This reminds me of safety management in my day job.

When an incident or near miss happens, saying:

I'll be more careful next time.

is not enough.

If the same conditions occur again, the same incident can happen again.

A stronger approach is:

Change the process so that the same dangerous state cannot be recreated.

I found that the same idea applies to software security.

What I Want to Improve Next

At the time this article was written, I had fixed the XSS risk in the AI response rendering and verified the behavior through:

Browser testing
DOM inspection
pytest

However, the pytest suite itself still had very narrow coverage.

It could not automatically detect the vulnerability that had just been fixed.

My next improvement is therefore to take real bugs and vulnerabilities that I have encountered and preserve them as:

Regression tests that prevent the same dangerous state from returning.

Instead of relying on:

Remember not to make the same mistake again.

I want the system to fail a test as soon as the same unsafe state is reintroduced.

In the next article, I plan to strengthen pytest so that it becomes more than a general functional test suite.

I want to treat it almost like:

An incident-prevention ledger that records previous bugs, near misses, and vulnerabilities.

Conclusion

This issue was not an obvious failure that caused the application to stop working.

The application looked normal.

But a security weakness still existed underneath the visible behavior.

In personal development, it is easy to focus mainly on adding features.

However, I think systems become stronger when we repeatedly find and fix:

Small bugs
Small design weaknesses
Small near misses

and preserve those lessons in tests.

After this article was published, a reader suggested using innerText.

I did not adopt the suggestion immediately.

Instead, I replaced the implementation in my own environment and verified:

HTML-like strings were still not interpreted as HTML
Line breaks remained visible
Existing rendering behavior did not break
Tests were unaffected

The result was that innerText could preserve the security properties and formatting I needed while making the implementation simpler.

So the createTextNode() and   implementation described earlier in this article is now a historical record of the first fix, not the final implementation.

This experience also reinforced another principle for me:

Whether a suggestion comes from AI or from another developer, I should test it in my own environment before deciding to adopt it.

I plan to continue using Codex not only for code generation, but as part of a wider cycle:

Review → Fix → Verify → Prevent Regression

GitHub:

https://github.com/tosane932/sales_data_app

Qiita:

https://qiita.com/tosane932

Existing DB Works, Empty DB Fails — Repairing a Broken Flask-Migrate History

tosane932 — Sat, 08 Aug 2026 01:01:59 +0000

Introduction

Hello from Japan! 🇯🇵

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

While reviewing the migration history of a sales management application built with Flask and PostgreSQL, I discovered a serious problem.

The application worked normally with my existing local database.

However, with a completely fresh PostgreSQL database, the current migration history could not build the required tables from scratch.

The reason was simple but important:

The first migration did not create the tables. It started by adding a column to a table that was assumed to already exist.

Empty PostgreSQL database
        ↓
Add is_active column to products
        ↓
products does not exist
        ↓
Migration fails

This article records the full process:

How I discovered the problem
How I investigated the migration history
How I added a missing initial migration
How I verified migration from an empty database
How I verified that existing databases would not be damaged
What I paid attention to while implementing and testing the fix

Investigating and Verifying the Problem with Codex

I used Codex in VS Code during this investigation and repair.

However, I did not hand the entire task over to Codex.

Instead, I divided the work into separate stages and restricted what Codex was allowed to do at each stage.

Static investigation
        ↓
Design the repair
        ↓
Implement only the approved migration changes
        ↓
Static verification
        ↓
Dynamic test with an empty database
        ↓
Test against a clone of the existing database
        ↓
Final verification
        ↓
Commit and push

For each stage, I explicitly defined conditions such as:

Do not modify files during the investigation stage
Limit implementation to exactly two migration files
Do not directly migrate the normal local database
Use a different Docker Compose project name for testing
Test the existing database only through a clone created with pg_dump
Do not automatically fix additional problems that are discovered
Allow commit and push only after final verification

I used Codex for:

Inspecting migration history
Showing diffs
Running verification commands
Organizing the results

However, I made the final decisions about:

Which repair strategy to use
Safety conditions around databases
Which resources could be deleted
Whether the migration was safe enough to commit and push

One lesson from this work was that AI coding assistance is not only about generating code.

It is important to define the allowed change scope, prohibited operations, verification conditions, and stopping conditions.

Development Environment

The main stack is:

Python
Flask
Flask-SQLAlchemy
Flask-Migrate
Alembic
PostgreSQL
Docker Compose
pytest

When the Docker container starts, migrations are applied before Gunicorn starts.

Conceptually, the startup process looks like this:

flask db upgrade && gunicorn ...

With this setup, if the migration fails, Gunicorn is never started.

That means the web application itself cannot start.

Discovering the Problem

When I checked migrations/versions/, only one migration file existed.

043c481b4069_add_is_active_to_products.py

This file was the first revision in the migration history.

revision = '043c481b4069'
down_revision = None

However, its upgrade() function did not create the products table.

It only added the is_active column to an already existing products table.

def upgrade():
    with op.batch_alter_table('products', schema=None) as batch_op:
        batch_op.add_column(
            sa.Column(
                'is_active',
                sa.Boolean(),
                server_default=sa.true(),
                nullable=False
            )
        )

In other words, there was no migration that created:

products
daily_sales

I searched the entire project and found no equivalent table-creation logic such as:

op.create_table('products', ...)
op.create_table('daily_sales', ...)
db.create_all()

The migration history was incomplete.

Why Did the Existing Database Still Work?

The existing database had most likely been created through another method before Flask-Migrate was introduced.

The historical sequence was probably something like this:

products and daily_sales created outside Alembic
        ↓
Flask-Migrate introduced
        ↓
is_active added to products

Because the tables already existed, the existing database could apply the column-addition migration successfully.

A fresh database was different.

The first migration effectively tried to execute something like:

ALTER TABLE products
ADD COLUMN is_active BOOLEAN DEFAULT true NOT NULL;

But an empty database had no products table.

The failure therefore looked approximately like this:

products does not exist
        ↓
First migration fails
        ↓
flask db upgrade exits with a non-zero status
        ↓
Gunicorn does not start
        ↓
The web application does not start

That was the key issue:

The application worked because the existing database already contained history that Alembic itself could not reproduce.

Current Models

The application currently contains two models.

Product

class Product(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    year = db.Column(db.Integer, nullable=False)
    month = db.Column(db.Integer, nullable=False)
    name = db.Column(db.String(100), nullable=False)
    price = db.Column(db.Integer, nullable=False)
    is_active = db.Column(
        db.Boolean,
        nullable=False,
        server_default=db.true()
    )

DailySales

class DailySales(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    product_id = db.Column(
        db.Integer,
        db.ForeignKey('products.id'),
        nullable=False
    )
    date = db.Column(db.Date, nullable=False)
    quantity = db.Column(
        db.Integer,
        nullable=False,
        default=0
    )

Repair Strategies I Considered

I considered several approaches.

1. Add an Initial Migration Before the Existing Revision

Create initial tables
        ↓
Add is_active

This preserves the existing history while adding the missing foundation.

It creates a natural migration chain.

2. Rewrite the Existing First Migration

Another option would be to change the existing revision so that it creates all current tables directly.

However, that would significantly change the meaning of a migration that had already been applied.

That would make the historical record less trustworthy.

3. Create a Conditional Migration

Another possibility would be:

If products does not exist:
    create it
else:
    add the column

This could support multiple database states.

However, it would introduce more state-dependent behavior and make verification more complicated.

4. Use `db.create_all()` and `stamp`

Another approach would be to create tables directly from the models and then use Alembic only to mark the database as being at a particular revision.

This could solve the immediate problem quickly.

However, it could create divergence between:

The models
The actual database
The migration history

I therefore did not use this approach.

The Approach I Chose

I chose to insert an initial migration at the beginning of the history.

The repaired migration chain became:

base
        ↓
b7e2c4a91f30 create products and daily_sales
        ↓
043c481b4069 add is_active to products
        ↓
head

This allowed the migration history to tell the actual story:

Create the initial tables
Add is_active later

Implementing the Initial Migration

I added:

migrations/versions/b7e2c4a91f30_create_initial_tables.py

The migration looked like this:

"""create initial products and daily_sales tables

Revision ID: b7e2c4a91f30
Revises:
Create Date: 2026-08-06
"""

from alembic import op
import sqlalchemy as sa


revision = 'b7e2c4a91f30'
down_revision = None
branch_labels = None
depends_on = None


def upgrade():
    op.create_table(
        'products',
        sa.Column(
            'id',
            sa.Integer(),
            nullable=False
        ),
        sa.Column(
            'year',
            sa.Integer(),
            nullable=False
        ),
        sa.Column(
            'month',
            sa.Integer(),
            nullable=False
        ),
        sa.Column(
            'name',
            sa.String(length=100),
            nullable=False
        ),
        sa.Column(
            'price',
            sa.Integer(),
            nullable=False
        ),
        sa.PrimaryKeyConstraint('id')
    )

    op.create_table(
        'daily_sales',
        sa.Column(
            'id',
            sa.Integer(),
            nullable=False
        ),
        sa.Column(
            'product_id',
            sa.Integer(),
            nullable=False
        ),
        sa.Column(
            'date',
            sa.Date(),
            nullable=False
        ),
        sa.Column(
            'quantity',
            sa.Integer(),
            nullable=False
        ),
        sa.ForeignKeyConstraint(
            ['product_id'],
            ['products.id']
        ),
        sa.PrimaryKeyConstraint('id')
    )


def downgrade():
    op.drop_table('daily_sales')
    op.drop_table('products')

Table Creation Order Matters

daily_sales.product_id references products.id.

Therefore, the upgrade must create the tables in this order:

1. products
2. daily_sales

The downgrade must remove them in reverse order:

1. daily_sales
2. products

If products were dropped first, the foreign key from daily_sales could prevent the operation.

This is a small detail, but an important one when creating migration history manually.

Why I Did Not Put `is_active` in the Initial Migration

I deliberately did not create is_active in the initial migration.

The history remained:

Base revision
└── Create products without is_active
        ↓
Existing revision
└── Add is_active

This preserved the historical meaning of the existing revision.

The initial migration alone does not need to match the current model.

What matters is:

Applying every revision through head should produce the current model structure.

That was the goal.

Changing the Existing Revision

In the existing 043c481b4069 migration, I changed only its revision relationship.

Before:

revision = '043c481b4069'
down_revision = None

After:

revision = '043c481b4069'
down_revision = 'b7e2c4a91f30'

I also updated the Revises field in its docstring.

-Revises:
+Revises: b7e2c4a91f30

I did not change the actual upgrade() or downgrade() logic.

The original operation remained intact.

Why I Did Not Add a `server_default` to `quantity`

The DailySales.quantity model has:

quantity = db.Column(
    db.Integer,
    nullable=False,
    default=0
)

The important detail is:

default=0

This is a Python-side SQLAlchemy default.

It is not a database-side default.

A database default would instead be represented using something like:

server_default='0'

I intentionally did not add that to the migration.

Doing so would have introduced a database-level behavior that the current model did not define.

I wanted the migration to reproduce the actual intended schema, not silently add extra behavior.

Static Verification Before Touching a Database

Before starting PostgreSQL, I checked the syntax of both migration files.

PYTHONPYCACHEPREFIX=/tmp/sales_data_app_pycompile \
python -m py_compile \
migrations/versions/b7e2c4a91f30_create_initial_tables.py \
migrations/versions/043c481b4069_add_is_active_to_products.py

The result was successful.

Exit code: 0
Syntax errors: none

I also confirmed that the revision chain formed a single path:

base
        ↓
b7e2c4a91f30
        ↓
043c481b4069
        ↓
head

Only after these static checks did I move on to database testing.

Testing a Completely Empty Database

I did not want to affect my normal local database.

Instead, I created an isolated environment by changing the Docker Compose project name.

docker compose \
  -p sales_data_app_migration_test \
  up --build -d

Using a different Compose project name creates separate:

Containers
Networks
Volumes

For example:

Normal environment:
sales_data_app_postgres_data

Migration test environment:
sales_data_app_migration_test_postgres_data

The test PostgreSQL database was completely empty.

The startup logs showed the migrations running in the expected order:

Running upgrade  -> b7e2c4a91f30,
create initial products and daily_sales tables

Running upgrade b7e2c4a91f30 -> 043c481b4069,
add is_active to products

Gunicorn then started normally.

Starting gunicorn 26.0.0
Listening at: http://0.0.0.0:5000
Booting worker

The HTTP request also succeeded.

200 text/html; charset=utf-8

This confirmed that the application could now start from a completely empty PostgreSQL database.

Schema Created from the Empty Database

Three tables were created:

alembic_version
products
daily_sales

`products`

Column	Type	NULL	DB Default
`id`	integer	No	ID sequence
`year`	integer	No	None
`month`	integer	No	None
`name`	varchar(100)	No	None
`price`	integer	No	None
`is_active`	boolean	No	`true`

`daily_sales`

Column	Type	NULL	DB Default
`id`	integer	No	ID sequence
`product_id`	integer	No	None
`date`	date	No	None
`quantity`	integer	No	None

The foreign key was also created as expected.

daily_sales.product_id
        ↓
products.id

The delete and update behavior remained:

ON DELETE: NO ACTION
ON UPDATE: NO ACTION

No ON DELETE CASCADE behavior was added because it does not exist in the model.

I also verified that the migration did not introduce:

Extra UNIQUE constraints
CHECK constraints
Model-independent indexes
A database-side default for quantity

The final revision was:

043c481b4069

Verifying an Existing Database

The next question was more dangerous:

What happens to a database that has already reached 043c481b4069?

I needed to confirm that the newly inserted ancestor migration would not suddenly run against the existing database.

I did not run this experiment directly against the real local database.

Instead, I used the following process:

Start the normal database in read-only mode
        ↓
Create a pg_dump
        ↓
Stop the normal database
        ↓
Restore the dump into a separate PostgreSQL environment
        ↓
Run flask db upgrade only against the cloned database

The normal web container was not started during the dump process.

I also used a read-only PostgreSQL setting:

PGOPTIONS=-c default_transaction_read_only=on

The idea was simple:

Test the migration against data that behaves like the real database, without using the real database.

Running `upgrade` Against the Cloned Database

The cloned database initially contained:

alembic_version = 043c481b4069
products = 16 rows
daily_sales = 16 rows

I ran the migration only against this cloned database.

docker compose \
  -p sales_data_app_existing_migration_test \
  run --rm --no-deps --build \
  web flask db upgrade

The command completed successfully.

Exit code: 0

Importantly, the logs did not contain:

Running upgrade -> b7e2c4a91f30
create initial products and daily_sales tables

There were also no:

CREATE TABLE

operations and no duplicate-table errors.

Alembic correctly treated the database as already being at head.

The newly inserted ancestor revision was not executed.

Comparing the Database Before and After `upgrade`

I compared the cloned database before and after running flask db upgrade.

The comparison included:

alembic_version
Table list
Column names
Data types
NULL constraints
Database defaults
Primary keys
Foreign keys
UNIQUE constraints
CHECK constraints
Indexes
Sequence states
Every row in products
Every row in daily_sales

I exported all rows as CSV ordered by primary key and compared them using both:

cmp

and SHA-256 hashes.

Everything matched exactly.

Comparison	Before	After
Revision	`043c481b4069`	`043c481b4069`
`products`	16 rows	16 rows
`daily_sales`	16 rows	16 rows
Schema	Same	Same
Constraints	Same	Same
Indexes	Same	Same
Sequences	Same	Same
All data	Same	Same

This confirmed both migration paths:

Empty DB:
Base revision runs

Existing DB:
Base revision does not run again

That was the result I needed.

Running pytest

Finally, I ran the existing test suite.

PYTHONDONTWRITEBYTECODE=1 \
pytest -p no:cacheprovider

Result:

3 passed in 0.06s

The existing tests also continued to pass after the migration repair.

Git Diff

Only two migration-related files changed.

migrations/versions/
├── b7e2c4a91f30_create_initial_tables.py
└── 043c481b4069_add_is_active_to_products.py

The final commit was:

9a4422e fix: add initial database migration

What I Learned

1. “The Existing Database Works” Does Not Mean the Migration History Is Correct

If an existing database already contains the necessary tables, an application can continue working even with incomplete migration history.

But a new:

Development environment
Test environment
Machine
Deployment target

may need to start from an empty database.

That means:

Existing environment starts successfully
≠
Migration history is correct

This was the biggest lesson from the incident.

2. Migrations Are About the Path, Not Only the Current Schema

Even when:

Current model
=
Current database schema

that alone is not enough.

The following path must also work:

Empty database
        ↓
Apply every revision in order
        ↓
Reach the current schema

A migration system is not only a description of the final schema.

It is also the reproducible path used to reach that schema.

3. Modifying an Applied Revision Requires Careful Verification

In this repair, I changed the down_revision of an already applied migration.

The migration graph was logically valid after the change.

However, that was not enough evidence for me.

I wanted to know how Alembic would treat a real existing database.

That is why I tested it against a cloned database created from pg_dump, instead of experimenting directly on the normal database.

4. Test Both Empty and Existing Databases

For migration repairs, I now think at least two paths should be tested:

1. Empty DB → upgrade head

2. Already migrated DB → upgrade head

Testing only one of these paths could leave either new environments or existing environments broken.

5. Even a Separate Compose Project Name Makes Testing Much Safer

By changing the Docker Compose project name, I could create containers, networks, and volumes that were separate from my normal environment.

For example:

docker compose -p migration_test ...

This made it much easier to test destructive or risky migration scenarios without touching the normal development environment.

However, using a different project name alone is not enough.

Before running anything, I still think it is important to verify the actual:

volume names
container names
database connection targets

Isolation is useful, but only if I confirm that the environment is really the one I intended to use.

6. Tell AI When to Stop, Not Just What to Change

When giving instructions to Codex, I explicitly included conditions such as:

This stage is investigation only
Do not start the database yet
Do not automatically fix additional problems you discover
Do not write to the normal database
Before deleting anything, show me the target and wait for approval
Commit and push only after final verification

When using AI for development work, I found that specifying:

what it should do

is only part of the instruction.

It is also important to specify:

where it must stop.

That makes unintended changes easier to prevent.

Summary

The root problem was that the first migration did not create the initial tables.

Instead, it assumed that products already existed and immediately tried to add a column to it.

Before the repair:

Empty database
        ↓
Add is_active to products
        ↓
products does not exist
        ↓
Migration fails

After the repair:

Empty database
        ↓
Create products
        ↓
Create daily_sales
        ↓
Add is_active to products
        ↓
Application starts

I also verified the other direction using a cloned version of the existing database.

The newly inserted base revision was not executed again, and the schema and data remained unchanged.

The biggest lesson for me was that migrations should not only be checked by asking:

"Does the application work with the database I already have?"

I also need to ask:

"Can I start with an empty database and reach the current state using only the migration history?"

That was the missing piece in this migration history — and repairing it made the database setup reproducible again.

🔨 XSS Fixed in 47 Seconds? Using VS Code Codex as a Veteran Inspector from Another Department

tosane932 — Tue, 04 Aug 2026 02:25:56 +0000

Introduction

Hello from Japan! 🇯🇵

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

My name is tosane932. I work as a professional truck driver in logistics while teaching myself Python and web application development.

I began studying on May 12, 2026, and my total learning time has now reached 152 hours.

This time, I used Codex in VS Code to perform a static review of a Flask application I am developing.

The target was sales_data_app, a sales management application for bakery stores.

It currently supports:

Product registration
Daily sales quantity entry
Sales rankings
Chart.js visualizations
Business advice through the Gemini API
PostgreSQL data management
Docker-based runtime environments
Testing with pytest and GitHub Actions

I had already reviewed the code myself several times.

This time, however, I asked Codex to inspect the entire project and identify potential improvements.

My conclusion was that Codex is extremely fast and highly capable at investigation.

At the same time, asking it to fix every reported issue at once could damage existing features or the database.

To me, Codex felt like:

An experienced veteran employee transferred from another department who does not yet understand how this particular workplace operates.

Its inspection ability is excellent.

However, a human who understands the actual workplace still needs to decide what is a defect and what is intentional behavior.

Installing Codex in VS Code

I installed the official Codex extension in VS Code.

Codex can inspect files in the currently opened project and help with tasks such as:

Explaining code
Investigating problems
Editing files
Running tests
Executing terminal commands

However, my first instruction prevented the investigation from moving forward.

Please inspect the structure of this project.

Do not modify files or run terminal commands.

Codex responded that it had no way to inspect the file list or file contents without using terminal commands.

I therefore revised the instruction to allow read-only commands while still prohibiting changes.

You may run read-only terminal commands required
to inspect the project structure.

However, the following actions are prohibited:

- Creating, modifying, or deleting files
- Installing packages
- Modifying the database
- Performing Git write operations
- Starting the application

After this change, Codex could inspect the entire project in read-only mode.

Rules I Set for Codex

Giving Codex unrestricted access felt dangerous, so I added explicit working rules to the custom instructions.

Working rules:

- Do not begin making changes without my explicit permission.
- First explain the facts you can confirm, possible causes,
  proposed fixes, affected areas, and required tests.
- Separate investigation from implementation.
  In principle, handle only one issue at a time.
- Do not mix unrelated changes into the same task.
- Do not run Git commit, push, reset, or rebase
  without explicit permission.
- Confirm with me before creating or deleting databases,
  running migrations, or modifying data.
- Confirm with me before deleting files,
  updating dependencies, or performing actions
  that may affect production.
- Read-only investigation commands are allowed.
- After making changes, report the modified files,
  the reason for each change, the affected areas,
  and the verification results.

I also configured Codex to request approval before performing operations instead of giving it full access.

Custom instructions describe expected behavior, but they are not a hard access-control system.

For that reason, I used both configuration restrictions and written instructions.

Performing a Static Review of the Entire Project

I asked Codex to review the whole project from several perspectives.

Inspect this entire project in read-only mode.

Report improvement candidates from the following perspectives:

- Obvious defects
- Security
- Maintainability
- Missing tests
- Unnecessary files or obsolete processing
- Differences between the README and implementation

Conditions:

- Do not create, modify, or delete files
- Do not modify the database
- Do not perform Git write operations
- Do not start the application or run tests yet
- Separate confirmed facts from assumptions
- Classify severity as high, medium, or low
- For each item, provide the relevant filename
  and location as evidence

Codex reported a total of 18 improvement candidates.

The main findings were as follows.

High Severity

Migrations that might fail when building from an empty database
Stored XSS in the dynamically rendered ranking section
Public data modification without authentication
Missing validation between sale dates, product dates, and sales status
File permissions for .env

Medium Severity

Repeated Gemini API execution without authentication
Invalid input being treated as a server error or even a successful request
No database-level prevention of duplicate sales for the same product and date
Insufficient tests for important behavior
Sales aggregation based only on product names
Unpinned Chart.js version
Containers running as the root user
Development dependencies included in production

Low Severity

Year selection fixed to 2026
Images and videos no longer referenced from the README
Local build artifacts and outdated documents
Placeholder URLs remaining in JSON-LD
Unclear relationship between CI and Render deployment conditions

Codex organized the findings with:

Filenames
Relevant lines
Confirmed facts
Possible effects
Suggested improvements

The number of findings surprised me.

However, asking Codex to fix all 18 issues at once would have been risky.

Do Not Accept AI Findings Without Verification

In the first review, Codex classified the .env file permissions as high severity.

I asked it to investigate the issue again.

Codex then checked additional facts:

/home/tosane had 0750 permissions
.env was excluded from Git
.env was not included in the Docker image
Render did not directly use the local .env file
The additional risk was small in a single-user environment

After this investigation, Codex revised its own severity rating from high to low or medium.

This demonstrated why the first AI response should not automatically be accepted.

Even when an AI answer is detailed and confidently written, it may still be wrong or incomplete.

I used the following process:

Perform a static review of the entire project
Recheck only the high-severity findings
Separate reproducibility conditions from current practical impact
Confirm the required specification before making changes
Select only one issue as the first fix

Choosing Stored XSS as the First Fix

Of the 18 findings, I selected stored XSS in the dashboard’s dynamic ranking display as the first issue to fix.

I chose it because:

The change could be limited to one file
It did not affect the database or authentication design
Normal product-name display could be preserved
The security benefit was clear
Manual verification would be straightforward

The problematic code inserted a product name retrieved from an API directly into an HTML string.

<div class="prod-name">${item[0]}</div>

The generated HTML string was later assigned to innerHTML.

rankContainer.innerHTML = htmlContent;

item[0] contained a product name entered by a user.

If the name contained HTML or event attributes, the browser could interpret it as markup instead of plain text.

Jinja2 auto-escaping protected the initial server-rendered page.

However, after data was fetched through the Fetch API, the ranking was updated in JavaScript using innerHTML.

That created a separate stored XSS risk.

Asking Codex for the Design Before Allowing Changes

I did not immediately allow Codex to edit the file.

First, I asked it to explain the proposed design.

Of the high-severity findings,
handle only the stored XSS issue
in the product-name ranking display.

Do not modify any files yet.

Explain the following:

1. The exact code location that requires modification
2. How to preserve the same display without using innerHTML
3. The files that would need to change
4. The impact on the existing ranking display
5. The expected behavior when product names contain
   Japanese text, symbols, or HTML-like strings
6. The tests required after the change

Do not handle the AI response display,
CSP, authentication, database,
or any other improvement in this task.

Codex proposed the following approach:

Stop using innerHTML
Create elements with document.createElement()
Assign product names through textContent
Use a DocumentFragment to append multiple rows efficiently
Preserve the existing CSS classes and DOM structure
Generate the empty-data message through the DOM API as well

It explained that the change could be completed entirely inside:

templates/dashboard.html

Allowing Codex to Implement the Fix

After reviewing the scope, I gave Codex permission to implement only that fix.

Implement only the stored XSS protection
for the dynamically generated product ranking.

Conditions:

- Modify only templates/dashboard.html
- Change only the ranking-update logic
- Do not touch the AI response, Chart.js,
  API, CSS, backend, or database
- Preserve existing class names and DOM structure
- Stop generating the ranking with innerHTML
- Use DOM APIs and textContent
- Do not run Git commit or push
- Do not run tests or start the application yet

After making the change, report:

1. The exact range changed
2. The difference before and after
3. Confirmation that no out-of-scope changes were made

Codex completed the change in approximately 47 seconds.

The Updated Code

The product name was assigned through textContent.

const productName = document.createElement('div');
productName.className = 'prod-name';
productName.textContent = item[0];

textContent does not interpret the value as HTML.

For example, even if the following product name is stored:

<b>Melon Bread</b>

the browser displays it as text instead of creating a bold b element.

Each ranking element is now created with the DOM API.

const rankingItem = document.createElement('div');
rankingItem.className = 'ranking-item';

const rankBadge = document.createElement('div');
rankBadge.className = 'rank-badge';

if (rank <= 3) {
    rankBadge.classList.add(`rank-${rank}`);
}

rankBadge.textContent = String(rank);

The quantity and unit are also created as separate elements instead of HTML strings.

const productQuantity = document.createElement('div');
productQuantity.className = 'prod-qty';
productQuantity.append(
    document.createTextNode(String(item[1]))
);

const productUnit = document.createElement('span');
productUnit.className = 'prod-unit';
productUnit.textContent = 'items';

productQuantity.append(productUnit);

Finally, the generated elements are appended together.

rankingItem.append(
    rankBadge,
    productName,
    productQuantity
);

fragment.append(rankingItem);

The previous ranking content is removed with:

rankContainer.replaceChildren();

Because the existing CSS classes and DOM structure were preserved, the display logic became safer without changing the appearance.

Manual Verification

After the fix, I registered test products, entered sales quantities, and verified the results.

The local environment and Render production environment use separate databases.

For that reason, I registered the same test products again in Render and checked the behavior from my smartphone.

Normal Product Name

Melon Bread

It displayed normally.

Product Name Containing an HTML Tag

<b>Melon Bread</b>

It was not displayed in bold.

The browser treated it as plain text rather than HTML.

Product Name Containing Japanese Text and Symbols

あんぱん <限定> & コーヒー

The characters <, >, and & displayed correctly.

I found no problems with:

Product registration
Sales quantity entry
Dynamic ranking updates
Dashboard charts

Final Check on the Render Demo from a Smartphone

After deployment, I operated the public Render demo from my smartphone.

I registered:

メロンパン
あんぱん <限定> & コーヒー

I then entered daily sales quantities and confirmed that the products appeared correctly in both the ranking and chart.

The HTML tags were not executed or rendered as formatting.

The special characters <, >, and & were not lost.

The existing sales-entry workflow, ranking, and charts also continued to work normally.

Reviewing the Diff

Codex reported that only templates/dashboard.html had changed.

I also reviewed the change manually with git diff.

git diff -- templates/dashboard.html

The git diff output was displayed through the less pager.

To exit the pager, press:

q = quit

This does not close the terminal.

It only closes the diff viewer and returns to the normal command prompt.

Can Codex Be Used in Real Development?

In this case, Codex did much more than generate code.

It handled:

Inspection of the entire project structure
Comparison between the README and implementation
Identification of security risks
Identification of the relevant files and lines
Separation of confirmed facts from assumptions
Clarification of reproduction conditions
Analysis of the impact on existing features
Suggestions for required tests
A narrowly scoped implementation
Reporting of the final changes

The ability to perform all of this quickly felt extremely powerful.

A few years ago, a person would have needed to open files one by one, record the relevant line numbers, and prepare a report manually.

Static-analysis tools and linters have existed for a long time.

However, explaining a problem in Japanese while considering project context, likely impact, and possible fixes would still have required substantial human effort.

That said, asking Codex to fix all 18 findings in one operation would have been dangerous.

Some findings required business decisions before any code change.

Examples included:

Whether edits to historical sales should be allowed
Whether discontinued products should allow corrections to past records
Whether anyone should be able to operate the public demo
Whether authentication should be mandatory
How to repair migrations for an empty database
How migration changes could affect the existing Render database

A technically reasonable change could still break the purpose of the application.

A Veteran Inspector from Another Department

The best logistics analogy I found was:

Codex is an experienced veteran employee transferred from another department who does not yet understand this workplace.

It can quickly identify dangerous equipment, questionable procedures, and suspicious code.

Its reports are fast and detailed.

However, it does not automatically know:

Why the current workflow exists
Which requirements have priority
Which exceptions are necessary
What the public demo is intended to allow
Which existing behaviors must be preserved

A human reviewing the findings still needs to decide:

Is this truly a defect?
Is it an intentional specification?
Is it a future improvement?
Does it need to be fixed immediately?
What scope can be changed safely?
Could the fix break another workflow?

Before allowing Codex to modify the code, I followed this process:

Inspect the whole project in read-only mode
Recheck the high-severity findings
Correct the severity of an overestimated item
Limit the first fix to one stored XSS issue
Review the design and impact before implementation
Limit the change to one file
Allow Codex to implement the fix
Review the diff with git diff
Register the test products locally and verify sales entry and dashboard display
Commit the change and push it to GitHub
Confirm completion of the Render deployment
Register the same products again in Render because its database is separate
Verify sales entry, rankings, and charts from a smartphone
Confirm that both the local and production environments behaved normally

Codex changed the code in approximately 47 seconds.

However, making those 47 seconds safe required a human to read the findings, understand the specification, and define the allowed scope.

What AI Can Handle and What Humans Must Decide

I organized the responsibilities as follows.

Tasks That Are Easy to Delegate to Codex

Inspecting files in the project
Identifying possible code issues
Showing evidence and relevant locations
Comparing possible fixes
Making narrowly scoped code changes
Reporting what changed
Suggesting test cases

Decisions That Still Belong to Humans

The purpose of the application
Business rules
Priorities
Acceptable risk
Public demo operation policies
Database and production-change decisions
Which findings to adopt or postpone
Final verification and release decisions

Codex works quickly when instructions are clear.

However, giving it full access with vague instructions such as:

Fix everything.

could lead to a serious incident.

The faster the worker, the more clearly the work area and stopping conditions must be defined.

Conclusion

I used Codex in VS Code to perform a static review of a Flask application and fix one stored XSS issue.

The final implementation changed only the dynamic ranking-generation logic in:

templates/dashboard.html

The fix included:

Removing ranking generation through innerHTML
Creating DOM elements with createElement()
Assigning product names through textContent
Preserving the existing CSS classes and DOM structure
Manually testing Japanese text, symbols, and HTML-like product names
Confirming that normal product registration, sales entry, and ranking display still worked

Codex demonstrated investigation and implementation speed that could be highly useful in real development.

However, fixing every issue identified by AI is not automatically the correct decision.

The important process is:

Investigate

Recheck

Define the specification

Limit the change

Review the diff

Verify the actual behavior

Codex is an extremely fast and capable worker.

However, the decision about what to fix, what not to fix, and when the application is ready to ship still belongs to the human developer.

My total learning time has now reached 152 hours.

I will continue using AI without handing over the entire project blindly.

My goal is to remain able to explain the cause, affected area, and reason for each change in my own words.

DEV Community: tosane932

Can You Explain the System AI Built for You? — I Audited My Own Web App with 20 Questions

Hello from Japan 🇯🇵

The Web Application I Audited

I Defined Three Levels of Understanding

The 20 Questions

I Could Explain the User-Facing Purpose Relatively Well

I Thought Sales Data Went "Through GitHub" to the Database

I Also Thought Docker Sent My Finished Local Container Directly to Render

I Knew the Name SQLAlchemy, but I Couldn't Explain It

I Was Confusing Cookies with Browser Cache

I Was Mixing Up APIs and API Keys

.gitignore Is Not a Feature That Hides Secrets

What Am I Actually Sending to Gemini?

Saying "It Analyzes Current Social Media Trends" Was an Overstatement

Even 91 GREEN pytest Tests Do Not Mean "Safe"

Not Being Able to Explain Something Wasn't Embarrassing — It Showed Me What to Learn Next

Being Able to Explain Something Simply Does Not Mean Complete Understanding

I Don't Need to Avoid Technical Terms

Writing Your Own Manual Can Be a Surprisingly Good Test

This Is Not an Argument Against Using AI

Summary

pytest Improvement Series

📝 Growing pytest into an “Incident Prevention Log” — Stage 5: Questioning GREEN with Mutation Testing

Hello from Japan 🇯🇵

87 GREEN Tests Still Had Five Blind Spots

pytest Improvement Series

In Stage 5, I Started Questioning GREEN

What Is Mutation Testing?

KILLED

SURVIVED

Because I Was Intentionally Breaking Working Code, I Defined Safety Rules First

The 11 Mutations

Six Mutations Were KILLED Immediately

SURVIVED #1: A Safe textContent Check Missed a Dangerous innerHTML

Why Did It Survive?

The Second Wave Focused Only on Failures Existing pytest Might Miss

SURVIVED #8: Removing the year Filter Changed Nothing Because the Fixture Was Too Simple

The Problem Was the Fixture, Not the Production Code

What I Learned Here

SURVIVED #9: Existing XSS Tests Missed Jinja |safe

Why Did It Survive Even Though I Already Had XSS Tests?

SURVIVED #10: A Normal Login Fixture Could Never Produce a Missing-Fingerprint Session

The Problem Was the "Normal Login" Fixture

SURVIVED #11: Removing the date Filter Changed Nothing Because There Was No Same-Product, Different-Date Data

Again, the Fixture Was the Problem

All Four Mutations in the Second Wave Initially SURVIVED

In the End, All 11 Selected Mutations Became Killable

pytest Grew from 87 to 91 Tests

From the Original Three Tests to About 30 Times as Many

Stage 5 Made No Formal Production-Code Changes

Five Things I Learned from Stage 5

1. GREEN Can Still Miss Broken Code

2. Fixtures Need Discriminating Power

3. Assertions Sometimes Need to Check Both "Exists" and "Does Not Exist"

4. Normal Fixtures Cannot Always Represent Abnormal States

5. RED Does Not Automatically Mean the Mutation Was KILLED

The Scariest Part Wasn't Breaking the Code — It Was Accidentally Leaving It Broken

Final Result

This Still Does Not Mean the Application Is Completely Safe

It Feels a Little Like Testing a Truck's Safety Equipment

Not "It's Probably Fine," but "What If?"

Summary

pytest Improvement Series

📝 Growing pytest into an “Incident Prevention Log” — Stage 4: Testing Failure Scenarios (71 ➡ 87 Tests)

Hello from Japan 🇯🇵

pytest Improvement Series

Introduction

Stage 4 Results

1. Can Migrations Build the Database from Nothing?

2. Invalid year / month Should Return 400, Not 500

3. Locking Down Gemini Failure Fallbacks

4. Testing the Authenticated AI Advice Happy Path

5. Fail Closed: Don't Trust an Old Session After Authentication Settings Change

The First Problem I Found

The RED Test Wasn't Following the Path I Thought It Was

My First Fix Was a Roughly 60-Line Hash Parser

Switching to a Fingerprint

6. Can the Application Reject a Tampered CSRF Token?

Login

`.gitignore` Is Not a Feature That Hides Secrets

SURVIVED #1: A Safe `textContent` Check Missed a Dangerous `innerHTML`

SURVIVED #8: Removing the `year` Filter Changed Nothing Because the Fixture Was Too Simple

SURVIVED #9: Existing XSS Tests Missed Jinja `|safe`

SURVIVED #11: Removing the `date` Filter Changed Nothing Because There Was No Same-Product, Different-Date Data

2. Invalid `year` / `month` Should Return 400, Not 500

Adding `CSRFProtect` with Flask-WTF

Protecting Six Routes with `login_required`

`git status` and `git diff` Are Like Checking the Dog's Mouth After a Walk