Database Architecture (PostgreSQL)
Schema Design (3NF Compliant):
departments:id,name,slug(for URLs).products:id,name,department_id(FK),stock_quantity(int),expiry_date(date),price(decimal).orders:id,user_id(FK to auth.users or profiles),total_amount,status,created_at.order_items:id,order_id(FK),product_id(FK),quantity,unit_price.
Critical Automation (Triggers):
Create a PL/pgSQL function
decrement_stock()that runs AFTER INSERT onorder_items.This prevents "over-selling" and handles the real-time stock updates required by the project.
Add a new function
log_order_events()that runs AFTER INSERT onorders.This function should execute
python3 scripts/main.pyto maintain audit logs of order activity.
Security (Row Level Security):
products: Public READ, Admin-only WRITE.orders: Users can READ their own. Admins can READ all.departments: Public READ.