Developer Handover Pack

Developer Handover Pack

Business KPI Dashboard

Fires on a fixed weekly schedule (Monday 07:00 local time by default) and connects to Xero and HubSpot via their respective REST APIs. It retrieves the financial and sales metrics for the current reporting period, normalises field values into a consistent schema, and writes each figure into the pre-mapped input cells of the master Google Sheet. The agent does not perform any calculation; it is a pure extract-and-write operation. On completion it emits a success status that triggers the KPI Commentary Agent. If any API call fails or returns unexpected null values, the agent halts, logs the error to the central error table, and sends an alert to the ops manager before any downstream agents fire.

// Input
trigger_type: 'schedule' | 'webhook'
reporting_period_start: ISO8601 date (auto-calculated as Monday 00:00 UTC)
reporting_period_end:   ISO8601 date (auto-calculated as Sunday 23:59 UTC)

// Xero fields retrieved
xero.total_revenue:        number (GBP/USD, per organisation currency)
xero.outstanding_invoices: number (count)
xero.outstanding_value:    number (monetary)
xero.cash_position:        number (bank account balance, primary account)

// HubSpot fields retrieved
hubspot.deal_count:        integer (deals in 'Close Date' range)
hubspot.pipeline_value:    number (sum of deal amounts)
hubspot.conversion_rate:   float  (closed-won / total deals, period)

// Output
sheets.write_status:       'success' | 'partial' | 'failed'
sheets.cells_written:      integer (count of cells updated)
sheets.timestamp:          ISO8601 datetime
agent.status:              'complete' | 'error'
agent.error_detail:        string | null

Fires after the Data Collection Agent emits a 'complete' status confirming all cells are written. It reads the updated KPI values from Google Sheets, compares them against the prior period values stored in a separate history tab, identifies statistically notable changes (defined as movement outside a configured percentage threshold per KPI), and passes a structured prompt to the configured large language model to produce a concise plain-English commentary. The commentary is then written to the designated Notion page for the current reporting week. The agent does not publish to Slack; it emits a confirmation that triggers the Distribution Agent. If the Notion write fails, the agent retries twice before logging an error and halting the chain.

// Input
trigger_event:            'data_collection_complete'
sheets.kpi_input_range:   A1-notation range of current-period values
sheets.history_range:     A1-notation range of prior-period values (KPI_History tab)
comparison_threshold_pct: float (default 10.0, configurable per KPI)

// KPI values read
current.total_revenue:        number
current.outstanding_value:    number
current.cash_position:        number
current.deal_count:           integer
current.pipeline_value:       number
current.conversion_rate:      float
prior.total_revenue:          number
prior.pipeline_value:         number
prior.conversion_rate:        float

// LLM prompt fields
prompt.kpi_delta_summary:     string (structured JSON of changes passed to model)
prompt.tone_instruction:      string ('concise, factual, plain English, under 120 words')
prompt.format_instruction:    string ('3 to 5 bullet points; no markdown headers')

// Output
notion.page_id:               string (UUID of the created/updated Notion page)
notion.page_url:              string (shareable URL for downstream use)
notion.write_status:          'success' | 'failed'
commentary.text:              string (final published commentary body)
agent.status:                 'complete' | 'error'

// On approval (anomaly path)
anomaly_flag:                 boolean
anomaly_fields:               array of string (KPI names outside threshold)
ops_manager_review_required:  boolean (true if any anomaly_flag is true)
ops_manager_approved:         boolean (set by manual webhook response before commentary is drafted)

Fires after the KPI Commentary Agent confirms the Notion page has been published successfully. It constructs a formatted Slack message containing the headline KPI figures, the AI-generated commentary text, and a direct link to the live Looker Studio dashboard. The message is posted to the configured Slack channel with stakeholder mentions. The agent reads the Notion page URL and commentary text from the upstream event payload; it does not re-query Notion or Google Sheets. This keeps the agent stateless and fast. On Slack API failure the agent retries once and then logs an error; it does not halt any upstream state since all data is already published.

// Input
trigger_event:            'commentary_published'
notion.page_url:          string (from upstream KPI Commentary Agent output)
commentary.text:          string (from upstream KPI Commentary Agent output)
looker_studio.dashboard_url: string (static, configured at build time)
slack.channel_id:         string (configured at build time, e.g. C0123ABCDEF)
slack.mention_user_ids:   array of string (Slack user IDs for tagging, configured at build time)

// Slack message fields constructed
message.headline:         string ('KPI Report: [Week ending DATE]')
message.kpi_summary:      string (top 3 headline figures formatted as plain text)
message.commentary_block: string (commentary.text, max 500 characters before truncation)
message.dashboard_link:   string (looker_studio.dashboard_url)
message.notion_link:      string (notion.page_url, labelled 'Full report in Notion')

// Output
slack.message_ts:         string (Slack message timestamp, for audit log)
slack.post_status:        'success' | 'failed'
agent.status:             'complete' | 'error'

// =====================================================================
// BUSINESS KPI DASHBOARD  |  END-TO-END DATA FLOW
// =====================================================================

// TRIGGER
// Source: Time-based schedule OR manual webhook
// Field:  trigger_type = 'schedule' | 'webhook'
// Field:  reporting_period_start = '2025-01-13T00:00:00Z'  (auto-calc)
// Field:  reporting_period_end   = '2025-01-19T23:59:59Z'  (auto-calc)
// Field:  run_id = uuid_v4()                               (generated)

// =====================================================================
// AGENT 1: DATA COLLECTION AGENT
// =====================================================================

// --- Xero API call ---
// Endpoint: GET https://api.xero.com/api.xro/2.0/Reports/ProfitAndLoss
// Params:   fromDate=reporting_period_start, toDate=reporting_period_end
// Auth:     OAuth 2.0 Bearer token (scope: accounting.reports.read)
xero.total_revenue        = Reports[0].Rows[revenue_row].Cells[1].Value
xero.outstanding_invoices = Invoices.filter(Status='AUTHORISED').length
xero.outstanding_value    = Invoices.filter(Status='AUTHORISED').sum(AmountDue)
xero.cash_position        = BankAccounts[primary_account_id].CurrentBalance

// --- HubSpot API call ---
// Endpoint: POST https://api.hubapi.com/crm/v3/objects/deals/search
// Filter:   closedate BETWEEN reporting_period_start AND reporting_period_end
// Auth:     Private App Bearer token (scope: crm.objects.deals.read)
hubspot.deal_count        = response.total
hubspot.pipeline_value    = response.results.sum(properties.amount)
hubspot.conversion_rate   = closed_won_deals / total_deals_in_period

// --- Anomaly gate (inline, before Sheets write) ---
// For each KPI field: if abs((current - prior) / prior) > threshold_pct
//   -> set anomaly_flag = true
//   -> append field name to anomaly_fields[]
//   -> send Slack DM alert to ops manager
//   -> await webhook approval (timeout 4h -> halt, log error)
// If no anomalies: continue immediately

// --- Google Sheets write ---
// Endpoint: PUT https://sheets.googleapis.com/v4/spreadsheets/{SHEET_ID}/values/{RANGE}
// Auth:     Service account JSON key (role: Editor on sheet)
// Tab:      KPI_Input
sheets.cell_B2            = xero.total_revenue
sheets.cell_B3            = xero.outstanding_invoices
sheets.cell_B4            = xero.outstanding_value
sheets.cell_B5            = xero.cash_position
sheets.cell_B6            = hubspot.deal_count
sheets.cell_B7            = hubspot.pipeline_value
sheets.cell_B8            = hubspot.conversion_rate
sheets.cell_B1            = run_id                  // audit cell
sheets.write_status       = 'success' | 'partial' | 'failed'

// --- Agent 1 emits ---
agent_1.status            = 'complete'
agent_1.cells_written     = integer
agent_1.timestamp         = ISO8601 datetime

// =====================================================================
// HANDOFF: DATA COLLECTION AGENT -> KPI COMMENTARY AGENT
// Condition: agent_1.status == 'complete'
// Payload passed: run_id, reporting_period_start, reporting_period_end,
//   sheets.write_status, anomaly_flag, anomaly_fields, ops_manager_approved
// =====================================================================

// =====================================================================
// AGENT 2: KPI COMMENTARY AGENT
// =====================================================================

// --- Google Sheets read (current period) ---
// Tab: KPI_Input, range: B2:B8
current.total_revenue        = sheets.cell_B2
current.outstanding_invoices = sheets.cell_B3
current.outstanding_value    = sheets.cell_B4
current.cash_position        = sheets.cell_B5
current.deal_count           = sheets.cell_B6
current.pipeline_value       = sheets.cell_B7
current.conversion_rate      = sheets.cell_B8

// --- Google Sheets read (prior period, history tab) ---
// Tab: KPI_History, last populated row
prior.total_revenue          = KPI_History[last_row].col_B
prior.pipeline_value         = KPI_History[last_row].col_G
prior.conversion_rate        = KPI_History[last_row].col_H

// --- Baseline check ---
// If KPI_History has zero rows: set is_baseline_week = true
// Commentary prompt instructs model to note this is week 1 baseline

// --- LLM inference ---
// Endpoint: POST https://api.openai.com/v1/chat/completions
// Model:    gpt-4o
// Auth:     Bearer API key from credential store
prompt.kpi_delta_summary     = JSON.stringify(delta_object)   // computed deltas
prompt.tone_instruction      = 'concise, factual, plain English, under 120 words'
prompt.format_instruction    = '3 to 5 bullet points; no markdown headers'
commentary.text              = response.choices[0].message.content

// --- Notion write ---
// Endpoint: POST https://api.notion.com/v1/pages
// Auth:     Integration token (Bearer), invited to target database
// Page properties written:
notion.page_title            = 'KPI Report: Week ending ' + reporting_period_end.date
notion.date_property         = reporting_period_end
notion.body_block            = commentary.text
notion.page_id               = response.id
notion.page_url              = response.url
notion.write_status          = 'success' | 'failed'

// --- Append to KPI_History tab ---
// Tab: KPI_History, append new row with current period values
KPI_History.append_row([reporting_period_end, total_revenue, ..., run_id])

// --- Agent 2 emits ---
agent_2.status               = 'complete'
agent_2.notion_page_url      = notion.page_url
agent_2.commentary_text      = commentary.text

// =====================================================================
// HANDOFF: KPI COMMENTARY AGENT -> DISTRIBUTION AGENT
// Condition: agent_2.status == 'complete' AND notion.write_status == 'success'
// Payload passed: run_id, reporting_period_end, notion.page_url,
//   commentary.text, current.total_revenue, current.pipeline_value,
//   current.conversion_rate
// =====================================================================

// =====================================================================
// AGENT 3: DISTRIBUTION AGENT
// =====================================================================

// --- Deduplication check ---
// Query error/audit log table: WHERE week_key = reporting_period_end.date
//   AND post_status = 'success'
// If record found: skip Slack post, log duplicate attempt, halt

// --- Slack Block Kit message construction ---
message.headline             = 'KPI Report: Week ending ' + reporting_period_end.date
message.kpi_summary          = format([total_revenue, pipeline_value, conversion_rate])
message.commentary_block     = commentary.text.slice(0, 500)  // truncate if needed
message.dashboard_link       = looker_studio.dashboard_url    // static config value
message.notion_link          = notion.page_url

// --- Slack API call ---
// Endpoint: POST https://slack.com/api/chat.postMessage
// Auth:     Bot token xoxb-... (scope: chat:write)
// Params:   channel = slack.channel_id (config)
//           blocks  = Block Kit JSON payload
//           text    = fallback plain text
slack.message_ts             = response.ts
slack.post_status            = 'success' | 'failed'

// --- Audit log write ---
// Write final run record to Supabase audit table
audit_log.run_id             = run_id
audit_log.week_key           = reporting_period_end.date
audit_log.post_status        = slack.post_status
audit_log.completed_at       = ISO8601 datetime

// --- Agent 3 emits ---
agent_3.status               = 'complete'
// END OF FLOW