mercadodevida/docs/reporting/REPORTING_ARCHITECTURE.md

Reporting — Arquitectura y análisis de datos

Estado: discovery / diseño inicial (sin dashboards ni endpoints implementados) Fecha: 2026-08-21 Tienda por defecto: Natural - Mercado de Vida

1. Decisiones ejecutivas

1. La fuente de verdad sigue siendo el monolito transaccional PostgreSQL. Reporting no crea ventas, pagos, stock ni clientes paralelos.
2. El hecho base es orders_orders + orders_items. Tanto ecommerce como TPV deben persistir una venta como pedido, distinguiéndola mediante orders_orders.source (ecommerce, pos, admin).
3. Los cálculos viven en backend, en un módulo reporting, no en React. El frontend recibe métricas preparadas, metadatos de disponibilidad y paginación.
4. Primera versión: SQL directo parametrizado sobre PostgreSQL, con CTEs y consultas separadas por informe. No se añade una tabla de agregados hasta medir volumen y latencia real.
5. Filtros son un contrato común y reproducible: fechas con zona horaria explícita, tienda/canal/terminal/cajero/método de pago/estado/producto/categoría/marca/cliente. La misma estructura alimenta API, URL y exportación.
6. No se muestran métricas no justificables. Margen, IVA por tipo, método de pago, devoluciones detalladas y diferencias de caja quedan como unavailable hasta disponer de datos históricos fiables.

2. Inventario de modelos existentes

2.1 Hechos transaccionales

Tabla	Grano	Datos aprovechables	Limitaciones actuales
orders_orders	Un pedido/venta	id, usuario, estado, moneda EUR, subtotal, descuento, impuesto, total, fechas, source, terminal y sesión POS	No tiene store_id directo. El total no separa portes. user_id es NULL para walk-in POS.
orders_items	Una línea de pedido	Producto/variante, SKU/EAN/nombre snapshot, precio unitario, descuento, impuesto, cantidad, fecha	No guarda cost_at_sale, tipo de IVA, marca/categoría snapshot ni devolución por línea.
payments_transactions	Un evento transaccional de proveedor	importe, moneda, estado, proveedor, order_id, fecha, idempotencia	No guarda método de pago, tienda, terminal, sesión, importes parciales de devolución ni FK a order. Eventos sin order_id son posibles.
inventory_movements	Un movimiento de variante/tienda	variante, tienda, operación, cantidad, fecha	No tiene order_id, usuario, motivo ni referencia de devolución; no permite casar una confirmación con una venta concreta.
pos_cash_sessions	Una sesión de caja/terminal	tienda, terminal, cajero, apertura/cierre, esperado, contado, diferencia	No existe tabla de movimientos de caja ni relación de pagos POS.

2.2 Dimensiones y configuración

Tabla	Uso en reporting
identity_users, users_profiles	Clientes ecommerce; orders_orders.user_id permite clientes únicos, nuevos y recurrentes. POS walk-in no tiene cliente.
backoffice_users	Cajeros/admin/editor; debe ser el actor de terminal/sesión y de auditoría, no un cliente.
catalog_products, catalog_product_variants	Nombre, estado, variante, SKU/EAN, peso, marca/categorías actuales.
brands_brands, categories_categories, catalog_product_categories	Dimensiones actuales de marca y categoría; los cambios posteriores afectan a informes históricos si no se añade snapshot/dimensión versionada.
pricing_variant_prices	Precio actual, oferta, coste actual y tipo de IVA. El coste actual no puede recalcular margen histórico.
pricing_price_history	Evolución del precio, no coste histórico de cada venta.
promotions_promotions, cart_carts.promo_code	Definición y uso de promociones; el pedido no guarda el código aplicado como snapshot.
tax_rates	Configuración actual de IVA por aplicación; no acredita el tipo usado en una venta histórica.
pos_stores, pos_terminals, pos_payment_methods	Multi-tienda, terminales y configuración de métodos. La configuración de método no es una transacción de pago.
orders_order_history	Auditoría de transiciones; útil para timeline, no sustituye devoluciones ni pagos.

3. Relaciones de reporting

orders_orders (1)
  ├── orders_items (N) ── catalog_products / variants
  ├── payments_transactions (N, order_id nullable)
  ├── identity_users (0..1)
  ├── pos_terminals (0..1) ── pos_stores
  ├── pos_cash_sessions (0..1) ── pos_stores + backoffice_users
  └── orders_order_history (N)

orders_items.variant_id
  ├── inventory_stock (N: store)
  ├── inventory_movements (N: store)
  └── pricing_variant_prices (1, current only)

Regla de joins: los endpoints de reporting deben partir de un CTE filtered_orders y unir líneas/agregados después. No hacer una consulta por pedido, producto o cliente (N+1).

4. Grano y definiciones de métricas

El servicio debe declarar en cada respuesta dataAvailability y definitions.

Disponibles con el modelo actual

• Pedidos/tickets: COUNT(DISTINCT o.id) con estados incluidos explícitos.
• Total cobrado registrado: SUM(o.total_cents) para ventas no canceladas, según la política de estados del endpoint.
• Ventas de mercancía brutas: SUM(i.unit_price_cents * i.quantity).
• Descuentos registrados: SUM(o.discount_cents) o SUM(i.discount_cents); nunca sumar ambos.
• Impuesto registrado: SUM(o.tax_cents) / SUM(i.tax_cents); la API debe escoger un solo grano.
• Unidades: SUM(i.quantity).
• Ticket medio: total cobrado / pedidos, con división segura por cero.
• Canal: o.source.
• Terminal/sesión/cajero: cuando la venta tenga terminal_id/cash_session_id; ecommerce sin asignación POS queda explícitamente como null/online.
• Productos: líneas agrupadas por product_id/variant_id, usando snapshots de nombre/SKU/EAN.
• Clientes únicos/nuevos/recurrentes: usuarios en pedidos, siempre separando walk-ins POS y excluyendo user_id IS NULL del denominador de clientes.
• Productos sin movimiento: stock actual por tienda + última fecha de orders_items en venta válida. Es una aproximación hasta que los movimientos tengan order_id.

No disponibles todavía o solo aproximables

• Ventas netas sin portes: orders_orders no separa shipping; hoy solo puede publicarse total cobrado y ventas de mercancía con nombres claros.
• Método de pago: payments_transactions no tiene payment_method_id/código; pos_payment_methods solo es catálogo de configuración.
• Pago mixto: no hay líneas de pago por pedido.
• Devoluciones por importe, producto, motivo o usuario: solo existen estados REFUNDED/PARTIALLY_REFUNDED y eventos de pago genéricos.
• IVA por tipo: las líneas guardan tax_cents, pero no guardan el vat_rate aplicado en el momento de la venta.
• Margen histórico: cost_cents actual no es cost_at_sale; no se muestra beneficio/margen hasta añadir snapshot de coste.
• Descuento por cupón/tipo: se guarda el total de descuento, no el cupón aplicado en orders_orders.
• Caja por método/movimiento: hay campos de cierre pero no entradas, salidas, retiradas ni pagos POS asociados.
• Comparación histórica por categoría/marca: usa la relación actual del catálogo, no una dimensión histórica; debe etiquetarse como clasificación actual o versionarse.

5. Correcciones de modelo necesarias antes de P0 financiero

No se deben ocultar estas carencias con cálculos frontend. Tickets de datos deben evaluar:

1. Añadir orders_orders.store_id NOT NULL con FK a pos_stores, backfill de la tienda por defecto e índice (store_id, created_at). POS y ecommerce deben escribirlo explícitamente.
2. Añadir a orders_items snapshots de vat_rate y cost_at_sale_cents nullable. Si el coste es NULL, margen es unavailable.
3. Crear order_payments/orders_payment_lines como líneas de pago inmutables: order, método, tienda, terminal, sesión, importe, moneda, provider reference, estado y timestamps. No almacenar PAN/CVV.
4. Crear order_refunds y líneas opcionales con importe, cantidad, motivo, actor, canal y timestamps; enlazar eventos de proveedor sin duplicarlos.
5. Persistir el promo_code/promotion id y el descuento aplicado por pedido/línea como snapshot.
6. Separar shipping en los totales (shipping_cents o una tabla de cargos) para no llamar “ventas netas” al total con portes.
7. Añadir movimientos de caja inmutables (cash_in, cash_out, sale, refund, opening, closing) vinculados a sesión y, cuando aplique, order/payment.

Cada cambio necesita migración, backfill/compatibilidad y ticket independiente; no forma parte de un dashboard improvisado.

6. Contrato API propuesto

Prefijo recomendado: /reporting. Todas las rutas requieren sesión de backoffice y permisos específicos.

GET /reporting/summary
GET /reporting/sales
GET /reporting/products
GET /reporting/categories
GET /reporting/brands
GET /reporting/payments
GET /reporting/cash-sessions
GET /reporting/discounts
GET /reporting/refunds
GET /reporting/customers
GET /reporting/inventory
GET /reporting/taxes
GET /reporting/dimensions/stores
GET /reporting/dimensions/terminals
GET /reporting/export/:report.csv
GET /reporting/export/:report.xlsx       (fase posterior)

Query común

from=2026-08-01T00:00:00Z
&to=2026-08-31T23:59:59Z
&compare=previous_equal|previous_calendar|none
&channel=all|ecommerce|pos|admin
&storeId=<uuid>                 (repetible)
&terminalId=<uuid>              (repetible)
&cashierId=<uuid>               (repetible)
&paymentMethodId=<uuid>         (repetible, cuando exista)
&productId=<uuid>               (repetible)
&categoryId=<uuid>              (repetible)
&brandId=<uuid>                 (repetible)
&customerId=<uuid>
&state=PAID,COMPLETED,...
&groupBy=day|week|month|hour|store|channel|terminal|cashier|payment
&page=1&pageSize=50
&sort=-revenue

El parser debe rechazar fechas invertidas, límites excesivos, IDs inválidos y combinaciones no soportadas. Rango inclusivo de inicio y exclusivo de fin ([from,to)) para evitar doble conteo.

Respuesta común

{
  "range": {"from":"...","to":"...","timezone":"Europe/Madrid"},
  "filters": {"channel":"pos","storeIds":[]},
  "comparison": {"range":null,"available":true},
  "dataAvailability": {"grossSales":true,"margin":false,"paymentMethod":false},
  "items": [],
  "totals": {},
  "updatedAt": "2026-08-21T20:00:00Z",
  "cache": {"hit":false,"maxAgeSeconds":30}
}

Los importes son céntimos enteros y la API entrega además currency: EUR. El porcentaje de variación debe ser null cuando el período anterior sea cero/no comparable, nunca Infinity.

7. Consultas y rendimiento

CTE base

WITH filtered_orders AS (
  SELECT o.*
  FROM orders_orders o
  WHERE o.created_at >= $1
    AND o.created_at < $2
    AND o.state IN (...) 
    AND ($3::text IS NULL OR o.source = $3)
    AND (cardinality($4::uuid[]) = 0 OR o.store_id = ANY($4))
), filtered_items AS (
  SELECT i.*, o.source, o.store_id, o.terminal_id
  FROM orders_items i
  JOIN filtered_orders o ON o.id = i.order_id
)
SELECT ...

La versión inicial debe medir EXPLAIN (ANALYZE, BUFFERS) con datos representativos. Índices recomendados solo tras confirmar planes:

• orders_orders (created_at, source, state) — valorar parciales según estados.
• orders_orders (store_id, created_at) una vez exista store_id.
• orders_orders (terminal_id, created_at) y (cash_session_id, created_at).
• orders_items (created_at) para última venta; (variant_id, created_at) para inventario.
• payments_transactions (created_at, status) y (order_id, created_at).
• inventory_movements (store_id, variant_id, created_at).

No añadir índices duplicados indiscriminadamente: comparar con los índices existentes y medir.

Caché

• P0 summary/sales: cache corta 30–60 s, key = reporte + hash ordenado de filtros.
• Tablas de dimensiones: 5 min o invalidación al cambiar catálogo.
• Exportaciones grandes: job/stream posterior, nunca cargar todo en React.
• Respuesta siempre indica updatedAt y cache.maxAgeSeconds.
• Materialized views/reporting tables quedan fuera hasta que EXPLAIN y volumen justifiquen su coste.

8. Frontend Admin

Ruta raíz: /reporting. Subrutas previstas:

/reporting              Resumen
/reporting/sales        Ventas
/reporting/products     Productos
/reporting/customers    Clientes
/reporting/payments     Pagos
/reporting/cash         Caja
/reporting/discounts    Descuentos
/reporting/refunds      Devoluciones
/reporting/inventory    Inventario
/reporting/taxes        IVA

Componentes reutilizables previstos:

• ReportingLayout, ReportingFilters, DateRangePicker, ComparisonSelector.
• KpiCard, ComparisonKpi, AvailabilityBadge, ReportingEmptyState.
• SalesChart, ChannelBreakdown, Heatmap (P2), ReportingTable.
• StoreSelector, TerminalSelector, CashierSelector, ProductSelector, ExportButton.

Los filtros se serializan en query params, con valores normalizados y sin secretos. La navegación drill-down conserva el filtro cuando la dimensión destino lo soporta.

9. Permisos y seguridad

El backend debe ser la autoridad, no solo visibleNavItems del frontend. Extender RBAC con permisos:

REPORTING_VIEW
REPORTING_SALES
REPORTING_PRODUCTS
REPORTING_CUSTOMERS
REPORTING_INVENTORY
REPORTING_PAYMENTS
REPORTING_CASH
REPORTING_FINANCIAL
REPORTING_EXPORT
REPORTING_ADMIN

Compatibilidad inicial: admin puede ver todo; editor y roles POS necesitan asignación explícita. REPORTING_FINANCIAL protege costes/margen, fiscal detallado y diferencias de caja. Aplicar autorización también a exports y a cada filtro de tienda/terminal, evitando que un cajero consulte otra tienda.

No devolver emails, direcciones ni identificadores de clientes salvo que el informe tenga permiso de clientes. Parametrizar todos los valores SQL y limitar pageSize/rango máximo.

10. Criterios de datos y estados

• Por defecto, ventas = estados PAID, PROCESSING, SHIPPED, DELIVERED, COMPLETED, PARTIALLY_REFUNDED; excluir PENDING, AWAITING_PAYMENT, CANCELLED y decidir cómo netear refund cuando exista la tabla de devoluciones.
• La respuesta diferencia loading, empty, no_data, partial_data, unavailable y error; no convierte una métrica no disponible en 0.
• Todas las horas se almacenan en UTC y se agrupan por zona configurada (Europe/Madrid inicialmente).
• Los cambios de catálogo no deben reescribir snapshots de pedido.
• Exportación debe incluir rango, zona horaria, filtros, fecha de generación y columnas seleccionadas.

11. Riesgos

Riesgo	Mitigación
orders_orders no tiene tienda para ecommerce	Añadir store_id antes del filtro multi-tienda obligatorio.
Totales mezclan mercancía, IVA, descuento y portes	Exponer nombres exactos y añadir cargos separados antes de “net sales”.
Datos POS aún incompletos	P0 de pagos/caja depende de líneas de pago y movimientos de caja.
JOIN de líneas duplica totales	CTE por grano: agregar líneas antes de unir dimensiones 1:N.
Coste actual usado históricamente	Rechazar margen hasta disponer de cost_at_sale_cents.
Reclasificación histórica	Añadir snapshots o declarar “clasificación actual”.
Consultas pesadas	Límites, índices medidos, cache corta, paginación server-side y EXPLAIN.
Fuga entre tiendas	Scope de tienda en SQL + autorización por usuario/terminal + tests negativos.
Export bloqueante	CSV streaming primero; XLSX/PDF en fase posterior/job.

12. Evolución futura

La frontera estable será:

Transactional modules
        ↓
Reporting query/read model (sin duplicar verdad)
        ↓
ReportingService / report definitions
        ↓
Reporting API + export adapter
        ↓
Admin Reporting UI

Más adelante puede insertarse una agregación/materialized view detrás de la misma interfaz cuando el volumen lo requiera. Forecasting, alertas, cohortes, RFM, informes programados, PDF y BI externo son P3 y no forman parte de la primera implementación.