296 lines
16 KiB
Markdown
296 lines
16 KiB
Markdown
# 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
|
||
|
||
```text
|
||
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.
|
||
|
||
```text
|
||
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
|
||
|
||
```text
|
||
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
|
||
|
||
```json
|
||
{
|
||
"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
|
||
|
||
```sql
|
||
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:
|
||
|
||
```text
|
||
/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:
|
||
|
||
```text
|
||
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á:
|
||
|
||
```text
|
||
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.
|