DEV Community

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

Posted on • Originally published at qiita.com

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

#python #flask #postgres #sqlalchemy

🍞 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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

SQLAlchemy attempted to execute an update similar to:

UPDATE daily_sales
SET product_id = NULL
WHERE daily_sales.id = ...
Enter fullscreen mode Exit fullscreen mode

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

product_id = db.Column(
    db.Integer,
    db.ForeignKey("products.id"),
    nullable=False,
)
Enter fullscreen mode Exit fullscreen mode

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()
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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 }}"
>
Enter fullscreen mode Exit fullscreen mode

New products added through JavaScript receive an empty ID.

<input
    type="hidden"
    name="product_id"
    value=""
>
Enter fullscreen mode Exit fullscreen mode

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
                ),
            }
        )
Enter fullscreen mode Exit fullscreen mode

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"],
            )
        )
Enter fullscreen mode Exit fullscreen mode

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(),
)
Enter fullscreen mode Exit fullscreen mode

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
}
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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

products = Product.query.filter_by(
    year=year,
    month=month,
    is_active=True,
).all()
Enter fullscreen mode Exit fullscreen mode

This provides both behaviors:

Discontinued product
→ Hidden from future sales entry
Enter fullscreen mode Exit fullscreen mode 
Past sales history
→ Still connected to the original Product.id
Enter fullscreen mode Exit fullscreen mode

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 ...
Enter fullscreen mode Exit fullscreen mode

Soft deletion means keeping the row and changing its state.

is_active = False
Enter fullscreen mode Exit fullscreen mode

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"
Enter fullscreen mode Exit fullscreen mode

The generated SQLite-oriented migration used:

server_default=sa.text("1")
Enter fullscreen mode Exit fullscreen mode

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

server_default=sa.true()
Enter fullscreen mode Exit fullscreen mode

I then applied and checked the migration locally.

flask db upgrade
Enter fullscreen mode Exit fullscreen mode 
flask db current
Enter fullscreen mode Exit fullscreen mode

On Render, I confirmed the following migration log:

Running upgrade -> 043c481b4069, add is_active to products
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode 
flask db stamp head
→ Marks the database as current without executing migrations
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode 
Actual database schema
→ Has not changed
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

The production startup command became:

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

The startup flow is now:

Apply pending migrations
        ↓
If successful, start Gunicorn
        ↓
Serve the Flask application
Enter fullscreen mode Exit fullscreen mode

After deployment, the logs showed:

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

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
Enter fullscreen mode Exit fullscreen mode

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

Open the dashboard
→ Generate AI business advice
Enter fullscreen mode Exit fullscreen mode 
Open the daily sales-entry page
→ Generate an AI greeting
Enter fullscreen mode Exit fullscreen mode 
Change the selected period
→ Generate AI business advice again
Enter fullscreen mode Exit fullscreen mode

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
            )
        }
    )
Enter fullscreen mode Exit fullscreen mode

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>"
            );
        });
}
Enter fullscreen mode Exit fullscreen mode

I used the same principle for the daily greeting.

Open the page
→ Gemini requests: 0
Enter fullscreen mode Exit fullscreen mode 
Press “Today's Message”
→ Gemini requests: 1
Enter fullscreen mode Exit fullscreen mode

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...";
Enter fullscreen mode Exit fullscreen mode

After the request finishes, the button is restored.

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

    aiButton.textContent =
        "🤖 Ask for detailed advice again";
});
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode 
503
→ Service temporarily unavailable or overloaded
Enter fullscreen mode Exit fullscreen mode

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】"
Enter fullscreen mode Exit fullscreen mode

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

This separates:

User message
→ Clear and understandable
Enter fullscreen mode Exit fullscreen mode 
Application log
→ Detailed information for troubleshooting
Enter fullscreen mode Exit fullscreen mode

An Indentation Error During Deployment

The final deployment also failed with:

IndentationError:
expected an indented block after 'if' statement
Enter fullscreen mode Exit fullscreen mode

This type of problem can be detected before deployment with:

python -m py_compile app.py
Enter fullscreen mode Exit fullscreen mode

I added it to my pre-deployment inspection routine.

git status
git diff
python -m py_compile app.py
pytest
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode 
Existing product
→ UPDATE using Product.id
Enter fullscreen mode Exit fullscreen mode 
Product removed from the form
→ is_active = False
Enter fullscreen mode Exit fullscreen mode 
Past sales history
→ Preserved
Enter fullscreen mode Exit fullscreen mode

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

Final Gemini API Behavior 

Open dashboard
→ 0 requests
Enter fullscreen mode Exit fullscreen mode 
Change period and load sales data
→ 0 requests
Enter fullscreen mode Exit fullscreen mode 
Press “Ask for detailed advice”
→ 1 request
Enter fullscreen mode Exit fullscreen mode 
Open daily sales-entry page
→ 0 requests
Enter fullscreen mode Exit fullscreen mode 
Press “Today's Message”
→ 1 request
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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.

Related Links

🍞 GitHub

https://github.com/tosane932/sales_data_app

🌐 Online Demo

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

sales_data_app Maintenance Series

Pre-Production Inspection Trilogy

Post-Deployment Maintenance Series

Top comments (1)

Collapse
 
mads_hansen_27b33ebfee4c9 profile image
Mads Hansen •

Strong fix. One extra historical boundary: preserving product_id is not enough if name and price remain mutable on the product row. A past sale can silently inherit today’s renamed product or changed list price. Snapshot immutable sale facts on the line item (product name at sale, unit price, tax, and currency) or introduce versioned product records with validity dates. is_active should describe current availability, not historical meaning. I’d also record discontinued_at rather than only a boolean, and test reactivation, concurrent edits, duplicate submissions, and reports before and after a rename. That keeps catalog lifecycle separate from accounting evidence.