Database Schema
Database Supabase (PostgreSQL) par hosted hai. Complete schema file: supabase/complete-schema.sql
Tables Overview
users ─────────────────────────────────────────┐
profiles ────────────────────────────────────┤
│
categories ──→ products ←── suppliers │
│ │
↓ │
sale_items ←── sales │
│
activity_logs ───────────────────────────────┘
Tables Detail
users — Authentication
| Column | Type | Description |
|---|---|---|
| id | UUID | Primary key |
| username | TEXT | Unique login username |
| password | TEXT | Bcrypt hashed password |
| full_name | TEXT | Display name |
| role | TEXT | admin / staff / manager |
| TEXT | Optional | |
| phone | TEXT | Optional |
| is_active | BOOLEAN | Account active status |
Default user: admin / admin123
categories — Product Categories
| Column | Type | Description |
|---|---|---|
| id | UUID | Primary key |
| name | TEXT | Unique category name |
| description | TEXT | Optional |
Examples: Smartphones, Accessories, Tablets, Chargers
suppliers — Vendors
| Column | Type | Description |
|---|---|---|
| id | UUID | Primary key |
| name | TEXT | Supplier company name |
| contact_person | TEXT | Contact name |
| TEXT | ||
| phone | TEXT | Phone |
| address | TEXT | Address |
products — Inventory Items
| Column | Type | Description |
|---|---|---|
| id | UUID | Primary key |
| name | TEXT | Product name |
| brand | TEXT | e.g. Samsung, Apple |
| category_id | UUID | FK → categories |
| imei_serial | TEXT | IMEI or serial number |
| cost_price | DECIMAL | Purchase price |
| selling_price | DECIMAL | Sale price |
| stock_quantity | INTEGER | Current stock |
| min_stock_level | INTEGER | Low stock alert threshold |
| image_url | TEXT | Product image (optional) |
| supplier_id | UUID | FK → suppliers |
| description | TEXT | Notes |
sales — Invoices / Orders
| Column | Type | Description |
|---|---|---|
| id | UUID | Primary key |
| invoice_number | TEXT | Unique invoice ID (INV-00001) |
| total_amount | DECIMAL | Bill total |
| payment_method | TEXT | cash / card / mobile_payment |
| created_by | UUID | FK → profiles |
| created_at | TIMESTAMP | Sale date/time |
sale_items — Line Items per Sale
| Column | Type | Description |
|---|---|---|
| id | UUID | Primary key |
| sale_id | UUID | FK → sales (CASCADE delete) |
| product_id | UUID | FK → products |
| quantity | INTEGER | Units sold |
| unit_price | DECIMAL | Price per unit at sale time |
| total_price | DECIMAL | quantity × unit_price |
activity_logs — Audit Trail
| Column | Type | Description |
|---|---|---|
| id | UUID | Primary key |
| action_type | TEXT | create / update / delete / sale / settings_change |
| entity_type | TEXT | product / category / sale / order / shop / settings |
| entity_id | UUID | Related record ID |
| entity_name | TEXT | Human-readable name |
| description | TEXT | Log message |
| old_value | JSONB | Previous state |
| new_value | JSONB | New state |
| created_at | TIMESTAMP | When action happened |
Important Database Functions
verify_user_password(username, password)
Login ke liye — password bcrypt se verify karta hai aur user data return karta hai.
update_user_password(user_id, current_password, new_password)
Current password verify karke naya password set karta hai.
update_user_username(user_id, new_username)
Username update — duplicate check ke saath.
update_product_stock() (Trigger Function)
Jab sale_items mein naya record insert hota hai, automatically products.stock_quantity minus hota hai.
Automatic Behaviors (Triggers)
| Trigger | Event | Action |
|---|---|---|
update_stock_on_sale | INSERT on sale_items | Stock minus |
update_*_updated_at | UPDATE on tables | updated_at timestamp update |
Row Level Security (RLS)
Saari tables par RLS enabled hai. Current policies mostly allow all operations for application use. users table par authenticated role policies hain.
Order Delete Par Stock Restore
Jab order delete hota hai (Orders page se):
sale_itemsse products aur quantities fetch- Har product ka stock wapas increase
salesrecord delete (sale_items CASCADE se delete)