reporting — Phase 10

Schema locked 2026-06-11. 3 tables, 43 cols: report_snapshot_run (16), inventory_valuation_snapshot (12), period_close_snapshot (15).

Reporting is the terminal operational-BI module — reads from ~15 locked schemas, nothing depends on it, zero forward-refs OUT. The schema contains only must-freeze snapshot tables; expensive aggregations live in materialized views (defined below as locked non-table deliverables), and simple source reads are live queries. All served via ReportingService using service_role (pooled port 6543, bypasses RLS intentionally — cross-schema reads cannot use tenant RLS policies).

Design principles (document at every session touching this schema):

• Snapshots freeze computed results, not source copies. The two snapshot tables store point-in-time computed values — avg_cost_cents as-of-then, valuation_cents, aggregated period totals — that become unrecoverable later (weighted-average cost changes with every receipt; period-close figures change as avg_cost drifts). FK references to source entities stay (item_variant_id, snapshot_run_id); only numeric results freeze. Names and descriptions are never snapshotted — looked up live from source FKs at display time. This is the reference-don't-copy reconciliation: a snapshot is an intentional frozen result, not a drift-prone cache, and it copies computed values not rows.
• Matviews are the primary aggregation pattern (not tables). Expensive aggregations live in matviews refreshed on *_changed events per CROSS_MODULE_CONTRACTS Rule 6; queried via service_role. Live queries for simple source reads. Only must-freeze records get a base table — everything else stays derived.
• Reads billing.ar_statement; doesn't shadow it. Billing owns AR period snapshots. reporting.period_close_snapshot adds the cross-module summary (AR + AP + COGS in one record) — it reads billing.ar_statement, never duplicates it.
• Compliance reporting is Audit's. GDPR/SAR/breach/regulator exports live in audit.*. Reporting = operational BI for operations staff only. Different audience. No compliance tables here.
• Entitlement-gated. advanced_reporting on platform.tenant_entitlement gates RFM / vendor-performance / cross-module P&L; basic sales + inventory available to all tiers. ReportingService checks entitlement — service-layer, no table.
• AI analytics route through AIService. Anomaly summaries and trend narratives call AIService; Reporting does no ML. v1.0 hook is service-layer only — no Reporting schema change needed; capability follows AIService timeline.

Cross-Phase FK seams:

Column	References	Status
*.tenant_id	platform.tenant	Locked — enforced
report_snapshot_run.triggered_by_user_id	identity.identity_user	Locked — enforced (nullable)
inventory_valuation_snapshot.snapshot_run_id	reporting.report_snapshot_run	Intra-schema — enforced
inventory_valuation_snapshot.site_id	multi_loc.site	Locked — enforced
inventory_valuation_snapshot.item_variant_id	inventory.item_variant	Locked — enforced
period_close_snapshot.snapshot_run_id	reporting.report_snapshot_run	Intra-schema — enforced
period_close_snapshot.site_id	multi_loc.site	Locked — enforced (nullable; NULL = tenant-wide consolidated row)
Reads (not FKs) from pos.sale, pos.sale_line, orders.order_header, crm.customer, purchasing.vendor_invoice, purchasing.purchase_order, purchasing.purchase_receipt, billing.ar_statement, billing.ar_charge, billing.ar_payment, billing.ap_payment, inventory.stock, inventory.item_variant, inventory.stock_movement_line, integrations.sync_run, notifications.delivery_attempt, audit.audit_export_job, platform.tenant_usage_summary, platform.tenant_entitlement	Cross-schema matview + query sources	Read seams — no FK enforcement; read-only via service_role

Deferred items:

• tax_period_snapshot — DEFERRED. Stripe Tax owns calculation and is system-of-record; Vrida is not a tax-filing system in v1.0. Build when/if direct tax filing is added — the frozen filed-record need is real only then.
• Saved / scheduled report delivery (email, PDF export) — v1.1.
• Notification advanced analytics (open/click rates, campaign ROI) — v1.1. v1.0 has mv_notification_summary (basic delivery counts); pixel-tracking instrumentation is v1.1.
• Multi-site consolidated dashboards — v1.5 (ARCHITECTURE.md §Multi-site reporting).
• Consumer-side analytics — Consumer Layer phase (post-v1.0).
• Streaming / CDC real-time push — v2.0. 30s-poll-from-matviews is the locked v1 model (ARCHITECTURE.md §Real-Time vs Streaming Reconciliation).
• Custom report builder — v2+.
• AI narrative summaries — v1.0 hook to AIService (no Reporting schema change needed).

---

reporting.report_snapshot_run — 16 cols

Snapshot job header — one row per snapshot execution, covering both snapshot types (inventory_valuation and period_close). Serves two purposes: idempotency guard (the partial-unique index on (tenant_id, snapshot_type, as_of_date) WHERE status = 'completed' prevents double-closing a period) and audit trail (did the snapshot run, once, successfully, and how many rows did it produce). Same run-header pattern as integrations.sync_run over its item rows.

RLS: tenant-isolated on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
snapshot_type	text	NOT NULL	—	CHECK IN ('inventory_valuation','period_close'). Which snapshot this run produced.
period_start	date	NOT NULL	—	Start of the reporting period this snapshot covers.
period_end	date	NOT NULL	—	End of the reporting period.
as_of_date	date	NOT NULL	—	The freeze date — e.g. the last day of the month for a month-end close. Values are frozen as-of this date.
status	text	NOT NULL	'running'	CHECK IN ('running','completed','failed')
row_count	integer	NOT NULL	0	Number of snapshot rows produced (child rows in inventory_valuation_snapshot or period_close_snapshot).
triggered_by	text	NOT NULL	'scheduled'	CHECK IN ('scheduled','manual')
triggered_by_user_id	UUID	nullable	—	FK → identity.identity_user. NULL for triggered_by = 'scheduled'.
started_at	timestamptz	NOT NULL	—	When ReportingService began the snapshot job.
completed_at	timestamptz	nullable	—	When the job reached 'completed' or 'failed'. NULL while running.
failure_reason	text	nullable	—	Human-readable failure detail for status = 'failed'. NULL for successful runs.
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete

Constraints:

• CHECK (manual-run user coherence): (triggered_by = 'scheduled' AND triggered_by_user_id IS NULL) OR (triggered_by = 'manual' AND triggered_by_user_id IS NOT NULL) — manual runs must carry the actor; scheduled runs have no user context.

Indexes:

• PK on id
• on (tenant_id)
• on (snapshot_type)
• on (status) WHERE status IN ('running','failed')
• PARTIAL UNIQUE on (tenant_id, snapshot_type, as_of_date) WHERE status = 'completed' AND deleted_at IS NULL — idempotency: one completed snapshot per type per as-of-date; prevents double-closing a period.

---

reporting.inventory_valuation_snapshot — 12 cols

Frozen month-end inventory value — one row per item variant per site per snapshot run. The clearest must-snapshot record in the ERP: item_variant.avg_cost_cents is updated by weighted-average costing on every purchase receipt, so the December 31 average cost no longer exists anywhere in the system by mid-January. valuation_cents is a frozen computed result (on_hand_qty × avg_cost_cents) captured at the exact moment of the snapshot. item_variant_id stays as a live FK reference — names and SKUs are never copied here; looked up from inventory.item_variant at display time. INSERT-ONCE — once written, snapshot rows are NEVER mutated. A correction produces a new report_snapshot_run, not an UPDATE. updated_at is present per standard schema convention, but the service layer must NEVER issue UPDATE on these frozen financial rows.

RLS: tenant-isolated on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
snapshot_run_id	UUID	NOT NULL	—	FK → reporting.report_snapshot_run. Links each row to its job record.
site_id	UUID	NOT NULL	—	FK → multi_loc.site. First-class reporting dimension — every valuation row is site-scoped.
item_variant_id	UUID	NOT NULL	—	FK → inventory.item_variant. Reference stays live; name and SKU not copied here.
as_of_date	date	NOT NULL	—	Denormalized from the run's as_of_date for direct query without joining to report_snapshot_run.
on_hand_qty	numeric	NOT NULL	—	Frozen quantity — inventory.stock.on_hand_qty as-of-then. Never updated after snapshot.
avg_cost_cents	bigint	NOT NULL	—	The frozen value — item_variant.avg_cost_cents as-of-then. Unrecoverable after later receipts update weighted-average cost.
valuation_cents	bigint	NOT NULL	—	Frozen result: on_hand_qty × avg_cost_cents computed at snapshot time.
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete

Indexes:

• PK on id
• on (tenant_id)
• on (snapshot_run_id)
• on (as_of_date)
• on (tenant_id, site_id, as_of_date) — primary month-end balance-sheet query
• on (item_variant_id)

---

reporting.period_close_snapshot — 15 cols

Frozen cross-module P&L approximation — one row per site (or one consolidated row with site_id IS NULL) per snapshot run. Combines AR outstanding (read from billing.ar_statement), AP outstanding, revenue, COGS, and gross profit in a single frozen record. billing.ar_statement owns receivables period snapshots; this table adds the cross-module summary (AR + AP + COGS together in one record) — it reads billing.ar_statement, does not shadow it. Approximation, not an accounting system — the merchant's accounting platform (QuickBooks, Xero) remains the source of truth for financial statements; this is an operational-BI freeze. INSERT-ONCE — once written, snapshot rows are NEVER mutated. A correction produces a new report_snapshot_run, not an UPDATE. updated_at is present per standard schema convention, but the service layer must NEVER issue UPDATE on these frozen financial rows.

RLS: tenant-isolated on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
snapshot_run_id	UUID	NOT NULL	—	FK → reporting.report_snapshot_run
site_id	UUID	nullable	—	FK → multi_loc.site. NULL = tenant-wide consolidated row; non-NULL = per-site row. Both patterns coexist for the same snapshot run — per-site rows for site-breakdown; consolidated row for tenant-wide totals.
period_start	date	NOT NULL	—	Start of the closed period.
period_end	date	NOT NULL	—	End of the closed period (e.g. last day of month).
total_revenue_cents	bigint	NOT NULL	0	Frozen revenue total for the period (POS sales + fulfilled orders combined).
total_cogs_cents	bigint	NOT NULL	0	Frozen cost-of-goods-sold for the period.
gross_profit_cents	bigint	NOT NULL	0	Frozen: total_revenue_cents − total_cogs_cents. Frozen because avg_cost drifts — recomputing "March gross profit" in June yields a different answer.
ar_outstanding_cents	bigint	NOT NULL	0	Receivables as-of-close. Read from billing.ar_statement; not a copy of individual charge rows.
ap_outstanding_cents	bigint	NOT NULL	0	Payables as-of-close. Read from open purchasing.vendor_invoice balances.
transaction_count	integer	NOT NULL	0	Total transaction count (POS sales + fulfilled orders) for the period.
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete

Indexes:

• PK on id
• on (tenant_id)
• on (snapshot_run_id)
• on (tenant_id, period_start, period_end)
• PARTIAL UNIQUE on (tenant_id, site_id, period_start, period_end) WHERE site_id IS NOT NULL AND deleted_at IS NULL — one close per site per period.
• PARTIAL UNIQUE on (tenant_id, period_start, period_end) WHERE site_id IS NULL AND deleted_at IS NULL — one consolidated close per period. Two-partial-index pattern for nullable site_id — Postgres cannot compare NULL = NULL in a unique constraint, so the consolidated row requires its own partial unique.

---

Column counts: report_snapshot_run(16) + inventory_valuation_snapshot(12) + period_close_snapshot(15) = 43

---

Materialized View Specifications

Non-table deliverables — locked Reporting obligations defined at schema lock. Each MV must declare its refresh strategy (aggressive or staleness-window) and triggering event per CROSS_MODULE_CONTRACTS Rule 6. These are reporting.* schema objects, not base tables. All queried via service_role. All include tenant_id and site_id as dimensions. Entitlement-gated MVs are noted.

MV name	Sources	Grain	Refresh strategy	Triggering event
mv_sales_summary	pos.sale_line, pos.sale, inventory.item_variant, inventory.item	day / site / product / category	Aggressive — refresh on every sales event; dashboard KPIs must reflect the current shift	sales_changed
mv_sales_by_customer	crm.customer, pos.sale, orders.order_header, pos.sale_line	customer / period / site	Staleness window 30s — customer-level aggregation; acceptable lag matches poll interval	sales_changed
mv_inventory_valuation_current	inventory.stock, inventory.item_variant, inventory.item	item variant / site	Aggressive — current avg_cost × on_hand_qty; changes on every receipt and every sale	stock_changed
mv_gross_margin	pos.sale_line, inventory.item_variant	product / period / site	Staleness window 30s — margin analysis; real-time precision not required	sales_changed
mv_customer_rfm	crm.customer, pos.sale, orders.order_header	customer / tenant	Staleness window — daily acceptable — RFM scores are strategic, not operational. Entitlement-gated (advanced_reporting)	sales_changed (daily batch refresh acceptable)
mv_ar_aging	billing.ar_charge, billing.ar_payment, billing.ar_payment_application, billing.ar_account	customer / aging bucket / tenant	Staleness window 30s — aging buckets (current / 30 / 60 / 90+ days past due)	ar_changed
mv_ap_aging	purchasing.vendor_invoice, billing.ap_payment	vendor / aging bucket / tenant	Staleness window 30s — AP aging buckets for payables dashboard	ap_changed
mv_vendor_performance	purchasing.purchase_order, purchasing.purchase_receipt, purchasing.vendor	vendor / period / tenant	Staleness window — daily acceptable — on-time delivery rates, PO-vs-receipt date diffs. Entitlement-gated (advanced_reporting)	receipt_changed (daily batch refresh acceptable)
mv_sync_health	integrations.sync_run	connector / period / tenant	Staleness window 30s — error rates, last success per connector, for operations monitoring	sync_changed
mv_notification_summary	notifications.delivery_attempt, notifications.notification	campaign / channel / period	Staleness window 30s — sent / delivered / failed counts per campaign. v1.0 only — open/click pixel tracking is v1.1	notification_changed

Entitlement enforcement: mv_customer_rfm and mv_vendor_performance are queried only when ReportingService confirms advanced_reporting entitlement for the tenant. The MV refresh still runs unconditionally (refresh cost is low); the gate is at query time, not at the DB layer.

Site_id on every MV: every matview above carries site_id as a dimension column (not just a filterable JOIN attribute). Multi-site breakdowns are served from the MV directly without re-aggregation.

mv_inventory_valuation_current vs inventory_valuation_snapshot: these are complementary, not redundant. mv_inventory_valuation_current = CURRENT live state (what is inventory worth now, rolling; refreshed on every stock_changed event). inventory_valuation_snapshot = FROZEN point-in-time (what was it worth on a specific date, unrecoverable later; written once at month-end close). Different questions; both needed.

---

Deferred Source-Table Indices

Index-only migrations to locked source schemas — Reporting's design obligation defined at lock time. These indices were deferred in the source module's lock (documented in PROJECT_DECISIONS per-module deferred items) because optimal index design depends on the MV query patterns defined here. They are additive touches (index-only, no schema changes to locked tables) and do not re-lock the source modules.

Source table	Index columns	Type	Serves
inventory.stock_movement_line	(tenant_id, item_variant_id, created_at DESC)	BTree	Cost-history and COGS queries used to compute mv_gross_margin and the period_close_snapshot COGS aggregation. Composite covers tenant isolation + item filter + time range in one scan.
billing.ar_charge	(tenant_id, charged_at)	BTree	AR aging bucket query: mv_ar_aging computes aging from charged_at relative to now(). Without this index, the aging MV refresh does a full tenant-filtered scan on every event.
billing.ap_payment	(tenant_id, status) WHERE status IN ('pending','partial')	BTree partial	AP aging: filters open / partially-applied payments for mv_ap_aging without scanning settled payments.
integrations.sync_run	(tenant_id, connector_id, started_at DESC)	BTree	Sync-health MV: mv_sync_health groups by connector + recent runs. Composite covers tenant + connector + recency in one scan.
audit.audit_export_job	(tenant_id, status)	BTree	Export-monitoring query: operational dashboard showing pending / running / completed export jobs. Small table; index supports status filter on the Reporting service-layer query.

Locked-module touch protocol: each index is applied as a migration tagged reporting_deferred_idx_<table> against the owning module's schema. The source module stays locked — index additions are additive and do not require re-locking per SCHEMA_DESIGN_RUNBOOK §6.

---