Integration and API Spec

Integration and API Spec

Financial Reporting Automation

Accounting data source. The Data Fetch Agent connects to Xero via OAuth 2.0 to retrieve P&L, Balance Sheet, and Cash Flow reports for the configured reporting period. All three report types are fetched in a single authenticated session per run.

// Endpoint examples
GET https://api.xero.com/api.xro/2.0/Reports/ProfitAndLoss?fromDate={period_start}&toDate={period_end}
GET https://api.xero.com/api.xro/2.0/Reports/BalanceSheet?date={period_end}
GET https://api.xero.com/api.xro/2.0/Reports/CashSummary?fromDate={period_start}&toDate={period_end}
// Output shape (abbreviated)
{ "Reports": [{ "ReportName": "Profit and Loss", "Rows": [ { "RowType": "Section", "Title": "Income", "Rows": [ { "RowType": "Row", "Cells": [ { "Value": "Revenue" }, { "Value": "42500.00" } ] } ] } ] }] }

Revenue pipeline and closed-won data source. The Data Fetch Agent calls the HubSpot Deals API to retrieve closed-won revenue totals and open pipeline value for the reporting period, providing the sales-side figures that appear alongside recognised accounting revenue in the report.

// Endpoint: fetch closed-won deals for period
POST https://api.hubapi.com/crm/v3/objects/deals/search
Authorization: Bearer {HUBSPOT_PRIVATE_APP_TOKEN}
Content-Type: application/json
// Request body
{ "filters": [ { "propertyName": "dealstage", "operator": "EQ", "value": "{CLOSED_WON_STAGE_ID}" }, { "propertyName": "closedate", "operator": "BETWEEN", "value": "{period_start_ms}", "highValue": "{period_end_ms}" } ], "properties": ["dealname", "amount", "closedate", "pipeline"], "limit": 100 }
// Output shape (abbreviated)
{ "results": [ { "id": "123456", "properties": { "dealname": "Acme Corp Renewal", "amount": "12500.00", "closedate": "2024-01-31" } } ], "total": 8 }

Report template and calculation layer. The Data Fetch Agent writes source figures into fixed cell references. The Variance Analysis Agent then reads those values alongside budget figures and writes the commentary block into the designated summary section. Both agents share the same OAuth credential and operate on the same spreadsheet ID.

// Write figures to named range (Agent 1)
POST https://sheets.googleapis.com/v4/spreadsheets/{SPREADSHEET_ID}/values/{xero_pl_range}:append
// Batch update multiple ranges
POST https://sheets.googleapis.com/v4/spreadsheets/{SPREADSHEET_ID}/values:batchUpdate
// Read variance data (Agent 2)
GET https://sheets.googleapis.com/v4/spreadsheets/{SPREADSHEET_ID}/values/{variance_input_range}
// Write commentary (Agent 2)
PUT https://sheets.googleapis.com/v4/spreadsheets/{SPREADSHEET_ID}/values/{variance_commentary_cell}
// Set control flag after population complete
PUT https://sheets.googleapis.com/v4/spreadsheets/{SPREADSHEET_ID}/values/_control!B2

PDF export destination and stakeholder file access layer. The Distribution Agent exports the completed Google Sheet as a PDF and saves it to a specified folder in Google Drive, named with the report period and type. The Slack notification includes the public or shared Drive link to this file.

// Export Sheets file as PDF to Drive
GET https://www.googleapis.com/drive/v3/files/{SPREADSHEET_ID}/export?mimeType=application/pdf
// Upload PDF to target folder
POST https://www.googleapis.com/upload/drive/v3/files?uploadType=multipart
// Metadata body
{ "name": "{REPORT_FILENAME}", "parents": ["{GDRIVE_REPORT_FOLDER_ID}"], "mimeType": "application/pdf" }
// Set file sharing permission
POST https://www.googleapis.com/drive/v3/files/{FILE_ID}/permissions
{ "role": "reader", "type": "anyone" }  // or 'domain' depending on GDRIVE_SHARE_MODE

Report delivery channel. The Distribution Agent posts a structured message to the configured finance Slack channel containing the Google Drive file link, report period label, and a plain-language summary of the key variance points generated by the Variance Analysis Agent.

// Post report notification message
POST https://slack.com/api/chat.postMessage
Authorization: Bearer {SLACK_BOT_TOKEN}
Content-Type: application/json
// Request body
{ "channel": "{SLACK_FINANCE_CHANNEL_ID}", "text": "Financial report ready: {report_type} for {period_label}", "blocks": [ { "type": "section", "text": { "type": "mrkdwn", "text": "*{report_type} Report - {period_label}*\n{variance_summary_text}" } }, { "type": "actions", "elements": [ { "type": "button", "text": { "type": "plain_text", "text": "Open Report" }, "url": "{gdrive_file_url}" } ] } ] }

// Credential store contents — Financial Reporting Automation
// Xero
XERO_CLIENT_ID              = '<registered app client ID>'
XERO_CLIENT_SECRET          = '<registered app client secret>'
XERO_TENANT_ID              = '<organisation/tenant ID from Xero>'
XERO_REFRESH_TOKEN          = '<long-lived OAuth refresh token>'

// HubSpot
HUBSPOT_PRIVATE_APP_TOKEN   = '<xoxb-style private app token>'
HUBSPOT_PORTAL_ID           = '<HubSpot account/portal ID>'
HUBSPOT_PIPELINE_ID         = '<target sales pipeline ID>'
HUBSPOT_CLOSED_WON_STAGE_ID = '<deal stage ID for Closed Won>'

// Google (shared service account)
GOOGLE_SERVICE_ACCOUNT_JSON = '<service account key JSON, base64-encoded>'
SPREADSHEET_ID              = '<Google Sheets report template file ID>'
GDRIVE_REPORT_FOLDER_ID     = '<target Drive folder ID for PDF exports>'
GDRIVE_SHARE_MODE           = 'anyone_reader | domain_reader | restricted'

// Slack
SLACK_BOT_TOKEN             = '<xoxb-... bot OAuth token>'
SLACK_FINANCE_CHANNEL_ID    = '<channel ID, not channel name>'
SLACK_SIGNING_SECRET        = '<app signing secret, for future webhook validation>'

// Named ranges (Google Sheets)
RANGE_XERO_PL               = 'PL_Data!B4:D60'
RANGE_XERO_BS               = 'BS_Data!B4:D40'
RANGE_XERO_CF               = 'CF_Data!B4:D30'
RANGE_HUBSPOT_PIPELINE      = 'Pipeline_Data!B4:D8'
RANGE_BUDGET                = 'Budget!B4:B60'
RANGE_VARIANCE_WORKING      = 'Variance_Working!A4:E60'
RANGE_VARIANCE_FLAGS        = 'Variance_Flags!A4:C60'
CELL_VARIANCE_COMMENTARY    = 'Summary!B22'
CELL_CONTROL_STATUS         = '_control!B2'

// Config
FETCH_CRON_EXPRESSION       = '0 6 * * 1'  // Every Monday 06:00 UTC (weekly flash)
FETCH_CRON_MONTHLY          = '0 6 1 * *'  // First of month 06:00 UTC (monthly pack)
VARIANCE_THRESHOLD_PCT      = '10'         // Percentage, integer, no symbol
REVIEW_WINDOW_HOURS         = '4'          // Business hours before auto-approve
REPORT_FILENAME_PATTERN     = '{report_type}_{period_label}_{run_date}.pdf'