Developer Handover Pack

Developer Handover Pack

Staff Scheduling & Rostering

Sends the weekly availability request to all active staff via Gmail at the start of each scheduling period. Monitors for form responses written into Google Sheets, identifies any staff who have not responded within 24 hours, and sends a single automated follow-up reminder via Gmail. Once all responses are logged or the collection window closes, marks the Google Sheet as complete to trigger the Roster Drafting Agent. This agent eliminates the most time-intensive and repeat-labour steps in the current process, replacing manual chasing and scattered reply logging with a structured, timestamped data record.

// Input
trigger: weekly_timer_event { scheduled_at: ISO8601, period_label: 'Week of YYYY-MM-DD' }
staff_list: Google Sheets tab 'Staff' { staff_id, full_name, email, employment_status }

// Processing
FOR each staff WHERE employment_status = 'active':
  Gmail.send({ to: staff.email, template: 'availability_request', period_label })
  log to Sheets tab 'Availability_Log': { staff_id, sent_at, response_status: 'pending' }
WAIT 24 hours
FOR each row WHERE response_status = 'pending':
  Gmail.send({ to: staff.email, template: 'availability_reminder', period_label })
  update Sheets: { reminder_sent_at: now() }
WAIT until collection_window_closes OR all response_status = 'received'
Sheets.setCell('Control', 'availability_complete', TRUE)

// Output
Google Sheets tab 'Availability_Log' fully populated:
  { staff_id, full_name, available_days[], preferred_hours, notes, submitted_at, reminder_sent }
Control cell 'availability_complete' = TRUE (triggers Roster Drafting Agent)

Triggered when the Availability Collection Agent marks the Google Sheet as complete. Reads the populated availability log and the week's shift demand requirements from a separate 'Demand' tab in Google Sheets. Applies the configured award rules (maximum daily hours, minimum rest between shifts, maximum consecutive days, overtime thresholds) to allocate staff to shifts and produce a draft roster. Any shift slot that cannot be filled, or any allocation that would breach a rule, is flagged in a conflict summary. The conflict summary is posted to the manager's Slack channel. The draft roster is written back to the 'Draft_Roster' tab in Google Sheets and a row is added to the 'Approval_Control' tab with status set to 'pending_review'. If no conflicts exist, an approval prompt is also sent to Slack.

// Input
Google Sheets 'Availability_Log': { staff_id, available_days[], preferred_hours, no_response_flag }
Google Sheets 'Demand': { shift_date, shift_label, start_time, end_time, min_staff, max_staff, role_required }
Google Sheets 'Award_Rules': { rule_id, rule_type, threshold_value, breach_action }
Deputy API GET /api/v1/operationalunit: { location_id, shift_templates[] }
Deputy API GET /api/v1/employee: { employee_id, deputy_staff_id, role, contracted_hours }

// Processing
FOR each shift in Demand WHERE shift_date in period_label:
  candidates = Availability_Log WHERE shift_date in available_days[]
  ranked_candidates = sort by (contracted_hours_remaining DESC, last_rostered_date ASC)
  FOR each candidate in ranked_candidates:
    IF award_rules_pass(candidate, shift): assign candidate to shift; break
  IF no_candidate_assigned: add to conflicts { shift_date, shift_label, reason: 'no_available_staff' }
  IF rule_breach detected: add to conflicts { staff_id, rule_id, breach_type, suggested_action }
Write Sheets 'Draft_Roster': { staff_id, shift_date, shift_label, start_time, end_time, role, status: 'draft' }
Write Sheets 'Conflict_Log': { conflict_id, shift_date, shift_label, staff_id, breach_type, suggested_action }
Write Sheets 'Approval_Control': { period_label, draft_created_at, conflict_count, approval_status: 'pending_review' }

// Output
Slack POST to #roster-approvals: conflict summary message with conflict_count, breach_type list, and approval link
Slack POST: approval prompt if conflict_count = 0
Google Sheets 'Draft_Roster' fully populated for manager review
Google Sheets 'Approval_Control'.approval_status = 'pending_review'

Triggered when the manager marks the draft roster as approved by updating the 'Approval_Control' tab in Google Sheets (or, if Deputy's approval workflow is used, by a Deputy webhook event). Reads the finalised Draft_Roster and creates each shift record in Deputy via the API. Once Deputy confirms successful shift creation, sends a personalised shift notification to each staff member via both Slack and Gmail. At pay-period close (a separate recurring timer), reads confirmed shift hours from Deputy and posts timesheet entries to Xero ready for payroll processing. This agent completes the end-to-end loop and eliminates all remaining manual publication and payroll data-entry steps.

// Input (on approval trigger)
Google Sheets 'Approval_Control': { approval_status: 'approved', approved_by, approved_at }
Google Sheets 'Draft_Roster': { staff_id, deputy_staff_id, shift_date, start_time, end_time, role, shift_label }
Staff contact data from Sheets 'Staff': { staff_id, full_name, email, slack_user_id }

// On approval: Deputy shift creation
FOR each row in Draft_Roster WHERE status = 'draft':
  Deputy API POST /api/v1/roster:
    { employee: deputy_staff_id, startTime: shift_date+start_time ISO8601,
      endTime: shift_date+end_time ISO8601, operationalUnit: location_id,
      published: true, comment: shift_label }
  IF Deputy response = 200: update Sheets Draft_Roster.status = 'published'
  IF Deputy response != 200: log to 'Error_Log', alert Slack #roster-approvals

// On approval: staff notifications
FOR each staff_id in published shifts:
  Slack POST to staff.slack_user_id (DM):
    personalised message listing each shift_date, start_time, end_time, shift_label
  Gmail.send({ to: staff.email, template: 'shift_confirmation',
    shifts: [ { shift_date, start_time, end_time, shift_label } ] })

// Output (on approval)
Deputy: all shifts created and published for the period
Slack DMs: personalised shift confirmations sent to each staff member
Gmail: shift confirmation emails sent to each staff member
Sheets 'Draft_Roster'.status = 'published' for all rows

// Input (on payroll timer trigger)
Deputy API GET /api/v1/roster?startTime=period_start&endTime=period_end&published=true
Response: { employee_id, startTime, endTime, totalHours, approved }

// Output (payroll export)
FOR each approved shift in Deputy roster response:
  Xero API POST /api.xro/2.0/Timesheets:
    { EmployeeID: xero_employee_id, StartDateUTC: period_start,
      EndDateUTC: period_end, TimesheetLines: [
        { EarningsRateID: standard_rate_id, NumberOfUnits: totalHours,
          TrackingItemName: shift_label } ] }
Xero timesheet entries created and ready for payroll run

// ============================================================
// STAFF SCHEDULING & ROSTERING: END-TO-END DATA FLOW
// ============================================================

// TRIGGER: Orchestration platform weekly timer
trigger_event = {
  scheduled_at: '2024-06-17T07:00:00+10:00',  // ISO8601 local time
  period_label: 'Week of 2024-06-17'
}

// ============================================================
// AGENT 1: Availability Collection Agent
// ============================================================

// Read staff list
Sheets.read('Staff') ->
  [{ staff_id, full_name, email, slack_user_id, employment_status }]
  FILTER: employment_status = 'active'

// Send availability request emails
FOR each active_staff:
  Gmail.send({
    to: staff.email,
    subject: 'Your availability for Week of 2024-06-17',
    body_template: 'availability_request',
    form_link: 'https://forms.google.com/[availability_form_id]',
    period_label: 'Week of 2024-06-17'
  })
  Sheets.append('Availability_Log', {
    staff_id, full_name, period_label,
    sent_at: now(), response_status: 'pending',
    reminder_sent: FALSE, reminder_sent_at: NULL
  })

// Wait 24 hours, then send follow-up reminder to non-responders
WAIT 24h
non_responders = Sheets.read('Availability_Log')
  FILTER: response_status = 'pending'
FOR each non_responder:
  Gmail.send({ to: staff.email, template: 'availability_reminder', period_label })
  Sheets.update('Availability_Log', staff_id, {
    reminder_sent: TRUE, reminder_sent_at: now()
  })

// Google Form responses write to Availability_Log (Sheets native integration)
// Each submission row:
// { staff_id, full_name, available_days[], preferred_hours,
//   notes, submitted_at, response_status: 'received' }

// De-duplication: if duplicate staff_id exists, keep max(submitted_at)
// Non-responders at window close: response_status = 'no_response'

// Collection window closes (T+48h from initial send)
Sheets.setCell('Control', 'availability_complete', TRUE)
// -> Triggers Agent 2

// ============================================================
// AGENT 1 -> AGENT 2 HANDOFF
// Handoff field: Sheets 'Control'.availability_complete = TRUE
// Handoff data: Sheets 'Availability_Log' fully populated
// ============================================================

// ============================================================
// AGENT 2: Roster Drafting Agent
// ============================================================

// Read inputs
availability = Sheets.read('Availability_Log') ->
  [{ staff_id, available_days[], preferred_hours, response_status }]

demand = Sheets.read('Demand') ->
  [{ shift_date, shift_label, start_time, end_time, min_staff, max_staff, role_required }]
  FILTER: shift_date in period_label range

award_rules = Sheets.read('Award_Rules') ->
  [{ rule_id, rule_type, threshold_value, breach_action }]
  // Examples: max_daily_hours=10, min_rest_between_shifts=10h,
  //           max_consecutive_days=5, overtime_threshold_weekly=38h

deputy_staff = Deputy.GET('/api/v1/employee') ->
  [{ employee_id: deputy_staff_id, display_name, role, contracted_hours }]

// Drafting logic
FOR each shift in demand:
  candidates = availability
    FILTER: shift.shift_date in staff.available_days[]
    AND staff.response_status != 'no_response'
    SORT: contracted_hours_remaining DESC, last_rostered_date ASC
  FOR each candidate in candidates:
    pass = award_rules_check(candidate, shift, award_rules)
    IF pass: assign { staff_id, shift_date, shift_label, start_time, end_time, role }; break
  IF no assignment:
    Conflict_Log.append({
      conflict_id: UUID, shift_date, shift_label,
      staff_id: NULL, breach_type: 'no_available_staff',
      suggested_action: 'Find casual cover'
    })
  IF rule_breach:
    Conflict_Log.append({
      conflict_id: UUID, shift_date, shift_label, staff_id,
      breach_type: rule.rule_type, suggested_action: rule.breach_action
    })

// Write draft roster
Sheets.write('Draft_Roster',
  [{ staff_id, deputy_staff_id, shift_date, shift_label,
     start_time, end_time, role, status: 'draft' }]
)
Sheets.write('Conflict_Log',
  [{ conflict_id, shift_date, shift_label, staff_id,
     breach_type, suggested_action }]
)
Sheets.write('Approval_Control', {
  period_label, draft_created_at: now(),
  conflict_count: Conflict_Log.count(),
  approval_status: 'pending_review'
})

// Post to Slack
Slack.POST('#roster-approvals', {
  text: 'Draft roster ready for Week of 2024-06-17.',
  conflict_count: N,
  conflict_summary: Conflict_Log[],
  draft_link: 'https://docs.google.com/spreadsheets/[roster_sheet_id]'
})

// ============================================================
// AGENT 2 -> AGENT 3 HANDOFF (via manager approval action)
// Handoff field: Sheets 'Approval_Control'.approval_status = 'approved'
// Set by: Manager updates cell manually OR via Deputy approval webhook
// ============================================================

// ============================================================
// AGENT 3: Roster Publication and Payroll Agent
// ============================================================

// On approval trigger
approved_roster = Sheets.read('Draft_Roster')
  FILTER: status = 'draft'   // all rows approved in batch

// Create shifts in Deputy
FOR each row in approved_roster:
  Deputy.POST('/api/v1/roster', {
    employee: row.deputy_staff_id,
    startTime: row.shift_date + 'T' + row.start_time + '+10:00',
    endTime: row.shift_date + 'T' + row.end_time + '+10:00',
    operationalUnit: location_id,
    published: true,
    comment: row.shift_label
  })
  IF response.status = 200:
    Sheets.update('Draft_Roster', row.staff_id, { status: 'published' })
  ELSE:
    Sheets.append('Error_Log', {
      error_id: UUID, agent: 'Publication', staff_id: row.staff_id,
      shift_date: row.shift_date, error_code: response.status,
      error_message: response.body, logged_at: now()
    })
    Slack.POST('#roster-approvals', { alert: 'Shift creation failed', details: row })

// Send staff notifications
staff_shifts = GROUP approved_roster BY staff_id
FOR each staff_member in staff_shifts:
  Slack.POST_DM(staff.slack_user_id, {
    text: 'Your shifts for Week of 2024-06-17:',
    shifts: [{ shift_date, shift_label, start_time, end_time }]
  })
  Gmail.send({
    to: staff.email,
    subject: 'Your shifts for Week of 2024-06-17',
    template: 'shift_confirmation',
    shifts: [{ shift_date, shift_label, start_time, end_time }]
  })

// ============================================================
// PAYROLL EXPORT (separate pay-period close timer)
// ============================================================

// Read confirmed hours from Deputy
deputy_timesheets = Deputy.GET('/api/v1/roster', {
  startTime: period_start_ISO8601,
  endTime: period_end_ISO8601,
  published: true
}) -> [{ employee_id, startTime, endTime, totalHours, approved }]
  FILTER: approved = true

// Map Deputy employee_id to Xero EmployeeID
mapping = Sheets.read('ID_Mapping') ->
  [{ staff_id, deputy_staff_id, xero_employee_id }]

// Post timesheets to Xero
FOR each timesheet_entry in deputy_timesheets:
  xero_id = mapping LOOKUP deputy_staff_id -> xero_employee_id
  earnings_rate_id = LOOKUP earnings_rates BY shift_label
  Xero.POST('/api.xro/2.0/Timesheets', {
    EmployeeID: xero_id,
    StartDateUTC: period_start_ISO8601,
    EndDateUTC: period_end_ISO8601,
    TimesheetLines: [{
      EarningsRateID: earnings_rate_id,
      NumberOfUnits: totalHours,
      TrackingItemName: shift_label
    }]
  })

// END OF FLOW