Developer Handover Pack

Developer Handover Pack

Bank Reconciliation Automation

Connects to the bank via the Plaid API and to the accounting ledger via Xero, pulls all new transactions on schedule, and normalises both datasets into a unified format ready for matching. This agent fires on a set schedule (daily or month-end) or can be triggered manually for an out-of-cycle run. It replaces the manual statement export, cleaning, and ledger export steps that account for 50 minutes of bookkeeper time per cycle. The normalisation step must handle all account types and currencies currently in use before handing off to the Matching and Exceptions Agent.

// Input
plaid.transactions.get({
  access_token: <PLAID_ACCESS_TOKEN>,
  start_date: last_successful_run_date,
  end_date: today,
  account_ids: [<ACCOUNT_ID_1>, <ACCOUNT_ID_2>, ...]
})

xero.accounting.transactions.list({
  DateFrom: last_successful_run_date,
  DateTo: today,
  Status: 'UNRECONCILED'
})

// Output
normalised_dataset: {
  bank_transactions: [
    {
      txn_id: string,
      account_id: string,
      date: ISO8601,
      amount: number,           // positive = credit, negative = debit
      currency: string,         // ISO 4217
      reference: string,
      description: string,
      merchant_name: string | null,
      source: 'plaid'
    }
  ],
  ledger_entries: [
    {
      xero_txn_id: string,
      contact_name: string,
      date: ISO8601,
      amount: number,
      currency: string,
      reference: string,
      account_code: string,
      status: 'UNRECONCILED',
      source: 'xero'
    }
  ],
  run_metadata: {
    run_id: uuid,
    triggered_at: ISO8601,
    trigger_type: 'scheduled' | 'manual',
    bank_txn_count: number,
    ledger_entry_count: number
  }
}

Applies rule-based and fuzzy matching logic to pair bank transactions with ledger entries from the normalised dataset delivered by the Bank Feed Sync Agent. Confirmed matches are posted back to Xero via the reconciliation API. Unresolved items (no match found or confidence below threshold) are written to the Google Sheets exceptions tab with pre-filled context including the bank reference, amount, date, and a suggested Xero account code based on description keywords. This agent replaces the four most time-intensive manual steps in the current process and is where calibration against historical data is most critical.

// Input
normalised_dataset: {
  bank_transactions: [ { txn_id, date, amount, currency, reference, description } ],
  ledger_entries: [ { xero_txn_id, date, amount, currency, reference, account_code } ],
  run_metadata: { run_id, bank_txn_count, ledger_entry_count }
}

matching_config: {
  exact_match_fields: ['amount', 'currency', 'reference'],
  fuzzy_match_fields: ['description', 'merchant_name'],
  fuzzy_threshold: 0.82,          // tuned during QA against 3 months historical data
  date_tolerance_days: 3,
  confidence_post_threshold: 0.95  // below this -> exceptions sheet
}

// Output
matched_pairs: [
  {
    txn_id: string,               // Plaid transaction_id
    xero_txn_id: string,          // Xero BankTransactionID
    match_type: 'exact' | 'fuzzy',
    confidence_score: number,
    posted_to_xero: boolean,
    xero_reconciliation_id: string
  }
]

exceptions: [
  {
    txn_id: string | null,
    xero_txn_id: string | null,
    date: ISO8601,
    amount: number,
    currency: string,
    bank_reference: string,
    description: string,
    suggested_account_code: string,
    exception_reason: 'no_match' | 'low_confidence' | 'duplicate_candidate',
    confidence_score: number | null
  }
]

run_summary: {
  run_id: string,
  matched_count: number,
  exception_count: number,
  closing_balance: number,
  currency: string,
  completed_at: ISO8601
}

Fires after the Matching and Exceptions Agent completes its run and, separately, after the bookkeeper marks all exceptions as resolved in the Google Sheets tab. In the first phase it sends a Slack alert to the finance channel with the matched count, exception count, closing balance, and a direct link to the exceptions sheet. In the second phase, once the bookkeeper's sign-off is detected (a status cell in the sheet is set to 'APPROVED'), it posts the final reconciliation period close record to Xero and sends a confirmation email to the finance lead via Gmail. This agent replaces the manual summary preparation and distribution steps.

// Input (Phase 1 - post-match alert)
run_summary: {
  run_id: string,
  matched_count: number,
  exception_count: number,
  closing_balance: number,
  currency: string,
  exceptions_sheet_url: string,
  completed_at: ISO8601
}

// Input (Phase 2 - close trigger)
sheet_event: {
  run_id: string,
  exceptions_status: 'APPROVED',
  approved_by: string,           // bookkeeper name from sheet cell
  approved_at: ISO8601
}

// Output (Phase 1)
slack_message: {
  channel: '#finance-reconciliation',
  text: 'Reconciliation run {run_id} complete. Matched: {matched_count}. Exceptions: {exception_count}. Closing balance: {currency} {closing_balance}. Review exceptions: {exceptions_sheet_url}'
}

// Output (Phase 2 - on approval)
xero_period_close: {
  BankAccountID: string,
  StatementDate: ISO8601,
  EndingBalance: number,
  Notes: 'Reconciliation {run_id} closed by {approved_by} at {approved_at}. Matched: {matched_count}. Exceptions resolved: {exception_count}.'
}

gmail_confirmation: {
  to: '<FINANCE_LEAD_EMAIL>',
  subject: 'Reconciliation closed for period ending {StatementDate}',
  body: 'The reconciliation run for {StatementDate} has been closed. {matched_count} transactions posted to Xero. All exceptions resolved and signed off by {approved_by}. Period close record written to Xero.'
}

// ─────────────────────────────────────────────────────────────────
// TRIGGER
// ─────────────────────────────────────────────────────────────────
cron_schedule OR manual_webhook_call
  -> orchestration_layer.start_run()
  -> run_id = uuid.v4()
  -> last_run_date = run_log_table.get_last_successful_date(run_id)

// ─────────────────────────────────────────────────────────────────
// AGENT 1: Bank Feed Sync Agent
// ─────────────────────────────────────────────────────────────────
plaid.transactions.get({
  access_token: PLAID_ACCESS_TOKEN,
  start_date: last_run_date,
  end_date: today,
  account_ids: [ACCOUNT_ID_1, ACCOUNT_ID_2]
})
  -> raw_bank_transactions[]         // fields: transaction_id, date, amount, iso_currency_code,
                                     //         merchant_name, name (description), account_id

xero.accounting.transactions.list({
  DateFrom: last_run_date,
  DateTo: today,
  Status: 'UNRECONCILED'
})
  -> raw_ledger_entries[]            // fields: BankTransactionID, ContactName, DateString,
                                     //         Total, CurrencyCode, Reference, LineItems[].AccountCode

normalise(raw_bank_transactions, raw_ledger_entries)
  -> normalised_dataset {
       bank_transactions[]: { txn_id, account_id, date, amount, currency, reference, description, merchant_name }
       ledger_entries[]:    { xero_txn_id, contact_name, date, amount, currency, reference, account_code }
       run_metadata:        { run_id, triggered_at, trigger_type, bank_txn_count, ledger_entry_count }
     }

// ─────────────────────────────────────────────────────────────────
// HANDOFF: Bank Feed Sync Agent -> Matching and Exceptions Agent
// ─────────────────────────────────────────────────────────────────
orchestration_layer.pass_payload(normalised_dataset)

// ─────────────────────────────────────────────────────────────────
// AGENT 2: Matching and Exceptions Agent
// ─────────────────────────────────────────────────────────────────
for each bank_transaction in normalised_dataset.bank_transactions:

  exact_match = find_ledger_entry_where(
    amount == bank_transaction.amount AND
    currency == bank_transaction.currency AND
    reference == bank_transaction.reference AND
    abs(date_diff) <= 3 days
  )

  if exact_match:
    matched_pairs.append({
      txn_id, xero_txn_id, match_type: 'exact', confidence_score: 1.0
    })

  else:
    fuzzy_score = jaro_winkler_similarity(
      normalise_string(bank_transaction.description),
      normalise_string(ledger_entry.reference)
    )
    if fuzzy_score >= 0.82 AND confidence_score >= 0.95:
      matched_pairs.append({
        txn_id, xero_txn_id, match_type: 'fuzzy', confidence_score: fuzzy_score
      })
    else:
      exceptions.append({
        txn_id, date, amount, currency, bank_reference: reference,
        description, suggested_account_code,
        exception_reason: 'no_match' | 'low_confidence',
        confidence_score: fuzzy_score | null
      })

// Post confirmed matches to Xero
for each pair in matched_pairs:
  xero.PUT /BankTransactions/{pair.xero_txn_id}
    -> IsReconciled: true
    -> pair.xero_reconciliation_id = response.BankTransactionID

// Write exceptions to Google Sheets
google_sheets.append_rows(
  spreadsheet_id: EXCEPTIONS_SHEET_ID,
  sheet_tab: 'exceptions',
  rows: exceptions[]   // columns: txn_id, date, amount, currency, bank_reference,
                       //          description, suggested_account_code,
                       //          exception_reason, confidence_score,
                       //          bookkeeper_action, resolved_at
)

run_summary = {
  run_id, matched_count, exception_count,
  closing_balance, currency,
  exceptions_sheet_url, completed_at
}

// ─────────────────────────────────────────────────────────────────
// HANDOFF: Matching and Exceptions Agent -> Notifications and Close Agent (Phase 1)
// ─────────────────────────────────────────────────────────────────
orchestration_layer.pass_payload(run_summary)

// ─────────────────────────────────────────────────────────────────
// AGENT 3: Notifications and Close Agent (Phase 1 - alert)
// ─────────────────────────────────────────────────────────────────
slack.chat.postMessage({
  channel: '#finance-reconciliation',
  text: 'Run {run_id}: matched={matched_count}, exceptions={exception_count},
         closing_balance={currency} {closing_balance}. Link: {exceptions_sheet_url}'
})

if exception_count == 0:
  // Skip Phase 2 wait; proceed directly to Xero close
  goto PHASE_2_CLOSE

// ─────────────────────────────────────────────────────────────────
// HUMAN STEP: Bookkeeper reviews exceptions in Google Sheets (~20 min)
// Sets exceptions_status cell to 'APPROVED' when complete
// ─────────────────────────────────────────────────────────────────

// ─────────────────────────────────────────────────────────────────
// HANDOFF: Google Sheets approval event -> Notifications and Close Agent (Phase 2)
// ─────────────────────────────────────────────────────────────────
sheet_event: { run_id, exceptions_status: 'APPROVED', approved_by, approved_at }
  -> orchestration_layer.trigger_phase_2(sheet_event)

// ─────────────────────────────────────────────────────────────────
// AGENT 3: Notifications and Close Agent (Phase 2 - close)
// ─────────────────────────────────────────────────────────────────
PHASE_2_CLOSE:
xero.PUT /BankStatements({
  BankAccountID: BANK_ACCOUNT_ID,
  StatementDate: today,
  EndingBalance: closing_balance,
  Notes: 'Reconciliation {run_id} closed by {approved_by} at {approved_at}'
})
  -> xero_period_close_id

gmail.send({
  to: FINANCE_LEAD_EMAIL,
  subject: 'Reconciliation closed for period ending {StatementDate}',
  body: '...matched={matched_count}, exceptions resolved, period close ID={xero_period_close_id}'
})

run_log_table.update({ run_id, status: 'COMPLETE', closed_at: ISO8601 })

// ─────────────────────────────────────────────────────────────────
// END OF FLOW
// ─────────────────────────────────────────────────────────────────