Integration and API Spec

Integration and API Spec

Team Performance Dashboard

Used by the Data Collection Agent to retrieve deal pipeline metrics on the Monday 7:00 AM schedule. All calls are read-only. A HubSpot private app token is the recommended auth method because it does not expire on short rotations and does not require a full OAuth consent screen per user.

// Input
HUBSPOT_PIPELINE_ID, HUBSPOT_DEAL_STAGE_IDS, date_range { start: Monday 00:00 UTC, end: Sunday 23:59 UTC }
// Output
deals_count: integer
pipeline_value_usd: float
deals_by_stage: { stage_id: string, count: integer, value: float }[]
weighted_forecast_usd: float

Used by the Data Collection Agent to retrieve sprint velocity, open issue counts, and cycle time for the current reporting period. Jira Cloud REST API v3 is assumed. Authentication uses an API token paired with the admin service account email via HTTP Basic Auth (base64-encoded).

// Input
JIRA_BOARD_ID, JIRA_PROJECT_KEY, active_sprint_id (resolved from board endpoint)
// Output
sprint_velocity_points: integer
open_issue_count: integer
avg_cycle_time_days: float
sprint_name: string
sprint_end_date: ISO8601 string

Used by both agents. The Data Collection Agent writes API-retrieved metrics to a defined staging area; the Validation and Commentary Agent reads from the same sheet to run validation logic. Team leads also write manual metric submissions directly into designated input cells. A service account is the recommended auth method to avoid user-session expiry.

// Input (write, Data Collection Agent)
week_start_date, deals_count, pipeline_value_usd, weighted_forecast_usd,
sprint_velocity_points, open_issue_count, avg_cycle_time_days,
manual_metric_[n] (populated by team leads)
// Output (read, Validation and Commentary Agent)
full_row object matching staging schema
missing_fields: string[] (fields where value is null or empty)
anomaly_flags: { field: string, current: float, previous: float, pct_change: float }[]

The Looker Studio dashboard is connected directly to the Google Sheets staging spreadsheet as its data source. Refreshing the dashboard means triggering a data source cache invalidation via the Looker Studio API (where available) or relying on Looker Studio's automatic refresh interval. The automation ensures the Google Sheets write is complete before the refresh call is made.

// Input
LOOKER_STUDIO_DATASOURCE_ID, trigger: google_sheets_write_confirmed = true
// Output
refresh_status: 'success' | 'pending' | 'error'
refreshed_at: ISO8601 timestamp

Used by the Validation and Commentary Agent to write the AI-drafted weekly summary to a defined Notion page and to surface the manager approval gate. The automation appends a new block to a designated Notion performance page each week. The manager reads and edits the draft in Notion, then triggers approval (via a Notion checkbox property or an external approval mechanism connected to the orchestration layer).

// Input
validated_data_set: object (from Google Sheets read)
anomaly_flags: { field: string, pct_change: float }[]
ai_commentary_draft: string (generated by LLM step in orchestration layer)
// Output
notion_page_url: string
approval_status: 'pending' | 'approved' | 'edited_and_approved'
approved_commentary: string (final text after manager edits)

Used by both agents. The Data Collection Agent sends targeted submission reminders to individual team leads for missing manual metric inputs. The Validation and Commentary Agent (after manager approval) posts the final dashboard link and approved commentary to the management Slack channel. A single Slack bot app handles both use cases.

// Input (reminder, Data Collection Agent)
missing_metric_fields: string[], team_lead_user_id: string, google_sheets_input_url: string
// Output (reminder)
message_ts: string (Slack message timestamp for threading if needed)
// Input (distribution, Validation and Commentary Agent)
approved_commentary: string, looker_studio_report_url: string, week_label: string
// Output (distribution)
message_ts: string, channel_id: string

// HubSpot
HUBSPOT_PRIVATE_APP_TOKEN        = 'pat-na1-xxxxxxxxxxxxxxxxxxxx'
HUBSPOT_PIPELINE_ID              = 'xxxxxxxxxxxxxxxxxxxxxxxx'
HUBSPOT_DEAL_STAGE_IDS           = 'stageid1,stageid2,stageid3'

// Jira
JIRA_BASE_URL                    = 'https://yourcompany.atlassian.net'
JIRA_API_TOKEN                   = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxx'
JIRA_SERVICE_ACCOUNT_EMAIL       = 'automation@yourcompany.com'
JIRA_BOARD_ID                    = '42'
JIRA_PROJECT_KEY                 = 'ENG'

// Google (shared service account)
GOOGLE_SERVICE_ACCOUNT_JSON      = '{ "type": "service_account", ... }'
GOOGLE_SHEETS_STAGING_SPREADSHEET_ID = '1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgVE2upms'
GOOGLE_SHEETS_STAGING_TAB_NAME   = 'WeeklyStaging'
GOOGLE_SHEETS_INPUT_RANGE        = 'WeeklyStaging!A2:Z200'

// Looker Studio
LOOKER_STUDIO_REPORT_ID          = 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx'
LOOKER_STUDIO_DATASOURCE_ID      = 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx'
LOOKER_STUDIO_REPORT_URL         = 'https://lookerstudio.google.com/reporting/...'

// Notion
NOTION_INTEGRATION_TOKEN         = 'secret_xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
NOTION_PERFORMANCE_PAGE_ID       = 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx'
NOTION_APPROVAL_PROPERTY_NAME    = 'Approved'

// Slack
SLACK_BOT_TOKEN                  = 'xoxb-xxxxxxxxxxxx-xxxxxxxxxxxx-xxxxxxxxxxxxxxxxxxxxxxxx'
SLACK_SIGNING_SECRET             = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
SLACK_MANAGEMENT_CHANNEL_ID      = 'C0XXXXXXXXX'
SLACK_REMINDER_CHANNEL_ID        = 'C0XXXXXXXXX'
TEAM_LEAD_SLACK_USER_IDS         = '{ "lead1@co.com": "U0XXXXXXXXX", "lead2@co.com": "U0XXXXXXXXX" }'

// LLM
LLM_API_KEY                      = 'sk-xxxxxxxxxxxxxxxxxxxxxxxxxxxx'
LLM_MODEL                        = 'gpt-4o'
LLM_TEMPERATURE                  = '0.3'

// Schedule
SCHEDULE_TIMEZONE                = 'America/New_York'
ANOMALY_THRESHOLD_PCT            = '25'
APPROVAL_POLL_INTERVAL_MINUTES   = '5'
APPROVAL_MAX_WAIT_MINUTES        = '240'
COLLECTION_MAX_WAIT_MINUTES      = '90'