🔧 View-Safe Database Migration Tool

activity_logs:
  - id (int)
  - user_type (enum('admin','user','vendor'))
  - user_id (int)
  - action (varchar(100))
  - description (text)
  - ip_address (varchar(45))
  - user_agent (text)
  - created_at (timestamp)

approval_actions:
  - action_id (int)
  - request_id (int)
  - approver_id (int)
  - action (enum('approved','rejected','returned','delegated','escalated'))
  - step_number (int)
  - comments (text)
  - attachments (json)
  - approval_conditions (text)
  - next_approver_id (int)
  - processing_time_minutes (int)
  - created_at (timestamp)
  - ip_address (varchar(45))
  - user_agent (text)

approval_analytics:
  - analytics_id (int)
  - date_recorded (date)
  - approver_id (int)
  - department_id (int)
  - total_approvals (int)
  - total_rejections (int)
  - total_escalations (int)
  - avg_approval_time_hours (decimal(8,2))
  - overdue_approvals (int)
  - auto_approvals (int)
  - value_approved (decimal(15,2))
  - value_rejected (decimal(15,2))
  - created_at (timestamp)

approval_delegations:
  - delegation_id (int)
  - delegator_id (int)
  - delegate_id (int)
  - delegation_type (enum('temporary','permanent','emergency'))
  - start_date (date)
  - end_date (date)
  - max_amount (decimal(15,2))
  - department_restriction (int)
  - reason (text)
  - is_active (tinyint(1))
  - created_at (timestamp)
  - updated_at (timestamp)

approval_history:
  - history_id (int)
  - request_id (int)
  - action_by (int)
  - action_type (enum('submitted','approved','rejected','returned','cancelled'))
  - level_name (varchar(100))
  - comments (text)
  - old_status (varchar(50))
  - new_status (varchar(50))
  - action_date (timestamp)

approval_levels:
  - level_id (int)
  - workflow_id (int)
  - level_number (int)
  - level_name (varchar(100))
  - approver_role (varchar(50))
  - approver_user_id (int)
  - is_mandatory (tinyint(1))
  - can_skip (tinyint(1))
  - timeout_days (int)

approval_notifications:
  - notification_id (varchar(50))
  - request_id (int)
  - user_id (int)
  - notification_type (enum('email','sms','push','digest'))
  - event_type (varchar(50))
  - subject (varchar(255))
  - message (text)
  - template_used (varchar(100))
  - delivery_channel (varchar(20))
  - sent_at (timestamp)
  - delivery_status (enum('sent','failed','pending','bounced'))
  - delivery_attempts (int)
  - last_attempt_at (timestamp)
  - opened_at (timestamp)
  - clicked_at (timestamp)
  - error_message (text)
  - message_id (varchar(100))

approval_queue:
  - queue_id (int)
  - request_id (int)
  - approver_id (int)
  - step_number (int)
  - assigned_at (timestamp)
  - due_date (timestamp)
  - escalated (tinyint(1))
  - escalation_level (int)
  - priority (enum('low','medium','high','critical'))
  - status (enum('pending','completed','escalated','delegated'))
  - completed_at (timestamp)
  - escalated_at (timestamp)
  - escalated_to (int)
  - comments (text)
  - created_at (timestamp)
  - updated_at (timestamp)

approval_workflows:
  - workflow_id (int)
  - workflow_name (varchar(100))
  - category_id (int)
  - min_amount (decimal(15,2))
  - max_amount (decimal(15,2))
  - description (text)
  - is_active (tinyint(1))
  - created_at (timestamp)

approvals:
  - approval_id (int)
  - request_id (int)
  - level_id (int)
  - approver_id (int)
  - status (enum('pending','approved','rejected','skipped'))
  - comments (text)
  - approved_amount (decimal(15,2))
  - action_date (timestamp)
  - created_at (timestamp)

audit_logs:
  - log_id (int)
  - user_id (int)
  - action (varchar(100))
  - table_name (varchar(100))
  - record_id (int)
  - old_values (json)
  - new_values (json)
  - ip_address (varchar(45))
  - user_agent (text)
  - created_at (timestamp)

backup_logs:
  - backup_id (int)
  - backup_name (varchar(100))
  - backup_type (enum('FULL','INCREMENTAL','MANUAL'))
  - file_path (varchar(255))
  - file_size (bigint)
  - backup_status (enum('RUNNING','COMPLETED','FAILED'))
  - started_by (int)
  - started_at (timestamp)
  - completed_at (timestamp)
  - error_message (text)

budget_categories:
  - category_id (int)
  - category_name (varchar(100))
  - is_active (tinyint(1))
  - created_at (timestamp)
  - category_code (varchar(20))
  - description (text)
  - budget_limit (decimal(15,2))
  - parent_category_id (int)
  - updated_at (timestamp)

budgets:
  - budget_id (int)
  - budget_year (year)
  - department (varchar(100))
  - category_id (int)
  - allocated_amount (decimal(15,2))
  - spent_amount (decimal(15,2))
  - committed_amount (decimal(15,2))
  - available_amount (decimal(15,2))
  - budget_manager (int)
  - status (enum('draft','approved','active','locked'))
  - created_by (int)
  - created_at (timestamp)
  - updated_at (timestamp)

contract_milestones:
  - milestone_id (int)
  - contract_id (int)
  - milestone_name (varchar(255))
  - description (text)
  - due_date (date)
  - payment_amount (decimal(15,2))
  - status (enum('pending','in_progress','completed','overdue'))
  - completion_date (date)
  - notes (text)

contracts:
  - contract_id (int)
  - contract_number (varchar(50))
  - contract_title (varchar(255))
  - vendor_id (int)
  - contract_type (enum('purchase','service','maintenance','lease','other'))
  - start_date (date)
  - end_date (date)
  - contract_value (decimal(15,2))
  - currency (varchar(10))
  - payment_schedule (enum('monthly','quarterly','milestone','one_time'))
  - auto_renewal (tinyint(1))
  - renewal_notice_days (int)
  - status (enum('draft','active','suspended','expired','terminated'))
  - contract_manager (int)
  - legal_reviewer (int)
  - file_path (varchar(500))
  - created_by (int)
  - created_at (timestamp)
  - updated_at (timestamp)

deliveries:
  - delivery_id (int)
  - po_id (int)
  - delivery_number (varchar(50))
  - vendor_delivery_note (varchar(100))
  - delivery_date (date)
  - received_by (int)
  - delivery_status (enum('partial','complete','damaged','rejected'))
  - inspection_notes (text)
  - received_at (timestamp)

delivery_items:
  - delivery_item_id (int)
  - delivery_id (int)
  - po_item_id (int)
  - delivered_quantity (decimal(10,2))
  - accepted_quantity (decimal(10,2))
  - rejected_quantity (decimal(10,2))
  - condition_notes (text)

departments:
  - department_id (int)
  - department_name (varchar(100))
  - is_active (tinyint(1))
  - created_at (timestamp)
  - department_code (varchar(20))
  - budget_limit (decimal(15,2))
  - head_user_id (int)
  - description (text)
  - updated_at (timestamp)

file_uploads:
  - file_id (int)
  - original_name (varchar(255))
  - stored_name (varchar(255))
  - file_path (varchar(500))
  - file_size (int)
  - mime_type (varchar(100))
  - file_hash (varchar(64))
  - related_type (varchar(50))
  - related_id (int)
  - uploaded_by (int)
  - uploaded_at (timestamp)

in_app_notifications:
  - id (int)
  - user_id (int)
  - notification_id (varchar(50))
  - type (varchar(50))
  - title (varchar(200))
  - message (text)
  - data (json)
  - priority (enum('low','normal','high'))
  - is_read (tinyint(1))
  - created_at (timestamp)
  - read_at (timestamp)

invoices:
  - invoice_id (int)
  - invoice_number (varchar(50))
  - vendor_invoice_number (varchar(100))
  - po_id (int)
  - vendor_id (int)
  - invoice_date (date)
  - due_date (date)
  - subtotal (decimal(15,2))
  - tax_amount (decimal(15,2))
  - discount_amount (decimal(15,2))
  - total_amount (decimal(15,2))
  - currency (varchar(10))
  - status (enum('received','under_review','approved','paid','disputed','rejected'))
  - payment_terms (varchar(255))
  - received_by (int)
  - approved_by (int)
  - file_path (varchar(500))
  - notes (text)
  - created_at (timestamp)
  - updated_at (timestamp)

notifications:
  - notification_id (int)
  - user_id (int)
  - title (varchar(255))
  - message (text)
  - type (enum('info','warning','success','error'))
  - related_type (enum('request','po','contract','payment','system'))
  - related_id (int)
  - is_read (tinyint(1))
  - read_at (timestamp)
  - expires_at (timestamp)
  - created_at (timestamp)

payments:
  - payment_id (int)
  - payment_number (varchar(50))
  - invoice_id (int)
  - vendor_id (int)
  - payment_method (enum('bank_transfer','check','cash','credit_card'))
  - payment_date (date)
  - amount (decimal(15,2))
  - currency (varchar(10))
  - reference_number (varchar(100))
  - bank_details (text)
  - status (enum('pending','processing','completed','failed','cancelled'))
  - processed_by (int)
  - approved_by (int)
  - notes (text)
  - created_at (timestamp)

po_approvals:
  - approval_id (int)
  - po_id (int)
  - approver_id (int)
  - approval_level (int)
  - status (enum('pending','approved','rejected'))
  - comments (text)
  - approved_date (timestamp)
  - created_at (timestamp)

po_communications:
  - communication_id (int)
  - po_id (int)
  - communication_type (enum('email','phone','meeting','document'))
  - direction (enum('inbound','outbound'))
  - subject (varchar(500))
  - message (text)
  - attachment_path (varchar(500))
  - sent_by (int)
  - received_from (varchar(255))
  - communication_date (timestamp)
  - status (enum('sent','delivered','read','replied'))

po_items:
  - po_item_id (int)
  - po_id (int)
  - request_id (int)
  - item_description (text)
  - quantity (int)
  - unit_price (decimal(10,2))
  - total_price (decimal(12,2))
  - status (enum('pending','delivered','cancelled'))
  - delivery_date (timestamp)
  - notes (text)
  - created_at (timestamp)

po_tracking:
  - tracking_id (int)
  - po_id (int)
  - tracking_number (varchar(100))
  - carrier (varchar(100))
  - status (enum('shipped','in_transit','delivered','returned'))
  - location (varchar(255))
  - estimated_delivery (date)
  - actual_delivery (timestamp)
  - notes (text)
  - updated_by (int)
  - created_at (timestamp)
  - updated_at (timestamp)

procurement_categories:
  - category_id (int)
  - category_name (varchar(100))
  - category_code (varchar(20))
  - description (text)
  - budget_limit (decimal(15,2))
  - approval_required (tinyint(1))
  - status (enum('active','inactive'))
  - created_at (timestamp)

procurement_request_items:
  - item_id (int)
  - request_id (int)
  - item_name (varchar(255))
  - description (text)
  - specification (text)
  - quantity (decimal(10,2))
  - unit (varchar(50))
  - unit_price (decimal(10,2))
  - total_price (decimal(12,2))
  - preferred_vendor (varchar(255))
  - notes (text)

procurement_requests:
  - request_id (int)
  - request_number (varchar(50))
  - title (varchar(255))
  - description (text)
  - category_id (int)
  - department (varchar(100))
  - requested_by (int)
  - urgency (enum('low','medium','high','urgent'))
  - required_date (date)
  - budget_estimate (decimal(15,2))
  - justification (text)
  - status (enum('draft','submitted','under_review','approved','rejected','cancelled','completed'))
  - current_approval_level (int)
  - final_approved_amount (decimal(15,2))
  - approved_by (int)
  - approved_at (timestamp)
  - rejected_reason (text)
  - created_at (timestamp)
  - updated_at (timestamp)
  - urgency_level (enum('low','medium','high','critical'))
  - currency (varchar(3))
  - delivery_location (varchar(200))
  - expected_delivery_date (date)
  - special_instructions (text)
  - submitted_at (timestamp)
  - rejected_at (timestamp)
  - po_created (tinyint(1))
  - po_number (varchar(50))
  - po_date (timestamp)

purchase_order_items:
  - po_item_id (int)
  - po_id (int)
  - item_name (varchar(255))
  - description (text)
  - quantity (decimal(10,2))
  - unit (varchar(50))
  - unit_price (decimal(10,2))
  - total_price (decimal(12,2))
  - delivered_quantity (decimal(10,2))
  - remaining_quantity (decimal(10,2))
  - delivery_status (enum('pending','partial','complete'))

purchase_orders:
  - po_id (int)
  - po_number (varchar(50))
  - request_id (int)
  - quotation_id (int)
  - vendor_id (int)
  - po_date (date)
  - expected_delivery_date (date)
  - total_amount (decimal(15,2))
  - tax_amount (decimal(15,2))
  - discount_amount (decimal(15,2))
  - final_amount (decimal(15,2))
  - currency (varchar(10))
  - payment_terms (varchar(255))
  - delivery_address (text)
  - special_instructions (text)
  - status (enum('draft','sent','acknowledged','partially_delivered','delivered','completed','cancelled'))
  - created_by (int)
  - approved_by (int)
  - created_at (timestamp)
  - updated_at (timestamp)

quotation_items:
  - quotation_item_id (int)
  - quotation_id (int)
  - item_name (varchar(255))
  - description (text)
  - quantity (decimal(10,2))
  - unit (varchar(50))
  - unit_price (decimal(10,2))
  - total_price (decimal(12,2))
  - delivery_period (varchar(100))
  - brand (varchar(100))
  - model (varchar(100))
  - warranty (varchar(100))

quotations:
  - quotation_id (int)
  - quotation_number (varchar(50))
  - request_id (int)
  - vendor_id (int)
  - quotation_date (date)
  - valid_until (date)
  - total_amount (decimal(15,2))
  - currency (varchar(10))
  - payment_terms (varchar(255))
  - delivery_terms (varchar(255))
  - warranty_terms (varchar(255))
  - notes (text)
  - status (enum('draft','submitted','under_review','accepted','rejected','expired'))
  - submitted_by (int)
  - reviewed_by (int)
  - created_at (timestamp)
  - updated_at (timestamp)

request_attachments:
  - attachment_id (int)
  - request_id (int)
  - file_name (varchar(255))
  - file_path (varchar(500))
  - file_size (int)
  - file_type (varchar(50))
  - uploaded_by (int)
  - uploaded_at (timestamp)

request_items:
  - item_id (int)
  - request_id (int)
  - item_description (text)
  - quantity (int)
  - unit_of_measure (varchar(20))
  - unit_cost (decimal(10,2))
  - total_cost (decimal(15,2))
  - specifications (text)
  - brand_preference (varchar(100))
  - model_number (varchar(100))
  - created_at (timestamp)

service_categories:
  - id (int)
  - category_name (varchar(100))
  - description (text)
  - is_active (tinyint(1))
  - created_at (timestamp)

system_config:
  - config_id (int)
  - config_key (varchar(100))
  - config_value (text)
  - config_type (enum('string','number','boolean','json'))
  - description (text)
  - is_editable (tinyint(1))
  - updated_by (int)
  - updated_at (timestamp)

test_vendors:
  - vendor_id (int)
  - vendor_name (varchar(255))
  - category_id (int)
  - created_by (int)

user_notification_preferences:
  - user_id (int)
  - email_enabled (tinyint(1))
  - sms_enabled (tinyint(1))
  - push_enabled (tinyint(1))
  - digest_frequency (enum('never','daily','weekly'))
  - digest_time (time)
  - urgent_only (tinyint(1))
  - reminder_hours (int)
  - escalation_notifications (tinyint(1))
  - approval_summary (tinyint(1))
  - mobile_number (varchar(20))
  - timezone (varchar(50))
  - created_at (timestamp)
  - updated_at (timestamp)

user_roles:
  - role_id (int)
  - role_name (varchar(50))
  - role_description (text)
  - permissions (json)
  - created_at (timestamp)
  - updated_at (timestamp)

users:
  - id (int)
  - username (varchar(50))
  - email (varchar(100))
  - password_hash (varchar(255))
  - first_name (varchar(50))
  - last_name (varchar(50))
  - phone (varchar(20))
  - department (varchar(50))
  - role_id (int)
  - is_active (tinyint(1))
  - last_login (timestamp)
  - created_at (timestamp)
  - updated_at (timestamp)
  - password (varchar(255))
  - role (enum('admin','md','procurement','finance','hod'))
  - name (varchar(255))
  - status (enum('active','inactive','suspended'))
  - phone_number (varchar(20))
  - full_name (varchar(100))
  - department_id (int)

vendor_accounts:
  - id (int)
  - vendor_id (int)
  - username (varchar(100))
  - password (varchar(255))
  - is_active (tinyint(1))
  - last_login (timestamp)
  - password_reset_token (varchar(255))
  - password_reset_expires (timestamp)
  - created_at (timestamp)
  - updated_at (timestamp)

vendor_automation_rules:
  - rule_id (int)
  - rule_name (varchar(100))
  - keywords (json)
  - category_id (int)
  - confidence_threshold (decimal(3,2))
  - is_active (tinyint(1))
  - created_at (timestamp)
  - updated_at (timestamp)

vendor_categories:
  - category_id (int)
  - category_name (varchar(100))
  - description (text)
  - status (enum('active','inactive'))
  - created_at (timestamp)
  - updated_at (timestamp)

vendor_documents:
  - document_id (int)
  - vendor_id (int)
  - document_type (enum('registration','tax_cert','insurance','license','other'))
  - document_name (varchar(255))
  - file_path (varchar(500))
  - file_size (int)
  - expiry_date (date)
  - status (enum('pending','approved','rejected','expired'))
  - uploaded_by (int)
  - uploaded_at (timestamp)

vendor_evaluations:
  - id (int)
  - vendor_id (int)
  - evaluator_id (int)
  - procurement_id (int)
  - quality_rating (decimal(2,1))
  - delivery_rating (decimal(2,1))
  - communication_rating (decimal(2,1))
  - price_rating (decimal(2,1))
  - overall_rating (decimal(2,1))
  - comments (text)
  - evaluation_date (timestamp)

vendor_notification_preferences:
  - vendor_id (int)
  - email_notifications (tinyint(1))
  - category_assignment_alerts (tinyint(1))
  - rfq_notifications (tinyint(1))
  - created_at (timestamp)
  - updated_at (timestamp)

vendor_ratings:
  - rating_id (int)
  - vendor_id (int)
  - contract_id (int)
  - quality_rating (decimal(3,2))
  - delivery_rating (decimal(3,2))
  - price_rating (decimal(3,2))
  - service_rating (decimal(3,2))
  - overall_rating (decimal(3,2))
  - comments (text)
  - rated_by (int)
  - rating_date (timestamp)

vendors:
  - id (int)
  - vendor_name (varchar(255))
  - business_type (varchar(100))
  - registration_number (varchar(100))
  - tax_number (varchar(100))
  - description (text)
  - contact_person (varchar(255))
  - email (varchar(255))
  - phone (varchar(50))
  - alternate_phone (varchar(50))
  - website (varchar(255))
  - address (text)
  - city (varchar(100))
  - state (varchar(100))
  - postal_code (varchar(20))
  - country (varchar(100))
  - bank_name (varchar(255))
  - account_number (varchar(100))
  - account_name (varchar(255))
  - sort_code (varchar(50))
  - budget_category (varchar(100))
  - budget_range (varchar(100))
  - specializations (text)
  - status (enum('pending','approved','rejected','suspended'))
  - approved_by (int)
  - approved_at (timestamp)
  - created_at (timestamp)
  - updated_at (timestamp)

vendors_main:
  - vendor_id (int)
  - vendor_code (varchar(20))
  - company_name (varchar(255))
  - contact_person (varchar(255))
  - email (varchar(255))
  - phone (varchar(20))
  - mobile (varchar(20))
  - website (varchar(255))
  - address (text)
  - city (varchar(100))
  - state (varchar(100))
  - country (varchar(100))
  - postal_code (varchar(20))
  - category_id (int)
  - tax_number (varchar(50))
  - registration_number (varchar(50))
  - bank_name (varchar(255))
  - bank_account (varchar(50))
  - payment_terms (varchar(100))
  - credit_limit (decimal(15,2))
  - rating (enum('excellent','good','average','poor'))
  - status (enum('active','inactive','suspended','blacklisted'))
  - notes (text)
  - created_by (int)
  - created_at (timestamp)
  - updated_at (timestamp)
  - vendor_name (varchar(100))
  - vendor_category (varchar(50))
  - is_preferred (tinyint(1))

workflow_instances:
  - instance_id (int)
  - request_id (int)
  - rule_id (int)
  - steps_definition (json)
  - current_step (int)
  - parallel_approval (tinyint(1))
  - requires_unanimous (tinyint(1))
  - status (enum('active','completed','cancelled'))
  - created_at (timestamp)
  - completed_at (timestamp)

workflow_rules:
  - rule_id (int)
  - rule_name (varchar(100))
  - description (text)
  - min_amount (decimal(15,2))
  - max_amount (decimal(15,2))
  - department_id (int)
  - category_id (int)
  - urgency_level (enum('low','medium','high','critical'))
  - approval_steps (json)
  - escalation_hours (int)
  - escalation_chain (json)
  - parallel_approval (tinyint(1))
  - requires_unanimous (tinyint(1))
  - auto_approve_threshold (decimal(15,2))
  - working_hours_only (tinyint(1))
  - is_active (tinyint(1))
  - effective_from (date)
  - effective_to (date)
  - created_by (int)
  - created_at (timestamp)
  - updated_at (timestamp)