Soft deletes: implementando exclusão lógica com segurança

1. Conceitos Fundamentais de Soft Delete

A exclusão lógica (soft delete) é uma técnica onde registros não são removidos fisicamente do banco de dados, mas marcados como "excluídos" através de um campo de controle. Diferentemente da exclusão física (hard delete), que remove permanentemente os dados com DELETE FROM, o soft delete preserva a informação para auditoria, recuperação e consistência histórica.

Por que usar soft delete?
- Auditoria completa de exclusões
- Recuperação rápida de dados excluídos acidentalmente
- Manutenção da integridade referencial em relatórios históricos
- Conformidade com requisitos regulatórios (LGPD, GDPR)

Desvantagens e armadilhas:
- Crescimento acelerado da tabela com registros "mortos"
- Complexidade adicional em consultas (sempre filtrar por WHERE deleted_at IS NULL)
- Risco de violação de chaves únicas em colunas que exigem exclusividade

2. Modelagem de Tabelas para Soft Delete

A modelagem correta é crucial. Recomenda-se adicionar as seguintes colunas de controle:

CREATE TABLE usuarios (
    id SERIAL PRIMARY KEY,
    nome VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP DEFAULT NULL,
    deleted_by INTEGER REFERENCES usuarios(id),
    is_active BOOLEAN GENERATED ALWAYS AS (deleted_at IS NULL) STORED
);

Escolha do tipo de dado:
- deleted_at TIMESTAMP NULL: mais flexível, permite saber quando foi excluído
- is_active BOOLEAN: simples, mas não registra data/hora
- Flag enumerada (ex: status VARCHAR(20) DEFAULT 'active'): útil para múltiplos estados

Índices parciais para otimização:

CREATE INDEX idx_usuarios_active ON usuarios (id) WHERE deleted_at IS NULL;
CREATE INDEX idx_usuarios_deleted ON usuarios (deleted_at) WHERE deleted_at IS NOT NULL;

3. Implementando a Lógica de Exclusão

Comando UPDATE padrão:

UPDATE usuarios 
SET deleted_at = CURRENT_TIMESTAMP, 
    deleted_by = 1 
WHERE id = 42;

Gatilho (trigger) para soft delete em cascata:

CREATE OR REPLACE FUNCTION soft_delete_pedidos()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE pedidos SET deleted_at = CURRENT_TIMESTAMP 
    WHERE usuario_id = OLD.id AND deleted_at IS NULL;
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

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

View lógica para ocultar registros excluídos:

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

4. Consultas Seguras com Soft Delete

Adicionando filtro obrigatório:

-- Consulta segura
SELECT * FROM usuarios WHERE deleted_at IS NULL AND email = 'exemplo@teste.com';

-- JOIN com soft delete
SELECT u.nome, p.descricao
FROM usuarios u
LEFT JOIN pedidos p ON p.usuario_id = u.id AND p.deleted_at IS NULL
WHERE u.deleted_at IS NULL;

Função auxiliar para garantir o filtro:

CREATE OR REPLACE FUNCTION apenas_ativos()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'SELECT' THEN
        -- Força o filtro em todas as consultas
        SET LOCAL session_preload_libraries = 'auto_explain';
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

Cuidados com subconsultas:

-- ERRADO: pode retornar registros excluídos
SELECT * FROM usuarios WHERE id IN (SELECT usuario_id FROM pedidos WHERE status = 'ativo');

-- CORRETO: filtra também na subconsulta
SELECT * FROM usuarios 
WHERE id IN (SELECT usuario_id FROM pedidos WHERE status = 'ativo' AND deleted_at IS NULL)
AND deleted_at IS NULL;

5. Políticas de Segurança e Acesso (RLS)

Row-Level Security para soft delete:

-- Habilitar RLS na tabela
ALTER TABLE usuarios ENABLE ROW LEVEL SECURITY;

-- Política para usuários comuns
CREATE POLICY usuarios_ativos_policy ON usuarios
FOR SELECT
USING (deleted_at IS NULL);

-- Política para auditores (acesso total)
CREATE POLICY auditores_policy ON usuarios
FOR SELECT
TO auditor_role
USING (true);

Criação de roles específicas:

CREATE ROLE auditor_role;
CREATE ROLE admin_role;

GRANT SELECT ON usuarios TO auditor_role;
GRANT UPDATE (deleted_at, deleted_by) ON usuarios TO admin_role;

6. Estratégias de Performance e Manutenção

Particionamento por data de exclusão:

CREATE TABLE usuarios_part (
    id SERIAL,
    nome VARCHAR(100),
    deleted_at TIMESTAMP
) PARTITION BY RANGE (deleted_at);

CREATE TABLE usuarios_ativos PARTITION OF usuarios_part
FOR VALUES FROM ('1970-01-01') TO ('9999-12-31')
WITH (FILLFACTOR = 90);

CREATE TABLE usuarios_excluidos_2024 PARTITION OF usuarios_part
FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

Job agendado para exclusão física definitiva:

-- PostgreSQL com pg_cron
SELECT cron.schedule('cleanup-soft-deletes', '0 3 * * 0', $$
    DELETE FROM usuarios 
    WHERE deleted_at IS NOT NULL 
    AND deleted_at < CURRENT_TIMESTAMP - INTERVAL '1 year'
    AND id NOT IN (SELECT usuario_id FROM pedidos WHERE deleted_at IS NULL)
$$);

Índices compostos para consultas frequentes:

CREATE INDEX idx_usuarios_email_active 
ON usuarios (email) WHERE deleted_at IS NULL;

CREATE INDEX idx_usuarios_created_active 
ON usuarios (created_at DESC) WHERE deleted_at IS NULL;

7. Migração e Evolução do Esquema

Adicionando soft delete sem downtime:

-- 1. Adicionar coluna sem NOT NULL
ALTER TABLE usuarios ADD COLUMN deleted_at TIMESTAMP DEFAULT NULL;

-- 2. Criar índices parciais
CREATE INDEX CONCURRENTLY idx_usuarios_active ON usuarios (id) WHERE deleted_at IS NULL;

-- 3. Atualizar aplicação para usar soft delete
-- 4. Após validação, remover hard delete da aplicação

Script de backfill para registros históricos:

UPDATE usuarios 
SET deleted_at = '2024-01-01 00:00:00'
WHERE deleted_at IS NULL 
  AND NOT EXISTS (SELECT 1 FROM pedidos WHERE usuario_id = usuarios.id);

Testes de regressão:

-- Teste de consulta legada
SELECT COUNT(*) FROM usuarios; -- Deve retornar apenas ativos
SELECT COUNT(*) FROM usuarios WHERE deleted_at IS NULL; -- Deve ser igual ao anterior

8. Casos Especiais e Boas Práticas

Evitando violação de chaves únicas:

-- Solução: índice único parcial
CREATE UNIQUE INDEX idx_usuarios_email_unique_active 
ON usuarios (email) WHERE deleted_at IS NULL;

-- Permite emails duplicados apenas para registros excluídos
INSERT INTO usuarios (email) VALUES ('teste@teste.com'); -- OK
UPDATE usuarios SET deleted_at = NOW() WHERE email = 'teste@teste.com'; -- OK
INSERT INTO usuarios (email) VALUES ('teste@teste.com'); -- OK (novo registro ativo)

Relacionamentos com chaves estrangeiras:

-- Opção 1: ON DELETE SET NULL
ALTER TABLE pedidos 
ADD CONSTRAINT fk_pedidos_usuario 
FOREIGN KEY (usuario_id) REFERENCES usuarios(id) 
ON DELETE SET NULL;

-- Opção 2: ON DELETE CASCADE (cuidado!)
ALTER TABLE pedidos 
ADD CONSTRAINT fk_pedidos_usuario 
FOREIGN KEY (usuario_id) REFERENCES usuarios(id) 
ON DELETE CASCADE;

Naming conventions para consistência:

-- Padrão recomendado
deleted_at      TIMESTAMP    -- Data/hora da exclusão
deleted_by      INTEGER      -- Quem excluiu (FK para tabela de usuários)
is_deleted      BOOLEAN      -- Flag gerada (deleted_at IS NOT NULL)
restored_at     TIMESTAMP    -- Data/hora da restauração (opcional)

Boas práticas finais:
1. Sempre documentar a política de soft delete no schema
2. Criar views para todos os casos de uso comuns
3. Implementar testes automatizados que verifiquem o filtro
4. Monitorar o crescimento de registros excluídos
5. Definir claramente prazos para exclusão física definitiva

Referências