Reporting & Analytics

Purpose: the authoritative catalog of POS reports, their requirements (data, filters, format), the compute architecture, and the data-model fields/collections each report depends on. Defined in the plan phase so the Appwrite collections capture the right data from day one. Status: as of 2026-07-09. Source decisions confirmed with product owner; MVP suite now implemented (see below). Related: data-model.md · pos-domain-model.md · mobile screen design/screens/25-reportes.md · web flow design/web-screens/flow-11-reportes.md · backlog: api-appwrite-reporting-daily-sales-report.md, api-appwrite-reporting-end-of-day-z-report.md.

Why this exists

Reporting turns raw POS events into business decisions (what sells, who sells it, where cash leaks, what the card processor costs). The legacy ReportService aggregated a single transactions table and had no product-, employee-, store-, or shift-level breakdown. As we build the Appwrite POS collections, we lock in the fields and indexes those reports need before the append-only ledgers and snapshot rows exist — refactoring them later means data backfills, not just code changes.

Implementation status (2026-07-09, `terminales-api`)

The MVP report suite is built on the API, matching the decisions below:

Endpoints live under src/reports/pos/mvp-reports.controller.ts (#178): daily sales, sales by employee / by product / by payment-method, voids & discounts, and cash-movements — each with its own dto/*-query.dto.ts (date-range + filters).
CSV export via src/reports/pos/report-csv.util.ts; shared pagination via report-pagination.ts; money formatting via report-money.ts. CSV is still the only export format (per the v1 decision).
Rollups: the daily_sales_rollups writer (src/pos/sales/rollup-writer.ts) is written by the durable finalize workflow (src/pos/sales/finalize-workflow.ts), so long-range reports read snapshots.
Schema is pinned by offline specs (rollups-schema.spec.ts, sales-schema.spec.ts).

Decisions

Compute strategy — hybrid. Live Appwrite queries for short ranges / drill-downs; pre-aggregated daily_sales_rollups + frozen shift_reports snapshots for fast long-range and historical reports.
Z-report — frozen immutable snapshot written by the shift-close flow (not recomputed on demand).
Export — CSV only for v1. PDF, share-sheet, and email are planned future formats (annotated, not built).

Compute architecture (hybrid)

Layer	Used for	Mechanism
Live query	Today / current shift, drill-downs, ticket lookup	Appwrite Query API client-direct under RLS; NestJS read where a cross-collection join is needed
Frozen snapshot	Z-reports / shift close	`shift_reports` document written by the shift-close DBOS workflow
Pre-aggregated rollup	Long ranges, trends, multi-store comparison, dashboards	`daily_sales_rollups` maintained incrementally by a DBOS workflow on order finalize / void

Business-date rule: rollups and Z-reports key on the store-local calendar date (stores.timezone, default America/Mexico_City), not UTC — otherwise “today’s sales” splits across the wrong day. Each derived row stores both businessDate (local YYYY-MM-DD) and the UTC range (utcFrom/utcTo) it covers.

Source-of-truth rule: rollups and snapshots are derived and reconcilable — never authoritative. The append-only ledgers (transactions, stock_movements) remain the source of truth and can rebuild any rollup by replay. Voids/refunds must decrement the affected rollup, never be silently dropped.

Offline rule: reporting is online-only; the offline terminal does not compute aggregates.

Role scope (applies to every report)

RLS scopes all reads to the store team. Within that:

Role	Sees
cashier	own shift / own sales only
supervisor	the whole store
manager / owner	the store and multiple stores (portfolio)

Common filters available everywhere: date range (from/to) and store (multi-store roles only).

Must-have reports (MVP)

1. Daily Sales Summary (Corte Diario)

Purpose: end-of-day business pulse per store.
Data: gross sales, net sales (gross − commissions), tax collected, discounts, refunds/voids, transaction count, average ticket, units sold; sales-by-tender breakdown; sales-by-hour series.
Filters: date range, store, terminal, employee.
Source: daily_sales_rollups (range); live fallback from sales_orders + transactions + order_payments.
Format: KPI cards + line/bar chart + summary table; CSV export.

2. Shift Z-Report & Cortes de caja history (mobile Corte Z / web W58)

Purpose: cash reconciliation and audit per shift.
Data (frozen at close): cashier, terminal, opened/closed at, opening float, expected vs counted cash, variance, sales by tender, cash movements, commissions, txn count, totals. History list shows corte #, cajero, fecha, vendido, diferencia, estado.
Filters: date range, store, employee, status (open/closed), variance threshold (e.g. shortages only).
Source: shift_reports (frozen snapshot); shifts for still-open shifts.
Format: reprintable detail + history table; CSV export.

3. Sales by Product / Category

Purpose: what sells; merchandising and margin decisions.
Data: per product/variant/category — units sold, gross, net, margin (revenue − cost), % of total, avg price; top-N ranking.
Filters: date range, store, category, employee, dimension toggle (product/category), top-N.
Source: line_items with denormalized productId / categoryId / unitCost snapshot (see adjustments).
Format: ranked table + bar chart; CSV export.

4. Sales by Payment Method (tender mix)

Purpose: understand cash vs card mix and card cost.
Data: per method/provider — amount, count, % of sales, commissions, net, effective commission rate.
Filters: date range, store, employee, method, provider.
Source: order_payments + transactions (commission / netAmount).
Format: summary table + donut/bar; CSV export.

5. Sales by Employee (cashier performance)

Purpose: staff productivity and loss-prevention attribution.
Data: per employee — gross/net sales, txn count, avg ticket, units, discounts given, voids/refunds, cash variance from their shifts.
Filters: date range, store, role.
Source: sales_orders.employeeId, transactions.employeeId, shift_reports, discounts.
Format: comparison table; CSV export.

6. Commissions Report (web W57)

Purpose: reconcile PSP / platform fees.
Data: by provider/method/concept — operations count, volume, commission, net, effective rate, margin.
Filters: date range, store, provider, method.
Source: transactions.commission / netAmount, payment_intents.provider.
Format: breakdown table; CSV export.

7. Cash Movements Report (Movimientos de efectivo)

Purpose: track non-sale cash in/out for reconciliation.
Data: per movement — kind (PAY_IN/PAY_OUT, plus DROP/PETTY_CASH if adopted), amount, reason, employee, shift, time; totals by kind.
Filters: date range, store, employee, kind.
Source: cash_movements.
Format: ledger table; CSV export.

8. Voids & Discounts (exceptions / loss prevention)

Purpose: surface overrides that erode revenue and flag abuse.
Data: voided orders and applied discounts — amount, reason, who performed it, who authorized (approval), original order ref, time.
Filters: date range, store, employee, approver, type (void/discount).
Source: transactions(type=VOID), sales_orders void fields, discounts, approvals, audit_log.
Format: exception table; CSV export.

Additional useful reports (later phases)

Report	Purpose	Key data	Main source	Notes / gap
Inventory / Stock	On-hand, low-stock, shrinkage	qty on hand, value, movements by type, ADJUSTMENT shrinkage	`stock_levels`, `stock_movements`	Needs `costPrice` for valuation
Sales by Terminal/Device	Per-device performance & health	sales, txn count, last seen, app/os version	`transactions.terminalId`, `terminals`
Multi-store Comparison	Owner/manager portfolio view	sales/net/margin per store, ranking, period delta	`daily_sales_rollups`	Rollups make this cheap
Tax / Fiscal (CFDI)	Tax collected for filing	tax by rate, taxable base, exempt	`line_items.taxAmount`, `tax_rates`	Aligns with later CFDI epic
Merchant Balance / Settlement	Wallet/payout reconciliation	credits, withdrawals, commissions, balance trend	legacy `balance_movements`, `merchant_balances`	Legacy TypeORM until migrated
Refunds Report	Refund volume & reasons	refunded amount, reason, original order	`transactions`, `payment_intents`	Model refund explicitly (see adjustments)
Audit Trail	Security/compliance review	who changed what, before/after	`audit_log`
Sales Trends / ABC	Forecasting, fast/slow movers	period-over-period, ABC class	`daily_sales_rollups`	Future analytics
Customer reports	Repeat/loyalty analytics	—	—	Blocked: no customer entity today; decide if needed

Data-model adjustments (what the collections must capture)

These extend the contracts defined in the AppwriteData backlog tickets. Field names below are additions on top of each ticket’s current contract.

A. New collections (reporting persistence)

shift_reports — frozen Z-report snapshot, written by the shift-close DBOS workflow; immutable.

shiftId, storeId, storeTeamId, merchantId, terminalId, employeeId
businessDate (store-local), openedAt, closedAt
openingFloat, expectedCash, countedCash, variance
grossSales, netSales, taxTotal, discountTotal, refundTotal, txnCount, unitsSold
salesByTender (JSON: method → {amount, count}), commissionsTotal
cashMovementsSummary (JSON: kind → {amount, count})
reportNumber (sequential per store). CSV-exportable.

daily_sales_rollups — incremental aggregate, maintained by a DBOS workflow on finalize/void.

storeId, storeTeamId, merchantId, businessDate (store-local YYYY-MM-DD), utcFrom, utcTo
grossSales, netSales, taxTotal, discountTotal, refundTotal, txnCount, unitsSold, avgTicket
salesByTender (JSON), salesByHour (JSON array[24]), commissionsTotal
Unique index (storeId, businessDate); reconcilable by replaying the day’s ledger.
Start with store/day. Add companion rollups (per-employee/day, per-product/day, per-category/day) only if live drill-down proves too slow.

B. Field additions / denormalization on existing collections

Rationale: append-only ledgers and historical reports must reflect point-in-time values. If a row only stores FKs, later price/cost/category/name edits corrupt historical reports. Snapshot the reporting-relevant fields at write time.

line_items — add snapshot fields productId, categoryId, productName, sku, unitCost (copied from products.costPrice at sale → margin without joining the mutable catalog). Keep existing unitPrice, taxAmount, discountAmount, qty.
sales_orders — add shiftId, terminalId, finalizedAt, businessDate; void/refund fields voidedAt, voidedBy, voidReason, refundedAmount.
transactions — add commission, netAmount, paymentMethod, provider, entryMode, cardBrand, completedAt; extend status with REFUNDED. (Already has shiftId/employeeId/terminalId.)
order_payments — add provider; consider tipAmount. (Already has method, amount, changeGiven.)
shifts — add storeId (+ storeTeamId/merchantId for scoping), variance, explicit openedAt/closedAt if base timestamps aren’t sufficient. (Already has openingFloat, expectedCash, countedCash, status, terminalId, employeeId.)
discounts — add appliedBy, approvalId (links to approvals for the exceptions report). (Already has scope, kind, value, refId.)
products — confirm costPrice is captured (required for margin/inventory valuation). Currently optional.
cash_movements — confirm kind, amount, reason, shiftId. Consider extending the enum with DROP/PETTY_CASH if operations need drawer drops distinct from pay-outs.

C. Indexes for reporting performance

Collection	Indexes
`sales_orders`	`(storeId, finalizedAt)`, `(employeeId, finalizedAt)`, `(shiftId)`, `(status)`
`transactions`	`(storeId, completedAt)`, `(type, completedAt)`, `(shiftId)`, `(employeeId)`
`line_items`	`(productId)`, `(categoryId)` (+ join key to order / business date)
`shift_reports`	`(storeId, businessDate)`, `(employeeId, closedAt)`
`daily_sales_rollups`	unique `(storeId, businessDate)`
`cash_movements`	`(shiftId)`, `(storeId, createdAt)`

Export

v1: CSV only for every report listed above.
Future (annotated, not yet built): PDF (formatted Z-report / daily cut), share-sheet (mobile), email delivery.