Developer Handover Pack

Developer Handover Pack

Fulfilment Performance Reporting

Connects to Shopify, ShipStation, and Xero on the configured schedule and retrieves all order, shipment, and returns data for the closed reporting period without any manual exports. The agent fires from a time-based trigger, executes three sequential API calls (one per platform), normalises the field names returned by each source to a canonical schema, deduplicates on order_id and shipment_id, and writes the cleaned result set to the designated Google Sheet tab. If any single API call fails or returns zero records, the agent halts and raises an error alert before writing anything to the sheet, preventing a partial dataset from reaching the KPI calculation step.

// Input
reporting_period_start: ISO 8601 date string (e.g. '2024-06-10')
reporting_period_end:   ISO 8601 date string (e.g. '2024-06-16')
warehouse_scope:        string[] | 'all'  // channel filter

// Shopify Orders API call
GET /admin/api/2024-04/orders.json
  ?status=any&created_at_min={start}&created_at_max={end}&limit=250
  -> fields: order_id, created_at, fulfillment_status, fulfilled_at, line_items[sku, quantity]

// ShipStation Shipments API call
GET /shipments?createDateStart={start}&createDateEnd={end}&pageSize=500
  -> fields: shipmentId, orderId, shipDate, carrierCode, trackingNumber, shipmentStatusCode

// Xero Credit Notes API call
GET /api.xro/2.0/CreditNotes?where=Date>={start}&&Date<={end}&Type=ACCREC
  -> fields: creditNoteId, date, reference, total, currencyCode, lineItems[itemCode, quantity, unitAmount]

// Output
google_sheet_tab:       'raw_data_{YYYY-WW}'  // new tab per reporting period
columns_written:        [order_id, order_created_at, fulfillment_status, fulfilled_at,
                         sku, order_quantity, shipment_id, ship_date, carrier_code,
                         tracking_number, shipment_status, credit_note_id,
                         return_date, return_reference, return_total, return_qty]
row_count:              integer  // passed to next agent as confirmation
status:                 'success' | 'partial_failure' | 'error'
error_detail:           string | null

Fires immediately after the Data Collection Agent confirms the Google Sheet tab is populated with a non-zero row count and a status of 'success'. The agent reads the raw_data tab for the current period, calculates the four core KPIs (on-time dispatch rate, fill rate, average dispatch lead time, and returns rate) using pre-defined calculation logic written into the sheet's kpi_calc tab, then compares each result against the configured target thresholds. Any KPI outside threshold is flagged. An AI language model step then receives the KPI results and threshold breach flags and produces a plain-English variance commentary paragraph. The completed KPI table and commentary are written to the kpi_output tab. The agent then pauses at the human review gate: it sends the draft commentary to the ops manager via a review notification (Slack DM or email) and waits for an approval signal before passing control to the Report Distribution Agent. If no approval is received within 2 hours, the agent re-notifies once, then escalates to the configured fallback reviewer.

// Input
sheet_tab_name:         string  // e.g. 'raw_data_2024-24'
row_count:              integer // from Data Collection Agent
reporting_period_start: ISO 8601 date string
reporting_period_end:   ISO 8601 date string

// KPI calculation inputs (read from raw_data tab)
total_orders:           integer
fulfilled_on_time:      integer  // fulfilled_at <= promised_dispatch_date
total_units_ordered:    integer
total_units_shipped:    integer
dispatch_lead_times:    integer[]  // days from order_created_at to ship_date
total_return_value:     float
total_order_value:      float

// KPI targets (from environment config)
target_on_time_dispatch_pct:  float  // e.g. 0.95
target_fill_rate_pct:         float  // e.g. 0.98
target_avg_lead_time_days:    float  // e.g. 2.0
target_returns_rate_pct:      float  // e.g. 0.03

// Output (written to kpi_output tab and passed to next agent)
on_time_dispatch_rate:  float  // e.g. 0.921
fill_rate:              float  // e.g. 0.984
avg_dispatch_lead_time: float  // days
returns_rate:           float  // e.g. 0.027
breached_kpis:          string[]  // KPI names outside threshold
commentary_draft:       string  // plain-English paragraph from AI step
review_status:          'pending' | 'approved' | 'edited_and_approved'

// On approval
approved_commentary:    string  // final text after ops manager review
approved_by:            string  // reviewer name or email
approved_at:            ISO 8601 datetime

Fires after the KPI and Commentary Agent emits an approved_commentary value and review_status of 'approved' or 'edited_and_approved'. The agent formats the KPI table and commentary into a structured Slack message and posts it to the configured operations channel. It then triggers a PDF export of the kpi_output tab via the Google Sheets API, attaches the PDF to a Gmail message, and sends it to all addresses in the REPORT_RECIPIENTS list. Finally, it appends a summary row to the historical_log tab, recording the period dates, all four KPI values, breach flags, the approved commentary, and a timestamp. No human action is required at this stage.

// Input
on_time_dispatch_rate:  float
fill_rate:              float
avg_dispatch_lead_time: float
returns_rate:           float
breached_kpis:          string[]
approved_commentary:    string
approved_by:            string
approved_at:            ISO 8601 datetime
reporting_period_start: ISO 8601 date string
reporting_period_end:   ISO 8601 date string
sheet_tab_name:         string

// Slack output
slack_channel:          string  // from env SLACK_OPS_CHANNEL_ID
slack_message_format:   'blocks'  // Slack Block Kit JSON
slack_post_ts:          string  // returned timestamp from Slack API

// Gmail output
pdf_export_url:         string  // Google Sheets export URL with format=pdf
email_recipients:       string[]  // from env REPORT_RECIPIENTS
email_subject:          'Fulfilment KPI Report: {period_start} to {period_end}'
email_body:             string  // includes KPI table as plain text + commentary
gmail_message_id:       string  // returned by Gmail API

// Historical log append (historical_log tab)
log_columns_written:    [log_date, period_start, period_end, on_time_dispatch_rate,
                         fill_rate, avg_dispatch_lead_time, returns_rate,
                         breached_kpis, approved_commentary, approved_by,
                         slack_post_ts, gmail_message_id]

// Output
distribution_status:    'success' | 'partial_failure' | 'error'
error_detail:           string | null

// ============================================================
// TRIGGER
// ============================================================
SCHEDULE_TRIGGER fires at cron '0 7 * * 1' (Monday 07:00)
  -> emits: { reporting_period_start, reporting_period_end, warehouse_scope }
  -> derived from: REPORTING_CADENCE env var + current date at trigger time

// ============================================================
// AGENT 1: Data Collection Agent
// ============================================================

// Step 1: Shopify Orders API
GET /admin/api/2024-04/orders.json
  params: { status: 'any', created_at_min: reporting_period_start,
            created_at_max: reporting_period_end, limit: 250 }
  headers: { X-Shopify-Access-Token: SHOPIFY_ACCESS_TOKEN }
  -> raw fields: id (->order_id), created_at (->order_created_at),
                 fulfillment_status, fulfilled_at,
                 line_items[sku, quantity (->order_quantity)]
  -> paginate using Link header until no 'next' rel present

// Step 2: ShipStation Shipments API
GET /shipments
  params: { createDateStart: reporting_period_start,
            createDateEnd: reporting_period_end, pageSize: 500, page: 1 }
  headers: { Authorization: Basic base64(SS_API_KEY:SS_API_SECRET) }
  -> raw fields: shipmentId, orderId, shipDate (->ship_date),
                 carrierCode (->carrier_code), trackingNumber (->tracking_number),
                 shipmentStatus (->shipment_status)
  -> paginate using pages field in response until page == pages

// Step 3: Xero Credit Notes API
GET /api.xro/2.0/CreditNotes
  params: { where: 'Date>=DateTime(Y,M,D)&&Date<=DateTime(Y,M,D)&&Type="ACCREC"' }
  headers: { Authorization: Bearer XERO_ACCESS_TOKEN }
  -> on 401: refresh token via POST /identity/connect/token
  -> raw fields: CreditNoteID (->credit_note_id), Date (->return_date),
                 Reference (->return_reference), Total (->return_total),
                 CurrencyCode, LineItems[ItemCode (->item_code),
                 Quantity (->return_qty), UnitAmount]

// Step 4: Deduplicate and normalise
  merge_key_shopify:    order_id
  merge_key_shipstation: shipmentId
  merge_key_xero:        credit_note_id
  join: Shopify.order_id LEFT JOIN ShipStation.orderId
        -> matched rows carry both order and shipment fields
  append: Xero credit note rows independently (keyed by return_date and credit_note_id)

// Step 5: Write to Google Sheets
Sheets API: spreadsheets.values.update
  range: '{raw_data_YYYY-WW}!A1'
  valueInputOption: 'RAW'
  body: { values: [[order_id, order_created_at, fulfillment_status, fulfilled_at,
                    sku, order_quantity, shipment_id, ship_date, carrier_code,
                    tracking_number, shipment_status, credit_note_id,
                    return_date, return_reference, return_total, return_qty]] }
  -> emits to Agent 2: { sheet_tab_name, row_count, status: 'success' }

// -- AGENT 1 -> AGENT 2 HANDOFF --
// Payload: { sheet_tab_name, row_count, reporting_period_start,
//            reporting_period_end, status }

// ============================================================
// AGENT 2: KPI and Commentary Agent
// ============================================================

// Step 6: Read raw_data tab and kpi_calc tab
Sheets API: spreadsheets.values.get
  ranges: ['{raw_data_YYYY-WW}!A:P', 'kpi_calc!A:Z', 'config.kpi_targets!A:B',
           'historical_log!A:N']  // last row for prior-period comparison
  -> derive:
       total_orders          = COUNT(DISTINCT order_id)
       fulfilled_on_time     = COUNT WHERE fulfilled_at <= promised_dispatch_date
       on_time_dispatch_rate = fulfilled_on_time / total_orders
       total_units_ordered   = SUM(order_quantity)
       total_units_shipped   = SUM(order_quantity WHERE shipment_status='shipped')
       fill_rate             = total_units_shipped / total_units_ordered
       dispatch_lead_times   = [ship_date - order_created_at] per order (days)
       avg_dispatch_lead_time= MEAN(dispatch_lead_times)
       total_return_value    = SUM(return_total)
       total_order_value     = SUM(order line item values from Shopify)
       returns_rate          = total_return_value / total_order_value

// Step 7: Compare KPIs against targets
  breach check:
    on_time_dispatch_rate < target_on_time_dispatch_pct -> flag
    fill_rate             < target_fill_rate_pct        -> flag
    avg_dispatch_lead_time > target_avg_lead_time_days  -> flag
    returns_rate           > target_returns_rate_pct    -> flag
  -> breached_kpis: string[]

// Step 8: AI commentary call
POST /v1/chat/completions  // language model endpoint
  body: {
    model: LLM_MODEL_ENV_VAR,
    messages: [
      { role: 'system', content: COMMENTARY_SYSTEM_PROMPT },
      { role: 'user',   content: JSON.stringify({
          period: { start, end },
          kpis: { on_time_dispatch_rate, fill_rate,
                  avg_dispatch_lead_time, returns_rate },
          targets: { target_on_time_dispatch_pct, target_fill_rate_pct,
                     target_avg_lead_time_days, target_returns_rate_pct },
          breached_kpis,
          prior_period_kpis: { ... }  // from historical_log last row
        })
      }
    ],
    max_tokens: 250
  }
  -> commentary_draft: string

// Step 9: Human review gate
Slack API: chat.postMessage
  channel: REVIEWER_SLACK_USER_ID  // DM
  blocks: [KPI table, commentary_draft, [Approve] [Edit] buttons]
  -> wait for interactive callback on /slack/actions webhook
  -> on 'approve':      review_status = 'approved'
                        approved_commentary = commentary_draft
  -> on 'edit':         open modal, accept edited_text
                        review_status = 'edited_and_approved'
                        approved_commentary = edited_text
  -> on timeout 2h:     re-notify REVIEWER_SLACK_USER_ID
  -> on timeout 4h:     Gmail escalation to FALLBACK_REVIEWER_EMAIL

// Write KPI results to kpi_output tab
Sheets API: spreadsheets.values.append
  range: 'kpi_output!A:N'
  -> row: [period_start, period_end, on_time_dispatch_rate, fill_rate,
           avg_dispatch_lead_time, returns_rate, breached_kpis (comma-joined),
           commentary_draft, review_status, approved_commentary,
           approved_by, approved_at]

// -- AGENT 2 -> AGENT 3 HANDOFF --
// Payload: { on_time_dispatch_rate, fill_rate, avg_dispatch_lead_time,
//            returns_rate, breached_kpis, approved_commentary,
//            approved_by, approved_at, reporting_period_start,
//            reporting_period_end, sheet_tab_name }

// ============================================================
// AGENT 3: Report Distribution Agent
// ============================================================

// Step 10: Slack channel post
Slack API: chat.postMessage
  channel: SLACK_OPS_CHANNEL_ID
  blocks: Block Kit JSON {
    header:  'Fulfilment KPI Report: {period_start} to {period_end}',
    section: on_time_dispatch_rate vs target (green/red circle),
    section: fill_rate vs target,
    section: avg_dispatch_lead_time vs target,
    section: returns_rate vs target,
    context: approved_commentary
  }
  -> slack_post_ts: string (returned by API)

// Step 11: PDF export
GET https://docs.google.com/spreadsheets/d/{SHEET_ID}/export
  params: { format: 'pdf', gid: PDF_EXPORT_GID, portrait: true,
            fitw: true, top_margin: 0.5, bottom_margin: 0.5 }
  -> pdf_bytes: binary

// Step 12: Gmail send
Gmail API: users.messages.send
  to:      REPORT_RECIPIENTS (comma-separated env var)
  subject: 'Fulfilment KPI Report: {period_start} to {period_end}'
  body:    plain-text KPI table + approved_commentary
  attach:  pdf_bytes as 'fulfilment_report_{period_start}_{period_end}.pdf'
  -> gmail_message_id: string

// Step 13: Historical log append
Sheets API: spreadsheets.values.append
  range: 'historical_log!A:N'
  insertDataOption: 'INSERT_ROWS'
  -> row: [log_date (now()), period_start, period_end, on_time_dispatch_rate,
           fill_rate, avg_dispatch_lead_time, returns_rate,
           breached_kpis (comma-joined), approved_commentary,
           approved_by, approved_at, slack_post_ts, gmail_message_id,
           distribution_status]

// ============================================================
// END OF RUN
// ============================================================
distribution_status: 'success'
-> error log row written to Supabase errors table only if any step
   returned a non-success status