Developer Handover Pack

Developer Handover Pack

Support Reporting and Insights

Handles the scheduled retrieval of ticket data from Zendesk via API and writes the clean dataset to Google Sheets without any manual export or formatting step. This agent fires on the configured schedule (daily at 07:00, weekly on Monday at 08:00, and monthly on the first working day at 08:00) and is entirely autonomous. It replaces the two most time-intensive bottleneck steps: the manual CSV export and the formatting work that currently follows it. Build complexity is Moderate because the Zendesk API query must handle pagination, incremental date-range parameterisation, and a conditional field mapping layer to normalise column names before writing to the Sheet.

// Input
schedule_event: { cadence: 'daily' | 'weekly' | 'monthly', period_start: ISO8601, period_end: ISO8601 }

// Zendesk API query parameters
GET /api/v2/search.json
  ?query=type:ticket created>={period_start} created<={period_end}
  &sort_by=created_at&sort_order=asc&per_page=100
  Headers: { Authorization: 'Bearer {ZENDESK_API_TOKEN}' }

// Output
google_sheets_write: {
  sheet_id: '{SHEET_ID}',
  tab_name: 'raw_data_{cadence}',
  rows: [
    { ticket_id, created_at, resolved_at, status, assignee_id,
      category_tag, csat_score, first_response_time_min,
      resolution_time_min, sla_breach: bool }
  ],
  write_mode: 'overwrite',
  confirmation_flag: 'data_fetch_complete'
}

Reads the refreshed Google Sheet populated by the Data Fetch Agent, calculates all core support metrics, drafts a plain-English commentary summarising key changes and anomalies for the period, and triggers a human review gate for monthly deep-dive reports. This agent is the most logic-intensive in the pipeline. It must handle three cadence-specific calculation templates, route the output correctly based on a monthly/non-monthly decision, and pass a ready-to-distribute summary to the Distribution Agent. For monthly reports, execution pauses at the commentary review step and waits for explicit manager approval before continuing. Complexity is rated Complex due to the conditional branching, metric formula definitions, and AI commentary generation layer.

// Input
trigger: { confirmation_flag: 'data_fetch_complete', cadence: 'daily' | 'weekly' | 'monthly',
           sheet_id: '{SHEET_ID}', tab_name: 'raw_data_{cadence}' }

// Metric calculations (written to 'metrics_summary_{cadence}' tab)
metrics_output: {
  period_start: ISO8601,
  period_end: ISO8601,
  ticket_volume: int,
  avg_first_response_time_min: float,
  avg_resolution_time_min: float,
  csat_score_pct: float,
  sla_breach_count: int,
  sla_breach_rate_pct: float,
  volume_by_category: { [category_tag]: int },
  delta_vs_prior_period: { csat_score_pct: float, ticket_volume: int }
}

// Commentary generation prompt fields
ai_prompt_context: { metrics_output, prior_period_metrics, cadence, tone: 'concise and professional' }
commentary_output: { summary_text: string (2-3 sentences), anomalies_flagged: bool }

// On approval (monthly cadence only)
manager_review_gate: {
  status: 'pending' | 'approved' | 'edited_and_approved',
  reviewed_by: 'Support Manager',
  approved_commentary: string,
  approval_timestamp: ISO8601
}

// Output
reporting_agent_output: {
  metrics_summary: metrics_output,
  commentary: approved_commentary | commentary_output.summary_text,
  looker_studio_url: string,
  confirmation_flag: 'report_ready'
}

Takes the completed metrics summary and commentary produced by the Reporting Agent and delivers the finalised report across every configured channel: Gmail for stakeholder email, Slack for the real-time channel post, and Notion for the persistent reporting log. This agent handles three distinct write operations in sequence and logs the outcome of each. It replaces Steps 7, 8, and 9 entirely. Build complexity is Moderate: the logic is straightforward but three separate API integrations must be configured, templated, and tested independently, and each must handle a partial-failure scenario gracefully without re-sending to channels that already received the report.

// Input
trigger: { confirmation_flag: 'report_ready', cadence: 'daily' | 'weekly' | 'monthly' }
payload: {
  period_label: string,
  ticket_volume: int,
  avg_resolution_time_min: float,
  csat_score_pct: float,
  sla_breach_rate_pct: float,
  commentary: string,
  looker_studio_url: string,
  anomalies_flagged: bool
}

// Gmail send
gmail_send: {
  to: ['{DISTRIBUTION_LIST_EMAILS}'],
  subject: 'Support Report: {period_label}',
  body_template: 'email_report_{cadence}.html',
  fields_injected: [ period_label, ticket_volume, csat_score_pct,
                     avg_resolution_time_min, sla_breach_rate_pct,
                     commentary, looker_studio_url ]
}

// Slack post
slack_post: {
  channel_id: '{SLACK_SUPPORT_CHANNEL_ID}',
  blocks: [ header_block(period_label), metrics_block(ticket_volume, csat_score_pct,
            sla_breach_rate_pct), commentary_block(commentary),
            cta_block(looker_studio_url), alert_block(anomalies_flagged) ]
}

// Notion append
notion_append: {
  database_id: '{NOTION_REPORTING_LOG_DB_ID}',
  properties: {
    report_date: date,
    cadence: string,
    period_label: string,
    ticket_volume: number,
    csat_score_pct: number,
    sla_breach_rate_pct: number,
    looker_studio_url: url,
    sent_at: ISO8601
  }
}

// Output
distribution_log: {
  gmail_sent: bool,
  slack_posted: bool,
  notion_logged: bool,
  errors: [ { channel: string, error_code: string, timestamp: ISO8601 } ]
}

// ============================================================
// SUPPORT REPORTING AND INSIGHTS: END-TO-END DATA FLOW
// ============================================================

// TRIGGER
// Automation platform fires cron schedule
schedule_event = {
  cadence: 'daily' | 'weekly' | 'monthly',
  period_start: ISO8601,   // e.g. '2025-06-02T00:00:00Z'
  period_end:   ISO8601    // e.g. '2025-06-08T23:59:59Z'
}

// ============================================================
// AGENT 1: DATA FETCH AGENT
// ============================================================

// Step 1: Query Zendesk REST API v2
zendesk_request = {
  method: 'GET',
  endpoint: '/api/v2/search.json',
  params: {
    query:      'type:ticket created>={period_start} created<={period_end}',
    sort_by:    'created_at',
    sort_order: 'asc',
    per_page:   100
  },
  auth: 'Bearer {ZENDESK_API_TOKEN}'
}

// Step 2: Paginate until all results fetched
zendesk_raw_response = [
  { id, created_at, updated_at, status, assignee_id, tags[],
    custom_fields: { first_response_time_min, resolution_time_min,
                     csat_score, sla_breach }, via.channel }
]

// Step 3: Normalise and write to Google Sheets
// Target: Sheet ID = {SHEET_ID}, tab = 'raw_data_{cadence}'
sheets_write_payload = [
  { ticket_id, created_at, resolved_at, status, assignee_id,
    category_tag, csat_score, first_response_time_min,
    resolution_time_min, sla_breach: bool }
]
// write_mode: overwrite | confirmation_flag: 'data_fetch_complete'

// --- HANDOFF: Data Fetch Agent -> Reporting Agent ---
// Trigger: confirmation_flag == 'data_fetch_complete'

// ============================================================
// AGENT 2: REPORTING AGENT
// ============================================================

// Step 4: Read raw_data_{cadence} tab from Google Sheets
sheets_read = {
  sheet_id: '{SHEET_ID}',
  tab_name: 'raw_data_{cadence}',
  fields: [ ticket_id, created_at, resolved_at, status, assignee_id,
            category_tag, csat_score, first_response_time_min,
            resolution_time_min, sla_breach ]
}

// Step 5: Calculate metrics and write to metrics_summary_{cadence} tab
metrics_output = {
  period_start, period_end,
  ticket_volume:              COUNT(ticket_id),
  avg_first_response_time_min: AVG(first_response_time_min),
  avg_resolution_time_min:     AVG(resolution_time_min),
  csat_score_pct:             AVG(csat_score) * 100,
  sla_breach_count:           COUNT(sla_breach == true),
  sla_breach_rate_pct:        (sla_breach_count / ticket_volume) * 100,
  volume_by_category:         GROUP_BY(category_tag, COUNT),
  delta_vs_prior: {
    csat_score_pct:  metrics_output.csat_score_pct - prior_period.csat_score_pct,
    ticket_volume:   metrics_output.ticket_volume  - prior_period.ticket_volume
  }
}
// Append metrics_output to 'metrics_history' tab (one row per period)

// Step 6: Anomaly check
anomalies_flagged = (
  delta_vs_prior.csat_score_pct  < -10.0  ||
  (delta_vs_prior.ticket_volume / prior_period.ticket_volume) > 0.30
)

// Step 7: Generate AI commentary
ai_prompt = {
  system: 'You are a concise support analyst. Write 2-3 sentences.',
  user:   { metrics_output, prior_period_metrics, cadence, anomalies_flagged }
}
commentary_output = { summary_text: string, anomalies_flagged: bool }

// Step 8: Monthly gate (conditional branch)
IF cadence == 'monthly':
  manager_review_gate = {
    status: 'pending',
    draft_commentary: commentary_output.summary_text,
    timeout_hours: 24
  }
  // Automation pauses; Support Manager receives approval request
  // On approval: approved_commentary = manager_edited_or_confirmed_text
  // On timeout: log error to Supabase, halt, send alert
ELSE:
  approved_commentary = commentary_output.summary_text

// Looker Studio dashboard updates automatically via Sheets connector
looker_studio_url = '{LOOKER_STUDIO_REPORT_URL}'

// confirmation_flag: 'report_ready'

// --- HANDOFF: Reporting Agent -> Distribution Agent ---
// Trigger: confirmation_flag == 'report_ready'

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

// Step 9: Send Gmail
gmail_send = {
  to:       '{DISTRIBUTION_LIST_EMAILS}',
  subject:  'Support Report: {period_label}',
  template: 'email_report_{cadence}.html',
  inject:   { period_label, ticket_volume, csat_score_pct,
              avg_resolution_time_min, sla_breach_rate_pct,
              approved_commentary, looker_studio_url }
}

// Step 10: Post Slack message
slack_post = {
  channel_id: '{SLACK_SUPPORT_CHANNEL_ID}',
  blocks: [ header(period_label), metrics(ticket_volume, csat_score_pct,
            sla_breach_rate_pct), commentary(approved_commentary),
            cta(looker_studio_url),
            IF anomalies_flagged: alert_block() ]
}

// Step 11: Append Notion log entry
notion_append = {
  database_id:        '{NOTION_REPORTING_LOG_DB_ID}',
  report_date:        period_end,
  cadence:            cadence,
  period_label:       period_label,
  ticket_volume:      ticket_volume,
  csat_score_pct:     csat_score_pct,
  sla_breach_rate_pct: sla_breach_rate_pct,
  looker_studio_url:  looker_studio_url,
  sent_at:            NOW()
}

// Final output: distribution_log
distribution_log = {
  gmail_sent:    bool,
  slack_posted:  bool,
  notion_logged: bool,
  errors: [ { channel, error_code, timestamp } ]
}
// All errors written to Supabase error_log table
// ============================================================