**Prepared by:** Oditha **Date:** 23 January 2026 **Version:** 1.0 **Reference:** [FRD - HoReCa Field Operations & Workflow Management](https://www.notion.so/FRD-HoReCa-Field-Operations-Workflow-Management-2f7cd2773bc548c9a2595d856f15ac15?pvs=21) --- ## Overview This document provides the complete database schema for the Lion HoReCa Excellence system. The database is designed for **MySQL** and supports the Commercial Excellence Laravel System Admin and Flutter Mobile App. --- ## Database Architecture ### Technology Stack - **RDBMS:** MySQL 8.0+ - **Storage Engine:** InnoDB - **Character Set:** utf8mb4 - **Collation:** utf8mb4_unicode_ci - **Timezone:** UTC (all timestamps stored in UTC) ### Design Principles - Normalization: 3NF (Third Normal Form) - Referential integrity enforced via foreign keys - Soft deletes for critical tables (deleted_at column) - Timestamps for audit trail (created_at, updated_at) - Indexes on frequently queried columns - JSON columns for flexible data structures where appropriate --- ## Table Definitions ### 1. users **Description:** User accounts for all system users (LSR, TM, SM, Admins) **Columns:** | Column | Type | Constraints | Description | | ----------------- | ---------------------------------------------------- | ------------------ | --------------------------- | | id | BIGINT UNSIGNED | PK, AUTO_INCREMENT | User ID | | name | VARCHAR(255) | NOT NULL | Full name | | email | VARCHAR(255) | NOT NULL, UNIQUE | Email address | | password | VARCHAR(255) | NOT NULL | Hashed password | | role | ENUM('admin', 'regional_manager', 'sm', 'tm', 'lsr') | NOT NULL | User role | | territory_id | BIGINT UNSIGNED | NULL, FK | Territory assignment | | region_id | BIGINT UNSIGNED | NULL, FK | Region assignment | | reports_to_id | BIGINT UNSIGNED | NULL, FK | Manager user ID (hierarchy) | | phone | VARCHAR(50) | NULL | Phone number | | avatar_url | VARCHAR(500) | NULL | Profile photo URL | | status | ENUM('active', 'inactive') | DEFAULT 'active' | Account status | | last_login_at | TIMESTAMP | NULL | Last login timestamp | | email_verified_at | TIMESTAMP | NULL | Email verification | | remember_token | VARCHAR(100) | NULL | Session token | | created_at | TIMESTAMP | NOT NULL | Record creation | | updated_at | TIMESTAMP | NOT NULL | Last update | | deleted_at | TIMESTAMP | NULL | Soft delete | **Indexes:** - PRIMARY KEY (id) - UNIQUE KEY (email) - INDEX (role) - INDEX (status) - INDEX (territory_id) - INDEX (region_id) - INDEX (reports_to_id) **Foreign Keys:** - territory_id REFERENCES territories(id) ON DELETE SET NULL - region_id REFERENCES regions(id) ON DELETE SET NULL - reports_to_id REFERENCES users(id) ON DELETE SET NULL --- ### 2. territories **Description:** Geographic territories for outlet and user assignment **Columns:** | Column | Type | Constraints | Description | | ---------- | -------------------------- | ------------------ | --------------- | | id | BIGINT UNSIGNED | PK, AUTO_INCREMENT | Territory ID | | name | VARCHAR(255) | NOT NULL | Territory name | | region_id | BIGINT UNSIGNED | NOT NULL, FK | Parent region | | status | ENUM('active', 'inactive') | DEFAULT 'active' | Status | | created_at | TIMESTAMP | NOT NULL | Record creation | | updated_at | TIMESTAMP | NOT NULL | Last update | **Indexes:** - PRIMARY KEY (id) - INDEX (region_id) - INDEX (status) **Foreign Keys:** - region_id REFERENCES regions(id) ON DELETE CASCADE --- ### 3. regions **Description:** Geographic regions for hierarchical organization **Columns:** | Column | Type | Constraints | Description | | ---------- | -------------------------- | ------------------ | --------------- | | id | BIGINT UNSIGNED | PK, AUTO_INCREMENT | Region ID | | name | VARCHAR(255) | NOT NULL | Region name | | status | ENUM('active', 'inactive') | DEFAULT 'active' | Status | | created_at | TIMESTAMP | NOT NULL | Record creation | | updated_at | TIMESTAMP | NOT NULL | Last update | **Indexes:** - PRIMARY KEY (id) - INDEX (status) --- ### 4. workflows **Description:** Workflow definitions configured by admins **Columns:** | Column | Type | Constraints | Description | | ----------- | ------------------------------------------------------------------------------------------ | ------------------ | --------------- | | id | BIGINT UNSIGNED | PK, AUTO_INCREMENT | Workflow ID | | name | VARCHAR(255) | NOT NULL, UNIQUE | Workflow name | | description | TEXT | NULL | Description | | category | ENUM('impulse', 'cooler', 'branding', 'pouring_material', 'competitor', 'audit', 'custom') | NOT NULL | Category | | icon | VARCHAR(10) | NULL | Emoji icon | | color | VARCHAR(20) | NULL | Hex color code | | status | ENUM('active', 'inactive') | DEFAULT 'active' | Status | | created_at | TIMESTAMP | NOT NULL | Record creation | | updated_at | TIMESTAMP | NOT NULL | Last update | | deleted_at | TIMESTAMP | NULL | Soft delete | **Indexes:** - PRIMARY KEY (id) - UNIQUE KEY (name) - INDEX (category) - INDEX (status) --- ### 5. workflow_steps **Description:** Individual steps within workflows **Columns:** | Column | Type | Constraints | Description | | ---------------- | ------------------------------ | ------------------ | ----------------- | | id | BIGINT UNSIGNED | PK, AUTO_INCREMENT | Step ID | | workflow_id | BIGINT UNSIGNED | NOT NULL, FK | Parent workflow | | step_order | INT | NOT NULL | Execution order | | name | VARCHAR(255) | NOT NULL | Step name | | description | TEXT | NULL | Description | | question_type | ENUM('selection', 'text_area') | NOT NULL | Question type | | is_mandatory | BOOLEAN | DEFAULT FALSE | Required step | | allow_skip | BOOLEAN | DEFAULT FALSE | Can be skipped | | allow_remarks | BOOLEAN | DEFAULT FALSE | Enable remarks | | require_photo | BOOLEAN | DEFAULT FALSE | Photo required | | photo_count | INT | DEFAULT 1 | Max photos | | sample_photo_url | VARCHAR(500) | NULL | Reference photo | | instructions | TEXT | NULL | User instructions | | created_at | TIMESTAMP | NOT NULL | Record creation | | updated_at | TIMESTAMP | NOT NULL | Last update | **Indexes:** - PRIMARY KEY (id) - INDEX (workflow_id, step_order) - INDEX (workflow_id) **Foreign Keys:** - workflow_id REFERENCES workflows(id) ON DELETE CASCADE --- ### 6. workflow_step_options **Description:** Options for selection-type workflow steps **Columns:** | Column | Type | Constraints | Description | | --------------------------- | --------------- | ------------------ | --------------- | | id | BIGINT UNSIGNED | PK, AUTO_INCREMENT | Option ID | | step_id | BIGINT UNSIGNED | NOT NULL, FK | Parent step | | option_text | VARCHAR(255) | NOT NULL | Display text | | option_value | VARCHAR(255) | NOT NULL | Value | | triggers_service_request | BOOLEAN | DEFAULT FALSE | SR trigger | | service_request_category_id | BIGINT UNSIGNED | NULL, FK | SR category | | service_request_template_id | BIGINT UNSIGNED | NULL, FK | SR template | | created_at | TIMESTAMP | NOT NULL | Record creation | | updated_at | TIMESTAMP | NOT NULL | Last update | **Indexes:** - PRIMARY KEY (id) - INDEX (step_id) - INDEX (service_request_category_id) - INDEX (service_request_template_id) **Foreign Keys:** - step_id REFERENCES workflow_steps(id) ON DELETE CASCADE - service_request_category_id REFERENCES service_request_categories(id) ON DELETE SET NULL - service_request_template_id REFERENCES service_request_templates(id) ON DELETE SET NULL --- ### 7. workflow_step_conditions **Description:** Conditional logic for dynamic step visibility **Columns:** | Column | Type | Constraints | Description | | ------------------ | ---------------------------------------- | ------------------ | ------------------------------------ | | id | BIGINT UNSIGNED | PK, AUTO_INCREMENT | Condition ID | | step_id | BIGINT UNSIGNED | NOT NULL, FK | Target step (shown if condition met) | | condition_step_id | BIGINT UNSIGNED | NOT NULL, FK | Condition step | | condition_operator | ENUM('equals', 'not_equals', 'contains') | NOT NULL | Comparison operator | | condition_value | VARCHAR(255) | NOT NULL | Expected value | | logic_operator | ENUM('and', 'or') | DEFAULT 'and' | Multi-condition logic | | created_at | TIMESTAMP | NOT NULL | Record creation | | updated_at | TIMESTAMP | NOT NULL | Last update | **Indexes:** - PRIMARY KEY (id) - INDEX (step_id) - INDEX (condition_step_id) **Foreign Keys:** - step_id REFERENCES workflow_steps(id) ON DELETE CASCADE - condition_step_id REFERENCES workflow_steps(id) ON DELETE CASCADE --- ### 8. workflow_group_templates **Description:** Bundled workflow templates for outlet assignment **Columns:** | Column | Type | Constraints | Description | | ----------- | -------------------------- | ------------------ | --------------- | | id | BIGINT UNSIGNED | PK, AUTO_INCREMENT | Template ID | | name | VARCHAR(255) | NOT NULL, UNIQUE | Template name | | description | TEXT | NULL | Description | | icon | VARCHAR(10) | NULL | Emoji icon | | color | VARCHAR(20) | NULL | Hex color code | | status | ENUM('active', 'inactive') | DEFAULT 'active' | Status | | created_at | TIMESTAMP | NOT NULL | Record creation | | updated_at | TIMESTAMP | NOT NULL | Last update | | deleted_at | TIMESTAMP | NULL | Soft delete | **Indexes:** - PRIMARY KEY (id) - UNIQUE KEY (name) - INDEX (status) --- ### 9. workflow_group_template_workflows **Description:** Workflows included in templates **Columns:** | Column | Type | Constraints | Description | | --------------- | --------------- | ------------------ | --------------- | | id | BIGINT UNSIGNED | PK, AUTO_INCREMENT | Record ID | | template_id | BIGINT UNSIGNED | NOT NULL, FK | Template | | workflow_id | BIGINT UNSIGNED | NOT NULL, FK | Workflow | | is_mandatory | BOOLEAN | DEFAULT FALSE | Required | | execution_order | INT | NOT NULL | Order | | created_at | TIMESTAMP | NOT NULL | Record creation | | updated_at | TIMESTAMP | NOT NULL | Last update | **Indexes:** - PRIMARY KEY (id) - INDEX (template_id, execution_order) - INDEX (workflow_id) - UNIQUE KEY (template_id, workflow_id) **Foreign Keys:** - template_id REFERENCES workflow_group_templates(id) ON DELETE CASCADE - workflow_id REFERENCES workflows(id) ON DELETE CASCADE --- ### 10. outlets **Description:** HoReCa outlet master data **Columns:** | Column | Type | Constraints | Description | | -------------------------- | --------------------------------------------------- | ------------------ | --------------- | | id | BIGINT UNSIGNED | PK, AUTO_INCREMENT | Outlet ID | | name | VARCHAR(255) | NOT NULL | Outlet name | | address | TEXT | NOT NULL | Full address | | latitude | DECIMAL(10,8) | NOT NULL | Latitude | | longitude | DECIMAL(11,8) | NOT NULL | Longitude | | outlet_type | ENUM('restaurant', 'hotel', 'cafe', 'bar', 'other') | NOT NULL | Type | | contact_phone | VARCHAR(50) | NULL | Phone | | contact_email | VARCHAR(255) | NULL | Email | | territory_id | BIGINT UNSIGNED | NOT NULL, FK | Territory | | region_id | BIGINT UNSIGNED | NOT NULL, FK | Region | | assigned_lsr_id | BIGINT UNSIGNED | NULL, FK | Assigned LSR | | assigned_tm_id | BIGINT UNSIGNED | NULL, FK | Assigned TM | | assigned_sm_id | BIGINT UNSIGNED | NULL, FK | Assigned SM | | workflow_group_template_id | BIGINT UNSIGNED | NOT NULL, FK | Template | | status | ENUM('active', 'inactive') | DEFAULT 'active' | Status | | created_at | TIMESTAMP | NOT NULL | Record creation | | updated_at | TIMESTAMP | NOT NULL | Last update | | deleted_at | TIMESTAMP | NULL | Soft delete | **Indexes:** - PRIMARY KEY (id) - INDEX (outlet_type) - INDEX (territory_id) - INDEX (region_id) - INDEX (assigned_lsr_id) - INDEX (assigned_tm_id) - INDEX (assigned_sm_id) - INDEX (workflow_group_template_id) - INDEX (status) - INDEX (latitude, longitude) **Foreign Keys:** - territory_id REFERENCES territories(id) ON DELETE RESTRICT - region_id REFERENCES regions(id) ON DELETE RESTRICT - assigned_lsr_id REFERENCES users(id) ON DELETE SET NULL - assigned_tm_id REFERENCES users(id) ON DELETE SET NULL - assigned_sm_id REFERENCES users(id) ON DELETE SET NULL - workflow_group_template_id REFERENCES workflow_group_templates(id) ON DELETE RESTRICT --- ### 11. outlet_workflow_customizations **Description:** Outlet-specific workflow enable/disable overrides **Columns:** | Column | Type | Constraints | Description | | ----------- | --------------- | ------------------ | --------------- | | id | BIGINT UNSIGNED | PK, AUTO_INCREMENT | Record ID | | outlet_id | BIGINT UNSIGNED | NOT NULL, FK | Outlet | | workflow_id | BIGINT UNSIGNED | NOT NULL, FK | Workflow | | is_enabled | BOOLEAN | DEFAULT TRUE | Enabled | | created_at | TIMESTAMP | NOT NULL | Record creation | | updated_at | TIMESTAMP | NOT NULL | Last update | **Indexes:** - PRIMARY KEY (id) - UNIQUE KEY (outlet_id, workflow_id) - INDEX (outlet_id) **Foreign Keys:** - outlet_id REFERENCES outlets(id) ON DELETE CASCADE - workflow_id REFERENCES workflows(id) ON DELETE CASCADE --- ### 12. outlet_workflow_steps **Description:** Outlet-specific step customizations **Columns:** | Column | Type | Constraints | Description | | ------------------ | --------------- | ------------------ | --------------- | | id | BIGINT UNSIGNED | PK, AUTO_INCREMENT | Record ID | | outlet_workflow_id | BIGINT UNSIGNED | NOT NULL, FK | Outlet workflow | | step_id | BIGINT UNSIGNED | NOT NULL, FK | Step | | is_enabled | BOOLEAN | DEFAULT TRUE | Enabled | | custom_step_order | INT | NULL | Override order | | created_at | TIMESTAMP | NOT NULL | Record creation | | updated_at | TIMESTAMP | NOT NULL | Last update | **Indexes:** - PRIMARY KEY (id) - INDEX (outlet_workflow_id) - INDEX (step_id) **Foreign Keys:** - outlet_workflow_id REFERENCES outlet_workflow_customizations(id) ON DELETE CASCADE - step_id REFERENCES workflow_steps(id) ON DELETE CASCADE --- ### 13. brands **Description:** Brand master list **Columns:** | Column | Type | Constraints | Description | | ---------- | -------------------------- | ------------------ | --------------- | | id | BIGINT UNSIGNED | PK, AUTO_INCREMENT | Brand ID | | name | VARCHAR(255) | NOT NULL, UNIQUE | Brand name | | status | ENUM('active', 'inactive') | DEFAULT 'active' | Status | | created_at | TIMESTAMP | NOT NULL | Record creation | | updated_at | TIMESTAMP | NOT NULL | Last update | **Indexes:** - PRIMARY KEY (id) - UNIQUE KEY (name) - INDEX (status) --- ### 14. outlet_brands **Description:** Brand configuration per outlet **Columns:** | Column | Type | Constraints | Description | | ------------ | --------------- | ------------------ | --------------- | | id | BIGINT UNSIGNED | PK, AUTO_INCREMENT | Record ID | | outlet_id | BIGINT UNSIGNED | NOT NULL, FK | Outlet | | brand_id | BIGINT UNSIGNED | NOT NULL, FK | Brand | | is_must_have | BOOLEAN | DEFAULT FALSE | Required | | created_at | TIMESTAMP | NOT NULL | Record creation | | updated_at | TIMESTAMP | NOT NULL | Last update | **Indexes:** - PRIMARY KEY (id) - UNIQUE KEY (outlet_id, brand_id) - INDEX (outlet_id) - INDEX (brand_id) **Foreign Keys:** - outlet_id REFERENCES outlets(id) ON DELETE CASCADE - brand_id REFERENCES brands(id) ON DELETE CASCADE --- ### 15. outlet_equipment **Description:** Equipment and materials configured per outlet **Columns:** | Column | Type | Constraints | Description | | -------------- | ------------------------------------------------------- | ------------------ | --------------- | | id | BIGINT UNSIGNED | PK, AUTO_INCREMENT | Record ID | | outlet_id | BIGINT UNSIGNED | NOT NULL, FK | Outlet | | equipment_type | ENUM('cooler', 'branding_material', 'pouring_material') | NOT NULL | Type | | equipment_name | VARCHAR(255) | NOT NULL | Name | | quantity | INT | NOT NULL | Quantity | | serial_numbers | TEXT | NULL | Serials (JSON) | | created_at | TIMESTAMP | NOT NULL | Record creation | | updated_at | TIMESTAMP | NOT NULL | Last update | **Indexes:** - PRIMARY KEY (id) - INDEX (outlet_id) - INDEX (equipment_type) **Foreign Keys:** - outlet_id REFERENCES outlets(id) ON DELETE CASCADE --- ### 16. posm_norms **Description:** POSM standard quantities per outlet type **Columns:** | Column | Type | Constraints | Description | | ----------------- | --------------------------------------------------- | ------------------ | --------------- | | id | BIGINT UNSIGNED | PK, AUTO_INCREMENT | Norm ID | | outlet_type | ENUM('restaurant', 'hotel', 'cafe', 'bar', 'other') | NOT NULL | Type | | material_name | VARCHAR(255) | NOT NULL | Material | | material_category | ENUM('signage', 'cooler', 'branding', 'pouring') | NOT NULL | Category | | standard_quantity | INT | NOT NULL | Std qty | | version | INT | DEFAULT 1 | Version | | effective_date | DATE | NOT NULL | Effective from | | created_at | TIMESTAMP | NOT NULL | Record creation | | updated_at | TIMESTAMP | NOT NULL | Last update | **Indexes:** - PRIMARY KEY (id) - INDEX (outlet_type) - INDEX (material_category) - INDEX (version, effective_date) --- ### 17. posm_audits **Description:** POSM audit tasks and schedule **Columns:** | Column | Type | Constraints | Description | | -------------- | --------------------------------------------- | ------------------- | ----------------------------- | | id | BIGINT UNSIGNED | PK, AUTO_INCREMENT | Audit ID | | outlet_id | BIGINT UNSIGNED | NOT NULL, FK | Outlet | | assigned_tm_id | BIGINT UNSIGNED | NOT NULL, FK | Assigned TM | | scheduled_date | DATE | NOT NULL | Scheduled | | due_date | DATE | NOT NULL | Due date (scheduled + 5 days) | | completed_date | DATE | NULL | Completed | | status | ENUM('scheduled', 'in_progress', 'completed') | DEFAULT 'scheduled' | Status | | is_overdue | BOOLEAN | DEFAULT FALSE | Overdue flag | | created_by | BIGINT UNSIGNED | NULL, FK | Creator | | created_at | TIMESTAMP | NOT NULL | Record creation | | updated_at | TIMESTAMP | NOT NULL | Last update | **Indexes:** - PRIMARY KEY (id) - INDEX (outlet_id) - INDEX (assigned_tm_id) - INDEX (scheduled_date) - INDEX (due_date) - INDEX (status) - INDEX (is_overdue) **Foreign Keys:** - outlet_id REFERENCES outlets(id) ON DELETE CASCADE - assigned_tm_id REFERENCES users(id) ON DELETE RESTRICT - created_by REFERENCES users(id) ON DELETE SET NULL --- ### 18. posm_audit_items **Description:** Individual material items in audit **Columns:** | Column | Type | Constraints | Description | | ---------------- | --------------- | ------------------ | --------------- | | id | BIGINT UNSIGNED | PK, AUTO_INCREMENT | Item ID | | audit_id | BIGINT UNSIGNED | NOT NULL, FK | Audit | | material_name | VARCHAR(255) | NOT NULL | Material | | norm_quantity | INT | NOT NULL | Norm | | actual_quantity | INT | NOT NULL | Actual | | variance | INT | NOT NULL | Difference | | remarks | TEXT | NULL | Remarks | | needs_restocking | BOOLEAN | DEFAULT FALSE | Restock flag | | created_at | TIMESTAMP | NOT NULL | Record creation | **Indexes:** - PRIMARY KEY (id) - INDEX (audit_id) **Foreign Keys:** - audit_id REFERENCES posm_audits(id) ON DELETE CASCADE --- ### 19. service_request_categories **Description:** SR category definitions **Columns:** | Column | Type | Constraints | Description | | -------------------- | ----------------------------------------- | ------------------ | ----------------- | | id | BIGINT UNSIGNED | PK, AUTO_INCREMENT | Category ID | | name | VARCHAR(255) | NOT NULL, UNIQUE | Category name | | description | TEXT | NULL | Description | | priority | ENUM('low', 'medium', 'high', 'critical') | DEFAULT 'medium' | Priority | | sla_hours | INT | NOT NULL | SLA duration | | assignment_hierarchy | JSON | NULL | Auto-assign rules | | status | ENUM('active', 'inactive') | DEFAULT 'active' | Status | | created_at | TIMESTAMP | NOT NULL | Record creation | | updated_at | TIMESTAMP | NOT NULL | Last update | **Indexes:** - PRIMARY KEY (id) - UNIQUE KEY (name) - INDEX (status) --- ### 20. service_request_templates **Description:** SR templates for standardized requests **Columns:** | Column | Type | Constraints | Description | | -------------------- | --------------- | ------------------ | ------------------------------- | | id | BIGINT UNSIGNED | PK, AUTO_INCREMENT | Template ID | | category_id | BIGINT UNSIGNED | NOT NULL, FK | Category | | name | VARCHAR(255) | NOT NULL | Template name | | description_template | TEXT | NOT NULL | Template text with placeholders | | custom_fields | JSON | NULL | Additional fields | | created_at | TIMESTAMP | NOT NULL | Record creation | | updated_at | TIMESTAMP | NOT NULL | Last update | **Indexes:** - PRIMARY KEY (id) - INDEX (category_id) **Foreign Keys:** - category_id REFERENCES service_request_categories(id) ON DELETE CASCADE --- ### 21. service_requests **Description:** Service request records **Columns:** | Column | Type | Constraints | Description | | ------------------- | -------------------------------------------------------------- | ------------------ | --------------- | | id | BIGINT UNSIGNED | PK, AUTO_INCREMENT | SR ID | | sr_number | VARCHAR(50) | NOT NULL, UNIQUE | SR-XXXXX | | title | VARCHAR(255) | NOT NULL | Title | | description | TEXT | NOT NULL | Description | | category_id | BIGINT UNSIGNED | NOT NULL, FK | Category | | outlet_id | BIGINT UNSIGNED | NOT NULL, FK | Outlet | | workflow_id | BIGINT UNSIGNED | NULL, FK | Workflow | | step_id | BIGINT UNSIGNED | NULL, FK | Step | | created_by | BIGINT UNSIGNED | NOT NULL, FK | Creator | | assigned_to | BIGINT UNSIGNED | NULL, FK | Assignee | | status | ENUM('open', 'in_progress', 'resolved', 'closed', 'cancelled') | DEFAULT 'open' | Status | | priority | ENUM('low', 'medium', 'high', 'critical') | NOT NULL | Priority | | sla_due_date | TIMESTAMP | NOT NULL | SLA deadline | | resolved_date | TIMESTAMP | NULL | Resolved | | closed_date | TIMESTAMP | NULL | Closed | | cancellation_reason | TEXT | NULL | Cancel reason | | created_at | TIMESTAMP | NOT NULL | Record creation | | updated_at | TIMESTAMP | NOT NULL | Last update | **Indexes:** - PRIMARY KEY (id) - UNIQUE KEY (sr_number) - INDEX (category_id) - INDEX (outlet_id) - INDEX (workflow_id) - INDEX (created_by) - INDEX (assigned_to) - INDEX (status) - INDEX (priority) - INDEX (sla_due_date) **Foreign Keys:** - category_id REFERENCES service_request_categories(id) ON DELETE RESTRICT - outlet_id REFERENCES outlets(id) ON DELETE CASCADE - workflow_id REFERENCES workflows(id) ON DELETE SET NULL - step_id REFERENCES workflow_steps(id) ON DELETE SET NULL - created_by REFERENCES users(id) ON DELETE RESTRICT - assigned_to REFERENCES users(id) ON DELETE SET NULL --- ### 22. service_request_activities **Description:** Activity log for service requests **Columns:** | Column | Type | Constraints | Description | | ------------------ | -------------------------------------------------------------- | ------------------ | ----------- | | id | BIGINT UNSIGNED | PK, AUTO_INCREMENT | Activity ID | | service_request_id | BIGINT UNSIGNED | NOT NULL, FK | SR | | user_id | BIGINT UNSIGNED | NOT NULL, FK | User | | action_type | ENUM('status_change', 'reassignment', 'comment', 'attachment') | NOT NULL | Action | | old_value | VARCHAR(255) | NULL | Old value | | new_value | VARCHAR(255) | NULL | New value | | comment | TEXT | NULL | Comment | | created_at | TIMESTAMP | NOT NULL | Timestamp | **Indexes:** - PRIMARY KEY (id) - INDEX (service_request_id) - INDEX (user_id) - INDEX (created_at) **Foreign Keys:** - service_request_id REFERENCES service_requests(id) ON DELETE CASCADE - user_id REFERENCES users(id) ON DELETE RESTRICT --- ### 23. outlet_visits **Description:** Check-in/check-out visit logs **Columns:** | Column | Type | Constraints | Description | | ------------------ | ----------------------------------------- | ------------------ | --------------- | | id | BIGINT UNSIGNED | PK, AUTO_INCREMENT | Visit ID | | outlet_id | BIGINT UNSIGNED | NOT NULL, FK | Outlet | | user_id | BIGINT UNSIGNED | NOT NULL, FK | Visitor | | check_in_time | TIMESTAMP | NOT NULL | Check-in | | check_in_lat | DECIMAL(10,8) | NOT NULL | Latitude | | check_in_lng | DECIMAL(11,8) | NOT NULL | Longitude | | check_out_time | TIMESTAMP | NULL | Check-out | | time_spent_minutes | INT | NULL | Duration | | visit_outcome | ENUM('completed', 'partial', 'no_action') | NULL | Outcome | | outcome_reason | TEXT | NULL | Reason | | created_at | TIMESTAMP | NOT NULL | Record creation | **Indexes:** - PRIMARY KEY (id) - INDEX (outlet_id) - INDEX (user_id) - INDEX (check_in_time) - INDEX (check_out_time) **Foreign Keys:** - outlet_id REFERENCES outlets(id) ON DELETE CASCADE - user_id REFERENCES users(id) ON DELETE RESTRICT --- ### 24. workflow_executions **Description:** Workflow execution instances **Columns:** | Column | Type | Constraints | Description | | ------------ | --------------------------------------------- | --------------------- | --------------- | | id | BIGINT UNSIGNED | PK, AUTO_INCREMENT | Execution ID | | visit_id | BIGINT UNSIGNED | NOT NULL, FK | Visit | | outlet_id | BIGINT UNSIGNED | NOT NULL, FK | Outlet | | workflow_id | BIGINT UNSIGNED | NOT NULL, FK | Workflow | | user_id | BIGINT UNSIGNED | NOT NULL, FK | User | | started_at | TIMESTAMP | NOT NULL | Started | | completed_at | TIMESTAMP | NULL | Completed | | status | ENUM('in_progress', 'completed', 'abandoned') | DEFAULT 'in_progress' | Status | | created_at | TIMESTAMP | NOT NULL | Record creation | | updated_at | TIMESTAMP | NOT NULL | Last update | **Indexes:** - PRIMARY KEY (id) - INDEX (visit_id) - INDEX (outlet_id) - INDEX (workflow_id) - INDEX (user_id) - INDEX (status) - INDEX (started_at) **Foreign Keys:** - visit_id REFERENCES outlet_visits(id) ON DELETE CASCADE - outlet_id REFERENCES outlets(id) ON DELETE CASCADE - workflow_id REFERENCES workflows(id) ON DELETE RESTRICT - user_id REFERENCES users(id) ON DELETE RESTRICT --- ### 25. workflow_execution_responses **Description:** User responses for each workflow step **Columns:** | Column | Type | Constraints | Description | | -------------- | --------------- | ------------------ | ---------------- | | id | BIGINT UNSIGNED | PK, AUTO_INCREMENT | Response ID | | execution_id | BIGINT UNSIGNED | NOT NULL, FK | Execution | | step_id | BIGINT UNSIGNED | NOT NULL, FK | Step | | response_value | VARCHAR(255) | NULL | Response | | response_text | TEXT | NULL | Text response | | remarks | TEXT | NULL | Remarks | | is_skipped | BOOLEAN | DEFAULT FALSE | Skipped | | skip_reason | VARCHAR(255) | NULL | Skip reason | | photo_urls | JSON | NULL | Photo URLs array | | created_at | TIMESTAMP | NOT NULL | Timestamp | **Indexes:** - PRIMARY KEY (id) - INDEX (execution_id) - INDEX (step_id) **Foreign Keys:** - execution_id REFERENCES workflow_executions(id) ON DELETE CASCADE - step_id REFERENCES workflow_steps(id) ON DELETE RESTRICT --- ### 26. files **Description:** File uploads (photos, attachments) **Columns:** | Column | Type | Constraints | Description | | ----------- | --------------- | ------------------ | -------------- | | id | BIGINT UNSIGNED | PK, AUTO_INCREMENT | File ID | | file_name | VARCHAR(255) | NOT NULL | Original name | | file_path | VARCHAR(500) | NOT NULL | Storage path | | file_url | VARCHAR(500) | NOT NULL | Public URL | | file_type | VARCHAR(100) | NOT NULL | MIME type | | file_size | BIGINT | NOT NULL | Size in bytes | | uploaded_by | BIGINT UNSIGNED | NOT NULL, FK | Uploader | | entity_type | VARCHAR(100) | NULL | Related entity | | entity_id | BIGINT UNSIGNED | NULL | Related ID | | created_at | TIMESTAMP | NOT NULL | Upload time | **Indexes:** - PRIMARY KEY (id) - INDEX (uploaded_by) - INDEX (entity_type, entity_id) **Foreign Keys:** - uploaded_by REFERENCES users(id) ON DELETE RESTRICT --- ### 27. notifications **Description:** Push notification log **Columns:** | Column | Type | Constraints | Description | | ----------- | --------------- | ------------------ | --------------- | | id | BIGINT UNSIGNED | PK, AUTO_INCREMENT | Notification ID | | user_id | BIGINT UNSIGNED | NOT NULL, FK | Recipient | | title | VARCHAR(255) | NOT NULL | Title | | body | TEXT | NOT NULL | Message | | type | VARCHAR(50) | NOT NULL | Type | | entity_type | VARCHAR(100) | NULL | Related entity | | entity_id | BIGINT UNSIGNED | NULL | Related ID | | is_read | BOOLEAN | DEFAULT FALSE | Read status | | sent_at | TIMESTAMP | NOT NULL | Sent time | | read_at | TIMESTAMP | NULL | Read time | **Indexes:** - PRIMARY KEY (id) - INDEX (user_id) - INDEX (is_read) - INDEX (sent_at) **Foreign Keys:** - user_id REFERENCES users(id) ON DELETE CASCADE --- ## Entity Relationship Diagram ### Core Relationships **User Management:** - users → territories (many-to-one) - users → regions (many-to-one) - users → users (self-referencing hierarchy via reports_to_id) **Geographic Hierarchy:** - territories → regions (many-to-one) **Workflow Configuration:** - workflows → workflow_steps (one-to-many) - workflow_steps → workflow_step_options (one-to-many) - workflow_steps → workflow_step_conditions (one-to-many) - workflow_group_templates → workflow_group_template_workflows → workflows (many-to-many) **Outlet Management:** - outlets → territories (many-to-one) - outlets → regions (many-to-one) - outlets → users (many-to-one for LSR, TM, SM) - outlets → workflow_group_templates (many-to-one) - outlets → outlet_workflow_customizations → workflows (many-to-many) - outlets → outlet_brands → brands (many-to-many) - outlets → outlet_equipment (one-to-many) **POSM Module:** - posm_audits → outlets (many-to-one) - posm_audits → users (many-to-one for assigned TM) - posm_audits → posm_audit_items (one-to-many) **Service Requests:** - service_requests → service_request_categories (many-to-one) - service_requests → outlets (many-to-one) - service_requests → workflows (many-to-one, nullable) - service_requests → users (many-to-one for creator and assignee) - service_requests → service_request_activities (one-to-many) - service_request_templates → service_request_categories (many-to-one) **Field Operations:** - outlet_visits → outlets (many-to-one) - outlet_visits → users (many-to-one) - workflow_executions → outlet_visits (many-to-one) - workflow_executions → workflows (many-to-one) - workflow_executions → workflow_execution_responses (one-to-many) - workflow_execution_responses → workflow_steps (many-to-one) --- ## Data Retention & Archival ### Retention Periods - **Visit logs:** 3 years - **Workflow executions:** 3 years - **Photos:** 2 years - **Service requests:** 5 years - **Audit records:** 5 years - **Activity logs:** 5 years ### Archival Strategy - Monthly archive jobs move old records to archive tables - Archive tables: Same schema with `_archive` suffix - Read-only access to archive data via reporting interface --- ## Performance Optimization ### Indexing Strategy - Primary keys on all tables - Foreign keys indexed - Query-heavy columns indexed (status, dates, user IDs) - Composite indexes for multi-column queries ### Query Optimization - Use EXPLAIN to analyze slow queries - Avoid SELECT \*, specify columns - Pagination with LIMIT and OFFSET - Use JOIN instead of subqueries where possible ### Caching - Redis for frequently accessed data: - User sessions - Workflow configurations - Outlet lists - Dashboard metrics (TTL: 5 minutes) --- ## Backup & Recovery ### Backup Schedule - **Full backup:** Daily at 02:00 UTC - **Incremental backup:** Every 4 hours - **Transaction log backup:** Every 15 minutes ### Retention - Daily backups: 30 days - Weekly backups: 12 weeks - Monthly backups: 12 months ### Recovery - Point-in-time recovery (PITR) supported - RTO: 4 hours - RPO: 15 minutes --- ## Database Security ### Access Control - **Admin users:** Full access - **Application users:** Limited to specific databases - **Read-only users:** Reporting queries only ### Encryption - Data at rest: AES-256 encryption - Data in transit: TLS 1.3 - Sensitive columns encrypted: passwords (bcrypt), tokens ### Audit Logging - All DDL operations logged - Failed login attempts logged - Suspicious query patterns monitored --- **Document Status:** Final **Next Steps:** Share with backend development team for implementation