Verifiable business reporting

Business Reporting

When to Use

Use when implementing sales pipeline reports, revenue/bookings/invoicing dashboards, inventory valuation/aging reports, fulfillment KPIs, or resource utilization summaries.

Core Rule: Every Metric Must Have an Explicit, Verifiable Definition

Ambiguous dashboard numbers destroy trust ("Why does the Sales Dashboard say 120k EUR while Finance says 95k EUR?"). Never write an aggregation query without defining five dimensions explicitly:

1. Distinct Commercial Metric Definitions

Never conflate pipeline, bookings, billings, and cash:

• Open Pipeline Value: Sum of amount on open Opportunities (status = 'open') grouped by stage, separate from Weighted Pipeline ($\sum \text{amount} \times \text{stage_probability}$).
• Bookings / Confirmed Order Value: Sum of net/gross amounts on confirmed SalesOrders (status IN ('confirmed', 'completed'), excluding draft and cancelled), anchored by confirmed_at date.
• Fulfilled / Delivered Value: Value of order lines actually shipped/delivered (fulfillment_lines), separate from unshipped backlog (open_order_backlog).
• Invoiced Revenue (Billings): Net amount on finalized Invoices minus Credit Notes (invoice_date / accounting period), never including draft or voided invoices.
• Collected Cash: Settled payments minus refunds.

2. Multi-Currency Aggregation Discipline

• Never Sum Raw Amounts Across Different Currencies (SUM(amount) where rows mix EUR, USD, and HUF)!
• Always either:
  1. Group and display totals per currency (EUR: 45,000.00, USD: 12,500.00), or
  2. Convert each transaction into the workspace's reporting base currency using its locked transaction exchange_rate_to_base (or the specified reporting date rate) and state the reporting currency and rate basis explicitly.

3. Timezone, Period Boundary & As-Of Snapshot Semantics

• Explicit Timestamp & Timezone:
  • State which date column governs the report (created_at vs. confirmed_at vs. expected_close_date vs. invoice_date).
  • Convert UTC timestamps to the workspace's configured business timezone (AT TIME ZONE workspace.timezone) before truncating by day/month/quarter (DATE_TRUNC('month', ...)), so a sale at 2026-03-31 23:30 CEST is not bucketed into the wrong month.
• Point-in-Time (As-Of) vs. Current State:
  • Distinguish current state ("What is open right now?") from historical point-in-time reconstruction ("What was the pipeline or stock balance on March 31st?"), using immutable stage history or inventory movement ledgers for historical queries.

4. Drill-Down Reconciliation & Permission Parity

• Summary == Sum of Drill-Down Rows: Every KPI card or chart bucket must reconcile 100% with the underlying drill-down table query using the exact same filter predicates.
• Enforce Tenant & Role Scoping: Reports and exports must apply the caller's exact tenant and department/territory visibility rules.