Developer Handover Pack

Developer Handover Pack

Weekly Business Review Automation

Connects to Xero and HubSpot on a Sunday 8 PM weekly schedule, retrieves last week's financial and pipeline figures using authenticated API calls, and appends a new consolidated row to the master Google Sheet. This agent has no user-facing output; its only responsibility is reliable, structured data delivery to the sheet so downstream agents have a single source of truth. It must handle partial API failures gracefully and not write an incomplete row.

// Input
schedule_trigger: { fired_at: ISO8601, week_label: 'YYYY-WW' }

// Xero fetch
xero.invoices.get({ date_from: week_start, date_to: week_end })
  -> { invoiced_revenue: float, payments_received: float, outstanding_ar: float }

// HubSpot fetch
hubspot.deals.search({ closedate_gte: week_start, closedate_lte: week_end })
  -> { open_deal_count: int, pipeline_value: float, deals_closed: int, stage_movements: int }

// Output
google_sheets.append_row(sheet_id, 'WeeklyData', {
  week_label: string,           // e.g. '2025-W16'
  week_start_date: date,
  week_end_date: date,
  invoiced_revenue: float,
  payments_received: float,
  outstanding_ar: float,
  open_deal_count: int,
  pipeline_value: float,
  deals_closed: int,
  stage_movements: int,
  data_status: 'complete' | 'partial_failure'
})

// On failure
IF xero_call fails: set invoiced_revenue = null, log error, set data_status = 'partial_failure'
IF hubspot_call fails: set pipeline fields = null, log error, set data_status = 'partial_failure'
IF data_status == 'partial_failure': halt pipeline, post error alert to Slack ops channel

Reads the most recently appended row in the master Google Sheet, compares each metric against the target thresholds stored in a separate reference tab named 'Targets', and uses an AI language model call to produce a concise plain-English commentary. The commentary names any off-target metrics, states the direction and magnitude of the variance, and suggests one sentence of context or attention per flagged metric. It also outputs a boolean flag used by the conditional branch that determines whether a human review step is inserted before distribution.

// Input
google_sheets.get_row(sheet_id, 'WeeklyData', row = latest)
  -> current_week_data: { week_label, invoiced_revenue, payments_received,
       outstanding_ar, open_deal_count, pipeline_value, deals_closed, stage_movements }

google_sheets.get_range(sheet_id, 'Targets', 'A2:C20')
  -> targets: [{ metric_name: string, target_value: float, direction: 'min'|'max' }]

// Threshold comparison (internal logic)
FOR EACH metric IN current_week_data:
  variance_pct = (actual - target) / target * 100
  off_target = (direction == 'min' && actual < target) ||
               (direction == 'max' && actual > target)
  critical = abs(variance_pct) >= 10   // threshold configurable in Targets tab

// AI prompt payload
llm.chat({ model: 'gpt-4o', messages: [
  { role: 'system', content: 'You are a business analyst writing a brief weekly review.' },
  { role: 'user', content: build_prompt(current_week_data, flagged_metrics) }
]})

// Output
{
  narrative_text: string,          // 150-250 words plain English
  flagged_metrics: [{ metric_name, actual, target, variance_pct, critical: bool }],
  has_critical_flags: bool,
  week_label: string
}

// On approval (when has_critical_flags == true)
Insert human review gate: notify process owner via email with deck preview link.
Pipeline pauses until owner approves or overrides within 2 hours.
IF no response after 2 hours: escalate to Slack DM and wait 30 min, then auto-proceed.

Takes the completed narrative and metric data from the Review Narrative Agent, pushes updated figures and commentary into named placeholders in the standing Google Slides deck, sends the deck link via Gmail to the leadership distribution list, posts a formatted summary to the Slack leadership channel, and archives the final deck to the designated Google Drive folder. This agent is the terminal step in the pipeline and must confirm successful send and post before marking the run complete.

// Input
{
  narrative_text: string,
  flagged_metrics: [{ metric_name, actual, target, variance_pct, critical }],
  has_critical_flags: bool,
  week_label: string,
  current_week_data: { invoiced_revenue, payments_received, outstanding_ar,
    open_deal_count, pipeline_value, deals_closed, stage_movements }
}

// Google Slides update
slides.batchUpdate(presentation_id, [
  replaceAllText({ '{{WEEK_LABEL}}': week_label }),
  replaceAllText({ '{{INVOICED_REVENUE}}': format_currency(invoiced_revenue) }),
  replaceAllText({ '{{PAYMENTS_RECEIVED}}': format_currency(payments_received) }),
  replaceAllText({ '{{OUTSTANDING_AR}}': format_currency(outstanding_ar) }),
  replaceAllText({ '{{OPEN_DEALS}}': open_deal_count }),
  replaceAllText({ '{{PIPELINE_VALUE}}': format_currency(pipeline_value) }),
  replaceAllText({ '{{DEALS_CLOSED}}': deals_closed }),
  replaceAllText({ '{{NARRATIVE}}': narrative_text })
])

// Gmail send
gmail.send({
  to: env.LEADERSHIP_EMAIL_LIST,       // comma-separated, stored as env var
  subject: 'Weekly Business Review - ' + week_label,
  body: render_email_template(narrative_text, flagged_metrics),
  attachments: []                       // link only, no attachment
})

// Slack post
slack.chat.postMessage({
  channel: env.LEADERSHIP_SLACK_CHANNEL_ID,
  text: 'Weekly pack ready for ' + week_label,
  blocks: build_slack_blocks(flagged_metrics, slides_url)
})

// Archive to Drive
drive.copy(presentation_id,
  { name: 'WeeklyReview_' + week_label, parents: [env.ARCHIVE_FOLDER_ID] }
)

// Output
{
  gmail_message_id: string,
  slack_ts: string,
  archived_file_id: string,
  run_status: 'success' | 'partial_failure',
  completed_at: ISO8601
}

// ============================================================
// WEEKLY BUSINESS REVIEW: END-TO-END DATA FLOW
// Trigger to final output, all field names and agent handoffs
// ============================================================

// TRIGGER
SCHEDULE cron('0 20 * * 0', tz=env.BUSINESS_TIMEZONE)
  -> { fired_at: ISO8601, week_label: 'YYYY-WW',
       week_start: date, week_end: date }

// ============================================================
// AGENT 1: Data Collection Agent
// ============================================================

// Step 1: Xero API call
GET https://api.xero.com/api.xro/2.0/Reports/ProfitAndLoss
  ?fromDate={week_start}&toDate={week_end}
  Authorization: Bearer {xero_access_token}
  -> raw_xero: { invoiced_revenue: float,
                 payments_received: float,
                 outstanding_ar: float }

// Step 2: HubSpot API call
POST https://api.hubapi.com/crm/v3/objects/deals/search
  Authorization: Bearer {hubspot_private_app_token}
  body: { filterGroups: [{ filters: [
    { propertyName: 'closedate', operator: 'BETWEEN',
      value: week_start, highValue: week_end }]}]}
  -> raw_hubspot: { open_deal_count: int,
                    pipeline_value: float,
                    deals_closed: int,
                    stage_movements: int }

// Step 3: Write to Google Sheets
POST https://sheets.googleapis.com/v4/spreadsheets/{SHEET_ID}/values
  /WeeklyData!A:J:append?valueInputOption=USER_ENTERED
  body: { values: [[
    week_label, week_start, week_end,
    invoiced_revenue, payments_received, outstanding_ar,
    open_deal_count, pipeline_value, deals_closed,
    stage_movements, 'complete'
  ]] }
  -> { updatedRange: string, updatedRows: 1 }

// HANDOFF: Data Collection Agent -> Review Narrative Agent
// Condition: data_status == 'complete' in appended row
// Payload passed: { week_label, sheet_id, latest_row_index }

// ============================================================
// AGENT 2: Review Narrative Agent
// ============================================================

// Step 4: Read latest data row
GET https://sheets.googleapis.com/v4/spreadsheets/{SHEET_ID}/values
  /WeeklyData!A{latest_row}:K{latest_row}
  -> current_week_data: { week_label, invoiced_revenue,
       payments_received, outstanding_ar, open_deal_count,
       pipeline_value, deals_closed, stage_movements }

// Step 5: Read targets reference tab
GET https://sheets.googleapis.com/v4/spreadsheets/{SHEET_ID}/values
  /Targets!A2:C20
  -> targets: [{ metric_name, target_value, direction }]

// Step 6: Threshold comparison (internal)
FOR metric IN current_week_data:
  variance_pct = (actual - target) / target * 100
  critical = abs(variance_pct) >= env.CRITICAL_THRESHOLD_PCT  // default 10
  -> flagged_metrics: [{ metric_name, actual, target,
                         variance_pct, critical: bool }]
  -> has_critical_flags: bool

// Step 7: AI language model call
POST https://api.openai.com/v1/chat/completions
  Authorization: Bearer {llm_api_key}
  body: { model: 'gpt-4o', max_tokens: 400,
    messages: [system_prompt, user_prompt(current_week_data, flagged_metrics)] }
  -> narrative_text: string  // 150-250 words

// Step 8: Conditional gate
IF has_critical_flags == true:
  NOTIFY process_owner via gmail (preview link + flagged list)
  PAUSE pipeline max 2 hours
  IF no_response: DM via Slack, pause 30 min, then auto-proceed
ELSE:
  Continue immediately

// HANDOFF: Review Narrative Agent -> Distribution Agent
// Payload: { narrative_text, flagged_metrics, has_critical_flags,
//            week_label, current_week_data }

// ============================================================
// AGENT 3: Distribution Agent
// ============================================================

// Step 9: Update Google Slides placeholders
POST https://slides.googleapis.com/v1/presentations/{PRESENTATION_ID}:batchUpdate
  body: { requests: [replaceAllText per placeholder] }
  -> { presentationId, replies }
  -> slides_url: 'https://docs.google.com/presentation/d/{PRESENTATION_ID}'

// Step 10: Send Gmail
POST https://gmail.googleapis.com/gmail/v1/users/me/messages/send
  body: { to: env.LEADERSHIP_EMAIL_LIST,
    subject: 'Weekly Business Review - ' + week_label,
    body_html: render_email_template(narrative_text, flagged_metrics, slides_url) }
  -> { gmail_message_id: string }

// Step 11: Post Slack message
POST https://slack.com/api/chat.postMessage
  Authorization: Bearer {slack_bot_token}
  body: { channel: env.LEADERSHIP_SLACK_CHANNEL_ID,
    blocks: build_slack_blocks(week_label, flagged_metrics, slides_url) }
  -> { ts: string, channel: string }

// Step 12: Archive to Google Drive
POST https://www.googleapis.com/drive/v3/files/{PRESENTATION_ID}/copy
  body: { name: 'WeeklyReview_' + week_label,
          parents: [env.ARCHIVE_FOLDER_ID] }
  -> { archived_file_id: string }

// FINAL OUTPUT
run_log.write({
  week_label, fired_at, completed_at: ISO8601,
  gmail_message_id, slack_ts, archived_file_id,
  has_critical_flags, flagged_metrics_count: int,
  run_status: 'success' | 'partial_failure'
})