Diseñando para la Desconfianza: Patrones de Auditoría en Bases de Datos

Cuando construyes una aplicación "To-Do List", si un usuario borra una tarea por error, es una molestia. Cuando construyes sistemas para banca, salud o seguridad pública, si un registro desaparece o se modifica sin rastro, es una negligencia legal.

En SudoLabs, nuestra filosofía de diseño de bases de datos para proyectos sensibles (como OFICRI) se basa en una premisa simple: No confiar en el estado actual. El valor que tiene una celda en este momento es irrelevante si no puedes explicar cómo llegó ahí.

El Mito del "Soft Delete"

La mayoría de los desarrolladores conocen el patrón "Soft Delete": agregar una columna deleted_at o is_active y filtrar los resultados.

-- Enfoque tradicional
SELECT * FROM expedientes WHERE deleted_at IS NULL;

Esto resuelve el problema de "recuperar" datos borrados, pero es insuficiente para la auditoría. Si un usuario cambia el "Estado" de un expediente de 'Pendiente' a 'Finalizado' y luego de vuelta a 'Pendiente', el Soft Delete no te cuenta esa historia. Solo ves el estado actual. Has perdido la trazabilidad del comportamiento errático o malicioso.

Patrón de Tabla de Historial (Audit Log)

Para garantizar integridad forense, implementamos un patrón de Tablas de Seguimiento Inmutables.

Por cada entidad crítica (ej. un Oficio o Expediente), existe una tabla satélite dedicada exclusivamente a registrar cambios de estado. Esta tabla es Append-Only (Solo inserción, nunca actualización ni borrado).

Estructura del Modelo

Imaginemos una tabla seguimiento_oficio:

La Lógica de Negocio

En lugar de simplemente actualizar la tabla principal, nuestras transacciones de base de datos siempre ocurren en pares atómicos:

1. UPDATE de la entidad principal (para reflejar la realidad actual).
2. INSERT en la tabla de seguimiento (para dejar evidencia histórica).

// Ejemplo conceptual en Node.js + Knex
await db.transaction(async (trx) => {
  // 1. Obtener estado actual para el historial
  const currentState = await trx('oficios').where('id', id).first();
 
  // 2. Actualizar entidad principal
  await trx('oficios')
    .where('id', id)
    .update({ estado: 'EN_LABORATORIO' });
 
  // 3. Insertar rastro de auditoría
  await trx('seguimiento_oficio').insert({
    oficio_id: id,
    estado_anterior: currentState.estado,
    estado_nuevo: 'EN_LABORATORIO',
    usuario_responsable: currentUser.id,
    fecha_evento: new Date(),
    meta_data: JSON.stringify({ ip: request.ip }) // Contexto crítico
  });
});

Si cualquiera de los dos pasos falla, la base de datos hace Rollback. Nunca puede existir un cambio de estado sin su huella de auditoría correspondiente.

Normalización y Consistencia (3NF)

Otro aspecto crucial de la seguridad es la integridad de los datos. En entornos de alta presión, la duplicidad de datos es el enemigo.

Si guardamos el nombre del "Perito Asignado" como texto en la tabla del documento, y ese perito cambia de apellido o rango, tenemos datos inconsistentes.

Nos adherimos estrictamente a la Tercera Forma Normal (3NF):

• Cada dato pertenece a un único lugar.
• Los documentos solo referencian IDs de usuarios, nunca sus nombres.
• Las tablas de configuración (ej. tipos_examen) controlan los valores permitidos, evitando "magic strings" o errores de tipeo en la base de datos.

Seguridad por Diseño, no por Parche

Implementar estos patrones hace que el desarrollo inicial sea un 20% más lento. Tienes que escribir más migraciones, más modelos y transacciones más complejas.

Pero el valor real se revela el día que hay un incidente.

• "¿Quién autorizó este cambio?"
• "¿Por qué este expediente demoró 5 días en esta etapa?"
• "¿Este documento fue modificado después de ser firmado?"

Con un sistema de auditoría robusto, estas preguntas se responden con una simple consulta SQL, no con una investigación forense de logs de texto desordenados. Esa tranquilidad es lo que vendemos como ingeniería de software profesional.