Reporting Tool

Overview

The Reporting tool provides a financial and utilisation dashboard for agency leadership. It combines Xero P&L data with Productive time/budget/invoice data to give a complete picture of business performance.

Data is cached in the report_snapshots D1 table to avoid hitting rate-limited APIs on every page load. Current month uses a 4-hour TTL; past months use 24-hour TTL.

Permissions

Access Level	Can View	Can Manage
Executive	Yes	Yes
Head	Yes	No
Manager	No	No
Lead	No	No
Employee	No	No

Executives can force-refresh cached data via the refresh endpoint.

Page Layout

Period Toggle

Header actions contain a Last Month / This Month toggle that controls all data on the page. Defaults to this month.

Financial Summary Cards (Row 1)

Four cards sourced from Xero Custom P&L Report:

• Revenue — total income for the period
• Expenses — total cost of sales + operating expenses
• Profit — revenue minus expenses
• Dividends — from custom report rows

Utilisation Summary Cards (Row 2)

Four cards sourced from Productive time entries:

• Billable Hours — hours logged against billable services
• Billable % — billable hours / worked hours
• Worked Hours — total hours logged by all people
• Worked % — worked hours / available hours (working days × 8 × headcount)

Derived Metric Cards (Row 3)

Four calculated cards combining both data sources:

• Effective Rate — billable revenue / billable hours
• Revenue per Head — revenue / active headcount
• Gross Margin % — (revenue − expenses) / revenue × 100
• Budget Burn % — actual hours / budgeted hours × 100

12-Month Trend Chart

Area chart with two toggle modes:

• Financial — Revenue and Profit areas over trailing 12 months
• Hours — Worked Hours and Billable Hours areas over trailing 12 months

Uses recharts with the shadcn ChartContainer pattern.

Breakdown Table

Data table with Squad / Team toggle:

Squad view columns: Squad, Billable Revenue, Invoiced Revenue, Project %, Support %

Team view columns: Name, Squad, Total Hours, Billable Hours, Project %, Support %

Footer row shows totals.

Data Sources

Xero

Custom P&L Report (configurable report ID, default 4041639) via GET /Reports/{id}. Requires accounting.reports.read OAuth scope — users must reconnect Xero after deployment.

The report includes standard P&L rows plus custom fields: Profit Ratio %, Company Tax, Actual Profit, Dividends, Individual Dividend.

Productive

• Time entries — org-wide with service includes for billable detection. Endpoint: GET /time_entries
• Budgets — with custom field for squad assignment. Endpoint: GET /budgets
• Invoices — with custom field for squad assignment. Endpoint: GET /invoices
• Custom field options — resolves option IDs to squad names. Endpoint: GET /custom_field_options

Custom field IDs are configurable in the Productive connection settings:

• Budget squad field (default: 12321)
• Invoice squad field (default: 12320)
• Project squad field (default: 12322)

Project vs Support Classification

Budgets tagged “Prepaid” are classified as Support. All others are classified as Project.

API Endpoints

Method	Path	Permission	Description
GET	/api/reporting/summary	view	Financial, utilisation, and derived metrics
GET	/api/reporting/breakdown	view	Squad or team breakdown table data
GET	/api/reporting/trends	view	12 months of trend data
POST	/api/reporting/refresh	manage	Force-clear cache and re-fetch

Query parameters: period (this_month | last_month), view (squad | team, breakdown only).

Components

Component	Purpose
reporting-page.tsx	Main page with period toggle, card grids, chart, and table
summary-card.tsx	Reusable KPI card with format options (currency, percent, hours, rate)
trend-chart.tsx	12-month area chart with Financial/Hours toggle
reporting-table.tsx	Breakdown data table with Squad/Team toggle

Caching

The report_snapshots table stores parsed API responses keyed by (report_type, period_key):

• xero_pnl — Xero P&L report data
• productive_time — Productive time entry aggregations
• productive_budgets — Productive budget data with squad classification
• productive_invoices — Productive invoice data with squad classification

TTL: 4 hours for current month, 24 hours for past months. Executives can force-refresh via the POST endpoint.