Developer Handover Pack

Developer Handover Pack

Budget Planning and Approval

Monitors the Google Forms submission tracker in the master Google Sheet, identifies department heads who have not submitted within 48 hours of the deadline, and dispatches targeted Slack reminders without any manual intervention from finance. The agent polls the submission status column on a scheduled basis and cross-references it against the department list and the deadline date stored in the planning calendar tab of the master sheet. It logs every reminder sent and the current submission status back to the tracking sheet so finance always has a live view of outstanding responses.

// Input
google_sheets.tracker_tab -> {
  department_name: string,
  submission_status: 'pending' | 'submitted' | 'overdue',
  slack_user_id: string,
  deadline_date: ISO8601,
  hours_until_deadline: number
}

// Output
slack.direct_message -> {
  recipient: slack_user_id,
  message_template: 'budget_reminder_48h' | 'budget_reminder_24h',
  cycle_label: string,
  form_link: url
}
google_sheets.tracker_tab -> {
  reminder_sent_at: ISO8601,
  reminder_count: number,
  last_reminder_type: '48h' | '24h'
}

Ingests all validated Google Form responses once the submission deadline passes or all departments have submitted, whichever comes first. The agent maps each line item to the correct cost centre using a lookup table stored in the master sheet config tab, runs rule-based checks for missing required fields and variance thresholds against prior period actuals pulled from Xero via API, and writes the consolidated draft to the master budget tab. Lines that breach the configured variance threshold are flagged in a separate review_required column so the finance manager can inspect exceptions only, rather than checking every row.

// Input
google_forms.response -> {
  department_name: string,
  cost_centre_code: string,
  line_items: [{ category: string, amount_usd: number, notes: string }],
  submitted_at: ISO8601,
  respondent_email: string
}
xero.api -> GET /reports/BalanceSheet | /reports/ProfitAndLoss -> {
  account_code: string,
  prior_period_actual_usd: number,
  period_label: string
}
google_sheets.config_tab -> {
  cost_centre_map: { category_label: string -> cost_centre_code: string },
  variance_threshold_pct: number,
  cycle_label: string
}

// Output
google_sheets.master_budget_tab -> {
  department_name: string,
  cost_centre_code: string,
  category: string,
  submitted_amount_usd: number,
  prior_period_actual_usd: number,
  variance_pct: number,
  review_required: boolean,
  validation_status: 'valid' | 'missing_fields' | 'flagged',
  consolidated_at: ISO8601
}

Fires when the finance manager marks the consolidated draft as cleared for approval by updating a designated cell in the master sheet. The agent generates the final budget summary document, creates a DocuSign envelope with the correct signatories and signing order drawn from the config tab, monitors signing progress, and handles fallback if a signer is unavailable within the configured timeout window. Once all signatures are collected, it saves the completed document to the correct Google Drive folder for the current cycle and posts a Slack completion notice to the finance channel.

// Input
google_sheets.master_budget_tab -> {
  approval_status: 'cleared_for_approval',
  cycle_label: string,
  consolidated_at: ISO8601
}
google_sheets.config_tab -> {
  signing_order: [{ signer_name: string, signer_email: string, order: number }],
  fallback_signer_email: string,
  signing_timeout_hours: number,
  drive_folder_id: string,
  slack_finance_channel: string
}

// On approval trigger
docusign.envelope -> {
  envelope_id: string,
  document_name: string,
  signers: [{ email: string, name: string, routing_order: number }],
  status: 'sent' | 'completed' | 'declined' | 'voided'
}

// Output
google_drive.file -> {
  file_id: string,
  file_name: string,
  folder_id: string,
  archived_at: ISO8601
}
google_sheets.master_budget_tab -> {
  approval_status: 'approved',
  docusign_envelope_id: string,
  signed_at: ISO8601,
  drive_file_id: string
}
slack.channel_message -> {
  channel: slack_finance_channel,
  message_template: 'budget_approved',
  cycle_label: string,
  drive_link: url
}

// ── TRIGGER ──────────────────────────────────────────────────────────────
// Scheduled trigger fires at cycle open date stored in config_tab
google_sheets.config_tab -> {
  cycle_label: 'Q1 2025',
  cycle_open_date: '2025-01-08',
  submission_deadline: '2025-01-20T17:00:00Z',
  department_list: ['Finance', 'Operations', 'Sales', 'Marketing', 'Engineering', 'HR'],
  variance_threshold_pct: 15,
  signing_order: [{name:'Rachel Horton', email:'rh@...', order:1}, {name:'Daniel Chu', email:'dc@...', order:2}],
  drive_folder_id: '1AbcXYZ...',
  slack_finance_channel: '#finance-budget'
}

// ── STEP 1: Form distribution ────────────────────────────────────────────
// Automation sends personalised Google Form link to each department head
google_forms.form_id -> pre_populate({
  department_name: string,         // from department_list
  prior_period_actual_usd: number  // pulled from xero at cycle open
})
-> delivery: email + slack_direct_message to each department head

// ── AGENT HANDOFF 1: Submission Coordinator Agent ────────────────────────
// Polls tracker_tab every 6 hours from cycle open until deadline
google_sheets.tracker_tab -> read {
  department_name: string,
  submission_status: 'pending' | 'submitted',
  slack_user_id: string,
  hours_until_deadline: number   // computed field: deadline - now()
}
// At 48h before deadline: fire 48h reminder for all status='pending'
// At 24h before deadline: fire 24h reminder for all status='pending'
slack.direct_message -> {
  recipient: slack_user_id,
  message_template: 'budget_reminder_48h' | 'budget_reminder_24h',
  form_link: url,
  cycle_label: 'Q1 2025'
}
// Write back to tracker_tab
google_sheets.tracker_tab -> write {
  reminder_sent_at: ISO8601,
  reminder_count: number,
  last_reminder_type: '48h' | '24h'
}

// ── DEPARTMENT SUBMISSION ────────────────────────────────────────────────
// Each department submits via Google Form
google_forms.response -> {
  department_name: string,
  cost_centre_code: string,
  line_items: [{category: string, amount_usd: number, notes: string}],
  submitted_at: ISO8601,
  respondent_email: string
}
// Form response written automatically to responses_tab in master sheet
google_sheets.tracker_tab -> update { submission_status: 'submitted', submitted_at: ISO8601 }

// ── AGENT HANDOFF 2: Budget Consolidation Agent ──────────────────────────
// Fires when all status='submitted' OR deadline timestamp passes
google_forms.responses_tab -> read all rows for cycle_label
// Validate each row
validate({
  required_fields: ['department_name','cost_centre_code','line_items'],
  missing_field_action: 'flag_as missing_fields, set review_required=true'
})
// Pull Xero actuals
xero.api -> GET /reports/ProfitAndLoss?fromDate=...&toDate=... -> {
  account_code: string,
  prior_period_actual_usd: number
}
// Map categories to cost centres
google_sheets.config_tab.cost_centre_map -> resolve {
  category_label -> cost_centre_code
  // unresolved: validation_status='missing_fields', review_required=true
}
// Compute variance
variance_pct = ((submitted_amount_usd - prior_period_actual_usd) / prior_period_actual_usd) * 100
review_required = abs(variance_pct) > variance_threshold_pct
// Write consolidated output
google_sheets.master_budget_tab -> write {
  department_name, cost_centre_code, category,
  submitted_amount_usd, prior_period_actual_usd,
  variance_pct, review_required,
  validation_status: 'valid' | 'missing_fields' | 'flagged',
  consolidated_at: ISO8601
}
// Log absent departments
google_sheets.error_log_tab -> write {
  department_name: string,
  status: 'no_submission',
  logged_at: ISO8601
}

// ── HUMAN STEP: Finance Manager reviews flagged lines ────────────────────
// Finance Manager inspects rows where review_required=true
// Adjusts amounts or notes in master_budget_tab directly
// When satisfied, updates:
google_sheets.master_budget_tab -> write {
  approval_status: 'cleared_for_approval'   // triggers Approval Routing Agent
}

// ── AGENT HANDOFF 3: Approval Routing Agent ──────────────────────────────
// Fires on approval_status change to 'cleared_for_approval'
// Generate PDF summary from master_budget_tab
document.generate -> {
  file_name: 'Budget_Q1 2025_2025-01-28_DRAFT.pdf',
  source: google_sheets.master_budget_tab
}
// Create DocuSign envelope
docusign.api -> POST /envelopes -> {
  envelope_id: string,
  document: { name: 'Budget_Q1 2025_DRAFT.pdf', file_base64: string },
  signers: [
    { email: 'rh@...', name: 'Rachel Horton', routing_order: 1 },
    { email: 'dc@...', name: 'Daniel Chu',   routing_order: 2 }
  ],
  status: 'sent'
}
// DocuSign webhook fires on envelope status change
docusign.webhook -> {
  envelope_id: string,
  status: 'completed' | 'declined' | 'voided'
}
// On 'completed':
docusign.api -> GET /envelopes/{envelope_id}/documents -> signed_pdf_blob
// Archive to Google Drive
google_drive.api -> POST /files -> {
  file_name: 'Budget_Q1 2025_2025-01-30_SIGNED.pdf',
  parent_folder_id: drive_folder_id,
  file_id: string
}
// Update master sheet
google_sheets.master_budget_tab -> write {
  approval_status: 'approved',
  docusign_envelope_id: string,
  signed_at: ISO8601,
  drive_file_id: string
}
// Notify finance channel
slack.channel_message -> {
  channel: '#finance-budget',
  message_template: 'budget_approved',
  cycle_label: 'Q1 2025',
  drive_link: 'https://drive.google.com/file/d/{file_id}'
}

// ── END ──────────────────────────────────────────────────────────────────