erp-core/docs/04-modelado/database-design/AUTOMATIC-TRACKING-SYSTEM.md

# Sistema de Tracking Automático (mail.thread Pattern)

**Fecha:** 2025-11-24
**Versión:** 1.0
**Estado:** Implementado

---

## Resumen Ejecutivo

El sistema de tracking automático implementa el patrón `mail.thread` de Odoo en PostgreSQL, proporcionando auditoría completa de cambios en registros críticos del sistema. Este sistema registra automáticamente:

- Creación de registros
- Cambios de estado (draft → confirmed → done)
- Modificaciones en campos críticos (monto, cliente, fechas)
- Eliminaciones (soft delete)

---

## Arquitectura del Sistema

### Componentes Principales

1. **Tabla de Configuración** (`system.field_tracking_config`)
   - Define qué campos trackear por cada tabla
   - Configurable sin modificar código

2. **Tabla de Historial** (`system.change_log`)
   - Almacena todos los cambios registrados
   - Optimizada para consultas rápidas

3. **Función Trigger Genérica** (`system.track_field_changes()`)
   - Se ejecuta automáticamente en INSERT/UPDATE/DELETE
   - Lee configuración y registra cambios

4. **Triggers por Tabla**
   - Activan el sistema en tablas críticas
   - Sin impacto en performance (AFTER triggers)

---

## Tablas del Sistema

### 1. system.field_tracking_config

Configuración de qué campos trackear:

```sql
CREATE TABLE system.field_tracking_config (
  id UUID PRIMARY KEY,
  table_schema VARCHAR(50) NOT NULL,      -- 'financial', 'sales', etc.
  table_name VARCHAR(100) NOT NULL,       -- 'invoices', 'sales_orders', etc.
  field_name VARCHAR(100) NOT NULL,       -- 'status', 'amount_total', etc.
  track_changes BOOLEAN DEFAULT true,     -- Activar/desactivar tracking
  field_type VARCHAR(50) NOT NULL,        -- Tipo de dato
  display_label VARCHAR(255) NOT NULL,    -- Label para UI
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT uq_field_tracking UNIQUE (table_schema, table_name, field_name)
);
```

**Ejemplo de registros:**
```sql
INSERT INTO system.field_tracking_config
  (table_schema, table_name, field_name, field_type, display_label)
VALUES
  ('financial', 'invoices', 'status', 'text', 'Estado'),
  ('financial', 'invoices', 'amount_total', 'numeric', 'Monto Total'),
  ('sales', 'sales_orders', 'partner_id', 'uuid', 'Cliente');
```

---

### 2. system.change_log

Historial de cambios:

```sql
CREATE TABLE system.change_log (
  id UUID PRIMARY KEY,
  tenant_id UUID NOT NULL,                -- Multi-tenancy

  -- Referencia al registro modificado
  table_schema VARCHAR(50) NOT NULL,
  table_name VARCHAR(100) NOT NULL,
  record_id UUID NOT NULL,

  -- Usuario y fecha
  changed_by UUID NOT NULL,
  changed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  -- Tipo de cambio
  change_type VARCHAR(20) NOT NULL,       -- 'create', 'update', 'delete', 'state_change'

  -- Campo modificado
  field_name VARCHAR(100),                -- NULL para create/delete
  field_label VARCHAR(255),

  -- Valores
  old_value TEXT,
  new_value TEXT,

  -- Metadata
  change_context JSONB,                   -- Info adicional
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
```

**Índices optimizados:**
```sql
CREATE INDEX idx_change_log_record
  ON system.change_log(table_schema, table_name, record_id, changed_at DESC);
```

---

## Función Trigger: system.track_field_changes()

Función genérica que se activa automáticamente:

```sql
CREATE OR REPLACE FUNCTION system.track_field_changes()
RETURNS TRIGGER AS $$
DECLARE
  v_tenant_id UUID;
  v_user_id UUID;
  v_field_config RECORD;
BEGIN
  -- 1. Registrar creación (INSERT)
  IF TG_OP = 'INSERT' THEN
    INSERT INTO system.change_log (...)
    VALUES (..., 'create', ...);
    RETURN NEW;
  END IF;

  -- 2. Registrar eliminación (soft delete)
  IF TG_OP = 'UPDATE' AND NEW.deleted_at IS NOT NULL THEN
    INSERT INTO system.change_log (...)
    VALUES (..., 'delete', ...);
    RETURN NEW;
  END IF;

  -- 3. Registrar cambios en campos configurados (UPDATE)
  IF TG_OP = 'UPDATE' THEN
    FOR v_field_config IN
      SELECT field_name, display_label, field_type
      FROM system.field_tracking_config
      WHERE table_schema = TG_TABLE_SCHEMA
        AND table_name = TG_TABLE_NAME
        AND track_changes = true
    LOOP
      -- Comparar valores antiguo y nuevo
      IF old_value IS DISTINCT FROM new_value THEN
        INSERT INTO system.change_log (...);
      END IF;
    END LOOP;
  END IF;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
```

---

## Tablas con Tracking Activo

### Financial Schema

| Tabla | Trigger | Campos Trackeados |
|-------|---------|-------------------|
| `financial.invoices` | `track_invoice_changes` | status, partner_id, invoice_date, amount_total, payment_term_id |
| `financial.journal_entries` | `track_journal_entry_changes` | status, date, journal_id |

### Purchase Schema

| Tabla | Trigger | Campos Trackeados |
|-------|---------|-------------------|
| `purchase.purchase_orders` | `track_purchase_order_changes` | status, partner_id, order_date, amount_total, receipt_status |

### Sales Schema

| Tabla | Trigger | Campos Trackeados |
|-------|---------|-------------------|
| `sales.sales_orders` | `track_sales_order_changes` | status, partner_id, order_date, amount_total, invoice_status, delivery_status |

### Inventory Schema

| Tabla | Trigger | Campos Trackeados |
|-------|---------|-------------------|
| `inventory.stock_moves` | `track_stock_move_changes` | status, product_id, product_qty, location_id, location_dest_id |

### Projects Schema

| Tabla | Trigger | Campos Trackeados |
|-------|---------|-------------------|
| `projects.projects` | `track_project_changes` | status, name, manager_id, date_start, date_end |

---

## Ejemplos de Uso

### 1. Ver historial completo de una factura

```sql
SELECT
  cl.changed_at,
  u.name AS changed_by_user,
  cl.change_type,
  cl.field_label,
  cl.old_value,
  cl.new_value
FROM system.change_log cl
JOIN auth.users u ON u.id = cl.changed_by
WHERE cl.table_schema = 'financial'
  AND cl.table_name = 'invoices'
  AND cl.record_id = '123e4567-e89b-12d3-a456-426614174000'
ORDER BY cl.changed_at DESC;
```

**Resultado esperado:**
```
changed_at          | changed_by_user | change_type  | field_label    | old_value | new_value
--------------------|-----------------|--------------|----------------|-----------|----------
2025-11-24 10:30:00 | Juan Pérez      | state_change | Estado         | draft     | open
2025-11-24 10:15:00 | Juan Pérez      | update       | Monto Total    | 1000.00   | 1500.00
2025-11-24 10:00:00 | Juan Pérez      | create       | NULL           | NULL      | NULL
```

---

### 2. Ver quién cambió el estado de una orden

```sql
SELECT
  changed_at,
  old_value AS estado_anterior,
  new_value AS estado_nuevo,
  u.name AS usuario
FROM system.change_log cl
JOIN auth.users u ON u.id = cl.changed_by
WHERE cl.table_name = 'sales_orders'
  AND cl.record_id = '<uuid-orden>'
  AND cl.field_name = 'status'
ORDER BY cl.changed_at DESC;
```

---

### 3. Ver cambios en un rango de fechas

```sql
SELECT
  cl.table_name,
  cl.record_id,
  cl.field_label,
  cl.old_value,
  cl.new_value,
  u.name AS usuario
FROM system.change_log cl
JOIN auth.users u ON u.id = cl.changed_by
WHERE cl.table_schema = 'financial'
  AND cl.changed_at BETWEEN '2025-11-01' AND '2025-11-30'
  AND cl.change_type = 'state_change'
ORDER BY cl.changed_at DESC;
```

---

### 4. Auditoría: Cambios en montos de facturas

```sql
SELECT
  cl.record_id,
  i.number AS factura_numero,
  cl.changed_at,
  cl.old_value AS monto_anterior,
  cl.new_value AS monto_nuevo,
  u.name AS modificado_por
FROM system.change_log cl
JOIN financial.invoices i ON i.id = cl.record_id
JOIN auth.users u ON u.id = cl.changed_by
WHERE cl.table_name = 'invoices'
  AND cl.field_name = 'amount_total'
  AND cl.changed_at >= CURRENT_DATE - INTERVAL '7 days'
ORDER BY cl.changed_at DESC;
```

---

## Agregar Tracking a Nuevas Tablas

### Paso 1: Agregar configuración de campos

```sql
INSERT INTO system.field_tracking_config
  (table_schema, table_name, field_name, field_type, display_label)
VALUES
  ('mi_schema', 'mi_tabla', 'status', 'text', 'Estado'),
  ('mi_schema', 'mi_tabla', 'amount', 'numeric', 'Monto'),
  ('mi_schema', 'mi_tabla', 'partner_id', 'uuid', 'Cliente');
```

### Paso 2: Crear trigger en la tabla

```sql
CREATE TRIGGER track_mi_tabla_changes
  AFTER INSERT OR UPDATE OR DELETE ON mi_schema.mi_tabla
  FOR EACH ROW EXECUTE FUNCTION system.track_field_changes();

COMMENT ON TRIGGER track_mi_tabla_changes ON mi_schema.mi_tabla IS
'Registra automáticamente cambios en mi_tabla';
```

**¡Listo!** El tracking está activo automáticamente.

---

## Desactivar Tracking de un Campo

```sql
UPDATE system.field_tracking_config
SET track_changes = false
WHERE table_schema = 'financial'
  AND table_name = 'invoices'
  AND field_name = 'notes';
```

---

## Performance y Optimización

### Índices Existentes

- `idx_change_log_record`: Búsqueda por tabla/registro
- `idx_change_log_changed_at`: Búsqueda por fecha
- `idx_change_log_record_date`: Índice compuesto optimizado

### Recomendaciones

1. **Archivado periódico**: Mover registros antiguos a tabla de archivo
2. **Particionado**: Considerar particionado por fecha si el volumen crece
3. **Monitoreo**: Vigilar tamaño de `system.change_log`

### Query de Mantenimiento

```sql
-- Ver tamaño de la tabla de tracking
SELECT
  pg_size_pretty(pg_total_relation_size('system.change_log')) AS total_size,
  COUNT(*) AS total_records
FROM system.change_log;

-- Limpiar registros antiguos (ejecutar periódicamente)
DELETE FROM system.change_log
WHERE changed_at < CURRENT_DATE - INTERVAL '2 years'
  AND change_type != 'state_change'; -- Mantener cambios de estado
```

---

## Beneficios del Sistema

### Para Auditoría
- Historial completo de cambios
- Respuesta inmediata a preguntas de "¿Quién modificó esto?"
- Cumplimiento de normativas (GDPR, SOX, etc.)

### Para Debugging
- Rastrear errores de datos
- Detectar patrones de uso
- Investigar inconsistencias

### Para Analytics
- Analizar flujos de trabajo
- Medir tiempos de procesamiento
- Identificar cuellos de botella

---

## Troubleshooting

### Problema: No se registran cambios

**Causa:** Trigger no creado o configuración faltante

**Solución:**
```sql
-- Verificar si existe el trigger
SELECT * FROM pg_trigger
WHERE tgname LIKE 'track_%';

-- Verificar configuración
SELECT * FROM system.field_tracking_config
WHERE table_name = 'mi_tabla';
```

---

### Problema: Performance degradada

**Causa:** Volumen alto de cambios

**Solución:**
1. Revisar índices
2. Archivar datos antiguos
3. Considerar particionado

---

## Próximos Pasos

### Mejoras Futuras (Opcional)

1. **API REST**: Endpoint `/api/v1/change-log/:recordId`
2. **UI Component**: `<ChangeHistory />` en frontend
3. **Notificaciones**: Alertas en cambios críticos
4. **Rollback**: Función para revertir cambios

---

## Referencias

- Patrón mail.thread de Odoo: https://www.odoo.com/documentation/16.0/developer/reference/backend/orm.html#odoo.models.Model
- PostgreSQL Triggers: https://www.postgresql.org/docs/current/plpgsql-trigger.html
- Auditoría en PostgreSQL: https://www.postgresql.org/docs/current/ddl-rowsecurity.html

---

**Documento creado por:** Software Architect Agent
**Última actualización:** 2025-11-24
**Versión:** 1.0