AIAZHCore Tables: Products and Categories
The products table is the foundation. Fields include product_id (PK), sku (unique stock-keeping unit), barcode, product_name, description, category_id (FK), brand, unit_price, cost_price, tax_rate, unit_type (piece/kg/liter), is_active, and image_url. The categories table organizes products hierarchically: category_id (PK), name, parent_category_id (self-referencing FK for subcategories), and sort_order. A well-designed category tree supports departmental reporting.
Inventory and Stock Management
The inventory table tracks stock levels across multiple locations: inventory_id (PK), product_id (FK), store_id (FK), quantity_on_hand, quantity_committed (reserved for open orders), reorder_point, reorder_quantity, and last_count_date. A stock_movements table records every inventory change: movement_id (PK), product_id (FK), store_id (FK), movement_type (receipt/sale/return/adjustment/transfer), quantity, reference_document, movement_date, and performed_by. This audit trail is essential for identifying discrepancies during physical inventory counts.
Sales Transaction Schema
The sales schema follows a header-detail pattern. The sales_orders table (header) records: order_id (PK), store_id (FK), customer_id (FK), employee_id (FK — the cashier), order_date, order_time, subtotal, discount_total, tax_total, grand_total, payment_status, and order_status. The sales_order_items table (detail) captures each line item: order_item_id (PK), order_id (FK), product_id (FK), quantity, unit_price_at_sale, discount_percent, line_total, and returned_quantity. Storing the unit_price_at_sale is critical because product prices change over time.
Payment Processing
The payments table handles multiple tender types per transaction: payment_id (PK), order_id (FK), payment_method (cash/card/UPI/credit/voucher), payment_amount, reference_number (for card/UPI transactions), payment_date, and is_verified. For split payments, multiple rows link to the same order_id. A separate registers table manages cash drawer operations: register_id (PK), store_id (FK), opening_balance, closing_balance, opened_by, closed_by, opening_date, closing_date, expected_cash, and variance.
Customer Relationship Management
The customers table stores: customer_id (PK), first_name, last_name, phone, email, date_of_birth, anniversary_date, loyalty_points, total_spent, registration_date, and is_vip. A loyalty_transactions table tracks points earned and redeemed per order. For retail chains, a customer_addresses table supports delivery orders with multiple addresses. The customer table integrates with the sales schema through the customer_id FK in sales_orders, enabling features like purchase history and personalized offers.
Multi-Store Architecture
For chain operations, the stores table defines each location: store_id (PK), store_name, address, city, state, phone, tax_registration_number, and is_active. Transfer orders between stores use a transfer_orders table: transfer_id (PK), from_store_id (FK), to_store_id (FK), product_id (FK), quantity, status (requested/approved/shipped/received), request_date, and completion_date. This structure supports centralized reporting while allowing decentralized operations.
POS Database Best Practices
Use database transactions for every sale — if any line item insert fails, the entire order should roll back. Index the barcode column for sub-millisecond product lookups at the register. Implement optimistic locking (version number column) on inventory tables to prevent overselling during high-concurrency periods. Partition sales_order_items by order_date for fast historical queries. Always store monetary values in the smallest currency unit (cents/paisa) as integers to avoid floating-point rounding errors.
Full System Prompt — Copy & Paste
Complete POS System design prompt. Covers database, API, authentication, business logic, and frontend. Paste this into the Business Prompt Builder.
Design and build a complete Point of Sale (POS) system with the following components. Use Node.js/Express for the backend, PostgreSQL for the database, and React for the frontend. All API endpoints must use JWT authentication with role-based access control.
--- DATABASE SCHEMA ---
Use PostgreSQL with UUID primary keys, foreign key constraints, and indexes on all FK columns. Store monetary values as integers (smallest currency unit) to avoid floating-point errors.
1. categories: category_id (PK), name, parent_category_id (self-referencing FK for subcategories), sort_order, is_active, created_at
2. products: product_id (PK), sku (UNIQUE), barcode (UNIQUE), product_name, description, category_id (FK), brand, unit_price, cost_price, tax_rate, unit_type (ENUM: piece/kg/liter), is_active, image_url, created_at, updated_at
3. stores: store_id (PK), store_name, address, city, state, phone, tax_registration_number, is_active, created_at
4. inventory: inventory_id (PK), product_id (FK), store_id (FK), quantity_on_hand, quantity_committed, reorder_point, reorder_quantity, last_count_date, updated_at
5. stock_movements: movement_id (PK), product_id (FK), store_id (FK), movement_type (ENUM: receipt/sale/return/adjustment/transfer), quantity, reference_document, movement_date, performed_by
6. customers: customer_id (PK), first_name, last_name, phone, email, date_of_birth, anniversary_date, loyalty_points, total_spent, registration_date, is_vip, created_at, updated_at
7. sales_orders: order_id (PK), store_id (FK), customer_id (FK), employee_id (FK), order_date, order_time, subtotal, discount_total, tax_total, grand_total, payment_status (ENUM: pending/partial/paid), order_status (ENUM: open/completed/cancelled/returned), created_at
8. sales_order_items: order_item_id (PK), order_id (FK), product_id (FK), quantity, unit_price_at_sale, discount_percent, line_total, returned_quantity, created_at
9. payments: payment_id (PK), order_id (FK), payment_method (ENUM: cash/card/UPI/credit/voucher), payment_amount, reference_number, payment_date, is_verified, created_at
10. registers: register_id (PK), store_id (FK), opening_balance, closing_balance, opened_by, closed_by, opening_date, closing_date, expected_cash, variance, status (ENUM: open/closed)
11. loyalty_transactions: loyalty_id (PK), customer_id (FK), order_id (FK), points_change, transaction_type (ENUM: earn/redeem), transaction_date, notes
12. transfer_orders: transfer_id (PK), from_store_id (FK), to_store_id (FK), product_id (FK), quantity, status (ENUM: requested/approved/shipped/received), request_date, completion_date, requested_by
--- REST API ENDPOINTS ---
Products:
- POST /api/products — add product (generate barcode if empty)
- GET /api/products — list products (search by name/SKU/barcode, filter by category)
- GET /api/products/:id — get product details with current stock across stores
- PUT /api/products/:id — update product
- DELETE /api/products/:id — soft delete (set is_active = false)
Categories:
- POST /api/categories — add category
- GET /api/categories — list categories as tree structure
- PUT /api/categories/:id — update category
Inventory:
- GET /api/inventory — list inventory by store (filter by product, stock level)
- PUT /api/inventory/:id — update stock count (physical count adjustment)
- GET /api/inventory/low-stock — get products below reorder point
- POST /api/stock-movements — record manual stock adjustment
- GET /api/stock-movements — movement history for a product
Sales:
- POST /api/sales — create sale transaction (atomic: create order + items + decrement inventory + create payment)
- GET /api/sales — list sales (filter by date range, store, cashier, status)
- GET /api/sales/:id — get sale details with items and payments
- POST /api/sales/:id/return — process return (create credit note, increment inventory)
- GET /api/sales/daily-summary — end-of-day report (total sales, tax, items sold)
Payments:
- POST /api/payments — process payment (supports split payments: multiple methods per order)
- GET /api/payments — list payments (filter by method, date, store)
Customers:
- POST /api/customers — register customer
- GET /api/customers — list customers (search by name/phone/email)
- GET /api/customers/:id — get customer with purchase history
- PUT /api/customers/:id — update customer
- GET /api/customers/:id/loyalty — loyalty points history
Stores:
- POST /api/stores — add store
- GET /api/stores — list stores
- PUT /api/stores/:id — update store
Transfers:
- POST /api/transfers — request stock transfer between stores
- PUT /api/transfers/:id/approve — approve transfer
- PUT /api/transfers/:id/ship — mark as shipped (decrement source store inventory)
- PUT /api/transfers/:id/receive — mark as received (increment destination store inventory)
Registers:
- POST /api/registers/open — open register (set opening balance)
- PUT /api/registers/:id/close — close register (calculate expected cash, record variance)
- GET /api/registers/:id — register details with transaction summary
--- AUTHENTICATION & ROLES ---
JWT-based authentication with these roles:
- admin: full access — products, stores, inventory, sales, reports, user management
- cashier: process sales, view products, handle payments, print receipts, end-of-day close
- manager: all cashier permissions + inventory adjustments, reports, transfer approvals, register oversight
--- BUSINESS LOGIC ---
1. Atomic sale transaction: When a sale is created, use a database transaction to: (a) create sales_orders record, (b) create sales_order_items for each line, (c) decrement inventory quantity_on_hand for each product, (d) create payments record(s). If any step fails, rollback everything.
2. Optimistic locking on inventory: Add a version column to inventory. Before decrementing stock, verify version hasn't changed since read. If changed, retry with fresh read. Prevents overselling during concurrent transactions.
3. Split payments: A single sale can have multiple payment records (e.g., 70% cash + 30% card). The sum of payment_amounts must equal grand_total. Validate before marking order as paid.
4. Loyalty points: When a sale is completed, earn points = floor(grand_total / 10). Points can be redeemed at 1 point = 0.01 currency unit. Track all point transactions in loyalty_transactions.
5. Multi-store transfer workflow: requested -> approved -> shipped -> received. When shipped, decrement source inventory. When received, increment destination inventory. Both steps must be atomic.
6. Register reconciliation: On close, calculate expected_cash = opening_balance + cash_payments - cash_refunds. variance = actual_cash - expected_cash. Flag variances exceeding threshold.
--- FRONTEND PAGES ---
1. POS Register / Checkout Screen: Product search by barcode scan or name, add to cart, quantity adjust, discount per item or order, payment modal (cash/card/UPI split), receipt print preview.
2. Product Management: Product grid with image thumbnails, inline edit, barcode generator, category tree management, bulk import CSV.
3. Inventory Dashboard: Stock levels per store, low-stock alerts highlighted, stock movement history, physical count sheet, transfer request form.
4. Sales Reports: Daily/weekly/monthly sales charts, top-selling products, sales by cashier, sales by category, tax summary, refund report.
5. Customer Management: Customer list, purchase history, loyalty points balance, VIP flagging.
6. Multi-Store View: Store selector, per-store dashboard, transfer management, inter-store comparison reports.
7. Register Management: Open/close register, current session transactions, variance report, end-of-day reconciliation.Database Schema Prompt — Copy & Paste
Database-only prompt for generating the complete POS system schema.
Design a complete Point of Sale (POS) system database schema with the following tables and relationships. Use PostgreSQL syntax with UUID primary keys, foreign key constraints, and indexes on all FK columns.
1. categories: category_id (PK), name, parent_category_id (self-referencing FK for subcategories), sort_order, is_active, created_at
2. products: product_id (PK), sku (UNIQUE), barcode (UNIQUE), product_name, description, category_id (FK), brand, unit_price, cost_price, tax_rate, unit_type (ENUM: piece/kg/liter), is_active, image_url, created_at, updated_at
3. stores: store_id (PK), store_name, address, city, state, phone, tax_registration_number, is_active, created_at
4. inventory: inventory_id (PK), product_id (FK), store_id (FK), quantity_on_hand, quantity_committed, reorder_point, reorder_quantity, last_count_date, updated_at
5. stock_movements: movement_id (PK), product_id (FK), store_id (FK), movement_type (ENUM: receipt/sale/return/adjustment/transfer), quantity, reference_document, movement_date, performed_by (FK to staff)
6. customers: customer_id (PK), first_name, last_name, phone, email, date_of_birth, anniversary_date, loyalty_points, total_spent, registration_date, is_vip, created_at, updated_at
7. sales_orders: order_id (PK), store_id (FK), customer_id (FK), employee_id (FK), order_date, order_time, subtotal, discount_total, tax_total, grand_total, payment_status (ENUM: pending/partial/paid), order_status (ENUM: open/completed/cancelled/returned), created_at
8. sales_order_items: order_item_id (PK), order_id (FK), product_id (FK), quantity, unit_price_at_sale, discount_percent, line_total, returned_quantity, created_at
9. payments: payment_id (PK), order_id (FK), payment_method (ENUM: cash/card/UPI/credit/voucher), payment_amount, reference_number, payment_date, is_verified, created_at
10. registers: register_id (PK), store_id (FK), opening_balance, closing_balance, opened_by (FK to staff), closed_by (FK to staff), opening_date, closing_date, expected_cash, variance, status (ENUM: open/closed)
11. loyalty_transactions: loyalty_id (PK), customer_id (FK), order_id (FK), points_change, transaction_type (ENUM: earn/redeem), transaction_date, notes
12. transfer_orders: transfer_id (PK), from_store_id (FK to stores), to_store_id (FK to stores), product_id (FK), quantity, status (ENUM: requested/approved/shipped/received), request_date, completion_date, requested_by (FK to staff)
Add the following indexes: idx_products_barcode ON products(barcode), idx_products_category ON products(category_id), idx_inventory_product_store ON inventory(product_id, store_id), idx_sales_customer ON sales_orders(customer_id), idx_sales_store ON sales_orders(store_id), idx_sales_date ON sales_orders(order_date), idx_order_items_order ON sales_order_items(order_id), idx_payments_order ON payments(order_id), idx_stock_movements_product ON stock_movements(product_id). Add CHECK constraints for positive quantities and amounts, and unique constraint on products(sku).