Employee Onboarding Media

Objective

Create a comprehensive employee onboarding content management system with structured learning paths, progress tracking, and completion verification for new hires.

Step 1: Create Onboarding Programs Table

Define onboarding tracks.

CREATE TABLE onboarding_programs (
    id INTEGER PRIMARY KEY,
    program_code VARCHAR(50) NOT NULL UNIQUE,
    program_name VARCHAR(200) NOT NULL,
    description TEXT,
    target_role VARCHAR(100),
    department VARCHAR(100),
    duration_days INTEGER DEFAULT 30,
    is_mandatory BOOLEAN DEFAULT TRUE,
    thumbnail IMAGE(JPEG),
    welcome_video VIDEO(MP4),
    status VARCHAR(50) DEFAULT 'active',
    created_by VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Create Onboarding Modules Table

Organize content into modules.

CREATE TABLE onboarding_modules (
    id INTEGER PRIMARY KEY,
    program_id INTEGER NOT NULL,
    module_number INTEGER,
    module_name VARCHAR(200),
    description TEXT,
    estimated_minutes INTEGER,
    unlock_day INTEGER DEFAULT 1,
    is_required BOOLEAN DEFAULT TRUE,
    prerequisite_module_id INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (program_id) REFERENCES onboarding_programs(id),
    FOREIGN KEY (prerequisite_module_id) REFERENCES onboarding_modules(id)
);

Step 3: Create Onboarding Content Table

Store learning materials.

CREATE TABLE onboarding_content (
    id INTEGER PRIMARY KEY,
    module_id INTEGER NOT NULL,
    content_order INTEGER,
    content_type VARCHAR(50),
    title VARCHAR(200),
    description TEXT,
    video_file VIDEO(MP4),
    document_file PDF,
    image_file IMAGE(JPEG),
    external_url VARCHAR(500),
    duration_minutes INTEGER,
    page_count INTEGER,
    is_required BOOLEAN DEFAULT TRUE,
    requires_acknowledgment BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (module_id) REFERENCES onboarding_modules(id)
);

Step 4: Create New Hires Table

Track employee onboarding.

CREATE TABLE new_hires (
    id INTEGER PRIMARY KEY,
    employee_id VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(200),
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    department VARCHAR(100),
    job_title VARCHAR(100),
    manager_id VARCHAR(50),
    manager_name VARCHAR(200),
    start_date DATE,
    location VARCHAR(100),
    employment_type VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 5: Create Onboarding Enrollments Table

Track program assignments.

CREATE TABLE onboarding_enrollments (
    id INTEGER PRIMARY KEY,
    new_hire_id INTEGER NOT NULL,
    program_id INTEGER NOT NULL,
    assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    assigned_by VARCHAR(100),
    due_date DATE,
    started_at TIMESTAMP,
    completed_at TIMESTAMP,
    progress_percent DECIMAL(5, 2) DEFAULT 0,
    status VARCHAR(50) DEFAULT 'assigned',
    FOREIGN KEY (new_hire_id) REFERENCES new_hires(id),
    FOREIGN KEY (program_id) REFERENCES onboarding_programs(id)
);

Step 6: Create Content Progress Table

Track completion of items.

CREATE TABLE content_progress (
    id INTEGER PRIMARY KEY,
    enrollment_id INTEGER NOT NULL,
    content_id INTEGER NOT NULL,
    started_at TIMESTAMP,
    completed_at TIMESTAMP,
    time_spent_seconds INTEGER DEFAULT 0,
    is_completed BOOLEAN DEFAULT FALSE,
    acknowledged BOOLEAN DEFAULT FALSE,
    acknowledged_at TIMESTAMP,
    quiz_score DECIMAL(5, 2),
    last_position_seconds INTEGER DEFAULT 0,
    FOREIGN KEY (enrollment_id) REFERENCES onboarding_enrollments(id),
    FOREIGN KEY (content_id) REFERENCES onboarding_content(id)
);

Step 7: Create Onboarding Tasks Table

Track action items.

CREATE TABLE onboarding_tasks (
    id INTEGER PRIMARY KEY,
    program_id INTEGER NOT NULL,
    task_order INTEGER,
    task_name VARCHAR(200),
    description TEXT,
    task_type VARCHAR(50),
    assigned_to VARCHAR(50),
    due_day_offset INTEGER,
    is_required BOOLEAN DEFAULT TRUE,
    requires_verification BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (program_id) REFERENCES onboarding_programs(id)
);

Step 8: Create Task Completions Table

Track task status.

CREATE TABLE task_completions (
    id INTEGER PRIMARY KEY,
    enrollment_id INTEGER NOT NULL,
    task_id INTEGER NOT NULL,
    status VARCHAR(50) DEFAULT 'pending',
    completed_at TIMESTAMP,
    verified_by VARCHAR(100),
    verified_at TIMESTAMP,
    notes TEXT,
    FOREIGN KEY (enrollment_id) REFERENCES onboarding_enrollments(id),
    FOREIGN KEY (task_id) REFERENCES onboarding_tasks(id)
);

Step 9: Insert Sample Programs

Add onboarding tracks.

INSERT INTO onboarding_programs (id, program_code, program_name, description, target_role, department, duration_days, is_mandatory, status) VALUES
    (1, 'ONB-GENERAL', 'General Employee Onboarding', 'Standard onboarding for all new hires', 'All', 'All', 30, TRUE, 'active'),
    (2, 'ONB-ENG', 'Engineering Onboarding', 'Technical onboarding for engineering team', 'Engineer', 'Engineering', 45, TRUE, 'active'),
    (3, 'ONB-SALES', 'Sales Onboarding', 'Sales team onboarding program', 'Sales Rep', 'Sales', 30, TRUE, 'active'),
    (4, 'ONB-MANAGER', 'New Manager Onboarding', 'Onboarding for new people managers', 'Manager', 'All', 60, TRUE, 'active');

Step 10: Insert Modules and Content

Add learning materials.

INSERT INTO onboarding_modules (id, program_id, module_number, module_name, description, estimated_minutes, unlock_day, is_required) VALUES
    (1, 1, 1, 'Welcome to the Company', 'Company overview and culture', 60, 1, TRUE),
    (2, 1, 2, 'HR Essentials', 'Benefits, policies, and compliance', 90, 1, TRUE),
    (3, 1, 3, 'IT Systems Setup', 'Getting your technology configured', 45, 1, TRUE),
    (4, 1, 4, 'Security & Compliance', 'Required security training', 60, 3, TRUE),
    (5, 1, 5, 'Your Role & Team', 'Understanding your position', 30, 5, TRUE),
    (6, 2, 1, 'Development Environment', 'Setting up your dev tools', 120, 1, TRUE),
    (7, 2, 2, 'Codebase Overview', 'Understanding our architecture', 180, 3, TRUE),
    (8, 2, 3, 'Engineering Practices', 'Coding standards and workflows', 90, 7, TRUE);

INSERT INTO onboarding_content (id, module_id, content_order, content_type, title, description, duration_minutes, is_required, requires_acknowledgment) VALUES
    (1, 1, 1, 'video', 'CEO Welcome Message', 'Personal welcome from our CEO', 8, TRUE, FALSE),
    (2, 1, 2, 'video', 'Company History & Mission', 'Our story and where we are going', 15, TRUE, FALSE),
    (3, 1, 3, 'video', 'Culture & Values', 'What makes us unique', 12, TRUE, FALSE),
    (4, 1, 4, 'document', 'Employee Handbook', 'Complete employee handbook', 30, TRUE, TRUE),
    (5, 2, 1, 'video', 'Benefits Overview', 'Your benefits explained', 20, TRUE, FALSE),
    (6, 2, 2, 'document', 'Benefits Enrollment Guide', 'How to enroll in benefits', 15, TRUE, FALSE),
    (7, 2, 3, 'video', 'Time Off Policies', 'PTO, holidays, and leave', 10, TRUE, FALSE),
    (8, 2, 4, 'document', 'Code of Conduct', 'Workplace conduct policies', 20, TRUE, TRUE),
    (9, 3, 1, 'video', 'IT Tools Overview', 'Software and systems you will use', 15, TRUE, FALSE),
    (10, 3, 2, 'document', 'IT Setup Checklist', 'Step-by-step setup guide', 30, TRUE, FALSE),
    (11, 4, 1, 'video', 'Information Security', 'Protecting company data', 25, TRUE, FALSE),
    (12, 4, 2, 'video', 'Phishing Awareness', 'Recognizing security threats', 15, TRUE, FALSE),
    (13, 4, 3, 'document', 'Security Policy', 'IT security policies', 15, TRUE, TRUE);

Step 11: Insert Tasks

Add onboarding action items.

INSERT INTO onboarding_tasks (id, program_id, task_order, task_name, description, task_type, assigned_to, due_day_offset, is_required, requires_verification) VALUES
    (1, 1, 1, 'Complete I-9 Documentation', 'Submit employment verification documents', 'hr_task', 'new_hire', 3, TRUE, TRUE),
    (2, 1, 2, 'Set Up Direct Deposit', 'Configure payroll direct deposit', 'hr_task', 'new_hire', 5, TRUE, FALSE),
    (3, 1, 3, 'Enroll in Benefits', 'Complete benefits enrollment', 'hr_task', 'new_hire', 30, TRUE, FALSE),
    (4, 1, 4, 'Schedule 1:1 with Manager', 'First meeting with your manager', 'meeting', 'new_hire', 1, TRUE, TRUE),
    (5, 1, 5, 'Complete Building Tour', 'Tour of office facilities', 'activity', 'manager', 2, TRUE, TRUE),
    (6, 1, 6, 'Meet Your Team', 'Introduction to team members', 'meeting', 'manager', 3, TRUE, FALSE),
    (7, 2, 1, 'Set Up Development Machine', 'Configure your dev environment', 'tech_task', 'new_hire', 1, TRUE, TRUE),
    (8, 2, 2, 'Clone Core Repositories', 'Get access to code repositories', 'tech_task', 'new_hire', 2, TRUE, FALSE),
    (9, 2, 3, 'Complete First Pull Request', 'Make your first code contribution', 'tech_task', 'new_hire', 14, TRUE, TRUE);

Step 12: Insert New Hires and Enrollments

Add employee data.

INSERT INTO new_hires (id, employee_id, email, first_name, last_name, department, job_title, manager_name, start_date, location) VALUES
    (1, 'EMP-2024-001', 'john.new@company.com', 'John', 'New', 'Engineering', 'Software Engineer', 'Mike Manager', '2024-01-15', 'San Francisco'),
    (2, 'EMP-2024-002', 'jane.starter@company.com', 'Jane', 'Starter', 'Sales', 'Sales Representative', 'Sarah Sales', '2024-01-15', 'New York'),
    (3, 'EMP-2024-003', 'bob.recent@company.com', 'Bob', 'Recent', 'Engineering', 'DevOps Engineer', 'Mike Manager', '2024-01-22', 'San Francisco'),
    (4, 'EMP-2024-004', 'alice.fresh@company.com', 'Alice', 'Fresh', 'Marketing', 'Marketing Coordinator', 'Mary Marketing', '2024-01-29', 'Chicago');

INSERT INTO onboarding_enrollments (id, new_hire_id, program_id, assigned_at, assigned_by, due_date, started_at, progress_percent, status) VALUES
    (1, 1, 1, '2024-01-15 09:00:00', 'HR System', '2024-02-14', '2024-01-15 09:30:00', 85.0, 'in_progress'),
    (2, 1, 2, '2024-01-15 09:00:00', 'HR System', '2024-02-28', '2024-01-16 10:00:00', 45.0, 'in_progress'),
    (3, 2, 1, '2024-01-15 09:00:00', 'HR System', '2024-02-14', '2024-01-15 09:00:00', 100.0, 'completed'),
    (4, 2, 3, '2024-01-15 09:00:00', 'HR System', '2024-02-14', '2024-01-16 14:00:00', 60.0, 'in_progress'),
    (5, 3, 1, '2024-01-22 09:00:00', 'HR System', '2024-02-21', '2024-01-22 10:00:00', 40.0, 'in_progress'),
    (6, 4, 1, '2024-01-29 09:00:00', 'HR System', '2024-02-28', NULL, 0.0, 'assigned');

Step 13: Insert Progress Data

Add completion records.

INSERT INTO content_progress (id, enrollment_id, content_id, started_at, completed_at, time_spent_seconds, is_completed, acknowledged) VALUES
    (1, 1, 1, '2024-01-15 09:30:00', '2024-01-15 09:40:00', 600, TRUE, FALSE),
    (2, 1, 2, '2024-01-15 09:45:00', '2024-01-15 10:05:00', 1200, TRUE, FALSE),
    (3, 1, 3, '2024-01-15 10:10:00', '2024-01-15 10:25:00', 900, TRUE, FALSE),
    (4, 1, 4, '2024-01-15 10:30:00', '2024-01-15 11:00:00', 1800, TRUE, TRUE),
    (5, 1, 5, '2024-01-15 14:00:00', '2024-01-15 14:25:00', 1500, TRUE, FALSE),
    (6, 3, 1, '2024-01-15 09:00:00', '2024-01-15 09:10:00', 600, TRUE, FALSE),
    (7, 3, 2, '2024-01-15 09:15:00', '2024-01-15 09:30:00', 900, TRUE, FALSE);

INSERT INTO task_completions (id, enrollment_id, task_id, status, completed_at, verified_by, verified_at) VALUES
    (1, 1, 1, 'completed', '2024-01-17 10:00:00', 'HR Admin', '2024-01-17 11:00:00'),
    (2, 1, 2, 'completed', '2024-01-18 14:00:00', NULL, NULL),
    (3, 1, 4, 'completed', '2024-01-15 11:00:00', 'Mike Manager', '2024-01-15 12:00:00'),
    (4, 1, 5, 'completed', '2024-01-16 10:00:00', 'Mike Manager', '2024-01-16 10:30:00'),
    (5, 3, 1, 'completed', '2024-01-16 09:00:00', 'HR Admin', '2024-01-16 10:00:00'),
    (6, 3, 4, 'completed', '2024-01-15 14:00:00', 'Sarah Sales', '2024-01-15 15:00:00');

Step 14: Onboarding Status Dashboard

View all new hires progress.

SELECT
    nh.first_name || ' ' || nh.last_name as employee_name,
    nh.department,
    nh.job_title,
    nh.start_date,
    op.program_name,
    oe.progress_percent,
    oe.due_date,
    oe.status,
    CASE
        WHEN oe.status = 'completed' THEN 'Complete'
        WHEN oe.due_date < CURRENT_DATE AND oe.status != 'completed' THEN 'Overdue'
        WHEN oe.progress_percent >= 75 THEN 'On Track'
        ELSE 'Needs Attention'
    END as health_status
FROM new_hires nh
INNER JOIN onboarding_enrollments oe ON nh.id = oe.new_hire_id
INNER JOIN onboarding_programs op ON oe.program_id = op.id
ORDER BY nh.start_date DESC, oe.progress_percent;

Step 15: Task Completion Report

Track action items.

SELECT
    nh.first_name || ' ' || nh.last_name as employee_name,
    op.program_name,
    ot.task_name,
    ot.due_day_offset,
    nh.start_date + ot.due_day_offset as due_date,
    tc.status,
    tc.completed_at,
    tc.verified_by
FROM new_hires nh
INNER JOIN onboarding_enrollments oe ON nh.id = oe.new_hire_id
INNER JOIN onboarding_programs op ON oe.program_id = op.id
INNER JOIN onboarding_tasks ot ON op.id = ot.program_id
LEFT JOIN task_completions tc ON oe.id = tc.enrollment_id AND ot.id = tc.task_id
WHERE nh.id = 1
ORDER BY ot.task_order;

Cleanup (Optional)

DROP TABLE IF EXISTS task_completions;
DROP TABLE IF EXISTS onboarding_tasks;
DROP TABLE IF EXISTS content_progress;
DROP TABLE IF EXISTS onboarding_enrollments;
DROP TABLE IF EXISTS new_hires;
DROP TABLE IF EXISTS onboarding_content;
DROP TABLE IF EXISTS onboarding_modules;
DROP TABLE IF EXISTS onboarding_programs;

Expected Outcomes

• Onboarding programs defined
• Content organized by modules
• Progress tracked per hire
• Tasks monitored
• Completion verified

Key Concepts Learned

• Onboarding program structure
• Multi-media learning content
• Progress tracking
• Task management
• Completion verification