employee-onboarding-automator

name: employee-onboarding-automator description: When the user needs to automate new employee onboarding processes including training task assignment, email notifications, and database updates. This skill handles 1) Querying employee databases (Snowflake) to identify new hires and their group assignments, 2) Analyzing onboarding documents (PDFs) to extract training requirements and deadlines, 3) Generating personalized task lists based on employee groups (Backend/Frontend/Testing/Data), 4) Inserting task records into appropriate database tables with calculated deadlines, 5) Sending onboarding emails to new employees with their task lists, 6) Checking for overdue incomplete tasks and sending reminder emails, 7) Updating tracking flags in the database. Triggers include 'new employees joining', 'onboarding automation', 'training task assignment', 'employee onboarding emails', 'task deadline reminders'.

Instructions

1. Initial Setup & Discovery

• Discover Environment: Use filesystem-list_directory to explore /workspace/dumps/workspace for onboarding documents (PDFs).
• Database Discovery: Use snowflake-list_databases and snowflake-list_schemas to locate the relevant database (e.g., LANDING_TASK_REMINDER).
• Table Discovery: Use snowflake-list_tables to find employee and task tables (e.g., EMPLOYEE, EMPLOYEE_LANDING, PUBLIC_TASKS, GROUP_TASKS_*).

2. Analyze Onboarding Document

• Read PDF: Use pdf-tools-get_pdf_info and pdf-tools-read_pdf_pages to extract the onboarding checklist from the PDF (e.g., landing_tips.pdf).
• Extract Task Structure: Parse the PDF to identify:
  • Public Tasks (for all departments): e.g., "Onboarding Training", "Security Training", "Confidentiality Training", "Company Culture", "Company Strategy". Note their relative deadlines (e.g., "complete by D+7").
  • Group-Specific Tasks: Identify tasks for Backend (D+30), Frontend (D+45), Testing (D+60), and Data (D+75) groups. Each group typically has "Development Process", "Development Standards", and "Development Environment" tasks.

3. Identify New Employees & Group Assignments

• Query Employee Data: Use snowflake-read_query on EMPLOYEE and EMPLOYEE_LANDING tables.
• Find New Hires: Identify employees where LANDING_TASK_ASSIGNED = FALSE and LANDING_DATE is recent (or as per user request).
• Determine Group Assignment: An employee's group is inferred from their REPORT_TO_ID (manager). Map managers to groups based on existing task table patterns (e.g., employees with tasks in GROUP_TASKS_BACKEND report to manager ID 8001).
• Get Manager Emails: Join with the EMPLOYEE table to get the manager's email for CC.

4. Check for Overdue Tasks

• Query All Task Tables: For PUBLIC_TASKS and each GROUP_TASKS_* table, find records where FINISHED_FLAG = FALSE AND DDL < CURRENT_DATE().
• Aggregate by Employee: Collect all overdue tasks for each employee, including their manager's email.

5. Calculate Deadlines & Generate Task Lists

• Base Date: Use CURRENT_DATE() as the start date (Day 0).
• Calculate Deadlines:
  • Public Tasks: Day 0 + 7 days.
  • Backend Tasks: Day 0 + 30 days.
  • Frontend Tasks: Day 0 + 45 days.
  • Testing Tasks: Day 0 + 60 days.
  • Data Tasks: Day 0 + 75 days.
• Format Dates: Use 'YYYY-MM-DD' format for database insertion and email content.

6. Update Database

• Insert New Tasks: Use snowflake-write_query to insert records into the appropriate tables (PUBLIC_TASKS, GROUP_TASKS_*) for each new employee.
• Update Tracking Flag: Update EMPLOYEE_LANDING table, setting LANDING_TASK_ASSIGNED = TRUE for processed new hires.

7. Send Emails

• Onboarding Emails: For each new employee, use emails-send_email.
  • To: Employee's email.
  • CC: Their manager's email (from REPORT_TO_ID lookup).
  • Subject: "Onboarding Training Tasks".
  • Body: Follow the exact format: