workspace-v1/orchestration/reportes/ANALISIS-TABLAS-AUDITORIA-2026-01-07.md

# ANALISIS DE TABLAS DE AUDITORIA
## Proyecto GAMILIT - Fase B1

**Fecha:** 2026-01-07
**Estado:** ANALISIS COMPLETADO

---

## RESUMEN EJECUTIVO

| Metrica | Valor |
|---------|-------|
| Tablas analizadas | 8 |
| Tablas con solapamiento critico | 3 |
| Tablas a mantener separadas | 3 |
| Tabla deprecated | 1 |
| Reduccion propuesta | 37.5% (8 -> 5) |

---

## LAS 8 TABLAS ANALIZADAS

| # | Tabla | Proposito | Lineas | Estado |
|---|-------|-----------|--------|--------|
| 1 | audit_logs | Auditoria completa de acciones | 124 | Activa |
| 2 | performance_metrics | Metricas de rendimiento | 102 | Activa |
| 3 | system_alerts | Alertas del sistema | 131 | Activa |
| 4 | system_logs | Logs del sistema | 115 | Activa |
| 5 | user_activity_logs | Analytics de usuarios (educativo) | 119 | Activa |
| 6 | activity_log | Admin dashboard | 219 | Activa |
| 7 | user_activity | Actividad simplificada | 43 | **DEPRECATED** |
| 8 | pending_user_initialization | Retry de inicializacion | 136 | Activa |

---

## SOLAPAMIENTOS IDENTIFICADOS

### CRITICO: user_activity (DEPRECATED)

**Problema:** Tabla `user_activity` duplica exactamente la funcionalidad de `activity_log`

| Aspecto | user_activity | activity_log |
|---------|---------------|--------------|
| Columnas | 8 | 10 |
| Proposito | Activity logging | Activity logging |
| Estado | DEPRECATED | Activa |
| Referencias backend | 13+ | Principal |

**Recomendacion:** ELIMINAR `user_activity`, migrar referencias a `activity_log`

### ALTO: audit_logs + system_logs

**Solapamiento:** 70%

| Columna Compartida | audit_logs | system_logs |
|-------------------|------------|-------------|
| tenant_id | Si | Si |
| actor/user_id | Si | Si |
| request_id | Si | Si |
| correlation_id | Si | Si |
| error_code | Si | Si |
| error_message | Si | Si |
| stack_trace | Si | Si |
| timestamps | Si | Si |

**Diferencias:**
- `audit_logs`: old_values, new_values, changes (cambios de datos)
- `system_logs`: line_number, execution_time_ms, memory_usage (ejecucion)

**Recomendacion:** Consolidar en `audit_logs_unified` con campo discriminador

### MEDIO: user_activity_logs + activity_log

**Solapamiento:** 60%

| Columna Compartida | user_activity_logs | activity_log |
|-------------------|-------------------|--------------|
| user_id | Si | Si |
| activity/action_type | Si | Si |
| description | Si | Si |
| metadata | Si | Si |
| ip_address | Si | Si |
| user_agent | Si | Si |

**Diferencias:**
- `user_activity_logs`: module_id, exercise_id, classroom_id (educativo)
- `activity_log`: entity_type, entity_id (generico)

**Recomendacion:** Consolidar en `user_event_log` con JSONB flexible

---

## TABLAS A MANTENER SEPARADAS

| Tabla | Razon |
|-------|-------|
| performance_metrics | Datos operacionales (time-series), no eventos |
| system_alerts | Logica especifica (escalamiento, resolucion) |
| pending_user_initialization | Proposito muy especifico (retry mechanism) |

---

## OPCIONES DE CONSOLIDACION

### Opcion A: Conservadora (Recomendada para inicio)

**Acciones:**
1. Eliminar `user_activity` (deprecated)
2. Mantener resto de tablas
3. Planificar consolidacion futura

**Esfuerzo:** 5-10 horas
**Riesgo:** Bajo

### Opcion B: Moderada (Recomendada a mediano plazo)

**Acciones:**
1. Eliminar `user_activity`
2. Crear `audit_logs_unified` (audit_logs + system_logs)
3. Crear `user_event_log` (user_activity_logs + activity_log)

**Tablas finales:** 5 (reduccion 37.5%)
**Esfuerzo:** 25-30 horas
**Riesgo:** Medio

### Opcion C: Agresiva (No recomendada)

**Acciones:**
1. Consolidar todas en 2 tablas
2. Migracion masiva de datos

**Esfuerzo:** 40-60 horas
**Riesgo:** Alto

---

## PLAN DE ACCION RECOMENDADO

### Fase Inmediata (Esta semana)

**Tarea B1.1:** Eliminar tabla `user_activity` (deprecated)

Pasos:
1. Auditar 13+ referencias en backend
2. Actualizar imports a `activity_log`
3. Ejecutar tests
4. DROP TABLE

**Esfuerzo:** 4-6 horas
**Riesgo:** Bajo (tabla ya deprecated)

### Fase Posterior (Siguiente sprint)

**Tarea B1.2:** Consolidar audit_logs + system_logs

Pasos:
1. Crear tabla `audit_logs_unified`
2. Migrar datos historicos
3. Actualizar aplicacion
4. Validar y DROP antiguas

**Esfuerzo:** 15-20 horas
**Riesgo:** Medio

---

## ESTRUCTURA PROPUESTA: audit_logs_unified

```sql
CREATE TABLE audit_logging.audit_logs_unified (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,

    -- Discriminador
    log_source VARCHAR(20) NOT NULL, -- 'audit' | 'application' | 'system'

    -- Clasificacion
    event_type TEXT NOT NULL,
    log_level VARCHAR(20), -- TRACE/DEBUG/INFO/WARN/ERROR/FATAL
    severity VARCHAR(20),  -- debug/info/warning/error/critical

    -- Actor
    actor_id UUID,
    actor_type VARCHAR(20) DEFAULT 'system',
    actor_ip INET,
    actor_user_agent TEXT,
    session_id TEXT,

    -- Tracking
    request_id TEXT,
    correlation_id TEXT,

    -- Contexto
    module_name TEXT,
    function_name TEXT,
    resource_type TEXT,
    resource_id UUID,

    -- Cambios (para audit)
    old_values JSONB,
    new_values JSONB,

    -- Descripcion
    message TEXT,
    description TEXT,

    -- Errores
    error_code TEXT,
    error_message TEXT,
    stack_trace TEXT,

    -- Datos flexibles
    metadata JSONB DEFAULT '{}',

    -- Status
    status VARCHAR(20),

    -- Timestamp
    created_at TIMESTAMPTZ DEFAULT gamilit.now_mexico(),

    CONSTRAINT log_source_check CHECK (log_source IN ('audit', 'application', 'system'))
);

-- Indices
CREATE INDEX idx_audit_unified_created ON audit_logging.audit_logs_unified(created_at DESC);
CREATE INDEX idx_audit_unified_source ON audit_logging.audit_logs_unified(log_source);
CREATE INDEX idx_audit_unified_tenant ON audit_logging.audit_logs_unified(tenant_id);
CREATE INDEX idx_audit_unified_actor ON audit_logging.audit_logs_unified(actor_id) WHERE actor_id IS NOT NULL;
CREATE INDEX idx_audit_unified_errors ON audit_logging.audit_logs_unified(log_level, created_at DESC)
    WHERE log_level IN ('ERROR', 'FATAL');
```

---

## CONCLUSION

**Recomendacion:** Comenzar con **Opcion A** (eliminar deprecated) y planificar **Opcion B** para siguiente sprint.

**Siguiente paso:** Auditar referencias de `user_activity` en backend para preparar eliminacion.

---

**Documento generado:** 2026-01-07
**Responsable:** Arquitecto de Datos