Skip to content

armgit / spree

Public Imported from GitHub
0

Open Source Platform for DTC, B2B Commerce, Marketplaces & Omnichannel. REST APIs, TypeScript SDKs, and production-ready Next.js storefront. Self-host it. Own your data. No vendor lock-in. Zero platform fees.

45.8 KB
Raw Blame

Reporting Semantic Layer

Status: In Progress — Implementation Path Steps 1–9 implemented on feat/dashboard-reports (registry + compiler + live adapter + reporting/query/schema endpoints + member-level permissions + home screen on the contract; Step 7 schema enrichment, Step 8 presets/week grain/market·country·variant dimensions/discount·delivery·tax metrics; Step 9 Spree::SavedReport + reporting/saved_reports CRUD + nine seeded classics + Spree::Exports::Report CSV + the Reports SPA pages with the picker builder and inferred visualizations). Rebased onto main 2026-09-03. Vocabulary reworked to the industry sales chain 2026-09-09 (Decision 14): margin, returns, commission and seller-payout metrics, seller and customer_type dimensions, permission-gated marketplace metrics. Step 10 (agent surface) pending. Target: Spree 6.0 (core + new dashboard home + Reports page) → 6.1 (facts adapter, AI) — Enterprise ClickHouse adapter out of band Depends on: 6.0-admin-api.md (v3 conventions), 5.5-admin-api-key-scopes.md (scope gating), 6.0-multi-vendor-marketplace.md (vendor forced scope + vendor dashboard consumer), 5.5-admin-api-cli.md (first agent surface), 5.6-solid-queue-single-container.md (fact writes + reconciliation jobs) Author: Damian + Claude Last updated: 2026-09-11 (Decision 16: the legacy Spree::Report system is removed; counters send keys, not copy)

Summary

Replace per-feature, hand-rolled analytics — the legacy admin's AnalyticsConcern, the v3 dashboard serializers, and the Spree::Report/ReportLineItem STI authoring model — with one semantic layer: a registry of metrics and dimensions (the developer extension point), a query contract + compiler (one POST /api/v3/admin/reporting/query endpoint; a saved report is a stored query whose visualization is inferred from its shape), and pluggable storage adapters (live OLTP by default — zero new infra; incremental fact tables opt-in; ClickHouse as an Enterprise adapter). Developers extend the vocabulary; merchants, the dashboard, saved reports, vendor dashboards, and the AI layer all compose sentences from it. Store, currency, and vendor scoping are enforced by the compiler below every consumer — including the LLM.

Problem

Spree currently has three parallel analytics implementations, none extensible at the right granularity:

Surface Implementation Problems
Legacy admin home Spree::Admin::AnalyticsConcern — inline AR aggregates Maintenance mode; duplicates definitions (its calc_growth_rate disagrees with the v3 serializer's growth_rate on the zero-baseline case)
React dashboard home Spree::Api::V3::Admin::Dashboard{Analytics,Rankings,Operations}Serializer — hand-rolled SQL POROs Every new number touches serializer + SDK + UI + locales; "revenue," channel scoping, and period comparison were each defined ad hoc and had to be deduplicated by review; chart buckets in UTC while Spree::Report uses store timezone
Reports Spree::Report STI subclasses + ReportLineItem Developers must write a whole report class per question; output is row-level CSV, not aggregates; no composition, no viz, no API-driven querying

The 5.6 dashboard work (branch claude/react-dashboard-home-reporting-71weaz) is the proof: shipping a Pulse-style home screen required inventing revenue semantics (SUM(pre_tax_amount) vs quantity * price), a nil-tolerant channel filter (Order.for_channel), a previous-period alignment convention, and a growth-rate convention — all as one-off code. The upcoming multi-vendor marketplace (6.0 headline) needs vendor dashboards that would otherwise be a fourth copy.

Key Decisions (do not deviate without discussion)

1. Developers extend vocabulary, not reports

The extension point is a registry of metrics and dimensions, mirroring Spree.order_routing.rules / Spree::Dependencies. Namespace: Spree.reporting / Spree::Reporting — Spree.analytics / Spree::Analytics was the storefront event-tracking config (tracked events + handlers), removed in 6.0 as unused; "reporting" also matches the merchant-facing Reports nav this plan powers.

Spree.reporting.metric :net_sales,
  sql: "SUM(#{Spree::LineItem.table_name}.pre_tax_amount)",
  base: :line_items,
  format: :money                       # money metrics REQUIRE the currency forced scope

Spree.reporting.metric :orders, sql: 'COUNT(DISTINCT spree_orders.id)', base: :orders, format: :integer

Spree.reporting.dimension :channel, base: :orders, column: :channel_id, lookup: 'Spree::Channel'
Spree.reporting.dimension :category,
  base: :line_items, joins: [variant: { product: :classifications }],
  column: 'spree_products_taxons.taxon_id', lookup: 'Spree::Category'
Spree.reporting.dimension :completed_at, base: :orders, type: :time, grains: %i[day week month quarter]

A definition carries: SQL fragment (metrics), join graph, type/format, allowed grains, lookup model for label/prefixed-id resolution, and authorization metadata. Registered definitions are the allowlist — the only things a query may reference.

2. Query contract stolen from Cube.dev/Lightdash, not invented

{ "metrics": ["net_sales", "orders"],
  "dimensions": [{ "name": "completed_at", "grain": "week" }, "channel"],
  "filters": [{ "dimension": "channel", "op": "eq", "value": "ch_x7Yq2k" }],
  "time_range": { "since": "-90d" },
  "compare": "previous_period",
  "sort": "-net_sales",
  "limit": 50 }

One endpoint: POST /api/v3/admin/reporting/query, gated by a read_reports secret-key scope or CanCanCan for admin JWTs. A saved report is this JSON as a store-scoped DB row (Spree::Reporting::SavedReport) — shareable, versionable, seedable. The dashboard home issues contract queries directly (fixed layout, deterministic); the Reports page lists seeded + user-saved reports rendered through the same components.

3. Forced scopes: store, currency, vendor — enforced by the compiler

  • Store: every compiled query is rooted in current_store associations (store.orders, store.line_items, …). Non-negotiable, never expressible or removable via the contract.
  • Currency: any query touching a format: :money metric is compiled with exactly one currency filter (param or store default). Cross-currency aggregation is a 422, not a silently wrong sum.
  • Vendor: when the caller is vendor-scoped (6.0-multi-vendor-marketplace.md), the compiler injects the vendor_id filter the way Spree::Reports::SalesTotal does by hand today. Vendor dashboards are the same saved reports running under a forced scope — not a second report set.

Enforcement lives below every consumer, including the AI layer. Never by prompt, never per-serializer.

Member-level permissions (implemented): registry definitions carry an optional subject: lambda resolving to the authorization class. Every query requires :read Spree::Order (all reporting is order data); referencing a member with a subject additionally requires :read on that class — so a JWT staff role without product access cannot query the product dimension (or filter by it), and one without customer access cannot see the customer ranking. Query#required_subjects exposes the fail-closed list so every consumer (API, saved reports, agents) enforces identically; checks run before filter-value resolution (403 wins over 404). The home screen mirrors this client-side via <Can>/usePermissions — widgets whose subjects the role cannot read are hidden instead of rendering 403-fed skeletons (UX only; the API enforces regardless). Secret keys are enforced per member too: a dimension declaring a subject must also declare its key_scope (registration raises otherwise — fail-closed by construction), and Query#required_key_scopes is checked against the key on top of the read_reports endpoint gate, so a key without read_products cannot query the product dimension any more than a limited staff role can (read_reports alone covers the order-data floor). Abilities gate vocabulary class-level only — row-shaped restrictions must be compiler-forced scopes (conditional CanCanCan rules do not narrow aggregates; accessible_by is deliberately not merged into adapters since block rules can't compile and fact-table adapters couldn't apply the conditions, which would make adapters disagree). Vendor users compose both mechanisms when 6.0-multi-vendor-marketplace.md lands: the compiler injects the forced vendor_id scope (their orders/products only) and their ability restricts referencable members.

4. Validation rejects; it never silently narrows

Unknown metric/dimension/grain/op → 422 with the v3 error shape naming the offender. This is the lesson from the silently-dropped Ransack predicates bug (fixed 2026-07-22): a filter that doesn't apply must fail loudly, because a query that "works" while ignoring a filter is worse than an error.

5. Storage is a pluggable adapter; live OLTP is the default

Spree::Reporting::Adapters::Live compiles to Arel against the transactional schema — zero new infra, correct on day one for the vast majority of installs (the current dashboard proves viability: the whole home payload is ~a dozen aggregate queries). Adapters::Facts (incremental rollup tables) is opt-in and lands after this branch (6.1). ClickHouse is an Enterprise adapter behind the same interface, never a core dependency. Live-first sequencing is deliberate (see decisions.md 2026-07-22): the contract is identical across adapters, so storage can be swapped without touching consumers, and the extension point ships a release earlier.

6. Grain SQL belongs to the adapter, not the definition

Spree supports PostgreSQL, MySQL, and SQLite. date_trunc vs strftime vs DATE_FORMAT is adapter code. Metric SQL fragments must stay portable (plain aggregates over portable expressions); time bucketing is compiler/adapter-owned.

7. Time grains resolve in the store's timezone

Spree::Report already does this (store.preferred_timezone); the 5.6 dashboard serializers bucket in UTC. The semantic layer standardizes on store timezone for day+ grains and fixes the dashboard inconsistency when it migrates.

8. Money is decimal end-to-end; formatting at the edge

Aggregates compute on decimal columns (pre_tax_amount, total). Floats never touch storage or the wire until final display values (Spree::Money / client formatting). This also rules out ankane/rollup's float value column as a fact store (see References).

9. AI layer: NL → query JSON, never NL → SQL

The registry serializes into the tool schema (same source as the OpenAPI spec), so the model cannot reference a metric that doesn't exist; validation errors are recoverable in the agent loop; forced scopes hold below the LLM. Read-only — no mutation confirmation gate needed. First agent surface is the CLI (spree api post /reporting/query, per 5.5-admin-api-cli.md), ahead of any in-app agent or MCP.

10. Spree::Report stays; its authoring role ends — superseded 2026-09-11 (Decision 16)

Spree::Report is a row-level CSV exporter with attachment/mailer plumbing — orthogonal, keeps working. It is no longer the thing developers extend to answer aggregate questions; new report types for analytics purposes are not accepted into core once the registry lands. (Long term the CSV export path can consume the semantic layer for aggregate exports; row-level exports remain Spree::Report's job.)

11. No Blazer or rollup dependency

ankane/blazer is analyst-facing raw SQL (no semantic guardrails, no per-store row scoping, engine UI not embeddable in the SPA) — the opposite trust model; it may be documented as a self-hosted ops option. ankane/rollup is disqualified as a backbone (single float value column, one metric per series name, jsonb dimensions are Postgres-only) but its recompute-current-period write model is the reference for the facts adapter. We steal designs (Blazer's checks/alerts, saved-query rows; rollup's incremental semantics), not dependencies.

12. Saved reports are store-wide, authored, and exported through the Export pipeline (2026-09-03)

Settled before implementing Steps 7–9:

  • Visibility: a saved report belongs to the store and is visible to every staff member with read_reports; the author is recorded for attribution only. Execution always re-runs required_subjects / required_key_scopes against the viewer. Per-user privacy is a later flag, not a v1 concern.
  • CSV export rides the existing Spree::Export pipeline as Spree::Exports::Report (query JSON in search_params, background job, attachment, email, history under Settings) rather than a synchronous download — one export UX across the admin outweighs the row cap making a sync path viable. The export re-authorizes its members against the requesting user, so it requires a user (API keys cannot create report exports).
  • Vocabulary: the full Step 8 set ships now — presets, week grain, market/country/variant dimensions, discounts_total/delivery_total/tax_total metrics.
  • Delivery: Steps 7–9 land on the same branch as Steps 1–6 as one PR; the UI is needed in the wild to validate the plan.

13. Review rulings on the shipped Steps 7–9 (2026-09-03)

Settled while reviewing the implementation, recorded because they change what a report means:

  • Sales metrics count what stayed sold. A canceled order keeps its completed_at, so both metric bases exclude status: 'canceled' explicitly. Refunds already net out of payment_total; cancellation is the one post-placement event that would otherwise inflate revenue and order counts forever.
  • The Total row is the dimensionless figure. Grouping joins (product ↔ categories, order ↔ shipping address) apply only to the grouped query; the ungrouped totals run on the base rows with the same filters, so a product in three categories is counted once in the footer and an order without a shipping address still counts in "Sales by country". A filter on a joined dimension narrows through an id subquery for the same reason. Rows of a fan-out breakdown therefore need not sum to the total, and that is correct — but they must never sum to less: dimension joins are LEFT joins (corrected 2026-09-10, they were inner), so a record whose optional association is missing groups under the NULL key that hydration renders as "Unassigned" rather than disappearing from the breakdown while the Total still counts it.
  • Built-in reports are read-only on the model, not just in the UI. Spree::SavedReport refuses name/description/query changes on a seeded row (seeded_report_read_only); they can be copied or deleted. The dashboard mirrors this with "Save a copy".
  • Report exports are refused at create, not in the job. Spree::Exports::Report compiles the query and checks the requesting user's members in a validation, so an unreadable or malformed query is a 422 the caller sees rather than an export that never becomes done. Key-created report exports stay unsupported (a user is required) until the export can carry the key's scopes.
  • Member authorization has one predicate. Query#unreadable_subjects(ability) is what the query endpoint, the export and any future agent tool call; Registry#compatible?(metric, dimension) is the one base-compatibility rule the schema and the compiler share.
  • The fulfillment vocabulary is the rollup domain. The fulfillment_status dimension publishes backorder | canceled | partial | unfulfilled | fulfilled | delivered — not the legacy pending/ready/shipped the model constant still carries for 6.1 removal.

14. The sales vocabulary follows the industry chain (2026-09-09)

A survey of what merchants expect a commerce back office to report (the whole catalogue minus marketing and behaviour, which need session data a headless storefront does not collect) showed our first nine metrics answer the right questions under the wrong names, and leave the two figures merchants ask for first — real net revenue and seller payouts — unreachable. Three rulings:

  • Names follow the industry chain, not our columns. The recognised sequence is gross_sales − discounts − returns = net_sales, then net_sales + shipping + duties + fees + taxes = total_sales. Our shipped gross_revenue was in fact total sales and net_revenue was neither term. The vocabulary is renamed to the chain before saved reports bake the wrong names in: gross_sales, discounts, returns, net_sales, shipping, duties, fees, taxes, total_sales. orders_count becomes orders, customers_count becomes customers, aov becomes average_order_value. This is a breaking rename of a vocabulary shipped only on this branch; registered names are public API from the release, so it happens now or never.

  • A returning customer is one with prior orders. Definitions in the field contradict each other (has-prior-orders versus two-or-more-lifetime). We publish the first: a customer is returning on an order when they had at least one earlier completed order, otherwise first-time. The distinction is per-order and evaluated against order history, not a stored flag, so it stays correct when history is imported or an order is canceled.

  • Reversals are reported when they happen, so past periods restate. A refund issued in March against a February order reduces March's returns and net_sales, not February's. This is the conventional treatment and the only one an unclosed accounting period can produce; the consequence is that a report over a past range can change after the fact, and the docs say so.

Two mechanics follow from the chain:

  • Order-level money that lives on another table (refunds, commissions) is aggregated through a correlated subquery on the base relation rather than a join, for the same reason grouping joins stay grouped-only (Decision 13): an order with three refunds must stay one row.
  • cost_of_goods and gross_margin read line_items.cost_price, which is nullable — a variant with no cost contributes zero, so margin over an incompletely-costed catalog reads high. The metric documents that rather than guessing a cost.

Bases are registry data, and there are three families (2026-09-09, same pass): the compiler's hardcoded :orders/:line_items pair became registry.base, each base declaring its family, its own table, how to reach it store-scoped, its time column and which bases its dimensions can group by. compatible? generalised from the two-base special case into that reachability rule. Core registers four bases in three families — sales (:orders, :line_items), payments (:payments) and inventory (:stock_movements) — and a query drawing from two families is refused, because they answer different questions on different clocks: sales money is anchored on order completion, a payment on when it was taken, a movement on when stock moved. The schema publishes the families so a builder never offers a pair the query would reject.

Payments answer what was actually taken and given back, by instrument (payments_received, payments_refunded, net_payments, payments_count, payments_failed; axes payment_method, payment_result, paid_at). Inventory answers movement over the typed ledger (units_received, units_shipped, units_adjusted, derived sell_through; axes moved_at, movement_kind, moved_variant, stock_location). Two mechanics worth recording: the movement kind carries direction, so each metric sums one kind rather than raw quantity — netting a receipt against a dispatch is the bug that shape prevents; and spree_stock_movements carries no store column, so its base scopes through StockMovement.for_store's stock level → variant → product walk, the only tenancy path that table has.

Sessions, referrers and conversion rates stay unavailable: a headless storefront does not send us the data. That is now the only recorded gap.

Decision 15 — Operations counters are registry members; the low stock threshold is a store preference (2026-09-10)

The home screen's Operations card was the last hand-rolled number surface: five counts as private methods on a serializer in the API gem, five matching icon-plus-filter rows hardcoded in the dashboard, one permission (read_dashboard) covering all of them, and a low_stock_threshold request parameter the dashboard never sent. Counters now join Spree.reporting as a fourth member type beside metrics, dimensions and bases: registry.counter :name, count: ->(store, channel:) { … }, subject:, key_scope:, link:, description:. They are not compiled into the query (no range, no currency, no base — a counter is the state of the store at this instant), so the registry only stores the lambda; Spree::Reporting::Counters evaluates them for one store and channel, filtered by the same member_allowed? predicate the schema and the query apply, now shared through Admin::ReportingAuthorization. GET /dashboard/counters returns { channel_id, counters: [{ key, value, link, nav }] } and the dashboard renders whatever arrives, keeping only an icon map with a fallback. No copy crosses the wire (2026-09-11): the counter is a key and a number, the dashboard translates the key from its own locale files, and a counter it has no entry for falls back to the humanized key. This is stricter than the 2026-09-05 ruling, which keeps label/description on type catalogs as fallback text — a counter is rendered by the dashboard alone, so the fallback bought nothing and guaranteed a mixed-language card whenever the request locale and the interface locale differed. One request serves the home card and every sidebar badge: the three post-sale badges each used to spend a list request with limit: 1 purely to read a total off the pagination meta, and the returns badge counted received while the counter did not — two definitions of the same number. Counters now carry the nav entry they badge, so the sidebar reads the shared response and an extension's counter badges its own entry with no dashboard release. The shell loads them, not the badges: the sidebar mounts only the children of the section being viewed, so a badge-driven request left every page outside Orders with no counts at all (a defect the three replaced badges shared), and a parent entry sums its own counter with its children's so the total survives a collapsed submenu. The link is declared beside the count so the list a merchant lands on shows exactly the rows counted — the previous out-of-stock link pointed at a products filter the server silently dropped. low_stock_threshold is a Spree::Store preference (integer, default 5, 0 = off), edited under Settings → Store → Inventory and named in the counter's own description; neither reference platform has a per-variant threshold, and Shopify's inventory report bands by days of supply rather than a count, so store-level is the grain. Not Spree.operations (reserved) and not a second namespace — the plan's own line that "state metrics join the registry" is the design.

Decision 16 — The legacy Spree::Report system is removed (2026-09-11, supersedes Decision 10)

Decision 10 kept Spree::Report on the grounds that it is a row-level CSV exporter and therefore orthogonal to this layer. That reasoning no longer holds. It has no v3 route and no dashboard screen, so a merchant cannot run one at all — it is reachable only from Ruby. Both subclasses core shipped, Reports::SalesTotal and Reports::ProductsPerformance, are aggregate questions this layer now answers as seeded reports. And the row-level niche it was kept for is already served by Spree::Export (Exports::Orders and friends), which unlike Spree::Report has an Admin API and a dashboard page.

So the whole subsystem goes: Spree::Report, its two subclasses, Spree::ReportLineItem and its two subclasses, Reports::GenerateJob, ReportMailer and its view, ReportSubscriber, the v3 ReportSerializer, the Spree.reports registry, the reports queue, the mailer preview and factory, and the Store / admin-user associations.

The tables are not touched. The migration that creates spree_reports and spree_report_line_items is deleted, so a fresh 6.0 install never creates them, while an existing store keeps its rows exactly where they are — inert, read by nothing, and available if a merchant wants to export them by hand. Dropping them is not scheduled; there is no data-loss moment to time.

Design Details

Registry (spree/core)

  • Spree::Reporting::Registry with metric(name, **opts) / dimension(name, **opts), exposed as Spree.reporting. Definitions are frozen structs; re-registration under the same name raises outside of an explicit replace: flag.
  • Bases name the root relation the compiler starts from, always store-scoped: :orders → store.orders.complete, :line_items → store.line_items (joined through completed orders). A query's members must share a compatible base; the compiler picks the finest.
  • Starter vocabulary (core-registered): the sales chain (gross_sales, discounts, returns, net_sales, shipping, duties, fees, taxes, total_sales — Decision 14), volume (orders, units_sold, customers, derived average_order_value), margin (cost_of_goods, gross_profit, gross_margin) and marketplace (commission, seller_payout, both permission-gated); dimensions completed_at (time), channel, category, product, variant, customer (grouped by order email, lookup via store.customers), customer_type, seller, market, country, payment_status, fulfillment_status. The dashboard home consumes a subset — see Implementation Path.
  • Dimension lookups return display payloads, not just labels: { id, label, meta } where meta comes from the lookup's compact serializer (product → slug, thumbnail_url, display price; customer → email; channel → name). This is what lets the top-products widget render thumbnails from a generic contract response.
  • Derived metrics reference other metrics (aov), computed post-aggregation so division-by-zero and rounding conventions live in one place (nil-growth convention from the 5.6 dashboard carries over: no baseline → null, client renders "New").

Compiler + endpoint (spree/api)

  • Spree::Reporting::Query — validates the JSON against the registry (unknown member/op/grain → 422 listing valid options), normalizes time ranges (-90d, ISO dates), resolves prefixed-id filter values via each dimension's lookup, applies forced scopes, and hands a normalized plan to the adapter.
  • compare: "previous_period" compiles both periods and returns aligned series + per-metric growth — replacing the hand-rolled previous_time_range/day-offset logic in DashboardAnalyticsSerializer.
  • Response shape (one query powers tiles and chart): { totals: { metric → { value, display, growth } }, rows: [ { dimensions: {...}, metrics: { metric → { value, display } }, compare: {...} } ], meta: { currency, channel, time_range, previous_time_range } }. totals is the whole-period aggregate (KPI tiles); rows is the grouped series (chart/rankings); compare mirrors per row when requested. Growth keeps the 5.6 convention: no previous-period baseline → null, client renders "New".
  • Dimension values in rows carry prefixed id + resolved label + meta ({ "id": "ch_x", "label": "Wholesale" }).
  • Result caching: Rails.cache keyed on (store, normalized query hash, adapter), short TTL; per-query cache: false escape hatch.
  • Guardrails: limit capped (≤ 1000), max dimensions per query, statement timeout on the live adapter.

Saved reports (spree/core + spree/api)

  • Spree::Reporting::SavedReport < Spree.base_class: store_id, optional user_id, name, query (JSON; the visualization is inferred from the query's shape), seeded flag; Spree::SingleStoreResource; prefixed id (sq_); lifecycle events. CRUD at /api/v3/admin/reporting/saved_reports.
  • Seeds ship the classic report set (sales over time, top products, top categories, top customers, sales by channel/country) as rows on the Reports page. The home screen does not depend on seeds — it issues its queries inline so the splash experience is deterministic on every install.

Storage adapters

class Spree::Reporting::Adapters::Base
  # plan: normalized query (members resolved to definitions, scopes applied)
  # returns rows of { dimension_values..., metric_values... }
  def execute(plan) = raise NotImplementedError
end
  • Live (default): Arel against OLTP through store associations. Ships on this branch.
  • Facts (opt-in, 6.1): typed daily-grain tables — spree_analytics_order_facts (store_id, channel_id, vendor_id, market_id, currency, day, decimal metric columns, counts) and spree_analytics_line_item_facts (adds product_id/taxon_id). Written incrementally from lifecycle events (order.completed, order.canceled, refund events — subscribers in app/subscribers/spree/), recompute-the-touched-day semantics (rollup-style), refunds/cancellations restate the original day (Pulse's accounting rule), nightly reconciliation + backfill via rake tasks (never migrations). Falls back to Live for members without fact coverage.
  • Enterprise ClickHouse: same interface, out of scope for this repo beyond keeping the seam honest.
  • Selection via Spree::Dependencies.reporting_adapter, per-store override possible later.

Dashboard + SDK (packages/*)

  • client.reporting.query(json) + client.reporting.savedReports.* in @spree/admin-sdk; hand-written types for the contract (no Alba serializer → no typelizer).
  • packages/dashboard: a useReportingQuery(query) hook; chart components take contract result shape directly; a Reports SPA page (nav slot admin.nav.reports already exists, unused) lists saved reports and renders them; "edit query" UI can start as JSON-lite (metric/dimension/filter pickers driven by a registry-introspection endpoint: GET /reporting/schema).
  • The branch's dashboard/analytics + dashboard/rankings endpoints are deleted and replaced by the query endpoint (see Implementation Path Step 6); dashboard/operations stays as the point-in-time state endpoint.

AI layer (later)

  • GET /reporting/schema (registry introspection) doubles as the tool-schema source. The agent tool is literally "run_analytics_query(json)" + "list/save report". "Why did EU revenue dip last week?" = a few compiled queries + a rendered chart + an offer to save. Ships against the CLI first, in-app agent second.

Implementation Path (this branch)

Everything below lands on claude/react-dashboard-home-reporting-71weaz. Nothing on that branch has shipped, so the hand-rolled endpoints are replaced outright — no shims, no back-compat.

Step 1 — Registry (spree/core)

Spree::Reporting::Registry + definition structs, exposed as Spree.reporting; core registers the starter vocabulary in the engine initializer. The net_sales SQL is lifted from DashboardSerializerHelpers#revenue_sum_sql, which this step deletes — the module was always the registry's larval form. Unit specs for registration, duplicate-name raising, allowlist introspection.

Step 2 — Compiler + Live adapter (spree/core)

Spree::Reporting::Query (contract validation → 422 payloads), plan normalization (time ranges, prefixed-id filter resolution, forced store/currency/vendor scopes), Adapters::Live (Arel through store.orders/store.line_items, base selection from requested members, store-timezone grains per DB, compare compiling both periods with day-alignment). The existing dashboard serializer specs convert into compiler specs — same factories, same expected numbers, new shape.

Step 3 — API surface (spree/api)

POST /api/v3/admin/reporting/query + GET /api/v3/admin/reporting/schema on a new ReportingController (scoped_resource :analytics, read_reports scope, CanCanCan for JWTs). Result caching keyed on (store, query hash). Controller specs incl. the channel-filter, foreign-store 404, and reject-unknown-member cases carried over from the dashboard specs.

Step 4 — SDK (packages/admin-sdk)

client.reporting.query(q), client.reporting.schema(), hand-written contract types (ReportingQuery, ReportingResult). Deletes the dashboard.analytics/dashboard.rankings methods + DashboardAnalytics/DashboardRankings types. dashboard.operations stays (point-in-time state, not analytics).

Step 5 — New dashboard home (splash screen) on the contract

useReportingQuery(query) in packages/dashboard wraps the SDK with TanStack Query (key = normalized query). The screen keeps the shipped Pulse layout; every widget becomes a contract query, with the header's channel switcher and date-range picker writing one shared filter/time_range object that all queries inherit:

Widget Query Powered by
KPI tiles + comparison chart metrics: [total_sales, orders, average_order_value, units_sold, customers], dimensions: [{completed_at, grain: day}], compare: previous_period One request: totals → the five tiles with growth badges; rows → both chart series
Rankings → Customers tab metrics: [total_sales, orders], dimensions: [customer], sort: -total_sales, limit: 5 rows (dimension meta → name/email + customer link)
Rankings → Categories tab metrics: [net_sales, units_sold], dimensions: [category], sort: -net_sales, limit: 5 rows (dimension meta → category link)
Top products metrics: [net_sales, units_sold], dimensions: [product], compare: previous_period, sort: -net_sales, limit: 5 rows (+ per-row growth for trend badges; product meta → thumbnail/slug/price)
Operations card dashboard.operations Evaluates the counters registered on Spree.reporting (Decision 15); the card renders whatever arrives — label, value and link all come from the server

Chart/tile/ranking-row components become shape-generic (consume ReportingResult), which is what the Reports page reuses in Step 7. GrowthBadge, legend, deep-link rows carry over as-is.

Step 6 — Delete the replaced surface

dashboard_analytics_serializer.rb, dashboard_rankings_serializer.rb, dashboard_serializer_helpers.rb, the analytics/rankings controller actions + routes, their specs, and the SDK members from Step 4. dashboard/operations survives (its hand-rolled serializer was later replaced by registry counters — Decision 15). The home e2e spec is updated in place — its UI-level assertions (tiles, legend, switcher, deep links) should pass unchanged, which is the acceptance test for the rebuild.

Step 7 — Schema enrichment (serves the builder and the AI layer in one change)

GET /reporting/schema today returns names only. Both goals need it to carry meaning: label/description per member (via Spree.t, so the picker UI and the tool schema localize from one source), format with the store currency for money metrics, per-dimension compatible_metrics (the base-compatibility rule the compiler enforces — without it an agent hits total_sales × category 422s by trial and error), filter ops plus enumerated values for status dimensions, the time-range grammar, and meta with the store timezone + currency. The payload is permission-filtered: members the caller cannot reference (per required_subjects / required_key_scopes) are omitted, so pickers never offer and agents never propose a member that would 403.

Step 8 — Time presets + filter vocabulary

  • Named time presets resolved in the store's timezone: yesterday, week_to_date, month_to_date, quarter_to_date, last_week, last_month, last_quarter, last_n_weeks/last_n_months, alongside the existing ISO and -Nd forms. Add the week grain (day/month only today; sales-by-week is table stakes).
  • Filters are dimensions: any registered dimension is already filterable by the compiler (eq/in). The starter set the builder and the home screen expose: channel (shipped), market, category (product reports filtered to a category are the first-class merchandising question), country (ship address), payment_status, fulfillment_status; product/customer for drill-downs. Register market (orders market_id) and country (join ship address) as dimensions so they are groupable and filterable — never filter-only special cases.
  • Money breakdown metrics for classic reports: discounts_total, delivery_total, tax_total; a variant dimension for SKU-level merchandising. Refunds stay deferred to the facts adapter (restatement semantics).

Step 9 — Saved reports, Reports page, builder, export

  • Spree::Reporting::SavedReport (query JSON — the visualization is inferred from the query's shape, no viz column until a chooser exists, store-scoped, prefixed id sq_, seeded flag, lifecycle events; write_reports gates mutations) with CRUD under /api/v3/admin/reporting/saved_reports. Execution re-runs required_subjects / required_key_scopes against the viewer's credential, never the author's — a report saved by a full admin 403s for a limited role.
  • Reports SPA page under the existing admin.nav.reports slot: seeded classics (sales over time, top products, top categories, top customers, sales by channel / market / country) + user reports, rendered through Step 5's shape-generic components.
  • Picker builder, no query language: metric checkboxes, one breakdown (dimension + grain), time preset/range, filters from Step 8, compare toggle, sort/limit. Visualization is inferred from the query shape (time dimension → bar/line with compare; value dimension → ranked list with share bars; no dimension → stat tiles) — merchants never configure charts. The home screen stays a plain overview (its chart is the seeded "Sales over time" report); the builder accepts a ?query= hand-off for future callers, but no home-screen button uses it.
  • CSV export of any query result through the existing exports infrastructure.

Step 10 — Agent surface

  • Result shape is already chat-friendly (totals + rows with display strings and hydrated labels). Expose separate tools — reporting.schema, reporting.query, reporting.explain (returns the resolved plan: members, ranges, currency, previous range — no execution, so the model can confirm its reading of "revenue last month by channel"), reporting.saved_reports.{list,get,save} — never one "ask" tool. First via the spree api CLI (already works with a read_reports + per-subject key) and a spree/agent-skills skill documenting the contract; the hosted MCP server waits on OAuth from 6.0-platform-auth.
  • Agent-loop guardrails the plan promised but the branch has not shipped: adapter statement timeout and the Rails.cache result cache keyed on (store, normalized query, adapter).

Later (separate branches)

  • Seller-scoped reporting: Spree::Seller and Order.for_seller landed on main, so the marketplace forced scope is implementable now: the compiler injects seller_id for seller principals (their orders/products only), the :reports catalog resource opens to the seller audience, and the seller panel home runs the same queries under that scope. Register a seller dimension for staff-side breakdowns.
  • Facts adapter (6.1): schema + event subscribers + reconciliation/backfill rakes + refund-restates-original-day rules, behind the same contract; opt-in, Live stays default.
  • In-app AI agent on top of Step 10; Blazer-style checks/alerts on saved reports as a follow-up plan.

Legacy surfaces: Spree::Admin::AnalyticsConcern untouched (legacy admin is maintenance-mode); Spree::Report removed per Decision 16.

Constraints on Current Work

  • New metric bases or dimension joins must keep the totals rule (Decision 13): joins are grouped-only, filters on joined dimensions go through the id subquery in Adapters::Live#apply_filters, and nothing re-adds a join to the ungrouped query.

  • Every consumer that executes a reporting query authorizes through Query#unreadable_subjects — no second copy of the member walk.

  • Time-range values are ISO 8601 dates or datetimes (iso8601, never parse); bare dates cover the store's whole day on either edge and the dashboard sends custom ranges as bare dates.

  • No new hand-rolled aggregate endpoints. New analytics needs go through the registry + POST /reporting/query; a point-in-time count for the home screen is a registered counter evaluated by dashboard/operations — never a new method on a serializer, and never a number the dashboard computes or labels itself.

  • A count shown in the shell is a counter, read from the shared endpoint — never a list request with limit: 1 for its meta.count, which costs a round trip per badge and invents a second definition of the number.

  • A counter's link is declared beside its count, or not at all: the list a number opens must show exactly the rows counted. A counter and the list it opens share one scope rather than each expressing the question separately: the stock counters read Spree::StockLevel's own low_stock/out_of_stock, which is what the Inventory page filters on.

  • Every surface that lists vocabulary members filters through ReportingAuthorization#member_allowed? — metrics, dimensions and counters declare subject + key_scope alike; no endpoint gets a second predicate.

  • The low stock threshold is the store's low_stock_threshold preference, read through preferred_low_stock_threshold; no request parameter, no global, no per-variant column.

  • Dimension joins are LEFT joins. An optional association (an order's shipping address, a product's categories) must produce an "Unassigned" group, never drop the row — the Total counts it either way, and a breakdown summing to less than its own total is a silent undercount.

  • A new base is registered, never hardcoded — registry.base declares its family, table, relation, time column and reachability; nothing in the compiler branches on a base name, and the resolve_sql table map is the only remaining place a new table is named.

  • One family per query. Sales, payments and inventory never mix — a metric from one family beside a metric from another is refused, not joined.

  • The sales chain is the vocabulary (Decision 14): net_sales and total_sales are the single definitions of revenue; do not re-derive either elsewhere, and do not reintroduce gross_revenue/net_revenue as names.

  • Order-level money on another table is a correlated subquery, never a join on the aggregating scope — an order with three refunds stays one row.

  • New filters are dimensions. Register a dimension (with resolve/hydrate/subject/key_scope when it identifies a resource); never add filter-only parameters to the endpoint.

  • Keep forced-scope discipline: all analytics queries go through store associations (store.orders, store.line_items, …), filter exactly one currency, and use Order.for_channel for optional channel scoping.

  • Spree::Report is gone (Decision 16) — aggregate questions are registry members, row-level CSV is a Spree::Export. Nothing may reference Spree::Report, Spree::ReportLineItem or Spree.reports again.

  • New time-bucketed queries must document their timezone; prefer store timezone (Decision 7).

  • Don't adopt Blazer/rollup as dependencies (Decision 11).

Open Questions

  • Saved reports written against the pre-rename vocabulary (only possible on a store seeded from this branch before 2026-09-09): the row survives, and both paths refuse it loudly — a save fails validation naming every valid metric, and running an existing row raises UnknownMember with the same list. Nothing rewrites them, because a rename is not always a substitution (net_revenue has no successor with the same meaning). If any such store exists at release, the note belongs in the upgrade guide rather than in a data task.
  • Fact currency handling: per-currency fact rows (query one currency at a time, mirroring Live) vs converted-at-write "reporting currency" columns. Leaning per-currency rows; conversion is a display concern until merchants demand blended totals.
  • Store timezone changes restate historical day buckets in the facts adapter — accept + document, or version the timezone per row?
  • Seller-scoped saved reports: how store-wide saved reports appear in the seller panel once the seller forced scope lands (visibility itself is decided — Decision 12).
  • Checks/alerts (Blazer-style scheduled saved-report thresholds → email/webhook): follow-up plan or a rider on the facts-adapter work?
  • Deleting a seeded report: Seeds::SavedReports skips by name, so a built-in report a merchant deletes returns on the next db:seed. A persisted seed key (or a tombstone) would let the seed tell "never created" from "deliberately removed" — it needs a column and an upgrade path for stores already seeded, so it is deferred rather than patched into the seeding loop.

References

  • docs/plans/6.0-multi-vendor-marketplace.md (vendor dashboards, forced vendor scope), 6.0-admin-api.md, 5.5-admin-api-key-scopes.md, 5.5-admin-api-cli.md (CLI-first agent surface), 5.6-solid-queue-single-container.md, docs/plans/decisions.md 2026-07-22 (live-first sequencing).
  • 5.6 Pulse-style dashboard branch claude/react-dashboard-home-reporting-71weaz — the motivating specimen (serializers under spree/api/app/serializers/spree/api/v3/admin/dashboard_*).
  • Legacy systems: spree/core/app/models/spree/report.rb (+ reports/, report_line_items/), spree/admin/.../analytics_concern.rb.
  • Prior art: Cube.dev and Lightdash query contracts; Shopify ShopifyQL + Sidekick (the DSL tax this avoids); Saleor Pulse (refund restatement rule).
  • Evaluated gems: ankane/rollup (reference for incremental recompute; rejected as backbone — float values, single-metric rows, PG-only dimensions), ankane/blazer (reference for checks/alerts + saved-query rows; rejected — raw-SQL trust model, no store scoping, non-embeddable UI).