Design Rationale — Reporting
Non-obvious design choices for the reporting module — the WHY behind each decision.
Reporting Module (Locked 2026-06-11)
DR1 — Snapshots freeze computed values, not source rows (reference-don't-copy reconciliation)
Decision: inventory_valuation_snapshot and period_close_snapshot store point-in-time COMPUTED NUMERIC VALUES (avg_cost_cents, valuation_cents, aggregated AR/AP/COGS totals). They do NOT copy source-row text (no item name, no SKU, no customer name, no description). FK references to source entities stay live (item_variant_id, snapshot_run_id, site_id).
Why: item_variant.avg_cost_cents is recalculated by weighted-average costing on every purchase receipt. By mid-January, the December 31 average cost no longer exists anywhere in the live system. Recomputing "March gross profit" in June yields a different answer because avg_cost has changed. These computed values must be frozen at the moment of closing — they are unrecoverable otherwise. The FK references (item_variant_id, etc.) are NOT frozen because they remain stable; the live FK is more accurate than any copy.
Rejected: Snapshotting item names, SKUs, customer names, or other text alongside numeric values — violates reference-don't-copy; creates two sources of truth for names; text copies drift silently when source entities are renamed.
Guard: A snapshot row must contain ONLY computed numeric values that change over time, plus FK references that stay stable. If a column being added to a snapshot table is text (name, description, SKU), stop and ask whether the FK reference already provides that text at display time. The copy is wrong if the FK reference suffices.
DR2 — Idempotent period close (run-header partial unique)
Decision: report_snapshot_run has a partial UNIQUE on (tenant_id, snapshot_type, as_of_date) WHERE status = 'completed' AND deleted_at IS NULL. A month-end close for a given date can be successfully completed exactly once per tenant per snapshot type.
Why: Double-closing a financial period produces duplicate snapshot rows with identical dates, making historical reporting ambiguous. The run header exists specifically to make the close idempotent. A failed run (status = 'failed') does NOT block a retry — the unique clause applies to completed runs only. Same idempotency pattern as integrations.sync_run.
Rejected: Uniqueness on (tenant_id, snapshot_type, as_of_date) without the status = 'completed' filter — would prevent retrying after a failed close, making a failed job unrecoverable without manual DB intervention.
Guard: The partial unique is the don't-double-close-a-period guarantee. Do not relax it to allow multiple completed rows for the same period. A correction to a closed period must produce a new run, not an UPDATE to existing snapshot rows.
DR3 — period_close_snapshot reads-not-shadows billing.ar_statement
Decision: period_close_snapshot.ar_outstanding_cents is a summary figure read from billing.ar_statement at close time. billing.ar_statement continues to own AR period snapshots. Reporting adds the cross-module view (AR + AP + COGS in one record); it does not duplicate the AR snapshot.
Why: Billing already owns the AR period snapshot including opening_balance_cents, closing_balance_cents, and the included_charge_ids JSONB for line-level traceability. Duplicating this into Reporting would create two sources of truth for the same AR figure and mean Billing and Reporting must stay synchronized across separate tables. The correct design is: Reporting reads Billing's snapshot at close time and stores only the top-line summary figure it needs.
Rejected: Adding ar_statement-equivalent columns (opening balance, charge detail) to period_close_snapshot — duplicates Billing's data; creates dual-SOT for AR.
Guard: period_close_snapshot.ar_outstanding_cents is a READ of Billing state at close time. If more AR detail is needed, read more columns from billing.ar_statement — do not add them to period_close_snapshot.
DR4 — Matviews are the primary aggregation pattern (not tables); 30s-poll is locked
Decision: Expensive aggregations live in materialized views refreshed on *_changed events (CROSS_MODULE_CONTRACTS Rule 6). Simple source reads are live queries. Only must-freeze records get a base table. "Real-time" dashboards are polled every 30 seconds from materialized views — NOT streaming (locked in ARCHITECTURE.md §Real-Time vs Streaming Reconciliation).
Why: A snapshot table for every aggregation pattern would produce dozens of tables that each require a maintenance job, drift detection, and correction procedures. Matviews are the correct abstraction: they aggregate on demand, are refreshed on source-table events, and are never the source of truth. The 30s-poll matches the service_role refresh pattern without requiring CDC/streaming infrastructure.
Rejected: Snapshot tables for all aggregations — too many frozen tables, each with drift risk and maintenance cost. Streaming / CDC real-time push — deferred v2.0.
Guard: Before adding a new base table to reporting.*, verify it meets the must-freeze criterion: (1) source data changes after the reporting period ends, AND (2) the report must reflect what was true at a specific moment, AND (3) reconstruction from event history is not reliable or prohibitively expensive. All three required. Otherwise use a matview.
DR5 — site_id is a first-class reporting dimension
Decision: site_id is a dimension column on every snapshot table (inventory_valuation_snapshot.site_id NOT NULL, period_close_snapshot.site_id nullable for consolidated rows) and a declared dimension on every matview.
Why: Locked architecture decision (ARCHITECTURE.md §Multi-site reporting): "Site_id is a first-class dimension in reporting. Every report can be filtered or broken down by site." Treating site_id as a post-aggregation JOIN filter means every multi-site breakdown re-aggregates over the full tenant dataset. Carrying site_id as a declared dimension lets the query planner use a site-scoped index directly.
Rejected: Omitting site_id from snapshot tables and joining through source entities at query time — requires re-aggregation per breakdown; breaks the multi-site dashboard pattern.
Guard: Every new snapshot table or matview added to reporting.* MUST carry site_id as a declared dimension. A matview spec that lists site_id only in a WHERE filter (not in GROUP BY / dimension columns) is incomplete.
DR6 — Compliance reporting is Audit's; Reporting owns operational BI only
Decision: No GDPR/SAR/breach/regulator-bound tables in reporting.*. Compliance reporting lives in audit.*. Reporting = operational BI for operations staff only.
Why: Different audiences (compliance officers / regulators vs. operations staff), different data shapes, different retention and access requirements. Merging them would mean operations staff can access compliance data (wrong access model) or compliance staff must navigate BI dashboards (wrong UX).
Rejected: Adding "compliance dashboard" tables to reporting.* — wrong ownership; requires Reporting to understand GDPR workflows; creates access-control entanglement.
Guard: If a proposed reporting.* table describes compliance obligations, GDPR responses, breach incidents, regulator notifications, or DPA management — it belongs in audit.*. Reporting never references data_subject_request, data_breach_incident, compliance_task, or dpa_agreement.
DR7 — Terminal module + insert-once frozen snapshots
Decision: Nothing in v1.0 depends on reporting.*. inventory_valuation_snapshot and period_close_snapshot rows are insert-once — never mutated after creation. A correction produces a new report_snapshot_run, not an UPDATE.
Why (terminal): Reporting is the operational BI sink — reads from ~15 schemas, nothing reads from it as a dependency. Building it last means MV definitions don't require constant revision as upstream schemas change. No forward-refs from Reporting create blocking dependencies on other modules.
Why (insert-once): A snapshot row captures the state of the world at a specific moment. Mutating it retroactively silently changes historical financial reports — the kind of error that is hard to detect and may affect tax records, balance sheets, or audit trails.
Rejected: Allowing UPDATE on snapshot rows to "correct" errors — retroactive mutation breaks the frozen-record guarantee.