Como implementar soft delete com auditoria em bancos relacionais

1. Fundamentos do Soft Delete e seus desafios

A exclusão lógica, ou soft delete, é uma técnica onde registros não são fisicamente removidos do banco de dados, mas marcados como "inativos" ou "excluídos". Diferente da exclusão física (DELETE), que remove permanentemente os dados, o soft delete preserva a informação para fins de auditoria, recuperação e compliance.

Problemas comuns:
- Integridade referencial: chaves estrangeiras podem quebrar se registros "excluídos" forem referenciados
- Performance de consultas: todas as queries precisam filtrar por deleted_at IS NULL, aumentando a complexidade
- Rastreabilidade: sem uma tabela de auditoria dedicada, perde-se quem, quando e por que excluiu

Quando optar por soft delete:
- Recuperação de dados acidentalmente excluídos
- Requisitos de compliance (LGPD, GDPR, SOX)
- Histórico de alterações para análise forense

2. Estrutura de tabelas para soft delete com auditoria

Colunas essenciais na tabela principal

CREATE TABLE usuarios (
    id SERIAL PRIMARY KEY,
    nome VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP DEFAULT NULL,
    deleted_by INTEGER DEFAULT NULL,
    deleted_reason VARCHAR(255) DEFAULT NULL
);

Flag booleano vs. timestamp:
- Timestamp (recomendado): permite saber quando a exclusão ocorreu, além de possibilitar consultas temporais
- Flag booleano: mais simples, mas perde a informação temporal

Tabela de auditoria separada

CREATE TABLE audit_log (
    id SERIAL PRIMARY KEY,
    table_name VARCHAR(50) NOT NULL,
    record_id INTEGER NOT NULL,
    operation VARCHAR(10) NOT NULL,  -- 'DELETE', 'UPDATE', 'INSERT'
    old_data JSONB,
    new_data JSONB,
    changed_by INTEGER NOT NULL,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    reason VARCHAR(255)
);

3. Implementação da lógica de soft delete no banco de dados

Trigger para soft delete com auditoria

CREATE OR REPLACE FUNCTION soft_delete_user()
RETURNS TRIGGER AS $$
BEGIN
    -- Captura dados antigos para auditoria
    INSERT INTO audit_log (
        table_name, record_id, operation,
        old_data, new_data, changed_by, reason
    ) VALUES (
        TG_TABLE_NAME, OLD.id, 'DELETE',
        row_to_json(OLD),
        row_to_json(NEW),
        NEW.deleted_by,
        NEW.deleted_reason
    );

    -- Marca como excluído
    NEW.deleted_at = CURRENT_TIMESTAMP;
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_soft_delete_user
    BEFORE UPDATE ON usuarios
    FOR EACH ROW
    WHEN (NEW.deleted_at IS NOT NULL AND OLD.deleted_at IS NULL)
    EXECUTE FUNCTION soft_delete_user();

View para filtrar registros ativos automaticamente

CREATE VIEW active_users AS
SELECT * FROM usuarios
WHERE deleted_at IS NULL;

4. Camada de aplicação: regras de negócio e consultas

Repositório com soft delete e log de auditoria (exemplo em Python)

class UserRepository:
    def soft_delete(self, user_id: int, deleted_by: int, reason: str):
        query = """
            UPDATE usuarios
            SET deleted_at = CURRENT_TIMESTAMP,
                deleted_by = %s,
                deleted_reason = %s
            WHERE id = %s AND deleted_at IS NULL
            RETURNING *;
        """
        cursor.execute(query, (deleted_by, reason, user_id))
        return cursor.fetchone()

    def find_active(self, user_id: int):
        query = "SELECT * FROM usuarios WHERE id = %s AND deleted_at IS NULL;"
        cursor.execute(query, (user_id,))
        return cursor.fetchone()

    def list_active(self):
        query = "SELECT * FROM usuarios WHERE deleted_at IS NULL;"
        cursor.execute(query)
        return cursor.fetchall()

Cuidados com joins e índices:
- Adicione índices compostos em (deleted_at, id) para melhorar performance
- Em joins, sempre inclua AND t.deleted_at IS NULL nas condições

5. Estratégias de recuperação e histórico

Restaurar registro "excluído"

UPDATE usuarios
SET deleted_at = NULL,
    deleted_by = NULL,
    deleted_reason = NULL,
    updated_at = CURRENT_TIMESTAMP
WHERE id = 123 AND deleted_at IS NOT NULL;

Consultar histórico de exclusões

SELECT 
    a.changed_at AS data_exclusao,
    u.nome AS usuario_excluido,
    a.changed_by AS id_operador,
    a.reason AS motivo
FROM audit_log a
JOIN usuarios u ON a.record_id = u.id
WHERE a.table_name = 'usuarios'
  AND a.operation = 'DELETE'
  AND a.changed_at BETWEEN '2024-01-01' AND '2024-12-31'
ORDER BY a.changed_at DESC;

6. Considerações de segurança e compliance

Proteção contra exclusão acidental

-- Permissão específica para soft delete
REVOKE UPDATE ON usuarios FROM PUBLIC;
GRANT UPDATE (deleted_at, deleted_by, deleted_reason) 
    ON usuarios TO app_role;

Imutabilidade dos logs de auditoria

Para evitar alterações retroativas nos logs:
- Use tabelas com permissões somente de INSERT para audit_log
- Configure triggers que impedem UPDATE/DELETE na tabela de auditoria
- Considere usar um banco separado ou append-only storage

Atendimento a regulamentações (LGPD, GDPR)

O direito ao esquecimento pode conflitar com soft delete. Estratégias:
- Anonimizar dados pessoais em registros "excluídos"
- Manter apenas metadados (datas, IDs) sem dados PII
- Implementar exclusão física real após período de retenção

7. Boas práticas e armadilhas comuns

Cuidados com chaves únicas e constraints

Registros "excluídos" podem violar constraints UNIQUE. Soluções:
- Incluir deleted_at na constraint única
- Usar índices parciais: CREATE UNIQUE INDEX ... WHERE deleted_at IS NULL

Estratégias de limpeza de dados

-- Exclusão física de registros com mais de 5 anos
DELETE FROM usuarios 
WHERE deleted_at IS NOT NULL 
  AND deleted_at < CURRENT_TIMESTAMP - INTERVAL '5 years';

Testes de integração

-- Teste: soft delete não remove registro fisicamente
BEGIN;
UPDATE usuarios SET deleted_at = CURRENT_TIMESTAMP WHERE id = 1;
SELECT COUNT(*) FROM usuarios WHERE id = 1; -- deve retornar 1
ROLLBACK;

-- Teste: auditoria registra a exclusão
BEGIN;
UPDATE usuarios SET deleted_at = CURRENT_TIMESTAMP, deleted_by = 10 WHERE id = 1;
SELECT COUNT(*) FROM audit_log WHERE table_name = 'usuarios' AND operation = 'DELETE';
ROLLBACK;

Referências