# Database Schema - Lion POSM Module

**Database Engine:** MySQL 8.0+

**Character Set:** utf8mb4

**Collation:** utf8mb4_unicode_ci

**Storage Engine:** InnoDB

**Timezone:** UTC (convert to Asia/Colombo in application layer)

---

## 1. User Management & Hierarchy Tables

### **users**

Stores all system users across different roles.

| Column | Type | Constraints | Description |
| --- | --- | --- | --- |
| id | BIGINT UNSIGNED | PK, AUTO_INCREMENT | Primary key |
| name | VARCHAR(255) | NOT NULL | User full name |
| email | VARCHAR(255) | UNIQUE, NOT NULL | User email (login) |
| phone | VARCHAR(20) | NULLABLE | Contact number |
| password | VARCHAR(255) | NOT NULL | Hashed password |
| role | ENUM | NOT NULL | system_admin, senior_manager, territory_manager, lsr, coordinator, logistics_officer, distribution_contact, event_supplier |
| is_active | BOOLEAN | DEFAULT TRUE | Account status |
| notification_preference | ENUM | DEFAULT 'realtime' | realtime, daily_digest |
| last_login_at | TIMESTAMP | NULLABLE | Last login timestamp |
| created_at | TIMESTAMP | NOT NULL | Record creation timestamp |
| updated_at | TIMESTAMP | NOT NULL | Record update timestamp |
| deleted_at | TIMESTAMP | NULLABLE | Soft delete timestamp |

**Indexes:**

- PRIMARY KEY (id)
- UNIQUE KEY (email)
- INDEX (role)
- INDEX (is_active)

### **hierarchy_nodes**

Stores company organizational hierarchy structure.

| Column | Type | Constraints | Description |
| --- | --- | --- | --- |
| id | BIGINT UNSIGNED | PK, AUTO_INCREMENT | Primary key |
| parent_id | BIGINT UNSIGNED | NULLABLE, FK(hierarchy_[nodes.id](http://nodes.id)) | Parent node (NULL for root) |
| name | VARCHAR(255) | NOT NULL | Node name (e.g., Market, Region) |
| level | INT | NOT NULL | Hierarchy level (0 = root) |
| description | TEXT | NULLABLE | Node description |
| created_at | TIMESTAMP | NOT NULL | Record creation timestamp |
| updated_at | TIMESTAMP | NOT NULL | Record update timestamp |
| deleted_at | TIMESTAMP | NULLABLE | Soft delete timestamp |

**Indexes:**

- PRIMARY KEY (id)
- INDEX (parent_id)
- INDEX (level)

**Foreign Keys:**

- parent_id REFERENCES hierarchy_nodes(id) ON DELETE CASCADE

### **user_assignments**

Maps users to hierarchy nodes.

| Column | Type | Constraints | Description |
| --- | --- | --- | --- |
| id | BIGINT UNSIGNED | PK, AUTO_INCREMENT | Primary key |
| user_id | BIGINT UNSIGNED | NOT NULL, FK([users.id](http://users.id)) | User reference |
| hierarchy_node_id | BIGINT UNSIGNED | NOT NULL, FK(hierarchy_[nodes.id](http://nodes.id)) | Hierarchy node reference |
| created_at | TIMESTAMP | NOT NULL | Record creation timestamp |
| updated_at | TIMESTAMP | NOT NULL | Record update timestamp |

**Indexes:**

- PRIMARY KEY (id)
- UNIQUE KEY (user_id, hierarchy_node_id)
- INDEX (user_id)
- INDEX (hierarchy_node_id)

**Foreign Keys:**

- user_id REFERENCES users(id) ON DELETE CASCADE
- hierarchy_node_id REFERENCES hierarchy_nodes(id) ON DELETE CASCADE

---

## 2. Product & Inventory Tables

### **products**

Product catalog master data.

| Column | Type | Constraints | Description |
| --- | --- | --- | --- |
| id | BIGINT UNSIGNED | PK, AUTO_INCREMENT | Primary key |
| sku | VARCHAR(100) | UNIQUE, NOT NULL | Stock Keeping Unit code |
| name | VARCHAR(255) | NOT NULL | Product name |
| description | TEXT | NULLABLE | Product description |
| unit_price | DECIMAL(12,2) | NOT NULL, DEFAULT 0.00 | Unit price in LKR |
| is_active | BOOLEAN | DEFAULT TRUE | Product active status |
| created_at | TIMESTAMP | NOT NULL | Record creation timestamp |
| updated_at | TIMESTAMP | NOT NULL | Record update timestamp |
| deleted_at | TIMESTAMP | NULLABLE | Soft delete timestamp |

**Indexes:**

- PRIMARY KEY (id)
- UNIQUE KEY (sku)
- INDEX (is_active)

### **warehouses**

Warehouse master data.

| Column | Type | Constraints | Description |
| --- | --- | --- | --- |
| id | BIGINT UNSIGNED | PK, AUTO_INCREMENT | Primary key |
| name | VARCHAR(255) | NOT NULL | Warehouse name |
| code | VARCHAR(50) | UNIQUE, NOT NULL | Warehouse code |
| address | TEXT | NULLABLE | Physical address |
| contact_person | VARCHAR(255) | NULLABLE | Contact person name |
| contact_phone | VARCHAR(20) | NULLABLE | Contact phone number |
| contact_email | VARCHAR(255) | NULLABLE | Contact email |
| is_active | BOOLEAN | DEFAULT TRUE | Warehouse status |
| created_at | TIMESTAMP | NOT NULL | Record creation timestamp |
| updated_at | TIMESTAMP | NOT NULL | Record update timestamp |
| deleted_at | TIMESTAMP | NULLABLE | Soft delete timestamp |

**Indexes:**

- PRIMARY KEY (id)
- UNIQUE KEY (code)
- INDEX (is_active)

### **distribution_points**

Distribution point master data.

| Column | Type | Constraints | Description |
| --- | --- | --- | --- |
| id | BIGINT UNSIGNED | PK, AUTO_INCREMENT | Primary key |
| name | VARCHAR(255) | NOT NULL | Distribution point name |
| code | VARCHAR(50) | UNIQUE, NOT NULL | Distribution point code |
| hierarchy_node_id | BIGINT UNSIGNED | NULLABLE, FK(hierarchy_[nodes.id](http://nodes.id)) | Assigned market/hierarchy |
| address | TEXT | NULLABLE | Physical address |
| contact_person | VARCHAR(255) | NOT NULL | Contact person name |
| contact_phone | VARCHAR(20) | NOT NULL | Contact phone number |
| contact_email | VARCHAR(255) | NOT NULL | Contact email |
| latitude | DECIMAL(10,8) | NULLABLE | GPS latitude |
| longitude | DECIMAL(11,8) | NULLABLE | GPS longitude |
| is_active | BOOLEAN | DEFAULT TRUE | Distribution point status |
| created_at | TIMESTAMP | NOT NULL | Record creation timestamp |
| updated_at | TIMESTAMP | NOT NULL | Record update timestamp |
| deleted_at | TIMESTAMP | NULLABLE | Soft delete timestamp |

**Indexes:**

- PRIMARY KEY (id)
- UNIQUE KEY (code)
- INDEX (hierarchy_node_id)
- INDEX (is_active)

**Foreign Keys:**

- hierarchy_node_id REFERENCES hierarchy_nodes(id) ON DELETE SET NULL

### **inventory_locations**

Inventory ledger per location (warehouse or distribution point).

| Column | Type | Constraints | Description |
| --- | --- | --- | --- |
| id | BIGINT UNSIGNED | PK, AUTO_INCREMENT | Primary key |
| product_id | BIGINT UNSIGNED | NOT NULL, FK([products.id](http://products.id)) | Product reference |
| location_type | ENUM | NOT NULL | warehouse, distribution_point |
| location_id | BIGINT UNSIGNED | NOT NULL | Warehouse or distribution point ID |
| quantity | DECIMAL(12,2) | NOT NULL, DEFAULT 0.00 | Available quantity |
| reserved_quantity | DECIMAL(12,2) | NOT NULL, DEFAULT 0.00 | Reserved stock (Ready to Deplete) |
| unit_cost | DECIMAL(12,2) | NULLABLE | Weighted average cost in LKR |
| updated_at | TIMESTAMP | NOT NULL | Last update timestamp |

**Indexes:**

- PRIMARY KEY (id)
- UNIQUE KEY (product_id, location_type, location_id)
- INDEX (product_id)
- INDEX (location_type, location_id)

**Foreign Keys:**

- product_id REFERENCES products(id) ON DELETE CASCADE

### **inventory_movements**

Transaction log for all inventory movements.

| Column | Type | Constraints | Description |
| --- | --- | --- | --- |
| id | BIGINT UNSIGNED | PK, AUTO_INCREMENT | Primary key |
| product_id | BIGINT UNSIGNED | NOT NULL, FK([products.id](http://products.id)) | Product reference |
| movement_type | ENUM | NOT NULL | stock_inward, stock_adjustment, transfer, depletion, order_dispatch, order_delivery, event_handover, event_return |
| from_location_type | ENUM | NULLABLE | warehouse, distribution_point, outlet |
| from_location_id | BIGINT UNSIGNED | NULLABLE | Source location ID |
| to_location_type | ENUM | NULLABLE | warehouse, distribution_point, outlet |
| to_location_id | BIGINT UNSIGNED | NULLABLE | Destination location ID |
| quantity | DECIMAL(12,2) | NOT NULL | Movement quantity (+ or -) |
| unit_cost | DECIMAL(12,2) | NULLABLE | Cost per unit in LKR |
| reference_type | VARCHAR(100) | NULLABLE | Reference entity type (depletion_plan, order, etc.) |
| reference_id | BIGINT UNSIGNED | NULLABLE | Reference entity ID |
| po_number | VARCHAR(100) | NULLABLE | Purchase order number (for stock inward) |
| reason | VARCHAR(255) | NULLABLE | Movement reason (for adjustments) |
| performed_by | BIGINT UNSIGNED | NOT NULL, FK([users.id](http://users.id)) | User who performed the movement |
| created_at | TIMESTAMP | NOT NULL | Movement timestamp |

**Indexes:**

- PRIMARY KEY (id)
- INDEX (product_id)
- INDEX (movement_type)
- INDEX (reference_type, reference_id)
- INDEX (created_at)
- INDEX (performed_by)

**Foreign Keys:**

- product_id REFERENCES products(id) ON DELETE CASCADE
- performed_by REFERENCES users(id) ON DELETE RESTRICT

---

## 3. Outlet & Norms Tables

### **outlets**

Outlet master data.

| Column | Type | Constraints | Description |
| --- | --- | --- | --- |
| id | BIGINT UNSIGNED | PK, AUTO_INCREMENT | Primary key |
| name | VARCHAR(255) | NOT NULL | Outlet name |
| rt_code | VARCHAR(100) | UNIQUE, NOT NULL | RT Code (unique identifier) |
| channel | VARCHAR(100) | NULLABLE | Sales channel (e.g., HoReCa, Retail) |
| address | TEXT | NULLABLE | Outlet address |
| latitude | DECIMAL(10,8) | NULLABLE | GPS latitude |
| longitude | DECIMAL(11,8) | NULLABLE | GPS longitude |
| distribution_point_id | BIGINT UNSIGNED | NOT NULL, FK(distribution_[points.id](http://points.id)) | Assigned distribution point |
| assigned_lsr_id | BIGINT UNSIGNED | NULLABLE, FK([users.id](http://users.id)) | Assigned LSR |
| is_active | BOOLEAN | DEFAULT TRUE | Outlet status |
| created_at | TIMESTAMP | NOT NULL | Record creation timestamp |
| updated_at | TIMESTAMP | NOT NULL | Record update timestamp |
| deleted_at | TIMESTAMP | NULLABLE | Soft delete timestamp |

**Indexes:**

- PRIMARY KEY (id)
- UNIQUE KEY (rt_code)
- INDEX (distribution_point_id)
- INDEX (assigned_lsr_id)
- INDEX (is_active)

**Foreign Keys:**

- distribution_point_id REFERENCES distribution_points(id) ON DELETE RESTRICT
- assigned_lsr_id REFERENCES users(id) ON DELETE SET NULL

### **norms**

Norms (ideal quantities) per SKU per outlet.

| Column | Type | Constraints | Description |
| --- | --- | --- | --- |
| id | BIGINT UNSIGNED | PK, AUTO_INCREMENT | Primary key |
| outlet_id | BIGINT UNSIGNED | NOT NULL, FK([outlets.id](http://outlets.id)) | Outlet reference |
| product_id | BIGINT UNSIGNED | NOT NULL, FK([products.id](http://products.id)) | Product reference |
| norm_quantity | DECIMAL(12,2) | NOT NULL | Ideal quantity to maintain |
| tolerance_percentage | DECIMAL(5,2) | DEFAULT 10.00 | System-wide tolerance (10%) |
| created_at | TIMESTAMP | NOT NULL | Record creation timestamp |
| updated_at | TIMESTAMP | NOT NULL | Record update timestamp |

**Indexes:**

- PRIMARY KEY (id)
- UNIQUE KEY (outlet_id, product_id)
- INDEX (outlet_id)
- INDEX (product_id)

**Foreign Keys:**

- outlet_id REFERENCES outlets(id) ON DELETE CASCADE
- product_id REFERENCES products(id) ON DELETE CASCADE

### **availability_reports**

LSR-captured outlet stock availability reports.

| Column | Type | Constraints | Description |
| --- | --- | --- | --- |
| id | BIGINT UNSIGNED | PK, AUTO_INCREMENT | Primary key |
| outlet_id | BIGINT UNSIGNED | NOT NULL, FK([outlets.id](http://outlets.id)) | Outlet reference |
| lsr_id | BIGINT UNSIGNED | NOT NULL, FK([users.id](http://users.id)) | LSR who captured the report |
| visit_latitude | DECIMAL(10,8) | NULLABLE | Captured GPS latitude |
| visit_longitude | DECIMAL(11,8) | NULLABLE | Captured GPS longitude |
| geo_tag_match | BOOLEAN | DEFAULT TRUE | Geo-tag validation result |
| geo_tag_distance | DECIMAL(8,2) | NULLABLE | Distance from registered location (meters) |
| submitted_at | TIMESTAMP | NOT NULL | Report submission timestamp |
| created_at | TIMESTAMP | NOT NULL | Record creation timestamp |

**Indexes:**

- PRIMARY KEY (id)
- INDEX (outlet_id)
- INDEX (lsr_id)
- INDEX (submitted_at)
- INDEX (geo_tag_match)

**Foreign Keys:**

- outlet_id REFERENCES outlets(id) ON DELETE CASCADE
- lsr_id REFERENCES users(id) ON DELETE CASCADE

### **availability_report_items**

Line items for availability reports.

| Column | Type | Constraints | Description |
| --- | --- | --- | --- |
| id | BIGINT UNSIGNED | PK, AUTO_INCREMENT | Primary key |
| availability_report_id | BIGINT UNSIGNED | NOT NULL, FK(availability_[reports.id](http://reports.id)) | Report reference |
| product_id | BIGINT UNSIGNED | NOT NULL, FK([products.id](http://products.id)) | Product reference |
| norm_quantity | DECIMAL(12,2) | NOT NULL | Norm quantity at time of capture |
| actual_quantity | DECIMAL(12,2) | NOT NULL | LSR-captured actual quantity |
| variance | DECIMAL(12,2) | NOT NULL | Calculated variance (actual - norm) |
| flag | ENUM | NOT NULL | red (out of norm), blue (within norm) |
| created_at | TIMESTAMP | NOT NULL | Record creation timestamp |

**Indexes:**

- PRIMARY KEY (id)
- INDEX (availability_report_id)
- INDEX (product_id)
- INDEX (flag)

**Foreign Keys:**

- availability_report_id REFERENCES availability_reports(id) ON DELETE CASCADE
- product_id REFERENCES products(id) ON DELETE CASCADE

---

## 4. Depletion Planning Tables

### **depletion_plans**

Depletion plan headers.

| Column | Type | Constraints | Description |
| --- | --- | --- | --- |
| id | BIGINT UNSIGNED | PK, AUTO_INCREMENT | Primary key |
| plan_number | VARCHAR(100) | UNIQUE, NOT NULL | Auto-generated plan number |
| budget_code | VARCHAR(100) | NOT NULL | Budget code (mandatory) |
| warehouse_id | BIGINT UNSIGNED | NOT NULL, FK([warehouses.id](http://warehouses.id)) | Source warehouse |
| status | ENUM | NOT NULL | pending, approved, rejected, ready_to_deplete, dispatched, received |
| submitted_by | BIGINT UNSIGNED | NOT NULL, FK([users.id](http://users.id)) | Coordinator who uploaded |
| submitted_at | TIMESTAMP | NOT NULL | Submission timestamp |
| approved_by | BIGINT UNSIGNED | NULLABLE, FK([users.id](http://users.id)) | SM who approved |
| approved_at | TIMESTAMP | NULLABLE | Approval timestamp |
| approval_comments | TEXT | NULLABLE | Approval/rejection comments |
| created_at | TIMESTAMP | NOT NULL | Record creation timestamp |
| updated_at | TIMESTAMP | NOT NULL | Record update timestamp |

**Indexes:**

- PRIMARY KEY (id)
- UNIQUE KEY (plan_number)
- INDEX (warehouse_id)
- INDEX (status)
- INDEX (submitted_by)
- INDEX (approved_by)
- INDEX (submitted_at)

**Foreign Keys:**

- warehouse_id REFERENCES warehouses(id) ON DELETE RESTRICT
- submitted_by REFERENCES users(id) ON DELETE RESTRICT
- approved_by REFERENCES users(id) ON DELETE RESTRICT

### **depletion_plan_items**

Line items for depletion plans.

| Column | Type | Constraints | Description |
| --- | --- | --- | --- |
| id | BIGINT UNSIGNED | PK, AUTO_INCREMENT | Primary key |
| depletion_plan_id | BIGINT UNSIGNED | NOT NULL, FK(depletion_[plans.id](http://plans.id)) | Plan reference |
| product_id | BIGINT UNSIGNED | NOT NULL, FK([products.id](http://products.id)) | Product reference |
| outlet_id | BIGINT UNSIGNED | NOT NULL, FK([outlets.id](http://outlets.id)) | Destination outlet |
| distribution_point_id | BIGINT UNSIGNED | NOT NULL, FK(distribution_[points.id](http://points.id)) | Destination distribution point |
| planned_quantity | DECIMAL(12,2) | NOT NULL | Originally planned quantity |
| approved_quantity | DECIMAL(12,2) | NULLABLE | SM-amended quantity (NULL = same as planned) |
| created_at | TIMESTAMP | NOT NULL | Record creation timestamp |
| updated_at | TIMESTAMP | NOT NULL | Record update timestamp |

**Indexes:**

- PRIMARY KEY (id)
- INDEX (depletion_plan_id)
- INDEX (product_id)
- INDEX (outlet_id)
- INDEX (distribution_point_id)

**Foreign Keys:**

- depletion_plan_id REFERENCES depletion_plans(id) ON DELETE CASCADE
- product_id REFERENCES products(id) ON DELETE RESTRICT
- outlet_id REFERENCES outlets(id) ON DELETE RESTRICT
- distribution_point_id REFERENCES distribution_points(id) ON DELETE RESTRICT

### **shipments**

Shipments generated from approved depletion plans.

| Column | Type | Constraints | Description |
| --- | --- | --- | --- |
| id | BIGINT UNSIGNED | PK, AUTO_INCREMENT | Primary key |
| shipment_number | VARCHAR(100) | UNIQUE, NOT NULL | Auto-generated shipment number |
| depletion_plan_id | BIGINT UNSIGNED | NOT NULL, FK(depletion_[plans.id](http://plans.id)) | Plan reference |
| warehouse_id | BIGINT UNSIGNED | NOT NULL, FK([warehouses.id](http://warehouses.id)) | Source warehouse |
| distribution_point_id | BIGINT UNSIGNED | NOT NULL, FK(distribution_[points.id](http://points.id)) | Destination distribution point |
| qr_code | VARCHAR(255) | UNIQUE, NOT NULL | Plain text QR code (shipment ID) |
| status | ENUM | NOT NULL | ready_to_deplete, dispatched, received |
| gate_pass_url | VARCHAR(500) | NULLABLE | S3 URL of gate pass PDF |
| dispatched_by | BIGINT UNSIGNED | NULLABLE, FK([users.id](http://users.id)) | Logistics officer who dispatched |
| dispatched_at | TIMESTAMP | NULLABLE | Dispatch timestamp |
| received_by_email | VARCHAR(255) | NULLABLE | Distribution contact who confirmed receipt |
| received_at | TIMESTAMP | NULLABLE | Receipt timestamp |
| has_discrepancy | BOOLEAN | DEFAULT FALSE | Discrepancy flag |
| created_at | TIMESTAMP | NOT NULL | Record creation timestamp |
| updated_at | TIMESTAMP | NOT NULL | Record update timestamp |

**Indexes:**

- PRIMARY KEY (id)
- UNIQUE KEY (shipment_number)
- UNIQUE KEY (qr_code)
- INDEX (depletion_plan_id)
- INDEX (warehouse_id)
- INDEX (distribution_point_id)
- INDEX (status)
- INDEX (dispatched_at)

**Foreign Keys:**

- depletion_plan_id REFERENCES depletion_plans(id) ON DELETE CASCADE
- warehouse_id REFERENCES warehouses(id) ON DELETE RESTRICT
- distribution_point_id REFERENCES distribution_points(id) ON DELETE RESTRICT
- dispatched_by REFERENCES users(id) ON DELETE SET NULL

### **shipment_items**

Line items for shipments.

| Column | Type | Constraints | Description |
| --- | --- | --- | --- |
| id | BIGINT UNSIGNED | PK, AUTO_INCREMENT | Primary key |
| shipment_id | BIGINT UNSIGNED | NOT NULL, FK([shipments.id](http://shipments.id)) | Shipment reference |
| product_id | BIGINT UNSIGNED | NOT NULL, FK([products.id](http://products.id)) | Product reference |
| planned_quantity | DECIMAL(12,2) | NOT NULL | Planned quantity |
| received_quantity | DECIMAL(12,2) | NULLABLE | Actual received quantity |
| discrepancy_quantity | DECIMAL(12,2) | NULLABLE | Discrepancy (received - planned) |
| created_at | TIMESTAMP | NOT NULL | Record creation timestamp |
| updated_at | TIMESTAMP | NOT NULL | Record update timestamp |

**Indexes:**

- PRIMARY KEY (id)
- INDEX (shipment_id)
- INDEX (product_id)

**Foreign Keys:**

- shipment_id REFERENCES shipments(id) ON DELETE CASCADE
- product_id REFERENCES products(id) ON DELETE RESTRICT

---

## 5. Order Management Tables

### **orders**

Outlet orders created by TMs.

| Column | Type | Constraints | Description |
| --- | --- | --- | --- |
| id | BIGINT UNSIGNED | PK, AUTO_INCREMENT | Primary key |
| order_number | VARCHAR(100) | UNIQUE, NOT NULL | Auto-generated order number |
| outlet_id | BIGINT UNSIGNED | NOT NULL, FK([outlets.id](http://outlets.id)) | Destination outlet |
| distribution_point_id | BIGINT UNSIGNED | NOT NULL, FK(distribution_[points.id](http://points.id)) | Source distribution point |
| availability_report_id | BIGINT UNSIGNED | NULLABLE, FK(availability_[reports.id](http://reports.id)) | Reference availability report |
| lsr_id | BIGINT UNSIGNED | NOT NULL, FK([users.id](http://users.id)) | Assigned LSR |
| tm_id | BIGINT UNSIGNED | NOT NULL, FK([users.id](http://users.id)) | TM who created the order |
| status | ENUM | NOT NULL | created, notified, dispatched, collected, delivered |
| notes | TEXT | NULLABLE | Order notes |
| dispatched_at | TIMESTAMP | NULLABLE | Dispatch from distribution point |
| collected_at | TIMESTAMP | NULLABLE | LSR collection timestamp |
| delivered_at | TIMESTAMP | NULLABLE | Delivery to outlet timestamp |
| receiver_name | VARCHAR(255) | NULLABLE | Outlet receiver name |
| receiver_phone | VARCHAR(20) | NULLABLE | Outlet receiver phone |
| receiver_signature_url | VARCHAR(500) | NULLABLE | S3 URL of signature image |
| delivery_latitude | DECIMAL(10,8) | NULLABLE | Delivery GPS latitude |
| delivery_longitude | DECIMAL(11,8) | NULLABLE | Delivery GPS longitude |
| geo_tag_match | BOOLEAN | DEFAULT TRUE | Geo-tag validation result |
| geo_tag_distance | DECIMAL(8,2) | NULLABLE | Distance from outlet location (meters) |
| created_at | TIMESTAMP | NOT NULL | Order creation timestamp |
| updated_at | TIMESTAMP | NOT NULL | Record update timestamp |

**Indexes:**

- PRIMARY KEY (id)
- UNIQUE KEY (order_number)
- INDEX (outlet_id)
- INDEX (distribution_point_id)
- INDEX (lsr_id)
- INDEX (tm_id)
- INDEX (status)
- INDEX (created_at)
- INDEX (geo_tag_match)

**Foreign Keys:**

- outlet_id REFERENCES outlets(id) ON DELETE RESTRICT
- distribution_point_id REFERENCES distribution_points(id) ON DELETE RESTRICT
- availability_report_id REFERENCES availability_reports(id) ON DELETE SET NULL
- lsr_id REFERENCES users(id) ON DELETE RESTRICT
- tm_id REFERENCES users(id) ON DELETE RESTRICT

### **order_items**

Line items for orders.

| Column | Type | Constraints | Description |
| --- | --- | --- | --- |
| id | BIGINT UNSIGNED | PK, AUTO_INCREMENT | Primary key |
| order_id | BIGINT UNSIGNED | NOT NULL, FK([orders.id](http://orders.id)) | Order reference |
| product_id | BIGINT UNSIGNED | NOT NULL, FK([products.id](http://products.id)) | Product reference |
| quantity | DECIMAL(12,2) | NOT NULL | Order quantity |
| created_at | TIMESTAMP | NOT NULL | Record creation timestamp |

**Indexes:**

- PRIMARY KEY (id)
- INDEX (order_id)
- INDEX (product_id)

**Foreign Keys:**

- order_id REFERENCES orders(id) ON DELETE CASCADE
- product_id REFERENCES products(id) ON DELETE RESTRICT

**Business Rule Constraint:**

- Maximum 50 items per order (enforced at application layer)

---

## 6. Event Stock Management Tables

### **event_budgets**

Event budget master data.

| Column | Type | Constraints | Description |
| --- | --- | --- | --- |
| id | BIGINT UNSIGNED | PK, AUTO_INCREMENT | Primary key |
| budget_code | VARCHAR(100) | UNIQUE, NOT NULL | Budget code |
| name | VARCHAR(255) | NOT NULL | Budget name |
| total_budget | DECIMAL(15,2) | NOT NULL | Total budget amount in LKR |
| allocated_amount | DECIMAL(15,2) | DEFAULT 0.00 | Allocated to delivery notes |
| missing_items_value | DECIMAL(15,2) | DEFAULT 0.00 | Value of missing items on returns |
| is_active | BOOLEAN | DEFAULT TRUE | Budget status |
| created_at | TIMESTAMP | NOT NULL | Record creation timestamp |
| updated_at | TIMESTAMP | NOT NULL | Record update timestamp |

**Indexes:**

- PRIMARY KEY (id)
- UNIQUE KEY (budget_code)
- INDEX (is_active)

### **event_suppliers**

Event supplier master data.

| Column | Type | Constraints | Description |
| --- | --- | --- | --- |
| id | BIGINT UNSIGNED | PK, AUTO_INCREMENT | Primary key |
| name | VARCHAR(255) | NOT NULL | Supplier name |
| code | VARCHAR(50) | UNIQUE, NOT NULL | Supplier code |
| contact_person | VARCHAR(255) | NOT NULL | Contact person name |
| contact_phone | VARCHAR(20) | NOT NULL | Contact phone |
| contact_email | VARCHAR(255) | NOT NULL | Contact email |
| is_active | BOOLEAN | DEFAULT TRUE | Supplier status |
| created_at | TIMESTAMP | NOT NULL | Record creation timestamp |
| updated_at | TIMESTAMP | NOT NULL | Record update timestamp |
| deleted_at | TIMESTAMP | NULLABLE | Soft delete timestamp |

**Indexes:**

- PRIMARY KEY (id)
- UNIQUE KEY (code)
- INDEX (is_active)

### **delivery_notes**

Delivery notes for event stock handover.

| Column | Type | Constraints | Description |
| --- | --- | --- | --- |
| id | BIGINT UNSIGNED | PK, AUTO_INCREMENT | Primary key |
| delivery_note_number | VARCHAR(100) | UNIQUE, NOT NULL | Auto-generated delivery note number |
| event_supplier_id | BIGINT UNSIGNED | NOT NULL, FK(event_[suppliers.id](http://suppliers.id)) | Event supplier reference |
| event_budget_id | BIGINT UNSIGNED | NOT NULL, FK(event_[budgets.id](http://budgets.id)) | Budget allocation reference |
| warehouse_id | BIGINT UNSIGNED | NOT NULL, FK([warehouses.id](http://warehouses.id)) | Source warehouse |
| qr_code | VARCHAR(255) | UNIQUE, NOT NULL | Plain text QR code (delivery note ID) |
| status | ENUM | NOT NULL | created, handed_over, returned, validated |
| pdf_url | VARCHAR(500) | NULLABLE | S3 URL of delivery note PDF |
| handed_over_by | BIGINT UNSIGNED | NULLABLE, FK([users.id](http://users.id)) | Logistics officer who handed over |
| handed_over_at | TIMESTAMP | NULLABLE | Handover timestamp |
| created_by | BIGINT UNSIGNED | NOT NULL, FK([users.id](http://users.id)) | System admin who created |
| created_at | TIMESTAMP | NOT NULL | Record creation timestamp |
| updated_at | TIMESTAMP | NOT NULL | Record update timestamp |

**Indexes:**

- PRIMARY KEY (id)
- UNIQUE KEY (delivery_note_number)
- UNIQUE KEY (qr_code)
- INDEX (event_supplier_id)
- INDEX (event_budget_id)
- INDEX (warehouse_id)
- INDEX (status)

**Foreign Keys:**

- event_supplier_id REFERENCES event_suppliers(id) ON DELETE RESTRICT
- event_budget_id REFERENCES event_budgets(id) ON DELETE RESTRICT
- warehouse_id REFERENCES warehouses(id) ON DELETE RESTRICT
- handed_over_by REFERENCES users(id) ON DELETE SET NULL
- created_by REFERENCES users(id) ON DELETE RESTRICT

### **delivery_note_items**

Line items for delivery notes.

| Column | Type | Constraints | Description |
| --- | --- | --- | --- |
| id | BIGINT UNSIGNED | PK, AUTO_INCREMENT | Primary key |
| delivery_note_id | BIGINT UNSIGNED | NOT NULL, FK(delivery_[notes.id](http://notes.id)) | Delivery note reference |
| product_id | BIGINT UNSIGNED | NOT NULL, FK([products.id](http://products.id)) | Product reference |
| quantity | DECIMAL(12,2) | NOT NULL | Quantity handed over |
| unit_cost | DECIMAL(12,2) | NOT NULL | Unit cost in LKR |
| total_cost | DECIMAL(15,2) | NOT NULL | Total line cost (quantity × unit_cost) |
| created_at | TIMESTAMP | NOT NULL | Record creation timestamp |

**Indexes:**

- PRIMARY KEY (id)
- INDEX (delivery_note_id)
- INDEX (product_id)

**Foreign Keys:**

- delivery_note_id REFERENCES delivery_notes(id) ON DELETE CASCADE
- product_id REFERENCES products(id) ON DELETE RESTRICT

### **return_notes**

Return notes for event stock returns (full returns only).

| Column | Type | Constraints | Description |
| --- | --- | --- | --- |
| id | BIGINT UNSIGNED | PK, AUTO_INCREMENT | Primary key |
| return_note_number | VARCHAR(100) | UNIQUE, NOT NULL | Auto-generated return note number |
| delivery_note_id | BIGINT UNSIGNED | UNIQUE, NOT NULL, FK(delivery_[notes.id](http://notes.id)) | Reference delivery note (1:1) |
| warehouse_id | BIGINT UNSIGNED | NOT NULL, FK([warehouses.id](http://warehouses.id)) | Return destination warehouse |
| status | ENUM | NOT NULL | pending, validated |
| has_discrepancy | BOOLEAN | DEFAULT FALSE | Discrepancy flag |
| returned_at | TIMESTAMP | NOT NULL | Return scan timestamp |
| validated_by | BIGINT UNSIGNED | NULLABLE, FK([users.id](http://users.id)) | Logistics officer who validated |
| validated_at | TIMESTAMP | NULLABLE | Validation timestamp |
| created_at | TIMESTAMP | NOT NULL | Record creation timestamp |
| updated_at | TIMESTAMP | NOT NULL | Record update timestamp |

**Indexes:**

- PRIMARY KEY (id)
- UNIQUE KEY (return_note_number)
- UNIQUE KEY (delivery_note_id)
- INDEX (warehouse_id)
- INDEX (status)
- INDEX (has_discrepancy)

**Foreign Keys:**

- delivery_note_id REFERENCES delivery_notes(id) ON DELETE RESTRICT
- warehouse_id REFERENCES warehouses(id) ON DELETE RESTRICT
- validated_by REFERENCES users(id) ON DELETE SET NULL

### **return_note_items**

Line items for return notes.

| Column | Type | Constraints | Description |
| --- | --- | --- | --- |
| id | BIGINT UNSIGNED | PK, AUTO_INCREMENT | Primary key |
| return_note_id | BIGINT UNSIGNED | NOT NULL, FK(return_[notes.id](http://notes.id)) | Return note reference |
| product_id | BIGINT UNSIGNED | NOT NULL, FK([products.id](http://products.id)) | Product reference |
| expected_quantity | DECIMAL(12,2) | NOT NULL | Expected return quantity (from delivery note) |
| returned_quantity | DECIMAL(12,2) | NOT NULL | Actual returned quantity |
| missing_quantity | DECIMAL(12,2) | NOT NULL | Missing quantity (expected - returned) |
| unit_cost | DECIMAL(12,2) | NOT NULL | Unit cost (from delivery note) |
| missing_value | DECIMAL(15,2) | NOT NULL | Value of missing items (missing_quantity × unit_cost) |
| created_at | TIMESTAMP | NOT NULL | Record creation timestamp |
| updated_at | TIMESTAMP | NOT NULL | Record update timestamp |

**Indexes:**

- PRIMARY KEY (id)
- INDEX (return_note_id)
- INDEX (product_id)

**Foreign Keys:**

- return_note_id REFERENCES return_notes(id) ON DELETE CASCADE
- product_id REFERENCES products(id) ON DELETE RESTRICT

---

## 7. Notification Tables

### **notifications**

Multi-channel notification log.

| Column | Type | Constraints | Description |
| --- | --- | --- | --- |
| id | BIGINT UNSIGNED | PK, AUTO_INCREMENT | Primary key |
| recipient_type | ENUM | NOT NULL | user, email, phone |
| recipient_id | BIGINT UNSIGNED | NULLABLE, FK([users.id](http://users.id)) | User ID (if recipient_type = user) |
| recipient_email | VARCHAR(255) | NULLABLE | Email (if recipient_type = email) |
| recipient_phone | VARCHAR(20) | NULLABLE | Phone (if recipient_type = phone) |
| channel | ENUM | NOT NULL | sms, email, push |
| trigger_type | VARCHAR(100) | NOT NULL | Notification trigger (depletion_approval, order_created, etc.) |
| subject | VARCHAR(255) | NULLABLE | Email subject or push title |
| body | TEXT | NOT NULL | Notification body |
| reference_type | VARCHAR(100) | NULLABLE | Reference entity type |
| reference_id | BIGINT UNSIGNED | NULLABLE | Reference entity ID |
| status | ENUM | NOT NULL | pending, sent, failed |
| sent_at | TIMESTAMP | NULLABLE | Sent timestamp |
| failed_reason | TEXT | NULLABLE | Failure reason |
| created_at | TIMESTAMP | NOT NULL | Record creation timestamp |

**Indexes:**

- PRIMARY KEY (id)
- INDEX (recipient_id)
- INDEX (recipient_email)
- INDEX (recipient_phone)
- INDEX (channel)
- INDEX (trigger_type)
- INDEX (status)
- INDEX (created_at)

**Foreign Keys:**

- recipient_id REFERENCES users(id) ON DELETE CASCADE

### **otp_tokens**

OTP tokens for distribution contact and event supplier portals.

| Column | Type | Constraints | Description |
| --- | --- | --- | --- |
| id | BIGINT UNSIGNED | PK, AUTO_INCREMENT | Primary key |
| email | VARCHAR(255) | NOT NULL | Recipient email |
| otp_code | VARCHAR(10) | NOT NULL | OTP code (6 digits) |
| purpose | VARCHAR(100) | NOT NULL | Portal access purpose |
| is_used | BOOLEAN | DEFAULT FALSE | Used flag |
| expires_at | TIMESTAMP | NOT NULL | Expiration timestamp (15 min) |
| created_at | TIMESTAMP | NOT NULL | Record creation timestamp |

**Indexes:**

- PRIMARY KEY (id)
- INDEX (email)
- INDEX (otp_code)
- INDEX (expires_at)
- INDEX (is_used)

---

## 8. Audit Tables

### **audit_logs**

Comprehensive audit trail.

| Column | Type | Constraints | Description |
| --- | --- | --- | --- |
| id | BIGINT UNSIGNED | PK, AUTO_INCREMENT | Primary key |
| user_id | BIGINT UNSIGNED | NULLABLE, FK([users.id](http://users.id)) | User who performed action |
| action | VARCHAR(100) | NOT NULL | Action performed (created, updated, deleted, approved, etc.) |
| entity_type | VARCHAR(100) | NOT NULL | Entity type (product, order, depletion_plan, etc.) |
| entity_id | BIGINT UNSIGNED | NULLABLE | Entity ID |
| old_values | JSON | NULLABLE | Previous values (for updates) |
| new_values | JSON | NULLABLE | New values |
| ip_address | VARCHAR(45) | NULLABLE | IP address (IPv4/IPv6) |
| user_agent | TEXT | NULLABLE | User agent string |
| created_at | TIMESTAMP | NOT NULL | Audit timestamp |

**Indexes:**

- PRIMARY KEY (id)
- INDEX (user_id)
- INDEX (action)
- INDEX (entity_type, entity_id)
- INDEX (created_at)

**Foreign Keys:**

- user_id REFERENCES users(id) ON DELETE SET NULL

---

## 9. Entity Relationships Summary

### **One-to-Many Relationships**

- users → user_assignments (1:N)
- hierarchy_nodes → user_assignments (1:N)
- hierarchy_nodes → hierarchy_nodes (parent-child) (1:N)
- hierarchy_nodes → distribution_points (1:N)
- products → inventory_locations (1:N)
- products → norms (1:N)
- warehouses → inventory_locations (1:N via polymorphic)
- distribution_points → inventory_locations (1:N via polymorphic)
- warehouses → depletion_plans (1:N)
- depletion_plans → depletion_plan_items (1:N)
- depletion_plans → shipments (1:N)
- shipments → shipment_items (1:N)
- distribution_points → outlets (1:N)
- outlets → norms (1:N)
- users (LSR) → outlets (1:N)
- outlets → availability_reports (1:N)
- availability_reports → availability_report_items (1:N)
- outlets → orders (1:N)
- distribution_points → orders (1:N)
- users (LSR) → orders (1:N)
- users (TM) → orders (1:N)
- orders → order_items (1:N)
- event_budgets → delivery_notes (1:N)
- event_suppliers → delivery_notes (1:N)
- warehouses → delivery_notes (1:N)
- delivery_notes → delivery_note_items (1:N)
- delivery_notes → return_notes (1:1)
- return_notes → return_note_items (1:N)

### **Many-to-Many Relationships**

- users ↔ hierarchy_nodes (via user_assignments)

### **Polymorphic Relationships**

- inventory_locations (location_type + location_id → warehouses OR distribution_points)
- inventory_movements (from_location_type/id, to_location_type/id → warehouses OR distribution_points OR outlets)

---

## 10. Database Indexes Strategy

### **Performance Optimization Guidelines**

**Primary Keys:**

- All tables use BIGINT UNSIGNED auto-increment IDs
- Supports high-volume transaction systems

**Unique Indexes:**

- Business keys (SKU, codes, numbers) for data integrity
- Email addresses for user authentication

**Foreign Key Indexes:**

- All FK columns indexed for JOIN performance
- CASCADE/SET NULL/RESTRICT configured based on business rules

**Query Optimization Indexes:**

- Status columns (for workflow queries)
- Date/timestamp columns (for reporting)
- Boolean flags (for filtering)
- Geo-tag validation flags (for discrepancy reports)

**Composite Indexes (Not Shown Above, Add as Needed):**

- (entity_type, entity_id, created_at) on audit_logs
- (outlet_id, submitted_at) on availability_reports
- (status, created_at) on orders
- (status, dispatched_at) on shipments

---

## 11. Data Retention & Archival

**Soft Deletes:**

- users, outlets, products, warehouses, distribution_points, event_suppliers
- Maintains referential integrity while hiding from active queries

**Hard Deletes (Cascade):**

- Child records (order_items, shipment_items, etc.) cascade delete with parent
- Maintains database cleanliness

**Audit Log Retention:**

- Recommended: 2 years minimum
- Consider partitioning audit_logs by year for performance

**Notification Log Retention:**

- Recommended: 6 months for troubleshooting
- Archive older records to cold storage

**Inventory Movement Log:**

- Permanent retention for financial audit compliance
- Consider partitioning by year after 3+ years

---

## 12. Migration & Seeding Notes

**Initial Seed Data Required:**

1. System admin user (id=1)
2. Root hierarchy node (level=0)
3. Static notification templates
4. Reason codes for stock adjustments

**Test Data Requirements:**

- 5-10 hierarchy nodes (multi-level)
- 20-30 products
- 2-3 warehouses
- 10-15 distribution points
- 50-100 outlets
- 10-15 users across roles
- Sample norms for outlets

**Production Cutover Checklist:**

- Import existing products, warehouses, distribution points
- Import outlet master data with geo-tags
- Import existing users
- Set up hierarchy structure
- Configure norms
- Initialize inventory locations with opening stock
- Generate initial inventory movements for audit trail

---

## 13. Database Performance Considerations

**Expected Load:**

- 100-200 concurrent mobile users (LSRs/TMs)
- 10-20 concurrent web admin users
- Peak: 500-1000 availability reports/day
- Peak: 200-400 orders/day
- 5-10 depletion plans/week

**Optimization Strategies:**

- Enable query cache for read-heavy tables (products, outlets, norms)
- Partition audit_logs and inventory_movements by date
- Implement Redis caching for:
    - User sessions
    - Hierarchy tree
    - Product catalog
    - Outlet-LSR assignments
- Use read replicas for reporting queries
- Configure connection pooling (min 20, max 100 connections)

**Monitoring:**

- Slow query log (>1s threshold)
- Table lock wait monitoring
- Disk space alerts (>80% capacity)
- Replication lag monitoring (if using replicas)

---

**Document Version:** 1.0

**Last Updated:** 2026-02-01

**Maintained By:** Technical Lead / Database Architect