Developer Handover Pack

Developer Handover Pack

Affiliate Programme Management

Fires on every new confirmed conversion event received from Tapfiliate. The agent retrieves the conversion payload, cross-references it against order data to verify the sale is legitimate, checks for duplicate conversion IDs already present in the Google Sheets log, confirms the order has not been refunded, and applies the correct commission rate for the affiliate's tier. If any anomaly is detected (duplicate ID, order status mismatch, refund flag, or commission tier not resolvable) the agent sets a status flag of FLAGGED and halts downstream processing for that record, routing it for manual review. Clean records are written to the log with status PENDING_PAYOUT and passed downstream. Estimated build time: 14 hours. Complexity: Moderate.

// Input: Tapfiliate conversion webhook payload
conversion_id       : string   // Tapfiliate unique conversion ID
affiliate_id        : string   // Tapfiliate affiliate identifier
affiliate_email     : string   // Affiliate email address
order_id            : string   // E-commerce order reference
order_value_usd     : float    // Gross order value in USD
conversion_date     : ISO8601  // UTC timestamp of conversion
commission_tier     : string   // Tier label from Tapfiliate (e.g. 'standard', 'vip')

// Output: validated commission record
conversion_id       : string   // Passed through unchanged
affiliate_id        : string   // Passed through unchanged
affiliate_email     : string   // Passed through unchanged
order_id            : string   // Passed through unchanged
order_value_usd     : float    // Confirmed gross value
commission_rate_pct : float    // Rate resolved from tier rules table
commission_amount   : float    // order_value_usd * commission_rate_pct
status              : enum     // 'PENDING_PAYOUT' | 'FLAGGED'
flag_reason         : string?  // Populated only when status = FLAGGED
logged_at           : ISO8601  // Timestamp of Sheets write

This agent handles two distinct trigger paths. On a monthly schedule at payout cycle close it reads all PENDING_PAYOUT records from the Google Sheets log, groups them by affiliate_id, totals the commission_amount for each affiliate, raises a bill in Xero for each affiliate with the total owed, and marks those records as BILLED in the log. Once a Xero bill transitions to AUTHORISED status (finance approval), the agent sends a personalised payment confirmation email via Gmail. The second trigger path fires immediately when a new affiliate is approved in Tapfiliate: the agent retrieves the affiliate's profile data and dispatches the onboarding email sequence via Gmail. HubSpot deal records are created or updated on both paths to capture attribution. Estimated build time: 22 hours. Complexity: Complex.

// Input (payout path): aggregated from Google Sheets CommissionLog
affiliate_id           : string   // Unique affiliate identifier
affiliate_email        : string   // Destination for confirmation email
affiliate_display_name : string   // Used in email personalisation
commission_records[]   : array    // All PENDING_PAYOUT rows for this affiliate
  .conversion_id       : string
  .order_value_usd     : float
  .commission_amount   : float
  .conversion_date     : ISO8601
total_commission_usd   : float    // Sum of commission_amount across records
payout_period_label    : string   // e.g. 'April 2025'

// Input (onboarding path): new affiliate approval event from Tapfiliate
affiliate_id           : string
affiliate_email        : string
affiliate_display_name : string
tracking_link          : string   // Unique referral URL from Tapfiliate
approved_at            : ISO8601

// Output (payout path)
xero_bill_id           : string   // Xero bill reference created
xero_bill_status       : enum     // 'DRAFT' | 'AWAITING_APPROVAL'
hubspot_deal_id        : string   // Created or updated deal ID
gmail_confirmation_sent: boolean  // True when confirmation email dispatched
sheets_status_updated  : enum     // 'BILLED' written back to CommissionLog

// Output (onboarding path)
gmail_welcome_sent     : boolean
hubspot_contact_id     : string   // Affiliate contact created/updated in HubSpot

// On approval (Xero bill AUTHORISED event)
xero_bill_id           : string   // Triggers Gmail confirmation send
payment_date           : ISO8601  // Expected transfer date from Xero
amount_paid_usd        : float

Fires every Monday morning on a fixed schedule. The agent reads all rows from the Google Sheets CommissionLog where conversion_date falls within the past seven days, calculates total conversions, total commission accrued, and ranks affiliates by commission_amount to identify top performers. It then composes a structured Slack message using a fixed template and posts it to the designated marketing Slack channel. No downstream actions are taken; this agent is read-only on all connected tools. Estimated build time: 6 hours. Complexity: Simple.

// Input: Google Sheets CommissionLog rows (past 7 days)
rows[]               : array
  .affiliate_id      : string
  .affiliate_email   : string
  .commission_amount : float
  .conversion_date   : ISO8601
  .status            : string   // Any status; all rows included in digest

// Output: computed digest values passed to Slack formatter
period_label         : string   // e.g. 'Week of 12 May 2025'
total_conversions    : integer  // Count of rows in date range
total_commission_usd : float    // Sum of commission_amount
top_affiliates[]     : array    // Top 3 by commission_amount
  .affiliate_email   : string
  .total_commission  : float
  .conversion_count  : integer
slack_message_posted : boolean
slack_channel_id     : string   // Resolved channel ID, not display name

// ============================================================
// TRIGGER: Tapfiliate conversion event
// ============================================================
TAPFILIATE_WEBHOOK_EVENT
  -> conversion_id       : string
  -> affiliate_id        : string
  -> affiliate_email     : string
  -> order_id            : string
  -> order_value_usd     : float
  -> conversion_date     : ISO8601
  -> commission_tier     : string

// ============================================================
// AGENT 1 HANDOFF: Conversion Validation Agent receives payload
// ============================================================
CONVERSION_VALIDATION_AGENT
  [1] DEDUP CHECK: query Google Sheets CommissionLog column A
      IF conversion_id EXISTS -> emit dedup_skip_event, HALT
  [2] REFUND CHECK: query order system by order_id
      IF order_status IN ('refunded', 'cancelled') -> status = 'FLAGGED', flag_reason = 'order_refunded'
  [3] TIER LOOKUP: query TierRules sheet by commission_tier
      IF tier_label NOT FOUND -> status = 'FLAGGED', flag_reason = 'tier_not_found'
      IF tier_label FOUND -> commission_rate_pct = resolved_rate
  [4] AMOUNT CALC: commission_amount = order_value_usd * commission_rate_pct
      IF commission_amount > 500 -> status = 'FLAGGED', flag_reason = 'high_value_review'
  [5] WRITE to Google Sheets CommissionLog
      fields written:
        conversion_id, affiliate_id, affiliate_email, order_id,
        order_value_usd, commission_rate_pct, commission_amount,
        status ('PENDING_PAYOUT' | 'FLAGGED'), flag_reason, logged_at
  IF status = 'FLAGGED' -> HALT downstream, alert Marketing Manager
  IF status = 'PENDING_PAYOUT' -> continue

// ============================================================
// AGENT 2 HANDOFF (PATH A): Payout and Communications Agent
//   Trigger: monthly schedule at payout cycle close
// ============================================================
PAYOUT_AND_COMMUNICATIONS_AGENT (PAYOUT PATH)
  [1] READ Google Sheets CommissionLog
      filter: status = 'PENDING_PAYOUT'
      group by: affiliate_id
      aggregate: total_commission_usd = SUM(commission_amount)
      output fields per affiliate:
        affiliate_id, affiliate_email, affiliate_display_name,
        commission_records[], total_commission_usd, payout_period_label
  [2] XERO: check for existing Contact by affiliate_email
      IF NOT EXISTS -> create Xero Contact (name = affiliate_display_name, email = affiliate_email)
      CREATE Xero Bill
        contact_id    = xero_contact_id
        reference     = 'AFFILIATE-{affiliate_id}-{payout_period_label}'
        amount_due    = total_commission_usd
        currency      = USD
        status        = 'AWAITING_APPROVAL'
      -> xero_bill_id returned
  [3] HUBSPOT: create or update Deal
        deal_name     = 'Affiliate Commission - {affiliate_display_name} - {payout_period_label}'
        pipeline_id   = [config: confirmed pre-build]
        deal_stage_id = [config: confirmed pre-build]
        amount        = total_commission_usd
        affiliate_id  = custom property
      -> hubspot_deal_id returned
  [4] UPDATE Google Sheets CommissionLog
        status -> 'BILLED'
        xero_bill_id written to log row

// ============================================================
// AGENT 2 RESUME (PATH A): on Xero bill AUTHORISED
// ============================================================
XERO_BILL_AUTHORISED_EVENT
  -> xero_bill_id, amount_paid_usd, payment_date
PAYOUT_AND_COMMUNICATIONS_AGENT (POST-APPROVAL)
  [5] GMAIL: send payment confirmation email
        to             = affiliate_email
        template       = 'payment_confirmation_v1'
        variables:
          affiliate_display_name, amount_paid_usd, payment_date,
          payout_period_label, xero_bill_id
      -> gmail_confirmation_sent = true
  [6] UPDATE Google Sheets CommissionLog
        status -> 'PAID'

// ============================================================
// AGENT 2 HANDOFF (PATH B): new affiliate approved in Tapfiliate
// ============================================================
TAPFILIATE_AFFILIATE_APPROVED_EVENT
  -> affiliate_id, affiliate_email, affiliate_display_name,
     tracking_link, approved_at
PAYOUT_AND_COMMUNICATIONS_AGENT (ONBOARDING PATH)
  [1] HUBSPOT: create or update Contact
        email          = affiliate_email
        first_name     = affiliate_display_name (parsed)
        affiliate_id   = custom property
        lifecycle_stage = 'partner'
      -> hubspot_contact_id returned
  [2] GMAIL: send onboarding email sequence
        step_1: welcome email (immediate)
          template = 'affiliate_welcome_v1'
          variables: affiliate_display_name, tracking_link
        step_2: programme terms (delay: +1 day)
          template = 'affiliate_terms_v1'
        step_3: creative assets (delay: +2 days)
          template = 'affiliate_assets_v1'
      -> gmail_welcome_sent = true

// ============================================================
// AGENT 3 HANDOFF: Reporting Agent
//   Trigger: weekly Monday 08:00 schedule
// ============================================================
REPORTING_AGENT
  [1] READ Google Sheets CommissionLog
      filter: conversion_date >= NOW() - 7 days
      compute:
        total_conversions    = COUNT(rows)
        total_commission_usd = SUM(commission_amount)
        top_affiliates[]     = TOP 3 BY commission_amount (grouped by affiliate_id)
          .affiliate_email, .total_commission, .conversion_count
  [2] SLACK: post digest message
        channel_id = [config: confirmed pre-build]
        template   = 'weekly_digest_v1'
        variables:
          period_label, total_conversions, total_commission_usd,
          top_affiliates[0..2]
      IF total_conversions = 0 -> post zero-activity message (do not skip)
      -> slack_message_posted = true

// ============================================================
// ERROR PATH (all agents)
// ============================================================
ON_ERROR
  -> log error to Supabase error_log table:
       agent_name, step_label, error_code, error_message,
       input_payload_snapshot, occurred_at
  -> trigger Slack alert to #automation-errors channel
  -> retry policy: 3 attempts with exponential backoff (30s, 2min, 10min)
  -> after 3 failures: halt and await manual intervention