DEV Community: 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:


text
1. Empty DB → upgrade head

2. Already migrated DB → upgrade head

🔨 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:

<b>メロンパン</b>
あんぱん <限定> & コーヒー

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.

From Two AI Apps to a Vision for “Frontline Support AI”

tosane932 — Mon, 03 Aug 2026 11:11:00 +0000

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 logistics driver while teaching myself web application development, mainly with Python.

I have been building personal projects around problems I have encountered in real workplaces and tools that made me think:

It would be useful if something like this existed.

The two main applications I currently have publicly available are:

A bakery sales management system
Puoppo, a news summarization and analysis application powered by official RSS feeds and AI

At first, I developed these as two completely separate applications.

However, when I placed both services side by side in a single image, I realized that they were actually moving in the same direction.

This article records why I built these two applications and the broader product vision that emerged from connecting them conceptually.

The Two AI Applications I Built

1. Bakery Sales Management System

The bakery sales management system is a web application for recording daily sales data and reviewing sales totals and product rankings.

Its main features include:

Product registration
Daily sales entry
Sales aggregation by product
Sales rankings
Chart-based visualization
AI-generated suggestions for improving sales

Why I Built It

A store produces many numbers every day.

However, recording numbers alone does not automatically lead to better decisions.

Store operators still need to answer questions such as:

Which products are actually selling?
Which products are losing sales?
What should be changed to improve revenue?
Is there room to improve the product lineup or sales method?

I therefore wanted to build more than a simple sales-recording tool.

My goal was:

To transform sales data into practical improvement ideas that can be used in the workplace.

Technology Stack

Python
Flask
PostgreSQL
Gemini API
Docker
pytest
GitHub Actions
Render

I have also introduced testing and CI/CD.

The project is no longer focused only on adding features. I am also building a development environment where I can continue improving the application safely.

2. News Summarization and Analysis App: Puoppo

Puoppo is a web application that retrieves news from official RSS feeds and uses AI to summarize and analyze it.

Its main features include:

Retrieving news from official RSS feeds
Keyword search
News article summarization
Comparison of multiple news stories
Reducing the time required to collect information

Why I Built It

A huge amount of information is published on the internet every day.

However, finding the information you need, reading articles one by one, and organizing their contents takes a great deal of time.

When information gathering must be done alongside work or study, reading every article is not realistic.

I therefore developed Puoppo as:

A system that collects relevant news and helps users understand it in a short amount of time.

Puoppo uses reliable official RSS feeds as its information sources and applies AI to summarize and analyze the retrieved news.

The goal is not merely to display a list of headlines.

It is to reduce the time users need to understand the information and make their own decisions.

What I Realized After Placing the Two Apps Side by Side

I recently placed the bakery sales management system and Puoppo together in a single promotional image.

Seeing them side by side made the role of each application much clearer.

Bakery Sales Management System

An application for understanding data generated inside the store.

It handles internal information such as:

Sales
Products
Rankings
Daily records

Puoppo

An application for understanding information generated outside the store.

It handles external information such as:

News
Industry trends
Regulatory changes
Economic information

The relationship can be summarized like this:

Application	Information Handled	Purpose
Bakery sales management system	Internal store sales data	Understanding the current situation and identifying improvements
Puoppo	External news and publicly available information	Information gathering and situational awareness

Until then, I had thought of them as separate personal projects.

However, they ultimately share the same purpose:

Collect data and information, organize it with AI, and connect it to the next action.

Connecting “Inside the Store” with “Outside the Store”

A store cannot fully understand its situation by looking only at its own sales data.

Many external factors can affect sales.

For a bakery, these could include:

The prices of flour, butter, and other ingredients
Weather
Local events
Seasons and temperature
Food industry trends
New competing stores
Subsidies or regulatory changes
Consumer cost-saving behavior

Combining this external information with a store’s internal sales data could support more specific decisions.

For example, a future system might make suggestions such as:

News about rising wheat prices has increased.

Consider reviewing the pricing of products with high ingredient costs.

Tomorrow has a high chance of rain, so customer traffic may decrease.

Consider adjusting production volume to reduce waste.

A local event is scheduled nearby.

Consider preparing more products that are easy to carry away.

By combining:

Internal business data
External news
Weather and local information
AI-powered analysis

the system could develop beyond simple sales management and begin supporting business decisions.

The SalesInsight AI Concept

The bakery sales management system originally began under the project name sales_data_app.

As development continued, I started to see the possibility of applying its basic structure to more industries than bakeries.

For example:

Izakaya restaurants
Teppanyaki restaurants
Okonomiyaki restaurants
Fish markets
Greengrocers
Retail stores
Restaurants

The core system could potentially be adapted by changing product data, terminology, and screen layouts for each industry.

I am therefore considering the following future project name:

SalesInsight AI

SalesInsight AI would analyze sales data for different types of businesses and provide improvement suggestions that can be acted on in real workplaces.

If it could also connect with Puoppo’s information-gathering features, it might develop into a broader business-support platform that:

Turns workplace numbers and changes in the outside world into the next practical action through AI.

Why I Connect GitHub and Qiita

I publish not only completed applications, but also the development process through GitHub and Qiita.

On GitHub, I record:

Source code
Changes
Commit history
The current state of each project

On Qiita, I record:

Problems encountered during implementation
Investigation steps
Mistakes
Fixes
Lessons learned

Topics I have written about include:

Building Docker environments
Multi-stage Docker builds
Secure API key management
Introducing pytest
CI with GitHub Actions
Near-misses involving Git operations
Removing unnecessary code and dependencies
Investigating errors in the development environment

These details are not visible from completed screenshots alone.

However, during real development, I have found that the following questions are often more important than the finished feature:

Why did the error occur?
How was the cause isolated?
What was changed to prevent the same incident?
Why was a particular technology selected?

I therefore use GitHub as a record of the deliverable and Qiita as a record of the problem-solving process.

Personal Projects Become a Line, Not Just Separate Points

When I first started personal development, I was focused only on completing one application.

However, as I continued learning and building multiple applications, the experiences from each project began to connect.

Through the bakery sales management system, I learned about:

Data entry
Aggregation
Analysis
AI-generated recommendations

Through Puoppo, I learned about:

Retrieving external information
RSS
Search
Summarization
AI analysis

The technologies and ideas learned in one application can be reused in another.

Creating an image that placed the two apps side by side made me realize:

The things I have built were not unrelated products. They were intermediate steps toward the same broader concept.

Each personal project may look like a small, separate point.

But when projects are accumulated with a purpose, those points begin to form a line—and eventually a larger service vision.

This Is Still Only a Concept

SalesInsight AI and its potential integration with Puoppo are currently future ideas.

The two existing applications still operate independently.

I do not want to present features that have not been implemented as if they were already complete.

However, I believe personal development is also about recording:

When an idea emerged and what I was thinking at that point.

The concept may change in the future.

Some parts may prove technically difficult.

Even so, recording the direction I can currently see will allow me to look back later and understand how the project evolved.

What I Want to Work on Next

While continuing to improve the stability of the existing applications, I would like to explore:

Industry-specific interfaces and product management
User authentication
Per-user data management
Saved analysis history
Improved AI recommendations
Integration with external news
Weather and local-information integration
Better smartphone usability
Broader test coverage
Stronger security and operational safeguards

I do not plan to implement everything at once.

I want to proceed through small experiments while avoiding damage to features that already work.

Conclusion

Placing the bakery sales management system and Puoppo together in one image revealed the common purpose behind the two personal projects.

The bakery sales management system handles information from inside the store.

Puoppo handles information from outside the store.

Connecting the two created the following vision:

Analyze internal business numbers together with external information and use AI to support the next practical action in the workplace.

At the moment, they are still two independent applications.

However, by continuing personal development and recording the process on GitHub and Qiita, I hope to gradually turn this concept into something real.

Rather than explaining the completed future only after it exists, I want to record the moment when the idea first emerged.

This article is one part of that development record.

🚛 A Five-Hour Inspection of a Working Flask App — Aligning Code, README, Docker, and Git with the Current Specification

tosane932 — Mon, 03 Aug 2026 03:19:28 +0000

Introduction

Hello from Japan! 🇯🇵

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

I am a working truck driver teaching myself Python.

I try to bring two habits from logistics into web application development:

Do not leave any cargo behind
Inspect everything before shipment

This was Day 69 of my Python-learning journey, with a total of 148 study hours.

This time, I did not add a new feature to my bakery sales management application, sales_data_app.

Instead, I spent five hours on:

Repository cleanup
Full inspection
Removal of obsolete code
Synchronizing documentation and configuration with the current application

The inspection covered much more than application code.

I reviewed:

README files
Screenshots
Demo videos
Thumbnails
.gitignore
.dockerignore
Old Excel-related logic
Unused imports
Unnecessary dependencies
Unused CSS
Outdated UI text
Git conflict markers
pytest
Synchronization with GitHub

The application itself was working.

However, a working application and a repository that accurately represents the current specification are not the same thing.

As development continues, small leftovers begin to accumulate:

Old specifications
Outdated images
Obsolete settings
Unused CSS
Dependencies that no longer have a purpose

In logistics terms, the delivery may have been completed successfully, while old materials and paperwork are still sitting in the corner of the truck.

Before loading the next shipment, I decided to inspect and clear the entire cargo area.

Main idea

This article is not only about organizing files.

A working application does not necessarily mean that the code, dependencies, UI, documentation, Docker configuration, and Git state all match the current specification.

What This Article Covers

This article explains:

How to inspect an entire Flask repository
A safe order for reviewing deletion candidates
The difference between .gitignore and .dockerignore
How to find unused imports, dependencies, and CSS
How to interpret an empty grep result
Why searching for Git conflict markers produced false positives
How to decide whether changes belong in one commit
Why I checked both pytest and working tree clean
A reusable repository inspection checklist

This is a long article.

You can read it from beginning to end, jump directly to a section that interests you, or use the repository inspection checklist near the end during your own maintenance work.

The Application

sales_data_app is a web application that combines:

Bakery product registration
Daily sales entry
Sales analysis
Business advice generated through the Gemini API

The main technology stack is:

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

The application supports the following workflow:

Register products and prices
        ↓
Enter or update today's sales quantities
        ↓
Review sales rankings and charts
        ↓
Receive business advice from Gemini

Thinking About the Inspection in Five Layers

Looking back, the inspection targets could be divided into five layers.

1. Runtime code
   Unused imports, obsolete logic, old settings, unused CSS

2. Dependencies
   Libraries that remained in requirements.txt unnecessarily

3. UI and documentation
   HTML wording, README files, screenshots, and demo assets

4. Delivery scope
   .gitignore and .dockerignore

5. Shipment status
   pytest, git diff, git status, and GitHub synchronization

Dividing the repository into these five layers made the inspection scope much clearer than simply searching for “unnecessary files.”

A repository inspection is not only about deleting files.

It is the process of aligning the current specification with the code, dependencies, UI, documentation, and delivery state.

Work Completed During the Five Hours

The main tasks were:

Organize demo videos, thumbnails, and screenshots
Correct image paths in the README
Capture screenshots of the current UI
Clean up .gitignore
Clean up .dockerignore
Remove obsolete Excel-related logic
Remove unused imports
Remove the openpyxl dependency
Remove unused CSS
Correct outdated page titles
Search for remnants of old specifications
Search for Git conflict markers
Commit changes by purpose
Run pytest
Verify synchronization with GitHub
Record the work in two README files

The following sections describe the actual order of work.

1. Organizing Images and Videos by Purpose

I started by organizing the demo assets in the repository.

The final structure became:

sales_data_app/
├── demo_mp4/
├── demo_thumbnail/
├── screenshot/
├── static/
├── templates/
└── README.md

Each directory now has a clear responsibility.

demo_mp4/
└── Demo videos

demo_thumbnail/
└── YouTube thumbnails

screenshot/
└── Screenshots used in the README

Previously, videos and images were mixed across the repository root and other locations.

This did not directly affect application behavior.

However, it made it difficult for another person to answer questions such as:

What is this image used for?
Is this file still required?
Is it necessary to run the application?
Is it documentation material?

Organizing files by both directory name and location makes their purpose easier to understand without opening them.

Folder organization is not merely cosmetic.

It is part of the information architecture of the repository.

Update README Paths After Moving Files

When files move into new directories, the README references must also change.

For example, after moving a screenshot into the screenshot directory:

![Product master registration screen](screenshot/screen01.jpg)

If the files are moved but the README paths are not updated, the images stop appearing on GitHub.

I checked the work in this order:

Move the files
        ↓
Update the README paths
        ↓
Confirm that the images display on GitHub

2. Aligning the README with the Current UI

Some screenshots in the README no longer matched the application’s current screens.

I started the latest version of the application and captured new screenshots.

The main updated screens were:

Product master registration
Menu registration completion
Daily sales entry
Sales analysis dashboard

README screenshots are not merely decoration.

When someone opens a repository, they may inspect the screenshots before reading the code.

If the screenshots are outdated, an improved application may still appear to be using its old UI.

An Outdated HTML Title

While reviewing the screens, I also found an outdated title in the HTML.

Before:

<title>Sales Aggregation Complete</title>

However, the current screen represents completed product-menu registration, not sales aggregation.

I changed it to:

<title>Menu Registration Complete</title>

The browser-tab title is also part of the application specification visible to users.

Updating the page heading while leaving the tab title outdated still creates inconsistency.

Even when the application works correctly, outdated wording may mislead users or developers reading the code.

UI wording should be synchronized with the current specification just like application logic.

3. Updating `.gitignore` for the Current Development Environment

Next, I reviewed files that Git does not need to track.

The cleaned-up .gitignore became:

# Python
.venv/
__pycache__/
*.pyc
.pytest_cache/

# Raw operational data and sensitive files
過去売上高/
*.xlsx
*.db
.env

# Other unnecessary files
*.bak
sales_data_app_android_demo.mp4

Do Not Track Virtual Environments or Caches

.venv/
__pycache__/
*.pyc
.pytest_cache/

These files are generated separately in each development environment.

They can be recreated and do not need to be stored on GitHub.

Do Not Track Secrets

.env

An .env file may contain sensitive values such as Gemini API keys.

It should be explicitly excluded to reduce the risk of accidentally pushing secrets to a public repository.

Do Not Track Local Data

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

The production application uses PostgreSQL.

Local SQLite files and old Excel documents do not need to be stored in GitHub.

However, an important point is:

A file looks old
≠
It is safe to delete immediately

I checked local.db and confirmed that the configuration still referenced it.

Therefore, I excluded it from Git tracking but did not immediately delete it from my local environment.

4. Using `.dockerignore` to Decide What Goes into Production

Files that Git should not track and files that Docker should not receive serve different purposes.

I therefore reviewed .dockerignore separately.

# Git
.git
.gitignore

# Environment variables and virtual environments
.env
.venv/
venv/

# Python and test caches
__pycache__/
*.pyc
.pytest_cache/

# Local data and obsolete documents
*.db
*.xlsx
過去売上高/
*.bak

# Development and documentation files
README.md
test_prompts.py
demo_mp4/
demo_thumbnail/
screenshot/

# Operating-system and log files
*.log
.DS_Store

README files, screenshots, demo videos, and tests are valuable on GitHub.

However, they were not required in this production Docker image.

.gitignore and .dockerignore have different responsibilities.

.gitignore: decides what Git does not track

.dockerignore: decides what Docker does not send into the build context

In logistics terms:

Documents worth storing in the warehouse
≠
Cargo that belongs on the delivery truck

GitHub may preserve development history and documentation.

Docker should carry only what the running application needs.

Two Questions for Classifying Files

When deciding where a file belongs, I used two questions:

1. Is this file worth preserving on GitHub?
2. Is this file needed when the production application runs?

File	GitHub	Docker	Reason
Application code	Yes	Yes	Required to run
HTML and CSS	Yes	Yes	Required for the UI
README	Yes	No	Documentation
Screenshots	Yes	No	Used for repository explanation
Demo videos	Yes	No	Not required at runtime
Test files	Yes	No in this image	Used for CI and development
`.env`	No	No	Sensitive information
`.venv`	No	No	Recreated per environment
SQLite database	No	No	Local development data

This approach is more useful than copying ignore files from another project without understanding them.

5. Searching for Leftovers from the Old Excel Implementation

The early version of this application used Excel.

The architecture later moved toward PostgreSQL, but some Excel-era code remained.

I found the following leftovers.

Old Folder Configuration in `config.py`

PAST_FOLDER_NAME = "過去売上高"
PAST_FOLDER = os.path.join(
    BASE_DIR,
    PAST_FOLDER_NAME,
)

Old Directory-Creation Logic in `app.py`

os.makedirs(
    config.PAST_FOLDER,
    exist_ok=True,
)

Unused Imports

from flask import send_file
from google.genai import types

Unnecessary Dependency

openpyxl

The current application stores sales data in PostgreSQL.

It no longer generates or sends Excel files.

After confirming the references, I removed the obsolete code and dependency.

6. Do Not Delete a Candidate Immediately

An important part of the inspection was avoiding immediate deletion when something looked obsolete.

I used the following sequence:

Search for the name
        ↓
Check where it is referenced
        ↓
Compare it with the current specification
        ↓
Delete it
        ↓
Search for the same name again
        ↓
Run tests

For example, finding openpyxl in requirements.txt was not enough reason to remove it immediately.

I first checked whether the code still used it.

I applied the same process to:

Configuration variables
Imports
CSS classes
Old folder names

Deleting code because it “looks old” is risky.

Treat the search before and after deletion as one complete verification process.

7. Final Diff: 23 Lines Removed and 2 Changed

After removing the old Excel logic, unused imports, unnecessary dependencies, unused CSS, and outdated UI text, the diff looked like this:

app.py                 | 10 +---------
config.py              |  4 ----
requirements.txt       |  1 -
static/style.css       |  8 --------
templates/success.html |  2 +-
5 files changed, 2 insertions(+), 23 deletions(-)

I did not add a large amount of new code.

Instead, I removed 23 lines that were no longer needed by the current specification.

Development does not only mean increasing the amount of code.

Reducing unused code with a clear reason is also an improvement.

Less code is not automatically better.

However, removing unnecessary code can:

Make the current behavior easier to understand
Reduce the risk of accidentally reusing obsolete logic
Clarify the purpose of dependencies
Reduce future maintenance targets
Make it easier to keep the README and implementation consistent

8. Finding and Removing Unused CSS

I used grep to check whether CSS remained that was no longer used by HTML or JavaScript.

grep -Rni "ai-loading" \
  templates \
  static \
  app.py

The first search found only the CSS definition:

static/style.css:680:.input-page .ai-loading {

The class was not referenced by the templates or JavaScript.

I removed the entire CSS block.

.input-page .ai-loading {
    /* Unused styles */
}

I then ran the same search again.

grep -Rni "ai-loading" \
  templates \
  static \
  app.py

The result contained zero matches.

No output

I also removed an old .btn-back style that the current dashboard no longer used.

A CSS Definition Does Not Prove That the Class Is Used

A class existing in a stylesheet and a class being used by the application are different things.

Both sides must be checked:

Does the CSS definition exist?
Is it referenced by HTML or JavaScript?

When only the definition remains and there are no references, it becomes a deletion candidate.

However, JavaScript may apply classes dynamically, so checking HTML alone is not sufficient.

9. A Red Cross from `grep` Did Not Mean an Error

When I searched for old wording and class names, the command displayed no matches.

However, my terminal showed a red cross.

At first, I thought the command had failed.

The grep exit statuses mean:

0: A matching line was found
1: No matching line was found
2 or higher: The command itself failed

In this case, exit code 1 meant that the old text was not found.

That was the expected result.

No match
=
No obsolete remainder

Do not judge success or failure only from terminal colors or symbols.

Check what the command’s exit status actually means.

Zero Search Results Are Still a Valid Result

Searches are not only useful for finding problems.

They can also verify that something no longer exists.

After deletion, searching for the same name and receiving zero matches confirms:

The deleted identifier no longer remains

In that case, no output is the result.

You do not need to memorize every command in this article.

What matters is understanding:

What you are searching for

What a match or no match means

How to reuse the command later from your shell history or notes

The regular expression used below can simply be understood as a condition that searches for Git conflict markers one complete line at a time.

10. Searching for Git Conflict Markers Produced Many CSS Matches

I next searched for leftover Git conflict markers.

When Git produces a merge conflict, files may contain:

<<<<<<< HEAD
Current content
=======
Other content
>>>>>>> branch-name

My first search was:

grep -RniE \
  '<<<<<<<|=======|>>>>>>>' \
  --exclude-dir=.git \
  --exclude-dir=.venv \
  --exclude-dir=__pycache__ \
  --exclude-dir=.pytest_cache \
  .

This produced many matches from divider comments in static/style.css.

/* ========================================
Section heading
======================================== */

The search term ======= matched the decorative CSS separator.

Restricting the Search to the Start of a Line Was Still Not Enough

I then restricted the pattern to the beginning of a line.

grep -RniE \
  '^(<<<<<<< |=======|>>>>>>> )' \
  --exclude-dir=.git \
  --exclude-dir=.venv \
  --exclude-dir=__pycache__ \
  --exclude-dir=.pytest_cache \
  .

However, the CSS divider lines also began with equal signs.

They were still detected.

Matching Only the Actual Git Conflict Format

Finally, I restricted the pattern using both the beginning and end of each line.

grep -RniE \
  '^(<<<<<<< .+|=======$|>>>>>>> .+)$' \
  --exclude-dir=.git \
  --exclude-dir=.venv \
  --exclude-dir=__pycache__ \
  --exclude-dir=.pytest_cache \
  .

The result was zero matches.

No output

This confirmed that no Git conflict markers remained.

Many Search Results Do Not Necessarily Mean Many Problems

This experience showed that:

Many search results
≠
Many actual problems

A broad pattern may match normal comments or code.

In this example:

Goal: Find Git conflict markers
        ↓
Search for lines containing =======
        ↓
Normal CSS divider comments also match

Before interpreting the results, ask:

What normal code could also match this search condition?

11. Use Search Commands for Hypothesis Testing

I did not use grep only as a text-finding tool.

I used it to test hypotheses such as:

Old Excel logic may still remain
Unused CSS may still remain
Outdated UI wording may still remain
Git conflict markers may still remain

The process was:

Form a hypothesis
        ↓
Search
        ↓
Inspect the results
        ↓
Decide whether they are real issues or false positives
        ↓
Adjust the code or search condition
        ↓
Search again

The important part is not memorizing the command.

It is knowing what you are trying to verify.

12. Group Related Changes and Commit Before Another Purpose Gets Mixed In

The changes affected five files:

app.py
config.py
requirements.txt
static/style.css
templates/success.html

Although several files changed, all changes served one purpose:

Remove obsolete specifications and unused code

I therefore recorded them in one commit.

git commit -m "Remove old Excel logic and unused code"

Too Many Small Commits and One Huge Commit Are Both Hard to Read

Creating a commit for every changed line can fragment the work unnecessarily.

On the other hand, combining many unrelated changes makes the diff difficult to understand later.

I used the following rule:

Group related changes
Commit before a different purpose becomes mixed in

In logistics terms:

Cargo for the same destination
        ↓
Load it together

Cargo for a different destination
        ↓
Ship before the loads become mixed

Checks Before Committing

Before committing, I ran:

git status
git diff --stat

git status showed which files had changed.

git diff --stat showed the approximate scale of the changes.

app.py                 | 10 +---------
config.py              |  4 ----
requirements.txt       |  1 -
static/style.css       |  8 --------
templates/success.html |  2 +-
5 files changed, 2 insertions(+), 23 deletions(-)

At this stage, I confirmed that no unrelated files had been included.

13. Running the Final Tests

After committing and pushing the changes, I ran pytest.

pytest

The result was:

================================================== test session starts ==================================================
platform linux -- Python 3.12.3, pytest-9.1.1, pluggy-1.6.0
rootdir: /home/tosane/sales_data_app
plugins: anyio-4.13.0
collected 3 items

test_prompts.py ...                                                                                                [100%]

=================================================== 3 passed in 0.20s ===================================================

All three tests passed.

3 passed in 0.20s

However, the current pytest suite consists of only three tests focused mainly on prompt-generation logic.

Therefore:

pytest passed
≠
Every part of the application is guaranteed

I combined the tests with:

Search verification
Diff inspection
UI inspection
Git status checks

Having tests and sufficiently protecting every important behavior are not the same thing.

It is necessary to understand which parts of the application are actually covered.

14. Checking `working tree clean`

Finally, I checked the Git state.

git status

The result was:

On branch main
Your branch is up to date with 'origin/main'.

nothing to commit, working tree clean

This confirmed:

No uncommitted changes remained
The local main branch matched GitHub
No unfinished files remained

Fixing the Code Is Not the End of the Task

For this inspection, I defined completion as:

Modify the code
        ↓
Review the diff
        ↓
Run the tests
        ↓
Commit the changes
        ↓
Push to GitHub
        ↓
Confirm synchronization with GitHub
        ↓
Confirm that no unfinished changes remain

In other words:

The code was fixed
≠
The task is complete

working tree clean is not merely a status message.

It means the next task can begin without mixing in unfinished changes from the previous task.

In logistics terms, the delivery is not finished until the truck returns and the cargo area is checked for anything left behind.

15. Recording the Work in the README Files

After cleaning up the code and repository, I updated two README files:

The sales_data_app README
My GitHub profile README

The `sales_data_app` README

I added the following items to the update history:

Organized demo videos, thumbnails, and screenshots
Updated README image paths
Cleaned up .gitignore
Cleaned up .dockerignore
Removed obsolete Excel processing
Removed unused imports, dependencies, and CSS
Updated an outdated page title
Confirmed that all three pytest tests passed
Confirmed that the Git working tree was clean

My GitHub Profile README

I recorded the repository-wide inspection as a milestone for July 19, 2026.

I also updated my total study time:

143 total hours
        ↓
148 total hours

The README update was recorded with:

git commit -m "Document repository inspection and 148 study hours"

16. Results of the Five-Hour Inspection

The final results were:

Work time              5 hours
Total study time       148 hours
Changed code files     5 files
Diff                   23 lines removed, 2 lines changed
Old specification      0 remaining matches
Git conflict markers   0 matches
pytest                 3 passed
Difference from GitHub None
Final state            working tree clean

No new feature was added.

However, I aligned all of the following with the current specification:

Code
Dependencies
HTML text
CSS
README
Screenshots
Directory structure
.gitignore
.dockerignore
Git state

What I Learned

1. Working Applications Can Still Contain Old Parts

Even if an application runs correctly, code and dependencies from older architectures may remain.

In this project:

Excel-centered architecture
        ↓
Migration to PostgreSQL
        ↓
Old Excel directory settings and openpyxl become unnecessary

When feature development continues without maintenance, old specifications can quietly remain.

The repository should therefore be inspected periodically to confirm that it matches the current architecture.

2. README Files and Images Are Part of the Software

Even when the code is current, outdated README text or screenshots present an outdated version of the project.

In a public repository, all of the following are part of the deliverable:

Code
README
Screenshots
Demo videos
Directory structure
Commit history

The README is not an optional attachment outside the codebase.

It is often the entry point through which users, recruiters, and other developers understand the project.

3. GitHub Contents and Docker Contents Are Different

README files and tests belong on GitHub.

They may not belong in the production Docker image.

GitHub
└── Stores documentation, tests, and development history

Docker
└── Contains the files required to run the application in production

.gitignore and .dockerignore should not automatically contain the same rules.

Each file must be evaluated according to its purpose.

4. Deletion Requires Evidence

Deleting code merely because it appears unused is dangerous.

I used the following sequence:

Search
↓
Inspect references
↓
Compare with the current specification
↓
Delete
↓
Search again
↓
Test

Deletion is not simply the act of reducing code.

It should be performed only after you can explain why the code is no longer required.

5. Zero Search Results Can Be Important

When confirming that obsolete code no longer remains, no matches from grep can be the expected result.

Search intended to find a problem
Matches found → Something needs inspection

Search intended to confirm removal
No matches → Expected state

The meaning of a search result depends on the purpose of the search.

6. Search Conditions Can Produce False Positives

Searching for ======= matched not only Git conflict markers but also CSS separator comments.

Many search results
≠
Many problems

The search condition itself may be too broad.

Do not judge only by the number of matches. Inspect why each result matched.

7. Divide Commits by Purpose, Not File Count

The cleanup changed five files.

However, every change served the same purpose:

Removing obsolete specifications and unused code

I therefore grouped them into one commit.

A useful commit boundary is based less on:

How many files changed?

and more on:

Why were these files changed?

8. `working tree clean` Is the Starting Condition for the Next Task

Starting new work while uncommitted changes remain can mix unrelated purposes:

Current changes
+
Next changes
=
A commit whose purpose is difficult to understand

By confirming working tree clean, the next task can begin with an empty cargo area.

Reusable Repository Inspection Checklist

The following checklist summarizes the process in a form that can be reused in other projects.

Runtime Code

[ ] Are there unused imports?
[ ] Do settings from retired specifications remain?
[ ] Are unused functions or processes still present?
[ ] Do references to old directories or filenames remain?
[ ] Are there unused CSS classes?
[ ] Could any apparently unused class be added dynamically by JavaScript?

Dependencies

[ ] Are unused libraries still listed?
[ ] Did you search the code before removing a dependency?
[ ] Could another library depend on it indirectly?
[ ] Does the application still run after removing it?
[ ] Do the tests still pass?

README and UI

[ ] Does the README match the current implementation?
[ ] Are the screenshots current?
[ ] Do image paths match the current directory structure?
[ ] Does the demo video still match the interface?
[ ] Do outdated UI labels or HTML title values remain?
[ ] Does the README describe any feature that no longer exists?

File Structure

[ ] Are videos, images, and documents organized by purpose?
[ ] Are unnecessary files scattered in the repository root?
[ ] Do backup files remain?
[ ] Can the purpose of a file be understood from its name?

Git

[ ] Does .gitignore exclude sensitive information?
[ ] Are virtual environments and caches excluded?
[ ] Are local databases and raw data excluded?
[ ] Did you review the changed files with git status?
[ ] Did you review the diff with git diff or git diff --stat?
[ ] Are unrelated changes mixed together?
[ ] Do Git conflict markers remain?
[ ] Did you push the changes to GitHub?
[ ] Is the working tree clean?

Docker

[ ] Does .dockerignore exclude .git?
[ ] Is .env excluded from the build context?
[ ] Are virtual environments and caches excluded?
[ ] Are local databases and Excel files excluded?
[ ] Are production-unnecessary README files, images, and videos excluded?
[ ] Have you distinguished files that belong on GitHub from files required by Docker?

Final Verification

[ ] Did you search again for deleted names?
[ ] Did you check whether search results were false positives?
[ ] Did the tests pass?
[ ] Do you understand what the tests cover?
[ ] Did you visually inspect the necessary screens?
[ ] Did you update the README with the work performed?
[ ] Are the local repository and GitHub synchronized?

Conclusion

I did not add a new feature during these five hours.

Even so, I brought the repository to the following state:

The code matches the current specification
Obsolete dependencies have been removed
The README matches the latest UI
The purpose of videos and images is easier to understand
Files stored on GitHub are organized
Unnecessary documentation is not included in the production Docker image
The tests pass
The repository is synchronized with GitHub
The working tree is clean

In logistics, loading cargo is not the only responsibility.

The following also matter:

Is unnecessary cargo mixed in?
Is the loading location easy to understand?
Do the cargo and shipping documents match?
Was anything left behind after delivery?
Can the next trip begin with an empty truck?

Software development was similar:

Add code
        ↓
Unload unnecessary code
        ↓
Align the documentation with the current state
        ↓
Organize the files included in production
        ↓
Run tests
        ↓
Ship to GitHub
        ↓
Confirm working tree clean

The strongest lesson from this inspection was:

A repository-wide inspection is not a task for deleting unnecessary files.

It is the process of aligning the current specification with the code, dependencies, UI, documentation, and delivery state.

Before loading the next feature, inspect the current cargo area.

These five hours became maintenance time that made the next stage of development safer.

“Save” and “Update” Are Not the Same — How a Former Okonomiyaki Chef Removed Confusing UI from a Flask App

tosane932 — Sun, 02 Aug 2026 13:27:53 +0000

Introduction

Hello from Japan! 🇯🇵

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

I have now completed a total of 143 hours of Python study.

About 11 years ago, I worked as a Hiroshima-style okonomiyaki chef.

I performed live cooking and sales demonstrations at major department stores and event spaces across Japan, including Iwate, the Kanto region, Kansai, and Kyushu.

My responsibilities included:

Hiring temporary sales staff
Estimating how many products we could sell
Ordering ingredients without creating excessive waste
Avoiding losses during limited event periods
Thinking about profit and cost from a business-owner’s perspective

I had to move quickly while making decisions about both daily operations and profitability.

You can also find my profile and projects here:

https://github.com/tosane932/tosane932

I am currently developing a bakery sales management application with Flask.

The application mainly supports the following workflow:

Register a monthly product menu
Enter the number of products sold each day
View sales rankings and charts
Receive improvement suggestions through the Gemini API

The features were already working.

However, when I used the application while imagining that I was the manager of a real bakery, I noticed several small but important points of confusion.

This work was not simply a color redesign.

I reviewed the wording, colors, navigation, and input-state displays from the following perspective:

Would a store manager hesitate when looking at this screen?

Could the wording cause an incorrect operation or misunderstanding?

Is the next action naturally clear?

When I worked at department-store events, I cooked products myself, planned sales pitches, explained the products to customers, and completed each sale.

I combined that experience with what I had previously learned about web design.

My goal was to improve the application so that it could be used comfortably by people of different ages and levels of technical experience.

Design goal

The purpose of this improvement was not to make the application more decorative.

The goal was to remove causes of confusion before the store manager encountered them.

The Application

The application I improved is a bakery sales management system built with Flask.

The main technologies are:

Python
Flask
PostgreSQL
SQLAlchemy
HTML
CSS
JavaScript
Gemini API
Docker
Render

The application has four main screens:

Top page
Product-registration completion page
Daily sales-entry page
Sales analysis dashboard

Problems I Noticed Before the Improvement

1. The Top Page Displayed a Future Year

The top page is used to register the product menu for a particular month.

However, when I opened the year-selection menu in 2026, it also displayed 2027.

2027
2026

At first, I wondered whether I had accidentally registered products for 2027 in the database.

After checking the application, I discovered that the database was not the cause.

The year 2027 had been written directly into the HTML as an option.

The product-menu registration page is mainly used to create the current operating year’s menu.

Therefore, I changed the top page so that only the current year can be selected.

The sales dashboard has a different purpose.

It is used to review previous results, so I changed it to display the current year and past years only.

{% for y in range(current_year, current_year - 4, -1) %}

In 2026, the options are:

Future sales results do not exist yet, so there is no reason to show future years on the analysis screen.

This was a small inconsistency, but it could make a user wonder:

What will happen if I select 2027?

Instead of waiting for an error to occur, I removed the contradiction before the user had to think about it.

2. The Button Said “Save” Even Though the Operation Was an Update

On the daily sales-entry page, the user enters how many units of each product were sold that day.

The backend was already implemented like this:

existing = DailySales.query.filter_by(
    product_id=int(product_id),
    date=sale_date,
).first()

if existing:
    existing.quantity = qty_int
else:
    sale = DailySales(
        product_id=int(product_id),
        date=sale_date,
        quantity=qty_int,
    )
    db.session.add(sale)

When data for the same product and date already exists, the registered quantity is replaced with the new value.

For example, when 14 units are already registered and the user enters 17, the result is:

14 → 17

It is not:

14 + 17 = 31

Important

Daily sales entry does not use an additive model.

When 14 units are already registered and the user enters 17, the stored value changes from 14 to 17.

However, the original button text was:

💾 Save

That wording did not tell the user whether the operation would:

Add a new quantity
Replace the current quantity with the entered value

Even when the backend behavior is correct, a mismatch between the actual operation and the UI wording can cause misunderstanding.

I changed the button to:

<button type="submit" class="btn-submit">
    💾 Update Today's Sales Quantities
</button>

I also changed the success message.

{% if success %}
    <div class="success-msg">
        ✅ Today's sales quantities were updated!
    </div>
{% endif %}

By using the word “update” instead of “save,” the result of the operation became clearer.

UI warning

Correct internal processing is not enough.

When the visible wording does not match the actual behavior, users may misunderstand the operation.

I replaced “Save” with “Update Today's Sales Quantities” to make the overwrite behavior explicit.

3. Users Could Forget Whether They Had Already Entered Today’s Sales

The daily sales-entry page can be opened multiple times in one day.

A store may not enter all data only once after closing.

For example, the workflow might be:

Enter an interim quantity of 14
Sell more products later
Open the entry page again
Correct the final quantity to 17

Before the improvement, the page did not show how many units were already stored in the database.

This could make a manager wonder:

Did I already enter today’s sales for this product?

Was 14 already registered?

If I enter 17 now, will it become 31?

I therefore displayed the current registered quantity below each product name.

Premium White Bread
🟢 Registered today: 14

I also placed the current quantity directly into the input field.

Input field: 14

When the user changes it to 17 and submits the form, the database value is replaced.

Retrieving Today’s Registered Quantities

I added a helper function that converts the selected date’s sales records into a dictionary containing product IDs and quantities.

def _get_today_sales_map(target_date):
    """Return registered sales quantities by product ID."""
    sales = DailySales.query.filter_by(
        date=target_date
    ).all()

    return {
        sale.product_id: sale.quantity
        for sale in sales
    }

The returned value looks like this:

{
    1: 14,
    2: 8,
    3: 0,
}

The dictionary key is the product ID.

The value is the registered sales quantity for that date.

Passing the Current Values to the Template

The daily sales-entry route passes the current sales data to the template for both GET and POST requests.

@app.route("/input", methods=["GET", "POST"])
def input_sales():
    today = datetime.date.today()

    if request.method == "POST":
        date_str = request.form.get("date")
        sale_date = datetime.date.fromisoformat(
            date_str
        )

        product_ids = request.form.getlist(
            "product_id"
        )

        quantities = request.form.getlist(
            "quantity"
        )

        logger.info(
            "Sales data submission received "
            f"for date: {sale_date}"
        )

        for product_id, quantity in zip(
            product_ids,
            quantities,
        ):
            if quantity.strip() == "":
                continue

            try:
                qty_int = int(float(quantity))
            except ValueError:
                logger.warning(
                    "Invalid quantity format skipped: "
                    f"product_id={product_id}, "
                    f"quantity={quantity}"
                )
                continue

            if qty_int < 0:
                logger.warning(
                    "Negative quantity skipped: "
                    f"product_id={product_id}, "
                    f"quantity={qty_int}"
                )
                continue

            existing = DailySales.query.filter_by(
                product_id=int(product_id),
                date=sale_date,
            ).first()

            if existing:
                existing.quantity = qty_int
            else:
                sale = DailySales(
                    product_id=int(product_id),
                    date=sale_date,
                    quantity=qty_int,
                )

                db.session.add(sale)

        db.session.commit()

        logger.info(
            "Sales data successfully committed "
            f"for date: {sale_date}"
        )

        return render_template(
            "input.html",
            success=True,
            products=_get_current_products(),
            today=today,
            today_sales=_get_today_sales_map(
                today
            ),
        )

    return render_template(
        "input.html",
        products=_get_current_products(),
        today=today,
        today_sales=_get_today_sales_map(
            today
        ),
    )

After the POST request, the route retrieves the latest values from the database again.

As a result, the page displayed after submission shows the newly updated quantities.

Displaying Current Values in the Template

I changed the product-entry section in input.html as follows:

{% for product in products %}
    <div class="product-row">
        <input
            type="hidden"
            name="product_id"
            value="{{ product.id }}"
        >

        <div class="product-info">
            <span class="product-name">
                {{ product.name }}
            </span>

            <span class="registered-quantity">
                🟢 Registered today:
                {{ today_sales.get(product.id, 0) }}
            </span>
        </div>

        <span class="product-price">
            ¥{{ product.price }}
        </span>

        <input
            type="number"
            name="quantity"
            class="qty-input"
            min="0"
            value="{{ today_sales.get(product.id, 0) }}"
            required
        >
    </div>
{% endfor %}

When no registered data exists, the second argument of get() displays zero.

{{ today_sales.get(product.id, 0) }}

The same value is used as the input field’s initial value.

value="{{ today_sales.get(product.id, 0) }}"

Now the user can understand the current registration status immediately after opening the page.

Why I Did Not Make the Text Green

At first, I considered displaying the entire “Registered today” message in green.

However, changing the text color could reduce readability depending on the background or the user’s display environment.

I kept the existing dark text color and used only a green circle as a visual marker.

<span class="registered-quantity">
    🟢 Registered today:
    {{ today_sales.get(product.id, 0) }}
</span>

The CSS inherits the normal text color.

.input-page .registered-quantity {
    color: inherit;
    font-size: 13px;
    font-weight: bold;
    line-height: 1.4;
}

I used three signals together:

A green circle
The words “Registered today”
Bold text

A user who has difficulty distinguishing colors can still understand the meaning from the text.

Accessibility note

Colors are used as a supporting cue.

I do not rely on color alone. Icons, specific wording, and button shapes are also used so that the interface is less dependent on color perception or technical experience.

Improving the Spacing Between Products

After adding the registered quantity, each row contained:

Product name
Current quantity
Price
Input field

The products began to look visually crowded.

I added spacing and a divider between each product.

.input-page .product-row {
    display: flex;
    align-items: flex-start;
    gap: 12px;

    margin-bottom: 18px;
    padding-bottom: 14px;

    border-bottom: 1px solid #f3ecde;
}

The product name and registered quantity are displayed vertically as one information group.

.input-page .product-info {
    display: flex;
    flex: 1;
    flex-direction: column;
    gap: 4px;

    min-width: 0;
}

This made the boundary between products easier to recognize.

Improving Navigation Between Screens

Before the improvement, it was not always obvious how to move to the next task.

I added navigation based on the actual store workflow.

Register the product menu
        ↓
Enter daily sales
        ↓
Review sales analysis and rankings

I added a dashboard button to the daily sales-entry page.

<a
    href="{{ url_for('dashboard') }}"
    class="btn-submit btn-dashboard-link"
>
    📊 View the Store Health Report
    (Sales Analysis and Rankings)
</a>

I also replaced the simple text link to the top page with a button-shaped link.

<a
    href="{{ url_for('index') }}"
    class="input-home-link"
>
    🍞 Return to the Top Page
</a>

The dashboard received links to the daily entry page and the top page.

<div class="dashboard-actions">
    <a
        href="{{ url_for('input_sales') }}"
        class="dashboard-action dashboard-input-link"
    >
        📝 Enter Daily Sales
    </a>

    <a
        href="{{ url_for('index') }}"
        class="dashboard-action dashboard-home-link"
    >
        🍞 Return to the Top Page
    </a>
</div>

The next business operation is now easier to reach from every screen.

Assigning a Meaning to Each Button Color

Instead of choosing unrelated colors for each page, I assigned colors according to the type of action.

Green
→ Product-menu registration

Honey
→ Daily sales entry and updates

Strawberry red
→ Sales analysis and rankings

Light cream
→ AI questions

White
→ Return to the top page

Pistachio Green for Product Registration

I used a pistachio- or leaf-inspired green for the product-menu registration button.

.btn-menu-register {
    background-color: #7fa45a;
    color: #ffffff;
}

.btn-menu-register:hover {
    background-color: #688c47;
}

Honey Color for Daily Sales Entry

I used a warm color associated with bread and honey for daily sales entry.

.input-page .btn-submit {
    background-color: #c9a063;
    color: #ffffff;
}

Strawberry Red for Sales Analysis

I used a strawberry-jam red for the sales analysis button.

.input-page .btn-dashboard-link {
    background-color: #e85d5d;
    color: #ffffff;
}

.input-page .btn-dashboard-link:hover {
    background-color: #cf4646;
}

This preserves the bakery theme while clearly separating analysis from daily input.

Light Cream for AI Questions

The “Ask for Today’s Message” button and the dashboard’s “Ask for Detailed Advice” button both call AI features.

I therefore gave them the same light cream color.

.dashboard-page .btn-ai-advice {
    background-color: #f7eed6;
    color: #8b5a1a;
}

.dashboard-page .btn-ai-advice:hover {
    background-color: #ebdcb9;
}

Using a consistent color makes it easier to recognize the same type of action across different pages.

However, the interface does not depend on color alone.

Icons and specific button text are also used.

Moving CSS Out of the HTML Files

As part of this improvement, I moved CSS from the individual HTML files into static/style.css.

sales_data_app/
├── app.py
├── templates/
│   ├── index.html
│   ├── input.html
│   ├── dashboard.html
│   └── success.html
└── static/
    └── style.css

Each HTML file loads the stylesheet as follows:

<link
    rel="stylesheet"
    href="{{ url_for('static', filename='style.css') }}"
>

I also added page-specific classes to each body element.

<body class="input-page">

<body class="dashboard-page">

<body class="success-page">

The CSS selectors begin with the page class.

.input-page .btn-submit {
    /* Applied only to the daily entry page */
}

.dashboard-page .btn-submit {
    /* Applied only to the dashboard */
}

Even when two pages use the same .btn-submit class name, this reduces the risk of one page’s styles affecting another page unexpectedly.

A CSS Side Effect That Actually Occurred

While reorganizing the CSS, the dashboard’s filter button lost its rounded corners and gained a black border.

The cause was the interaction between the shared .btn-submit style and the dashboard-specific style.

I explicitly defined the required appearance for the dashboard.

.dashboard-page .btn-submit {
    width: auto;
    margin-top: 0;
    padding: 10px 15px;

    background-color: #8b5a1a;
    color: #ffffff;
    border: none;
    border-radius: 8px;
    outline: none;

    box-shadow: none;
    cursor: pointer;
}

Centralizing CSS reduces duplicated code, but it also increases the number of elements that one change can affect.

I learned that shared styles and page-specific styles need clearly defined responsibilities.

CSS warning

Shared CSS can reduce duplication, but changes may also affect other pages using the same class.

Common styles and page-specific styles should be separated carefully, and every screen should be reviewed after a change.

“Easy to Use for Everyone” as the Main Decision Rule

An important concept in my application development is:

An application that is easy to use for people of all ages and backgrounds.

I used that idea as the decision rule for this improvement.

The changes included:

Replacing the ambiguous word “Save” with the specific word “Update”
Displaying the currently registered quantity
Placing the current value in the input field
Removing confusing options such as future years
Assigning consistent colors based on action type
Using icons and text in addition to color
Replacing small text links with clearly clickable buttons
Adding navigation to the next business task on each page

I prioritized reducing thought and hesitation over making the interface visually flashy.

How My Okonomiyaki Sales Experience Helped the UI Improvement

I previously performed live okonomiyaki cooking and sales demonstrations at department stores.

I made the product myself, prepared the sales pitch, explained it to customers, and comple

📝 Ending Product Sales Without Breaking Sales History — Implementing Soft Deletes and Reducing Gemini API Usage in Flask

tosane932 — Sun, 02 Aug 2026 13:05:26 +0000

🍞 Introduction

Hello from Japan! 🇯🇵

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

I am a professional truck driver teaching myself Python while developing a bakery sales management application with Flask, PostgreSQL, and the Gemini API.

At the time of this work, I had completed approximately 135 hours of programming study.

I also made a major update to the application and its README.

https://github.com/tosane932/sales_data_app

When I added product-master editing, I encountered several connected problems:

Adding or replacing menu items could break their relationship with existing sales history
Products with sales records could not be physically deleted
Renaming an existing product could be treated as adding a completely different product
Opening the dashboard consumed Gemini API requests automatically
Users received unclear messages when the free API quota was exhausted

I spent approximately six hours reviewing the entire flow:

Product updates
→ Product discontinuation
→ Database migration
→ Production startup
→ Gemini API execution

This article records the failed implementation and how I changed it into a safer structure.

This is a long development record, so thank you for your patience.

Development Environment

Python 3.12
Flask
Flask-SQLAlchemy
Flask-Migrate
PostgreSQL in production
SQLite locally
Docker
Render
Gunicorn
Google Gemini API
Chart.js
GitHub Actions
pytest

The First Error

When I updated the product master, the following error occurred:

psycopg2.errors.NotNullViolation:
null value in column "product_id" of relation "daily_sales"
violates not-null constraint

SQLAlchemy attempted to execute an update similar to:

UPDATE daily_sales
SET product_id = NULL
WHERE daily_sales.id = ...

The DailySales.product_id column was defined with nullable=False.

product_id = db.Column(
    db.Integer,
    db.ForeignKey("products.id"),
    nullable=False,
)

Therefore, when I tried to delete a product connected to existing sales history, SQLAlchemy could not change the related product_id to NULL.

Cause: I Deleted Existing Products and Registered Them Again

The original product-master update logic deleted all products for the selected year and month, then recreated them from the submitted form.

Product.query.filter_by(
    year=year,
    month=month,
).delete()

for product in products_data:
    db.session.add(
        Product(
            year=year,
            month=month,
            name=product["name"],
            price=product["price"],
        )
    )

db.session.commit()

This looks simple when considering only the product-master table.

However, the daily-sales table references Product.id.

products
└── id = 1

daily_sales
└── product_id = 1

Deleting and recreating the product generates a different primary key even when the visible product name is unchanged.

Before deletion:
White Bread, id = 1

After re-registration:
White Bread, id = 15

To a human, both records represent the same product.

To the database, they are completely different rows.

Solution 1: Update Products by ID, Not by Name

I added each existing product's Product.id to the form as a hidden field.

<input
    type="hidden"
    name="product_id"
    value="{{ product.id }}"
>

New products added through JavaScript receive an empty ID.

<input
    type="hidden"
    name="product_id"
    value=""
>

The Flask route receives product IDs, names, and prices in matching order.

product_ids = request.form.getlist("product_id")
product_names = request.form.getlist("prod_name")
product_prices = request.form.getlist("prod_price")

products_data = []

for product_id, name, price in zip(
    product_ids,
    product_names,
    product_prices,
):
    if name.strip():
        products_data.append(
            {
                "id": (
                    int(product_id)
                    if product_id.isdigit()
                    else None
                ),
                "name": name.strip(),
                "price": (
                    int(price)
                    if price.isdigit()
                    else 0
                ),
            }
        )

Existing products are updated by primary key.

Only products without an ID are inserted as new rows.

existing_products = Product.query.filter_by(
    year=year,
    month=month,
).all()

existing_dict = {
    product.id: product
    for product in existing_products
}

for product_data in products_data:
    product_id = product_data["id"]

    if (
        product_id is not None
        and product_id in existing_dict
    ):
        existing_product = existing_dict[product_id]

        existing_product.name = product_data["name"]
        existing_product.price = product_data["price"]
        existing_product.is_active = True

    else:
        db.session.add(
            Product(
                year=year,
                month=month,
                name=product_data["name"],
                price=product_data["price"],
            )
        )

Now a product name can be changed while preserving the same Product.id.

The existing sales history remains connected to the same database record.

Solution 2: Use Soft Deletes Instead of Physical Deletes

For discontinued products, I decided not to delete the database row.

Instead, I added a sales-status flag.

is_active = db.Column(
    db.Boolean,
    nullable=False,
    default=True,
    server_default=db.true(),
)

I collect the IDs submitted by the form.

submitted_ids = {
    product_data["id"]
    for product_data in products_data
    if product_data["id"] is not None
}

Any existing product that is no longer included in the form is marked as inactive.

for product in existing_products:
    if product.id not in submitted_ids:
        product.is_active = False

Only active products are shown on the sales-entry screen.

products = Product.query.filter_by(
    year=year,
    month=month,
    is_active=True,
).all()

This provides both behaviors:

Discontinued product
→ Hidden from future sales entry

Past sales history
→ Still connected to the original Product.id

The product disappears from normal operation without deleting the historical record.

Why Soft Deletion Was the Better Fit

Physical deletion means removing the row itself.

DELETE FROM products ...

Soft deletion means keeping the row and changing its state.

is_active = False

For a system that stores sales history, the product record is part of the historical evidence.

Even when the product is no longer sold, past records still need to answer questions such as:

Which product was sold?
What was its name?
What price was registered?
Which sales records referred to it?
When did it stop being active?

Deleting the row would make that history difficult or impossible to interpret safely.

Solution 3: Add the Column with Flask-Migrate

I generated a migration locally.

flask db migrate -m "add is_active to products"

The generated SQLite-oriented migration used:

server_default=sa.text("1")

I changed it to a form that is also clear for PostgreSQL.

server_default=sa.true()

I then applied and checked the migration locally.

flask db upgrade

flask db current

On Render, I confirmed the following migration log:

Running upgrade -> 043c481b4069, add is_active to products

This showed that the production database had applied the new is_active column.

Important: `stamp` and `upgrade` Have Different Roles

These two commands are not interchangeable.

flask db upgrade
→ Executes migrations and changes the database schema

flask db stamp head
→ Marks the database as current without executing migrations

If stamp runs automatically during every startup, an unapplied migration could be marked as already applied.

That would create a dangerous mismatch:

Alembic history
→ Says the migration is applied

Actual database schema
→ Has not changed

I therefore removed automatic stamp logic from the application and used upgrade in the startup command instead.

Solution 4: Switch Production Startup to Gunicorn

I replaced Flask's built-in development server with Gunicorn.

In requirements.txt:

gunicorn==26.0.0

The production startup command became:

CMD [
    "sh",
    "-c",
    "flask db upgrade && gunicorn --bind 0.0.0.0:${PORT:-5000} app:app"
]

The startup flow is now:

Apply pending migrations
        ↓
If successful, start Gunicorn
        ↓
Serve the Flask application

After deployment, the logs showed:

Starting gunicorn 26.0.0
Listening at: http://0.0.0.0:10000
Booting worker
Your service is live

The Next Problem: I Exhausted the Gemini API Free Tier

During testing, I encountered the following error:

429 RESOURCE_EXHAUSTED

Quota exceeded for metric:
generate_content_free_tier_requests

limit: 20
model: gemini-2.5-flash

Originally, the application called the Gemini API simply by opening certain pages.

Open the dashboard
→ Generate AI business advice

Open the daily sales-entry page
→ Generate an AI greeting

Change the selected period
→ Generate AI business advice again

For a free API tier, this design consumed requests unnecessarily.

A user could spend the daily allowance without intentionally asking for AI analysis.

Solution 5: Run AI Only When the User Requests It

I changed the default page behavior to display fixed text.

The application now calls Gemini only when the user presses an AI button.

I separated the AI analysis into its own API endpoint.

@app.route("/api/ai-advice")
def api_ai_advice():
    year_param = request.args.get("year")
    month_param = request.args.get("month")

    target_year = (
        int(year_param)
        if year_param
        else None
    )

    target_month = (
        int(month_param)
        if month_param
        else None
    )

    sales_data = _get_sales_from_db(
        target_year,
        target_month,
    )

    ranked_sales = sorted(
        sales_data.items(),
        key=lambda item: item[1],
        reverse=True,
    )

    return jsonify(
        {
            "ai_advice": _generate_ai_advice(
                ranked_sales
            )
        }
    )

The browser calls the endpoint only when the button is pressed.

function loadAiAdvice() {
    const year =
        document.getElementById("selectYear").value;

    const month =
        document.getElementById("selectMonth").value;

    fetch(`/api/ai-advice?year=${year}&month=${month}`)
        .then((response) => response.json())
        .then((data) => {
            document.getElementById(
                "aiAdviceText"
            ).innerHTML = data.ai_advice.replace(
                /\n/g,
                "<br>"
            );
        });
}

I used the same principle for the daily greeting.

Open the page
→ Gemini requests: 0

Press “Today's Message”
→ Gemini requests: 1

This makes API usage intentional instead of automatic.

Preventing Repeated Clicks

While the AI request is running, I disable the button.

aiButton.disabled = true;
aiButton.textContent = "Analyzing...";

After the request finishes, the button is restored.

.finally(() => {
    aiButton.disabled = false;

    aiButton.textContent =
        "🤖 Ask for detailed advice again";
});

This prevents:

Repeated API requests
Accidental double clicks
Duplicate processing
Faster consumption of the free quota
Confusing overlapping responses

Showing Different Messages for 429 and 503 Errors

The user-facing meaning of these errors is different.

429
→ Quota exceeded or rate limited

503
→ Service temporarily unavailable or overloaded

I separated the messages.

error_text = str(error)

if (
    "GenerateRequestsPerDayPerProjectPerModel-FreeTier"
    in error_text
):
    return (
        "☕【Today's AI analysis limit has been reached】\n"
        "Your sales data was saved and aggregated normally."
    )

if (
    "429" in error_text
    or "RESOURCE_EXHAUSTED" in error_text
):
    return "☕【The AI is taking a short break】"

if (
    "503" in error_text
    or "UNAVAILABLE" in error_text
):
    return "🥐【The AI assistant is currently busy】"

Detailed exception information is written to the logs instead of being displayed directly to the user.

This separates:

User message
→ Clear and understandable

Application log
→ Detailed information for troubleshooting

An Indentation Error During Deployment

The final deployment also failed with:

IndentationError:
expected an indented block after 'if' statement

This type of problem can be detected before deployment with:

python -m py_compile app.py

I added it to my pre-deployment inspection routine.

git status
git diff
python -m py_compile app.py
pytest

One incorrect indentation level can prevent the entire production application from starting.

A quick syntax check is therefore a useful final gate.

Final Product-Master Behavior

New product
→ INSERT

Existing product
→ UPDATE using Product.id

Product removed from the form
→ is_active = False

Past sales history
→ Preserved

The product name can change without breaking its relationship with existing sales records.

Final Gemini API Behavior

Open dashboard
→ 0 requests

Change period and load sales data
→ 0 requests

Press “Ask for detailed advice”
→ 1 request

Open daily sales-entry page
→ 0 requests

Press “Today's Message”
→ 1 request

The user now controls when the limited AI resource is consumed.

What I Learned

1. The Same Product Name Does Not Mean the Same Database Record

When primary keys are different, the database treats the rows as different products.

Updates should be based on stable IDs, not only visible names.

2. Historical Data Should Not Be Deleted Casually

For products connected to sales history, soft deletion was more appropriate than physical deletion.

3. Migrations Have Three Stages

Generate
→ Review
→ Apply

Automatically generated migrations still need human review, especially when both SQLite and PostgreSQL are involved.

4. Automatic AI Execution Is Not Always the Best Design

Calling the AI only when requested improved:

Page speed
Free-tier usage
User understanding
Operational predictability

5. Run a Syntax Check Before Deployment

python -m py_compile app.py

A one-line indentation error can prevent the production application from starting.

Summary

This work was much more than adding a delete button.

The actual process was:

Product update
        ↓
Foreign-key error
        ↓
Switch to ID-based updates
        ↓
Introduce soft deletion
        ↓
Generate and review a migration
        ↓
Apply it to production
        ↓
Switch production startup to Gunicorn
        ↓
Reduce unnecessary Gemini API requests
        ↓
Improve 429 and 503 error messages

The goal was not simply:

Make the product disappear from the screen.

I needed to consider:

Sales history
Foreign-key relationships
Production database migrations
API quotas
Repeated user input
Production startup
User-facing error messages
Troubleshooting logs

Only after checking all of those areas did the update feel complete.

My next step is to expand pytest coverage for:

Product updates
Soft deletion
API endpoints
Error-handling behavior
Database relationships

Thank you for reading.

sales_data_app Maintenance Series

Pre-Production Inspection Trilogy

Post-Deployment Maintenance Series

My Progress After Two Months and 129 Hours of Learning Python — Building a Bakery Sales Management System

tosane932 — Sun, 02 Aug 2026 07:16:14 +0000

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

Introduction

Hello from Japan! 🇯🇵

I started learning Python on May 12, 2026.

Approximately two months have passed, and my total study time has reached 129 hours.

To record my current progress, I have published a bakery sales management system that I developed while learning.

GitHub repository:

https://github.com/tosane932/sales_data_app

🍞 What I Built

I built a web-based sales management application for bakery stores.

It is more than a simple sales-entry form.

The system includes:

Product master management
Daily sales entry
Data storage in PostgreSQL
Sales rankings
AI-powered sales analysis
An AI staff assistant

You can try the deployed application here:

https://bakery-salesdata.onrender.com/

The application runs on a free hosting plan, so it may take a little time to start if the server has been inactive.

📖 Why I Built It

I currently work in logistics as a driver of large and medium-sized trucks.

Before working in logistics, I also had experience selling food at department-store events.

In those workplaces, I repeatedly encountered situations where sales management depended heavily on Excel.

That made me wonder:

Could daily sales entry, aggregation, and analysis all be handled in one system?

That question became the starting point for this application.

I wanted to build something that reflected actual workplace needs rather than creating a project only for programming practice.

⚒️ Technology Stack

Backend

Python
Flask
SQLAlchemy
Flask-Migrate

Frontend

HTML
CSS
JavaScript
Fetch API
Chart.js

Database

PostgreSQL
SQLite

AI

Google Gemini API

Infrastructure

Docker
Docker Compose
Render

Testing and Automation

pytest
GitHub Actions

⚙️ Implemented Features

Product Registration

Users can register bakery products and their prices for each month.

The product information is then used by the daily sales-entry feature.

Daily Sales Entry

Users can enter the number of units sold for each product.

The page also displays a short seasonal message generated by AI.

Sales Analysis

The application aggregates the stored sales data and displays:

Product rankings
Sales charts
AI-generated business advice

The goal is not only to store numbers, but also to help users understand what the numbers may indicate.

💡 What I Focused On

In logistics, we work with the assumption that:

An accident may happen.

I tried to apply the same mindset to software development.

Instead of assuming:

The application will probably work correctly.

I designed it around the possibility that:

A user may enter invalid data, or an important setting may be missing.

Examples include:

Input validation
Exception handling
Application logging
Environment-variable management
Fail-Fast behavior

I wanted the system to detect problems early instead of depending only on users or developers behaving perfectly.

Defensive Design Examples

Input Validation

The application checks values before saving them to the database.

This reduces the risk of invalid sales data entering the system.

Environment-Variable Checks

The application verifies that required configuration values are available.

If a required value is missing, the system can stop before a user reaches the broken feature.

Logging

Important startup events and errors are written to standard output.

This makes it easier to investigate problems in Docker and Render.

Database Migration Management

I moved away from relying only on:

db.create_all()

and introduced Flask-Migrate to manage database schema changes through migration history.

🏗️ What Was Most Difficult

The most difficult problem was:

It works locally, but it does not work in production.

I encountered differences involving:

Docker
PostgreSQL
Render
Environment variables
Python package versions
Database connection URLs
Persistent storage

I investigated each issue separately instead of trying to change everything at once.

For example, I checked:

Whether the required package was installed
Whether the version matched requirements.txt
Whether DATABASE_URL existed
Whether the application was connected to SQLite or PostgreSQL
Whether the migration command completed successfully
Whether the production logs matched my expectations

This process taught me that production problems are not always caused by the application code itself.

Sometimes the code is correct, but the surrounding infrastructure is incomplete or configured differently.

🚚 From Local Development to Automatic Deployment

The current workflow is:

Modify the application locally
        ↓
Run tests
        ↓
Push the changes to GitHub
        ↓
GitHub Actions runs pytest
        ↓
Render detects the update
        ↓
The application is built and deployed

The project is now configured so that a GitHub push triggers automated testing through GitHub Actions and deployment to Render.

This does not mean the system is perfect.

However, it is a significant improvement over manually updating the production environment without an automated check.

✅ Testing

I currently use pytest to test the prompt-generation logic.

For example, I verify that:

Sales data is included in the generated prompt
Important AI instructions are included
The function returns a string
The output does not lose required content

The number of tests is still limited.

My next goal is to expand the test coverage to include:

Database operations
Input validation
Flask routes
API error handling
Authentication-related behavior
Integration between application components

I also learned that simply writing tests is not enough.

I need to deliberately break the relevant behavior and confirm that the tests actually detect it.

📊 Current Application Structure

The overall structure is approximately:

Browser
   ↓
Flask application
   ↓
SQLAlchemy
   ↓
PostgreSQL

For AI-related functions:

Sales data
   ↓
Prompt-generation logic
   ↓
Gemini API
   ↓
AI-generated advice
   ↓
Displayed in the browser

For deployment:

GitHub
   ↓
GitHub Actions
   ↓
Render
   ↓
Flask application and PostgreSQL

Building these connected parts was much more difficult than creating a single page.

However, it helped me understand how application code, databases, testing, and infrastructure work together.

🚧 What I Want to Add Next

I plan to continue developing the system with features such as:

Inventory management
Purchase-order suggestions
AI-powered stockout prediction
Profit analysis
Weekday and seasonal analysis
More comprehensive automated tests
Improved error messages
Better mobile usability

My goal is to develop the project into a system that could genuinely help people working in a busy store.

What I Learned After 129 Hours

After approximately two months of study, I have learned that building a working application requires more than writing Python code.

It also involves:

Understanding user needs
Designing database structures
Managing dependencies
Handling failures
Reading logs
Testing assumptions
Deploying the application
Maintaining the production environment

I still have many areas to improve.

However, I can now investigate problems by separating them into categories such as:

Code
Data
Dependencies
Environment variables
Database
Infrastructure
User input

That change in how I approach problems may be one of the most important results of these 129 hours.

🍞 Conclusion

Approximately two months and 129 hours after beginning Python, I was able to develop and publish this application.

There are still many improvements I want to make and many topics I need to learn.

However, I have tried not to make “studying programming” the final goal.

Instead, I have focused on:

Learning by building something that actually works.

I want to continue adding features and improving the application little by little.

When I read this article again several months from now, I hope I will be able to think:

This was how far I had come at that time.

Thank you for reading.

Demo

https://bakery-salesdata.onrender.com/

GitHub

https://github.com/tosane932/sales_data_app

sales_data_app Maintenance Series

Pre-Production Inspection Trilogy

Post-Deployment Maintenance Series

🍞 sales_data_app on GitHub

【Render】Migrating from SQLite to PostgreSQL: Fixing the `postgres://` Connection Error and the “Still Using SQLite” Problem [3/3]

tosane932 — Sun, 02 Aug 2026 00:46:11 +0000

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

Introduction

Hello from Japan! 🇯🇵

I am tosane932, a professional truck driver working in logistics while teaching myself Python.

At the time of writing, I had completed approximately 127 hours of programming study.

In the previous article, Part 2 of this three-part series, I removed the familiar db.create_all() approach and established a database migration foundation using Flask-Migrate.

This article records the next step in that work:

Completely moving from SQLite in the local-style setup to PostgreSQL in the Render production environment.

During this infrastructure transition, I encountered two unexpected problems:

A connection error caused by Render's postgres:// database URL format
A strange situation where the code had been corrected, but the production logs still showed SQLiteImpl

I will share the actual sequence of events, including the mistake that turned out not to be in the code at all.

https://github.com/tosane932/sales_data_app

1. Background and Purpose of the Migration

Until this point, the deployed application on Render had been using SQLite.

SQLite is simple and convenient, but the application was running without persistent disk storage.

Because of the behavior of the hosting environment, database data could be reset during events such as:

Redeployment
Service restart
Container replacement

That was not suitable for a sales-management application that needed to preserve records.

To build a more reliable production foundation, I decided to:

Create a PostgreSQL database on Render
Connect the Flask application to it
Apply schema migrations automatically during deployment
Preserve production data independently of the application container

The goal was to establish a production database environment that could support actual data-driven operation.

2. Implementation Steps and Observed Results

Step 1: Add Database URL Conversion Logic in `config.py`

The DATABASE_URL provided by Render began with:

postgres://

However, the SQLAlchemy configuration I was using expected:

postgresql://

Using the URL without conversion caused a connection error.

To handle the difference safely, I added the following logic to config.py.

import os


raw_db_url = os.environ.get("DATABASE_URL")

if raw_db_url:
    # Convert postgres:// into a format SQLAlchemy can interpret.
    if raw_db_url.startswith("postgres://"):
        SQLALCHEMY_DATABASE_URI = raw_db_url.replace(
            "postgres://",
            "postgresql://",
            1,
        )
    else:
        SQLALCHEMY_DATABASE_URI = raw_db_url
else:
    # Use local SQLite when DATABASE_URL is not set.
    SQLALCHEMY_DATABASE_URI = (
        f"sqlite:///{os.path.join(os.path.dirname(__file__), 'local.db')}"
    )

This configuration has two paths.

Production Path

When DATABASE_URL exists:

DATABASE_URL
→ Convert postgres:// if necessary
→ Connect to PostgreSQL

Local Fallback Path

When DATABASE_URL does not exist:

No DATABASE_URL
→ Use local.db
→ Connect to SQLite

I updated the code, committed it, and pushed it to GitHub.

However, production still did not behave as expected.

Step 2: Run Migrations Automatically During Container Startup

I wanted the production database schema to be upgraded automatically whenever a new version was deployed.

I therefore changed the Docker startup command to run:

flask db upgrade

before starting the Flask application.

CMD flask db upgrade && python app.py

The intended startup flow became:

Start the container
        ↓
Apply all pending database migrations
        ↓
Start the Flask application

This reduced the risk of deploying application code that expected a newer schema while the database was still on an older revision.

Step 3: The Invisible Problem—Production Still Reported SQLite

After completing both changes, I deployed the application with confidence.

However, the production logs showed:

INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.

The application was still using:

SQLiteImpl

even though I had added the PostgreSQL URL-conversion logic.

I first suspected that the code itself was wrong.

To confirm the actual contents of the file, I ran:

cat config.py

The expected conversion logic was present.

I then suspected that I might have forgotten to commit the change.

git commit -m "Fix: Replace postgres:// with postgresql:// for Render PostgreSQL connection"

Git returned:

On branch main
Your branch is up to date with 'origin/main'.

nothing to commit, working tree clean

The message:

nothing to commit

confirmed that:

The code had already been committed
The commit had already been pushed
The working tree matched the remote repository

The problem was not an uncommitted code change.

At that point, I changed my perspective.

Instead of asking:

Is the connection code wrong?

I asked:

Does the production PostgreSQL database actually exist?

That question revealed the real cause.

When I checked the Render dashboard, I discovered that I had never created the PostgreSQL database itself.

I had prepared the connection code before preparing the destination.

The application had no production DATABASE_URL, so the fallback logic worked exactly as written:

No DATABASE_URL
        ↓
Use SQLite

The SQLite log was not evidence that the new code had failed.

It was evidence that the fallback path was functioning.

Step 4: Create the PostgreSQL Database on Render

I created a new PostgreSQL database from the Render dashboard using the free plan.

I waited until its status changed to:

Available

Render then provided an:

Internal Database URL

I copied that value into the web service's environment variables as:

DATABASE_URL

Saving the environment variable triggered a new deployment automatically.

This time, the logs changed.

INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
==> Your service is live 🎉

The important difference was:

PostgresqlImpl

This confirmed that:

The application received DATABASE_URL
The connection URL was accepted
Alembic connected to PostgreSQL
The migration process ran against PostgreSQL
The web service started successfully

The application was no longer using the SQLite fallback in production.

What Actually Happened

The problem looked like this:

I added PostgreSQL connection code
        ↓
Production still used SQLite
        ↓
I suspected the code and Git history
        ↓
Both were correct
        ↓
The PostgreSQL database had never been created
        ↓
DATABASE_URL did not exist
        ↓
The application correctly fell back to SQLite

The code was not ignoring PostgreSQL.

There was simply no PostgreSQL environment available for it to use.

Why the SQLite Fallback Made the Problem Harder to Notice

The fallback was designed to make local development convenient.

else:
    SQLALCHEMY_DATABASE_URI = (
        f"sqlite:///{os.path.join(os.path.dirname(__file__), 'local.db')}"
    )

When no DATABASE_URL was available, the application still started successfully.

That was useful locally.

However, in production, it also meant that a missing database configuration did not cause startup to fail.

Instead, the application quietly used SQLite.

This creates a trade-off.

Advantage

Local development remains easy.

No PostgreSQL required
→ Application starts with SQLite

Risk

A missing production environment variable may remain hidden.

DATABASE_URL missing in production
→ Application starts anyway
→ SQLite is used unexpectedly

A stricter production configuration could fail immediately instead of falling back silently.

For example:

import os


environment = os.environ.get("APP_ENV", "development")
raw_db_url = os.environ.get("DATABASE_URL")

if environment == "production" and not raw_db_url:
    raise RuntimeError(
        "DATABASE_URL must be configured in production."
    )

if raw_db_url:
    SQLALCHEMY_DATABASE_URI = raw_db_url.replace(
        "postgres://",
        "postgresql://",
        1,
    )
else:
    SQLALCHEMY_DATABASE_URI = (
        f"sqlite:///{os.path.join(os.path.dirname(__file__), 'local.db')}"
    )

This would make the intended behavior explicit:

Development
→ SQLite fallback is allowed

Production
→ Missing DATABASE_URL stops startup

That follows the Fail-Fast principle more closely.

Automatically Applying Migrations During Startup

The Docker command used was:

CMD flask db upgrade && python app.py

This ensures that the application starts only after the migration command succeeds.

The && operator means:

Run the second command only if the first command succeeds

Therefore:

flask db upgrade succeeds
→ Start app.py

flask db upgrade fails
→ Do not start app.py

This provides a basic deployment safety mechanism.

The application is not started against a database schema that failed to upgrade.

However, running migrations during application startup also requires care.

Possible concerns include:

Multiple application instances attempting the same migration
Long-running migrations delaying startup
Schema changes that lock production tables
Failed migrations preventing the service from becoming available
Destructive migrations affecting existing data

For a small application running as a single service, this approach may be practical.

For a larger system, a separate migration job or release command may be safer.

SQLite and PostgreSQL Behave Differently

The log output also showed an important database difference.

SQLite

Will assume non-transactional DDL.

PostgreSQL

Will assume transactional DDL.

DDL refers to schema-changing operations such as:

Creating tables
Altering tables
Adding columns
Creating indexes

PostgreSQL can handle many schema operations inside transactions.

That can make it possible to roll back certain failed migration operations.

SQLite has different limitations and behavior.

This is one reason why a migration tested only against SQLite should not automatically be considered proven for PostgreSQL.

The production database engine must also be tested.

How I Verified the Migration

The production log gave several useful pieces of evidence.

Context impl PostgresqlImpl.

This proved that Alembic was connected using the PostgreSQL implementation.

Will assume transactional DDL.

This showed that Alembic recognized the database as PostgreSQL.

Your service is live

This confirmed that the startup process completed after the migration command.

However, a complete verification should also include checks such as:

Confirming that the expected tables exist
Confirming that the current Alembic revision is recorded
Confirming that existing application operations work
Creating and reading actual records
Confirming that records remain after redeployment

Useful commands include:

flask db current

and:

flask db history

The PostgreSQL database also contains an Alembic version table used to track the applied revision.

The Three-Part Environment Modernization Process

This series covered three related improvements.

Part 1: Synchronizing Dependencies

I discovered a mismatch between:

google-genai 2.10.0

and:

requirements.txt: google-genai 2.4.0

I aligned the versions and verified the application behavior with pytest and manual testing.

Part 2: Introducing Schema History Management

I removed:

db.create_all()

and introduced:

Flask-Migrate
Alembic
Migration history managed through Git

Part 3: Moving Production to PostgreSQL

Created a PostgreSQL database on Render
Configured DATABASE_URL
Converted postgres:// when necessary
Ran migrations during startup
Confirmed PostgresqlImpl in the deployment logs

Together, these changes created a more reproducible and production-oriented foundation.

Summary

Through this infrastructure migration, I established the following improvements.

1. Environment Synchronization

Corrected dependency-version mismatches
Updated requirements.txt
Verified behavior with pytest

2. Modernized Database Operations

Removed db.create_all()
Introduced Flask-Migrate
Added migration history management

3. More Reliable Production Infrastructure

Migrated from SQLite to PostgreSQL
Added dynamic URL conversion
Configured automatic migration execution
Confirmed the database implementation through logs

The most important lesson was:

Correct code and existing infrastructure are separate requirements.

The connection logic can be perfect.

However, if the database has not been created and the environment variable has not been configured, there is nothing to connect to.

The application code and production infrastructure must fit together.

Only then can the system provide persistent data and predictable deployment behavior.

This work moved the project closer to a practical development and operations cycle based on:

Declared dependencies
Version-controlled schema changes
Production database persistence
Automated deployment
Log-based verification

Thank you for reading.

This is Part 3 of a three-part series.

Part 1 covered dependency synchronization and pytest verification.

Part 2 covered moving from db.create_all() to Flask-Migrate.

Part 3 covered the production migration from SQLite to Render PostgreSQL.

sales_data_app Maintenance Series

Pre-Production Inspection Trilogy

Post-Deployment Maintenance Series

🍞 sales_data_app on GitHub

🧪 Moving Beyond `db.create_all()`: Managing Database Schema History with Flask-Migrate [2/3]

tosane932 — Sun, 02 Aug 2026 00:22:18 +0000

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

Updated on July 14, 2026: Revised Section 2, “Implementation Steps and Observed Results.”

Introduction

Hello from Japan! 🇯🇵

I am tosane932, a professional truck driver working in logistics while teaching myself Python.

In the previous article—the first delivery in this three-part series—I fixed a google-genai version mismatch and verified the existing behavior with pytest.

This time, I moved on to the main task:

Modernizing the way my application manages its database schema.

Until now, I had relied on the convenient command:

db.create_all()

However, I decided to remove that approach and switch to Flask-Migrate, which uses Alembic underneath.

This article records:

Why I stopped relying on db.create_all()
How I introduced Flask-Migrate
How I initialized the migration environment
What happened when Alembic compared my models with the existing database
How I removed the old automatic table-creation logic

The complete project is available here:

https://github.com/tosane932/sales_data_app

Overview

Previously, my Flask application created database tables using:

db.create_all()

I replaced that approach with schema migration management using:

Flask-Migrate
Alembic
SQLAlchemy
Git

The goal was to move from simple table creation toward a system that records and applies database schema changes over time.

This article covers the transition through the initial migration setup.

1. Why I Introduced Flask-Migrate

Until this point, the application executed db.create_all() during startup.

Based on the SQLAlchemy models defined in the project, such as:

Product
DailySales

the command created any missing tables automatically.

This was convenient during the early stages of development.

However, it had several limitations.

It Does Not Manage Changes to Existing Tables

db.create_all() can create a table that does not yet exist.

However, it does not manage later schema changes such as:

Adding a column
Removing a column
Renaming a column
Changing a data type
Adding an index
Changing a constraint

For example, adding a new field to a SQLAlchemy model does not automatically update an existing production table in a controlled way.

It Does Not Record Schema History

With db.create_all(), there is no migration history showing:

What changed
When it changed
Why it changed
Which revision is currently applied
How to reproduce the same change in another environment

It Can Create Differences Between Environments

If database changes are handled manually, the following environments may gradually become inconsistent:

Local development
Docker
CI
Render PostgreSQL
Another developer's environment

The model definitions in the source code may represent one schema while the actual database contains another.

To reduce that risk, I introduced Flask-Migrate so database changes could be recorded as source-controlled migration files.

2. Implementation Steps and Observed Results

Step 1: Install and Configure Flask-Migrate

I installed Flask-Migrate and added it to requirements.txt.

I then integrated it into app.py.

from flask import Flask
from flask_migrate import Migrate

from models import DailySales, Product, db

import config


app = Flask(__name__)

app.config["SQLALCHEMY_DATABASE_URI"] = (
    config.SQLALCHEMY_DATABASE_URI
)

app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = (
    config.SQLALCHEMY_TRACK_MODIFICATIONS
)

db.init_app(app)

migrate = Migrate(app, db)

The important addition was:

migrate = Migrate(app, db)

This connects:

The Flask application
The SQLAlchemy database object
Flask-Migrate
Alembic

After this configuration, Flask's CLI can access migration commands such as:

flask db init
flask db migrate
flask db upgrade

Step 2: Initialize the Migration Environment

From the project root in my local development environment, I ran:

flask db init

This generated the migration environment and configuration files.

Actual Output

Creating directory /home/tosane/sales_data_app/migrations ...  done
Creating directory /home/tosane/sales_data_app/migrations/versions ...  done
Generating /home/tosane/sales_data_app/migrations/env.py ...  done
Generating /home/tosane/sales_data_app/migrations/README ...  done
Generating /home/tosane/sales_data_app/migrations/alembic.ini ...  done
Generating /home/tosane/sales_data_app/migrations/script.py.mako ...  done

The following structure was created:

migrations/
├── README
├── alembic.ini
├── env.py
├── script.py.mako
└── versions/

In logistics terms, this directory became the database's maintenance record system.

It provides a place to store future schema-change instructions.

Step 3: Compare the Models with the Existing Database

Next, I attempted to generate the initial migration.

flask db migrate -m "initial migration"

Alembic compared:

The current SQLAlchemy model definitions in models.py
The existing tables in the local SQLite database

Actual Output

INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.plugins] setting up autogenerate plugin...
INFO  [alembic.env] No changes in schema detected.

The key message was:

No changes in schema detected.

This meant that Alembic did not find a schema difference between the current SQLAlchemy models and the existing local database.

In other words, the model definitions and the database structure were already aligned at that moment.

Because no difference was detected, there was no schema change for Alembic to record in a new migration revision.

This was the expected result for the existing local environment.

What `No Changes in Schema Detected` Actually Means

This message does not mean that Flask-Migrate failed.

It means:

Model metadata
=
Current database schema

at the time of comparison.

Alembic's autogeneration process looks for differences such as:

A model column that does not exist in the database
A database table that no longer exists in the models
A changed nullable setting
A new index
A changed data type

If no relevant difference exists, there is nothing to generate.

The migration environment was initialized successfully, but the current database did not require a schema-changing migration.

Step 4: Remove the Old `db.create_all()` Logic

After introducing Flask-Migrate, I removed the automatic table-creation code from app.py.

The deleted code was:

with app.app_context():
    try:
        db.create_all()

        logger.info(
            "Database tables initialized successfully."
        )

    except Exception as exc:
        logger.error(
            "Failed to initialize database tables: %s",
            exc,
        )

Previously, this block attempted to create missing tables every time the application started.

After the migration system was introduced, schema changes were no longer supposed to happen implicitly during application startup.

Instead, they would be handled explicitly through migration commands.

flask db migrate
flask db upgrade

This creates a clearer separation of responsibilities:

Application startup
→ Start and serve the application

Migration operation
→ Change the database schema

That separation makes database changes more visible and intentional.

Why I Removed `db.create_all()` Instead of Keeping Both

It would have been possible to keep db.create_all() while also introducing Flask-Migrate.

However, that would create two separate systems attempting to manage the database schema.

db.create_all()
+
Flask-Migrate

That could make it difficult to determine:

Which process created a table
Whether a migration had actually been applied
Why one environment differed from another
Whether startup behavior was hiding a missing migration

I wanted one clear source of truth for schema changes.

For that reason, I removed the old startup-based creation logic after introducing the migration framework.

The New Database Management Flow

Before the change, the process was roughly:

Start the Flask application
        ↓
Run db.create_all()
        ↓
Create missing tables
        ↓
Continue startup

After the change, the intended process became:

Change a SQLAlchemy model
        ↓
Generate a migration revision
        ↓
Review the generated migration file
        ↓
Commit it to Git
        ↓
Apply the migration
        ↓
Start the application using the updated schema

Typical commands will be:

flask db migrate -m "describe the schema change"

and:

flask db upgrade

The generated migration files can then be reviewed and stored in Git together with the application code.

Why Migration Files Must Be Reviewed

Flask-Migrate and Alembic can detect many schema differences automatically.

However, automatically generated migration files should not be treated as unquestionable output.

Before applying a migration, I should confirm:

Which tables will be changed
Which columns will be added or removed
Whether any data could be lost
Whether SQLite and PostgreSQL behave differently
Whether the downgrade operation is valid
Whether existing production data needs transformation

The command:

flask db migrate

generates a proposal based on detected differences.

It does not replace human review.

That is similar to a pre-departure inspection sheet.

The existence of the document is useful, but someone still needs to inspect what it says before departure.

SQLite and PostgreSQL Require Extra Attention

My local environment and production environment do not necessarily use the same database engine.

The project has used:

SQLite locally
PostgreSQL in production

A migration that behaves correctly in SQLite may not behave exactly the same way in PostgreSQL.

Possible differences include:

Supported data types
Constraint behavior
ALTER TABLE support
Default values
Index behavior
Transaction handling

For that reason, the migration process must eventually be verified against the production-style PostgreSQL environment as well.

The local migration setup is only the foundation.

It does not automatically guarantee that every future migration will be safe in production.

Current Result

After this work, the application reached the following state:

Flask-Migrate was installed
The dependency was added to requirements.txt
Flask-Migrate was connected to the Flask application
The migrations directory was initialized
Alembic compared the current models with the existing SQLite schema
No schema differences were detected
The old db.create_all() startup logic was removed
Future schema changes can now be represented as migration files

The database-management process is now more explicit and easier to reproduce.

Summary

This work replaced startup-time table creation with a migration-based database management foundation.

The main lessons were:

db.create_all() is useful for creating missing tables, but it does not manage schema history
Existing table changes require a migration system
Flask-Migrate connects Flask and SQLAlchemy to Alembic
flask db init creates the migration environment
flask db migrate compares model metadata with the current database schema
No changes in schema detected means no relevant schema difference was found
Migration generation and migration application are separate operations
Automatically generated migration files still require human review
Running both db.create_all() and migrations can create unclear ownership of the schema
Local SQLite verification does not automatically prove PostgreSQL compatibility

By removing db.create_all() and introducing Flask-Migrate, I established the foundation for managing database changes through Git.

The next step is:

Applying migrations safely in the Render PostgreSQL production environment.

This is Part 2 of a three-part series.

Part 1 covered the google-genai version mismatch and pytest verification.

Part 3 will cover production deployment and automatic migration execution on Render.

sales_data_app Maintenance Series

Pre-Production Inspection Trilogy

Post-Deployment Maintenance Series

🍞 sales_data_app on GitHub

🐍 Fixing a `google-genai` Version Mismatch and Verifying the Behavior with pytest [1/3]

tosane932 — Sun, 02 Aug 2026 00:00:57 +0000

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

Introduction

Hello from Japan! 🇯🇵

I am tosane932, a professional truck driver working in logistics while teaching myself Python.

In my previous article, I tested a Docker multi-stage build and measured the actual change in image size.

At the end of that article, I said that I would write next about pytest and CI/CD.

This article was supposed to be the practical follow-up.

However, while preparing for that work, I encountered an unexpected side issue.

I only intended to introduce Flask-Migrate.

Instead, the pip installation logs revealed that the version of a library in my local development environment had been changed without me noticing.

The library was:

google-genai

From there, I went through the following process:

Identify the version mismatch
Restore the version that had already been tested locally
Update requirements.txt
Manually verify the Gemini API functionality
Run pytest to check for regressions

This article records that process without hiding the inconvenient parts.

https://github.com/tosane932/sales_data_app

Overview

While installing Flask-Migrate, I noticed a mismatch between:

The version of google-genai installed in my local development environment
The version declared in requirements.txt

The local environment had been using:

google-genai 2.10.0

However, requirements.txt still specified:

google-genai==2.4.0

When I ran:

pip install -r requirements.txt

pip followed the configuration file and replaced the newer local version with the older declared version.

This article explains how I discovered the issue, synchronized the environments, and verified the application behavior with automated tests.

1. The Problem and Its Background

I was preparing to introduce Flask-Migrate.

During that work, I ran:

pip install -r requirements.txt

The installation log contained the following lines:

Attempting uninstall: google-genai
    Found existing installation: google-genai 2.10.0
    Uninstalling google-genai-2.10.0:

That message caught my attention.

After checking the environment, I discovered that I had been developing and testing the application locally with:

google-genai 2.10.0

However, requirements.txt still contained the older version:

google-genai==2.4.0

Because pip treats requirements.txt as the declared source of dependency versions, it removed version 2.10.0 and installed version 2.4.0.

In other words, my local environment had been unintentionally downgraded.

The application had appeared stable before the installation command, but the dependency configuration and the actual environment were not synchronized.

This created several possible risks:

A method available in 2.10.0 might not exist in 2.4.0
API behavior may differ between versions
A production or CI environment could reproduce the older version
The local environment might behave differently from deployment
A future installation could silently change the application's behavior

I therefore decided that I needed to do more than simply reinstall the newer package.

I needed to make the declared configuration and the real environment match exactly.

2. Investigation and Recovery Process

Step 1: Confirm the Installed Version

First, I checked the currently installed package.

pip show google-genai

This command displays information such as:

Package name
Installed version
Installation location
Dependencies

The log had already shown that version 2.10.0 existed before the downgrade, but I wanted to confirm the current state directly.

Step 2: Restore the Previously Tested Version

I treated version 2.10.0 as the correct version because it was the version I had already used during local development and manual testing.

I reinstalled it explicitly.

pip install google-genai==2.10.0

Specifying the version made the intended state clear.

Instead of allowing pip to choose a version implicitly, I restored the exact version that had already been tested.

Step 3: Update `requirements.txt`

Restoring the local package was not enough.

If requirements.txt still specified 2.4.0, the same downgrade would happen again in:

A clean virtual environment
GitHub Actions
Docker builds
Render deployments
Another developer's machine

I therefore updated the dependency declaration.

google-genai==2.10.0

The important point was to align these two states:

Actual local environment
=
Declared dependency configuration

Without that alignment, the application could behave differently depending on where it was installed.

3. Manual Application Verification

After restoring the package version, I manually tested the application features that use the Gemini API.

I checked the AI-generated advice on:

The daily sales input page
The dashboard

The application used:

gemini-2.5-flash

I confirmed that the AI advice was generated without missing content or unexpected errors.

This manual check was important because dependency installation can succeed even when application-level behavior has changed.

The following command only proves that the package can be installed:

pip install google-genai==2.10.0

It does not prove that:

The API client is initialized correctly
The model request works
The response format is compatible
The application still handles the result correctly

For that reason, I verified the actual user-facing functionality through the browser.

4. Verifying the Logic with pytest

In addition to manual testing, I ran the prompt-related automated tests that I had already created.

pytest test_prompts.py -v

The purpose was to check whether restoring the library version had caused any regression in the existing application logic.

The test suite covered three behaviors:

The sales data is included in the generated prompt
The required instructions are included
The function returns a string

The result was:

3 passed

All three tests completed successfully.

This provided an additional level of confirmation that the prompt-generation logic remained intact after the dependency correction.

What the Test Result Proved—and What It Did Not

The passing tests confirmed that the behaviors defined in test_prompts.py still worked.

However, they did not directly prove that the Gemini API itself was functioning.

The prompt tests check local logic such as:

result = build_sales_prompt("Test data")
assert isinstance(result, str)

They do not necessarily make a real external API request.

That is why I used both:

Manual browser verification for the Gemini API integration
pytest for the local prompt-building logic

These two checks covered different parts of the system.

Manual verification
→ External API integration and visible application behavior

pytest
→ Local logic and expected output structure

A passing unit test should not be treated as proof of every part of the application.

Why the pip Log Was Important

The issue was not discovered through an application crash.

It was discovered through the installation log.

The key message was:

Attempting uninstall: google-genai

If I had ignored the output and only checked whether the installation command completed successfully, I might not have noticed that the package had been downgraded.

The installation itself was successful.

The environment change was still unintended.

This taught me that a successful command can still contain important warnings or state changes.

The final line of a command is not the only useful information.

Local Environments Can Hide Configuration Problems

My local environment had accumulated packages and versions from earlier development work.

As a result, it had reached a state that was not fully represented by requirements.txt.

The mismatch looked like this:

Local environment:
google-genai 2.10.0

requirements.txt:
google-genai 2.4.0

The application could appear to work locally because the newer package was already installed.

However, a clean environment would follow the dependency file and install the older version.

This is the same type of problem behind the phrase:

It worked on my machine.

The problem is not always the application code.

Sometimes the undeclared environment itself is the hidden dependency.

Why Exact Version Pinning Helped

By writing:

google-genai==2.10.0

I made the intended version explicit.

This improves reproducibility because pip is instructed to install the same version in every environment.

However, exact pinning also creates a maintenance responsibility.

The version will not automatically move to a newer release.

That means I must periodically review:

Security updates
Deprecation notices
API changes
Compatibility with the selected Gemini model
Changes to transitive dependencies

Exact pinning reduces accidental changes.

It does not remove the need for future updates.

A Safer Verification Flow

After this incident, I understood the value of the following sequence.

1. Inspect the Current Environment

pip show google-genai

2. Compare It with the Configuration

grep "google-genai" requirements.txt

3. Install the Intended Version

pip install google-genai==2.10.0

4. Update the Dependency File

google-genai==2.10.0

5. Verify the Installed State Again

pip show google-genai

6. Run Automated Tests

pytest test_prompts.py -v

7. Test the Actual API-Related Feature

Open the application and confirm that the Gemini-generated advice still works.

This sequence checks both configuration and behavior.

Result

After completing the work, the following states were established:

The pip installation log revealed the dependency mismatch
The local environment was restored to google-genai 2.10.0
requirements.txt was updated to the same version
The Gemini API feature worked during manual testing
All three prompt-related pytest cases passed
The local environment and dependency configuration were synchronized

This provided a safer foundation for the next step:

Introducing Flask-Migrate and managing database schema history.

Summary

The main lessons from this work were:

Installation logs can reveal unintended package changes
A successful pip command does not mean the environment changed exactly as intended
Local packages and requirements.txt can drift apart
A clean CI or production environment follows the declared dependency configuration
Restoring a package locally is not enough; the configuration file must also be updated
Manual API verification and unit tests protect different parts of the application
Exact version pinning improves reproducibility but requires maintenance
Environment synchronization should be confirmed before introducing another major dependency

I originally intended only to prepare for Flask-Migrate.

Instead, I discovered and corrected a hidden dependency mismatch.

This was an unexpected detour, but it prevented the next stage of development from being built on an inconsistent environment.

This is Part 1 of a three-part series.

The next article will cover the introduction of Flask-Migrate and database migration management.

sales_data_app Maintenance Series

Pre-Production Inspection Trilogy

Post-Deployment Maintenance Series

🍞 sales_data_app on GitHub

✅️ A Truck Driver Built a “Pre-Departure Inspection Gate” — Then Found a Hole in His Tests

tosane932 — Thu, 30 Jul 2026 19:32:03 +0000

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

🍞 What Went Wrong When I Introduced pytest and GitHub Actions into `sales_data_app`

https://github.com/tosane932/sales_data_app

Introduction

Hello from Japan! 🇯🇵

I am tosane932, a professional truck driver working in logistics while teaching myself Python.

In my previous article, I tested a Docker multi-stage build and measured the actual image-size difference.

At the end of that article, I said that my next topic would be pytest and CI/CD.

This is the practical follow-up.

I will share the conclusion first:

I wrote some tests, felt satisfied, and then discovered that they were allowing an important inconsistency to pass unnoticed.

This article records what happened and what I learned from it.

The Current Deployment Flow—and Its Risk

sales_data_app is connected to Render.

Whenever I run:

git push

Render automatically builds and deploys the latest version.

This is convenient.

However, without an automated test stage, buggy code can also travel directly into production.

In logistics terms, it was like sending a truck onto the road without performing a pre-departure inspection.

I decided to add an inspection gate using:

pytest
GitHub Actions

The goal was to test the code automatically before treating a push as safe.

Writing My First pytest Tests

sales_data_app contains a function called:

build_sales_prompt

This function creates the prompt sent to the Gemini API.

I started with three simple tests.

# test_prompts.py

from prompts import build_sales_prompt


def test_build_sales_prompt_includes_sales_summary():
    sample_data = "Monday: 50 melon buns, 30 red bean buns"

    result = build_sales_prompt(sample_data)

    assert sample_data in result


def test_build_sales_prompt_includes_key_instructions():
    result = build_sales_prompt("Test data")

    assert "3 suggestions" in result
    assert "bullet points" in result


def test_build_sales_prompt_returns_string():
    result = build_sales_prompt("Test data")

    assert isinstance(result, str)

When I ran the tests, all three passed.

test_prompts.py::test_build_sales_prompt_includes_sales_summary PASSED
test_prompts.py::test_build_sales_prompt_includes_key_instructions PASSED
test_prompts.py::test_build_sales_prompt_returns_string PASSED

At first glance, the function appeared to be protected.

I Deliberately Broke the Code—and Found a Hole in the Test

The prompt-generation function contained two instructions using the same number.

# prompts.py

def build_sales_prompt(sales_summary: str) -> str:
    return f"""
You are a management consultant with expertise in food-industry
trends and consumer behavior.

Based on the bakery sales data below, provide three specific
suggestions that staff can immediately apply in the workplace.

[Sales Data]
{sales_summary}

[Analysis Points]
- Recent food trends and changes in competing businesses
- Needs of different customer groups
- Potential for promotion on social media
- Hidden demand based on weekdays, seasons, and holidays

[Output Format]
Provide three bullet-point suggestions.
Keep each suggestion to one or two sentences.
"""

The number three appeared in two different contexts:

“Provide three specific suggestions”
“Provide three bullet-point suggestions”

I wanted to know whether my test was actually protecting both instructions.

So I deliberately changed the prompt.

Pattern 1: Change Only the Lower Instruction

I changed:

Provide three bullet-point suggestions

to:

Provide five bullet-point suggestions

The upper instruction still requested three suggestions.

Result

PASSED

The inconsistency passed through the test.

Pattern 2: Change Both Instructions to Five

I changed both instructions from three to five.

Result

FAILED

The test detected the change.

Pattern 3: Keep the Upper Instruction at Five and Restore the Lower One to Three

The prompt now contained:

Provide five specific suggestions

and:

Provide three bullet-point suggestions

Result

PASSED

Once again, the inconsistent prompt passed through the test.

Why the Original Test Failed to Protect the Prompt

The problem was this assertion:

assert "3 suggestions" in result

This test only asked:

Does the relevant text appear somewhere in the complete prompt?

As long as one matching phrase remained, the assertion passed.

It did not check whether both instructions were correct or whether they contradicted each other.

The test was verifying the existence of a fragment, not the meaning of the prompt structure.

This taught me an important distinction:

Writing a test and protecting the intended behavior are not the same thing.

A green test result may only mean that the assertion was too weak to detect the problem.

Improving the Test

Once I understood the problem, I changed the test to check each instruction in its full context.

def test_build_sales_prompt_includes_key_instructions():
    result = build_sales_prompt("Test data")

    assert "provide three specific suggestions" in result.lower()
    assert "provide three bullet-point suggestions" in result.lower()

Now the test protects both locations independently.

When I repeated the same deliberate changes, the inconsistent versions failed correctly.

AssertionError:
assert 'provide three bullet-point suggestions'
in '...provide five bullet-point suggestions...'

The failure message also became more useful.

It clearly showed:

What the test expected
What the prompt actually contained
Which instruction had changed

The test now had a more specific purpose.

What Mutation Testing Taught Me

I did not use a formal mutation-testing tool in this experiment.

However, the basic idea was similar:

Deliberately change the production code
Run the tests
Check whether the tests detect the change

If the tests still pass after an important behavior is broken, the test suite may not be protecting that behavior.

This was much more revealing than simply running the tests against correct code.

A test that passes only tells me:

The current code satisfies the current assertion.

A deliberately broken version can reveal:

Whether the assertion would notice the failure I actually care about.

Building an Automatic Inspection Gate with GitHub Actions

After improving the test, I created a GitHub Actions workflow so the tests would run automatically on every push and pull request.

I created:

.github/workflows/test.yml

with the following configuration:

name: Run Tests

on:
  push:
    branches:
      - main

  pull_request:
    branches:
      - main

jobs:
  test:
    runs-on: ubuntu-latest

    steps:
      - name: Check out repository
        uses: actions/checkout@v4

      - name: Set up Python
        uses: actions/setup-python@v5
        with:
          python-version: "3.12"

      - name: Install dependencies
        run: |
          pip install -r requirements.txt

      - name: Run pytest
        run: |
          pytest test_prompts.py -v

The intended flow was:

Push code to GitHub
        ↓
GitHub creates a clean virtual environment
        ↓
Install dependencies
        ↓
Run pytest
        ↓
Display success or failure

This was my software version of a pre-departure inspection gate.

The First GitHub Actions Run Failed

I pushed the workflow and waited for the result.

The job failed.

The error included:

exit code 127

This usually means that the requested command could not be found.

The cause became clear quickly.

I had installed pytest directly in my local environment:

pip install pytest

However, I had not added it to:

requirements.txt

GitHub Actions starts from a clean environment every time.

It only installs what is explicitly listed in the project configuration.

My local computer knew about pytest.

The CI environment did not.

This was another example of:

It works locally.

not meaning:

It can be reproduced anywhere.

Adding pytest to the Dependencies

I added pytest to requirements.txt.

echo "pytest" >> requirements.txt

Then I committed and pushed the change again.

This time, GitHub Actions displayed a green check mark.

Success

The automatic inspection gate was now running successfully.

What the Clean CI Environment Revealed

The local environment had accumulated packages from previous development work.

Because of that, missing dependency declarations could remain hidden.

GitHub Actions exposed the problem by creating a clean environment.

The difference looked like this:

Local environment:
pytest was already installed
        ↓
The command worked
        ↓
The missing dependency declaration stayed hidden

GitHub Actions:
Clean environment
        ↓
Install only requirements.txt
        ↓
pytest was missing
        ↓
Command not found

The failure was inconvenient, but useful.

It proved that CI is not only a place to run tests.

It also checks whether the project can be reconstructed from its declared configuration.

A Test Gate Is Only as Good as Its Inspection Items

At first, I imagined the GitHub Actions workflow itself as the safety gate.

However, this experiment showed that the gate does not decide what is dangerous.

The test code defines the inspection items.

If the test checks only:

assert "3 suggestions" in result

then the gate only confirms that the phrase appears somewhere.

It does not know that two related instructions must remain consistent.

In logistics terms, it would be like creating a vehicle inspection gate that checks only:

Are there tires on the truck?

while ignoring:

Tire pressure
Damage
Loose wheel nuts
Uneven wear
Whether all tires are the correct type

The existence of an inspection process does not guarantee the quality of the inspection.

The checklist itself matters.

Why Passing Tests Can Create False Confidence

Before deliberately breaking the prompt, I saw:

3 passed

and felt reassured.

However, the tests were only as strong as their assertions.

A passing test can create false confidence when:

The assertion is too broad
Only the happy path is tested
Important context is ignored
Multiple conditions are combined into one weak check
The test confirms output type but not output meaning
The test suite does not cover production behavior

This does not mean that tests are useless.

It means that tests are executable assumptions.

If the assumption is vague, the protection is vague.

More Robust Test Design

For this function, stronger tests could include several independent checks.

from prompts import build_sales_prompt


def test_build_sales_prompt_contains_input_data():
    sample_data = "Monday: 50 melon buns"

    result = build_sales_prompt(sample_data)

    assert sample_data in result


def test_build_sales_prompt_requests_three_suggestions():
    result = build_sales_prompt("Test data")

    assert "provide three specific suggestions" in result.lower()


def test_build_sales_prompt_requires_three_bullet_points():
    result = build_sales_prompt("Test data")

    assert "provide three bullet-point suggestions" in result.lower()


def test_build_sales_prompt_returns_string():
    result = build_sales_prompt("Test data")

    assert isinstance(result, str)


def test_build_sales_prompt_is_not_empty():
    result = build_sales_prompt("Test data")

    assert result.strip()

Separating the assertions makes the purpose of each test clearer.

When one fails, the test name also explains which contract was broken.

Production Deployment and the Inspection Gate

There is another important point.

Running GitHub Actions on every push does not automatically guarantee that Render waits for the tests before deploying.

The complete safe flow should ideally be:

Push or pull request
        ↓
Run automated tests
        ↓
Tests pass
        ↓
Merge or deploy

If the deployment platform automatically deploys every push independently of the GitHub Actions result, the application may still be deployed even when the test workflow fails.

That means a real deployment gate may require additional configuration, such as:

Deploying only from a protected branch
Requiring status checks before merging
Using pull requests instead of pushing directly to main
Triggering deployment only after the test job succeeds
Configuring branch protection rules

Creating the tests is one layer.

Connecting the test result to deployment permission is another layer.

A Warning I Also Noticed

During the workflow run, I also saw a warning related to the Node.js runtime used by a GitHub Action.

It did not affect the pass or fail result of my tests.

However, I recorded it as a future maintenance item.

CI configurations also age.

Even if the application code does not change, workflow actions and runtime environments may eventually require updates.

That is another reason to review automation logs instead of looking only at the final green check mark.

Summary

Here is what I learned from this work.

1. Writing a Test Is Not Enough

You cannot know what a test truly protects until you deliberately break the relevant behavior.

2. Weak String Checks Can Miss Contradictions

When the same value appears in multiple contexts, checking only whether the value exists somewhere is not enough.

The surrounding meaning must also be tested.

3. Local and CI Environments Are Different

A package installed only on a developer's machine does not exist in a clean CI environment.

Dependencies must be declared explicitly.

4. CI Failures Are Part of the Learning Process

An error such as:

exit code 127

provides information.

The failure itself becomes a troubleshooting clue.

5. A Green Check Mark Is Not Absolute Proof

It only proves that the configured jobs and assertions passed.

It does not prove that every important behavior has been tested.

6. Tests and Deployment Gates Are Separate Concerns

Running tests on push does not automatically mean a deployment platform will refuse failing code.

The workflow must be connected to branch and deployment rules.

Final Thoughts

This was the second project in a row where the result differed from what I expected.

In the previous experiment, I introduced a Docker multi-stage build and reduced the image by only 3 MB.

In this experiment, I introduced tests and discovered that the first version of those tests was weaker than I thought.

That is exactly why I believe these records are worth writing.

Real development rarely follows the perfect story:

Learn a technique
        ↓
Implement it once
        ↓
Everything works

The actual process is more like:

Learn a technique
        ↓
Implement it
        ↓
Misunderstand part of it
        ↓
Observe an unexpected result
        ↓
Investigate
        ↓
Improve the design

The unexpected parts often contain the most useful lessons.

This article is based on actual development logs.

sales_data_app Maintenance Series

Pre-Production Inspection Trilogy

Post-Deployment Maintenance Series

🍞 sales_data_app on GitHub

🚚 Three Coding Near Misses I Experienced as a Truck Driver

tosane932 — Thu, 30 Jul 2026 17:25:57 +0000

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

Introduction

Hello from Japan! 🇯🇵

I am tosane932, a professional truck driver working in logistics while teaching myself Python.

https://github.com/tosane932/sales_data_app

In workplace safety, there is a concept commonly known as Heinrich’s accident triangle.

It describes a relationship in which one serious accident is associated with many smaller incidents and an even larger number of near misses—situations where no accident occurred, but something dangerous almost happened.

Recently, while introducing pytest and GitHub Actions into sales_data_app, I experienced three coding near misses in a row.

Fortunately, none of them caused actual damage.

However, precisely because nothing serious happened, I believe they are worth reviewing.

This article records:

What happened
Why it happened
How I noticed it
What habit prevented it from becoming a real problem

🐧 Near Miss 1: Terminal Output Became File Names

I had left the output from a docker build command visible in my terminal so I could review it.

The log contained lines such as:

=> [internal] load build definition from Dockerfile
=> => naming to docker.io/library/sales-after:latest

Later, I casually ran:

git status

Unexpected files appeared.

Untracked files:
        =
        CACHED
        [internal]
        exporting
        naming
        resolve
        transferring
        unpacking

After thinking about the cause, I realized that characters such as > and => have a special meaning to the shell.

The > character is used for output redirection.

At some point while copying or pasting the log, part of the text appears to have been interpreted as a shell command.

Words surrounding the redirection symbols were then created as file names.

The actual damage was small.

Only eight empty junk files were created, and they had not yet been added to Git.

However, if I had run:

git add .

without checking first, those meaningless files could have entered the repository history.

Lesson

Always run git status before using a broad command such as git add ..

A single confirmation step can prevent unrelated files from being committed.

🐧 Near Miss 2: A New `.gitignore` Pattern Joined the Previous Line

I wanted to exclude backup files such as:

Dockerfile.bak

from Git tracking.

I added the following pattern to .gitignore:

echo "*.bak" >> .gitignore

When I checked the result, I found this:

sales_data_app_android_demo.mp4*.bak

The two patterns should have been on separate lines.

Instead, they had been joined together.

The cause was that the final line of .gitignore did not end with a newline.

When I appended text using:

echo "*.bak" >> .gitignore

the new pattern was attached directly to the existing final line.

Git then interpreted the entire string as one strange pattern:

sales_data_app_android_demo.mp4*.bak

As a result, neither the .mp4 file nor the .bak files were ignored correctly.

When I ran git status, the .mp4 file appeared as untracked even though I expected Git to ignore it.

That confirmed that the pattern was not working.

Lesson

After appending text with echo >>, always inspect the file using cat.

For example:

cat .gitignore

Do not assume that a text file already ends with a newline.

A safer way to guarantee separation is:

printf '\n*.bak\n' >> .gitignore

However, even when using a safer command, checking the final contents is still important.

🐧 Near Miss 3: I Almost Overwrote the Wrong File

This was the most frightening of the three incidents.

I was improving my pytest test code.

The file I needed to edit was:

test_prompts.py

At the same time, I also had the production file open:

prompts.py

Because the file names were similar, I accidentally overwrote the production prompts.py file with the contents of the test code.

Fortunately, immediately after saving it, I felt that something was wrong.

I noticed the mistake and restored the correct contents.

No actual damage occurred.

However, what would have happened if I had not noticed and had pushed the change?

Possible consequences included:

The Gemini prompt-generation logic used in production could have been replaced by test code
The application could have stopped working
The application could have behaved unexpectedly
The broken production file could have been deployed
GitHub Actions might still have passed

The final point is especially important.

The test file itself was not necessarily broken.

The mistake affected a production module that may not have been fully protected by the existing tests.

In other words, this was the type of error that could have passed through the test gate I had just introduced.

That is why this incident frightened me more than the others.

Lesson

When editing similarly named files, confirm the file name immediately before saving.

After editing, also use:

git status

and:

git diff

to confirm that only the intended files changed.

For example:

git status --short

shows a compact list of changed files.

git diff -- prompts.py

shows exactly what changed inside a specific file.

The important question is not only:

Does the code look correct?

It is also:

Did I modify the file I actually intended to modify?

Why Tests Alone Could Not Prevent This Mistake

Introducing automated tests improves safety, but tests cannot detect every type of human error.

Tests only protect behavior that has been explicitly covered.

If I accidentally replace a production file and the test suite does not verify that module correctly, the CI pipeline may still show a green result.

That means software safety requires several layers:

Automated tests
Static analysis
Code review
Git diffs
File-name confirmation
Deployment checks
Runtime logs

A passing test result does not mean:

Every possible mistake has been prevented.

It means:

The behaviors currently covered by the tests passed.

That distinction became very clear during this near miss.

🐧 Summary

None of these three incidents caused actual damage.

However, they all shared one important characteristic:

If I had noticed them slightly later, they could have become real problems.

Like near misses in logistics, these events should not end with:

Nothing happened, so it was fine.

The valuable questions are:

Why did the mistake occur?
Why did I notice it?
Which habit prevented actual damage?
How can I reduce the chance of repeating it?

In my case:

Near Miss 1

I noticed the junk files because I had made git status a habit.

Near Miss 2

I noticed the broken .gitignore pattern because I checked the file after appending to it.

Near Miss 3

I noticed the wrong overwrite because I felt something was unusual immediately after saving.

None of these required an advanced technical skill.

The difference was whether I performed one additional check.

Small Checks Are the Final Safety Barrier

In truck driving, major accidents are often prevented by ordinary actions:

Checking mirrors again
Confirming the loading area
Inspecting the cargo
Looking behind the vehicle
Leaving enough following distance
Performing a final check before departure

Software development has similar actions:

git status
git diff
Reading the target file name
Checking .gitignore
Reviewing staged changes
Running tests
Inspecting deployment logs

These actions are not exciting.

They do not look like major technical achievements.

However, they often form the final barrier between a small mistake and a production incident.

The more I learn programming, the more I feel that coding and truck driving share the same basic safety principle:

Do not depend on everything going perfectly.

Build habits that help you notice when something is wrong.

A Practical Pre-Commit Check

After these incidents, I began thinking of the following sequence as a simple pre-departure inspection.

git status --short
git diff
git diff --staged

Then I confirm:

Are only the intended files changed?
Are any strange untracked files present?
Did .gitignore work?
Did I accidentally modify a production file?
Are secrets or temporary files included?
Does the staged diff match the commit message?

Only after that do I commit.

git add <specific-files>
git commit -m "..."

Whenever possible, I prefer adding specific files instead of immediately using:

git add .

This takes a little longer, but it makes the cargo being committed more visible.

Final Thoughts

These incidents were harmless.

That does not make them meaningless.

A near miss is valuable because it reveals a weakness before the weakness causes real damage.

This time, I learned that:

Terminal text can be interpreted as shell syntax
Appending text can fail when the previous line has no newline
Similar file names can cause edits to land in the wrong place
Automated tests cannot replace basic confirmation
git status and git diff are simple but powerful safety checks

Writing code and driving a truck are different jobs.

However, the last line of defense against human error is often the same:

A series of small, unglamorous checks.

This article is based on actual work logs.

GitHub

https://github.com/tosane932/sales_data_app

Qiita

https://qiita.com/tosane932

DEV Community: tosane932

Existing DB Works, Empty DB Fails — Repairing a Broken Flask-Migrate History

Introduction

Investigating and Verifying the Problem with Codex

Development Environment

Discovering the Problem

Why Did the Existing Database Still Work?

Current Models

Product

DailySales

Repair Strategies I Considered

1. Add an Initial Migration Before the Existing Revision

2. Rewrite the Existing First Migration

3. Create a Conditional Migration

4. Use db.create_all() and stamp

The Approach I Chose

Implementing the Initial Migration

Table Creation Order Matters

Why I Did Not Put is_active in the Initial Migration

Changing the Existing Revision

Why I Did Not Add a server_default to quantity

Static Verification Before Touching a Database

Testing a Completely Empty Database

Schema Created from the Empty Database

products

daily_sales

Verifying an Existing Database

Running upgrade Against the Cloned Database

Comparing the Database Before and After upgrade

Running pytest

Git Diff

What I Learned

1. “The Existing Database Works” Does Not Mean the Migration History Is Correct

2. Migrations Are About the Path, Not Only the Current Schema

3. Modifying an Applied Revision Requires Careful Verification

4. Test Both Empty and Existing Databases

🔨 XSS Fixed in 47 Seconds? Using VS Code Codex as a Veteran Inspector from Another Department

Introduction

Installing Codex in VS Code

Rules I Set for Codex

Performing a Static Review of the Entire Project

High Severity

Medium Severity

Low Severity

Do Not Accept AI Findings Without Verification

Choosing Stored XSS as the First Fix

Asking Codex for the Design Before Allowing Changes

Allowing Codex to Implement the Fix

The Updated Code

Manual Verification

Normal Product Name

Product Name Containing an HTML Tag

Product Name Containing Japanese Text and Symbols

Final Check on the Render Demo from a Smartphone

Reviewing the Diff

Can Codex Be Used in Real Development?

A Veteran Inspector from Another Department

What AI Can Handle and What Humans Must Decide

Tasks That Are Easy to Delegate to Codex

Decisions That Still Belong to Humans

Conclusion

Related Links

GitHub

Live Demo

From Two AI Apps to a Vision for “Frontline Support AI”

Introduction

The Two AI Applications I Built

1. Bakery Sales Management System

Why I Built It

Technology Stack

2. News Summarization and Analysis App: Puoppo

Why I Built It

What I Realized After Placing the Two Apps Side by Side

Bakery Sales Management System

Puoppo

Connecting “Inside the Store” with “Outside the Store”

The SalesInsight AI Concept

SalesInsight AI

Why I Connect GitHub and Qiita

Personal Projects Become a Line, Not Just Separate Points

4. Use `db.create_all()` and `stamp`

Why I Did Not Put `is_active` in the Initial Migration

Why I Did Not Add a `server_default` to `quantity`

`products`

`daily_sales`

Running `upgrade` Against the Cloned Database

Comparing the Database Before and After `upgrade`

3. Updating `.gitignore` for the Current Development Environment

4. Using `.dockerignore` to Decide What Goes into Production

Old Folder Configuration in `config.py`

Old Directory-Creation Logic in `app.py`

9. A Red Cross from `grep` Did Not Mean an Error

14. Checking `working tree clean`

The `sales_data_app` README

8. `working tree clean` Is the Starting Condition for the Next Task