Skip to content

Marketplace Reporting & Dashboards

Purpose

Read-only, server-authoritative sales/settlement/return/rating/inventory reporting for sellers (their own store) and admins (the whole marketplace). Every figure is computed server-side from the same immutable transaction records the rest of the marketplace already relies on - never recalculated from current product price, current commission configuration, or any client-supplied number. There is no write endpoint in this slice: no export, no scheduled report, no admin override of a figure. Builds on Seller Order Fulfilment, Payments & Seller Settlements, Marketplace Returns & Refunds, and Product Reviews & Ratings; notifies nothing itself, but its numbers describe the same events Marketplace Notifications fires on.

Seller isolation

Every repository method on IMarketplaceReportingRepository takes a Guid? sellerUserId - null means marketplace-wide. The seller-scoped API handlers always pass the caller's own ICurrentUserService.UserId; the admin handlers always pass null. This means the admin dashboard is not a separately-implemented aggregation with its own risk of drifting from the per-seller numbers - it is literally the same query, unfiltered - so an admin total is trivially reconcilable against the sum of every individual seller's own dashboard for the same date range, by construction, not by a separate reconciliation step. SellerUserId is always resolved server-side from ICurrentUserService inside the seller controller action - no request parameter on SellerReportsController accepts a client-supplied seller id (enforced by ReportingControllersAuthorizationTests.SellerReportsActions_NoParameterAcceptsASellerIdFromTheClient).

Financial source of truth

Figure Source Notes
GMV Sum(Order.SubTotal) for orders placed in range The original, server-authoritative customer order merchandise value - never redefined as net-after-refund. Admin-only figure (a single customer order can span multiple sellers).
Merchandise sales / tax Sum(SellerOrder.SubTotal) / Sum(SellerOrder.TaxAmount) Per-seller-order snapshot, not the catalog Product.Price at report time.
Gross / commission / net payable / seller shipping Sum(SellerSettlement.GrossAmount / PlatformCommissionAmount / NetPayableAmount / SellerShippingAmount) for settlements created in range Zero if no settlement exists yet for an order in range - never estimated from current commission config. A Settled settlement's fields are immutable (see Payments & Seller Settlements); reporting only ever reads them, never recomputes them. Anchored to CreatedAt - see "Settlement date semantics" below.
Settled amount Sum(NetPayableAmount) where Status = Settled and SettledAt falls in range Not a cohort figure - anchored to SettledAt, not CreatedAt. See "Settlement date semantics" below.
Pending settlement amount Sum(NetPayableAmount) where Status ∈ {Pending, Eligible, Processing, OnHold}, for settlements created in range The current outstanding amount for settlements created in the range - historical "pending as of a past date" cannot be reconstructed without settlement-status history, so this is the one honest definition available. Anchored to CreatedAt, like every other cohort figure above.
Settlement adjustments Sum(SettlementAdjustment.NetSellerDebitAmount) Reported as its own, clearly separate figure (SettlementAdjustmentsAmount) - never added into NetPayableAmount or the refund total, since it is itself a distinct, additive-only post-settlement correction record.
Refund amount Sum(RefundTransaction.TotalRefundAmount) The sole source of refund totals - never derived by subtracting a "refunded quantity" from merchandise sales, which would double-count against SettlementAdjustment.
Return count / rate COUNT(ReturnRequest) in range / ReturnCount ÷ TotalSellerOrders Rate is null (not 0) when there were zero orders in range, so the frontend can distinguish "0% returns" from "no data yet".
Refund rate (admin only) TotalRefunds ÷ Gmv null when GMV is zero in range.
Average rating / review count ProductReview aggregates (Visible only) scoped to the seller's products Mirrors Product Reviews & Ratings - hidden reviews never counted.
Inventory stats SellerInventory/SellerProduct current state, not date-ranged Active listings, total sellable units, low-stock, out-of-stock counts as of now - inventory has no meaningful "for this date range" framing, unlike every other metric here.

No double counting: refunds, settlement adjustments, and settlement net-payable are always reported as three separate fields side by side - a UI or downstream consumer that needs a single "seller take-home" number must combine them explicitly; this slice never pre-combines them into one figure that would obscure which part came from where.

Settlement date semantics

A SellerSettlement has two independent timestamps - CreatedAt (when the settlement record was generated) and SettledAt (when it actually reached Status = Settled, i.e. when the payout was recorded) - and they routinely fall in different report ranges (generated this week, paid out next week). Every settlement figure in this slice picks exactly one of the two anchors, deliberately:

  • Gross/Shipping/Commission/NetPayable/PendingSettlementAmount are COHORT figures, anchored to CreatedAt - "how much did settlements generated in this period represent". PendingSettlement Amount specifically means the current outstanding amount for settlements created in the range - not "what was pending as of some past date", which cannot be reconstructed without a full settlement-status history table (this codebase has none).
  • SettledAmount is NOT a cohort figure - it is anchored to SettledAt (Status = Settled AND SettledAt within the range), i.e. "how much cash actually moved during this period". A settlement created in an earlier period and paid out in this one counts here; one created in this period but not yet paid out does not.
  • SettlementAdjustmentsAmount is anchored to the adjustment's own CreatedAt, entirely independent of its parent settlement's CreatedAt/SettledAt - a post-settlement refund can (and routinely does) land in a different report range than the settlement it adjusts.

Consequences a report reader should expect:

  • Querying the range a settlement was created in shows it in the cohort figures (Gross/ Commission/NetPayable/Pending) but not in SettledAmount unless it also happened to be paid out in that same window.
  • Querying the range a settlement was settled in shows it in SettledAmount even if it was created in an earlier, unrelated range - and does not re-add it to that range's cohort figures.
  • A historical report for an old period never changes merely because a settlement created back then was later settled - the old period's SettledAmount stays honestly zero for it; only the current period (containing the real SettledAt) ever counts it as settled.

Every combination above (created-before/settled-inside, created-inside/settled-after, created-and-settled-inside, created-inside/still-pending, adjustment-dated-independently, and the old-range-unaffected case) is covered by a dedicated test in ReportingFlowTests.cs - see "Tests" below.

Query performance

MarketplaceReportingRepository (Infrastructure/Repositories/) queries only the already-indexed, date/seller-bounded rows it needs - no full entity graph is ever loaded to aggregate in memory, and every list query is AsNoTracking.

Provider-aware aggregation - the SQLite fallback is NOT the production strategy

Aggregation is provider-aware, branching on _db.Database.ProviderName (a plain string check, not a package dependency - MarketplaceReportingRepository.IsSqlite):

  • MySQL/Pomelo (production): every decimal SUM, GROUP BY, ORDER BY, and pagination (Skip/Take) is pushed to the database via SumAsync/GroupBy/OrderBy/Skip+Take - no financial row list is ever materialized into API memory solely to aggregate it. A year's worth of order/order-line/settlement rows is never loaded to compute a total; only the already-aggregated result (one row per status/product/seller, or one page of already-grouped rows) crosses into application memory.
  • Microsoft.EntityFrameworkCore.Sqlite (this codebase's own test provider, ONLY): falls back to projecting just the needed decimal column(s) into a small anonymous shape (.Select(x => new { x.Amount }).ToListAsync()) and calling .Sum()/.GroupBy() in-process. This exists purely for test-provider compatibility, not as the production aggregation strategy - SQLite's EF Core provider cannot translate Sum() over a decimal column at all (NotSupportedException, SQLite has no native DECIMAL type), including for a plain, non-grouped SumAsync call. Even where this fallback runs, only bounded scalar columns are ever pulled - still filtered by the same date/seller predicate as everything else - never a full entity graph.

Every aggregation preserves exact decimal precision on both code paths - amounts are never converted to double for summing (ReportRatingStats.AverageRating and ReportSellerPerformanceRow.AverageRating are the sole double fields in this slice, and both are genuine star-rating averages, never a financial amount).

Applies to GetOrderStatsAsync, GetParentOrderStatsAsync, GetSettlementStatsAsync, GetReturnStatsAsync, GetTopProductsAsync, GetTopSellersAsync, GetProductPerformanceReportAsync, and GetSellerPerformanceReportAsync. GetOrdersReportAsync/GetReturnsReportAsync/ GetSettlementsReportAsync never needed the branch at all - their pagination was already pushed to the database (Skip/Take before ToListAsync) on both providers from the start.

GetProductPerformanceReportAsync's MySQL path additionally computes the per-product distinct order count via the standard EF Core Select(x => x.Id).Distinct().Count() idiom inside the GroupBy projection, which translates to COUNT(DISTINCT ...) - the one aggregate expression in this file worth re-checking if a future EF Core/Pomelo version changes GroupBy translation support.

Known Pomelo gotcha (found by the manual MySQL smoke test below, now fixed): constructing a custom record/class directly inside a database-translated GroupBy().Select(g => new SomeRecord(...)) does not reliably translate on Pomelo - it throws InvalidOperationException at query-compile time instead of translating, even though the equivalent projection into a plain anonymous type (new { ... }) translates fine. Every MySQL-branch GroupBy().Select() in this file therefore projects into a plain anonymous type first, and only maps that already-materialized, already-bounded result into the real record/DTO in C# afterward (GetTopProductsAsync, and all three sub-aggregates inside GetSellerPerformanceReportAsync). GetProductPerformanceReportAsync already followed this shape by construction (its record projection happens in a separate .Select() after Skip/Take, not inside the GroupBy().Select() itself). SQLite's own GroupBy path is always client-side (LINQ-to-Objects, after ToListAsync()), so it structurally cannot reproduce this failure - ReportingFlowTests.cs's GetTopProductsAsync_MultipleProducts_... and GetSellerPerformanceReportAsync_MultipleSellers_... tests guard the underlying grouping/ ordering/merge logic on both providers, but the Pomelo-specific translation risk itself is only re-verified by re-running the manual smoke test below.

Manual MySQL provider verification

The automated test suite (ReportingFlowTests.cs) runs exclusively against Microsoft.EntityFrameworkCore.Sqlite - it never executes the MySQL/Pomelo aggregation branch described above. Before merging any change to a GroupBy/Sum/OrderBy query in MarketplaceReportingRepository, re-verify it against a real MySQL 8 server:

  1. cd to the repo root, docker compose up -d mysql redis (uses the existing docker-compose.yml service, port from docker compose ps - 3309 by default, ${MYSQL_HOST_PORT} if overridden in .env). Never point this at ecommerce_db if a dev api container is already using it - CREATE DATABASE a disposable, throwaway database instead (e.g. reporting_mysql_smoke).
  2. dotnet ef database update --project src/ECommerce.Infrastructure --startup-project src/ECommerce.API --connection "Server=localhost;Port=<port>;Database=<disposable-db>;User=root; Password=<local-only>;SslMode=None;AllowPublicKeyRetrieval=True;" to apply every migration.
  3. Add a temporary, never-committed test file that opens AppDbContext against that connection string (mirroring DependencyInjection.cs's real UseMySql(...) call exactly - same MySqlServerVersion, no retry strategy) instead of SqliteDbContextFactory, seed representative data through the same real command handlers ReportingFlowTests.cs uses, and call each MarketplaceReportingRepository method directly.
  4. DROP DATABASE the disposable database and delete the temporary test file(s) once done - nothing from this procedure is ever committed.

This exact procedure caught the GroupBy().Select()-into-a-record gotcha above.

GetSellerPerformanceReportAsync - a documented, bounded exception

GetSellerPerformanceReportAsync (admin-only seller leaderboard) is the one query that does not paginate in the database on either provider. On MySQL it still pushes each of its three sub-aggregates (per-seller order count/merchandise, per-seller net-payable, per-seller average rating) to the database independently via GroupBy/Sum/Average - so no order/settlement/review row list is loaded, only one small row per seller per sub-aggregate. Only the final step - merging those three already-small per-seller lists into one row per seller, and sorting/paginating that merged result - happens in memory. This is a deliberate, bounded-by-seller-count tradeoff (documented here, not an oversight): merging three independently-grouped per-seller aggregates into one row is not naturally expressible as a single further-grouped SQL query without duplicating a three-way LEFT JOIN by hand, and the merge is bounded by the number of sellers with activity in the range - never by order count - which stays small even at real marketplace scale.

Additive index migrations (no data change, clean Down() on both):

AddMarketplaceReportingIndexes
  SellerOrder        (SellerUserId, CreatedAt)
  SellerSettlement    (SellerUserId, CreatedAt)
  ReturnRequest       (SellerUserId, RequestedAt)
  RefundTransaction   (SellerUserId, RecordedAt)
  Order               (CreatedAt)

AddSellerSettlementSettledAtIndex
  SellerSettlement    (SellerUserId, SettledAt)   -- supports the SettledAmount query above,
                                                    -- which filters by SettledAt independently
                                                    -- of the (SellerUserId, CreatedAt) index

Date/time semantics

ReportDateRange.Resolve(preset, from, to) (Application/Features/Reporting/ReportingShared.cs) - every date boundary is UTC and inclusive of both ends.

Preset Range
Today Start of today (UTC) → now
Last7Days 7 days ago → now
Last30Days (default when preset is omitted) 30 days ago → now
ThisMonth 1st of current month → now
PreviousMonth 1st → last day of the previous calendar month
Custom from/to as supplied - max 366 days, from must be ≤ to, otherwise ReportInvalidDateRangeException → 400 REPORT_INVALID_DATE_RANGE

A zero-result range (e.g. before the seller's first order) is a normal, successful response with every count/sum at zero and every list empty - never an error.

Pagination

ReportPagination.Validate(page, pageSize) - default pageSize = 20, max 100. An out-of-range page/pageSize (≤ 0, or pageSize > 100) is rejected, not silently clamped - ReportInvalidPaginationException → 400 REPORT_INVALID_PAGINATION - so a caller never silently receives a different page shape than it asked for.

Backend architecture

CQRS queries via MediatR, mirroring every other read feature in this codebase - no commands, no writes.

Seller (Application/Features/Reporting/SellerReportingFeatures.cs): GetSellerDashboardQuery, GetSellerSalesReportQuery, GetSellerOrderReportQuery, GetSellerProductPerformanceQuery, GetSellerReturnsReportQuery, GetSellerSettlementsReportQuery.

Admin (Application/Features/Reporting/AdminReportingFeatures.cs): GetAdminMarketplaceDashboardQuery, GetMarketplaceSalesReportQuery, GetMarketplaceSellerPerformanceQuery, GetMarketplaceProductPerformanceQuery, GetMarketplaceReturnsReportQuery, GetAdminSettlementsReportQuery - each is the unfiltered (sellerUserId: null) form of the matching seller query (see "Seller isolation" above); admin DTOs additionally carry TotalCustomers/ActiveSellers/Gmv/TopSellers, which have no seller-scoped equivalent.

Both sets sit on IMarketplaceReportingRepository (Infrastructure/Repositories/ MarketplaceReportingRepository.cs), a standalone repository (not on IUnitOfWork) - the same pattern as ISettlementRepository/IReturnsRepository/IProductReviewRepository/ IMarketplaceNotificationRepository.

Seller APIs

GET /api/seller/reports/dashboard    ?preset|from&to
GET /api/seller/reports/sales        ?preset|from&to
GET /api/seller/reports/orders       ?preset|from&to&page&pageSize&sort
GET /api/seller/reports/products     ?preset|from&to&page&pageSize&sort
GET /api/seller/reports/returns      ?preset|from&to&page&pageSize&sort
GET /api/seller/reports/settlements  ?preset|from&to&page&pageSize&sort

[Authorize(Roles = "Seller,Admin")] - matches the existing SettlementsController convention: an Admin caller can reach these actions, but sees only their own (typically empty) seller data, since SellerUserId always comes from ICurrentUserService, never a route/query parameter.

Admin APIs

GET /api/admin/reports/dashboard     ?preset|from&to
GET /api/admin/reports/sales         ?preset|from&to
GET /api/admin/reports/sellers       ?preset|from&to&page&pageSize&sort
GET /api/admin/reports/products      ?preset|from&to&page&pageSize&sort
GET /api/admin/reports/returns       ?preset|from&to&page&pageSize&sort
GET /api/admin/reports/settlements   ?preset|from&to&page&pageSize&sort

[Authorize(Roles = "Admin")]. Distinct from the pre-existing GET /api/admin/dashboard (basic Product/Order/User counts + revenue, used by the legacy AdminDashboard.jsx cards) - that endpoint is untouched; /admin/reports/* is an additive, deeper reporting surface the legacy dashboard now links out to.

DTOs

Every reporting DTO is defined once in SellerReportingFeatures.cs (seller-scoped) or AdminReportingFeatures.cs (admin-scoped, importing the seller namespace's shared shapes - ReportDateRangeDto, TopProductDto, PagedResultDto<T>). Paginated endpoints return PagedResultDto<T> { items, total, page, pageSize }; the returns/settlements endpoints additionally wrap that page inside a small summary DTO ({ range, returnCount, refundAmount, items } / { range, grossAmount, ..., items }) so a caller gets the range-wide totals and one page of rows in a single response.

Frontend

  • Seller - /seller/reports (pages/Seller/Reports/SellerReports.jsx), linked from the seller sidebar and from a "Reports & Dashboards" button on /seller (the existing listings-focused dashboard is otherwise untouched). Six tabs - Overview, Sales, Orders, Products, Returns, Settlements - each independently fetches its endpoint via a dedicated React Query hook (useSellerDashboard, useSellerSalesReport, etc. in hooks/index.js), sharing one date-range picker across tabs.
  • Admin - /admin/reports (pages/Admin/Reports/AdminReports.jsx), linked from the admin sidebar and from a "Reports & Dashboards" button on /admin. Same six-tab shape, with "Sellers" in place of "Orders" (a marketplace-wide seller leaderboard has no seller-scoped equivalent).
  • Shared controls (components/reports/ReportToolbar.jsx) - DateRangeFilter (preset select + conditional custom from/to date inputs, values matching ReportDateRange.Resolve exactly), Pagination, BarList (a labelled, proportional-width bar list built from plain CSS/HTML - no charting library exists in package.json and none was added here, matching the requirement not to pull in a large new dependency for this slice), and shared StatCard/Card display widgets so the two dashboards stay visually consistent.
  • Every status badge pairs a color with its text label (Badge component) - status is never conveyed by color alone. Tables use <caption class="sr-only">, scope="col" headers, and accessible pagination (nav[aria-label="Pagination"]).

Authorization

  • SellerReportsController - class-level [Authorize(Roles = "Seller,Admin")], no action-level [AllowAnonymous] override, no action parameter accepts a client-supplied seller/user id.
  • AdminReportsController - class-level [Authorize(Roles = "Admin")], same no-override guarantee.
  • Verified by reflection-based wiring tests (Api/ReportingControllersAuthorizationTests.cs) - these check attribute wiring only; the actual HTTP-level enforcement is ASP.NET Core's authorization middleware, exercised end-to-end by the rest of the test suite (login as Seller/Admin/Customer and call the endpoints) rather than re-proven here.

Deferred work (explicitly out of scope for this slice)

  • Exports (CSV/Excel/PDF) - every endpoint returns JSON only; no export/download feature exists in this slice.
  • Scheduled/emailed reports - no background job or delivery channel produces a report automatically; every figure is computed on-demand, synchronously, per request.
  • Promotions/coupons metrics - feature/promotions-coupons has not been merged into this branch and no coupon/discount field is guessed at or referenced anywhere in this slice. The DTOs and repository queries are structured so that a future discount-amount figure could be added as one more field alongside MerchandiseSales/TaxAmount without reshaping any existing endpoint - but no such field exists yet.
  • Custom/saved report configurations - date range and pagination are the only supported filters; there is no per-user saved-report or custom-column feature.
  • Real-time/live-updating dashboards - every figure is a point-in-time snapshot fetched on tab load/date-range change; no polling or push-based refresh exists here (contrast Marketplace Notifications's unread-count polling, which this slice does not replicate).

Tests

  • Application/ReportingFlowTests.cs - the financial-correctness suite: zero-order seller, one-order seller, seller-to-seller isolation (Seller A's report excludes Seller B's orders; the admin report reconciles A+B), partial/full/multiple returns without double counting, refund-before-settlement (settlement stats stay honestly zero), refund-after-settlement (creates a SettlementAdjustment, never mutates the original settlement's NetPayableAmount), settled-immutable settlement, commission/shipping/tax always sourced from the settlement/order snapshot, date-range boundary inclusion, zero-result ranges, invalid date-range/pagination rejection, pagination correctness (including the marketplace-wide product-performance report, whose pagination is pushed to the database on MySQL), and a reflection check proving no query record exposes a spoofable "requested seller" parameter.
  • Settlement date semantics (see the section above) - six dedicated scenarios, each backdating a real, command-handler-produced settlement's CreatedAt/SettledAt via a raw SQL update (the domain entity itself exposes no public setter for either field) rather than hand-seeding a synthetic row: created-before-range/settled-inside-range (counted in SettledAmount), created-inside-range/settled-after-range (not counted in SettledAmount for that range), created-and-settled-inside-range (counted exactly once in both the cohort and settled figures, verified against a disjoint range and against the admin-reconciled total), created-inside-range/ still-pending (counted in PendingSettlementAmount, not SettledAmount), a post-settlement adjustment remaining dated by its own CreatedAt independent of its parent settlement's, and a historical range around the old CreatedAt never showing SettledAmount for a settle event that happened later.
  • Grouping/ordering regression coverage - GetTopProductsAsync_MultipleProducts_ GroupsAndOrdersByRevenueDescending and GetSellerPerformanceReportAsync_MultipleSellers_ MergesPerSellerAggregatesCorrectly (the latter previously had no dedicated test at all). Added after the manual MySQL smoke test (see "Manual MySQL provider verification" above) found both methods' MySQL-only code paths threw InvalidOperationException from an unrelated GroupBy().Select()-into-a-record translation gap - these two tests guard the underlying grouping/ordering/merge logic on both providers; they cannot reproduce the Pomelo-specific translation failure itself (SQLite's GroupBy is always client-side), which only the manual smoke-test procedure re-verifies.
  • Api/ReportingControllersAuthorizationTests.cs - reflection-based [Authorize] wiring for both controllers (see "Authorization" above).