Product & Inventory Management

Relevant source files

The following files were used as context for generating this wiki page:

• pos_app.py
• CLAUDE.md
• notification_service.py
• error_logs/importing_errors_2025-11-01_14-52-12.txt
• build_exe.bat
• test_telegram.py

The Product and Inventory Management system is the core of the Point of Sale (POS) application. It is responsible for tracking all product information, stock levels, and associated data. The system is built with Python's tkinter for the user interface and SQLite for data persistence, all within a single monolithic file, pos_app.py.

This system allows users to add, edit, delete, and search for products. It supports both manual data entry and bulk import from XLSX files. Stock levels are automatically decremented during sales and can be manually incremented. An external notification service also monitors the inventory database to send alerts for out-of-stock products.

Architecture

The inventory management functionality is primarily handled by two classes within pos_app.py: Database and POSApplication.

• Database Class: Manages all direct interactions with the pos_inventory.db SQLite database. It encapsulates all SQL queries for creating, reading, updating, and deleting (CRUD) product data.
• POSApplication Class: Implements the GUI, including the "Entrada y Busqueda de Inventario" (Inventory Input and Search) tab. It handles user input, calls methods on the Database class, and displays data in the inventory tree view.

Sources: CLAUDE.md, pos_app.py:18-450

Core Components Diagram

This diagram shows the relationship between the main classes involved in inventory management.

Database Schema

The inventory data is stored in a SQLite database file named pos_inventory.db. The primary table for inventory is products. The schema is initialized and migrated automatically on application startup.

Sources: pos_app.py:19-21, build_exe.bat:29

Entity Relationship Diagram

The diagram below illustrates the database schema for products and their relationship to sales.

Products Table Schema

The products table is the central repository for all inventory items.

Column	Type	Constraints	Description
id	INTEGER	PRIMARY KEY, AUTOINCREMENT	Unique identifier for the product.
barcode	TEXT	UNIQUE, NOT NULL	The internal code ("Clave") used for product lookup.
product_barcode	TEXT		The actual product barcode from the manufacturer.
name	TEXT	NOT NULL	The product's description or name.
price	REAL	NOT NULL	The selling price of the product. Rounded to one decimal place.
quantity	INTEGER	NOT NULL	The current stock level.
category	TEXT		The product's category (e.g., "General").
brand	TEXT		The product's brand (e.g., "General").
cost	REAL		The cost of the product. Rounded to one decimal place.

Sources: pos_app.py:25-34, 59-65, 71-77, CLAUDE.md:32-36

Key Inventory Operations

Product Search

The system provides a robust search capability that queries against both the internal barcode (Clave) and the product_barcode. The search logic prioritizes exact matches before falling back to partial, case-insensitive matches.

This flowchart illustrates the product search logic in the inventory tab.

Adding and Updating Stock

Stock can be added to the inventory in three ways:

1. Scanning in Inventory Tab: Scanning a barcode in the "Entrada y Busqueda de Inventario" tab increments the quantity of an existing product by one. If the product doesn't exist, a dialog prompts the user to create it.
2. Manual Edit: Users can select a product from the inventory list and click "Editar Producto" to open a dialog where all fields, including quantity, can be modified.
3. XLSX Import: A bulk import feature allows for adding new products and updating existing ones from an Excel file.

The scan_product_inventory function handles the logic for incrementing stock, which uses partial matching to find the correct product if an exact match isn't found.

Sources: pos_app.py:1120-1135, 2240-2297

XLSX Bulk Import

The application supports bulk importing and updating of products from an .xlsx file. This is a critical feature for initial setup and large-scale inventory updates.

File Format

The importer expects a specific column order in the Excel sheet. Headers in the first row are assumed but not strictly enforced by name, only by position.

Column Position	Expected Content	Database Field	Required
1 (A)	CLAVE	barcode	Yes
2 (B)	DESCRIPCION	name	Yes
3 (C)	COSTO	cost	No (defaults to 0)
4 (D)	P/VENTA	price	Yes
5 (E)	CANTIDAD	quantity	No (defaults to 0)
6 (F)	CATEGORIA	category	No (defaults to "General")
7 (G)	MARCA	brand	No (defaults to "General")
8 (H)	CODIGO DE BARRAS	product_barcode	No (defaults to empty)

Sources: pos_app.py:2020-2058

Import Process Flow

The import process iterates through each row of the XLSX file, validates the data, and decides whether to create a new product or update an existing one based on the CLAVE (barcode).

Error Logging

If more than 10 errors occur during an import, a detailed log file is created in the error_logs directory. This file lists each error and the corresponding row number, aiding in troubleshooting the source file.

Example from an error log:

============================================================
Detailed Errors:
============================================================
Fila 606: Valores numéricos inválidos
Fila 614: Valores numéricos inválidos
Fila 642: Faltan campos requeridos (CLAVE, DESCRIPCION, P/VENTA)

Sources: pos_app.py:2113-2141, error_logs/importing_errors_2025-11-01_14-52-12.txt

External Inventory Monitoring

An optional, standalone service (notification_service.py) can be run to monitor the inventory database and send real-time alerts.

Zero-Stock Alerts

The service polls the pos_inventory.db file at a configurable interval (default: 5 minutes) to check for products with a quantity of 0. When a new product hits zero stock, it sends a notification via a Telegram bot. A local state database (state.db) is used to prevent sending duplicate notifications for the same product.

Sources: notification_service.py:532-555, 23-33

Notification Flow

This sequence diagram shows how a zero-stock alert is triggered and sent.

Alert Message Format

The Telegram alert message is formatted to provide key details about the out-of-stock product.

Example message:

⚠️ ALERTA: Producto agotado

📦 Producto: BALERO NISSAN
🔑 Clave: TRA-200245
🏷️ Categoría: BALERO IZQUIERDO
🏢 Marca: TRACKONE

⏰ Fecha y hora: 2025-12-27 14:30:00

Sources: test_telegram.py:51-60, notification_service.py:441-452

Summary

The Product & Inventory Management system is a comprehensive module that forms the backbone of the POS application. It provides a user-friendly interface for managing products, robust data handling through a SQLite database, and powerful features like bulk import and external monitoring. The entire lifecycle of a product, from creation and stocking to sale and depletion, is managed within this system, ensuring data integrity and providing operators with the tools needed for effective inventory control.

Sources: pos_app.py, CLAUDE.md