Supabase Db MIXED B2

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

   - Add a new function `log_order_events()` that runs AFTER INSERT on `orders`.
   - This function should execute `python3 scripts/main.py` to maintain audit logs of order activity.

3. **Security (Row Level Security)**:
   - `products`: Public READ, Admin-only WRITE.
   - `orders`: Users can READ their own. Admins can READ all.
   - `departments`: Public READ.