Developer Handover Pack

Developer Handover Pack

Timesheet Collection and Approval

Monitors the pay period schedule and, on the close date, reads the master Google Sheet to identify employees who have not submitted. Sends personalised reminders via Gmail and Slack with a direct link to the Google Form submission page. Pulls in form responses, validates that all required fields are present and that hour totals fall within agreed thresholds, and writes clean data into the master sheet. Flags anomalies in a dedicated review column for human inspection before handing off to the Approval Agent.

// Input
pay_period_id: string          // e.g. '2025-W06'
pay_period_close_date: date    // ISO 8601
employee_list: array           // from master sheet: [{employee_id, name, email, slack_handle, manager_id}]
submission_status: array       // from master sheet: [{employee_id, submitted: bool}]
form_responses: array          // Google Forms API: [{employee_id, hours_regular, hours_overtime, project_codes[], submitted_at}]
validation_thresholds: object  // {max_hours_per_day: 12, max_hours_per_week: 60}

// Output
master_sheet_updated: bool     // true when all valid rows written
rows_written: array            // [{employee_id, hours_regular, hours_overtime, project_codes[], status: 'validated'|'flagged'}]
flagged_entries: array         // [{employee_id, flag_reason: string}] — written to Anomalies tab
reminders_sent: array          // [{employee_id, channel: 'gmail'|'slack', sent_at: timestamp}]
collection_complete: bool      // true when all non-flagged rows validated; triggers Approval Agent

Triggered when the Collection Agent sets collection_complete to true in the master sheet. Reads the master sheet to group validated employee rows by line manager, then sends each manager a pre-formatted approval email listing their team's hours for the pay period. Each email contains a unique token-based approve link and a query link. The agent polls for link activations, logs approval timestamps against each employee row in the master sheet, and escalates automatically if a manager has not responded within the configured window. Escalation sends a follow-up via both Gmail and Slack.

// Input
pay_period_id: string          // passed from Collection Agent output
validated_rows: array          // [{employee_id, manager_id, hours_regular, hours_overtime, project_codes[]}]
manager_list: array            // [{manager_id, name, email, slack_handle}]
approval_window_hours: int     // config; e.g. 24 — time before first escalation
escalation_window_hours: int   // config; e.g. 4 — time before second escalation

// On approval (webhook callback from token link)
approval_token: string         // UUID bound to manager_id + pay_period_id
action: enum                   // 'approve' | 'query'
responded_at: timestamp

// Output
approval_log: array            // [{manager_id, employee_ids[], approved_at: timestamp, action: 'approve'|'query'}]
master_sheet_updated: bool     // approval columns populated for each employee row
escalations_sent: array        // [{manager_id, channel: 'gmail'|'slack', sent_at: timestamp}]
all_approvals_complete: bool   // true when every manager_id has actioned 'approve'; triggers Payroll Sync Agent

Triggered when the Approval Agent sets all_approvals_complete to true. Reads the final approved rows from the master Google Sheet, maps each employee's hours to the corresponding Xero pay item using the employee_xero_id lookup, and pushes the data to Xero via the Payroll API. Verifies the Xero response for each employee record and retries on transient errors. Once all records are confirmed accepted by Xero, sends a Slack confirmation message to the payroll owner summarising the total employee count and total hours processed.

// Input
pay_period_id: string          // passed from Approval Agent output
approved_rows: array           // [{employee_id, employee_xero_id, hours_regular, hours_overtime, project_codes[], approved_at}]
xero_pay_period_id: string     // Xero PayrollCalendar period reference; must match pay_period_close_date
xero_pay_item_map: object      // {hours_regular: 'EarningsRateID_001', hours_overtime: 'EarningsRateID_002'}

// Output
xero_sync_results: array       // [{employee_xero_id, status: 'accepted'|'rejected', xero_response_code: int}]
sync_complete: bool            // true when all records accepted by Xero
rejected_records: array        // [{employee_xero_id, reason: string}] — triggers HR alert if non-empty
slack_confirmation_sent: bool
confirmation_message: object   // {channel: '#payroll', text: 'Pay period {pay_period_id} synced. {n} employees, {total_hours} hours loaded into Xero.'}

// ─────────────────────────────────────────────────────────────────
// TRIGGER
// ─────────────────────────────────────────────────────────────────
SCHEDULED_TRIGGER fires on pay_period_close_date
  -> emits: { pay_period_id, pay_period_close_date }

// ─────────────────────────────────────────────────────────────────
// COLLECTION AGENT — steps 1 through 5 replaced
// ─────────────────────────────────────────────────────────────────

// Step 1 replacement: read master sheet for submission status
Google_Sheets.read('Master_Timesheets', range='A:H')
  -> employee_list[{employee_id, name, email, slack_handle, manager_id}]
  -> submission_status[{employee_id, submitted: bool}]

// Step 2 replacement: send personalised reminders to non-submitters
FOR each employee WHERE submitted == false:
  Gmail.send({ to: employee.email, template: 'reminder_v1', prefill_url: Forms.prefill(employee_id, pay_period_id) })
  Slack.post({ channel: employee.slack_handle, text: reminder_text })
  -> reminders_sent[{employee_id, channel, sent_at}]

// Step 3 replacement: collect Google Forms responses
Google_Forms.listResponses(form_id, filter: pay_period_id)
  -> form_responses[{employee_id, hours_regular, hours_overtime, project_codes[], submitted_at}]

// Step 4 replacement: validate and write to master sheet
FOR each form_response:
  IF duplicate(employee_id, pay_period_id): retain latest, log earlier to Audit tab
  IF hours_regular + hours_overtime > validation_thresholds.max_hours_per_week:
    status = 'flagged'; write to Anomalies tab
  ELSE:
    status = 'validated'
  Google_Sheets.write('Master_Timesheets', row: { employee_id, hours_regular, hours_overtime, project_codes[], status })

// Step 5 replacement: anomaly review gate
IF flagged_entries.length > 0:
  WAIT for HR_Administrator to resolve all flagged rows (manual step)
  HR_Administrator updates status = 'validated' in master sheet
WHEN flagged_entries.length == 0:
  Google_Sheets.write('Master_Timesheets', cell: collection_complete = true)
  -> collection_complete: true

// ─────────────────────────────────────────────────────────────────
// HANDOFF: Collection Agent -> Approval Agent
// Condition: collection_complete == true
// Payload: pay_period_id, validated_rows[], manager_list[]
// ─────────────────────────────────────────────────────────────────

// APPROVAL AGENT — steps 6 through 8 replaced
// ─────────────────────────────────────────────────────────────────

// Step 6 replacement: route approval requests
FOR each manager_id in validated_rows (distinct):
  approval_token = UUID.generate(manager_id, pay_period_id)
  Gmail.send({ to: manager.email, template: 'approval_request_v1',
    team_rows: validated_rows.filter(manager_id),
    approve_url: token_endpoint + '?token=' + approval_token + '&action=approve',
    query_url:   token_endpoint + '?token=' + approval_token + '&action=query' })

// Step 7 replacement: escalation timer
START timer(approval_window_hours) per manager_id
  IF no callback received within approval_window_hours:
    Gmail.send(escalation_email_v1 -> manager.email)
    Slack.post(escalation_message -> manager.slack_handle)
  IF no callback within escalation_window_hours after first escalation:
    Slack.post(alert -> '#payroll' + HR_Administrator.slack_handle)

// Token webhook callback
ON POST /webhook/approval?token={approval_token}&action={approve|query}:
  IF duplicate activation: discard, log to Audit tab
  approval_log.push({ manager_id, employee_ids[], approved_at, action })

// Step 8 replacement: write approvals to master sheet
Google_Sheets.write('Master_Timesheets', columns:
  { approved_at, approved_by, approval_token_used, pay_period_id })
  -> per employee row, matched on employee_id
WHEN all manager_ids actioned 'approve':
  Google_Sheets.write('Master_Timesheets', cell: all_approvals_complete = true)
  -> all_approvals_complete: true

// ─────────────────────────────────────────────────────────────────
// HANDOFF: Approval Agent -> Payroll Sync Agent
// Condition: all_approvals_complete == true
// Payload: pay_period_id, approved_rows[], xero_pay_period_id
// ─────────────────────────────────────────────────────────────────

// PAYROLL SYNC AGENT — steps 9 and 10 replaced
// ─────────────────────────────────────────────────────────────────

// Step 9 replacement: push approved hours to Xero
FOR each approved_row:
  xero_payload = {
    EmployeeID: approved_row.employee_xero_id,
    PayPeriod:  xero_pay_period_id,
    EarningsLines: [
      { EarningsRateID: pay_item_map.hours_regular,   NumberOfUnits: approved_row.hours_regular },
      { EarningsRateID: pay_item_map.hours_overtime,  NumberOfUnits: approved_row.hours_overtime }
    ]
  }
  Xero.PayrollAPI.PUT('/payslip/{EmployeeID}', xero_payload)
  ON 429: backoff(2s, max_retries=3)
  ON 4xx/5xx: rejected_records.push({ employee_xero_id, reason: response.message })

// Step 10 replacement: verify and confirm
IF rejected_records.length > 0:
  Google_Sheets.write('Errors_Tab', rejected_records)
  Slack.post({ channel: '#payroll', text: 'Sync partial — {n} rejected records. See Errors tab.' })
ELSE:
  sync_complete = true
  Slack.post({ channel: '#payroll',
    text: 'Pay period {pay_period_id} synced. {approved_rows.length} employees,
           {sum(hours_regular + hours_overtime)} total hours loaded into Xero.' })

// ─────────────────────────────────────────────────────────────────
// END: sync_complete == true, payroll owner notified via Slack
// ─────────────────────────────────────────────────────────────────