Skip to content

Financial Analytics Reports (Tier 2)

Date: 2026-06-21

Problem / motivation

Financial KPI Dashboard (Feature 1) added at-a-glance KPI tiles. This feature is the fast-follow it promised: the six heavier, drill-down reports that the tiles summarise. Each answers an operational question the existing report catalogue doesn't:

  • ITBMS liability — what VAT do I owe this period (output tax collected − input tax paid)?
  • Customer profitability — which customers actually make me money (margin, not just revenue)?
  • Project profitability — is each job profitable, and how does it track vs. budget?
  • Inventory turnover & dead stock — what's moving, what's dead capital on the shelf?
  • AR collections — who owes me, how overdue, how concentrated is the risk?
  • Cash-flow forecast — given open AR/AP due dates and recurring items, will I run short?

All six are computed from data that already exists. No new tables.

In scope

  • Six new GraphQL report queries under app/graphql/reports/ (see GraphQL surface).
  • Four follow the existing paginated GenericReportPageResponse[T] + CSV-export pattern (customer profitability, project profitability, inventory turnover, AR collections).
  • Two return dedicated summary shapes (ITBMS liability, cash-flow forecast) because they carry period totals, not just a row list.
  • Pure, unit-tested builders for every report's derived math (margins, variance, turnover, aging buckets, net VAT, weekly cash bucketing + runway).

Out of scope

  • No new tables / columns / migrations / backfill. All reports reuse the existing Path.REPORTS permission (no new RBAC Path).
  • Project expenses & supplier costs — only Invoice/Order/Quote/Budget carry project_id; the ledger and expenses do not. Project P&L therefore covers revenue, COGS (from invoice-line cost snapshots), gross profit, and budget variance — not operating expenses.
  • PDF rendering (CSV/Excel export via getReportPresignedUrl is included where the paginated pattern provides it).

Data model changes

None. No alembic revision.

What's being implemented

New area packages under app/graphql/reports/{queries,services,repositories,strawberry}/<area>/ for each of: itbms_liability, customer_profitability, project_profitability, inventory_turnover, ar_collections, cash_flow_forecast.

Per report: a repository (raw aggregation SQL), a response type (extends BaseReportResponse for the paginated ones), a pure builder where there's real computation, a service (wires repo → builder → GenericReportPageResponse/dedicated response), and a query (Path.REPORTS).

Reused: FinancialReportRepository.get_cash_balance_before (opening cash), the invoice/supplier balance tax_amount + Expense.tax_amount (VAT), InvoiceDetail.unit_cost_snapshot (COGS), InventoryRepository.get_total_quantity (on-hand), Item.average_cost, the recurring invoice/expense schedules, and the ~Invoice.is_voided_or_canceled hybrid (void exclusion).

GraphQL surface

type Query {
  itbmsLiabilityReport(dateFilter: DateRangeFilter!): ItbmsLiabilityReport!
  customerProfitabilityReport(dateFilter: DateRangeFilter!, generateCsv: Boolean! = false): CustomerProfitabilityPage!
  projectProfitabilityReport(dateFilter: DateRangeFilter!, generateCsv: Boolean! = false): ProjectProfitabilityPage!
  inventoryTurnoverReport(dateFilter: DateRangeFilter!, deadStockDays: Int! = 90, generateCsv: Boolean! = false): InventoryTurnoverPage!
  arCollectionsReport(generateCsv: Boolean! = false): ArCollectionsPage!
  arCollectionsEffectiveness(dateFilter: DateRangeFilter!): ArCollectionsEffectiveness!
  cashFlowForecast(startDate: Date!, weeks: Int! = 13): CashFlowForecast!
}
  • *Page = GenericReportPageResponse[<Row>] (records + csvUrl).
  • ArCollections rows include avgDaysToPay — the client's amount-weighted historical invoice-to-receipt days (null if they have no recorded payments).
  • ArCollectionsEffectiveness — startDate, endDate, beginningAr, creditSales, collections, endingAr, endingCurrentAr, collectionEffectivenessIndex, dso.
  • ItbmsLiabilityReport — startDate, endDate, outputTax, inputTax, netPayable, byRate: [ItbmsRateLine].
  • CashFlowForecast — startDate, weeks, openingCash, projectedClosingCash, lowestBalance, lowestBalanceWeekStart, runwayWeeks, buckets: [CashFlowWeek].

RBAC

  • Reuses Path.REPORTS. No new Path/Resource, no backfill.

Frontend contract

  • Paginated reports: read records (one row per entity) + csvUrl (set when generateCsv: true).
  • itbmsLiabilityReport: render the three totals as the headline; byRate is the breakdown.
  • cashFlowForecast: plot buckets[].runningBalance; surface runwayWeeks (null = no shortfall in the horizon) and the lowestBalance / lowestBalanceWeekStart as the risk callout.
  • Money fields are Decimal; percentages are whole-number percents; a null ratio/percent means "not computable" (render a dash).

Open questions

  • None blocking. Days-to-pay and project opex are explicit deferrals (see out of scope).

What shipped

All six reports landed, each as a query + service + (pure) builder + response, with repositories for the five that aggregate directly (AR collections reuses the existing aged-receivable repository). task all is green and 21 reports tests pass (7 new pure-builder tests covering VAT netting, margins, turnover/dead-stock, aging buckets, and cash bucketing + runway).

  • itbmsLiabilityReport — output vs input ITBMS, net payable, per-rate breakdown.
  • customerProfitabilityReport — per-client revenue, COGS, gross profit, margin, invoice count.
  • projectProfitabilityReport — per-project revenue, COGS, margin, budget consumption.
  • inventoryTurnoverReport — per-item turnover, days-on-hand, last-sale, dead-stock flag.
  • arCollectionsReport — per-client aging buckets, overdue %, oldest age, weighted avg age, and avgDaysToPay (amount-weighted invoice→receipt days per client).
  • arCollectionsEffectiveness — period CEI, DSO, and the underlying AR/cash figures.
  • cashFlowForecast — weekly inflow/outflow/running balance, lowest balance, runway.

Correction (vs. the first draft of this plan): customer days-to-pay and CEI were initially deferred on the belief that sales payments had no invoice link. They do — customer collections are receipts (InvoiceReceiptDetail.invoice_id → dated Receipt), distinct from the supplier-side payments module. Both metrics now ship. endingCurrentAr in the CEI uses the live aging snapshot, so the index is exact for windows ending at/near today and approximate for older windows. Still deferred: project operating expenses (no project_id on the ledger or expenses). No migration, no backfill, reused Path.REPORTS.

Future additions

  • Point-in-time current AR for CEI — reconstruct endingCurrentAr as of any past endDate (from receipts applied through that date) so historical-period CEI is exact, not approximated from the live aging snapshot.
  • Project operating expenses — once expenses/ledger carry project_id.
  • Cash-flow forecast scenarios — best/worst-case collection assumptions on the AR leg.