Integration and API Spec

Integration and API Spec

Business KPI Dashboard

Read-only financial data source. The Data Collection Agent (Agent 1) calls the Xero Accounting API on schedule to retrieve revenue, invoice, and cash position figures for the current reporting period.

// Xero API calls made by Agent 1
GET /api.xro/2.0/Reports/ProfitAndLoss?fromDate={period_start}&toDate={period_end}
GET /api.xro/2.0/Invoices?where=Status=="AUTHORISED"&DateFrom={period_start}&DateTo={period_end}
GET /api.xro/2.0/Reports/BankSummary?fromDate={period_start}&toDate={period_end}
// Key fields extracted
response.Reports[0].Rows -> total_revenue
response.Invoices -> outstanding_invoice_count, outstanding_invoice_total
response.Reports[0].Rows -> closing_cash_balance

Read-only sales pipeline data source. Agent 1 calls the HubSpot CRM API to retrieve deal count, total pipeline value, and conversion rate for the reporting period.

// HubSpot API calls made by Agent 1
POST /crm/v3/objects/deals/search
  body: { filterGroups: [{ filters: [
    { propertyName: 'pipeline', operator: 'EQ', value: HUBSPOT_PIPELINE_ID },
    { propertyName: 'closedate', operator: 'BETWEEN', value: period_start, highValue: period_end }
  ]}], properties: ['dealname','amount','dealstage','hs_deal_stage_probability'] }
// Key fields extracted
response.results -> deal_count
SUM(response.results[].amount WHERE dealstage IN HUBSPOT_REPORTING_STAGE_IDS) -> pipeline_value
AVG(response.results[].hs_deal_stage_probability) -> avg_conversion_rate

Central KPI store. Agent 1 writes raw metric values into designated input cells. Existing spreadsheet formulas calculate derived KPIs (margin, growth rate, etc.). Agent 2 reads the calculated output cells to generate commentary.

// Agent 1 write — single batchUpdate call
PUT /v4/spreadsheets/{GOOGLE_SPREADSHEET_ID}/values/{SHEETS_INPUT_RANGE}:batchUpdate
  body: { valueInputOption: 'USER_ENTERED', data: [
    { range: 'KPI_Input!B2', values: [[total_revenue]] },
    { range: 'KPI_Input!B3', values: [[outstanding_invoice_total]] },
    { range: 'KPI_Input!B4', values: [[closing_cash_balance]] },
    { range: 'KPI_Input!B5', values: [[deal_count]] },
    { range: 'KPI_Input!B6', values: [[pipeline_value]] },
    { range: 'KPI_Input!B7', values: [[avg_conversion_rate]] }
  ]}
// Agent 2 read
GET /v4/spreadsheets/{GOOGLE_SPREADSHEET_ID}/values/{SHEETS_OUTPUT_RANGE}
  -> returns calculated KPI rows for commentary generation

Commentary publication target and reporting archive. Agent 2 writes the AI-generated KPI commentary to a designated Notion page for the current reporting week.

// Agent 2 creates a new Notion page
POST /v1/pages
  headers: { Authorization: Bearer NOTION_TOKEN, Notion-Version: '2022-06-28' }
  body: {
    parent: { database_id: NOTION_DATABASE_ID },
    properties: { Name: { title: [{ text: { content: 'KPI Report - {YYYY-WW}' }}]}},
    children: [
      { object: 'block', type: 'paragraph',
        paragraph: { rich_text: [{ type: 'text', text: { content: ai_commentary_text }}]}}
    ]
  }
// Response
response.id -> notion_page_id (passed to Agent 3 as handoff payload)

Automated report distribution. Agent 3 posts a formatted message to the configured team channel containing headline KPI figures, the AI commentary summary, and the Looker Studio dashboard link.

// Agent 3 posts to Slack
POST /api/chat.postMessage
  headers: { Authorization: Bearer SLACK_BOT_TOKEN }
  body: {
    channel: SLACK_CHANNEL_ID,
    text: 'KPI Dashboard for week {YYYY-WW} is live.',
    blocks: [
      { type: 'header', text: { type: 'plain_text', text: 'Weekly KPI Report - {YYYY-WW}' }},
      { type: 'section', text: { type: 'mrkdwn', text: ai_commentary_summary }},
      { type: 'section', fields: [
          { type: 'mrkdwn', text: '*Revenue:* {total_revenue}' },
          { type: 'mrkdwn', text: '*Pipeline:* {pipeline_value}' },
          { type: 'mrkdwn', text: '*Deals:* {deal_count}' },
          { type: 'mrkdwn', text: '*Conversion:* {avg_conversion_rate}%' }
      ]},
      { type: 'actions', elements: [
          { type: 'button', text: { type: 'plain_text', text: 'View Dashboard' },
            url: LOOKER_STUDIO_DASHBOARD_URL }
      ]}
    ]
  }

Live visual dashboard shared via link in the Slack message. No API write is required; Looker Studio connects directly to the Google Sheet as its data source and refreshes automatically when the sheet is updated.

// No API call made by the automation platform
// Looker Studio refreshes automatically from Google Sheets data source
// Agent 3 uses the stored URL only
LOOKER_STUDIO_DASHBOARD_URL -> embedded in Slack Block Kit button action
// Validation step in Agent 3
HTTP GET {LOOKER_STUDIO_DASHBOARD_URL} -> assert HTTP 200 before posting to Slack
  on non-200: halt Agent 3, notify via SLACK_ALERT_CHANNEL_ID, log error

# Xero
XERO_CLIENT_ID          = '<from Xero Developer Portal>'
XERO_CLIENT_SECRET      = '<from Xero Developer Portal>'
XERO_TENANT_ID          = '<retrieved from GET /connections after OAuth handshake>'
XERO_REFRESH_TOKEN      = '<stored after initial OAuth; auto-rotated on each refresh>'

# HubSpot
HUBSPOT_PRIVATE_APP_TOKEN     = '<from HubSpot Private App settings>'
HUBSPOT_PIPELINE_ID           = '<retrieved from GET /crm/v3/pipelines/deals>'
HUBSPOT_REPORTING_STAGE_IDS   = '["stage_id_1", "stage_id_2"]'

# Google
GOOGLE_SA_KEY_JSON            = '<full JSON contents of service account key file>'
GOOGLE_SPREADSHEET_ID         = '<alphanumeric ID from Google Sheets URL>'
SHEETS_INPUT_RANGE            = 'KPI_Input!B1:B7'
SHEETS_OUTPUT_RANGE           = 'KPI_Calculated!B2:B9'

# Notion
NOTION_TOKEN                  = '<Internal Integration Token from notion.so/my-integrations>'
NOTION_DATABASE_ID            = '<alphanumeric ID from Notion database URL>'
NOTION_PARENT_PAGE_ID         = '<ID of parent page under which weekly reports are created>'

# Slack
SLACK_BOT_TOKEN               = '<Bot User OAuth Token from api.slack.com/apps>'
SLACK_CHANNEL_ID              = '<target channel ID, not display name>'
SLACK_ALERT_CHANNEL_ID        = '<ops alert channel ID for anomaly and error notifications>'
SLACK_STAKEHOLDER_USER_IDS    = '["U012AB3CD", "U098ZY7WX"]'

# Looker Studio
LOOKER_STUDIO_DASHBOARD_URL   = '<public share link, verified accessible before each run>'

# Internal
INTERNAL_WEBHOOK_SECRET       = '<random 32-byte hex string, shared across all three workflows>'
ANOMALY_THRESHOLD_REVENUE_PCT = '15'   # % deviation from prior period that triggers anomaly flag
ANOMALY_THRESHOLD_PIPELINE_PCT = '20'  # % deviation from prior period that triggers anomaly flag