Developer Handover Pack

Developer Handover Pack

Team Performance Dashboard

Runs on a fixed weekly schedule, firing at 07:00 AM every Monday before the team starts work. It calls the HubSpot and Jira APIs in sequence, writes all retrieved metric values into the designated Google Sheets staging rows, then evaluates which metrics are flagged as requiring manual team-lead input and dispatches targeted Slack reminders to the relevant individuals with a direct link to their input cell. The agent waits up to 90 minutes for manual inputs before handing off to the Validation and Commentary Agent regardless of completion status. Complexity: Moderate. Estimated build: 14 hours.

// Input
trigger: { type: 'schedule', day: 'Monday', time: '07:00', tz: 'America/New_York' }

// HubSpot pull — expected fields
hubspot.deals.properties: [ 'dealname', 'amount', 'closedate', 'dealstage', 'pipeline' ]
hubspot.pipeline_summary: { open_deal_count: int, weighted_revenue: float, won_this_week: float }

// Jira pull — expected fields
jira.sprint.current: { sprint_id: str, sprint_name: str, velocity: int }
jira.issues: { open_count: int, closed_this_week: int, cycle_time_avg_days: float }

// Manual input check — Sheets read
sheets.staging_row.manual_fields: [ 'team_lead_metric_A', 'team_lead_metric_B', ... ]
sheets.staging_row.status_flag: 'pending' | 'submitted'

// Output
sheets.staging_row: {
  week_commencing: date,
  hs_deal_count: int,
  hs_weighted_revenue: float,
  hs_won_revenue: float,
  jira_velocity: int,
  jira_open_issues: int,
  jira_cycle_time: float,
  manual_fields_status: 'complete' | 'partial',
  agent_handoff_ready: bool
}
slack.reminder_sent_to: [ '@team_lead_1', '@team_lead_2' ]  // only where status = 'pending'

Activates after the Data Collection Agent sets agent_handoff_ready to true on the staging row, or after the 90-minute wait window closes. It reads the full staging row from Google Sheets, runs field-level validation (null checks and week-on-week delta checks against the prior week's row), flags any metric that has moved more than 25% in either direction, and then calls an LLM to draft a plain-English weekly summary. The draft is written to a dated Notion page in the designated performance commentary database. The page is set to status 'Awaiting Approval'. A Slack direct message is sent to the manager containing the Notion page URL and a summary of any flagged anomalies. Once the manager marks the Notion page as 'Approved', the agent triggers the Looker Studio data refresh and then posts the dashboard link plus the approved commentary to the management Slack channel. Complexity: Complex. Estimated build: 14 hours.

// Input
sheets.staging_row: { week_commencing, hs_deal_count, hs_weighted_revenue, hs_won_revenue,
  jira_velocity, jira_open_issues, jira_cycle_time, manual_fields_status, agent_handoff_ready }
sheets.prior_week_row: { same fields, offset by -7 days on week_commencing }

// Validation step — computed fields
validation.null_fields: [ string ]           // list of field names where value is null or empty
validation.anomaly_flags: [
  { field: str, current: float, prior: float, pct_change: float, direction: 'up'|'down' }
]  // only entries where abs(pct_change) > 25
validation.late_data_flag: bool

// LLM prompt context supplied
llm.context: { current_row, prior_row, anomaly_flags, late_data_flag, commentary_tone: 'concise' }

// Output — Notion page written
notion.page: {
  database_id: '<performance_commentary_db_id>',
  title: 'Weekly Report — {week_commencing}',
  properties: { Status: 'Awaiting Approval', Week: date },
  body_blocks: [ metrics_table, anomaly_callout, llm_draft_commentary ]
}

// Slack DM to manager
slack.dm: { user_id: '<manager_slack_id>', text: 'Draft ready for review', notion_url: str,
  anomaly_count: int, late_data: bool }

// On approval — Notion page status set to 'Approved' by manager
looker_studio.refresh: { data_source_id: '<sheets_connector_id>' }
slack.channel_post: {
  channel: '<management_channel_id>',
  text: approved_commentary_text,
  attachments: [ { title: 'Live Dashboard', url: '<looker_studio_report_url>' } ]
}

// ─────────────────────────────────────────────────────────────────
// TRIGGER
// ─────────────────────────────────────────────────────────────────
schedule.fire({ day: 'Monday', time: '07:00', tz: 'America/New_York' })
  -> event: { trigger_ts: ISO8601, week_commencing: date }

// ──────────────────────────────────────────────��──────────────────
// DATA COLLECTION AGENT — begins
// ─────────────────────────────────────────────────────────────────

// Step 1: Dedupe guard
sheets.read('weekly_staging', filter: { week_commencing: event.week_commencing })
  -> IF row_exists AND agent_handoff_ready = true: HALT (duplicate run detected)
  -> ELSE: continue

// Step 2: HubSpot API call
hubspot.GET /crm/v3/objects/deals
  params: { properties: ['dealname','amount','closedate','dealstage','pipeline'],
            filterGroups: [{ filters: [{ propertyName: 'closedate',
                                         operator: 'BETWEEN',
                                         value: week_start, highValue: week_end }] }] }
  -> raw: deals[]
  -> mapped: { hs_deal_count: int, hs_weighted_revenue: float, hs_won_revenue: float }

// Step 3: Jira API call
jira.GET /rest/agile/1.0/board/{boardId}/sprint
  params: { state: 'active' }
  -> sprint_id: str, sprint_name: str

jira.GET /rest/agile/1.0/sprint/{sprint_id}/issue
  params: { fields: ['status','resolutiondate','created'] }
  -> mapped: { jira_velocity: int, jira_open_issues: int, jira_cycle_time: float }

// Step 4: Sheets staging write (initial — API-sourced fields)
sheets.upsert('weekly_staging', key: 'week_commencing', values: {
  week_commencing,
  hs_deal_count, hs_weighted_revenue, hs_won_revenue,
  jira_velocity, jira_open_issues, jira_cycle_time,
  manual_fields_status: 'pending',
  agent_handoff_ready: false
})

// Step 5: Manual input check and Slack reminders
sheets.read('weekly_staging', week_commencing)
  -> for each manual_field WHERE value IS NULL:
       slack.postMessage({ user_id: config.team_lead_map[field],
                           text: 'Please enter {field} — link: {sheets_input_url}' })

// Step 6: Wait up to 90 minutes for manual inputs
timer.wait(90_minutes)
  -> sheets.read('weekly_staging', week_commencing)
  -> IF all manual_fields populated: manual_fields_status = 'complete'
  -> ELSE: manual_fields_status = 'partial', write LATE_DATA_FLAG = true

// Step 7: Set handoff flag
sheets.update('weekly_staging', { agent_handoff_ready: true })

// ─────────────────────────────────────────────────────────────────
// AGENT HANDOFF: Data Collection -> Validation and Commentary
// ─────────────────────────────────────────────────────────────────

// ─────────────────────────────────────────────────────────────────
// VALIDATION AND COMMENTARY AGENT — begins
// ─────────────────────────────────────────────────────────────────

// Step 8: Read current and prior week rows
sheets.read('weekly_staging', filter: { week_commencing: current_week }) -> current_row
sheets.read('weekly_staging', filter: { week_commencing: prior_week })   -> prior_row

// Step 9: Null validation
validation.null_fields = [ field FOR field IN current_row WHERE value IS NULL ]

// Step 10: Anomaly detection
FOR each numeric field IN current_row:
  pct_change = ((current - prior) / prior) * 100
  IF abs(pct_change) > config.anomaly_threshold (default: 25):
    validation.anomaly_flags.append({ field, current, prior, pct_change, direction })

// Step 11: LLM commentary draft
llm.complete({
  system: config.commentary_prompt_template,
  user: { current_row, prior_row, anomaly_flags, late_data_flag, max_words: 200 }
}) -> draft_commentary: str

// Step 12: Write Notion page
notion.pages.create({
  parent: { database_id: config.notion_db_id },
  properties: { Title: 'Weekly Report — {week_commencing}',
                Status: 'Awaiting Approval', Week: week_commencing },
  children: [ metrics_table_block, anomaly_callout_block,
               paragraph_block(draft_commentary) ]
}) -> notion_page_url: str

// Step 13: Slack DM to manager
slack.postMessage({ user_id: config.manager_slack_id,
  text: 'Draft ready. Anomalies: {anomaly_count}. Late data: {late_data_flag}',
  blocks: [{ type: 'button', text: 'Review in Notion', url: notion_page_url }] })

// Step 14: Poll for manager approval
POLL every 2 minutes, timeout 4 hours:
  notion.pages.retrieve(notion_page_url) -> page.properties.Status
  IF Status = 'Approved': proceed
  IF timeout: slack.postMessage(config.manager_slack_id, 'Approval overdue') -> HALT

// ─────────────────────────────────────────────────────────────────
// POST-APPROVAL: Looker Studio refresh + Slack distribution
// ────────────────────────────────────────────────────���────────────

// Step 15: Looker Studio refresh (Sheets connector — data already written at Step 4)
// No explicit API call required if using Sheets connector; verify connector type.
// If BigQuery intermediary: call bq.jobs.insert (load job) before this step.
looker_studio.verify_refresh({ report_url: config.looker_studio_report_url })

// Step 16: Slack channel post — distribution
slack.postMessage({
  channel: config.management_channel_id,
  text: notion.pages.retrieve(notion_page_url).commentary_text,
  attachments: [{ title: 'Live Dashboard', url: config.looker_studio_report_url }]
})

// ─────────────────────────────────────────────────────────────────
// END OF FLOW
// ─────────────────────────────────────────────────────────────────