Audience: Product owners, QA, frontend and backend developers Scope: The analytics dashboard, the report registry, exports, the operator sync surface, and the background ETL

Reports & Analytics Module - Overview

1. What the module is

Two halves, one admin API at /api/admin/analytics, nothing customer-facing.

Analytics — "why is the business performing this way." Dashboard tiles, KPIs with period comparison, day-by-day trends, the conversion funnel, channel and payment-method splits, top products, geography and review sentiment. Served from pre-aggregated daily rollups, so these are cheap.

Reports — "what happened." A registry of 19 reports across 9 categories, served by three generic routes rather than one route per report.

Every figure is read from MongoDB, which is populated from PostgreSQL by a background ETL. PostgreSQL stays the source of truth — the analytics store is a projection, and every figure in it is rebuildable from the transactional store.

2. The routes — 19, all admin

All under /api/admin/analytics/, all requiring Authorization: Bearer <admin token>.

Rate limits are per admin account, not per IP — a shop's staff share one public address.

3. Ten permission modules — the split is the point

Analytics plus nine Reports* modules. An operations lead can hold ReportsInventory_READ and ReportsOrders_READ without ever seeing revenue; a finance user the reverse. _CREATE, _DELETE and _RESTORE are unused — they exist because the permission catalogue is a cross product — and Analytics_UPDATE is the module's only non-_READ action in use, gating the three sync triggers.

GET /reports returns only the reports the caller may run. Build the report picker from that response — do not hard-code the list. Refund figures sit behind ReportsSales_READ.

4. The ETL: PostgreSQL → MongoDB, every two minutes

The module reads PostgreSQL and writes Mongo. 13 streams project in dependency order (products before order_lines, so snapshot fields are never null); order_lines is parent-driven by the orders stream with no watermark of its own. The incremental sync runs every 2 minutes — that interval is the dashboard's staleness.

Figures trail PostgreSQL by up to one sync interval (~2 min), and every response says so. dataAsOf and stalenessSeconds ride on every envelope; behind: true means a stream is still catching up. Before the first sync the API returns 503 (ANALYTICS_NOT_YET_SYNCED) rather than zeros — zero revenue is indistinguishable from no trading.

5. The watermark needs four mechanisms, not one

updated_at is never written by a trigger. It is written by the ORM ($onUpdateFn(() => new Date())) or as sql\now()`` (transaction-start time) — both stamp earlier than commit. A sync that read past an uncommitted row's timestamp and advanced its watermark would lose that row forever.

So the ETL runs: overlap (15 minutes, well beyond any transaction, free because re-projection is idempotent) + conditional advance (a truncated run only advances as far as it processed; the watermark never moves backward) + reconcile (hourly, re-projects 30 days ignoring the watermark, reports drift) + backfill (resumable full rebuild, seeds the watermark where it finished).

6. analytics_pending_work — because a watermark cannot see everything

Settling a refund never touches the orders row — orders.refunded_amount moves at refund approval, and settlement writes only order_refunds. Nothing would carry that settlement into net revenue. So settling a refund queues the order (and, for an old order, its rollup day), and a drain job re-projects them. The queue is idempotent (_id = kind:key), and entries resolve only after their work commits.

7. Rollups are recomputed, never incremented

MongoDB here is a standalone with no multi-document transactions, so a running counter would need exactly-once delivery, which at-least-once dispatch does not provide. Every rollup document is instead a full recompute of one (day, channel) via replaceOne(upsert) — a duplicate or out-of-order event is harmless by construction, and a field that becomes zero actually becomes zero. A partially-projected day is wrong for one tick and correct on the next.

8. Periods, timezones and money

• All day boundaries are Asia/Kathmandu, pinned at projection time so a timezone change cannot re-bucket history. An order at 23:50 local on the 31st belongs to that month.
• Comparison periods are calendar-aligned. "This month" on the 7th compares 1–7 August against 1–7 July — meta.comparison.truncatedForFairness: true says so.
• Net revenue deducts SETTLED refunds only. approvedRefundedAmount and settledRefundedAmount are separate fields and will legitimately differ — an approved-but-unsettled refund is a liability, not a deduction.
• POS is not counted twice. A counter sale is one order with channel: "pos", not a second record. analytics_pos_sale_facts carries till dimensions only and is never a revenue source.
• All money is integer paisa. netRevenue: 1452000 is NPR 14,520.00.

9. Exports — asynchronous, capped, expiring

A report over a year of orders can aggregate for minutes, so exports are queued jobs: POST /exports returns immediately with an exportId; poll GET /exports/{exportId} until ready, then follow downloadUrl. idempotencyKey is required and unique per admin — a double-click is safe. Files are capped at 50,000 rows (the cap sets truncated on the record AND writes a warning line into the file), swept after 48 hours (410 ANALYTICS_EXPORT_EXPIRED afterwards), and the download route re-checks the report's permission against the caller's live role.

10. The operator sync surface — reads and triggers, never destroys

GET /sync (Analytics_READ) answers "is the dashboard stale, and why?" — consecutiveFailures (5 in a row = stopped pipeline), behind (page budget), driftCount (reconcile repairs). POST /sync/refresh|backfill|reconcile (Analytics_UPDATE) trigger the work. There is no reset or delete action — the repair for bad analytics data is to re-project it, and neither backfill nor reconcile deletes anything.

11. What the module deliberately does not do

• No product-view funnel stage. No view counter exists anywhere in PostgreSQL, so a product→cart conversion rate has no denominator. /funnel returns unavailableStages saying so; the funnel starts at cart.
• No substring search. Report search is prefix-anchored and case-insensitive — filters={"customerName":"ram"} matches "Ram Bahadur", not "Sita Ram". The index cannot serve unanchored scans.
• wishlistCount is the one figure a facts-only rebuild cannot restore — no wishlist stream exists.
• No outbox for exports. The module writes no PostgreSQL table, so the outbox has no transaction to join; the export record is Mongo, written first, with an hourly sweep that re-enqueues anything stuck in queued.

12. Where to go next