Developer Handover Pack

Everything needed to build the automation from scratch: current state, full agent specs, data flow, and the build stack.

4 pagesPDF · Technical

FS-DOC-04Technical

Developer Handover Pack

Financial Reporting Automation

[YourCompany.com] · Finance Department · Prepared by FullSpec · [Today's Date]

This document gives the FullSpec build team everything needed to construct, wire, and test the Financial Reporting automation end to end. It covers the current-state step map with bottleneck flags, full agent specifications with IO contracts, a complete data-flow trace, and the recommended build stack. The process owner and finance manager are responsible for confirming API access, variance thresholds, and the Google Sheets template structure before build begins. Everything else, including agent logic, API connections, approval gates, error handling, and deployment, is handled by FullSpec.

01Process snapshot

Step

Name

Description

Export Profit and Loss from Xero

Bookkeeper logs into Xero, selects the correct date range, and exports the P&L as CSV or Excel. Time cost: 15 min/cycle.

Export Balance Sheet from Xero

A second export is run for the balance sheet, saved locally, and named for the period. Time cost: 10 min/cycle.

Export Cash-Flow Data

Bookkeeper runs a third Xero export for cash-flow movements and saves to the same local folder. Time cost: 10 min/cycle.

Pull Revenue Pipeline from HubSpot

Finance manager exports current pipeline and closed-won totals from HubSpot to cross-check against booked revenue. Time cost: 15 min/cycle.

Paste Data into Master Spreadsheet (BOTTLENECK)

All exported files are opened and figures are copied cell by cell into the master Google Sheets report. Time cost: 45 min/cycle.

Recalculate Totals and Verify Formulas (BOTTLENECK)

Formulas are checked for broken references, totals are cross-checked against source exports, and discrepancies are resolved manually. Time cost: 30 min/cycle.

Flag and Investigate Variances

Finance manager reviews budget-versus-actual figures and writes brief notes on any line item more than 10% off budget. Time cost: 40 min/cycle.

Format Charts and Summary Page

Charts are refreshed, the summary page is formatted for readability, and the document is styled to match the standard template. Time cost: 25 min/cycle.

Export Report to PDF

Completed spreadsheet is exported as a PDF and saved to the shared Google Drive reporting folder. Time cost: 10 min/cycle.

Distribute Report to Stakeholders

PDF is shared via Slack to the owner, board members, or department heads, and a confirmation message is sent. Time cost: 15 min/cycle.

Time cost summary: Total manual time per cycle is 215 minutes (3 hours 35 minutes). At approximately 4 report cycles per month, that equates to roughly 860 minutes (14.3 hours) per month and approximately 6 hours per week when weighted across weekly flash and monthly full-pack runs. The automation replaces steps 1 through 7, 9, and 10 entirely. Step 8 (format charts and summary page) is absorbed into the pre-built template structure and does not require a dedicated agent. The single remaining human step is the finance manager reviewing the drafted variance commentary and approving distribution, estimated at 25 minutes per cycle.

Developer Handover PackPage 1 of 4

FS-DOC-04Technical

02Agent specifications

Data Fetch Agent

Connects to Xero and HubSpot on the configured schedule, authenticates via OAuth 2.0, and retrieves all required financial figures for the reporting period. Writes P&L, balance sheet, and cash-flow values from Xero, and closed-won plus open pipeline totals from HubSpot, into the exact cell references defined in the Google Sheets report template. Triggers formula recalculation by updating a designated refresh cell after all writes are complete. Confirms completion by writing a status flag ('DATA_READY') to a control tab before handing off to the Variance Analysis Agent.

Trigger

Scheduled cron: first working day after period close for monthly packs; every Monday 07:00 local time for weekly flash reports. Schedule is configured per report type in the orchestration layer.

Tools

Xero (OAuth 2.0 API), HubSpot (Private App API key), Google Sheets (Service Account via Sheets API v4)

Replaces steps

Steps 1, 2, 3, 4, 5, 6

Estimated build

14 hours, Moderate complexity

// Input
report_type: 'monthly_full' | 'weekly_flash'
period_start: ISO 8601 date string  // e.g. '2025-03-01'
period_end:   ISO 8601 date string  // e.g. '2025-03-31'
sheet_id:     string  // Google Sheets document ID from env
xero_tenant_id: string  // from env

// Output
status:             'DATA_READY' | 'FETCH_ERROR'
cells_written:      integer  // count of Sheets cells updated
xero_pl_rows:       integer  // P&L line items written
xero_bs_rows:       integer  // balance sheet line items written
xero_cf_rows:       integer  // cash-flow line items written
hubspot_pipeline_total: number  // open pipeline value USD
hubspot_closed_won:     number  // closed-won revenue for period
fetch_timestamp:    ISO 8601 datetime
error_detail:       string | null

Xero OAuth 2.0 scopes required: openid, profile, email, accounting.reports.read, accounting.transactions.read. A connected app must be registered in Xero developer portal before build begins. OAuth token refresh must be handled automatically; store refresh_token in the credential store, not in environment variables.
HubSpot connection must use a Private App token scoped to crm.objects.deals.read and crm.objects.pipelines.read. Confirm the HubSpot tier supports API access (Starter and above). Token stored in the shared credential store under key HUBSPOT_PRIVATE_APP_TOKEN.
Google Sheets writes must use a dedicated Service Account (not a personal OAuth token) so the agent runs unattended. Service Account email must be granted Editor access to the report Sheet before build starts.
Cell reference map (e.g. P&L revenue to cell B5, gross profit to B12) must be finalised and locked before agent build. Any future template layout change requires a corresponding mapping update in the orchestration layer config.
Dedupe behaviour: if the agent fires twice for the same period (e.g. due to a retry), it must check the control tab for an existing 'DATA_READY' flag for that period and skip re-writing to avoid overwriting manual corrections.
Fallback: if Xero or HubSpot returns a non-200 response after three retries with exponential backoff (2 s, 4 s, 8 s), write status 'FETCH_ERROR' to the control tab and trigger an error alert to the monitoring Slack channel. Do not proceed to the Variance Analysis Agent.
Confirm with the finance manager whether the Xero tenant has multi-currency enabled; if so, all figures must be converted to the base reporting currency before writing to Sheets using the period-end exchange rate from the Xero API.

Variance Analysis Agent

Reads the fully populated Google Sheets report after the Data Fetch Agent sets the 'DATA_READY' status flag. Compares each actual figure in the actuals column against the corresponding budget figure in the budget column for every line item in scope. Calculates the variance as both an absolute dollar value and a percentage. For any line item where the absolute variance percentage exceeds the configured threshold (default 10%), it generates a plain-language commentary sentence describing the movement and probable cause category. Assembles all flagged items into a structured commentary block and writes it to the designated summary section cell range. Sets a second status flag ('ANALYSIS_READY') on the control tab and sends a review notification to the finance manager via Slack, including a direct link to the report sheet.

Trigger

Control tab status flag transitions to 'DATA_READY' for the current period. The orchestration layer polls this flag every 2 minutes after the Data Fetch Agent fires.

Tools

Google Sheets (Service Account, Sheets API v4)

Replaces steps

Step 7

Estimated build

10 hours, Moderate complexity

// Input
sheet_id:           string  // same document ID as Data Fetch Agent
actuals_range:      string  // e.g. 'Report!C5:C80'
budget_range:       string  // e.g. 'Report!D5:D80'
line_item_labels:   string  // e.g. 'Report!A5:A80'
variance_threshold: number  // default 0.10 (10%), configurable in env
commentary_target:  string  // e.g. 'Summary!B3:B30' cell range for output
control_tab:        string  // e.g. 'Control!A1'

// Output
status:             'ANALYSIS_READY' | 'ANALYSIS_ERROR'
flagged_items:      integer  // count of line items above threshold
commentary_written: boolean
variance_summary: [
  { line_item: string, actual: number, budget: number,
    variance_abs: number, variance_pct: number,
    flag: boolean, commentary_line: string }
]
analysis_timestamp: ISO 8601 datetime
error_detail:       string | null

// On approval
approval_status:    'APPROVED' | 'REJECTED' | 'TIMEOUT'
approved_by:        string  // finance manager identifier
approval_timestamp: ISO 8601 datetime

The variance threshold value (default 10%) must be stored as a named environment variable (VARIANCE_THRESHOLD) so it can be adjusted without a code change. Confirm the initial value with the finance manager before go-live.
Budget figures must exist in the Google Sheets template before the agent can run. If any budget cell in the defined range is empty or non-numeric, the agent must skip that row and log a warning rather than failing the entire run.
Commentary generation uses a rule-based template (not an external LLM API) to keep the build deterministic and auditable. Templates such as '[Line item] came in [X]% above/below budget for the period' are sufficient for v1. An LLM upgrade path can be added in a later iteration if required.
The Slack review notification must include: the report period, the count of flagged items, the variance threshold in use, and a direct hyperlink to the Google Sheet. Use the Slack API chat.postMessage method with the finance manager's Slack user ID or a dedicated review channel.
If the finance manager takes no action within the configured review window (default 24 hours, stored as REVIEW_WINDOW_HOURS), the orchestration layer must set approval_status to 'TIMEOUT' and proceed to the Distribution Agent. This behaviour must be confirmed with the process owner before build.
The agent must not modify any cell outside the commentary_target range. Write access should be scoped at the Service Account level where Sheets API permissions allow.

Distribution Agent

Fires after the finance manager approves the report via the review notification link, or after the configured review window elapses without a rejection. Calls the Google Sheets API to export the report sheet as a PDF using the built-in export URL endpoint, then saves the file to the designated Google Drive folder with a standardised filename including the period and report type. Posts a Slack message to the configured finance channel containing the Google Drive file link and a brief summary of the key variance highlights drawn from the Variance Analysis Agent output. Writes a final status flag ('DISTRIBUTED') to the control tab and logs the distribution event.

Trigger

Control tab approval_status transitions to 'APPROVED' or 'TIMEOUT'. Orchestration layer polls every 5 minutes after the Variance Analysis Agent sets 'ANALYSIS_READY'.

Tools

Google Drive (Service Account, Drive API v3), Slack (Bot Token, chat.postMessage)

Replaces steps

Steps 9, 10

Estimated build

8 hours, Simple complexity

// Input
sheet_id:           string  // Google Sheets document ID
sheet_gid:          integer  // specific tab GID for PDF export
drive_folder_id:    string  // destination Drive folder ID from env
report_type:        'monthly_full' | 'weekly_flash'
period_label:       string  // e.g. 'March-2025'
slack_channel_id:   string  // from env e.g. C0XXXXXXX
variance_summary:   array   // passed from Variance Analysis Agent output
approval_status:    'APPROVED' | 'TIMEOUT'

// Output
status:             'DISTRIBUTED' | 'DISTRIBUTION_ERROR'
pdf_file_id:        string  // Google Drive file ID of saved PDF
pdf_file_name:      string  // e.g. 'FinancialReport_March-2025_monthly_full.pdf'
drive_share_link:   string  // shareable Drive URL
slack_message_ts:   string  // Slack message timestamp for audit
distribution_timestamp: ISO 8601 datetime
error_detail:       string | null

PDF export uses the Google Sheets export URL pattern: https://docs.google.com/spreadsheets/d/{sheet_id}/export?format=pdf&gid={sheet_gid}&portrait=true&fitw=true. Authenticate the request with the Service Account Bearer token.
The Drive destination folder (drive_folder_id) must be created and shared with the Service Account before the agent is deployed. Folder structure should follow: /Financial Reports/{Year}/{Month} or a flat structure agreed with the finance manager.
PDF filename must follow the pattern: FinancialReport_{PeriodLabel}_{ReportType}.pdf using the period_label and report_type inputs. This ensures reports are uniquely named and sortable.
Slack Bot Token must have scopes: chat:write, files:write (if attaching the PDF directly in future), channels:read. Store as SLACK_BOT_TOKEN in the credential store.
The Slack message body must include: period label, approval status (approved vs auto-released), count of flagged variances, and the Drive share link. Do not include raw financial figures in the Slack message body for security reasons.
If the Drive upload or Slack post fails, write 'DISTRIBUTION_ERROR' to the control tab and send an alert to the monitoring channel. Do not retry distribution automatically more than once to avoid duplicate Slack messages.

Developer Handover PackPage 2 of 4

FS-DOC-04Technical

03End-to-end data flow

Full trigger-to-output data flow, Financial Reporting Automation

// ── TRIGGER ──────────────────────────────────────────────────────────────
SCHEDULE fires for report_type ('monthly_full' | 'weekly_flash')
  inputs:  report_type, period_start, period_end
  outputs: job_id (uuid), trigger_timestamp

// ── AGENT 1: DATA FETCH AGENT ────────────────────────────────────────────
DATA FETCH AGENT receives: report_type, period_start, period_end,
                            sheet_id, xero_tenant_id

  [Step A] Xero API call: GET /api.xro/2.0/Reports/ProfitAndLoss
    params:  fromDate=period_start, toDate=period_end,
             tenantId=xero_tenant_id
    returns: pl_rows[] { account_name, net_amount }

  [Step B] Xero API call: GET /api.xro/2.0/Reports/BalanceSheet
    params:  date=period_end, tenantId=xero_tenant_id
    returns: bs_rows[] { account_name, balance }

  [Step C] Xero API call: GET /api.xro/2.0/Reports/CashSummary
    params:  fromDate=period_start, toDate=period_end
    returns: cf_rows[] { label, total }

  [Step D] HubSpot API call: POST /crm/v3/objects/deals/search
    filter:  closedate in [period_start, period_end],
             dealstage = 'closedwon'
    returns: hubspot_closed_won (sum of amount field)

  [Step E] HubSpot API call: GET /crm/v3/pipelines/deals
    returns: hubspot_pipeline_total (sum of open deal amounts)

  [Step F] Google Sheets API: batchUpdate to sheet_id
    writes:  pl_rows  -> range 'Report!B5:B{n}'
             bs_rows  -> range 'Report!B{n+1}:B{m}'
             cf_rows  -> range 'Report!B{m+1}:B{p}'
             hubspot_closed_won  -> cell 'Report!B82'
             hubspot_pipeline_total -> cell 'Report!B83'
             refresh_trigger     -> cell 'Control!B2' = NOW()

  [Step G] Google Sheets API: write control flag
    writes:  'Control!A1' = 'DATA_READY'
             'Control!A2' = fetch_timestamp
             'Control!A3' = cells_written (integer)

  HANDOFF: Orchestration layer polls 'Control!A1' every 2 min
           until value = 'DATA_READY', then fires Variance Analysis Agent

// ── AGENT 2: VARIANCE ANALYSIS AGENT ────────────────────────────────────
VARIANCE ANALYSIS AGENT receives: sheet_id, actuals_range,
                                   budget_range, line_item_labels,
                                   variance_threshold, commentary_target

  [Step A] Google Sheets API: batchGet
    reads:  actuals_range  'Report!C5:C80'  -> actuals[]
            budget_range   'Report!D5:D80'  -> budgets[]
            line_item_labels 'Report!A5:A80' -> labels[]

  [Step B] For each row i:
    variance_abs[i] = actuals[i] - budgets[i]
    variance_pct[i] = variance_abs[i] / budgets[i]
    flag[i]         = abs(variance_pct[i]) > variance_threshold

  [Step C] For each flag[i] == true:
    commentary_line[i] = template(
      '{labels[i]} came in {pct}% {above|below} budget
       ({+/-}${abs} for the period).'
    )

  [Step D] Google Sheets API: batchUpdate
    writes:  assembled commentary block -> 'Summary!B3:B30'
             flagged_items count         -> 'Control!A4'
             'Control!A1'               = 'ANALYSIS_READY'
             'Control!A5'               = analysis_timestamp

  [Step E] Slack API: chat.postMessage
    channel: FINANCE_MANAGER_USER_ID or REVIEW_CHANNEL_ID
    text:    'Report ready for review: {period_label}.
              {flagged_items} variance(s) flagged above {threshold}%.
              Review link: {sheet_url}
              Approve: {approval_url} | Reject: {rejection_url}'

  HANDOFF: Orchestration layer polls 'Control!A1' every 5 min
           until value = 'APPROVED' | 'TIMEOUT' | 'REJECTED'
           approval_status written to 'Control!A6' by webhook callback

// ── APPROVAL GATE (HUMAN STEP) ───────────────────────────────────────────
FINANCE MANAGER reviews draft in Google Sheets (~25 min)
  clicks Approve link -> webhook POST sets 'Control!A6' = 'APPROVED'
  clicks Reject link  -> webhook POST sets 'Control!A6' = 'REJECTED'
  no action in REVIEW_WINDOW_HOURS -> orchestration sets 'TIMEOUT'

// ── AGENT 3: DISTRIBUTION AGENT ─────────────────────────────────────────
DISTRIBUTION AGENT receives: sheet_id, sheet_gid, drive_folder_id,
                              report_type, period_label,
                              slack_channel_id, variance_summary[],
                              approval_status

  [Step A] HTTP GET: Sheets export URL
    url:    https://docs.google.com/spreadsheets/d/{sheet_id}/export
            ?format=pdf&gid={sheet_gid}&portrait=true&fitw=true
    auth:   Service Account Bearer token
    result: pdf_blob (binary)

  [Step B] Google Drive API: files.create (multipart upload)
    name:     'FinancialReport_{period_label}_{report_type}.pdf'
    parents:  [drive_folder_id]
    mimeType: 'application/pdf'
    returns:  pdf_file_id, drive_share_link

  [Step C] Slack API: chat.postMessage
    channel: slack_channel_id
    text:    'Financial Report: {period_label} ({report_type})
              Status: {approval_status}
              Variances flagged: {flagged_items}
              View report: {drive_share_link}'
    returns: slack_message_ts

  [Step D] Google Sheets API: write final control flag
    writes:  'Control!A1'  = 'DISTRIBUTED'
             'Control!A7'  = distribution_timestamp
             'Control!A8'  = pdf_file_id
             'Control!A9'  = slack_message_ts

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

Developer Handover PackPage 3 of 4

FS-DOC-04Technical

04Recommended build stack

Orchestration layer

A workflow automation tool with native support for scheduled triggers, HTTP request nodes, and a shared credential store. One workflow per agent (three total): Data Fetch Workflow, Variance Analysis Workflow, Distribution Workflow. A fourth lightweight workflow handles the approval gate webhook callback and writes the approval_status to the Google Sheets control tab. All three agent workflows share credentials from a single encrypted credential store (Xero OAuth, HubSpot Private App Token, Google Service Account JSON, Slack Bot Token). Workflows are version-controlled and stored in the FullSpec project repository.

Webhook configuration

Two inbound webhooks are required: (1) an Approval webhook that accepts POST from the finance manager's approve or reject link click, reads the action parameter ('approved' | 'rejected'), and writes the result to 'Control!A6' in the report Sheet; (2) an Error alert webhook used by the orchestration layer to receive failure notifications from any agent and forward them to the monitoring Slack channel. Both webhook endpoints must use HTTPS and validate a shared secret header (X-Webhook-Secret) stored in the credential store as WEBHOOK_SECRET.

Templating approach

The Google Sheets report template is the single source of truth for report layout. Cell references are externalised into a JSON config file (cell_map.json) committed to the project repository, containing entries such as { 'pl_revenue': 'Report!B5', 'bs_total_assets': 'Report!B45' }. The automation reads this config at runtime so that layout changes only require updating cell_map.json rather than modifying workflow logic. The variance commentary template strings are stored in a separate commentary_templates.json file and follow the pattern: '{label} came in {pct}% {direction} budget ({sign}${abs} for the period).' PDF export uses the native Google Sheets export URL with parameters for portrait orientation and fit-to-width.

Error logging

All agent run events, including successes, warnings, and failures, are written to a Supabase table (schema: automation_runs) with columns: run_id (uuid), workflow_name (text), period_label (text), status (text), error_detail (text), created_at (timestamptz). On any 'FETCH_ERROR', 'ANALYSIS_ERROR', or 'DISTRIBUTION_ERROR' status, the orchestration layer triggers a Slack alert to the designated monitoring channel (MONITORING_SLACK_CHANNEL_ID) with the run_id, workflow name, period, and error_detail so the FullSpec team can investigate without waiting for a manual check. Supabase connection string stored as SUPABASE_URL and SUPABASE_SERVICE_KEY in the credential store.

Testing approach

All three agents are built and tested against a dedicated sandbox environment before connecting to production credentials. Xero sandbox tenant (available via Xero developer portal demo company) and a HubSpot sandbox portal are used for API testing. A cloned copy of the Google Sheets report template is used as the test sheet, with a separate sheet_id stored as SHEET_ID_SANDBOX in the environment. Two historical periods are replayed through the full workflow to validate cell writes, variance calculation accuracy, PDF export fidelity, and Slack message formatting. Sandbox and production credentials are kept in separate credential sets within the shared credential store and are never mixed.

Estimated total build time

Data Fetch Agent: 14 hours. Variance Analysis Agent: 10 hours. Distribution Agent: 8 hours. End-to-end integration testing and parallel-run support: 10 hours (included in the 32-hour total build effort). Total: 32 hours across a 3 to 4 week delivery window.

Pre-build checklist: (1) Xero connected app created and OAuth scopes confirmed (accounting.reports.read, accounting.transactions.read). (2) HubSpot Private App token issued with crm.objects.deals.read and crm.objects.pipelines.read scopes. (3) Google Service Account created, JSON key downloaded, and Editor access granted to the report Sheet and destination Drive folder. (4) Slack Bot Token issued with chat:write and channels:read scopes, and added to the target finance channel. (5) Google Sheets report template audited and cell_map.json populated with final cell references. (6) Variance threshold confirmed with the finance manager and set as VARIANCE_THRESHOLD in the environment. (7) Review window duration confirmed and set as REVIEW_WINDOW_HOURS. Contact support@gofullspec.com if any of these items are blocked before the build start date.

Developer Handover PackPage 4 of 4

Developer Handover Pack