Table: accounting_tasks

Structure:

id | int | NO | PRI |  | auto_increment
assignment_id | int | NO | MUL |  | 
task_type | enum('bookkeeping','tax_filing','financial_report','audit','other') | NO |  |  | 
description | text | YES |  |  | 
due_date | date | YES |  |  | 
completed | tinyint(1) | YES |  | 0 | 
completed_at | datetime | YES |  |  | 
created_at | timestamp | NO |  | CURRENT_TIMESTAMP | DEFAULT_GENERATED

CREATE TABLE SQL:

CREATE TABLE `accounting_tasks` (
  `id` int NOT NULL AUTO_INCREMENT,
  `assignment_id` int NOT NULL,
  `task_type` enum('bookkeeping','tax_filing','financial_report','audit','other') NOT NULL,
  `description` text,
  `due_date` date DEFAULT NULL,
  `completed` tinyint(1) DEFAULT '0',
  `completed_at` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `assignment_id` (`assignment_id`),
  CONSTRAINT `accounting_tasks_ibfk_1` FOREIGN KEY (`assignment_id`) REFERENCES `client_staff_assignments` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Table: client_services

Structure:

client_id | int | NO | PRI |  | 
service_id | int | NO | PRI |  | 

CREATE TABLE SQL:

CREATE TABLE `client_services` (
  `client_id` int NOT NULL,
  `service_id` int NOT NULL,
  PRIMARY KEY (`client_id`,`service_id`),
  KEY `service_id` (`service_id`),
  CONSTRAINT `client_services_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `client_services_ibfk_2` FOREIGN KEY (`service_id`) REFERENCES `services` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Table: client_staff_assignments

Structure:

id | int | NO | PRI |  | auto_increment
client_id | int | YES | MUL |  | 
staff_id | int | YES | MUL |  | 
service_id | int | YES | MUL |  | 
status | tinyint(1) | YES |  | 1 | 

CREATE TABLE SQL:

CREATE TABLE `client_staff_assignments` (
  `id` int NOT NULL AUTO_INCREMENT,
  `client_id` int DEFAULT NULL,
  `staff_id` int DEFAULT NULL,
  `service_id` int DEFAULT NULL,
  `status` tinyint(1) DEFAULT '1' COMMENT '1=active, 0=inactive',
  PRIMARY KEY (`id`),
  KEY `staff_id` (`staff_id`),
  KEY `service_id` (`service_id`),
  KEY `fk_client_staff_client` (`client_id`),
  CONSTRAINT `client_staff_assignments_ibfk_2` FOREIGN KEY (`staff_id`) REFERENCES `users` (`id`),
  CONSTRAINT `client_staff_assignments_ibfk_3` FOREIGN KEY (`service_id`) REFERENCES `services` (`id`),
  CONSTRAINT `fk_client_staff_client` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=36 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Table: clients

Structure:

id | int | NO | PRI |  | auto_increment
client_type | enum('company','individual') | NO |  |  | 
company_name | varchar(255) | YES |  |  | 
full_name | varchar(255) | YES |  |  | 
contact_email | varchar(255) | YES |  |  | 
phone_number | varchar(50) | YES |  |  | 
kra_pin | varchar(50) | YES |  |  | 
id_number | varchar(50) | YES |  |  | 
nssf_username | varchar(100) | YES |  |  | 
nssf_password | varchar(100) | YES |  |  | 
shif_username | varchar(100) | YES |  |  | 
shif_password | varchar(100) | YES |  |  | 
id_upload_path | varchar(255) | YES |  |  | 
cr12_upload_path | varchar(255) | YES |  |  | 
start_date | date | YES |  |  | 
engagement_letter_path | varchar(255) | YES |  |  | 
next_of_kin | varchar(255) | YES |  |  | 
next_of_kin_number | varchar(50) | YES |  |  | 
next_of_kin_relationship | varchar(100) | YES |  |  | 
services | text | YES |  |  | 
bof1_upload_path | varchar(255) | YES |  |  | 
pin_certificate_path | varchar(255) | YES |  |  | 
cert_incorporation_path | varchar(255) | YES |  |  | 
business_registration_path | varchar(255) | YES |  |  | 
director_list | text | YES |  |  | 
created_by | int | YES |  |  | 
created_at | timestamp | NO |  | CURRENT_TIMESTAMP | DEFAULT_GENERATED

CREATE TABLE SQL:

CREATE TABLE `clients` (
  `id` int NOT NULL AUTO_INCREMENT,
  `client_type` enum('company','individual') NOT NULL,
  `company_name` varchar(255) DEFAULT NULL,
  `full_name` varchar(255) DEFAULT NULL,
  `contact_email` varchar(255) DEFAULT NULL,
  `phone_number` varchar(50) DEFAULT NULL,
  `kra_pin` varchar(50) DEFAULT NULL,
  `id_number` varchar(50) DEFAULT NULL,
  `nssf_username` varchar(100) DEFAULT NULL,
  `nssf_password` varchar(100) DEFAULT NULL,
  `shif_username` varchar(100) DEFAULT NULL,
  `shif_password` varchar(100) DEFAULT NULL,
  `id_upload_path` varchar(255) DEFAULT NULL,
  `cr12_upload_path` varchar(255) DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `engagement_letter_path` varchar(255) DEFAULT NULL,
  `next_of_kin` varchar(255) DEFAULT NULL,
  `next_of_kin_number` varchar(50) DEFAULT NULL,
  `next_of_kin_relationship` varchar(100) DEFAULT NULL,
  `services` text,
  `bof1_upload_path` varchar(255) DEFAULT NULL,
  `pin_certificate_path` varchar(255) DEFAULT NULL,
  `cert_incorporation_path` varchar(255) DEFAULT NULL,
  `business_registration_path` varchar(255) DEFAULT NULL,
  `director_list` text,
  `created_by` int DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=92 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Table: departments

Structure:

id | int | NO | PRI |  | auto_increment
name | varchar(100) | NO |  |  | 
head_name | varchar(100) | YES |  |  | 
description | text | YES |  |  | 
head_id | int | YES | MUL |  | 
created_at | timestamp | NO |  | CURRENT_TIMESTAMP | DEFAULT_GENERATED
updated_at | timestamp | NO |  | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP
status | tinyint(1) | YES | MUL | 1 | 

CREATE TABLE SQL:

CREATE TABLE `departments` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `head_name` varchar(100) DEFAULT NULL,
  `description` text,
  `head_id` int DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `status` tinyint(1) DEFAULT '1' COMMENT '1=active, 0=inactive',
  PRIMARY KEY (`id`),
  KEY `head_id` (`head_id`),
  KEY `idx_department_status` (`status`),
  CONSTRAINT `departments_ibfk_1` FOREIGN KEY (`head_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Table: employees

Structure:

id | int | NO | PRI |  | auto_increment
client_id | int | YES | MUL |  | 
full_name | varchar(255) | YES |  |  | 
id_number | varchar(20) | YES |  |  | 
email | varchar(255) | YES |  |  | 
phone | varchar(20) | YES |  |  | 
next_of_kin | varchar(255) | YES |  |  | 
bank_account_last4 | char(4) | YES |  |  | 
bank_name | varchar(255) | YES |  |  | 
contract_start | date | YES |  |  | 
contract_end | date | YES |  |  | 
position | varchar(255) | YES |  |  | 
contract_type | varchar(50) | YES |  |  | 
created_at | timestamp | NO |  | CURRENT_TIMESTAMP | DEFAULT_GENERATED

CREATE TABLE SQL:

CREATE TABLE `employees` (
  `id` int NOT NULL AUTO_INCREMENT,
  `client_id` int DEFAULT NULL,
  `full_name` varchar(255) DEFAULT NULL,
  `id_number` varchar(20) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `next_of_kin` varchar(255) DEFAULT NULL,
  `bank_account_last4` char(4) DEFAULT NULL,
  `bank_name` varchar(255) DEFAULT NULL,
  `contract_start` date DEFAULT NULL,
  `contract_end` date DEFAULT NULL,
  `position` varchar(255) DEFAULT NULL,
  `contract_type` varchar(50) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `client_id` (`client_id`),
  CONSTRAINT `employees_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Table: hr_tasks

Structure:

id | int | NO | PRI |  | auto_increment
assignment_id | int | NO | MUL |  | 
task_type | enum('recruitment','onboarding','compliance','training','benefits','other') | NO |  |  | 
description | text | YES |  |  | 
due_date | date | YES |  |  | 
completed | tinyint(1) | YES |  | 0 | 
completed_at | datetime | YES |  |  | 
created_at | timestamp | NO |  | CURRENT_TIMESTAMP | DEFAULT_GENERATED

CREATE TABLE SQL:

CREATE TABLE `hr_tasks` (
  `id` int NOT NULL AUTO_INCREMENT,
  `assignment_id` int NOT NULL,
  `task_type` enum('recruitment','onboarding','compliance','training','benefits','other') NOT NULL,
  `description` text,
  `due_date` date DEFAULT NULL,
  `completed` tinyint(1) DEFAULT '0',
  `completed_at` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `assignment_id` (`assignment_id`),
  CONSTRAINT `hr_tasks_ibfk_1` FOREIGN KEY (`assignment_id`) REFERENCES `client_staff_assignments` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Table: payroll_employees

Structure:

id | int | NO | PRI |  | auto_increment
client_id | int | YES | MUL |  | 
full_name | varchar(255) | YES |  |  | 
id_number | varchar(50) | YES |  |  | 
email | varchar(255) | YES |  |  | 
phone | varchar(50) | YES |  |  | 
next_of_kin | varchar(255) | YES |  |  | 
bank_account | varchar(100) | YES |  |  | 
bank_name | varchar(100) | YES |  |  | 
position | varchar(100) | YES |  |  | 
contract_start | date | YES |  |  | 
contract_end | date | YES |  |  | 
contract_type | enum('Permanent','Contract','Intern') | YES |  |  | 
created_at | timestamp | NO |  | CURRENT_TIMESTAMP | DEFAULT_GENERATED

CREATE TABLE SQL:

CREATE TABLE `payroll_employees` (
  `id` int NOT NULL AUTO_INCREMENT,
  `client_id` int DEFAULT NULL,
  `full_name` varchar(255) DEFAULT NULL,
  `id_number` varchar(50) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `next_of_kin` varchar(255) DEFAULT NULL,
  `bank_account` varchar(100) DEFAULT NULL,
  `bank_name` varchar(100) DEFAULT NULL,
  `position` varchar(100) DEFAULT NULL,
  `contract_start` date DEFAULT NULL,
  `contract_end` date DEFAULT NULL,
  `contract_type` enum('Permanent','Contract','Intern') DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `client_id` (`client_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Table: payroll_tasks

Structure:

id | int | NO | PRI |  | auto_increment
assignment_id | int | NO | MUL |  | 
period | enum('weekly','biweekly','monthly','quarterly','annually') | NO |  |  | 
status | enum('pending','processing','completed','failed') | YES |  | pending | 
processed_at | datetime | YES |  |  | 
created_at | timestamp | NO |  | CURRENT_TIMESTAMP | DEFAULT_GENERATED

CREATE TABLE SQL:

CREATE TABLE `payroll_tasks` (
  `id` int NOT NULL AUTO_INCREMENT,
  `assignment_id` int NOT NULL,
  `period` enum('weekly','biweekly','monthly','quarterly','annually') NOT NULL,
  `status` enum('pending','processing','completed','failed') DEFAULT 'pending',
  `processed_at` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `assignment_id` (`assignment_id`),
  CONSTRAINT `payroll_tasks_ibfk_1` FOREIGN KEY (`assignment_id`) REFERENCES `client_staff_assignments` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Table: payslips

Structure:

id | int | NO | PRI |  | auto_increment
employee_id | int | YES | MUL |  | 
month | year | NO |  |  | 
file_path | varchar(255) | YES |  |  | 
uploaded_at | timestamp | NO |  | CURRENT_TIMESTAMP | DEFAULT_GENERATED

CREATE TABLE SQL:

CREATE TABLE `payslips` (
  `id` int NOT NULL AUTO_INCREMENT,
  `employee_id` int DEFAULT NULL,
  `month` year NOT NULL,
  `file_path` varchar(255) DEFAULT NULL,
  `uploaded_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `employee_id` (`employee_id`),
  CONSTRAINT `payslips_ibfk_1` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Table: salary_history

Structure:

id | int | NO | PRI |  | auto_increment
employee_id | int | YES | MUL |  | 
month | year | NO |  |  | 
salary | decimal(10,2) | YES |  |  | 

CREATE TABLE SQL:

CREATE TABLE `salary_history` (
  `id` int NOT NULL AUTO_INCREMENT,
  `employee_id` int DEFAULT NULL,
  `month` year NOT NULL,
  `salary` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `employee_id` (`employee_id`),
  CONSTRAINT `salary_history_ibfk_1` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Table: service_documents

Structure:

id | int | NO | PRI |  | auto_increment
assignment_id | int | NO | MUL |  | 
document_name | varchar(255) | NO |  |  | 
file_path | varchar(255) | NO |  |  | 
uploaded_at | timestamp | NO |  | CURRENT_TIMESTAMP | DEFAULT_GENERATED
uploaded_by | int | NO | MUL |  | 

CREATE TABLE SQL:

CREATE TABLE `service_documents` (
  `id` int NOT NULL AUTO_INCREMENT,
  `assignment_id` int NOT NULL,
  `document_name` varchar(255) NOT NULL,
  `file_path` varchar(255) NOT NULL,
  `uploaded_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `uploaded_by` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `assignment_id` (`assignment_id`),
  KEY `uploaded_by` (`uploaded_by`),
  CONSTRAINT `service_documents_ibfk_1` FOREIGN KEY (`assignment_id`) REFERENCES `client_staff_assignments` (`id`),
  CONSTRAINT `service_documents_ibfk_2` FOREIGN KEY (`uploaded_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Table: service_status

Structure:

id | int | NO | PRI |  | auto_increment
assignment_id | int | NO | MUL |  | 
status | enum('pending','in_progress','completed','on_hold') | YES |  | pending | 
notes | text | YES |  |  | 
last_updated | timestamp | NO |  | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP

CREATE TABLE SQL:

CREATE TABLE `service_status` (
  `id` int NOT NULL AUTO_INCREMENT,
  `assignment_id` int NOT NULL,
  `status` enum('pending','in_progress','completed','on_hold') DEFAULT 'pending',
  `notes` text,
  `last_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `assignment_id` (`assignment_id`),
  CONSTRAINT `service_status_ibfk_1` FOREIGN KEY (`assignment_id`) REFERENCES `client_staff_assignments` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Table: services

Structure:

id | int | NO | PRI |  | auto_increment
name | varchar(255) | YES |  |  | 
description | text | YES |  |  | 
department_id | int | YES | MUL |  | 
is_active | tinyint(1) | YES |  | 1 | 
created_at | timestamp | NO |  | CURRENT_TIMESTAMP | DEFAULT_GENERATED
updated_at | timestamp | NO |  | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP
category | enum('accounting','payroll','hr','admin') | NO |  |  | 

CREATE TABLE SQL:

CREATE TABLE `services` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `description` text,
  `department_id` int DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `category` enum('accounting','payroll','hr','admin') NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_service_department` (`department_id`),
  CONSTRAINT `fk_service_department` FOREIGN KEY (`department_id`) REFERENCES `departments` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Table: users

Structure:

id | int | NO | PRI |  | auto_increment
name | varchar(255) | YES |  |  | 
email | varchar(255) | YES | UNI |  | 
password | varchar(255) | YES |  |  | 
role | enum('client','staff','admin') | NO |  |  | 
is_client_portal_user | tinyint(1) | YES |  | 0 | 
service_access | longtext | YES |  |  | 
created_at | timestamp | NO |  | CURRENT_TIMESTAMP | DEFAULT_GENERATED
otp_code | varchar(10) | YES |  |  | 
otp_expires_at | datetime | YES |  |  | 
phone | varchar(20) | YES |  |  | 
department_id | int | YES | MUL |  | 
client_id | int | YES | MUL |  | 
updated_at | datetime | YES |  | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP

CREATE TABLE SQL:

CREATE TABLE `users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `role` enum('client','staff','admin') NOT NULL,
  `is_client_portal_user` tinyint(1) DEFAULT '0',
  `service_access` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `otp_code` varchar(10) DEFAULT NULL,
  `otp_expires_at` datetime DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `department_id` int DEFAULT NULL,
  `client_id` int DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`),
  KEY `fk_user_department` (`department_id`),
  KEY `fk_client` (`client_id`),
  CONSTRAINT `fk_department` FOREIGN KEY (`department_id`) REFERENCES `departments` (`id`),
  CONSTRAINT `fk_user_department` FOREIGN KEY (`department_id`) REFERENCES `departments` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci