Developer Handover Pack

Developer Handover Pack

Vendor and Subscription Renewal Tracking

Runs daily on a scheduled trigger set at 07:00 AM in the automation platform. Reads every active row from the Google Sheets subscription register and evaluates whether each contract's renewal date falls within the 60-day, 30-day, or 7-day alert window. Checks whether a decision has already been recorded in the Decision Status column to avoid re-alerting on resolved contracts. Categorises each in-window contract by urgency level (High for 7-day, Medium for 30-day, Low for 60-day) and appends a ShortNotice flag where the vendor notice period is shorter than the remaining days before renewal. Outputs a structured list for the Stakeholder Notification Agent. This is a Moderate complexity agent due to the conditional window logic, the dedupe check against existing decisions, and the Notion read used to cross-reference vendor page data.

// Input
Google Sheets row fields: vendor_name (string), renewal_date (ISO 8601 date), annual_cost (float),
  notice_period_days (integer), auto_renew (boolean), decision_status (string: blank | 'Renew' | 'Cancel'),
  contract_owner_slack_id (string), contract_owner_email (string), finance_contact_email (string),
  notion_page_id (string)

// Output
Array of triage objects: [
  {
    vendor_name: string,
    renewal_date: ISO 8601 date,
    annual_cost: float,
    days_until_renewal: integer,
    urgency_level: 'High' | 'Medium' | 'Low',
    short_notice_flag: boolean,
    decision_status: string,
    contract_owner_slack_id: string,
    contract_owner_email: string,
    finance_contact_email: string,
    notion_page_id: string,
    sheet_row_index: integer
  }
]
// Contracts where decision_status is already 'Renew' or 'Cancel' are excluded from output.

Receives the triage output list from the Renewal Triage Agent and composes two sets of outbound messages. First, it sends an individual Slack direct message to each unique contract_owner_slack_id for every contract assigned to them that is in an alert window. The Slack message is structured to include vendor name, renewal date, annual cost, urgency level, any ShortNotice warning, and a plain-text prompt to log the decision in the register. Second, it sends a single consolidated Gmail summary to the finance contact, grouping all contracts due within the next 30 days by urgency and listing expected charges with a direct link to the live Google Sheet. If no contracts are in the triage output, the agent exits without sending any messages. This is a Simple complexity agent: the logic is linear once the triage list is received, and both Slack and Gmail have stable API surfaces.

// Input
Triage list from Renewal Triage Agent (array of triage objects as defined above)
Finance contact email: finance_contact_email (string, pulled from first triage object or config)
Google Sheets public URL: register_url (string, stored in workflow config)

// Output (Slack)
Slack DM per contract owner: {
  recipient: contract_owner_slack_id,
  message_blocks: [
    header: '{vendor_name} renewal due in {days_until_renewal} days',
    fields: ['Renewal date', 'Annual cost', 'Urgency', 'Short notice warning (if flag true)'],
    action_prompt: 'Log your decision (Renew or Cancel) in the register: {register_url}'
  ]
}

// Output (Gmail)
Single email to finance_contact_email: {
  subject: 'Upcoming subscription renewals: {N} contracts due in next 30 days',
  body: grouped table of vendor_name, renewal_date, annual_cost, urgency_level,
  footer: register_url
}

// Note: contracts with urgency_level 'Low' (60-day window) are included in Gmail summary
// but excluded from Slack DM unless short_notice_flag is true.

Watches the Google Sheets register for rows where the Decision Status column transitions to 'Renew' (written by the contract owner). On detecting a confirmed renewal, it creates a draft bill in Xero pre-populated with the vendor name mapped to the matching Xero supplier contact, the annual cost as the bill amount, and the renewal date as the due date. The bill is created in Draft status only. It does not submit, approve, or schedule payment. After creating the Xero bill, the agent writes the Xero bill ID and a 'Bill Created' timestamp back to the Google Sheet row so the register reflects the action taken. This is a Moderate complexity agent due to the Xero supplier lookup, the conditional trigger on cell value change, and the write-back to Google Sheets.

// Input
From Google Sheets confirmed renewal row: {
  vendor_name: string,
  annual_cost: float,
  renewal_date: ISO 8601 date,
  sheet_row_index: integer,
  decision_status: 'Renew'
}

// Xero supplier lookup
GET /Contacts?where=Name=={vendor_name}&IsSupplier=true
Returns: { ContactID: string, Name: string } or empty array
If empty: log error, skip bill creation, alert finance_contact_email via Gmail fallback

// Output (Xero)
POST /Invoices (Type: ACCPAY) {
  Type: 'ACCPAY',
  Status: 'DRAFT',
  Contact: { ContactID: <looked-up ContactID> },
  DueDate: renewal_date,
  LineItems: [{ Description: vendor_name + ' renewal', Quantity: 1, UnitAmount: annual_cost }]
}
Returns: { InvoiceID: string, InvoiceNumber: string, Status: 'DRAFT' }

// Write-back to Google Sheets
Update row at sheet_row_index: {
  xero_bill_id: InvoiceID,
  bill_created_at: ISO 8601 datetime
}

// On approval (manual, finance officer only)
Finance officer reviews draft bill in Xero and approves or deletes it manually.
No automation action is taken after bill creation.

// ============================================================
// VENDOR AND SUBSCRIPTION RENEWAL TRACKING
// End-to-end data flow: trigger to final output
// ============================================================

// TRIGGER
// Automation platform scheduler fires daily at 07:00 AM
trigger: {
  type: 'schedule',
  time: '07:00',
  timezone: 'business_timezone_from_config'
}

// ============================================================
// STEP 1: Read subscription register from Google Sheets
// ============================================================
google_sheets.getRows({
  spreadsheet_id: config.REGISTER_SPREADSHEET_ID,
  sheet_name: 'Active Contracts',
  fields: [
    'vendor_name', 'renewal_date', 'annual_cost',
    'notice_period_days', 'auto_renew', 'decision_status',
    'contract_owner_slack_id', 'contract_owner_email',
    'finance_contact_email', 'notion_page_id', 'xero_bill_id'
  ]
})
// Returns: rows[] where each row maps to one active contract

// ============================================================
// AGENT HANDOFF 1: Raw register rows -> Renewal Triage Agent
// ============================================================

// RENEWAL TRIAGE AGENT
// For each row, compute days_until_renewal = renewal_date - today()
for each row in rows:
  days_until_renewal = dateDiff(today(), row.renewal_date)  // integer, days

  // Determine urgency window
  if days_until_renewal <= 7:  urgency_level = 'High'
  elif days_until_renewal <= 30: urgency_level = 'Medium'
  elif days_until_renewal <= 60: urgency_level = 'Low'
  else: skip row  // outside alert window

  // Dedupe check: skip if decision already recorded
  if row.decision_status in ['Renew', 'Cancel']: skip row

  // Short notice flag
  short_notice_flag = (days_until_renewal <= row.notice_period_days)

  // Cross-reference Notion for notice period confirmation
  notion.getPage({ page_id: row.notion_page_id })
  // Returns: { notice_period_days (if overrides sheet value), last_updated }

  // Append to triage_list
  triage_list.append({
    vendor_name, renewal_date, annual_cost,
    days_until_renewal, urgency_level, short_notice_flag,
    decision_status, contract_owner_slack_id,
    contract_owner_email, finance_contact_email,
    notion_page_id, sheet_row_index
  })

// ============================================================
// AGENT HANDOFF 2: triage_list -> Stakeholder Notification Agent
// ============================================================

if triage_list.length == 0:
  log('no-op: no contracts in alert window')
  exit

// STAKEHOLDER NOTIFICATION AGENT
// Group triage_list by contract_owner_slack_id
owner_groups = groupBy(triage_list, 'contract_owner_slack_id')

for each owner_slack_id, contracts in owner_groups:
  // Send Slack DM per contract owner
  slack.postMessage({
    channel: owner_slack_id,
    blocks: buildSlackBlocks({
      contracts: contracts,  // vendor_name, renewal_date, annual_cost,
                             // urgency_level, short_notice_flag
      register_url: config.REGISTER_URL
    })
  })
  // On Slack ID missing: fallback to gmail.send -> contract_owner_email
  // Log fallback event to error_log table

// Build Gmail finance summary (contracts due within 30 days only)
finance_contracts = triage_list.filter(c => c.days_until_renewal <= 30)
finance_contracts.sortBy('urgency_level', descending=true)

gmail.send({
  to: triage_list[0].finance_contact_email,
  subject: `Upcoming subscription renewals: ${finance_contracts.length} contracts due in next 30 days`,
  body: buildFinanceSummaryHTML({
    contracts: finance_contracts,  // vendor_name, renewal_date, annual_cost, urgency_level
    register_url: config.REGISTER_URL
  })
})

// ============================================================
// MANUAL STEP: Contract owner logs decision in Google Sheets
// decision_status column updated to 'Renew' or 'Cancel'
// This step is NOT automated. Owner acts on Slack prompt.
// ============================================================

// ============================================================
// AGENT HANDOFF 3: Decision detected -> Finance Record Agent
// Poll trigger: every 15 min, check for rows where
// decision_status == 'Renew' AND xero_bill_id IS BLANK
// ============================================================

// FINANCE RECORD AGENT
new_renewals = google_sheets.getRows({
  filter: 'decision_status == Renew AND xero_bill_id == blank'
})

for each renewal in new_renewals:
  // Lookup Xero supplier contact
  contact = xero.getContacts({
    where: `Name=="${renewal.vendor_name}" AND IsSupplier==true`
  })
  // Returns: { ContactID, Name } or empty

  if contact is empty:
    log.error({ vendor_name, reason: 'Xero supplier not found' })
    gmail.send({ to: finance_contact_email, subject: 'Action required: create Xero supplier for ' + vendor_name })
    continue

  // Create draft bill in Xero
  bill = xero.createInvoice({
    Type: 'ACCPAY',
    Status: 'DRAFT',
    Contact: { ContactID: contact.ContactID },
    DueDate: renewal.renewal_date,
    LineItems: [{
      Description: renewal.vendor_name + ' renewal',
      Quantity: 1,
      UnitAmount: renewal.annual_cost
    }]
  })
  // Returns: { InvoiceID, InvoiceNumber, Status: 'DRAFT' }

  // Write-back to Google Sheets register
  google_sheets.updateRow({
    row_index: renewal.sheet_row_index,
    fields: {
      xero_bill_id: bill.InvoiceID,
      bill_created_at: now()  // ISO 8601 datetime
    }
  })

// ============================================================
// MANUAL STEP: Finance Officer reviews and approves draft bill in Xero
// No further automation action. Flow ends.
// ============================================================