supabase-db

name: supabase-db description: Detailed rules for SQL schema, 3NF normalization, RLS, and Triggers.

Database Architecture (PostgreSQL)

1. 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.

2. Critical Automation (Triggers):

   • Create a PL/pgSQL function decrement_stock() that runs AFTER INSERT on order_items.
   • This prevents "over-selling" and handles the real-time stock updates required by the project.

3. Security (Row Level Security):

   • products: Public READ, Admin-only WRITE.
   • orders: Users can READ their own. Admins can READ all.
   • departments: Public READ.