ADR-0010 · Analytics & reporting data architecture (the command-center read model)

Status: 🟡 Proposed — direction endorsed by product 2026-07-20; staging open for design debate · Depends on: ADR-0009, ADR-0007 · Related: ADR-0001, observability (CLAUDE.md top deferred risk)

The one-line thesis

ADR-0009 is the write model — flexible, jsonb-backed, config-driven, optimized so a new vertical is data. This ADR is the read model — the analytics/reporting layer the Admin Command Center runs on, engineered so cross-domain dashboards, views, and widgets stay fast and simple as verticals multiply. The two are deliberately different shapes: you cannot build a scalable analytics plane directly on a jsonb write model, and you should not try.

Context

The command center (ADR-0007) must let an operator monitor and manage detailed insight across every business domain and archetype — multiple dashboards, analytics views, and widgets — without hitting query complexity, performance walls, or structural limits as categories grow. That is a read-side, aggregation-heavy workload over a data model (ADR-0009) that was intentionally optimized for write-side flexibility. The mismatch is real and predictable, so it is designed for here rather than discovered in production.

The central tension (name it, then resolve it)

	Write model (ADR-0009)	Read model (this ADR)
Optimized for	flexibility — new vertical = config	aggregation — GROUP BY across all verticals
Shape	business_items.attributes jsonb + shared txn tables	typed facts + pre-aggregated rollups
Access	per-tenant, RLS-scoped, OLTP	cross-tenant aggregates, admin-gated, OLAP-ish
Cardinality	one row per item/booking/order	one row per (metric × dimension × period)

jsonb does not aggregate at scale. GIN indexes accelerate filtering (attributes @> '{...}'), not GROUP BY attributes->>'city' over millions of rows across domains with different attribute shapes. If the command center queries the OLTP tables directly, three things break as QRSETU scales: (1) analytical queries contend with user-facing writes on the same tables; (2) cross-archetype rollups become bespoke per-domain SQL; (3) jsonb aggregation degrades non-linearly. All three are avoidable with the right read model.

What the write model already gives us for free

Good news first: the shared transactional spine (leads, appointments, orders/order_items from ADR-0009) is already uniform and typed across domains — an appointment is an appointment. Most business analytics (volume, demand, conversion, revenue) live there, and that table shape is inherently aggregation-friendly. The problem is concentrated in (a) the descriptive business_items.attributes jsonb, and (b) cross-archetype comparability (a "listing" vs an "appointment" vs an "order" are different units). This ADR targets exactly those two.

Decision drivers

• Cross-domain, cross-archetype comparability — one exec dashboard must show all verticals side by side, even though their primary entities differ.
• Read/write isolation — analytics must not contend with OLTP; a heavy admin query must never slow a merchant's card.
• Bounded query complexity — dashboards read pre-shaped rows, not 12-way joins over jsonb. Honor CLAUDE.md's composite-RPC rule (avoid ≥3 parallel read RPCs saturating the connection pool) — the command center is the poster child for it.
• Structural headroom — introducing a new vertical must add analytics coverage as data, mirroring ADR-0009; it must not require a new dashboard query or a schema change to the read model.
• A migration path to real OLAP — Postgres-native now, but the contract must let us move hot aggregation to a read replica / column store (ClickHouse-class) later as a move, not a rewrite.
• PII/RLS discipline — admin aggregates cross tenants deliberately, but must expose counts/sums, never row-level PII.
• Feeds, but is not, system observability — business analytics (this ADR) and ops observability (metrics/alerting, CLAUDE.md's top deferred risk) share patterns but stay separate stores/concerns.

The design (six moves)

1. Column-promotion discipline — analytical fields are typed columns, jsonb is display-only

The rule that keeps the write model from poisoning the read model: any attribute that will be filtered, sorted, grouped, or charted is a first-class typed column (common-core on business_items, or a promoted/generated column); attributes jsonb holds only the display-only long tail. The ADR-0009 schema-as-data definition gains an analytical: true (and indexed: true) flag per field, which maps that field to a promoted column (or a Postgres generated column over the jsonb as a bridge) with the right index. So "price", "city", "status", "area_sqft" for real estate become columns; "nearby_landmarks" stays jsonb. Promotion is declared in config, enforced in migration.

2. Canonical metric/fact model — the cross-archetype comparability layer

Normalize each archetype's heterogeneous activity into a small, shared fact vocabulary so one dashboard spans all verticals. Every archetype maps its raw entities onto canonical metrics:

Canonical metric	Inventory	Booking	Lead/Portfolio	Catalog	E-commerce
supply_count	listings	services	portfolio items	offerings	products
demand_count (inbound)	inquiries	appointments	leads	inquiries	orders
conversion	inquiry→deal	booking→show	lead→won	—	cart→paid
revenue	— / deal value	service value	deal value	—	order total
engagement	views/scans	views/scans	views/scans	views/scans	views/scans

A fact table — analytics_events (append-only) and/or narrow fact rows — carries { business_id, domain_id, archetype, metric_type, value, currency, occurred_at, source }. Archetype defines the mapping; the command center only ever reads canonical metrics, so adding a vertical adds no new dashboard query. Per-archetype drill-downs still hit the typed OLTP columns for detail.

3. Read/write separation — rollup tables + scheduled refresh, raw stream retained

Dashboards read pre-aggregated rollup tables, never raw OLTP:

• business_metrics_daily (per business × day × metric), domain_metrics_daily (per domain × day × metric), and coarser tier/archetype rollups — one row per (dimension × period × metric).
• Refreshed incrementally by pg_cron scheduled jobs (already in the stack) and/or triggers; heavy cross-cuts can be materialized views (REFRESH MATERIALIZED VIEW CONCURRENTLY).
• Keep the raw analytics_events stream so rollups can be rebuilt, new metrics backfilled, and a future OLAP store hydrated from one contract. Dashboards = O(rows-in-rollup), independent of raw volume.

4. Composite admin RPCs over the rollups

One get_admin_dashboard(filters) composite RPC per dashboard returns a structured JSON of every widget's data from the rollups in a single round trip — SECURITY DEFINER, requireAdmin-gated, SET search_path, REVOKE ALL / GRANT EXECUTE. This honors the composite-RPC standard and keeps the pool safe. Merchant-facing "my analytics" reuse the same rollups filtered to business_id (RLS-scoped) — build the read model once, serve both audiences.

5. Partitioning & volume headroom — choose keys now

The append-only high-volume tables (analytics_events, subscription_usage_logs, and potentially orders) are declaratively partitioned by time (range) from the start; analytics_events may sub-partition by domain_id if a few domains dominate. Partition-key choice is expensive to change later, so it's decided now even if partitioning is switched on only past a volume threshold. Rollups age out raw partitions (drop old raw, keep aggregates).

6. RLS / PII discipline for aggregates

Admin aggregates cross tenants by design, via the SECURITY DEFINER admin RPCs that bypass per-tenant RLS — but they return aggregates only (counts, sums, rates), never raw rows or PII. Any drill-down to row level re-imposes RLS or an explicit admin-audit path. Rollup tables themselves carry no PII (they store metric values + dimension keys).

Options considered

A — Query the OLTP tables directly from the command center

• For: zero new schema; always live.
• Against: analytics contend with user writes; cross-archetype rollups are bespoke per-domain SQL over jsonb; degrades non-linearly with domains × items × events. Rejected — it is the exact failure mode this ADR exists to prevent.

B — Materialized views only, over the OLTP tables

• For: Postgres-native; no separate store; decouples read shape from write shape.
• Against: refresh is coarse-grained and costs OLTP CPU; still same database/instance; no clean path to OLAP; jsonb aggregation still in the view definitions. Partial — used within Option C for specific heavy cross-cuts, not as the whole answer.

C — Rollup/fact tables + canonical metrics + scheduled refresh, raw stream retained · [recommended]

The six-move design above.

• For: dashboards are O(rollup) and simple; cross-archetype comparability via canonical metrics; adding a vertical adds no dashboard query; Postgres-native today; the retained raw stream + fact contract make a later OLAP move a migration, not a rewrite; serves admin and merchant analytics from one model.
• Against: more schema + pg_cron refresh jobs to operate (and the known pg_cron-not-yet-on-Dev issue must be resolved); rollups are eventually-consistent (refresh lag) — acceptable for analytics, called out for real-time needs.

D — Dedicated OLAP warehouse / column store from day one (ClickHouse / BigQuery / read replica)

• For: best raw aggregation performance at very large scale.
• Against: operational + cost overhead unjustified at current scale; another system to secure/promote across Dev/Prod; premature. Deferred — Option C's fact contract is explicitly designed so this becomes a later, low-friction move keyed off the same analytics_events stream.

Recommendation

C, staged. Ship the canonical-metric fact model + daily rollup tables + composite admin RPCs on Postgres now, with column-promotion discipline enforced through the ADR-0009 schema-as-data. Retain the raw analytics_events stream and keep the fact contract clean so Option D (OLAP store / read replica) is a future move, not a rewrite. Reuse the rollups for merchant-facing analytics. Decide partition keys up front; switch partitioning on past a volume threshold.

Consequences

• New read-model schema: analytics_events (append-only, partitioned) + *_metrics_daily rollups + a few materialized views, all with RLS and admin-gated SECURITY DEFINER RPCs; shipped expand-contract, promoted Dev→Prod.
• ADR-0009's schema-as-data gains analytical/indexed flags — the field definition now also drives column promotion + index creation, closing the loop between the write and read models. A field flagged analytical after the fact triggers an expand-contract backfill (promote + populate the column).
• pg_cron becomes load-bearing for analytics — this makes resolving the pg_cron-not-on-Dev tracker item a prerequisite, and refresh jobs are captured as migrations (per the DB rules), not just live DB state.
• Command-center dashboards are composite-RPC-backed — one RPC per dashboard, structured JSON, from rollups; the ≥3-parallel-read-RPC anti-pattern is designed out.
• Merchant analytics reuse the read model — "your business this month" widgets are the same rollups filtered by business_id; build once.
• Feeds observability, stays separate — the fact stream can later emit to the deferred metrics/alerting stack, but business analytics and system observability remain distinct stores/RPCs (don't conflate the top deferred risk with product dashboards).
• Schema-version awareness — analytics over promoted columns are version-safe; mixed-version jsonb stays display-only and is never aggregated, so schema evolution doesn't corrupt dashboards.

Open questions for product / engineering

1. ✅ Resolved (2026-07-20): supply_count / demand_count / conversion / revenue / engagement confirmed as the v1 vocabulary. Domain-specific metrics that don't map cleanly (e.g. Loan Agent disbursal value, Real Estate deal cycle time) are added later as additional canonical metrics without breaking existing dashboards.
2. Refresh cadence — daily rollups for v1, or does any command-center widget need near-real-time (which pushes some metrics to trigger-based incremental rollup or a live OLTP read for that one number)?
3. ✅ Resolved (2026-07-20): both admin and merchant analytics ship in R1, from the same rollups (admin = cross-tenant aggregates; merchant = the same rollups filtered to their own business_id). Confirms the "build the read model once, serve both audiences" consequence — merchant "my business this month" widgets are R1, not deferred.
4. OLAP trigger — what volume / query-latency threshold flips us from Postgres rollups to Option D (read replica or column store)? Define the signal now so it's a planned move.
5. Event emission point — do OLTP writes emit analytics_events via DB triggers (consistent, in-transaction) or via the Edge Functions (decoupled, but must not be missed)? (Lean triggers for correctness.)

Related

• ADR-0009 — the write model this read model complements; its schema-as-data gains the analytical/indexed flags.
• ADR-0007 — subscription_usage_logs/usage counters are a metric source; the command center hosts both entitlements and analytics.
• ADR-0001 — organization/workspace dimensions for franchise/multi-location rollups.
• Tracker: analytics read-model build; pg_cron-on-Dev prerequisite; partition-key decision; column-promotion flags on the field schema (documentation/portal/dev-tracker/tracker.md).
• CLAUDE.md — composite-RPC standard; observability as the top deferred risk (kept separate from product analytics).