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

A developer building a bakery sales management application with Flask, PostgreSQL, and the Gemini API implemented soft deletes to preserve sales history when editing product masters, and reduced Gemini API usage. The developer, a self-taught programmer with about 135 hours of study, encountered a NotNullViolation error when deleting products referenced by sales records, and solved it by updating existing products by primary key and inserting only new ones. The update also cut Gemini API calls by optimizing the flow.

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 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: php 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. python @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. js 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. js .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://github.com/tosane932/sales data app https://bakery-salesdata.onrender.com/ https://bakery-salesdata.onrender.com/