Integration and API Spec

Integration and API Spec

Board and Investor Reporting Automation

Used by Agent 1 (Data Collector) to pull profit and loss, cash flow, and balance sheet reports for the closed reporting period via the Xero Accounting API (v2). All calls are read-only. No write operations are performed against Xero.

// Input
fromDate: string  // e.g. '2024-06-01'
toDate:   string  // e.g. '2024-06-30'
// Output (written to Google Sheets staging layer)
xero.revenue_total:        number
xero.gross_profit:         number
xero.net_profit:           number
xero.cash_position:        number
xero.total_assets:         number
xero.total_liabilities:    number
xero.report_period_label:  string  // e.g. 'June 2024'

Used by Agent 1 (Data Collector) to retrieve sales pipeline data including deals by stage, weighted forecast total, and closed-won revenue for the reporting period. Uses the HubSpot CRM API v3.

// Input
pipeline_id:  string   // from credential store
closedate_gte: string  // period start, e.g. '2024-06-01'
closedate_lte: string  // period end,   e.g. '2024-06-30'
// Output (written to Google Sheets staging layer)
hs.pipeline_total_open:    number  // sum of amount across all open stages
hs.weighted_forecast:      number  // calculated: sum(amount * probability)
hs.closed_won_revenue:     number  // sum of amount where dealstage = closed-won
hs.deal_count_by_stage:    object  // { stage_name: count }
hs.report_period_label:    string

Used by all three agents as the central staging and coordination layer. Agent 1 writes financial and pipeline data. Agent 2 writes KPI submissions. Agent 3 reads the completed dataset to assemble the board pack. A status flag cell signals workflow progression between agents.

// Reads (Agent 3 input)
spreadsheet_id: string  // from credential store
sheet: 'financials'    // range: A2:G2 (current period row)
sheet: 'pipeline'      // range: A2:D2
sheet: 'kpi_submissions' // range: A2:E{n} where n = number of KPI rows
// Writes (Agent 1 and Agent 2 output)
financials!A2:G2   <- xero output fields
pipeline!A2:D2     <- hubspot output fields
kpi_submissions!A{n}:E{n}  <- one row per KPI submission
_control!B2        <- workflow_status string

Used by Agent 3 (Pack Assembly) to read the master board pack template, produce a period-specific working copy, populate it with data from Google Sheets, export the approved version as PDF, and upload the final file to the shared board distribution folder.

// Input
template_file_id:  string  // from credential store
board_folder_id:   string  // from credential store
period_label:      string  // e.g. 'June 2024 Board Pack'
field_map:         object  // all populated fields from Sheets (see section 03)
// Actions
1. POST /drive/v3/files/{templateId}/copy  -> new_file_id
2. POST /docs/v1/documents/{new_file_id}:batchUpdate  -> replaceAllText per placeholder
3. GET  /drive/v3/files/{new_file_id}/export?mimeType=application/pdf  -> pdf_bytes
4. POST /drive/v3/files (multipart upload to board_folder_id)  -> final_pdf_file_id
// Output
draft_doc_url:    string  // link sent to CEO via Slack (Agent 2 channel)
final_pdf_url:    string  // link distributed to board members via email notification

Used by Agent 2 (KPI Collection) to send structured prompts to each department head, track response status, issue a single automated reminder for non-responders, and notify the CEO when the draft pack is ready. Uses the Slack Web API via a custom bot app.

// Outbound: KPI prompt (per department head)
POST /api/chat.postMessage
{ channel: SLACK_USER_{NAME}, blocks: [ ...KPI_PROMPT_BLOCK_KIT... ] }
// Inbound: department head response (interactive payload)
{ type: 'block_actions', user: { id, name }, actions: [ { action_id, value } ] }
// Outbound: reminder (24h after initial prompt, non-responders only)
POST /api/chat.postMessage
{ channel: SLACK_USER_{NAME}, text: 'Reminder: KPI submission due in 2 hours.' }
// Outbound: CEO notification
POST /api/chat.postMessage
{ channel: SLACK_USER_CEO, text: 'Board pack draft is ready for review.', blocks: [ ...link_button... ] }

// Credential store contents (production environment)
// Xero
XERO_CLIENT_ID          = '<from Xero developer portal>'
XERO_CLIENT_SECRET      = '<from Xero developer portal>'
XERO_REFRESH_TOKEN      = '<issued after OAuth consent, rotate every 60 days>'
XERO_TENANT_ID          = '<resolved after OAuth consent, static>'

// HubSpot
HUBSPOT_API_TOKEN       = '<Private App token from HubSpot settings>'
HUBSPOT_PIPELINE_ID     = '<resolved from GET /crm/v3/pipelines/deals at build time>'

// Google (shared service account)
GOOGLE_SERVICE_ACCOUNT_JSON = '<base64-encoded service account key JSON>'
GOOGLE_SPREADSHEET_ID       = '<production Sheets file ID>'
GDRIVE_TEMPLATE_FILE_ID     = '<master board pack template Google Doc/Slides ID>'
GDRIVE_BOARD_FOLDER_ID      = '<shared board distribution folder ID>'

// Slack
SLACK_BOT_TOKEN         = '<xoxb- token from Slack app>'
SLACK_SIGNING_SECRET    = '<from Slack app Basic Information page>'
SLACK_USER_CEO          = '<Slack user ID of CEO>'
SLACK_USER_<NAME>       = '<Slack user ID per department head, one entry each>'

// Orchestration config (not secrets but environment-specific)
REPORT_TRIGGER_DATE     = '<cron expression or date param per reporting schedule>'
SLACK_KPI_DEADLINE_HOURS = '24'

// Test environment overrides (separate named environment)
GOOGLE_SPREADSHEET_ID   = '<test Sheets file ID>'
GDRIVE_TEMPLATE_FILE_ID = '<test template copy ID>'
GDRIVE_BOARD_FOLDER_ID  = '<test Drive folder ID>'
XERO_TENANT_ID          = '<Xero demo company tenant ID>'