Developer Handover Pack

Developer Handover Pack

Training & Certification Tracking

Runs on a daily schedule to scan all active employee certification records, classify each record into the correct reminder tier (90-day, 60-day, 30-day, or overdue), and prepare and dispatch personalised reminder payloads. It handles the branching logic for which employees need which message, fires Gmail reminder emails and Slack manager notifications, writes a timestamped log entry to Google Sheets for every action taken, and flags overdue records for priority escalation. This agent replaces the manual spreadsheet scan, HRIS cross-check, email drafting, and log-update steps that consume the bulk of the HR Coordinator's weekly time.

// Input
BambooHR.employees[] -> filter: status == 'Active'
  -> fields: employee_id, full_name, email, manager_id, manager_email, manager_slack_id
Google Sheets[CertificationLog] -> rows where cert_expiry_date <= today + 90 days
  -> fields: employee_id, cert_type, cert_name, cert_expiry_date, last_reminder_tier, last_reminder_date, status

// Classification logic
days_until_expiry = cert_expiry_date - today
if days_until_expiry <= 0            -> tier = 'OVERDUE'
if days_until_expiry <= 30           -> tier = 'TIER_30'
if days_until_expiry <= 60           -> tier = 'TIER_60'
if days_until_expiry <= 90           -> tier = 'TIER_90'
skip record if last_reminder_tier == tier AND last_reminder_date >= today - 6 days

// Output (per qualifying record)
Gmail.send -> { to: employee_email, template: reminder_[tier].html,
  vars: { full_name, cert_name, cert_expiry_date, typeform_submission_url } }
Slack.postMessage -> { channel: manager_slack_id,
  text: '[employee full_name] cert [cert_name] is [tier] — expires [cert_expiry_date]' }
Google Sheets[CertificationLog].update -> { row: employee_id + cert_type,
  fields: { last_reminder_tier: tier, last_reminder_date: today,
            status: tier == 'OVERDUE' ? 'Escalation Required' : 'Reminder Sent' } }

Monitors incoming Typeform submissions for certificate uploads and processes each submission end-to-end without human involvement. On receipt of a valid submission, the agent validates that all required fields are present and the file upload is non-empty, constructs a standardised file name, files the certificate to the correct named folder in Google Drive, writes the new completion and expiry dates back to BambooHR on the employee's profile, and marks the certification record as complete in the Google Sheets log. If validation fails, the agent logs the error and sends an alert to the HR Coordinator via Slack. This agent replaces four consecutive manual steps that together consumed over 40 minutes per certificate received.

// Input (Typeform webhook payload)
Typeform.form_response -> {
  answers: {
    employee_id:        string,   // hidden field pre-populated from Typeform link
    cert_type:          string,   // dropdown: mapped to BambooHR cert type values
    cert_name:          string,   // free text or dropdown
    completion_date:    date,     // format: YYYY-MM-DD
    new_expiry_date:    date,     // format: YYYY-MM-DD
    certificate_file:   file_url  // Typeform-hosted temporary download URL (expires 1 hour)
  },
  submitted_at: ISO8601 timestamp
}

// Validation checks (abort and alert if any fail)
assert employee_id is not null
assert cert_type is not null
assert completion_date is valid date
assert new_expiry_date > today
assert certificate_file URL returns HTTP 200 and Content-Type in ['application/pdf','image/jpeg','image/png']

// File naming convention
file_name = '{employee_id}_{cert_type}_{new_expiry_date}.{extension}'
// Example: EMP042_FirstAid_2026-05-15.pdf

// Output
Google Drive.upload -> {
  folder_path: '/HR/Certifications/{cert_type}/{employee_id}/',
  file_name: file_name,
  source_url: certificate_file
}
BambooHR.employee[employee_id].customFields.update -> {
  cert_completion_date: completion_date,
  cert_expiry_date:     new_expiry_date,
  cert_status:          'Current'
}
Google Sheets[CertificationLog].update -> {
  row: employee_id + cert_type,
  fields: {
    status:           'Complete',
    completion_date:  completion_date,
    new_expiry_date:  new_expiry_date,
    drive_file_url:   Google Drive file URL,
    last_updated:     submitted_at
  }
}

// On validation failure
Slack.postMessage -> { channel: HR_COORDINATOR_SLACK_ID,
  text: 'Typeform submission failed validation for employee_id [employee_id]: [error reason]. Manual review required.' }

// ============================================================
// TRAINING & CERTIFICATION TRACKING: END-TO-END DATA FLOW
// ============================================================

// TRIGGER: Scheduled daily run (07:00 local time)
// ============================================================
SCHEDULER.fire -> timestamp: today_date

// --- AGENT HANDOFF 1: Certification Monitor Agent begins ---

// STEP 1: Pull active employees from BambooHR
BambooHR.GET /employees?fields=id,firstName,lastName,workEmail,supervisorId
  -> employees[]: { employee_id, full_name, email, manager_id }

// STEP 2: Resolve manager contact details
BambooHR.GET /employees/{manager_id}?fields=workEmail
  -> manager_email: string
Slack.GET users.lookupByEmail?email={manager_email}
  -> manager_slack_id: string

// STEP 3: Read certification records from Google Sheets
Google Sheets[CertificationLog].GET rows
  -> filter: cert_expiry_date <= today + 90 days AND status != 'Complete'
  -> fields per row: {
       employee_id, cert_type, cert_name,
       cert_expiry_date,         // format: YYYY-MM-DD
       last_reminder_tier,       // TIER_90 | TIER_60 | TIER_30 | OVERDUE | null
       last_reminder_date,       // format: YYYY-MM-DD or null
       status                    // Pending | Reminder Sent | Escalation Required | Complete
     }

// STEP 4: Classify each record
FOR each record in filtered_rows:
  days_until_expiry = cert_expiry_date - today
  tier = classify(days_until_expiry)
    // classify: <= 0 -> OVERDUE; <= 30 -> TIER_30; <= 60 -> TIER_60; <= 90 -> TIER_90
  skip IF last_reminder_tier == tier AND last_reminder_date >= today - 6 days

// STEP 5: Send Gmail reminder (non-OVERDUE records only)
Gmail.send -> {
  to:       record.email,
  subject:  '[Action Required] {cert_name} renewal due in {days_until_expiry} days',
  template: reminder_{tier}.html,
  vars: {
    full_name:              record.full_name,
    cert_name:              record.cert_name,
    cert_expiry_date:       record.cert_expiry_date,
    typeform_submission_url: TYPEFORM_BASE_URL + '?employee_id=' + record.employee_id
  }
}

// STEP 6: Notify manager via Slack (all tiers including OVERDUE)
Slack.postMessage -> {
  channel: manager_slack_id,
  text:    '{full_name} | {cert_name} | Status: {tier} | Expiry: {cert_expiry_date}',
  blocks:  [ summary_card with action button: 'View Record' ]
}

// STEP 7: Write log entry to Google Sheets
Google Sheets[CertificationLog].UPDATE row matching employee_id + cert_type -> {
  last_reminder_tier:  tier,
  last_reminder_date:  today,
  status:              tier == 'OVERDUE' ? 'Escalation Required' : 'Reminder Sent'
}

// --- Certification Monitor Agent complete for this cycle ---

// ============================================================
// PARALLEL PATH: Typeform submission received (event-driven)
// ============================================================

// TRIGGER: Typeform webhook POST to automation platform endpoint
Typeform.webhook.POST -> /webhook/cert-intake
  -> payload: {
       form_id:          string,
       submitted_at:     ISO8601,
       answers: {
         employee_id:       string,
         cert_type:         string,
         cert_name:         string,
         completion_date:   YYYY-MM-DD,
         new_expiry_date:   YYYY-MM-DD,
         certificate_file:  url  // Typeform CDN, expires ~1 hour
       }
     }

// --- AGENT HANDOFF 2: Renewal Intake and Filing Agent begins ---

// STEP 8: Validate payload
ASSERT employee_id != null
ASSERT cert_type    != null
ASSERT completion_date is valid ISO date
ASSERT new_expiry_date > today
ASSERT HTTP.GET(certificate_file).status == 200
ASSERT Content-Type IN ['application/pdf','image/jpeg','image/png']
ON FAILURE -> Slack.alert(HR_COORDINATOR_SLACK_ID, reason) -> EXIT

// STEP 9: Download certificate file
file_bytes  = HTTP.GET(certificate_file)  // attempt within 5 min of submission
extension   = derive_extension(Content-Type)
file_name   = '{employee_id}_{cert_type}_{new_expiry_date}.{extension}'
  // Example: EMP042_FirstAid_2026-05-15.pdf

// STEP 10: Upload to Google Drive
Google Drive.ensureFolder('/HR/Certifications/{cert_type}/{employee_id}/')
Google Drive.upload -> {
  folder_path: '/HR/Certifications/{cert_type}/{employee_id}/',
  file_name:   file_name,
  content:     file_bytes
}
  -> drive_file_id:  string
  -> drive_file_url: string

// STEP 11: Write back to BambooHR employee profile
BambooHR.POST /employees/{employee_id}/customTabFields -> {
  customField_cert_completion_date: completion_date,
  customField_cert_expiry_date:     new_expiry_date,
  customField_cert_status:          'Current'
}

// STEP 12: Update Google Sheets log
Google Sheets[CertificationLog].UPDATE row matching employee_id + cert_type -> {
  status:           'Complete',
  completion_date:  completion_date,
  new_expiry_date:  new_expiry_date,
  drive_file_url:   drive_file_url,
  last_updated:     submitted_at
}

// --- Renewal Intake and Filing Agent complete ---

// ============================================================
// HUMAN STEP: Escalation review (OVERDUE records only)
// HR Coordinator reviews Slack alert and contacts employee directly
// No automation action; HR updates status manually in Google Sheets
// ============================================================