# 04-database-schema.md

# Database Schema Document
Version: 1.0  
Project Type: Single Vendor eCommerce  
Framework: Laravel + MySQL / MariaDB  
Architecture Goal: Fast + Scalable + Clean + AI Friendly

---

# 1. PURPOSE OF THIS DOCUMENT

This file converts ER Diagram into actual database schema.

Contains:

- Table names
- Columns
- Data types
- Nullability
- Default values
- Unique rules
- Indexes
- Foreign keys

Goal:

Use this file to generate Laravel migrations accurately.

---

# 2. DATABASE ENGINE RECOMMENDATION

## Engine

Use:

- InnoDB

## Charset

Use:

- utf8mb4

## Collation

Use:

- utf8mb4_unicode_ci

---

# 3. NAMING RULES

## Tables

snake_case plural

Examples:

- products
- order_items
- courier_shipments

## Columns

snake_case

Examples:

- created_at
- customer_phone
- low_stock_alert

---

# 4. CORE TABLE SCHEMA

---

# admins

| Column | Type | Null | Default | Index |
|---|---|---|---|---|
| id | BIGINT UNSIGNED | No | AI | PK |
| role_id | BIGINT UNSIGNED | No | - | IDX |
| name | VARCHAR(150) | No | - | |
| email | VARCHAR(190) | No | - | UNIQUE |
| phone | VARCHAR(30) | Yes | NULL | IDX |
| password | VARCHAR(255) | No | - | |
| avatar | VARCHAR(255) | Yes | NULL | |
| status | TINYINT(1) | No | 1 | IDX |
| last_login_at | TIMESTAMP | Yes | NULL | |
| last_login_ip | VARCHAR(45) | Yes | NULL | |
| remember_token | VARCHAR(100) | Yes | NULL | |
| created_at | TIMESTAMP | Yes | NULL | |
| updated_at | TIMESTAMP | Yes | NULL | |

FK:

- role_id → roles.id

---

# roles

| Column | Type |
|---|---|
| id | BIGINT UNSIGNED PK |
| name | VARCHAR(100) |
| slug | VARCHAR(120) UNIQUE |
| created_at | TIMESTAMP NULL |
| updated_at | TIMESTAMP NULL |

---

# permissions

| Column | Type |
|---|---|
| id | BIGINT UNSIGNED PK |
| name | VARCHAR(150) |
| slug | VARCHAR(150) UNIQUE |
| module | VARCHAR(100) |
| created_at | TIMESTAMP NULL |
| updated_at | TIMESTAMP NULL |

---

# role_permission

| Column | Type |
|---|---|
| id | BIGINT UNSIGNED PK |
| role_id | BIGINT UNSIGNED IDX |
| permission_id | BIGINT UNSIGNED IDX |

UNIQUE:

(role_id, permission_id)

---

# categories

| Column | Type |
|---|---|
| id | BIGINT UNSIGNED PK |
| parent_id | BIGINT UNSIGNED NULL IDX |
| name | VARCHAR(150) |
| slug | VARCHAR(180) UNIQUE |
| icon | VARCHAR(255) NULL |
| image | VARCHAR(255) NULL |
| description | TEXT NULL |
| sort_order | INT | DEFAULT 0 |
| status | TINYINT(1) DEFAULT 1 |
| meta_title | VARCHAR(190) NULL |
| meta_description | VARCHAR(255) NULL |
| created_at | TIMESTAMP NULL |
| updated_at | TIMESTAMP NULL |

Self FK:

- parent_id → categories.id

---

# brands

| Column | Type |
|---|---|
| id | BIGINT UNSIGNED PK |
| name | VARCHAR(150) |
| slug | VARCHAR(180) UNIQUE |
| logo | VARCHAR(255) NULL |
| description | TEXT NULL |
| status | TINYINT(1) DEFAULT 1 |
| meta_title | VARCHAR(190) NULL |
| meta_description | VARCHAR(255) NULL |
| created_at | TIMESTAMP NULL |
| updated_at | TIMESTAMP NULL |

---

# products

| Column | Type |
|---|---|
| id | BIGINT UNSIGNED PK |
| category_id | BIGINT UNSIGNED IDX |
| brand_id | BIGINT UNSIGNED NULL IDX |
| name | VARCHAR(190) |
| slug | VARCHAR(220) UNIQUE |
| sku | VARCHAR(120) UNIQUE |
| barcode | VARCHAR(120) NULL IDX |
| short_description | TEXT NULL |
| description | LONGTEXT NULL |
| cost_price | DECIMAL(12,2) DEFAULT 0 |
| price | DECIMAL(12,2) |
| compare_price | DECIMAL(12,2) NULL |
| weight | DECIMAL(10,2) NULL |
| tax_type | ENUM('fixed','percent') NULL |
| tax_amount | DECIMAL(10,2) DEFAULT 0 |
| min_qty | INT DEFAULT 1 |
| stock_qty | INT DEFAULT 0 |
| low_stock_alert | INT DEFAULT 5 |
| is_featured | TINYINT(1) DEFAULT 0 |
| is_best_seller | TINYINT(1) DEFAULT 0 |
| is_trending | TINYINT(1) DEFAULT 0 |
| is_new_arrival | TINYINT(1) DEFAULT 0 |
| has_variant | TINYINT(1) DEFAULT 0 |
| status | TINYINT(1) DEFAULT 1 |
| meta_title | VARCHAR(190) NULL |
| meta_description | VARCHAR(255) NULL |
| created_at | TIMESTAMP NULL |
| updated_at | TIMESTAMP NULL |
| deleted_at | TIMESTAMP NULL |

FK:

- category_id → categories.id
- brand_id → brands.id

Indexes:

- status
- category_id,status
- created_at

---

# product_images

| Column | Type |
|---|---|
| id | BIGINT UNSIGNED PK |
| product_id | BIGINT UNSIGNED IDX |
| image | VARCHAR(255) |
| sort_order | INT DEFAULT 0 |
| is_primary | TINYINT(1) DEFAULT 0 |
| created_at | TIMESTAMP NULL |

---

# attributes

| Column | Type |
|---|---|
| id | BIGINT UNSIGNED PK |
| name | VARCHAR(100) |
| slug | VARCHAR(120) UNIQUE |

---

# attribute_values

| Column | Type |
|---|---|
| id | BIGINT UNSIGNED PK |
| attribute_id | BIGINT UNSIGNED IDX |
| value | VARCHAR(100) |
| slug | VARCHAR(120) |

UNIQUE:

(attribute_id, slug)

---

# product_variants

| Column | Type |
|---|---|
| id | BIGINT UNSIGNED PK |
| product_id | BIGINT UNSIGNED IDX |
| sku | VARCHAR(120) UNIQUE |
| barcode | VARCHAR(120) NULL |
| variant_name | VARCHAR(190) |
| cost_price | DECIMAL(12,2) DEFAULT 0 |
| price | DECIMAL(12,2) |
| stock_qty | INT DEFAULT 0 |
| weight | DECIMAL(10,2) NULL |
| image | VARCHAR(255) NULL |
| status | TINYINT(1) DEFAULT 1 |
| created_at | TIMESTAMP NULL |
| updated_at | TIMESTAMP NULL |

---

# orders

| Column | Type |
|---|---|
| id | BIGINT UNSIGNED PK |
| invoice_no | VARCHAR(50) UNIQUE |
| customer_id | BIGINT UNSIGNED NULL IDX |
| customer_name | VARCHAR(150) |
| customer_phone | VARCHAR(30) IDX |
| customer_alt_phone | VARCHAR(30) NULL |
| customer_email | VARCHAR(190) NULL |
| shipping_address | TEXT |
| area_id | BIGINT UNSIGNED NULL IDX |
| city_id | BIGINT UNSIGNED NULL IDX |
| note | TEXT NULL |
| subtotal | DECIMAL(12,2) |
| discount_amount | DECIMAL(12,2) DEFAULT 0 |
| coupon_id | BIGINT UNSIGNED NULL IDX |
| shipping_charge | DECIMAL(12,2) DEFAULT 0 |
| tax_amount | DECIMAL(12,2) DEFAULT 0 |
| total_amount | DECIMAL(12,2) |
| payment_method | VARCHAR(50) DEFAULT 'cod' |
| payment_status | ENUM('unpaid','paid','partial','refunded') DEFAULT 'unpaid' |
| order_status | ENUM('new','pending','confirmed','packed','shipped','delivered','cancelled','returned') DEFAULT 'new' |
| source | VARCHAR(50) NULL |
| ip_address | VARCHAR(45) NULL |
| user_agent | VARCHAR(255) NULL |
| ordered_at | TIMESTAMP NULL |
| confirmed_at | TIMESTAMP NULL |
| packed_at | TIMESTAMP NULL |
| shipped_at | TIMESTAMP NULL |
| delivered_at | TIMESTAMP NULL |
| cancelled_at | TIMESTAMP NULL |
| returned_at | TIMESTAMP NULL |
| created_at | TIMESTAMP NULL |
| updated_at | TIMESTAMP NULL |

Indexes:

- customer_phone
- order_status
- created_at
- ordered_at

---

# order_items

| Column | Type |
|---|---|
| id | BIGINT UNSIGNED PK |
| order_id | BIGINT UNSIGNED IDX |
| product_id | BIGINT UNSIGNED IDX |
| product_variant_id | BIGINT UNSIGNED NULL IDX |
| product_name | VARCHAR(190) |
| sku | VARCHAR(120) |
| price | DECIMAL(12,2) |
| cost_price | DECIMAL(12,2) DEFAULT 0 |
| qty | INT |
| subtotal | DECIMAL(12,2) |

---

# customers

| Column | Type |
|---|---|
| id | BIGINT UNSIGNED PK |
| name | VARCHAR(150) |
| phone | VARCHAR(30) UNIQUE |
| alt_phone | VARCHAR(30) NULL |
| email | VARCHAR(190) NULL |
| first_order_at | TIMESTAMP NULL |
| last_order_at | TIMESTAMP NULL |
| total_orders | INT DEFAULT 0 |
| total_spent | DECIMAL(14,2) DEFAULT 0 |
| tags | VARCHAR(255) NULL |
| is_blocked | TINYINT(1) DEFAULT 0 |
| notes | TEXT NULL |
| created_at | TIMESTAMP NULL |
| updated_at | TIMESTAMP NULL |

---

# expenses

| Column | Type |
|---|---|
| id | BIGINT UNSIGNED PK |
| category_id | BIGINT UNSIGNED IDX |
| title | VARCHAR(190) |
| amount | DECIMAL(12,2) |
| expense_date | DATE |
| note | TEXT NULL |
| created_by_admin_id | BIGINT UNSIGNED NULL |
| created_at | TIMESTAMP NULL |

---

# coupons

| Column | Type |
|---|---|
| id | BIGINT UNSIGNED PK |
| code | VARCHAR(80) UNIQUE |
| type | ENUM('fixed','percent') |
| value | DECIMAL(12,2) |
| min_order_amount | DECIMAL(12,2) DEFAULT 0 |
| max_discount | DECIMAL(12,2) NULL |
| usage_limit | INT NULL |
| used_count | INT DEFAULT 0 |
| start_at | TIMESTAMP NULL |
| end_at | TIMESTAMP NULL |
| status | TINYINT(1) DEFAULT 1 |

---

# settings

| Column | Type |
|---|---|
| id | BIGINT UNSIGNED PK |
| group_name | VARCHAR(100) IDX |
| key_name | VARCHAR(150) |
| value | LONGTEXT NULL |
| type | VARCHAR(50) DEFAULT 'string' |
| autoload | TINYINT(1) DEFAULT 1 |

UNIQUE:

(group_name, key_name)

---

# courier_shipments

| Column | Type |
|---|---|
| id | BIGINT UNSIGNED PK |
| order_id | BIGINT UNSIGNED IDX |
| courier_id | BIGINT UNSIGNED IDX |
| consignment_id | VARCHAR(120) NULL |
| tracking_code | VARCHAR(120) NULL IDX |
| status | VARCHAR(80) DEFAULT 'pending' |
| request_payload | LONGTEXT NULL |
| response_payload | LONGTEXT NULL |
| created_at | TIMESTAMP NULL |
| updated_at | TIMESTAMP NULL |

---

# payments

| Column | Type |
|---|---|
| id | BIGINT UNSIGNED PK |
| order_id | BIGINT UNSIGNED IDX |
| transaction_no | VARCHAR(120) NULL UNIQUE |
| method | VARCHAR(50) |
| amount | DECIMAL(12,2) |
| status | VARCHAR(50) |
| paid_at | TIMESTAMP NULL |
| raw_response | LONGTEXT NULL |

---

# 5. IMPORTANT SUPPORT TABLES

Need same style schema for:

- product_tags
- flash_sales
- flash_sale_items
- banners
- blogs
- blog_categories
- faqs
- menus
- menu_items
- admin_activity_logs
- stock_movements
- purchases
- purchase_items
- refunds
- reviews
- failed_jobs
- jobs
- migrations

---

# 6. INDEXING STRATEGY

## Must Add Composite Indexes

products:

- (category_id, status)
- (brand_id, status)

orders:

- (order_status, created_at)
- (customer_phone, created_at)

customers:

- (phone, last_order_at)

settings:

- (group_name, key_name)

---

# 7. SOFT DELETE TABLES

Use soft deletes for:

- products
- categories
- brands
- pages
- blogs

---

# 8. JSON COLUMN RECOMMENDATION

Use JSON where needed:

- settings.value
- courier response
- popup rules
- report filters cache

---

# 9. MIGRATION ORDER

1. roles
2. permissions
3. admins
4. categories
5. brands
6. products
7. product_images
8. attributes
9. variants
10. customers
11. orders
12. order_items
13. payments
14. expenses
15. coupons
16. settings
17. couriers
18. courier_shipments

---

# 10. PERFORMANCE RULES

- Always index foreign keys
- Avoid nullable if unnecessary
- Use decimal for money
- Use bigint ids
- Archive old logs later
- Use cache for settings

---

# 11. FINAL GOAL

Use this file to generate Laravel migrations with AI safely.

No guessing needed.

---