Proyecto práctico: Auditoría y versionado de registros en PostgreSQL

Objetivo: crear un sistema de auditoría que registre INSERT/UPDATE/DELETE sobre tablas (ej: users), guarde los estados antiguo y nuevo en JSONB, calcule un diff simple, y permita consultas eficientes del historial.

Requisitos previos

• PostgreSQL 11+ (se usan JSONB y funciones PL/pgSQL)
• Acceso psql o herramienta equivalente
• Conocimientos básicos de SQL y triggers

Estructura de carpetas

project-audit-postgres/
├─ migrations/
│  ├─ 001_create_tables.sql
│  ├─ 002_create_audit_function_and_triggers.sql
│  └─ 003_indexes_and_views.sql
├─ examples/
│  └─ usage_examples.sql
└─ README.md

Archivos principales (código completo)

migrations/001_create_tables.sql

-- Crea tabla de negocio de ejemplo
CREATE TABLE users (
  id bigserial PRIMARY KEY,
  username text NOT NULL UNIQUE,
  email text NOT NULL UNIQUE,
  data jsonb,
  created_at timestamptz DEFAULT now()
);

Por qué: una tabla sencilla con JSONB para campos dinámicos. JSONB te permite almacenar metadatos o atributos flexibles sin migraciones constantes.

migrations/002_create_audit_function_and_triggers.sql

-- Tabla de auditoría centralizada
CREATE TABLE audit_log (
  id bigserial PRIMARY KEY,
  table_name text NOT NULL,
  record_id text,
  operation text NOT NULL CHECK (operation IN ('INSERT','UPDATE','DELETE')),
  changed_by text,
  changed_at timestamptz DEFAULT now(),
  old_data jsonb,
  new_data jsonb,
  diff jsonb
);

-- Función de auditoría genérica
CREATE OR REPLACE FUNCTION audit_if_needed() RETURNS trigger AS $$
DECLARE
  v_old jsonb;
  v_new jsonb;
  v_diff jsonb;
BEGIN
  IF TG_OP = 'DELETE' THEN
    v_old := to_jsonb(OLD);
    v_new := NULL;
    v_diff := NULL;
  ELSIF TG_OP = 'INSERT' THEN
    v_old := NULL;
    v_new := to_jsonb(NEW);
    v_diff := NULL; -- puedes optar por mostrar el full record como diff
  ELSE
    v_old := to_jsonb(OLD);
    v_new := to_jsonb(NEW);
    -- Calcula diff sencillo: solo claves que cambiaron
    v_diff := (
      SELECT jsonb_object_agg(key, jsonb_build_object('old', v_old -> key, 'new', v_new -> key))
      FROM jsonb_object_keys(v_new) AS key
      WHERE (v_old -> key) IS DISTINCT FROM (v_new -> key)
    );
  END IF;

  INSERT INTO audit_log(table_name, record_id, operation, changed_by, old_data, new_data, diff)
  VALUES (TG_TABLE_NAME, COALESCE((NEW.id)::text, (OLD.id)::text), TG_OP, current_setting('audit.current_user', true), v_old, v_new, v_diff);

  -- Para triggers AFTER: devolver NEW para INSERT/UPDATE; para DELETE no importa
  IF TG_OP = 'DELETE' THEN
    RETURN OLD;
  ELSE
    RETURN NEW;
  END IF;
END;
$$ LANGUAGE plpgsql;

-- Trigger en la tabla users
CREATE TRIGGER users_audit
AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW EXECUTE FUNCTION audit_if_needed();

Por qué: una sola tabla de audit_log centraliza eventos. La función es genérica y usa TG_TABLE_NAME y TG_OP para reutilizarse en varias tablas. Usamos JSONB para almacenar snapshots y un diff para consultas más rápidas sobre cambios por campo.

migrations/003_indexes_and_views.sql

-- Índices para consultas frecuentes
CREATE INDEX idx_audit_table_name_changed_at ON audit_log(table_name, changed_at DESC);
CREATE INDEX idx_audit_record_id ON audit_log(record_id);

-- Vista específica para consultar historial de users
CREATE VIEW users_history AS
SELECT id AS audit_id, record_id, operation, changed_by, changed_at, old_data, new_data, diff
FROM audit_log
WHERE table_name = 'users'
ORDER BY changed_at DESC;

Por qué: índices para filtrar por tabla, por registro y ordenar por fecha. La vista facilita consultas desde la aplicación.

examples/usage_examples.sql

-- Ejemplos de uso: establecer usuario de auditoría a nivel de sesión
-- Es buena práctica que la app haga: SET LOCAL audit.current_user = 'username'; dentro de la transacción

-- Simula usuario 'alice'
SELECT set_config('audit.current_user', 'alice', true);

-- INSERT
INSERT INTO users(username, email, data) VALUES ('alice', 'alice@example.com', '{"role":"admin"}');

-- UPDATE (cambia email)
UPDATE users SET email = 'alice@newdomain.com' WHERE username = 'alice';

-- DELETE
DELETE FROM users WHERE username = 'alice';

-- Consultar historial de un record
-- Supongamos que el id era 1
SELECT * FROM users_history WHERE record_id = '1' ORDER BY changed_at DESC;

Por qué: set_config con scope LOCAL permite que el valor exista solo durante la transacción o conexión y evita que se mezcle entre requests en pools sin control si el app server no hace reset apropiado (ver apartado de advertencias).

Explicaciones y decisiones de diseño

• Triggers AFTER: garantizan que OLD/NEW reflejan el estado real del row; la inserción en audit_log ocurre en la misma transacción, por lo que si la transacción falla, nada queda registrado.
• JSONB: flexible, indexable y fácil de serializar. Permite almacenar snapshots completos y realizar queries dinámicas sobre campos.
• Diff simple: el cálculo usa jsonb_object_keys para iterar claves nuevas y comparar con el antiguo estado. No es una solución de diff perfecta (no maneja arrays complejos), pero cubre campos cambiados y es suficiente para la mayoría de auditorías CRUD.
• current_user/session var: usamos set_config('audit.current_user', '...') en la conexión. Alternativa: usar application_name, roles, o un campo en la app que la base actualice por cada conexión. Si usas poolers (pgbouncer en transaction mode), debes enviar el valor en cada transacción.
• Performance: una tabla audit_log puede crecer rápido. Planifica retención (archivado en S3, particionamiento por fecha, o limpieza periódica). Ten en cuenta el I/O adicional por cada change.

Prácticas recomendadas y seguridad

• Protege audit_log contra borrados accidentales: define políticas y roles que impidan DELETE/UPDATE directa por usuarios no administrativos.
• Considera particionamiento por rango (changed_at) si produces muchos eventos.
• Si necesitas integridad inmutable, exporta los logs a un sistema append-only (ej: Kafka, archivos firmados, o almacenamiento WORM).
• No confíes en valores de audit.current_user enviados por el cliente; establece este valor desde la capa de aplicación con credenciales seguras, y valida permisos con roles de DB si es necesario.

Siguientes pasos comunes

• Agregar triggers a otras tablas usando la misma función genérica.
• Mejorar el diff para manejar arrays y objetos anidados (recursión o librería PL).
• Agregar una API o vista que muestre cambios humanos (quién cambió qué campo y cuándo) para auditoría en UI.

Consejo avanzado: si necesitas audit trails absolutamente inmutables y verificables, exporta los eventos hacia una cola o sistema de ledger fuera de la base (por ejemplo, Kafka con compacted topics o un bucket con firma HMAC por batch). Esto evita que cambios en la misma base de datos borren o modifiquen el log.

Advertencia de seguridad: si usas connection pooling, asegúrate de establecer audit.current_user por transacción (SET LOCAL) y no confiar en que la variable persista del request anterior; de lo contrario se contaminarán registros con usuarios incorrectos.