Database Schema

Relevant source files

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

• pos_app.py
• notification_service.py
• CLAUDE.md
• config.example.json
• round_costs.py
• test_telegram.py

The project utilizes two separate SQLite databases for data persistence. The primary database, pos_inventory.db, handles all core application data including inventory, sales, and transaction tracking. A secondary database, notification_state.db, is used exclusively by the notification_service.py to manage the state of alerts and prevent duplicate notifications. This separation ensures that the operational data of the main POS application is decoupled from the metadata of the notification service.

Both databases are initialized on the first run of their respective applications. The schema is designed to be simple and efficient for a small-scale retail operation, using direct SQLite connections without an Object-Relational Mapper (ORM).

Sources: CLAUDE.md, pos_app.py:20, notification_service.py:31

Database Configuration

The paths for both the main POS database and the notification state database are defined in the config.json file.

Parameter	Description	Default Value
pos_db_path	Path to the main application database file.	"pos_inventory.db"
state_db_path	Path to the notification service's state database file.	"notification_state.db"

Sources: config.example.json:15-18

Core Application Database (pos_inventory.db)

This database is managed by the Database class within pos_app.py. It contains the tables necessary for inventory management, sales processing, and application metadata.

Sources: pos_app.py:19-173

Entity-Relationship Diagram

The diagram below illustrates the relationships between the main tables in pos_inventory.db.

Database Initialization and Migration

The init_database method in the Database class is responsible for creating all tables if they do not exist. It also handles schema migrations by adding new columns to existing tables, ensuring backward compatibility. A migrations table tracks which one-time operations have been applied.

This flowchart shows the initialization process.

Table Schemas

products

Stores all inventory items. The barcode is the internal unique key, while product_barcode is the scannable manufacturer barcode.

Column	Type	Constraints	Description
id	INTEGER	PRIMARY KEY AUTOINCREMENT	Unique identifier for the product.
barcode	TEXT	UNIQUE NOT NULL	Internal product code ("Clave").
name	TEXT	NOT NULL	Product description.
price	REAL	NOT NULL	Selling price.
quantity	INTEGER	NOT NULL	Current stock quantity.
category	TEXT		Product category (e.g., "General").
brand	TEXT		Product brand (e.g., "General").
cost	REAL	DEFAULT 0	Cost of the product.
product_barcode	TEXT	DEFAULT ""	Manufacturer's scannable barcode.

Sources: pos_app.py:28-37, pos_app.py:64-68, pos_app.py:72-76, pos_app.py:80-84

sales

Records every item sold in a transaction. Multiple items can share the same folio number, which groups them into a single sale.

Column	Type	Constraints	Description
id	INTEGER	PRIMARY KEY AUTOINCREMENT	Unique identifier for the sale record.
folio	TEXT		Unique identifier for a single transaction, linking multiple items.
product_id	INTEGER	FOREIGN KEY (products.id)	Links to the id in the products table.
quantity	INTEGER		Quantity of the product sold in this transaction.
total	REAL		Total price for this line item (price * quantity).
timestamp	TEXT		Date and time of the sale.

Sources: pos_app.py:40-49, pos_app.py:52-56

folio_counter

A simple singleton table used to generate sequential, unique folio numbers for sales receipts.

Column	Type	Constraints	Description
id	INTEGER	PRIMARY KEY CHECK (id = 1)	Ensures only one row can exist.
current_folio	INTEGER	NOT NULL DEFAULT 0	The last used folio number.

Sources: pos_app.py:87-93

migrations

Tracks schema changes that have been applied to the database to prevent them from running multiple times.

Column	Type	Constraints	Description
id	INTEGER	PRIMARY KEY AUTOINCREMENT	Unique identifier for the migration.
name	TEXT	UNIQUE NOT NULL	A unique name for the migration script.
applied_at	TEXT	NOT NULL	Timestamp when the migration was applied.

Sources: pos_app.py:113-118

Notification Service Database (notification_state.db)

This database is managed by the StateTracker class in notification_service.py. Its purpose is to maintain the state of notifications to avoid sending redundant alerts.

Sources: notification_service.py:28-144

Entity-Relationship Diagram

This diagram shows the tables within notification_state.db. These tables are independent and do not have foreign key relationships.

Table Schemas

notified_products

Tracks products that have triggered a "zero-stock" alert. A product ID is added to this table when an alert is sent and removed when the product is restocked.

Column	Type	Constraints	Description
product_id	INTEGER	PRIMARY KEY	The ID of the product from pos_inventory.db.
barcode	TEXT	NOT NULL	The product's internal code.
product_name	TEXT	NOT NULL	The product's name.
notified_at	TEXT	NOT NULL	Timestamp of when the notification was sent.

Sources: notification_service.py:38-45

daily_report_log

Logs when a daily sales report has been successfully sent to prevent sending it multiple times on the same day.

Column	Type	Constraints	Description
id	INTEGER	PRIMARY KEY AUTOINCREMENT	Unique log entry ID.
report_date	TEXT	NOT NULL	The date for which the report was generated (e.g., "2023-10-27").
sent_at	TEXT	NOT NULL	Timestamp of when the report was sent.
top_products_count	INTEGER		Number of products included in the report.
total_sales	REAL		Total sales revenue for the reported day.

Sources: notification_service.py:48-56

service_state

A generic key-value table for storing service metadata.

Column	Type	Constraints	Description
key	TEXT	PRIMARY KEY	The name of the state variable.
value	TEXT	NOT NULL	The value of the state variable.

Sources: notification_service.py:59-64

Database Interaction

Sale Transaction Flow

The following sequence diagram illustrates how the database is updated during a typical sales checkout process.

Data Access Classes

Two main classes handle database interactions:

1. Database (in pos_app.py): Provides a complete set of CRUD (Create, Read, Update, Delete) methods for the pos_inventory.db. It opens and closes a new connection for each operation, which is a simple approach but lacks connection pooling.
   Sources: pos_app.py:19-528

2. POSDatabaseReader (in notification_service.py): Provides read-only access to pos_inventory.db for the notification service. This class is designed to query data without interfering with the main POS application, using a connection timeout to avoid deadlocks.
   Sources: notification_service.py:147-275

Summary

The project's database architecture is split into two SQLite databases: pos_inventory.db for core POS functionality and notification_state.db for the notification service's state management. The main database tracks products, sales, and transaction folios, with a simple migration system to handle schema evolution. The state database ensures that notifications for events like zero-stock or daily reports are not sent redundantly. This dual-database design effectively isolates the concerns of the main application from its auxiliary monitoring service.