Developer Handover Pack

Developer Handover Pack

Commission and Payout Calculation

Connects to HubSpot on a scheduled trigger aligned to the configured pay period end date. Calls the HubSpot Deals API to retrieve all deals where stage equals closed-won and close date falls within the pay period window. Normalises field names, reformats currency values to two decimal places, removes records tagged as test or voided, and writes one clean row per deal into a designated Google Sheets tab. All records are tagged with the rep's HubSpot owner ID and a period label derived from the trigger timestamp. No manual intervention is required unless the HubSpot connection returns an authentication error.

// Input
trigger.pay_period_start_date  : ISO 8601 date string
trigger.pay_period_end_date    : ISO 8601 date string
trigger.period_label           : string  e.g. '2024-06-B'

// HubSpot query filter applied
hubspot.deals.filter.stage     = 'closedwon'
hubspot.deals.filter.closedate >= pay_period_start_date
hubspot.deals.filter.closedate <= pay_period_end_date

// Output (one row per deal written to Google Sheets tab: raw_deals_{period_label})
deal_id          : string   (HubSpot deal ID)
deal_name        : string
close_date       : ISO 8601 date string
deal_value       : float    (USD, 2 decimal places)
owner_id         : string   (HubSpot owner ID)
owner_email      : string
product_line     : string   (mapped from hs_deal_stage_probability custom property)
is_split_deal    : boolean
split_percentage : float    (null if is_split_deal = false)
clawback_ref     : string   (deal_id of prior period deal if clawback applies, else null)
period_label     : string
record_status    : string   ('clean' | 'flagged_test' | 'flagged_void')

Reads the clean deal list written by the Deal Ingestion Agent and the active commission rate table from a separate Google Sheets tab. For each rep, it sums deal values for the period, determines quota attainment percentage, resolves the correct tier from the rate table, and applies the rate to each deal to produce a gross commission line. Split deals are handled by multiplying deal value by the split percentage before applying the rate. Clawback references trigger a negative adjustment line linked to the originating deal ID. Any deal that cannot be matched to a rep record or cannot be resolved to a single rate tier is written to an exceptions list rather than the payout summary. The agent writes its outputs to a payout summary tab and an exceptions tab, then signals the orchestration layer that it has completed.

// Input (read from Google Sheets)
raw_deals_{period_label}          : sheet tab   (output of Deal Ingestion Agent)
commission_rate_table             : sheet tab   (columns: tier_label, quota_attainment_min_pct,
                                                 quota_attainment_max_pct, commission_rate_pct,
                                                 product_line, effective_date)
rep_quota_table                   : sheet tab   (columns: owner_id, owner_email, quota_usd, period_label)

// Processing sequence
1. Group deals by owner_id
2. Sum deal_value per owner_id -> rep_total_revenue
3. rep_quota_attainment_pct = (rep_total_revenue / quota_usd) * 100
4. Match quota_attainment_pct to rate table tier -> resolved_rate_pct
5. For each deal: gross_commission = deal_value * (split_percentage ?? 1.0) * resolved_rate_pct
6. Apply clawback: if clawback_ref != null, append negative line = -(prior_deal_value * resolved_rate_pct)
7. Flag unresolved records -> exceptions list

// Output A: payout_summary_{period_label} tab (one row per rep)
owner_id              : string
owner_email           : string
period_label          : string
deal_count            : integer
rep_total_revenue     : float
quota_usd             : float
quota_attainment_pct  : float
resolved_rate_pct     : float
gross_commission      : float
clawback_adjustment   : float  (negative, 0.00 if none)
net_commission        : float  (gross_commission + clawback_adjustment)
calculation_status    : string ('confirmed' | 'pending_exception')

// Output B: exceptions_{period_label} tab (one row per unresolved deal)
deal_id               : string
owner_id              : string
exception_reason      : string  ('no_rep_match' | 'no_rate_tier' | 'ambiguous_split')
requires_manual_review: boolean  (always true)

Takes the approved payout summary and executes all downstream distribution steps in sequence. First, it posts a structured Slack message to the sales manager containing the rep-level summary table and an approve or flag interactive button. The workflow pauses at this point and waits for the manager's response. On approval, it pushes each rep's net_commission as an additional earnings line to Gusto using the Gusto Payroll API, creates a single commission expense journal entry in Xero, and sends each rep a personalised Slack DM listing their deal count, rate applied, and net commission for the period. If the manager clicks flag, the workflow routes back to the exceptions handling path and sends an alert to the finance analyst. A configurable timeout (default 48 hours) prevents the workflow from stalling indefinitely if the manager does not respond.

// Input (read from Google Sheets)
payout_summary_{period_label}  : sheet tab   (output of Commission Calculation Agent)
exceptions_{period_label}      : sheet tab   (surfaced in Slack approval message if non-empty)

// Step 1: Slack approval message (sent to manager Slack user ID)
slack.channel      : manager_slack_user_id  (configured credential)
slack.blocks       : summary table + approve button + flag button
slack.callback_id  : 'commission_approval_{period_label}'

// On approval (manager clicks Approve)
gusto_payload per rep:
  employee_id       : string   (mapped from owner_email -> Gusto employee lookup)
  earning_type      : 'Commission'
  amount            : net_commission  (float, USD)
  pay_period_label  : period_label

xero_journal_entry:
  date              : trigger.pay_period_end_date
  description       : 'Sales commission - {period_label}'
  line_items[0].account_code  : '420'  (confirm cost centre with process owner)
  line_items[0].amount        : SUM(net_commission) across all reps
  line_items[0].description   : 'Gross sales commission {period_label}'
  attachments[0]              : Google Sheets export of payout_summary_{period_label} (PDF)

// Slack DM per rep (sent after Gusto and Xero steps confirm success)
slack.channel      : rep_slack_user_id  (mapped from owner_email -> Slack user lookup)
slack.text         : 'Hi {first_name}, your commission for {period_label} has been approved.'
slack.blocks       : deal_count, rep_total_revenue, resolved_rate_pct, net_commission

// On flag (manager clicks Flag)
slack.channel      : finance_analyst_slack_user_id
slack.text         : 'Commission approval flagged by manager for {period_label}. Review required.'
workflow.status    : paused_pending_review

// On timeout (48 hours elapsed, no response)
slack.channel      : finance_analyst_slack_user_id
slack.text         : 'Commission approval timed out for {period_label}. Manual action required.'
workflow.status    : timed_out

// ============================================================
// COMMISSION AND PAYOUT CALCULATION: END-TO-END DATA FLOW
// ============================================================

// TRIGGER
// Orchestration layer scheduled job fires on pay_period_end_date
trigger.pay_period_start_date  = '2024-06-01'   // ISO 8601
trigger.pay_period_end_date    = '2024-06-15'   // ISO 8601
trigger.period_label           = '2024-06-B'    // derived from end date
trigger.run_id                 = 'run_20240615_001'  // unique per execution

// ============================================================
// AGENT 1: DEAL INGESTION AGENT
// ============================================================

// Step 1: HubSpot Deals API call
GET /crm/v3/objects/deals
  ?filters[0].propertyName=dealstage
  &filters[0].operator=EQ
  &filters[0].value=closedwon
  &filters[1].propertyName=closedate
  &filters[1].operator=BETWEEN
  &filters[1].value=trigger.pay_period_start_date
  &filters[1].highValue=trigger.pay_period_end_date
  &properties=dealname,amount,closedate,hubspot_owner_id,
              hs_product_line,is_split_deal,split_pct,clawback_ref

// Step 2: Normalisation and filtering
for each deal in hubspot_response.results:
  deal_id          = deal.id
  deal_name        = deal.properties.dealname
  close_date       = deal.properties.closedate  // ISO 8601
  deal_value       = float(deal.properties.amount)  // USD, 2dp
  owner_id         = deal.properties.hubspot_owner_id
  owner_email      = hubspot_owners_lookup[owner_id].email
  product_line     = deal.properties.hs_product_line ?? 'standard'
  is_split_deal    = bool(deal.properties.is_split_deal)
  split_percentage = float(deal.properties.split_pct) ?? null
  clawback_ref     = deal.properties.clawback_ref ?? null
  period_label     = trigger.period_label
  record_status    = classify_record(deal_value, deal_name)
    // 'flagged_void' if deal_value == 0 or null
    // 'flagged_test' if deal_name contains '[TEST]'
    // 'clean' otherwise

// Step 3: Write to Google Sheets
// Clean records -> tab: raw_deals_2024-06-B
// Flagged records -> tab: exceptions_2024-06-B
sheets.spreadsheetId  = PAYOUT_LEDGER_SPREADSHEET_ID  // from credential store
sheets.range          = 'raw_deals_2024-06-B!A1'
sheets.valueInputOption = 'USER_ENTERED'
sheets.values         = [deal_id, deal_name, close_date, deal_value,
                          owner_id, owner_email, product_line,
                          is_split_deal, split_percentage, clawback_ref,
                          period_label, record_status]

// Agent 1 completion signal -> triggers Agent 2
agent1.status  = 'complete'
agent1.output  = { tab: 'raw_deals_2024-06-B', row_count: N, flagged_count: M }

// ============================================================
// AGENT 2: COMMISSION CALCULATION AGENT
// ============================================================

// Step 4: Read inputs from Google Sheets
deals       = sheets.read('raw_deals_2024-06-B')           // from Agent 1
rate_table  = sheets.read('commission_rate_table')         // maintained by finance team
quota_table = sheets.read('rep_quota_table')               // filtered by period_label

// Step 5: Per-rep calculation loop
for each owner_id in unique(deals.owner_id):
  rep_deals             = filter(deals, owner_id == owner_id, record_status == 'clean')
  rep_total_revenue     = sum(rep_deals.deal_value)  // adjusted for split_percentage
  quota_usd             = quota_table[owner_id, period_label].quota_usd
  quota_attainment_pct  = (rep_total_revenue / quota_usd) * 100
  resolved_rate_pct     = rate_table.lookup(
                            quota_attainment_pct,
                            product_line,
                            effective_date <= trigger.pay_period_end_date
                          )  // returns null if no match -> exception

  for each deal in rep_deals:
    effective_value    = deal.deal_value * (deal.split_percentage ?? 1.0)
    gross_commission   = effective_value * resolved_rate_pct

  clawback_adjustment  = sum(clawback_lines)  // negative float or 0.00
  net_commission       = sum(gross_commission) + clawback_adjustment

// Step 6: Write outputs to Google Sheets
// Confirmed reps  -> tab: payout_summary_2024-06-B
// Exception reps  -> tab: exceptions_2024-06-B  (appended)
payout_summary.fields = [owner_id, owner_email, period_label, deal_count,
                          rep_total_revenue, quota_usd, quota_attainment_pct,
                          resolved_rate_pct, gross_commission,
                          clawback_adjustment, net_commission, calculation_status]

// Agent 2 completion signal -> triggers Agent 3
agent2.status  = 'complete'
agent2.output  = { tab: 'payout_summary_2024-06-B', confirmed: X, exceptions: Y }

// ============================================================
// AGENT 3: PAYOUT DISTRIBUTION AGENT
// ============================================================

// Step 7: Post Slack approval message to manager
slack.postMessage(
  channel    = MANAGER_SLACK_USER_ID,   // from credential store
  blocks     = build_approval_blocks(payout_summary, exceptions),
  callback_id = 'commission_approval_2024-06-B'
)
workflow.wait_for_interaction(timeout = '48h')

// Step 8a: On manager approval
if interaction.action == 'approve':

  // Push to Gusto
  for each rep in payout_summary where calculation_status == 'confirmed':
    gusto_employee_id = employee_map[rep.owner_email]  // from mapping table
    POST /v1/companies/{company_id}/employees/{gusto_employee_id}/additional_earnings
    body = {
      earning_type : 'Commission',
      amount       : rep.net_commission,
      payment_date : trigger.pay_period_end_date
    }

  // Create Xero journal entry
  POST /api.xro/2.0/ManualJournals
  body = {
    Date        : trigger.pay_period_end_date,
    Narration   : 'Sales commission 2024-06-B',
    JournalLines: [
      { AccountCode: '420', Description: 'Gross commission',
        LineAmount: SUM(payout_summary.net_commission) },
      { AccountCode: '200', Description: 'Commission payable clearing',
        LineAmount: -SUM(payout_summary.net_commission) }
    ]
  }

  // Send Slack DM per rep
  for each rep in payout_summary where calculation_status == 'confirmed':
    rep_slack_id = slack_user_map[rep.owner_email]  // from mapping table
    slack.postMessage(
      channel = rep_slack_id,
      text    = 'Hi {first_name}, your commission for 2024-06-B is {net_commission}.',
      blocks  = build_rep_breakdown_blocks(rep)
    )

// Step 8b: On manager flag or timeout
else:
  slack.postMessage(
    channel = FINANCE_ANALYST_SLACK_USER_ID,
    text    = 'Commission approval flagged or timed out for 2024-06-B. Manual action required.'
  )
  workflow.status = 'paused_pending_review'

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