Developer Handover Pack

Developer Handover Pack

Vendor Performance Tracking

Pulls invoice and delivery data from Xero on each trigger event, cross-references purchase orders, and extracts the fields needed for scoring without any manual export. This agent fires on every new Xero invoice record or PO status change to received, making it the entry point for the entire automation chain. It is responsible for data completeness: if a required field is missing or a PO cannot be matched, the agent must park the record and raise an alert rather than pass incomplete data downstream.

// Input
Xero webhook payload OR poll result {
  invoice_id: string,
  invoice_number: string,
  contact_id: string,          // maps to vendor_id in Airtable
  contact_name: string,
  invoice_date: ISO8601,
  due_date: ISO8601,
  total_amount: float,
  line_items: [{ description, quantity, unit_amount, account_code }],
  po_reference: string,        // must match PO number in Xero
  status: 'AUTHORISED' | 'PAID' | 'DRAFT'
}

// PO cross-reference lookup (Xero PurchaseOrders endpoint)
purchase_order {
  po_number: string,
  expected_delivery_date: ISO8601,
  actual_delivery_date: ISO8601 | null,
  ordered_amount: float,
  ordered_line_items: [{ description, quantity, unit_amount }]
}

// Output — structured vendor event record written to Airtable
vendor_event_record {
  vendor_id: string,            // Airtable Vendors table record ID
  vendor_name: string,
  event_date: ISO8601,
  invoice_number: string,
  billed_amount: float,
  ordered_amount: float,
  invoice_variance: float,      // billed_amount minus ordered_amount
  expected_delivery_date: ISO8601,
  actual_delivery_date: ISO8601 | null,
  delivery_date_variance_days: integer | null,
  data_complete: boolean,       // false triggers a parse-error alert
  raw_xero_invoice_id: string
}

Applies a configurable scoring model to each vendor event record created by the Data Collection Agent, identifies threshold breaches, and writes a plain-English performance summary to the vendor's Airtable record and the Google Sheets scorecard. Scoring weights and threshold values must be confirmed by the ops manager before this agent is built. The agent treats null delivery_date_variance_days as a neutral score contribution (zero penalty, zero bonus) rather than an error, to avoid penalising vendors for delivery records still in transit.

// Input — vendor_event_record from Airtable
vendor_event_record {
  vendor_id: string,
  vendor_name: string,
  invoice_variance: float,
  delivery_date_variance_days: integer | null,
  data_complete: boolean
}

// Scoring model (configurable — values to be confirmed with ops manager)
scoring_weights {
  on_time_delivery:   40,       // percent of total score
  invoice_accuracy:   40,
  quality_flags:      20
}
thresholds {
  flag_below_score:   65,       // vendor flagged if rolling 90-day score < 65
  invoice_variance_pct_cap: 5,  // >5% variance = full penalty on invoice_accuracy
  late_days_cap: 3              // >3 days late = full penalty on on_time_delivery
}

// Output — written to Airtable Vendors table and Google Sheets scorecard
scoring_output {
  vendor_id: string,
  event_score: integer,         // 0 to 100 for this single event
  rolling_score_90d: float,     // recalculated from last 90 days of events
  flagged: boolean,             // true if rolling_score_90d < threshold
  performance_summary: string,  // plain-English paragraph, max 120 words
  scorecard_row: {
    vendor_name, event_date, event_score, rolling_score_90d, flagged
  }
}

Handles all downstream communication and reporting: sending Slack alerts when a vendor is flagged, drafting vendor emails for manager approval via Gmail, and assembling the monthly review report from Airtable data into Google Sheets. This agent has two distinct trigger paths: the real-time escalation path fires whenever the Scoring Agent sets flagged to true on a vendor record; the monthly reporting path fires on a scheduled trigger on the last business day of each calendar month. Both paths share Airtable as their data source but write to different outputs.

// Input — Path A (escalation)
flagged_vendor {
  vendor_id: string,
  vendor_name: string,
  rolling_score_90d: float,
  performance_summary: string,
  last_event_date: ISO8601,
  flagged: true
}

// Input — Path B (monthly report)
report_payload {
  report_month: string,         // e.g. 'January 2025'
  vendor_events: [ vendor_event_record ],   // all events this month
  vendor_scores: [ { vendor_id, vendor_name, rolling_score_90d, flagged } ]
}

// Output — Path A: Slack alert
slack_message {
  channel: '#ops-alerts',
  text: 'Vendor [vendor_name] flagged. 90-day score: [rolling_score_90d]. Last event: [last_event_date]. Summary: [performance_summary]'
}

// Output — Path A: Gmail draft awaiting approval
gmail_draft {
  to: vendor_email,             // sourced from Airtable Vendors.ContactEmail
  subject: 'Performance Review Notice — [vendor_name]',
  body: populated from approved template,
  draft_id: string              // stored in Airtable for manager retrieval
}

// On approval — ops manager confirms via Slack interactive button or email reply
approval_action {
  approved: boolean,
  manager_name: string,
  approval_timestamp: ISO8601
}
// If approved: Gmail draft is sent. If rejected: draft is deleted, Airtable note updated.

// Output — Path B: Google Sheets monthly report row
monthly_report_row {
  report_month, vendor_name, event_count, avg_event_score,
  rolling_score_90d, flagged_this_month: boolean, escalation_sent: boolean
}

// ============================================================
// VENDOR PERFORMANCE TRACKING — END-TO-END DATA FLOW
// Volume: ~40 vendor events/month | Runs this month: 220
// ============================================================

// TRIGGER
// Xero fires webhook (or poll detects change) on:
//   - New invoice with status AUTHORISED
//   - PurchaseOrder.DeliveryDate populated (PO marked received)
Xero.invoice_created | Xero.po_received
  -> payload: { invoice_id, invoice_number, contact_id, contact_name,
                invoice_date, due_date, total_amount, po_reference,
                line_items[], status }

// ============================================================
// AGENT HANDOFF 1: Trigger -> Vendor Data Collection Agent
// ============================================================

VendorDataCollectionAgent.run(invoice_id, po_reference)
  // Step 1: Fetch invoice detail
  Xero.GET /invoices/{invoice_id}
    -> { invoice_number, contact_id, total_amount, line_items[],
         due_date, invoice_date }

  // Step 2: Cross-reference purchase order
  Xero.GET /purchaseorders?PurchaseOrderNumber={po_reference}
    -> { po_number, expected_delivery_date, actual_delivery_date,
         ordered_amount, ordered_line_items[] }

  // Step 3: Derive variance fields
  invoice_variance         = total_amount - ordered_amount
  delivery_date_variance   = actual_delivery_date - expected_delivery_date (days)
                             // null if actual_delivery_date not yet set

  // Step 4: Resolve Airtable vendor_id
  Airtable.GET Vendors WHERE XeroContactID = contact_id
    -> { vendor_id, vendor_name }

  // Step 5: Dedupe check
  Airtable.GET VendorEvents WHERE invoice_number = invoice_number
    -> if record exists: HALT, log 'DUPLICATE_SKIPPED'

  // Step 6: Write vendor event record
  Airtable.POST VendorEvents {
    vendor_id, vendor_name, event_date, invoice_number,
    billed_amount: total_amount, ordered_amount,
    invoice_variance, expected_delivery_date, actual_delivery_date,
    delivery_date_variance_days, data_complete, raw_xero_invoice_id
  }
    -> new_record_id: string

// ============================================================
// AGENT HANDOFF 2: VendorEvents record created -> Performance Scoring Agent
// ============================================================

PerformanceScoringAgent.run(new_record_id)
  // Step 1: Read event record
  Airtable.GET VendorEvents/{new_record_id}
    -> { vendor_id, invoice_variance, delivery_date_variance_days,
         data_complete }

  // Guard: if data_complete == false -> log 'SCORING_SKIPPED', HALT

  // Step 2: Read scoring config
  Airtable.GET ScoringConfig[0]
    -> { flag_below_score, invoice_variance_pct_cap, late_days_cap,
         weight_on_time, weight_invoice_accuracy, weight_quality }

  // Step 3: Calculate event_score (0–100)
  on_time_score      = score_delivery(delivery_date_variance_days, late_days_cap)
  invoice_score      = score_invoice(invoice_variance / ordered_amount, variance_pct_cap)
  quality_score      = Airtable.GET VendorEvents.quality_flags -> score_quality()
  event_score        = (on_time_score * weight_on_time)
                     + (invoice_score * weight_invoice_accuracy)
                     + (quality_score * weight_quality)

  // Step 4: Calculate rolling 90-day score
  Airtable.GET VendorEvents
    WHERE vendor_id = vendor_id
    AND event_date >= TODAY - 90 days
    -> [event_score_list]
  rolling_score_90d  = AVERAGE(event_score_list)   // min 3 records to set flagged

  // Step 5: Determine flag status
  flagged = (COUNT(event_score_list) >= 3) AND (rolling_score_90d < flag_below_score)

  // Step 6: Generate plain-English summary (template fill)
  performance_summary = build_summary(vendor_name, rolling_score_90d,
                                      on_time_score, invoice_score)

  // Step 7: Write score back to Airtable
  Airtable.PATCH Vendors/{vendor_id} {
    rolling_score_90d, flagged, last_event_date: event_date,
    performance_summary
  }
  Airtable.PATCH VendorEvents/{new_record_id} { event_score }

  // Step 8: Append row to Google Sheets scorecard
  GoogleSheets.appendRow('VendorScorecard') {
    vendor_name, event_date, event_score, rolling_score_90d, flagged
  }

// ============================================================
// AGENT HANDOFF 3A: flagged == true -> Escalation and Reporting Agent (Real-time path)
// ============================================================

EscalationReportingAgent.escalate(vendor_id)
  // Guard: check Airtable AlertLog WHERE vendor_id AND alert_date >= TODAY - 7 days
  //   -> if exists: log 'ALERT_SUPPRESSED', HALT

  // Step 1: Post Slack alert
  Slack.postMessage(channel='#ops-alerts') {
    text: 'Vendor [vendor_name] flagged. Score: [rolling_score_90d]. [performance_summary]'
    // Include: approve/reject interactive buttons if Slack app configured
  }

  // Step 2: Create Gmail draft
  Gmail.createDraft {
    to: Airtable.Vendors.ContactEmail,
    subject: 'Performance Review Notice — [vendor_name]',
    body: Airtable.EmailTemplates['vendor_escalation'].body
           with { vendor_name, rolling_score_90d, performance_summary,
                  response_deadline: TODAY + 5 business days }
  }
    -> draft_id: string

  // Step 3: Store draft reference
  Airtable.PATCH Vendors/{vendor_id} { pending_draft_id: draft_id }
  Airtable.POST AlertLog { vendor_id, alert_date: TODAY, draft_id }

// ============================================================
// HUMAN GATE: Ops Manager reviews Slack alert, approves or rejects
// ============================================================

OpsManager.decision(approval_action)
  -> { approved: boolean, manager_name, approval_timestamp }

  if approved:
    Gmail.sendDraft(draft_id)
    Airtable.PATCH Vendors/{vendor_id} {
      escalation_sent: true, escalation_date: approval_timestamp,
      escalation_approved_by: manager_name
    }
  else:
    Gmail.deleteDraft(draft_id)
    Airtable.PATCH Vendors/{vendor_id} {
      escalation_sent: false, escalation_rejected_reason: 'manager_declined'
    }

// ============================================================
// AGENT HANDOFF 3B: Scheduled trigger -> Escalation and Reporting Agent (Monthly path)
// ============================================================

EscalationReportingAgent.generateMonthlyReport(report_month)
  // Step 1: Fetch all events for the month
  Airtable.GET VendorEvents
    WHERE event_date >= FIRST_DAY(report_month)
    AND event_date <= LAST_DAY(report_month)
    -> [vendor_event_list]

  // Step 2: Aggregate per vendor
  for each vendor_id in DISTINCT(vendor_event_list.vendor_id):
    event_count         = COUNT(events for vendor)
    avg_event_score     = AVERAGE(event_score for vendor)
    rolling_score_90d   = Airtable.Vendors[vendor_id].rolling_score_90d
    flagged_this_month  = ANY(flagged == true in events for vendor)
    escalation_sent     = Airtable.Vendors[vendor_id].escalation_sent

  // Step 3: Append summary rows to Google Sheets
  GoogleSheets.appendRows('MonthlyReport') {
    report_month, vendor_name, event_count, avg_event_score,
    rolling_score_90d, flagged_this_month, escalation_sent
  }

// ============================================================
// END OF FLOW
// ============================================================