# 03-er-diagram.md

# ER Diagram Planning Document
Version: 1.0  
Project Type: Single Vendor eCommerce  
Framework: Laravel  
Architecture Style: Scalable + Modular + AI Friendly

---

# 1. PURPOSE OF THIS DOCUMENT

This file defines:

- All database tables
- Relationships
- Foreign keys
- Core business flow
- Expandable architecture

Goal:

No important table should be missed.

---

# 2. MAIN DATABASE MODULES

Database divided into:

1. Authentication & Admin
2. Product Catalog
3. Orders & Checkout
4. Customer Data
5. Inventory
6. Accounting
7. Marketing
8. Website CMS
9. Settings
10. Courier Integration
11. Logs & Security
12. Reports Support

---

# 3. AUTHENTICATION & ADMIN TABLES

## admins

Stores admin users.

Columns:

- id
- role_id
- name
- email
- phone
- password
- avatar
- status
- last_login_at
- last_login_ip
- remember_token
- created_at
- updated_at

## roles

- id
- name
- slug
- created_at
- updated_at

## permissions

- id
- name
- slug
- module
- created_at
- updated_at

## role_permission

Pivot table.

- id
- role_id
- permission_id

## admin_activity_logs

- id
- admin_id
- action
- module
- target_id
- ip
- user_agent
- created_at

---

# 4. PRODUCT CATALOG TABLES

## categories

- id
- parent_id (nullable)
- name
- slug
- icon
- image
- description
- sort_order
- status
- meta_title
- meta_description
- created_at
- updated_at

(Self relation for category tree)

## brands

- id
- name
- slug
- logo
- description
- status
- meta_title
- meta_description
- created_at
- updated_at

## products

Main product table.

- id
- category_id
- brand_id (nullable)
- name
- slug
- sku
- barcode
- short_description
- description
- cost_price
- price
- compare_price
- weight
- tax_type
- tax_amount
- min_qty
- stock_qty
- low_stock_alert
- is_featured
- is_best_seller
- is_trending
- is_new_arrival
- has_variant
- status
- meta_title
- meta_description
- created_at
- updated_at
- deleted_at

## product_images

- id
- product_id
- image
- sort_order
- is_primary
- created_at

## product_videos

- id
- product_id
- provider
- url
- created_at

## attributes

Examples:

- Size
- Color
- Material

Columns:

- id
- name
- slug
- created_at

## attribute_values

- id
- attribute_id
- value
- slug

## product_variants

- id
- product_id
- sku
- barcode
- variant_name
- cost_price
- price
- stock_qty
- weight
- image
- status
- created_at
- updated_at

## product_variant_items

Maps variant values.

- id
- product_variant_id
- attribute_id
- attribute_value_id

## product_tags

- id
- name
- slug

## product_tag_items

- id
- product_id
- product_tag_id

## related_products

- id
- product_id
- related_product_id

---

# 5. ORDERS & CHECKOUT TABLES

## orders

Main order table.

- id
- invoice_no
- customer_id (nullable)
- customer_name
- customer_phone
- customer_alt_phone
- customer_email (nullable)
- shipping_address
- area_id (nullable)
- city_id (nullable)
- note
- subtotal
- discount_amount
- coupon_id (nullable)
- shipping_charge
- tax_amount
- total_amount
- payment_method
- payment_status
- order_status
- source
- ip_address
- user_agent
- ordered_at
- confirmed_at
- packed_at
- shipped_at
- delivered_at
- cancelled_at
- returned_at
- created_at
- updated_at

## order_items

- id
- order_id
- product_id
- product_variant_id (nullable)
- product_name
- sku
- price
- cost_price
- qty
- subtotal

## order_status_histories

- id
- order_id
- status
- note
- changed_by_admin_id
- created_at

## carts

Guest carts.

- id
- session_key
- ip_address
- product_id
- product_variant_id (nullable)
- qty
- created_at
- updated_at

## abandoned_carts

- id
- phone (nullable)
- session_key
- cart_data
- last_seen_at
- recovered_at (nullable)

---

# 6. CUSTOMER DATA TABLES

(No login account model)

## customers

Generated from orders.

- id
- name
- phone
- alt_phone
- email
- first_order_at
- last_order_at
- total_orders
- total_spent
- tags
- is_blocked
- notes
- created_at
- updated_at

## customer_addresses

- id
- customer_id
- label
- address
- area_id
- city_id
- is_default

## customer_notes

- id
- customer_id
- admin_id
- note
- created_at

---

# 7. INVENTORY TABLES

## stock_movements

Tracks every stock change.

- id
- product_id
- product_variant_id (nullable)
- type
  - purchase
  - sale
  - return
  - damage
  - adjust
- qty
- reference_type
- reference_id
- note
- created_at

## suppliers

- id
- name
- phone
- email
- address
- due_amount
- status

## purchases

- id
- supplier_id
- invoice_no
- subtotal
- discount
- total
- paid
- due
- purchased_at

## purchase_items

- id
- purchase_id
- product_id
- product_variant_id (nullable)
- cost_price
- qty
- subtotal

---

# 8. ACCOUNTING TABLES

## expenses

- id
- category_id
- title
- amount
- expense_date
- note
- created_by_admin_id
- created_at

## expense_categories

- id
- name
- slug

## incomes

Optional manual income.

- id
- title
- amount
- income_date
- note

## transactions

Unified ledger.

- id
- type
- reference_type
- reference_id
- amount
- direction
- method
- note
- created_at

---

# 9. MARKETING TABLES

## coupons

- id
- code
- type
- value
- min_order_amount
- max_discount
- usage_limit
- used_count
- start_at
- end_at
- status

## coupon_usages

- id
- coupon_id
- order_id
- phone
- amount
- used_at

## flash_sales

- id
- title
- start_at
- end_at
- status

## flash_sale_items

- id
- flash_sale_id
- product_id
- discount_type
- discount_value

## subscribers

Newsletter.

- id
- email
- created_at

## popup_campaigns

- id
- title
- type
- content
- image
- start_at
- end_at
- status

---

# 10. WEBSITE CMS TABLES

## pages

- id
- title
- slug
- content
- meta_title
- meta_description
- status

## blogs

- id
- category_id
- title
- slug
- image
- excerpt
- content
- status
- published_at

## blog_categories

- id
- name
- slug

## banners

- id
- title
- image
- mobile_image
- link
- position
- sort_order
- start_at
- end_at
- status

## faqs

- id
- question
- answer
- sort_order
- status

---

# 11. SETTINGS TABLES

## settings

Key-value storage.

- id
- group_name
- key_name
- value
- type
- autoload

Examples:

- site_name
- logo
- favicon
- primary_color
- facebook_pixel
- courier_default
- homepage_section_status

## menus

- id
- name
- location
- status

## menu_items

- id
- menu_id
- parent_id
- label
- url
- sort_order
- target

---

# 12. LOCATION TABLES

## cities

- id
- name
- slug
- status

## areas

- id
- city_id
- name
- slug
- shipping_charge
- courier_zone_code
- status

---

# 13. PAYMENT TABLES

## payments

- id
- order_id
- transaction_no
- method
- amount
- status
- paid_at
- raw_response

## refunds

- id
- order_id
- payment_id (nullable)
- amount
- reason
- status
- processed_at

---

# 14. COURIER TABLES

## couriers

- id
- name
- slug
- status

## courier_credentials

- id
- courier_id
- key_name
- value

## courier_shipments

- id
- order_id
- courier_id
- consignment_id
- tracking_code
- status
- request_payload
- response_payload
- created_at
- updated_at

---

# 15. REVIEW TABLES

## reviews

- id
- product_id
- customer_name
- phone (nullable)
- rating
- comment
- status
- created_at

---

# 16. SECURITY TABLES

## failed_logins

- id
- email
- ip
- attempted_at

## sessions_meta

Optional admin session tracking.

- id
- admin_id
- ip
- user_agent
- last_seen_at

---

# 17. SYSTEM TABLES (Laravel)

- migrations
- jobs
- failed_jobs
- cache
- cache_locks
- password_reset_tokens

---

# 18. RELATIONSHIP SUMMARY

## Core Relations

categories 1 → many products  
brands 1 → many products  
products 1 → many images  
products 1 → many variants  
orders 1 → many order_items  
orders 1 → many status_histories  
customers 1 → many orders  
cities 1 → many areas  
areas 1 → many orders  
coupons 1 → many orders  
orders 1 → 1 payment  
orders 1 → many courier_shipments

---

# 19. INDEXES REQUIRED

Add indexes on:

- products.slug
- products.sku
- orders.invoice_no
- orders.customer_phone
- orders.order_status
- orders.created_at
- customers.phone
- coupons.code
- settings.group_name,key_name
- areas.city_id
- stock_movements.product_id

---

# 20. FUTURE READY TABLES (OPTIONAL LATER)

- wishlists
- wallets
- loyalty_points
- affiliates
- delivery_boys
- warehouses
- pos_sales
- mobile_devices

---

# 21. FINAL NOTE

This schema is designed so that:

- frontend can be simple
- backend can run full business
- reports can be accurate
- AI can generate modules cleanly
- future expansion possible

---