Developer Handover Pack

Developer Handover Pack

Cash Flow Forecasting Automation

Connects to Xero and Stripe on a fixed Monday morning schedule and pulls all financial data required to populate the forecast. The agent authenticates via OAuth 2.0 (Xero) and a restricted API key (Stripe), handles pagination across multiple bank accounts, and retries failed API calls up to three times before triggering a failure alert. The bookkeeper does not need to log in to either system. All retrieved data is timestamped at the moment of retrieval and assembled into a single structured payload passed to the Categorisation Agent. Complexity: Moderate.

// Input
trigger: { type: 'schedule', day: 'Monday', time: '06:00', timezone: 'business_tz' }

// Xero API calls (paginated, per bank account)
xero.bankTransactions.list({ dateFrom: now-7d, dateTo: now, status: 'AUTHORISED' })
xero.reports.getAgedReceivables({ reportDate: now, agingPeriod: 30 })
xero.bills.list({ dueDateTo: now+56d, status: ['AUTHORISED','DRAFT'] })

// Stripe API call
stripe.payouts.list({ arrival_date.lte: now+56d, status: 'pending' })

// Output
payload: {
  run_id: string (uuid),
  run_timestamp: ISO8601,
  bank_transactions: [ { id, date, amount, description, account_id, xero_contact_name } ],
  ar_ageing: { current: number, days_30: number, days_60: number, days_90_plus: number },
  upcoming_bills: [ { id, due_date, amount, contact_name, status } ],
  stripe_payouts: [ { id, amount, currency, arrival_date, status } ]
}

Receives the structured data payload from the Data Collection Agent and classifies each bank transaction against the business's chart-of-accounts coding rules. Rules are stored as a configuration table (category_rules) mapping keyword patterns and Xero account codes to forecast line labels. Transactions matching a rule with confidence above the threshold are assigned automatically. Transactions below the threshold, or with no rule match, are written to a flagged_items list rather than guessed. The fully categorised dataset and flagged list are then written to the master Google Sheet. Complexity: Moderate.

// Input
payload: { run_id, run_timestamp, bank_transactions[], ar_ageing{}, upcoming_bills[], stripe_payouts[] }
config: { category_rules[], confidence_threshold: 0.80, sheet_id: string, data_tab: string, flags_tab: string }

// Processing
for each transaction in bank_transactions:
  match against category_rules by xero_account_code, then description keyword
  if match_confidence >= 0.80: assign forecast_line_label, set flagged = false
  else: set forecast_line_label = 'UNMATCHED', set flagged = true, append to flagged_items[]

// Output (written to Google Sheets via Sheets API v4)
data_tab write: {
  run_id, run_timestamp,
  weekly_columns: { week_start_date: { inflows: number, outflows: number, net: number } },
  ar_ageing: { current, days_30, days_60, days_90_plus },
  upcoming_bills: [ { due_date, amount, contact_name } ],
  stripe_payouts: [ { arrival_date, amount } ]
}
flags_tab write: {
  flagged_items: [ { transaction_id, date, amount, description, reason: string } ]
}
approval_cell: cleared to '' (empty) to reset any prior approval state

Monitors the designated approval cell in the Google Sheet for the finance lead's approval signal. When the cell value is set to 'Approved', the agent triggers downstream delivery: it exports the summary tab as a PDF via the Google Sheets export endpoint, emails the PDF to the stakeholder distribution list via Gmail, and posts a formatted cash summary message to the Slack finance channel. The agent does not fire until the approval signal is received; if no approval is set within 48 hours of the data write, a reminder is posted to Slack. Complexity: Simple.

// Input
trigger: approval_cell value == 'Approved'
config: {
  sheet_id: string,
  summary_tab: string,
  export_format: 'application/pdf',
  gmail_from: string (authenticated sender address),
  gmail_to: [ string ] (stakeholder distribution list),
  gmail_subject_template: 'Cash Flow Forecast - Week of {{week_start_date}}',
  gmail_body_template: string (references run_timestamp, flagged_item_count),
  slack_channel: string (finance channel ID),
  slack_message_template: string (references opening_balance, projected_close_4w, flagged_item_count),
  no_approval_reminder_hours: 48
}

// Processing
step_1: export summary_tab to PDF via Sheets export URL with access token
step_2: attach PDF to Gmail draft; populate subject and body from templates; send
step_3: compose Slack message from template fields; post to slack_channel
step_4: write archive entry to Google Sheets archive_tab: { run_id, approved_at, pdf_url, sent_to[] }

// Output
gmail_send: { message_id, sent_at, recipients[], attachment_filename }
slack_post: { ts, channel, message_preview }
archive_row: { run_id, week_start_date, approved_at, sent_at, pdf_filename }

// On approval
approval_cell is not cleared after send; archive_tab entry acts as the audit record.
If no approval detected within 48 hours: post Slack reminder to finance channel with run_id and sheet link.

// ─────────────────────────────────────────────────────────────────────
// TRIGGER
// ─────────────────────────────────────────────────────────────────────
orchestration_scheduler fires at 06:00 Monday
  -> event: { type: 'schedule', day: 'Monday', time: '06:00', timezone: BUSINESS_TZ }
  -> initialise run_id (uuid), run_timestamp (ISO8601)

// ─────────────────────────────────────────────────────────────────────
// AGENT 1: DATA COLLECTION AGENT
// ─────────────────────────────────────────────────────────────────────

// Xero OAuth 2.0 (tenant_id from credential store)
GET /api.xero.com/api.xro/2.0/BankTransactions
  params: { DateFrom: now-7d, DateTo: now, Status: 'AUTHORISED' }
  -> bank_transactions[]: { BankTransactionID, Date, Total, Description,
                            BankAccount.AccountID, Contact.Name }
  [paginated: page=1..N, max 100/page; halt + SLACK_ALERT if pages > 50]

GET /api.xero.com/api.xro/2.0/Reports/AgedReceivablesByContact
  params: { reportDate: now, fromDate: now-90d }
  -> ar_ageing: { current, days_30, days_60, days_90_plus } (all: number, base currency)

GET /api.xero.com/api.xro/2.0/Invoices
  params: { Type: 'ACCPAY', DueDateTo: now+56d, Status: ['AUTHORISED','DRAFT'] }
  -> upcoming_bills[]: { InvoiceID, DueDate, AmountDue, Contact.Name, Status }

// Stripe restricted API key (payouts:read)
GET /v1/payouts
  params: { 'arrival_date[lte]': now+56d, status: 'pending', limit: 100 }
  -> stripe_payouts[]: { id, amount, currency, arrival_date, status }

// Retry logic: up to 3 attempts per endpoint, 5s backoff
// On failure after 3 retries: halt run, POST Slack alert { run_id, failed_endpoint }

// ── HANDOFF: Data Collection Agent -> Categorisation Agent ──────────
payload_v1: {
  run_id: string,
  run_timestamp: ISO8601,
  bank_transactions: [ { id, date, amount, description, account_id, contact_name } ],
  ar_ageing: { current, days_30, days_60, days_90_plus },
  upcoming_bills: [ { id, due_date, amount, contact_name, status } ],
  stripe_payouts: [ { id, amount, currency, arrival_date, status } ]
}

// ─────────────────────────────────────────────────────────────────────
// AGENT 2: CATEGORISATION AGENT
// ─────────────────────────────────────────────────────────────────────

load config: { category_rules[], confidence_threshold: 0.80,
               sheet_id, data_tab, flags_tab, approval_tab, APPROVAL_CELL_REF }

for each tx in bank_transactions[]:
  attempt match: category_rules[].xero_account_code == tx.account_id
               OR category_rules[].description_keywords intersect tx.description
  if match_confidence >= 0.80:
    tx.forecast_line_label = matched_rule.forecast_line_label
    tx.flagged = false
  else:
    tx.forecast_line_label = 'UNMATCHED'
    tx.flagged = true
    append to flagged_items[]: { transaction_id, date, amount, description, reason }

deduplicate by transaction_id (overwrite on replay)

// Google Sheets API v4 batchUpdate
WRITE data_tab:
  range: DATA_WRITE_RANGE (config variable)
  values: [ run_id, run_timestamp, weekly columns by week_start_date,
            ar_ageing fields, upcoming_bills rows, stripe_payouts rows ]

WRITE flags_tab:
  range: FLAGS_WRITE_RANGE (config variable)
  values: flagged_items[] rows

CLEAR approval_cell: APPROVAL_CELL_REF -> '' (reset state)

// On Sheets write failure: retry x2, then POST Slack alert { run_id, error_message }

// ── HANDOFF: Categorisation Agent -> Distribution Agent ─────────────
// Distribution Agent begins polling APPROVAL_CELL_REF every 5 minutes
// Human step: Finance Lead reviews data_tab and flags_tab,
//             applies manual adjustments (steps 7-8, retained),
//             sets APPROVAL_CELL_REF = 'Approved'

// ─────────────────────────────────────────────────────────────────────
// AGENT 3: DISTRIBUTION AGENT
// ─────────────────────────────────────────────────────────────────────

poll trigger: APPROVAL_CELL_REF value == 'Approved' (case-insensitive)

// Step 1: PDF export
GET https://docs.google.com/spreadsheets/d/{sheet_id}/export
  params: { format: 'pdf', gid: SUMMARY_TAB_GID }
  -> pdf_blob (binary), pdf_filename: 'CashForecast_{{week_start_date}}.pdf'

// Step 2: Gmail send (OAuth 2.0, scope: gmail.send)
POST /gmail/v1/users/me/messages/send
  to: DISTRIBUTION_LIST[]
  from: GMAIL_SENDER_ADDRESS
  subject: 'Cash Flow Forecast - Week of {{week_start_date}}'
  body: gmail_body_template { run_timestamp, flagged_item_count }
  attachment: pdf_blob
  -> { message_id, sent_at }

// Step 3: Slack post (bot token, scope: chat:write)
POST /api.slack.com/api/chat.postMessage
  channel: SLACK_FINANCE_CHANNEL_ID
  text: slack_message_template {
    opening_balance: bank_transactions[0].balance,
    projected_close_4w: weekly_columns[week+4].closing_balance,
    flagged_item_count: flagged_items.length
  }
  -> { ts, channel }

// Step 4: Archive write
APPEND archive_tab row:
  { run_id, week_start_date, approved_at, sent_at, pdf_filename, recipients_count }

// 48-hour no-approval guard (parallel timer from Sheets write completion):
if APPROVAL_CELL_REF != 'Approved' after 48h:
  POST Slack reminder: { run_id, sheet_link, message: 'Forecast awaiting approval' }

// ─────────────────────────────────────────────────────────────────────
// END OF RUN
// ─────────────────────────────────────────────────────────────────────