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:
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.
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
.
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.
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.
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.
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:
Deleting the row would make that history difficult or impossible to interpret safely.
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.
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.
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
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.
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.
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:
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
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.
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.
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.
When primary keys are different, the database treats the rows as different products.
Updates should be based on stable IDs, not only visible names.
For products connected to sales history, soft deletion was more appropriate than physical deletion.
Generate
β Review
β Apply
Automatically generated migrations still need human review, especially when both SQLite and PostgreSQL are involved.
Calling the AI only when requested improved:
python -m py_compile app.py
A one-line indentation error can prevent the production application from starting.
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:
Only after checking all of those areas did the update feel complete.
My next step is to expand pytest coverage for:
Thank you for reading.
https://github.com/tosane932/sales_data_app
https://bakery-salesdata.onrender.com/