Soft delete que vaza: registro apagado que volta a aparecer em consulta, relatório e índice único

Um DELETE de verdade é uma garantia do banco: depois do commit, nenhuma consulta, índice, relatório ou job encontra a linha, sem que ninguém precise fazer nada. O soft delete troca essa garantia por uma convenção. A linha continua existindo, e a exclusão vira um predicado, excluido_em IS NULL, que precisa ser repetido em todo lugar que lê a tabela. Uma regra que precisa ser lembrada em cem lugares vai ser esquecida em algum, e basta um.

Quem le a tabela usuarios             Lembra de excluido_em IS NULL?
----------------------------------    ---------------------------------------------
API, pelo ORM com escopo padrao       sim
Relatorio de cobranca (SQL cru)       nao: conta 212 assentos, 187 estao ativos
Tela de escala (JOIN com turnos)      so em turnos: mostra o nome de quem ja saiu
Job do resumo semanal por e-mail      nao: continua enviando para quem saiu
Indexador da busca                    nao: o apagado aparece no autocompletar
Restricao UNIQUE (empresa, e-mail)    nao tem como: bloqueia o recadastro

O escopo padrão do ORM dá a impressão de que o problema está resolvido, porque as telas principais passam por ele. Só que a tabela tem outros leitores, e o ORM não fala por eles. A tabela abaixo liga cada chamado do mês à sua causa e ao motivo de ninguém ter percebido antes.

O esquema abaixo é o ponto de partida mais comum: uma coluna excluido_em, nula enquanto o registro está vivo, e uma restrição UNIQUE criada antes de alguém pensar em soft delete. Cada comando em seguida estava correto para quem o escreveu, e cada um vaza de um jeito diferente.

CREATE TABLE usuarios (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  empresa_id  bigint      NOT NULL REFERENCES empresas (id),
  email       text        NOT NULL,
  nome        text        NOT NULL,
  criado_em   timestamptz NOT NULL DEFAULT now(),
  excluido_em timestamptz,                 -- NULL = vivo
  UNIQUE (empresa_id, email)               -- criada antes de existir soft delete
);

-- "Apagar" e um UPDATE. A linha continua na tabela.
UPDATE usuarios SET excluido_em = now() WHERE id = 42;

-- Vazamento 1: relatorio de cobranca em SQL cru conta os apagados
SELECT empresa_id, count(*) AS assentos
FROM usuarios
GROUP BY empresa_id;

-- Vazamento 2: o filtro foi lembrado em turnos e esquecido em usuarios
SELECT t.id, t.inicio, u.nome
FROM turnos t
JOIN usuarios u ON u.id = t.usuario_id
WHERE t.excluido_em IS NULL
  AND t.inicio >= now();

-- Vazamento 3: no LEFT JOIN, o filtro no WHERE faz o turno de um usuario apagado
-- sumir do resultado. A condicao tem de ir no ON para ele aparecer sem responsavel.
SELECT t.id, t.inicio, u.nome
FROM turnos t
LEFT JOIN usuarios u ON u.id = t.usuario_id
WHERE t.excluido_em IS NULL
  AND u.excluido_em IS NULL;

-- Vazamento 4: a Carla foi apagada em marco e voltou para a empresa
INSERT INTO usuarios (empresa_id, email, nome)
VALUES (7, '[email protected]', 'Carla Nunes');
-- ERROR: duplicate key value violates unique constraint "usuarios_empresa_id_email_key"

• O relatório de cobrança conta linhas, e linha apagada é linha. Ele foi escrito em SQL cru, fora do ORM, e o escopo padrão que protegia a API nunca passou por ali.
• No JOIN, o filtro precisa existir uma vez para cada tabela com soft delete. Quem escreveu lembrou de turnos e esqueceu de usuarios, e a escala passou a exibir o nome de quem já saiu.
• No LEFT JOIN, corrigir colocando o filtro no WHERE cria outro defeito: o turno atribuído a um usuário apagado some do resultado, em vez de aparecer como sem responsável. A condição sobre a tabela da direita precisa estar na cláusula ON.
• A restrição UNIQUE compara valores e não conhece a regra de negócio. Para ela, a Carla apagada em março ainda ocupa o e-mail, e o recadastro falha com violação de unicidade.

Nenhum desses defeitos aparece em desenvolvimento, onde quase não existe registro apagado, nem em teste, onde as fixtures só criam usuários vivos. Eles crescem com a idade do sistema: quanto mais antiga a base, maior a proporção de linhas apagadas e mais visível cada consulta que esqueceu o filtro.

O recadastro falha porque a pergunta que a restrição responde, existe outra linha com este e-mail, não é a pergunta do negócio, existe outro usuário vivo com este e-mail. No PostgreSQL a resposta certa é um índice único parcial, que só inclui as linhas que satisfazem um predicado. Uma restrição UNIQUE declarada na tabela não aceita WHERE, por isso a regra passa a ser um índice.

-- 1. Cria primeiro a regra nova: unicidade so entre os vivos.
--    CONCURRENTLY nao bloqueia escrita e nao pode rodar dentro de uma transacao.
CREATE UNIQUE INDEX CONCURRENTLY usuarios_email_vivo_uk
  ON usuarios (empresa_id, email)
  WHERE excluido_em IS NULL;

-- 2. So entao remove a restricao antiga, que contava os apagados.
ALTER TABLE usuarios DROP CONSTRAINT usuarios_empresa_id_email_key;

-- 3. Indice parcial tambem para as listagens: menor e so com as linhas que a tela usa.
CREATE INDEX CONCURRENTLY usuarios_empresa_nome_vivo_idx
  ON usuarios (empresa_id, nome)
  WHERE excluido_em IS NULL;

-- Upsert precisa repetir o predicado para o Postgres escolher o indice parcial
INSERT INTO usuarios (empresa_id, email, nome)
VALUES ($1, $2, $3)
ON CONFLICT (empresa_id, email) WHERE excluido_em IS NULL
DO UPDATE SET nome = EXCLUDED.nome;

• A armadilha mais comum é trocar a restrição por UNIQUE (empresa_id, email, excluido_em). Como dois NULL não são considerados iguais para fins de unicidade, duas linhas vivas com o mesmo e-mail passam, e a regra some justamente para os registros que importam. A partir do PostgreSQL 15 é possível declarar NULLS NOT DISTINCT, mas o índice parcial expressa a intenção de forma mais direta.
• No MySQL, que não tem índice parcial, o equivalente é uma coluna gerada que vale 1 quando o registro está vivo e NULL quando está apagado, incluída no índice único: as linhas apagadas ficam com NULL e deixam de colidir. No SQL Server, o índice filtrado com WHERE excluido_em IS NULL cumpre o mesmo papel.
• Restaurar um registro passa a poder falhar: se alguém recadastrou o mesmo e-mail enquanto o antigo estava apagado, desfazer a exclusão viola o índice. Esse é o comportamento correto. Trate o erro 23505 na restauração como conflito e ofereça unir os cadastros, em vez de devolver um erro 500.
• A validação de unicidade feita na aplicação precisa seguir a mesma regra. Se o ORM valida olhando só os vivos e o banco ainda considera os apagados, ou o contrário, o usuário recebe um erro genérico no lugar da mensagem de validação.
• O índice parcial das listagens é menor que o índice completo e contém exatamente as linhas que as telas consultam. Para o planejador usá-lo, a consulta precisa trazer o mesmo predicado, excluido_em IS NULL, o que a visão da próxima seção garante.

Corrigir as consultas que vazaram resolve o chamado de hoje e nada mais. A próxima consulta, escrita daqui a seis meses por alguém que não acompanhou este incidente, vai esquecer o filtro de novo. A correção duradoura é inverter o padrão: ler os vivos passa a ser o caminho sem esforço, e alcançar os apagados passa a exigir uma decisão explícita. No PostgreSQL isso se faz com uma visão e com privilégios.

BEGIN;
SET LOCAL lock_timeout = '3s';

-- A tabela fisica ganha um nome que diz o que ela contem
ALTER TABLE usuarios RENAME TO usuarios_todos;

-- O nome antigo passa a ser a visao so com os vivos: consultas e ORM continuam funcionando
CREATE VIEW usuarios AS
  SELECT id, empresa_id, email, nome, criado_em, excluido_em
  FROM usuarios_todos
  WHERE excluido_em IS NULL;

-- A aplicacao so enxerga a visao, e nao recebe DELETE.
-- A tabela fisica fica para os papeis de suporte, faturamento e expurgo.
REVOKE ALL ON usuarios_todos FROM app_api;
GRANT SELECT, INSERT, UPDATE ON usuarios TO app_api;

COMMIT;

-- Apagar continua sendo um UPDATE, agora pela visao.
-- Depois dele a linha deixa de existir para o papel da aplicacao.
UPDATE usuarios SET excluido_em = now() WHERE id = $1;

A tabela física ganha um nome que diz o que ela contém, e o nome antigo vira uma visão que só mostra os vivos. Visões simples sobre uma única tabela são atualizáveis no Postgres, então INSERT e UPDATE continuam funcionando pelo nome de sempre e o ORM não percebe a troca. Por padrão, uma visão acessa a tabela com os privilégios do seu dono, por isso o papel da aplicação lê e grava por ela sem ter nenhum acesso à tabela física. O SQL cru de um relatório, um JOIN novo ou um script que use a conexão da aplicação simplesmente não tem como enxergar um apagado. E como o papel não recebeu DELETE, uma exclusão física acidental falha com erro de permissão em vez de destruir dado.

• A lista de colunas da visão é fixada na criação. Coluna nova na tabela física exige recriar a visão na mesma migração, e vale um teste que compare as duas listas.
• Papéis que precisam ver os apagados, como o suporte que restaura cadastros, o job de expurgo e o faturamento por período, recebem acesso à tabela física. São poucos, e neles o filtro é uma decisão consciente.
• A ferramenta de BI e a réplica analítica devem conectar com um papel que também só enxerga as visões. É por ali que sai a maior parte dos números errados.
• O RENAME pede um lock exclusivo por um instante. O lock_timeout curto faz a migração desistir e ser repetida, em vez de ficar na fila atrás de uma transação longa segurando todas as outras consultas da tabela.

A visão não é a única forma de centralizar a regra. A tabela a seguir compara as estratégias pelo que cada uma realmente protege.

Com unicidade e leitura resolvidas no banco, sobra o que nenhum índice ou visão alcança: os dados e sistemas que dependem do registro apagado. Em um DELETE de verdade, a chave estrangeira obriga a decidir o destino dos filhos. No soft delete a linha continua lá, a chave estrangeira continua satisfeita e nada obriga ninguém a decidir nada.

• Filhos: decida por tabela filha se a exclusão do pai apaga junto, desvincula ou é bloqueada. Turnos futuros voltam para a fila de sem responsável, e turnos passados ficam intactos porque são histórico. Quando apagar em cascata, grave o mesmo instante em excluido_em do pai e dos filhos, para que restaurar o pai consiga restaurar exatamente os filhos que caíram com ele.
• Credenciais: a consulta de autenticação costuma ler só a tabela de tokens ou de sessões. Se ela não passa pelo usuário, o token de um usuário apagado continua valendo. Revogue tokens e sessões na mesma transação da exclusão.
• Sistemas derivados: índice de busca, cache e data warehouse guardam cópias. Soft delete é um UPDATE, então a captura de mudanças do banco entrega uma atualização, e o consumidor que só remove documentos ao ver um evento de exclusão nunca remove este. Publique um evento explícito de usuário excluído, gravado na mesma transação.
• Jobs e notificações: o resumo semanal lê destinatários de uma tabela de preferências. Qualquer tabela que guarde uma referência ao usuário e seja lida sozinha é um ponto de vazamento.

// Excluir um usuario e uma operacao de negocio, nao um UPDATE solto:
// tudo o que depende dele muda na mesma transacao.
export async function excluirUsuario(pool, empresaId, usuarioId) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    const { rowCount } = await client.query(
      'UPDATE usuarios SET excluido_em = now() WHERE id = $1 AND empresa_id = $2',
      [usuarioId, empresaId],
    );
    if (rowCount === 0) {
      await client.query('ROLLBACK');
      return false; // inexistente ou ja apagado: a visao nao o enxerga mais
    }
    // Credenciais deixam de valer junto com o usuario
    await client.query('DELETE FROM tokens_api WHERE usuario_id = $1', [usuarioId]);
    // Turnos futuros voltam para a fila de sem responsavel; o historico fica intacto
    await client.query(
      'UPDATE turnos SET usuario_id = NULL WHERE usuario_id = $1 AND inicio >= now()',
      [usuarioId],
    );
    // Busca, cache e data warehouse ficam sabendo por um evento explicito (outbox)
    await client.query('INSERT INTO eventos_saida (tipo, payload) VALUES ($1, $2)', [
      'usuario.excluido',
      JSON.stringify({ usuarioId, empresaId }),
    ]);
    await client.query('COMMIT');
    return true;
  } catch (erro) {
    await client.query('ROLLBACK').catch(() => {});
    throw erro;
  } finally {
    client.release();
  }
}

Relatórios de período são um caso à parte, porque neles a resposta certa não é nem todas as linhas nem só os vivos de hoje. A fatura com 212 assentos estava errada, mas cobrar os 187 vivos no dia do fechamento também estaria: 4 dos 25 apagados foram removidos durante o mês faturado e, pelo contrato, usuário ativo em qualquer momento do período é cobrado. O número correto era 191. Esse tipo de consulta precisa enxergar os apagados de propósito, com um papel que tenha esse acesso, e precisa de uma definição de ativo no período escrita no SQL.

-- Assentos faturaveis: quem esteve ativo em algum momento do periodo.
-- Roda com o papel de faturamento, que le a tabela fisica de proposito.
SELECT empresa_id, count(*) AS assentos
FROM usuarios_todos
WHERE criado_em < $2                               -- fim do periodo (exclusivo)
  AND (excluido_em IS NULL OR excluido_em >= $1)   -- inicio do periodo
GROUP BY empresa_id;

É por isso que excluido_em deve ser um instante e não um booleano: só com a data é possível responder quem estava ativo em março, expurgar por janela de retenção e restaurar em conjunto o que foi apagado junto.

Soft delete sem expurgo é só uma tabela que cresce para sempre. As linhas apagadas pesam nos índices completos, nos backups e nas varreduras, e continuam sendo dado pessoal guardado. Um pedido de eliminação com base na LGPD ou no GDPR não é atendido por uma coluna excluido_em preenchida: o dado continua na tabela, no índice de busca e nas exportações. Defina uma janela de retenção por tabela, o tempo em que restaurar ainda faz sentido, e depois dela apague de verdade.

-- Indice so com os apagados: o expurgo nao varre a tabela inteira
CREATE INDEX CONCURRENTLY usuarios_expurgo_idx
  ON usuarios_todos (excluido_em)
  WHERE excluido_em IS NOT NULL;

-- Job diario, com o papel de expurgo: apaga de verdade o que passou da janela de retencao.
-- Repita ate afetar zero linhas. SKIP LOCKED evita disputar linha com uma restauracao em curso.
WITH lote AS (
  SELECT id
  FROM usuarios_todos
  WHERE excluido_em < now() - interval '90 days'
  ORDER BY excluido_em
  LIMIT 1000
  FOR UPDATE SKIP LOCKED
)
DELETE FROM usuarios_todos u
USING lote
WHERE u.id = lote.id;

• Apague em lotes pequenos e repita até não sobrar linha. Um DELETE único de milhões de linhas segura locks por minutos, gera um pico de WAL e atrasa as réplicas.
• Os filhos precisam de destino antes do pai: ON DELETE CASCADE, SET NULL ou expurgo próprio. E toda coluna de chave estrangeira que aponta para a tabela precisa de índice, senão cada lote varre as tabelas filhas inteiras.
• Quando a retenção for obrigatória por motivo fiscal ou de auditoria, anonimize em vez de apagar: troque nome e e-mail por valores neutros e mantenha o id, para que o histórico continue íntegro sem identificar a pessoa.
• O expurgo também precisa chegar aos sistemas derivados e respeitar a rotação dos backups. Documente em quanto tempo um dado apagado deixa de existir em todos os lugares.

Por fim, a prova. O teste abaixo conecta com o mesmo papel da aplicação, cria um usuário sentinela, apaga e percorre todas as funções de leitura exportadas pelo módulo de consultas, exigindo que a sentinela não apareça em nenhuma. Uma consulta nova entra na verificação só por ser exportada, sem depender de alguém se lembrar de escrever o teste dela. Os outros dois testes fixam as garantias do banco: o e-mail de um apagado pode ser reutilizado, dois vivos não dividem e-mail e a aplicação não alcança a tabela física.

import test, { after } from 'node:test';
import assert from 'node:assert/strict';
import pg from 'pg';
import * as consultas from './consultas.js'; // toda leitura que a aplicacao expoe
import { excluirUsuario } from './usuarios.js';

// Conecta com o mesmo papel da aplicacao (app_api), nao com o dono do banco.
// Pressupoe as migracoes aplicadas e a empresa 1 criada pelo seed.
const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL });
after(() => pool.end());

async function criarUsuario(empresaId, email) {
  const { rows } = await pool.query(
    'INSERT INTO usuarios (empresa_id, email, nome) VALUES ($1, $2, $3) RETURNING id',
    [empresaId, email, 'Sentinela'],
  );
  return rows[0].id;
}

test('usuario apagado nao aparece em nenhuma leitura da aplicacao', async () => {
  const sentinela = 'sentinela.' + Date.now() + '@exemplo.com';
  const id = await criarUsuario(1, sentinela);
  await pool.query(
    "INSERT INTO turnos (empresa_id, usuario_id, inicio) VALUES (1, $1, now() + interval '1 day')",
    [id],
  );
  assert.equal(await excluirUsuario(pool, 1, id), true);

  // Cada funcao exportada de consultas.js recebe (pool, empresaId) e devolve linhas
  for (const [nome, consulta] of Object.entries(consultas)) {
    const linhas = await consulta(pool, 1);
    assert.ok(!JSON.stringify(linhas).includes(sentinela), 'vazou em ' + nome);
  }
});

test('e-mail de apagado pode ser recadastrado, mas dois vivos nao dividem e-mail', async () => {
  const email = 'carla.' + Date.now() + '@exemplo.com';
  const id = await criarUsuario(1, email);
  await excluirUsuario(pool, 1, id);
  await criarUsuario(1, email); // o recadastro funciona
  await assert.rejects(criarUsuario(1, email), { code: '23505' }); // unique_violation
});

test('o papel da aplicacao nao alcanca a tabela fisica', async () => {
  // 42501 = insufficient_privilege
  await assert.rejects(pool.query('SELECT 1 FROM usuarios_todos LIMIT 1'), { code: '42501' });
});

Rode contra um Postgres real, em contêiner, com as migrações aplicadas e o papel app_api criado. A garantia que se quer provar está nos privilégios e nos índices, e é exatamente isso que um banco em memória ou um mock não reproduz.