Integration and API Spec

Integration and API Spec

Vendor Performance Tracking

Primary data source for invoice records and purchase order status. The automation connects to Xero via OAuth 2.0 with PKCE to read invoice and PO data on each trigger event. No write operations are performed against Xero.

// Input
Webhook POST or poll response: Xero invoice or PO event payload

// Key fields extracted
InvoiceID, Contact.ContactID, Contact.Name (vendor name)
InvoiceNumber, DateString, DueDateString
AmountDue, AmountPaid, SubTotal, TotalTax
LineItems[].Quantity, LineItems[].UnitAmount, LineItems[].Description
PurchaseOrderID (linked via Invoice.PurchaseOrders[].PurchaseOrderID)
PurchaseOrder.DeliveryDate, PurchaseOrder.Status

// Output (passed to Agent 1 internal record)
Structured vendor event object: see Section 03 field mapping

Central vendor record store and event log. Agent 1 writes new event records; Agent 2 updates scores and flags; Agent 3 reads event history to assemble the monthly report. All Airtable access uses a single Personal Access Token scoped to the relevant base.

// Input (Agent 1 write)
POST /v0/{baseId}/{eventsTableId}
Body: { fields: { VendorID: [recXXXX], EventDate, DeliveryVarianceDays,
        InvoiceAccuracyPct, BilledAmount, OrderedAmount, EventNotes } }

// Input (Agent 2 update)
PATCH /v0/{baseId}/{vendorsTableId}/{recordId}
Body: { fields: { CurrentScore, FlagStatus, PerformanceSummary, LastEventDate } }

// Output (Agent 3 read for monthly report)
GET /v0/{baseId}/{eventsTableId}?filterByFormula=...
Returns array of event records for the current month

Hosts the master vendor scorecard and receives the monthly summary report. Agent 2 writes flag status per vendor row; Agent 3 appends monthly report rows at end-of-month. Access uses a Google service account with the Sheets API enabled.

// Scorecard update (Agent 2)
PUT /v4/spreadsheets/{spreadsheetId}/values/{range}
Range: VendorScorecard!C{rowNum}:E{rowNum}
Values: [[CurrentScore, FlagStatus, LastUpdated]]

// Monthly report append (Agent 3)
POST /v4/spreadsheets/{spreadsheetId}/values/{range}:append
Range: MonthlyReport!A:G
Values: [[ReportMonth, VendorID, VendorName, AvgScore, FlagCount, Resolution, GeneratedAt]]

Receives flagged-vendor alerts from Agent 3 posted to the ops channel. Uses a Slack Bot Token installed into the workspace. No interactive components are required at current scope; the approval step is handled outside Slack.

// Slack alert payload (Agent 3 output)
POST https://slack.com/api/chat.postMessage
Headers: { Authorization: 'Bearer {SLACK_BOT_TOKEN}' }
Body: {
  channel: '{SLACK_OPS_CHANNEL_ID}',
  text: 'Vendor flagged: {VendorName} | Score: {CurrentScore} (threshold: {ScoreThreshold})',
  blocks: [
    { type: 'section', text: { type: 'mrkdwn',
      text: '*{VendorName}* scored *{CurrentScore}* on {EventDate}\n
             Reason: {EventType}\n<{AirtableRecordURL}|View vendor record>' } }
  ]
}

Sends vendor escalation emails after the ops manager approves the draft. Agent 3 composes the message using a stored template and sends it via the Gmail API on behalf of the ops manager's Google Workspace account.

// Gmail send payload (Agent 3 output, post-approval only)
POST https://gmail.googleapis.com/gmail/v1/users/me/messages/send
Headers: { Authorization: 'Bearer {access_token}' }
Body: {
  raw: base64url(RFC2822 message string)
}

// RFC2822 fields
From: {GMAIL_SENDER_ADDRESS}
To: {VendorContactEmail}  // sourced from Airtable Vendors record
Subject: {GMAIL_TEMPLATE_SUBJECT} (populated with VendorName)
Content-Type: text/plain; charset=UTF-8
Body: rendered template with {VendorName}, {EventSummary}, {ResponseDeadlineDays}

// CREDENTIAL STORE CONTENTS
// Xero
XERO_CLIENT_ID           = '<Xero app client ID>'
XERO_CLIENT_SECRET       = '<Xero app client secret>'
XERO_TENANT_ID           = '<Xero organisation tenant ID>'
XERO_WEBHOOK_KEY         = '<Xero webhook signing key>'
XERO_REFRESH_TOKEN       = '<persisted OAuth refresh token>'

// Airtable
AIRTABLE_PAT             = '<Airtable Personal Access Token>'
AIRTABLE_BASE_ID         = '<base ID, format: appXXXXXXXXXXXXXX>'
AIRTABLE_VENDORS_TABLE_ID   = '<table ID for Vendors table>'
AIRTABLE_EVENTS_TABLE_ID    = '<table ID for VendorEvents table>'
AIRTABLE_APPROVAL_TABLE_ID  = '<table ID for Approvals table>'

// Google (shared service account for Sheets and Gmail)
GOOGLE_SA_KEY            = '<service account JSON key, base64-encoded>'
GMAIL_SENDER_ADDRESS     = '<ops manager email address>'
GMAIL_REFRESH_TOKEN      = '<OAuth refresh token for Gmail send, if not using SA delegation>'
SHEETS_SCORECARD_ID      = '<Google Sheets spreadsheet ID>'
SHEETS_SCORECARD_TAB     = 'VendorScorecard'
SHEETS_REPORT_TAB        = 'MonthlyReport'

// Slack
SLACK_BOT_TOKEN          = '<xoxb-... bot token>'
SLACK_OPS_CHANNEL_ID     = '<channel ID, format: CXXXXXXXXXX>'

// Gmail template config
GMAIL_TEMPLATE_SUBJECT   = 'Performance Notice: {VendorName}'
GMAIL_TEMPLATE_BODY_ID   = '<key referencing approved body template in config>'

// Scoring config
SCORE_THRESHOLD_FLAG     = '<numeric threshold below which vendor is flagged, e.g. 60>'
SCORE_THRESHOLD_CRITICAL = '<numeric threshold for critical flag, e.g. 40>'
APPROVAL_EXPIRY_HOURS    = '48'

// Processing log
PROCESSED_EVENTS_TABLE_ID = '<table ID for deduplication log>'