Developer Handover Pack

Developer Handover Pack

Client Deposit & Payment Tracking

The Deposit Monitor Agent is the entry point of the automation. It listens for new invoices created in Xero that carry deposit terms, writes a structured record to the Google Sheets payment tracker, and then polls the Stripe payment feed on a daily schedule to detect incoming payments. Matching logic compares the incoming Stripe charge amount and client reference against open deposit records in Google Sheets. Confirmed matches pass downstream for reconciliation and confirmation. Unmatched payments, including partial payments, misreferenced transfers, and split payments, are flagged for bookkeeper review before any reconciliation action is taken. This agent replaces the daily manual bank feed check (step 3), the spreadsheet update (step 4), the deposit reconciliation in Xero (step 8), the final balance spreadsheet update (step 9), and the full settlement closure in Xero (step 11), as well as the initial invoice logging in step 1.

// Input
xero_invoice_id: string           // e.g. 'INV-0042'
xero_client_name: string          // matched against Stripe metadata.client_name
xero_client_email: string
deposit_amount_usd: float         // from Xero invoice deposit line
deposit_due_date: date            // ISO 8601
invoice_total_usd: float
invoice_currency: string          // must be 'USD'; flag others for review

// Stripe poll payload (daily)
stripe_charge_id: string
stripe_amount_cents: int          // divide by 100 to compare
stripe_metadata.invoice_ref: string
stripe_metadata.client_name: string
stripe_payment_date: date

// Output (on match)
match_status: 'confirmed' | 'unmatched' | 'partial'
matched_charge_id: string
matched_amount_usd: float
sheets_row_id: string             // row reference in payment tracker
xero_reconciliation_status: 'applied' | 'pending_review'

// Output (on no match by due date)
overdue_flag: true
days_overdue: int
sheets_row_id: string

The Client Communication Agent handles all outbound email to the client throughout the deposit and payment lifecycle. It receives triggers from the Deposit Monitor Agent, either an overdue flag or a confirmed match, and sends the appropriate email via Gmail. Overdue chasers are sent on a pre-set schedule with duplicate-send prevention. Deposit confirmations and final payment reminders are sent once per qualifying event. Every outbound email is logged automatically as a contact activity in HubSpot against the matching client record, including the date sent and the outstanding amount. This agent replaces manual steps 2, 5, 6, 7, and 10.

// Input (chaser branch)
xero_client_email: string
xero_client_name: string
xero_invoice_id: string
deposit_amount_usd: float
deposit_due_date: date
days_overdue: int
overdue_flag: true
sheets_row_id: string

// Input (confirmation branch)
xero_client_email: string
xero_client_name: string
xero_invoice_id: string
matched_amount_usd: float
invoice_total_usd: float
remaining_balance_usd: float      // invoice_total_usd minus matched_amount_usd
final_payment_due_date: date
match_status: 'confirmed'

// Output
gmail_message_id: string          // stored for dedupe reference
email_type: 'chaser' | 'confirmation' | 'final_reminder'
hubspot_activity_id: string       // created engagement record ID
hubspot_contact_id: string
send_timestamp: datetime
sheets_row_updated: true

The Internal Notification Agent is the lightest of the three agents. It fires a single Slack message to the designated project channel when the Deposit Monitor Agent confirms that an invoice is fully paid and closed in Xero. The message includes the client name, invoice reference, and cleared amount so the project team can proceed without logging into any finance tool. This agent replaces manual step 12.

// Input
xero_invoice_id: string
xero_client_name: string
invoice_total_usd: float
cleared_date: date
slack_channel_id: string          // set in config, not dynamic

// Output
slack_message_ts: string          // Slack message timestamp, used as idempotency key
slack_channel_id: string
notification_logged: true         // written back to Google Sheets payment tracker row

// ────────────────────────────────────────────────────────────────��
// TRIGGER: Xero webhook fires on invoice.created
// ─────────────────────────────────────────────────────────────────
xero_webhook_payload {
  xero_invoice_id: 'INV-0042'
  xero_client_name: 'Acme Pty Ltd'
  xero_client_email: 'finance@acme.example'
  deposit_amount_usd: 1500.00
  deposit_due_date: '2025-05-10'
  invoice_total_usd: 6000.00
  invoice_currency: 'USD'
}

// ─────────────────────────────────────────────────────────────────
// AGENT HANDOFF 1: Deposit Monitor Agent receives webhook payload
// ─────────────────────────────────────────────────────────────────

// ACTION: Write initial row to Google Sheets payment tracker
sheets.appendRow('PaymentTracker', {
  invoice_id: xero_invoice_id,             // 'INV-0042'
  client_name: xero_client_name,           // 'Acme Pty Ltd'
  client_email: xero_client_email,
  deposit_due: deposit_due_date,           // '2025-05-10'
  deposit_amount: deposit_amount_usd,      // 1500.00
  invoice_total: invoice_total_usd,        // 6000.00
  match_status: 'pending',
  chaser_count: 0
})
// Returns: sheets_row_id -> 'row_47'

// ACTION: Daily Stripe poll (cron, every 24h)
stripe.charges.list({
  created_gte: deposit_due_date - 7_days,
  limit: 100
})
// Per charge, evaluate:
//   PRIMARY MATCH: stripe_amount_cents == deposit_amount_usd * 100
//                  AND stripe_metadata.invoice_ref == xero_invoice_id
//   SECONDARY MATCH: normalise(stripe_metadata.client_name)
//                     == normalise(xero_client_name)
//   PARTIAL: 0 < stripe_amount_cents < deposit_amount_usd * 100
//   UNMATCHED: no condition met

// ─────────────────────────────────────────────────────────────────
// BRANCH A: match_status == 'confirmed'
// ─────────────────────────────────────────────────────────────────
match_result {
  match_status: 'confirmed'
  matched_charge_id: 'ch_3PxT7A2eZvKYlo2C0abc1234'
  matched_amount_usd: 1500.00
  sheets_row_id: 'row_47'
  xero_reconciliation_status: 'applied'
}

// ACTION: Apply payment to Xero invoice
xero.payments.create({
  InvoiceID: xero_invoice_id,              // 'INV-0042'
  Date: stripe_payment_date,
  Amount: matched_amount_usd,              // 1500.00
  Reference: matched_charge_id
})

// ACTION: Update Google Sheets row
sheets.updateRow('row_47', {
  match_status: 'confirmed',
  matched_amount: 1500.00,
  reconciled_date: today,
  remaining_balance: 4500.00               // invoice_total - matched_amount
})

// ─────────────────────────────────────────────────────────────────
// AGENT HANDOFF 2: Deposit Monitor Agent -> Client Communication Agent
// ─────────────────────────────────────────────────────────────────
communication_trigger_payload {
  email_type: 'confirmation'
  xero_client_email: 'finance@acme.example'
  xero_client_name: 'Acme Pty Ltd'
  xero_invoice_id: 'INV-0042'
  matched_amount_usd: 1500.00
  remaining_balance_usd: 4500.00
  final_payment_due_date: '2025-06-10'
  sheets_row_id: 'row_47'
}

// ACTION: Send deposit confirmation via Gmail
gmail.send({
  to: xero_client_email,
  subject: 'Deposit Received: INV-0042',
  body: template('confirmation', {
    client_name: xero_client_name,
    matched_amount: matched_amount_usd,
    remaining_balance: remaining_balance_usd,
    due_date: final_payment_due_date
  })
})
// Returns: gmail_message_id -> 'msg_abc9876'

// ACTION: Log activity in HubSpot
hubspot.engagements.create({
  type: 'EMAIL',
  contact_id: hubspot_contact_id,          // resolved via xero_client_email
  metadata: {
    subject: 'Deposit Received: INV-0042',
    sent_at: send_timestamp,
    amount_outstanding: remaining_balance_usd
  }
})
// Returns: hubspot_activity_id -> 'eng_112233'

// ─────────────────────────────────────────────────────────────────
// BRANCH B: overdue_flag == true (no match by deposit_due_date)
// ─────────────────────────────────────────────────────────────────
overdue_payload {
  email_type: 'chaser'
  xero_client_email: 'finance@acme.example'
  xero_invoice_id: 'INV-0042'
  deposit_amount_usd: 1500.00
  days_overdue: 1
  sheets_row_id: 'row_47'
}
// Day-of-week check: skip Saturday (6) and Sunday (0)
// Dedupe check: query Gmail sent for subject containing 'INV-0042' within last 5 days
// If no duplicate found, send chaser and increment sheets[row_47].chaser_count
// Max chasers: 4. On chaser_count == 4, set sheets[row_47].escalation_flag = true

// ─────────────────────────────────────────────────────────────────
// BRANCH C: match_status == 'partial' or 'unmatched' -> manual review
// ─────────────────────────────────────────────────────────────────
// sheets.updateRow('row_47', { match_status: 'pending_review' })
// No Xero reconciliation call. No Gmail send.
// Bookkeeper reviews Google Sheets flag and confirms allocation manually.
// On bookkeeper confirmation, workflow resumes from BRANCH A.

// ─────────────────────────────────────────────────────────────────
// AGENT HANDOFF 3: Full settlement confirmed -> Internal Notification Agent
// ─────────────────────────────────────────────────────────────────
settlement_payload {
  xero_invoice_id: 'INV-0042'
  xero_client_name: 'Acme Pty Ltd'
  invoice_total_usd: 6000.00
  cleared_date: '2025-06-11'
  slack_channel_id: 'C04XXXXABCD'          // set in config
}

// Pre-post check: GET /Invoices/INV-0042 -> Status == 'PAID'
// Idempotency check: sheets[row_47].slack_message_ts is null

// ACTION: Post to Slack
slack.chat.postMessage({
  channel: slack_channel_id,
  blocks: [
    { type: 'header', text: 'Payment Cleared' },
    { type: 'section', text: 'Acme Pty Ltd | INV-0042 | $6,000.00' },
    { type: 'context', text: 'Cleared: 2025-06-11' }
  ],
  text: 'Payment cleared: Acme Pty Ltd INV-0042 $6,000.00'
})
// Returns: slack_message_ts -> '1749000000.123456'

// ACTION: Write slack_message_ts to Google Sheets
sheets.updateRow('row_47', {
  slack_notified: true,
  slack_message_ts: '1749000000.123456'
})