Untitled
unknown
mysql
2 months ago
53 kB
5
Indexable
-- V1__Comprehensive_Schema.sql -- Comprehensive ERP Schema for Altec Mechanical Group -- Includes indexes for performance optimization and liberal application-time periods SET NAMES utf8mb4; -- Shift Type Table CREATE TABLE shift_type ( shift_type_id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) NOT NULL UNIQUE, start_time TIME NOT NULL, end_time TIME NOT NULL, effective_from DATE NOT NULL DEFAULT '1970-01-01', effective_to DATE NOT NULL DEFAULT '9999-12-31', PERIOD FOR valid_period (effective_from, effective_to), valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START, valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (valid_from, valid_to) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 WITH SYSTEM VERSIONING; -- Initial shift types INSERT INTO shift_type (name, start_time, end_time) VALUES ('Morning', '07:00:00', '15:00:00'), ('Regular', '09:00:00', '17:00:00'), ('Late', '11:00:00', '19:00:00'); -- Role Table CREATE TABLE role ( role_id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) NOT NULL UNIQUE, description TEXT DEFAULT NULL, effective_from DATE NOT NULL DEFAULT '1970-01-01', effective_to DATE NOT NULL DEFAULT '9999-12-31', PERIOD FOR valid_period (effective_from, effective_to), valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START, valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (valid_from, valid_to) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 WITH SYSTEM VERSIONING; -- Add an index on name for faster lookups CREATE INDEX idx_role_name ON role (name); -- Permission Table CREATE TABLE permission ( permission_id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) NOT NULL UNIQUE, description TEXT DEFAULT NULL, effective_from DATE NOT NULL DEFAULT '1970-01-01', effective_to DATE NOT NULL DEFAULT '9999-12-31', PERIOD FOR valid_period (effective_from, effective_to), valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START, valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (valid_from, valid_to) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 WITH SYSTEM VERSIONING; -- Add an index on name for faster lookups CREATE INDEX idx_permission_name ON permission (name); -- Role Hierarchy Table CREATE TABLE role_hierarchy ( parent_role_id INT NOT NULL, child_role_id INT NOT NULL, effective_from DATE NOT NULL DEFAULT '1970-01-01', effective_to DATE NOT NULL DEFAULT '9999-12-31', PERIOD FOR valid_period (effective_from, effective_to), PRIMARY KEY (parent_role_id, child_role_id, effective_from), FOREIGN KEY (parent_role_id) REFERENCES role(role_id), FOREIGN KEY (child_role_id) REFERENCES role(role_id), valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START, valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (valid_from, valid_to) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 WITH SYSTEM VERSIONING; -- Account Type Table CREATE TABLE account_type ( account_type_id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) NOT NULL UNIQUE, description VARCHAR(255) DEFAULT NULL, effective_from DATE NOT NULL DEFAULT '1970-01-01', effective_to DATE NOT NULL DEFAULT '9999-12-31', PERIOD FOR valid_period (effective_from, effective_to), valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START, valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (valid_from, valid_to) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 WITH SYSTEM VERSIONING; -- Add an index on name for faster lookups CREATE INDEX idx_account_type_name ON account_type (name); -- Transaction Type Lookup Table CREATE TABLE transaction_type ( transaction_type_id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) NOT NULL UNIQUE, description VARCHAR(255) DEFAULT NULL, effective_from DATE NOT NULL DEFAULT '1970-01-01', effective_to DATE NOT NULL DEFAULT '9999-12-31', PERIOD FOR valid_period (effective_from, effective_to), valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START, valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (valid_from, valid_to) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 WITH SYSTEM VERSIONING; -- Add an index on name for faster lookups CREATE INDEX idx_transaction_type_name ON transaction_type (name); -- Event Type Lookup Table CREATE TABLE event_type ( event_type_id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) NOT NULL UNIQUE, description VARCHAR(255) DEFAULT NULL, effective_from DATE NOT NULL DEFAULT '1970-01-01', effective_to DATE NOT NULL DEFAULT '9999-12-31', PERIOD FOR valid_period (effective_from, effective_to), valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START, valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (valid_from, valid_to) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 WITH SYSTEM VERSIONING; -- Add an index on name for faster lookups CREATE INDEX idx_event_type_name ON event_type (name); -- Artifact Type Lookup Table CREATE TABLE artifact_type ( artifact_type_id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) NOT NULL UNIQUE, description VARCHAR(255) DEFAULT NULL, effective_from DATE NOT NULL DEFAULT '1970-01-01', effective_to DATE NOT NULL DEFAULT '9999-12-31', PERIOD FOR valid_period (effective_from, effective_to), valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START, valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (valid_from, valid_to) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 WITH SYSTEM VERSIONING; -- Add an index on name for faster lookups CREATE INDEX idx_artifact_type_name ON artifact_type (name); -- Entity Table for Multi-Entity Support CREATE TABLE entity ( entity_id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL UNIQUE, description VARCHAR(255) DEFAULT NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, effective_from DATE NOT NULL DEFAULT '1970-01-01', effective_to DATE NOT NULL DEFAULT '9999-12-31', PERIOD FOR valid_period (effective_from, effective_to), valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START, valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (valid_from, valid_to) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 WITH SYSTEM VERSIONING; -- Add index on is_active for quick filtering CREATE INDEX idx_entity_is_active ON entity (is_active); -- Main Tables -- User Account Table CREATE TABLE user_account ( user_id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) NOT NULL UNIQUE, password_hash VARCHAR(255) NOT NULL, full_name VARCHAR(100) NOT NULL, email VARCHAR(100) DEFAULT NULL, phone VARCHAR(20) DEFAULT NULL, avatar_url VARCHAR(255) DEFAULT NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, is_anonymized TINYINT(1) NOT NULL DEFAULT 0, is_deleted TINYINT(1) NOT NULL DEFAULT 0, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, effective_from DATE NOT NULL DEFAULT '1970-01-01', effective_to DATE NOT NULL DEFAULT '9999-12-31', PERIOD FOR valid_period (effective_from, effective_to), valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START, valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (valid_from, valid_to) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 WITH SYSTEM VERSIONING; -- Indexes for User Account CREATE INDEX idx_user_account_username ON user_account (username); CREATE INDEX idx_user_account_email ON user_account (email); CREATE INDEX idx_user_account_is_active ON user_account (is_active); -- User Permission Table CREATE TABLE user_permission ( user_id INT NOT NULL, permission_id INT NOT NULL, effective_from DATE NOT NULL DEFAULT '1970-01-01', effective_to DATE NOT NULL DEFAULT '9999-12-31', PERIOD FOR valid_period (effective_from, effective_to), PRIMARY KEY (user_id, permission_id, effective_from), FOREIGN KEY (user_id) REFERENCES user_account(user_id), FOREIGN KEY (permission_id) REFERENCES permission(permission_id), valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START, valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (valid_from, valid_to) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 WITH SYSTEM VERSIONING; -- Worker Table CREATE TABLE worker ( worker_id INT AUTO_INCREMENT PRIMARY KEY, user_id INT NOT NULL UNIQUE, employee_code VARCHAR(50) UNIQUE, hire_date DATE DEFAULT NULL, termination_date DATE DEFAULT NULL, is_deleted TINYINT(1) NOT NULL DEFAULT 0, effective_from DATE NOT NULL DEFAULT '1970-01-01', effective_to DATE NOT NULL DEFAULT '9999-12-31', PERIOD FOR valid_period (effective_from, effective_to), valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START, valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (valid_from, valid_to), FOREIGN KEY (user_id) REFERENCES user_account(user_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 WITH SYSTEM VERSIONING; -- Indexes for Worker CREATE INDEX idx_worker_user_id ON worker (user_id); CREATE INDEX idx_worker_employee_code ON worker (employee_code); -- User Role Table CREATE TABLE user_role ( user_id INT NOT NULL, role_id INT NOT NULL, effective_from DATE NOT NULL DEFAULT '1970-01-01', effective_to DATE NOT NULL DEFAULT '9999-12-31', PERIOD FOR valid_period (effective_from, effective_to), PRIMARY KEY (user_id, role_id, effective_from), FOREIGN KEY (user_id) REFERENCES user_account(user_id), FOREIGN KEY (role_id) REFERENCES role(role_id), valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START, valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (valid_from, valid_to) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 WITH SYSTEM VERSIONING; -- Role Permission Table CREATE TABLE role_permission ( role_id INT NOT NULL, permission_id INT NOT NULL, effective_from DATE NOT NULL DEFAULT '1970-01-01', effective_to DATE NOT NULL DEFAULT '9999-12-31', PERIOD FOR valid_period (effective_from, effective_to), PRIMARY KEY (role_id, permission_id, effective_from), FOREIGN KEY (role_id) REFERENCES role(role_id), FOREIGN KEY (permission_id) REFERENCES permission(permission_id), valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START, valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (valid_from, valid_to) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 WITH SYSTEM VERSIONING; -- Crew Table CREATE TABLE crew ( crew_id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, is_deleted TINYINT(1) NOT NULL DEFAULT 0, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, effective_from DATE NOT NULL DEFAULT '1970-01-01', effective_to DATE NOT NULL DEFAULT '9999-12-31', PERIOD FOR valid_period (effective_from, effective_to), valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START, valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (valid_from, valid_to) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 WITH SYSTEM VERSIONING; -- Indexes for Crew CREATE INDEX idx_crew_name ON crew (name); CREATE INDEX idx_crew_is_active ON crew (is_active); -- Crew Shift Table CREATE TABLE crew_shift ( crew_shift_id INT AUTO_INCREMENT PRIMARY KEY, crew_id INT NOT NULL, shift_type_id INT NOT NULL, start_date DATE NOT NULL, end_date DATE DEFAULT NULL, effective_from DATE NOT NULL DEFAULT '1970-01-01', effective_to DATE NOT NULL DEFAULT '9999-12-31', PERIOD FOR valid_period (effective_from, effective_to), valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START, valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (valid_from, valid_to), FOREIGN KEY (crew_id) REFERENCES crew(crew_id), FOREIGN KEY (shift_type_id) REFERENCES shift_type(shift_type_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 WITH SYSTEM VERSIONING; -- Indexes for Crew Shift CREATE INDEX idx_crew_shift_crew_id ON crew_shift (crew_id); CREATE INDEX idx_crew_shift_shift_type_id ON crew_shift (shift_type_id); CREATE INDEX idx_crew_shift_start_date ON crew_shift (start_date); -- Crew Member Table CREATE TABLE crew_member ( crew_member_id INT AUTO_INCREMENT PRIMARY KEY, crew_id INT NOT NULL, worker_id INT NOT NULL, role_id INT NOT NULL, start_date DATE NOT NULL, end_date DATE DEFAULT NULL, effective_from DATE NOT NULL DEFAULT '1970-01-01', effective_to DATE NOT NULL DEFAULT '9999-12-31', PERIOD FOR valid_period (effective_from, effective_to), valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START, valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (valid_from, valid_to), FOREIGN KEY (crew_id) REFERENCES crew(crew_id), FOREIGN KEY (worker_id) REFERENCES worker(worker_id), FOREIGN KEY (role_id) REFERENCES role(role_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 WITH SYSTEM VERSIONING; -- Indexes for Crew Member CREATE INDEX idx_crew_member_crew_id ON crew_member (crew_id); CREATE INDEX idx_crew_member_worker_id ON crew_member (worker_id); CREATE INDEX idx_crew_member_role_id ON crew_member (role_id); -- Shift Change Request Table CREATE TABLE shift_change_request ( request_id INT AUTO_INCREMENT PRIMARY KEY, worker_id INT NOT NULL, from_shift_type_id INT NOT NULL, to_shift_type_id INT NOT NULL, requested_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, processed_by INT DEFAULT NULL, processed_at TIMESTAMP DEFAULT NULL, notes TEXT DEFAULT NULL, current_state VARCHAR(50) NOT NULL DEFAULT 'INITIATED', effective_from DATE NOT NULL DEFAULT '1970-01-01', effective_to DATE NOT NULL DEFAULT '9999-12-31', PERIOD FOR valid_period (effective_from, effective_to), FOREIGN KEY (worker_id) REFERENCES worker(worker_id), FOREIGN KEY (from_shift_type_id) REFERENCES shift_type(shift_type_id), FOREIGN KEY (to_shift_type_id) REFERENCES shift_type(shift_type_id), FOREIGN KEY (processed_by) REFERENCES user_account(user_id), valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START, valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (valid_from, valid_to) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 WITH SYSTEM VERSIONING; -- Indexes for Shift Change Request CREATE INDEX idx_shift_change_request_worker_id ON shift_change_request (worker_id); CREATE INDEX idx_shift_change_request_current_state ON shift_change_request (current_state); -- Job Site Table CREATE TABLE job_site ( job_site_id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(200) NOT NULL, address VARCHAR(255) DEFAULT NULL, latitude DECIMAL(10,7) DEFAULT NULL, longitude DECIMAL(10,7) DEFAULT NULL, created_by INT NOT NULL, is_deleted TINYINT(1) NOT NULL DEFAULT 0, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, effective_from DATE NOT NULL DEFAULT '1970-01-01', effective_to DATE NOT NULL DEFAULT '9999-12-31', PERIOD FOR valid_period (effective_from, effective_to), valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START, valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (valid_from, valid_to), FOREIGN KEY (created_by) REFERENCES user_account(user_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 WITH SYSTEM VERSIONING; -- Indexes for Job Site CREATE INDEX idx_job_site_name ON job_site (name); CREATE INDEX idx_job_site_created_by ON job_site (created_by); -- Timeline Event Table CREATE TABLE timeline_event ( event_id INT AUTO_INCREMENT PRIMARY KEY, job_site_id INT NOT NULL, event_type_id INT NOT NULL, reference_id INT NOT NULL, event_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, effective_from DATE NOT NULL DEFAULT '1970-01-01', effective_to DATE NOT NULL DEFAULT '9999-12-31', PERIOD FOR valid_period (effective_from, effective_to), valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START, valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (valid_from, valid_to), FOREIGN KEY (job_site_id) REFERENCES job_site(job_site_id), FOREIGN KEY (event_type_id) REFERENCES event_type(event_type_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 WITH SYSTEM VERSIONING; -- Indexes for Timeline Event CREATE INDEX idx_timeline_event_job_site_id ON timeline_event (job_site_id); CREATE INDEX idx_timeline_event_event_type_id ON timeline_event (event_type_id); CREATE INDEX idx_timeline_event_reference_id ON timeline_event (reference_id); -- Post Table CREATE TABLE post ( post_id INT AUTO_INCREMENT PRIMARY KEY, job_site_id INT NOT NULL, author_id INT NOT NULL, content TEXT NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, is_public TINYINT(1) NOT NULL DEFAULT 0, public_access_token VARCHAR(64) DEFAULT NULL, is_deleted TINYINT(1) NOT NULL DEFAULT 0, effective_from DATE NOT NULL DEFAULT '1970-01-01', effective_to DATE NOT NULL DEFAULT '9999-12-31', PERIOD FOR valid_period (effective_from, effective_to), valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START, valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (valid_from, valid_to), FOREIGN KEY (job_site_id) REFERENCES job_site(job_site_id), FOREIGN KEY (author_id) REFERENCES user_account(user_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 WITH SYSTEM VERSIONING; -- Indexes for Post CREATE INDEX idx_post_job_site_id ON post (job_site_id); CREATE INDEX idx_post_author_id ON post (author_id); CREATE INDEX idx_post_created_at ON post (created_at); -- Daily Report Table CREATE TABLE daily_report ( report_id INT AUTO_INCREMENT PRIMARY KEY, worker_id INT NOT NULL, job_site_id INT NOT NULL, report_date DATE NOT NULL, summary TEXT DEFAULT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, is_deleted TINYINT(1) NOT NULL DEFAULT 0, effective_from DATE NOT NULL DEFAULT '1970-01-01', effective_to DATE NOT NULL DEFAULT '9999-12-31', PERIOD FOR valid_period (effective_from, effective_to), valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START, valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (valid_from, valid_to), FOREIGN KEY (worker_id) REFERENCES worker(worker_id), FOREIGN KEY (job_site_id) REFERENCES job_site(job_site_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 WITH SYSTEM VERSIONING; -- Indexes for Daily Report CREATE INDEX idx_daily_report_worker_id ON daily_report (worker_id); CREATE INDEX idx_daily_report_job_site_id ON daily_report (job_site_id); CREATE INDEX idx_daily_report_report_date ON daily_report (report_date); -- Artifact Table CREATE TABLE artifact ( artifact_id INT AUTO_INCREMENT PRIMARY KEY, artifact_type_id INT NOT NULL, created_by INT NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, media_url VARCHAR(255) DEFAULT NULL, content TEXT DEFAULT NULL, is_deleted TINYINT(1) NOT NULL DEFAULT 0, effective_from DATE NOT NULL DEFAULT '1970-01-01', effective_to DATE NOT NULL DEFAULT '9999-12-31', PERIOD FOR valid_period (effective_from, effective_to), valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START, valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (valid_from, valid_to), FOREIGN KEY (artifact_type_id) REFERENCES artifact_type(artifact_type_id), FOREIGN KEY (created_by) REFERENCES user_account(user_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 WITH SYSTEM VERSIONING; -- Indexes for Artifact CREATE INDEX idx_artifact_artifact_type_id ON artifact (artifact_type_id); CREATE INDEX idx_artifact_created_by ON artifact (created_by); CREATE INDEX idx_artifact_created_at ON artifact (created_at); -- GPS Log Entry Table CREATE TABLE gps_log_entry ( gps_log_entry_id INT AUTO_INCREMENT PRIMARY KEY, worker_id INT NOT NULL, latitude DECIMAL(10,7) NOT NULL, longitude DECIMAL(10,7) NOT NULL, log_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, accuracy DECIMAL(5,2) DEFAULT NULL, altitude DECIMAL(8,2) DEFAULT NULL, speed DECIMAL(5,2) DEFAULT NULL, is_deleted TINYINT(1) NOT NULL DEFAULT 0, is_clock_in_out TINYINT(1) NOT NULL DEFAULT 0, valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START, valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (valid_from, valid_to), FOREIGN KEY (worker_id) REFERENCES worker(worker_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 WITH SYSTEM VERSIONING; -- Indexes for GPS Log Entry CREATE INDEX idx_gps_log_entry_worker_id ON gps_log_entry (worker_id); CREATE INDEX idx_gps_log_entry_log_timestamp ON gps_log_entry (log_timestamp); CREATE INDEX idx_gps_log_entry_is_clock_in_out ON gps_log_entry (is_clock_in_out); -- Post Artifact Mapping CREATE TABLE post_artifact ( post_id INT NOT NULL, artifact_id INT NOT NULL, PRIMARY KEY (post_id, artifact_id), FOREIGN KEY (post_id) REFERENCES post(post_id), FOREIGN KEY (artifact_id) REFERENCES artifact(artifact_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- Daily Report Artifact Mapping CREATE TABLE daily_report_artifact ( report_id INT NOT NULL, artifact_id INT NOT NULL, PRIMARY KEY (report_id, artifact_id), FOREIGN KEY (report_id) REFERENCES daily_report(report_id), FOREIGN KEY (artifact_id) REFERENCES artifact(artifact_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- Blueprint Table CREATE TABLE blueprint ( blueprint_id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(200) NOT NULL, description TEXT DEFAULT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, is_deleted TINYINT(1) NOT NULL DEFAULT 0, effective_from DATE NOT NULL DEFAULT '1970-01-01', effective_to DATE NOT NULL DEFAULT '9999-12-31', PERIOD FOR valid_period (effective_from, effective_to), valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START, valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (valid_from, valid_to) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 WITH SYSTEM VERSIONING; -- Indexes for Blueprint CREATE INDEX idx_blueprint_name ON blueprint (name); -- Blueprint Segment Table CREATE TABLE blueprint_segment ( segment_id INT AUTO_INCREMENT PRIMARY KEY, blueprint_id INT NOT NULL, name VARCHAR(200) NOT NULL, description TEXT DEFAULT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, is_deleted TINYINT(1) NOT NULL DEFAULT 0, effective_from DATE NOT NULL DEFAULT '1970-01-01', effective_to DATE NOT NULL DEFAULT '9999-12-31', PERIOD FOR valid_period (effective_from, effective_to), valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START, valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (valid_from, valid_to), FOREIGN KEY (blueprint_id) REFERENCES blueprint(blueprint_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 WITH SYSTEM VERSIONING; -- Indexes for Blueprint Segment CREATE INDEX idx_blueprint_segment_blueprint_id ON blueprint_segment (blueprint_id); CREATE INDEX idx_blueprint_segment_name ON blueprint_segment (name); -- Task Table CREATE TABLE task ( task_id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(200) NOT NULL, description TEXT DEFAULT NULL, estimated_hours DECIMAL(5,2) DEFAULT NULL, trade VARCHAR(50) DEFAULT NULL, created_by INT NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, blueprint_segment_id INT DEFAULT NULL, is_deleted TINYINT(1) NOT NULL DEFAULT 0, effective_from DATE NOT NULL DEFAULT '1970-01-01', effective_to DATE NOT NULL DEFAULT '9999-12-31', PERIOD FOR valid_period (effective_from, effective_to), valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START, valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (valid_from, valid_to), FOREIGN KEY (created_by) REFERENCES user_account(user_id), FOREIGN KEY (blueprint_segment_id) REFERENCES blueprint_segment(segment_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 WITH SYSTEM VERSIONING; -- Indexes for Task CREATE INDEX idx_task_name ON task (name); CREATE INDEX idx_task_created_by ON task (created_by); CREATE INDEX idx_task_blueprint_segment_id ON task (blueprint_segment_id); -- Task Dependency Table CREATE TABLE task_dependency ( task_id INT NOT NULL, depends_on_task_id INT NOT NULL, effective_from DATE NOT NULL DEFAULT '1970-01-01', effective_to DATE NOT NULL DEFAULT '9999-12-31', PERIOD FOR valid_period (effective_from, effective_to), PRIMARY KEY (task_id, depends_on_task_id, effective_from), FOREIGN KEY (task_id) REFERENCES task(task_id), FOREIGN KEY (depends_on_task_id) REFERENCES task(task_id), valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START, valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (valid_from, valid_to) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 WITH SYSTEM VERSIONING; -- Assignment Table CREATE TABLE assignment ( assignment_id INT AUTO_INCREMENT PRIMARY KEY, crew_id INT NOT NULL, scheduled_date DATE NOT NULL, shift_type_id INT NOT NULL, notes TEXT DEFAULT NULL, assigned_by INT NOT NULL, assigned_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, is_deleted TINYINT(1) NOT NULL DEFAULT 0, effective_from DATE NOT NULL DEFAULT '1970-01-01', effective_to DATE NOT NULL DEFAULT '9999-12-31', PERIOD FOR valid_period (effective_from, effective_to), valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START, valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (valid_from, valid_to), FOREIGN KEY (crew_id) REFERENCES crew(crew_id), FOREIGN KEY (shift_type_id) REFERENCES shift_type(shift_type_id), FOREIGN KEY (assigned_by) REFERENCES user_account(user_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 WITH SYSTEM VERSIONING; -- Indexes for Assignment CREATE INDEX idx_assignment_crew_id ON assignment (crew_id); CREATE INDEX idx_assignment_scheduled_date ON assignment (scheduled_date); CREATE INDEX idx_assignment_shift_type_id ON assignment (shift_type_id); -- Assignment Task Mapping CREATE TABLE assignment_task ( assignment_id INT NOT NULL, task_id INT NOT NULL, effective_from DATE NOT NULL DEFAULT '1970-01-01', effective_to DATE NOT NULL DEFAULT '9999-12-31', PERIOD FOR valid_period (effective_from, effective_to), PRIMARY KEY (assignment_id, task_id, effective_from), FOREIGN KEY (assignment_id) REFERENCES assignment(assignment_id), FOREIGN KEY (task_id) REFERENCES task(task_id), valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START, valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (valid_from, valid_to) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 WITH SYSTEM VERSIONING; -- Weekly Timesheet Table CREATE TABLE weekly_timesheet ( timesheet_id INT AUTO_INCREMENT PRIMARY KEY, worker_id INT NOT NULL, week_start_date DATE NOT NULL, submitted_at TIMESTAMP DEFAULT NULL, approved_by INT DEFAULT NULL, approved_at TIMESTAMP DEFAULT NULL, total_hours DECIMAL(5,2) DEFAULT NULL, is_deleted TINYINT(1) NOT NULL DEFAULT 0, current_state VARCHAR(50) NOT NULL DEFAULT 'PENDING', effective_from DATE NOT NULL DEFAULT '1970-01-01', effective_to DATE NOT NULL DEFAULT '9999-12-31', PERIOD FOR valid_period (effective_from, effective_to), valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START, valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (valid_from, valid_to), FOREIGN KEY (worker_id) REFERENCES worker(worker_id), FOREIGN KEY (approved_by) REFERENCES user_account(user_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 WITH SYSTEM VERSIONING; -- Indexes for Weekly Timesheet CREATE INDEX idx_weekly_timesheet_worker_id ON weekly_timesheet (worker_id); CREATE INDEX idx_weekly_timesheet_week_start_date ON weekly_timesheet (week_start_date); CREATE INDEX idx_weekly_timesheet_current_state ON weekly_timesheet (current_state); -- Time Entry Table CREATE TABLE time_entry ( time_entry_id INT AUTO_INCREMENT PRIMARY KEY, timesheet_id INT NOT NULL, date DATE NOT NULL, clock_in_time TIMESTAMP DEFAULT NULL, clock_out_time TIMESTAMP DEFAULT NULL, clock_in_gps_log_entry_id INT DEFAULT NULL, clock_out_gps_log_entry_id INT DEFAULT NULL, shift_type_id INT NOT NULL, approved_by INT DEFAULT NULL, approved_at TIMESTAMP DEFAULT NULL, total_hours DECIMAL(5,2) DEFAULT NULL, notes TEXT DEFAULT NULL, is_deleted TINYINT(1) NOT NULL DEFAULT 0, current_state VARCHAR(50) NOT NULL DEFAULT 'PENDING', effective_from DATE NOT NULL DEFAULT '1970-01-01', effective_to DATE NOT NULL DEFAULT '9999-12-31', PERIOD FOR valid_period (effective_from, effective_to), valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START, valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (valid_from, valid_to), FOREIGN KEY (timesheet_id) REFERENCES weekly_timesheet(timesheet_id), FOREIGN KEY (clock_in_gps_log_entry_id) REFERENCES gps_log_entry(gps_log_entry_id), FOREIGN KEY (clock_out_gps_log_entry_id) REFERENCES gps_log_entry(gps_log_entry_id), FOREIGN KEY (shift_type_id) REFERENCES shift_type(shift_type_id), FOREIGN KEY (approved_by) REFERENCES user_account(user_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 WITH SYSTEM VERSIONING; -- Indexes for Time Entry CREATE INDEX idx_time_entry_timesheet_id ON time_entry (timesheet_id); CREATE INDEX idx_time_entry_date ON time_entry (date); CREATE INDEX idx_time_entry_shift_type_id ON time_entry (shift_type_id); CREATE INDEX idx_time_entry_current_state ON time_entry (current_state); -- Inventory Item Table CREATE TABLE inventory_item ( item_id INT AUTO_INCREMENT PRIMARY KEY, item_code VARCHAR(50) NOT NULL UNIQUE, name VARCHAR(200) NOT NULL, description TEXT DEFAULT NULL, unit_price DECIMAL(10,2) NOT NULL, reorder_level DECIMAL(10,2) DEFAULT NULL, safety_stock_level DECIMAL(10,2) DEFAULT NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, is_deleted TINYINT(1) NOT NULL DEFAULT 0, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, effective_from DATE NOT NULL DEFAULT '1970-01-01', effective_to DATE NOT NULL DEFAULT '9999-12-31', PERIOD FOR valid_period (effective_from, effective_to), valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START, valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (valid_from, valid_to) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 WITH SYSTEM VERSIONING; -- Indexes for Inventory Item CREATE INDEX idx_inventory_item_item_code ON inventory_item (item_code); CREATE INDEX idx_inventory_item_name ON inventory_item (name); CREATE INDEX idx_inventory_item_is_active ON inventory_item (is_active); -- Inventory Location Table CREATE TABLE inventory_location ( location_id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, address VARCHAR(255) DEFAULT NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, effective_from DATE NOT NULL DEFAULT '1970-01-01', effective_to DATE NOT NULL DEFAULT '9999-12-31', PERIOD FOR valid_period (effective_from, effective_to), valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START, valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (valid_from, valid_to) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 WITH SYSTEM VERSIONING; -- Indexes for Inventory Location CREATE INDEX idx_inventory_location_name ON inventory_location (name); CREATE INDEX idx_inventory_location_is_active ON inventory_location (is_active); -- Inventory Stock Table CREATE TABLE inventory_stock ( stock_id INT AUTO_INCREMENT PRIMARY KEY, item_id INT NOT NULL, location_id INT NOT NULL, quantity_on_hand DECIMAL(10,2) NOT NULL DEFAULT 0.00, serial_number VARCHAR(20) DEFAULT NULL, effective_from DATE NOT NULL DEFAULT '1970-01-01', effective_to DATE NOT NULL DEFAULT '9999-12-31', PERIOD FOR valid_period (effective_from, effective_to), FOREIGN KEY (item_id) REFERENCES inventory_item(item_id), FOREIGN KEY (location_id) REFERENCES inventory_location(location_id), valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START, valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (valid_from, valid_to) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 WITH SYSTEM VERSIONING; -- Indexes for Inventory Stock CREATE INDEX idx_inventory_stock_item_id ON inventory_stock (item_id); CREATE INDEX idx_inventory_stock_serial_number ON inventory_stock (serial_number); -- Inventory Transaction Table CREATE TABLE inventory_transaction ( transaction_id INT AUTO_INCREMENT PRIMARY KEY, journal_entry_id INT DEFAULT NULL, item_id INT NOT NULL, from_location_id INT DEFAULT NULL, to_location_id INT DEFAULT NULL, quantity DECIMAL(10,2) NOT NULL, transaction_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, transaction_type_id INT NOT NULL, initiated_by INT NOT NULL, notes TEXT DEFAULT NULL, is_deleted TINYINT(1) NOT NULL DEFAULT 0, effective_from DATE NOT NULL DEFAULT '1970-01-01', effective_to DATE NOT NULL DEFAULT '9999-12-31', PERIOD FOR valid_period (effective_from, effective_to), valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START, valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (valid_from, valid_to), FOREIGN KEY (item_id) REFERENCES inventory_item(item_id), FOREIGN KEY (from_location_id) REFERENCES inventory_location(location_id), FOREIGN KEY (to_location_id) REFERENCES inventory_location(location_id), FOREIGN KEY (initiated_by) REFERENCES user_account(user_id), FOREIGN KEY (transaction_type_id) REFERENCES transaction_type(transaction_type_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 WITH SYSTEM VERSIONING; -- Indexes for Inventory Transaction CREATE INDEX idx_inventory_transaction_item_id ON inventory_transaction (item_id); CREATE INDEX idx_inventory_transaction_transaction_type_id ON inventory_transaction (transaction_type_id); CREATE INDEX idx_inventory_transaction_transaction_timestamp ON inventory_transaction (transaction_timestamp); -- Supplier Table CREATE TABLE supplier ( supplier_id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(200) NOT NULL, contact_info TEXT DEFAULT NULL, is_deleted TINYINT(1) NOT NULL DEFAULT 0, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, effective_from DATE NOT NULL DEFAULT '1970-01-01', effective_to DATE NOT NULL DEFAULT '9999-12-31', PERIOD FOR valid_period (effective_from, effective_to), valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START, valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (valid_from, valid_to) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 WITH SYSTEM VERSIONING; -- Indexes for Supplier CREATE INDEX idx_supplier_name ON supplier (name); -- Supplier Item Mapping CREATE TABLE supplier_item ( supplier_item_id INT AUTO_INCREMENT PRIMARY KEY, supplier_id INT NOT NULL, inventory_item_id INT NOT NULL, confirmed_price DECIMAL(10,2) NOT NULL, last_confirmed_date DATE NOT NULL, lead_time_days INT DEFAULT NULL, effective_from DATE NOT NULL DEFAULT '1970-01-01', effective_to DATE NOT NULL DEFAULT '9999-12-31', PERIOD FOR valid_period (effective_from, effective_to), FOREIGN KEY (supplier_id) REFERENCES supplier(supplier_id), FOREIGN KEY (inventory_item_id) REFERENCES inventory_item(item_id), valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START, valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (valid_from, valid_to) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 WITH SYSTEM VERSIONING; -- Indexes for Supplier Item CREATE INDEX idx_supplier_item_supplier_id ON supplier_item (supplier_id); CREATE INDEX idx_supplier_item_inventory_item_id ON supplier_item (inventory_item_id); -- Reorder Table CREATE TABLE reorder ( request_id INT AUTO_INCREMENT PRIMARY KEY, inventory_item_id INT NOT NULL, supplier_id INT NOT NULL, requested_quantity DECIMAL(10,2) NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, effective_from DATE NOT NULL DEFAULT '1970-01-01', effective_to DATE NOT NULL DEFAULT '9999-12-31', PERIOD FOR valid_period (effective_from, effective_to), valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START, valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (valid_from, valid_to), FOREIGN KEY (inventory_item_id) REFERENCES inventory_item(item_id), FOREIGN KEY (supplier_id) REFERENCES supplier(supplier_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 WITH SYSTEM VERSIONING; -- Indexes for Reorder CREATE INDEX idx_reorder_inventory_item_id ON reorder (inventory_item_id); CREATE INDEX idx_reorder_supplier_id ON reorder (supplier_id); -- Purchase Order Table CREATE TABLE purchase_order ( purchase_order_id INT AUTO_INCREMENT PRIMARY KEY, supplier_id INT NOT NULL, order_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, expected_delivery_date TIMESTAMP DEFAULT NULL, is_deleted TINYINT(1) NOT NULL DEFAULT 0, current_state VARCHAR(50) NOT NULL DEFAULT 'DRAFT', effective_from DATE NOT NULL DEFAULT '1970-01-01', effective_to DATE NOT NULL DEFAULT '9999-12-31', PERIOD FOR valid_period (effective_from, effective_to), valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START, valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (valid_from, valid_to), FOREIGN KEY (supplier_id) REFERENCES supplier(supplier_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 WITH SYSTEM VERSIONING; -- Indexes for Purchase Order CREATE INDEX idx_purchase_order_supplier_id ON purchase_order (supplier_id); CREATE INDEX idx_purchase_order_current_state ON purchase_order (current_state); -- Purchase Order Line Table CREATE TABLE purchase_order_line ( line_id INT AUTO_INCREMENT PRIMARY KEY, purchase_order_id INT NOT NULL, item_id INT NOT NULL, quantity DECIMAL(10,2) NOT NULL, unit_price DECIMAL(10,2) NOT NULL, received_quantity DECIMAL(10,2) NOT NULL DEFAULT 0, effective_from DATE NOT NULL DEFAULT '1970-01-01', effective_to DATE NOT NULL DEFAULT '9999-12-31', PERIOD FOR valid_period (effective_from, effective_to), FOREIGN KEY (purchase_order_id) REFERENCES purchase_order(purchase_order_id), FOREIGN KEY (item_id) REFERENCES inventory_item(item_id), valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START, valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (valid_from, valid_to) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 WITH SYSTEM VERSIONING; -- Indexes for Purchase Order Line CREATE INDEX idx_purchase_order_line_purchase_order_id ON purchase_order_line (purchase_order_id); CREATE INDEX idx_purchase_order_line_item_id ON purchase_order_line (item_id); -- Return Transaction Table CREATE TABLE return_transaction ( return_id INT AUTO_INCREMENT PRIMARY KEY, item_id INT NOT NULL, quantity DECIMAL(10,2) NOT NULL, return_reason TEXT DEFAULT NULL, return_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, initiated_by INT NOT NULL, is_deleted TINYINT(1) NOT NULL DEFAULT 0, current_state VARCHAR(50) NOT NULL DEFAULT 'INITIATED', effective_from DATE NOT NULL DEFAULT '1970-01-01', effective_to DATE NOT NULL DEFAULT '9999-12-31', PERIOD FOR valid_period (effective_from, effective_to), valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START, valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (valid_from, valid_to), FOREIGN KEY (item_id) REFERENCES inventory_item(item_id), FOREIGN KEY (initiated_by) REFERENCES user_account(user_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 WITH SYSTEM VERSIONING; -- Indexes for Return Transaction CREATE INDEX idx_return_transaction_item_id ON return_transaction (item_id); CREATE INDEX idx_return_transaction_initiated_by ON return_transaction (initiated_by); CREATE INDEX idx_return_transaction_current_state ON return_transaction (current_state); -- Account Table CREATE TABLE account ( account_id INT AUTO_INCREMENT PRIMARY KEY, entity_id INT NOT NULL, account_code VARCHAR(20) NOT NULL UNIQUE, name VARCHAR(100) NOT NULL, account_type_id INT NOT NULL, parent_account_id INT DEFAULT NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, is_deleted TINYINT(1) NOT NULL DEFAULT 0, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, effective_from DATE NOT NULL DEFAULT '1970-01-01', effective_to DATE NOT NULL DEFAULT '9999-12-31', PERIOD FOR valid_period (effective_from, effective_to), valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START, valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (valid_from, valid_to), FOREIGN KEY (entity_id) REFERENCES entity(entity_id), FOREIGN KEY (account_type_id) REFERENCES account_type(account_type_id), FOREIGN KEY (parent_account_id) REFERENCES account(account_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 WITH SYSTEM VERSIONING; -- Indexes for Account CREATE INDEX idx_account_account_code ON account (account_code); CREATE INDEX idx_account_name ON account (name); CREATE INDEX idx_account_account_type_id ON account (account_type_id); CREATE INDEX idx_account_is_active ON account (is_active); -- Journal Entry Table CREATE TABLE journal_entry ( journal_entry_id INT AUTO_INCREMENT PRIMARY KEY, entity_id INT NOT NULL, description VARCHAR(255) DEFAULT NULL, entry_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, created_by INT NOT NULL, is_deleted TINYINT(1) NOT NULL DEFAULT 0, effective_from DATE NOT NULL DEFAULT '1970-01-01', effective_to DATE NOT NULL DEFAULT '9999-12-31', PERIOD FOR valid_period(effective_from, effective_to), valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START, valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME(valid_from, valid_to), FOREIGN KEY (entity_id) REFERENCES entity(entity_id), FOREIGN KEY (created_by) REFERENCES user_account(user_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 WITH SYSTEM VERSIONING; -- Indexes for Journal Entry CREATE INDEX idx_journal_entry_entity_id ON journal_entry (entity_id); CREATE INDEX idx_journal_entry_created_by ON journal_entry (created_by); CREATE INDEX idx_journal_entry_entry_timestamp ON journal_entry (entry_timestamp); -- Journal Entry Line Table CREATE TABLE journal_entry_line ( journal_entry_line_id INT AUTO_INCREMENT PRIMARY KEY, journal_entry_id INT NOT NULL, account_id INT NOT NULL, amount DECIMAL(10,2) NOT NULL, is_debit TINYINT(1) NOT NULL, description VARCHAR(255) DEFAULT NULL, effective_from DATE NOT NULL DEFAULT '1970-01-01', effective_to DATE NOT NULL DEFAULT '9999-12-31', PERIOD FOR valid_period(effective_from, effective_to), valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START, valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME(valid_from, valid_to), FOREIGN KEY (journal_entry_id) REFERENCES journal_entry(journal_entry_id), FOREIGN KEY (account_id) REFERENCES account(account_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 WITH SYSTEM VERSIONING; -- Indexes for Journal Entry Line CREATE INDEX idx_journal_entry_line_journal_entry_id ON journal_entry_line (journal_entry_id); CREATE INDEX idx_journal_entry_line_account_id ON journal_entry_line (account_id); -- Journal Entry Mapping Table CREATE TABLE journal_entry_mapping ( mapping_id INT AUTO_INCREMENT PRIMARY KEY, entity_type VARCHAR(50) NOT NULL, entity_id INT NOT NULL, journal_entry_id INT NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, effective_from DATE NOT NULL DEFAULT '1970-01-01', effective_to DATE NOT NULL DEFAULT '9999-12-31', PERIOD FOR valid_period(effective_from, effective_to), FOREIGN KEY (journal_entry_id) REFERENCES journal_entry(journal_entry_id), valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START, valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME(valid_from, valid_to) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 WITH SYSTEM VERSIONING; -- Indexes for Journal Entry Mapping CREATE INDEX idx_journal_entry_mapping_entity_type_entity_id ON journal_entry_mapping (entity_type, entity_id); CREATE INDEX idx_journal_entry_mapping_journal_entry_id ON journal_entry_mapping (journal_entry_id); -- Vehicle Table CREATE TABLE vehicle ( vehicle_id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, purchase_date DATE NOT NULL, cost DECIMAL(10,2) NOT NULL, depreciation DECIMAL(10,2) DEFAULT NULL, is_deleted TINYINT(1) NOT NULL DEFAULT 0, current_state VARCHAR(50) NOT NULL DEFAULT 'ACTIVE', created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, effective_from DATE NOT NULL DEFAULT '1970-01-01', effective_to DATE NOT NULL DEFAULT '9999-12-31', PERIOD FOR valid_period(effective_from, effective_to), valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START, valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME(valid_from, valid_to) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 WITH SYSTEM VERSIONING; -- Indexes for Vehicle CREATE INDEX idx_vehicle_name ON vehicle (name); CREATE INDEX idx_vehicle_current_state ON vehicle (current_state); -- Vehicle Maintenance Table CREATE TABLE vehicle_maintenance ( maintenance_id INT AUTO_INCREMENT PRIMARY KEY, vehicle_id INT NOT NULL, maintenance_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, description TEXT DEFAULT NULL, cost DECIMAL(10,2) NOT NULL, effective_from DATE NOT NULL DEFAULT '1970-01-01', effective_to DATE NOT NULL DEFAULT '9999-12-31', PERIOD FOR valid_period(effective_from, effective_to), valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START, valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME(valid_from, valid_to), FOREIGN KEY (vehicle_id) REFERENCES vehicle(vehicle_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 WITH SYSTEM VERSIONING; -- Indexes for Vehicle Maintenance CREATE INDEX idx_vehicle_maintenance_vehicle_id ON vehicle_maintenance (vehicle_id); CREATE INDEX idx_vehicle_maintenance_maintenance_timestamp ON vehicle_maintenance (maintenance_timestamp); -- Estimate Table CREATE TABLE estimate ( estimate_id INT AUTO_INCREMENT PRIMARY KEY, task_id INT DEFAULT NULL, details TEXT DEFAULT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, is_deleted TINYINT(1) NOT NULL DEFAULT 0, current_state VARCHAR(50) NOT NULL DEFAULT 'DRAFT', effective_from DATE NOT NULL DEFAULT '1970-01-01', effective_to DATE NOT NULL DEFAULT '9999-12-31', PERIOD FOR valid_period(effective_from, effective_to), FOREIGN KEY (task_id) REFERENCES task(task_id), valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START, valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME(valid_from, valid_to) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 WITH SYSTEM VERSIONING; -- Indexes for Estimate CREATE INDEX idx_estimate_task_id ON estimate (task_id); CREATE INDEX idx_estimate_current_state ON estimate (current_state); -- Proposal Table CREATE TABLE proposal ( proposal_id INT AUTO_INCREMENT PRIMARY KEY, estimate_id INT DEFAULT NULL, details TEXT DEFAULT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, is_deleted TINYINT(1) NOT NULL DEFAULT 0, current_state VARCHAR(50) NOT NULL DEFAULT 'DRAFT', effective_from DATE NOT NULL DEFAULT '1970-01-01', effective_to DATE NOT NULL DEFAULT '9999-12-31', PERIOD FOR valid_period(effective_from, effective_to), FOREIGN KEY (estimate_id) REFERENCES estimate(estimate_id) ON DELETE SET NULL, valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START, valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME(valid_from, valid_to) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 WITH SYSTEM VERSIONING; -- Indexes for Proposal CREATE INDEX idx_proposal_estimate_id ON proposal (estimate_id); CREATE INDEX idx_proposal_current_state ON proposal (current_state); -- Bid Table CREATE TABLE bid ( bid_id INT AUTO_INCREMENT PRIMARY KEY, proposal_id INT DEFAULT NULL, amount DECIMAL(10,2) NOT NULL, details TEXT DEFAULT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, is_deleted TINYINT(1) NOT NULL DEFAULT 0, current_state VARCHAR(50) NOT NULL DEFAULT 'PREPARED', effective_from DATE NOT NULL DEFAULT '1970-01-01', effective_to DATE NOT NULL DEFAULT '9999-12-31', PERIOD FOR valid_period(effective_from, effective_to), FOREIGN KEY (proposal_id) REFERENCES proposal(proposal_id) ON DELETE SET NULL, valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START, valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME(valid_from, valid_to) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 WITH SYSTEM VERSIONING; -- Indexes for Bid CREATE INDEX idx_bid_proposal_id ON bid (proposal_id); CREATE INDEX idx_bid_current_state ON bid (current_state); -- Contract Table CREATE TABLE contract ( contract_id INT AUTO_INCREMENT PRIMARY KEY, bid_id INT DEFAULT NULL, details TEXT DEFAULT NULL, effective_date DATE NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, is_deleted TINYINT(1) NOT NULL DEFAULT 0, current_state VARCHAR(50) NOT NULL DEFAULT 'DRAFT', effective_from DATE NOT NULL DEFAULT '1970-01-01', effective_to DATE NOT NULL DEFAULT '9999-12-31', PERIOD FOR valid_period(effective_from, effective_to), FOREIGN KEY (bid_id) REFERENCES bid(bid_id) ON DELETE SET NULL, valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START, valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME(valid_from, valid_to) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 WITH SYSTEM VERSIONING; -- Indexes for Contract CREATE INDEX idx_contract_bid_id ON contract (bid_id); CREATE INDEX idx_contract_current_state ON contract (current_state); -- Notification Table CREATE TABLE notification ( notification_id INT AUTO_INCREMENT PRIMARY KEY, recipient_user_id INT NOT NULL, message TEXT NOT NULL, notification_type VARCHAR(50) NOT NULL, is_read TINYINT(1) NOT NULL DEFAULT 0, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, effective_from DATE NOT NULL DEFAULT '1970-01-01', effective_to DATE NOT NULL DEFAULT '9999-12-31', PERIOD FOR valid_period(effective_from, effective_to), FOREIGN KEY (recipient_user_id) REFERENCES user_account(user_id), valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START, valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME(valid_from, valid_to) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 WITH SYSTEM VERSIONING; -- Indexes for Notification CREATE INDEX idx_notification_recipient_user_id ON notification (recipient_user_id); CREATE INDEX idx_notification_is_read ON notification (is_read); -- Points Transaction Table CREATE TABLE points_transaction ( transaction_id INT AUTO_INCREMENT PRIMARY KEY, worker_id INT NOT NULL, points INT NOT NULL, description VARCHAR(255) DEFAULT NULL, timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, effective_from DATE NOT NULL DEFAULT '1970-01-01', effective_to DATE NOT NULL DEFAULT '9999-12-31', PERIOD FOR valid_period(effective_from, effective_to), FOREIGN KEY (worker_id) REFERENCES worker(worker_id), valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START, valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME(valid_from, valid_to) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 WITH SYSTEM VERSIONING; -- Indexes for Points Transaction CREATE INDEX idx_points_transaction_worker_id ON points_transaction (worker_id); CREATE INDEX idx_points_transaction_timestamp ON points_transaction (timestamp); -- Client Log Entry Table CREATE TABLE client_log_entry ( log_id INT AUTO_INCREMENT PRIMARY KEY, user_id INT DEFAULT NULL, device_id VARCHAR(255) DEFAULT NULL, timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, log_level VARCHAR(50) NOT NULL, message TEXT NOT NULL, exception TEXT DEFAULT NULL, stack_trace TEXT DEFAULT NULL, app_version VARCHAR(50) DEFAULT NULL, os_version VARCHAR(50) DEFAULT NULL, device_model VARCHAR(100) DEFAULT NULL, additional_data JSON DEFAULT NULL, is_deleted TINYINT(1) NOT NULL DEFAULT 0, effective_from DATE NOT NULL DEFAULT '1970-01-01', effective_to DATE NOT NULL DEFAULT '9999-12-31', PERIOD FOR valid_period (`effective_from`, `effective_to`), valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START, valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (`valid_from`, `valid_to`), FOREIGN KEY (user_id) REFERENCES user_account(user_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 WITH SYSTEM VERSIONING; -- Indexes for Client Log Entry CREATE INDEX idx_client_log_entry_user_id ON client_log_entry (user_id); CREATE INDEX idx_client_log_entry_device_id ON client_log_entry (device_id); CREATE INDEX idx_client_log_entry_timestamp ON client_log_entry (timestamp); CREATE INDEX idx_client_log_entry_log_level ON client_log_entry (log_level); -- Final Notes -- System-versioned tables store row-level history with explicit versioning columns. -- Application-time periods added to enable business logic based on validity periods. -- Soft deletes implemented with is_deleted columns. -- Time zone awareness enabled by using TIMESTAMP data types. -- Multi-entity support for future subsidiaries. -- End of V1__Comprehensive_Schema.sql
Editor is loading...
Leave a Comment